Schema Tool Update

7 responses to “Schema Tool Update”

1. 
   Mark

   July 30, 2007 at 2:25 pm | Permalink

   I like the look of your schema tool, I was just wanting a few pointers in getting it working properly, I have it set up, and it has created the table 'schema_version' and is dumping the output of this table. I was wondering if it should have also created the tables 'test_table' and 'another_test_table'. If I want to run the schema scripts (migration000001.cfc and migration000002.cfc) how should I do this?, is it a matter of adding a line in the application.cfm to get them running?.

2. 
   barneyb

   July 31, 2007 at 7:09 am | Permalink

   Mark,

   The schema_version table is a "system" table that the tool uses internally. test_table and another_test_table are tables "user" tables that the demo app creates for itself. In other words, every app managed by the tool will have a schema_version table, plus whatever table the app itself needs.

   Application.cfm (or wherever you have app initialization code) needs the magic line that creates and runs the schema tool, and that's it. As long as you point it at the right package (directory), it'll find all your scripts, and run them in sequence.

3. 
   Mark

   August 26, 2007 at 3:04 am | Permalink

   Thanks for the reply Barney, that cleared most things up for me, and I seem to have it working so that is creates a schema_version table in whatever application I have it in, I am really impressed with how this could potentially work. However I am still unclear as to how to run the scripts in the directory that is specified, for some reason I can't get these to run, even with the demo.

   The line I am using in Application.cfm is below, this creates the schema_version fine but does not run the demo scripts which I have put in a schema_scripts directory to test.

   createObject("component", "schema_tool.tool").init(application.config.dsn, "cms.schema_scripts").ensureCurrent("#application.applicationname#.schema_tool");

   Both schema_tool and cms are mapped locations. Any help would be much appreciated.

4. 
   barneyb

   August 26, 2007 at 7:12 pm | Permalink

   Mark,

   The "cms.schema_scripts" package should contain your scripts, named according to the spec of "migrationXXXXXX.cfc" or "migrationXXXXXX.sql", depending on whether you're using CFC or SQL-based scripts. There MUST be exactly six digits in each filename, and they MUST be numbered sequentially starting with one (or 000001).

   If you've set up your mappings correctly, the demo should definitely work, so I'm not sure what's up. The easiest way to check your mapping is to do an expandPath on it (#expandPath('/cms/schema_scripts')# for your example above), and then pass that to CFDIRECTORY and see what files it returns. It should return the list of migration scripts. If not, you're misconfigured somewhere.

   Hope that helps.

5. 
   denny

   September 2, 2007 at 5:47 pm | Permalink

   Hey Barney!

   Nice tool, but I've got a couple of questions:

   It bumps the version before it executes the migration, and I'm worried that if an error occurs, it'll read like the schema is up to date, when it isn't. I put try/catch/aborts around the migrate(s), and put the revision bump at the end, to see if that would do it… it sorta did.

   I'm still playing with it, so I don't know if that broke something…

   The other comment is, what about a team scenario, where two members may have updated the schema at the "same time" so to speak?

   I figured that if this tool works for me, that problem could be solved in the "business logic" (always check the repository prior to adding a migration script, say), but thought it was an interesting thought, or whatever. =]

   Glad that you won a wii, that's cool- I just checked out the projects that were submitted, and I'm kinda stoked- looks like there are a couple of them that would make great additions to what I hope to be a release of Model-Glue actionpacks to make RAD more rapid… eh. we'll see.

   Anyways, thanks for the code, esse!

6. 
   barneyb

   September 2, 2007 at 7:16 pm | Permalink

   Denny,

   There IS a bug in the incrementing of the major version. I've fixed it in SVN, but haven't made a new public release with the fix (one more entry on my list of things to do "sometime"). Since the major version only updates between migrations, the bug can only manifest itself if there is an issue with the first step of a given migration, but you're right, there is an issue there. The minor versions (steps within a migration) are double protected: the increment happens second, and the whole thing is wrapped in a CFTRANSACTION. The reason the major version increments first is for internal bookkeeping reasons. It's safe for it to increment even if the first migration step fails, because it'll be minor version zero (which means the first step is still next in line). The bug was in when the minor version was reset to zero; it happened at the wrong time before.

   For the two-developer conflict scenario, there's nothing the tool can do about it. You have to resolve the conflicts when the second developer commits his/her changes. Migration code does have some ordering ramifications, so resolving a conflict might not be as simple as otherwise (quite possibly requiring manual tweaks to the `schema_version` table), but it's really no different from any other conflict between multiple developers.

7. 
   denny

   September 2, 2007 at 9:34 pm | Permalink

   Right on, Barney!

   This is a really nice tool.

   For example, I'm about to change a bunch of selects that have values like this: |2008| to this: 2008 – since the database contains the old values, it will be sweet to hit them both at the same time.

   I realized a bit after I posted, that you'd handle "overwriting" in SVN (or whatever) as a conflict, before you'd commit it anyways. Unless you've got a sql file, and a cfc… hmmm…
   As with dreamweaver- look before you put. :]

   Love the svn:keyworded, well commented files as well. Good job, and thanks again!