author | Sylvain Thénault <sylvain.thenault@logilab.fr> |
Tue, 21 Jul 2009 23:09:27 +0200 | |
branch | stable |
changeset 2413 | b1bea4cab594 |
parent 2396 | 8bfb99d7bbcc |
child 2493 | 9806571ea790 |
permissions | -rw-r--r-- |
0 | 1 |
"""SQL utilities functions and classes. |
2 |
||
3 |
:organization: Logilab |
|
1977
606923dff11b
big bunch of copyright / docstring update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1954
diff
changeset
|
4 |
:copyright: 2001-2009 LOGILAB S.A. (Paris, FRANCE), license is LGPL v2. |
0 | 5 |
:contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr |
1977
606923dff11b
big bunch of copyright / docstring update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1954
diff
changeset
|
6 |
:license: GNU Lesser General Public License, v2.1 - http://www.gnu.org/licenses |
0 | 7 |
""" |
8 |
__docformat__ = "restructuredtext en" |
|
9 |
||
1016
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
10 |
from warnings import warn |
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
11 |
from datetime import datetime, date, timedelta |
1016
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
12 |
|
1783 | 13 |
import logilab.common as lgc |
0 | 14 |
from logilab.common.shellutils import ProgressBar |
1016
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
15 |
from logilab.common import db |
0 | 16 |
from logilab.common.adbh import get_adv_func_helper |
17 |
from logilab.common.sqlgen import SQLGenerator |
|
18 |
||
19 |
from indexer import get_indexer |
|
20 |
||
21 |
from cubicweb import Binary, ConfigurationError |
|
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
22 |
from cubicweb.utils import todate, todatetime |
0 | 23 |
from cubicweb.common.uilib import remove_html_tags |
24 |
from cubicweb.server import SQL_CONNECT_HOOKS |
|
1132 | 25 |
from cubicweb.server.utils import crypt_password |
0 | 26 |
|
27 |
||
1783 | 28 |
lgc.USE_MX_DATETIME = False |
1251
af40e615dc89
introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
29 |
SQL_PREFIX = 'cw_' |
0 | 30 |
|
31 |
||
2396
8bfb99d7bbcc
[cw-ctl] improve dialog messages
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents:
1977
diff
changeset
|
32 |
def sqlexec(sqlstmts, cursor_or_execute, withpb=True, pbtitle='', delimiter=';'): |
0 | 33 |
"""execute sql statements ignoring DROP/ CREATE GROUP or USER statements |
34 |
error. If a cnx is given, commit at each statement |
|
35 |
""" |
|
36 |
if hasattr(cursor_or_execute, 'execute'): |
|
37 |
execute = cursor_or_execute.execute |
|
38 |
else: |
|
39 |
execute = cursor_or_execute |
|
40 |
sqlstmts = sqlstmts.split(delimiter) |
|
41 |
if withpb: |
|
2396
8bfb99d7bbcc
[cw-ctl] improve dialog messages
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents:
1977
diff
changeset
|
42 |
pb = ProgressBar(len(sqlstmts), title=pbtitle) |
0 | 43 |
for sql in sqlstmts: |
44 |
sql = sql.strip() |
|
45 |
if withpb: |
|
46 |
pb.update() |
|
47 |
if not sql: |
|
48 |
continue |
|
49 |
# some dbapi modules doesn't accept unicode for sql string |
|
50 |
execute(str(sql)) |
|
51 |
if withpb: |
|
52 |
print |
|
53 |
||
54 |
||
55 |
def sqlgrants(schema, driver, user, |
|
56 |
text_index=True, set_owner=True, |
|
57 |
skip_relations=(), skip_entities=()): |
|
58 |
"""return sql to give all access privileges to the given user on the system |
|
59 |
schema |
|
60 |
""" |
|
61 |
from yams.schema2sql import grant_schema |
|
62 |
from cubicweb.server.sources import native |
|
63 |
output = [] |
|
64 |
w = output.append |
|
65 |
w(native.grant_schema(user, set_owner)) |
|
66 |
w('') |
|
67 |
if text_index: |
|
68 |
indexer = get_indexer(driver) |
|
69 |
w(indexer.sql_grant_user(user)) |
|
70 |
w('') |
|
1251
af40e615dc89
introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
71 |
w(grant_schema(schema, user, set_owner, skip_entities=skip_entities, prefix=SQL_PREFIX)) |
0 | 72 |
return '\n'.join(output) |
73 |
||
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
74 |
|
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
75 |
def sqlschema(schema, driver, text_index=True, |
0 | 76 |
user=None, set_owner=False, |
77 |
skip_relations=('has_text', 'identity'), skip_entities=()): |
|
78 |
"""return the system sql schema, according to the given parameters""" |
|
79 |
from yams.schema2sql import schema2sql |
|
80 |
from cubicweb.server.sources import native |
|
81 |
if set_owner: |
|
82 |
assert user, 'user is argument required when set_owner is true' |
|
83 |
output = [] |
|
84 |
w = output.append |
|
85 |
w(native.sql_schema(driver)) |
|
86 |
w('') |
|
87 |
if text_index: |
|
88 |
indexer = get_indexer(driver) |
|
89 |
w(indexer.sql_init_fti()) |
|
90 |
w('') |
|
91 |
dbhelper = get_adv_func_helper(driver) |
|
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
92 |
w(schema2sql(dbhelper, schema, prefix=SQL_PREFIX, |
0 | 93 |
skip_entities=skip_entities, skip_relations=skip_relations)) |
94 |
if dbhelper.users_support and user: |
|
95 |
w('') |
|
96 |
w(sqlgrants(schema, driver, user, text_index, set_owner, |
|
97 |
skip_relations, skip_entities)) |
|
98 |
return '\n'.join(output) |
|
99 |
||
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
100 |
|
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
101 |
def sqldropschema(schema, driver, text_index=True, |
0 | 102 |
skip_relations=('has_text', 'identity'), skip_entities=()): |
103 |
"""return the sql to drop the schema, according to the given parameters""" |
|
104 |
from yams.schema2sql import dropschema2sql |
|
105 |
from cubicweb.server.sources import native |
|
106 |
output = [] |
|
107 |
w = output.append |
|
108 |
w(native.sql_drop_schema(driver)) |
|
109 |
w('') |
|
110 |
if text_index: |
|
111 |
indexer = get_indexer(driver) |
|
112 |
w(indexer.sql_drop_fti()) |
|
113 |
w('') |
|
1251
af40e615dc89
introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
114 |
w(dropschema2sql(schema, prefix=SQL_PREFIX, |
1954 | 115 |
skip_entities=skip_entities, |
116 |
skip_relations=skip_relations)) |
|
0 | 117 |
return '\n'.join(output) |
118 |
||
1016
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
119 |
try: |
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
120 |
from mx.DateTime import DateTimeType, DateTimeDeltaType |
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
121 |
except ImportError: |
1953 | 122 |
DateTimeType = DateTimeDeltaType = None |
0 | 123 |
|
124 |
class SQLAdapterMixIn(object): |
|
125 |
"""Mixin for SQL data sources, getting a connection from a configuration |
|
126 |
dictionary and handling connection locking |
|
127 |
""" |
|
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
128 |
|
0 | 129 |
def __init__(self, source_config): |
130 |
try: |
|
131 |
self.dbdriver = source_config['db-driver'].lower() |
|
132 |
self.dbname = source_config['db-name'] |
|
133 |
except KeyError: |
|
134 |
raise ConfigurationError('missing some expected entries in sources file') |
|
135 |
self.dbhost = source_config.get('db-host') |
|
136 |
port = source_config.get('db-port') |
|
137 |
self.dbport = port and int(port) or None |
|
138 |
self.dbuser = source_config.get('db-user') |
|
139 |
self.dbpasswd = source_config.get('db-password') |
|
140 |
self.encoding = source_config.get('db-encoding', 'UTF-8') |
|
1016
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
141 |
self.dbapi_module = db.get_dbapi_compliant_module(self.dbdriver) |
0 | 142 |
self.binary = self.dbapi_module.Binary |
143 |
self.dbhelper = self.dbapi_module.adv_func_helper |
|
144 |
self.sqlgen = SQLGenerator() |
|
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
145 |
|
0 | 146 |
def get_connection(self, user=None, password=None): |
147 |
"""open and return a connection to the database""" |
|
148 |
if user or self.dbuser: |
|
149 |
self.info('connecting to %s@%s for user %s', self.dbname, |
|
150 |
self.dbhost or 'localhost', user or self.dbuser) |
|
151 |
else: |
|
152 |
self.info('connecting to %s@%s', self.dbname, |
|
153 |
self.dbhost or 'localhost') |
|
154 |
cnx = self.dbapi_module.connect(self.dbhost, self.dbname, |
|
155 |
user or self.dbuser, |
|
156 |
password or self.dbpasswd, |
|
157 |
port=self.dbport) |
|
158 |
init_cnx(self.dbdriver, cnx) |
|
159 |
#self.dbapi_module.type_code_test(cnx.cursor()) |
|
160 |
return cnx |
|
161 |
||
162 |
def merge_args(self, args, query_args): |
|
163 |
if args is not None: |
|
164 |
args = dict(args) |
|
165 |
for key, val in args.items(): |
|
166 |
# convert cubicweb binary into db binary |
|
167 |
if isinstance(val, Binary): |
|
168 |
val = self.binary(val.getvalue()) |
|
1016
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
169 |
# XXX <3.2 bw compat |
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
170 |
elif type(val) is DateTimeType: |
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
171 |
warn('found mx date time instance, please update to use datetime', |
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
172 |
DeprecationWarning) |
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
173 |
val = datetime(val.year, val.month, val.day, |
1026 | 174 |
val.hour, val.minute, int(val.second)) |
1016
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
175 |
elif type(val) is DateTimeDeltaType: |
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
176 |
warn('found mx date time instance, please update to use datetime', |
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
177 |
DeprecationWarning) |
1026 | 178 |
val = timedelta(0, int(val.seconds), 0) |
0 | 179 |
args[key] = val |
180 |
# should not collide |
|
181 |
args.update(query_args) |
|
182 |
return args |
|
183 |
return query_args |
|
184 |
||
185 |
def process_result(self, cursor): |
|
186 |
"""return a list of CubicWeb compliant values from data in the given cursor |
|
187 |
""" |
|
188 |
descr = cursor.description |
|
189 |
encoding = self.encoding |
|
190 |
process_value = self.dbapi_module.process_value |
|
191 |
binary = Binary |
|
192 |
results = cursor.fetchall() |
|
193 |
for i, line in enumerate(results): |
|
194 |
result = [] |
|
195 |
for col, value in enumerate(line): |
|
196 |
if value is None: |
|
197 |
result.append(value) |
|
198 |
continue |
|
199 |
result.append(process_value(value, descr[col], encoding, binary)) |
|
200 |
results[i] = result |
|
201 |
return results |
|
202 |
||
203 |
||
204 |
def preprocess_entity(self, entity): |
|
205 |
"""return a dictionary to use as extra argument to cursor.execute |
|
1251
af40e615dc89
introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
206 |
to insert/update an entity into a SQL database |
0 | 207 |
""" |
208 |
attrs = {} |
|
209 |
eschema = entity.e_schema |
|
210 |
for attr, value in entity.items(): |
|
211 |
rschema = eschema.subject_relation(attr) |
|
212 |
if rschema.is_final(): |
|
213 |
atype = str(entity.e_schema.destination(attr)) |
|
214 |
if atype == 'Boolean': |
|
215 |
value = self.dbhelper.boolean_value(value) |
|
216 |
elif atype == 'Password': |
|
217 |
# if value is a Binary instance, this mean we got it |
|
218 |
# from a query result and so it is already encrypted |
|
219 |
if isinstance(value, Binary): |
|
220 |
value = value.getvalue() |
|
221 |
else: |
|
222 |
value = crypt_password(value) |
|
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
223 |
# XXX needed for sqlite but I don't think it is for other backends |
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
224 |
elif atype == 'Datetime' and isinstance(value, date): |
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
225 |
value = todatetime(value) |
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
226 |
elif atype == 'Date' and isinstance(value, datetime): |
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
227 |
value = todate(value) |
0 | 228 |
elif isinstance(value, Binary): |
229 |
value = self.binary(value.getvalue()) |
|
1408
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
230 |
# XXX <3.2 bw compat |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
231 |
elif type(value) is DateTimeType: |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
232 |
warn('found mx date time instance, please update to use datetime', |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
233 |
DeprecationWarning) |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
234 |
value = datetime(value.year, value.month, value.day, |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
235 |
value.hour, value.minute, int(value.second)) |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
236 |
elif type(value) is DateTimeDeltaType: |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
237 |
warn('found mx date time instance, please update to use datetime', |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
238 |
DeprecationWarning) |
6bf19f175ea5
mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents:
1263
diff
changeset
|
239 |
value = timedelta(0, int(value.seconds), 0) |
1251
af40e615dc89
introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
240 |
attrs[SQL_PREFIX+str(attr)] = value |
0 | 241 |
return attrs |
242 |
||
243 |
||
244 |
from logging import getLogger |
|
245 |
from cubicweb import set_log_methods |
|
246 |
set_log_methods(SQLAdapterMixIn, getLogger('cubicweb.sqladapter')) |
|
247 |
||
248 |
def init_sqlite_connexion(cnx): |
|
249 |
# XXX should not be publicly exposed |
|
250 |
#def comma_join(strings): |
|
251 |
# return ', '.join(strings) |
|
252 |
#cnx.create_function("COMMA_JOIN", 1, comma_join) |
|
253 |
||
254 |
class concat_strings(object): |
|
255 |
def __init__(self): |
|
256 |
self.values = [] |
|
257 |
def step(self, value): |
|
258 |
if value is not None: |
|
259 |
self.values.append(value) |
|
260 |
def finalize(self): |
|
261 |
return ', '.join(self.values) |
|
262 |
# renamed to GROUP_CONCAT in cubicweb 2.45, keep old name for bw compat for |
|
263 |
# some time |
|
264 |
cnx.create_aggregate("CONCAT_STRINGS", 1, concat_strings) |
|
265 |
cnx.create_aggregate("GROUP_CONCAT", 1, concat_strings) |
|
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
266 |
|
0 | 267 |
def _limit_size(text, maxsize, format='text/plain'): |
268 |
if len(text) < maxsize: |
|
269 |
return text |
|
270 |
if format in ('text/html', 'text/xhtml', 'text/xml'): |
|
271 |
text = remove_html_tags(text) |
|
272 |
if len(text) > maxsize: |
|
273 |
text = text[:maxsize] + '...' |
|
274 |
return text |
|
275 |
||
276 |
def limit_size3(text, format, maxsize): |
|
277 |
return _limit_size(text, maxsize, format) |
|
278 |
cnx.create_function("LIMIT_SIZE", 3, limit_size3) |
|
279 |
||
280 |
def limit_size2(text, maxsize): |
|
281 |
return _limit_size(text, maxsize) |
|
282 |
cnx.create_function("TEXT_LIMIT_SIZE", 2, limit_size2) |
|
283 |
import yams.constraints |
|
284 |
if hasattr(yams.constraints, 'patch_sqlite_decimal'): |
|
285 |
yams.constraints.patch_sqlite_decimal() |
|
286 |
||
287 |
||
288 |
sqlite_hooks = SQL_CONNECT_HOOKS.setdefault('sqlite', []) |
|
289 |
sqlite_hooks.append(init_sqlite_connexion) |
|
290 |
||
291 |
def init_cnx(driver, cnx): |
|
292 |
for hook in SQL_CONNECT_HOOKS.get(driver, ()): |
|
293 |
hook(cnx) |