Changeset 55

Timestamp:

04/02/07 18:07:48

Author:

fumanchu

Message:

sqlserver and msaccess now work.

Files:

• trunk/geniusql/adapters.py (modified) (4 diffs)
• trunk/geniusql/codewalk.py (modified) (1 diff)
• trunk/geniusql/dbtypes.py (modified) (10 diffs)
• trunk/geniusql/objects.py (modified) (1 diff)
• trunk/geniusql/providers/__init__.py (modified) (1 diff)
• trunk/geniusql/providers/ado.py (modified) (4 diffs)
• trunk/geniusql/providers/msaccess.py (modified) (11 diffs)
• trunk/geniusql/providers/sqlserver.py (modified) (14 diffs)
• trunk/geniusql/test/test_msaccess.py (modified) (3 diffs)
• trunk/geniusql/typerefs.py (modified) (1 diff)

trunk/geniusql/adapters.py

@@ -143 +143 @@
-    
-    def database_type(self, pytype, hints=None):
-commend a DatabaseTyp
+turn a DatabaseType instanc
-        
-        hints: if provided, this should be a dict of property attributes
@@ -173 +173 @@
-        for dbtype in self.known_types['varchar']:
-            if bytes <= dbtype.max_bytes:
-                print dbtype, bytes
-                return dbtype(bytes=bytes)
-        raise ValueError("%r is greater than the maximum bytes %r."
@@ -332 +331 @@
-        """Return the SQL for the given binary operation."""
-        return "%s %s %s" % (op1.sql, op, op2.sql)
+    
+    def __copy__(self):
+        return self.__class__()
+    copy = __copy__
-
-
@@ -500 +503 @@
-        # Coerce to str for pickle.loads restriction.
-        if isinstance(value, unicode):
-self.encoding
+'raw-unicode-escape'
-        else:
-            value = str(value)

trunk/geniusql/codewalk.py

@@ -175 +175 @@
-        b = self._bytecode
-        if verbose:
-
+\n
-        b_len = len(b)      # Speed hack
-        while self.cursor < b_len:

trunk/geniusql/dbtypes.py

@@ -8 +8 @@
-    """A database type, such as 'REAL' or 'DATE' (with size details)."""
-    
+    # This isn't the most elegant solution, but it's better than making
+    # producers of each subclass write their own repr and copy methods.
+    _initargs = ()
+    
+    # These are class-level attributes and should not be overridden or copied
+    # (they're only used at the class level, so it wouldn't make much sense).
-    default_pytype = None
-    default_adapters = None
@@ -17 +23 @@
-    
-    def __repr__(self):
-
+
+
+
+
+
+
+
+
-    
-    def cast(self, sql, totype):
@@ -28 +41 @@
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-    
-    # The maximum allowable maximum.
-    max_bytes = 255
-    # Whether the type allows the bytes to be configured.
-    fixed_bytes = False
-    
-    _bytes = 255
@@ -40 +67 @@
-        return self._bytes
-    def _set_bytes(self, value):
-        if self.fixed_bytes:
-            value = self.max_bytes
-        if value is not None:
-            if value < 1:
@@ -54 +79 @@
-    def ddl(self):
-        """Return the type for use in CREATE or ALTER statements."""
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-    variable = True
-    
@@ -82 +114 @@
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-    
-    default_pytype = int
@@ -109 +167 @@
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-    
-    # Max binary precision for floating-point columns (= 53 for PostgreSQL 8).
@@ -118 +192 @@
-    max_precision = 53
-    
-    # Whether the type allows the precision to be configured.
-    fixed_precision = False
-    
-    _precision = 255
-    def _get_precision(self):
-        return self._precision
-    def _set_precision(self, value):
-        if self.fixed_precision:
-            value = self.max_precision
-        if value is not None:
-            if value > 1:
@@ -140 +209 @@
-    def ddl(self):
-        """Return the type for use in CREATE or ALTER statements."""
-
-
-
-
-
-
+
+
+
+
-    default_pytype = float
-
-
-class ExactNumericType(DatabaseType):
+    
+    _initargs = DatabaseType._initargs + ("precision", "max_precision",
+                                          "scale")
-    
-    # For exact numeric types, 'precision' refers to decimal digits
@@ -215 +285 @@
-    _max = None
-    
+    _initargs = DatabaseType._initargs + ("_min", "_max")
+    
-    def min(self):
-        """Return the minimum value allowed."""

trunk/geniusql/objects.py

@@ -155 +155 @@
-    
-    def __copy__(self):
-
-
+
+
+
-        newcol.autoincrement = self.autoincrement
-        newcol.initial = self.initial
-
+.copy()
-        return newcol
-    copy = __copy__

trunk/geniusql/providers/__init__.py

@@ -50 +50 @@
-
-registry = _Registry({
-ado
-ado
+msaccess
+msaccess
-    
-    "firebird": "geniusql.providers.firebird.FirebirdDatabase",

trunk/geniusql/providers/ado.py

@@ -25 +25 @@
-import pywintypes
-import datetime
-
-import time
-
-import threading
-import warnings
-
-
-import geniusql
-, select, typerefs
+
-from geniusql import isolation as _isolation
-
@@ -52 +47 @@
-
-adUseClient = 3
-
-# See http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
-dbtypes = {# ADO Name           SQL Server              MS Access
-        0: 'EMPTY',
-        2: 'SMALLINT',        # SMALLINT                INTEGER
-        3: 'INTEGER',         # IDENTITY (6.5), INT     AUTONUMBER, LONG
-        4: 'SINGLE',          # REAL                    SINGLE
-        5: 'DOUBLE',          # FLOAT                   DOUBLE
-        6: 'CURRENCY',        # MONEY, SMALLMONEY       CURRENCY
-        7: 'DATE',            #                         DATETIME (Access 97)
-        8: 'BSTR', 9: 'IDISPATCH', 10: 'ERROR',
-        11: 'BOOLEAN',        # BIT                     YESNO
-        12: 'VARIANT',        # SQL_VARIANT (2000 +)
-        13: 'IUNKNOWN', 14: 'DECIMAL', 16: 'TINYINT',
-        17: 'UNSIGNEDTINYINT',# TINYINT                 BYTE
-        18: 'UNSIGNEDSMALLINT', 19: 'UNSIGNEDINT',
-        20: 'BIGINT',         # BIGINT
-        21: 'UNSIGNEDBIGINT',
-        72: 'GUID',
-        128: 'BINARY',        # BINARY, TIMESTAMP
-        129: 'CHAR',          # CHAR
-        130: 'WCHAR',         # NCHAR (7.0+)
-        131: 'NUMERIC',       # DECIMAL, NUMERIC        DECIMAL (Access 2000)
-        132: 'USERDEFINED',
-        133: 'DBDATE', 134: 'DBTIME',
-        135: 'DBTIMESTAMP',   # DATETIME, SMALLDATETIME DATETIME (ODBC 97)
-        200: 'VARCHAR',       # VARCHAR                 TEXT (Access 97)
-        201: 'LONGVARCHAR',   # TEXT                    MEMO (Access 97)
-        202: 'VARWCHAR',      # NVARCHAR                TEXT (Access 2000)
-        203: 'LONGVARWCHAR',  # NTEXT (7.0+)            MEMO (Access 2000+)
-        204: 'VARBINARY',     # VARBINARY
-        205: 'LONGVARBINARY', # IMAGE                   OLEOBJECT
-}
-
-DBCOLUMNFLAGS_WRITE = 0x4
@@ -264 +226 @@
-        raise TypeError("unsupported operand type(s) for %s: "
-                        "%r and %r" % (op, op1.pytype, op2.pytype))
-
-
-class CURRENCY(adapters.SQL92DOUBLE):
-    def pull(self, value):
-        if isinstance(value, tuple):
-            # See http://groups.google.com/group/comp.lang.python/
-            #           browse_frm/thread/fed03c64735c9e9c
-            value = map(long, value)
-            return ((value[1] & 0xFFFFFFFFL) | (value[0] << 32)) / 1e4
-        return float(value)
-
-class decimalCURRENCY(adapters.DECIMAL):
-    def pull(self, value):
-        # pywin32 build 205 began support for returning
-        # COM Currency objects as decimal objects.
-        # See http://pywin32.cvs.sourceforge.net/pywin32/pywin32/CHANGES.txt?view=markup
-        if not isinstance(value, typerefs.decimal.Decimal):
-            # See http://groups.google.com/group/comp.lang.python/
-            #           browse_frm/thread/fed03c64735c9e9c
-            value = map(long, value)
-            value = (value[1] & 0xFFFFFFFFL) | (value[0] << 32)
-            return typerefs.decimal.Decimal(value) / 10000
-        return value
-
-class fpCURRENCY(adapters.FIXEDPOINT):
-    def pull(self, value):
-        if isinstance(value, typerefs.decimal.Decimal):
-            value = str(value)
-            scale = 0
-            atoms = value.rsplit(".", 1)
-            if len(atoms) > 1:
-                scale = len(atoms[-1])
-            return typerefs.fixedpoint.FixedPoint(value, scale)
-        else:
-            # See http://groups.google.com/group/comp.lang.python/
-            #           browse_frm/thread/fed03c64735c9e9c
-            value = map(long, value)
-            value = (value[1] & 0xFFFFFFFFL) | (value[0] << 32)
-            return typerefs.fixedpoint.FixedPoint(value, 4) / 1e4
-
-
@@ -555 +478 @@
-        raise errors.MappingError(tablename)
-    
-    def _get_columns(self, tablename, conn=None):
-        # For some reason, adSchemaPrimaryKeys would only return a single
-        # record for a PK that had multiple columns. Use adSchemaIndexes.
-        # coldefs will be:
-        # [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
-        # (u'INDEX_CATALOG', 202), (u'INDEX_SCHEMA', 202), (u'INDEX_NAME', 202),
-        # (u'PRIMARY_KEY', 11), (u'UNIQUE', 11), (u'CLUSTERED', 11), (u'TYPE', 18),
-        # (u'FILL_FACTOR', 3), (u'INITIAL_SIZE', 3), (u'NULLS', 3),
-        # (u'SORT_BOOKMARKS', 11), (u'AUTO_UPDATE', 11), (u'NULL_COLLATION', 3),
-        # (u'ORDINAL_POSITION', 19), (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72),
-        # (u'COLUMN_PROPID', 19), (u'COLLATION', 2), (u'CARDINALITY', 21),
-        # (u'PAGES', 3), (u'FILTER_CONDITION', 202), (u'INTEGRATED', 11)]
-        data, _ = self.db.fetch(adSchemaIndexes, conn=conn, schema=True)
-        pknames = [row[17] for row in data
-                   if (tablename == row[2]) and row[6]]
-        
-        # columns will be
-        # [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
-        # (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72), (u'COLUMN_PROPID', 19),
-        # (u'ORDINAL_POSITION', 19), (u'COLUMN_HASDEFAULT', 11),
-        # (u'COLUMN_DEFAULT', 203), (u'COLUMN_FLAGS', 19), (u'IS_NULLABLE', 11),
-        # (u'DATA_TYPE', 18), (u'TYPE_GUID', 72), (u'CHARACTER_MAXIMUM_LENGTH', 19),
-        # (u'CHARACTER_OCTET_LENGTH', 19), (u'NUMERIC_PRECISION', 18),
-        # (u'NUMERIC_SCALE', 2), (u'DATETIME_PRECISION', 19),
-        # (u'CHARACTER_SET_CATALOG', 202), (u'CHARACTER_SET_SCHEMA', 202),
-        # (u'CHARACTER_SET_NAME', 202), (u'COLLATION_CATALOG', 202),
-        # (u'COLLATION_SCHEMA', 202), (u'COLLATION_NAME', 202),
-        # (u'DOMAIN_CATALOG', 202), (u'DOMAIN_SCHEMA', 202),
-        # (u'DOMAIN_NAME', 202), (u'DESCRIPTION', 203)]
-        data, _ = self.db.fetch(adSchemaColumns, conn=conn, schema=True)
-        
-        cols = []
-        typer = self.db.adapterset
-        for row in data:
-            # I tried passing criteria to OpenSchema, but passing None is
-            # not the same as passing pythoncom.Empty (which errors).
-            if row[2] != tablename:
-                continue
-            
-            dbtypename = dbtypes[row[11]]
-            dbtype = typer.canonicalize(dbtypename)()
-            pytype = dbtype.default_pytype
-            if pytype is None:
-                raise TypeError("%r has no default pytype." % dbtype)
-            
-            default = row[8]
-            if default is not None:
-                if issubclass(pytype, (int, long, float)):
-                    # We may have stuck extraneous quotes in the default
-                    # value when using numeric defaults with MSAccess.
-                    if default.startswith("'") and default.endswith("'"):
-                        default = default[1:-1]
-                default = pytype(default)
-            
-            name = str(row[3])
-            c = geniusql.Column(pytype, dbtype, default,
-                                key=(name in pknames),
-                                name=name, qname=self.db.quote(name))
-            
-            # This only works for SQL Server. The MSAccessDatabase will
-            # wrap this method and override autoincrement.
-            colflags = int(row[9])
-            if ((colflags & DBCOLUMNFLAGS_ISFIXEDLENGTH)
-                and not (colflags & DBCOLUMNFLAGS_WRITE)):
-                c.autoincrement = True
-            
-            if dbtype in typer.known_types['int']:
-                dbtype.bytes = row[15]
-            elif dbtype in typer.known_types['float']:
-                dbtype.precision = row[15]
-                dbtype.scale = row[16]
-##            elif dbtype == "CURRENCY":
-##                # CURRENCY allows 15 places to the left of the decimal point,
-##                # and 4 places to the right.
-##                c.hints['precision'] = 19
-##                c.hints['scale'] = 4
-            elif dbtype in typer.known_types['numeric']:
-                dbtype.precision = row[15]
-                dbtype.scale = row[16]
-            elif (dbtype in typer.known_types['char'] or
-                  dbtype in typer.known_types['varchar']):
-                if row[13]:
-                    # row[13] will be a float
-                    dbtype.bytes = b = int(row[13])
-##                else:
-##                    # I'm kinda guessing on this. If we use "MEMO" in an
-##                    # MSAccess CREATE statement, it comes back as "WCHAR",
-##                    # and seems to support over 65536 bytes.
-##                    dbtype.bytes = b = (2 ** 31) - 1
-##            elif dbtype in ("LONGVARCHAR", "LONGVARBINARY", "LONGVARWCHAR"):
-##                if row[13]:
-##                    # row[13] will be a float
-##                    c.hints['bytes'] = b = int(row[13])
-##                    c.dbtype = "%s(%s)" % (c.dbtype, b)
-##                else:
-##                    c.hints['bytes'] = 65535
-            
-            c.adapter = typer.default(pytype, dbtype)
-            cols.append(c)
-        return cols
-    
-    def _get_indices(self, tablename=None, conn=None):
-        # cols will be

trunk/geniusql/providers/msaccess.py

@@ +1 @@
+import datetime
+
+from geniusql import adapters, conns, dbtypes, objects, typerefs
-from geniusql.providers import ado
+
+import win32com.client
+
+
+# ------------------------------ Adapters ------------------------------ #
-
-
@@ -29 +37 @@
-                (value.days, (value.seconds / 86400.0)))
-
-
-class MSAccess_date(ado.COM_date):
-    
@@ -50 +57 @@
-        return '#%s/%s/%s#' % (value.month, value.day, value.year)
-
-
-class MSAccess_datetime(ado.COM_datetime):
-    
@@ -69 +75 @@
-                 value.hour, value.minute, value.second))
-
-
-class MSAccess_time(ado.COM_time):
-    
@@ -76 +81 @@
-
-
+class CURRENCY_float(adapters.SQL92DOUBLE):
+    
+    def pull(self, value):
+        if isinstance(value, tuple):
+            # See http://groups.google.com/group/comp.lang.python/
+            #           browse_frm/thread/fed03c64735c9e9c
+            value = map(long, value)
+            return ((value[1] & 0xFFFFFFFFL) | (value[0] << 32)) / 1e4
+        return float(value)
+
+class CURRENCY_decimal(adapters.DECIMAL):
+    
+    def pull(self, value):
+        # pywin32 build 205 began support for returning
+        # COM Currency objects as decimal objects.
+        # See http://pywin32.cvs.sourceforge.net/pywin32/pywin32/CHANGES.txt?view=markup
+        if not isinstance(value, typerefs.decimal.Decimal):
+            # See http://groups.google.com/group/comp.lang.python/
+            #           browse_frm/thread/fed03c64735c9e9c
+            value = map(long, value)
+            value = (value[1] & 0xFFFFFFFFL) | (value[0] << 32)
+            return typerefs.decimal.Decimal(value) / 10000
+        return value
+
+class CURRENCY_FixedPoint(adapters.FIXEDPOINT):
+    
+    def pull(self, value):
+        if isinstance(value, typerefs.decimal.Decimal):
+            value = str(value)
+            scale = 0
+            atoms = value.rsplit(".", 1)
+            if len(atoms) > 1:
+                scale = len(atoms[-1])
+            return typerefs.fixedpoint.FixedPoint(value, scale)
+        else:
+            # See http://groups.google.com/group/comp.lang.python/
+            #           browse_frm/thread/fed03c64735c9e9c
+            value = map(long, value)
+            value = (value[1] & 0xFFFFFFFFL) | (value[0] << 32)
+            return typerefs.fixedpoint.FixedPoint(value, 4) / 1e4
+
+
+# ---------------------------- DatabaseTypes ---------------------------- #
+
+# These are Access 2000+ types.
+# See http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
+# http://msdn2.microsoft.com/en-us/library/ms714540.aspx
+# http://office.microsoft.com/en-us/access/HP010322481033.aspx
+
+# "A Text field can store up to 255 characters, but the default field size
+# is 50 characters. A Memo field can store up to 65,536 characters. If you
+# want to store formatted text or long documents, you should create an OLE
+# Object field instead of a Memo field. Both Text and Memo data types store
+# only the characters entered in a field; space characters for unused
+# positions in the field aren't stored. You can sort or group on a Text
+# field or a Memo field, but Access only uses the first 255 characters
+# when you sort or group on a Memo field."
+
+class TEXT(dbtypes.AdjustableStringType):
+    
+    _initargs = dbtypes.AdjustableStringType._initargs + ("with_compression",)
+    
+    # Actually 255 chars, 2 bytes per char unless compressed
+    max_bytes = 255
+    bytes = 255
+    variable = True
+    
+    # "With the Microsoft Jet 4.0 database engine, all data for the TEXT
+    # data types are now stored in the Unicode 2-byte character
+    # representation format. It replaces the Multi-byte Character Set
+    # (MBCS) format that was used in previous versions. Although Unicode
+    # representation requires more space to store each character, columns
+    # with TEXT data types can be defined to automatically compress the
+    # data if it is possible to do so.
+    # 
+    # When you create TEXT data types with SQL, the Unicode compression
+    # property defaults to No. To set the Unicode compression property
+    # to Yes, use the WITH COMPRESSION (or WITH COMP) keywords at the
+    # field-level declaration."
+    with_compression = False
+    
+    def ddl(self):
+        """Return the type for use in CREATE or ALTER statements."""
+        withcomp = ""
+        if self.with_compression:
+            withcomp = " WITH COMPRESSION"
+        return "%s(%s)%s" % (self.__class__.__name__, self.bytes, withcomp)
+
+
+class MEMO(dbtypes.FrozenStringType):
+    # MEMO is 1 GB max when set programatically (only 64K when set
+    # in Access UI). But then, 1 GB is the limit for the whole DB.
+    # Note that OpenSchema will return a DATA_TYPE of "WCHAR".
+    synonyms = ['WCHAR']
+    bytes = max_bytes = 65535           # (2.14 GB if not binary data)
+    variable = True
+
+
+class TINYINT(dbtypes.IntegerType):
+    synonyms = ['INTEGER1', 'BYTE']
+    bytes = max_bytes = 1
+    signed = False
+
+class SMALLINT(dbtypes.IntegerType):
+    synonyms = ['SHORT', 'INTEGER2']
+    bytes = max_bytes = 2
+
+class INTEGER(dbtypes.IntegerType):
+    synonyms = ['LONG', 'INT', 'INTEGER4']
+    bytes = max_bytes = 4
+
+
+class FLOAT(dbtypes.InexactNumericType):
+    synonyms = ['DOUBLE', 'FLOAT8', 'IEEEDOUBLE', 'NUMBER']
+    precision = max_precision = 53
+    default_adapters = {float: adapters.SQL92DOUBLE}
+
+class REAL(dbtypes.InexactNumericType):
+    synonyms = ['SINGLE', 'FLOAT4', 'IEEESINGLE']
+    precision = max_precision = 24
+    default_adapters = {float: adapters.SQL92REAL}
+
+
+class DECIMAL(dbtypes.ExactNumericType):
+    synonyms = ['NUMERIC', 'DEC']
+    
+    # "...precision, the default is 18 and the maximum allowed value is 28.
+    # For the scale, the default is 0 and the maximum allowed value is 28."
+    precision = 18
+    max_precision = 28
+    scale = 0
+##    
+##    # Hm. Docs say 28, but I can't seem to get more than 12 working.
+##    numeric_max_precision = 12
+##    numeric_max_bytes = 6
+
+
+class CURRENCY(dbtypes.FrozenPrecisionType):
+    
+    _initargs = dbtypes.FrozenPrecisionType._initargs + ("scale", )
+    
+    # "The CURRENCY data type is used to store numeric data that contains
+    # up to 15 digits on the left side of the decimal point, and up to 4
+    # digits on the right. It uses 8 bytes of memory for storage, and its
+    # only synonym is MONEY."
+    synonyms = ['MONEY']
+    
+    precision = max_precision = 19
+    scale = property(lambda self: 4, lambda self, value: None)
+    
+    default_adapters = {float: CURRENCY_float}
+    if typerefs.decimal:
+        if hasattr(typerefs.decimal, "Decimal"):
+            default_adapters[typerefs.decimal.Decimal] = CURRENCY_decimal
+        else:
+            default_adapters[typerefs.decimal] = CURRENCY_decimal
+    if typerefs.fixedpoint:
+        default_adapters[typerefs.fixedpoint.FixedPoint] = CURRENCY_FixedPoint
+
+
+class YESNO(dbtypes.DatabaseType):
+    # "The BOOLEAN data types are logical types that result in either True
+    # or False values. They use 1 byte of memory for storage, and their
+    # synonyms are BIT, LOGICAL, LOGICAL1, and YESNO. A True value is
+    # equal to -1 while a False value is equal to 0."
+    default_adapters = {bool: adapters.BOOLEAN}
+
+
+class BINARY(dbtypes.AdjustableByteType):
+    # "The BINARY data type is used to store a small amount of any type
+    # of data in its native, binary format. It uses 1 byte of memory for
+    # each character stored, and you can optionally specify the number
+    # of bytes to be allocated. If the number of bytes is not specified,
+    # it defaults to 510 bytes, which is the maximum number of bytes
+    # allowed. Its synonyms are BINARY, VARBINARY, and BINARY VARYING.
+    # The BINARY data type is not available in the Access user interface."
+    synonyms = ['VARBINARY', 'BINARY VARYING']
+    bytes = 510
+    max_bytes = 510
+    variable = False
+
+
+class DATETIME(dbtypes.DateTimeType):
+    # "The DATETIME data type is used to store date, time, and combination
+    # date/time values for the years ranging from 100 to 9999.
+    # It uses 8 bytes of memory for storage, and its synonyms are
+    # DATE, TIME, DATETIME, and TIMESTAMP.
+    synonyms = ['DATE', 'TIME', 'TIMESTAMP']
+    _min = datetime.datetime(100, 1, 1)
+    _max = datetime.datetime(9999, 12, 31)
+    
+    default_adapters = {datetime.datetime: MSAccess_datetime,
+                        datetime.date: MSAccess_date,
+                        datetime.time: MSAccess_time,
+                        datetime.timedelta: MSAccess_timedelta,
+                        }
+
+
-class MSAccessAdapterSet(ado.ADOAdapterSet):
-    
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
-
-
@@ -236 +390 @@
-    tableclass = MSAccessTable
-    
+    # See http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
+    adotypes = {
+        # MS Access             ADO Name
+        2: SMALLINT,            # SMALLINT
+        3: INTEGER,             # INTEGER
+        4: REAL,                # SINGLE
+        5: FLOAT,               # DOUBLE
+        6: CURRENCY,            # CURRENCY
+        7: DATETIME,            # DATE (Access 97)
+        11: YESNO,              # BOOLEAN
+        17: TINYINT,            # UNSIGNEDTINYINT
+        128: BINARY,            # BINARY
+        130: MEMO,              # WCHAR
+        131: DECIMAL,           # NUMERIC (Access 2000)
+        135: DATETIME,          # DBTIMESTAMP (ODBC 97)
+        200: TEXT,              # VARCHAR (Access 97)
+        201: MEMO,              # LONGVARCHAR (Access 97)
+        202: TEXT,              # VARWCHAR (Access 2000)
+        203: MEMO,              # LONGVARWCHAR (Access 2000+)
+        # 205: OLEOBJECT,       # LONGVARBINARY
+        # 0: EMPTY,
+        # 8: BSTR, 9: IDISPATCH, 10: ERROR,
+        # 12: VARIANT, 13: IUNKNOWN, 14: DECIMAL, 16: TINYINT,
+        # 18: UNSIGNEDSMALLINT, 19: UNSIGNEDINT, 20: BIGINT,
+        # 21: UNSIGNEDBIGINT, 72: GUID,
+        # 129: CHAR, 
+        # 132: USERDEFINED, 133: DBDATE, 134: DBTIME,
+        # 204: VARBINARY,
+    }
+    
-    def _get_columns(self, tablename, conn=None):
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-        if conn is None:
-            conn = self.db.connections._factory()
-        
-        try:
-            # Horrible hack to get autoincrement property
-            query = "SELECT * FROM %s WHERE FALSE;" % self.db.quote(tablename)
-            bareconn = conn
@@ -254 +517 @@
-                                            ((8, 1), (16396, 18), (3, 49)),
-                                            # *args =
-
-
+ado.
+ado.
-            try:
-
+ado.
-            except:
-                pass
@@ -265 +528 @@
-            
-            try:
+                # Return no columns when inspecting system tables
-                if "no read permission" in x.args[2][2]:
-                    conn = None
@@ -274 +538 @@
-            raise x
-        
-
+ado.
-        for c in cols:
-            f = resFields.InvokeTypes(0, 0, 2, (9, 0), ((12, 1),), c.name)
-
+ado.
-            fprop = fprops.InvokeTypes(0, 0, 2, (9, 0), ((12, 1), ), "ISAUTOINCREMENT")
-
+
+
+
+
+
+
+
+
+
+
+
-        
-        try:
-
+ado.
-        except:
-            pass
@@ -295 +569 @@
-            name type [DEFAULT x|AUTOINCREMENT(initial, 1)]
-        """
-btype = column.dbtype
+dl = column.dbtype.ddl()
-        
-        if column.autoincrement:
+            if column.dbtype.default_pytype not in (int, long):
+                raise ValueError("Microsoft Access does not allow COUNTER "
+                                 "columns of type %r" % dbtype)
+            ddl = " COUNTER(%s, 1) NOT NULL" % column.initial
+        else:
-            # MS Access does not allow a column to have
-            # both an AUTOINCREMENT clause and a DEFAULT clause.
-            # It also needs no type in this case.
-            dbtype = "AUTOINCREMENT(%s, 1)" % column.initial
-        else:
-            default = column.default or ""
-            if default:
@@ -309 +585 @@
-                    # Crazy quote hack to get a numeric default to work.
-                    defspec = "'%s'" % defspec
-btype = "%s DEFAULT %s" % (dbtype
-
-btype
+dl = "%s DEFAULT %s" % (ddl
+
+dl
-    
-    def create_database(self):

trunk/geniusql/providers/sqlserver.py

@@ -1 +1 @@
-import datetime
-
-
+, objects
-from geniusql.providers import ado
-
@@ -19 +19 @@
-
-
-
+Adjustable
-    max_bytes = 8000
-    variable = False
-
-
+Adjustable
-    max_bytes = 8000
-    variable = True
-
-
+Adjustable
-    max_bytes = 8000
-    variable = False
-
-
+Adjustable
-    max_bytes = 8000
-    variable = True
@@ -43 +43 @@
-##                        % (sql, self, totype))
-
-
+Adjustable
-    synonyms=['WCHAR']
-    max_bytes = 4000
-    variable = False
-
-
+Adjustable
-    synonyms=['VARWCHAR']
-    max_bytes = 4000
@@ -58 +58 @@
-# Use nvarchar(max), varchar(max), and varbinary(max) instead."
-# http://msdn2.microsoft.com/en-us/library/ms187993.aspx
-
+Frozen
-    synonyms = ['LONGVARWCHAR']
-    bytes = max_bytes = ((2 ** 30) - 1) * 2
-
-
-
+
+
-    synonyms = ['LONGVARCHAR']
-    bytes = max_bytes = (2 ** 31) - 1
-
-
-
+
+
-    synonyms = ['LONGVARBINARY']
-    bytes = max_bytes = (2 ** 31) - 1
-    fixed_bytes = True
-
-
@@ -77 +74 @@
-    synonyms=['BOOLEAN']
-    bytes = max_bytes = 1
-    fixed_bytes = True
-    default_pytype = bool
-    default_adapters = {bool: adapters.SQL92BIT}
@@ -99 +95 @@
-                        }
-
-
-class SMALLDATETIME(dbtypes.DateTimeType):
-    _min=datetime.datetime(1900, 1, 1)
-    _max=datetime.datetime(2079, 6, 6)
-
+
-class FLOAT(dbtypes.InexactNumericType):
-    synonyms = ['DOUBLE']
-    precision = max_precision = 53
-    fixed_precision = True
-    default_adapters = {float: adapters.SQL92DOUBLE}
-    
@@ -121 +116 @@
-
-class REAL(dbtypes.InexactNumericType):
-=
+ = 
-    precision = max_precision = 24
-    fixed_precision = True
-    default_adapters = {float: adapters.SQL92REAL}
-    
@@ -139 +133 @@
-    synonyms = ['UNSIGNEDTINYINT']
-    bytes = max_bytes = 1
-fixed_bytes = Tru
+signed = Fals
-    default_adapters = {int: adapters.SQL92SMALLINT,
-                        long: adapters.SQL92SMALLINT,
@@ -146 +140 @@
-class SMALLINT(dbtypes.IntegerType):
-    bytes = max_bytes = 2
-    fixed_bytes = True
-    default_adapters = {int: adapters.SQL92SMALLINT,
-                        long: adapters.SQL92SMALLINT,
@@ -154 +147 @@
-    synonyms = ['INTEGER', 'IDENTITY']
-    bytes = max_bytes = 4
-    fixed_bytes = True
-
-class BIGINT(dbtypes.IntegerType):
-    """8-byte BIGINT type for SQL Server (2000+ only)."""
-    bytes = max_bytes = 8
-    fixed_bytes = True
-    default_pytype = long
-
@@ -168 +159 @@
-    default_adapters[datetime.timedelta] = ado.COM_timedelta
-
-
+
+
+
-    
-    scale = 4
@@ -253 +246 @@
-
-
-
-class SQLServerDecompiler(ado.ADOSQLDecompiler):
-    
@@ -326 +318 @@
-    tableclass = SQLServerTable
-    
+    # See http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
+    adotypes = {
+        # SQL Server              ADO Name
+        2: SMALLINT,        # SMALLINT
+        3: INT,             # INTEGER (also for IDENTITY (SQL Server 6.5))
+        4: REAL,            # SINGLE
+        5: FLOAT,           # DOUBLE
+        6: MONEY,           # CURRENCY (also could be SMALLMONEY)
+        11: BIT,            # BOOLEAN
+        # 12: SQL_VARIANT,  # VARIANT (2000 +)
+        17: TINYINT,        # UNSIGNEDTINYINT
+        20: BIGINT,         # BIGINT
+        128: BINARY,        # BINARY (also could be TIMESTAMP)
+        129: CHAR,          # CHAR
+        130: NCHAR,         # WCHAR (7.0+)
+        131: NUMERIC,       # DECIMAL, NUMERIC
+        135: DATETIME,      # DBTIMESTAMP (also could be SMALLDATETIME)
+        200: VARCHAR,       # VARCHAR
+        201: TEXT,          # LONGVARCHAR
+        202: NVARCHAR,      # VARWCHAR
+        203: NTEXT,         # LONGVARWCHAR (7.0+)
+        204: VARBINARY,     # VARBINARY
+        205: IMAGE,         # LONGVARBINARY
+        # 0: EMPTY,
+        # 7: DATE, 8: BSTR, 9: IDISPATCH, 10: ERROR,
+        # 13: IUNKNOWN, 14: DECIMAL, 16: TINYINT,
+        # 18: UNSIGNEDSMALLINT, 19: UNSIGNEDINT, 21: UNSIGNEDBIGINT,
+        # 72: GUID,
+        # 132: USERDEFINED, 133: DBDATE, 134: DBTIME,
+    }
+    
+    def _get_columns(self, tablename, conn=None):
+        # For some reason, adSchemaPrimaryKeys would only return a single
+        # record for a PK that had multiple columns. Use adSchemaIndexes.
+        # coldefs will be:
+        # [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
+        # (u'INDEX_CATALOG', 202), (u'INDEX_SCHEMA', 202), (u'INDEX_NAME', 202),
+        # (u'PRIMARY_KEY', 11), (u'UNIQUE', 11), (u'CLUSTERED', 11), (u'TYPE', 18),
+        # (u'FILL_FACTOR', 3), (u'INITIAL_SIZE', 3), (u'NULLS', 3),
+        # (u'SORT_BOOKMARKS', 11), (u'AUTO_UPDATE', 11), (u'NULL_COLLATION', 3),
+        # (u'ORDINAL_POSITION', 19), (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72),
+        # (u'COLUMN_PROPID', 19), (u'COLLATION', 2), (u'CARDINALITY', 21),
+        # (u'PAGES', 3), (u'FILTER_CONDITION', 202), (u'INTEGRATED', 11)]
+        data, _ = self.db.fetch(ado.adSchemaIndexes, conn=conn, schema=True)
+        pknames = [row[17] for row in data
+                   if (tablename == row[2]) and row[6]]
+        
+        # columns will be
+        # [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
+        # (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72), (u'COLUMN_PROPID', 19),
+        # (u'ORDINAL_POSITION', 19), (u'COLUMN_HASDEFAULT', 11),
+        # (u'COLUMN_DEFAULT', 203), (u'COLUMN_FLAGS', 19), (u'IS_NULLABLE', 11),
+        # (u'DATA_TYPE', 18), (u'TYPE_GUID', 72), (u'CHARACTER_MAXIMUM_LENGTH', 19),
+        # (u'CHARACTER_OCTET_LENGTH', 19), (u'NUMERIC_PRECISION', 18),
+        # (u'NUMERIC_SCALE', 2), (u'DATETIME_PRECISION', 19),
+        # (u'CHARACTER_SET_CATALOG', 202), (u'CHARACTER_SET_SCHEMA', 202),
+        # (u'CHARACTER_SET_NAME', 202), (u'COLLATION_CATALOG', 202),
+        # (u'COLLATION_SCHEMA', 202), (u'COLLATION_NAME', 202),
+        # (u'DOMAIN_CATALOG', 202), (u'DOMAIN_SCHEMA', 202),
+        # (u'DOMAIN_NAME', 202), (u'DESCRIPTION', 203)]
+        data, _ = self.db.fetch(ado.adSchemaColumns, conn=conn, schema=True)
+        
+        cols = []
+        typer = self.db.adapterset
+        for row in data:
+            # I tried passing criteria to OpenSchema, but passing None is
+            # not the same as passing pythoncom.Empty (which errors).
+            if row[2] != tablename:
+                continue
+            
+            dbtype = self.adotypes[row[11]]()
+            pytype = dbtype.default_pytype
+            if pytype is None:
+                raise TypeError("%r has no default pytype." % dbtype)
+            
+            default = row[8]
+            if default is not None:
+                default = pytype(default)
+            
+            name = str(row[3])
+            c = objects.Column(pytype, dbtype, default,
+                               key=(name in pknames),
+                               name=name, qname=self.db.quote(name))
+            
+            colflags = int(row[9])
+            if ((colflags & ado.DBCOLUMNFLAGS_ISFIXEDLENGTH)
+                and not (colflags & ado.DBCOLUMNFLAGS_WRITE)):
+                c.autoincrement = True
+            
+            if dbtype in typer.known_types['int']:
+                dbtype.bytes = row[15]
+            elif dbtype in typer.known_types['float']:
+                dbtype.precision = row[15]
+                dbtype.scale = row[16]
+            elif dbtype in typer.known_types['numeric']:
+                dbtype.precision = row[15]
+                dbtype.scale = row[16]
+            elif (dbtype in typer.known_types['char'] or
+                  dbtype in typer.known_types['varchar']):
+                if row[13]:
+                    # row[13] will be a float
+                    dbtype.bytes = b = int(row[13])
+            
+            c.adapter = typer.default(pytype, dbtype)
+            cols.append(c)
+        return cols
+    
-    def create_database(self):
-        conn = self.db.connections._get_conn(master=True)
@@ -337 +436 @@
-        conn.Close()
-        self.clear()
-    
-    def column(self, pytype=unicode, dbtype=None, default=None,
-               key=False, autoincrement=False, hints=None):
-        """Return a Column object from the given arguments."""
-        from geniusql import objects
-        col = objects.Column(pytype, dbtype, default, key)
-        col.autoincrement = autoincrement
-        
-        typer = self.db.adapterset
-        if autoincrement:
-            col.dbtype = INT()
-        if col.dbtype is None:
-            col.dbtype = typer.database_type(pytype, hints or {})
-        col.adapter = typer.default(pytype, col.dbtype)
-        
-        return col
-    
-    def columnclause(self, column):

trunk/geniusql/test/test_msaccess.py

@@ -16 +16 @@
-                          "The MSAccess test will not be run.")
-    else:
-        
-        class CurrencyAdapter(ado.TypeAdapter_MSAccess):
-            """Stores Decimal and FixedPoint objects as CURRENCY."""
-            
-            def decimal_type(self, precision, scale):
-                if precision == 0:
-                    precision = 19
-                if scale > precision:
-                    scale = precision
-                if scale > 4 or precision - scale > 15:
-                    return "TEXT"
-                return "CURRENCY"
-            
-        DB_class = "access"
-        opts = {'connections.Connect':
@@ -37 +24 @@
-            import zoo_fixture
-            
-
-
-                fta = db.typeadapter
-
-
-
-
-
-
-
-
-
-
-
+##
+##
+##                fta = db.adapterset
+##
+##
+##
+##
+##
+##
+##
+##
+##
+##
+##
-            # test the standard MS Access setup where Decimal and FixedPoint
-            # objects are stored in the database as INTEGERS, LONGS or NUMERIC
@@ -56 +43 @@
-            print
-            print "Standard MSAccess test."
-
+##
-            zoo_fixture.run(DB_class, "zoo.mdb", opts)
-            

trunk/geniusql/typerefs.py

@@ -26 +26 @@
-    fixedpoint = None
-
-
+
+
+
+
+
+
+
+
+