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