coldbox-modules / cfmigrations Goto Github PK

It would be awesome if you could define an array of datasources in the configuration of the module and each of these datasources would get the migration operations applied to it. We have a multi-tenant application where the database for each tenant needs to be migrated with each release and this is the only thing keeping us from using the tool. Would we love to start using it if this is something that could be easily added.

On bootstrap:

The CFML compiler encountered an unexpected java.lang.ClassCastException exception.

The reason for this was: coldfusion.compiler.ASTtagAttribute cannot be cast to coldfusion.compiler.ExprNode Occurred at:java.lang.ClassCastException:

coldfusion.compiler.ASTtagAttribute cannot be cast to coldfusion.compiler.ExprNode at coldfusion.compiler.ExprVisitor.insertCast(ExprVisitor.java:210) at coldfusion.compiler.ExprVisitor.call(ExprVisitor.java:610) at coldfusion.compiler.ExprVisitor.transform(ExprVisitor.java:83) at coldfusion.compiler.Treewalker.postorder(Treewalker.java:100) at coldfusion.compiler.Treewalker.postorder(Treewalker.java:82) at coldfusion.compiler.Treewalker.postorder(Treewalker.java:27) at coldfusion.compiler.Treewalker.postorder(Treewalker.java:27) at coldfusion.compiler.Treewalker.postorder(Treewalker.java:27) at coldfusion.compiler.NeoTranslator.parseAndTransform(NeoTranslator.java:459) at coldfusion.compiler.NeoTranslator.translateJava(NeoTranslator.java:391) at coldfusion.compiler.NeoTranslator.translateJava(NeoTranslator.java:160) at coldfusion.runtime.TemplateClassLoader$TemplateCache$1.fetch(TemplateClassLoader.java:467) at coldfusion.util.LruCache.get(LruCache.java:180) at col...
 
The error occurred in /app/inleague/modules/cfmigrations/models/MigrationService.cfc: line 60Called from /app/inleague/coldbox/system/core/util/Util.cfc: line 287Called from /app/inleague/coldbox/system/ioc/config/Mapping.cfc: line 604Called from /app/inleague/coldbox/system/ioc/config/Mapping.cfc: line 614Called from /app/inleague/coldbox/system/ioc/config/Binder.cfc: line 1013Called from /app/inleague/coldbox/system/ioc/config/Binder.cfc: line 1008Called from /app/inleague/coldbox/system/ioc/config/Binder.cfc: line 319Called from /app/inleague/coldbox/system/web/services/ModuleService.cfc: line 527Called from /app/inleague/coldbox/system/web/services/ModuleService.cfc: line 481Called from /app/inleague/coldbox/system/web/services/ModuleService.cfc: line 476Called from /app/inleague/coldbox/system/web/services/ModuleService.cfc: line 422Called from /app/inleague/coldbox/system/web/services/LoaderService.cfc: line 57Called from /app/inleague/coldbox/system/bootstrap.cfc: line 98Called from /app/inleague/Application.cfc: line 158Called from /app/inleague/coldbox/system/core/util/Util.cfc: line 287Called from /app/inleague/coldbox/system/ioc/config/Mapping.cfc: line 604Called from /app/inleague/coldbox/system/ioc/config/Mapping.cfc: line 614Called from /app/inleague/coldbox/system/ioc/config/Binder.cfc: line 1013Called from /app/inleague/coldbox/system/ioc/config/Binder.cfc: line 1008Called from /app/inleague/coldbox/system/ioc/config/Binder.cfc: line 319Called from /app/inleague/coldbox/system/web/services/ModuleService.cfc: line 527Called from /app/inleague/coldbox/system/web/services/ModuleService.cfc: line 481Called from /app/inleague/coldbox/system/web/services/ModuleService.cfc: line 476Called from /app/inleague/coldbox/system/web/services/ModuleService.cfc: line 422Called from /app/inleague/coldbox/system/web/services/LoaderService.cfc: line 57Called from /app/inleague/coldbox/system/bootstrap.cfc: line 98Called from /app/inleague/Application.cfc: line 158

cfmigrations' test harness has the same missing jquery.js file that I've seen in a couple other Coldbox modules. Pushing a fix.

Add squash support for migrations based on this: https://laravel.com/docs/8.x/migrations#squashing-migrations

We recently had a very unusual edge case in which we had a migration that ran an ALTER DATABASE command that could not be processed inside a transaction.

We made a slight change to MigrationService.cfc to enable this:

               preProcessHook( migrationStruct );

		if ( structKeyExists( migration, 'transaction' ) && !migration.transaction ) {
			invoke(
				migration,
				direction,
				[ schema, query ]
			);
			logMigration( direction, migrationStruct.componentName );
		}
		else {
			transaction action="begin" {
				try {
					invoke(
						migration,
						direction,
						[ schema, query ]
					);
					logMigration( direction, migrationStruct.componentName );
				} catch ( any e ) {
					transaction action="rollback";
					rethrow;
				}
			}
		}

		postProcessHook( migrationStruct );

And then on the migration itself we just set this.transaction = false;.

Would you take this as a PR?

First reported in Jira: https://ortussolutions.atlassian.net/browse/BOX-93?filter=-2

When we try to run a migration from the command line, we get the following error:

In a nutshell, the grammar wasn't defined when we hit the "migrate up" but we had no idea where to look. The error you'll see is

So Brad suggested improving the error message at this point.

Hi,

I'm testing h2 database in a ColdBox application (on Lucee) ...
I can connect to database with Quick (I've created a Model and an Handler) but I'm not able to run migrations...

this is the error displayed in CommandBox after migration init and migration creation

> migrate init
cfmigrations configured!

> * > CLI v5.2.1 > 04:25 PM > ~/coldbox-examples/testApp/ > testApp (1.0.0) > lucee 5.3.7 (running) >
> migrate up

ERROR (5.2.1+00295)                                                                                                                                                                                                                                                                                                       Schema "information_schema" not found; SQL statement:SELECT 1 FROM "information_schema"."tables" WHERE "table_name" = ? AND "table_schema" = ? [90079-172]   

MigrationService.cfc

    SELECT 1 FROM "information_schema"."tables" WHERE "table_name" = 'cfmigrations' AND "table_schema" = 'testAppDB'

Both Application.cfc and box.json use ENVIRONMENT settings:

# ColdBox Name and Environment
APPNAME=tastApp
ENVIRONMENT=development

DB_BUNDLEVERSION=1.3.172
DB_PORT=
DB_USER=sa
DB_BUNDLENAME=org.h2
DB_CLASS=org.h2.Driver
DB_HOST=127.0.0.1
DB_DRIVER=org.h2.Driver
DB_SCHEMA=appDB
DB_DATABASE=appDB
DB_CONNECTIONSTRING=jdbc:h2:/Users/elena/coldbox-examples/testApp/database/appDB;MODE=MySQL;
DB_PASSWORD=

h2 was downloaded from Lucee download page and saved in /lib folder (I've added dependencies in box.json)

this is the grammar settings:

"defaultGrammar":"AutoDiscover@qb"

What am I missing?
Kind regards,
Elena

testApp.zip

Ok, I just tested this in MySQL
the point is compileTableExists fires this query
SELECT 1 FROM information_schema.tablesWHEREtable_name = 'cfmigrations'
if Laur and you are both using the same credentials for your databases you will indeed see each others db. If you would use separate credentials for each db, which is more secure anyway, you will not have this problem

elpete [4:53 PM]
qb doesn’t see the connection string. It just uses it. There may be a way to get it from Java, but I think the easier path is to have hasTable take an optional schema parameter.

While testing cfmigrations, I've been running migrations down then back up. I assume that "down" means starting with the last migration run, run the down function then remove the record from the cfmigrations table and keep doing this until none are left....

Down occasionally does not fire for some reason, verified by adding cfdump("Got this far");abort; in my migration file, inside the "down" method.

function onAppInit(event,rc,prc){
		getInstance( "MigrationService@cfmigrations" ).runAllMigrations( "down" );
		getInstance( "MigrationService@cfmigrations" ).runAllMigrations( "up" );
}

I use QB in provider mode everywhere. In migrations, I get bit because I forget to use newQuery() when creating and populating multiple tables (usually lookups - states, countries, tax rates, etc). Changing the QB injection to be a provider would still allow old code using newQuery() to operate as normal while alleviating this problem.

There is an excellent setting for seedEnvironments. It occurred to me there might be some !environments for certain features like "fresh". I will put this in my future submissions, but if someone gets to it before me, that is also awesome.

Running migrationService.hasMigrationsToRun( "up" ) to check for migrations to run causes cfmigrations to run a SQL query for every migration cfc in the migrations directory. So if you have a large app with hundreds or thousands of migrations, cfmigrations will run SELECT name from cfmigrations hundreds or thousands of times!

To fix this, we should cache the query results in the variables scope:

https://github.com/coldbox-modules/cfmigrations/blob/v2.0.10/models/MigrationService.cfc#L311-L318

var migrations = queryExecute(
    "
        SELECT name
        FROM #getMigrationsTable()#
    ",
    {},
    { datasource : getDatasource() }
);

The latest version of Wirebox is unable to parse a full Windows file path due to a check in the injector that dumps out a blank string when passed anything with a : character. https://github.com/Ortus-Solutions/commandbox/blob/8d12ac9b13b7ff95af4cd1f7b5bac7a92785ddc6/src/cfml/system/wirebox/system/ioc/Injector.cfc#L542

The findAll() function at

Wirebox then errors out when running migrate up.

This is not reflected in the github master but the Forgebox module is missing commas separating struct attributes in MigrationService.cfc, lines 178 and 245.

A migration can contain any number of SQL statements. Right now, if one of the statements fail, any previous statements that executed successfully are not rolled back. This leaves the database is a state that the migration is not expected and cannot rollback.

We should add a transaction around the migration function to prevent this issue.

Hello - this module looks very promising. Our application has 3 datasources - main (ui/billing/etc), logging (where devices send their data) and alerts. I would like to use cfmigrations to keep them all up to date. How do I set up the environment variables so that migrate install works? Any other "gotcha's" to be aware of?

When trying to run the command box migrate install I get the following output

ERROR (5.6.1+00618)

java.lang.NullPointerException

My datasource configuration works fine within my application - which is running in the ACF commandbox image. My database is the mcr.microsoft.com/azure-sql-edge:latest image. Running box config set verboseErrors=true does not change the output of the command.

Here is my .cfmigrations.json configuration

{
    "default": {
        "manager": "cfmigrations.models.QBMigrationManager",
        "migrationsDirectory": "resources/database/migrations/",
        "seedsDirectory": "resources/database/seeds/",
        "properties": {
            "defaultGrammar": "SqlServerGrammar@qb",
            "schema": "dbo",
            "migrationsTable": "cfmigrations",
            "connectionInfo": {
                "password": "mypassword",
                "connectionString": "jdbc:mssql://localhost:1433?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useLegacyDatetimeCode=true",
                "class": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
                "username": "coldbox",
                "bundleName": "mssqljdbc4",
                "bundleVersion": "4.0.2206.100"
            }
        }
    }
}

Can anyone help me figure out what I'm doing wrong?

I created a migration file that did not create properly but did not run properly.

CommandBox> migrate create tbltest_create --open
Created /C_drive\dev\project\ols\ols_api\resources\database\migrations/2022_09_29_101349_tbltest_create.cfc

I would be glad to update and do a pull request but did not see "open" within the code when searching for it in VSCode.

Hi,
it would also be useful to have the value of migratedDate = cfmigrations.migration_ran

Thanks,
Ivan

right now cfmigrations only checks its own config if the datasource exists, but normally there is already a datasource in the .env or cfconfig, it would be nice if it checked and only if it does not find any datasource then error out.