libervia-backend: src/memory/sqlite.py annotate

annotate src/memory/sqlite.py @ 2138:6e509ee853a8

plugin OTR, core; use of new sendMessage + OTR mini refactoring: - new client.sendMessage method is used instead of sendMessageToStream - client.feedback is used in OTR - OTR now add message processing hints and carbon private element as recommanded by XEP-0364. Explicit Message Encryption is still TODO - OTR use the new sendMessageFinish trigger, this has a number of advantages: * there is little risk that OTR is skipped by other plugins (they have to use client.sendMessage as recommanded) * being at the end of the chain, OTR can check and remove any HTML or other leaking elements * OTR doesn't have to skip other plugins anymore, this means that things like delivery receipts are now working with OTR (but because there is not full stanza encryption, they can leak metadata) * OTR can decide to follow storage hint by letting or deleting "history" key

author	Goffi <goffi@goffi.org>
date	Sun, 05 Feb 2017 15:00:01 +0100
parents	b536dd121da1
children	c3cac21157d4

rev	line source
1934 2daf7b4c6756 use of /usr/bin/env instead of /usr/bin/python in shebang Goffi <goffi@goffi.org> parents: 1766 diff changeset	1 #!/usr/bin/env python2
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	2 # -- coding: utf-8 --
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	3
609 84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	4 # SAT: a jabber client
1766 d17772b0fe22 copyright update Goffi <goffi@goffi.org> parents: 1705 diff changeset	5 # Copyright (C) 2009-2016 Jérôme Poisson (goffi@goffi.org)
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	6
609 84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	7 # This program is free software: you can redistribute it and/or modify
84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	8 # it under the terms of the GNU Affero General Public License as published by
84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	9 # the Free Software Foundation, either version 3 of the License, or
84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	10 # (at your option) any later version.
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	11
609 84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	12 # This program is distributed in the hope that it will be useful,
84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	15 # GNU Affero General Public License for more details.
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	16
609 84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	17 # You should have received a copy of the GNU Affero General Public License
84a6e83157c2 fixed licences in docstrings (they are now in comments) Goffi <goffi@goffi.org> parents: 593 diff changeset	18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	19
771 bfabeedbf32e core: i18n refactoring: Goffi <goffi@goffi.org> parents: 681 diff changeset	20 from sat.core.i18n import _
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	21 from sat.core.constants import Const as C
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	22 from sat.core import exceptions
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	23 from sat.core.log import getLogger
301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	24 log = getLogger(__name__)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	25 from sat.memory.crypto import BlockCipher, PasswordHasher
1045 65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	26 from sat.tools.config import fixConfigOption
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	27 from twisted.enterprise import adbapi
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	28 from twisted.internet import defer
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	29 from twisted.python import failure
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	30 from collections import OrderedDict
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	31 import re
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	32 import os.path
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	33 import cPickle as pickle
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	34 import hashlib
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	35 import sqlite3
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	36
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	37 CURRENT_DB_VERSION = 3
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	38
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	39 # XXX: DATABASE schemas are used in the following way:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	40 # - 'current' key is for the actual database schema, for a new base
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	41 # - x(int) is for update needed between x-1 and x. All number are needed between y and z to do an update
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	42 # e.g.: if CURRENT_DB_VERSION is 6, 'current' is the actuel DB, and to update from version 3, numbers 4, 5 and 6 are needed
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	43 # a 'current' data dict can contains the keys:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	44 # - 'CREATE': it contains an Ordered dict with table to create as keys, and a len 2 tuple as value, where value[0] are the columns definitions and value[1] are the table constraints
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	45 # - 'INSERT': it contains an Ordered dict with table where values have to be inserted, and many tuples containing values to insert in the order of the rows (#TODO: manage named columns)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	46 # an update data dict (the ones with a number) can contains the keys 'create', 'delete', 'cols create', 'cols delete', 'cols modify', 'insert' or 'specific'. See Updater.generateUpdateData for more infos. This method can be used to autogenerate update_data, to ease the work of the developers.
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	47
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	48 DATABASE_SCHEMAS = {
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	49 "current": {'CREATE': OrderedDict((
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	50 ('profiles', (("id INTEGER PRIMARY KEY ASC", "name TEXT"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	51 ("UNIQUE (name)",))),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	52 ('message_types', (("type TEXT PRIMARY KEY",),
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	53 tuple())),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	54 ('history', (("uid TEXT PRIMARY KEY", "update_uid TEXT", "profile_id INTEGER", "source TEXT", "dest TEXT", "source_res TEXT", "dest_res TEXT",
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	55 "timestamp DATETIME NOT NULL", "received_timestamp DATETIME", # XXX: timestamp is the time when the message was emitted. If received time stamp is not NULL, the message was delayed and timestamp is the declared value (and received_timestamp the time of reception)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	56 "type TEXT", "extra BLOB"),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	57 ("FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE", "FOREIGN KEY(type) REFERENCES message_types(type)",
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	58 "UNIQUE (profile_id, timestamp, source, dest, source_res, dest_res)" # avoid storing 2 time the same message (specially for delayed cones)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	59 ))),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	60 ('message', (("id INTEGER PRIMARY KEY ASC", "history_uid INTEGER", "message TEXT", "language TEXT"),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	61 ("FOREIGN KEY(history_uid) REFERENCES history(uid) ON DELETE CASCADE",))),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	62 ('subject', (("id INTEGER PRIMARY KEY ASC", "history_uid INTEGER", "subject TEXT", "language TEXT"),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	63 ("FOREIGN KEY(history_uid) REFERENCES history(uid) ON DELETE CASCADE",))),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	64 ('thread', (("id INTEGER PRIMARY KEY ASC", "history_uid INTEGER", "thread_id TEXT", "parent_id TEXT"),("FOREIGN KEY(history_uid) REFERENCES history(uid) ON DELETE CASCADE",))),
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	65 ('param_gen', (("category TEXT", "name TEXT", "value TEXT"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	66 ("PRIMARY KEY (category,name)",))),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	67 ('param_ind', (("category TEXT", "name TEXT", "profile_id INTEGER", "value TEXT"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	68 ("PRIMARY KEY (category,name,profile_id)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	69 ('private_gen', (("namespace TEXT", "key TEXT", "value TEXT"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	70 ("PRIMARY KEY (namespace, key)",))),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	71 ('private_ind', (("namespace TEXT", "key TEXT", "profile_id INTEGER", "value TEXT"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	72 ("PRIMARY KEY (namespace, key, profile_id)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	73 ('private_gen_bin', (("namespace TEXT", "key TEXT", "value BLOB"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	74 ("PRIMARY KEY (namespace, key)",))),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	75 ('private_ind_bin', (("namespace TEXT", "key TEXT", "profile_id INTEGER", "value BLOB"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	76 ("PRIMARY KEY (namespace, key, profile_id)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE")))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	77 )),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	78 'INSERT': OrderedDict((
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	79 ('message_types', (("'chat'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	80 ("'error'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	81 ("'groupchat'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	82 ("'headline'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	83 ("'normal'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	84 ("'info'",) # info is not standard, but used to keep track of info like join/leave in a MUC
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	85 )),
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	86 )),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	87 },
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	88 3: {'specific': 'update_v3'
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	89 },
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	90 2: {'specific': 'update2raw_v2'
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	91 },
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	92 1: {'cols create': {'history': ('extra BLOB',)},
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	93 },
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	94 }
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	95
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	96 NOT_IN_EXTRA = ('received_timestamp', 'update_uid') # keys which are in message data extra but not stored in sqlite's extra field
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	97 # this is specific to this sqlite storage and for now only used for received_timestamp
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	98 # because this value is stored in a separate field
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	99
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	100
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	101 class ConnectionPool(adbapi.ConnectionPool):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	102 # Workaround to avoid IntegrityError causing (i)pdb to be launched in debug mode
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	103 def _runQuery(self, trans, args, *kw):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	104 try:
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	105 trans.execute(args, *kw)
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	106 except sqlite3.IntegrityError as e:
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	107 raise failure.Failure(e)
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	108 return trans.fetchall()
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	109
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	110
588 beaf6bec2fcd Remove every old-style class. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 587 diff changeset	111 class SqliteStorage(object):
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	112 """This class manage storage with Sqlite database"""
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	113
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	114 def __init__(self, db_filename, sat_version):
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	115 """Connect to the given database
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	116 @param db_filename: full path to the Sqlite database"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	117 self.initialized = defer.Deferred() # triggered when memory is fully initialised and ready
e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	118 self.profiles = {} # we keep cache for the profiles (key: profile name, value: profile id)
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	119
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	120 log.info(_("Connecting database"))
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	121 new_base = not os.path.exists(db_filename) # do we have to create the database ?
936 6404df5305e3 memory: be sure that local_dir exists before creating a new database souliane <souliane@mailoo.org> parents: 934 diff changeset	122 if new_base: # the dir may not exist if it's not the XDG recommended one
6404df5305e3 memory: be sure that local_dir exists before creating a new database souliane <souliane@mailoo.org> parents: 934 diff changeset	123 dir_ = os.path.dirname(db_filename)
6404df5305e3 memory: be sure that local_dir exists before creating a new database souliane <souliane@mailoo.org> parents: 934 diff changeset	124 if not os.path.exists(dir_):
6404df5305e3 memory: be sure that local_dir exists before creating a new database souliane <souliane@mailoo.org> parents: 934 diff changeset	125 os.makedirs(dir_, 0700)
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	126 self.dbpool = ConnectionPool("sqlite3", db_filename, check_same_thread=False)
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	127
624 70988f08d0ad core: fixed bad database creation on first run in sqlite storage Goffi <goffi@goffi.org> parents: 609 diff changeset	128 # init_defer is the initialisation deferred, initialisation is ok when all its callbacks have been done
934 34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	129 # XXX: foreign_keys activation doesn't seem to work, probably because of the multi-threading
34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	130 # All the requests that need to use this feature should be run with runInteraction instead,
34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	131 # so you can set the PRAGMA as it is done in self.deleteProfile
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	132 init_defer = self.dbpool.runOperation("PRAGMA foreign_keys = ON").addErrback(lambda x: log.error(_("Can't activate foreign keys")))
624 70988f08d0ad core: fixed bad database creation on first run in sqlite storage Goffi <goffi@goffi.org> parents: 609 diff changeset	133
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	134 def getNewBaseSql():
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	135 log.info(_("The database is new, creating the tables"))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	136 database_creation = ["PRAGMA user_version=%d" % CURRENT_DB_VERSION]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	137 database_creation.extend(Updater.createData2Raw(DATABASE_SCHEMAS['current']['CREATE']))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	138 database_creation.extend(Updater.insertData2Raw(DATABASE_SCHEMAS['current']['INSERT']))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	139 return database_creation
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	140
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	141 def getUpdateSql():
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	142 updater = Updater(self.dbpool, sat_version)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	143 return updater.checkUpdates()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	144
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	145 def commitStatements(statements):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	146
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	147 if statements is None:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	148 return defer.succeed(None)
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	149 log.debug(u"===== COMMITTING STATEMENTS =====\n%s\n============\n\n" % '\n'.join(statements))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	150 d = self.dbpool.runInteraction(self._updateDb, tuple(statements))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	151 return d
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	152
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	153 init_defer.addCallback(lambda ignore: getNewBaseSql() if new_base else getUpdateSql())
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	154 init_defer.addCallback(commitStatements)
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	155
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	156 def fillProfileCache(ignore):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	157 d = self.dbpool.runQuery("SELECT name,id FROM profiles").addCallback(self._profilesCache)
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	158 d.chainDeferred(self.initialized)
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	159
624 70988f08d0ad core: fixed bad database creation on first run in sqlite storage Goffi <goffi@goffi.org> parents: 609 diff changeset	160 init_defer.addCallback(fillProfileCache)
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	161
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	162 def _updateDb(self, interaction, statements):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	163 for statement in statements:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	164 interaction.execute(statement)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	165
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	166 #Profiles
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	167 def _profilesCache(self, profiles_result):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	168 """Fill the profiles cache
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	169 @param profiles_result: result of the sql profiles query"""
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	170 for profile in profiles_result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	171 name, id_ = profile
e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	172 self.profiles[name] = id_
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	173
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	174 def getProfilesList(self):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	175 """"Return list of all registered profiles"""
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	176 return self.profiles.keys()
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	177
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	178 def hasProfile(self, profile_name):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	179 """return True if profile_name exists
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	180 @param profile_name: name of the profile to check"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	181 return profile_name in self.profiles
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	182
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	183 def createProfile(self, name):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	184 """Create a new profile
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	185 @param name: name of the profile
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	186 @return: deferred triggered once profile is actually created"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	187
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	188 def getProfileId(ignore):
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	189 return self.dbpool.runQuery("SELECT (id) FROM profiles WHERE name = ?", (name, ))
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	190
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	191 def profile_created(profile_id):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	192 _id = profile_id[0][0]
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	193 self.profiles[name] = _id # we synchronise the cache
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	194
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	195 d = self.dbpool.runQuery("INSERT INTO profiles(name) VALUES (?)", (name, ))
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	196 d.addCallback(getProfileId)
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	197 d.addCallback(profile_created)
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	198 return d
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	199
420 acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	200 def deleteProfile(self, name):
acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	201 """Delete profile
acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	202 @param name: name of the profile
acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	203 @return: deferred triggered once profile is actually deleted"""
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	204 def deletionError(failure_):
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	205 log.error(_(u"Can't delete profile [%s]") % name)
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	206 return failure_
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	207
934 34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	208 def delete(txn):
1705 3765e10ec52f core (sqlite storage): do explicit delete to profile linked tables for deleteProfile Goffi <goffi@goffi.org> parents: 1455 diff changeset	209 profile_id = self.profiles.pop(name)
934 34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	210 txn.execute("PRAGMA foreign_keys = ON")
34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	211 txn.execute("DELETE FROM profiles WHERE name = ?", (name,))
1705 3765e10ec52f core (sqlite storage): do explicit delete to profile linked tables for deleteProfile Goffi <goffi@goffi.org> parents: 1455 diff changeset	212 # FIXME: the following queries should be done by the ON DELETE CASCADE
3765e10ec52f core (sqlite storage): do explicit delete to profile linked tables for deleteProfile Goffi <goffi@goffi.org> parents: 1455 diff changeset	213 # but it seems they are not, so we explicitly do them by security
3765e10ec52f core (sqlite storage): do explicit delete to profile linked tables for deleteProfile Goffi <goffi@goffi.org> parents: 1455 diff changeset	214 # this need more investigation
3765e10ec52f core (sqlite storage): do explicit delete to profile linked tables for deleteProfile Goffi <goffi@goffi.org> parents: 1455 diff changeset	215 txn.execute("DELETE FROM history WHERE profile_id = ?", (profile_id,))
3765e10ec52f core (sqlite storage): do explicit delete to profile linked tables for deleteProfile Goffi <goffi@goffi.org> parents: 1455 diff changeset	216 txn.execute("DELETE FROM param_ind WHERE profile_id = ?", (profile_id,))
3765e10ec52f core (sqlite storage): do explicit delete to profile linked tables for deleteProfile Goffi <goffi@goffi.org> parents: 1455 diff changeset	217 txn.execute("DELETE FROM private_ind WHERE profile_id = ?", (profile_id,))
3765e10ec52f core (sqlite storage): do explicit delete to profile linked tables for deleteProfile Goffi <goffi@goffi.org> parents: 1455 diff changeset	218 txn.execute("DELETE FROM private_ind_bin WHERE profile_id = ?", (profile_id,))
934 34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	219 return None
34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	220
34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	221 d = self.dbpool.runInteraction(delete)
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	222 d.addCallback(lambda ignore: log.info(_("Profile [%s] deleted") % name))
593 70bae685d05c core: added forgotten errback in sqlite's deleteProfile Goffi <goffi@goffi.org> parents: 592 diff changeset	223 d.addErrback(deletionError)
420 acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	224 return d
acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	225
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	226 #Params
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	227 def loadGenParams(self, params_gen):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	228 """Load general parameters
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	229 @param params_gen: dictionary to fill
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	230 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	231
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	232 def fillParams(result):
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	233 for param in result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	234 category, name, value = param
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	235 params_gen[(category, name)] = value
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	236 log.debug(_(u"loading general parameters from database"))
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	237 return self.dbpool.runQuery("SELECT category,name,value FROM param_gen").addCallback(fillParams)
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	238
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	239 def loadIndParams(self, params_ind, profile):
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	240 """Load individual parameters
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	241 @param params_ind: dictionary to fill
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	242 @param profile: a profile which must exist
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	243 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	244
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	245 def fillParams(result):
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	246 for param in result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	247 category, name, value = param
423 6c20c76abdcc backend: - bridge async D-Bus method now automatically manage callback and errback, we just have to return a deferred Goffi <goffi@goffi.org> parents: 420 diff changeset	248 params_ind[(category, name)] = value
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	249 log.debug(_(u"loading individual parameters from database"))
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	250 d = self.dbpool.runQuery("SELECT category,name,value FROM param_ind WHERE profile_id=?", (self.profiles[profile], ))
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	251 d.addCallback(fillParams)
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	252 return d
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	253
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	254 def getIndParam(self, category, name, profile):
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	255 """Ask database for the value of one specific individual parameter
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	256 @param category: category of the parameter
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	257 @param name: name of the parameter
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	258 @param profile: %(doc_profile)s
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	259 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	260 d = self.dbpool.runQuery("SELECT value FROM param_ind WHERE category=? AND name=? AND profile_id=?", (category, name, self.profiles[profile]))
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	261 d.addCallback(self.__getFirstResult)
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	262 return d
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	263
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	264 def setGenParam(self, category, name, value):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	265 """Save the general parameters in database
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	266 @param category: category of the parameter
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	267 @param name: name of the parameter
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	268 @param value: value to set
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	269 @return: deferred"""
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	270 d = self.dbpool.runQuery("REPLACE INTO param_gen(category,name,value) VALUES (?,?,?)", (category, name, value))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	271 d.addErrback(lambda ignore: log.error(_(u"Can't set general parameter (%(category)s/%(name)s) in database" % {"category": category, "name": name})))
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	272 return d
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	273
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	274 def setIndParam(self, category, name, value, profile):
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	275 """Save the individual parameters in database
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	276 @param category: category of the parameter
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	277 @param name: name of the parameter
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	278 @param value: value to set
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	279 @param profile: a profile which must exist
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	280 @return: deferred"""
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	281 d = self.dbpool.runQuery("REPLACE INTO param_ind(category,name,profile_id,value) VALUES (?,?,?,?)", (category, name, self.profiles[profile], value))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	282 d.addErrback(lambda ignore: log.error(_(u"Can't set individual parameter (%(category)s/%(name)s) for [%(profile)s] in database" % {"category": category, "name": name, "profile": profile})))
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	283 return d
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	284
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	285 #History
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	286
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	287 def _addToHistoryCb(self, dummy, data):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	288 # Message metadata were successfuly added to history
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	289 # now we can add message and subject
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	290 uid = data['uid']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	291 for key in ('message', 'subject'):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	292 for lang, value in data[key].iteritems():
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	293 d = self.dbpool.runQuery("INSERT INTO {key}(history_uid, {key}, language) VALUES (?,?,?)".format(key=key),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	294 (uid, value, lang or None))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	295 d.addErrback(lambda dummy: log.error(_(u"Can't save following {key} in history (uid: {uid}, lang:{lang}): {value}".format(
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	296 key=key, uid=uid, lang=lang, value=value))))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	297 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	298 thread = data['extra']['thread']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	299 except KeyError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	300 pass
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	301 else:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	302 thread_parent = data['extra'].get('thread_parent')
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	303 d = self.dbpool.runQuery("INSERT INTO thread(history_uid, thread_id, parent_id) VALUES (?,?,?)",
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	304 (uid, thread, thread_parent))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	305 d.addErrback(lambda dummy: log.error(_(u"Can't save following thread in history (uid: {uid}): thread:{thread}), parent:{parent}".format(
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	306 uid=uid, thread=thread, parent=thread_parent))))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	307
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	308 def _addToHistoryEb(self, failure_, data):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	309 failure_.trap(sqlite3.IntegrityError)
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	310 sqlite_msg = failure_.value.args[0]
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	311 if "UNIQUE constraint failed" in sqlite_msg:
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	312 log.debug(u"message {} is already in history, not storing it again".format(data['uid']))
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	313 if 'received_timestamp' not in data:
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	314 log.warning(u"duplicate message is not delayed, this is maybe a bug: data={}".format(data))
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	315 # we cancel message to avoid sending duplicate message to frontends
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	316 raise failure.Failure(exceptions.CancelError("Cancelled duplicated message"))
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	317 else:
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	318 log.error(u"Can't store message in history: {}".format(failure_))
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	319
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	320 def _logHistoryError(self, failure_, from_jid, to_jid, data):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	321 if failure_.check(exceptions.CancelError):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	322 # we propagate CancelError to avoid sending message to frontends
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	323 raise failure_
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	324 log.error(_(u"Can't save following message in history: from [{from_jid}] to [{to_jid}] (uid: {uid})"
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	325 .format(from_jid=from_jid.full(), to_jid=to_jid.full(), uid=data['uid'])))
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	326
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	327 def addToHistory(self, data, profile):
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	328 """Store a new message in history
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	329
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	330 @param data(dict): message data as build by SatMessageProtocol.onMessage
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	331 """
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	332 extra = pickle.dumps({k: v for k, v in data['extra'].iteritems() if k not in NOT_IN_EXTRA}, 0)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	333 from_jid = data['from']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	334 to_jid = data['to']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	335 d = self.dbpool.runQuery("INSERT INTO history(uid, update_uid, profile_id, source, dest, source_res, dest_res, timestamp, received_timestamp, type, extra) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
1962 a45235d8dc93 memory (sqlite): fixed handling of extra (pickled data) by using sqlite3.Binary Goffi <goffi@goffi.org> parents: 1961 diff changeset	336 (data['uid'], data['extra'].get('update_uid'), self.profiles[profile], data['from'].userhost(), to_jid.userhost(), from_jid.resource, to_jid.resource, data['timestamp'], data.get('received_timestamp'), data['type'], sqlite3.Binary(extra)))
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	337 d.addCallbacks(self._addToHistoryCb, self._addToHistoryEb, callbackArgs=[data], errbackArgs=[data])
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	338 d.addErrback(self._logHistoryError, from_jid, to_jid, data)
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	339 return d
e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	340
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	341 def sqliteHistoryToList(self, query_result):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	342 """Get SQL query result and return a list of message data dicts"""
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	343 result = []
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	344 current = {'uid': None}
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	345 for row in reversed(query_result):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	346 uid, update_uid, source, dest, source_res, dest_res, timestamp, received_timestamp,\
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	347 type_, extra, message, message_lang, subject, subject_lang, thread, thread_parent = row
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	348 if uid != current['uid']:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	349 # new message
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	350 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	351 extra = pickle.loads(str(extra or ""))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	352 except EOFError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	353 extra = {}
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	354 current = {
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	355 'from': "%s/%s" % (source, source_res) if source_res else source,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	356 'to': "%s/%s" % (dest, dest_res) if dest_res else dest,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	357 'uid': uid,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	358 'message': {},
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	359 'subject': {},
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	360 'type': type_,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	361 'extra': extra,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	362 'timestamp': timestamp,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	363 }
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	364 if update_uid is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	365 current['extra']['update_uid'] = update_uid
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	366 if received_timestamp is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	367 current['extra']['received_timestamp'] = str(received_timestamp)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	368 result.append(current)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	369
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	370 if message is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	371 current['message'][message_lang or ''] = message
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	372
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	373 if subject is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	374 current['subject'][subject_lang or ''] = subject
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	375
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	376 if thread is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	377 current_extra = current['extra']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	378 current_extra['thread'] = thread
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	379 if thread_parent is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	380 current_extra['thread_parent'] = thread_parent
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	381 else:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	382 if thread_parent is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	383 log.error(u"Database inconsistency: thread parent without thread (uid: {uid}, thread_parent: {parent})"
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	384 .format(uid=uid, parent=thread_parent))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	385
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	386 return result
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	387
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	388 def listDict2listTuple(self, messages_data):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	389 """Return a list of tuple as used in bridge from a list of messages data"""
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	390 ret = []
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	391 for m in messages_data:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	392 ret.append((m['uid'], m['timestamp'], m['from'], m['to'], m['message'], m['subject'], m['type'], m['extra']))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	393 return ret
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	394
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	395 def historyGet(self, from_jid, to_jid, limit=None, between=True, filters=None, profile=None):
1222 e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	396 """Retrieve messages in history
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	397
1222 e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	398 @param from_jid (JID): source JID (full, or bare for catchall)
e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	399 @param to_jid (JID): dest JID (full, or bare for catchall)
e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	400 @param limit (int): maximum number of messages to get:
e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	401 - 0 for no message (returns the empty list)
e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	402 - None for unlimited
e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	403 @param between (bool): confound source and dest (ignore the direction)
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	404 @param search (unicode): pattern to filter the history results
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	405 @param profile (unicode): %(doc_profile)s
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	406 @return: list of tuple as in [messageNew]
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	407 """
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	408 assert profile
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	409 if filters is None:
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	410 filters = {}
1221 5e5661ab5c81 memory: getHistory with limit=0 now returns an empty list, use limit=None to return all messages souliane <souliane@mailoo.org> parents: 1099 diff changeset	411 if limit == 0:
5e5661ab5c81 memory: getHistory with limit=0 now returns an empty list, use limit=None to return all messages souliane <souliane@mailoo.org> parents: 1099 diff changeset	412 return defer.succeed([])
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	413
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	414 query_parts = [u"SELECT uid, update_uid, source, dest, source_res, dest_res, timestamp, received_timestamp,\
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	415 type, extra, message, message.language, subject, subject.language, thread_id, thread.parent_id\
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	416 FROM history LEFT JOIN message ON history.uid = message.history_uid\
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	417 LEFT JOIN subject ON history.uid=subject.history_uid\
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	418 LEFT JOIN thread ON history.uid=thread.history_uid\
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	419 WHERE profile_id=? AND"] # FIXME: not sure if it's the best request, messages and subjects can appear several times here
538 2c4016921403 core, frontends, bridgen plugins: fixed methods which were unproperly managing multi-profiles Goffi <goffi@goffi.org> parents: 512 diff changeset	420 values = [self.profiles[profile]]
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	421
669 ffb716804580 core, bridge: extra parameter is saved in history: Goffi <goffi@goffi.org> parents: 624 diff changeset	422 def test_jid(type_, _jid):
512 862c0d6ab974 core, bridge, quick_frontend: MUC private messages history management: Goffi <goffi@goffi.org> parents: 480 diff changeset	423 values.append(_jid.userhost())
862c0d6ab974 core, bridge, quick_frontend: MUC private messages history management: Goffi <goffi@goffi.org> parents: 480 diff changeset	424 if _jid.resource:
862c0d6ab974 core, bridge, quick_frontend: MUC private messages history management: Goffi <goffi@goffi.org> parents: 480 diff changeset	425 values.append(_jid.resource)
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	426 return u'(%s=? AND %s_res=?)' % (type_, type_)
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	427 return u'%s=?' % (type_, )
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	428
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	429 if between:
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	430 query_parts.append(u"((%s AND %s) OR (%s AND %s))" % (test_jid('source', from_jid),
681 66633443bcd7 memory: fixed getHistory behavior when parameter between=True: souliane <souliane@mailoo.org> parents: 669 diff changeset	431 test_jid('dest', to_jid),
66633443bcd7 memory: fixed getHistory behavior when parameter between=True: souliane <souliane@mailoo.org> parents: 669 diff changeset	432 test_jid('source', to_jid),
66633443bcd7 memory: fixed getHistory behavior when parameter between=True: souliane <souliane@mailoo.org> parents: 669 diff changeset	433 test_jid('dest', from_jid)))
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	434 else:
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	435 query_parts.append(u"%s AND %s" % (test_jid('source', from_jid),
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	436 test_jid('dest', to_jid)))
448 17c7e48bf68f core: - history management improved Goffi <goffi@goffi.org> parents: 441 diff changeset	437
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	438 if filters:
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	439 if 'body' in filters:
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	440 # TODO: use REGEXP (function to be defined) instead of GLOB: https://www.sqlite.org/lang_expr.html
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	441 query_parts.append(u"AND message LIKE ?")
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	442 values.append(u"%{}%".format(filters['body']))
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	443 if 'search' in filters:
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	444 query_parts.append(u"AND (message LIKE ? OR source_res LIKE ?)")
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	445 values.extend([u"%{}%".format(filters['search'])] * 2)
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	446 if 'types' in filters:
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	447 types = filters['types'].split()
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	448 query_parts.append(u"AND type IN ({})".format(u','.join("?"*len(types))))
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	449 values.extend(types)
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	450 if 'not_types' in filters:
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	451 types = filters['not_types'].split()
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	452 query_parts.append(u"AND type NOT IN ({})".format(u','.join("?"*len(types))))
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	453 values.extend(types)
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	454
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	455
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	456 query_parts.append(u"ORDER BY timestamp DESC") # we reverse the order in sqliteHistoryToList
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	457 # we use DESC here so LIMIT keep the last messages
1221 5e5661ab5c81 memory: getHistory with limit=0 now returns an empty list, use limit=None to return all messages souliane <souliane@mailoo.org> parents: 1099 diff changeset	458 if limit is not None:
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	459 query_parts.append(u"LIMIT ?")
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	460 values.append(limit)
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	461
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	462 d = self.dbpool.runQuery(u" ".join(query_parts), values)
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	463 d.addCallback(self.sqliteHistoryToList)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	464 d.addCallback(self.listDict2listTuple)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	465 return d
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	466
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	467 #Private values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	468 def loadGenPrivates(self, private_gen, namespace):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	469 """Load general private values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	470 @param private_gen: dictionary to fill
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	471 @param namespace: namespace of the values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	472 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	473
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	474 def fillPrivates(result):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	475 for private in result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	476 key, value = private
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	477 private_gen[key] = value
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	478 log.debug(_(u"loading general private values [namespace: %s] from database") % (namespace,))
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	479 d = self.dbpool.runQuery("SELECT key,value FROM private_gen WHERE namespace=?", (namespace, )).addCallback(fillPrivates)
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	480 return d.addErrback(lambda x: log.debug(_(u"No data present in database for namespace %s") % namespace))
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	481
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	482 def loadIndPrivates(self, private_ind, namespace, profile):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	483 """Load individual private values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	484 @param privates_ind: dictionary to fill
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	485 @param namespace: namespace of the values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	486 @param profile: a profile which must exist
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	487 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	488
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	489 def fillPrivates(result):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	490 for private in result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	491 key, value = private
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	492 private_ind[key] = value
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	493 log.debug(_(u"loading individual private values [namespace: %s] from database") % (namespace,))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	494 d = self.dbpool.runQuery("SELECT key,value FROM private_ind WHERE namespace=? AND profile_id=?", (namespace, self.profiles[profile]))
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	495 d.addCallback(fillPrivates)
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	496 return d.addErrback(lambda x: log.debug(_(u"No data present in database for namespace %s") % namespace))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	497
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	498 def setGenPrivate(self, namespace, key, value):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	499 """Save the general private value in database
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	500 @param category: category of the privateeter
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	501 @param key: key of the private value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	502 @param value: value to set
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	503 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	504 d = self.dbpool.runQuery("REPLACE INTO private_gen(namespace,key,value) VALUES (?,?,?)", (namespace, key, value))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	505 d.addErrback(lambda ignore: log.error(_(u"Can't set general private value (%(key)s) [namespace:%(namespace)s] in database" %
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	506 {"namespace": namespace, "key": key})))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	507 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	508
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	509 def setIndPrivate(self, namespace, key, value, profile):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	510 """Save the individual private value in database
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	511 @param namespace: namespace of the value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	512 @param key: key of the private value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	513 @param value: value to set
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	514 @param profile: a profile which must exist
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	515 @return: deferred"""
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	516 d = self.dbpool.runQuery("REPLACE INTO private_ind(namespace,key,profile_id,value) VALUES (?,?,?,?)", (namespace, key, self.profiles[profile], value))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	517 d.addErrback(lambda ignore: log.error(_(u"Can't set individual private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" %
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	518 {"namespace": namespace, "key": key, "profile": profile})))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	519 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	520
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	521 def delGenPrivate(self, namespace, key):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	522 """Delete the general private value from database
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	523 @param category: category of the privateeter
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	524 @param key: key of the private value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	525 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	526 d = self.dbpool.runQuery("DELETE FROM private_gen WHERE namespace=? AND key=?", (namespace, key))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	527 d.addErrback(lambda ignore: log.error(_(u"Can't delete general private value (%(key)s) [namespace:%(namespace)s] in database" %
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	528 {"namespace": namespace, "key": key})))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	529 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	530
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	531 def delIndPrivate(self, namespace, key, profile):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	532 """Delete the individual private value from database
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	533 @param namespace: namespace of the value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	534 @param key: key of the private value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	535 @param profile: a profile which must exist
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	536 @return: deferred"""
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	537 d = self.dbpool.runQuery("DELETE FROM private_ind WHERE namespace=? AND key=? AND profile=?)", (namespace, key, self.profiles[profile]))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	538 d.addErrback(lambda ignore: log.error(_(u"Can't delete individual private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" %
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	539 {"namespace": namespace, "key": key, "profile": profile})))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	540 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	541
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	542 def loadGenPrivatesBinary(self, private_gen, namespace):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	543 """Load general private binary values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	544 @param private_gen: dictionary to fill
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	545 @param namespace: namespace of the values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	546 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	547
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	548 def fillPrivates(result):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	549 for private in result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	550 key, value = private
436 5e9d28ca5109 core: sqlite persistentBinaryDict storage fix Goffi <goffi@goffi.org> parents: 432 diff changeset	551 private_gen[key] = pickle.loads(str(value))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	552 log.debug(_(u"loading general private binary values [namespace: %s] from database") % (namespace,))
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	553 d = self.dbpool.runQuery("SELECT key,value FROM private_gen_bin WHERE namespace=?", (namespace, )).addCallback(fillPrivates)
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	554 return d.addErrback(lambda x: log.debug(_(u"No binary data present in database for namespace %s") % namespace))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	555
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	556 def loadIndPrivatesBinary(self, private_ind, namespace, profile):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	557 """Load individual private binary values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	558 @param privates_ind: dictionary to fill
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	559 @param namespace: namespace of the values
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	560 @param profile: a profile which must exist
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	561 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	562
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	563 def fillPrivates(result):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	564 for private in result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	565 key, value = private
436 5e9d28ca5109 core: sqlite persistentBinaryDict storage fix Goffi <goffi@goffi.org> parents: 432 diff changeset	566 private_ind[key] = pickle.loads(str(value))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	567 log.debug(_(u"loading individual private binary values [namespace: %s] from database") % (namespace,))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	568 d = self.dbpool.runQuery("SELECT key,value FROM private_ind_bin WHERE namespace=? AND profile_id=?", (namespace, self.profiles[profile]))
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	569 d.addCallback(fillPrivates)
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	570 return d.addErrback(lambda x: log.debug(_(u"No binary data present in database for namespace %s") % namespace))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	571
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	572 def setGenPrivateBinary(self, namespace, key, value):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	573 """Save the general private binary value in database
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	574 @param category: category of the privateeter
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	575 @param key: key of the private value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	576 @param value: value to set
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	577 @return: deferred"""
1962 a45235d8dc93 memory (sqlite): fixed handling of extra (pickled data) by using sqlite3.Binary Goffi <goffi@goffi.org> parents: 1961 diff changeset	578 d = self.dbpool.runQuery("REPLACE INTO private_gen_bin(namespace,key,value) VALUES (?,?,?)", (namespace, key, sqlite3.Binary(pickle.dumps(value, 0))))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	579 d.addErrback(lambda ignore: log.error(_(u"Can't set general private binary value (%(key)s) [namespace:%(namespace)s] in database" %
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	580 {"namespace": namespace, "key": key})))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	581 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	582
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	583 def setIndPrivateBinary(self, namespace, key, value, profile):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	584 """Save the individual private binary value in database
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	585 @param namespace: namespace of the value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	586 @param key: key of the private value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	587 @param value: value to set
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	588 @param profile: a profile which must exist
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	589 @return: deferred"""
1962 a45235d8dc93 memory (sqlite): fixed handling of extra (pickled data) by using sqlite3.Binary Goffi <goffi@goffi.org> parents: 1961 diff changeset	590 d = self.dbpool.runQuery("REPLACE INTO private_ind_bin(namespace,key,profile_id,value) VALUES (?,?,?,?)", (namespace, key, self.profiles[profile], sqlite3.Binary(pickle.dumps(value, 0))))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	591 d.addErrback(lambda ignore: log.error(_(u"Can't set individual binary private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" %
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	592 {"namespace": namespace, "key": key, "profile": profile})))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	593 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	594
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	595 def delGenPrivateBinary(self, namespace, key):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	596 """Delete the general private binary value from database
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	597 @param category: category of the privateeter
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	598 @param key: key of the private value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	599 @return: deferred"""
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	600 d = self.dbpool.runQuery("DELETE FROM private_gen_bin WHERE namespace=? AND key=?", (namespace, key))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	601 d.addErrback(lambda ignore: log.error(_(u"Can't delete general private binary value (%(key)s) [namespace:%(namespace)s] in database" %
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	602 {"namespace": namespace, "key": key})))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	603 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	604
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	605 def delIndPrivateBinary(self, namespace, key, profile):
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	606 """Delete the individual private binary value from database
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	607 @param namespace: namespace of the value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	608 @param key: key of the private value
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	609 @param profile: a profile which must exist
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	610 @return: deferred"""
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	611 d = self.dbpool.runQuery("DELETE FROM private_ind_bin WHERE namespace=? AND key=? AND profile=?)", (namespace, key, self.profiles[profile]))
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	612 d.addErrback(lambda ignore: log.error(_(u"Can't delete individual private binary value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" %
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	613 {"namespace": namespace, "key": key, "profile": profile})))
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	614 return d
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	615 ##Helper methods##
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	616
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	617 def __getFirstResult(self, result):
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	618 """Return the first result of a database query
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	619 Useful when we are looking for one specific value"""
416 32dc8b18c2ae core: param loading/purging on profile connection/disconnection Goffi <goffi@goffi.org> parents: 413 diff changeset	620 return None if not result else result[0][0]
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	621
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	622
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	623 class Updater(object):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	624 stmnt_regex = re.compile(r"(?:[\w ]+(?:$[\w, ]+$)?)+")
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	625 clean_regex = re.compile(r"^ +\|(?<= ) +\|(?<=,) +\| +$")
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	626 CREATE_SQL = "CREATE TABLE %s (%s)"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	627 INSERT_SQL = "INSERT INTO %s VALUES (%s)"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	628 DROP_SQL = "DROP TABLE %s"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	629 ALTER_SQL = "ALTER TABLE %s ADD COLUMN %s"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	630 RENAME_TABLE_SQL = "ALTER TABLE %s RENAME TO %s"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	631
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	632 CONSTRAINTS = ('PRIMARY', 'UNIQUE', 'CHECK', 'FOREIGN')
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	633 TMP_TABLE = "tmp_sat_update"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	634
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	635 def __init__(self, dbpool, sat_version):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	636 self._sat_version = sat_version
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	637 self.dbpool = dbpool
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	638
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	639 def getLocalVersion(self):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	640 """ Get local database version
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	641 @return: version (int)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	642
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	643 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	644 return self.dbpool.runQuery("PRAGMA user_version").addCallback(lambda ret: int(ret[0][0]))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	645
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	646 def _setLocalVersion(self, version):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	647 """ Set local database version
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	648 @param version: version (int)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	649 @return: deferred
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	650
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	651 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	652 return self.dbpool.runOperation("PRAGMA user_version=%d" % version)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	653
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	654 def getLocalSchema(self):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	655 """ return raw local schema
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	656 @return: list of strings with CREATE sql statements for local database
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	657
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	658 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	659 d = self.dbpool.runQuery("select sql from sqlite_master where type = 'table'")
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	660 d.addCallback(lambda result: [row[0] for row in result])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	661 return d
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	662
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	663 @defer.inlineCallbacks
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	664 def checkUpdates(self):
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	665 """ Check if a database schema/content update is needed, according to DATABASE_SCHEMAS
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	666 @return: deferred which fire a list of SQL update statements, or None if no update is needed
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	667
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	668 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	669 local_version = yield self.getLocalVersion()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	670 raw_local_sch = yield self.getLocalSchema()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	671 local_sch = self.rawStatements2data(raw_local_sch)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	672 current_sch = DATABASE_SCHEMAS['current']['CREATE']
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	673 local_hash = self.statementHash(local_sch)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	674 current_hash = self.statementHash(current_sch)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	675
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	676 # Force the update if the schemas are unchanged but a specific update is needed
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	677 force_update = local_hash == current_hash and local_version < CURRENT_DB_VERSION \
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	678 and 'specific' in DATABASE_SCHEMAS[CURRENT_DB_VERSION]
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	679
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	680 if local_hash == current_hash and not force_update:
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	681 if local_version != CURRENT_DB_VERSION:
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	682 log.warning(_("Your local schema is up-to-date, but database versions mismatch, fixing it..."))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	683 yield self._setLocalVersion(CURRENT_DB_VERSION)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	684 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	685 # an update is needed
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	686
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	687 if local_version == CURRENT_DB_VERSION:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	688 # Database mismatch and we have the latest version
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	689 if self._sat_version.endswith('D'):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	690 # we are in a development version
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	691 update_data = self.generateUpdateData(local_sch, current_sch, False)
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	692 log.warning(_("There is a schema mismatch, but as we are on a dev version, database will be updated"))
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	693 update_raw = yield self.update2raw(update_data, True)
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	694 defer.returnValue(update_raw)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	695 else:
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	696 log.error(_(u"schema version is up-to-date, but local schema differ from expected current schema"))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	697 update_data = self.generateUpdateData(local_sch, current_sch, True)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	698 update_raw = yield self.update2raw(update_data)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	699 log.warning(_(u"Here are the commands that should fix the situation, use at your own risk (do a backup before modifying database), you can go to SàT's MUC room at sat@chat.jabberfr.org for help\n### SQL###\n%s\n### END SQL ###\n") % u'\n'.join("%s;" % statement for statement in update_raw))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	700 raise exceptions.DatabaseError("Database mismatch")
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	701 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	702 # Database is not up-to-date, we'll do the update
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	703 if force_update:
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	704 log.info(_("Database content needs a specific processing, local database will be updated"))
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	705 else:
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	706 log.info(_("Database schema has changed, local database will be updated"))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	707 update_raw = []
1019 6a16ec17a458 better PEP-8 compliance souliane <souliane@mailoo.org> parents: 993 diff changeset	708 for version in xrange(local_version + 1, CURRENT_DB_VERSION + 1):
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	709 try:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	710 update_data = DATABASE_SCHEMAS[version]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	711 except KeyError:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	712 raise exceptions.InternalError("Missing update definition (version %d)" % version)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	713 update_raw_step = yield self.update2raw(update_data)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	714 update_raw.extend(update_raw_step)
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	715 update_raw.append("PRAGMA user_version=%d" % CURRENT_DB_VERSION)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	716 defer.returnValue(update_raw)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	717
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	718 @staticmethod
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	719 def createData2Raw(data):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	720 """ Generate SQL statements from statements data
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	721 @param data: dictionary with table as key, and statements data in tuples as value
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	722 @return: list of strings with raw statements
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	723
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	724 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	725 ret = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	726 for table in data:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	727 defs, constraints = data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	728 assert isinstance(defs, tuple)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	729 assert isinstance(constraints, tuple)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	730 ret.append(Updater.CREATE_SQL % (table, ', '.join(defs + constraints)))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	731 return ret
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	732
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	733 @staticmethod
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	734 def insertData2Raw(data):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	735 """ Generate SQL statements from statements data
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	736 @param data: dictionary with table as key, and statements data in tuples as value
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	737 @return: list of strings with raw statements
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	738
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	739 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	740 ret = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	741 for table in data:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	742 values_tuple = data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	743 assert isinstance(values_tuple, tuple)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	744 for values in values_tuple:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	745 assert isinstance(values, tuple)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	746 ret.append(Updater.INSERT_SQL % (table, ', '.join(values)))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	747 return ret
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	748
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	749 def statementHash(self, data):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	750 """ Generate hash of template data
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	751 useful to compare schemas
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	752
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	753 @param data: dictionary of "CREATE" statement, with tables names as key,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	754 and tuples of (col_defs, constraints) as values
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	755 @return: hash as string
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	756 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	757 hash_ = hashlib.sha1()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	758 tables = data.keys()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	759 tables.sort()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	760
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	761 def stmnts2str(stmts):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	762 return ','.join([self.clean_regex.sub('',stmt) for stmt in sorted(stmts)])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	763
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	764 for table in tables:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	765 col_defs, col_constr = data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	766 hash_.update("%s:%s:%s" % (table, stmnts2str(col_defs), stmnts2str(col_constr)))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	767 return hash_.digest()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	768
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	769 def rawStatements2data(self, raw_statements):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	770 """ separate "CREATE" statements into dictionary/tuples data
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	771 @param raw_statements: list of CREATE statements as strings
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	772 @return: dictionary with table names as key, and a (col_defs, constraints) tuple
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	773
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	774 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	775 schema_dict = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	776 for create_statement in raw_statements:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	777 if not create_statement.startswith("CREATE TABLE "):
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	778 log.warning("Unexpected statement, ignoring it")
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	779 continue
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	780 _create_statement = create_statement[13:]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	781 table, raw_col_stats = _create_statement.split(' ',1)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	782 if raw_col_stats[0] != '(' or raw_col_stats[-1] != ')':
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	783 log.warning("Unexpected statement structure, ignoring it")
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	784 continue
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	785 col_stats = [stmt.strip() for stmt in self.stmnt_regex.findall(raw_col_stats[1:-1])]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	786 col_defs = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	787 constraints = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	788 for col_stat in col_stats:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	789 name = col_stat.split(' ',1)[0]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	790 if name in self.CONSTRAINTS:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	791 constraints.append(col_stat)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	792 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	793 col_defs.append(col_stat)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	794 schema_dict[table] = (tuple(col_defs), tuple(constraints))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	795 return schema_dict
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	796
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	797 def generateUpdateData(self, old_data, new_data, modify=False):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	798 """ Generate data for automatic update between two schema data
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	799
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	800 @param old_data: data of the former schema (which must be updated)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	801 @param new_data: data of the current schema
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	802 @param modify: if True, always use "cols modify" table, else try to ALTER tables
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	803 @return: update data, a dictionary with:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	804 - 'create': dictionary of tables to create
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	805 - 'delete': tuple of tables to delete
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	806 - 'cols create': dictionary of columns to create (table as key, tuple of columns to create as value)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	807 - 'cols delete': dictionary of columns to delete (table as key, tuple of columns to delete as value)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	808 - 'cols modify': dictionary of columns to modify (table as key, tuple of old columns to transfert as value). With this table, a new table will be created, and content from the old table will be transfered to it, only cols specified in the tuple will be transfered.
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	809
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	810 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	811
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	812 create_tables_data = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	813 create_cols_data = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	814 modify_cols_data = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	815 delete_cols_data = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	816 old_tables = set(old_data.keys())
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	817 new_tables = set(new_data.keys())
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	818
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	819 def getChanges(set_olds, set_news):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	820 to_create = set_news.difference(set_olds)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	821 to_delete = set_olds.difference(set_news)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	822 to_check = set_news.intersection(set_olds)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	823 return tuple(to_create), tuple(to_delete), tuple(to_check)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	824
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	825 tables_to_create, tables_to_delete, tables_to_check = getChanges(old_tables, new_tables)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	826
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	827 for table in tables_to_create:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	828 create_tables_data[table] = new_data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	829
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	830 for table in tables_to_check:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	831 old_col_defs, old_constraints = old_data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	832 new_col_defs, new_constraints = new_data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	833 for obj in old_col_defs, old_constraints, new_col_defs, new_constraints:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	834 if not isinstance(obj, tuple):
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	835 raise exceptions.InternalError("Columns definitions must be tuples")
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	836 defs_create, defs_delete, ignore = getChanges(set(old_col_defs), set(new_col_defs))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	837 constraints_create, constraints_delete, ignore = getChanges(set(old_constraints), set(new_constraints))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	838 created_col_names = set([name.split(' ',1)[0] for name in defs_create])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	839 deleted_col_names = set([name.split(' ',1)[0] for name in defs_delete])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	840 if (created_col_names.intersection(deleted_col_names or constraints_create or constraints_delete) or
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	841 (modify and (defs_create or constraints_create or defs_delete or constraints_delete))):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	842 # we have modified columns, we need to transfer table
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	843 # we determinate which columns are in both schema so we can transfer them
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	844 old_names = set([name.split(' ',1)[0] for name in old_col_defs])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	845 new_names = set([name.split(' ',1)[0] for name in new_col_defs])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	846 modify_cols_data[table] = tuple(old_names.intersection(new_names));
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	847 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	848 if defs_create:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	849 create_cols_data[table] = (defs_create)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	850 if defs_delete or constraints_delete:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	851 delete_cols_data[table] = (defs_delete)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	852
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	853 return {'create': create_tables_data,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	854 'delete': tables_to_delete,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	855 'cols create': create_cols_data,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	856 'cols delete': delete_cols_data,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	857 'cols modify': modify_cols_data
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	858 }
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	859
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	860 @defer.inlineCallbacks
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	861 def update2raw(self, update, dev_version=False):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	862 """ Transform update data to raw SQLite statements
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	863
1455 4fb3280c4568 tmp (wokkel.rsm): use of super instead of direct call in PubSubRequest Goffi <goffi@goffi.org> parents: 1409 diff changeset	864 @param update: update data as returned by generateUpdateData
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	865 @param dev_version: if True, update will be done in dev mode: no deletion will be done, instead a message will be shown. This prevent accidental lost of data while working on the code/database.
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	866 @return: list of string with SQL statements needed to update the base
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	867 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	868 ret = self.createData2Raw(update.get('create', {}))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	869 drop = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	870 for table in update.get('delete', tuple()):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	871 drop.append(self.DROP_SQL % table)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	872 if dev_version:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	873 if drop:
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	874 log.info("Dev version, SQL NOT EXECUTED:\n--\n%s\n--\n" % "\n".join(drop))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	875 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	876 ret.extend(drop)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	877
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	878 cols_create = update.get('cols create', {})
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	879 for table in cols_create:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	880 for col_def in cols_create[table]:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	881 ret.append(self.ALTER_SQL % (table, col_def))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	882
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	883 cols_delete = update.get('cols delete', {})
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	884 for table in cols_delete:
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	885 log.info("Following columns in table [%s] are not needed anymore, but are kept for dev version: %s" % (table, ", ".join(cols_delete[table])))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	886
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	887 cols_modify = update.get('cols modify', {})
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	888 for table in cols_modify:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	889 ret.append(self.RENAME_TABLE_SQL % (table, self.TMP_TABLE))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	890 main, extra = DATABASE_SCHEMAS['current']['CREATE'][table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	891 ret.append(self.CREATE_SQL % (table, ', '.join(main + extra)))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	892 common_cols = ', '.join(cols_modify[table])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	893 ret.append("INSERT INTO %s (%s) SELECT %s FROM %s" % (table, common_cols, common_cols, self.TMP_TABLE))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	894 ret.append(self.DROP_SQL % self.TMP_TABLE)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	895
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	896 insert = update.get('insert', {})
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	897 ret.extend(self.insertData2Raw(insert))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	898
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	899 specific = update.get('specific', None)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	900 if specific:
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	901 cmds = yield getattr(self, specific)()
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	902 ret.extend(cmds or [])
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	903 defer.returnValue(ret)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	904
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	905 @defer.inlineCallbacks
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	906 def update_v3(self):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	907 """Update database from v2 to v3 (message refactoring)"""
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	908 # XXX: this update do all the messages in one huge transaction
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	909 # this is really memory consuming, but was OK on a reasonably
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	910 # big database for tests. If issues are happening, we can cut it
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	911 # in smaller transactions using LIMIT and by deleting already updated
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	912 # messages
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	913 log.info(u"Database update to v3, this may take a while")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	914
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	915 # we need to fix duplicate timestamp, as it can result in conflicts with the new schema
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	916 rows = yield self.dbpool.runQuery("SELECT timestamp, COUNT(*) as c FROM history GROUP BY timestamp HAVING c>1")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	917 if rows:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	918 log.info("fixing duplicate timestamp")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	919 fixed = []
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	920 for timestamp, dummy in rows:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	921 ids_rows = yield self.dbpool.runQuery("SELECT id from history where timestamp=?", (timestamp,))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	922 for idx, (id_,) in enumerate(ids_rows):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	923 fixed.append(id_)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	924 yield self.dbpool.runQuery("UPDATE history SET timestamp=? WHERE id=?", (float(timestamp) + idx * 0.001, id_))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	925 log.info(u"fixed messages with ids {}".format(u', '.join([unicode(id_) for id_ in fixed])))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	926
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	927 def historySchema(txn):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	928 log.info(u"History schema update")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	929 txn.execute("ALTER TABLE history RENAME TO tmp_sat_update")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	930 txn.execute("CREATE TABLE history (uid TEXT PRIMARY KEY, update_uid TEXT, profile_id INTEGER, source TEXT, dest TEXT, source_res TEXT, dest_res TEXT, timestamp DATETIME NOT NULL, received_timestamp DATETIME, type TEXT, extra BLOB, FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE, FOREIGN KEY(type) REFERENCES message_types(type), UNIQUE (profile_id, timestamp, source, dest, source_res, dest_res))")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	931 txn.execute("INSERT INTO history (uid, profile_id, source, dest, source_res, dest_res, timestamp, type, extra) SELECT id, profile_id, source, dest, source_res, dest_res, timestamp, type, extra FROM tmp_sat_update")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	932
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	933 yield self.dbpool.runInteraction(historySchema)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	934
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	935 def newTables(txn):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	936 log.info(u"Creating new tables")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	937 txn.execute("CREATE TABLE message (id INTEGER PRIMARY KEY ASC, history_uid INTEGER, message TEXT, language TEXT, FOREIGN KEY(history_uid) REFERENCES history(uid) ON DELETE CASCADE)")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	938 txn.execute("CREATE TABLE thread (id INTEGER PRIMARY KEY ASC, history_uid INTEGER, thread_id TEXT, parent_id TEXT, FOREIGN KEY(history_uid) REFERENCES history(uid) ON DELETE CASCADE)")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	939 txn.execute("CREATE TABLE subject (id INTEGER PRIMARY KEY ASC, history_uid INTEGER, subject TEXT, language TEXT, FOREIGN KEY(history_uid) REFERENCES history(uid) ON DELETE CASCADE)")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	940
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	941 yield self.dbpool.runInteraction(newTables)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	942
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	943 log.info(u"inserting new message type")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	944 yield self.dbpool.runQuery("INSERT INTO message_types VALUES (?)", ('info',))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	945
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	946 log.info(u"messages update")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	947 rows = yield self.dbpool.runQuery("SELECT id, timestamp, message, extra FROM tmp_sat_update")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	948 total = len(rows)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	949
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	950 def updateHistory(txn, queries):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	951 for query, args in iter(queries):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	952 txn.execute(query, args)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	953
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	954 queries = []
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	955 for idx, row in enumerate(rows, 1):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	956 if idx % 1000 == 0 or total - idx == 0:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	957 log.info("preparing message {}/{}".format(idx, total))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	958 id_, timestamp, message, extra = row
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	959 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	960 extra = pickle.loads(str(extra or ""))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	961 except EOFError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	962 extra = {}
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	963 except Exception:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	964 log.warning(u"Can't handle extra data for message id {}, ignoring it".format(id_))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	965 extra = {}
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	966
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	967 queries.append(("INSERT INTO message(history_uid, message) VALUES (?,?)", (id_, message)))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	968
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	969 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	970 subject = extra.pop('subject')
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	971 except KeyError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	972 pass
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	973 else:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	974 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	975 subject = subject.decode('utf-8')
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	976 except UnicodeEncodeError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	977 log.warning(u"Error while decoding subject, ignoring it")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	978 del extra['subject']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	979 else:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	980 queries.append(("INSERT INTO subject(history_uid, subject) VALUES (?,?)", (id_, subject)))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	981
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	982 received_timestamp = extra.pop('timestamp', None)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	983 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	984 del extra['archive']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	985 except KeyError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	986 # archive was not used
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	987 pass
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	988
1962 a45235d8dc93 memory (sqlite): fixed handling of extra (pickled data) by using sqlite3.Binary Goffi <goffi@goffi.org> parents: 1961 diff changeset	989 queries.append(("UPDATE history SET received_timestamp=?,extra=? WHERE uid=?",(id_, received_timestamp, sqlite3.Binary(pickle.dumps(extra, 0)))))
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	990
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	991 yield self.dbpool.runInteraction(updateHistory, queries)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	992
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	993 log.info("Dropping temporary table")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	994 yield self.dbpool.runQuery("DROP TABLE tmp_sat_update")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	995 log.info("Database update finished :)")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	996
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	997 def update2raw_v2(self):
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	998 """Update the database from v1 to v2 (add passwords encryptions):
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	999
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1000 - the XMPP password value is re-used for the profile password (new parameter)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1001 - the profile password is stored hashed
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1002 - the XMPP password is stored encrypted, with the profile password as key
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1003 - as there are no other stored passwords yet, it is enough, otherwise we
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1004 would need to encrypt the other passwords as it's done for XMPP password
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1005 """
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1006 xmpp_pass_path = ('Connection', 'Password')
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1007
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1008 def encrypt_values(values):
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1009 ret = []
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1010 list_ = []
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1011
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1012 def prepare_queries(result, xmpp_password):
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1013 try:
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1014 id_ = result[0][0]
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1015 except IndexError:
1409 3265a2639182 massive (preventive) addition of 'u' (unicode) before the strings passed to logging functions souliane <souliane@mailoo.org> parents: 1396 diff changeset	1016 log.error(u"Profile of id %d is referenced in 'param_ind' but it doesn't exist!" % profile_id)
1099 1baa116501fa memory (sqlite): fixes upgrade Deferred issue souliane <souliane@mailoo.org> parents: 1065 diff changeset	1017 return defer.succeed(None)
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1018
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1019 sat_password = xmpp_password
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1020 d1 = PasswordHasher.hash(sat_password)
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1021 personal_key = BlockCipher.getRandomKey(base64=True)
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1022 d2 = BlockCipher.encrypt(sat_password, personal_key)
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1023 d3 = BlockCipher.encrypt(personal_key, xmpp_password)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1024
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1025 def gotValues(res):
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1026 sat_cipher, personal_cipher, xmpp_cipher = res[0][1], res[1][1], res[2][1]
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1027 ret.append("INSERT INTO param_ind(category,name,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1028 (C.PROFILE_PASS_PATH[0], C.PROFILE_PASS_PATH[1], id_, sat_cipher))
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1029
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1030 ret.append("INSERT INTO private_ind(namespace,key,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1031 (C.MEMORY_CRYPTO_NAMESPACE, C.MEMORY_CRYPTO_KEY, id_, personal_cipher))
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1032
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1033 ret.append("REPLACE INTO param_ind(category,name,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1034 (xmpp_pass_path[0], xmpp_pass_path[1], id_, xmpp_cipher))
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1035
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1036 return defer.DeferredList([d1, d2, d3]).addCallback(gotValues)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1037
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1038 for profile_id, xmpp_password in values:
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1039 d = self.dbpool.runQuery("SELECT id FROM profiles WHERE id=?", (profile_id,))
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1040 d.addCallback(prepare_queries, xmpp_password)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1041 list_.append(d)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1042
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1043 d_list = defer.DeferredList(list_)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1044 d_list.addCallback(lambda dummy: ret)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1045 return d_list
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1046
1045 65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1047 def updateLiberviaConf(values):
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1048 try:
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1049 profile_id = values[0][0]
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1050 except IndexError:
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1051 return # no profile called "libervia"
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1052
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1053 def cb(selected):
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1054 try:
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1055 password = selected[0][0]
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1056 except IndexError:
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1057 log.error("Libervia profile exists but no password is set! Update Libervia configuration will be skipped.")
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1058 return
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1059 fixConfigOption('libervia', 'passphrase', password, False)
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1060 d = self.dbpool.runQuery("SELECT value FROM param_ind WHERE category=? AND name=? AND profile_id=?", xmpp_pass_path + (profile_id,))
1047 5f7f913c05ac memory: synchronize the upgrade to database v2 to be sure libervia's profile password is read to be written in the config file before its encryption souliane <souliane@mailoo.org> parents: 1045 diff changeset	1061 return d.addCallback(cb)
1045 65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1062
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1063 d = self.dbpool.runQuery("SELECT id FROM profiles WHERE name='libervia'")
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1064 d.addCallback(updateLiberviaConf)
1047 5f7f913c05ac memory: synchronize the upgrade to database v2 to be sure libervia's profile password is read to be written in the config file before its encryption souliane <souliane@mailoo.org> parents: 1045 diff changeset	1065 d.addCallback(lambda dummy: self.dbpool.runQuery("SELECT profile_id,value FROM param_ind WHERE category=? AND name=?", xmpp_pass_path))
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1066 d.addCallback(encrypt_values)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1067 return d

Mercurial > libervia-backend

annotate src/memory/sqlite.py @ 2138:6e509ee853a8