Schema Tool Update

Small update to the schema tool: it should now support MS SQL Server in addition to MySQL, courtesy of Joshua Frankamp. SQL Server doesn't have an unsigned int type and it requires a column list on INSERT statements. Based on his patch, I've fixed the syntax so that it'll work on both platforms without issue. I'd hope that it will work on pretty much any database (the SQL is very simple), but those are the only two that have been tested.

7 responses to “Schema Tool Update”

  1. Mark

    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. Mark

    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.

  3. denny

    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!

  4. denny

    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!