databasekotlinsqlitecreate-tablecompanion-object

Calling companion table in Kotlin

How create various tables in Kotlin

I am upgrading from Java to Kotlin, and I need to create several tables in a DB, first I create a companion object and define the table, in the DBHelper class, I create the database and try to call the query, but it does not create any table. This always works in Java, need understand how works companion. I'm already new to Kotlin.

class SpeciesHelper {

    companion object{
        const val TABLE_NAME: String = "species"
        const val COLUMN_ID: String = "id"
        const val COLUMN_NAME: String = "name"
        const val COLUMN_DESC: String = "description"
        const val COLUMN_IMAGE: String = "image"
        const val COLUMN_SKILL: String = "skill"
        const val COLUMN_TYPE: String = "type"
        const val COLUMN_STAR: String = "star"

        val SQL_CREATE_ENTRIES= buildString {
            append("CREATE TABLE $TABLE_NAME (")
            append("$COLUMN_ID INTEGER PRIMARY_KEY,")
            append("$COLUMN_NAME TEXT,")
            append("$COLUMN_DESC TEXT,")
            append("$COLUMN_IMAGE TEXT,")
            append("$COLUMN_SKILL TEXT,")
            append("$COLUMN_TYPE INTEGER,")
            append("$COLUMN_STAR TEXT)")
        }

        val SQL_DELETE_ENTRIES: String = "DROP TABLE IF EXISTS $TABLE_NAME"
    }

}

class DBHelper(context: Context?) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        const val DATABASE_NAME: String = "db_species"
        const val DATABASE_VERSION: Int = 1
    }

    override fun onCreate(db: SQLiteDatabase?) {
        db?.execSQL(SpeciesHelper.SQL_CREATE_ENTRIES)
        db?.execSQL(StarsHelper.SQL_CREATE_ENTRIES)
    }

    override fun onUpgrade(db: SQLiteDatabase?, p1: Int, p2: Int) {
        db?.execSQL(SpeciesHelper.SQL_DELETE_ENTRIES);
        db?.execSQL(StarsHelper.SQL_DELETE_ENTRIES);
        onCreate(db)
    }
}
Solution

  • As far as the species table goes (only supplied code) then your code is fine and works as shown.

    e.g. using activity code:-

    class MainActivity : AppCompatActivity() {
    lateinit var dbHelper: DBHelper
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        dbHelper = DBHelper(this)
        val csr =dbHelper.writableDatabase.query("sqlite_master",null,null,null,null,null,null)
        DatabaseUtils.dumpCursor(csr)
    }
}

    Then the log shows (extract of the schema):-

    2024-05-30 13:40:50.807 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@4f2cde9
2024-05-30 13:40:50.808 I/System.out: 0 {
2024-05-30 13:40:50.808 I/System.out:    type=table
2024-05-30 13:40:50.809 I/System.out:    name=android_metadata
2024-05-30 13:40:50.809 I/System.out:    tbl_name=android_metadata
2024-05-30 13:40:50.809 I/System.out:    rootpage=3
2024-05-30 13:40:50.809 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2024-05-30 13:40:50.809 I/System.out: }
2024-05-30 13:40:50.809 I/System.out: 1 {
2024-05-30 13:40:50.809 I/System.out:    type=table
2024-05-30 13:40:50.809 I/System.out:    name=species
2024-05-30 13:40:50.809 I/System.out:    tbl_name=species
2024-05-30 13:40:50.809 I/System.out:    rootpage=4
2024-05-30 13:40:50.809 I/System.out:    sql=CREATE TABLE species (id INTEGER PRIMARY_KEY,name TEXT,description TEXT,image TEXT,skill TEXT,type INTEGER,star TEXT)
2024-05-30 13:40:50.809 I/System.out: }
2024-05-30 13:40:50.809 I/System.out: <<<<<

    So if the table does not exist, and the database does, it is quite likely (and quite common) that either an empty database exists (for whatever reason) or that the database exists less the species table but with other tables.

    If the database exists then the onCreate method will not be called. Simplest circumvention BUT one that loses all data is to uninstall the app.

    If you need to retain data, then you should:-

    1. increase the version number.
    2. add code to the onUpdate method that apply the required schema changes ensuring that it will run only when required according to the old and new version passed to the method
    3. after doing 1 and 2 rerun the app.

    e.g. (untested) along the lines of:-

    override fun onUpgrade(db: SQLiteDatabase?, p1: Int, p2: Int) {
    //db?.execSQL(SpeciesHelper.SQL_DELETE_ENTRIES);
    //db?.execSQL(StarsHelper.SQL_DELETE_ENTRIES);
    if (p2 <= 2) {
        db?.execSQL(SpeciesHelper.SQL_CREATE_ENTRIES)
    }
    //onCreate(db)
}

    You may wish to temporarily introduce the activity code that extracts and dumps the schema for debugging.