0

This boggles the mind.

I have created a pre-populated SQLite database to use with Room.

Here's the DDL:

CREATE TABLE "us_cities" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "gnis_feature_id" INTEGER,
  "city_name" TEXT,
  "state_code" TEXT,
  "state_numeric" TEXT,
  "county_name" TEXT,
  "county_numeric" TEXT,
  "latitude" REAL,
  "longitude" REAL
);

And here's my entity definition:

@Entity(tableName = "us_cities")
data class UsCity(
    @PrimaryKey
    var id: Int,
    var gnis_feature_id: Int? = null,
    var city_name: String? = null,
    var state_code: String? = null,
    var state_numeric: String? = null,
    var county_name: String? = null,
    var county_numeric: String? = null,
    var latitude: Float? = null,
    var longitude: Float? = null
) {
    constructor() : this(1)
}

As soon as I run the app, Android slams me with Pre-packaged database has invalid schema error, with the expected columns in the correct order, but the found columns in this UTTERLY WRONG order:

data class UsCityWRONG(
    var gnis_feature_id: Int? = null,
    var city_name: String? = null,
    var state_code: String? = null,
    var state_numeric: String? = null,
    var county_name: String? = null,
    var county_numeric: String? = null,
    /**
     * Android fails to correctly parse the embedded SQLite database
     * It COMPLETELY ignores the actual column definition ordinal position, 
     * demanding that `id` appear in THIS EXACT position, despite the fact that 
     * it is clearly defined as the FIRST column
     */
    @PrimaryKey
    var id: Int,
    var latitude: Float? = null,
    var longitude: Float? = null
) {
    //thus also requiring a convoluted no-arg constructor
    constructor() : this(null, null, null, null, null, null, 1)
}

For the life of me, I can't figure out why Android cares which ordinal position the columns vs. entity props are defined in.

Yet another day of my life sucked away.

Any advice will be greatly appreciated. I've uninstalled the app, cleaned project, rebuilt project, deleted and re-copied the .db file to my assets directory, ad infinitum.

In addition, I prefer to use lat and long as column names - perfectly valid in SQLite, but when Android parses the .db file and finds a column named long, it simply skips the column completely, thus also slamming me with the invalid schema error.

rmirabelle
  • 6,268
  • 7
  • 45
  • 42
  • 1
    Please include the entire error message. – ianhanniballake Jan 19 '21 at 05:45
  • hilariously, I'm now completely unable to replicate the error. I reverted back to my original correct entity class definition (id first) and the exception simply disappeared, despite blocking me for over 2 hours across 50 different reinstalls, cache clears etc. – rmirabelle Jan 19 '21 at 05:58
  • 3
    thats' probably not it, but maybe it's because in your entity you missed `@PrimaryKey(autoGenerate = true)` annotation? `id` is the only column that is moved, and it seems to be placed according to the alphabetical order – Stachu Jan 19 '21 at 06:02
  • @Stachu good catch of alphabetization! I didn't miss `@PrimaryKey` but did miss `(autoGenerate = true)`. It seems illogical that Android would parse the data class differently based solely on annotations, but it's something to try. Thanks! – rmirabelle Jan 19 '21 at 17:27

0 Answers0