tdm00 / cfwheels-dbmigrate-plugin Goto Github PK

sometimes when creating a migration something fails and the whole migration should be rolled back like:

t = createTable(name='taggings',id=false);
t.integer('tagid');
t.integer('articleid');
t.timestamps();
t.create();

addRecord(table='taggings',tagid=1,columnNoExisit=1);
addRecord(table='taggings',tagid=4,articleid=1);

The correct SQL syntax is generated for creating a table and sequence in Oracle, however a trigger needs to be created to use the sequence values for the primary key column of the table.

Need to create a test suite of migration files that can be used to verify the plugin is working before release.

For example, having a migration to create a basic table with one of each column type. Having this in a "tests" folder that's stored in the plugin. These can then be copied into a project's db/migrations folder to run against a database server and verify that the SQL is generated correctly.

Verify compatibility with CFWheels 1.1.6

The 'quoteTableName' function (Abstract.cfc) needs to be moved to the 'PostgreSQL' adapter as Postgres requires double quotes.

Looks like after 0.8.1 and 0.8.2 have an error when using the web interface.

Migrating from 0 up to 20110409150040.

-------- 20110409150040_creates_articles_table -------------
Error migrating to 20110409150040.
Element MIGRATION.CFC is undefined in LOC.

The string value "This is Ashok's Pen."

needs to become

"This is Ashok's Pen."

with the MySQL escape character or

"This is Ashok''s Pen."

with a double single-quote to escape the character

See if it's possible to add H2 database support
http://www.h2database.com/html/main.html

All of the wiki pages should be setup for this project as a reference for developers on how to use this plugin.

What steps will reproduce the problem?

1. Create a table and or column with null DEFAULT value.
2. issue a "renameColumn()" on that column.
3. the syntax comes out as int(10) NULL DEFAULT; instead of int(10) NULL DEFAULT NULL;

What is the expected output? What do you see instead?
Get a SQL syntax error near ' '

What version of the product are you using? On what operating system?
MySQL 5+ on Linux CentOS 5 with CFWHEELS 1.1.3

Please provide any additional information below.

The 'AddColumnOptions' in Abstract.cfc needs to be copied into the PostgreSQL.cfc and the following changed:

} else if(arguments.options.type == 'boolean') {
                    arguments.sql = arguments.sql & " DEFAULT #IIf(arguments.options.default,1,0)#";

to

} else if(arguments.options.type == 'boolean') {
                    arguments.sql = arguments.sql & " DEFAULT #IIf(arguments.options.default,de('true'),de('false'))#";

Add a template option for the different functions that can be performed using DBMigrate

What steps will reproduce the problem?
1.Create a table with columns using the reference function:

t = createTable(name='companyproducts');
t.references(referenceNames='product,option');
t.create();

2.Then try to remove one of the columns:

removeColumn(table='companyproducts', referenceNames='option');

What is the expected output? What do you see instead?

You expect it to remove the column and all relationships. I tried to first remove the foreign key and the index before removing the column, and that still didn't work.
Here is the actual error: [Err] 1025 - Error on rename of '.\cottonusasupplychaindb#sql-f84_8e' to '.\cottonusasupplychaindb\companyproducts' (errno: 150)

What version of the product are you using? On what operating system?

Version 9 of dbmigrate plugin, with CFWheels 1.1.5 using CF9 and MySQL 5

Please provide any additional information below.

Great plugin! Thanks for all the great work on it

Not sure how hard it would be but when creating new migration scripts for create table it would be cool if one could add the table name so that the migration script would already have the table name in the createTable and dropTable function.

When adding records it would be great if I the createdat and updatedat would be automatically used also if available.

t = createTable('tags');
t.string('name');
t.timestamps();
t.create();

addRecord(table='tags',id=1,name='ColdFusion');
addRecord(table='tags',id=2,name='Wheels')

thanks

I need to add an auto incrementing primary key to an existing table. I discovered in the Wiki docs (https://github.com/talltroym/cfwheels-dbmigrate-plugin/wiki/apitabledefinitioncolumn) that there is a column type called "primaryKey", so I used that value in my addColumn method. According to the live code here on Github, the function 'primaryKey' does exist within TableDefinition.cfc to handle this action. Yet the plugin downloadable from the CFWheels plugins page does NOT contain the "primaryKey" method, and thus I received an error telling me so.

Is there a different preferred way that this is supposed to be accomplished? To accomplish my task, I simply modified my copy of tableDefinition.cfc to use the 'primaryKey' function found in the repo and it worked fine, but others on my team will not be able to execute my migration.

Thanks!

Doug Boude

There seems to be a problem when this code is deployed to Linux application servers. There needs to be both a DBMigrate and dbmigrate folder for the application to work.

While developing a new application, I find myself changing things around sometimes and migrating to 0 - empty to clear things out. The problem is that sometimes I remove entire migration scripts, but when I reset to version 0, it doesn't delete all of the records in schemainfo. When migrating to 0, I think it should just deleteAll() from schemainfo.

What steps will reproduce the problem?

1. Create a table with a date field. (ex. lastlogindate)
2. in the up function put addRecord(....,lastlogindate=CreateODBCDate(now()),....);

What is the expected output? What do you see instead?

The code fails with at the insert b/c the date is quoted.
from sql log.
is:
....,'{ts '2011-07-05 16:32:08'}',....
should be:
....,{ts '2011-07-05 16:32:08'},....

What version of the product are you using? On what operating system?
0.6 Mac 10.6.8 apache cfwheels 1.1.3

Fix is to add the following else if to AddRecord Function in Migration.cfc
else if(IsDate(arguments[loc.key])) {
loc.columnValues = ListAppend(loc.columnValues,"#arguments[loc.key]#");
}

If I have made a gross assumption or other error please let me know. I just started with the plugin today, and I don't see any date examples.

This project needs a changelog with all changes since version 0.6.0

The templates add the extends attribute to the cfcomponent tag. This extends path is correct if CFWheels is in the root of the web root directory. However if cfwheels is located in a sub-folder, the path is incorrect and this results in an error message "load error: Could not find the ColdFusion component or interface plugins.dbmigrate.migration.) (description unavailable)"

When the template file is written to disk it should check the application.rootcomponentpath to determine the correct path to the migration component that it extends.

When creating a join table, it would be great if I could do this.

t = createTable(name='taggings',primaryKey='tagid,articleid');
t.integer('tagid');
t.integer('articleid');
t.timestamps();
t.create();

thanks

When using the updateRecord() function to update a date value the new value is being encased in single quotes like the following:
UPDATE states SET UPDATEDAT = '{ts '2011-11-03 16:59:19'}';

however this is incorrect and shouldn't use be encased in single quotes like the following:

UPDATE states SET UPDATEDAT = {ts '2011-11-03 16:59:19'};

Since the MariaDB project is a fork, more or less, of the MySQL database project (minus the Oracle involvement) this might be easy to add.

I've been trying to install the dbmigrate on my current site and currently it doesn't install, every time I get the error:

Message invalid component definition, can't find plugins.talltroym.talltroym
Stacktrace The Error Occurred in
/Users/simeon/Sites/billings/wheels/Plugins.cfc: line 125
123: <cfset loc.wheelsVersion = SpanExcluding(variables.$class.wheelsVersion, " ")> 124: <cfloop collection="#loc.plugins#" item="loc.iPlugins"> 125: <cfset loc.plugin = createobject("component", $componentPathToPlugin(loc.iPlugins)).init()> 126: <cfif not StructKeyExists(loc.plugin, "version") OR ListFind(loc.plugin.version, loc.wheelsVersion) OR variables.$class.loadIncompatiblePlugins> 127: <cfset variables.$class.plugins[loc.iPlugins] = loc.plugin>

I have tried everything I can think of, including repackaging the plugin in different folder structures but I can't seem to get this to work. I am running Railo 3.3.2. Have I just missed something really simple?

Presently, when executing more than one ChangeColumn (or renameColumn) operation, you must use the following code (example):

renameColumn("tablename", "oldColName", "newColName"); // Only if changing the column name
changeColumn(table="tablename", columnName="newColName", columnType="string", default="", null=true, limit="20");

Neither of these functions allows for the change to be executed on a table object in the same manner the createTable function does. If an error occurs part way through a long sequence of these commands, then all subsequent changes will fail, but all previous changes have already been executed. As such, the migrate function fails to complete (up), and cannot be reverted (down) due to the database being left in a state that would allow either the up or down migration to be run. This results in manual database changes being required to bring the database back into line.

I would like to suggest that changeTable follow a similar structure as the DBMigrate createTable function:

t = changeTable(name);
t.changeColumn(columnName, columnType [newColumnName, referenceName, default, null, limit, precision, scale, addColumns]);
// ... additional change or rename column definitions would go here ...
t.change();

Note that the proposed re-definition of changeColumn adds support for renaming a column. I think it should also support adding an auto-incrementing primary key to a column.

I think that when creating a column of type binary, I think there should also be a way of defining how large the blob should be.

What steps will reproduce the problem?

1. MySQL database (and potentially other engines)
2. Add column(s) using references() with the null argument
3. Run migration script

What is the expected output? What do you see instead?
I expect for the migration to run completely. Instead, it creates tables up to the point where the erroneous call appears and just stops. The interface loads back up, failing silently, with no indication of an error message or any change to the schema (though there could have been some partial changes).

The plugin listed on the CFWheels website (http://cfwheels.org/plugins/) is for version 0.6.0 and should be updated for this latest version.

String values with more then one single quote aren't being escaped in generated SQL.

The following code sample produces this problem

addRecord(table="icds", code="766.1", changeindicator="", codestatus="C", shortdescription="HEAVY FOR DATE INFAN OT", mediumdescription="OTH HEAVY FOR DATES INFANTS", longdescription="OTHER 'HEAVY-FOR-DATES' INFANTS NOT RELATED TO GESTATION PERIOD",createdat="#Now()#",updatedat="#Now()#");

What steps will reproduce the problem?

1. Loading the current version of DBMigrate on CFWheels 1.1.4 or 1.1.5 reports that the plugin may not be compatible.

What is the expected output? What do you see instead?
No warning about possible incompatibility

What version of the product are you using? On what operating system?
CFWheels 1.1.4 and CFWheels 1.1.5

Please provide any additional information below.
Attached a patch file to fix this as the current version works fine with CFWheels 1.1.4 and 1.1.5

This should be populated by ANT when the plugin is built using the plugin.version variable in the build.properties file.
This value should be displayed to the user on the index page of the plugin and available, if needed, to other areas of the plugin.

Develop the ability to automated migrations, possibly through a HTTP GET request to the DBMigrate plugin URL with additional parameters. This would allow the migrations to take place from an automated build tool (like ANT) or a custom script or even from a users web browser.