Database creation question...

[Ned Wolpert]

In using activiti 5.1 via the grails plugin, is the intent that a
fresh database is initialized once the grails application is started
for the first time? Or is it expected that the database is created
before the application is first started via the 'ant db.create' method
from the activiti 5.1 setup?

We use liquibase for our database handling (database-migration plugin)
and activiti. Since activiti has its own method for maintaining its
own database structure, I'd like to handle the conflicts correctly. I
wouldn't mind putting the database schema into the liquibase config
myself, but I end up with a problem when the liquibase plugin cannot
execute because the activiti plugin cannot load due to tables missing.
I wouldn't mind ignore the activiti tables and letting the plugin
handle them but it looks like activiti grails plugin will not create
them in a fresh database. (Note I've disabled GORM database schema
changes in the config by commenting out the dbCreate section of the
DataSource.groovy file.

[Chee Kin Lim]

Hi there,

I not really get your question. The activiti plugin support both (whether the activiti's database schema created or not).

If the activiti's database schema created via 'ant db.create', you just need to set the databaseSchemaUpdate = false in Config.groovy and dbCreate = 'update' in DataSource.groovy.

You can find out more about the setting of databaseSchemaUpdate at http://activiti.org/userguide/index.html#databaseConfiguration

Hope this help.

Regards,
Chee Kin

[Ned Wolpert]

Okay, so, in a brand new grails project, if I add in the activiti plugin, and would like the activiti plugin to create/manage the activiti tables, what is the correct configuration? What I have tried so

Using PostgreSQL as the database drive, replacing h2 with postgres in the correct places...

databaseSchemaUpdate=true in Config.groovy

dbCreate="create"  in DataSource.groovy.

Now, according to the documents, the valid attributes for databaseSchemaUpdate are true, false and 'create-drop'. In a production environment, I obviously do not want 'create-drop', but would 'create' make sense? It _does_ work (creates the tables) if I set it to create... is that a valid setting for the databaseSchemaUpdate variable?

The reason for this question is we use liquibase base (via the grails plugin database-migration) for database manipulation. However, I cannot use the liquibase plugin to manage the activiti tables as it becomes a chicken-egg problem; when starting the migration script, the activiti tables don't exist when the activiti plugin is loaded so it errors out before liquibase can create the tables. 

--
Virtually, Ned Wolpert
http://codehead.tumblr.com/
http://codeheadsystems.wordpress.com/

"Settle thy studies, Faustus, and begin..."   --Marlowe

[Ned Wolpert]

According to the database log, it looks like if I have the databaseSchemaUpdate = true, and dbCreate is commented out altogether, postgres gives the following logs...

2011-01-19 11:07:18 MST LOG:  execute S_1: BEGIN

2011-01-19 11:07:18 MST ERROR:  relation "act_ge_property" does not exist at character 20

2011-01-19 11:07:18 MST STATEMENT:  select VALUE_ from ACT_GE_PROPERTY where NAME_ = 'schema.version'

2011-01-19 11:07:18 MST ERROR:  current transaction is aborted, commands ignored until end of transaction block

2011-01-19 11:07:18 MST STATEMENT:  create table ACT_GE_PROPERTY (^M

            NAME_ varchar(64),^M

            VALUE_ varchar(300),^M

            REV_ integer,^M

            primary key (NAME_)^M

        )

2011-01-19 11:07:18 MST ERROR:  current transaction is aborted, commands ignored until end of transaction block

...

This means that the transaction block its trying to call the sql statement to get the schema version, failing, then tries to create the database. That create fails because the transaction block is still active... once you have an error in the transaction, all future sql commands are ignored until the commit/rollback is executed.

[Chee Kin Lim]

Hi Ned,

Please see my reply below.

Regards,
Chee Kin

On Thu, Jan 20, 2011 at 12:16 AM, Ned Wolpert <ned.w...@gmail.com> wrote:

Okay, so, in a brand new grails project, if I add in the activiti plugin, and would like the activiti plugin to create/manage the activiti tables, what is the correct configuration? What I have tried so

Using PostgreSQL as the database drive, replacing h2 with postgres in the correct places...

databaseSchemaUpdate=true in Config.groovy

dbCreate="create"  in DataSource.groovy.

Now, according to the documents, the valid attributes for databaseSchemaUpdate are true, false and 'create-drop'. In a production environment, I obviously do not want 'create-drop', but would 'create' make sense?

In production environment, I think the default databaseSchemaUpdate setting (databaseSchemaUpdate=false) is more appropriate, only set to true when u need to upgrade the plugin.

 

It _does_ work (creates the tables) if I set it to create... is that a valid setting for the databaseSchemaUpdate variable?

I am not aware databaseSchemaUpdate="create" is working, but I am advice you not to use it, as it may remove by Activiti developer in the future as this feature is not officially documented.

[Chee Kin Lim]

Please try databaseSchemaUpdate=false as mentioned in my previous response, I assumed activiti database schema was created.

Regards,
Chee Kin

[Ned Wolpert]

I'm probably not making the issue clear. Two problems exists that I see, and are specific with _not_ using the GORM method for creating tables from the domain objects. (Note that I've tried several combinations for databaseSchemaUpdate(Config.groovy) and dbCreate(DataSource.groovy) and are listed below.)  The goal is to not create the activiti schema before running grails. Rather I want the schema created either by liquibase during the execution of the grails plugin database-migration, or from activiti plugin itself. If I have to do anything before this step, then I cannot use the liquibase plugin and instead would use liquibase via ant script or command line. I'd rather my devs not have to execute two commands to 'setup' the database.

1) If I want to have the liquibase grails plugin 'database-migration' to create the activiti tables, then I would set databaseSchemaUpdate=false. This does not work. Reason is because when I try to run the grails script 'dbm-update' that the liquibase plugin provides, activiti errors out when the plugin is being initialized because activiti plugin will try to load up the domain model data before the liquibase plugin executes its code to create the tables.  (Note that here I have the dbCreate _not_ set. its commented out due to my desire to have liquibase handle the table creation/updates) In this case, I do not see a way for the activiti plugin to help with the issue as its a grails issue itself. (Loading/execution order)

2) If instead I want the activiti plugin to handle the activiti database table creation, _and_ the liquibase plugin to handle the other database tables, then I set databaseSchemaUpdate=true. This fails for the reason I mentioned above: invalid sql being executed. (To re-iterate, a transaction is started, then the select is called to get the current version of the activiti schema which fails, which prevents all table creation. The log I gave shows this. The fix is to not start the transaction until after the select is called.) Here, activiti can be fixed to solve this issue. (Note, again, dbCreate is not set in this case.)

What was _not_ a problem was if I had grails GORM manage the tables. (Meaning, dbCreate="update" or something else) This doesn't work for me due to the fact that the GORM migrations do not do enough for database maintenance for us.

If we cannot solve item 2 above, my workaround is to use liquibase without the grails plugin... not optimal but gets my team moving. However, I think the solution I mention is easy enough though I'm not sure if its in the activiti plugin or activiti itself.

[Chee Kin Lim]

Hi Ned,

Thanks for writing. I am clear about the issue you are facing now.

I have solution for item 1 but not item 2 as DB transaction was managed by Grails, I not sure how to start the transaction until after the select is called.

Solution for item 1 is simple, when you execute dbm-update script, just tell the activiti plugin not to load the activiti engine, you can make it so by edit the ActivitiGrailsPlugin.groovy (the plugin definition), when it detect a system property is set or dbm-update script is executing (I not sure how to detect which script is running, pls let's me know if you know how).

Hope this help.

Regards,
Chee Kin

[Ned Wolpert]

As far as issue with item 2, is this happening because the core activiti jar checks for the existence of the tables using the select statement, then building the database should that return an error? In other words, that the steps are purely in the domain of the core activiti project and not in the activiti plugin at all then? If so, it makes more sense for the activiti core project to make use of the transactions and the plugin to disable grails from added them in during the execution of the script. (I don't remember off-hand how to do that outside of a service...)

I'll check through your solution to item 1 today. If that works then I'm good.

[Ned Wolpert]

Can you update the activiti plugin so that if the -Dactiviengine=false property is set, then the activiti engine isn't executed? That would work for me in that I can just have people add that to the initialization routine. (I wish I could figure out how to temporarily disable a plugin, since that's effectively the same thing but less invasive.)

[Chee Kin Lim]

Hi Ned,

Please use activiti plugin 5.1.2 at http://grails-activiti-plugin.googlecode.com/files/grails-activiti-5.1.2.zip

You can disable activiti engine by specify system property -DdisabledActiviti=true. Eg. grails -DdisabledActiviti=true run-app.

Regards,
Chee Kin

[Ned Wolpert]

Worked perfectly for me. When are you planning on your next release?

[Chee Kin Lim]

Hi Ned,

Great! Good to hear that. :)

The official release plan of the plugin will always follow activiti release. I will release 5.2 to grails plugin repos early next month when activiti release 5.2 included features and fixes of 5.1.1 and 5.1.2.

Good day to you!

Regards,
Chee Kin