18 """SQL utilities functions and classes.""" |
18 """SQL utilities functions and classes.""" |
19 |
19 |
20 __docformat__ = "restructuredtext en" |
20 __docformat__ = "restructuredtext en" |
21 |
21 |
22 import os |
22 import os |
|
23 import re |
23 import subprocess |
24 import subprocess |
24 from datetime import datetime, date |
25 from datetime import datetime, date |
|
26 from itertools import ifilter |
25 |
27 |
26 from logilab import database as db, common as lgc |
28 from logilab import database as db, common as lgc |
27 from logilab.common.shellutils import ProgressBar |
29 from logilab.common.shellutils import ProgressBar |
28 from logilab.common.date import todate, todatetime, utcdatetime, utctime |
30 from logilab.common.date import todate, todatetime, utcdatetime, utctime |
29 from logilab.database.sqlgen import SQLGenerator |
31 from logilab.database.sqlgen import SQLGenerator |
47 print ' '.join(cmd) |
49 print ' '.join(cmd) |
48 return subprocess.call(cmd) |
50 return subprocess.call(cmd) |
49 |
51 |
50 |
52 |
51 def sqlexec(sqlstmts, cursor_or_execute, withpb=not os.environ.get('APYCOT_ROOT'), |
53 def sqlexec(sqlstmts, cursor_or_execute, withpb=not os.environ.get('APYCOT_ROOT'), |
52 pbtitle='', delimiter=';'): |
54 pbtitle='', delimiter=';', cnx=None): |
53 """execute sql statements ignoring DROP/ CREATE GROUP or USER statements |
55 """execute sql statements ignoring DROP/ CREATE GROUP or USER statements |
54 error. If a cnx is given, commit at each statement |
56 error. |
|
57 |
|
58 :sqlstmts_as_string: a string or a list of sql statements. |
|
59 :cursor_or_execute: sql cursor or a callback used to execute statements |
|
60 :cnx: if given, commit/rollback at each statement. |
|
61 |
|
62 :withpb: if True, display a progresse bar |
|
63 :pbtitle: a string displayed as the progress bar title (if `withpb=True`) |
|
64 |
|
65 :delimiter: a string used to split sqlstmts (if it is a string) |
|
66 |
|
67 Return the failed statements (same type as sqlstmts) |
55 """ |
68 """ |
56 if hasattr(cursor_or_execute, 'execute'): |
69 if hasattr(cursor_or_execute, 'execute'): |
57 execute = cursor_or_execute.execute |
70 execute = cursor_or_execute.execute |
58 else: |
71 else: |
59 execute = cursor_or_execute |
72 execute = cursor_or_execute |
60 sqlstmts = sqlstmts.split(delimiter) |
73 sqlstmts_as_string = False |
|
74 if isinstance(sqlstmts, basestring): |
|
75 sqlstmts_as_string = True |
|
76 sqlstmts = sqlstmts.split(delimiter) |
61 if withpb: |
77 if withpb: |
62 pb = ProgressBar(len(sqlstmts), title=pbtitle) |
78 pb = ProgressBar(len(sqlstmts), title=pbtitle) |
|
79 failed = [] |
63 for sql in sqlstmts: |
80 for sql in sqlstmts: |
64 sql = sql.strip() |
81 sql = sql.strip() |
65 if withpb: |
82 if withpb: |
66 pb.update() |
83 pb.update() |
67 if not sql: |
84 if not sql: |
68 continue |
85 continue |
69 # some dbapi modules doesn't accept unicode for sql string |
86 try: |
70 execute(str(sql)) |
87 # some dbapi modules doesn't accept unicode for sql string |
|
88 execute(str(sql)) |
|
89 except Exception, err: |
|
90 if cnx: |
|
91 cnx.rollback() |
|
92 failed.append(sql) |
|
93 else: |
|
94 if cnx: |
|
95 cnx.commit() |
71 if withpb: |
96 if withpb: |
72 print |
97 print |
|
98 if sqlstmts_as_string: |
|
99 failed = delimiter.join(failed) |
|
100 return failed |
73 |
101 |
74 |
102 |
75 def sqlgrants(schema, driver, user, |
103 def sqlgrants(schema, driver, user, |
76 text_index=True, set_owner=True, |
104 text_index=True, set_owner=True, |
77 skip_relations=(), skip_entities=()): |
105 skip_relations=(), skip_entities=()): |
134 skip_relations=skip_relations)) |
162 skip_relations=skip_relations)) |
135 w('') |
163 w('') |
136 w(native.sql_drop_schema(driver)) |
164 w(native.sql_drop_schema(driver)) |
137 return '\n'.join(output) |
165 return '\n'.join(output) |
138 |
166 |
|
167 |
|
168 _SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION = re.compile('^(?!(sql|pg)_)').match |
|
169 def sql_drop_all_user_tables(driver_or_helper, sqlcursor): |
|
170 """Return ths sql to drop all tables found in the database system.""" |
|
171 if not getattr(driver_or_helper, 'list_tables', None): |
|
172 dbhelper = db.get_db_helper(driver_or_helper) |
|
173 else: |
|
174 dbhelper = driver_or_helper |
|
175 |
|
176 cmds = [dbhelper.sql_drop_sequence('entities_id_seq')] |
|
177 # for mssql, we need to drop views before tables |
|
178 if hasattr(dbhelper, 'list_views'): |
|
179 cmds += ['DROP VIEW %s;' % name |
|
180 for name in ifilter(_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION, dbhelper.list_views(sqlcursor))] |
|
181 cmds += ['DROP TABLE %s;' % name |
|
182 for name in ifilter(_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION, dbhelper.list_tables(sqlcursor))] |
|
183 return '\n'.join(cmds) |
139 |
184 |
140 class SQLAdapterMixIn(object): |
185 class SQLAdapterMixIn(object): |
141 """Mixin for SQL data sources, getting a connection from a configuration |
186 """Mixin for SQL data sources, getting a connection from a configuration |
142 dictionary and handling connection locking |
187 dictionary and handling connection locking |
143 """ |
188 """ |