0001from sqlobject.dbconnection import DBAPI
0002kinterbasdb = None
0003import re
0004import os
0005
0006class FirebirdConnection(DBAPI):
0007
0008 supportTransactions = False
0009 dbName = 'firebird'
0010 schemes = [dbName]
0011
0012 def __init__(self, host, db, user='sysdba',
0013 passwd='masterkey', autoCommit=1,
0014 dialect=None, role=None, charset=None, **kw):
0015 global kinterbasdb
0016 if kinterbasdb is None:
0017 import kinterbasdb
0018 self.module = kinterbasdb
0019
0020 self.limit_re = re.compile('^\s*(select )(.*)', re.IGNORECASE)
0021
0022 self.host = host
0023 self.db = db
0024 self.user = user
0025 self.passwd = passwd
0026 if dialect:
0027 self.dialect = int(dialect)
0028 else:
0029 self.dialect = None
0030 self.role = role
0031 self.charset = charset
0032
0033 DBAPI.__init__(self, **kw)
0034
0035 def connectionFromURI(cls, uri):
0036 auth, password, host, port, path, args = cls._parseURI(uri)
0037 if not password:
0038 password = 'masterkey'
0039 if not auth:
0040 auth='sysdba'
0041 if os.name == 'nt' and path[0] == '/':
0042
0043 path = path[1:]
0044 path = path.replace('/', os.sep)
0045 return cls(host, db=path, user=auth, passwd=password, **args)
0046 connectionFromURI = classmethod(connectionFromURI)
0047
0048 def _runWithConnection(self, meth, *args):
0049 if not self.autoCommit:
0050 return DBAPI._runWithConnection(self, meth, args)
0051 conn = self.getConnection()
0052
0053 try:
0054 conn.begin()
0055 except kinterbasdb.ProgrammingError:
0056 pass
0057 try:
0058 val = meth(conn, *args)
0059 try:
0060 conn.commit()
0061 except kinterbasdb.ProgrammingError:
0062 pass
0063 finally:
0064 self.releaseConnection(conn)
0065 return val
0066
0067 def _setAutoCommit(self, conn, auto):
0068
0069
0070 pass
0071
0072 def makeConnection(self):
0073 extra = {}
0074 if self.dialect:
0075 extra['dialect'] = self.dialect
0076 return kinterbasdb.connect(
0077 host=self.host,
0078 database=self.db,
0079 user=self.user,
0080 password=self.passwd,
0081 role=self.role,
0082 charset=self.charset,
0083 **extra
0084 )
0085
0086 def _queryInsertID(self, conn, soInstance, id, names, values):
0087 """Firebird uses 'generators' to create new ids for a table.
0088 The users needs to create a generator named GEN_<tablename>
0089 for each table this method to work."""
0090 table = soInstance.sqlmeta.table
0091 idName = soInstance.sqlmeta.idName
0092 sequenceName = getattr(soInstance, '_idSequence',
0093 'GEN_%s' % table)
0094 c = conn.cursor()
0095 if id is None:
0096 c.execute('SELECT gen_id(%s,1) FROM rdb$database'
0097 % sequenceName)
0098 id = c.fetchone()[0]
0099 names = [idName] + names
0100 values = [id] + values
0101 q = self._insertSQL(table, names, values)
0102 if self.debug:
0103 self.printDebug(conn, q, 'QueryIns')
0104 c.execute(q)
0105 if self.debugOutput:
0106 self.printDebug(conn, id, 'QueryIns', 'result')
0107 return id
0108
0109 def _queryAddLimitOffset(self, query, start, end):
0110 """Firebird slaps the limit and offset (actually 'first' and
0111 'skip', respectively) statement right after the select."""
0112 if not start:
0113 limit_str = "SELECT FIRST %i" % end
0114 if not end:
0115 limit_str = "SELECT SKIP %i" % start
0116 else:
0117 limit_str = "SELECT FIRST %i SKIP %i" % (end-start, start)
0118
0119 match = self.limit_re.match(query)
0120 if match and len(match.groups()) == 2:
0121 return ' '.join([limit_str, match.group(2)])
0122 else:
0123 return query
0124
0125 def createTable(self, soClass):
0126 self.query('CREATE TABLE %s (\n%s\n)' % (soClass.sqlmeta.table, self.createColumns(soClass)))
0128 self.query("CREATE GENERATOR GEN_%s" % soClass.sqlmeta.table)
0129
0130 def createColumn(self, soClass, col):
0131 return col.firebirdCreateSQL()
0132
0133 def createIDColumn(self, soClass):
0134 return '%s INT NOT NULL PRIMARY KEY' % soClass.sqlmeta.idName
0135
0136 def createIndexSQL(self, soClass, index):
0137 return index.firebirdCreateIndexSQL(soClass)
0138
0139 def joinSQLType(self, join):
0140 return 'INT NOT NULL'
0141
0142 def tableExists(self, tableName):
0143
0144
0145
0146 result = self.queryOne("SELECT COUNT(rdb$relation_name) FROM rdb$relations WHERE rdb$relation_name = '%s'"
0147 % tableName.upper())
0148 return result[0]
0149
0150 def addColumn(self, tableName, column):
0151 self.query('ALTER TABLE %s ADD %s' %
0152 (tableName,
0153 column.firebirdCreateSQL()))
0154
0155 def dropTable(self, tableName, cascade=False):
0156 self.query("DROP TABLE %s" % tableName)
0157 self.query("DROP GENERATOR GEN_%s" % tableName)
0158
0159 def delColumn(self, tableName, column):
0160 self.query('ALTER TABLE %s DROP %s' %
0161 (tableName,
0162 column.dbName))
0163
0164 def columnsFromSchema(self, tableName, soClass):
0165 """
0166 Look at the given table and create Col instances (or
0167 subclasses of Col) for the fields it finds in that table.
0168 """
0169
0170 fieldqry = """\
0171 SELECT RDB$RELATION_FIELDS.RDB$FIELD_NAME as field,
0172 RDB$TYPES.RDB$TYPE_NAME as t,
0173 RDB$FIELDS.RDB$FIELD_LENGTH as flength,
0174 RDB$FIELDS.RDB$FIELD_SCALE as fscale,
0175 RDB$RELATION_FIELDS.RDB$NULL_FLAG as nullAllowed,
0176 RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE as thedefault,
0177 RDB$FIELDS.RDB$FIELD_SUB_TYPE as blobtype
0178 FROM RDB$RELATION_FIELDS
0179 INNER JOIN RDB$FIELDS ON
0180 (RDB$RELATION_FIELDS.RDB$FIELD_SOURCE = RDB$FIELDS.RDB$FIELD_NAME)
0181 INNER JOIN RDB$TYPES ON (RDB$FIELDS.RDB$FIELD_TYPE =
0182 RDB$TYPES.RDB$TYPE)
0183 WHERE
0184 (RDB$RELATION_FIELDS.RDB$RELATION_NAME = '%s')
0185 AND (RDB$TYPES.RDB$FIELD_NAME = 'RDB$FIELD_TYPE')"""
0186
0187 colData = self.queryAll(fieldqry % tableName.upper())
0188 results = []
0189 for field, t, flength, fscale, nullAllowed, thedefault, blobType in colData:
0190 if field == 'id':
0191 continue
0192 colClass, kw = self.guessClass(t, flength, fscale)
0193 kw['name'] = soClass.sqlmeta.style.dbColumnToPythonAttr(field)
0194 kw['notNone'] = not nullAllowed
0195 kw['default'] = thedefault
0196 results.append(colClass(**kw))
0197 return results
0198
0199 _intTypes=['INT64', 'SHORT','LONG']
0200 _dateTypes=['DATE','TIME','TIMESTAMP']
0201
0202 def guessClass(self, t, flength, fscale=None):
0203 """
0204 An internal method that tries to figure out what Col subclass
0205 is appropriate given whatever introspective information is
0206 available -- both very database-specific.
0207 """
0208
0209 if t in self._intTypes:
0210 return col.IntCol, {}
0211 elif t == 'VARYING':
0212 return col.StringCol, {'length': flength}
0213 elif t == 'TEXT':
0214 return col.StringCol, {'length': flength,
0215 'varchar': False}
0216 elif t in self._dateTypes:
0217 return col.DateTimeCol, {}
0218 else:
0219 return col.Col, {}