diff options
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/base.py | 47 | ||||
| -rw-r--r-- | test/dialect/test_sqlite.py | 68 | ||||
| -rw-r--r-- | test/engine/test_reflection.py | 10 |
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)) |