answered
2014-10-28 18:32:11 +0300
With Matts' pointer I manually rebuilt my db completely (I still left the junk in there, the next step will be to remove a lot more junk).
Basically the steps I took:
stop contactsd.service
copy contacts.db to my PC
dump it to SQL:
2a. sqlite3 contacts.db
2b. .mode insert
2c. .output contactsDb.sql
2d. .dump
2e. .exit
add the various pragma setup statements found in the source file mentioned above to the sql file (encoding, temp_store, journal_mode, synchronous)
create a new sqlite db from the sql file: sqlite3 contacts.db < contactsDb.sql (mv/rm contacts.db first)
ran updates 10, 11, 12, 13 on the newly created db: sqlite3 contacts.db < update10.sql
copy contacts.db back to phone & reboot phone (may also work with just a restart of contactsd.service)
The end result was a db that was smaller then my previous db file and performance also seems a bit better, I still have the deleted contacts entries and probably want to cull them from the db too at some point but for now this is an excellent result.
I will put the update1?.sql files on my github later today and post a link so that if others need it...
BTW I suspect that since I based everything on the new schema update10 may have already done all the work, if I have time I'll look closer at the changes in update 11, 12 (I rolled 13 into 12) to see if they really matter or if at the end of update10 I can already set user_version=14.
Either way, maybe a warning by the contacts program when it detects a db that is not at the right version is in order?
journalctl is showing a lot of warnings that suggest I am missing a table called Families which should either live in contacts.db or in commhist or both, I know that I never deleted such a table....
(In general I never deleted tables, just entries, the backup of contacts.db the only db I ever touched manually does not contain Families either....)
Keeper-of-the-Keys ( 2014-10-27 19:25:46 +0300 )editThe 'Families' table should have been added by a schema upgrade script, on updating from version 11 to 12. You need to use "pragma user_version[=X]" to show and to modify the version - you will need to create the tables yourself now, using sqlite3.
The schema and the upgrade sequence are shown here: https://github.com/nemomobile/qtcontacts-sqlite/blob/master/src/engine/contactsdatabase.cpp Once Uitukka has completed installation, you should have version 14 of the schema (which is equivalent to the current master branch).
Note that you should have nothing related running while manipulating the DB schema; it is best to stop lipstick and all contacts-related services before trying to modify the schema.
MattVogt ( 2014-10-27 23:50:13 +0300 )editApparently I am at version 9 of the schema... I see how my jolla may not have like my messed up db much (though my manual interventions only started in the update 8 period which should have been schema version 12 if I understand you correctly).
Keeper-of-the-Keys ( 2014-10-28 14:34:31 +0300 )edit