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 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 parse_table(s, l, t):
'''
Table bookings as bb [headercolor: #cccccc] {
id integer
country varchar [NOT NULL, ref: > countries.country_name]
booking_date date unique pk
created_at timestamp
indexes {
(id, country) [pk] // composite primary key
}
}
'''
init_dict = {
'name': t['name'],
}
if 'settings' in t:
init_dict.update(t['settings'])
if 'alias' in t:
init_dict['alias'] = t['alias'][0]
if 'note' in t:
# will override one from settings
init_dict['note'] = t['note'][0]
if 'comment_before' in t:
comment = '\n'.join(c[0] for c in t['comment_before'])
init_dict['comment'] = comment
result = Table(**init_dict)
for column in t['columns']:
result.add_column(column)
for index_ in t.get('indexes', []):
result.add_index(index_)
return result
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_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_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_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_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_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_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)
