Monday, January 4, 2010

Groovy on Grails & Sqlite

By default grails uses HSQLDB for development. I switched it to use file based, and it was alright for the most part, but I would find inconsistencies where I would delete a record, and the app would query db fine, but querying with HSQL manager would not return the correct results. Just getting the manager up was a pain HSQL file with manager (I used this guide).
Using sqlite with rails development, I've always liked it better and thought the gui tools were better. Following are the steps to get it working for those interested.
  1. Go to the link hibernate-sqlite for the project and sqlite driver. These two places is where you will find documentation, and pretty much all the info you'll need.
  2. In order to add the jars to your grails project you have to first install the ivy plugin using the command -
    grails install-plugin ivy
  3. add this dependency - dependency org="org.xerial" name="sqlite-jdbc" rev="3.6.16"
  4. now run grails get-dependencies
  5. 4 jars will be added in your lib folder you will need to delete the jdbc-api-1.4 jar. ( I haven't really looked into more details and hopefully someone working with grails longer can help but leaving it there causes a compilation exception)
  6. Now from the hibernate-sqlite page download the Local project either build yourself or with jars. Move the file from src/java/dialect/SQLiteDialect.java to your src/java/dialect/ folder
  7. Create a sqlite file somewhere in your path ( I put it in the project) and use the command from terminal -- sqlite3 dev.sqlite3 -- and just make sure the file gets created, you can run the command -- .databases -- .
  8. In the DataSource.groovy file just configure your datasource now .. example. dbCreate="create"
    url="jdbc:sqlite:/(path to file)/dev.sqlite3"
    logSql="true"
    dialect="dialect.SQLiteDialect"
    driverClassName="org.sqlite.JDBC"
  9. For good measure I just commented out the global hsqldb stuff at the top of the file.

That should be it to get your grails project working with sqlite. For a gui to the database, I like to use SQLite Manager a firefox plugin. If I left anything out let me know. Note: I haven't tested it out extensively but the crud tests I did run ran fine.
One thing to note is dbCreate option 'update' didn't work. I first had to use create, and then comment it out after initial db creation. Minor annoyance.