Maven SQL plugin execution order

Question

I am facing some problem in ordering the sql execution.

We have one sql script file for each table under one directory. Ex: There is user.sql and role.sql in the directory c:\SqlScripts. We are using the sql maven plugin to execute these sql files.

The role table has a Foriegn key to the user table and the execution is failing because the plugin is trying to execute the role.sql before user.sql. I cannot use the orderfile attribute as we specify the as c:\SqlScripts*.sql.

How do I overcome this problem?

I see 2 solutions:

1. Merge all scripts into one file in the creation order, the script to create role comes after the script to create user.

2. Remove the * wildcard and specify each file in the attribute in the order of execution.

3. Number the name of the files according to the execution order ex: 1_user.sql, 2_role.sql and use the 'ascending' orderfile attribute(not sure if this would work though, as the ordering may not be applied)

Any more elegant solutions?

Answer 1

I cannot use the orderfile attribute as we specify the as c:\SqlScripts*.sql.

Obviously, this will only work if you don't have strict ordering requirements. If you do...

How do I overcome this problem?

...use a setup that allows to deal with ordering in a deterministic way.

I see 3 solutions (...). Any more elegant solutions?

I think you nailed. My preference goes to option 3.