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

annotate sat/memory/sqlite.py @ 3250:e4d3ba75b1b2

core (memory/disco): fixed types of disco extensions: typeCheck() is not automatically called on reception by Wokkel, as a result extensions fields values may be strings instead of the field type. TypeCheck is now explicitly called in memory.disco to avoid that. It is not called immediately on reception as the string value is needed to calculate the capability hash

author	Goffi <goffi@goffi.org>
date	Tue, 14 Apr 2020 20:25:05 +0200
parents	7255286a298a
children	780fb8dd07ef

rev	line source
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1 #!/usr/bin/env python3
3137 559a625a236b fixed shebangs Goffi <goffi@goffi.org> parents: 3136 diff changeset	2
412 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
3136 9d0df638c8b4 dates update Goffi <goffi@goffi.org> parents: 3120 diff changeset	5 # Copyright (C) 2009-2020 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
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	24 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	25 from sat.tools.config import fixConfigOption
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	26 from twisted.enterprise import adbapi
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	27 from twisted.internet import defer
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	28 from twisted.words.protocols.jabber import jid
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
2964 17c61d09a85b memory (sqlite): exit with an error if current db version is higher than version expected in current code Goffi <goffi@goffi.org> parents: 2928 diff changeset	31 import sys
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	32 import re
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	33 import os.path
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	34 import pickle as pickle
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	35 import hashlib
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	36 import sqlite3
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	37 import json
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	38
2964 17c61d09a85b memory (sqlite): exit with an error if current db version is higher than version expected in current code Goffi <goffi@goffi.org> parents: 2928 diff changeset	39 log = getLogger(__name__)
17c61d09a85b memory (sqlite): exit with an error if current db version is higher than version expected in current code Goffi <goffi@goffi.org> parents: 2928 diff changeset	40
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	41 CURRENT_DB_VERSION = 8
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	42
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	43 # XXX: DATABASE schemas are used in the following way:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	44 # - '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	45 # - 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	46 # 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	47 # a 'current' data dict can contains the keys:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	48 # - '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	49 # - '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)
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	50 # - 'INDEX':
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	51 # 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.
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	52 # TODO: indexes need to be improved
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	53
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	54 DATABASE_SCHEMAS = {
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	55 "current": {'CREATE': OrderedDict((
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	56 ('profiles', (("id INTEGER PRIMARY KEY ASC", "name TEXT"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	57 ("UNIQUE (name)",))),
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	58 ('components', (("profile_id INTEGER PRIMARY KEY", "entry_point TEXT NOT NULL"),
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	59 ("FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE",))),
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	60 ('message_types', (("type TEXT PRIMARY KEY",),
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	61 ())),
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	62 ('history', (("uid TEXT PRIMARY KEY", "stanza_id TEXT", "update_uid TEXT", "profile_id INTEGER", "source TEXT", "dest TEXT", "source_res TEXT", "dest_res TEXT",
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	63 "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	64 "type TEXT", "extra BLOB"),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	65 ("FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE", "FOREIGN KEY(type) REFERENCES message_types(type)",
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	66 "UNIQUE (profile_id, stanza_id, source, dest)" # avoid storing 2 times the same message
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	67 ))),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	68 ('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	69 ("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	70 ('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	71 ("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	72 ('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	73 ('param_gen', (("category TEXT", "name TEXT", "value TEXT"),
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	74 ("PRIMARY KEY (category, name)",))),
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	75 ('param_ind', (("category TEXT", "name TEXT", "profile_id INTEGER", "value TEXT"),
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	76 ("PRIMARY KEY (profile_id, category, name)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))),
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	77 ('private_gen', (("namespace TEXT", "key TEXT", "value TEXT"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	78 ("PRIMARY KEY (namespace, key)",))),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	79 ('private_ind', (("namespace TEXT", "key TEXT", "profile_id INTEGER", "value TEXT"),
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	80 ("PRIMARY KEY (profile_id, namespace, key)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))),
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	81 ('private_gen_bin', (("namespace TEXT", "key TEXT", "value BLOB"),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	82 ("PRIMARY KEY (namespace, key)",))),
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	83 ('private_ind_bin', (("namespace TEXT", "key TEXT", "profile_id INTEGER", "value BLOB"),
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	84 ("PRIMARY KEY (profile_id, namespace, key)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))),
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	85 ('files', (("id TEXT NOT NULL", "version TEXT NOT NULL", "parent TEXT NOT NULL",
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	86 "type TEXT CHECK(type in ('{file}', '{directory}')) NOT NULL DEFAULT '{file}'".format(
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	87 file=C.FILE_TYPE_FILE, directory=C.FILE_TYPE_DIRECTORY),
2507 4c45df43ea44 core (memory/sqlite): renamed column for files from hash to file_hash: Goffi <goffi@goffi.org> parents: 2500 diff changeset	88 "file_hash TEXT", "hash_algo TEXT", "name TEXT NOT NULL", "size INTEGER",
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	89 "namespace TEXT", "mime_type TEXT",
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	90 "created DATETIME NOT NULL", "modified DATETIME",
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	91 "owner TEXT", "access TEXT", "extra TEXT", "profile_id INTEGER"),
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	92 ("PRIMARY KEY (id, version)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))),
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 'INSERT': OrderedDict((
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	95 ('message_types', (("'chat'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	96 ("'error'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	97 ("'groupchat'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	98 ("'headline'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	99 ("'normal'",),
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	100 ("'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	101 )),
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	102 )),
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	103 'INDEX': (('history', (('profile_id', 'timestamp'),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	104 ('profile_id', 'received_timestamp'))),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	105 ('message', ('history_uid',)),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	106 ('subject', ('history_uid',)),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	107 ('thread', ('history_uid',)),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	108 ('files', ('profile_id', 'mime_type', 'owner', 'parent'))),
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	109 },
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	110 8: {'specific': 'update_v8'
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	111 },
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	112 7: {'specific': 'update_v7'
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	113 },
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	114 6: {'cols create': {'history': ('stanza_id TEXT',)},
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	115 },
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	116 5: {'create': {'files': (("id TEXT NOT NULL", "version TEXT NOT NULL", "parent TEXT NOT NULL",
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	117 "type TEXT CHECK(type in ('{file}', '{directory}')) NOT NULL DEFAULT '{file}'".format(
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	118 file=C.FILE_TYPE_FILE, directory=C.FILE_TYPE_DIRECTORY),
2507 4c45df43ea44 core (memory/sqlite): renamed column for files from hash to file_hash: Goffi <goffi@goffi.org> parents: 2500 diff changeset	119 "file_hash TEXT", "hash_algo TEXT", "name TEXT NOT NULL", "size INTEGER",
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	120 "namespace TEXT", "mime_type TEXT",
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	121 "created DATETIME NOT NULL", "modified DATETIME",
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	122 "owner TEXT", "access TEXT", "extra TEXT", "profile_id INTEGER"),
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	123 ("PRIMARY KEY (id, version)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))},
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	124 },
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	125 4: {'create': {'components': (('profile_id INTEGER PRIMARY KEY', 'entry_point TEXT NOT NULL'), ('FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE',))}
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	126 },
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	127 3: {'specific': 'update_v3'
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	128 },
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	129 2: {'specific': 'update2raw_v2'
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	130 },
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	131 1: {'cols create': {'history': ('extra BLOB',)},
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	132 },
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	133 }
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	134
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	135 NOT_IN_EXTRA = ('stanza_id', 'received_timestamp', 'update_uid') # keys which are in message data extra but not stored in sqlite's extra field
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	136 # 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	137 # 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	138
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	139
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	140 class ConnectionPool(adbapi.ConnectionPool):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	141 def _runQuery(self, trans, args, *kw):
2357 fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	142 retry = kw.pop('query_retry', 6)
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	143 try:
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	144 trans.execute(args, *kw)
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	145 except sqlite3.IntegrityError as e:
2747 6487b8855c9a memory (sqlite): minor comment move Goffi <goffi@goffi.org> parents: 2746 diff changeset	146 # Workaround to avoid IntegrityError causing (i)pdb to be
6487b8855c9a memory (sqlite): minor comment move Goffi <goffi@goffi.org> parents: 2746 diff changeset	147 # launched in debug mode
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	148 raise failure.Failure(e)
2357 fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	149 except Exception as e:
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	150 # FIXME: in case of error, we retry a couple of times
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	151 # this is a workaround, we need to move to better
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	152 # Sqlite integration, probably with high level library
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	153 retry -= 1
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	154 if retry == 0:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	155 log.error(_('too many db tries, we abandon! Error message: {msg}\n'
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	156 'query was {query}'
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	157 .format(msg=e, query=' '.join([str(a) for a in args]))))
2357 fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	158 raise e
2746 1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	159 log.warning(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	160 _('exception while running query, retrying ({try_}): {msg}').format(
2357 fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	161 try_ = 6 - retry,
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	162 msg = e))
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	163 kw['query_retry'] = retry
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	164 return self._runQuery(trans, args, *kw)
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	165 return trans.fetchall()
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	166
2746 1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	167 def _runInteraction(self, interaction, args, *kw):
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	168 # sometimes interaction may fail while committing in _runInteraction
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	169 # and it may be due to a db lock. So we work around it in a similar way
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	170 # as for _runQuery but with only 3 tries
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	171 retry = kw.pop('interaction_retry', 4)
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	172 try:
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	173 return adbapi.ConnectionPool._runInteraction(self, interaction, args, *kw)
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	174 except Exception as e:
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	175 retry -= 1
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	176 if retry == 0:
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	177 log.error(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	178 _('too many interaction tries, we abandon! Error message: {msg}\n'
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	179 'interaction method was: {interaction}\n'
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	180 'interaction arguments were: {args}'
2919 e4715a609d75 memory (sqlite): minor log improvment Goffi <goffi@goffi.org> parents: 2918 diff changeset	181 .format(msg=e, interaction=interaction,
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	182 args=', '.join([str(a) for a in args]))))
2746 1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	183 raise e
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	184 log.warning(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	185 _('exception while running interaction, retrying ({try_}): {msg}')
2746 1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	186 .format(try_ = 4 - retry, msg = e))
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	187 kw['interaction_retry'] = retry
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	188 return self._runInteraction(interaction, args, *kw)
1e2f0856c845 memory (sqlite): retry _runInteraction in the same way as for _runQuery, this improve reliability Goffi <goffi@goffi.org> parents: 2735 diff changeset	189
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	190
588 beaf6bec2fcd Remove every old-style class. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 587 diff changeset	191 class SqliteStorage(object):
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	192 """This class manage storage with Sqlite database"""
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	193
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	194 def __init__(self, db_filename, sat_version):
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	195 """Connect to the given database
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	196
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	197 @param db_filename: full path to the Sqlite database
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	198 """
3120 0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	199 # triggered when memory is fully initialised and ready
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	200 self.initialized = defer.Deferred()
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	201 # we keep cache for the profiles (key: profile name, value: profile id)
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	202 self.profiles = {}
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	203
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	204 log.info(_("Connecting database"))
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	205 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	206 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	207 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	208 if not os.path.exists(dir_):
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	209 os.makedirs(dir_, 0o700)
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	210
2357 fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	211 def foreignKeysOn(sqlite):
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	212 sqlite.execute('PRAGMA foreign_keys = ON')
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	213
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	214 self.dbpool = ConnectionPool("sqlite3", db_filename, cp_openfun=foreignKeysOn, check_same_thread=False, timeout=15)
624 70988f08d0ad core: fixed bad database creation on first run in sqlite storage Goffi <goffi@goffi.org> parents: 609 diff changeset	215
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	216 def getNewBaseSql():
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	217 log.info(_("The database is new, creating the tables"))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	218 database_creation = ["PRAGMA user_version=%d" % CURRENT_DB_VERSION]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	219 database_creation.extend(Updater.createData2Raw(DATABASE_SCHEMAS['current']['CREATE']))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	220 database_creation.extend(Updater.insertData2Raw(DATABASE_SCHEMAS['current']['INSERT']))
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	221 database_creation.extend(Updater.indexData2Raw(DATABASE_SCHEMAS['current']['INDEX']))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	222 return database_creation
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	223
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	224 def getUpdateSql():
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	225 updater = Updater(self, sat_version)
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	226 return updater.checkUpdates()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	227
2357 fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	228 # init_defer is the initialisation deferred, initialisation is ok when all its callbacks have been done
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	229
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	230 init_defer = defer.succeed(None)
fa43e285df1d core (memory/sqlite): better stability: Goffi <goffi@goffi.org> parents: 2209 diff changeset	231
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	232 init_defer.addCallback(lambda ignore: getNewBaseSql() if new_base else getUpdateSql())
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	233 init_defer.addCallback(self.commitStatements)
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	234
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	235 def fillProfileCache(ignore):
3120 0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	236 return self.dbpool.runQuery("SELECT profile_id, entry_point FROM components").addCallback(self._cacheComponentsAndProfiles)
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	237
624 70988f08d0ad core: fixed bad database creation on first run in sqlite storage Goffi <goffi@goffi.org> parents: 609 diff changeset	238 init_defer.addCallback(fillProfileCache)
3120 0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	239 init_defer.chainDeferred(self.initialized)
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	240
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	241 def commitStatements(self, statements):
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	242
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	243 if statements is None:
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	244 return defer.succeed(None)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	245 log.debug("\n===== COMMITTING STATEMENTS =====\n%s\n============\n\n" % '\n'.join(statements))
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	246 d = self.dbpool.runInteraction(self._updateDb, tuple(statements))
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	247 return d
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	248
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	249 def _updateDb(self, interaction, statements):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	250 for statement in statements:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	251 interaction.execute(statement)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	252
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	253 ## Profiles
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	254
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	255 def _cacheComponentsAndProfiles(self, components_result):
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	256 """Get components results and send requests profiles
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	257
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	258 they will be both put in cache in _profilesCache
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	259 """
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	260 return self.dbpool.runQuery("SELECT name,id FROM profiles").addCallback(
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	261 self._cacheComponentsAndProfiles2, components_result)
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	262
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	263 def _cacheComponentsAndProfiles2(self, profiles_result, components):
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	264 """Fill the profiles cache
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	265
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	266 @param profiles_result: result of the sql profiles query
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	267 """
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	268 self.components = dict(components)
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	269 for profile in profiles_result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	270 name, id_ = profile
e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	271 self.profiles[name] = id_
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	272
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	273 def getProfilesList(self):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	274 """"Return list of all registered profiles"""
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	275 return list(self.profiles.keys())
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	276
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	277 def hasProfile(self, profile_name):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	278 """return True if profile_name exists
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	279
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	280 @param profile_name: name of the profile to check
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	281 """
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	282 return profile_name in self.profiles
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	283
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	284 def profileIsComponent(self, profile_name):
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	285 try:
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	286 return self.profiles[profile_name] in self.components
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	287 except KeyError:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	288 raise exceptions.NotFound("the requested profile doesn't exists")
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	289
2144 1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	290 def getEntryPoint(self, profile_name):
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	291 try:
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	292 return self.components[self.profiles[profile_name]]
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	293 except KeyError:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	294 raise exceptions.NotFound("the requested profile doesn't exists or is not a component")
2144 1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	295
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	296 def createProfile(self, name, component=None):
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	297 """Create a new profile
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	298
2144 1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	299 @param name(unicode): name of the profile
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	300 @param component(None, unicode): if not None, must point to a component entry point
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	301 @return: deferred triggered once profile is actually created
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	302 """
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	303
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	304 def getProfileId(ignore):
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	305 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	306
2144 1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	307 def setComponent(profile_id):
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	308 id_ = profile_id[0][0]
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	309 d_comp = self.dbpool.runQuery("INSERT INTO components(profile_id, entry_point) VALUES (?, ?)", (id_, component))
2765 378188abe941 misc: replaced all "dummy" by the more conventional and readable "__" ("_" being used for gettext) Goffi <goffi@goffi.org> parents: 2747 diff changeset	310 d_comp.addCallback(lambda __: profile_id)
2144 1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	311 return d_comp
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	312
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	313 def profile_created(profile_id):
2144 1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	314 id_= profile_id[0][0]
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	315 self.profiles[name] = id_ # we synchronise the cache
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	316
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	317 d = self.dbpool.runQuery("INSERT INTO profiles(name) VALUES (?)", (name, ))
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	318 d.addCallback(getProfileId)
2144 1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	319 if component is not None:
1d3f73e065e1 core, jp: component handling + client handling refactoring: Goffi <goffi@goffi.org> parents: 2143 diff changeset	320 d.addCallback(setComponent)
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	321 d.addCallback(profile_created)
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	322 return d
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	323
420 acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	324 def deleteProfile(self, name):
acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	325 """Delete profile
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	326
420 acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	327 @param name: name of the profile
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	328 @return: deferred triggered once profile is actually deleted
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	329 """
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	330 def deletionError(failure_):
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	331 log.error(_("Can't delete profile [%s]") % name)
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	332 return failure_
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	333
934 34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	334 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	335 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	336 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	337 # 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	338 # 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	339 # 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	340 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	341 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	342 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	343 txn.execute("DELETE FROM private_ind_bin WHERE profile_id = ?", (profile_id,))
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	344 txn.execute("DELETE FROM components 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	345 return None
34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	346
34dd9287dfe5 plugin account: bug fix profile deletion from the database + unsubscribe the contacts souliane <souliane@mailoo.org> parents: 853 diff changeset	347 d = self.dbpool.runInteraction(delete)
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	348 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	349 d.addErrback(deletionError)
420 acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	350 return d
acd908528ef7 core: profile creation/deletion through database Goffi <goffi@goffi.org> parents: 416 diff changeset	351
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	352 ## Params
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	353 def loadGenParams(self, params_gen):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	354 """Load general parameters
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	355
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	356 @param params_gen: dictionary to fill
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	357 @return: deferred
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	358 """
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	359
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	360 def fillParams(result):
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	361 for param in result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	362 category, name, value = param
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	363 params_gen[(category, name)] = value
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	364 log.debug(_("loading general parameters from database"))
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	365 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	366
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	367 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	368 """Load individual parameters
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	369
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	370 @param params_ind: dictionary to fill
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	371 @param profile: a profile which must exist
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	372 @return: deferred
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	373 """
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	374
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	375 def fillParams(result):
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	376 for param in result:
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	377 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	378 params_ind[(category, name)] = value
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	379 log.debug(_("loading individual parameters from database"))
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	380 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	381 d.addCallback(fillParams)
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	382 return d
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	383
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	384 def getIndParam(self, category, name, profile):
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	385 """Ask database for the value of one specific individual parameter
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	386
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	387 @param category: category of the parameter
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	388 @param name: name of the parameter
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	389 @param profile: %(doc_profile)s
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	390 @return: deferred
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	391 """
3120 0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	392 d = self.dbpool.runQuery(
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	393 "SELECT value FROM param_ind WHERE category=? AND name=? AND profile_id=?",
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	394 (category, name, self.profiles[profile]))
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	395 d.addCallback(self.__getFirstResult)
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	396 return d
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	397
3120 0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	398 async def getIndParamValues(self, category, name):
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	399 """Ask database for the individual values of a parameter for all profiles
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	400
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	401 @param category: category of the parameter
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	402 @param name: name of the parameter
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	403 @return dict: profile => value map
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	404 """
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	405 result = await self.dbpool.runQuery(
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	406 "SELECT profiles.name, param_ind.value FROM param_ind JOIN profiles ON "
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	407 "param_ind.profile_id = profiles.id WHERE param_ind.category=? "
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	408 "and param_ind.name=?",
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	409 (category, name))
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	410 return dict(result)
0c29155ac68b core: backend autoconnection: Goffi <goffi@goffi.org> parents: 3079 diff changeset	411
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	412 def setGenParam(self, category, name, value):
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	413 """Save the general parameters in database
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	414
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	415 @param category: category of the parameter
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	416 @param name: name of the parameter
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	417 @param value: value to set
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	418 @return: deferred"""
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	419 d = self.dbpool.runQuery("REPLACE INTO param_gen(category,name,value) VALUES (?,?,?)", (category, name, value))
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	420 d.addErrback(lambda ignore: log.error(_("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	421 return d
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	422
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	423 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	424 """Save the individual parameters in database
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	425
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	426 @param category: category of the parameter
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	427 @param name: name of the parameter
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	428 @param value: value to set
62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	429 @param profile: a profile which must exist
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	430 @return: deferred
c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	431 """
412 62b17854254e database integration: first draft Goffi <goffi@goffi.org> parents: diff changeset	432 d = self.dbpool.runQuery("REPLACE INTO param_ind(category,name,profile_id,value) VALUES (?,?,?,?)", (category, name, self.profiles[profile], value))
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	433 d.addErrback(lambda ignore: log.error(_("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	434 return d
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	435
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	436 ## History
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	437
2765 378188abe941 misc: replaced all "dummy" by the more conventional and readable "__" ("_" being used for gettext) Goffi <goffi@goffi.org> parents: 2747 diff changeset	438 def _addToHistoryCb(self, __, data):
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	439 # Message metadata were successfuly added to history
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	440 # now we can add message and subject
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	441 uid = data['uid']
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	442 d_list = []
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	443 for key in ('message', 'subject'):
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	444 for lang, value in data[key].items():
3191 7255286a298a memory (sqlite): don't store empty messages Goffi <goffi@goffi.org> parents: 3160 diff changeset	445 if not value.strip():
7255286a298a memory (sqlite): don't store empty messages Goffi <goffi@goffi.org> parents: 3160 diff changeset	446 # no need to store empty messages
7255286a298a memory (sqlite): don't store empty messages Goffi <goffi@goffi.org> parents: 3160 diff changeset	447 continue
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	448 d = self.dbpool.runQuery(
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	449 "INSERT INTO {key}(history_uid, {key}, language) VALUES (?,?,?)"
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	450 .format(key=key),
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	451 (uid, value, lang or None))
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	452 d.addErrback(lambda __: log.error(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	453 _("Can't save following {key} in history (uid: {uid}, lang:{lang}):"
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	454 " {value}").format(
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	455 key=key, uid=uid, lang=lang, value=value)))
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	456 d_list.append(d)
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	457 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	458 thread = data['extra']['thread']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	459 except KeyError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	460 pass
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	461 else:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	462 thread_parent = data['extra'].get('thread_parent')
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	463 d = self.dbpool.runQuery(
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	464 "INSERT INTO thread(history_uid, thread_id, parent_id) VALUES (?,?,?)",
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	465 (uid, thread, thread_parent))
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	466 d.addErrback(lambda __: log.error(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	467 _("Can't save following thread in history (uid: {uid}): thread: "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	468 "{thread}), parent:{parent}").format(
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	469 uid=uid, thread=thread, parent=thread_parent)))
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	470 d_list.append(d)
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	471 return defer.DeferredList(d_list)
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	472
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	473 def _addToHistoryEb(self, failure_, data):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	474 failure_.trap(sqlite3.IntegrityError)
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	475 sqlite_msg = failure_.value.args[0]
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	476 if "UNIQUE constraint failed" in sqlite_msg:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	477 log.debug("message {} is already in history, not storing it again"
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	478 .format(data['uid']))
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	479 if 'received_timestamp' not in data:
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	480 log.warning(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	481 "duplicate message is not delayed, this is maybe a bug: data={}"
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	482 .format(data))
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	483 # 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	484 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	485 else:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	486 log.error("Can't store message in history: {}".format(failure_))
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	487
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	488 def _logHistoryError(self, failure_, from_jid, to_jid, data):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	489 if failure_.check(exceptions.CancelError):
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	490 # we propagate CancelError to avoid sending message to frontends
c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	491 raise failure_
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	492 log.error(_(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	493 "Can't save following message in history: from [{from_jid}] to [{to_jid}] "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	494 "(uid: {uid})")
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	495 .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	496
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	497 def addToHistory(self, data, profile):
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	498 """Store a new message in history
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	499
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	500 @param data(dict): message data as build by SatMessageProtocol.onMessage
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	501 """
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	502 extra = pickle.dumps({k: v for k, v in data['extra'].items()
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	503 if k not in NOT_IN_EXTRA}, 0)
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	504 from_jid = data['from']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	505 to_jid = data['to']
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	506 d = self.dbpool.runQuery(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	507 "INSERT INTO history(uid, stanza_id, update_uid, profile_id, source, dest, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	508 "source_res, dest_res, timestamp, received_timestamp, type, extra) VALUES "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	509 "(?,?,?,?,?,?,?,?,?,?,?,?)",
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	510 (data['uid'], data['extra'].get('stanza_id'), data['extra'].get('update_uid'),
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	511 self.profiles[profile], data['from'].userhost(), to_jid.userhost(),
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	512 from_jid.resource, to_jid.resource, data['timestamp'],
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	513 data.get('received_timestamp'), data['type'], sqlite3.Binary(extra)))
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	514 d.addCallbacks(self._addToHistoryCb,
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	515 self._addToHistoryEb,
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	516 callbackArgs=[data],
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	517 errbackArgs=[data])
1961 c73e08094a95 memory (sqlite): better handling of IntegrityError Goffi <goffi@goffi.org> parents: 1955 diff changeset	518 d.addErrback(self._logHistoryError, from_jid, to_jid, data)
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	519 return d
e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	520
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	521 def sqliteHistoryToList(self, query_result):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	522 """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	523 result = []
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	524 current = {'uid': None}
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	525 for row in reversed(query_result):
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	526 (uid, stanza_id, update_uid, source, dest, source_res, dest_res, timestamp,
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	527 received_timestamp, type_, extra, message, message_lang, subject,
860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	528 subject_lang, thread, thread_parent) = row
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	529 if uid != current['uid']:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	530 # new message
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	531 try:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	532 extra = pickle.loads(extra or b"")
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	533 except EOFError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	534 extra = {}
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	535 current = {
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	536 '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	537 '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	538 'uid': uid,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	539 'message': {},
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	540 'subject': {},
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	541 'type': type_,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	542 'extra': extra,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	543 'timestamp': timestamp,
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	544 }
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	545 if stanza_id is not None:
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	546 current['extra']['stanza_id'] = stanza_id
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	547 if update_uid is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	548 current['extra']['update_uid'] = update_uid
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	549 if received_timestamp is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	550 current['extra']['received_timestamp'] = str(received_timestamp)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	551 result.append(current)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	552
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	553 if message is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	554 current['message'][message_lang or ''] = message
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	555
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	556 if subject is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	557 current['subject'][subject_lang or ''] = subject
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	558
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	559 if thread is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	560 current_extra = current['extra']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	561 current_extra['thread'] = thread
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	562 if thread_parent is not None:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	563 current_extra['thread_parent'] = thread_parent
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	564 else:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	565 if thread_parent is not None:
3013 860c550028d6 memory (sqlite): properly wait for messages to be writen in database: Goffi <goffi@goffi.org> parents: 2989 diff changeset	566 log.error(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	567 "Database inconsistency: thread parent without thread (uid: "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	568 "{uid}, thread_parent: {parent})"
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	569 .format(uid=uid, parent=thread_parent))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	570
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	571 return result
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	572
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	573 def listDict2listTuple(self, messages_data):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	574 """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	575 ret = []
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	576 for m in messages_data:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	577 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	578 return ret
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	579
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	580 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	581 """Retrieve messages in history
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	582
1222 e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	583 @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	584 @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	585 @param limit (int): maximum number of messages to get:
e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	586 - 0 for no message (returns the empty list)
e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	587 - None for unlimited
e6e0ea4dc835 memory: add Parameter "Chat history limit" souliane <souliane@mailoo.org> parents: 1221 diff changeset	588 @param between (bool): confound source and dest (ignore the direction)
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	589 @param filters (dict[unicode, unicode]): pattern to filter the history results
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	590 @param profile (unicode): %(doc_profile)s
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	591 @return: list of tuple as in [messageNew]
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	592 """
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	593 assert profile
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	594 if filters is None:
b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	595 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	596 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	597 return defer.succeed([])
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	598
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	599 query_parts = ["SELECT uid, stanza_id, 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	600 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	601 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	602 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	603 LEFT JOIN thread ON history.uid=thread.history_uid\
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	604 WHERE profile_id=?"] # 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	605 values = [self.profiles[profile]]
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	606
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	607 def test_jid(type_, jid_):
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	608 values.append(jid_.userhost())
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	609 if jid_.resource:
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	610 values.append(jid_.resource)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	611 return '({type_}=? AND {type_}_res=?)'.format(type_=type_)
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	612 return '{type_}=?'.format(type_=type_)
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	613
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	614 if not from_jid and not to_jid:
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	615 # not jid specified, we want all one2one communications
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	616 pass
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	617 elif between:
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	618 if not from_jid or not to_jid:
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	619 # we only have one jid specified, we check all messages
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	620 # from or to this jid
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	621 jid_ = from_jid or to_jid
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	622 query_parts.append("AND ({source} OR {dest})".format(
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	623 source=test_jid('source', jid_),
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	624 dest=test_jid('dest' , jid_)))
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	625 else:
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	626 # we have 2 jids specified, we check all communications between
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	627 # those 2 jids
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	628 query_parts.append(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	629 "AND (({source_from} AND {dest_to}) "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	630 "OR ({source_to} AND {dest_from}))".format(
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	631 source_from=test_jid('source', from_jid),
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	632 dest_to=test_jid('dest', to_jid),
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	633 source_to=test_jid('source', to_jid),
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	634 dest_from=test_jid('dest', from_jid)))
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	635 else:
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	636 # we want one communication in specific direction (from somebody or
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	637 # to somebody).
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	638 q = []
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	639 if from_jid is not None:
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	640 q.append(test_jid('source', from_jid))
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	641 if to_jid is not None:
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	642 q.append(test_jid('dest', to_jid))
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	643 query_parts.append("AND " + " AND ".join(q))
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	644
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	645 if filters:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	646 if 'timestamp_start' in filters:
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	647 query_parts.append("AND timestamp>= ?")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	648 values.append(float(filters['timestamp_start']))
3079 f8cc88c773c8 core (memory/sqlite): added `before_uid` filter to retrieve history before a message UID Goffi <goffi@goffi.org> parents: 3028 diff changeset	649 if 'before_uid' in filters:
f8cc88c773c8 core (memory/sqlite): added `before_uid` filter to retrieve history before a message UID Goffi <goffi@goffi.org> parents: 3028 diff changeset	650 query_parts.append("AND history.rowid<(select rowid from history where uid=?)")
f8cc88c773c8 core (memory/sqlite): added `before_uid` filter to retrieve history before a message UID Goffi <goffi@goffi.org> parents: 3028 diff changeset	651 values.append(filters['before_uid'])
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	652 if 'body' in filters:
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	653 # TODO: use REGEXP (function to be defined) instead of GLOB: https://www.sqlite.org/lang_expr.html
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	654 query_parts.append("AND message LIKE ?")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	655 values.append("%{}%".format(filters['body']))
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	656 if 'search' in filters:
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	657 query_parts.append("AND (message LIKE ? OR source_res LIKE ?)")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	658 values.extend(["%{}%".format(filters['search'])] * 2)
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	659 if 'types' in filters:
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	660 types = filters['types'].split()
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	661 query_parts.append("AND type IN ({})".format(','.join("?"*len(types))))
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	662 values.extend(types)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	663 if 'not_types' in filters:
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	664 types = filters['not_types'].split()
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	665 query_parts.append("AND type NOT IN ({})".format(','.join("?"*len(types))))
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	666 values.extend(types)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	667 if 'last_stanza_id' in filters:
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	668 # this request get the last message with a "stanza_id" that we
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	669 # have in history. This is mainly used to retrieve messages sent
310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	670 # while we were offline, using MAM (XEP-0313).
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	671 if (filters['last_stanza_id'] is not True
2699 310e41bd6666 core (memory/sqlite): added stanza_id: Goffi <goffi@goffi.org> parents: 2562 diff changeset	672 or limit != 1):
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	673 raise ValueError("Unexpected values for last_stanza_id filter")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	674 query_parts.append("AND stanza_id IS NOT NULL")
2013 b536dd121da1 backend (memory), frontends: improved history filtering: Goffi <goffi@goffi.org> parents: 1962 diff changeset	675
2715 b35c84ea73cf plugin XEP-0045: MAM implementation for MUC Goffi <goffi@goffi.org> parents: 2711 diff changeset	676
2989 c13333fcde5e memory (sqlite): fixed order for last_stanza_id: Goffi <goffi@goffi.org> parents: 2964 diff changeset	677 # timestamp may be identical for 2 close messages (specially when delay is
c13333fcde5e memory (sqlite): fixed order for last_stanza_id: Goffi <goffi@goffi.org> parents: 2964 diff changeset	678 # used) that's why we order ties by received_timestamp
c13333fcde5e memory (sqlite): fixed order for last_stanza_id: Goffi <goffi@goffi.org> parents: 2964 diff changeset	679 # We'll reverse the order in sqliteHistoryToList
c13333fcde5e memory (sqlite): fixed order for last_stanza_id: Goffi <goffi@goffi.org> parents: 2964 diff changeset	680 # we use DESC here so LIMIT keep the last messages
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	681 query_parts.append("ORDER BY timestamp DESC, history.received_timestamp DESC")
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	682 if limit is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	683 query_parts.append("LIMIT ?")
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	684 values.append(limit)
587 952322b1d490 Remove trailing whitespaces. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 572 diff changeset	685
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	686 d = self.dbpool.runQuery(" ".join(query_parts), values)
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	687 d.addCallback(self.sqliteHistoryToList)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	688 d.addCallback(self.listDict2listTuple)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	689 return d
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	690
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	691 ## Private values
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	692
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	693 def _privateDataEb(self, failure_, operation, namespace, key=None, profile=None):
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	694 """generic errback for data queries"""
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	695 log.error(_("Can't {operation} data in database for namespace {namespace}{and_key}{for_profile}: {msg}").format(
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	696 operation = operation,
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	697 namespace = namespace,
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	698 and_key = (" and key " + key) if key is not None else "",
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	699 for_profile = (' [' + profile + ']') if profile is not None else '',
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	700 msg = failure_))
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	701
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	702 def _load_pickle(self, v):
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	703 # FIXME: workaround for Python 3 port, some pickled data are byte while other are strings
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	704 try:
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	705 return pickle.loads(v)
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	706 except TypeError:
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	707 data = pickle.loads(v.encode('utf-8'))
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	708 log.warning(f"encoding issue in pickled data: {data}")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	709 return data
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	710
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	711 def _generateDataDict(self, query_result, binary):
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	712 if binary:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	713 return {k: self._load_pickle(v) for k,v in query_result}
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	714 else:
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	715 return dict(query_result)
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	716
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	717 def _getPrivateTable(self, binary, profile):
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	718 """Get table to use for private values"""
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	719 table = ['private']
425 e4e9187e3b5b backend, bridge: asynchronous history Goffi <goffi@goffi.org> parents: 423 diff changeset	720
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	721 if profile is None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	722 table.append('gen')
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	723 else:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	724 table.append('ind')
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	725
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	726 if binary:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	727 table.append('bin')
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	728
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	729 return '_'.join(table)
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	730
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	731 def getPrivates(self, namespace, keys=None, binary=False, profile=None):
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	732 """Get private value(s) from databases
592 e5a875a3311b Fix pep8 support in src/memory. Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 588 diff changeset	733
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	734 @param namespace(unicode): namespace of the values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	735 @param keys(iterable, None): keys of the values to get
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	736 None to get all keys/values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	737 @param binary(bool): True to deserialise binary values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	738 @param profile(unicode, None): profile to use for individual values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	739 None to use general values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	740 @return (dict[unicode, object]): gotten keys/values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	741 """
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	742 log.debug(_("getting {type}{binary} private values from database for namespace {namespace}{keys}".format(
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	743 type = "general" if profile is None else "individual",
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	744 binary = " binary" if binary else "",
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	745 namespace = namespace,
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	746 keys = " with keys {}".format(", ".join(keys)) if keys is not None else "")))
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	747 table = self._getPrivateTable(binary, profile)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	748 query_parts = ["SELECT key,value FROM", table, "WHERE namespace=?"]
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	749 args = [namespace]
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	750
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	751 if keys is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	752 placeholders = ','.join(len(keys) * '?')
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	753 query_parts.append('AND key IN (' + placeholders + ')')
2209 ea41cf1e6d29 memory (persistent, sqlite): fixed getPrivates bug with keys arguments and its use in LazyPersistentBinaryDict Goffi <goffi@goffi.org> parents: 2182 diff changeset	754 args.extend(keys)
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	755
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	756 if profile is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	757 query_parts.append('AND profile_id=?')
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	758 args.append(self.profiles[profile])
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	759
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	760 d = self.dbpool.runQuery(" ".join(query_parts), args)
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	761 d.addCallback(self._generateDataDict, binary)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	762 d.addErrback(self._privateDataEb, "get", namespace, 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	763 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	764
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	765 def setPrivateValue(self, namespace, key, value, binary=False, profile=None):
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	766 """Set a private value in database
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	767
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	768 @param namespace(unicode): namespace of the values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	769 @param key(unicode): key of the value to set
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	770 @param value(object): value to set
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	771 @param binary(bool): True if it's a binary values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	772 binary values need to be serialised, used for everything but strings
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	773 @param profile(unicode, None): profile to use for individual value
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	774 if None, it's a general value
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	775 """
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	776 table = self._getPrivateTable(binary, profile)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	777 query_values_names = ['namespace', 'key', 'value']
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	778 query_values = [namespace, key]
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	779
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	780 if binary:
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	781 value = sqlite3.Binary(pickle.dumps(value, 0))
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	782
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	783 query_values.append(value)
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	784
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	785 if profile is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	786 query_values_names.append('profile_id')
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	787 query_values.append(self.profiles[profile])
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	788
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	789 query_parts = ["REPLACE INTO", table, '(', ','.join(query_values_names), ')',
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	790 "VALUES (", ",".join('?'*len(query_values_names)), ')']
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	791
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	792 d = self.dbpool.runQuery(" ".join(query_parts), query_values)
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	793 d.addErrback(self._privateDataEb, "set", namespace, 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	794 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	795
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	796 def delPrivateValue(self, namespace, key, binary=False, profile=None):
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	797 """Delete private value from database
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	798
432 31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	799 @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	800 @param key: key of the private value
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	801 @param binary(bool): True if it's a binary values
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	802 @param profile(unicode, None): profile to use for individual value
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	803 if None, it's a general value
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	804 """
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	805 table = self._getPrivateTable(binary, profile)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	806 query_parts = ["DELETE FROM", table, "WHERE namespace=? AND key=?"]
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	807 args = [namespace, key]
087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	808 if profile is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	809 query_parts.append("AND profile_id=?")
2182 087eec4c6c07 memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict: Goffi <goffi@goffi.org> parents: 2144 diff changeset	810 args.append(self.profiles[profile])
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	811 d = self.dbpool.runQuery(" ".join(query_parts), args)
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	812 d.addErrback(self._privateDataEb, "delete", namespace, 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	813 return d
31e8c48b5f5d core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) Goffi <goffi@goffi.org> parents: 425 diff changeset	814
2735 ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	815 def delPrivateNamespace(self, namespace, binary=False, profile=None):
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	816 """Delete all data from a private namespace
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	817
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	818 Be really cautious when you use this method, as all data with given namespace are
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	819 removed.
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	820 Params are the same as for delPrivateValue
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	821 """
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	822 table = self._getPrivateTable(binary, profile)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	823 query_parts = ["DELETE FROM", table, "WHERE namespace=?"]
2735 ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	824 args = [namespace]
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	825 if profile is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	826 query_parts.append("AND profile_id=?")
2735 ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	827 args.append(self.profiles[profile])
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	828 d = self.dbpool.runQuery(" ".join(query_parts), args)
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	829 d.addErrback(self._privateDataEb, "delete namespace", namespace, profile=profile)
2735 ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	830 return d
ba74914277cf core (memory/sqlite): new delPrivateNamespace method to delete all data of a specific namespace for a profile. Goffi <goffi@goffi.org> parents: 2722 diff changeset	831
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	832 ## Files
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	833
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	834 @defer.inlineCallbacks
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	835 def getFiles(self, client, file_id=None, version='', parent=None, type_=None,
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	836 file_hash=None, hash_algo=None, name=None, namespace=None, mime_type=None,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	837 owner=None, access=None, projection=None, unique=False):
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	838 """retrieve files with with given filters
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	839
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	840 @param file_id(unicode, None): id of the file
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	841 None to ignore
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	842 @param version(unicode, None): version of the file
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	843 None to ignore
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	844 empty string to look for current version
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	845 @param parent(unicode, None): id of the directory containing the files
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	846 None to ignore
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	847 empty string to look for root files/directories
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	848 @param projection(list[unicode], None): name of columns to retrieve
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	849 None to retrieve all
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	850 @param unique(bool): if True will remove duplicates
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	851 other params are the same as for [setFile]
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	852 @return (list[dict]): files corresponding to filters
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	853 """
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	854 query_parts = ["SELECT"]
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	855 if unique:
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	856 query_parts.append('DISTINCT')
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	857 if projection is None:
2507 4c45df43ea44 core (memory/sqlite): renamed column for files from hash to file_hash: Goffi <goffi@goffi.org> parents: 2500 diff changeset	858 projection = ['id', 'version', 'parent', 'type', 'file_hash', 'hash_algo', 'name',
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	859 'size', 'namespace', 'mime_type', 'created', 'modified', 'owner',
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	860 'access', 'extra']
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	861 query_parts.append(','.join(projection))
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	862 query_parts.append("FROM files WHERE")
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	863 filters = ['profile_id=?']
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	864 args = [self.profiles[client.profile]]
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	865
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	866 if file_id is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	867 filters.append('id=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	868 args.append(file_id)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	869 if version is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	870 filters.append('version=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	871 args.append(version)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	872 if parent is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	873 filters.append('parent=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	874 args.append(parent)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	875 if type_ is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	876 filters.append('type=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	877 args.append(type_)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	878 if file_hash is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	879 filters.append('file_hash=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	880 args.append(file_hash)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	881 if hash_algo is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	882 filters.append('hash_algo=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	883 args.append(hash_algo)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	884 if name is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	885 filters.append('name=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	886 args.append(name)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	887 if namespace is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	888 filters.append('namespace=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	889 args.append(namespace)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	890 if mime_type is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	891 filters.append('mime_type=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	892 args.append(mime_type)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	893 if owner is not None:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	894 filters.append('owner=?')
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	895 args.append(owner.full())
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	896 if access is not None:
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	897 raise NotImplementedError('Access check is not implemented yet')
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	898 # a JSON comparison is needed here
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	899
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	900 filters = ' AND '.join(filters)
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	901 query_parts.append(filters)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	902 query = ' '.join(query_parts)
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	903
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	904 result = yield self.dbpool.runQuery(query, args)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	905 files_data = [dict(list(zip(projection, row))) for row in result]
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	906 to_parse = {'access', 'extra'}.intersection(projection)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	907 to_filter = {'owner'}.intersection(projection)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	908 if to_parse or to_filter:
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	909 for file_data in files_data:
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	910 for key in to_parse:
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	911 value = file_data[key]
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	912 file_data[key] = {} if value is None else json.loads(value)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	913 owner = file_data.get('owner')
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	914 if owner is not None:
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	915 file_data['owner'] = jid.JID(owner)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	916 defer.returnValue(files_data)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	917
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	918 def setFile(self, client, name, file_id, version='', parent=None, type_=C.FILE_TYPE_FILE,
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	919 file_hash=None, hash_algo=None, size=None, namespace=None, mime_type=None,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	920 created=None, modified=None, owner=None, access=None, extra=None):
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	921 """set a file metadata
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	922
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	923 @param client(SatXMPPClient): client owning the file
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	924 @param name(unicode): name of the file (must not contain "/")
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	925 @param file_id(unicode): unique id of the file
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	926 @param version(unicode): version of this file
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	927 @param parent(unicode): id of the directory containing this file
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	928 None if it is a root file/directory
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	929 @param type_(unicode): one of:
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	930 - file
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	931 - directory
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	932 @param file_hash(unicode): unique hash of the payload
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	933 @param hash_algo(unicode): algorithm used for hashing the file (usually sha-256)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	934 @param size(int): size in bytes
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	935 @param namespace(unicode, None): identifier (human readable is better) to group files
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	936 for instance, namespace could be used to group files in a specific photo album
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	937 @param mime_type(unicode): MIME type of the file, or None if not known/guessed
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	938 @param created(int): UNIX time of creation
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	939 @param modified(int,None): UNIX time of last modification, or None to use created date
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	940 @param owner(jid.JID, None): jid of the owner of the file (mainly useful for component)
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	941 @param access(dict, None): serialisable dictionary with access rules. See [memory.memory] for details
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	942 @param extra(dict, None): serialisable dictionary of any extra data
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	943 will be encoded to json in database
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	944 """
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	945 if extra is not None:
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	946 assert isinstance(extra, dict)
2507 4c45df43ea44 core (memory/sqlite): renamed column for files from hash to file_hash: Goffi <goffi@goffi.org> parents: 2500 diff changeset	947 query = ('INSERT INTO files(id, version, parent, type, file_hash, hash_algo, name, size, namespace, '
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	948 'mime_type, created, modified, owner, access, extra, profile_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	949 d = self.dbpool.runQuery(query, (file_id, version.strip(), parent, type_,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	950 file_hash, hash_algo,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	951 name, size, namespace,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	952 mime_type, created, modified,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	953 owner.full() if owner is not None else None,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	954 json.dumps(access) if access else None,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	955 json.dumps(extra) if extra else None,
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	956 self.profiles[client.profile]))
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	957 d.addErrback(lambda failure: log.error(_("Can't save file metadata for [{profile}]: {reason}".format(profile=client.profile, reason=failure))))
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	958 return d
898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	959
2526 35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	960 def _fileUpdate(self, cursor, file_id, column, update_cb):
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	961 query = 'SELECT {column} FROM files where id=?'.format(column=column)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	962 for i in range(5):
2526 35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	963 cursor.execute(query, [file_id])
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	964 try:
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	965 older_value_raw = cursor.fetchone()[0]
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	966 except TypeError:
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	967 raise exceptions.NotFound
2918 21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	968 if older_value_raw is None:
21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	969 value = {}
21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	970 else:
21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	971 value = json.loads(older_value_raw)
2526 35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	972 update_cb(value)
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	973 value_raw = json.dumps(value)
2918 21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	974 if older_value_raw is None:
21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	975 update_query = 'UPDATE files SET {column}=? WHERE id=? AND {column} is NULL'.format(column=column)
21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	976 update_args = (value_raw, file_id)
21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	977 else:
21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	978 update_query = 'UPDATE files SET {column}=? WHERE id=? AND {column}=?'.format(column=column)
21cf8395616c memory (sqlite): fixed fileUpdate when original value is not set (NULL) Goffi <goffi@goffi.org> parents: 2895 diff changeset	979 update_args = (value_raw, file_id, older_value_raw)
2526 35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	980 try:
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	981 cursor.execute(update_query, update_args)
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	982 except sqlite3.Error:
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	983 pass
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	984 else:
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	985 if cursor.rowcount == 1:
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	986 break;
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	987 log.warning(_("table not updated, probably due to race condition, trying again ({tries})").format(tries=i+1))
2526 35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	988 else:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	989 log.error(_("Can't update file table"))
2526 35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	990
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	991 def fileUpdate(self, file_id, column, update_cb):
2928 c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	992 """Update a column value using a method to avoid race conditions
2526 35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	993
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	994 the older value will be retrieved from database, then update_cb will be applied
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	995 to update it, and file will be updated checking that older value has not been changed meanwhile
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	996 by an other user. If it has changed, it tries again a couple of times before failing
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	997 @param column(str): column name (only "access" or "extra" are allowed)
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	998 @param update_cb(callable): method to update the value of the colum
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	999 the method will take older value as argument, and must update it in place
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1000 update_cb must not care about serialization,
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1001 it get the deserialized data (i.e. a Python object) directly
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1002 Note that the callable must be thread-safe
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1003 @raise exceptions.NotFound: there is not file with this id
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1004 """
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1005 if column not in ('access', 'extra'):
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1006 raise exceptions.InternalError('bad column name')
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1007 return self.dbpool.runInteraction(self._fileUpdate, file_id, column, update_cb)
35d591086974 core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition Goffi <goffi@goffi.org> parents: 2507 diff changeset	1008
2928 c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1009 def fileDelete(self, file_id):
c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1010 """Delete file metadata from the database
c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1011
c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1012 @param file_id(unicode): id of the file to delete
c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1013 NOTE: file itself must still be removed, this method only handle metadata in
c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1014 database
c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1015 """
c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1016 return self.dbpool.runQuery("DELETE FROM files WHERE id = ?", (file_id,))
c0f6fd75af5f core (memory, memory/sqlite): implemented fileDelete Goffi <goffi@goffi.org> parents: 2919 diff changeset	1017
413 dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	1018 ##Helper methods##
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	1019
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	1020 def __getFirstResult(self, result):
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	1021 """Return the first result of a database query
dd4caab17008 core: - individual parameters managed through sqlite Goffi <goffi@goffi.org> parents: 412 diff changeset	1022 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	1023 return None if not result else result[0][0]
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1024
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1025
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1026 class Updater(object):
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	1027 stmnt_regex = re.compile(r"[\w/' ]+(?:$.?$)?[^,]")
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1028 clean_regex = re.compile(r"^ +\|(?<= ) +\|(?<=,) +\| +$")
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1029 CREATE_SQL = "CREATE TABLE %s (%s)"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1030 INSERT_SQL = "INSERT INTO %s VALUES (%s)"
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1031 INDEX_SQL = "CREATE INDEX %s ON %s(%s)"
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1032 DROP_SQL = "DROP TABLE %s"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1033 ALTER_SQL = "ALTER TABLE %s ADD COLUMN %s"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1034 RENAME_TABLE_SQL = "ALTER TABLE %s RENAME TO %s"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1035
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1036 CONSTRAINTS = ('PRIMARY', 'UNIQUE', 'CHECK', 'FOREIGN')
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1037 TMP_TABLE = "tmp_sat_update"
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1038
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1039 def __init__(self, sqlite_storage, sat_version):
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1040 self._sat_version = sat_version
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1041 self.sqlite_storage = sqlite_storage
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1042
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1043 @property
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1044 def dbpool(self):
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1045 return self.sqlite_storage.dbpool
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1046
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1047 def getLocalVersion(self):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1048 """ Get local database version
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	1049
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1050 @return: version (int)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1051 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1052 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	1053
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1054 def _setLocalVersion(self, version):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1055 """ Set local database version
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	1056
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1057 @param version: version (int)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1058 @return: deferred
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1059 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1060 return self.dbpool.runOperation("PRAGMA user_version=%d" % version)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1061
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1062 def getLocalSchema(self):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1063 """ return raw local schema
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	1064
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1065 @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	1066 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1067 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	1068 d.addCallback(lambda result: [row[0] for row in result])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1069 return d
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1070
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1071 @defer.inlineCallbacks
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1072 def checkUpdates(self):
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1073 """ Check if a database schema/content update is needed, according to DATABASE_SCHEMAS
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	1074
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1075 @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	1076 """
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1077 # TODO: only "table" type (i.e. "CREATE" statements) is checked,
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1078 # "index" should be checked too.
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1079 # This may be not relevant is we move to a higher level library (alchimia?)
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1080 local_version = yield self.getLocalVersion()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1081 raw_local_sch = yield self.getLocalSchema()
2500 898b6e1fdc7a memory (sqlite): files handling: Goffi <goffi@goffi.org> parents: 2483 diff changeset	1082
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1083 local_sch = self.rawStatements2data(raw_local_sch)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1084 current_sch = DATABASE_SCHEMAS['current']['CREATE']
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1085 local_hash = self.statementHash(local_sch)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1086 current_hash = self.statementHash(current_sch)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1087
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1088 # 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	1089 force_update = local_hash == current_hash and local_version < CURRENT_DB_VERSION \
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1090 and {'index', 'specific'}.intersection(DATABASE_SCHEMAS[CURRENT_DB_VERSION])
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1091
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1092 if local_hash == current_hash and not force_update:
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1093 if local_version != CURRENT_DB_VERSION:
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	1094 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	1095 yield self._setLocalVersion(CURRENT_DB_VERSION)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1096 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1097 # an update is needed
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1098
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1099 if local_version == CURRENT_DB_VERSION:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1100 # Database mismatch and we have the latest version
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1101 if self._sat_version.endswith('D'):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1102 # we are in a development version
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1103 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	1104 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	1105 update_raw = yield self.update2raw(update_data, True)
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1106 defer.returnValue(update_raw)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1107 else:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1108 log.error(_("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	1109 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	1110 update_raw = yield self.update2raw(update_data)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1111 log.warning(_("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") % '\n'.join("%s;" % statement for statement in update_raw))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1112 raise exceptions.DatabaseError("Database mismatch")
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1113 else:
2964 17c61d09a85b memory (sqlite): exit with an error if current db version is higher than version expected in current code Goffi <goffi@goffi.org> parents: 2928 diff changeset	1114 if local_version > CURRENT_DB_VERSION:
17c61d09a85b memory (sqlite): exit with an error if current db version is higher than version expected in current code Goffi <goffi@goffi.org> parents: 2928 diff changeset	1115 log.error(_(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1116 "You database version is higher than the one used in this SàT "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1117 "version, are you using several version at the same time? We "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1118 "can't run SàT with this database."))
2964 17c61d09a85b memory (sqlite): exit with an error if current db version is higher than version expected in current code Goffi <goffi@goffi.org> parents: 2928 diff changeset	1119 sys.exit(1)
17c61d09a85b memory (sqlite): exit with an error if current db version is higher than version expected in current code Goffi <goffi@goffi.org> parents: 2928 diff changeset	1120
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1121 # 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	1122 if force_update:
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1123 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	1124 else:
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1125 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	1126 update_raw = []
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1127 for version in range(local_version + 1, CURRENT_DB_VERSION + 1):
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1128 try:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1129 update_data = DATABASE_SCHEMAS[version]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1130 except KeyError:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1131 raise exceptions.InternalError("Missing update definition (version %d)" % version)
2720 453a12ff6f51 core (memory/sqlite): do not call commitStatements if there is nothing to commit in checkUpdates Goffi <goffi@goffi.org> parents: 2716 diff changeset	1132 if "specific" in update_data and update_raw:
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1133 # if we have a specific, we must commit current statements
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1134 # because a specific may modify database itself, and the database
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1135 # must be in the expected state of the previous version.
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1136 yield self.sqlite_storage.commitStatements(update_raw)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1137 del update_raw[:]
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1138 update_raw_step = yield self.update2raw(update_data)
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1139 if update_raw_step is not None:
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1140 # can be None with specifics
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1141 update_raw.extend(update_raw_step)
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1142 update_raw.append("PRAGMA user_version=%d" % CURRENT_DB_VERSION)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1143 defer.returnValue(update_raw)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1144
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1145 @staticmethod
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1146 def createData2Raw(data):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1147 """ Generate SQL statements from statements data
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	1148
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1149 @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	1150 @return: list of strings with raw statements
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1151 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1152 ret = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1153 for table in data:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1154 defs, constraints = data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1155 assert isinstance(defs, tuple)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1156 assert isinstance(constraints, tuple)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1157 ret.append(Updater.CREATE_SQL % (table, ', '.join(defs + constraints)))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1158 return ret
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1159
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1160 @staticmethod
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1161 def insertData2Raw(data):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1162 """ Generate SQL statements from statements data
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	1163
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1164 @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	1165 @return: list of strings with raw statements
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1166 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1167 ret = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1168 for table in data:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1169 values_tuple = data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1170 assert isinstance(values_tuple, tuple)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1171 for values in values_tuple:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1172 assert isinstance(values, tuple)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1173 ret.append(Updater.INSERT_SQL % (table, ', '.join(values)))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1174 return ret
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1175
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1176 @staticmethod
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1177 def indexData2Raw(data):
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1178 """ Generate SQL statements from statements data
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1179
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1180 @param data: dictionary with table as key, and statements data in tuples as value
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1181 @return: list of strings with raw statements
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1182 """
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1183 ret = []
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1184 assert isinstance(data, tuple)
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1185 for table, col_data in data:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1186 assert isinstance(table, str)
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1187 assert isinstance(col_data, tuple)
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1188 for cols in col_data:
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1189 if isinstance(cols, tuple):
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1190 assert all([isinstance(c, str) for c in cols])
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1191 indexed_cols = ','.join(cols)
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1192 elif isinstance(cols, str):
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1193 indexed_cols = cols
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1194 else:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1195 raise exceptions.InternalError("unexpected index columns value")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1196 index_name = table + '__' + indexed_cols.replace(',', '_')
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1197 ret.append(Updater.INDEX_SQL % (index_name, table, indexed_cols))
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1198 return ret
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1199
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1200 def statementHash(self, data):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1201 """ Generate hash of template data
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	1202
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1203 useful to compare schemas
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1204 @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	1205 and tuples of (col_defs, constraints) as values
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1206 @return: hash as string
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1207 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1208 hash_ = hashlib.sha1()
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1209 tables = list(data.keys())
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1210 tables.sort()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1211
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1212 def stmnts2str(stmts):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1213 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	1214
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1215 for table in tables:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1216 col_defs, col_constr = data[table]
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1217 hash_.update(
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1218 ("%s:%s:%s" % (table, stmnts2str(col_defs), stmnts2str(col_constr)))
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1219 .encode('utf-8'))
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1220 return hash_.digest()
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1221
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1222 def rawStatements2data(self, raw_statements):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1223 """ separate "CREATE" statements into dictionary/tuples data
2143 c3cac21157d4 memory (sqlite): introduced component table, schema updated: Goffi <goffi@goffi.org> parents: 2013 diff changeset	1224
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1225 @param raw_statements: list of CREATE statements as strings
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1226 @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	1227 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1228 schema_dict = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1229 for create_statement in raw_statements:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1230 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	1231 log.warning("Unexpected statement, ignoring it")
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1232 continue
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1233 _create_statement = create_statement[13:]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1234 table, raw_col_stats = _create_statement.split(' ',1)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1235 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	1236 log.warning("Unexpected statement structure, ignoring it")
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1237 continue
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1238 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	1239 col_defs = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1240 constraints = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1241 for col_stat in col_stats:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1242 name = col_stat.split(' ',1)[0]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1243 if name in self.CONSTRAINTS:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1244 constraints.append(col_stat)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1245 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1246 col_defs.append(col_stat)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1247 schema_dict[table] = (tuple(col_defs), tuple(constraints))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1248 return schema_dict
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1249
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1250 def generateUpdateData(self, old_data, new_data, modify=False):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1251 """ 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	1252
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1253 @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	1254 @param new_data: data of the current schema
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1255 @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	1256 @return: update data, a dictionary with:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1257 - 'create': dictionary of tables to create
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1258 - 'delete': tuple of tables to delete
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1259 - '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	1260 - '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	1261 - '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	1262 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1263
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1264 create_tables_data = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1265 create_cols_data = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1266 modify_cols_data = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1267 delete_cols_data = {}
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1268 old_tables = set(old_data.keys())
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1269 new_tables = set(new_data.keys())
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1270
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1271 def getChanges(set_olds, set_news):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1272 to_create = set_news.difference(set_olds)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1273 to_delete = set_olds.difference(set_news)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1274 to_check = set_news.intersection(set_olds)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1275 return tuple(to_create), tuple(to_delete), tuple(to_check)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1276
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1277 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	1278
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1279 for table in tables_to_create:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1280 create_tables_data[table] = new_data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1281
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1282 for table in tables_to_check:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1283 old_col_defs, old_constraints = old_data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1284 new_col_defs, new_constraints = new_data[table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1285 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	1286 if not isinstance(obj, tuple):
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	1287 raise exceptions.InternalError("Columns definitions must be tuples")
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1288 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	1289 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	1290 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	1291 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	1292 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	1293 (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	1294 # we have modified columns, we need to transfer table
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1295 # 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	1296 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	1297 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	1298 modify_cols_data[table] = tuple(old_names.intersection(new_names));
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1299 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1300 if defs_create:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1301 create_cols_data[table] = (defs_create)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1302 if defs_delete or constraints_delete:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1303 delete_cols_data[table] = (defs_delete)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1304
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1305 return {'create': create_tables_data,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1306 'delete': tables_to_delete,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1307 'cols create': create_cols_data,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1308 'cols delete': delete_cols_data,
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1309 'cols modify': modify_cols_data
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1310 }
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1311
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1312 @defer.inlineCallbacks
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1313 def update2raw(self, update, dev_version=False):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1314 """ 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	1315
1455 4fb3280c4568 tmp (wokkel.rsm): use of super instead of direct call in PubSubRequest Goffi <goffi@goffi.org> parents: 1409 diff changeset	1316 @param update: update data as returned by generateUpdateData
853 c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1317 @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	1318 @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	1319 """
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1320 ret = self.createData2Raw(update.get('create', {}))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1321 drop = []
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1322 for table in update.get('delete', tuple()):
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1323 drop.append(self.DROP_SQL % table)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1324 if dev_version:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1325 if drop:
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	1326 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	1327 else:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1328 ret.extend(drop)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1329
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1330 cols_create = update.get('cols create', {})
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1331 for table in cols_create:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1332 for col_def in cols_create[table]:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1333 ret.append(self.ALTER_SQL % (table, col_def))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1334
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1335 cols_delete = update.get('cols delete', {})
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1336 for table in cols_delete:
993 301b342c697a core: use of the new core.log module: Goffi <goffi@goffi.org> parents: 936 diff changeset	1337 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	1338
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1339 cols_modify = update.get('cols modify', {})
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1340 for table in cols_modify:
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1341 ret.append(self.RENAME_TABLE_SQL % (table, self.TMP_TABLE))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1342 main, extra = DATABASE_SCHEMAS['current']['CREATE'][table]
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1343 ret.append(self.CREATE_SQL % (table, ', '.join(main + extra)))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1344 common_cols = ', '.join(cols_modify[table])
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1345 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	1346 ret.append(self.DROP_SQL % self.TMP_TABLE)
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1347
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1348 insert = update.get('insert', {})
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1349 ret.extend(self.insertData2Raw(insert))
c2f6ada7858f core (sqlite): automatic database update: Goffi <goffi@goffi.org> parents: 839 diff changeset	1350
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1351 index = update.get('index', tuple())
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1352 ret.extend(self.indexData2Raw(index))
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1353
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1354 specific = update.get('specific', None)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1355 if specific:
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1356 cmds = yield getattr(self, specific)()
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1357 ret.extend(cmds or [])
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1358 defer.returnValue(ret)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1359
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1360 def update_v8(self):
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1361 """Update database from v7 to v8 (primary keys order changes + indexes)"""
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1362 log.info("Database update to v8")
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1363 statements = ["PRAGMA foreign_keys = OFF"]
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1364
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1365 # here is a copy of create and index data, we can't use "current" table
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1366 # because it may change in a future version, which would break the update
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1367 # when doing v8
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1368 create = {
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1369 'param_gen': (
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1370 ("category TEXT", "name TEXT", "value TEXT"),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1371 ("PRIMARY KEY (category, name)",)),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1372 'param_ind': (
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1373 ("category TEXT", "name TEXT", "profile_id INTEGER", "value TEXT"),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1374 ("PRIMARY KEY (profile_id, category, name)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE")),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1375 'private_ind': (
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1376 ("namespace TEXT", "key TEXT", "profile_id INTEGER", "value TEXT"),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1377 ("PRIMARY KEY (profile_id, namespace, key)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE")),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1378 'private_ind_bin': (
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1379 ("namespace TEXT", "key TEXT", "profile_id INTEGER", "value BLOB"),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1380 ("PRIMARY KEY (profile_id, namespace, key)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE")),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1381 }
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1382 index = (
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1383 ('history', (('profile_id', 'timestamp'),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1384 ('profile_id', 'received_timestamp'))),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1385 ('message', ('history_uid',)),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1386 ('subject', ('history_uid',)),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1387 ('thread', ('history_uid',)),
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1388 ('files', ('profile_id', 'mime_type', 'owner', 'parent')))
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1389
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1390 for table in ('param_gen', 'param_ind', 'private_ind', 'private_ind_bin'):
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1391 statements.append("ALTER TABLE {0} RENAME TO {0}_old".format(table))
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1392 schema = {table: create[table]}
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1393 cols = [d.split()[0] for d in schema[table][0]]
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1394 statements.extend(Updater.createData2Raw(schema))
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1395 statements.append("INSERT INTO {table}({cols}) "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1396 "SELECT {cols} FROM {table}_old".format(
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1397 table=table,
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1398 cols=','.join(cols)))
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1399 statements.append("DROP TABLE {}_old".format(table))
2787 298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1400
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1401 statements.extend(Updater.indexData2Raw(index))
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1402 statements.append("PRAGMA foreign_keys = ON")
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1403 return statements
298408833ec2 memory (sqlite): optimizations Goffi <goffi@goffi.org> parents: 2771 diff changeset	1404
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1405 @defer.inlineCallbacks
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1406 def update_v7(self):
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1407 """Update database from v6 to v7 (history unique constraint change)"""
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1408 log.info("Database update to v7, this may be long depending on your history "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1409 "size, please be patient.")
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1410
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1411 log.info("Some cleaning first")
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1412 # we need to fix duplicate stanza_id, as it can result in conflicts with the new schema
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1413 # normally database should not contain any, but better safe than sorry.
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1414 rows = yield self.dbpool.runQuery(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1415 "SELECT stanza_id, COUNT(*) as c FROM history WHERE stanza_id is not NULL "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1416 "GROUP BY stanza_id HAVING c>1")
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1417 if rows:
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1418 count = sum([r[1] for r in rows]) - len(rows)
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1419 log.info("{count} duplicate stanzas found, cleaning".format(count=count))
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1420 for stanza_id, count in rows:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1421 log.info("cleaning duplicate stanza {stanza_id}".format(stanza_id=stanza_id))
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1422 row_uids = yield self.dbpool.runQuery(
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1423 "SELECT uid FROM history WHERE stanza_id = ? LIMIT ?",
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1424 (stanza_id, count-1))
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1425 uids = [r[0] for r in row_uids]
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1426 yield self.dbpool.runQuery(
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1427 "DELETE FROM history WHERE uid IN ({})".format(",".join("?"*len(uids))),
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1428 uids)
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1429
2722 14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1430 def deleteInfo(txn):
14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1431 # with foreign_keys on, the delete takes ages, so we deactivate it here
14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1432 # the time to delete info messages from history.
14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1433 txn.execute("PRAGMA foreign_keys = OFF")
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1434 txn.execute("DELETE FROM message WHERE history_uid IN (SELECT uid FROM history WHERE "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1435 "type='info')")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1436 txn.execute("DELETE FROM subject WHERE history_uid IN (SELECT uid FROM history WHERE "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1437 "type='info')")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1438 txn.execute("DELETE FROM thread WHERE history_uid IN (SELECT uid FROM history WHERE "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1439 "type='info')")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1440 txn.execute("DELETE FROM message WHERE history_uid IN (SELECT uid FROM history WHERE "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1441 "type='info')")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1442 txn.execute("DELETE FROM history WHERE type='info'")
2722 14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1443 # not sure that is is necessary to reactivate here, but in doubt…
14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1444 txn.execute("PRAGMA foreign_keys = ON")
14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1445
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1446 log.info('Deleting "info" messages (this can take a while)')
2722 14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1447 yield self.dbpool.runInteraction(deleteInfo)
14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1448
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1449 log.info("Cleaning done")
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1450
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1451 # we have to rename table we will replace
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1452 # tables referencing history need to be replaced to, else reference would
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1453 # be to the old table (which will be dropped at the end). This buggy behaviour
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1454 # seems to be fixed in new version of Sqlite
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1455 yield self.dbpool.runQuery("ALTER TABLE history RENAME TO history_old")
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1456 yield self.dbpool.runQuery("ALTER TABLE message RENAME TO message_old")
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1457 yield self.dbpool.runQuery("ALTER TABLE subject RENAME TO subject_old")
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1458 yield self.dbpool.runQuery("ALTER TABLE thread RENAME TO thread_old")
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1459
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1460 # history
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1461 query = ("CREATE TABLE history (uid TEXT PRIMARY KEY, stanza_id TEXT, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1462 "update_uid TEXT, profile_id INTEGER, source TEXT, dest TEXT, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1463 "source_res TEXT, dest_res TEXT, timestamp DATETIME NOT NULL, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1464 "received_timestamp DATETIME, type TEXT, extra BLOB, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1465 "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1466 "FOREIGN KEY(type) REFERENCES message_types(type), "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1467 "UNIQUE (profile_id, stanza_id, source, dest))")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1468 yield self.dbpool.runQuery(query)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1469
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1470 # message
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1471 query = ("CREATE TABLE message (id INTEGER PRIMARY KEY ASC, history_uid INTEGER"
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1472 ", message TEXT, language TEXT, FOREIGN KEY(history_uid) REFERENCES "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1473 "history(uid) ON DELETE CASCADE)")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1474 yield self.dbpool.runQuery(query)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1475
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1476 # subject
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1477 query = ("CREATE TABLE subject (id INTEGER PRIMARY KEY ASC, history_uid INTEGER"
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1478 ", subject TEXT, language TEXT, FOREIGN KEY(history_uid) REFERENCES "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1479 "history(uid) ON DELETE CASCADE)")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1480 yield self.dbpool.runQuery(query)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1481
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1482 # thread
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1483 query = ("CREATE TABLE thread (id INTEGER PRIMARY KEY ASC, history_uid INTEGER"
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1484 ", thread_id TEXT, parent_id TEXT, FOREIGN KEY(history_uid) REFERENCES "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1485 "history(uid) ON DELETE CASCADE)")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1486 yield self.dbpool.runQuery(query)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1487
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1488 log.info("Now transfering old data to new tables, please be patient.")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1489
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1490 log.info("\nTransfering table history")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1491 query = ("INSERT INTO history (uid, stanza_id, update_uid, profile_id, source, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1492 "dest, source_res, dest_res, timestamp, received_timestamp, type, extra"
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1493 ") SELECT uid, stanza_id, update_uid, profile_id, source, dest, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1494 "source_res, dest_res, timestamp, received_timestamp, type, extra "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1495 "FROM history_old")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1496 yield self.dbpool.runQuery(query)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1497
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1498 log.info("\nTransfering table message")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1499 query = ("INSERT INTO message (id, history_uid, message, language) SELECT id, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1500 "history_uid, message, language FROM message_old")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1501 yield self.dbpool.runQuery(query)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1502
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1503 log.info("\nTransfering table subject")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1504 query = ("INSERT INTO subject (id, history_uid, subject, language) SELECT id, "
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1505 "history_uid, subject, language FROM subject_old")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1506 yield self.dbpool.runQuery(query)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1507
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1508 log.info("\nTransfering table thread")
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1509 query = ("INSERT INTO thread (id, history_uid, thread_id, parent_id) SELECT id"
ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1510 ", history_uid, thread_id, parent_id FROM thread_old")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1511 yield self.dbpool.runQuery(query)
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1512
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1513 log.info("\nRemoving old tables")
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1514 # because of foreign keys, tables referencing history_old
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1515 # must be deleted first
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1516 yield self.dbpool.runQuery("DROP TABLE thread_old")
4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1517 yield self.dbpool.runQuery("DROP TABLE subject_old")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1518 yield self.dbpool.runQuery("DROP TABLE message_old")
2721 4aaa47f62d8d core (memory/sqlite): fixed v7 update performance issue: Goffi <goffi@goffi.org> parents: 2720 diff changeset	1519 yield self.dbpool.runQuery("DROP TABLE history_old")
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1520 log.info("\nReducing database size (this can take a while)")
2722 14e1db0c1383 core (memory/sqlite): further improvments in update_v7: Goffi <goffi@goffi.org> parents: 2721 diff changeset	1521 yield self.dbpool.runQuery("VACUUM")
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1522 log.info("Database update done :)")
2716 06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1523
06160b529da6 core (memory/sqlite): changed history constraint Goffi <goffi@goffi.org> parents: 2715 diff changeset	1524 @defer.inlineCallbacks
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1525 def update_v3(self):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1526 """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	1527 # 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	1528 # 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	1529 # 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	1530 # 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	1531 # messages
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1532 log.info("Database update to v3, this may take a while")
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1533
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1534 # 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	1535 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	1536 if rows:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1537 log.info("fixing duplicate timestamp")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1538 fixed = []
2765 378188abe941 misc: replaced all "dummy" by the more conventional and readable "__" ("_" being used for gettext) Goffi <goffi@goffi.org> parents: 2747 diff changeset	1539 for timestamp, __ in rows:
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1540 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	1541 for idx, (id_,) in enumerate(ids_rows):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1542 fixed.append(id_)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1543 yield self.dbpool.runQuery("UPDATE history SET timestamp=? WHERE id=?", (float(timestamp) + idx * 0.001, id_))
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1544 log.info("fixed messages with ids {}".format(', '.join([str(id_) for id_ in fixed])))
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1545
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1546 def historySchema(txn):
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1547 log.info("History schema update")
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1548 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	1549 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	1550 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	1551
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1552 yield self.dbpool.runInteraction(historySchema)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1553
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1554 def newTables(txn):
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1555 log.info("Creating new tables")
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1556 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	1557 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	1558 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	1559
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1560 yield self.dbpool.runInteraction(newTables)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1561
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1562 log.info("inserting new message type")
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1563 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	1564
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1565 log.info("messages update")
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1566 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	1567 total = len(rows)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1568
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1569 def updateHistory(txn, queries):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1570 for query, args in iter(queries):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1571 txn.execute(query, args)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1572
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1573 queries = []
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1574 for idx, row in enumerate(rows, 1):
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1575 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	1576 log.info("preparing message {}/{}".format(idx, total))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1577 id_, timestamp, message, extra = row
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1578 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1579 extra = pickle.loads(str(extra or ""))
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1580 except EOFError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1581 extra = {}
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1582 except Exception:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1583 log.warning("Can't handle extra data for message id {}, ignoring it".format(id_))
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1584 extra = {}
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1585
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1586 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	1587
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1588 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1589 subject = extra.pop('subject')
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1590 except KeyError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1591 pass
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1592 else:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1593 try:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1594 subject = subject
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1595 except UnicodeEncodeError:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1596 log.warning("Error while decoding subject, ignoring it")
1955 633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1597 del extra['subject']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1598 else:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1599 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	1600
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1601 received_timestamp = extra.pop('timestamp', None)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1602 try:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1603 del extra['archive']
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1604 except KeyError:
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1605 # archive was not used
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1606 pass
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1607
1962 a45235d8dc93 memory (sqlite): fixed handling of extra (pickled data) by using sqlite3.Binary Goffi <goffi@goffi.org> parents: 1961 diff changeset	1608 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	1609
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1610 yield self.dbpool.runInteraction(updateHistory, queries)
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1611
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1612 log.info("Dropping temporary table")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1613 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	1614 log.info("Database update finished :)")
633b5c21aefd backend, frontend: messages refactoring (huge commit, not finished): Goffi <goffi@goffi.org> parents: 1934 diff changeset	1615
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1616 def update2raw_v2(self):
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1617 """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	1618
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1619 - 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	1620 - the profile password is stored hashed
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1621 - 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	1622 - 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	1623 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	1624 """
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1625 xmpp_pass_path = ('Connection', 'Password')
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1626
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1627 def encrypt_values(values):
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1628 ret = []
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1629 list_ = []
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1630
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1631 def prepare_queries(result, xmpp_password):
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1632 try:
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1633 id_ = result[0][0]
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1634 except IndexError:
3028 ab2696e34d29 Python 3 port: Goffi <goffi@goffi.org> parents: 3013 diff changeset	1635 log.error("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	1636 return defer.succeed(None)
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1637
9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1638 sat_password = xmpp_password
3160 330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1639 sat_cipher = PasswordHasher.hash(sat_password)
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1640 personal_key = BlockCipher.getRandomKey(base64=True)
3160 330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1641 personal_cipher = BlockCipher.encrypt(sat_password, personal_key)
330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1642 xmpp_cipher = BlockCipher.encrypt(personal_key, xmpp_password)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1643
3160 330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1644 ret.append("INSERT INTO param_ind(category,name,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1645 (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	1646
3160 330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1647 ret.append("INSERT INTO private_ind(namespace,key,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1648 (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	1649
3160 330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1650 ret.append("REPLACE INTO param_ind(category,name,profile_id,value) VALUES ('%s','%s',%s,'%s')" %
330a5f1d9eea core (memory/crypto): replaced `PyCrypto` by `cryptography`: Goffi <goffi@goffi.org> parents: 3137 diff changeset	1651 (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	1652
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1653
1065 9378c80e3408 memory (sqlite): fixes upgrade to database v2 souliane <souliane@mailoo.org> parents: 1047 diff changeset	1654 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	1655 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	1656 d.addCallback(prepare_queries, xmpp_password)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1657 list_.append(d)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1658
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1659 d_list = defer.DeferredList(list_)
2765 378188abe941 misc: replaced all "dummy" by the more conventional and readable "__" ("_" being used for gettext) Goffi <goffi@goffi.org> parents: 2747 diff changeset	1660 d_list.addCallback(lambda __: ret)
1030 15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1661 return d_list
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1662
1045 65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1663 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	1664 try:
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1665 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	1666 except IndexError:
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1667 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	1668
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1669 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	1670 try:
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1671 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	1672 except IndexError:
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1673 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	1674 return
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1675 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	1676 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	1677 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	1678
65fffdcb97f1 memory: auto-update configuration file with libervia's passphrase when migrating the database souliane <souliane@mailoo.org> parents: 1030 diff changeset	1679 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	1680 d.addCallback(updateLiberviaConf)
2765 378188abe941 misc: replaced all "dummy" by the more conventional and readable "__" ("_" being used for gettext) Goffi <goffi@goffi.org> parents: 2747 diff changeset	1681 d.addCallback(lambda __: 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	1682 d.addCallback(encrypt_values)
15f43b54d697 core, memory, bridge: added profile password + password encryption: souliane <souliane@mailoo.org> parents: 1019 diff changeset	1683 return d

Mercurial > libervia-backend

annotate sat/memory/sqlite.py @ 3250:e4d3ba75b1b2