Sqlite3 - Integer primary key with auto increment

General Discussion
1

So, I’ve been playing around with liquibase some more and if I define a column with type integer and primary key and set the autoincrement value to true, the generated sql is incorrect


CREATE TABLE log (logid INTEGER CONSTRAINT logid PRIMARY KEY AUTOINCREMENT AUTOINCREMENT  NOT NULL, date INTEGER NOT NULL, serverid TEXT NOT NULL, auxid TEXT, severity TEXT NOT NULL, message TEXT NOT NULL);


notice AUTOINCREMENT is inserted twice. 


I’m using Liquibase 2.0.1, not quite sure why this is happening but after a little bit of digging I realised that in sqlite3 if a column is described as integer and primary key, auto increment is implicit. If I remove the autoincrement attribute it would work on sqlite but not on postgresql.


Any suggestions on how to work around this?


Thanks,

Suneet

2

Oops I forgot the changeSet


        

            

                

            

            

                

            

            

                

            

            

            

                

            

            

                

            

        


Looks fairly standard to me!

3

Looks like once again I’ve found the problem. I was referencing sqlite 0.56 jar when I was using sqlite3 :S, when I changed it to reference sqlite-jdbc-3.6.0.jar, it works.

4

The changesSet should include autoIncrement=“true” so liquibase understands that you intend it to be autoincrement. Liquibase should generate the correct sql per database, including not including any identity clause for sqlite since it is implied. 


I have not really dealt with sqlite before to know the best way to test it out, different versions in use, etc. Is there anyone who can help with the sqlite support in 2.0? I can let you know what to do from a liquibase code standpoint, but I don’t know sqlite well enough to know what to change the liquibase code to.

Nathan

5

Nathan, could you please mark SQLite as PARTIAL Support on your web site? We waste time with this thinking SQLite is supported and obviously it’s not.