Advice on how to create a schema for defaultSchemaName

General Discussion
1

Hi,

I am using liquibase-2.0-rc2-SNAPSHOT-bin.

I would like to have all by database object in a separate schema. So I:

  • use an Ant to create a property
  • use <createTable tableName=“myTable”  schemaName="${schemaName}"> to create my table
  • use a changeset to create mySchema before importing the rest:

    create schema ${schemaName}

However, I also want the “databasechangelog” table inside mySchema. When I then use an Ant argument:

  • defaultSchemaName=“mySchema”
    I get an error about not being able to create the databasechangelog table, because it is not present yet.

Is there a way to create this schema from within Liquibase or do I need to do this before starting Liquibase from Ant?

thanks,
Liquiuser

2

You do need to have the schema that the databasechangelog table exists in created before running liquibase.  We track all our changes in that table, and so we need to access it as part of liquibase start up.  For now, you will will need to create the schema in some other way, then use liquibase.

Nathan

3

Hi,

Ok, thanks.

I was hoping that it was possible to specify a sql() which would run before liquibase “really” started.
That way we could set up everything from scratch in an empty, clean database. Very nice for unit testing.

regards,
Liquiuser

4

I agree and have thought about it a bit, but it is sort of a chicken and egg problem.  When starting liquibase, we don’t know what initial startup we need to do because we can’t check the table in the schema to know what we have already done.

I’ve thought about having a block before the changesets that would describe the schema and other pre-liquibase-execution items, but I tend to think that is too much of a database administration feature.  For example, we could create schemas, but what about creating users Would you include the password in the changelog file? Assigning users to the given schema? etc.  Also, many of those activities require a database user with more permissions than the user you should be executing your liqubase scripts as.

Nathan

5

Hi,

Yes, it is too much database specific administration.
I now use the Ant target before called liquibase.

drop schema ${db.schema} if exists; create schema ${db.schema};

But for Oracle where a schema is a user, and you need grants,  the statements are very different.

It would just be  nice to have these specific statements in a liquibase xml file with the “dbms” switch, and maybe in the context “create new database from scratch”.

thanks,
Liquiuser

6

See how to create a schema for defaultSchemaName - #3 by lbruun for a possible solution if you are using Spring Boot or just Spring Framework.