Grails and SQLite

Question

Trying to get SQLite working with grails...stuff I've found on the web seems a little dated - references to ivy and plugins and such, but based on these:

http://stackoverflow.com/questions/1199512/grails-sqlite
http://bigohno.blogspot.com/2010/01/groovy-on-grails-sqlite.html
http://maven-repository.com/artifact/org.xerial/sqlite-jdbc/3.6.17

I've been able to get it working in a test environment...oddly, when I "prod war" my grails app and deploy to tomcat it fails with:

Dialect class not found: hibernate.SQLiteDialect

Here's my setup:

in conf/hibernate added a class for the SQLiteDialect. This .java was taken from here http://code.google.com/p/hibernate-sqlite/

Then in my DataSource.groovy I have:

hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
}
// environment specific settings
environments {
    development {
        dataSource {
            // SQLite
            // !!!see also BuildConfig for Dependancies!!!
            dbCreate="update"
            url='jdbc:sqlite:C:\\sqlite-shell-win32-x86-3080100\\rss_1.db'
            logSql="true"
            dialect="hibernate.SQLiteDialect"
            driverClassName="org.sqlite.JDBC"
            readOnly="true"

        }
    }

    production {
        dataSource {
            // SQLite
            dbCreate="update"
            url="jdbc:sqlite:/opt/sqlite/dbs/rss/1/rss_1.db"
            logSql="true"
            dialect="hibernate.SQLiteDialect"
            driverClassName="org.sqlite.JDBC"
            readOnly="true"
            showsql="false"

        }
    }
}

and in BuildConfig.groovy I have:

dependencies {

        runtime 'org.xerial:sqlite-jdbc:3.6.17'

    }

I also jar'd up the .java dialect class and put in in lib - some posts said this helped. I also put sqlite-jdbc-3.7.15-M1.jar in lib.

Now when I run-app in my dev environment it runs fine...but when I deploy to tomcat I get the dialect error.

Is there something special I need to do to the prod environment for the dialect?

Answer 1

Here's how to setup SQLite with Grails:

Download SQLite from http://www.sqlite.org/download.html , extract and save to a directory. You may also want to create directories for your databases.

Download SQLite JDBC jar from https://bitbucket.org/xerial/sqlite-jdbc and put the jar in your grails lib directory.

Download a SQLIte dialect...google search as there are many, but you may reference https://github.com/gwenn/sqlite-dialect or https://gist.github.com/virasak/54436

In grails, create a class in src/java and put your dialect code in.

I also jar'd this class up and put the jar in lib.

Setup your grails datasource, e.g.,:

dataSource {
            // SQLite
            dbCreate="update"
            url="jdbc:sqlite:/opt/sqlite/dbs/rss/1/rss_1.db"
            logSql="true"
            dialect="SQLiteDialect"
            driverClassName="org.sqlite.JDBC" 
}

NOTE: Depending on whether your sqlite dialect class is in a package, you may need to prefix the package name to the dialect above (mine was not).

In BuildConfig.groovy, add a dependency to sqlite jdbc, like so:

dependencies {

        runtime 'org.xerial:sqlite-jdbc:3.6.17'

    }

That's what worked for me!