def setup_class(cls):
global metadata, cattable, matchtable
metadata = MetaData(testing.db)
cattable = Table(
"cattable",
metadata,
Column("id", Integer),
Column("description", String(50)),
PrimaryKeyConstraint("id", name="PK_cattable"),
)
matchtable = Table(
"matchtable",
metadata,
Column("id", Integer),
Column("title", String(200)),
Column("category_id", Integer, ForeignKey("cattable.id")),
PrimaryKeyConstraint("id", name="PK_matchtable"),
)
DDL(
"""CREATE FULLTEXT INDEX
ON cattable (description)
KEY INDEX PK_cattable"""
).execute_at("after-create", matchtable)
DDL(
"""CREATE FULLTEXT INDEX
ON matchtable (title)
KEY INDEX PK_matchtable"""
).execute_at("after-create", matchtable)
metadata.create_all()
cattable.insert().execute(
[
{"id": 1, "description": "Python"},
{"id": 2, "description": "Ruby"},
]
)
matchtable.insert().execute(
[
{
"id": 1,
"title": "Web Development with Rails",
"category_id": 2,
},
{"id": 2, "title": "Dive Into Python", "category_id": 1},
{
"id": 3,
"title": "Programming Matz's Ruby",
"category_id": 2,
},
{"id": 4, "title": "Guide to Django", "category_id": 1},
{"id": 5, "title": "Python in a Nutshell", "category_id": 1},
]
)
DDL("WAITFOR DELAY '00:00:05'").execute(bind=engines.testing_engine())
def test_cross_schema_fk_pk_name_overlaps(self):
# test for issue #4228
metadata = self.metadata
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()
insp = inspect(testing.db)
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"],
}],
)
def test_render_add_pk_constraint(self):
t, t2 = self._constraint_create_fixture()
assert t.c.a.primary_key is False
constraint = PrimaryKeyConstraint(t.c.a)
assert t.c.a.primary_key is True
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD PRIMARY KEY (a)",
)
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 test_pk_fk_constraint_create(self):
metadata = self.metadata
Table(
"employees",
metadata,
Column("id", Integer),
Column("soc", String(40)),
Column("name", String(30)),
PrimaryKeyConstraint("id", "soc"),
)
Table(
"elements",
metadata,
Column("id", Integer),
Column("stuff", String(30)),
Column("emp_id", Integer),
Column("emp_soc", String(40)),
PrimaryKeyConstraint("id", name="elements_primkey"),
ForeignKeyConstraint(["emp_id", "emp_soc"],
["employees.id", "employees.soc"]),
)
self.assert_sql_execution(
testing.db,
lambda: metadata.create_all(checkfirst=False),
CompiledSQL("CREATE TABLE employees ("
"id INTEGER NOT NULL, "
"soc VARCHAR(40) NOT NULL, "
"name VARCHAR(30), "
"PRIMARY KEY (id, soc)"
")"),
CompiledSQL("CREATE TABLE elements ("
"id INTEGER NOT NULL, "
"stuff VARCHAR(30), "
"emp_id INTEGER, "
"emp_soc VARCHAR(40), "
"CONSTRAINT elements_primkey PRIMARY KEY (id), "
"FOREIGN KEY(emp_id, emp_soc) "
"REFERENCES employees (id, soc)"
")"),
)
def test_empty_pkc(self):
# test that an empty primary key is ignored
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("x", Integer, autoincrement=False),
Column("y", Integer, autoincrement=False),
PrimaryKeyConstraint(),
)
self.assert_compile(schema.CreateTable(tbl),
"CREATE TABLE test (x INTEGER, y INTEGER)")
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_table_pkc_explicit_nonclustered(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("x", Integer, autoincrement=False),
Column("y", Integer, autoincrement=False),
PrimaryKeyConstraint("x", "y", mssql_clustered=False),
)
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NOT NULL, "
"PRIMARY KEY NONCLUSTERED (x, y))",
)
def test_table_uc_clustering(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("x", Integer, autoincrement=False),
Column("y", Integer, autoincrement=False),
PrimaryKeyConstraint("x"),
UniqueConstraint("y", mssql_clustered=True),
)
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NULL, "
"PRIMARY KEY (x), UNIQUE CLUSTERED (y))",
)
def test_include_indexes_resembling_pk(self, explicit_pk):
metadata = self.metadata
t = Table(
"sometable",
metadata,
Column("id_a", Unicode(255), primary_key=True),
Column("id_b", Unicode(255), primary_key=True),
Column("group", Unicode(255), primary_key=True),
Column("col", Unicode(255)),
# Oracle won't let you do this unless the indexes have
# the columns in different order
Index("pk_idx_1", "id_b", "id_a", "group", unique=True),
Index("pk_idx_2", "id_b", "group", "id_a", unique=True),
)
if explicit_pk:
t.append_constraint(
PrimaryKeyConstraint("id_a",
"id_b",
"group",
name="some_primary_key"))
metadata.create_all()
insp = inspect(testing.db)
eq_(
insp.get_indexes("sometable"),
[
{
"name": "pk_idx_1",
"column_names": ["id_b", "id_a", "group"],
"dialect_options": {},
"unique": True,
},
{
"name": "pk_idx_2",
"column_names": ["id_b", "group", "id_a"],
"dialect_options": {},
"unique": True,
},
],
)
def factory(**kw):
return PrimaryKeyConstraint("a", **kw)