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

annotate sat/memory/sqlite.py @ 3004:d86cddc1cd05

primitivus: select the widget even if an other one is selected when the selection comes from an user action: new `user_action` arguments has been added to newWidget to specify when the new widget comes from a direct user action.

author	Goffi <goffi@goffi.org>
date	Sun, 14 Jul 2019 20:38:34 +0200
parents	c13333fcde5e
children	860c550028d6

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

Mercurial > libervia-backend

annotate sat/memory/sqlite.py @ 3004:d86cddc1cd05