root/galaxy-central/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/databases/oracle.py

リビジョン 3, 36.3 KB (コミッタ: kohda, 14 年 前)

Install Unix tools  http://hannonlab.cshl.edu/galaxy_unix_tools/galaxy.html

行番号 
1# oracle.py
2# Copyright (C) 2005, 2006, 2007, 2008, 2009 Michael Bayer mike_mp@zzzcomputing.com
3#
4# This module is part of SQLAlchemy and is released under
5# the MIT License: http://www.opensource.org/licenses/mit-license.php
6"""Support for the Oracle database.
7
8Oracle version 8 through current (11g at the time of this writing) are supported.
9
10Driver
11------
12
13The Oracle dialect uses the cx_oracle driver, available at
14http://cx-oracle.sourceforge.net/ .   The dialect has several behaviors
15which are specifically tailored towards compatibility with this module.
16
17Connecting
18----------
19
20Connecting with create_engine() uses the standard URL approach of
21``oracle://user:pass@host:port/dbname[?key=value&key=value...]``.  If dbname is present, the
22host, port, and dbname tokens are converted to a TNS name using the cx_oracle
23:func:`makedsn()` function.  Otherwise, the host token is taken directly as a TNS name.
24
25Additional arguments which may be specified either as query string arguments on the
26URL, or as keyword arguments to :func:`~sqlalchemy.create_engine()` are:
27
28* *allow_twophase* - enable two-phase transactions.  Defaults to ``True``.
29
30* *auto_convert_lobs* - defaults to True, see the section on LOB objects.
31
32* *auto_setinputsizes* - the cx_oracle.setinputsizes() call is issued for all bind parameters.
33  This is required for LOB datatypes but can be disabled to reduce overhead.  Defaults
34  to ``True``.
35
36* *mode* - This is given the string value of SYSDBA or SYSOPER, or alternatively an
37  integer value.  This value is only available as a URL query string argument.
38
39* *threaded* - enable multithreaded access to cx_oracle connections.  Defaults
40  to ``True``.  Note that this is the opposite default of cx_oracle itself.
41
42* *use_ansi* - Use ANSI JOIN constructs (see the section on Oracle 8).  Defaults
43  to ``True``.  If ``False``, Oracle-8 compatible constructs are used for joins.
44
45* *optimize_limits* - defaults to ``False``. see the section on LIMIT/OFFSET.
46
47Auto Increment Behavior
48-----------------------
49
50SQLAlchemy Table objects which include integer primary keys are usually assumed to have
51"autoincrementing" behavior, meaning they can generate their own primary key values upon
52INSERT.  Since Oracle has no "autoincrement" feature, SQLAlchemy relies upon sequences
53to produce these values.   With the Oracle dialect, *a sequence must always be explicitly
54specified to enable autoincrement*.  This is divergent with the majority of documentation
55examples which assume the usage of an autoincrement-capable database.   To specify sequences,
56use the sqlalchemy.schema.Sequence object which is passed to a Column construct::
57
58  t = Table('mytable', metadata,
59        Column('id', Integer, Sequence('id_seq'), primary_key=True),
60        Column(...), ...
61  )
62
63This step is also required when using table reflection, i.e. autoload=True::
64
65  t = Table('mytable', metadata,
66        Column('id', Integer, Sequence('id_seq'), primary_key=True),
67        autoload=True
68  )
69
70LOB Objects
71-----------
72
73cx_oracle presents some challenges when fetching LOB objects.  A LOB object in a result set
74is presented by cx_oracle as a cx_oracle.LOB object which has a read() method.  By default,
75SQLAlchemy converts these LOB objects into Python strings.  This is for two reasons.  First,
76the LOB object requires an active cursor association, meaning if you were to fetch many rows
77at once such that cx_oracle had to go back to the database and fetch a new batch of rows,
78the LOB objects in the already-fetched rows are now unreadable and will raise an error.
79SQLA "pre-reads" all LOBs so that their data is fetched before further rows are read. 
80The size of a "batch of rows" is controlled by the cursor.arraysize value, which SQLAlchemy
81defaults to 50 (cx_oracle normally defaults this to one). 
82
83Secondly, the LOB object is not a standard DBAPI return value so SQLAlchemy seeks to
84"normalize" the results to look more like other DBAPIs.
85
86The conversion of LOB objects by this dialect is unique in SQLAlchemy in that it takes place
87for all statement executions, even plain string-based statements for which SQLA has no awareness
88of result typing.  This is so that calls like fetchmany() and fetchall() can work in all cases
89without raising cursor errors.  The conversion of LOB in all cases, as well as the "prefetch"
90of LOB objects, can be disabled using auto_convert_lobs=False. 
91
92LIMIT/OFFSET Support
93--------------------
94
95Oracle has no support for the LIMIT or OFFSET keywords.  Whereas previous versions of SQLAlchemy
96used the "ROW NUMBER OVER..." construct to simulate LIMIT/OFFSET, SQLAlchemy 0.5 now uses
97a wrapped subquery approach in conjunction with ROWNUM.  The exact methodology is taken from
98http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html .  Note that the
99"FIRST ROWS()" optimization keyword mentioned is not used by default, as the user community felt
100this was stepping into the bounds of optimization that is better left on the DBA side, but this
101prefix can be added by enabling the optimize_limits=True flag on create_engine().
102
103Two Phase Transaction Support
104-----------------------------
105
106Two Phase transactions are implemented using XA transactions.  Success has been reported of them
107working successfully but this should be regarded as an experimental feature.
108
109Oracle 8 Compatibility
110----------------------
111
112When using Oracle 8, a "use_ansi=False" flag is available which converts all
113JOIN phrases into the WHERE clause, and in the case of LEFT OUTER JOIN
114makes use of Oracle's (+) operator.
115
116Synonym/DBLINK Reflection
117-------------------------
118
119When using reflection with Table objects, the dialect can optionally search for tables
120indicated by synonyms that reference DBLINK-ed tables by passing the flag
121oracle_resolve_synonyms=True as a keyword argument to the Table construct.  If DBLINK
122is not in use this flag should be left off.
123
124"""
125
126import datetime, random, re
127
128from sqlalchemy import util, sql, schema, log
129from sqlalchemy.engine import default, base
130from sqlalchemy.sql import compiler, visitors, expression
131from sqlalchemy.sql import operators as sql_operators, functions as sql_functions
132from sqlalchemy import types as sqltypes
133
134
135class OracleNumeric(sqltypes.Numeric):
136    def get_col_spec(self):
137        if self.precision is None:
138            return "NUMERIC"
139        else:
140            return "NUMERIC(%(precision)s, %(scale)s)" % {'precision': self.precision, 'scale' : self.scale}
141
142class OracleInteger(sqltypes.Integer):
143    def get_col_spec(self):
144        return "INTEGER"
145
146class OracleSmallInteger(sqltypes.Smallinteger):
147    def get_col_spec(self):
148        return "SMALLINT"
149
150class OracleDate(sqltypes.Date):
151    def get_col_spec(self):
152        return "DATE"
153    def bind_processor(self, dialect):
154        return None
155
156    def result_processor(self, dialect):
157        def process(value):
158            if not isinstance(value, datetime.datetime):
159                return value
160            else:
161                return value.date()
162        return process
163
164class OracleDateTime(sqltypes.DateTime):
165    def get_col_spec(self):
166        return "DATE"
167
168    def result_processor(self, dialect):
169        def process(value):
170            if value is None or isinstance(value, datetime.datetime):
171                return value
172            else:
173                # convert cx_oracle datetime object returned pre-python 2.4
174                return datetime.datetime(value.year, value.month,
175                    value.day,value.hour, value.minute, value.second)
176        return process
177
178# Note:
179# Oracle DATE == DATETIME
180# Oracle does not allow milliseconds in DATE
181# Oracle does not support TIME columns
182
183# only if cx_oracle contains TIMESTAMP
184class OracleTimestamp(sqltypes.TIMESTAMP):
185    def get_col_spec(self):
186        return "TIMESTAMP"
187
188    def get_dbapi_type(self, dialect):
189        return dialect.TIMESTAMP
190
191    def result_processor(self, dialect):
192        def process(value):
193            if value is None or isinstance(value, datetime.datetime):
194                return value
195            else:
196                # convert cx_oracle datetime object returned pre-python 2.4
197                return datetime.datetime(value.year, value.month,
198                    value.day,value.hour, value.minute, value.second)
199        return process
200
201class OracleString(sqltypes.String):
202    def get_col_spec(self):
203        return "VARCHAR(%(length)s)" % {'length' : self.length}
204
205class OracleNVarchar(sqltypes.Unicode, OracleString):
206    def get_col_spec(self):
207        return "NVARCHAR2(%(length)s)" % {'length' : self.length}
208
209class OracleText(sqltypes.Text):
210    def get_dbapi_type(self, dbapi):
211        return dbapi.CLOB
212
213    def get_col_spec(self):
214        return "CLOB"
215
216    def result_processor(self, dialect):
217        super_process = super(OracleText, self).result_processor(dialect)
218        if not dialect.auto_convert_lobs:
219            return super_process
220        lob = dialect.dbapi.LOB
221        def process(value):
222            if isinstance(value, lob):
223                if super_process:
224                    return super_process(value.read())
225                else:
226                    return value.read()
227            else:
228                if super_process:
229                    return super_process(value)
230                else:
231                    return value
232        return process
233
234
235class OracleChar(sqltypes.CHAR):
236    def get_col_spec(self):
237        return "CHAR(%(length)s)" % {'length' : self.length}
238
239class OracleBinary(sqltypes.Binary):
240    def get_dbapi_type(self, dbapi):
241        return dbapi.BLOB
242
243    def get_col_spec(self):
244        return "BLOB"
245
246    def bind_processor(self, dialect):
247        return None
248
249    def result_processor(self, dialect):
250        if not dialect.auto_convert_lobs:
251            return None
252        lob = dialect.dbapi.LOB
253        def process(value):
254            if isinstance(value, lob):
255                return value.read()
256            else:
257                return value
258        return process
259
260class OracleRaw(OracleBinary):
261    def get_col_spec(self):
262        return "RAW(%(length)s)" % {'length' : self.length}
263
264class OracleBoolean(sqltypes.Boolean):
265    def get_col_spec(self):
266        return "SMALLINT"
267
268    def result_processor(self, dialect):
269        def process(value):
270            if value is None:
271                return None
272            return value and True or False
273        return process
274
275    def bind_processor(self, dialect):
276        def process(value):
277            if value is True:
278                return 1
279            elif value is False:
280                return 0
281            elif value is None:
282                return None
283            else:
284                return value and True or False
285        return process
286
287colspecs = {
288    sqltypes.Integer : OracleInteger,
289    sqltypes.Smallinteger : OracleSmallInteger,
290    sqltypes.Numeric : OracleNumeric,
291    sqltypes.Float : OracleNumeric,
292    sqltypes.DateTime : OracleDateTime,
293    sqltypes.Date : OracleDate,
294    sqltypes.String : OracleString,
295    sqltypes.Binary : OracleBinary,
296    sqltypes.Boolean : OracleBoolean,
297    sqltypes.Text : OracleText,
298    sqltypes.TIMESTAMP : OracleTimestamp,
299    sqltypes.CHAR: OracleChar,
300}
301
302ischema_names = {
303    'VARCHAR2' : OracleString,
304    'NVARCHAR2' : OracleNVarchar,
305    'CHAR' : OracleString,
306    'DATE' : OracleDateTime,
307    'DATETIME' : OracleDateTime,
308    'NUMBER' : OracleNumeric,
309    'BLOB' : OracleBinary,
310    'BFILE' : OracleBinary,
311    'CLOB' : OracleText,
312    'TIMESTAMP' : OracleTimestamp,
313    'RAW' : OracleRaw,
314    'FLOAT' : OracleNumeric,
315    'DOUBLE PRECISION' : OracleNumeric,
316    'LONG' : OracleText,
317}
318
319class OracleExecutionContext(default.DefaultExecutionContext):
320    def pre_exec(self):
321        super(OracleExecutionContext, self).pre_exec()
322        if self.dialect.auto_setinputsizes:
323            self.set_input_sizes()
324        if self.compiled_parameters is not None and len(self.compiled_parameters) == 1:
325            for key in self.compiled.binds:
326                bindparam = self.compiled.binds[key]
327                name = self.compiled.bind_names[bindparam]
328                value = self.compiled_parameters[0][name]
329                if bindparam.isoutparam:
330                    dbtype = bindparam.type.dialect_impl(self.dialect).get_dbapi_type(self.dialect.dbapi)
331                    if not hasattr(self, 'out_parameters'):
332                        self.out_parameters = {}
333                    self.out_parameters[name] = self.cursor.var(dbtype)
334                    self.parameters[0][name] = self.out_parameters[name]
335
336    def create_cursor(self):
337        c = self._connection.connection.cursor()
338        if self.dialect.arraysize:
339            c.cursor.arraysize = self.dialect.arraysize
340        return c
341
342    def get_result_proxy(self):
343        if hasattr(self, 'out_parameters'):
344            if self.compiled_parameters is not None and len(self.compiled_parameters) == 1:
345                for bind, name in self.compiled.bind_names.iteritems():
346                    if name in self.out_parameters:
347                        type = bind.type
348                        result_processor = type.dialect_impl(self.dialect).result_processor(self.dialect)
349                        if result_processor is not None:
350                            self.out_parameters[name] = result_processor(self.out_parameters[name].getvalue())
351                        else:
352                            self.out_parameters[name] = self.out_parameters[name].getvalue()
353            else:
354                for k in self.out_parameters:
355                    self.out_parameters[k] = self.out_parameters[k].getvalue()
356
357        if self.cursor.description is not None:
358            for column in self.cursor.description:
359                type_code = column[1]
360                if type_code in self.dialect.ORACLE_BINARY_TYPES:
361                    return base.BufferedColumnResultProxy(self)
362
363        return base.ResultProxy(self)
364
365class OracleDialect(default.DefaultDialect):
366    name = 'oracle'
367    supports_alter = True
368    supports_unicode_statements = False
369    max_identifier_length = 30
370    supports_sane_rowcount = True
371    supports_sane_multi_rowcount = False
372    preexecute_pk_sequences = True
373    supports_pk_autoincrement = False
374    default_paramstyle = 'named'
375
376    def __init__(self, use_ansi=True, auto_setinputsizes=True, auto_convert_lobs=True, threaded=True, allow_twophase=True, optimize_limits=False, arraysize=50, **kwargs):
377        default.DefaultDialect.__init__(self, **kwargs)
378        self.use_ansi = use_ansi
379        self.threaded = threaded
380        self.arraysize = arraysize
381        self.allow_twophase = allow_twophase
382        self.optimize_limits = optimize_limits
383        self.supports_timestamp = self.dbapi is None or hasattr(self.dbapi, 'TIMESTAMP' )
384        self.auto_setinputsizes = auto_setinputsizes
385        self.auto_convert_lobs = auto_convert_lobs
386        if self.dbapi is None or not self.auto_convert_lobs or not 'CLOB' in self.dbapi.__dict__:
387            self.dbapi_type_map = {}
388            self.ORACLE_BINARY_TYPES = []
389        else:
390            # only use this for LOB objects.  using it for strings, dates
391            # etc. leads to a little too much magic, reflection doesn't know if it should
392            # expect encoded strings or unicodes, etc.
393            self.dbapi_type_map = {
394                self.dbapi.CLOB: OracleText(),
395                self.dbapi.BLOB: OracleBinary(),
396                self.dbapi.BINARY: OracleRaw(),
397            }
398            self.ORACLE_BINARY_TYPES = [getattr(self.dbapi, k) for k in ["BFILE", "CLOB", "NCLOB", "BLOB"] if hasattr(self.dbapi, k)]
399
400    def dbapi(cls):
401        import cx_Oracle
402        return cx_Oracle
403    dbapi = classmethod(dbapi)
404
405    def create_connect_args(self, url):
406        dialect_opts = dict(url.query)
407        for opt in ('use_ansi', 'auto_setinputsizes', 'auto_convert_lobs',
408                    'threaded', 'allow_twophase'):
409            if opt in dialect_opts:
410                util.coerce_kw_type(dialect_opts, opt, bool)
411                setattr(self, opt, dialect_opts[opt])
412
413        if url.database:
414            # if we have a database, then we have a remote host
415            port = url.port
416            if port:
417                port = int(port)
418            else:
419                port = 1521
420            dsn = self.dbapi.makedsn(url.host, port, url.database)
421        else:
422            # we have a local tnsname
423            dsn = url.host
424
425        opts = dict(
426            user=url.username,
427            password=url.password,
428            dsn=dsn,
429            threaded=self.threaded,
430            twophase=self.allow_twophase,
431            )
432        if 'mode' in url.query:
433            opts['mode'] = url.query['mode']
434            if isinstance(opts['mode'], basestring):
435                mode = opts['mode'].upper()
436                if mode == 'SYSDBA':
437                    opts['mode'] = self.dbapi.SYSDBA
438                elif mode == 'SYSOPER':
439                    opts['mode'] = self.dbapi.SYSOPER
440                else:
441                    util.coerce_kw_type(opts, 'mode', int)
442        # Can't set 'handle' or 'pool' via URL query args, use connect_args
443
444        return ([], opts)
445
446    def is_disconnect(self, e):
447        if isinstance(e, self.dbapi.InterfaceError):
448            return "not connected" in str(e)
449        else:
450            return "ORA-03114" in str(e) or "ORA-03113" in str(e)
451
452    def type_descriptor(self, typeobj):
453        return sqltypes.adapt_type(typeobj, colspecs)
454
455    def create_xid(self):
456        """create a two-phase transaction ID.
457
458        this id will be passed to do_begin_twophase(), do_rollback_twophase(),
459        do_commit_twophase().  its format is unspecified."""
460
461        id = random.randint(0, 2 ** 128)
462        return (0x1234, "%032x" % id, "%032x" % 9)
463       
464    def do_release_savepoint(self, connection, name):
465        # Oracle does not support RELEASE SAVEPOINT
466        pass
467
468    def do_begin_twophase(self, connection, xid):
469        connection.connection.begin(*xid)
470
471    def do_prepare_twophase(self, connection, xid):
472        connection.connection.prepare()
473
474    def do_rollback_twophase(self, connection, xid, is_prepared=True, recover=False):
475        self.do_rollback(connection.connection)
476
477    def do_commit_twophase(self, connection, xid, is_prepared=True, recover=False):
478        self.do_commit(connection.connection)
479
480    def do_recover_twophase(self, connection):
481        pass
482
483    def has_table(self, connection, table_name, schema=None):
484        if not schema:
485            schema = self.get_default_schema_name(connection)
486        cursor = connection.execute("""select table_name from all_tables where table_name=:name and owner=:schema_name""", {'name':self._denormalize_name(table_name), 'schema_name':self._denormalize_name(schema)})
487        return cursor.fetchone() is not None
488
489    def has_sequence(self, connection, sequence_name, schema=None):
490        if not schema:
491            schema = self.get_default_schema_name(connection)
492        cursor = connection.execute("""select sequence_name from all_sequences where sequence_name=:name and sequence_owner=:schema_name""", {'name':self._denormalize_name(sequence_name), 'schema_name':self._denormalize_name(schema)})
493        return cursor.fetchone() is not None
494
495    def _normalize_name(self, name):
496        if name is None:
497            return None
498        elif name.upper() == name and not self.identifier_preparer._requires_quotes(name.lower().decode(self.encoding)):
499            return name.lower().decode(self.encoding)
500        else:
501            return name.decode(self.encoding)
502
503    def _denormalize_name(self, name):
504        if name is None:
505            return None
506        elif name.lower() == name and not self.identifier_preparer._requires_quotes(name.lower()):
507            return name.upper().encode(self.encoding)
508        else:
509            return name.encode(self.encoding)
510
511    def get_default_schema_name(self, connection):
512        return self._normalize_name(connection.execute('SELECT USER FROM DUAL').scalar())
513    get_default_schema_name = base.connection_memoize(
514        ('dialect', 'default_schema_name'))(get_default_schema_name)
515
516    def table_names(self, connection, schema):
517        # note that table_names() isnt loading DBLINKed or synonym'ed tables
518        if schema is None:
519            s = "select table_name from all_tables where nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM', 'SYSAUX')"
520            cursor = connection.execute(s)
521        else:
522            s = "select table_name from all_tables where nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM','SYSAUX') AND OWNER = :owner"
523            cursor = connection.execute(s, {'owner': self._denormalize_name(schema)})
524        return [self._normalize_name(row[0]) for row in cursor]
525
526    def _resolve_synonym(self, connection, desired_owner=None, desired_synonym=None, desired_table=None):
527        """search for a local synonym matching the given desired owner/name.
528
529        if desired_owner is None, attempts to locate a distinct owner.
530
531        returns the actual name, owner, dblink name, and synonym name if found.
532        """
533
534        sql = """select OWNER, TABLE_OWNER, TABLE_NAME, DB_LINK, SYNONYM_NAME
535                   from   ALL_SYNONYMS WHERE """
536
537        clauses = []
538        params = {}
539        if desired_synonym:
540            clauses.append("SYNONYM_NAME=:synonym_name")
541            params['synonym_name'] = desired_synonym
542        if desired_owner:
543            clauses.append("TABLE_OWNER=:desired_owner")
544            params['desired_owner'] = desired_owner
545        if desired_table:
546            clauses.append("TABLE_NAME=:tname")
547            params['tname'] = desired_table
548
549        sql += " AND ".join(clauses)
550
551        result = connection.execute(sql, **params)
552        if desired_owner:
553            row = result.fetchone()
554            if row:
555                return row['TABLE_NAME'], row['TABLE_OWNER'], row['DB_LINK'], row['SYNONYM_NAME']
556            else:
557                return None, None, None, None
558        else:
559            rows = result.fetchall()
560            if len(rows) > 1:
561                raise AssertionError("There are multiple tables visible to the schema, you must specify owner")
562            elif len(rows) == 1:
563                row = rows[0]
564                return row['TABLE_NAME'], row['TABLE_OWNER'], row['DB_LINK'], row['SYNONYM_NAME']
565            else:
566                return None, None, None, None
567
568    def reflecttable(self, connection, table, include_columns):
569        preparer = self.identifier_preparer
570
571        resolve_synonyms = table.kwargs.get('oracle_resolve_synonyms', False)
572
573        if resolve_synonyms:
574            actual_name, owner, dblink, synonym = self._resolve_synonym(connection, desired_owner=self._denormalize_name(table.schema), desired_synonym=self._denormalize_name(table.name))
575        else:
576            actual_name, owner, dblink, synonym = None, None, None, None
577
578        if not actual_name:
579            actual_name = self._denormalize_name(table.name)
580        if not dblink:
581            dblink = ''
582        if not owner:
583            owner = self._denormalize_name(table.schema or self.get_default_schema_name(connection))
584
585        c = connection.execute ("select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS%(dblink)s where TABLE_NAME = :table_name and OWNER = :owner" % {'dblink':dblink}, {'table_name':actual_name, 'owner':owner})
586
587        while True:
588            row = c.fetchone()
589            if row is None:
590                break
591
592            (colname, coltype, length, precision, scale, nullable, default) = (self._normalize_name(row[0]), row[1], row[2], row[3], row[4], row[5]=='Y', row[6])
593
594            if include_columns and colname not in include_columns:
595                continue
596
597            # INTEGER if the scale is 0 and precision is null
598            # NUMBER if the scale and precision are both null
599            # NUMBER(9,2) if the precision is 9 and the scale is 2
600            # NUMBER(3) if the precision is 3 and scale is 0
601            #length is ignored except for CHAR and VARCHAR2
602            if coltype == 'NUMBER' :
603                if precision is None and scale is None:
604                    coltype = OracleNumeric
605                elif precision is None and scale == 0  :
606                    coltype = OracleInteger
607                else :
608                    coltype = OracleNumeric(precision, scale)
609            elif coltype=='CHAR' or coltype=='VARCHAR2':
610                coltype = ischema_names.get(coltype, OracleString)(length)
611            else:
612                coltype = re.sub(r'\(\d+\)', '', coltype)
613                try:
614                    coltype = ischema_names[coltype]
615                except KeyError:
616                    util.warn("Did not recognize type '%s' of column '%s'" %
617                              (coltype, colname))
618                    coltype = sqltypes.NULLTYPE
619
620            colargs = []
621            if default is not None:
622                colargs.append(schema.DefaultClause(sql.text(default)))
623
624            table.append_column(schema.Column(colname, coltype, nullable=nullable, *colargs))
625
626        if not table.columns:
627            raise AssertionError("Couldn't find any column information for table %s" % actual_name)
628
629        c = connection.execute("""SELECT
630             ac.constraint_name,
631             ac.constraint_type,
632             loc.column_name AS local_column,
633             rem.table_name AS remote_table,
634             rem.column_name AS remote_column,
635             rem.owner AS remote_owner
636           FROM all_constraints%(dblink)s ac,
637             all_cons_columns%(dblink)s loc,
638             all_cons_columns%(dblink)s rem
639           WHERE ac.table_name = :table_name
640           AND ac.constraint_type IN ('R','P')
641           AND ac.owner = :owner
642           AND ac.owner = loc.owner
643           AND ac.constraint_name = loc.constraint_name
644           AND ac.r_owner = rem.owner(+)
645           AND ac.r_constraint_name = rem.constraint_name(+)
646           -- order multiple primary keys correctly
647           ORDER BY ac.constraint_name, loc.position, rem.position"""
648         % {'dblink':dblink}, {'table_name' : actual_name, 'owner' : owner})
649
650        fks = {}
651        while True:
652            row = c.fetchone()
653            if row is None:
654                break
655            #print "ROW:" , row
656            (cons_name, cons_type, local_column, remote_table, remote_column, remote_owner) = row[0:2] + tuple([self._normalize_name(x) for x in row[2:]])
657            if cons_type == 'P':
658                table.primary_key.add(table.c[local_column])
659            elif cons_type == 'R':
660                try:
661                    fk = fks[cons_name]
662                except KeyError:
663                    fk = ([], [])
664                    fks[cons_name] = fk
665                if remote_table is None:
666                    # ticket 363
667                    util.warn(
668                        ("Got 'None' querying 'table_name' from "
669                         "all_cons_columns%(dblink)s - does the user have "
670                         "proper rights to the table?") % {'dblink':dblink})
671                    continue
672
673                if resolve_synonyms:
674                    ref_remote_name, ref_remote_owner, ref_dblink, ref_synonym = self._resolve_synonym(connection, desired_owner=self._denormalize_name(remote_owner), desired_table=self._denormalize_name(remote_table))
675                    if ref_synonym:
676                        remote_table = self._normalize_name(ref_synonym)
677                        remote_owner = self._normalize_name(ref_remote_owner)
678
679                if not table.schema and self._denormalize_name(remote_owner) == owner:
680                    refspec =  ".".join([remote_table, remote_column])
681                    t = schema.Table(remote_table, table.metadata, autoload=True, autoload_with=connection, oracle_resolve_synonyms=resolve_synonyms, useexisting=True)
682                else:
683                    refspec =  ".".join([x for x in [remote_owner, remote_table, remote_column] if x])
684                    t = schema.Table(remote_table, table.metadata, autoload=True, autoload_with=connection, schema=remote_owner, oracle_resolve_synonyms=resolve_synonyms, useexisting=True)
685
686                if local_column not in fk[0]:
687                    fk[0].append(local_column)
688                if refspec not in fk[1]:
689                    fk[1].append(refspec)
690
691        for name, value in fks.iteritems():
692            table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name, link_to_name=True))
693
694
695class _OuterJoinColumn(sql.ClauseElement):
696    __visit_name__ = 'outer_join_column'
697   
698    def __init__(self, column):
699        self.column = column
700
701class OracleCompiler(compiler.DefaultCompiler):
702    """Oracle compiler modifies the lexical structure of Select
703    statements to work under non-ANSI configured Oracle databases, if
704    the use_ansi flag is False.
705    """
706
707    operators = compiler.DefaultCompiler.operators.copy()
708    operators.update(
709        {
710            sql_operators.mod : lambda x, y:"mod(%s, %s)" % (x, y),
711            sql_operators.match_op: lambda x, y: "CONTAINS (%s, %s)" % (x, y)
712        }
713    )
714
715    functions = compiler.DefaultCompiler.functions.copy()
716    functions.update (
717        {
718            sql_functions.now : 'CURRENT_TIMESTAMP'
719        }
720    )
721
722    def __init__(self, *args, **kwargs):
723        super(OracleCompiler, self).__init__(*args, **kwargs)
724        self.__wheres = {}
725
726    def default_from(self):
727        """Called when a ``SELECT`` statement has no froms, and no ``FROM`` clause is to be appended.
728
729        The Oracle compiler tacks a "FROM DUAL" to the statement.
730        """
731
732        return " FROM DUAL"
733
734    def apply_function_parens(self, func):
735        return len(func.clauses) > 0
736
737    def visit_join(self, join, **kwargs):
738        if self.dialect.use_ansi:
739            return compiler.DefaultCompiler.visit_join(self, join, **kwargs)
740        else:
741            return self.process(join.left, asfrom=True) + ", " + self.process(join.right, asfrom=True)
742
743    def _get_nonansi_join_whereclause(self, froms):
744        clauses = []
745
746        def visit_join(join):
747            if join.isouter:
748                def visit_binary(binary):
749                    if binary.operator == sql_operators.eq:
750                        if binary.left.table is join.right:
751                            binary.left = _OuterJoinColumn(binary.left)
752                        elif binary.right.table is join.right:
753                            binary.right = _OuterJoinColumn(binary.right)
754                clauses.append(visitors.cloned_traverse(join.onclause, {}, {'binary':visit_binary}))
755            else:
756                clauses.append(join.onclause)
757
758        for f in froms:
759            visitors.traverse(f, {}, {'join':visit_join})
760        return sql.and_(*clauses)
761
762    def visit_outer_join_column(self, vc):
763        return self.process(vc.column) + "(+)"
764
765    def visit_sequence(self, seq):
766        return self.dialect.identifier_preparer.format_sequence(seq) + ".nextval"
767
768    def visit_alias(self, alias, asfrom=False, **kwargs):
769        """Oracle doesn't like ``FROM table AS alias``.  Is the AS standard SQL??"""
770
771        if asfrom:
772            alias_name = isinstance(alias.name, expression._generated_label) and \
773                            self._truncated_identifier("alias", alias.name) or alias.name
774           
775            return self.process(alias.original, asfrom=True, **kwargs) + " " +\
776                    self.preparer.format_alias(alias, alias_name)
777        else:
778            return self.process(alias.original, **kwargs)
779
780    def _TODO_visit_compound_select(self, select):
781        """Need to determine how to get ``LIMIT``/``OFFSET`` into a ``UNION`` for Oracle."""
782        pass
783
784    def visit_select(self, select, **kwargs):
785        """Look for ``LIMIT`` and OFFSET in a select statement, and if
786        so tries to wrap it in a subquery with ``rownum`` criterion.
787        """
788
789        if not getattr(select, '_oracle_visit', None):
790            if not self.dialect.use_ansi:
791                if self.stack and 'from' in self.stack[-1]:
792                    existingfroms = self.stack[-1]['from']
793                else:
794                    existingfroms = None
795
796                froms = select._get_display_froms(existingfroms)
797                whereclause = self._get_nonansi_join_whereclause(froms)
798                if whereclause:
799                    select = select.where(whereclause)
800                    select._oracle_visit = True
801
802            if select._limit is not None or select._offset is not None:
803                # See http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
804                #
805                # Generalized form of an Oracle pagination query:
806                #   select ... from (
807                #     select /*+ FIRST_ROWS(N) */ ...., rownum as ora_rn from (
808                #         select distinct ... where ... order by ...
809                #     ) where ROWNUM <= :limit+:offset
810                #   ) where ora_rn > :offset
811                # Outer select and "ROWNUM as ora_rn" can be dropped if limit=0
812
813                # TODO: use annotations instead of clone + attr set ?
814                select = select._generate()
815                select._oracle_visit = True
816
817                # Wrap the middle select and add the hint
818                limitselect = sql.select([c for c in select.c])
819                if select._limit and self.dialect.optimize_limits:
820                    limitselect = limitselect.prefix_with("/*+ FIRST_ROWS(%d) */" % select._limit)
821
822                limitselect._oracle_visit = True
823                limitselect._is_wrapper = True
824
825                # If needed, add the limiting clause
826                if select._limit is not None:
827                    max_row = select._limit
828                    if select._offset is not None:
829                        max_row += select._offset
830                    limitselect.append_whereclause(
831                            sql.literal_column("ROWNUM")<=max_row)
832 
833                # If needed, add the ora_rn, and wrap again with offset.
834                if select._offset is None:
835                    select = limitselect
836                else:
837                     limitselect = limitselect.column(
838                             sql.literal_column("ROWNUM").label("ora_rn"))
839                     limitselect._oracle_visit = True
840                     limitselect._is_wrapper = True
841 
842                     offsetselect = sql.select(
843                             [c for c in limitselect.c if c.key!='ora_rn'])
844                     offsetselect._oracle_visit = True
845                     offsetselect._is_wrapper = True
846 
847                     offsetselect.append_whereclause(
848                             sql.literal_column("ora_rn")>select._offset)
849 
850                     select = offsetselect
851
852        kwargs['iswrapper'] = getattr(select, '_is_wrapper', False)
853        return compiler.DefaultCompiler.visit_select(self, select, **kwargs)
854
855    def limit_clause(self, select):
856        return ""
857
858    def for_update_clause(self, select):
859        if select.for_update == "nowait":
860            return " FOR UPDATE NOWAIT"
861        else:
862            return super(OracleCompiler, self).for_update_clause(select)
863
864
865class OracleSchemaGenerator(compiler.SchemaGenerator):
866    def get_column_specification(self, column, **kwargs):
867        colspec = self.preparer.format_column(column)
868        colspec += " " + column.type.dialect_impl(self.dialect).get_col_spec()
869        default = self.get_column_default_string(column)
870        if default is not None:
871            colspec += " DEFAULT " + default
872
873        if not column.nullable:
874            colspec += " NOT NULL"
875        return colspec
876
877    def visit_sequence(self, sequence):
878        if not self.checkfirst  or not self.dialect.has_sequence(self.connection, sequence.name, sequence.schema):
879            self.append("CREATE SEQUENCE %s" % self.preparer.format_sequence(sequence))
880            self.execute()
881
882class OracleSchemaDropper(compiler.SchemaDropper):
883    def visit_sequence(self, sequence):
884        if not self.checkfirst or self.dialect.has_sequence(self.connection, sequence.name, sequence.schema):
885            self.append("DROP SEQUENCE %s" % self.preparer.format_sequence(sequence))
886            self.execute()
887
888class OracleDefaultRunner(base.DefaultRunner):
889    def visit_sequence(self, seq):
890        return self.execute_string("SELECT " + self.dialect.identifier_preparer.format_sequence(seq) + ".nextval FROM DUAL", {})
891
892class OracleIdentifierPreparer(compiler.IdentifierPreparer):
893    def format_savepoint(self, savepoint):
894        name = re.sub(r'^_+', '', savepoint.ident)
895        return super(OracleIdentifierPreparer, self).format_savepoint(savepoint, name)
896
897
898dialect = OracleDialect
899dialect.statement_compiler = OracleCompiler
900dialect.schemagenerator = OracleSchemaGenerator
901dialect.schemadropper = OracleSchemaDropper
902dialect.preparer = OracleIdentifierPreparer
903dialect.defaultrunner = OracleDefaultRunner
904dialect.execution_ctx_cls = OracleExecutionContext
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。