Quote:
Originally Posted by HarryT
Wow - that's an amazing improvement! There does seem to be something that's being seriously messed up by the database update in the recent release, by the sound of it. Do you just send it SQL commands to update the database schema? "ALTER DATABASE...", etc?
|
Stock SQL. Since you seem to know this stuff, here are the statements.
V14 to V15: remove a constraint on a column. SQLite cannot do that with an ALTER TABLE so you must create a new table and copy the info.
Code:
db.execSQL("create table if not exists " + "newCats" + " (" +
C.KEY_CAT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
C.KEY_CAT_GROUP + " TEXT, " +
C.KEY_CAT_NAME + " TEXT, " +
C.KEY_CAT_NAME_SORT + " TEXT COLLATE LOCALIZED, " +
C.KEY_CAT_FL + " TEXT COLLATE LOCALIZED);");
db.execSQL("INSERT INTO newCats SELECT * FROM " + C.TABLE_CATEGORIES + ";");
db.execSQL("DROP TABLE " + C.TABLE_CATEGORIES + ";");
db.execSQL("ALTER TABLE newCats RENAME TO " + C.TABLE_CATEGORIES + ";");
V15 to V16: add the new date_added_to_cc column.
Code:
db.execSQL(buildQuery("ALTER TABLE", C.TABLE_BOOKS, "ADD COLUMN", C.KEY_DATE_ADDED_TO_CC, "TIMESTAMP;"));
db.execSQL(buildQuery("UPDATE", C.TABLE_BOOKS, "SET", C.KEY_DATE_ADDED_TO_CC, "=", C.KEY_DATE_CHANGED));
One thing occurs to me. When I developed the 14->15 upgrade I checked to see if the indices attached to the original table remained after the rename. Perhaps I missed something and they were dropped. If so then that would account for the loss of performance, turning every query into a full table scan.
I will build and populate a V3.3.6 install, upgrade it to V3.4.2, then do QUERY EXPLAIN to see if the indices are still in use.