2

I've Android application that has some hardcoded data in sqlite database. How to pregenerate the database file in the gradle script e.g. from text file containing sql commands (or maybe two, the second one will contain creation of android specific table android_metadata)?

I expect that file will be stored in assets folder and copied to proper (internal database) folder during application initialization. E.g. like here:

How to use my own sqlite database?

or

What is a Full Android Database Helper class for an existing SQLite database?

I know that I can run sql command during the db initialization, but I don't want to bother every device cpu to do the same thing.

Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
czs
  • 185
  • 2
  • 12
  • You could install the `sqlite3` binary on your development machine and write a Gradle task to use it to run your SQL statements and create your database. Or, you could find a plain Java library that interfaces with SQLite and use it as part of a custom Gradle plugin. – CommonsWare Nov 02 '17 at 18:05

1 Answers1

4

Ok, find it by myself:)

in build.gradle of your app:

...
configurations {
    driver
}

dependencies {
    ...
    driver 'org.xerial:sqlite-jdbc:3.20.1'
    ...
}

URLClassLoader loader = GroovyObject.class.classLoader
configurations.driver.each {File file -> loader.addURL(file.toURI().toURL())}

task createDB << {
    ext.loadScript = { sql, file ->
        println("Applying " + file)
        String[] sqlcmds = file.text.split(";")
        sqlcmds.collect{it.trim()}.findAll{!it.isEmpty() && !it.startsWith("--")}.each{
            try {
                sql.execute(it)
            } catch(java.sql.SQLException e) {
                System.err << "Invalid SQL statement: " + it
                e.printStackTrace(System.err)
                throw.e
            }
        }
    }

    def sql = groovy.sql.Sql.newInstance('jdbc:sqlite:path/to/asset/folder/dbfile','','','org.sqlite.JDBC')
    new File("path/to/sql/init/files").eachFileMatch(~/.*\.sql/) {loadScript(sql, it)}
    sql.close()
}
preBuild.dependsOn createDB

don't forget to add android specific creation sql script:

CREATE TABLE IF NOT EXISTS "android_metadata" ("locale" TEXT DEFAULT 'en_US');
INSERT OR REPLACE INTO "android_metadata" (ROWID, "locale") VALUES ((SELECT ROWID FROM "android_metadata" WHERE "locale" = 'en_US'), 'en_US');

Also you must handle recreation (e.g. either delete the db file before it's created or put safe sql statements like CREATE TABLE IF NOT EXISTS or INSERT OR IGNORE INTO intu init scripts)

How to load it in application is answered e.g. here: Copy SQLite database from assets folder

czs
  • 185
  • 2
  • 12