def test_no_into(self):
builder = InsertBuilder() \
.add("a", Integer(), 1) \
.add("b", Integer(), 3) \
.add("c", Float(), 1.0)
self.assertRaises(AssertionError, builder.build)
def test_no_table(self):
builder = UpdateBuilder() \
.add("a", Integer(), 1) \
.add("b", Integer(), 3) \
.add("a", Float(), 1.0)
self.assertRaises(AssertionError, builder.build)
def test_no_name(self):
builder = DDLBuilder() \
.field("id", Integer()) \
.field("a", Text()) \
.field("b", Integer()) \
.field("c", Float()) \
.field("d", TimeStamp()) \
.primary_key("id")
self.assertRaises(AssertionError, builder.build)
def test_override_into(self):
builder = InsertBuilder() \
.into("B") \
.add("a", Integer(), 1) \
.add("b", Integer(), 3) \
.into("C")
self.assertEqual(builder.build(),
'INSERT INTO "C" ("a", "b") VALUES (1, 3)')
def test_override_table(self):
builder = UpdateBuilder() \
.table("B") \
.where("id", Integer(), 1) \
.add("a", Integer(), 1) \
.add("b", Integer(), 3) \
.table("C")
self.assertEqual(builder.build(),
'UPDATE "C" SET "a" = 1, "b" = 3 WHERE "id" = 1')
def test_is_empty(self):
builder = DDLBuilder()
self.assertTrue(builder.is_empty())
builder.field("a", Integer())
self.assertFalse(builder.is_empty())
builder.field("b", Integer()) \
.field("c", Float())
self.assertFalse(builder.is_empty())
def test_no_primary_key(self):
builder = DDLBuilder() \
.name("A") \
.field("id", Integer()) \
.field("a", Text()) \
.field("b", Integer()) \
.field("c", Float()) \
.field("d", TimeStamp())
self.assertEqual(
builder.build(),
'CREATE TABLE IF NOT EXISTS "A" ("id" INTEGER, "a" VARCHAR(255), "b" INTEGER, "c" NUMERIC, "d" TIMESTAMP)'
)
def test_is_empty(self):
builder = InsertBuilder()
self.assertTrue(builder.is_empty())
builder.add("a", Integer(), 1)
self.assertFalse(builder.is_empty())
builder.add("b", Integer(), 3) \
.add("c", Float(), 1.0)
self.assertFalse(builder.is_empty())
def test_override_where(self):
builder = SelectBuilder() \
.table("B") \
.where("id", Integer(), 1) \
.join("C", "id", "B", "id") \
.add("B", "a") \
.add("B", "b") \
.add("C", "c") \
.where("id", Integer(), 2)
self.assertEqual(builder.build(), (
'SELECT "B"."a", "B"."b", "C"."c" FROM "B" JOIN "C" ON ("C"."id" = "B"."id") WHERE "B"."id" = 2',
[('B', 'a'), ('B', 'b'), ('C', 'c')]))
def test_build(self):
builder = UpdateBuilder() \
.table("B") \
.where("id", Integer(), 1) \
.add("a", Text(), "The quick brown fox jumps over the lazy dog") \
.add("b", Integer(), 12) \
.add("c", Float(), 1.0) \
.add("d", TimeStamp(), datetime.datetime(2004, 10, 19, 10, 23, 54, tzinfo=datetime.timezone.utc))
self.assertRegex(
builder.build(),
r'UPDATE "B" SET "a" = \$[A-Za-z0-9]{5}\$The quick brown fox jumps over the lazy dog\$[A-Za-z0-9]{5}\$, "b" = 12, "c" = 1\.0, "d" = TIMESTAMP \'2004-10-19 10:23:54\' WHERE "id" = 1'
)
def test_empty(self):
builder = SelectBuilder() \
.table("B") \
.where("id", Integer(), 1) \
.join("C", "id", "B", "id")
self.assertRaises(AssertionError, builder.build)
def test_build(self):
builder = DDLBuilder() \
.name("A") \
.field("id", Integer()) \
.field("a", Text()) \
.field("b", Integer()) \
.field("c", Float(), False) \
.field("d", TimeStamp()) \
.primary_key("id") \
.foreign_key("b", "B", "id", on_update=DDLConstraintAction.CASCADE) \
.unique(["a","b"])
self.assertRegex(
builder.build(),
r'CREATE TABLE IF NOT EXISTS "A" \("id" INTEGER, "a" VARCHAR\(255\), "b" INTEGER, "c" NUMERIC NOT NULL, "d" TIMESTAMP, PRIMARY KEY\("id"\), CONSTRAINT fk_[A-Za-z0-9]{6} FOREIGN KEY\("b"\) REFERENCES "B"\("id"\) ON UPDATE CASCADE, CONSTRAINT uk_[A-Za-z0-9]{6} UNIQUE \("a","b"\)\)'
)
def test_ddl_filed(self):
tests = [
(DDLField("a", Integer(), True), '"a" INTEGER'),
(DDLField("a", Integer(), False), '"a" INTEGER NOT NULL'),
(DDLField("a", Text(), False), '"a" VARCHAR(255) NOT NULL'),
(DDLField("a", Text(), True), '"a" VARCHAR(255)'),
(DDLField("a", Text(max_length=10), True), '"a" VARCHAR(10)'),
(DDLField("a", Float(), True), '"a" NUMERIC'),
(DDLField("a", Float(), False), '"a" NUMERIC NOT NULL'),
(DDLField("a", TimeStamp(), True), '"a" TIMESTAMP'),
(DDLField("a", TimeStamp(), False), '"a" TIMESTAMP NOT NULL'),
(DDLField("a", TimeStamp(with_zone=True),
True), '"a" TIMESTAMP WITH TIME ZONE'),
]
for (f, r) in tests:
self.assertEqual(f.build(), r)
def test_no_table(self):
builder = SelectBuilder() \
.where("id", Integer(), 1) \
.join("C", "id", "B", "id") \
.add("B", "a") \
.add("B", "b") \
.add("C", "c")
self.assertRaises(AssertionError, builder.build)
def test_build(self):
builder = InsertBuilder() \
.into("B") \
.add("a", Text(), "The quick brown fox jumps over the lazy dog") \
.add("b", Integer(), 12) \
.add("c", Float(), 1.0) \
.add("d", TimeStamp(), datetime.datetime(2004, 10, 19, 10, 23, 54, tzinfo=datetime.timezone.utc))
self.assertRegex(
builder.build(),
r'INSERT INTO "B" \("a", "b", "c", "d"\) VALUES \(\$[A-Za-z0-9]{5}\$The quick brown fox jumps over the lazy dog\$[A-Za-z0-9]{5}\$, 12, 1\.0, TIMESTAMP \'2004-10-19 10:23:54\'\)'
)
def test_build_no_join(self):
builder = SelectBuilder() \
.table("B") \
.where("id", Integer(), 1) \
.add("B", "a") \
.add("B", "b")
self.assertEqual(
builder.build(),
('SELECT "B"."a", "B"."b" FROM "B" WHERE "B"."id" = 1', [
('B', 'a'), ('B', 'b')
]))
def test_build(self):
builder = SelectBuilder() \
.table("B") \
.where("id", Integer(), 1) \
.join("C", "id", "B", "id") \
.join("D", "id", "C", "id") \
.add("B", "a") \
.add("B", "b") \
.add("C", "c") \
.add("D", "d")
self.assertEqual(builder.build(), (
'SELECT "B"."a", "B"."b", "C"."c", "D"."d" FROM "B" JOIN "C" ON ("C"."id" = "B"."id") JOIN "D" ON ("D"."id" = "C"."id") WHERE "B"."id" = 1',
[('B', 'a'), ('B', 'b'), ('C', 'c'), ('D', 'd')]))
def test_empty(self):
builder = UpdateBuilder() \
.table("B") \
.where("id", Integer(), 1)
self.assertRaises(AssertionError, builder.build)