class ReflectionTest(fixtures.TestBase, ComparesTables, AssertsCompiledSQL): __only_on__ = "mssql" __backend__ = True def test_basic_reflection(self, metadata, connection): meta = metadata users = Table( "engine_users", meta, Column("user_id", types.INT, primary_key=True), Column("user_name", types.VARCHAR(20), nullable=False), Column("test1", types.CHAR(5), nullable=False), Column("test2", types.Float(5), nullable=False), Column("test2.5", types.Float(), nullable=False), Column("test3", types.Text()), Column("test4", types.Numeric, nullable=False), Column("test4.5", types.Numeric(10, 2), nullable=False), Column("test5", types.DateTime), Column( "parent_user_id", types.Integer, ForeignKey("engine_users.user_id"), ), Column("test6", types.DateTime, nullable=False), Column("test7", types.Text()), Column("test8", types.LargeBinary()), Column("test_passivedefault2", types.Integer, server_default="5"), Column("test9", types.BINARY(100)), Column("test_numeric", types.Numeric()), ) addresses = Table( "engine_email_addresses", meta, Column("address_id", types.Integer, primary_key=True), Column("remote_user_id", types.Integer, ForeignKey(users.c.user_id)), Column("email_address", types.String(20)), ) meta.create_all(connection) meta2 = MetaData() reflected_users = Table("engine_users", meta2, autoload_with=connection) reflected_addresses = Table( "engine_email_addresses", meta2, autoload_with=connection, ) self.assert_tables_equal(users, reflected_users) self.assert_tables_equal(addresses, reflected_addresses) @testing.combinations( (mssql.XML, "XML"), (mssql.IMAGE, "IMAGE"), (mssql.MONEY, "MONEY"), (mssql.NUMERIC(10, 2), "NUMERIC(10, 2)"), (mssql.FLOAT, "FLOAT(53)"), (mssql.REAL, "REAL"), # FLOAT(5) comes back as REAL (mssql.FLOAT(5), "REAL"), argnames="type_obj,ddl", ) def test_assorted_types(self, metadata, connection, type_obj, ddl): table = Table("type_test", metadata, Column("col1", type_obj)) table.create(connection) m2 = MetaData() table2 = Table("type_test", m2, autoload_with=connection) self.assert_compile( schema.CreateTable(table2), "CREATE TABLE type_test (col1 %s NULL)" % ddl, ) def test_identity(self, metadata, connection): table = Table( "identity_test", metadata, Column( "col1", Integer, mssql_identity_start=2, mssql_identity_increment=3, primary_key=True, ), ) with testing.expect_deprecated( "The dialect options 'mssql_identity_start' and"): table.create(connection) meta2 = MetaData() table2 = Table("identity_test", meta2, autoload_with=connection) eq_(table2.c["col1"].dialect_options["mssql"]["identity_start"], None) eq_( table2.c["col1"].dialect_options["mssql"]["identity_increment"], None, ) eq_(table2.c["col1"].identity.start, 2) eq_(table2.c["col1"].identity.increment, 3) def test_skip_types(self, connection): connection.exec_driver_sql( "create table foo (id integer primary key, data xml)") with mock.patch.object(connection.dialect, "ischema_names", {"int": mssql.INTEGER}): with testing.expect_warnings( "Did not recognize type 'xml' of column 'data'"): eq_( inspect(connection).get_columns("foo"), [ { "name": "id", "type": testing.eq_type_affinity(sqltypes.INTEGER), "nullable": False, "default": None, "autoincrement": False, }, { "name": "data", "type": testing.eq_type_affinity( sqltypes.NullType), "nullable": True, "default": None, "autoincrement": False, }, ], ) def test_cross_schema_fk_pk_name_overlaps(self, metadata, connection): # test for issue #4228 Table( "subject", metadata, Column("id", Integer), PrimaryKeyConstraint("id", name="subj_pk"), schema=testing.config.test_schema, ) Table( "referrer", metadata, Column("id", Integer, primary_key=True), Column( "sid", ForeignKey( "%s.subject.id" % testing.config.test_schema, name="fk_subject", ), ), schema=testing.config.test_schema, ) Table( "subject", metadata, Column("id", Integer), PrimaryKeyConstraint("id", name="subj_pk"), schema=testing.config.test_schema_2, ) metadata.create_all(connection) insp = inspect(connection) eq_( insp.get_foreign_keys("referrer", testing.config.test_schema), [{ "name": "fk_subject", "constrained_columns": ["sid"], "referred_schema": "test_schema", "referred_table": "subject", "referred_columns": ["id"], "options": {}, }], ) def test_table_name_that_is_greater_than_16_chars(self, metadata, connection): Table( "ABCDEFGHIJKLMNOPQRSTUVWXYZ", metadata, Column("id", Integer, primary_key=True), Column("foo", Integer), Index("foo_idx", "foo"), ) metadata.create_all(connection) t = Table("ABCDEFGHIJKLMNOPQRSTUVWXYZ", MetaData(), autoload_with=connection) eq_(t.name, "ABCDEFGHIJKLMNOPQRSTUVWXYZ") @testing.combinations( ("local_temp", "#tmp", True), ("global_temp", "##tmp", True), ("nonexistent", "#no_es_bueno", False), id_="iaa", argnames="table_name, exists", ) def test_temporary_table(self, metadata, connection, table_name, exists): if exists: tt = Table( table_name, metadata, Column("id", Integer, primary_key=True), Column("txt", mssql.NVARCHAR(50)), Column("dt2", mssql.DATETIME2), ) tt.create(connection) connection.execute( tt.insert(), [ { "id": 1, "txt": "foo", "dt2": datetime.datetime(2020, 1, 1, 1, 1, 1), }, { "id": 2, "txt": "bar", "dt2": datetime.datetime(2020, 2, 2, 2, 2, 2), }, ], ) if not exists: with expect_raises(exc.NoSuchTableError): Table( table_name, metadata, autoload_with=connection, ) else: tmp_t = Table(table_name, metadata, autoload_with=connection) result = connection.execute( tmp_t.select().where(tmp_t.c.id == 2)).fetchall() eq_( result, [(2, "bar", datetime.datetime(2020, 2, 2, 2, 2, 2))], ) @testing.combinations( ("local_temp", "#tmp", True), ("global_temp", "##tmp", True), ("nonexistent", "#no_es_bueno", False), id_="iaa", argnames="table_name, exists", ) def test_has_table_temporary(self, metadata, connection, table_name, exists): if exists: tt = Table( table_name, metadata, Column("id", Integer), ) tt.create(connection) found_it = testing.db.dialect.has_table(connection, table_name) eq_(found_it, exists) def test_has_table_temp_not_present_but_another_session(self): """test #6910""" with testing.db.connect() as c1, testing.db.connect() as c2: try: with c1.begin(): c1.exec_driver_sql( "create table #myveryveryuniquetemptablename (a int)") assert not c2.dialect.has_table( c2, "#myveryveryuniquetemptablename") finally: with c1.begin(): c1.exec_driver_sql( "drop table #myveryveryuniquetemptablename") def test_has_table_temp_temp_present_both_sessions(self): """test #7168, continues from #6910""" with testing.db.connect() as c1, testing.db.connect() as c2: try: with c1.begin(): c1.exec_driver_sql( "create table #myveryveryuniquetemptablename (a int)") with c2.begin(): c2.exec_driver_sql( "create table #myveryveryuniquetemptablename (a int)") assert c2.dialect.has_table(c2, "#myveryveryuniquetemptablename") c2.rollback() finally: with c1.begin(): c1.exec_driver_sql( "drop table #myveryveryuniquetemptablename") with c2.begin(): c2.exec_driver_sql( "drop table #myveryveryuniquetemptablename") def test_db_qualified_items(self, metadata, connection): Table("foo", metadata, Column("id", Integer, primary_key=True)) Table( "bar", metadata, Column("id", Integer, primary_key=True), Column("foo_id", Integer, ForeignKey("foo.id", name="fkfoo")), ) metadata.create_all(connection) dbname = connection.exec_driver_sql("select db_name()").scalar() owner = connection.exec_driver_sql("SELECT user_name()").scalar() referred_schema = "%(dbname)s.%(owner)s" % { "dbname": dbname, "owner": owner, } inspector = inspect(connection) bar_via_db = inspector.get_foreign_keys("bar", schema=referred_schema) eq_( bar_via_db, [{ "referred_table": "foo", "referred_columns": ["id"], "referred_schema": referred_schema, "name": "fkfoo", "constrained_columns": ["foo_id"], "options": {}, }], ) assert inspect(connection).has_table("bar", schema=referred_schema) m2 = MetaData() Table( "bar", m2, schema=referred_schema, autoload_with=connection, ) eq_(m2.tables["%s.foo" % referred_schema].schema, referred_schema) def test_fk_on_unique_index(self, metadata, connection): # test for issue #7160 Table( "uidx_parent", metadata, Column("id", Integer, primary_key=True), Column("uidx_col1", Integer, nullable=False), Column("uidx_col2", Integer, nullable=False), Index( "UIDX_composite", "uidx_col1", "uidx_col2", unique=True, ), ) Table( "uidx_child", metadata, Column("id", Integer, primary_key=True), Column("parent_uidx_col1", Integer, nullable=False), Column("parent_uidx_col2", Integer, nullable=False), ForeignKeyConstraint( ["parent_uidx_col1", "parent_uidx_col2"], ["uidx_parent.uidx_col1", "uidx_parent.uidx_col2"], name="FK_uidx_parent", ), ) metadata.create_all(connection) inspector = inspect(connection) fk_info = inspector.get_foreign_keys("uidx_child") eq_( fk_info, [{ "referred_table": "uidx_parent", "referred_columns": ["uidx_col1", "uidx_col2"], "referred_schema": None, "name": "FK_uidx_parent", "constrained_columns": [ "parent_uidx_col1", "parent_uidx_col2", ], "options": {}, }], ) def test_indexes_cols(self, metadata, connection): t1 = Table("t", metadata, Column("x", Integer), Column("y", Integer)) Index("foo", t1.c.x, t1.c.y) metadata.create_all(connection) m2 = MetaData() t2 = Table("t", m2, autoload_with=connection) eq_(set(list(t2.indexes)[0].columns), set([t2.c["x"], t2.c.y])) def test_indexes_cols_with_commas(self, metadata, connection): t1 = Table( "t", metadata, Column("x, col", Integer, key="x"), Column("y", Integer), ) Index("foo", t1.c.x, t1.c.y) metadata.create_all(connection) m2 = MetaData() t2 = Table("t", m2, autoload_with=connection) eq_(set(list(t2.indexes)[0].columns), set([t2.c["x, col"], t2.c.y])) def test_indexes_cols_with_spaces(self, metadata, connection): t1 = Table( "t", metadata, Column("x col", Integer, key="x"), Column("y", Integer), ) Index("foo", t1.c.x, t1.c.y) metadata.create_all(connection) m2 = MetaData() t2 = Table("t", m2, autoload_with=connection) eq_(set(list(t2.indexes)[0].columns), set([t2.c["x col"], t2.c.y])) def test_indexes_with_filtered(self, metadata, connection): t1 = Table( "t", metadata, Column("id", Integer, primary_key=True), Column("x", types.String(20)), Column("y", types.Integer), ) Index("idx_x", t1.c.x, mssql_where=t1.c.x == "test") Index("idx_y", t1.c.y, mssql_where=t1.c.y >= 5) metadata.create_all(connection) ind = testing.db.dialect.get_indexes(connection, "t", None) filtered_indexes = [] for ix in ind: if "dialect_options" in ix: filtered_indexes.append(ix["dialect_options"]["mssql_where"]) eq_(sorted(filtered_indexes), ["([x]='test')", "([y]>=(5))"]) t2 = Table("t", MetaData(), autoload_with=connection) idx = list(sorted(t2.indexes, key=lambda idx: idx.name))[0] self.assert_compile(CreateIndex(idx), "CREATE INDEX idx_x ON t (x) WHERE ([x]='test')") def test_max_ident_in_varchar_not_present(self, metadata, connection): """test [ticket:3504]. Here we are testing not just that the "max" token comes back as None, but also that these types accept "max" as the value of "length" on construction, which isn't a directly documented pattern however is likely in common use. """ Table( "t", metadata, Column("t1", types.String), Column("t2", types.Text("max")), Column("t3", types.Text("max")), Column("t4", types.LargeBinary("max")), Column("t5", types.VARBINARY("max")), ) metadata.create_all(connection) for col in inspect(connection).get_columns("t"): is_(col["type"].length, None) in_("max", str(col["type"].compile(dialect=connection.dialect)))
def test_float_as_real(self): # FLOAT(5) comes back as REAL self._test_specific_type(mssql.FLOAT(5), "REAL")