SQLite dialect - support relection from affinitypr/65

SQLite allows column types that aren't technically understood in sqlite
by using 'data affinity', which is an algorithm for converting column
types in to some sort of useful type that can be stored and retrieved
from the db. Unfortunatly, this breaks reflection since we (previously)
expected a sqlite db to reflect column types that we permit in the
`ischema_names` for that dialect.

This patch changes the logic for 'unknown' column types during
reflection to instead run through SQLite's data affinity algorithm, and
assigns appropriate types from that.

It also expands the matching for column type to include column types
with spaces (strongly discouraged but allowed by sqlite) and also
completely empty column types (in which case the NullType is assigned,
which sqlite will treat as a Blob - or rather, Blob is treated as
NullType). These changes mean that SQLite will never raise an error for
an unknown type during reflection - there will always be some 'useful'
type returned, which follows the spirit of SQLite (accomodation before
sanity!).

1 files changed, 36 insertions, 11 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 258b7944f..3357c81ca 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -403,6 +403,7 @@ ischema_names = {
     'CHAR': sqltypes.CHAR,
     'DATE': sqltypes.DATE,
     'DATETIME': sqltypes.DATETIME,
+    'DOUBLE': sqltypes.FLOAT,
     'DECIMAL': sqltypes.DECIMAL,
     'FLOAT': sqltypes.FLOAT,
     'INT': sqltypes.INTEGER,
@@ -806,22 +807,17 @@ class SQLiteDialect(default.DefaultDialect):
         return columns
 
     def _get_column_info(self, name, type_, nullable, default, primary_key):
-        match = re.match(r'(\w+)(\(.*?\))?', type_)
+        match = re.match(r'([\w ]+)(\(.*?\))?', type_)
         if match:
             coltype = match.group(1)
             args = match.group(2)
         else:
-            coltype = "VARCHAR"
+            coltype = ''
             args = ''
-        try:
-            coltype = self.ischema_names[coltype]
-            if args is not None:
-                args = re.findall(r'(\d+)', args)
-                coltype = coltype(*[int(a) for a in args])
-        except KeyError:
-            util.warn("Did not recognize type '%s' of column '%s'" %
-                      (coltype, name))
-            coltype = sqltypes.NullType()
+        coltype = self._resolve_col_affinity(coltype)
+        if args is not None:
+            args = re.findall(r'(\d+)', args)
+            coltype = coltype(*[int(a) for a in args])
 
         if default is not None:
             default = util.text_type(default)
@@ -835,6 +831,35 @@ class SQLiteDialect(default.DefaultDialect):
             'primary_key': primary_key,
         }
 
+    def _resolve_col_affinity(self, coltype):
+        """Return a data type from a reflected column, using affinity tules.
+
+        SQLite's goal for universal compatability introduces some complexity
+        during reflection, as a column's defined type might not actually be a
+        type that SQLite understands - or indeed, my not be defined *at all*.
+        Internally, SQLite handles this with a 'data type affinity' for each
+        column definition, mapping to one of 'TEXT', 'NUMERIC', 'INTEGER',
+        'REAL', or 'NONE' (raw bits). The algorithm that determines this is
+        listed in http://www.sqlite.org/datatype3.html section 2.1.
+
+        This method allows SQLAlchemy to support that algorithm, while still
+        providing access to smarter reflection utilities by regcognizing
+        column definitions that SQLite only supports through affinity (like
+        DATE and DOUBLE).
+        """
+        if coltype in self.ischema_names:
+            return self.ischema_names[coltype]
+        if 'INT' in coltype:
+            return sqltypes.INTEGER
+        elif 'CHAR' in coltype or 'CLOB' in coltype or 'TEXT' in coltype:
+            return sqltypes.TEXT,
+        elif 'BLOB' in coltype or not coltype: 
+            return sqltypes.NullType
+        elif 'REAL' in coltype or 'FLOA' in coltype or 'DOUB' in coltype:
+            return sqltype.REAL
+        else:
+            return sqltypes.NUMERIC
+
     @reflection.cache
     def get_pk_constraint(self, connection, table_name, schema=None, **kw):
         cols = self.get_columns(connection, table_name, schema, **kw)