Mercurial > libervia-backend

comparison sat/memory/sqlite.py @ 2722:14e1db0c1383

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
core (memory/sqlite): further improvments in update_v7: - "info" messages deletion is now done by deactivating foreign keys. This seriously boost performances. - "VACUUM" is done at the end to reduce database size.
author Goffi <goffi@goffi.org>
date Wed, 12 Dec 2018 00:53:14 +0100
parents 4aaa47f62d8d
children ba74914277cf
comparison
equal deleted inserted replaced
2721:4aaa47f62d8d 2722:14e1db0c1383
1190 defer.returnValue(ret) 1190 defer.returnValue(ret)
1191 1191
1192 @defer.inlineCallbacks 1192 @defer.inlineCallbacks
1193 def update_v7(self): 1193 def update_v7(self):
1194 """Update database from v6 to v7 (history unique constraint change)""" 1194 """Update database from v6 to v7 (history unique constraint change)"""
1195 log.info(u"Database update to v7, this may take a while") 1195 log.info(u"Database update to v7, this may be long depending on your history "
1196 1196 u"size, please be patient.")
1197 log.info(u"some cleaning first") 1197
1198 log.info(u"Some cleaning first")
1198 # we need to fix duplicate stanza_id, as it can result in conflicts with the new schema 1199 # we need to fix duplicate stanza_id, as it can result in conflicts with the new schema
1199 # normally database should not contain any, but better safe than sorry. 1200 # normally database should not contain any, but better safe than sorry.
1200 rows = yield self.dbpool.runQuery( 1201 rows = yield self.dbpool.runQuery(
1201 u"SELECT stanza_id, COUNT(*) as c FROM history WHERE stanza_id is not NULL " 1202 u"SELECT stanza_id, COUNT(*) as c FROM history WHERE stanza_id is not NULL "
1202 u"GROUP BY stanza_id HAVING c>1") 1203 u"GROUP BY stanza_id HAVING c>1")
1211 uids = [r[0] for r in row_uids] 1212 uids = [r[0] for r in row_uids]
1212 yield self.dbpool.runQuery( 1213 yield self.dbpool.runQuery(
1213 "DELETE FROM history WHERE uid IN ({})".format(u",".join(u"?"*len(uids))), 1214 "DELETE FROM history WHERE uid IN ({})".format(u",".join(u"?"*len(uids))),
1214 uids) 1215 uids)
1215 1216
1216 log.info(u'Deleting "info" messages (this can take a while, please be patient)') 1217 def deleteInfo(txn):
1217 yield self.dbpool.runQuery(u"DELETE FROM history where type='info'"); 1218 # with foreign_keys on, the delete takes ages, so we deactivate it here
1219 # the time to delete info messages from history.
1220 txn.execute("PRAGMA foreign_keys = OFF")
1221 txn.execute(u"DELETE FROM message WHERE history_uid IN (SELECT uid FROM history WHERE "
1222 u"type='info')")
1223 txn.execute(u"DELETE FROM subject WHERE history_uid IN (SELECT uid FROM history WHERE "
1224 u"type='info')")
1225 txn.execute(u"DELETE FROM thread WHERE history_uid IN (SELECT uid FROM history WHERE "
1226 u"type='info')")
1227 txn.execute(u"DELETE FROM message WHERE history_uid IN (SELECT uid FROM history WHERE "
1228 u"type='info')")
1229 txn.execute(u"DELETE FROM history WHERE type='info'")
1230 # not sure that is is necessary to reactivate here, but in doubt…
1231 txn.execute("PRAGMA foreign_keys = ON")
1232
1233 log.info(u'Deleting "info" messages (this can take a while)')
1234 yield self.dbpool.runInteraction(deleteInfo)
1235
1218 log.info(u"Cleaning done") 1236 log.info(u"Cleaning done")
1219 1237
1220 # we have to rename table we will replace 1238 # we have to rename table we will replace
1221 # tables referencing history need to be replaced to, else reference would 1239 # tables referencing history need to be replaced to, else reference would
1222 # be to the old table (which will be dropped at the end). This buggy behaviour 1240 # be to the old table (which will be dropped at the end). This buggy behaviour
1277 log.info(u"\nTransfering table thread") 1295 log.info(u"\nTransfering table thread")
1278 query = (u"INSERT INTO thread (id, history_uid, thread_id, parent_id) SELECT id" 1296 query = (u"INSERT INTO thread (id, history_uid, thread_id, parent_id) SELECT id"
1279 u", history_uid, thread_id, parent_id FROM thread_old") 1297 u", history_uid, thread_id, parent_id FROM thread_old")
1280 yield self.dbpool.runQuery(query) 1298 yield self.dbpool.runQuery(query)
1281 1299
1300 log.info(u"\nRemoving old tables")
1282 # because of foreign keys, tables referencing history_old 1301 # because of foreign keys, tables referencing history_old
1283 # must be deleted first 1302 # must be deleted first
1284 yield self.dbpool.runQuery("DROP TABLE thread_old") 1303 yield self.dbpool.runQuery("DROP TABLE thread_old")
1285 yield self.dbpool.runQuery("DROP TABLE subject_old") 1304 yield self.dbpool.runQuery("DROP TABLE subject_old")
1286 yield self.dbpool.runQuery("DROP TABLE message_old") 1305 yield self.dbpool.runQuery("DROP TABLE message_old")
1287 yield self.dbpool.runQuery("DROP TABLE history_old") 1306 yield self.dbpool.runQuery("DROP TABLE history_old")
1307 log.info(u"\nReducing database size (this can take a while)")
1308 yield self.dbpool.runQuery("VACUUM")
1288 log.info(u"Database update done :)") 1309 log.info(u"Database update done :)")
1289 1310
1290 @defer.inlineCallbacks 1311 @defer.inlineCallbacks
1291 def update_v3(self): 1312 def update_v3(self):
1292 """Update database from v2 to v3 (message refactoring)""" 1313 """Update database from v2 to v3 (message refactoring)"""