changeset a904a90544cb in /home/hg/repos/gajim-plugins

author: lovetox forenjunkie@chello.at branches: details:gajim-plugins?cmd=changeset;node=a904a90544cb description: SQL refactoring

diffstat:

omemo/omemo/encryption.py | 84 +-------------------- omemo/omemo/liteaxolotlstore.py | 2 + omemo/omemo/liteidentitykeystore.py | 5 - omemo/omemo/liteprekeystore.py | 4 - omemo/omemo/litesessionstore.py | 5 - omemo/omemo/litesignedprekeystore.py | 6 +- omemo/omemo/sql.py | 144 +++++++++++++++++++++++++++++++++++ 7 files changed, 148 insertions(+), 102 deletions(-)

diffs (truncated from 336 to 300 lines):

diff -r 30150e936a8c -r a904a90544cb omemo/omemo/encryption.py --- a/omemo/omemo/encryption.py Sat Jul 23 14:10:36 2016 +0200 +++ b/omemo/omemo/encryption.py Sat Jul 23 15:16:14 2016 +0200 @@ -18,8 +18,6 @@ # the Gajim-OMEMO plugin. If not, see http://www.gnu.org/licenses/. #

-from .db_helpers import table_exists, user_version -

class EncryptionState(): """ Used to store if OMEMO is enabled or not between gajim restarts """ @@ -28,7 +26,7 @@ """ :type dbConn: Connection """ - self.dbConn = migrate(dbConn) + self.dbConn = dbConn

def activate(self, jid): q = """INSERT OR REPLACE INTO encryption_state (jid, encryption) @@ -54,83 +52,3 @@ if result is None: return False return result[0] == 1 - - -def migrate(dbConn): - """ Creates the encryption_state table and migrates it if needed. - """ - if user_version(dbConn) == 0: - create_table = """ CREATE TABLE encryption_state ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - jid TEXT UNIQUE, - encryption INTEGER, - timestamp NUMERIC DEFAULT CURRENT_TIMESTAMP - ); - """ - - if table_exists(dbConn, 'encryption_state'): - # Given a database which already has `encryption_state` and has - # `user_version` 0, we assume the database needs migration and - # migrate it to add an `ID INTEGER AUTOINCREMENT` column - migrate_sql = """ - BEGIN TRANSACTION; - ALTER TABLE encryption_state RENAME TO encryption_state_back; - %s - INSERT INTO encryption_state(jid, encryption, timestamp) - SELECT jid, encryption, timestamp FROM encryption_state_back; - DROP TABLE encryption_state_back; - PRAGMA user_version=1; - END TRANSACTION ; - """ % (create_table) - dbConn.executescript(migrate_sql) - else: - # The database has `user_version` 0 and has no `encryption_state - # table, so crate it! - dbConn.executescript(""" BEGIN TRANSACTION; - %s - PRAGMA user_version=1; - END TRANSACTION; - """ % (create_table)) - # Find all double entrys and delete them - if user_version(dbConn) < 2: - delete_dupes = """ DELETE FROM identities WHERE _id not in ( - SELECT MIN(_id) - FROM identities - GROUP BY - recipient_id, public_key - ); - """ - - dbConn.executescript(""" BEGIN TRANSACTION; - %s - PRAGMA user_version=2; - END TRANSACTION; - """ % (delete_dupes)) - - if user_version(dbConn) < 3: - # Create a UNIQUE INDEX so every public key/recipient_id tuple - # can only be once in the db - add_index = """ CREATE UNIQUE INDEX IF NOT EXISTS - public_key_index - ON identities (public_key, recipient_id); - """ - - dbConn.executescript(""" BEGIN TRANSACTION; - %s - PRAGMA user_version=3; - END TRANSACTION; - """ % (add_index)) - - if user_version(dbConn) < 4: - # Adds column "active" to the sessions table - add_active = """ ALTER TABLE sessions - ADD COLUMN active INTEGER DEFAULT 1; - """ - - dbConn.executescript(""" BEGIN TRANSACTION; - %s - PRAGMA user_version=4; - END TRANSACTION; - """ % (add_active)) - - return dbConn diff -r 30150e936a8c -r a904a90544cb omemo/omemo/liteaxolotlstore.py --- a/omemo/omemo/liteaxolotlstore.py Sat Jul 23 14:10:36 2016 +0200 +++ b/omemo/omemo/liteaxolotlstore.py Sat Jul 23 15:16:14 2016 +0200 @@ -27,6 +27,7 @@ from .litesessionstore import LiteSessionStore from .litesignedprekeystore import LiteSignedPreKeyStore from .encryption import EncryptionState +from .sql import SQLDatabase

log = logging.getLogger('gajim.plugin_system.omemo')

@@ -42,6 +43,7 @@ raise AssertionError('Expected a sqlite3.Connection got ' + str(connection))

+ self.sql = SQLDatabase(connection) self.identityKeyStore = LiteIdentityKeyStore(connection) self.preKeyStore = LitePreKeyStore(connection) self.signedPreKeyStore = LiteSignedPreKeyStore(connection) diff -r 30150e936a8c -r a904a90544cb omemo/omemo/liteidentitykeystore.py --- a/omemo/omemo/liteidentitykeystore.py Sat Jul 23 14:10:36 2016 +0200 +++ b/omemo/omemo/liteidentitykeystore.py Sat Jul 23 15:16:14 2016 +0200 @@ -33,11 +33,6 @@ :type dbConn: Connection """ self.dbConn = dbConn - dbConn.execute( - "CREATE TABLE IF NOT EXISTS identities (" + - "_id INTEGER PRIMARY KEY AUTOINCREMENT, recipient_id TEXT," + - "registration_id INTEGER, public_key BLOB, private_key BLOB," + - "next_prekey_id INTEGER, timestamp INTEGER, trust INTEGER);")

def getIdentityKeyPair(self): q = "SELECT public_key, private_key FROM identities " + \ diff -r 30150e936a8c -r a904a90544cb omemo/omemo/liteprekeystore.py --- a/omemo/omemo/liteprekeystore.py Sat Jul 23 14:10:36 2016 +0200 +++ b/omemo/omemo/liteprekeystore.py Sat Jul 23 15:16:14 2016 +0200 @@ -28,10 +28,6 @@ :type dbConn: Connection """ self.dbConn = dbConn - dbConn.execute("CREATE TABLE IF NOT EXISTS prekeys(" + - "_id INTEGER PRIMARY KEY AUTOINCREMENT," + - "prekey_id INTEGER UNIQUE, sent_to_server BOOLEAN, " + - " record BLOB);")

def loadPreKey(self, preKeyId): q = "SELECT record FROM prekeys WHERE prekey_id = ?" diff -r 30150e936a8c -r a904a90544cb omemo/omemo/litesessionstore.py --- a/omemo/omemo/litesessionstore.py Sat Jul 23 14:10:36 2016 +0200 +++ b/omemo/omemo/litesessionstore.py Sat Jul 23 15:16:14 2016 +0200 @@ -27,11 +27,6 @@ :type dbConn: Connection """ self.dbConn = dbConn - dbConn.execute("CREATE TABLE IF NOT EXISTS sessions (" + - "_id INTEGER PRIMARY KEY AUTOINCREMENT," + - "recipient_id TEXT," + "device_id INTEGER," + - "record BLOB," + "timestamp INTEGER, " + - "UNIQUE(recipient_id, device_id));")

def loadSession(self, recipientId, deviceId): q = "SELECT record FROM sessions WHERE recipient_id = ? AND device_id = ?" diff -r 30150e936a8c -r a904a90544cb omemo/omemo/litesignedprekeystore.py --- a/omemo/omemo/litesignedprekeystore.py Sat Jul 23 14:10:36 2016 +0200 +++ b/omemo/omemo/litesignedprekeystore.py Sat Jul 23 15:16:14 2016 +0200 @@ -28,10 +28,6 @@ :type dbConn: Connection """ self.dbConn = dbConn - dbConn.execute( - "CREATE TABLE IF NOT EXISTS signed_prekeys (" + - "_id INTEGER PRIMARY" + " KEY AUTOINCREMENT," + - "prekey_id INTEGER UNIQUE, timestamp INTEGER, record BLOB);")

def loadSignedPreKey(self, signedPreKeyId): q = "SELECT record FROM signed_prekeys WHERE prekey_id = ?" @@ -83,4 +79,4 @@ cursor = self.dbConn.cursor() cursor.execute(q) result = cursor.fetchone() - return result[0] + return result[0] is not None diff -r 30150e936a8c -r a904a90544cb omemo/omemo/sql.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/omemo/omemo/sql.py Sat Jul 23 15:16:14 2016 +0200 @@ -0,0 +1,144 @@ +# -*- coding: utf-8 -*- +# +# Copyright 2015 Tarek Galal tare2.galal@gmail.com +# +# This file is part of Gajim-OMEMO plugin. +# +# The Gajim-OMEMO plugin is free software: you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the Free +# Software Foundation, either version 3 of the License, or (at your option) any +# later version. +# +# Gajim-OMEMO is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along with +# the Gajim-OMEMO plugin. If not, see http://www.gnu.org/licenses/. +# +from .db_helpers import user_version + + +class SQLDatabase(): + """ SQL Database """ + + def __init__(self, dbConn): + """ + :type dbConn: Connection + """ + self.dbConn = dbConn + self.createDb() + self.migrateDb() + + def createDb(self): + if user_version(self.dbConn) == 0: + + # Creates + # IdentityKeyStore + # PreKeyStore + # SignedPreKeyStore + # SessionStore + # EncryptionStore + + create_tables = ''' + CREATE TABLE IF NOT EXISTS identities ( + _id INTEGER PRIMARY KEY AUTOINCREMENT, recipient_id TEXT, + registration_id INTEGER, public_key BLOB, private_key BLOB, + next_prekey_id INTEGER, timestamp INTEGER, trust INTEGER); + + CREATE UNIQUE INDEX IF NOT EXISTS + public_key_index ON identities (public_key, recipient_id); + + CREATE TABLE IF NOT EXISTS prekeys( + _id INTEGER PRIMARY KEY AUTOINCREMENT, + prekey_id INTEGER UNIQUE, sent_to_server BOOLEAN, + record BLOB); + + CREATE TABLE IF NOT EXISTS signed_prekeys ( + _id INTEGER PRIMARY KEY AUTOINCREMENT, + prekey_id INTEGER UNIQUE, + timestamp NUMERIC DEFAULT CURRENT_TIMESTAMP, record BLOB); + + CREATE TABLE IF NOT EXISTS sessions ( + _id INTEGER PRIMARY KEY AUTOINCREMENT, + recipient_id TEXT, device_id INTEGER, + record BLOB, timestamp INTEGER, active INTEGER DEFAULT 1, + UNIQUE(recipient_id, device_id)); + + CREATE TABLE IF NOT EXISTS encryption_state ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + jid TEXT UNIQUE, + encryption INTEGER, + timestamp NUMERIC DEFAULT CURRENT_TIMESTAMP + ); + ''' + + create_db_sql = """ + BEGIN TRANSACTION; + %s + PRAGMA user_version=5; + END TRANSACTION; + """ % (create_tables) + self.dbConn.executescript(create_db_sql) + + def migrateDb(self): + """ Migrates the DB + """ + + # Find all double entrys and delete them + if user_version(self.dbConn) < 2: + delete_dupes = """ DELETE FROM identities WHERE _id not in ( + SELECT MIN(_id) + FROM identities + GROUP BY + recipient_id, public_key + ); + """ + + self.dbConn.executescript(""" BEGIN TRANSACTION; + %s + PRAGMA user_version=2; + END TRANSACTION; + """ % (delete_dupes)) + + if user_version(self.dbConn) < 3: + # Create a UNIQUE INDEX so every public key/recipient_id tuple + # can only be once in the db + add_index = """ CREATE UNIQUE INDEX IF NOT EXISTS + public_key_index