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