android - Why does an updated, modified DB file still contain old, previous schemas?

Question

Foremost, I'm an Android developer and SQLite noob -- I've only done a few very simple apps as well as worked with SQL, MySQL, and related DBMSs but not SQLite before.

I'm trying to implement a database, decided to utilize Room with a pre-populated DB file -- using DB Browser for SQLite to create, update the file. One of the problems is I receive errors complaining the schemas don't match ("expected", "found"). Initially, I had mistakenly chosen data types not compatible with Room. I changed them, some even accidentally -- darn auto-selected drop-down menus -- but ultimately fixed them all.

Here's the comparison...

Expected:
TableInfo{name='Ingredient', columns={unit_system=Column{name='unit_system', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, amount=Column{name='amount', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, recipe_id=Column{name='recipe_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, ingredient_id=Column{name='ingredient_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, unit_size=Column{name='unit_size', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, prep_type=Column{name='prep_type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, food_id=Column{name='food_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='Food', onDelete='NO ACTION', onUpdate='NO ACTION', columnNames=[food_id], referenceColumnNames=[id]}, ForeignKey{referenceTable='Recipe', onDelete='NO ACTION', onUpdate='NO ACTION', columnNames=[recipe_id], referenceColumnNames=[id]}], indices=[]}
Found:
TableInfo{name='Ingredient', columns={unit_system=Column{name='unit_system', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, amount=Column{name='amount', type='NUMERIC', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='null'}, recipe_id=Column{name='recipe_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, ingredient_id=Column{name='ingredient_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, unit_size=Column{name='unit_size', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, prep_type=Column{name='prep_type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, food_id=Column{name='food_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

I've tried:

• deleting the file from the project, restarting Android Studio, then pasting the newer one
• editing it as a text file in Android Studio
• deleting the file from the project, Invalidate Caches / Restart, then paste the newer one
• deleting the file, recreate the database from scratch, add it back to the project assets

Here are the CREATE statements from DB Browser:

CREATE TABLE "Food" (
"id"    INTEGER,
"name"  TEXT NOT NULL,
PRIMARY KEY("id" AUTOINCREMENT))

CREATE TABLE "Ingredient" (
"ingredient_id" INTEGER,
"amount"    REAL NOT NULL,
"unit_size" TEXT,
"prep_type" TEXT,
"food_id"   INTEGER NOT NULL,
"recipe_id" INTEGER NOT NULL,
"unit_system"   TEXT NOT NULL,
PRIMARY KEY("ingredient_id" AUTOINCREMENT))

CREATE TABLE "Recipe" (
"id"    INTEGER,
"name"  TEXT NOT NULL,
"category"  TEXT NOT NULL,
"media" TEXT NOT NULL,
"servings_amt"  INTEGER NOT NULL,
"servings_type" TEXT NOT NULL,
"directions_us" TEXT NOT NULL,
"directions_metric" TEXT NOT NULL,
"tags"  TEXT,
"favorite"  INTEGER DEFAULT 0,
PRIMARY KEY("id" AUTOINCREMENT))

Here's what appears in Android Studio (auto opens after pasted)

SQLite format 3 @ .G? ? ?9 ?? ?!!?tableIngredientIngredientCREATE TABLE "Ingredient" ( "ingredient_id" INTEGER, "amount" REAL NOT NULL, "unit_size" TEXT, "prep_type" TEXT, "food_id" INTEGER NOT NULL, "recipe_id" INTEGER NOT NULL, "unit_system" TEXT NOT NULL, PRIMARY KEY("ingredient_id" AUTOINCREMENT) )?_?tableRecipeRecipeCREATE TABLE "Recipe" ( "id" INTEGER, "name" TEXT NOT NULL, "category" TEXT NOT NULL, "media" TEXT NOT NULL, "severings_amt" INTEGER NOT NULL, "servings_type" TEXT NOT NULL, "directions_us" TEXT NOT NULL, "directions_metric" TEXT NOT NULL, "tags" TEXT, "favorite" INTEGER NOT NULL, PRIMARY KEY("id" AUTOINCREMENT) )P++Ytablesqlite_sequencesqlite_sequenceCREATE TABLE sqlite_sequence(name,seq)s?ItableFoodFoodCREATE TABLE "Food" ( "id" INTEGER, "name" TEXT NOT NULL, PRIMARY KEY("id" AUTOINCREMENT) )

What am I doing wrong?

Thanks.

question from:https://stackoverflow.com/questions/65931561/why-does-an-updated-modified-db-file-still-contain-old-previous-schemas

Answer 1

I'm not sure if I'm seeing this correctly or not, but... Your Create SQL is

    CREATE TABLE "Ingredient" (
"ingredient_id" INTEGER,
"amount"    REAL NOT NULL,
"unit_size" TEXT,
"prep_type" TEXT,
"food_id"   INTEGER NOT NULL,
"recipe_id" INTEGER NOT NULL,
"unit_system"   TEXT NOT NULL,
PRIMARY KEY("ingredient_id" AUTOINCREMENT))

Shouldn't it be

CREATE TABLE "Ingredient" (
"ingredient_id" INTEGER,
"amount"    NUMERIC NOT NULL,
"unit_size" TEXT,
"prep_type" TEXT,
"food_id"   INTEGER NOT NULL,
"recipe_id" INTEGER NOT NULL,
"unit_system"   TEXT NOT NULL,
PRIMARY KEY("ingredient_id" AUTOINCREMENT))