MySchemaShell - step closer to DB structure synchronization
I'm struggling with DB structure versions management. Recently I tried SchemaShell (cake schema), but found it quite inappropriate with the way the SVN works. Additionally one could only overwrite DB structure with schema file or overwrite schema file with database structure - that's not good for me. In early development phase fluent DB structure versions management is essential, and without decent tool - it's just pain in the ass. MySchemaShell is my first step to creating that tool.
First approach I get quite familiar with was http://bakery.cakephp.org/articles/view/cakephp-yaml-migrations-and-fixtures-without-pear, but when I tried this and SchemaSchell - there was almost no differences between this two tools. Both YAML and Schema approach have the sane cons (which I described in the intro), but Schema was more Cake'ish so I took that on my workshop.
The basic problem was that it is possibly problematic with using it with SVN. I couldn't think of any process without possible problems when generating schema from actual database (which have potentially changed development session):
Now I can get to the point. I needed that 'cake schema' would at least ask me about ADD's and DROPS he is planning to do, and call only that ADD's and DROP's I agree to. I want my schema to be only in one file (particular version is still in particular revision).
So now the scenario is:
- Yes
- my schema again - do You want me to DROP `just_added_field` from `table_you_work_on_right_now`?
- Of course not.
- my_schema here, altering 'users' table, adding `name` field
Example of that scenario:
Download code
I assume that ADD's are good, and DROP's are unwanted in most (of my) cases. So its possible to make my_schema not to ask about anything and remove all drops it could do in normal behavior:
Download code
Download code
If You want to use this tool - copy following code to /app/vendors/shells/my_schema.php
Thank You for reading. Any suggestions are most welcome.
Greg
The basic problem was that it is possibly problematic with using it with SVN. I couldn't think of any process without possible problems when generating schema from actual database (which have potentially changed development session):
- Team could always overvite one schema.php file. But the problem would be with conflicts. The conflicts could be resolved like any other conflict, but everyone in team should be disciplined enough to call 'schema generate' before any 'svn up'. If one forget it, and call 'svn up', make some DB changes 'call schema generate' and updated data are in some revision back there. So You can start for searching needle in the haystack... On the second hand - when some Peter add field in table 'X' and commit new schema, and I have some local changes in DB I need to choose:
- call 'schema generate' and overwrite Peters changes - not an option
- call 'schema run update' and choose [y] when it asks if I want to drop fields I've just added - eorse
- manually edit schema.php file and add my new fields into it - possible but inconvenient
-
Team could always generate snapshots instead of overwriting one file. But I don't need schema_1.php, schema_2.php ... schema_234.php in my repository when I have versions in SVN - its just redundancy. But what if two developers call 'schema generate snapshot' and both get schema_7.php. The faster one have no problem. He just calls 'svn add' and commits. But second tries to commit and get error: schema_7.php already in repository. He can rename it to schema_8.php but need to merge this changes manually - still inconvienient way to that
Now I can get to the point. I needed that 'cake schema' would at least ask me about ADD's and DROPS he is planning to do, and call only that ADD's and DROP's I agree to. I want my schema to be only in one file (particular version is still in particular revision).
So now the scenario is:
- svn up
- cake my_schema run update -with-check
- Yes
- my schema again - do You want me to DROP `just_added_field` from `table_you_work_on_right_now`?
- Of course not.
- my_schema here, altering 'users' table, adding `name` field
- my_schema generate (and choose overvrite when it asks)
- Now I have merged changes from repo and my DB in schema.php, so can call 'svn ci'
Example of that scenario:
Download code
./cake my_schema run update -with-check
Comparing Database to Schema...
in articles table we are about to...
...ADD field(s) named...
... dummy_add(type:integer; null:false; default:0)
Do You agree ?
[y] > y
... dummy_add2(type:integer; null:false; default:0)
Do You agree ?
[y] > y
...DROP field(s) named...
... dummy(type:integer; null:false; default:)
Do You agree ?
[n] > n
in categories table we are about to...
...ADD field(s) named...
... dummy2(type:integer; null:false; default:)
Do You agree ?
[y] > y
The following statements will run.
ALTER TABLE `articles`
ADD `dummy_add` int(11) DEFAULT 0 NOT NULL,
ADD `dummy_add2` int(11) DEFAULT 0 NOT NULL;
ALTER TABLE `categories`
ADD `dummy2` int(11) NOT NULL;
Are you sure you want to alter the tables? (y/n)
[n] >
I assume that ADD's are good, and DROP's are unwanted in most (of my) cases. So its possible to make my_schema not to ask about anything and remove all drops it could do in normal behavior:
Download code
./cake my_schema generate -with-check -add-only
Example:Download code
Comparing Database to Schema...
***Accorging to -add-only option there are 1 DROP'S ommited***
The following statements will run.
ALTER TABLE `articles`
ADD `dummy_add` int(11) DEFAULT 0 NOT NULL,
ADD `dummy_add2` int(11) DEFAULT 0 NOT NULL;
ALTER TABLE `categories`
ADD `dummy2` int(11) NOT NULL;
Are you sure you want to alter the tables? (y/n)
[n] >
If You want to use this tool - copy following code to /app/vendors/shells/my_schema.php
Thank You for reading. Any suggestions are most welcome.
Greg
Latest Comments