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)