def test_table_setter(self) -> None:
r1 = ReferenceBlueprint(
ReferenceBlueprint.MANY_TO_ONE,
name='refname',
table1='bookings',
col1='order_id',
table2='orders',
col2='order',
)
r2 = ReferenceBlueprint(
ReferenceBlueprint.MANY_TO_ONE,
name='refname',
table1='purchases',
col1='order_id',
table2='orders',
col2='order',
)
c = Column(name='order',
type_='integer',
default=0,
ref_blueprints=[r1, r2])
t = Table('orders')
c.table = t
self.assertEqual(c.table, t)
self.assertEqual(c.ref_blueprints[0].table1, t.name)
self.assertEqual(c.ref_blueprints[1].table1, t.name)
def test_pk(self) -> None:
t = Table('products')
t.add_column(Column('id', 'integer'))
t.add_column(Column('name', 'varchar'))
r = Index(subject_names=['id', 'name'], table=t, pk=True)
t.add_index(r)
expected = 'PRIMARY KEY ("id", "name")'
self.assertEqual(r.sql, expected)
def test_add_column(self) -> None:
t = Table('products')
c1 = Column('id', 'integer')
c2 = Column('name', 'varchar2')
t.add_column(c1)
t.add_column(c2)
self.assertEqual(c1.table, t)
self.assertEqual(c2.table, t)
self.assertEqual(t.columns, [c1, c2])
def test_unique_type_composite(self) -> None:
t = Table('products')
t.add_column(Column('id', 'integer'))
t.add_column(Column('name', 'varchar'))
r = Index(subject_names=['id', 'name'],
table=t,
type_='hash',
unique=True)
t.add_index(r)
expected = 'CREATE UNIQUE INDEX ON "products" USING HASH ("id", "name");'
self.assertEqual(r.sql, expected)
def test_add_index(self) -> None:
t = Table('products')
c1 = Column('id', 'integer')
c2 = Column('name', 'varchar2')
i1 = Index(['id'])
i2 = Index(['name'])
t.add_column(c1)
t.add_column(c2)
t.add_index(i1)
t.add_index(i2)
self.assertEqual(i1.table, t)
self.assertEqual(i2.table, t)
self.assertEqual(t.indexes, [i1, i2])
def test_duplicate_ref(self) -> None:
t = Table('products')
c1 = Column('id', 'integer')
c2 = Column('name', 'varchar2')
t.add_column(c1)
t.add_column(c2)
t2 = Table('names')
c21 = Column('name_val', 'varchar2')
t2.add_column(c21)
r1 = TableReference(c2, t2, c21)
t.add_ref(r1)
r2 = TableReference(c2, t2, c21)
self.assertEqual(r1, r2)
with self.assertRaises(DuplicateReferenceError):
t.add_ref(r2)
def test_basic_sql(self) -> None:
t = Table('products')
t.add_column(Column('id', 'integer'))
r = Index(subject_names=['id'], table=t)
t.add_index(r)
expected = 'CREATE INDEX ON "products" ("id");'
self.assertEqual(r.sql, expected)
def parse_column(s, l, t):
'''
address varchar(255) [unique, not null, note: 'to include unit number']
'''
init_dict = {
'name': t['name'],
'type_': t['type'],
}
# deprecated
for constraint in t.get('constraints', []):
if constraint == 'pk':
init_dict['pk'] = True
elif constraint == 'unique':
init_dict['unique'] = True
if 'settings' in t:
init_dict.update(t['settings'])
# comments after column definition have priority
if 'comment' in t:
init_dict['comment'] = t['comment'][0]
if 'comment' not in init_dict and 'comment_before' in t:
comment = '\n'.join(c[0] for c in t['comment_before'])
init_dict['comment'] = comment
return Column(**init_dict)
def test_add_bad_index(self) -> None:
t = Table('products')
c = Column('id', 'integer')
i = Index(['id', 'name'])
t.add_column(c)
with self.assertRaises(ColumnNotFoundError):
t.add_index(i)
def test_note(self) -> None:
n = Note('Table note')
t = Table('products', note=n)
c = Column('id', 'integer')
t.add_column(c)
expected = 'CREATE TABLE "products" (\n -- Table note\n "id" integer\n);\n'
self.assertEqual(t.sql, expected)
def test_index_inline(self) -> None:
t = Table('products')
c1 = Column('id', 'integer')
c2 = Column('name', 'varchar2')
t.add_column(c1)
t.add_column(c2)
i = Index(['id', 'name'], pk=True)
t.add_index(i)
expected = \
'''CREATE TABLE "products" (
"id" integer,
"name" varchar2,
PRIMARY KEY ("id", "name")
);
'''
self.assertEqual(t.sql, expected)
def test_composite_with_expression(self) -> None:
t = Table('products')
t.add_column(Column('id', 'integer'))
r = Index(subject_names=['id', '(id*3)'], table=t)
t.add_index(r)
self.assertEqual(r.subjects, [t['id'], '(id*3)'])
expected = 'CREATE INDEX ON "products" ("id", (id*3));'
self.assertEqual(r.sql, expected)
def test_note(self) -> None:
t = Table('products')
t.add_column(Column('id', 'integer'))
n = Note('Index note')
r = Index(subject_names=['id'], table=t, note=n)
t.add_index(r)
expected = 'CREATE INDEX ON "products" ("id"); -- Index note'
self.assertEqual(r.sql, expected)
def test_ref(self) -> None:
t = Table('products')
c1 = Column('id', 'integer')
c2 = Column('name', 'varchar2')
t.add_column(c1)
t.add_column(c2)
t2 = Table('names')
c21 = Column('name_val', 'varchar2')
t2.add_column(c21)
r = TableReference(c2, t2, c21)
t.add_ref(r)
expected = \
'''CREATE TABLE "products" (
"id" integer,
"name" varchar2,
FOREIGN KEY ("name") REFERENCES "names ("name_val")
);
'''
self.assertEqual(t.sql, expected)
def test_ref_index(self):
t = Table('products')
c1 = Column('id', 'integer')
c2 = Column('name', 'varchar2')
t.add_column(c1)
t.add_column(c2)
t2 = Table('names')
c21 = Column('name_val', 'varchar2')
t2.add_column(c21)
r = TableReference(c2, t2, c21)
t.refs.append(r)
i = Index(['id', 'name'])
t.add_index(i)
expected = \
'''CREATE TABLE "products" (
"id" integer,
"name" varchar2,
FOREIGN KEY ("name") REFERENCES "names ("name_val")
);
CREATE INDEX ON "products" ("id", "name");
'''
self.assertEqual(t.sql, expected)
def test_note(self) -> None:
n = Note('Column note')
r = Column(name='id', type_='integer', note=n)
expected = '"id" integer -- Column note'
self.assertEqual(r.sql, expected)
def test_pk_autoinc(self) -> None:
r = Column(name='id', type_='integer', pk=True, autoinc=True)
expected = '"id" integer PRIMARY KEY AUTOINCREMENT'
self.assertEqual(r.sql, expected)
def test_unique_not_null(self) -> None:
r = Column(name='id', type_='integer', unique=True, not_null=True)
expected = '"id" integer UNIQUE NOT NULL'
self.assertEqual(r.sql, expected)
def test_one_column(self) -> None:
t = Table('products')
c = Column('id', 'integer')
t.add_column(c)
expected = 'CREATE TABLE "products" (\n "id" integer\n);\n'
self.assertEqual(t.sql, expected)
def test_basic_sql(self) -> None:
r = Column(name='id', type_='integer')
expected = '"id" integer'
self.assertEqual(r.sql, expected)
def test_default(self) -> None:
r = Column(name='order', type_='integer', default=0)
expected = '"order" integer DEFAULT 0'
self.assertEqual(r.sql, expected)