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!).

3 files changed, 107 insertions, 18 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)
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index 1b424e6c8..319b708e7 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -1,10 +1,12 @@
 #!coding: utf-8
 
 """SQLite-specific tests."""
+import os
+from collections import Counter
+import datetime
 
 from sqlalchemy.testing import eq_, assert_raises, \
     assert_raises_message
-import datetime
 from sqlalchemy import Table, String, select, Text, CHAR, bindparam, Column,\
     Unicode, Date, MetaData, UnicodeText, Time, Integer, TIMESTAMP, \
     Boolean, func, NUMERIC, DateTime, extract, ForeignKey, text, Numeric,\
@@ -18,7 +20,6 @@ from sqlalchemy.engine.url import make_url
 from sqlalchemy.testing import fixtures, AssertsCompiledSQL, \
     AssertsExecutionResults, engines
 from sqlalchemy import testing
-import os
 from sqlalchemy.schema import CreateTable
 
 class TestTypes(fixtures.TestBase, AssertsExecutionResults):
@@ -29,7 +30,7 @@ class TestTypes(fixtures.TestBase, AssertsExecutionResults):
         """Test that the boolean only treats 1 as True
 
         """
-
+        
         meta = MetaData(testing.db)
         t = Table('bool_table', meta, Column('id', Integer,
                   primary_key=True), Column('boo',
@@ -227,8 +228,8 @@ class TestTypes(fixtures.TestBase, AssertsExecutionResults):
         )
         t.create()
         t2 = Table('t', MetaData(), autoload=True, autoload_with=testing.db)
-        assert isinstance(t2.c.x.type, sqltypes.NullType)
-        assert isinstance(t2.c.y.type, sqltypes.NullType)
+        assert isinstance(t2.c.x.type, sqltypes.Numeric)
+        assert isinstance(t2.c.y.type, sqltypes.Numeric)
 
 
 class DateTimeTest(fixtures.TestBase, AssertsCompiledSQL):
@@ -1022,3 +1023,60 @@ class ReflectFKConstraintTest(fixtures.TestBase):
             set([con.name for con in c.constraints]),
             set([None, None])
         )
+
+
+class ColumnTypeAffinityReflectionTest(fixtures.TestBase):
+    """Tests on data type affinities for SQLite during relection.
+
+    See http://www.sqlite.org/datatype3.html - section 2.
+    """
+    __only_on__ = 'sqlite'
+
+    def setup(self):
+        testing.db.execute("""
+            CREATE TABLE a (
+                "id" INTEGER PRIMARY KEY,
+                "foo" DOUBLE,
+                "bar" DECIMAL(19,4),
+                "baz" VARCHAR(200),
+                "boff",
+                "biff" LONGTEXT
+            )""")
+        # These example names come from section 2.2 of the datatype docs,
+        # after pruning out types which we convert to more convenient types
+        self.example_typenames_integer = ["TINYINT", "MEDIUMINT", "INT2",
+            "UNSIGNED BIG INT", "INT8"]
+        self.example_typenames_text = ["CHARACTER(20)", "CLOB",
+            "VARYING CHARACTER(70)", "NATIVE CHARACTER(70)"]
+        self.example_typenames_none = [""]
+        self.example_typenames_real = ["DOUBLE PRECISION"]
+        cols = ["i%d %s" % (n, t) for n, t in enumerate(
+            self.example_typenames_integer)]
+        cols += ["t%d %s" % (n, t) for n, t in enumerate(
+            self.example_typenames_text)]
+        cols += ["o%d %s" % (n, t) for n, t in enumerate(
+            self.example_typenames_none)]
+        cols += ["n%d %s" % (n, t) for n, t in enumerate(
+            self.example_typenames_real)]
+        cols = ','.join(cols)
+        testing.db.execute("CREATE TABLE b (%s)" % (cols,))
+
+    def teardown(self):
+        testing.db.execute("drop table a")
+        testing.db.execute("drop table b")
+
+    def test_can_reflect_with_affinity(self):
+        "Test that 'affinity-types' don't break reflection outright."
+        meta = MetaData()
+        a = Table('a', meta, autoload=True, autoload_with=testing.db)
+        eq_(len(a.columns), 6)
+
+    def test_correct_reflection_with_affinity(self):
+        "Test that coltypes are detected correctly from affinity rules."
+        meta = MetaData()
+        b = Table('b', meta, autoload=True, autoload_with=testing.db)
+        typecounts = Counter(type(col.type) for col in b.columns)
+        eq_(typecounts[sqltypes.INTEGER], len(self.example_typenames_integer))
+        eq_(typecounts[sqltypes.TEXT], len(self.example_typenames_text))
+        eq_(typecounts[sqltypes.NullType], len(self.example_typenames_none))
+        eq_(typecounts[sqltypes.REAL], len(self.example_typenames_real))
diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py
index 2f311f7e7..5ba3cac1b 100644
--- a/test/engine/test_reflection.py
+++ b/test/engine/test_reflection.py
@@ -4,8 +4,8 @@ import unicodedata
 import sqlalchemy as sa
 from sqlalchemy import schema, events, event, inspect
 from sqlalchemy import MetaData, Integer, String
-from sqlalchemy.testing import ComparesTables, \
-                            engines, AssertsCompiledSQL, fixtures
+from sqlalchemy.testing import (ComparesTables, engines, AssertsCompiledSQL,
+    fixtures, skip)
 from sqlalchemy.testing.schema import Table, Column
 from sqlalchemy.testing import eq_, assert_raises, assert_raises_message
 from sqlalchemy import testing
@@ -317,8 +317,14 @@ class ReflectionTest(fixtures.TestBase, ComparesTables):
         t2a = Table('test2', m2, autoload=True)
         assert t2a._autoincrement_column is t2a.c.id2
 
+    @skip('sqlite')
     @testing.provide_metadata
     def test_unknown_types(self):
+        """Test the handling of unknown types for the given dialect.
+
+        sqlite is skipped because it has special rules for unknown types using
+        'affinity types' - this feature is tested in that dialect's test spec.
+        """
         meta = self.metadata
         t = Table("test", meta,
             Column('foo', sa.DateTime))