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

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

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

行番号 
1# sybase.py
2# Copyright (C) 2007 Fisch Asset Management AG http://www.fam.ch
3# Coding: Alexander Houben alexander.houben@thor-solutions.ch
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"""
9Sybase database backend.
10
11Known issues / TODO:
12
13 * Uses the mx.ODBC driver from egenix (version 2.1.0)
14 * The current version of sqlalchemy.databases.sybase only supports
15   mx.ODBC.Windows (other platforms such as mx.ODBC.unixODBC still need
16   some development)
17 * Support for pyodbc has been built in but is not yet complete (needs
18   further development)
19 * Results of running tests/alltests.py:
20     Ran 934 tests in 287.032s
21     FAILED (failures=3, errors=1)
22 * Tested on 'Adaptive Server Anywhere 9' (version 9.0.1.1751)
23"""
24
25import datetime, operator
26
27from sqlalchemy import util, sql, schema, exc
28from sqlalchemy.sql import compiler, expression
29from sqlalchemy.engine import default, base
30from sqlalchemy import types as sqltypes
31from sqlalchemy.sql import operators as sql_operators
32from sqlalchemy import MetaData, Table, Column
33from sqlalchemy import String, Integer, SMALLINT, CHAR, ForeignKey
34
35
36__all__ = [
37    'SybaseTypeError'
38    'SybaseNumeric', 'SybaseFloat', 'SybaseInteger', 'SybaseBigInteger',
39    'SybaseTinyInteger', 'SybaseSmallInteger',
40    'SybaseDateTime_mxodbc', 'SybaseDateTime_pyodbc',
41    'SybaseDate_mxodbc', 'SybaseDate_pyodbc',
42    'SybaseTime_mxodbc', 'SybaseTime_pyodbc',
43    'SybaseText', 'SybaseString', 'SybaseChar', 'SybaseBinary',
44    'SybaseBoolean', 'SybaseTimeStamp', 'SybaseMoney', 'SybaseSmallMoney',
45    'SybaseUniqueIdentifier',
46    ]
47
48
49RESERVED_WORDS = set([
50    "add", "all", "alter", "and",
51    "any", "as", "asc", "backup",
52    "begin", "between", "bigint", "binary",
53    "bit", "bottom", "break", "by",
54    "call", "capability", "cascade", "case",
55    "cast", "char", "char_convert", "character",
56    "check", "checkpoint", "close", "comment",
57    "commit", "connect", "constraint", "contains",
58    "continue", "convert", "create", "cross",
59    "cube", "current", "current_timestamp", "current_user",
60    "cursor", "date", "dbspace", "deallocate",
61    "dec", "decimal", "declare", "default",
62    "delete", "deleting", "desc", "distinct",
63    "do", "double", "drop", "dynamic",
64    "else", "elseif", "encrypted", "end",
65    "endif", "escape", "except", "exception",
66    "exec", "execute", "existing", "exists",
67    "externlogin", "fetch", "first", "float",
68    "for", "force", "foreign", "forward",
69    "from", "full", "goto", "grant",
70    "group", "having", "holdlock", "identified",
71    "if", "in", "index", "index_lparen",
72    "inner", "inout", "insensitive", "insert",
73    "inserting", "install", "instead", "int",
74    "integer", "integrated", "intersect", "into",
75    "iq", "is", "isolation", "join",
76    "key", "lateral", "left", "like",
77    "lock", "login", "long", "match",
78    "membership", "message", "mode", "modify",
79    "natural", "new", "no", "noholdlock",
80    "not", "notify", "null", "numeric",
81    "of", "off", "on", "open",
82    "option", "options", "or", "order",
83    "others", "out", "outer", "over",
84    "passthrough", "precision", "prepare", "primary",
85    "print", "privileges", "proc", "procedure",
86    "publication", "raiserror", "readtext", "real",
87    "reference", "references", "release", "remote",
88    "remove", "rename", "reorganize", "resource",
89    "restore", "restrict", "return", "revoke",
90    "right", "rollback", "rollup", "save",
91    "savepoint", "scroll", "select", "sensitive",
92    "session", "set", "setuser", "share",
93    "smallint", "some", "sqlcode", "sqlstate",
94    "start", "stop", "subtrans", "subtransaction",
95    "synchronize", "syntax_error", "table", "temporary",
96    "then", "time", "timestamp", "tinyint",
97    "to", "top", "tran", "trigger",
98    "truncate", "tsequal", "unbounded", "union",
99    "unique", "unknown", "unsigned", "update",
100    "updating", "user", "using", "validate",
101    "values", "varbinary", "varchar", "variable",
102    "varying", "view", "wait", "waitfor",
103    "when", "where", "while", "window",
104    "with", "with_cube", "with_lparen", "with_rollup",
105    "within", "work", "writetext",
106    ])
107
108ischema = MetaData()
109
110tables = Table("SYSTABLE", ischema,
111    Column("table_id", Integer, primary_key=True),
112    Column("file_id", SMALLINT),
113    Column("table_name", CHAR(128)),
114    Column("table_type", CHAR(10)),
115    Column("creator", Integer),
116    #schema="information_schema"
117    )
118
119domains = Table("SYSDOMAIN", ischema,
120    Column("domain_id", Integer, primary_key=True),
121    Column("domain_name", CHAR(128)),
122    Column("type_id", SMALLINT),
123    Column("precision", SMALLINT, quote=True),
124    #schema="information_schema"
125    )
126
127columns = Table("SYSCOLUMN", ischema,
128    Column("column_id", Integer, primary_key=True),
129    Column("table_id", Integer, ForeignKey(tables.c.table_id)),
130    Column("pkey", CHAR(1)),
131    Column("column_name", CHAR(128)),
132    Column("nulls", CHAR(1)),
133    Column("width", SMALLINT),
134    Column("domain_id", SMALLINT, ForeignKey(domains.c.domain_id)),
135    # FIXME: should be mx.BIGINT
136    Column("max_identity", Integer),
137    # FIXME: should be mx.ODBC.Windows.LONGVARCHAR
138    Column("default", String),
139    Column("scale", Integer),
140    #schema="information_schema"
141    )
142
143foreignkeys = Table("SYSFOREIGNKEY", ischema,
144    Column("foreign_table_id", Integer, ForeignKey(tables.c.table_id), primary_key=True),
145    Column("foreign_key_id", SMALLINT, primary_key=True),
146    Column("primary_table_id", Integer, ForeignKey(tables.c.table_id)),
147    #schema="information_schema"
148    )
149fkcols = Table("SYSFKCOL", ischema,
150    Column("foreign_table_id", Integer, ForeignKey(columns.c.table_id), primary_key=True),
151    Column("foreign_key_id", SMALLINT, ForeignKey(foreignkeys.c.foreign_key_id), primary_key=True),
152    Column("foreign_column_id", Integer, ForeignKey(columns.c.column_id), primary_key=True),
153    Column("primary_column_id", Integer),
154    #schema="information_schema"
155    )
156
157class SybaseTypeError(sqltypes.TypeEngine):
158    def result_processor(self, dialect):
159        return None
160
161    def bind_processor(self, dialect):
162        def process(value):
163            raise exc.InvalidRequestError("Data type not supported", [value])
164        return process
165
166    def get_col_spec(self):
167        raise exc.CompileError("Data type not supported")
168
169class SybaseNumeric(sqltypes.Numeric):
170    def get_col_spec(self):
171        if self.scale is None:
172            if self.precision is None:
173                return "NUMERIC"
174            else:
175                return "NUMERIC(%(precision)s)" % {'precision' : self.precision}
176        else:
177            return "NUMERIC(%(precision)s, %(scale)s)" % {'precision': self.precision, 'scale' : self.scale}
178
179class SybaseFloat(sqltypes.FLOAT, SybaseNumeric):
180    def __init__(self, precision = 10, asdecimal = False, scale = 2, **kwargs):
181        super(sqltypes.FLOAT, self).__init__(precision, asdecimal, **kwargs)
182        self.scale = scale
183
184    def get_col_spec(self):
185        # if asdecimal is True, handle same way as SybaseNumeric
186        if self.asdecimal:
187            return SybaseNumeric.get_col_spec(self)
188        if self.precision is None:
189            return "FLOAT"
190        else:
191            return "FLOAT(%(precision)s)" % {'precision': self.precision}
192
193    def result_processor(self, dialect):
194        def process(value):
195            if value is None:
196                return None
197            return float(value)
198        if self.asdecimal:
199            return SybaseNumeric.result_processor(self, dialect)
200        return process
201
202class SybaseInteger(sqltypes.Integer):
203    def get_col_spec(self):
204        return "INTEGER"
205
206class SybaseBigInteger(SybaseInteger):
207    def get_col_spec(self):
208        return "BIGINT"
209
210class SybaseTinyInteger(SybaseInteger):
211    def get_col_spec(self):
212        return "TINYINT"
213
214class SybaseSmallInteger(SybaseInteger):
215    def get_col_spec(self):
216        return "SMALLINT"
217
218class SybaseDateTime_mxodbc(sqltypes.DateTime):
219    def __init__(self, *a, **kw):
220        super(SybaseDateTime_mxodbc, self).__init__(False)
221
222    def get_col_spec(self):
223        return "DATETIME"
224
225class SybaseDateTime_pyodbc(sqltypes.DateTime):
226    def __init__(self, *a, **kw):
227        super(SybaseDateTime_pyodbc, self).__init__(False)
228
229    def get_col_spec(self):
230        return "DATETIME"
231
232    def result_processor(self, dialect):
233        def process(value):
234            if value is None:
235                return None
236            # Convert the datetime.datetime back to datetime.time
237            return value
238        return process
239
240    def bind_processor(self, dialect):
241        def process(value):
242            if value is None:
243                return None
244            return value
245        return process
246
247class SybaseDate_mxodbc(sqltypes.Date):
248    def __init__(self, *a, **kw):
249        super(SybaseDate_mxodbc, self).__init__(False)
250
251    def get_col_spec(self):
252        return "DATE"
253
254class SybaseDate_pyodbc(sqltypes.Date):
255    def __init__(self, *a, **kw):
256        super(SybaseDate_pyodbc, self).__init__(False)
257
258    def get_col_spec(self):
259        return "DATE"
260
261class SybaseTime_mxodbc(sqltypes.Time):
262    def __init__(self, *a, **kw):
263        super(SybaseTime_mxodbc, self).__init__(False)
264
265    def get_col_spec(self):
266        return "DATETIME"
267
268    def result_processor(self, dialect):
269        def process(value):
270            if value is None:
271                return None
272            # Convert the datetime.datetime back to datetime.time
273            return datetime.time(value.hour, value.minute, value.second, value.microsecond)
274        return process
275
276class SybaseTime_pyodbc(sqltypes.Time):
277    def __init__(self, *a, **kw):
278        super(SybaseTime_pyodbc, self).__init__(False)
279
280    def get_col_spec(self):
281        return "DATETIME"
282
283    def result_processor(self, dialect):
284        def process(value):
285            if value is None:
286                return None
287            # Convert the datetime.datetime back to datetime.time
288            return datetime.time(value.hour, value.minute, value.second, value.microsecond)
289        return process
290
291    def bind_processor(self, dialect):
292        def process(value):
293            if value is None:
294                return None
295            return datetime.datetime(1970, 1, 1, value.hour, value.minute, value.second, value.microsecond)
296        return process
297
298class SybaseText(sqltypes.Text):
299    def get_col_spec(self):
300        return "TEXT"
301
302class SybaseString(sqltypes.String):
303    def get_col_spec(self):
304        return "VARCHAR(%(length)s)" % {'length' : self.length}
305
306class SybaseChar(sqltypes.CHAR):
307    def get_col_spec(self):
308        return "CHAR(%(length)s)" % {'length' : self.length}
309
310class SybaseBinary(sqltypes.Binary):
311    def get_col_spec(self):
312        return "IMAGE"
313
314class SybaseBoolean(sqltypes.Boolean):
315    def get_col_spec(self):
316        return "BIT"
317
318    def result_processor(self, dialect):
319        def process(value):
320            if value is None:
321                return None
322            return value and True or False
323        return process
324
325    def bind_processor(self, dialect):
326        def process(value):
327            if value is True:
328                return 1
329            elif value is False:
330                return 0
331            elif value is None:
332                return None
333            else:
334                return value and True or False
335        return process
336
337class SybaseTimeStamp(sqltypes.TIMESTAMP):
338    def get_col_spec(self):
339        return "TIMESTAMP"
340
341class SybaseMoney(sqltypes.TypeEngine):
342    def get_col_spec(self):
343        return "MONEY"
344
345class SybaseSmallMoney(SybaseMoney):
346    def get_col_spec(self):
347        return "SMALLMONEY"
348
349class SybaseUniqueIdentifier(sqltypes.TypeEngine):
350    def get_col_spec(self):
351        return "UNIQUEIDENTIFIER"
352
353class SybaseSQLExecutionContext(default.DefaultExecutionContext):
354    pass
355
356class SybaseSQLExecutionContext_mxodbc(SybaseSQLExecutionContext):
357
358    def __init__(self, dialect, connection, compiled=None, statement=None, parameters=None):
359        super(SybaseSQLExecutionContext_mxodbc, self).__init__(dialect, connection, compiled, statement, parameters)
360
361    def pre_exec(self):
362        super(SybaseSQLExecutionContext_mxodbc, self).pre_exec()
363
364    def post_exec(self):
365        if self.compiled.isinsert:
366            table = self.compiled.statement.table
367            # get the inserted values of the primary key
368
369            # get any sequence IDs first (using @@identity)
370            self.cursor.execute("SELECT @@identity AS lastrowid")
371            row = self.cursor.fetchone()
372            lastrowid = int(row[0])
373            if lastrowid > 0:
374                # an IDENTITY was inserted, fetch it
375                # FIXME: always insert in front ? This only works if the IDENTITY is the first column, no ?!
376                if not hasattr(self, '_last_inserted_ids') or self._last_inserted_ids is None:
377                    self._last_inserted_ids = [lastrowid]
378                else:
379                    self._last_inserted_ids = [lastrowid] + self._last_inserted_ids[1:]
380        super(SybaseSQLExecutionContext_mxodbc, self).post_exec()
381
382class SybaseSQLExecutionContext_pyodbc(SybaseSQLExecutionContext):
383    def __init__(self, dialect, connection, compiled=None, statement=None, parameters=None):
384        super(SybaseSQLExecutionContext_pyodbc, self).__init__(dialect, connection, compiled, statement, parameters)
385
386    def pre_exec(self):
387        super(SybaseSQLExecutionContext_pyodbc, self).pre_exec()
388
389    def post_exec(self):
390        if self.compiled.isinsert:
391            table = self.compiled.statement.table
392            # get the inserted values of the primary key
393
394            # get any sequence IDs first (using @@identity)
395            self.cursor.execute("SELECT @@identity AS lastrowid")
396            row = self.cursor.fetchone()
397            lastrowid = int(row[0])
398            if lastrowid > 0:
399                # an IDENTITY was inserted, fetch it
400                # FIXME: always insert in front ? This only works if the IDENTITY is the first column, no ?!
401                if not hasattr(self, '_last_inserted_ids') or self._last_inserted_ids is None:
402                    self._last_inserted_ids = [lastrowid]
403                else:
404                    self._last_inserted_ids = [lastrowid] + self._last_inserted_ids[1:]
405        super(SybaseSQLExecutionContext_pyodbc, self).post_exec()
406
407class SybaseSQLDialect(default.DefaultDialect):
408    colspecs = {
409        # FIXME: unicode support
410        #sqltypes.Unicode : SybaseUnicode,
411        sqltypes.Integer : SybaseInteger,
412        sqltypes.SmallInteger : SybaseSmallInteger,
413        sqltypes.Numeric : SybaseNumeric,
414        sqltypes.Float : SybaseFloat,
415        sqltypes.String : SybaseString,
416        sqltypes.Binary : SybaseBinary,
417        sqltypes.Boolean : SybaseBoolean,
418        sqltypes.Text : SybaseText,
419        sqltypes.CHAR : SybaseChar,
420        sqltypes.TIMESTAMP : SybaseTimeStamp,
421        sqltypes.FLOAT : SybaseFloat,
422    }
423
424    ischema_names = {
425        'integer' : SybaseInteger,
426        'unsigned int' : SybaseInteger,
427        'unsigned smallint' : SybaseInteger,
428        'unsigned bigint' : SybaseInteger,
429        'bigint': SybaseBigInteger,
430        'smallint' : SybaseSmallInteger,
431        'tinyint' : SybaseTinyInteger,
432        'varchar' : SybaseString,
433        'long varchar' : SybaseText,
434        'char' : SybaseChar,
435        'decimal' : SybaseNumeric,
436        'numeric' : SybaseNumeric,
437        'float' : SybaseFloat,
438        'double' : SybaseFloat,
439        'binary' : SybaseBinary,
440        'long binary' : SybaseBinary,
441        'varbinary' : SybaseBinary,
442        'bit': SybaseBoolean,
443        'image' : SybaseBinary,
444        'timestamp': SybaseTimeStamp,
445        'money': SybaseMoney,
446        'smallmoney': SybaseSmallMoney,
447        'uniqueidentifier': SybaseUniqueIdentifier,
448
449        'java.lang.Object' : SybaseTypeError,
450        'java serialization' : SybaseTypeError,
451    }
452
453    name = 'sybase'
454    # Sybase backend peculiarities
455    supports_unicode_statements = False
456    supports_sane_rowcount = False
457    supports_sane_multi_rowcount = False
458    execution_ctx_cls = SybaseSQLExecutionContext
459   
460    def __new__(cls, dbapi=None, *args, **kwargs):
461        if cls != SybaseSQLDialect:
462            return super(SybaseSQLDialect, cls).__new__(cls, *args, **kwargs)
463        if dbapi:
464            print dbapi.__name__
465            dialect = dialect_mapping.get(dbapi.__name__)
466            return dialect(*args, **kwargs)
467        else:
468            return object.__new__(cls, *args, **kwargs)
469
470    def __init__(self, **params):
471        super(SybaseSQLDialect, self).__init__(**params)
472        self.text_as_varchar = False
473        # FIXME: what is the default schema for sybase connections (DBA?) ?
474        self.set_default_schema_name("dba")
475
476    def dbapi(cls, module_name=None):
477        if module_name:
478            try:
479                dialect_cls = dialect_mapping[module_name]
480                return dialect_cls.import_dbapi()
481            except KeyError:
482                raise exc.InvalidRequestError("Unsupported SybaseSQL module '%s' requested (must be " + " or ".join([x for x in dialect_mapping.keys()]) + ")" % module_name)
483        else:
484            for dialect_cls in dialect_mapping.values():
485                try:
486                    return dialect_cls.import_dbapi()
487                except ImportError, e:
488                    pass
489            else:
490                raise ImportError('No DBAPI module detected for SybaseSQL - please install mxodbc')
491    dbapi = classmethod(dbapi)
492
493    def type_descriptor(self, typeobj):
494        newobj = sqltypes.adapt_type(typeobj, self.colspecs)
495        return newobj
496
497    def last_inserted_ids(self):
498        return self.context.last_inserted_ids
499
500    def get_default_schema_name(self, connection):
501        return self.schema_name
502
503    def set_default_schema_name(self, schema_name):
504        self.schema_name = schema_name
505
506    def do_execute(self, cursor, statement, params, **kwargs):
507        params = tuple(params)
508        super(SybaseSQLDialect, self).do_execute(cursor, statement, params, **kwargs)
509
510    # FIXME: remove ?
511    def _execute(self, c, statement, parameters):
512        try:
513            if parameters == {}:
514                parameters = ()
515            c.execute(statement, parameters)
516            self.context.rowcount = c.rowcount
517            c.DBPROP_COMMITPRESERVE = "Y"
518        except Exception, e:
519            raise exc.DBAPIError.instance(statement, parameters, e)
520
521    def table_names(self, connection, schema):
522        """Ignore the schema and the charset for now."""
523        s = sql.select([tables.c.table_name],
524                       sql.not_(tables.c.table_name.like("SYS%")) and
525                       tables.c.creator >= 100
526                       )
527        rp = connection.execute(s)
528        return [row[0] for row in rp.fetchall()]
529
530    def has_table(self, connection, tablename, schema=None):
531        # FIXME: ignore schemas for sybase
532        s = sql.select([tables.c.table_name], tables.c.table_name == tablename)
533
534        c = connection.execute(s)
535        row = c.fetchone()
536        print "has_table: " + tablename + ": " + str(bool(row is not None))
537        return row is not None
538
539    def reflecttable(self, connection, table, include_columns):
540        # Get base columns
541        if table.schema is not None:
542            current_schema = table.schema
543        else:
544            current_schema = self.get_default_schema_name(connection)
545
546        s = sql.select([columns, domains], tables.c.table_name==table.name, from_obj=[columns.join(tables).join(domains)], order_by=[columns.c.column_id])
547
548        c = connection.execute(s)
549        found_table = False
550        # makes sure we append the columns in the correct order
551        while True:
552            row = c.fetchone()
553            if row is None:
554                break
555            found_table = True
556            (name, type, nullable, charlen, numericprec, numericscale, default, primary_key, max_identity, table_id, column_id) = (
557                row[columns.c.column_name],
558                row[domains.c.domain_name],
559                row[columns.c.nulls] == 'Y',
560                row[columns.c.width],
561                row[domains.c.precision],
562                row[columns.c.scale],
563                row[columns.c.default],
564                row[columns.c.pkey] == 'Y',
565                row[columns.c.max_identity],
566                row[tables.c.table_id],
567                row[columns.c.column_id],
568            )
569            if include_columns and name not in include_columns:
570                continue
571
572            # FIXME: else problems with SybaseBinary(size)
573            if numericscale == 0:
574                numericscale = None
575
576            args = []
577            for a in (charlen, numericprec, numericscale):
578                if a is not None:
579                    args.append(a)
580            coltype = self.ischema_names.get(type, None)
581            if coltype == SybaseString and charlen == -1:
582                coltype = SybaseText()
583            else:
584                if coltype is None:
585                    util.warn("Did not recognize type '%s' of column '%s'" %
586                              (type, name))
587                    coltype = sqltypes.NULLTYPE
588                coltype = coltype(*args)
589            colargs = []
590            if default is not None:
591                colargs.append(schema.DefaultClause(sql.text(default)))
592
593            # any sequences ?
594            col = schema.Column(name, coltype, nullable=nullable, primary_key=primary_key, *colargs)
595            if int(max_identity) > 0:
596                col.sequence = schema.Sequence(name + '_identity')
597                col.sequence.start = int(max_identity)
598                col.sequence.increment = 1
599
600            # append the column
601            table.append_column(col)
602
603        # any foreign key constraint for this table ?
604        # note: no multi-column foreign keys are considered
605        s = "select st1.table_name, sc1.column_name, st2.table_name, sc2.column_name from systable as st1 join sysfkcol on st1.table_id=sysfkcol.foreign_table_id join sysforeignkey join systable as st2 on sysforeignkey.primary_table_id = st2.table_id join syscolumn as sc1 on sysfkcol.foreign_column_id=sc1.column_id and sc1.table_id=st1.table_id join syscolumn as sc2 on sysfkcol.primary_column_id=sc2.column_id and sc2.table_id=st2.table_id where st1.table_name='%(table_name)s';" % { 'table_name' : table.name }
606        c = connection.execute(s)
607        foreignKeys = {}
608        while True:
609            row = c.fetchone()
610            if row is None:
611                break
612            (foreign_table, foreign_column, primary_table, primary_column) = (
613                row[0], row[1], row[2], row[3],
614            )
615            if not primary_table in foreignKeys.keys():
616                foreignKeys[primary_table] = [['%s' % (foreign_column)], ['%s.%s'%(primary_table, primary_column)]]
617            else:
618                foreignKeys[primary_table][0].append('%s'%(foreign_column))
619                foreignKeys[primary_table][1].append('%s.%s'%(primary_table, primary_column))
620        for primary_table in foreignKeys.keys():
621            #table.append_constraint(schema.ForeignKeyConstraint(['%s.%s'%(foreign_table, foreign_column)], ['%s.%s'%(primary_table,primary_column)]))
622            table.append_constraint(schema.ForeignKeyConstraint(foreignKeys[primary_table][0], foreignKeys[primary_table][1], link_to_name=True))
623
624        if not found_table:
625            raise exc.NoSuchTableError(table.name)
626
627
628class SybaseSQLDialect_mxodbc(SybaseSQLDialect):
629    execution_ctx_cls = SybaseSQLExecutionContext_mxodbc
630   
631    def __init__(self, **params):
632        super(SybaseSQLDialect_mxodbc, self).__init__(**params)
633
634        self.dbapi_type_map = {'getdate' : SybaseDate_mxodbc()}
635
636    def import_dbapi(cls):
637        #import mx.ODBC.Windows as module
638        import mxODBC as module
639        return module
640    import_dbapi = classmethod(import_dbapi)
641
642    colspecs = SybaseSQLDialect.colspecs.copy()
643    colspecs[sqltypes.Time] = SybaseTime_mxodbc
644    colspecs[sqltypes.Date] = SybaseDate_mxodbc
645    colspecs[sqltypes.DateTime] = SybaseDateTime_mxodbc
646
647    ischema_names = SybaseSQLDialect.ischema_names.copy()
648    ischema_names['time'] = SybaseTime_mxodbc
649    ischema_names['date'] = SybaseDate_mxodbc
650    ischema_names['datetime'] = SybaseDateTime_mxodbc
651    ischema_names['smalldatetime'] = SybaseDateTime_mxodbc
652
653    def is_disconnect(self, e):
654        # FIXME: optimize
655        #return isinstance(e, self.dbapi.Error) and '[08S01]' in str(e)
656        #return True
657        return False
658
659    def do_execute(self, cursor, statement, parameters, context=None, **kwargs):
660        super(SybaseSQLDialect_mxodbc, self).do_execute(cursor, statement, parameters, context=context, **kwargs)
661
662    def create_connect_args(self, url):
663        '''Return a tuple of *args,**kwargs'''
664        # FIXME: handle mx.odbc.Windows proprietary args
665        opts = url.translate_connect_args(username='user')
666        opts.update(url.query)
667        argsDict = {}
668        argsDict['user'] = opts['user']
669        argsDict['password'] = opts['password']
670        connArgs = [[opts['dsn']], argsDict]
671        return connArgs
672
673
674class SybaseSQLDialect_pyodbc(SybaseSQLDialect):
675    execution_ctx_cls = SybaseSQLExecutionContext_pyodbc
676   
677    def __init__(self, **params):
678        super(SybaseSQLDialect_pyodbc, self).__init__(**params)
679        self.dbapi_type_map = {'getdate' : SybaseDate_pyodbc()}
680
681    def import_dbapi(cls):
682        import mypyodbc as module
683        return module
684    import_dbapi = classmethod(import_dbapi)
685
686    colspecs = SybaseSQLDialect.colspecs.copy()
687    colspecs[sqltypes.Time] = SybaseTime_pyodbc
688    colspecs[sqltypes.Date] = SybaseDate_pyodbc
689    colspecs[sqltypes.DateTime] = SybaseDateTime_pyodbc
690
691    ischema_names = SybaseSQLDialect.ischema_names.copy()
692    ischema_names['time'] = SybaseTime_pyodbc
693    ischema_names['date'] = SybaseDate_pyodbc
694    ischema_names['datetime'] = SybaseDateTime_pyodbc
695    ischema_names['smalldatetime'] = SybaseDateTime_pyodbc
696
697    def is_disconnect(self, e):
698        # FIXME: optimize
699        #return isinstance(e, self.dbapi.Error) and '[08S01]' in str(e)
700        #return True
701        return False
702
703    def do_execute(self, cursor, statement, parameters, context=None, **kwargs):
704        super(SybaseSQLDialect_pyodbc, self).do_execute(cursor, statement, parameters, context=context, **kwargs)
705
706    def create_connect_args(self, url):
707        '''Return a tuple of *args,**kwargs'''
708        # FIXME: handle pyodbc proprietary args
709        opts = url.translate_connect_args(username='user')
710        opts.update(url.query)
711
712        self.autocommit = False
713        if 'autocommit' in opts:
714            self.autocommit = bool(int(opts.pop('autocommit')))
715
716        argsDict = {}
717        argsDict['UID'] = opts['user']
718        argsDict['PWD'] = opts['password']
719        argsDict['DSN'] = opts['dsn']
720        connArgs = [[';'.join(["%s=%s"%(key, argsDict[key]) for key in argsDict])], {'autocommit' : self.autocommit}]
721        return connArgs
722
723
724dialect_mapping = {
725    'sqlalchemy.databases.mxODBC' : SybaseSQLDialect_mxodbc,
726#    'pyodbc' : SybaseSQLDialect_pyodbc,
727    }
728
729
730class SybaseSQLCompiler(compiler.DefaultCompiler):
731    operators = compiler.DefaultCompiler.operators.copy()
732    operators.update({
733        sql_operators.mod: lambda x, y: "MOD(%s, %s)" % (x, y),
734    })
735
736    extract_map = compiler.DefaultCompiler.extract_map.copy()
737    extract_map.update ({
738        'doy': 'dayofyear',
739        'dow': 'weekday',
740        'milliseconds': 'millisecond'
741    })
742
743
744    def bindparam_string(self, name):
745        res = super(SybaseSQLCompiler, self).bindparam_string(name)
746        if name.lower().startswith('literal'):
747            res = 'STRING(%s)' % res
748        return res
749
750    def get_select_precolumns(self, select):
751        s = select._distinct and "DISTINCT " or ""
752        if select._limit:
753            #if select._limit == 1:
754                #s += "FIRST "
755            #else:
756                #s += "TOP %s " % (select._limit,)
757            s += "TOP %s " % (select._limit,)
758        if select._offset:
759            if not select._limit:
760                # FIXME: sybase doesn't allow an offset without a limit
761                # so use a huge value for TOP here
762                s += "TOP 1000000 "
763            s += "START AT %s " % (select._offset+1,)
764        return s
765
766    def limit_clause(self, select):
767        # Limit in sybase is after the select keyword
768        return ""
769
770    def visit_binary(self, binary):
771        """Move bind parameters to the right-hand side of an operator, where possible."""
772        if isinstance(binary.left, expression._BindParamClause) and binary.operator == operator.eq:
773            return self.process(expression._BinaryExpression(binary.right, binary.left, binary.operator))
774        else:
775            return super(SybaseSQLCompiler, self).visit_binary(binary)
776
777    def label_select_column(self, select, column, asfrom):
778        if isinstance(column, expression.Function):
779            return column.label(None)
780        else:
781            return super(SybaseSQLCompiler, self).label_select_column(select, column, asfrom)
782
783    function_rewrites =  {'current_date': 'getdate',
784                         }
785    def visit_function(self, func):
786        func.name = self.function_rewrites.get(func.name, func.name)
787        res = super(SybaseSQLCompiler, self).visit_function(func)
788        if func.name.lower() == 'getdate':
789            # apply CAST operator
790            # FIXME: what about _pyodbc ?
791            cast = expression._Cast(func, SybaseDate_mxodbc)
792            # infinite recursion
793            # res = self.visit_cast(cast)
794            res = "CAST(%s AS %s)" % (res, self.process(cast.typeclause))
795        return res
796
797    def visit_extract(self, extract):
798        field = self.extract_map.get(extract.field, extract.field)
799        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr))
800
801    def for_update_clause(self, select):
802        # "FOR UPDATE" is only allowed on "DECLARE CURSOR" which SQLAlchemy doesn't use
803        return ''
804
805    def order_by_clause(self, select):
806        order_by = self.process(select._order_by_clause)
807
808        # SybaseSQL only allows ORDER BY in subqueries if there is a LIMIT
809        if order_by and (not self.is_subquery() or select._limit):
810            return " ORDER BY " + order_by
811        else:
812            return ""
813
814
815class SybaseSQLSchemaGenerator(compiler.SchemaGenerator):
816    def get_column_specification(self, column, **kwargs):
817
818        colspec = self.preparer.format_column(column)
819
820        if (not getattr(column.table, 'has_sequence', False)) and column.primary_key and \
821                column.autoincrement and isinstance(column.type, sqltypes.Integer):
822            if column.default is None or (isinstance(column.default, schema.Sequence) and column.default.optional):
823                column.sequence = schema.Sequence(column.name + '_seq')
824
825        if hasattr(column, 'sequence'):
826            column.table.has_sequence = column
827            #colspec += " numeric(30,0) IDENTITY"
828            colspec += " Integer IDENTITY"
829        else:
830            colspec += " " + column.type.dialect_impl(self.dialect).get_col_spec()
831
832        if not column.nullable:
833            colspec += " NOT NULL"
834
835        default = self.get_column_default_string(column)
836        if default is not None:
837            colspec += " DEFAULT " + default
838
839        return colspec
840
841
842class SybaseSQLSchemaDropper(compiler.SchemaDropper):
843    def visit_index(self, index):
844        self.append("\nDROP INDEX %s.%s" % (
845            self.preparer.quote_identifier(index.table.name),
846            self.preparer.quote(self._validate_identifier(index.name, False), index.quote)
847            ))
848        self.execute()
849
850
851class SybaseSQLDefaultRunner(base.DefaultRunner):
852    pass
853
854
855class SybaseSQLIdentifierPreparer(compiler.IdentifierPreparer):
856    reserved_words = RESERVED_WORDS
857
858    def __init__(self, dialect):
859        super(SybaseSQLIdentifierPreparer, self).__init__(dialect)
860
861    def _escape_identifier(self, value):
862        #TODO: determin SybaseSQL's escapeing rules
863        return value
864
865    def _fold_identifier_case(self, value):
866        #TODO: determin SybaseSQL's case folding rules
867        return value
868
869
870dialect = SybaseSQLDialect
871dialect.statement_compiler = SybaseSQLCompiler
872dialect.schemagenerator = SybaseSQLSchemaGenerator
873dialect.schemadropper = SybaseSQLSchemaDropper
874dialect.preparer = SybaseSQLIdentifierPreparer
875dialect.defaultrunner = SybaseSQLDefaultRunner
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。