def test_create_pk_with_using(self): m = MetaData() tbl = Table( "testtbl", m, Column("data", String(255)), PrimaryKeyConstraint("data", mysql_using="btree"), ) self.assert_compile( schema.CreateTable(tbl), "CREATE TABLE testtbl (data VARCHAR(255) NOT NULL, " "PRIMARY KEY (data) USING btree)", )
def test_column_computed_raises(self, persisted): m = MetaData() t = Table( "t", m, Column("x", Integer), Column("y", Integer, Computed("x + 2", persisted=persisted)), ) assert_raises_message( exc.CompileError, "Firebird computed columns do not support a persistence method", schema.CreateTable(t).compile, dialect=firebird.dialect(), )
def test_column_computed(self, text, persisted): m = MetaData() kwargs = {"persisted": persisted} if persisted != "ignore" else {} t = Table( "t", m, Column("x", Integer), Column("y", Integer, Computed("x + 2", **kwargs)), ) self.assert_compile( schema.CreateTable(t), "CREATE TABLE t (x INTEGER, y INTEGER GENERATED " "ALWAYS AS (x + 2)%s)" % text, )
def test_identity_separate_from_primary_key(self): metadata = MetaData() tbl = Table( "test", metadata, Column("id", Integer, autoincrement=False, primary_key=True), Column("x", Integer, autoincrement=True), ) self.assert_compile( schema.CreateTable(tbl), "CREATE TABLE test (id INTEGER NOT NULL, " "x INTEGER NOT NULL IDENTITY(1,1), " "PRIMARY KEY (id))", )
def test_table_uc_explicit_nonclustered(self): metadata = MetaData() tbl = Table( "test", metadata, Column("x", Integer, autoincrement=False), Column("y", Integer, autoincrement=False), UniqueConstraint("x", "y", mssql_clustered=False), ) self.assert_compile( schema.CreateTable(tbl), "CREATE TABLE test (x INTEGER NULL, y INTEGER NULL, " "UNIQUE NONCLUSTERED (x, y))", )
def test_table_pkc_clustering(self): metadata = MetaData() tbl = Table( "test", metadata, Column("x", Integer, autoincrement=False), Column("y", Integer, autoincrement=False), PrimaryKeyConstraint("x", "y", mssql_clustered=True), ) self.assert_compile( schema.CreateTable(tbl), "CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NOT NULL, " "PRIMARY KEY CLUSTERED (x, y))", )
def test_sequence_start_0(self): metadata = MetaData() tbl = Table( "test", metadata, Column("id", Integer, Sequence("", 0), primary_key=True), ) with testing.expect_deprecated( "Use of Sequence with SQL Server in order to affect "): self.assert_compile( schema.CreateTable(tbl), "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(0,1), " "PRIMARY KEY (id))", )
def test_identity_illegal_two_autoincrements(self): metadata = MetaData() tbl = Table( "test", metadata, Column("id", Integer, autoincrement=True), Column("id2", Integer, autoincrement=True), ) # this will be rejected by the database, just asserting this is what # the two autoincrements will do right now self.assert_compile( schema.CreateTable(tbl), "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(1,1), " "id2 INTEGER NOT NULL IDENTITY(1,1))", )
def test_column_level_ck_name(self): t = Table( "tbl", MetaData(), Column( "a", Integer, CheckConstraint("a > 5", name="ck_a_greater_five"), ), ) self.assert_compile( schema.CreateTable(t), "CREATE TABLE tbl (a INTEGER CONSTRAINT " "ck_a_greater_five CHECK (a > 5))", )
def test_innodb_autoincrement(self): t1 = Table( "sometable", MetaData(), Column("assigned_id", Integer(), primary_key=True, autoincrement=False), Column("id", Integer(), primary_key=True, autoincrement=True), mysql_engine="InnoDB", ) self.assert_compile( schema.CreateTable(t1), "CREATE TABLE sometable (assigned_id " "INTEGER NOT NULL, id INTEGER NOT NULL " "AUTO_INCREMENT, PRIMARY KEY (id, assigned_id)" ")ENGINE=InnoDB", ) t1 = Table( "sometable", MetaData(), Column("assigned_id", Integer(), primary_key=True, autoincrement=True), Column("id", Integer(), primary_key=True, autoincrement=False), mysql_engine="InnoDB", ) self.assert_compile( schema.CreateTable(t1), "CREATE TABLE sometable (assigned_id " "INTEGER NOT NULL AUTO_INCREMENT, id " "INTEGER NOT NULL, PRIMARY KEY " "(assigned_id, id))ENGINE=InnoDB", )
def test_table_options(self): m = MetaData() t = Table( "foo", m, Column("x", Integer), prefixes=["GLOBAL TEMPORARY"], oracle_on_commit="PRESERVE ROWS", ) self.assert_compile( schema.CreateTable(t), "CREATE GLOBAL TEMPORARY TABLE " "foo (x INTEGER) ON COMMIT PRESERVE ROWS", )
def test_innodb_autoincrement_reserved_word_column_name(self): t1 = Table( "sometable", MetaData(), Column("id", Integer(), primary_key=True, autoincrement=False), Column("order", Integer(), primary_key=True, autoincrement=True), mysql_engine="InnoDB", ) self.assert_compile( schema.CreateTable(t1), "CREATE TABLE sometable (" "id INTEGER NOT NULL, " "`order` INTEGER NOT NULL AUTO_INCREMENT, " "PRIMARY KEY (`order`, id)" ")ENGINE=InnoDB", )
def test_varchar_raise(self, type_): type_ = sqltypes.to_instance(type_) assert_raises_message( exc.CompileError, "VARCHAR requires a length on dialect mysql", type_.compile, dialect=mysql.dialect(), ) t1 = Table("sometable", MetaData(), Column("somecolumn", type_)) assert_raises_message( exc.CompileError, r"\(in table 'sometable', column 'somecolumn'\)\: " r"(?:N)?VARCHAR requires a length on dialect mysql", schema.CreateTable(t1).compile, dialect=mysql.dialect(), )
def test_deferrable_column_fk(self): t = Table( "tbl", MetaData(), Column("a", Integer), Column( "b", Integer, ForeignKey("tbl.a", deferrable=True, initially="DEFERRED"), ), ) self.assert_compile( schema.CreateTable(t), "CREATE TABLE tbl (a INTEGER, b INTEGER, " "FOREIGN KEY(b) REFERENCES tbl " "(a) DEFERRABLE INITIALLY DEFERRED)", )
def test_deferrable_column_check(self): t = Table( "tbl", MetaData(), Column("a", Integer), Column( "b", Integer, CheckConstraint("a < b", deferrable=True, initially="DEFERRED"), ), ) self.assert_compile( schema.CreateTable(t), "CREATE TABLE tbl (a INTEGER, b INTEGER CHECK (a < b) " "DEFERRABLE INITIALLY DEFERRED)", )
def test_create_table_with_partition_hash(self): t1 = Table( "testtable", MetaData(), Column("id", Integer(), primary_key=True, autoincrement=True), Column("other_id", Integer(), primary_key=True, autoincrement=False), mysql_partitions="2", mysql_partition_by="HASH(other_id)", ) self.assert_compile( schema.CreateTable(t1), "CREATE TABLE testtable (" "id INTEGER NOT NULL AUTO_INCREMENT, " "other_id INTEGER NOT NULL, " "PRIMARY KEY (id, other_id)" ")PARTITION BY HASH(other_id) PARTITIONS 2", )
def test_deferrable_initially_kw_not_ignored(self): m = MetaData() Table("t1", m, Column("id", Integer, primary_key=True)) t2 = Table( "t2", m, Column( "id", Integer, ForeignKey("t1.id", deferrable=True, initially="DEFERRED"), primary_key=True, ), ) self.assert_compile( schema.CreateTable(t2), "CREATE TABLE t2 (id INTEGER NOT NULL, " "PRIMARY KEY (id), FOREIGN KEY(id) REFERENCES t1 (id) " "DEFERRABLE INITIALLY DEFERRED)", )
def test_fk_match_clause(self): t = Table( "tbl", MetaData(), Column("a", Integer), Column("b", Integer, ForeignKey("tbl.a", match="SIMPLE")), ) self.assert_compile( schema.CreateTable(t), "CREATE TABLE tbl (a INTEGER, b INTEGER, " "FOREIGN KEY(b) REFERENCES tbl " "(a) MATCH SIMPLE)", ) self.assert_compile( schema.AddConstraint(list(t.foreign_keys)[0].constraint), "ALTER TABLE tbl ADD FOREIGN KEY(b) " "REFERENCES tbl (a) MATCH SIMPLE", )
def test_match_kw_raises(self): m = MetaData() Table("t1", m, Column("id", Integer, primary_key=True)) t2 = Table( "t2", m, Column( "id", Integer, ForeignKey("t1.id", match="XYZ"), primary_key=True, ), ) assert_raises_message( exc.CompileError, "MySQL ignores the 'MATCH' keyword while at the same time causes " "ON UPDATE/ON DELETE clauses to be ignored.", schema.CreateTable(t2).compile, dialect=mysql.dialect(), )
def test_render_ck_constraint_inline(self): t, t2 = self._constraint_create_fixture() CheckConstraint( "a < b", name="my_test_constraint", deferrable=True, initially="DEFERRED", table=t, ) # before we create an AddConstraint, # the CONSTRAINT comes out inline self.assert_compile( schema.CreateTable(t), "CREATE TABLE tbl (" "a INTEGER, " "b INTEGER, " "CONSTRAINT my_test_constraint CHECK (a < b) " "DEFERRABLE INITIALLY DEFERRED" ")", )
def test_money(self): """Exercise type specification for money types.""" columns = [ (mssql.MSMoney, [], {}, "MONEY"), (mssql.MSSmallMoney, [], {}, "SMALLMONEY"), ] metadata = MetaData() table_args = ["test_mssql_money", metadata] for index, spec in enumerate(columns): type_, args, kw, res = spec table_args.append( Column("c%s" % index, type_(*args, **kw), nullable=None)) money_table = Table(*table_args) dialect = mssql.dialect() gen = dialect.ddl_compiler(dialect, schema.CreateTable(money_table)) for col in money_table.c: index = int(col.name[1:]) testing.eq_( gen.get_column_specification(col), "%s %s" % (col.name, columns[index][3]), ) self.assert_(repr(col))
def test_external_ck_constraint_cancels_internal(self): t, t2 = self._constraint_create_fixture() constraint = CheckConstraint( "a < b", name="my_test_constraint", deferrable=True, initially="DEFERRED", table=t, ) schema.AddConstraint(constraint) # once we make an AddConstraint, # inline compilation of the CONSTRAINT # is disabled self.assert_compile( schema.CreateTable(t), "CREATE TABLE tbl (" "a INTEGER, " "b INTEGER" ")", )
def test_numeric(self): "Exercise type specification and options for numeric types." columns = [ # column type, args, kwargs, expected ddl (types.NUMERIC, [], {}, "NUMERIC"), (types.NUMERIC, [None], {}, "NUMERIC"), (types.NUMERIC, [12, 4], {}, "NUMERIC(12, 4)"), (types.Float, [], {}, "FLOAT"), (types.Float, [None], {}, "FLOAT"), (types.Float, [12], {}, "FLOAT(12)"), (mssql.MSReal, [], {}, "REAL"), (types.Integer, [], {}, "INTEGER"), (types.BigInteger, [], {}, "BIGINT"), (mssql.MSTinyInteger, [], {}, "TINYINT"), (types.SmallInteger, [], {}, "SMALLINT"), ] metadata = MetaData() table_args = ["test_mssql_numeric", metadata] for index, spec in enumerate(columns): type_, args, kw, res = spec table_args.append( Column("c%s" % index, type_(*args, **kw), nullable=None)) numeric_table = Table(*table_args) dialect = mssql.dialect() gen = dialect.ddl_compiler(dialect, schema.CreateTable(numeric_table)) for col in numeric_table.c: index = int(col.name[1:]) testing.eq_( gen.get_column_specification(col), "%s %s" % (col.name, columns[index][3]), ) self.assert_(repr(col))
def test_char(self): """Exercise COLLATE-ish options on string types.""" columns = [ (mssql.MSChar, [], {}, "CHAR"), (mssql.MSChar, [1], {}, "CHAR(1)"), ( mssql.MSChar, [1], { "collation": "Latin1_General_CI_AS" }, "CHAR(1) COLLATE Latin1_General_CI_AS", ), (mssql.MSNChar, [], {}, "NCHAR"), (mssql.MSNChar, [1], {}, "NCHAR(1)"), ( mssql.MSNChar, [1], { "collation": "Latin1_General_CI_AS" }, "NCHAR(1) COLLATE Latin1_General_CI_AS", ), (mssql.MSString, [], {}, "VARCHAR(max)"), (mssql.MSString, [1], {}, "VARCHAR(1)"), ( mssql.MSString, [1], { "collation": "Latin1_General_CI_AS" }, "VARCHAR(1) COLLATE Latin1_General_CI_AS", ), (mssql.MSNVarchar, [], {}, "NVARCHAR(max)"), (mssql.MSNVarchar, [1], {}, "NVARCHAR(1)"), ( mssql.MSNVarchar, [1], { "collation": "Latin1_General_CI_AS" }, "NVARCHAR(1) COLLATE Latin1_General_CI_AS", ), (mssql.MSText, [], {}, "TEXT"), ( mssql.MSText, [], { "collation": "Latin1_General_CI_AS" }, "TEXT COLLATE Latin1_General_CI_AS", ), (mssql.MSNText, [], {}, "NTEXT"), ( mssql.MSNText, [], { "collation": "Latin1_General_CI_AS" }, "NTEXT COLLATE Latin1_General_CI_AS", ), ] metadata = MetaData() table_args = ["test_mssql_charset", metadata] for index, spec in enumerate(columns): type_, args, kw, res = spec table_args.append( Column("c%s" % index, type_(*args, **kw), nullable=None)) charset_table = Table(*table_args) dialect = mssql.dialect() gen = dialect.ddl_compiler(dialect, schema.CreateTable(charset_table)) for col in charset_table.c: index = int(col.name[1:]) testing.eq_( gen.get_column_specification(col), "%s %s" % (col.name, columns[index][3]), ) self.assert_(repr(col))