1
from test.lib.testing import assert_raises, assert_raises_message
2
from sqlalchemy import *
1
from sqlalchemy.testing import assert_raises, assert_raises_message
2
from sqlalchemy import Table, Integer, String, Column, PrimaryKeyConstraint,\
3
ForeignKeyConstraint, ForeignKey, UniqueConstraint, Index, MetaData, \
3
5
from sqlalchemy import exc, schema
5
from test.lib import config, engines
6
from sqlalchemy.engine import ddl
7
from test.lib.testing import eq_
8
from test.lib.assertsql import AllOf, RegexSQL, ExactSQL, CompiledSQL
9
from sqlalchemy.dialects.postgresql import base as postgresql
6
from sqlalchemy.testing import fixtures, AssertsExecutionResults, \
8
from sqlalchemy import testing
9
from sqlalchemy.engine import default
10
from sqlalchemy.testing import engines
11
from sqlalchemy.testing import eq_
12
from sqlalchemy.testing.assertsql import AllOf, RegexSQL, ExactSQL, CompiledSQL
13
from sqlalchemy.sql import table, column
11
class ConstraintTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
15
class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults):
12
16
__dialect__ = 'default'
16
metadata = MetaData(testing.db)
21
def test_constraint(self):
22
employees = Table('employees', metadata,
18
@testing.provide_metadata
19
def test_pk_fk_constraint_create(self):
20
metadata = self.metadata
22
Table('employees', metadata,
23
23
Column('id', Integer),
24
24
Column('soc', String(40)),
25
25
Column('name', String(30)),
26
26
PrimaryKeyConstraint('id', 'soc')
28
elements = Table('elements', metadata,
28
Table('elements', metadata,
29
29
Column('id', Integer),
30
30
Column('stuff', String(30)),
31
31
Column('emp_id', Integer),
32
32
Column('emp_soc', String(40)),
33
33
PrimaryKeyConstraint('id', name='elements_primkey'),
34
ForeignKeyConstraint(['emp_id', 'emp_soc'], ['employees.id', 'employees.soc'])
38
def test_double_fk_usage_raises(self):
39
f = ForeignKey('b.id')
41
Column('x', Integer, f)
42
assert_raises(exc.InvalidRequestError, Column, "y", Integer, f)
44
def test_circular_constraint(self):
45
a = Table("a", metadata,
34
ForeignKeyConstraint(['emp_id', 'emp_soc'],
35
['employees.id', 'employees.soc'])
37
self.assert_sql_execution(
39
lambda: metadata.create_all(checkfirst=False),
40
CompiledSQL('CREATE TABLE employees ('
44
'PRIMARY KEY (id, soc)'
47
CompiledSQL('CREATE TABLE elements ('
51
'emp_soc VARCHAR(40), '
52
'CONSTRAINT elements_primkey PRIMARY KEY (id), '
53
'FOREIGN KEY(emp_id, emp_soc) '
54
'REFERENCES employees (id, soc)'
60
@testing.provide_metadata
61
def test_cyclic_fk_table_constraint_create(self):
62
metadata = self.metadata
46
65
Column('id', Integer, primary_key=True),
47
66
Column('bid', Integer),
48
ForeignKeyConstraint(["bid"], ["b.id"], name="afk")
67
ForeignKeyConstraint(["bid"], ["b.id"])
50
b = Table("b", metadata,
51
70
Column('id', Integer, primary_key=True),
52
71
Column("aid", Integer),
53
72
ForeignKeyConstraint(["aid"], ["a.id"], use_alter=True, name="bfk")
57
def test_circular_constraint_2(self):
58
a = Table("a", metadata,
74
self._assert_cyclic_constraint(metadata)
76
@testing.provide_metadata
77
def test_cyclic_fk_column_constraint_create(self):
78
metadata = self.metadata
59
81
Column('id', Integer, primary_key=True),
60
82
Column('bid', Integer, ForeignKey("b.id")),
62
b = Table("b", metadata,
63
85
Column('id', Integer, primary_key=True),
64
Column("aid", Integer, ForeignKey("a.id", use_alter=True, name="bfk")),
68
@testing.fails_on('mysql', 'FIXME: unknown')
69
def test_check_constraint(self):
70
foo = Table('foo', metadata,
86
Column("aid", Integer,
87
ForeignKey("a.id", use_alter=True, name="bfk")
90
self._assert_cyclic_constraint(metadata)
92
def _assert_cyclic_constraint(self, metadata):
94
CompiledSQL('CREATE TABLE b ('
95
'id INTEGER NOT NULL, '
100
CompiledSQL('CREATE TABLE a ('
101
'id INTEGER NOT NULL, '
104
'FOREIGN KEY(bid) REFERENCES b (id)'
108
if testing.db.dialect.supports_alter:
110
CompiledSQL('ALTER TABLE b ADD CONSTRAINT bfk '
111
'FOREIGN KEY(aid) REFERENCES a (id)')
113
self.assert_sql_execution(
115
lambda: metadata.create_all(checkfirst=False),
120
if testing.db.dialect.supports_alter:
121
assertions.append(CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk'))
123
CompiledSQL("DROP TABLE a"),
124
CompiledSQL("DROP TABLE b"),
126
self.assert_sql_execution(
128
lambda: metadata.drop_all(checkfirst=False),
132
@testing.provide_metadata
133
def test_check_constraint_create(self):
134
metadata = self.metadata
136
Table('foo', metadata,
71
137
Column('id', Integer, primary_key=True),
72
138
Column('x', Integer),
73
139
Column('y', Integer),
74
140
CheckConstraint('x>y'))
75
bar = Table('bar', metadata,
141
Table('bar', metadata,
76
142
Column('id', Integer, primary_key=True),
77
143
Column('x', Integer, CheckConstraint('x>7')),
78
144
Column('z', Integer)
82
foo.insert().execute(id=1,x=9,y=5)
83
assert_raises(exc.DBAPIError, foo.insert().execute, id=2,x=5,y=9)
84
bar.insert().execute(id=1,x=10)
85
assert_raises(exc.DBAPIError, bar.insert().execute, id=2,x=5)
87
def test_unique_constraint(self):
88
foo = Table('foo', metadata,
147
self.assert_sql_execution(
149
lambda: metadata.create_all(checkfirst=False),
151
CompiledSQL('CREATE TABLE foo ('
152
'id INTEGER NOT NULL, '
159
CompiledSQL('CREATE TABLE bar ('
160
'id INTEGER NOT NULL, '
161
'x INTEGER CHECK (x>7), '
169
@testing.provide_metadata
170
def test_unique_constraint_create(self):
171
metadata = self.metadata
173
Table('foo', metadata,
89
174
Column('id', Integer, primary_key=True),
90
175
Column('value', String(30), unique=True))
91
bar = Table('bar', metadata,
176
Table('bar', metadata,
92
177
Column('id', Integer, primary_key=True),
93
178
Column('value', String(30)),
94
179
Column('value2', String(30)),
95
180
UniqueConstraint('value', 'value2', name='uix1')
98
foo.insert().execute(id=1, value='value1')
99
foo.insert().execute(id=2, value='value2')
100
bar.insert().execute(id=1, value='a', value2='a')
101
bar.insert().execute(id=2, value='a', value2='b')
102
assert_raises(exc.DBAPIError, foo.insert().execute, id=3, value='value1')
103
assert_raises(exc.DBAPIError, bar.insert().execute, id=3, value='a', value2='b')
183
self.assert_sql_execution(
185
lambda: metadata.create_all(checkfirst=False),
187
CompiledSQL('CREATE TABLE foo ('
188
'id INTEGER NOT NULL, '
189
'value VARCHAR(30), '
193
CompiledSQL('CREATE TABLE bar ('
194
'id INTEGER NOT NULL, '
195
'value VARCHAR(30), '
196
'value2 VARCHAR(30), '
198
'CONSTRAINT uix1 UNIQUE (value, value2)'
203
@testing.provide_metadata
105
204
def test_index_create(self):
205
metadata = self.metadata
106
207
employees = Table('employees', metadata,
107
208
Column('id', Integer, primary_key=True),
108
209
Column('first_name', String(30)),
109
210
Column('last_name', String(30)),
110
211
Column('email_address', String(30)))
113
213
i = Index('employee_name_index',
114
214
employees.c.last_name, employees.c.first_name)
116
215
assert i in employees.indexes
118
217
i2 = Index('employee_email_index',
119
218
employees.c.email_address, unique=True)
121
219
assert i2 in employees.indexes
221
self.assert_sql_execution(
223
lambda: metadata.create_all(checkfirst=False),
224
RegexSQL("^CREATE TABLE"),
226
CompiledSQL('CREATE INDEX employee_name_index ON '
227
'employees (last_name, first_name)', []),
228
CompiledSQL('CREATE UNIQUE INDEX employee_email_index ON '
229
'employees (email_address)', [])
233
@testing.provide_metadata
123
234
def test_index_create_camelcase(self):
124
235
"""test that mixed-case index identifiers are legal"""
237
metadata = self.metadata
126
239
employees = Table('companyEmployees', metadata,
127
240
Column('id', Integer, primary_key=True),
128
241
Column('firstName', String(30)),
129
242
Column('lastName', String(30)),
130
243
Column('emailAddress', String(30)))
134
i = Index('employeeNameIndex',
247
Index('employeeNameIndex',
135
248
employees.c.lastName, employees.c.firstName)
138
i = Index('employeeEmailIndex',
250
Index('employeeEmailIndex',
139
251
employees.c.emailAddress, unique=True)
142
# Check that the table is useable. This is mostly for pg,
143
# which can be somewhat sticky with mixed-case identifiers
144
employees.insert().execute(firstName='Joe', lastName='Smith', id=0)
145
ss = employees.select().execute().fetchall()
146
assert ss[0].firstName == 'Joe'
147
assert ss[0].lastName == 'Smith'
253
self.assert_sql_execution(
255
lambda: metadata.create_all(checkfirst=False),
256
RegexSQL("^CREATE TABLE"),
258
CompiledSQL('CREATE INDEX "employeeNameIndex" ON '
259
'"companyEmployees" ("lastName", "firstName")', []),
260
CompiledSQL('CREATE UNIQUE INDEX "employeeEmailIndex" ON '
261
'"companyEmployees" ("emailAddress")', [])
265
@testing.provide_metadata
149
266
def test_index_create_inline(self):
150
"""Test indexes defined with tables"""
267
# test an index create using index=True, unique=True
269
metadata = self.metadata
152
271
events = Table('events', metadata,
153
272
Column('id', Integer, primary_key=True),
170
291
lambda: events.create(testing.db),
171
292
RegexSQL("^CREATE TABLE events"),
173
ExactSQL('CREATE UNIQUE INDEX ix_events_name ON events (name)'),
174
ExactSQL('CREATE INDEX ix_events_location ON events (location)'),
175
ExactSQL('CREATE UNIQUE INDEX sport_announcer ON events (sport, announcer)'),
294
ExactSQL('CREATE UNIQUE INDEX ix_events_name ON events '
296
ExactSQL('CREATE INDEX ix_events_location ON events '
298
ExactSQL('CREATE UNIQUE INDEX sport_announcer ON events '
299
'(sport, announcer)'),
176
300
ExactSQL('CREATE INDEX idx_winners ON events (winner)')
180
# verify that the table is functional
181
events.insert().execute(id=1, name='hockey finals', location='rink',
182
sport='hockey', announcer='some canadian',
184
ss = events.select().execute().fetchall()
304
@testing.provide_metadata
305
def test_index_functional_create(self):
306
metadata = self.metadata
308
t = Table('sometable', metadata,
309
Column('id', Integer, primary_key=True),
310
Column('data', String(50))
312
Index('myindex', t.c.data.desc())
313
self.assert_sql_execution(
315
lambda: t.create(testing.db),
316
CompiledSQL('CREATE TABLE sometable (id INTEGER NOT NULL, '
317
'data VARCHAR(50), PRIMARY KEY (id))'),
318
ExactSQL('CREATE INDEX myindex ON sometable (data DESC)')
321
class ConstraintCompilationTest(fixtures.TestBase, AssertsCompiledSQL):
322
__dialect__ = 'default'
324
def test_create_plain(self):
325
t = Table('t', MetaData(), Column('x', Integer))
326
i = Index("xyz", t.c.x)
328
schema.CreateIndex(i),
329
"CREATE INDEX xyz ON t (x)"
332
def test_drop_plain_unattached(self):
334
schema.DropIndex(Index(name="xyz")),
338
def test_drop_plain(self):
340
schema.DropIndex(Index(name="xyz")),
344
def test_create_schema(self):
345
t = Table('t', MetaData(), Column('x', Integer), schema="foo")
346
i = Index("xyz", t.c.x)
348
schema.CreateIndex(i),
349
"CREATE INDEX xyz ON foo.t (x)"
352
def test_drop_schema(self):
353
t = Table('t', MetaData(), Column('x', Integer), schema="foo")
354
i = Index("xyz", t.c.x)
186
361
def test_too_long_idx_name(self):
187
362
dialect = testing.db.dialect.__class__()
230
425
"CREATE INDEX foo ON t1 (x, y)"
233
def test_index_asserts_cols_standalone(self):
234
t1 = Table('t1', metadata,
237
t2 = Table('t2', metadata,
240
assert_raises_message(
242
"Column 't2.y' is not part of table 't1'.",
244
"bar", t1.c.x, t2.c.y
247
def test_index_asserts_cols_inline(self):
248
t1 = Table('t1', metadata,
251
assert_raises_message(
253
"Index 'bar' is against table 't1', and "
254
"cannot be associated with table 't2'.",
255
Table, 't2', metadata,
256
Column('y', Integer),
260
def test_raise_index_nonexistent_name(self):
262
# the KeyError isn't ideal here, a nicer message
266
Table, 't', m, Column('x', Integer), Index("foo", "q")
269
def test_raise_not_a_column(self):
275
def test_no_warning_w_no_columns(self):
278
def test_raise_clauseelement_not_a_column(self):
280
t2 = Table('t2', m, Column('x', Integer))
281
class SomeClass(object):
282
def __clause_element__(self):
286
Index, "foo", SomeClass()
289
def test_create_plain(self):
290
t = Table('t', MetaData(), Column('x', Integer))
291
i = Index("xyz", t.c.x)
293
schema.CreateIndex(i),
294
"CREATE INDEX xyz ON t (x)"
297
def test_drop_plain_unattached(self):
299
schema.DropIndex(Index(name="xyz")),
303
def test_drop_plain(self):
304
t = Table('t', MetaData(), Column('x', Integer))
305
i = Index("xyz", t.c.x)
307
schema.DropIndex(Index(name="xyz")),
311
def test_create_schema(self):
312
t = Table('t', MetaData(), Column('x', Integer), schema="foo")
313
i = Index("xyz", t.c.x)
315
schema.CreateIndex(i),
316
"CREATE INDEX xyz ON foo.t (x)"
319
def test_drop_schema(self):
320
t = Table('t', MetaData(), Column('x', Integer), schema="foo")
321
i = Index("xyz", t.c.x)
327
class ConstraintCompilationTest(fixtures.TestBase, AssertsCompiledSQL):
328
__dialect__ = 'default'
330
428
def _test_deferrable(self, constraint_factory):
429
dialect = default.DefaultDialect()
331
431
t = Table('tbl', MetaData(),
332
432
Column('a', Integer),
333
433
Column('b', Integer),
334
434
constraint_factory(deferrable=True))
336
sql = str(schema.CreateTable(t).compile(bind=testing.db))
436
sql = str(schema.CreateTable(t).compile(dialect=dialect))
337
437
assert 'DEFERRABLE' in sql, sql
338
438
assert 'NOT DEFERRABLE' not in sql, sql
552
726
"ALTER TABLE tbl ADD PRIMARY KEY (a)"
729
class ConstraintAPITest(fixtures.TestBase):
730
def test_double_fk_usage_raises(self):
731
f = ForeignKey('b.id')
733
Column('x', Integer, f)
734
assert_raises(exc.InvalidRequestError, Column, "y", Integer, f)
736
def test_auto_append_constraint(self):
740
Column('a', Integer),
745
Column('a', Integer),
750
UniqueConstraint(t.c.a),
751
CheckConstraint(t.c.a > 5),
752
ForeignKeyConstraint([t.c.a], [t2.c.a]),
753
PrimaryKeyConstraint(t.c.a)
755
assert c in t.constraints
756
t.append_constraint(c)
757
assert c in t.constraints
759
c = Index('foo', t.c.a)
760
assert c in t.indexes
762
def test_auto_append_lowercase_table(self):
763
t = table('t', column('a'))
764
t2 = table('t2', column('a'))
766
UniqueConstraint(t.c.a),
767
CheckConstraint(t.c.a > 5),
768
ForeignKeyConstraint([t.c.a], [t2.c.a]),
769
PrimaryKeyConstraint(t.c.a),
774
def test_tometadata_ok(self):
778
Column('a', Integer),
783
Column('a', Integer),
787
UniqueConstraint(t.c.a)
788
CheckConstraint(t.c.a > 5)
789
ForeignKeyConstraint([t.c.a], [t2.c.a])
790
PrimaryKeyConstraint(t.c.a)
794
t3 = t.tometadata(m2)
796
eq_(len(t3.constraints), 4)
798
for c in t3.constraints:
801
def test_check_constraint_copy(self):
804
Column('a', Integer),
807
ck = CheckConstraint(t.c.a > 5)
809
assert ck in t.constraints
810
assert ck2 not in t.constraints
813
def test_ambig_check_constraint_auto_append(self):
817
Column('a', Integer),
822
Column('a', Integer),
825
c = CheckConstraint(t.c.a > t2.c.b)
826
assert c not in t.constraints
827
assert c not in t2.constraints
829
def test_index_asserts_cols_standalone(self):
830
metadata = MetaData()
832
t1 = Table('t1', metadata,
835
t2 = Table('t2', metadata,
838
assert_raises_message(
840
"Column 't2.y' is not part of table 't1'.",
842
"bar", t1.c.x, t2.c.y
845
def test_index_asserts_cols_inline(self):
846
metadata = MetaData()
848
t1 = Table('t1', metadata,
851
assert_raises_message(
853
"Index 'bar' is against table 't1', and "
854
"cannot be associated with table 't2'.",
855
Table, 't2', metadata,
856
Column('y', Integer),
860
def test_raise_index_nonexistent_name(self):
862
# the KeyError isn't ideal here, a nicer message
866
Table, 't', m, Column('x', Integer), Index("foo", "q")
869
def test_raise_not_a_column(self):
875
def test_raise_expr_no_column(self):
876
idx = Index('foo', func.lower(5))
878
assert_raises_message(
880
"Index 'foo' is not associated with any table.",
881
schema.CreateIndex(idx).compile, dialect=testing.db.dialect
883
assert_raises_message(
885
"Index 'foo' is not associated with any table.",
886
schema.CreateIndex(idx).compile
890
def test_no_warning_w_no_columns(self):
891
# I think the test here is, there is no warning.
892
# people want to create empty indexes for the purpose of
894
idx = Index(name="foo")
896
assert_raises_message(
898
"Index 'foo' is not associated with any table.",
899
schema.CreateIndex(idx).compile, dialect=testing.db.dialect
901
assert_raises_message(
903
"Index 'foo' is not associated with any table.",
904
schema.CreateIndex(idx).compile
907
def test_raise_clauseelement_not_a_column(self):
909
t2 = Table('t2', m, Column('x', Integer))
910
class SomeClass(object):
911
def __clause_element__(self):
915
Index, "foo", SomeClass()