I have a changeSet generated from a MySQL database. A column of one table is of type=“LONGTEXT” which I understand allows text up to length 2^32 characters. Running this changeSet on a PostgreSQL database fails with the error ‘type “longtext” does not exist’. According to Postgres docs, type=“TEXT” is of unlimited length and thus would be a good fit for “LONGTEXT”. (http://www.postgresql.org/docs/8.2/interactive/datatype-character.html).
I expected Liquibase to know enough to map type=“LONGTEXT” to “TEXT” when applying to a Postgres database. Apparently this is not the case. Am I misunderstanding the goals of Liquibase? I’d like to maintain a single group of changelogs and be able to apply it to different types of databases. Is the best practice to maintain a separate line of changes for every database supported by my application? Or a primary line with some alternate changeSets configured with appropriate preconditions?
Thanks.
Edit: adding separate changesets with preconditions seems like it will work OK. It also seems that onFail=“MARK_RAN” is the most appropriate if I have, say, two changeSets, one for postgres and one for non-postgres (or eventually other databases should future incompatibilities be discovered).
You are not misunderstanding the goal, that is a big advantage to liquibase and we attempt to handle data type conversions transparently. Unfortunately longtext is not one that we currently deal with and so it gets past through directly and postgres cannot handle it.
If you are running liquibase 2.0, you can fix the issue yourself by extending PostgresTypeConverter (http://liquibase.org/extensions for more information) and overriding the getDataType method to return “TEXT” for an input of longtext. There is a similar mechanism for 1.9 but the method is on the PostgresDatabase class.
An alternate option would be to write an post-processing script that will take advantage of liquibase changelog parameters. You can replace all dataType=“longtext” strings wtih dataType="${longtext}" and then add <property name="longtext value=“longtext” dbms=“mysql”/> and a tags to the beginning of the changeset.
I am planning on focusing on the database diff/changelog support as part of 2.1 and will keep this issue in mind. If you would like, you could create a bug at http://liquibase.jira.com
Nathan
Thanks for the reply.
If you’re looking for things to keep in mind, I have another issue in the same vein. I haven’t fully tracked down the cause of everything, and I’m not planning to spend time on it any time soon, but here it is:
In addition to MySQL and Postgres, I’m using the H2 embedded db. I’m also using Hibernate 3. The database in question is an old (been in use long before using liquibase) db with a schema generated by Hibernate’s hbm2ddl tools (SchemaUpdate).
For some reason (either Hibernate or H2), all table names and column names in the H2 database are UPPER CASE. Liquibase H2Database also appears to convert names to upper case before creating them in the database unless the column name happens to be an H2 keyword, in which case it is quoted and passed through unchanged. My database happens to have columns with the name “element” which is reserved for H2 and thus Liquibase creates a column name “element” rather than “ELEMENT”. But when Hibernate goes looking for data, it looks under “ELEMENT” (and I’m not sure if it’s Hibernate or H2 doing the upcasing) and promptly fails.
My solution at the moment is to maintain separate mysql, postgres, and h2 changelogs. This is workable with a small number of dbms, and the alternative is spending a lot of time tweaking the (100 or so tables) common changelog. As time goes on it should be easier to provide common changelogs (because each one will be much smaller than the initial 100 table schema).
Yes, handling casing gets to be odd and the best way is often opposite for many people. That is part of the reason for the push for extensibilty in liquibase 2.0–you can now easily override the function that handles casing of object names in H2 to more easily have one changelog per database.
Nathan
Originally posted by: Nathan
you can now easily override the function that handles casing of object names in H2
Oh, I’m interested. I’ll have to take a look. I thought you had said in an earlier posting that there were bugs in casing/quoting? Is it just a matter of my overriding a method or two?
Best,
Laird
Yes, there should be just a method or two to override. The reason some of the bugs haven’t been fixed is less an issue of them being hard to change and more an issue that different people want different and opposite things. If you know exactly what you want, it should be easy to get liquibase to do it for you.
The documentation is still relatively sparse, any help anyone that is working though extensions could do to help fill it out would be great.
Nathan
