|
From: <sub...@co...> - 2012-03-02 18:25:24
|
Author: phd Date: Fri Mar 2 11:25:11 2012 New Revision: 4508 Log: Major update by Petr Jakes in FirebirdConnection: introspection was completely rewritten and extended; ``charset`` was renamed to ``dbEncoding``. Modified: SQLObject/trunk/docs/Authors.txt SQLObject/trunk/docs/News.txt SQLObject/trunk/sqlobject/firebird/firebirdconnection.py Modified: SQLObject/trunk/docs/Authors.txt ============================================================================== --- SQLObject/trunk/docs/Authors.txt Thu Mar 1 08:46:20 2012 (r4507) +++ SQLObject/trunk/docs/Authors.txt Fri Mar 2 11:25:11 2012 (r4508) @@ -25,6 +25,7 @@ * David Keeney <dkeeney at rdbhost.com> * Daniel Fetchinson <fetchinson at googlemail.com> * Neil Muller <drnlmuller+sqlobject at gmail.com> +* Petr Jakes <petr.jakes at tpc.cz> * Oleg Broytman <ph...@ph...> .. image:: http://sflogo.sourceforge.net/sflogo.php?group_id=74338&type=10 Modified: SQLObject/trunk/docs/News.txt ============================================================================== --- SQLObject/trunk/docs/News.txt Thu Mar 1 08:46:20 2012 (r4507) +++ SQLObject/trunk/docs/News.txt Fri Mar 2 11:25:11 2012 (r4508) @@ -16,6 +16,9 @@ * PostgresConnection performs translation of exceptions to standard SQLObject's hierarchy of exceptions. +* Major update in FirebirdConnection: introspection was completely + rewritten and extended; ``charset`` was renamed to ``dbEncoding``. + SQLObject 1.2.2 =============== Modified: SQLObject/trunk/sqlobject/firebird/firebirdconnection.py ============================================================================== --- SQLObject/trunk/sqlobject/firebird/firebirdconnection.py Thu Mar 1 08:46:20 2012 (r4507) +++ SQLObject/trunk/sqlobject/firebird/firebirdconnection.py Fri Mar 2 11:25:11 2012 (r4508) @@ -1,8 +1,11 @@ import re import os +import warnings from sqlobject.dbconnection import DBAPI from sqlobject import col +kinterbasdb = None + class FirebirdConnection(DBAPI): supportTransactions = False @@ -11,10 +14,15 @@ limit_re = re.compile('^\s*(select )(.*)', re.IGNORECASE) - def __init__(self, host, port, db, user='sysdba', + def __init__(self, host, db, port='3050', user='sysdba', password='masterkey', autoCommit=1, dialect=None, role=None, charset=None, **kw): - import kinterbasdb + global kinterbasdb + if kinterbasdb is None: + import kinterbasdb + # See http://kinterbasdb.sourceforge.net/dist_docs/usage.html + # for an explanation; in short: use datetime, decimal and unicode. + kinterbasdb.init(type_conv=200) self.module = kinterbasdb self.host = host @@ -27,8 +35,11 @@ else: self.dialect = None self.role = role - self.charset = charset - + if charset: + self.dbEncoding = charset.replace('-', '') # encoding defined by user in the connection string + else: + self.dbEncoding = charset + self.defaultDbEncoding = '' # encoding defined during database creation and stored in the database DBAPI.__init__(self, **kw) @classmethod @@ -41,7 +52,7 @@ if (path[0] == '/') and path[-3:].lower() not in ('fdb', 'gdb'): path = path[1:] path = path.replace('/', os.sep) - return cls(host, port, db=path, user=auth, password=password, **args) + return cls(host, port=port, db=path, user=auth, password=password, **args) def _runWithConnection(self, meth, *args): if not self.autoCommit: @@ -77,7 +88,7 @@ user=self.user, password=self.password, role=self.role, - charset=self.charset, + charset=self.dbEncoding, **extra ) @@ -163,64 +174,215 @@ def delColumn(self, sqlmeta, column): self.query('ALTER TABLE %s DROP %s' % (sqlmeta.table, column.dbName)) + def readDefaultEncodingFromDB(self): + if self.defaultDbEncoding is "": # get out if encoding is known allready (can by None as well)) + self.defaultDbEncoding = str(self.queryOne("SELECT rdb$character_set_name FROM rdb$database")[0].strip().lower()) # encoding defined during db creation + if self.defaultDbEncoding == "none": + self.defaultDbEncoding = None + if self.dbEncoding != self.defaultDbEncoding: + warningText = """\n + Database charset: %s is different from connection charset: %s.\n""" % (self.defaultDbEncoding, self.dbEncoding) + warnings.warn(warningText) + #TODO: ??? print out the uri string, so user can see what is going on + warningText = \ + """\n + Every CHAR or VARCHAR field can (or, better: must) have a character set defined in Firebird. + In the case, field charset is not defined, SQLObject try to use a db default encoding instead. + Firebird is unable to transliterate between character sets. + So you must set the correct values on the server and on the client if everything is to work smoothely.\n""" + warnings.warn(warningText) + + if not self.dbEncoding: # defined by user in the connection string + self.dbEncoding = self.defaultDbEncoding + warningText = """\n + encoding: %s will be used as default for this connection\n""" % self.dbEncoding + warnings.warn(warningText) + def columnsFromSchema(self, tableName, soClass): """ Look at the given table and create Col instances (or subclasses of Col) for the fields it finds in that table. """ - fieldqry = """\ - SELECT rf.RDB$FIELD_NAME as field, - t.RDB$TYPE_NAME as t, - f.RDB$FIELD_LENGTH as flength, - f.RDB$FIELD_SCALE as fscale, - rf.RDB$NULL_FLAG as nullAllowed, - coalesce(rf.RDB$DEFAULT_SOURCE, f.rdb$default_source) as thedefault, - f.RDB$FIELD_SUB_TYPE as blobtype - FROM RDB$RELATION_FIELDS rf - INNER JOIN RDB$FIELDS f ON rf.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME - INNER JOIN RDB$TYPES t ON f.RDB$FIELD_TYPE = t.RDB$TYPE - WHERE rf.RDB$RELATION_NAME = '%s' - AND t.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'""" + self.readDefaultEncodingFromDB() + + fieldQuery="""\ + SELECT r.RDB$FIELD_NAME AS field_name, + CASE f.RDB$FIELD_TYPE + when 7 then 'smallint' + when 8 then 'integer' + when 16 then 'int64' + when 9 then 'quad' + when 10 then 'float' + when 11 then 'd_float' + when 17 then 'boolean' + when 27 then 'double' + when 12 then 'date' + when 13 then 'time' + when 35 then 'timestamp' + when 261 then 'blob' + when 37 then 'varchar' + when 14 then 'char' + when 40 then 'cstring' + when 45 then 'blob_id' + ELSE 'UNKNOWN' + END AS field_type, + case f.rdb$field_type + when 7 then + case f.rdb$field_sub_type + when 1 then 'numeric' + when 2 then 'decimal' + end + when 8 then + case f.rdb$field_sub_type + when 1 then 'numeric' + when 2 then 'decimal' + end + when 16 then + case f.rdb$field_sub_type + when 1 then 'numeric' + when 2 then 'decimal' + else 'bigint' + end + when 14 then + case f.rdb$field_sub_type + when 0 then 'unspecified' + when 1 then 'binary' + when 3 then 'acl' + else + case + when f.rdb$field_sub_type is null then 'unspecified' + end + end + when 37 then + case f.rdb$field_sub_type + when 0 then 'unspecified' + when 1 then 'text' + when 3 then 'acl' + else + case + when f.rdb$field_sub_type is null then 'unspecified' + end + end + when 261 then + case f.rdb$field_sub_type + when 0 then 'unspecified' + when 1 then 'text' + when 2 then 'blr' + when 3 then 'acl' + when 4 then 'reserved' + when 5 then 'encoded-meta-data' + when 6 then 'irregular-finished-multi-db-tx' + when 7 then 'transactional_description' + when 8 then 'external_file_description' + end + end as "ActualSubType", + f.RDB$FIELD_LENGTH AS field_length, + f.RDB$FIELD_PRECISION AS field_precision, + f.RDB$FIELD_SCALE AS field_scale, + cset.RDB$CHARACTER_SET_NAME AS field_charset, + coll.RDB$COLLATION_NAME AS field_collation, + r.rdb$default_source, + r.RDB$NULL_FLAG AS field_not_null_constraint, + r.RDB$DESCRIPTION AS field_description + FROM RDB$RELATION_FIELDS r + LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME + LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID + LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID + WHERE r.RDB$RELATION_NAME='%s' -- table name + ORDER BY r.RDB$FIELD_POSITION + """ - colData = self.queryAll(fieldqry % tableName.upper()) + colData = self.queryAll(fieldQuery % tableName.upper()) results = [] - for field, t, flength, fscale, nullAllowed, thedefault, blobType in colData: + for field, fieldType, fieldSubtype, fieldLength, fieldPrecision, fieldScale, fieldCharset, collationName, defaultSource, fieldNotNullConstraint, fieldDescription in colData: field = field.strip().lower() - if thedefault: - thedefault = thedefault.split(' ')[1] - if thedefault.startswith("'") and thedefault.endswith("'"): - thedefault = thedefault[1:-1] + fieldType = fieldType.strip() + if fieldCharset: + fieldCharset = str(fieldCharset.strip()) + if fieldCharset.startswith('UNICODE_FSS'): # 'UNICODE_FSS' is less strict Firebird/Interbase UTF8 definition + fieldCharset = "UTF8" + if fieldSubtype: + fieldSubtype=fieldSubtype.strip() + if fieldType == "int64": + fieldType = fieldSubtype + + if defaultSource: # can look like: "DEFAULT 0", "DEFAULT 'default text'", None + defaultSource = defaultSource.split(' ')[1] + if defaultSource.startswith ("'") and defaultSource.endswith ("'"): + defaultSource = str(defaultSource[1:-1]) + elif fieldType in ("integer", "smallint", "bigint"): + defaultSource=int(defaultSource) + elif fieldType in ("float", "double"): + defaultSource=float(defaultSource) + #TODO: other types for defaultSource + # elif fieldType == "datetime": + idName = str(soClass.sqlmeta.idName or 'id').upper() if field.upper() == idName: continue - colClass, kw = self.guessClass(t, flength, fscale) - kw['name'] = soClass.sqlmeta.style.dbColumnToPythonAttr(field).strip() - kw['dbName'] = field - kw['notNone'] = not nullAllowed - kw['default'] = thedefault + if fieldScale: + #PRECISION refers to the total number of digits, and SCALE refers to the number of digits to the right of the decimal point + #Both numbers can be from 1 to 18 (SQL dialect 1: 1-15), but SCALE mustbe less than or equal to PRECISION + if fieldScale > fieldLength: + fieldScale = fieldLength + colClass, kw = self.guessClass(fieldType, fieldLength, fieldCharset, fieldScale, ) + kw['name'] = str(soClass.sqlmeta.style.dbColumnToPythonAttr(field).strip()) + kw['dbName'] = str(field) + kw['notNone'] = not fieldNotNullConstraint + kw['default'] = defaultSource results.append(colClass(**kw)) return results - _intTypes=['INT64', 'SHORT','LONG'] - _dateTypes=['DATE','TIME','TIMESTAMP'] - - def guessClass(self, t, flength, fscale=None): + def guessClass(self, t, flength, fCharset, fscale=None): """ An internal method that tries to figure out what Col subclass is appropriate given whatever introspective information is available -- both very database-specific. """ - if t in self._intTypes: + ##TODO: check if negative values are allowed for fscale + + if t == 'smallint': # -32,768 to +32,767, 16 bits return col.IntCol, {} - elif t == 'VARYING': - return col.StringCol, {'length': flength} - elif t == 'TEXT': - return col.StringCol, {'length': flength, - 'varchar': False} - elif t in self._dateTypes: + elif t == 'integer': # -2,147,483,648 to +2,147,483,647, 32 bits + return col.IntCol, {} + elif t == 'bigint': # -2^63 to 2^63-1 or -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, 64 bits + return col.IntCol, {} + elif t == 'float': # 32 bits, 3.4x10^-38 to 3.4x10^38, 7 digit precision (7 significant decimals) + return col.FloatCol, {} + elif t == 'double': # 64 bits, 1.7x10^-308 to 1.7x10^308, 15 digit precision (15 significant decimals) + return col.FloatCol, {} + elif t == 'numeric': # Numeric and Decimal are internally stored as smallint, integer or bigint depending on the size. They can handle up to 18 digits. + if (not flength or not fscale): # If neither PRECISION nor SCALE are specified, Firebird/InterBase defines the column as INTEGER instead of NUMERIC and stores only the integer portion of the value + return col.IntCol, {} + return col.DecimalCol, {'size': flength, 'precision': fscale} # check if negative values are allowed for fscale + + elif t == 'decimal': # Numeric and Decimal are internally stored as smallint, integer or bigint depending on the size. They can handle up to 18 digits. + return col.DecimalCol, {'size': flength, 'precision': fscale} # check if negative values are allowed for fscale + elif t == 'date': # 32 bits, 1 Jan 100. to 29 Feb 32768. + return col.DateCol, {} + elif t == 'time': # 32 bits, 00:00 to 23:59.9999 + return col.TimeCol, {} + elif t == 'timestamp': # 64 bits, 1 Jan 100 to 28 Feb 32768. return col.DateTimeCol, {} + elif t == 'char': # 32767 bytes + if fCharset and (fCharset != "NONE"): + return col.UnicodeCol, {'length': flength, 'varchar': False, 'dbEncoding': fCharset} + elif self.dbEncoding: + return col.UnicodeCol, {'length': flength, 'varchar': False, 'dbEncoding': self.dbEncoding} + else: + return col.StringCol, {'length': flength, 'varchar': False} + elif t == 'varchar': # 32767 bytes + if fCharset and (fCharset != "NONE"): + return col.UnicodeCol, {'length': flength, 'varchar': True, 'dbEncoding': fCharset} + elif self.dbEncoding: + return col.UnicodeCol, {'length': flength, 'varchar': True, 'dbEncoding': self.dbEncoding} + else: + return col.StringCol, {'length': flength, 'varchar': True} + + elif t == 'blob': # 32GB + return col.BLOBCol, {} else: return col.Col, {} |
