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

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

Install Unix tools  http://hannonlab.cshl.edu/galaxy_unix_tools/galaxy.html
行番号 
1# informix.py
2# Copyright (C) 2005,2006, 2007, 2008, 2009 Michael Bayer mike_mp@zzzcomputing.com
3#
4# coding: gbk
5#
6# This module is part of SQLAlchemy and is released under
7# the MIT License: http://www.opensource.org/licenses/mit-license.php
8
9import datetime
10
11from sqlalchemy import sql, schema, exc, pool, util
12from sqlalchemy.sql import compiler
13from sqlalchemy.engine import default
14from sqlalchemy import types as sqltypes
15
16
17# for offset
18
19class informix_cursor(object):
20    def __init__( self , con ):
21        self.__cursor = con.cursor()
22        self.rowcount = 0
23
24    def offset( self , n ):
25        if n > 0:
26            self.fetchmany( n )
27            self.rowcount = self.__cursor.rowcount - n
28            if self.rowcount < 0:
29                self.rowcount = 0
30        else:
31            self.rowcount = self.__cursor.rowcount
32
33    def execute( self , sql , params ):
34        if params is None or len( params ) == 0:
35            params = []
36
37        return self.__cursor.execute( sql , params )
38
39    def __getattr__( self , name ):
40        if name not in ( 'offset' , '__cursor' , 'rowcount' , '__del__' , 'execute' ):
41            return getattr( self.__cursor , name )
42
43class InfoNumeric(sqltypes.Numeric):
44    def get_col_spec(self):
45        if not self.precision:
46            return 'NUMERIC'
47        else:
48            return "NUMERIC(%(precision)s, %(scale)s)" % {'precision': self.precision, 'scale' : self.scale}
49
50class InfoInteger(sqltypes.Integer):
51    def get_col_spec(self):
52        return "INTEGER"
53
54class InfoSmallInteger(sqltypes.Smallinteger):
55    def get_col_spec(self):
56        return "SMALLINT"
57
58class InfoDate(sqltypes.Date):
59    def get_col_spec( self ):
60        return "DATE"
61
62class InfoDateTime(sqltypes.DateTime ):
63    def get_col_spec(self):
64        return "DATETIME YEAR TO SECOND"
65
66    def bind_processor(self, dialect):
67        def process(value):
68            if value is not None:
69                if value.microsecond:
70                    value = value.replace( microsecond = 0 )
71            return value
72        return process
73
74class InfoTime(sqltypes.Time ):
75    def get_col_spec(self):
76        return "DATETIME HOUR TO SECOND"
77
78    def bind_processor(self, dialect):
79        def process(value):
80            if value is not None:
81                if value.microsecond:
82                    value = value.replace( microsecond = 0 )
83            return value
84        return process
85
86    def result_processor(self, dialect):
87        def process(value):
88            if isinstance( value , datetime.datetime ):
89                return value.time()
90            else:
91                return value
92        return process
93
94class InfoText(sqltypes.String):
95    def get_col_spec(self):
96        return "VARCHAR(255)"
97
98class InfoString(sqltypes.String):
99    def get_col_spec(self):
100        return "VARCHAR(%(length)s)" % {'length' : self.length}
101
102    def bind_processor(self, dialect):
103        def process(value):
104            if value == '':
105                return None
106            else:
107                return value
108        return process
109
110class InfoChar(sqltypes.CHAR):
111    def get_col_spec(self):
112        return "CHAR(%(length)s)" % {'length' : self.length}
113
114class InfoBinary(sqltypes.Binary):
115    def get_col_spec(self):
116        return "BYTE"
117
118class InfoBoolean(sqltypes.Boolean):
119    default_type = 'NUM'
120    def get_col_spec(self):
121        return "SMALLINT"
122
123    def result_processor(self, dialect):
124        def process(value):
125            if value is None:
126                return None
127            return value and True or False
128        return process
129
130    def bind_processor(self, dialect):
131        def process(value):
132            if value is True:
133                return 1
134            elif value is False:
135                return 0
136            elif value is None:
137                return None
138            else:
139                return value and True or False
140        return process
141
142colspecs = {
143    sqltypes.Integer : InfoInteger,
144    sqltypes.Smallinteger : InfoSmallInteger,
145    sqltypes.Numeric : InfoNumeric,
146    sqltypes.Float : InfoNumeric,
147    sqltypes.DateTime : InfoDateTime,
148    sqltypes.Date : InfoDate,
149    sqltypes.Time: InfoTime,
150    sqltypes.String : InfoString,
151    sqltypes.Binary : InfoBinary,
152    sqltypes.Boolean : InfoBoolean,
153    sqltypes.Text : InfoText,
154    sqltypes.CHAR: InfoChar,
155}
156
157
158ischema_names = {
159    0   : InfoString,       # CHAR
160    1   : InfoSmallInteger, # SMALLINT
161    2   : InfoInteger,      # INT
162    3   : InfoNumeric,      # Float
163    3   : InfoNumeric,      # SmallFloat
164    5   : InfoNumeric,      # DECIMAL
165    6   : InfoInteger,      # Serial
166    7   : InfoDate,         # DATE
167    8   : InfoNumeric,      # MONEY
168    10  : InfoDateTime,     # DATETIME
169    11  : InfoBinary,       # BYTE
170    12  : InfoText,         # TEXT
171    13  : InfoString,       # VARCHAR
172    15  : InfoString,       # NCHAR
173    16  : InfoString,       # NVARCHAR
174    17  : InfoInteger,      # INT8
175    18  : InfoInteger,      # Serial8
176    43  : InfoString,       # LVARCHAR
177    -1  : InfoBinary,       # BLOB
178    -1  : InfoText,         # CLOB
179}
180
181
182class InfoExecutionContext(default.DefaultExecutionContext):
183    # cursor.sqlerrd
184    # 0 - estimated number of rows returned
185    # 1 - serial value after insert or ISAM error code
186    # 2 - number of rows processed
187    # 3 - estimated cost
188    # 4 - offset of the error into the SQL statement
189    # 5 - rowid after insert
190    def post_exec(self):
191        if getattr(self.compiled, "isinsert", False) and self.last_inserted_ids() is None:
192            self._last_inserted_ids = [self.cursor.sqlerrd[1]]
193        elif hasattr( self.compiled , 'offset' ):
194            self.cursor.offset( self.compiled.offset )
195        super(InfoExecutionContext, self).post_exec()
196
197    def create_cursor( self ):
198        return informix_cursor( self.connection.connection )
199
200class InfoDialect(default.DefaultDialect):
201    name = 'informix'
202    default_paramstyle = 'qmark'
203    # for informix 7.31
204    max_identifier_length = 18
205
206    def __init__(self, use_ansi=True, **kwargs):
207        self.use_ansi = use_ansi
208        default.DefaultDialect.__init__(self, **kwargs)
209
210    def dbapi(cls):
211        import informixdb
212        return informixdb
213    dbapi = classmethod(dbapi)
214
215    def is_disconnect(self, e):
216        if isinstance(e, self.dbapi.OperationalError):
217            return 'closed the connection' in str(e) or 'connection not open' in str(e)
218        else:
219            return False
220
221    def do_begin(self , connect ):
222        cu = connect.cursor()
223        cu.execute( 'SET LOCK MODE TO WAIT' )
224        #cu.execute( 'SET ISOLATION TO REPEATABLE READ' )
225
226    def type_descriptor(self, typeobj):
227        return sqltypes.adapt_type(typeobj, colspecs)
228
229    def create_connect_args(self, url):
230        if url.host:
231            dsn = '%s@%s' % ( url.database , url.host )
232        else:
233            dsn = url.database
234
235        if url.username:
236            opt = { 'user':url.username , 'password': url.password }
237        else:
238            opt = {}
239
240        return ([dsn], opt)
241
242    def table_names(self, connection, schema):
243        s = "select tabname from systables"
244        return [row[0] for row in connection.execute(s)]
245
246    def has_table(self, connection, table_name, schema=None):
247        cursor = connection.execute("""select tabname from systables where tabname=?""", table_name.lower() )
248        return bool( cursor.fetchone() is not None )
249
250    def reflecttable(self, connection, table, include_columns):
251        c = connection.execute ("select distinct OWNER from systables where tabname=?", table.name.lower() )
252        rows = c.fetchall()
253        if not rows :
254            raise exc.NoSuchTableError(table.name)
255        else:
256            if table.owner is not None:
257                if table.owner.lower() in [r[0] for r in rows]:
258                    owner = table.owner.lower()
259                else:
260                    raise AssertionError("Specified owner %s does not own table %s"%(table.owner, table.name))
261            else:
262                if len(rows)==1:
263                    owner = rows[0][0]
264                else:
265                    raise AssertionError("There are multiple tables with name %s in the schema, you must specifie owner"%table.name)
266
267        c = connection.execute ("""select colname , coltype , collength , t3.default , t1.colno from syscolumns as t1 , systables as t2 , OUTER sysdefaults as t3
268                                    where t1.tabid = t2.tabid and t2.tabname=? and t2.owner=?
269                                      and t3.tabid = t2.tabid and t3.colno = t1.colno
270                                    order by t1.colno""", table.name.lower(), owner )
271        rows = c.fetchall()
272
273        if not rows:
274            raise exc.NoSuchTableError(table.name)
275
276        for name , colattr , collength , default , colno in rows:
277            name = name.lower()
278            if include_columns and name not in include_columns:
279                continue
280
281            # in 7.31, coltype = 0x000
282            #                       ^^-- column type
283            #                      ^-- 1 not null , 0 null
284            nullable , coltype = divmod( colattr , 256 )
285            if coltype not in ( 0 , 13 ) and default:
286                default = default.split()[-1]
287
288            if coltype == 0 or coltype == 13: # char , varchar
289                coltype = ischema_names.get(coltype, InfoString)(collength)
290                if default:
291                    default = "'%s'" % default
292            elif coltype == 5: # decimal
293                precision , scale = ( collength & 0xFF00 ) >> 8 , collength & 0xFF
294                if scale == 255:
295                    scale = 0
296                coltype = InfoNumeric(precision, scale)
297            else:
298                try:
299                    coltype = ischema_names[coltype]
300                except KeyError:
301                    util.warn("Did not recognize type '%s' of column '%s'" %
302                              (coltype, name))
303                    coltype = sqltypes.NULLTYPE
304
305            colargs = []
306            if default is not None:
307                colargs.append(schema.DefaultClause(sql.text(default)))
308
309            table.append_column(schema.Column(name, coltype, nullable = (nullable == 0), *colargs))
310
311        # FK
312        c = connection.execute("""select t1.constrname as cons_name , t1.constrtype as cons_type ,
313                                         t4.colname as local_column , t7.tabname as remote_table ,
314                                         t6.colname as remote_column
315                                    from sysconstraints as t1 , systables as t2 ,
316                                         sysindexes as t3 , syscolumns as t4 ,
317                                         sysreferences as t5 , syscolumns as t6 , systables as t7 ,
318                                         sysconstraints as t8 , sysindexes as t9
319                                   where t1.tabid = t2.tabid and t2.tabname=? and t2.owner=? and t1.constrtype = 'R'
320                                     and t3.tabid = t2.tabid and t3.idxname = t1.idxname
321                                     and t4.tabid = t2.tabid and t4.colno = t3.part1
322                                     and t5.constrid = t1.constrid and t8.constrid = t5.primary
323                                     and t6.tabid = t5.ptabid and t6.colno = t9.part1 and t9.idxname = t8.idxname
324                                     and t7.tabid = t5.ptabid""", table.name.lower(), owner )
325        rows = c.fetchall()
326        fks = {}
327        for cons_name, cons_type, local_column, remote_table, remote_column in rows:
328            try:
329                fk = fks[cons_name]
330            except KeyError:
331                fk = ([], [])
332                fks[cons_name] = fk
333            refspec = ".".join([remote_table, remote_column])
334            schema.Table(remote_table, table.metadata, autoload=True, autoload_with=connection)
335            if local_column not in fk[0]:
336                fk[0].append(local_column)
337            if refspec not in fk[1]:
338                fk[1].append(refspec)
339
340        for name, value in fks.iteritems():
341            table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1] , None, link_to_name=True ))
342
343        # PK
344        c = connection.execute("""select t1.constrname as cons_name , t1.constrtype as cons_type ,
345                                         t4.colname as local_column
346                                    from sysconstraints as t1 , systables as t2 ,
347                                         sysindexes as t3 , syscolumns as t4
348                                   where t1.tabid = t2.tabid and t2.tabname=? and t2.owner=? and t1.constrtype = 'P'
349                                     and t3.tabid = t2.tabid and t3.idxname = t1.idxname
350                                     and t4.tabid = t2.tabid and t4.colno = t3.part1""", table.name.lower(), owner )
351        rows = c.fetchall()
352        for cons_name, cons_type, local_column in rows:
353            table.primary_key.add( table.c[local_column] )
354
355class InfoCompiler(compiler.DefaultCompiler):
356    """Info compiler modifies the lexical structure of Select statements to work under
357    non-ANSI configured Oracle databases, if the use_ansi flag is False."""
358
359    def __init__(self, *args, **kwargs):
360        self.limit = 0
361        self.offset = 0
362
363        compiler.DefaultCompiler.__init__( self , *args, **kwargs )
364
365    def default_from(self):
366        return " from systables where tabname = 'systables' "
367
368    def get_select_precolumns( self , select ):
369        s = select._distinct and "DISTINCT " or ""
370        # only has limit
371        if select._limit:
372            off = select._offset or 0
373            s += " FIRST %s " % ( select._limit + off )
374        else:
375            s += ""
376        return s
377
378    def visit_select(self, select):
379        if select._offset:
380            self.offset = select._offset
381            self.limit  = select._limit or 0
382        # the column in order by clause must in select too
383
384        def __label( c ):
385            try:
386                return c._label.lower()
387            except:
388                return ''
389
390        # TODO: dont modify the original select, generate a new one
391        a = [ __label(c) for c in select._raw_columns ]
392        for c in select._order_by_clause.clauses:
393            if ( __label(c) not in a ):
394                select.append_column( c )
395
396        return compiler.DefaultCompiler.visit_select(self, select)
397
398    def limit_clause(self, select):
399        return ""
400
401    def visit_function( self , func ):
402        if func.name.lower() == 'current_date':
403            return "today"
404        elif func.name.lower() == 'current_time':
405            return "CURRENT HOUR TO SECOND"
406        elif func.name.lower() in ( 'current_timestamp' , 'now' ):
407            return "CURRENT YEAR TO SECOND"
408        else:
409            return compiler.DefaultCompiler.visit_function( self , func )
410
411    def visit_clauselist(self, list, **kwargs):
412        return ', '.join([s for s in [self.process(c) for c in list.clauses] if s is not None])
413
414class InfoSchemaGenerator(compiler.SchemaGenerator):
415    def get_column_specification(self, column, first_pk=False):
416        colspec = self.preparer.format_column(column)
417        if column.primary_key and len(column.foreign_keys)==0 and column.autoincrement and \
418           isinstance(column.type, sqltypes.Integer) and not getattr( self , 'has_serial' , False ) and first_pk:
419            colspec += " SERIAL"
420            self.has_serial = True
421        else:
422            colspec += " " + column.type.dialect_impl(self.dialect).get_col_spec()
423            default = self.get_column_default_string(column)
424            if default is not None:
425                colspec += " DEFAULT " + default
426
427        if not column.nullable:
428            colspec += " NOT NULL"
429
430        return colspec
431
432    def post_create_table(self, table):
433        if hasattr( self , 'has_serial' ):
434            del self.has_serial
435        return ''
436
437    def visit_primary_key_constraint(self, constraint):
438        # for informix 7.31 not support constraint name
439        name = constraint.name
440        constraint.name = None
441        super(InfoSchemaGenerator, self).visit_primary_key_constraint(constraint)
442        constraint.name = name
443
444    def visit_unique_constraint(self, constraint):
445        # for informix 7.31 not support constraint name
446        name = constraint.name
447        constraint.name = None
448        super(InfoSchemaGenerator, self).visit_unique_constraint(constraint)
449        constraint.name = name
450
451    def visit_foreign_key_constraint( self , constraint ):
452        if constraint.name is not None:
453            constraint.use_alter = True
454        else:
455            super( InfoSchemaGenerator , self ).visit_foreign_key_constraint( constraint )
456
457    def define_foreign_key(self, constraint):
458        # for informix 7.31 not support constraint name
459        if constraint.use_alter:
460            name = constraint.name
461            constraint.name = None
462            self.append( "CONSTRAINT " )
463            super(InfoSchemaGenerator, self).define_foreign_key(constraint)
464            constraint.name = name
465            if name is not None:
466                self.append( " CONSTRAINT " + name )
467        else:
468            super(InfoSchemaGenerator, self).define_foreign_key(constraint)
469
470    def visit_index(self, index):
471        if len( index.columns ) == 1 and index.columns[0].foreign_key:
472            return
473        super(InfoSchemaGenerator, self).visit_index(index)
474
475class InfoIdentifierPreparer(compiler.IdentifierPreparer):
476    def __init__(self, dialect):
477        super(InfoIdentifierPreparer, self).__init__(dialect, initial_quote="'")
478
479    def _requires_quotes(self, value):
480        return False
481
482class InfoSchemaDropper(compiler.SchemaDropper):
483    def drop_foreignkey(self, constraint):
484        if constraint.name is not None:
485            super( InfoSchemaDropper , self ).drop_foreignkey( constraint )
486
487dialect = InfoDialect
488poolclass = pool.SingletonThreadPool
489dialect.statement_compiler = InfoCompiler
490dialect.schemagenerator = InfoSchemaGenerator
491dialect.schemadropper = InfoSchemaDropper
492dialect.preparer = InfoIdentifierPreparer
493dialect.execution_ctx_cls = InfoExecutionContext
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。