author | Sylvain Thénault <sylvain.thenault@logilab.fr> |
Wed, 21 Apr 2010 16:53:47 +0200 | |
branch | stable |
changeset 5368 | d321e4b62a10 |
parent 5214 | 3285b6e3b930 |
child 5421 | 8167de96c523 |
permissions | -rw-r--r-- |
0 | 1 |
"""SQL utilities functions and classes. |
2 |
||
3 |
:organization: Logilab |
|
4212
ab6573088b4a
update copyright: welcome 2010
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
4195
diff
changeset
|
4 |
:copyright: 2001-2010 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 |
||
2493
9806571ea790
major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2396
diff
changeset
|
10 |
import os |
4298
2ca56131079e
enable cubicweb-ctl db-dump and db-restore on Windows with SQL Server
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
4212
diff
changeset
|
11 |
import subprocess |
4719
aaed3f813ef8
kill dead/useless code as suggested by pylint
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4622
diff
changeset
|
12 |
from datetime import datetime, date |
1016
26387b836099
use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents:
0
diff
changeset
|
13 |
|
4849
3827b9ee77ac
missing rename
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4848
diff
changeset
|
14 |
from logilab import database as db, common as lgc |
0 | 15 |
from logilab.common.shellutils import ProgressBar |
4466
8b0ca7904820
moved generic datetime manipulation function to lgc
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4436
diff
changeset
|
16 |
from logilab.common.date import todate, todatetime |
4848
41f84eea63c9
rename logilab.db into logilab.database
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4831
diff
changeset
|
17 |
from logilab.database.sqlgen import SQLGenerator |
0 | 18 |
|
19 |
from cubicweb import Binary, ConfigurationError |
|
4023
eae23c40627a
drop common subpackage
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3689
diff
changeset
|
20 |
from cubicweb.uilib import remove_html_tags |
2596
d02eed70937f
[R repo, schema] use VIRTUAL_RTYPES const
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2512
diff
changeset
|
21 |
from cubicweb.schema import PURE_VIRTUAL_RTYPES |
0 | 22 |
from cubicweb.server import SQL_CONNECT_HOOKS |
1132 | 23 |
from cubicweb.server.utils import crypt_password |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
24 |
from rql.utils import RQL_FUNCTIONS_REGISTRY |
0 | 25 |
|
1783 | 26 |
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
|
27 |
SQL_PREFIX = 'cw_' |
0 | 28 |
|
4353
7db69db4913c
command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4342
diff
changeset
|
29 |
def _run_command(cmd): |
4342
b4e186da08f2
handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4322
diff
changeset
|
30 |
"""backup/restore command are string w/ lgc < 0.47, lists with earlier versions |
b4e186da08f2
handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4322
diff
changeset
|
31 |
""" |
b4e186da08f2
handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4322
diff
changeset
|
32 |
if isinstance(cmd, basestring): |
4353
7db69db4913c
command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4342
diff
changeset
|
33 |
print '->', cmd |
7db69db4913c
command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4342
diff
changeset
|
34 |
return subprocess.call(cmd, shell=True) |
4342
b4e186da08f2
handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4322
diff
changeset
|
35 |
print ' '.join(cmd) |
b4e186da08f2
handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4322
diff
changeset
|
36 |
return subprocess.call(cmd) |
b4e186da08f2
handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4322
diff
changeset
|
37 |
|
0 | 38 |
|
3623
9b838e2d72bb
avoid progress bar when test launched from apycot
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2759
diff
changeset
|
39 |
def sqlexec(sqlstmts, cursor_or_execute, withpb=not os.environ.get('APYCOT_ROOT'), |
9b838e2d72bb
avoid progress bar when test launched from apycot
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2759
diff
changeset
|
40 |
pbtitle='', delimiter=';'): |
0 | 41 |
"""execute sql statements ignoring DROP/ CREATE GROUP or USER statements |
42 |
error. If a cnx is given, commit at each statement |
|
43 |
""" |
|
44 |
if hasattr(cursor_or_execute, 'execute'): |
|
45 |
execute = cursor_or_execute.execute |
|
46 |
else: |
|
47 |
execute = cursor_or_execute |
|
48 |
sqlstmts = sqlstmts.split(delimiter) |
|
49 |
if withpb: |
|
2396
8bfb99d7bbcc
[cw-ctl] improve dialog messages
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents:
1977
diff
changeset
|
50 |
pb = ProgressBar(len(sqlstmts), title=pbtitle) |
0 | 51 |
for sql in sqlstmts: |
52 |
sql = sql.strip() |
|
53 |
if withpb: |
|
54 |
pb.update() |
|
55 |
if not sql: |
|
56 |
continue |
|
57 |
# some dbapi modules doesn't accept unicode for sql string |
|
58 |
execute(str(sql)) |
|
59 |
if withpb: |
|
60 |
print |
|
61 |
||
62 |
||
63 |
def sqlgrants(schema, driver, user, |
|
64 |
text_index=True, set_owner=True, |
|
65 |
skip_relations=(), skip_entities=()): |
|
66 |
"""return sql to give all access privileges to the given user on the system |
|
67 |
schema |
|
68 |
""" |
|
69 |
from yams.schema2sql import grant_schema |
|
70 |
from cubicweb.server.sources import native |
|
71 |
output = [] |
|
72 |
w = output.append |
|
73 |
w(native.grant_schema(user, set_owner)) |
|
74 |
w('') |
|
75 |
if text_index: |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
76 |
dbhelper = db.get_db_helper(driver) |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
77 |
w(dbhelper.sql_grant_user_on_fti(user)) |
0 | 78 |
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
|
79 |
w(grant_schema(schema, user, set_owner, skip_entities=skip_entities, prefix=SQL_PREFIX)) |
0 | 80 |
return '\n'.join(output) |
81 |
||
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
82 |
|
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
83 |
def sqlschema(schema, driver, text_index=True, |
0 | 84 |
user=None, set_owner=False, |
2596
d02eed70937f
[R repo, schema] use VIRTUAL_RTYPES const
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2512
diff
changeset
|
85 |
skip_relations=PURE_VIRTUAL_RTYPES, skip_entities=()): |
0 | 86 |
"""return the system sql schema, according to the given parameters""" |
87 |
from yams.schema2sql import schema2sql |
|
88 |
from cubicweb.server.sources import native |
|
89 |
if set_owner: |
|
90 |
assert user, 'user is argument required when set_owner is true' |
|
91 |
output = [] |
|
92 |
w = output.append |
|
93 |
w(native.sql_schema(driver)) |
|
94 |
w('') |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
95 |
dbhelper = db.get_db_helper(driver) |
0 | 96 |
if text_index: |
4913
083b4d454192
server/web api for accessing to deleted_entites
Katia Saurfelt <katia.saurfelt@logilab.fr>
parents:
4899
diff
changeset
|
97 |
w(dbhelper.sql_init_fti().replace(';', ';;')) |
0 | 98 |
w('') |
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
99 |
w(schema2sql(dbhelper, schema, prefix=SQL_PREFIX, |
4913
083b4d454192
server/web api for accessing to deleted_entites
Katia Saurfelt <katia.saurfelt@logilab.fr>
parents:
4899
diff
changeset
|
100 |
skip_entities=skip_entities, |
083b4d454192
server/web api for accessing to deleted_entites
Katia Saurfelt <katia.saurfelt@logilab.fr>
parents:
4899
diff
changeset
|
101 |
skip_relations=skip_relations).replace(';', ';;')) |
0 | 102 |
if dbhelper.users_support and user: |
103 |
w('') |
|
104 |
w(sqlgrants(schema, driver, user, text_index, set_owner, |
|
4913
083b4d454192
server/web api for accessing to deleted_entites
Katia Saurfelt <katia.saurfelt@logilab.fr>
parents:
4899
diff
changeset
|
105 |
skip_relations, skip_entities).replace(';', ';;')) |
0 | 106 |
return '\n'.join(output) |
107 |
||
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
108 |
|
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
109 |
def sqldropschema(schema, driver, text_index=True, |
2596
d02eed70937f
[R repo, schema] use VIRTUAL_RTYPES const
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2512
diff
changeset
|
110 |
skip_relations=PURE_VIRTUAL_RTYPES, skip_entities=()): |
0 | 111 |
"""return the sql to drop the schema, according to the given parameters""" |
112 |
from yams.schema2sql import dropschema2sql |
|
113 |
from cubicweb.server.sources import native |
|
114 |
output = [] |
|
115 |
w = output.append |
|
116 |
if text_index: |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
117 |
dbhelper = db.get_db_helper(driver) |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
118 |
w(dbhelper.sql_drop_fti()) |
0 | 119 |
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
|
120 |
w(dropschema2sql(schema, prefix=SQL_PREFIX, |
1954 | 121 |
skip_entities=skip_entities, |
122 |
skip_relations=skip_relations)) |
|
5214
3285b6e3b930
fix cwctl db-init -d on SQL Server
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5013
diff
changeset
|
123 |
w('') |
3285b6e3b930
fix cwctl db-init -d on SQL Server
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5013
diff
changeset
|
124 |
w(native.sql_drop_schema(driver)) |
0 | 125 |
return '\n'.join(output) |
126 |
||
127 |
||
128 |
class SQLAdapterMixIn(object): |
|
129 |
"""Mixin for SQL data sources, getting a connection from a configuration |
|
130 |
dictionary and handling connection locking |
|
131 |
""" |
|
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
132 |
|
0 | 133 |
def __init__(self, source_config): |
134 |
try: |
|
135 |
self.dbdriver = source_config['db-driver'].lower() |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
136 |
dbname = source_config['db-name'] |
0 | 137 |
except KeyError: |
138 |
raise ConfigurationError('missing some expected entries in sources file') |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
139 |
dbhost = source_config.get('db-host') |
0 | 140 |
port = source_config.get('db-port') |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
141 |
dbport = port and int(port) or None |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
142 |
dbuser = source_config.get('db-user') |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
143 |
dbpassword = source_config.get('db-password') |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
144 |
dbencoding = source_config.get('db-encoding', 'UTF-8') |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
145 |
dbextraargs = source_config.get('db-extra-arguments') |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
146 |
self.dbhelper = db.get_db_helper(self.dbdriver) |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
147 |
self.dbhelper.record_connection_info(dbname, dbhost, dbport, dbuser, |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
148 |
dbpassword, dbextraargs, |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
149 |
dbencoding) |
0 | 150 |
self.sqlgen = SQLGenerator() |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
151 |
# copy back some commonly accessed attributes |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
152 |
dbapi_module = self.dbhelper.dbapi_module |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
153 |
self.OperationalError = dbapi_module.OperationalError |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
154 |
self.InterfaceError = dbapi_module.InterfaceError |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
155 |
self._binary = dbapi_module.Binary |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
156 |
self._process_value = dbapi_module.process_value |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
157 |
self._dbencoding = dbencoding |
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
158 |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
159 |
def get_connection(self): |
0 | 160 |
"""open and return a connection to the database""" |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
161 |
return self.dbhelper.get_connection() |
0 | 162 |
|
4893
15ae9a33a7f2
[db backup] fix name error in backup_to_file: we've to pass .confirm all along the chain as for restore
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4854
diff
changeset
|
163 |
def backup_to_file(self, backupfile, confirm): |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
164 |
for cmd in self.dbhelper.backup_commands(backupfile, |
4342
b4e186da08f2
handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4322
diff
changeset
|
165 |
keepownership=False): |
4353
7db69db4913c
command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4342
diff
changeset
|
166 |
if _run_command(cmd): |
7db69db4913c
command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4342
diff
changeset
|
167 |
if not confirm(' [Failed] Continue anyway?', default='n'): |
4342
b4e186da08f2
handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4322
diff
changeset
|
168 |
raise Exception('Failed command: %s' % cmd) |
2493
9806571ea790
major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2396
diff
changeset
|
169 |
|
9806571ea790
major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2396
diff
changeset
|
170 |
def restore_from_file(self, backupfile, confirm, drop=True): |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
171 |
for cmd in self.dbhelper.restore_commands(backupfile, |
4854
b06d2a3b27d9
logilab.db compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
172 |
keepownership=False, |
2493
9806571ea790
major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2396
diff
changeset
|
173 |
drop=drop): |
4353
7db69db4913c
command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4342
diff
changeset
|
174 |
if _run_command(cmd): |
7db69db4913c
command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4342
diff
changeset
|
175 |
if not confirm(' [Failed] Continue anyway?', default='n'): |
4195
86dcaf6bb92f
closes #601987
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4179
diff
changeset
|
176 |
raise Exception('Failed command: %s' % cmd) |
2493
9806571ea790
major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
2396
diff
changeset
|
177 |
|
0 | 178 |
def merge_args(self, args, query_args): |
179 |
if args is not None: |
|
4474
55fe19813bb7
kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4466
diff
changeset
|
180 |
newargs = {} |
55fe19813bb7
kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4466
diff
changeset
|
181 |
for key, val in args.iteritems(): |
0 | 182 |
# convert cubicweb binary into db binary |
183 |
if isinstance(val, Binary): |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
184 |
val = self._binary(val.getvalue()) |
4474
55fe19813bb7
kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4466
diff
changeset
|
185 |
newargs[key] = val |
0 | 186 |
# should not collide |
4474
55fe19813bb7
kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4466
diff
changeset
|
187 |
newargs.update(query_args) |
55fe19813bb7
kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4466
diff
changeset
|
188 |
return newargs |
0 | 189 |
return query_args |
190 |
||
5013
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
191 |
def process_result(self, cursor, column_callbacks=None): |
0 | 192 |
"""return a list of CubicWeb compliant values from data in the given cursor |
193 |
""" |
|
5013
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
194 |
# use two different implementations to avoid paying the price of |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
195 |
# callback lookup for each *cell* in results when there is nothing to |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
196 |
# lookup |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
197 |
if not column_callbacks: |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
198 |
return self._process_result(cursor) |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
199 |
return self._cb_process_result(cursor, column_callbacks) |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
200 |
|
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
201 |
def _process_result(self, cursor, column_callbacks=None): |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
202 |
# begin bind to locals for optimization |
0 | 203 |
descr = cursor.description |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
204 |
encoding = self._dbencoding |
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
205 |
process_value = self._process_value |
0 | 206 |
binary = Binary |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
207 |
# /end |
0 | 208 |
results = cursor.fetchall() |
209 |
for i, line in enumerate(results): |
|
210 |
result = [] |
|
211 |
for col, value in enumerate(line): |
|
212 |
if value is None: |
|
213 |
result.append(value) |
|
214 |
continue |
|
215 |
result.append(process_value(value, descr[col], encoding, binary)) |
|
216 |
results[i] = result |
|
217 |
return results |
|
218 |
||
5013
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
219 |
def _cb_process_result(self, cursor, column_callbacks): |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
220 |
# begin bind to locals for optimization |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
221 |
descr = cursor.description |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
222 |
encoding = self._dbencoding |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
223 |
process_value = self._process_value |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
224 |
binary = Binary |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
225 |
# /end |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
226 |
results = cursor.fetchall() |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
227 |
for i, line in enumerate(results): |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
228 |
result = [] |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
229 |
for col, value in enumerate(line): |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
230 |
if value is None: |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
231 |
result.append(value) |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
232 |
continue |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
233 |
cbstack = column_callbacks.get(col, None) |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
234 |
if cbstack is None: |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
235 |
value = process_value(value, descr[col], encoding, binary) |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
236 |
else: |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
237 |
for cb in cbstack: |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
238 |
value = cb(self, value) |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
239 |
result.append(value) |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
240 |
results[i] = result |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
241 |
return results |
ad91f93bbb93
[source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4965
diff
changeset
|
242 |
|
0 | 243 |
def preprocess_entity(self, entity): |
244 |
"""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
|
245 |
to insert/update an entity into a SQL database |
0 | 246 |
""" |
247 |
attrs = {} |
|
248 |
eschema = entity.e_schema |
|
4965
04543ed0bbdc
[source] only consider edited_attributes in source.preprocess_entity()
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
4913
diff
changeset
|
249 |
for attr in entity.edited_attributes: |
04543ed0bbdc
[source] only consider edited_attributes in source.preprocess_entity()
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
4913
diff
changeset
|
250 |
value = entity[attr] |
3689
deb13e88e037
follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3623
diff
changeset
|
251 |
rschema = eschema.subjrels[attr] |
deb13e88e037
follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3623
diff
changeset
|
252 |
if rschema.final: |
0 | 253 |
atype = str(entity.e_schema.destination(attr)) |
254 |
if atype == 'Boolean': |
|
255 |
value = self.dbhelper.boolean_value(value) |
|
256 |
elif atype == 'Password': |
|
257 |
# if value is a Binary instance, this mean we got it |
|
258 |
# from a query result and so it is already encrypted |
|
259 |
if isinstance(value, Binary): |
|
260 |
value = value.getvalue() |
|
261 |
else: |
|
262 |
value = crypt_password(value) |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
263 |
value = self._binary(value) |
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
264 |
# 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
|
265 |
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
|
266 |
value = todatetime(value) |
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
267 |
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
|
268 |
value = todate(value) |
0 | 269 |
elif isinstance(value, Binary): |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
270 |
value = self._binary(value.getvalue()) |
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
|
271 |
attrs[SQL_PREFIX+str(attr)] = value |
4965
04543ed0bbdc
[source] only consider edited_attributes in source.preprocess_entity()
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
4913
diff
changeset
|
272 |
attrs[SQL_PREFIX+'eid'] = entity.eid |
0 | 273 |
return attrs |
274 |
||
275 |
||
276 |
from logging import getLogger |
|
277 |
from cubicweb import set_log_methods |
|
278 |
set_log_methods(SQLAdapterMixIn, getLogger('cubicweb.sqladapter')) |
|
279 |
||
280 |
def init_sqlite_connexion(cnx): |
|
281 |
||
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
282 |
class group_concat(object): |
0 | 283 |
def __init__(self): |
284 |
self.values = [] |
|
285 |
def step(self, value): |
|
286 |
if value is not None: |
|
287 |
self.values.append(value) |
|
288 |
def finalize(self): |
|
289 |
return ', '.join(self.values) |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
290 |
cnx.create_aggregate("GROUP_CONCAT", 1, group_concat) |
1619
e4845b54a704
force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents:
1408
diff
changeset
|
291 |
|
0 | 292 |
def _limit_size(text, maxsize, format='text/plain'): |
293 |
if len(text) < maxsize: |
|
294 |
return text |
|
295 |
if format in ('text/html', 'text/xhtml', 'text/xml'): |
|
296 |
text = remove_html_tags(text) |
|
297 |
if len(text) > maxsize: |
|
298 |
text = text[:maxsize] + '...' |
|
299 |
return text |
|
300 |
||
301 |
def limit_size3(text, format, maxsize): |
|
302 |
return _limit_size(text, maxsize, format) |
|
303 |
cnx.create_function("LIMIT_SIZE", 3, limit_size3) |
|
304 |
||
305 |
def limit_size2(text, maxsize): |
|
306 |
return _limit_size(text, maxsize) |
|
307 |
cnx.create_function("TEXT_LIMIT_SIZE", 2, limit_size2) |
|
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
308 |
|
0 | 309 |
import yams.constraints |
4831
c5aec27c1bf7
[repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4719
diff
changeset
|
310 |
yams.constraints.patch_sqlite_decimal() |
0 | 311 |
|
312 |
sqlite_hooks = SQL_CONNECT_HOOKS.setdefault('sqlite', []) |
|
313 |
sqlite_hooks.append(init_sqlite_connexion) |