# HG changeset patch
# User Goffi <goffi@goffi.org>
# Date 1544572394 -3600
# Node ID 14e1db0c138362610508e542f7ede3b90aca9033
# Parent  4aaa47f62d8d0daeccbb6817bc496d8f3aefcbff
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.

diff -r 4aaa47f62d8d -r 14e1db0c1383 sat/memory/sqlite.py
--- a/sat/memory/sqlite.py	Tue Dec 11 23:53:27 2018 +0100
+++ b/sat/memory/sqlite.py	Wed Dec 12 00:53:14 2018 +0100
@@ -1192,9 +1192,10 @@
     @defer.inlineCallbacks
     def update_v7(self):
         """Update database from v6 to v7 (history unique constraint change)"""
-        log.info(u"Database update to v7, this may take a while")
+        log.info(u"Database update to v7, this may be long depending on your history "
+                 u"size, please be patient.")
 
-        log.info(u"some cleaning first")
+        log.info(u"Some cleaning first")
         # we need to fix duplicate stanza_id, as it can result in conflicts with the new schema
         # normally database should not contain any, but better safe than sorry.
         rows = yield self.dbpool.runQuery(
@@ -1213,8 +1214,25 @@
                     "DELETE FROM history WHERE uid IN ({})".format(u",".join(u"?"*len(uids))),
                     uids)
 
-        log.info(u'Deleting "info" messages (this can take a while, please be patient)')
-        yield self.dbpool.runQuery(u"DELETE FROM history where type='info'");
+        def deleteInfo(txn):
+            # with foreign_keys on, the delete takes ages, so we deactivate it here
+            # the time to delete info messages from history.
+            txn.execute("PRAGMA foreign_keys = OFF")
+            txn.execute(u"DELETE FROM message WHERE history_uid IN (SELECT uid FROM history WHERE "
+                        u"type='info')")
+            txn.execute(u"DELETE FROM subject WHERE history_uid IN (SELECT uid FROM history WHERE "
+                        u"type='info')")
+            txn.execute(u"DELETE FROM thread WHERE history_uid IN (SELECT uid FROM history WHERE "
+                        u"type='info')")
+            txn.execute(u"DELETE FROM message WHERE history_uid IN (SELECT uid FROM history WHERE "
+                        u"type='info')")
+            txn.execute(u"DELETE FROM history WHERE type='info'")
+            # not sure that is is necessary to reactivate here, but in doubt…
+            txn.execute("PRAGMA foreign_keys = ON")
+
+        log.info(u'Deleting "info" messages (this can take a while)')
+        yield self.dbpool.runInteraction(deleteInfo)
+
         log.info(u"Cleaning done")
 
         # we have to rename table we will replace
@@ -1279,12 +1297,15 @@
                  u", history_uid, thread_id, parent_id FROM thread_old")
         yield self.dbpool.runQuery(query)
 
+        log.info(u"\nRemoving old tables")
         # because of foreign keys, tables referencing history_old
         # must be deleted first
         yield self.dbpool.runQuery("DROP TABLE thread_old")
         yield self.dbpool.runQuery("DROP TABLE subject_old")
         yield self.dbpool.runQuery("DROP TABLE message_old")
         yield self.dbpool.runQuery("DROP TABLE history_old")
+        log.info(u"\nReducing database size (this can take a while)")
+        yield self.dbpool.runQuery("VACUUM")
         log.info(u"Database update done :)")
 
     @defer.inlineCallbacks