def test_that_mssql_specified_not_nullable_emits_not_null(self):
schemagenerator = \
mssql.MSSQLDialect().schemagenerator(mssql.MSSQLDialect(), None)
self.column.nullable = False
column_specification = \
schemagenerator.get_column_specification(self.column)
eq_("test_column VARCHAR NOT NULL", column_specification)
def test_that_mssql_none_nullability_does_not_emit_nullability(self):
schemagenerator = \
mssql.MSSQLDialect().schemagenerator(mssql.MSSQLDialect(), None)
self.column.nullable = None
column_specification = \
schemagenerator.get_column_specification(self.column)
eq_("test_column VARCHAR", column_specification)
def testmsnvarchar(self):
dialect = mssql.MSSQLDialect()
# run the test twice to ensure the caching step works too
for x in range(0, 1):
col = Column('', Unicode(length=10))
dialect_type = col.type.dialect_impl(dialect)
assert isinstance(dialect_type, mssql.MSNVarchar)
assert dialect_type.get_col_spec() == 'NVARCHAR(10)'
def testdialecttypedecorators(self):
"""test that a a Dialect can provide a dialect-specific subclass of a TypeDecorator subclass."""
import sqlalchemy.databases.mssql as mssql
dialect = mssql.MSSQLDialect()
# run the test twice to insure the caching step works too
for x in range(0, 1):
col = Column('', Unicode(length=10))
dialect_type = col.type.dialect_impl(dialect)
assert isinstance(dialect_type, mssql.MSUnicode)
assert dialect_type.get_col_spec() == 'NVARCHAR(10)'
assert isinstance(dialect_type.impl, mssql.MSString)
def test_that_mssql_default_nullability_emits_null(self):
schemagenerator = \
mssql.MSSQLDialect().schemagenerator(mssql.MSSQLDialect(), None)
column_specification = \
schemagenerator.get_column_specification(self.column)
eq_("test_column VARCHAR NULL", column_specification)
class IdentityInsertTest(TestBase, AssertsCompiledSQL):
__only_on__ = 'mssql'
__dialect__ = mssql.MSSQLDialect()
@classmethod
def setup_class(cls):
global metadata, cattable
metadata = MetaData(testing.db)
cattable = Table(
'cattable',
metadata,
Column('id', Integer),
Column('description', String(50)),
PrimaryKeyConstraint('id', name='PK_cattable'),
)
def setup(self):
metadata.create_all()
def teardown(self):
metadata.drop_all()
def test_compiled(self):
self.assert_compile(
cattable.insert().values(id=9, description='Python'),
"INSERT INTO cattable (id, description) VALUES (:id, :description)"
)
def test_execute(self):
cattable.insert().values(id=9, description='Python').execute()
cats = cattable.select().order_by(cattable.c.id).execute()
eq_([(9, 'Python')], list(cats))
result = cattable.insert().values(description='PHP').execute()
eq_([10], result.last_inserted_ids())
lastcat = cattable.select().order_by(desc(cattable.c.id)).execute()
eq_((10, 'PHP'), lastcat.fetchone())
def test_executemany(self):
cattable.insert().execute([
{
'id': 89,
'description': 'Python'
},
{
'id': 8,
'description': 'Ruby'
},
{
'id': 3,
'description': 'Perl'
},
{
'id': 1,
'description': 'Java'
},
])
cats = cattable.select().order_by(cattable.c.id).execute()
eq_([(1, 'Java'), (3, 'Perl'), (8, 'Ruby'), (89, 'Python')],
list(cats))
cattable.insert().execute([
{
'description': 'PHP'
},
{
'description': 'Smalltalk'
},
])
lastcats = cattable.select().order_by(desc(
cattable.c.id)).limit(2).execute()
eq_([(91, 'Smalltalk'), (90, 'PHP')], list(lastcats))
class CompileTest(TestBase, AssertsCompiledSQL):
__dialect__ = mssql.MSSQLDialect()
def test_insert(self):
t = table('sometable', column('somecolumn'))
self.assert_compile(
t.insert(),
"INSERT INTO sometable (somecolumn) VALUES (:somecolumn)")
def test_update(self):
t = table('sometable', column('somecolumn'))
self.assert_compile(
t.update(t.c.somecolumn == 7),
"UPDATE sometable SET somecolumn=:somecolumn WHERE sometable.somecolumn = :somecolumn_1",
dict(somecolumn=10))
def test_in_with_subqueries(self):
"""Test that when using subqueries in a binary expression
the == and != are changed to IN and NOT IN respectively.
"""
t = table('sometable', column('somecolumn'))
self.assert_compile(
t.select().where(t.c.somecolumn == t.select()),
"SELECT sometable.somecolumn FROM sometable WHERE sometable.somecolumn IN (SELECT sometable.somecolumn FROM sometable)"
)
self.assert_compile(
t.select().where(t.c.somecolumn != t.select()),
"SELECT sometable.somecolumn FROM sometable WHERE sometable.somecolumn NOT IN (SELECT sometable.somecolumn FROM sometable)"
)
def test_count(self):
t = table('sometable', column('somecolumn'))
self.assert_compile(
t.count(),
"SELECT count(sometable.somecolumn) AS tbl_row_count FROM sometable"
)
def test_noorderby_insubquery(self):
"""test that the ms-sql dialect removes ORDER BY clauses from subqueries"""
table1 = table(
'mytable',
column('myid', Integer),
column('name', String),
column('description', String),
)
q = select([table1.c.myid], order_by=[table1.c.myid]).alias('foo')
crit = q.c.myid == table1.c.myid
self.assert_compile(
select(['*'], crit),
"""SELECT * FROM (SELECT mytable.myid AS myid FROM mytable) AS foo, mytable WHERE foo.myid = mytable.myid"""
)
def test_aliases_schemas(self):
metadata = MetaData()
table1 = table(
'mytable',
column('myid', Integer),
column('name', String),
column('description', String),
)
table4 = Table('remotetable',
metadata,
Column('rem_id', Integer, primary_key=True),
Column('datatype_id', Integer),
Column('value', String(20)),
schema='remote_owner')
s = table4.select()
c = s.compile(dialect=self.__dialect__)
assert table4.c.rem_id in set(c.result_map['rem_id'][1])
s = table4.select(use_labels=True)
c = s.compile(dialect=self.__dialect__)
print c.result_map
assert table4.c.rem_id in set(
c.result_map['remote_owner_remotetable_rem_id'][1])
self.assert_compile(
table4.select(),
"SELECT remotetable_1.rem_id, remotetable_1.datatype_id, remotetable_1.value FROM remote_owner.remotetable AS remotetable_1"
)
self.assert_compile(
table4.select(use_labels=True),
"SELECT remotetable_1.rem_id AS remote_owner_remotetable_rem_id, remotetable_1.datatype_id AS remote_owner_remotetable_datatype_id, remotetable_1.value AS remote_owner_remotetable_value FROM remote_owner.remotetable AS remotetable_1"
)
self.assert_compile(
table1.join(table4, table1.c.myid == table4.c.rem_id).select(),
"SELECT mytable.myid, mytable.name, mytable.description, remotetable_1.rem_id, remotetable_1.datatype_id, remotetable_1.value FROM mytable JOIN remote_owner.remotetable AS remotetable_1 ON remotetable_1.rem_id = mytable.myid"
)
def test_delete_schema(self):
metadata = MetaData()
tbl = Table('test',
metadata,
Column('id', Integer, primary_key=True),
schema='paj')
self.assert_compile(tbl.delete(tbl.c.id == 1),
"DELETE FROM paj.test WHERE paj.test.id = :id_1")
s = select([tbl.c.id]).where(tbl.c.id == 1)
self.assert_compile(
tbl.delete().where(tbl.c.id == (s)),
"DELETE FROM paj.test WHERE paj.test.id IN (SELECT test_1.id FROM paj.test AS test_1 WHERE test_1.id = :id_1)"
)
def test_delete_schema_multipart(self):
metadata = MetaData()
tbl = Table('test',
metadata,
Column('id', Integer, primary_key=True),
schema='banana.paj')
self.assert_compile(
tbl.delete(tbl.c.id == 1),
"DELETE FROM banana.paj.test WHERE banana.paj.test.id = :id_1")
s = select([tbl.c.id]).where(tbl.c.id == 1)
self.assert_compile(
tbl.delete().where(tbl.c.id == (s)),
"DELETE FROM banana.paj.test WHERE banana.paj.test.id IN (SELECT test_1.id FROM banana.paj.test AS test_1 WHERE test_1.id = :id_1)"
)
def test_delete_schema_multipart_needs_quoting(self):
metadata = MetaData()
tbl = Table('test',
metadata,
Column('id', Integer, primary_key=True),
schema='banana split.paj')
self.assert_compile(
tbl.delete(tbl.c.id == 1),
"DELETE FROM [banana split].paj.test WHERE [banana split].paj.test.id = :id_1"
)
s = select([tbl.c.id]).where(tbl.c.id == 1)
self.assert_compile(
tbl.delete().where(tbl.c.id == (s)),
"DELETE FROM [banana split].paj.test WHERE [banana split].paj.test.id IN (SELECT test_1.id FROM [banana split].paj.test AS test_1 WHERE test_1.id = :id_1)"
)
def test_delete_schema_multipart_both_need_quoting(self):
metadata = MetaData()
tbl = Table('test',
metadata,
Column('id', Integer, primary_key=True),
schema='banana split.paj with a space')
self.assert_compile(
tbl.delete(tbl.c.id == 1),
"DELETE FROM [banana split].[paj with a space].test WHERE [banana split].[paj with a space].test.id = :id_1"
)
s = select([tbl.c.id]).where(tbl.c.id == 1)
self.assert_compile(
tbl.delete().where(tbl.c.id == (s)),
"DELETE FROM [banana split].[paj with a space].test WHERE [banana split].[paj with a space].test.id IN (SELECT test_1.id FROM [banana split].[paj with a space].test AS test_1 WHERE test_1.id = :id_1)"
)
def test_union(self):
t1 = table('t1', column('col1'), column('col2'), column('col3'),
column('col4'))
t2 = table('t2', column('col1'), column('col2'), column('col3'),
column('col4'))
(s1, s2) = (select([t1.c.col3.label('col3'),
t1.c.col4.label('col4')],
t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
select([t2.c.col3.label('col3'),
t2.c.col4.label('col4')],
t2.c.col2.in_(["t2col2r2", "t2col2r3"])))
u = union(s1, s2, order_by=['col3', 'col4'])
self.assert_compile(u, "SELECT t1.col3 AS col3, t1.col4 AS col4 FROM t1 WHERE t1.col2 IN (:col2_1, :col2_2) "\
"UNION SELECT t2.col3 AS col3, t2.col4 AS col4 FROM t2 WHERE t2.col2 IN (:col2_3, :col2_4) ORDER BY col3, col4")
self.assert_compile(u.alias('bar').select(), "SELECT bar.col3, bar.col4 FROM (SELECT t1.col3 AS col3, t1.col4 AS col4 FROM t1 WHERE "\
"t1.col2 IN (:col2_1, :col2_2) UNION SELECT t2.col3 AS col3, t2.col4 AS col4 FROM t2 WHERE t2.col2 IN (:col2_3, :col2_4)) AS bar")
def test_function(self):
self.assert_compile(func.foo(1, 2), "foo(:foo_1, :foo_2)")
self.assert_compile(func.current_time(), "CURRENT_TIME")
self.assert_compile(func.foo(), "foo()")
m = MetaData()
t = Table('sometable', m, Column('col1', Integer),
Column('col2', Integer))
self.assert_compile(
select([func.max(t.c.col1)]),
"SELECT max(sometable.col1) AS max_1 FROM sometable")
def test_function_overrides(self):
self.assert_compile(func.current_date(), "GETDATE()")
self.assert_compile(func.length(3), "LEN(:length_1)")
def test_extract(self):
t = table('t', column('col1'))
for field in 'day', 'month', 'year':
self.assert_compile(
select([extract(field, t.c.col1)]),
'SELECT DATEPART("%s", t.col1) AS anon_1 FROM t' % field)