I’m evaluating LiquiBase for a project based on Maven / Spring Framework / iBatis / PostgreSQL (production db) / … and LiquiBase seems to be very helpful, I thank all the commiters!
For the unit test of the data access components, I’m using HSQLDB (memory) and JUnit to create and to populate a DB before each test (setUp) and clean up at the end (tearDown).
The Spring LiquiBase wrapper doesn’t offer a solution out-of-the-box for this use case because the DB is created when the bean is loaded (only once whereas it should be called between each unit test).
So I call programmatically LiquiBase during the setUp and the tearDown and it works.
protected void setUp() throws Exception
{
super.setUp();
...
dataSource = (DataSource)context.getBean("dataSource");
Liquibase liquibase = new Liquibase("src/main/resources/res/cp1/db.changelog.xml", new FileSystemFileOpener(), dataSource.getConnection());
liquibase.update("");
}
protected void tearDown() throws Exception
{
Liquibase liquibase = new Liquibase(“src/main/resources/res/cp1/db.clear.xml”, new FileSystemFileOpener(), dataSource.getConnection());
liquibase.update("");
super.tearDown();
}
My question is to know if it is a “correct” way of using LiquiBase for unit test and generally to have feedbacks or best practices about the use of LiquiBase for uint tests.
Renaud
What you have looks like the right approach. I’ve tried the same approach for unit tests and it worked pretty well. A couple thoughts I have on the general approach:
-
There is a Liquibase.dropAll() method that may do what your tearDown method is doing. Or if you close and restart your in-memory database after each test you would get the same result without the drop tables overhead.
-
I found that h2 is significantly faster than hsql for this type of use. If your regular app uses hsql stay with it, but if you are normally using a persistent database like mysql and just looking at hsql for a faster test-only database you should check out h2.
-
In the end, I found that for my application it was faster to run my tests against an already-updated mysql database than to create and populate h2 for each test. It takes more management of data, but in general it was easier and faster for me.
Nathan
I’m using Liquibase version 1.9.5 and H2 version 1.2.124 ( or HSQLDB 1.8.0.7).
I’m trying the liquibase.dropAll() on tearDown() for the unit test cleanup but I have an exception (see stack trace).
This exception is due to the definition of a foreign key constraint:
Is there a work-around to avoid this issue ?
I could add the foreign key constraints only for the integration / production DBs (using conditional change log) but I would like to have a unit test DB as close as possible of the production DB.
liquibase.exception.JDBCException: Error executing SQL ALTER TABLE WKF_ACTIVITIES_HISTORY DROP CONSTRAINT `FK_ACTIVITIES_HISTORY_2_PATIENTS`
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
at liquibase.database.AbstractDatabase.dropDatabaseObjects(AbstractDatabase.java:846)
at liquibase.Liquibase.dropAll(Liquibase.java:449)
at liquibase.Liquibase.dropAll(Liquibase.java:436)
at com.palantiris.patient.dao.ibatis.PatientEventDaoTest.tearDown(PatientEventDaoTest.java:48)
...
Caused by: org.h2.jdbc.JdbcSQLException: Constraint "FK_ACTIVITIES_HISTORY_2_PATIENTS" not found; SQL statement:
ALTER TABLE WKF_ACTIVITIES_HISTORY DROP CONSTRAINT `FK_ACTIVITIES_HISTORY_2_PATIENTS` [90057-124]
at org.h2.message.Message.getSQLException(Message.java:111)
at org.h2.message.Message.getSQLException(Message.java:122)
at org.h2.message.Message.getSQLException(Message.java:75)
at org.h2.command.ddl.AlterTableDropConstraint.update(AlterTableDropConstraint.java:41)
at org.h2.command.CommandContainer.update(CommandContainer.java:72)
at org.h2.command.Command.executeUpdate(Command.java:209)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:171)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:147)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:78)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:48)
... 18 more
the previous exception seems due to the single quote; it works with the following syntax:
ALTER TABLE WKF_ACTIVITIES_HISTORY DROP CONSTRAINT FK_ACTIVITIES_HISTORY_2_PATIENTS
instead of
ALTER TABLE WKF_ACTIVITIES_HISTORY DROP CONSTRAINT `FK_ACTIVITIES_HISTORY_2_PATIENTS`
My last reply about single quote is wrong … the problem is different:
I added some logs to see what Liquibase generates during the unit tests:
setUp / Liquibase.update("")
>>> 'CREATE TABLE cp1 (id INT GENERATED BY DEFAULT AS IDENTITY IDENTITY NOT NULL, raw_data VARCHAR(64) NOT NULL, CONSTRAINT PK_CP1 PRIMARY KEY (id))'
>>> 'CREATE TABLE cp2_table (id INT GENERATED BY DEFAULT AS IDENTITY IDENTITY NOT NULL, data VARCHAR(64) NOT NULL, cp1_ref INT NOT NULL, CONSTRAINT PK_CP2_TABLE PRIMARY KEY (id))'
>>> 'ALTER TABLE cp2_table ADD CONSTRAINT fk_cp2_table_2_cp1_table FOREIGN KEY (cp1_ref) REFERENCES cp1(id)'
>>> 'CREATE TABLE cp2a_table (id INT GENERATED BY DEFAULT AS IDENTITY IDENTITY NOT NULL, data VARCHAR(64) NOT NULL, cp1_ref INT NOT NULL, CONSTRAINT PK_CP2A_TABLE PRIMARY KEY (id))'
>>> 'ALTER TABLE cp2a_table ADD CONSTRAINT fk_cp2a_table_2_cp1_table FOREIGN KEY (cp1_ref) REFERENCES cp1(id)'
and unit test tearDown / Liquibase.dropAll()
>>> 'ALTER TABLE CP2_TABLE DROP CONSTRAINT FK_CP2_TABLE_2_CP1_TABLE'
>>> 'ALTER TABLE CP2_TABLE DROP CONSTRAINT FK_CP2_TABLE_2_CP1_TABLE'
>>> 'DROP TABLE CP2A_TABLE CASCADE'
>>> 'DROP TABLE CP1 CASCADE'
>>> 'DROP TABLE CP2_TABLE CASCADE'
We can see that we try to delete twice the foreignKey constraint ‘FK_CP2_TABLE_2_CP1_TABLE’ and not the constraint ‘FK_CP2A_TABLE_2_CP1_TABLE’
Using the debugger, the issue seems to be inside the method SqlDatabaseSnapshot.readForeignKeyInformation();
I add a log when a new constraint is put inside the Set foreignKeys
-- 'FK_CP2A_TABLE_2_CP1_TABLE(CP2A_TABLE.CP1_REF ->CP1.ID)'
-- 'FK_CP2_TABLE_2_CP1_TABLE(CP2_TABLE.CP1_REF, CP1_REF ->CP1.ID, ID)'
and at the end of the method I iterate on the set to print the content but the content has been changed !
-- 'FK_CP2_TABLE_2_CP1_TABLE(CP2_TABLE.CP1_REF, CP1_REF ->CP1.ID, ID)'
-- 'FK_CP2_TABLE_2_CP1_TABLE(CP2_TABLE.CP1_REF, CP1_REF ->CP1.ID, ID)'
.
I guess it is due to the override of the method ‘equals()’ or ‘hashCode()’ of the ForeignKey
For info the change set is:
http://www.liquibase.org/xml/ns/dbchangelog/1.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
<addForeignKeyConstraint constraintName=“fk_cp2_table_2_cp1_table”
baseTableName=“cp2_table” baseColumnNames=“cp1_ref”
referencedTableName=“cp1” referencedColumnNames=“id” />
<addForeignKeyConstraint constraintName=“fk_cp2a_table_2_cp1_table”
baseTableName=“cp2a_table” baseColumnNames=“cp1_ref”
referencedTableName=“cp1” referencedColumnNames=“id” />
Ran into the same issue. If you downgrade to 1.9.4 this problem seems to go away. I think its something that was introduced in 1.9.5
I haven’t tried to investigate more than that mostly because SVN will not me get the list of tags and branches so its difficult to do diffs etc.
I’ll look into it for 2.0 http://liquibase.jira.com/browse/CORE-645
Thanks for the troubleshooting.
Nathan
