def test_dialect_specific(self):
class AddThingy(DDLElement):
__visit_name__ = 'add_thingy'
class DropThingy(DDLElement):
__visit_name__ = 'drop_thingy'
@compiles(AddThingy, 'sqlite')
def visit_add_thingy(thingy, compiler, **kw):
return "ADD SPECIAL SL THINGY"
@compiles(AddThingy)
def visit_add_thingy(thingy, compiler, **kw):
return "ADD THINGY"
@compiles(DropThingy)
def visit_drop_thingy(thingy, compiler, **kw):
return "DROP THINGY"
self.assert_compile(AddThingy(),
"ADD THINGY"
)
self.assert_compile(DropThingy(),
"DROP THINGY"
)
from sqlalchemy.dialects.sqlite import base
self.assert_compile(AddThingy(),
"ADD SPECIAL SL THINGY",
dialect=base.dialect()
)
self.assert_compile(DropThingy(),
"DROP THINGY",
dialect=base.dialect()
)
@compiles(DropThingy, 'sqlite')
def visit_drop_thingy(thingy, compiler, **kw):
return "DROP SPECIAL SL THINGY"
self.assert_compile(DropThingy(),
"DROP SPECIAL SL THINGY",
dialect=base.dialect()
)
self.assert_compile(DropThingy(),
"DROP THINGY",
)
def test_dialect_specific(self):
class AddThingy(DDLElement):
__visit_name__ = 'add_thingy'
class DropThingy(DDLElement):
__visit_name__ = 'drop_thingy'
@compiles(AddThingy, 'sqlite')
def visit_add_thingy(thingy, compiler, **kw):
return "ADD SPECIAL SL THINGY"
@compiles(AddThingy)
def visit_add_thingy(thingy, compiler, **kw):
return "ADD THINGY"
@compiles(DropThingy)
def visit_drop_thingy(thingy, compiler, **kw):
return "DROP THINGY"
self.assert_compile(AddThingy(),
"ADD THINGY"
)
self.assert_compile(DropThingy(),
"DROP THINGY"
)
from sqlalchemy.dialects.sqlite import base
self.assert_compile(AddThingy(),
"ADD SPECIAL SL THINGY",
dialect=base.dialect()
)
self.assert_compile(DropThingy(),
"DROP THINGY",
dialect=base.dialect()
)
@compiles(DropThingy, 'sqlite')
def visit_drop_thingy(thingy, compiler, **kw):
return "DROP SPECIAL SL THINGY"
self.assert_compile(DropThingy(),
"DROP SPECIAL SL THINGY",
dialect=base.dialect()
)
self.assert_compile(DropThingy(),
"DROP THINGY",
)
def test_types(self):
class MyType(TypeEngine):
pass
@compiles(MyType, 'sqlite')
def visit_type(type, compiler, **kw):
return "SQLITE_FOO"
@compiles(MyType, 'postgresql')
def visit_type(type, compiler, **kw):
return "POSTGRES_FOO"
from sqlalchemy.dialects.sqlite import base as sqlite
from sqlalchemy.dialects.postgresql import base as postgresql
self.assert_compile(
MyType(),
"SQLITE_FOO",
dialect=sqlite.dialect()
)
self.assert_compile(
MyType(),
"POSTGRES_FOO",
dialect=postgresql.dialect()
)
def test_legacy_quoted_identifiers_unit(self):
dialect = sqlite.dialect()
dialect._broken_fk_pragma_quotes = True
for row in [
(0, "target", "tid", "id"),
(0, '"target"', "tid", "id"),
(0, "[target]", "tid", "id"),
(0, "'target'", "tid", "id"),
(0, "`target`", "tid", "id"),
]:
fks = {}
fkeys = []
dialect._parse_fk(fks, fkeys, *row)
eq_(
fkeys,
[
{
"referred_table": "target",
"referred_columns": ["id"],
"referred_schema": None,
"name": None,
"constrained_columns": ["tid"],
}
],
)
def test_types(self):
class MyType(TypeEngine):
pass
@compiles(MyType, 'sqlite')
def visit_type(type, compiler, **kw):
return "SQLITE_FOO"
@compiles(MyType, 'postgresql')
def visit_type(type, compiler, **kw):
return "POSTGRES_FOO"
from sqlalchemy.dialects.sqlite import base as sqlite
from sqlalchemy.dialects.postgresql import base as postgresql
self.assert_compile(
MyType(),
"SQLITE_FOO",
dialect=sqlite.dialect()
)
self.assert_compile(
MyType(),
"POSTGRES_FOO",
dialect=postgresql.dialect()
)
def test_default_on_existing(self):
"""test that the existing compiler function remains
as 'default' when overriding the compilation of an
existing construct."""
t1 = table('t1', column('c1'), column('c2'))
dispatch = Select._compiler_dispatch
try:
@compiles(Select, 'sqlite')
def compile(element, compiler, **kw):
return "OVERRIDE"
s1 = select([t1])
self.assert_compile(
s1, "SELECT t1.c1, t1.c2 FROM t1",
)
from sqlalchemy.dialects.sqlite import base as sqlite
self.assert_compile(
s1, "OVERRIDE",
dialect=sqlite.dialect()
)
finally:
Select._compiler_dispatch = dispatch
if hasattr(Select, '_compiler_dispatcher'):
del Select._compiler_dispatcher
def test_default_on_existing(self):
"""test that the existing compiler function remains
as 'default' when overriding the compilation of an
existing construct."""
t1 = table('t1', column('c1'), column('c2'))
dispatch = Select._compiler_dispatch
try:
@compiles(Select, 'sqlite')
def compile(element, compiler, **kw):
return "OVERRIDE"
s1 = select([t1])
self.assert_compile(
s1,
"SELECT t1.c1, t1.c2 FROM t1",
)
from sqlalchemy.dialects.sqlite import base as sqlite
self.assert_compile(s1, "OVERRIDE", dialect=sqlite.dialect())
finally:
Select._compiler_dispatch = dispatch
if hasattr(Select, '_compiler_dispatcher'):
del Select._compiler_dispatcher
def test_sqlite_no_autoincrement(self):
table = Table('noautoinctable', MetaData(), Column('id',
Integer, primary_key=True), Column('x', Integer,
default=None))
self.assert_compile(schema.CreateTable(table),
'CREATE TABLE noautoinctable (id INTEGER '
'NOT NULL, x INTEGER, PRIMARY KEY (id))',
dialect=sqlite.dialect())
def test_sqlite_autoincrement(self):
table = Table('autoinctable', MetaData(), Column('id', Integer,
primary_key=True), Column('x', Integer,
default=None), sqlite_autoincrement=True)
self.assert_compile(schema.CreateTable(table),
'CREATE TABLE autoinctable (id INTEGER NOT '
'NULL PRIMARY KEY AUTOINCREMENT, x INTEGER)'
, dialect=sqlite.dialect())
def test_sqlite_no_autoincrement(self):
table = Table('noautoinctable', MetaData(),
Column('id', Integer, primary_key=True),
Column('x', Integer, default=None))
self.assert_compile(schema.CreateTable(table),
'CREATE TABLE noautoinctable (id INTEGER '
'NOT NULL, x INTEGER, PRIMARY KEY (id))',
dialect=sqlite.dialect())
def test_sqlite_autoincrement(self):
table = Table('autoinctable', MetaData(), Column('id', Integer,
primary_key=True), Column('x', Integer,
default=None), sqlite_autoincrement=True)
self.assert_compile(schema.CreateTable(table),
'CREATE TABLE autoinctable (id INTEGER NOT '
'NULL PRIMARY KEY AUTOINCREMENT, x INTEGER)'
, dialect=sqlite.dialect())
def test_sqlite_uses_juliandates(self):
import datetime
from sqlalchemy.dialects.sqlite.base import dialect
start = datetime.date(2011, 9, 1)
end = datetime.date(2011, 9, 6)
sqlite = dialect()
self.assertEqual(
str(self.day_difference(start, end).compile(dialect=sqlite)),
'CAST(julianday(?) - julianday(?) AS INT)')
def test_sqlite_no_autoincrement(self):
table = Table(
"noautoinctable", MetaData(), Column("id", Integer, primary_key=True), Column("x", Integer, default=None)
)
self.assert_compile(
schema.CreateTable(table),
"CREATE TABLE noautoinctable (id INTEGER " "NOT NULL, x INTEGER, PRIMARY KEY (id))",
dialect=sqlite.dialect(),
)
def test_old_style_default(self):
"""test non-quoted integer value on older sqlite pragma"""
dialect = sqlite.dialect()
eq_(
dialect._get_column_info("foo", "INTEGER", False, 3, False),
{'primary_key': False, 'nullable': False,
'default': '3', 'autoincrement': False,
'type': INTEGER, 'name': 'foo'}
)
def test_sqlite_autoincrement_int_affinity(self):
class MyInteger(TypeDecorator):
impl = Integer
table = Table("autoinctable", MetaData(), Column("id", MyInteger, primary_key=True), sqlite_autoincrement=True)
self.assert_compile(
schema.CreateTable(table),
"CREATE TABLE autoinctable (id INTEGER NOT " "NULL PRIMARY KEY AUTOINCREMENT)",
dialect=sqlite.dialect(),
)
def test_old_style_default(self):
"""test non-quoted integer value on older sqlite pragma"""
dialect = sqlite.dialect()
eq_(
dialect._get_column_info("foo", "INTEGER", False, 3, False),
{'primary_key': False, 'nullable': False,
'default': '3', 'autoincrement': False,
'type': INTEGER, 'name': 'foo'}
)
def _test_lookup_direct(self, fixture, warnings=False):
dialect = sqlite.dialect()
for from_, to_ in self._fixture_as_string(fixture):
if warnings:
def go():
return dialect._resolve_type_affinity(from_)
final_type = testing.assert_warnings(go,
["Could not instantiate"], regex=True)
else:
final_type = dialect._resolve_type_affinity(from_)
expected_type = type(to_)
is_(type(final_type), expected_type)
def _test_lookup_direct(self, fixture, warnings=False):
dialect = sqlite.dialect()
for from_, to_ in self._fixture_as_string(fixture):
if warnings:
def go():
return dialect._resolve_type_affinity(from_)
final_type = testing.assert_warnings(go,
["Could not instantiate"], regex=True)
else:
final_type = dialect._resolve_type_affinity(from_)
expected_type = type(to_)
is_(type(final_type), expected_type)
def test_ddl(self):
class AddThingy(DDLElement):
__visit_name__ = 'add_thingy'
class DropThingy(DDLElement):
__visit_name__ = 'drop_thingy'
class MyCompiler(UserDefinedCompiler):
compile_elements = [AddThingy, DropThingy]
def visit_add_thingy(self, thingy, **kw):
return "ADD THINGY"
def visit_drop_thingy(self, thingy, **kw):
return "DROP THINGY"
class MySqliteCompiler(MyCompiler):
dialect = 'sqlite'
def visit_add_thingy(self, thingy, **kw):
return "ADD SPECIAL SL THINGY"
self.assert_compile(AddThingy(),
"ADD THINGY"
)
self.assert_compile(DropThingy(),
"DROP THINGY"
)
from sqlalchemy.dialects.sqlite import base
self.assert_compile(AddThingy(),
"ADD SPECIAL SL THINGY",
dialect=base.dialect()
)
self.assert_compile(DropThingy(),
"DROP THINGY",
dialect=base.dialect()
)
def test_select(self):
t1 = table("t1", column("c1"), column("c2"))
@compiles(Select, "sqlite")
def compile_(element, compiler, **kw):
return "OVERRIDE"
s1 = select([t1])
self.assert_compile(s1, "SELECT t1.c1, t1.c2 FROM t1")
from sqlalchemy.dialects.sqlite import base as sqlite
self.assert_compile(s1, "OVERRIDE", dialect=sqlite.dialect())
def test_sqlite_autoincrement(self):
table = Table(
"autoinctable",
MetaData(),
Column("id", Integer, primary_key=True),
Column("x", Integer, default=None),
sqlite_autoincrement=True,
)
self.assert_compile(
schema.CreateTable(table),
"CREATE TABLE autoinctable (id INTEGER NOT " "NULL PRIMARY KEY AUTOINCREMENT, x INTEGER)",
dialect=sqlite.dialect(),
)
def test_select(self):
t1 = table("t1", column("c1"), column("c2"))
@compiles(Select, "sqlite")
def compile_(element, compiler, **kw):
return "OVERRIDE"
s1 = select([t1])
self.assert_compile(s1, "SELECT t1.c1, t1.c2 FROM t1")
from sqlalchemy.dialects.sqlite import base as sqlite
self.assert_compile(s1, "OVERRIDE", dialect=sqlite.dialect())
def test_sqlite_autoincrement_int_affinity(self):
class MyInteger(sqltypes.TypeDecorator):
impl = Integer
table = Table(
'autoinctable',
MetaData(),
Column('id', MyInteger, primary_key=True),
sqlite_autoincrement=True,
)
self.assert_compile(schema.CreateTable(table),
'CREATE TABLE autoinctable (id INTEGER NOT '
'NULL PRIMARY KEY AUTOINCREMENT)',
dialect=sqlite.dialect())
def test_sqlite_autoincrement_int_affinity(self):
class MyInteger(sqltypes.TypeDecorator):
impl = Integer
table = Table(
'autoinctable',
MetaData(),
Column('id', MyInteger, primary_key=True),
sqlite_autoincrement=True,
)
self.assert_compile(schema.CreateTable(table),
'CREATE TABLE autoinctable (id INTEGER NOT '
'NULL PRIMARY KEY AUTOINCREMENT)',
dialect=sqlite.dialect())
def test_select(self):
t1 = table('t1', column('c1'), column('c2'))
@compiles(Select, 'sqlite')
def compile(element, compiler, **kw):
return "OVERRIDE"
s1 = select([t1])
self.assert_compile(
s1,
"SELECT t1.c1, t1.c2 FROM t1",
)
from sqlalchemy.dialects.sqlite import base as sqlite
self.assert_compile(s1, "OVERRIDE", dialect=sqlite.dialect())
def test_old_style_default(self):
"""test non-quoted integer value on older sqlite pragma"""
dialect = sqlite.dialect()
eq_(
dialect._get_column_info("foo", "INTEGER", False, 3, False),
{
"primary_key": False,
"nullable": False,
"default": "3",
"autoincrement": False,
"type": INTEGER,
"name": "foo",
},
)
def test_no_convert_unicode(self):
"""test no utf-8 encoding occurs"""
dialect = sqlite.dialect()
for t in (
String(convert_unicode=True),
CHAR(convert_unicode=True),
Unicode(),
UnicodeText(),
String(convert_unicode=True),
CHAR(convert_unicode=True),
Unicode(),
UnicodeText(),
):
bindproc = t.dialect_impl(dialect).bind_processor(dialect)
assert not bindproc or isinstance(bindproc(util.u("some string")), util.text_type)
def test_select(self):
t1 = table('t1', column('c1'), column('c2'))
@compiles(Select, 'sqlite')
def compile(element, compiler, **kw):
return "OVERRIDE"
s1 = select([t1])
self.assert_compile(
s1, "SELECT t1.c1, t1.c2 FROM t1",
)
from sqlalchemy.dialects.sqlite import base as sqlite
self.assert_compile(
s1, "OVERRIDE",
dialect=sqlite.dialect()
)
def test_no_convert_unicode(self):
"""test no utf-8 encoding occurs"""
dialect = sqlite.dialect()
for t in (
String(convert_unicode=True),
CHAR(convert_unicode=True),
Unicode(),
UnicodeText(),
String(convert_unicode=True),
CHAR(convert_unicode=True),
Unicode(),
UnicodeText(),
):
bindproc = t.dialect_impl(dialect).bind_processor(dialect)
assert not bindproc or \
isinstance(bindproc(util.u('some string')), util.text_type)
def test_legacy_quoted_identifiers_unit(self):
dialect = sqlite.dialect()
dialect._broken_fk_pragma_quotes = True
for row in [
(0, 'target', 'tid', 'id'),
(0, '"target"', 'tid', 'id'),
(0, '[target]', 'tid', 'id'),
(0, "'target'", 'tid', 'id'),
(0, '`target`', 'tid', 'id'),
]:
fks = {}
fkeys = []
dialect._parse_fk(fks, fkeys, *row)
eq_(fkeys, [{
'referred_table': 'target',
'referred_columns': ['id'],
'referred_schema': None,
'name': None,
'constrained_columns': ['tid']
}])
def test_legacy_quoted_identifiers_unit(self):
dialect = sqlite.dialect()
dialect._broken_fk_pragma_quotes = True
for row in [
(0, 'target', 'tid', 'id'),
(0, '"target"', 'tid', 'id'),
(0, '[target]', 'tid', 'id'),
(0, "'target'", 'tid', 'id'),
(0, '`target`', 'tid', 'id'),
]:
fks = {}
fkeys = []
dialect._parse_fk(fks, fkeys, *row)
eq_(fkeys, [{
'referred_table': 'target',
'referred_columns': ['id'],
'referred_schema': None,
'name': None,
'constrained_columns': ['tid']
}])
class SQLTest(TestBase, AssertsCompiledSQL):
"""Tests SQLite-dialect specific compilation."""
__dialect__ = sqlite.dialect()
def test_extract(self):
t = sql.table('t', sql.column('col1'))
mapping = {
'month': '%m',
'day': '%d',
'year': '%Y',
'second': '%S',
'hour': '%H',
'doy': '%j',
'minute': '%M',
'epoch': '%s',
'dow': '%w',
'week': '%W',
}
for field, subst in mapping.items():
self.assert_compile(select([extract(field, t.c.col1)]),
"SELECT CAST(STRFTIME('%s', t.col1) AS "
"INTEGER) AS anon_1 FROM t" % subst)
def test_sqlite_uses_max(self):
from sqlalchemy.dialects.sqlite.base import dialect
sqlite = dialect()
self.assertEqual(
str(self.least(1, 2).compile(dialect=sqlite)),
'MIN(?, ?)')
def test_sqlite_needs_two_arguments(self):
from sqlalchemy.dialects.sqlite.base import dialect
sqlite = dialect()
self.assertRaises(ValueError,
self.day_difference(1, 2, 3).compile, dialect=sqlite)
def test_expression(self):
self.assert_compile(matchtable.c.title.match('somstr'),
'matchtable.title MATCH ?', dialect=sqlite.dialect())
def test_old_style_default(self):
"""test non-quoted integer value on older sqlite pragma"""
dialect = sqlite.dialect()
info = dialect._get_column_info("foo", "INTEGER", False, 3, False)
eq_(info['default'], '3')
def test_expression(self):
self.assert_compile(matchtable.c.title.match("somstr"), "matchtable.title MATCH ?", dialect=sqlite.dialect())
def test_old_style_default(self):
"""test non-quoted integer value on older sqlite pragma"""
dialect = sqlite.dialect()
info = dialect._get_column_info("foo", "INTEGER", False, 3, False)
eq_(info['default'], '3')
def test_expression(self):
self.assert_compile(matchtable.c.title.match('somstr'),
'matchtable.title MATCH ?',
dialect=sqlite.dialect())
class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
"""Tests SQLite-dialect specific compilation."""
__dialect__ = sqlite.dialect()
def test_extract(self):
t = sql.table('t', sql.column('col1'))
mapping = {
'month': '%m',
'day': '%d',
'year': '%Y',
'second': '%S',
'hour': '%H',
'doy': '%j',
'minute': '%M',
'epoch': '%s',
'dow': '%w',
'week': '%W',
}
for field, subst in mapping.items():
self.assert_compile(
select([extract(field, t.c.col1)]),
"SELECT CAST(STRFTIME('%s', t.col1) AS "
"INTEGER) AS anon_1 FROM t" % subst)
def test_true_false(self):
self.assert_compile(sql.false(), "0")
self.assert_compile(sql.true(), "1")
def test_localtime(self):
self.assert_compile(func.localtimestamp(),
'DATETIME(CURRENT_TIMESTAMP, "localtime")')
def test_constraints_with_schemas(self):
metadata = MetaData()
t1 = Table('t1',
metadata,
Column('id', Integer, primary_key=True),
schema='master')
t2 = Table('t2',
metadata,
Column('id', Integer, primary_key=True),
Column('t1_id', Integer, ForeignKey('master.t1.id')),
schema='master')
t3 = Table('t3',
metadata,
Column('id', Integer, primary_key=True),
Column('t1_id', Integer, ForeignKey('master.t1.id')),
schema='alternate')
t4 = Table(
't4',
metadata,
Column('id', Integer, primary_key=True),
Column('t1_id', Integer, ForeignKey('master.t1.id')),
)
# schema->schema, generate REFERENCES with no schema name
self.assert_compile(
schema.CreateTable(t2), "CREATE TABLE master.t2 ("
"id INTEGER NOT NULL, "
"t1_id INTEGER, "
"PRIMARY KEY (id), "
"FOREIGN KEY(t1_id) REFERENCES t1 (id)"
")")
# schema->different schema, don't generate REFERENCES
self.assert_compile(
schema.CreateTable(t3), "CREATE TABLE alternate.t3 ("
"id INTEGER NOT NULL, "
"t1_id INTEGER, "
"PRIMARY KEY (id)"
")")
# same for local schema
self.assert_compile(
schema.CreateTable(t4), "CREATE TABLE t4 ("
"id INTEGER NOT NULL, "
"t1_id INTEGER, "
"PRIMARY KEY (id)"
")")