Mercurial > libervia-backend
annotate sat/memory/sqlite.py @ 3013:860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
the deferreds of the queries writing message to databases where not gathered and returned,
so the caller was not waiting for them to continue its workflow. This was resulting in
messages not always written when database was read just after the write (a case common
with MUC implementation), and message was appearing empty when sent to bridge.
fix 328
| author | Goffi <goffi@goffi.org> |
|---|---|
| date | Thu, 18 Jul 2019 21:58:34 +0200 |
| parents | c13333fcde5e |
| children | ab2696e34d29 |
| 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 | 2 # -*- coding: utf-8 -*- |
| 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 | 5 # Copyright (C) 2009-2019 Jérôme Poisson (goffi@goffi.org) |
| 412 | 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 | 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 | 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 | 19 |
| 771 | 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 | 26 from twisted.enterprise import adbapi |
| 27 from twisted.internet import defer | |
| 2500 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 84 ("PRIMARY KEY (profile_id, namespace, key)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))), |
| 2500 | 85 ('files', (("id TEXT NOT NULL", "version TEXT NOT NULL", "parent TEXT NOT NULL", |
| 86 "type TEXT CHECK(type in ('{file}', '{directory}')) NOT NULL DEFAULT '{file}'".format( | |
| 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 | 89 "namespace TEXT", "mime_type TEXT", |
| 90 "created DATETIME NOT NULL", "modified DATETIME", | |
| 91 "owner TEXT", "access TEXT", "extra TEXT", "profile_id INTEGER"), | |
| 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 | 103 'INDEX': (('history', (('profile_id', 'timestamp'), |
| 104 ('profile_id', 'received_timestamp'))), | |
| 105 ('message', ('history_uid',)), | |
| 106 ('subject', ('history_uid',)), | |
| 107 ('thread', ('history_uid',)), | |
| 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 | 110 8: {'specific': 'update_v8' |
| 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 | 116 5: {'create': {'files': (("id TEXT NOT NULL", "version TEXT NOT NULL", "parent TEXT NOT NULL", |
| 117 "type TEXT CHECK(type in ('{file}', '{directory}')) NOT NULL DEFAULT '{file}'".format( | |
| 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 | 120 "namespace TEXT", "mime_type TEXT", |
| 121 "created DATETIME NOT NULL", "modified DATETIME", | |
| 122 "owner TEXT", "access TEXT", "extra TEXT", "profile_id INTEGER"), | |
| 123 ("PRIMARY KEY (id, version)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE"))}, | |
| 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 | 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 | 192 """This class manage storage with Sqlite database""" |
| 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 | 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 | 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 | 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 | 232 |
| 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 | 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 | 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 | 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 | 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 | 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 | 271 def getProfilesList(self): |
| 272 """"Return list of all registered profiles""" | |
| 273 return self.profiles.keys() | |
| 274 | |
| 275 def hasProfile(self, profile_name): | |
| 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 | 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 | 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 | 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 | 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 | 319 d.addCallback(profile_created) |
| 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 | 350 ## Params |
| 412 | 351 def loadGenParams(self, params_gen): |
| 352 """Load general parameters | |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
353 |
| 412 | 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 | 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 | 363 return self.dbpool.runQuery("SELECT category,name,value FROM param_gen").addCallback(fillParams) |
| 364 | |
| 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 | 368 @param params_ind: dictionary to fill |
| 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 | 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 | 379 d.addCallback(fillParams) |
| 380 return d | |
| 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 | 394 def setGenParam(self, category, name, value): |
| 395 """Save the general parameters in database | |
| 2500 | 396 |
| 412 | 397 @param category: category of the parameter |
| 398 @param name: name of the parameter | |
| 399 @param value: value to set | |
| 400 @return: deferred""" | |
| 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 | 403 return d |
| 404 | |
| 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 | 408 @param category: category of the parameter |
| 409 @param name: name of the parameter | |
| 410 @param value: value to set | |
| 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 | 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 | 416 return d |
|
413
dd4caab17008
core: - individual parameters managed through sqlite
Goffi <goffi@goffi.org>
parents:
412
diff
changeset
|
417 |
| 2500 | 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'] |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
424 d_list = [] |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
425 for key in ('message', 'subject'): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
426 for lang, value in data[key].iteritems(): |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
427 d = self.dbpool.runQuery( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
428 "INSERT INTO {key}(history_uid, {key}, language) VALUES (?,?,?)" |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
429 .format(key=key), |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
430 (uid, value, lang or None)) |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
431 d.addErrback(lambda __: log.error( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
432 _(u"Can't save following {key} in history (uid: {uid}, lang:{lang}):" |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
433 u" {value}").format( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
434 key=key, uid=uid, lang=lang, value=value))) |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
435 d_list.append(d) |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
436 try: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
437 thread = data['extra']['thread'] |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
438 except KeyError: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
439 pass |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
440 else: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
441 thread_parent = data['extra'].get('thread_parent') |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
442 d = self.dbpool.runQuery( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
443 "INSERT INTO thread(history_uid, thread_id, parent_id) VALUES (?,?,?)", |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
444 (uid, thread, thread_parent)) |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
445 d.addErrback(lambda __: log.error( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
446 _(u"Can't save following thread in history (uid: {uid}): thread: " |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
447 u"{thread}), parent:{parent}").format( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
448 uid=uid, thread=thread, parent=thread_parent))) |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
449 d_list.append(d) |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
450 return defer.DeferredList(d_list) |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
451 |
|
1961
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
452 def _addToHistoryEb(self, failure_, data): |
|
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
453 failure_.trap(sqlite3.IntegrityError) |
|
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
454 sqlite_msg = failure_.value.args[0] |
|
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
455 if "UNIQUE constraint failed" in sqlite_msg: |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
456 log.debug(u"message {} is already in history, not storing it again" |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
457 .format(data['uid'])) |
|
1961
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
458 if 'received_timestamp' not in data: |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
459 log.warning( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
460 u"duplicate message is not delayed, this is maybe a bug: data={}" |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
461 .format(data)) |
|
1961
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
462 # 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
|
463 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
|
464 else: |
|
1961
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
465 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
|
466 |
|
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
467 def _logHistoryError(self, failure_, from_jid, to_jid, data): |
|
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
468 if failure_.check(exceptions.CancelError): |
|
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
469 # we propagate CancelError to avoid sending message to frontends |
|
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
470 raise failure_ |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
471 log.error(_( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
472 u"Can't save following message in history: from [{from_jid}] to [{to_jid}] " |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
473 u"(uid: {uid})") |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
474 .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
|
475 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
476 def addToHistory(self, data, profile): |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
477 """Store a new message in history |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
478 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
479 @param data(dict): message data as build by SatMessageProtocol.onMessage |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
480 """ |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
481 extra = pickle.dumps({k: v for k, v in data['extra'].iteritems() |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
482 if k not in NOT_IN_EXTRA}, 0) |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
483 from_jid = data['from'] |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
484 to_jid = data['to'] |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
485 d = self.dbpool.runQuery( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
486 u"INSERT INTO history(uid, stanza_id, update_uid, profile_id, source, dest, " |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
487 u"source_res, dest_res, timestamp, received_timestamp, type, extra) VALUES " |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
488 u"(?,?,?,?,?,?,?,?,?,?,?,?)", |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
489 (data['uid'], data['extra'].get('stanza_id'), data['extra'].get('update_uid'), |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
490 self.profiles[profile], data['from'].userhost(), to_jid.userhost(), |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
491 from_jid.resource, to_jid.resource, data['timestamp'], |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
492 data.get('received_timestamp'), data['type'], sqlite3.Binary(extra))) |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
493 d.addCallbacks(self._addToHistoryCb, |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
494 self._addToHistoryEb, |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
495 callbackArgs=[data], |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
496 errbackArgs=[data]) |
|
1961
c73e08094a95
memory (sqlite): better handling of IntegrityError
Goffi <goffi@goffi.org>
parents:
1955
diff
changeset
|
497 d.addErrback(self._logHistoryError, from_jid, to_jid, data) |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
498 return d |
|
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
499 |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
500 def sqliteHistoryToList(self, query_result): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
501 """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
|
502 result = [] |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
503 current = {'uid': None} |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
504 for row in reversed(query_result): |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
505 (uid, stanza_id, update_uid, source, dest, source_res, dest_res, timestamp, |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
506 received_timestamp, type_, extra, message, message_lang, subject, |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
507 subject_lang, thread, thread_parent) = row |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
508 if uid != current['uid']: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
509 # new message |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
510 try: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
511 extra = pickle.loads(str(extra or "")) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
512 except EOFError: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
513 extra = {} |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
514 current = { |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
515 '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
|
516 '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
|
517 'uid': uid, |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
518 'message': {}, |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
519 'subject': {}, |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
520 'type': type_, |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
521 'extra': extra, |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
522 'timestamp': timestamp, |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
523 } |
|
2699
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
524 if stanza_id is not None: |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
525 current['extra']['stanza_id'] = stanza_id |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
526 if update_uid is not None: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
527 current['extra']['update_uid'] = update_uid |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
528 if received_timestamp is not None: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
529 current['extra']['received_timestamp'] = str(received_timestamp) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
530 result.append(current) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
531 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
532 if message is not None: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
533 current['message'][message_lang or ''] = message |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
534 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
535 if subject is not None: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
536 current['subject'][subject_lang or ''] = subject |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
537 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
538 if thread is not None: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
539 current_extra = current['extra'] |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
540 current_extra['thread'] = thread |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
541 if thread_parent is not None: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
542 current_extra['thread_parent'] = thread_parent |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
543 else: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
544 if thread_parent is not None: |
|
3013
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
545 log.error( |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
546 u"Database inconsistency: thread parent without thread (uid: " |
|
860c550028d6
memory (sqlite): properly wait for messages to be writen in database:
Goffi <goffi@goffi.org>
parents:
2989
diff
changeset
|
547 u"{uid}, thread_parent: {parent})" |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
548 .format(uid=uid, parent=thread_parent)) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
549 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
550 return result |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
551 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
552 def listDict2listTuple(self, messages_data): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
553 """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
|
554 ret = [] |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
555 for m in messages_data: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
556 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
|
557 return ret |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
558 |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
559 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
|
560 """Retrieve messages in history |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
561 |
|
1222
e6e0ea4dc835
memory: add Parameter "Chat history limit"
souliane <souliane@mailoo.org>
parents:
1221
diff
changeset
|
562 @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
|
563 @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
|
564 @param limit (int): maximum number of messages to get: |
|
e6e0ea4dc835
memory: add Parameter "Chat history limit"
souliane <souliane@mailoo.org>
parents:
1221
diff
changeset
|
565 - 0 for no message (returns the empty list) |
|
e6e0ea4dc835
memory: add Parameter "Chat history limit"
souliane <souliane@mailoo.org>
parents:
1221
diff
changeset
|
566 - None for unlimited |
|
e6e0ea4dc835
memory: add Parameter "Chat history limit"
souliane <souliane@mailoo.org>
parents:
1221
diff
changeset
|
567 @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
|
568 @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
|
569 @param profile (unicode): %(doc_profile)s |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
570 @return: list of tuple as in [messageNew] |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
571 """ |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
572 assert profile |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
573 if filters is None: |
|
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
574 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
|
575 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
|
576 return defer.succeed([]) |
|
592
e5a875a3311b
Fix pep8 support in src/memory.
Emmanuel Gil Peyrot <linkmauve@linkmauve.fr>
parents:
588
diff
changeset
|
577 |
|
2699
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
578 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
|
579 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
|
580 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
|
581 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
|
582 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
|
583 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
|
584 values = [self.profiles[profile]] |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
585 |
|
2699
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
586 def test_jid(type_, jid_): |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
587 values.append(jid_.userhost()) |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
588 if jid_.resource: |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
589 values.append(jid_.resource) |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
590 return u'({type_}=? AND {type_}_res=?)'.format(type_=type_) |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
591 return u'{type_}=?'.format(type_=type_) |
|
587
952322b1d490
Remove trailing whitespaces.
Emmanuel Gil Peyrot <linkmauve@linkmauve.fr>
parents:
572
diff
changeset
|
592 |
|
2699
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
593 if not from_jid and not to_jid: |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
594 # not jid specified, we want all one2one communications |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
595 pass |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
596 elif between: |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
597 if not from_jid or not to_jid: |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
598 # 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
|
599 # from or to this jid |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
600 jid_ = from_jid or to_jid |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
601 query_parts.append(u"AND ({source} OR {dest})".format( |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
602 source=test_jid(u'source', jid_), |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
603 dest=test_jid(u'dest' , jid_))) |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
604 else: |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
605 # 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
|
606 # those 2 jids |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
607 query_parts.append( |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
608 u"AND (({source_from} AND {dest_to}) " |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
609 u"OR ({source_to} AND {dest_from}))".format( |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
610 source_from=test_jid('source', from_jid), |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
611 dest_to=test_jid('dest', to_jid), |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
612 source_to=test_jid('source', to_jid), |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
613 dest_from=test_jid('dest', from_jid))) |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
614 else: |
|
2699
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
615 # 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
|
616 # to somebody). |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
617 q = [] |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
618 if from_jid is not None: |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
619 q.append(test_jid('source', from_jid)) |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
620 if to_jid is not None: |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
621 q.append(test_jid('dest', to_jid)) |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
622 query_parts.append(u"AND " + u" AND ".join(q)) |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
623 |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
624 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
|
625 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
|
626 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
|
627 values.append(float(filters[u'timestamp_start'])) |
|
2715
b35c84ea73cf
plugin XEP-0045: MAM implementation for MUC
Goffi <goffi@goffi.org>
parents:
2711
diff
changeset
|
628 if u'body' in filters: |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
629 # 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
|
630 query_parts.append(u"AND message LIKE ?") |
|
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
631 values.append(u"%{}%".format(filters['body'])) |
|
2715
b35c84ea73cf
plugin XEP-0045: MAM implementation for MUC
Goffi <goffi@goffi.org>
parents:
2711
diff
changeset
|
632 if u'search' in filters: |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
633 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
|
634 values.extend([u"%{}%".format(filters['search'])] * 2) |
|
2715
b35c84ea73cf
plugin XEP-0045: MAM implementation for MUC
Goffi <goffi@goffi.org>
parents:
2711
diff
changeset
|
635 if u'types' in filters: |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
636 types = filters['types'].split() |
|
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
637 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
|
638 values.extend(types) |
|
2715
b35c84ea73cf
plugin XEP-0045: MAM implementation for MUC
Goffi <goffi@goffi.org>
parents:
2711
diff
changeset
|
639 if u'not_types' in filters: |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
640 types = filters['not_types'].split() |
|
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
641 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
|
642 values.extend(types) |
|
2715
b35c84ea73cf
plugin XEP-0045: MAM implementation for MUC
Goffi <goffi@goffi.org>
parents:
2711
diff
changeset
|
643 if u'last_stanza_id' in filters: |
|
2699
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
644 # 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
|
645 # 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
|
646 # while we were offline, using MAM (XEP-0313). |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
647 if (filters[u'last_stanza_id'] is not True |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
648 or limit != 1): |
|
310e41bd6666
core (memory/sqlite): added stanza_id:
Goffi <goffi@goffi.org>
parents:
2562
diff
changeset
|
649 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
|
650 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
|
651 |
|
2715
b35c84ea73cf
plugin XEP-0045: MAM implementation for MUC
Goffi <goffi@goffi.org>
parents:
2711
diff
changeset
|
652 |
|
2989
c13333fcde5e
memory (sqlite): fixed order for last_stanza_id:
Goffi <goffi@goffi.org>
parents:
2964
diff
changeset
|
653 # 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
|
654 # 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
|
655 # We'll reverse the order in sqliteHistoryToList |
|
c13333fcde5e
memory (sqlite): fixed order for last_stanza_id:
Goffi <goffi@goffi.org>
parents:
2964
diff
changeset
|
656 # 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
|
657 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
|
658 if limit is not None: |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
659 query_parts.append(u"LIMIT ?") |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
660 values.append(limit) |
|
587
952322b1d490
Remove trailing whitespaces.
Emmanuel Gil Peyrot <linkmauve@linkmauve.fr>
parents:
572
diff
changeset
|
661 |
|
2013
b536dd121da1
backend (memory), frontends: improved history filtering:
Goffi <goffi@goffi.org>
parents:
1962
diff
changeset
|
662 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
|
663 d.addCallback(self.sqliteHistoryToList) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
664 d.addCallback(self.listDict2listTuple) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
665 return d |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
666 |
| 2500 | 667 ## Private values |
|
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 _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
|
670 """generic errback for data queries""" |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
671 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
|
672 operation = operation, |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
673 namespace = namespace, |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
674 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
|
675 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
|
676 msg = failure_)) |
|
592
e5a875a3311b
Fix pep8 support in src/memory.
Emmanuel Gil Peyrot <linkmauve@linkmauve.fr>
parents:
588
diff
changeset
|
677 |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
678 def _generateDataDict(self, query_result, binary): |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
679 if binary: |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
680 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
|
681 else: |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
682 return dict(query_result) |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
683 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
684 def _getPrivateTable(self, binary, profile): |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
685 """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
|
686 table = [u'private'] |
|
425
e4e9187e3b5b
backend, bridge: asynchronous history
Goffi <goffi@goffi.org>
parents:
423
diff
changeset
|
687 |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
688 if profile is None: |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
689 table.append(u'gen') |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
690 else: |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
691 table.append(u'ind') |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
692 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
693 if binary: |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
694 table.append(u'bin') |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
695 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
696 return u'_'.join(table) |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
697 |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
698 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
|
699 """Get private value(s) from databases |
|
592
e5a875a3311b
Fix pep8 support in src/memory.
Emmanuel Gil Peyrot <linkmauve@linkmauve.fr>
parents:
588
diff
changeset
|
700 |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
701 @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
|
702 @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
|
703 None to get all keys/values |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
704 @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
|
705 @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
|
706 None to use general values |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
707 @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
|
708 """ |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
709 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
|
710 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
|
711 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
|
712 namespace = namespace, |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
713 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
|
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_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
|
716 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
|
717 |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
718 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
|
719 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
|
720 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
|
721 args.extend(keys) |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
722 |
|
2182
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_parts.append(u'AND profile_id=?') |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
725 args.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 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
|
728 d.addCallback(self._generateDataDict, binary) |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
729 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
|
730 return d |
|
31e8c48b5f5d
core: - memory refactoring (moved memory.py and sqlite.py from tools to memory)
Goffi <goffi@goffi.org>
parents:
425
diff
changeset
|
731 |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
732 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
|
733 """Set a private value in database |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
734 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
735 @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
|
736 @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
|
737 @param value(object): value to set |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
738 @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
|
739 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
|
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 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
742 """ |
|
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_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
|
745 query_values = [namespace, key] |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
746 |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
747 if binary: |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
748 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
|
749 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
750 query_values.append(value) |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
751 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
752 if profile is not None: |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
753 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
|
754 query_values.append(self.profiles[profile]) |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
755 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
756 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
|
757 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
|
758 |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
759 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
|
760 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
|
761 return d |
|
31e8c48b5f5d
core: - memory refactoring (moved memory.py and sqlite.py from tools to memory)
Goffi <goffi@goffi.org>
parents:
425
diff
changeset
|
762 |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
763 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
|
764 """Delete private value from database |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
765 |
|
432
31e8c48b5f5d
core: - memory refactoring (moved memory.py and sqlite.py from tools to memory)
Goffi <goffi@goffi.org>
parents:
425
diff
changeset
|
766 @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
|
767 @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
|
768 @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
|
769 @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
|
770 if None, it's a general value |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
771 """ |
|
2182
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
772 table = self._getPrivateTable(binary, profile) |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
773 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
|
774 args = [namespace, key] |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
775 if profile is not None: |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
776 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
|
777 args.append(self.profiles[profile]) |
|
087eec4c6c07
memory (persistent, sqlite): better private values handling + new LazyPersistentBinaryDict:
Goffi <goffi@goffi.org>
parents:
2144
diff
changeset
|
778 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
|
779 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
|
780 return d |
|
31e8c48b5f5d
core: - memory refactoring (moved memory.py and sqlite.py from tools to memory)
Goffi <goffi@goffi.org>
parents:
425
diff
changeset
|
781 |
|
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
|
782 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
|
783 """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
|
784 |
|
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
|
785 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
|
786 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
|
787 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
|
788 """ |
|
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
|
789 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
|
790 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
|
791 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
|
792 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
|
793 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
|
794 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
|
795 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
|
796 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
|
797 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
|
798 |
| 2500 | 799 ## Files |
| 800 | |
| 801 @defer.inlineCallbacks | |
| 802 def getFiles(self, client, file_id=None, version=u'', parent=None, type_=None, | |
| 803 file_hash=None, hash_algo=None, name=None, namespace=None, mime_type=None, | |
| 804 owner=None, access=None, projection=None, unique=False): | |
| 805 """retrieve files with with given filters | |
| 806 | |
| 807 @param file_id(unicode, None): id of the file | |
| 808 None to ignore | |
| 809 @param version(unicode, None): version of the file | |
| 810 None to ignore | |
| 811 empty string to look for current version | |
| 812 @param parent(unicode, None): id of the directory containing the files | |
| 813 None to ignore | |
| 814 empty string to look for root files/directories | |
| 815 @param projection(list[unicode], None): name of columns to retrieve | |
| 816 None to retrieve all | |
| 817 @param unique(bool): if True will remove duplicates | |
| 818 other params are the same as for [setFile] | |
| 819 @return (list[dict]): files corresponding to filters | |
| 820 """ | |
| 821 query_parts = ["SELECT"] | |
| 822 if unique: | |
| 823 query_parts.append('DISTINCT') | |
| 824 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
|
825 projection = ['id', 'version', 'parent', 'type', 'file_hash', 'hash_algo', 'name', |
| 2500 | 826 'size', 'namespace', 'mime_type', 'created', 'modified', 'owner', |
| 827 'access', 'extra'] | |
| 828 query_parts.append(','.join(projection)) | |
| 829 query_parts.append("FROM files WHERE") | |
| 830 filters = ['profile_id=?'] | |
| 831 args = [self.profiles[client.profile]] | |
| 832 | |
| 833 if file_id is not None: | |
| 834 filters.append(u'id=?') | |
| 835 args.append(file_id) | |
| 836 if version is not None: | |
| 837 filters.append(u'version=?') | |
| 838 args.append(version) | |
| 839 if parent is not None: | |
| 840 filters.append(u'parent=?') | |
| 841 args.append(parent) | |
| 842 if type_ is not None: | |
| 843 filters.append(u'type=?') | |
| 844 args.append(type_) | |
| 845 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
|
846 filters.append(u'file_hash=?') |
| 2500 | 847 args.append(file_hash) |
| 848 if hash_algo is not None: | |
| 849 filters.append(u'hash_algo=?') | |
| 850 args.append(hash_algo) | |
| 851 if name is not None: | |
| 852 filters.append(u'name=?') | |
| 853 args.append(name) | |
| 854 if namespace is not None: | |
| 855 filters.append(u'namespace=?') | |
| 856 args.append(namespace) | |
| 857 if mime_type is not None: | |
| 858 filters.append(u'mime_type=?') | |
| 859 args.append(mime_type) | |
| 860 if owner is not None: | |
| 861 filters.append(u'owner=?') | |
| 862 args.append(owner.full()) | |
| 863 if access is not None: | |
| 864 raise NotImplementedError('Access check is not implemented yet') | |
| 865 # a JSON comparison is needed here | |
| 866 | |
| 867 filters = u' AND '.join(filters) | |
| 868 query_parts.append(filters) | |
| 869 query = u' '.join(query_parts) | |
| 870 | |
| 871 result = yield self.dbpool.runQuery(query, args) | |
| 872 files_data = [dict(zip(projection, row)) for row in result] | |
| 873 to_parse = {'access', 'extra'}.intersection(projection) | |
| 874 to_filter = {'owner'}.intersection(projection) | |
| 875 if to_parse or to_filter: | |
| 876 for file_data in files_data: | |
| 877 for key in to_parse: | |
| 878 value = file_data[key] | |
| 879 file_data[key] = {} if value is None else json.loads(value) | |
| 880 owner = file_data.get('owner') | |
| 881 if owner is not None: | |
| 882 file_data['owner'] = jid.JID(owner) | |
| 883 defer.returnValue(files_data) | |
| 884 | |
| 885 def setFile(self, client, name, file_id, version=u'', parent=None, type_=C.FILE_TYPE_FILE, | |
| 886 file_hash=None, hash_algo=None, size=None, namespace=None, mime_type=None, | |
| 887 created=None, modified=None, owner=None, access=None, extra=None): | |
| 888 """set a file metadata | |
| 889 | |
| 890 @param client(SatXMPPClient): client owning the file | |
| 891 @param name(unicode): name of the file (must not contain "/") | |
| 892 @param file_id(unicode): unique id of the file | |
| 893 @param version(unicode): version of this file | |
| 894 @param parent(unicode): id of the directory containing this file | |
| 895 None if it is a root file/directory | |
| 896 @param type_(unicode): one of: | |
| 897 - file | |
| 898 - directory | |
| 899 @param file_hash(unicode): unique hash of the payload | |
| 900 @param hash_algo(unicode): algorithm used for hashing the file (usually sha-256) | |
| 901 @param size(int): size in bytes | |
| 902 @param namespace(unicode, None): identifier (human readable is better) to group files | |
| 903 for instance, namespace could be used to group files in a specific photo album | |
| 904 @param mime_type(unicode): MIME type of the file, or None if not known/guessed | |
| 905 @param created(int): UNIX time of creation | |
| 906 @param modified(int,None): UNIX time of last modification, or None to use created date | |
| 907 @param owner(jid.JID, None): jid of the owner of the file (mainly useful for component) | |
| 908 @param access(dict, None): serialisable dictionary with access rules. See [memory.memory] for details | |
| 909 @param extra(dict, None): serialisable dictionary of any extra data | |
| 910 will be encoded to json in database | |
| 911 """ | |
| 912 if extra is not None: | |
| 913 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
|
914 query = ('INSERT INTO files(id, version, parent, type, file_hash, hash_algo, name, size, namespace, ' |
| 2500 | 915 'mime_type, created, modified, owner, access, extra, profile_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)') |
| 916 d = self.dbpool.runQuery(query, (file_id, version.strip(), parent, type_, | |
| 917 file_hash, hash_algo, | |
| 918 name, size, namespace, | |
| 919 mime_type, created, modified, | |
| 920 owner.full() if owner is not None else None, | |
| 921 json.dumps(access) if access else None, | |
| 922 json.dumps(extra) if extra else None, | |
| 923 self.profiles[client.profile])) | |
| 924 d.addErrback(lambda failure: log.error(_(u"Can't save file metadata for [{profile}]: {reason}".format(profile=client.profile, reason=failure)))) | |
| 925 return d | |
| 926 | |
|
2526
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
927 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
|
928 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
|
929 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
|
930 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
|
931 try: |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
932 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
|
933 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
|
934 raise exceptions.NotFound |
|
2918
21cf8395616c
memory (sqlite): fixed fileUpdate when original value is not set (NULL)
Goffi <goffi@goffi.org>
parents:
2895
diff
changeset
|
935 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
|
936 value = {} |
|
21cf8395616c
memory (sqlite): fixed fileUpdate when original value is not set (NULL)
Goffi <goffi@goffi.org>
parents:
2895
diff
changeset
|
937 else: |
|
21cf8395616c
memory (sqlite): fixed fileUpdate when original value is not set (NULL)
Goffi <goffi@goffi.org>
parents:
2895
diff
changeset
|
938 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
|
939 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
|
940 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
|
941 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
|
942 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
|
943 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
|
944 else: |
|
21cf8395616c
memory (sqlite): fixed fileUpdate when original value is not set (NULL)
Goffi <goffi@goffi.org>
parents:
2895
diff
changeset
|
945 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
|
946 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
|
947 try: |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
948 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
|
949 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
|
950 pass |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
951 else: |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
952 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
|
953 break; |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
954 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
|
955 else: |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
956 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
|
957 |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
958 def fileUpdate(self, file_id, column, update_cb): |
|
2928
c0f6fd75af5f
core (memory, memory/sqlite): implemented fileDelete
Goffi <goffi@goffi.org>
parents:
2919
diff
changeset
|
959 """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
|
960 |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
961 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
|
962 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
|
963 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
|
964 @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
|
965 @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
|
966 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
|
967 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
|
968 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
|
969 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
|
970 @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
|
971 """ |
|
35d591086974
core (memory, sqlite): added fileUpdate method to update "extra" and "access" avoiding race condition
Goffi <goffi@goffi.org>
parents:
2507
diff
changeset
|
972 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
|
973 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
|
974 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
|
975 |
|
2928
c0f6fd75af5f
core (memory, memory/sqlite): implemented fileDelete
Goffi <goffi@goffi.org>
parents:
2919
diff
changeset
|
976 def fileDelete(self, file_id): |
|
c0f6fd75af5f
core (memory, memory/sqlite): implemented fileDelete
Goffi <goffi@goffi.org>
parents:
2919
diff
changeset
|
977 """Delete file metadata from the database |
|
c0f6fd75af5f
core (memory, memory/sqlite): implemented fileDelete
Goffi <goffi@goffi.org>
parents:
2919
diff
changeset
|
978 |
|
c0f6fd75af5f
core (memory, memory/sqlite): implemented fileDelete
Goffi <goffi@goffi.org>
parents:
2919
diff
changeset
|
979 @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
|
980 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
|
981 database |
|
c0f6fd75af5f
core (memory, memory/sqlite): implemented fileDelete
Goffi <goffi@goffi.org>
parents:
2919
diff
changeset
|
982 """ |
|
c0f6fd75af5f
core (memory, memory/sqlite): implemented fileDelete
Goffi <goffi@goffi.org>
parents:
2919
diff
changeset
|
983 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
|
984 |
|
413
dd4caab17008
core: - individual parameters managed through sqlite
Goffi <goffi@goffi.org>
parents:
412
diff
changeset
|
985 ##Helper methods## |
|
dd4caab17008
core: - individual parameters managed through sqlite
Goffi <goffi@goffi.org>
parents:
412
diff
changeset
|
986 |
|
dd4caab17008
core: - individual parameters managed through sqlite
Goffi <goffi@goffi.org>
parents:
412
diff
changeset
|
987 def __getFirstResult(self, result): |
|
dd4caab17008
core: - individual parameters managed through sqlite
Goffi <goffi@goffi.org>
parents:
412
diff
changeset
|
988 """Return the first result of a database query |
|
dd4caab17008
core: - individual parameters managed through sqlite
Goffi <goffi@goffi.org>
parents:
412
diff
changeset
|
989 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
|
990 return None if not result else result[0][0] |
|
853
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 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
993 class Updater(object): |
| 2500 | 994 stmnt_regex = re.compile(r"[\w/' ]+(?:\(.*?\))?[^,]*") |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
995 clean_regex = re.compile(r"^ +|(?<= ) +|(?<=,) +| +$") |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
996 CREATE_SQL = "CREATE TABLE %s (%s)" |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
997 INSERT_SQL = "INSERT INTO %s VALUES (%s)" |
| 2787 | 998 INDEX_SQL = "CREATE INDEX %s ON %s(%s)" |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
999 DROP_SQL = "DROP TABLE %s" |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1000 ALTER_SQL = "ALTER TABLE %s ADD COLUMN %s" |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1001 RENAME_TABLE_SQL = "ALTER TABLE %s RENAME TO %s" |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1002 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1003 CONSTRAINTS = ('PRIMARY', 'UNIQUE', 'CHECK', 'FOREIGN') |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1004 TMP_TABLE = "tmp_sat_update" |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1005 |
|
2716
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1006 def __init__(self, sqlite_storage, sat_version): |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1007 self._sat_version = sat_version |
|
2716
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1008 self.sqlite_storage = sqlite_storage |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1009 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1010 @property |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1011 def dbpool(self): |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1012 return self.sqlite_storage.dbpool |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1013 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1014 def getLocalVersion(self): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1015 """ Get local database version |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
1016 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1017 @return: version (int) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1018 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1019 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
|
1020 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1021 def _setLocalVersion(self, version): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1022 """ Set local database version |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
1023 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1024 @param version: version (int) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1025 @return: deferred |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1026 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1027 return self.dbpool.runOperation("PRAGMA user_version=%d" % version) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1028 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1029 def getLocalSchema(self): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1030 """ return raw local schema |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
1031 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1032 @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
|
1033 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1034 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
|
1035 d.addCallback(lambda result: [row[0] for row in result]) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1036 return d |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1037 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1038 @defer.inlineCallbacks |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1039 def checkUpdates(self): |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1040 """ 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
|
1041 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1042 @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
|
1043 """ |
| 2787 | 1044 # TODO: only "table" type (i.e. "CREATE" statements) is checked, |
| 1045 # "index" should be checked too. | |
| 1046 # 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
|
1047 local_version = yield self.getLocalVersion() |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1048 raw_local_sch = yield self.getLocalSchema() |
| 2500 | 1049 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1050 local_sch = self.rawStatements2data(raw_local_sch) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1051 current_sch = DATABASE_SCHEMAS['current']['CREATE'] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1052 local_hash = self.statementHash(local_sch) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1053 current_hash = self.statementHash(current_sch) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1054 |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1055 # 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
|
1056 force_update = local_hash == current_hash and local_version < CURRENT_DB_VERSION \ |
| 2787 | 1057 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
|
1058 |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1059 if local_hash == current_hash and not force_update: |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1060 if local_version != CURRENT_DB_VERSION: |
|
993
301b342c697a
core: use of the new core.log module:
Goffi <goffi@goffi.org>
parents:
936
diff
changeset
|
1061 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
|
1062 yield self._setLocalVersion(CURRENT_DB_VERSION) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1063 else: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1064 # an update is needed |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1065 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1066 if local_version == CURRENT_DB_VERSION: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1067 # Database mismatch and we have the latest version |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1068 if self._sat_version.endswith('D'): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1069 # we are in a development version |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1070 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
|
1071 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
|
1072 update_raw = yield self.update2raw(update_data, True) |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1073 defer.returnValue(update_raw) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1074 else: |
|
993
301b342c697a
core: use of the new core.log module:
Goffi <goffi@goffi.org>
parents:
936
diff
changeset
|
1075 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
|
1076 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
|
1077 update_raw = yield self.update2raw(update_data) |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1078 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
|
1079 raise exceptions.DatabaseError("Database mismatch") |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1080 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
|
1081 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
|
1082 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
|
1083 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
|
1084 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
|
1085 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
|
1086 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
|
1087 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1088 # 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
|
1089 if force_update: |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1090 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
|
1091 else: |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1092 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
|
1093 update_raw = [] |
| 1019 | 1094 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
|
1095 try: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1096 update_data = DATABASE_SCHEMAS[version] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1097 except KeyError: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1098 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
|
1099 if "specific" in update_data and update_raw: |
|
2716
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1100 # 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
|
1101 # 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
|
1102 # 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
|
1103 yield self.sqlite_storage.commitStatements(update_raw) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1104 del update_raw[:] |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1105 update_raw_step = yield self.update2raw(update_data) |
|
2716
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1106 if update_raw_step is not None: |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1107 # can be None with specifics |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1108 update_raw.extend(update_raw_step) |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1109 update_raw.append("PRAGMA user_version=%d" % CURRENT_DB_VERSION) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1110 defer.returnValue(update_raw) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1111 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1112 @staticmethod |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1113 def createData2Raw(data): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1114 """ Generate SQL statements from statements data |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
1115 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1116 @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
|
1117 @return: list of strings with raw statements |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1118 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1119 ret = [] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1120 for table in data: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1121 defs, constraints = data[table] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1122 assert isinstance(defs, tuple) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1123 assert isinstance(constraints, tuple) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1124 ret.append(Updater.CREATE_SQL % (table, ', '.join(defs + constraints))) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1125 return ret |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1126 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1127 @staticmethod |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1128 def insertData2Raw(data): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1129 """ Generate SQL statements from statements data |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
1130 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1131 @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
|
1132 @return: list of strings with raw statements |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1133 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1134 ret = [] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1135 for table in data: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1136 values_tuple = data[table] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1137 assert isinstance(values_tuple, tuple) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1138 for values in values_tuple: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1139 assert isinstance(values, tuple) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1140 ret.append(Updater.INSERT_SQL % (table, ', '.join(values))) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1141 return ret |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1142 |
| 2787 | 1143 @staticmethod |
| 1144 def indexData2Raw(data): | |
| 1145 """ Generate SQL statements from statements data | |
| 1146 | |
| 1147 @param data: dictionary with table as key, and statements data in tuples as value | |
| 1148 @return: list of strings with raw statements | |
| 1149 """ | |
| 1150 ret = [] | |
| 1151 assert isinstance(data, tuple) | |
| 1152 for table, col_data in data: | |
| 1153 assert isinstance(table, basestring) | |
| 1154 assert isinstance(col_data, tuple) | |
| 1155 for cols in col_data: | |
| 1156 if isinstance(cols, tuple): | |
| 1157 assert all([isinstance(c, basestring) for c in cols]) | |
| 1158 indexed_cols = u','.join(cols) | |
| 1159 elif isinstance(cols, basestring): | |
| 1160 indexed_cols = cols | |
| 1161 else: | |
| 1162 raise exceptions.InternalError(u"unexpected index columns value") | |
| 1163 index_name = table + u'__' + indexed_cols.replace(u',', u'_') | |
| 1164 ret.append(Updater.INDEX_SQL % (index_name, table, indexed_cols)) | |
| 1165 return ret | |
| 1166 | |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1167 def statementHash(self, data): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1168 """ Generate hash of template data |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
1169 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1170 useful to compare schemas |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1171 @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
|
1172 and tuples of (col_defs, constraints) as values |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1173 @return: hash as string |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1174 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1175 hash_ = hashlib.sha1() |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1176 tables = data.keys() |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1177 tables.sort() |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1178 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1179 def stmnts2str(stmts): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1180 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
|
1181 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1182 for table in tables: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1183 col_defs, col_constr = data[table] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1184 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
|
1185 return hash_.digest() |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1186 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1187 def rawStatements2data(self, raw_statements): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1188 """ separate "CREATE" statements into dictionary/tuples data |
|
2143
c3cac21157d4
memory (sqlite): introduced component table, schema updated:
Goffi <goffi@goffi.org>
parents:
2013
diff
changeset
|
1189 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1190 @param raw_statements: list of CREATE statements as strings |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1191 @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
|
1192 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1193 schema_dict = {} |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1194 for create_statement in raw_statements: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1195 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
|
1196 log.warning("Unexpected statement, ignoring it") |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1197 continue |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1198 _create_statement = create_statement[13:] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1199 table, raw_col_stats = _create_statement.split(' ',1) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1200 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
|
1201 log.warning("Unexpected statement structure, ignoring it") |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1202 continue |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1203 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
|
1204 col_defs = [] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1205 constraints = [] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1206 for col_stat in col_stats: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1207 name = col_stat.split(' ',1)[0] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1208 if name in self.CONSTRAINTS: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1209 constraints.append(col_stat) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1210 else: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1211 col_defs.append(col_stat) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1212 schema_dict[table] = (tuple(col_defs), tuple(constraints)) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1213 return schema_dict |
|
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 def generateUpdateData(self, old_data, new_data, modify=False): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1216 """ 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
|
1217 |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1218 @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
|
1219 @param new_data: data of the current schema |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1220 @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
|
1221 @return: update data, a dictionary with: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1222 - 'create': dictionary of tables to create |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1223 - 'delete': tuple of tables to delete |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1224 - '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
|
1225 - '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
|
1226 - '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
|
1227 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1228 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1229 create_tables_data = {} |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1230 create_cols_data = {} |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1231 modify_cols_data = {} |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1232 delete_cols_data = {} |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1233 old_tables = set(old_data.keys()) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1234 new_tables = set(new_data.keys()) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1235 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1236 def getChanges(set_olds, set_news): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1237 to_create = set_news.difference(set_olds) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1238 to_delete = set_olds.difference(set_news) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1239 to_check = set_news.intersection(set_olds) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1240 return tuple(to_create), tuple(to_delete), tuple(to_check) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1241 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1242 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
|
1243 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1244 for table in tables_to_create: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1245 create_tables_data[table] = new_data[table] |
|
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 for table in tables_to_check: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1248 old_col_defs, old_constraints = old_data[table] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1249 new_col_defs, new_constraints = new_data[table] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1250 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
|
1251 if not isinstance(obj, tuple): |
|
993
301b342c697a
core: use of the new core.log module:
Goffi <goffi@goffi.org>
parents:
936
diff
changeset
|
1252 raise exceptions.InternalError("Columns definitions must be tuples") |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1253 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
|
1254 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
|
1255 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
|
1256 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
|
1257 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
|
1258 (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
|
1259 # we have modified columns, we need to transfer table |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1260 # 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
|
1261 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
|
1262 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
|
1263 modify_cols_data[table] = tuple(old_names.intersection(new_names)); |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1264 else: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1265 if defs_create: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1266 create_cols_data[table] = (defs_create) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1267 if defs_delete or constraints_delete: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1268 delete_cols_data[table] = (defs_delete) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1269 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1270 return {'create': create_tables_data, |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1271 'delete': tables_to_delete, |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1272 'cols create': create_cols_data, |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1273 'cols delete': delete_cols_data, |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1274 'cols modify': modify_cols_data |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1275 } |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1276 |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1277 @defer.inlineCallbacks |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1278 def update2raw(self, update, dev_version=False): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1279 """ 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
|
1280 |
|
1455
4fb3280c4568
tmp (wokkel.rsm): use of super instead of direct call in PubSubRequest
Goffi <goffi@goffi.org>
parents:
1409
diff
changeset
|
1281 @param update: update data as returned by generateUpdateData |
|
853
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1282 @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
|
1283 @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
|
1284 """ |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1285 ret = self.createData2Raw(update.get('create', {})) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1286 drop = [] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1287 for table in update.get('delete', tuple()): |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1288 drop.append(self.DROP_SQL % table) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1289 if dev_version: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1290 if drop: |
|
993
301b342c697a
core: use of the new core.log module:
Goffi <goffi@goffi.org>
parents:
936
diff
changeset
|
1291 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
|
1292 else: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1293 ret.extend(drop) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1294 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1295 cols_create = update.get('cols create', {}) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1296 for table in cols_create: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1297 for col_def in cols_create[table]: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1298 ret.append(self.ALTER_SQL % (table, col_def)) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1299 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1300 cols_delete = update.get('cols delete', {}) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1301 for table in cols_delete: |
|
993
301b342c697a
core: use of the new core.log module:
Goffi <goffi@goffi.org>
parents:
936
diff
changeset
|
1302 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
|
1303 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1304 cols_modify = update.get('cols modify', {}) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1305 for table in cols_modify: |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1306 ret.append(self.RENAME_TABLE_SQL % (table, self.TMP_TABLE)) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1307 main, extra = DATABASE_SCHEMAS['current']['CREATE'][table] |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1308 ret.append(self.CREATE_SQL % (table, ', '.join(main + extra))) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1309 common_cols = ', '.join(cols_modify[table]) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1310 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
|
1311 ret.append(self.DROP_SQL % self.TMP_TABLE) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1312 |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1313 insert = update.get('insert', {}) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1314 ret.extend(self.insertData2Raw(insert)) |
|
c2f6ada7858f
core (sqlite): automatic database update:
Goffi <goffi@goffi.org>
parents:
839
diff
changeset
|
1315 |
| 2787 | 1316 index = update.get('index', tuple()) |
| 1317 ret.extend(self.indexData2Raw(index)) | |
| 1318 | |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1319 specific = update.get('specific', None) |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1320 if specific: |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1321 cmds = yield getattr(self, specific)() |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1322 ret.extend(cmds or []) |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1323 defer.returnValue(ret) |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1324 |
| 2787 | 1325 def update_v8(self): |
| 1326 """Update database from v7 to v8 (primary keys order changes + indexes)""" | |
| 1327 log.info(u"Database update to v8") | |
| 1328 statements = ["PRAGMA foreign_keys = OFF"] | |
| 1329 | |
| 1330 # here is a copy of create and index data, we can't use "current" table | |
| 1331 # because it may change in a future version, which would break the update | |
| 1332 # when doing v8 | |
| 1333 create = { | |
| 1334 'param_gen': ( | |
| 1335 ("category TEXT", "name TEXT", "value TEXT"), | |
| 1336 ("PRIMARY KEY (category, name)",)), | |
| 1337 'param_ind': ( | |
| 1338 ("category TEXT", "name TEXT", "profile_id INTEGER", "value TEXT"), | |
| 1339 ("PRIMARY KEY (profile_id, category, name)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE")), | |
| 1340 'private_ind': ( | |
| 1341 ("namespace TEXT", "key TEXT", "profile_id INTEGER", "value TEXT"), | |
| 1342 ("PRIMARY KEY (profile_id, namespace, key)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE")), | |
| 1343 'private_ind_bin': ( | |
| 1344 ("namespace TEXT", "key TEXT", "profile_id INTEGER", "value BLOB"), | |
| 1345 ("PRIMARY KEY (profile_id, namespace, key)", "FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE")), | |
| 1346 } | |
| 1347 index = ( | |
| 1348 ('history', (('profile_id', 'timestamp'), | |
| 1349 ('profile_id', 'received_timestamp'))), | |
| 1350 ('message', ('history_uid',)), | |
| 1351 ('subject', ('history_uid',)), | |
| 1352 ('thread', ('history_uid',)), | |
| 1353 ('files', ('profile_id', 'mime_type', 'owner', 'parent'))) | |
| 1354 | |
| 1355 for table in ('param_gen', 'param_ind', 'private_ind', 'private_ind_bin'): | |
| 1356 statements.append("ALTER TABLE {0} RENAME TO {0}_old".format(table)) | |
| 1357 schema = {table: create[table]} | |
| 1358 cols = [d.split()[0] for d in schema[table][0]] | |
| 1359 statements.extend(Updater.createData2Raw(schema)) | |
| 1360 statements.append(u"INSERT INTO {table}({cols}) " | |
| 1361 u"SELECT {cols} FROM {table}_old".format( | |
| 1362 table=table, | |
| 1363 cols=u','.join(cols))) | |
| 1364 statements.append(u"DROP TABLE {}_old".format(table)) | |
| 1365 | |
| 1366 statements.extend(Updater.indexData2Raw(index)) | |
| 1367 statements.append("PRAGMA foreign_keys = ON") | |
| 1368 return statements | |
| 1369 | |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1370 @defer.inlineCallbacks |
|
2716
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1371 def update_v7(self): |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1372 """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
|
1373 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
|
1374 u"size, please be patient.") |
|
2721
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1375 |
|
2722
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1376 log.info(u"Some cleaning first") |
|
2721
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1377 # 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
|
1378 # 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
|
1379 rows = yield self.dbpool.runQuery( |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1380 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
|
1381 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
|
1382 if rows: |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1383 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
|
1384 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
|
1385 for stanza_id, count in rows: |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1386 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
|
1387 row_uids = yield self.dbpool.runQuery( |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1388 "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
|
1389 (stanza_id, count-1)) |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1390 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
|
1391 yield self.dbpool.runQuery( |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1392 "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
|
1393 uids) |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1394 |
|
2722
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1395 def deleteInfo(txn): |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1396 # 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
|
1397 # 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
|
1398 txn.execute("PRAGMA foreign_keys = OFF") |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1399 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
|
1400 u"type='info')") |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1401 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
|
1402 u"type='info')") |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1403 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
|
1404 u"type='info')") |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1405 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
|
1406 u"type='info')") |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1407 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
|
1408 # 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
|
1409 txn.execute("PRAGMA foreign_keys = ON") |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1410 |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1411 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
|
1412 yield self.dbpool.runInteraction(deleteInfo) |
|
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1413 |
|
2721
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1414 log.info(u"Cleaning done") |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1415 |
|
2716
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1416 # we have to rename table we will replace |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1417 # 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
|
1418 # 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
|
1419 # seems to be fixed in new version of Sqlite |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1420 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
|
1421 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
|
1422 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
|
1423 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
|
1424 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1425 # history |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1426 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
|
1427 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
|
1428 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
|
1429 u"received_timestamp DATETIME, type TEXT, extra BLOB, " |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1430 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
|
1431 u"FOREIGN KEY(type) REFERENCES message_types(type), " |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1432 u"UNIQUE (profile_id, stanza_id, source, dest))") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1433 yield self.dbpool.runQuery(query) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1434 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1435 # message |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1436 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
|
1437 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
|
1438 u"history(uid) ON DELETE CASCADE)") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1439 yield self.dbpool.runQuery(query) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1440 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1441 # subject |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1442 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
|
1443 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
|
1444 u"history(uid) ON DELETE CASCADE)") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1445 yield self.dbpool.runQuery(query) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1446 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1447 # thread |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1448 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
|
1449 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
|
1450 u"history(uid) ON DELETE CASCADE)") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1451 yield self.dbpool.runQuery(query) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1452 |
|
2721
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1453 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
|
1454 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1455 log.info(u"\nTransfering table history") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1456 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
|
1457 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
|
1458 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
|
1459 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
|
1460 u"FROM history_old") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1461 yield self.dbpool.runQuery(query) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1462 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1463 log.info(u"\nTransfering table message") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1464 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
|
1465 u"history_uid, message, language FROM message_old") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1466 yield self.dbpool.runQuery(query) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1467 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1468 log.info(u"\nTransfering table subject") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1469 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
|
1470 u"history_uid, subject, language FROM subject_old") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1471 yield self.dbpool.runQuery(query) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1472 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1473 log.info(u"\nTransfering table thread") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1474 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
|
1475 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
|
1476 yield self.dbpool.runQuery(query) |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1477 |
|
2722
14e1db0c1383
core (memory/sqlite): further improvments in update_v7:
Goffi <goffi@goffi.org>
parents:
2721
diff
changeset
|
1478 log.info(u"\nRemoving old tables") |
|
2721
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1479 # 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
|
1480 # must be deleted first |
|
4aaa47f62d8d
core (memory/sqlite): fixed v7 update performance issue:
Goffi <goffi@goffi.org>
parents:
2720
diff
changeset
|
1481 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
|
1482 yield self.dbpool.runQuery("DROP TABLE subject_old") |
|
2716
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1483 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
|
1484 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
|
1485 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
|
1486 yield self.dbpool.runQuery("VACUUM") |
|
2716
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1487 log.info(u"Database update done :)") |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1488 |
|
06160b529da6
core (memory/sqlite): changed history constraint
Goffi <goffi@goffi.org>
parents:
2715
diff
changeset
|
1489 @defer.inlineCallbacks |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1490 def update_v3(self): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1491 """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
|
1492 # 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
|
1493 # 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
|
1494 # 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
|
1495 # 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
|
1496 # messages |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1497 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
|
1498 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1499 # 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
|
1500 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
|
1501 if rows: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1502 log.info("fixing duplicate timestamp") |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1503 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
|
1504 for timestamp, __ in rows: |
|
1955
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1505 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
|
1506 for idx, (id_,) in enumerate(ids_rows): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1507 fixed.append(id_) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1508 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
|
1509 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
|
1510 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1511 def historySchema(txn): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1512 log.info(u"History schema update") |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1513 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
|
1514 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
|
1515 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
|
1516 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1517 yield self.dbpool.runInteraction(historySchema) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1518 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1519 def newTables(txn): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1520 log.info(u"Creating new tables") |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1521 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
|
1522 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
|
1523 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
|
1524 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1525 yield self.dbpool.runInteraction(newTables) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1526 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1527 log.info(u"inserting new message type") |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1528 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
|
1529 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1530 log.info(u"messages update") |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1531 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
|
1532 total = len(rows) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1533 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1534 def updateHistory(txn, queries): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1535 for query, args in iter(queries): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1536 txn.execute(query, args) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1537 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1538 queries = [] |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1539 for idx, row in enumerate(rows, 1): |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1540 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
|
1541 log.info("preparing message {}/{}".format(idx, total)) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1542 id_, timestamp, message, extra = row |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1543 try: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1544 extra = pickle.loads(str(extra or "")) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1545 except EOFError: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1546 extra = {} |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1547 except Exception: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1548 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
|
1549 extra = {} |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1550 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1551 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
|
1552 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1553 try: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1554 subject = extra.pop('subject') |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1555 except KeyError: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1556 pass |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1557 else: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1558 try: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1559 subject = subject.decode('utf-8') |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1560 except UnicodeEncodeError: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1561 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
|
1562 del extra['subject'] |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1563 else: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1564 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
|
1565 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1566 received_timestamp = extra.pop('timestamp', None) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1567 try: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1568 del extra['archive'] |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1569 except KeyError: |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1570 # archive was not used |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1571 pass |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1572 |
|
1962
a45235d8dc93
memory (sqlite): fixed handling of extra (pickled data) by using sqlite3.Binary
Goffi <goffi@goffi.org>
parents:
1961
diff
changeset
|
1573 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
|
1574 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1575 yield self.dbpool.runInteraction(updateHistory, queries) |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1576 |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1577 log.info("Dropping temporary table") |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1578 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
|
1579 log.info("Database update finished :)") |
|
633b5c21aefd
backend, frontend: messages refactoring (huge commit, not finished):
Goffi <goffi@goffi.org>
parents:
1934
diff
changeset
|
1580 |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1581 def update2raw_v2(self): |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1582 """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
|
1583 |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1584 - 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
|
1585 - the profile password is stored hashed |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1586 - 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
|
1587 - 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
|
1588 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
|
1589 """ |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1590 xmpp_pass_path = ('Connection', 'Password') |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1591 |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1592 def encrypt_values(values): |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1593 ret = [] |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1594 list_ = [] |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1595 |
|
1065
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1596 def prepare_queries(result, xmpp_password): |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1597 try: |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1598 id_ = result[0][0] |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1599 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
|
1600 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
|
1601 return defer.succeed(None) |
|
1065
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1602 |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1603 sat_password = xmpp_password |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1604 d1 = PasswordHasher.hash(sat_password) |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1605 personal_key = BlockCipher.getRandomKey(base64=True) |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1606 d2 = BlockCipher.encrypt(sat_password, personal_key) |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1607 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
|
1608 |
|
1065
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1609 def gotValues(res): |
|
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1610 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
|
1611 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
|
1612 (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
|
1613 |
|
1065
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1614 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
|
1615 (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
|
1616 |
|
1065
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1617 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
|
1618 (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
|
1619 |
|
1065
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1620 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
|
1621 |
|
1065
9378c80e3408
memory (sqlite): fixes upgrade to database v2
souliane <souliane@mailoo.org>
parents:
1047
diff
changeset
|
1622 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
|
1623 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
|
1624 d.addCallback(prepare_queries, xmpp_password) |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1625 list_.append(d) |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1626 |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1627 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
|
1628 d_list.addCallback(lambda __: ret) |
|
1030
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1629 return d_list |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1630 |
|
1045
65fffdcb97f1
memory: auto-update configuration file with libervia's passphrase when migrating the database
souliane <souliane@mailoo.org>
parents:
1030
diff
changeset
|
1631 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
|
1632 try: |
|
65fffdcb97f1
memory: auto-update configuration file with libervia's passphrase when migrating the database
souliane <souliane@mailoo.org>
parents:
1030
diff
changeset
|
1633 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
|
1634 except IndexError: |
|
65fffdcb97f1
memory: auto-update configuration file with libervia's passphrase when migrating the database
souliane <souliane@mailoo.org>
parents:
1030
diff
changeset
|
1635 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
|
1636 |
|
65fffdcb97f1
memory: auto-update configuration file with libervia's passphrase when migrating the database
souliane <souliane@mailoo.org>
parents:
1030
diff
changeset
|
1637 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
|
1638 try: |
|
65fffdcb97f1
memory: auto-update configuration file with libervia's passphrase when migrating the database
souliane <souliane@mailoo.org>
parents:
1030
diff
changeset
|
1639 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
|
1640 except IndexError: |
|
65fffdcb97f1
memory: auto-update configuration file with libervia's passphrase when migrating the database
souliane <souliane@mailoo.org>
parents:
1030
diff
changeset
|
1641 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
|
1642 return |
|
65fffdcb97f1
memory: auto-update configuration file with libervia's passphrase when migrating the database
souliane <souliane@mailoo.org>
parents:
1030
diff
changeset
|
1643 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
|
1644 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
|
1645 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
|
1646 |
|
65fffdcb97f1
memory: auto-update configuration file with libervia's passphrase when migrating the database
souliane <souliane@mailoo.org>
parents:
1030
diff
changeset
|
1647 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
|
1648 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
|
1649 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
|
1650 d.addCallback(encrypt_values) |
|
15f43b54d697
core, memory, bridge: added profile password + password encryption:
souliane <souliane@mailoo.org>
parents:
1019
diff
changeset
|
1651 return d |