*Tags*: symfony schema data types
Symfony understands the usual column types: boolean, integer, float, date,
varchar(size), longvarchar (converted, for instance, to text in MySQL), and
so on. For text content over 256 characters, you need to use the longvarchar
type, which has no size (but cannot exceed 65KB in MySQL).
Generating a YAML Data Model from an Existing Database
Symfony can use the Creole database access layer to generate a schema.yml
file from an existing database, thanks to introspection (the capability of
databases to determine the structure of the tables on which they are
operating). This can be particularly useful when you do reverse-engineering,
or if you prefer working on the database before working on the object model.
In order to do this, you need to make sure that the project propel.ini file
points to the correct database and contains all connection settings, and
then call the propel-build-schema command:
symfony propel-build-schema
A brand-new schema.yml file built from your database structure is generated
in the config/ directory. You can build your model based on this schema.
The schema-generation command is quite powerful and can add a lot of
database-dependent information to your schema. As the YAML format doesn't
handle this kind of vendor information, you need to generate an XML schema
to take advantage of it. You can do this simply by adding an xml argument to
the build-schema task:
symfony propel-build-schema xml
Instead of generating a schema.yml file, this will create a schema.xml file
fully compatible with Propel, containing all the vendor information. But be
aware that generated XML schemas tend to be quite verbose and difficult to
read.
|
The propel-build-sql and propel-build-schema tasks don't use the
connection settings defined in the databases.yml file. Rather, these tasks
use the connection settings in another file, called propel.ini and stored in
the project config/ directory |
documented on: 2007.02.09
By default, 'propel-load-data' replaces the existing database content.
However, the auto-increment primary key field would be different than fresh
loading the data, because the replacement is done by removing all the
existing records and adding new ones.
If you also want the auto-increment primary key field to be the same as
fresh loading, use the following command beforehand:
symfony propel-insert-sql
BTW, If the last argument call is append, the command will not erase the
current data but append them.
> Import fixtures feature is cool during the design/test process, it would be
> even cooler if we had export feature also.
You should make a search in the forum, this subjet already has an answer:
$data = new sfPropelData();
$data->dumpData(sfConfig::get('sf_data_dir').DIRECTORY_SEPARATOR.'fixtures/dump.yml');
documented on: 19 January 2007, francois
Dump existing data into YAML file
With symfony 1, this is now nice and easy….
Add your database settings to the config files.
Generate your schema from the database.
Then create and run the following batch file…
<?php
define('SF_ROOT_DIR', realpath(dirname(__FILE__).'/..'));
define('SF_APP', 'frontend');
define('SF_ENVIRONMENT', 'dev');
define('SF_DEBUG', true);
require_once(SF_ROOT_DIR.DIRECTORY_SEPARATOR.'apps'.DIRECTORY_SEPARATOR.SF_APP.DIRECTORY_SEPARATOR.'config'.DIRECTORY_SEPARATOR.'config.php');
// initialize database manager
$databaseManager = new sfDatabaseManager();
$databaseManager->initialize();
$data = new sfPropelData();
$data->dumpData(sfConfig::get('sf_data_dir').DIRECTORY_SEPARATOR.'fixtures/dump.yml');
?>
documented on: 19 December 2006, flat stanley
*Tags*: symfony model visualization schema UML diagram
Usage
dot -Tpng graph/schema.dot -o graph/schema.png
documented on: 2007.07.13
Model visualization with symfony
Adding some simple lines enables propel's graphviz dot file generation:
in /data/symfony/tasks/sfPakePropel.php add:
pake_desc('create a graphviz dot file for current model');
pake_task('propel-graphviz', 'project_exists');
function run_propel_graphviz($task, $args)
{
_propel_convert_yml_schema(false, 'generated-');
_propel_copy_xml_schema_from_plugins('generated-');
_call_phing($task, 'graphviz');
$finder = pakeFinder::type('file')->name('generated-*schema.xml');
pake_remove($finder, array('config', 'plugins'));
}
Now you can use it like that:
> symfony propel-graphviz
...
propel > graphviz:
[echo] +------------------------------------------+
[echo] | |
[echo] | Generating Graphiz for YOUR Propel |
[echo] | project! |
[echo] | |
[echo] +------------------------------------------+
...
This creates a folder "graph" in your projects folder containing a
"schema.dot" file which can be converted to e.g. a PNG file using the
graphviz package from http://www.graphviz.org/
Fonts, colors, shapes etc. can be customized.
documented on: 12 January 2007, roga
Model visualization with symfony
Terrific! After simply adding the following line, I got the schema map!!!
$ diff -wU 1 /usr/share/pear/data/symfony/tasks/sfPakePropel.php.org /usr/share/pear/data/symfony/tasks/sfPakePropel.php
--- /usr/share/pear/data/symfony/tasks/sfPakePropel.php.org 2007-02-22 09:49:14.000000000 -0500
+++ /usr/share/pear/data/symfony/tasks/sfPakePropel.php 2007-04-12 13:38:45.000000000 -0400
@@ -43,2 +43,5 @@
+pake_desc('create a graphviz dot file for current model');
+pake_task('propel-graphviz', 'project_exists');
+
function run_propel_convert_yml_schema($task, $args)
@@ -383 +386,10 @@
}
+
+function run_propel_graphviz($task, $args)
+{
+ _propel_convert_yml_schema(false, 'generated-');
+ _propel_copy_xml_schema_from_plugins('generated-');
+ _call_phing($task, 'graphviz');
+ $finder = pakeFinder::type('file')->name('generated-*schema.xml');
+ pake_remove($finder, array('config', 'plugins'));
+}
francois, why can't we put it directly in sfPakePropel.php, why a plugin
instead?
check it out and get the patch there.
Fonts, colors, shapes etc. can be customized….
12 January 2007 15:15
— roga wrote on Fri
roga, can you elaborate a bit more about them?
Having installed the graphviz package, it took me a while to figure out how
to open the .dot file, because there isn't a tool called graphviz. Only
after having tried each individual files did I know how to open it:
Model visualization with symfony
dot -Tpng input.dot -o output.png
— roga
dot -Tpng graph/schema.dot -o graph/schema.png
What's cool about it is that it can handle all dabase tables from plugins as
well. Here is an example from my
> Database name in schema's should always be propel.
This is incorrect. The database name in the schema.xml must correspond to
the name of the connection in databases.yml. If your connection is
"mydatabase" then the database name in schema.xml must be "mydatabase".
This point should be better explained in the docs, imo.
documented on: 23 January 2007, chtito
schema xml, database name and propel
Actually not incorrect, but perhaps half explained. By default the name is
always "propel" unless you change it.
documented on: 23 January 2007, Draven
schema xml, database name and propel
I had to work this stuff out today. With the aid of the snippets, and some
examination of Creole, I found that one can have:
all:
propel:
class: sfPropelDatabase
param:
datasource: framework
phptype: oracle
hostspec: host1
username: usr1
password: pwd1
tmsinternet:
class: sfPropelDatabase
param:
is_default: true
datasource: legacy
phptype: oracle
hostspec: host2
username: usr1
password: pwd1
One little feature I wasn't previously aware of is the is_default flag to
select which of the databases should be used when performing queries outside
of the model. More info available here:
documented on: 23 January 2007, halfer
if you using multiple database from Propel, It can be solve by writing two
or more schema files.
PROJECT_DIR/config/databases.yml
all:
database1:
class: sfPropelDatabase
param:
dsn: pgsql://foo:bar@hostname/database1
database2:
class: sfPropelDatabase
param:
dsn: mysql://foo:bar@hostname/database2
PROJECT_DIR/config/database1.schema.xml
<?xml version="1.0" encoding="UTF-8"?>
<database name="database1" defaultIdMethod="native" noxsd="true">
<table name="foo" phpName="Foo">
....
</table>
</database>
PROJECT_DIR/config/database2.schema.xml
<?xml version="1.0" encoding="UTF-8"?>
<database name="database2" defaultIdMethod="native" noxsd="true">
<table name="bar" phpName="Bar">
....
</table>
</database>
Last, build model command.
$ symfony propel-build-model
Build complete.
Example, getting multiple databases connection handler.
$database1_connection_handler = Propel::getConnection(FooPeer::DATABASE_NAME);
$database2_connection_handler = Propel::getConnection(BarPeer::DATABASE_NAME);
documented on: 2006-08-30, by Kota Sakoda
I used to use the following code when I update my schema.
symfony propel-build-model
symfony propel-build-sql
symfony propel-insert-sql
But now the system is in beta phase, with tables filled with data and the
user requesting to add extra fields to one table.
How should I tackle that?
Updating one table def
> But now the system is in beta phase, with tables filled with data and the user requesting to add extra fields to one table.
Do not use 'symfony propel-insert-sql'.
It will clear all existing data in the database.
It will generate a SQL-DDL that creates the database tables from the
schema.yml data model. The created file is
myproject/data/sql/lib.model.schema.sql.
That's all symfony needs to know about the data model. You can then either
use the schema.sql file directly to build the tables, or manually update a
single table def in database. For instance, in MySQL, type this:
mysql -u root -p blog < striped.schema.updated.sql
documented on: 29 October 2007, sfxpt
I was looking for the best way to implement a parent child relationship in a
single table. I use this to allow for unlimited sub-categories in an
ecommerce application.
Here is the method I included in my Category.php class for the parent_id
field.
implement table with parent child relationship
I've always been a fan of using a Modified Preorder Tree Traversal for
complex parent/Child tree structures. Someone even made a plugin to simplify
this with propel.
documented on: 05 September 2006, Draven
implement table with parent child relationship
I have also created a helper script to populate a select list with
categories with the child categories indented underneath.
PHP
-- Symfony
-- Smarty
-- Eclipse PHP
Linux
-- Distros
-- Fedora
-- Ubuntu
-- Kernel
I use these mainly on the admin section for adding products to categories
etc. You have to override the default template created for the module that
needs this helper. Basically copy the relevant file from cache and change
object_select_tag to object_select_tag_parent_child.
The code relies on the getTopLevel mehtod defined above being implmented.
|
|
Most of the code is the same as object_select_tag. The real
modifications are in the function getChildOptions. |
documented on: 19 September 2006, mclarke4
Nested Set
> create an application using the PostgreSQL?
Just add your PostgreSQL database credentials to ./config/databases.yml and
./config/propel.ini, remembering to set the database phptype type to
pgsql. Everything else you do is the same - just work through the various
examples and read the Symfony documentation.
Symfony + PostgreSQL
Here I am making the connection:
//databases.yml
all:
propel:
class: sfPropelDatabase
param:
dsn: pgsql://user:passwd@localhost/base
//propel.ini
propel.database = pgsql
propel.database.createUrl = pgsql://user:passwd@localhost
propel.database.url = pgsql://user:passwd@localhost/base
but I have some problems when I create a new project…
Problem solved. The problem was the structure of the file schema.yml
documented on: 09 April 2008, Alphax
