Arthur Koziel
A fresh supply of thoughts about Web Development & Mac OS X
Symfony has been around for quite some time now and most developers have their prefered way of doing things - time to ask a question that has been floating around for a very long time:
How do you keep your model and database in sync?
A) You build your database in some other tools (e.g. phpMyAdmin) and run propel-build-schema followed by propel-build-model.
B) You write a schema.yml/xml file and run propel-build-all to generate the model, create and insert the sql.
Please state your opinion (or maybe a even better way) in the comments below.
Post a Comment
© 2008 Arthur Koziel — About | Archive | Colophon | Contact | Feeds
Bookmarks 
- IEBlog : IE8 Security Part IV: The XSS Filter
- IE8 will include and enable an XSS filter by default. To disable it, a custom HTTP header needs to be set.
- YAXML
- YAXML is a perl script which transforms YAML into XML and back.
- Visual language 1.0
- PDF describing the BBC.co.uk global page restructure.
Comments
I use xcase, work locally, build locally, and transfer db changes with this tool. Been using it for over a year now, cost about 200 bucks for mysql. Well worth it.
On development or fresh installation, I would run propel-build-all-load.
On upgrade or existing production machine, have to do a dump of data only from the database, then run propel-build-all, then import the data back.
If only there is a command option of propel-upgrade-db, that would be wonderful. I think it is possible, since the database is in schema format. It just need to compare existing database schema and execute the necessarily SQL.
I agree James with propel-upgrade-db task. Upgrading database needs now most of manual work when upgrading project to production machine.
I usually store any sql used to upgrade dev database in a wiki, then when we go production i copy paste every pending updgrade sql to production sql server, after disabling the application. If then everything seems ok, I activate the application again, and until now it always went ok, as long as every upgrade sql was in the wiki.
But that’s interesting discussion, as it is a problem for everybody, I’m not sure of how an hypothetical propel-upgrade-db would work.
As I wrote not too long ago in an article on my own weblog, I prefer to write batch scripts that do the upgrades. In those batch scripts, I execute the alter queries on the production database.
Doctrine has support for database change management in the form of migrations, but I haven’t used it yet so I’m not sure how good it is. Who knows, Propel might also implement a similar system.
B
Most of the time, when creating a new project, I start by completing my schema.xml file. Afterwards I run the symfony build-all function to recreate the model and database.
When starting the project or module, i try to make the schema as complete as possible.
If i need to adapt/change the model afterwards, i change the schema, rebuild the model but i do not insert sql. I manually patch the database using phpmyadmin, saving the sql statements for later patching on the production machine.
Would be nice to have a symfony task that can upgrade the database model at once. Could save us alot of time
http://svn.symfony-project.com.....unk/README
Thanks Kiril. I will try the plugin…looks like a great help.
In a dev environment, I usually change the schema, run build-model, then sync the my DB with manual ALTER queries MySQLQueryBrowser, in order to keep my datas ( when working on projects with a lot of fixtures, multiple erase/reload takes some times ).
When it’s done, I dump the datas ( complete inserts ) from the prod DB, then run propel-build-all-load and dump the datas back into the DB
One more vote for http://svn.symfony-project.com.....unk/README
We usually design our database around modelling tools (either DBDesigner4, phpMyAdmin or just simply MySQL Administrator) in InnoDB for foreign key restrictions, then propel-build-schema to generate the schema.yml. After that, we filter schema.yml with some custom tools for any Propel-related errors (such as invalid default values for fields, I18N options, etc.) and build the model.
For changing the schema, we usually “branch” the database by copying the schema, modifying, running MySQL Diff (http://www.mysqldiff.org/) to get the migration script between the two databases, make adjustments and then run the resulting patch on the original DB on staging, to ensure data integrity when running the same SQL script to upgrade on production.
Hi,
I’d like to ask, if it is possible to generate the schema.yml or schema.xml from existing file containing sql to build the database.
I am using Case Studio to create the model of database, and then I am able to generate SQL file to create the database.
It is possible to create the database a then use propel-build-schema to refactor schema.xml(yml). But what if I don’t want to do it throught existing database?
I’d like to use Case Studio to create the database model (it’s modeling tool). Than I am able to generate SQL. And now I want to generate schema.yml, correct it manualy if it is needed (set the phpNames, …). At the end I’d like to use propel-build-model, propel-insert-sql and propel-load-data.
Thanks for reply …
Don’t know if I’m late on this but we usually use the sfPropelSqlDiffPlugin (http://trac.symfony-project.com/wiki/sfPropelSqlDiffPlugin) to manage the changes.
The plugin checks the diffs between the model and the actual database.
We usually just run a build which creates a diff.sql file with all the ALTER requests. You can then either execute the requests directly in a MySQL client or just run propel-insert-sql-diff.
Hope this helps!