Coldfusion Migrations

[thoen]

I have been developing Rails applications for the last 2 years. But
since I still maintain some old CF apps, I am looking forward to using
cfwheels to make enhancing/maintaining the applications easier (as
long as I can figure out how to get cfwheels to play nice with my
existing app, and move things over as needed, if anyone has any ideas
or experience in this, I would be grateful for some advice).

One of the great benefits of Rails is Migrations. A great way to
manage schema changes on multiple servers/computers with multiple
developers. But I could not find anything similar with CF or any of
the CF frameworks (could have missed something, for sure).

So I put together some preliminary code. It is not built on cfwheels
(would be interested in thoughts on how to turn it into a plugin. The
link on the cfwheels site to "Using and Creating Plugins" seems to
take you to a summary cfwheels page).

I discuss the code at http://coldfusiononrails.blogspot.com/. Code can
be found at http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=141&lngWId=9
(A zip file).

I would be very interested in getting your thoughts on this.
Particularly how to make it more plug and play.

Best,
Tom

[joshua clingenpeel]

Hello Tom,

On Tue, Oct 6, 2009 at 8:18 AM, thoen <th...@edgevaleinteractive.com> wrote:
>
> I have been developing Rails applications for the last 2 years. But
> since I still maintain some old CF apps, I am looking forward to using
> cfwheels to make enhancing/maintaining the applications easier (as
> long as I can figure out how to get cfwheels to play nice with my
> existing app, and move things over as needed, if anyone has any ideas
> or experience in this, I would be grateful for some advice).
>

I'm in much the same boat. Vanilla CF, Fusebox 3 and 5, and RoR.
Biggest challenge for me is legacy spaghetti code and clients not
wanting to pay for major refactors. :)

> One of the great benefits of Rails is Migrations. A great way to
> manage schema changes on multiple servers/computers with multiple
> developers. But I could not find anything similar with CF or any of
> the CF frameworks (could have missed something, for sure).
>

I believe migrations for CFWheels is being worked on, or at least is
in the works. I'm excited for this as well. I really like the Scaffold
plugin right now.

> So I put together some preliminary code. It is not built on cfwheels
> (would be interested in thoughts on how to turn it into a plugin. The
> link on the cfwheels site to "Using and Creating Plugins" seems to
> take you to a summary cfwheels page).
>
> I discuss the code at http://coldfusiononrails.blogspot.com/. Code can
> be found at http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=141&lngWId=9
> (A zip file).
>

Would you be able to post the code somewhere it doesn't have to be
downloaded or decompressed in order to view? Do you have a github
account, or an svn repo somewhere with this code?

> I would be very interested in getting your thoughts on this.
> Particularly how to make it more plug and play.
>

I'll take a look at it when I get a chance.

> Best,
> Tom
>
> >
>



--
Josh
Sent from Seattle, WA, United States

[thoen]

> Would you be able to post the code somewhere it doesn't have to be
> downloaded or decompressed in order to view? Do you have a github
> account, or an svn repo somewhere with this code?
>

Hey Josh - You can get the code here at sourceforge:
http://cfmigrate.svn.sourceforge.net/viewvc/cfmigrate/

I am with you that clients are not going to pay for a refactor. But if
I can have them live together, than I can make the change whenever
there is code they need modified. Might be a pipe dream, haven't look
enough into cfwheels to tell.

Is there a has_many :through feature in cfwheels? Most of my
relationships are many-to-many with a reified linking table.

Tom

[joshua clingenpeel]

I believe the :through analog was included in 0.9.4 as 'shortcut'
(haven't tested this myself):

http://code.google.com/p/cfwheels/wiki/Associations#Creating_a_Shortcut_for_a_Many-to-Many_Relationship

[Chris Peters]

This is pretty awesome. I linked to your post in our articles feed on
the community page:
http://cfwheels.org/community

On Oct 6, 11:18 am, thoen <th...@edgevaleinteractive.com> wrote:
> I have been developing Rails applications for the last 2 years. But
> since I still maintain some old CF apps, I am looking forward to using
> cfwheels to make enhancing/maintaining the applications easier (as
> long as I can figure out how to get cfwheels to play nice with my
> existing app, and move things over as needed, if anyone has any ideas
> or experience in this, I would be grateful for some advice).
>
> One of the great benefits of Rails is Migrations. A great way to
> manage schema changes on multiple servers/computers with multiple
> developers. But I could not find anything similar with CF or any of
> the CF frameworks (could have missed something, for sure).
>
> So I put together some preliminary code. It is not built on cfwheels
> (would be interested in thoughts on how to turn it into a plugin. The
> link on the cfwheels site to "Using and Creating Plugins" seems to
> take you to a summary cfwheels page).
>

> I discuss the code athttp://coldfusiononrails.blogspot.com/. Code can
> be found athttp://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1...

[tpet...@gmail.com]

migrations has been tasked to me and it will be worked on once we get
1.0 out the door and finish writing the test for the public api. it's
my bad that it has taken this long to get them in there.

thank for the links. i will definitely take a look at the code and the
blog posts (might sparks some ideas).

one of the questions i've been pondering is how will migrations work
in production in terms of running them? anyone want to throw some
ideas around?

[Chris Peters]

I feel that the security risk (and risk for errors) is too great in production mode. It would be safer in maintenance mode, though knowledge of the "except" URL param could make that unacceptable even then.

(FYI, when the app is in maintenance mode, I can run except after that call without needing to know the reload password. I'm not sure if this is intended behavior...)

[thoen]

It seems to me that migrations are only worthwhile if you use them at
every level (dev, test, qa, prod). They should be considered
inseparable from the application code. They are co-dependent.

In Rails, migrations are run at the time of deployment by the person
doing the deployment. If it is a developer updating code on his
development box from the repository, then he needs to be able to run
the migrations. If the deployment team is rolling it out to the
production server farm then they need to be able to run the
migrations.

Rails migrations are run by a user that has access to the application
root. They are run from the command line, not through web interface.

Is there anyway in CFML to isolate a set of tasks like this?

[Chris Peters]

My thought was that it would be practical to put the application in maintenance mode to do the migration anyway. That way the deployment can be tested before it's released into the wild. Is this unreasonable because of the "downtime" associated with it?

If we did allow the migrations to be run in production mode, we could have it only be allowed when the value for get("reloadPassword") is set in the URL with the password argument.

As you know, migrations would be most powerful if we implemented them so that they're done with CFML instead of direct SQL code, that way they can be platform-independent. That's what ends up making this challenging, though not impossible.

[thoen]

Ah, mental sunrise.

I had been thinking that maintenance was a different installation, not
a different mode on the production installation. This makes perfect
sense then.

As to writing the DDE statements in CFML, are you suggesting that we
create a DSL (similar to rails)? Then the DSL would be interpreted by
the ORM to create the sql statements. It wouldn't be hard to come up
with a base set:
create_table
drop_table
add_column
drop_column
add_index
drop_index

[Chris Peters]

Yeah, we may be able to do everything as functions rather than having an interpreted language? More of a question. Most of what we have in Wheels today is function-based because that's what CFML developers tend to be used to.

[joshua clingenpeel]

Still haven't had the time to look at your code Tom, but I'm charged
about getting migrations in. Pseudocode, but:

loc.fields = ArrayNew(1);
loc.fields[1] = {};
loc.fields[1].name = "title";
loc.fields[1].type = "string";
loc.fields[2] = {};
loc.fields[2].name = "body";
loc.fields[2].type = "text";

create_table(name="post", fields=loc.fields, createTimestamps=true);

Would create a table 'posts' with the fields 'id, title, body,
createdAt, updatedAt' once run.

That's a bunch of code to write in a simple migration file if you're
used to the brevity of simple Rails migrations, but I suppose you
could generate migrations with, say:
"/migration/generate/table/post/fields/title:string,body:text" or
something like that...

This may be too simplistic, since I haven't looked much under the hood.

[Chris Peters]

Keep the ideas flowing. I wasn't trying to poo-poo the idea of an interpreted syntax. :)

[Russ Johnson]

Just want to jump in really quick and suggest that you guys look at
the code for the now defunct cfrails project on riaforge. Sam Larbi I
believe started it and his migrations were for MSSQL only but I was
playing around with them a bit under ColdBox and they worked pretty
good. His method of defining the migrations was pretty nice and alot
less verbose than Im seeing suggested here.

[Chris Peters]

Excellent idea. We should steal him and make him help us out. :D

[tpet...@gmail.com]

its called borrowing, not stealing ;)

http://code.google.com/p/cfwheels/issues/detail?id=94

there is some code in a zip that Ryan from Vendigo Design contributed
awhile back. the dsl he wrote for it was pretty sweet and i encourage
everyone to take a look at it and discuss.

i'm very happy that people are taking an interest in this subject. if
anyone wants to help out, please do so. it's always appreciated.

i like your idea chris of not allowing the migrations to run in
production. i agree that maintenance mode is where "deployment" of the
migrations should be ran for a production site.

thoen, unfortunately you cannot run from coldfusion from a command
line yet. awhile back, vince from new atlanta was playing around and
actually got coldfusion to work from the command line. it used to be
that this worked back when cf was written in c++ back in the 5.0 and
below days but i guess when it moved to java they never completed that
part or was deprecated.

On Oct 7, 2:05 pm, Chris Peters <ch...@clearcrystalmedia.com> wrote:
> Excellent idea. We should steal him and make him help us out. :D
>

[James Gibson]

I would think that migrations should need to do:

- create a table
- drop a table
- rename a table
- change a table

- add a column
- remove a column
- change a column
- rename a column

- add an index
- remove an index

I think this should be a fairly exhaustive list of functionality. If
this is something hot, I could take a look into it this weekend.

On Oct 7, 2:05 pm, Chris Peters <ch...@clearcrystalmedia.com> wrote:
> Excellent idea. We should steal him and make him help us out. :D
>

[James Gibson]

All of this take about migrations has got me fired up! I whipped up a
quick example migration file. Take a look and let me know what you
think.

<cfcomponent extends="Migration">

<cffunction name="up">
<cfscript>
var loc = {};

loc.columns = {};
loc.columns[1] = {columnName="id", columnType="primaryKey"};
loc.columns[2] = {columnName="firstName", columnType="varchar",
isNull=false, limit=100};
loc.columns[3] = {columnName="lastName", columnType="varchar",
isNull=false, limit=100};
loc.columns[4] = {columnName="emailAddress", columnType="varchar",
isNull=false, limit=100};

createTable(tableName="account", columns=loc.columns,
createdAt=true, updatedAt=true, deletedAt=true);

addColumn(tableName="account", columnName="userName",
columnType="varchar", isNull=false, limit=50);
addColumn(tableName="account", columnName="password",
columnType="varchar", isNull=false, limit=1000);

executeSql(sql="add in a raw sql statement here to be executed");

renameColumn(tableName="account", columnName="password",
newColumnName="authenticationToken");
</cfscript>
</cffunction>

<cffunction name="down">
<cfscript>
dropTable(tableName="account");
</cfscript>
</cffunction>

</cfcomponent>

James

[Chris Peters]

So you're saying that you could either pass an array of columns to createTable OR call addColumn manually? Just want to make sure that I'm following. I like it!

[James Gibson]

Hey Chris,

Thats is correct. For table manipulation, we would have the following
methods:

createTable()
renameTable()
dropTable()

Create table would take in the array columns like you said. For column
manipulation, we would have:

addColumn()
changeColumn() <- would change the attributes of a file like data type
renameColumn()
removeColumn()

We would also have a couple methods for adding indexes:

addIndex()
removeIndex()

And a catch all method for running SQL directly:

executeSql()

All of the above methods would (for the developer) be contained in the
up() and down() methods. Also, I was thinking we could do a dump()
method which would take the entire database structure and store it
either as coldfusion code or as a text file with JSON syntax. We could
also have a load() method that would read either file and create the
database from it.

I have started to mock up the basic functionality on my local machine.
Anymore feedback would be greatly appreciated.

Thanks,
James

[Chris Peters]

So would I be able to do this when creating a table?

createTable(name="sometable")
addColumn(table="sometable", name="somecolumn", type="varchar")
...

That may be cleaner that building that uber array, but it would be down to the developer's preference.

[James Gibson]

Hey Chris,

I am pretty sure that would only work if we combined all of the SQL
statements together in the backend. I am pretty sure you cannot create
a table without columns which is what would happen by calling

createTable(name="somtable");

I was thinking (at least initially) that each method would be able to
produce the complete syntax to run on the database. This was we could
show the developer a log of what happened.

James

[Chris Peters]

Gotcha. Just curious.

[thoen]

Would you consider passing in a delimited string instead of an array.
Something like:

createTable("account", "firstName:varchar
lastName:varchar, isNull:false, limit:10
emailAddress:varchar, isNull:true, limit:50
created_at:datetime")

[joshua clingenpeel]

createTable should by default create the 'id' column along with all of
the other columns specified in the arguments, right? Couldn't a user
do createTable("people") and get a table back with one column, then
add extra columns through the other methods?