/SQLAlchemy-0.7.8/lib/sqlalchemy/dialects/informix/base.py
Python | 596 lines | 562 code | 19 blank | 15 comment | 7 complexity | 19633bc5b9528115d57f30cf9f6d3d0f MD5 | raw file
1# informix/base.py
2# Copyright (C) 2005-2012 the SQLAlchemy authors and contributors <see AUTHORS file>
3# coding: gbk
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
7
8"""Support for the Informix database.
9
10.. note::
11
12 The Informix dialect functions on current SQLAlchemy versions
13 but is not regularly tested, and may have many issues and
14 caveats not currently handled.
15
16"""
17
18
19import datetime
20
21from sqlalchemy import sql, schema, exc, pool, util
22from sqlalchemy.sql import compiler, text
23from sqlalchemy.engine import default, reflection
24from sqlalchemy import types as sqltypes
25
26RESERVED_WORDS = set(
27 ["abs", "absolute", "access", "access_method", "acos", "active", "add",
28 "address", "add_months", "admin", "after", "aggregate", "alignment",
29 "all", "allocate", "all_rows", "alter", "and", "ansi", "any", "append",
30 "array", "as", "asc", "ascii", "asin", "at", "atan", "atan2", "attach",
31 "attributes", "audit", "authentication", "authid", "authorization",
32 "authorized", "auto", "autofree", "auto_reprepare", "auto_stat_mode",
33 "avg", "avoid_execute", "avoid_fact", "avoid_full", "avoid_hash",
34 "avoid_index", "avoid_index_sj", "avoid_multi_index", "avoid_nl",
35 "avoid_star_join", "avoid_subqf", "based", "before", "begin",
36 "between", "bigint", "bigserial", "binary", "bitand", "bitandnot",
37 "bitnot", "bitor", "bitxor", "blob", "blobdir", "boolean", "both",
38 "bound_impl_pdq", "buffered", "builtin", "by", "byte", "cache", "call",
39 "cannothash", "cardinality", "cascade", "case", "cast", "ceil", "char",
40 "character", "character_length", "char_length", "check", "class",
41 "class_origin", "client", "clob", "clobdir", "close", "cluster",
42 "clustersize", "cobol", "codeset", "collation", "collection",
43 "column", "columns", "commit", "committed", "commutator", "component",
44 "components", "concat", "concurrent", "connect", "connection",
45 "connection_name", "connect_by_iscycle", "connect_by_isleaf",
46 "connect_by_rootconst", "constraint", "constraints", "constructor",
47 "context", "continue", "copy", "cos", "costfunc", "count", "crcols",
48 "create", "cross", "current", "current_role", "currval", "cursor",
49 "cycle", "database", "datafiles", "dataskip", "date", "datetime",
50 "day", "dba", "dbdate", "dbinfo", "dbpassword", "dbsecadm",
51 "dbservername", "deallocate", "debug", "debugmode", "debug_env", "dec",
52 "decimal", "declare", "decode", "decrypt_binary", "decrypt_char",
53 "dec_t", "default", "default_role", "deferred", "deferred_prepare",
54 "define", "delay", "delete", "deleting", "delimited", "delimiter",
55 "deluxe", "desc", "describe", "descriptor", "detach", "diagnostics",
56 "directives", "dirty", "disable", "disabled", "disconnect", "disk",
57 "distinct", "distributebinary", "distributesreferences",
58 "distributions", "document", "domain", "donotdistribute", "dormant",
59 "double", "drop", "dtime_t", "each", "elif", "else", "enabled",
60 "encryption", "encrypt_aes", "encrypt_tdes", "end", "enum",
61 "environment", "error", "escape", "exception", "exclusive", "exec",
62 "execute", "executeanywhere", "exemption", "exists", "exit", "exp",
63 "explain", "explicit", "express", "expression", "extdirectives",
64 "extend", "extent", "external", "fact", "false", "far", "fetch",
65 "file", "filetoblob", "filetoclob", "fillfactor", "filtering", "first",
66 "first_rows", "fixchar", "fixed", "float", "floor", "flush", "for",
67 "force", "forced", "force_ddl_exec", "foreach", "foreign", "format",
68 "format_units", "fortran", "found", "fraction", "fragment",
69 "fragments", "free", "from", "full", "function", "general", "get",
70 "gethint", "global", "go", "goto", "grant", "greaterthan",
71 "greaterthanorequal", "group", "handlesnulls", "hash", "having", "hdr",
72 "hex", "high", "hint", "hold", "home", "hour", "idslbacreadarray",
73 "idslbacreadset", "idslbacreadtree", "idslbacrules",
74 "idslbacwritearray", "idslbacwriteset", "idslbacwritetree",
75 "idssecuritylabel", "if", "ifx_auto_reprepare", "ifx_batchedread_table",
76 "ifx_int8_t", "ifx_lo_create_spec_t", "ifx_lo_stat_t", "immediate",
77 "implicit", "implicit_pdq", "in", "inactive", "increment", "index",
78 "indexes", "index_all", "index_sj", "indicator", "informix", "init",
79 "initcap", "inline", "inner", "inout", "insert", "inserting", "instead",
80 "int", "int8", "integ", "integer", "internal", "internallength",
81 "interval", "into", "intrvl_t", "is", "iscanonical", "isolation",
82 "item", "iterator", "java", "join", "keep", "key", "label", "labeleq",
83 "labelge", "labelglb", "labelgt", "labelle", "labellt", "labellub",
84 "labeltostring", "language", "last", "last_day", "leading", "left",
85 "length", "lessthan", "lessthanorequal", "let", "level", "like",
86 "limit", "list", "listing", "load", "local", "locator", "lock", "locks",
87 "locopy", "loc_t", "log", "log10", "logn", "long", "loop", "lotofile",
88 "low", "lower", "lpad", "ltrim", "lvarchar", "matched", "matches",
89 "max", "maxerrors", "maxlen", "maxvalue", "mdy", "median", "medium",
90 "memory", "memory_resident", "merge", "message_length", "message_text",
91 "middle", "min", "minute", "minvalue", "mod", "mode", "moderate",
92 "modify", "module", "money", "month", "months_between", "mounting",
93 "multiset", "multi_index", "name", "nchar", "negator", "new", "next",
94 "nextval", "next_day", "no", "nocache", "nocycle", "nomaxvalue",
95 "nomigrate", "nominvalue", "none", "non_dim", "non_resident", "noorder",
96 "normal", "not", "notemplatearg", "notequal", "null", "nullif",
97 "numeric", "numrows", "numtodsinterval", "numtoyminterval", "nvarchar",
98 "nvl", "octet_length", "of", "off", "old", "on", "online", "only",
99 "opaque", "opclass", "open", "optcompind", "optical", "optimization",
100 "option", "or", "order", "ordered", "out", "outer", "output",
101 "override", "page", "parallelizable", "parameter", "partition",
102 "pascal", "passedbyvalue", "password", "pdqpriority", "percaltl_cos",
103 "pipe", "pli", "pload", "policy", "pow", "power", "precision",
104 "prepare", "previous", "primary", "prior", "private", "privileges",
105 "procedure", "properties", "public", "put", "raise", "range", "raw",
106 "read", "real", "recordend", "references", "referencing", "register",
107 "rejectfile", "relative", "release", "remainder", "rename",
108 "reoptimization", "repeatable", "replace", "replication", "reserve",
109 "resolution", "resource", "restart", "restrict", "resume", "retain",
110 "retainupdatelocks", "return", "returned_sqlstate", "returning",
111 "returns", "reuse", "revoke", "right", "robin", "role", "rollback",
112 "rollforward", "root", "round", "routine", "row", "rowid", "rowids",
113 "rows", "row_count", "rpad", "rtrim", "rule", "sameas", "samples",
114 "sampling", "save", "savepoint", "schema", "scroll", "seclabel_by_comp",
115 "seclabel_by_name", "seclabel_to_char", "second", "secondary",
116 "section", "secured", "security", "selconst", "select", "selecting",
117 "selfunc", "selfuncargs", "sequence", "serial", "serial8",
118 "serializable", "serveruuid", "server_name", "session", "set",
119 "setsessionauth", "share", "short", "siblings", "signed", "sin",
120 "sitename", "size", "skall", "skinhibit", "skip", "skshow",
121 "smallfloat", "smallint", "some", "specific", "sql", "sqlcode",
122 "sqlcontext", "sqlerror", "sqlstate", "sqlwarning", "sqrt",
123 "stability", "stack", "standard", "start", "star_join", "statchange",
124 "statement", "static", "statistics", "statlevel", "status", "stdev",
125 "step", "stop", "storage", "store", "strategies", "string",
126 "stringtolabel", "struct", "style", "subclass_origin", "substr",
127 "substring", "sum", "support", "sync", "synonym", "sysdate",
128 "sysdbclose", "sysdbopen", "system", "sys_connect_by_path", "table",
129 "tables", "tan", "task", "temp", "template", "test", "text", "then",
130 "time", "timeout", "to", "today", "to_char", "to_date",
131 "to_dsinterval", "to_number", "to_yminterval", "trace", "trailing",
132 "transaction", "transition", "tree", "trigger", "triggers", "trim",
133 "true", "trunc", "truncate", "trusted", "type", "typedef", "typeid",
134 "typename", "typeof", "uid", "uncommitted", "under", "union",
135 "unique", "units", "unknown", "unload", "unlock", "unsigned",
136 "update", "updating", "upon", "upper", "usage", "use",
137 "uselastcommitted", "user", "use_hash", "use_nl", "use_subqf",
138 "using", "value", "values", "var", "varchar", "variable", "variance",
139 "variant", "varying", "vercols", "view", "violations", "void",
140 "volatile", "wait", "warning", "weekday", "when", "whenever", "where",
141 "while", "with", "without", "work", "write", "writedown", "writeup",
142 "xadatasource", "xid", "xload", "xunload", "year"
143 ])
144
145class InfoDateTime(sqltypes.DateTime):
146 def bind_processor(self, dialect):
147 def process(value):
148 if value is not None:
149 if value.microsecond:
150 value = value.replace(microsecond=0)
151 return value
152 return process
153
154class InfoTime(sqltypes.Time):
155 def bind_processor(self, dialect):
156 def process(value):
157 if value is not None:
158 if value.microsecond:
159 value = value.replace(microsecond=0)
160 return value
161 return process
162
163 def result_processor(self, dialect, coltype):
164 def process(value):
165 if isinstance(value, datetime.datetime):
166 return value.time()
167 else:
168 return value
169 return process
170
171colspecs = {
172 sqltypes.DateTime : InfoDateTime,
173 sqltypes.TIMESTAMP: InfoDateTime,
174 sqltypes.Time: InfoTime,
175}
176
177
178ischema_names = {
179 0 : sqltypes.CHAR, # CHAR
180 1 : sqltypes.SMALLINT, # SMALLINT
181 2 : sqltypes.INTEGER, # INT
182 3 : sqltypes.FLOAT, # Float
183 3 : sqltypes.Float, # SmallFloat
184 5 : sqltypes.DECIMAL, # DECIMAL
185 6 : sqltypes.Integer, # Serial
186 7 : sqltypes.DATE, # DATE
187 8 : sqltypes.Numeric, # MONEY
188 10 : sqltypes.DATETIME, # DATETIME
189 11 : sqltypes.LargeBinary, # BYTE
190 12 : sqltypes.TEXT, # TEXT
191 13 : sqltypes.VARCHAR, # VARCHAR
192 15 : sqltypes.NCHAR, # NCHAR
193 16 : sqltypes.NVARCHAR, # NVARCHAR
194 17 : sqltypes.Integer, # INT8
195 18 : sqltypes.Integer, # Serial8
196 43 : sqltypes.String, # LVARCHAR
197 -1 : sqltypes.BLOB, # BLOB
198 -1 : sqltypes.CLOB, # CLOB
199}
200
201
202class InfoTypeCompiler(compiler.GenericTypeCompiler):
203 def visit_DATETIME(self, type_):
204 return "DATETIME YEAR TO SECOND"
205
206 def visit_TIME(self, type_):
207 return "DATETIME HOUR TO SECOND"
208
209 def visit_TIMESTAMP(self, type_):
210 return "DATETIME YEAR TO SECOND"
211
212 def visit_large_binary(self, type_):
213 return "BYTE"
214
215 def visit_boolean(self, type_):
216 return "SMALLINT"
217
218class InfoSQLCompiler(compiler.SQLCompiler):
219 def default_from(self):
220 return " from systables where tabname = 'systables' "
221
222 def get_select_precolumns(self, select):
223 s = ""
224 if select._offset:
225 s += "SKIP %s " % select._offset
226 if select._limit:
227 s += "FIRST %s " % select._limit
228 s += select._distinct and "DISTINCT " or ""
229 return s
230
231 def visit_select(self, select, asfrom=False, parens=True, **kw):
232 text = compiler.SQLCompiler.visit_select(self, select, asfrom, parens, **kw)
233 if asfrom and parens and self.dialect.server_version_info < (11,):
234 #assuming that 11 version doesn't need this, not tested
235 return "table(multiset" + text + ")"
236 else:
237 return text
238
239 def limit_clause(self, select):
240 return ""
241
242 def visit_function(self, func, **kw):
243 if func.name.lower() == 'current_date':
244 return "today"
245 elif func.name.lower() == 'current_time':
246 return "CURRENT HOUR TO SECOND"
247 elif func.name.lower() in ('current_timestamp', 'now'):
248 return "CURRENT YEAR TO SECOND"
249 else:
250 return compiler.SQLCompiler.visit_function(self, func, **kw)
251
252 def visit_mod(self, binary, **kw):
253 return "MOD(%s, %s)" % (self.process(binary.left), self.process(binary.right))
254
255
256class InfoDDLCompiler(compiler.DDLCompiler):
257
258 def visit_add_constraint(self, create):
259 preparer = self.preparer
260 return "ALTER TABLE %s ADD CONSTRAINT %s" % (
261 self.preparer.format_table(create.element.table),
262 self.process(create.element)
263 )
264
265 def get_column_specification(self, column, **kw):
266 colspec = self.preparer.format_column(column)
267 first = None
268 if column.primary_key and column.autoincrement:
269 try:
270 first = [c for c in column.table.primary_key.columns
271 if (c.autoincrement and
272 isinstance(c.type, sqltypes.Integer) and
273 not c.foreign_keys)].pop(0)
274 except IndexError:
275 pass
276
277 if column is first:
278 colspec += " SERIAL"
279 else:
280 colspec += " " + self.dialect.type_compiler.process(column.type)
281 default = self.get_column_default_string(column)
282 if default is not None:
283 colspec += " DEFAULT " + default
284
285 if not column.nullable:
286 colspec += " NOT NULL"
287
288 return colspec
289
290 def get_column_default_string(self, column):
291 if (isinstance(column.server_default, schema.DefaultClause) and
292 isinstance(column.server_default.arg, basestring)):
293 if isinstance(column.type, (sqltypes.Integer, sqltypes.Numeric)):
294 return self.sql_compiler.process(text(column.server_default.arg))
295
296 return super(InfoDDLCompiler, self).get_column_default_string(column)
297
298 ### Informix wants the constraint name at the end, hence this ist c&p from sql/compiler.py
299 def visit_primary_key_constraint(self, constraint):
300 if len(constraint) == 0:
301 return ''
302 text = "PRIMARY KEY "
303 text += "(%s)" % ', '.join(self.preparer.quote(c.name, c.quote)
304 for c in constraint)
305 text += self.define_constraint_deferrability(constraint)
306
307 if constraint.name is not None:
308 text += " CONSTRAINT %s" % self.preparer.format_constraint(constraint)
309 return text
310
311 def visit_foreign_key_constraint(self, constraint):
312 preparer = self.dialect.identifier_preparer
313 remote_table = list(constraint._elements.values())[0].column.table
314 text = "FOREIGN KEY (%s) REFERENCES %s (%s)" % (
315 ', '.join(preparer.quote(f.parent.name, f.parent.quote)
316 for f in constraint._elements.values()),
317 preparer.format_table(remote_table),
318 ', '.join(preparer.quote(f.column.name, f.column.quote)
319 for f in constraint._elements.values())
320 )
321 text += self.define_constraint_cascades(constraint)
322 text += self.define_constraint_deferrability(constraint)
323
324 if constraint.name is not None:
325 text += " CONSTRAINT %s " % \
326 preparer.format_constraint(constraint)
327 return text
328
329 def visit_unique_constraint(self, constraint):
330 text = "UNIQUE (%s)" % (', '.join(self.preparer.quote(c.name, c.quote) for c in constraint))
331 text += self.define_constraint_deferrability(constraint)
332
333 if constraint.name is not None:
334 text += "CONSTRAINT %s " % self.preparer.format_constraint(constraint)
335 return text
336
337class InformixIdentifierPreparer(compiler.IdentifierPreparer):
338
339 reserved_words = RESERVED_WORDS
340
341
342class InformixDialect(default.DefaultDialect):
343 name = 'informix'
344
345 max_identifier_length = 128 # adjusts at runtime based on server version
346
347 type_compiler = InfoTypeCompiler
348 statement_compiler = InfoSQLCompiler
349 ddl_compiler = InfoDDLCompiler
350 colspecs = colspecs
351 ischema_names = ischema_names
352 preparer = InformixIdentifierPreparer
353 default_paramstyle = 'qmark'
354
355 def __init__(self, has_transactions=True, *args, **kwargs):
356 self.has_transactions = has_transactions
357 default.DefaultDialect.__init__(self, *args, **kwargs)
358
359 def initialize(self, connection):
360 super(InformixDialect, self).initialize(connection)
361
362 # http://www.querix.com/support/knowledge-base/error_number_message/error_200
363 if self.server_version_info < (9, 2):
364 self.max_identifier_length = 18
365 else:
366 self.max_identifier_length = 128
367
368 def do_begin(self, connection):
369 cu = connection.cursor()
370 cu.execute('SET LOCK MODE TO WAIT')
371 if self.has_transactions:
372 cu.execute('SET ISOLATION TO REPEATABLE READ')
373
374 def do_commit(self, connection):
375 if self.has_transactions:
376 connection.commit()
377
378 def do_rollback(self, connection):
379 if self.has_transactions:
380 connection.rollback()
381
382 def _get_table_names(self, connection, schema, type, **kw):
383 schema = schema or self.default_schema_name
384 s = "select tabname, owner from systables where owner=? and tabtype=?"
385 return [row[0] for row in connection.execute(s, schema, type)]
386
387 @reflection.cache
388 def get_table_names(self, connection, schema=None, **kw):
389 return self._get_table_names(connection, schema, 'T', **kw)
390
391 @reflection.cache
392 def get_view_names(self, connection, schema=None, **kw):
393 return self._get_table_names(connection, schema, 'V', **kw)
394
395 @reflection.cache
396 def get_schema_names(self, connection, **kw):
397 s = "select owner from systables"
398 return [row[0] for row in connection.execute(s)]
399
400 def has_table(self, connection, table_name, schema=None):
401 schema = schema or self.default_schema_name
402 cursor = connection.execute(
403 """select tabname from systables where tabname=? and owner=?""",
404 table_name, schema)
405 return cursor.first() is not None
406
407 @reflection.cache
408 def get_columns(self, connection, table_name, schema=None, **kw):
409 schema = schema or self.default_schema_name
410 c = connection.execute(
411 """select colname, coltype, collength, t3.default, t1.colno from
412 syscolumns as t1 , systables as t2 , OUTER sysdefaults as t3
413 where t1.tabid = t2.tabid and t2.tabname=? and t2.owner=?
414 and t3.tabid = t2.tabid and t3.colno = t1.colno
415 order by t1.colno""", table_name, schema)
416
417 primary_cols = self.get_primary_keys(connection, table_name, schema, **kw)
418
419 columns = []
420 rows = c.fetchall()
421 for name, colattr, collength, default, colno in rows:
422 name = name.lower()
423
424 autoincrement = False
425 primary_key = False
426
427 if name in primary_cols:
428 primary_key = True
429
430 # in 7.31, coltype = 0x000
431 # ^^-- column type
432 # ^-- 1 not null, 0 null
433 not_nullable, coltype = divmod(colattr, 256)
434 if coltype not in (0, 13) and default:
435 default = default.split()[-1]
436
437 if coltype == 6: # Serial, mark as autoincrement
438 autoincrement = True
439
440 if coltype == 0 or coltype == 13: # char, varchar
441 coltype = ischema_names[coltype](collength)
442 if default:
443 default = "'%s'" % default
444 elif coltype == 5: # decimal
445 precision, scale = (collength & 0xFF00) >> 8, collength & 0xFF
446 if scale == 255:
447 scale = 0
448 coltype = sqltypes.Numeric(precision, scale)
449 else:
450 try:
451 coltype = ischema_names[coltype]
452 except KeyError:
453 util.warn("Did not recognize type '%s' of column '%s'" %
454 (coltype, name))
455 coltype = sqltypes.NULLTYPE
456
457 column_info = dict(name=name, type=coltype, nullable=not not_nullable,
458 default=default, autoincrement=autoincrement,
459 primary_key=primary_key)
460 columns.append(column_info)
461 return columns
462
463 @reflection.cache
464 def get_foreign_keys(self, connection, table_name, schema=None, **kw):
465 schema_sel = schema or self.default_schema_name
466 c = connection.execute(
467 """select t1.constrname as cons_name,
468 t4.colname as local_column, t7.tabname as remote_table,
469 t6.colname as remote_column, t7.owner as remote_owner
470 from sysconstraints as t1 , systables as t2 ,
471 sysindexes as t3 , syscolumns as t4 ,
472 sysreferences as t5 , syscolumns as t6 , systables as t7 ,
473 sysconstraints as t8 , sysindexes as t9
474 where t1.tabid = t2.tabid and t2.tabname=? and t2.owner=? and t1.constrtype = 'R'
475 and t3.tabid = t2.tabid and t3.idxname = t1.idxname
476 and t4.tabid = t2.tabid and t4.colno in (t3.part1, t3.part2, t3.part3,
477 t3.part4, t3.part5, t3.part6, t3.part7, t3.part8, t3.part9, t3.part10,
478 t3.part11, t3.part11, t3.part12, t3.part13, t3.part4, t3.part15, t3.part16)
479 and t5.constrid = t1.constrid and t8.constrid = t5.primary
480 and t6.tabid = t5.ptabid and t6.colno in (t9.part1, t9.part2, t9.part3,
481 t9.part4, t9.part5, t9.part6, t9.part7, t9.part8, t9.part9, t9.part10,
482 t9.part11, t9.part11, t9.part12, t9.part13, t9.part4, t9.part15, t9.part16) and t9.idxname =
483 t8.idxname
484 and t7.tabid = t5.ptabid""", table_name, schema_sel)
485
486
487 def fkey_rec():
488 return {
489 'name' : None,
490 'constrained_columns' : [],
491 'referred_schema' : None,
492 'referred_table' : None,
493 'referred_columns' : []
494 }
495
496 fkeys = util.defaultdict(fkey_rec)
497
498 rows = c.fetchall()
499 for cons_name, local_column, \
500 remote_table, remote_column, remote_owner in rows:
501
502 rec = fkeys[cons_name]
503 rec['name'] = cons_name
504 local_cols, remote_cols = \
505 rec['constrained_columns'], rec['referred_columns']
506
507 if not rec['referred_table']:
508 rec['referred_table'] = remote_table
509 if schema is not None:
510 rec['referred_schema'] = remote_owner
511
512 if local_column not in local_cols:
513 local_cols.append(local_column)
514 if remote_column not in remote_cols:
515 remote_cols.append(remote_column)
516
517 return fkeys.values()
518
519 @reflection.cache
520 def get_primary_keys(self, connection, table_name, schema=None, **kw):
521 schema = schema or self.default_schema_name
522
523 # Select the column positions from sysindexes for sysconstraints
524 data = connection.execute(
525 """select t2.*
526 from systables as t1, sysindexes as t2, sysconstraints as t3
527 where t1.tabid=t2.tabid and t1.tabname=? and t1.owner=?
528 and t2.idxname=t3.idxname and t3.constrtype='P'""",
529 table_name, schema
530 ).fetchall()
531
532 colpositions = set()
533
534 for row in data:
535 colpos = set([getattr(row, 'part%d' % x) for x in range(1,16)])
536 colpositions |= colpos
537
538 if not len(colpositions):
539 return []
540
541 # Select the column names using the columnpositions
542 # TODO: Maybe cache a bit of those col infos (eg select all colnames for one table)
543 place_holder = ','.join('?'*len(colpositions))
544 c = connection.execute(
545 """select t1.colname
546 from syscolumns as t1, systables as t2
547 where t2.tabname=? and t1.tabid = t2.tabid and
548 t1.colno in (%s)""" % place_holder,
549 table_name, *colpositions
550 ).fetchall()
551
552 return reduce(lambda x,y: list(x)+list(y), c, [])
553
554 @reflection.cache
555 def get_indexes(self, connection, table_name, schema, **kw):
556 # TODO: schema...
557 c = connection.execute(
558 """select t1.*
559 from sysindexes as t1 , systables as t2
560 where t1.tabid = t2.tabid and t2.tabname=?""",
561 table_name)
562
563 indexes = []
564 for row in c.fetchall():
565 colnames = [getattr(row, 'part%d' % x) for x in range(1,16)]
566 colnames = [x for x in colnames if x]
567 place_holder = ','.join('?'*len(colnames))
568 c = connection.execute(
569 """select t1.colname
570 from syscolumns as t1, systables as t2
571 where t2.tabname=? and t1.tabid = t2.tabid and
572 t1.colno in (%s)""" % place_holder,
573 table_name, *colnames
574 ).fetchall()
575 c = reduce(lambda x,y: list(x)+list(y), c, [])
576 indexes.append({
577 'name': row.idxname,
578 'unique': row.idxtype.lower() == 'u',
579 'column_names': c
580 })
581 return indexes
582
583 @reflection.cache
584 def get_view_definition(self, connection, view_name, schema=None, **kw):
585 schema = schema or self.default_schema_name
586 c = connection.execute(
587 """select t1.viewtext
588 from sysviews as t1 , systables as t2
589 where t1.tabid=t2.tabid and t2.tabname=?
590 and t2.owner=? order by seqno""",
591 view_name, schema).fetchall()
592
593 return ''.join([row[0] for row in c])
594
595 def _get_default_schema_name(self, connection):
596 return connection.execute('select CURRENT_ROLE from systables').scalar()