Search code examples
androiddatabasekotlinandroid-room

Android - Kotlin - Prepoluate room database

I am stuck for 4 days on the same issue so I hope getting some help hear. I am trying to load data to a database through an SQL script but often when I run the app for the first time I have an error :

java.lang.RuntimeException: Unable to start activity ComponentInfo{small.app.beeroclock/small.app.beeroclock.MainActivity}: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: Country(small.app.beeroclock.db.Country).
     Expected:
    TableInfo{name='Country', columns={country_name=Column{name='country_name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, country_code=Column{name='country_code', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_Country_country_code', unique=false, columns=[country_code]}]}
     Found:
    TableInfo{name='Country', columns={}, foreignKeys=[], indices=[]}

Thing is I don't undestand the Pre-packaged database has an invalid schema cause my database is based on the Entities :

@Entity
data class City(
    @NotNull
    @PrimaryKey
    val name: String,
    @NotNull
    val lat: Double,
    @NotNull
    val lng: Double,
    @NotNull
    val country_code: String
) {
}

And here the the appdatabase class :

@Database(
    entities = arrayOf(Country::class, Timezone::class, Zone::class, City::class),
    version = 1
)


abstract class AppDatabase : RoomDatabase() {
    abstract fun countryDAO(): CountryDAO
    abstract fun timezoneDAO(): TimezoneDAO
    abstract fun zoneDAO(): ZoneDAO
    abstract fun cityDAO(): CityDAO

}

lateinit var _context: Context

@Volatile
private lateinit var INSTANCE: AppDatabase
fun getInstance(context: Context): AppDatabase {
    synchronized(AppDatabase::class.java) {
        if (!::INSTANCE.isInitialized) {
            _context = context
            INSTANCE = Room.databaseBuilder(
                context.applicationContext,
                AppDatabase::class.java,
                "app_database"
            ).addCallback(rdc).fallbackToDestructiveMigration()
                .build()
            //.addCallback(rdc)
        }
        return INSTANCE;
    }
}

var rdc: RoomDatabase.Callback = object : RoomDatabase.Callback() {
    override fun onCreate(db: SupportSQLiteDatabase) {
        super.onCreate(db)
        //INSERT INTO `country` VALUES ('AF','Afghanistan')
//TODO : Issue to insert cities
        try {
            db.beginTransaction()
            _context.assets.open("sql/timezonedb.sql").bufferedReader().forEachLine {
                db.execSQL(it)
            }
            db.setTransactionSuccessful();
            Log.d("Callback", "The creation of the db is done")
        } catch (e: Exception) {
            e.printStackTrace()
            Log.d("Callback", "Issue with the database import")
        } finally {
            db.endTransaction();
        }

    }


    override fun onOpen(db: SupportSQLiteDatabase) {
    }
}

I guess it is not coming from the sql file itself because if, so I would have a sql error.

If anyone can highlight me I would really appreciate that :D

Regardss

Solution

  • Ok I figure a way to do what I want but I think it not that easy (for new bee of db like me).

    So to anyone with the same issue this I how I proceed.

    First export the schema of the db using : exportSchema property in the db class

    @Database(
    entities = arrayOf(Country::class, Timezone::class, Zone::class, City::class),
    version = 1,
    exportSchema = true
)

    And adding the needed properties in the gradle module file :

    apply plugin: 'kotlin-kapt'


android {
    .....
    defaultConfig {
       ......
        kapt {
            arguments {
                arg("room.schemaLocation", "$projectDir/schemas")
            }
        }
    }
}

    After rebuilding the project you should get a json file under ../app/schema This JSON file contain all you need to create a db in sql. I extract the "createSql" line and replace the ${TABLE_NAME} by the real name :

    "tableName": "Country",
        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`country_code` TEXT NOT NULL, `country_name` TEXT NOT NULL, PRIMARY KEY(`country_code`))",

    become :

    createSql": "CREATE TABLE IF NOT EXISTS `Country` (`country_code` TEXT NOT NULL, `country_name` TEXT NOT NULL, PRIMARY KEY(`country_code`));

    So with those queries I created a SQL file.

    Now to get a db file I use DB browser by SQLite : https://sqlitebrowser.org/

    From there I created a new db file and run the sql file in it.

    Finally, I added the db file to my android project under the assets folder (create a new one if you have none) and add the command in the db builder to create from a db file :

    .createFromAsset("fileName.db")

    Please leave comment if not clear.

    Regards