def test_with(self): sub_query = (Query.from_(self.table_efg).select('fizz')) test_query = (Query.with_(sub_query, "an_alias").from_( AliasedQuery("an_alias")).select('*')) self.assertEqual( 'WITH an_alias AS (SELECT "fizz" FROM "efg") SELECT * FROM an_alias', str(test_query))
def test_select_from_with_returning(self): sub_query = PostgreSQLQuery.into( self.table_abc).insert(1).returning('*') test_query = (Query.with_(sub_query, "an_alias").from_( AliasedQuery("an_alias")).select("*")) self.assertEqual( 'WITH an_alias AS (INSERT INTO "abc" VALUES (1) RETURNING *) SELECT * FROM an_alias', str(test_query))
def test_join_with_with(self): sub_query = (Query.from_(self.table_efg).select('fizz')) test_query = (Query.with_(sub_query, "an_alias").from_( self.table_abc).join(AliasedQuery('an_alias')).on( AliasedQuery('an_alias').fizz == self.table_abc.buzz).select( '*')) self.assertEqual( 'WITH an_alias AS (SELECT "fizz" FROM "efg") ' 'SELECT * FROM "abc" JOIN an_alias ON "an_alias"."fizz"="abc"."buzz"', str(test_query))
def test_with_column_recursive(self): sub_query = (Query.from_(self.table_efg).select("fizz").union( Query.from_("an_alias").select("fizz"))) test_query = (Query.with_(sub_query, "an_alias", Field("fizz")).from_( AliasedQuery("an_alias")).select("*")) self.assertEqual( 'WITH RECURSIVE an_alias("fizz") AS ((SELECT "fizz" FROM "efg") UNION (SELECT "fizz" FROM "an_alias")) SELECT * FROM an_alias', str(test_query), )
def test_with_more_than_one(self): s1 = Query.from_(self.table_efg).select("fizz") s2 = Query.from_("a1").select("foo") a1 = AliasedQuery("a1", s1) a2 = AliasedQuery("a2", s2) test_query = (Query.with_(s1, "a1").with_( s2, "a2").from_("a1").from_("a2").select(a1.fizz, a2.foo)) self.assertEqual( 'WITH a1 AS (SELECT "fizz" FROM "efg") ,a2 AS (SELECT "foo" FROM "a1") SELECT "a1"."fizz","a2"."foo" FROM "a1","a2"', str(test_query), )
def test_update_with_statement(self): table_efg = Table('efg') sub_query = Query.from_(table_efg).select("fizz") an_alias = AliasedQuery("an_alias") q = (Query.with_( sub_query, "an_alias").update(self.table_abc).from_(an_alias).set( self.table_abc.lname, an_alias.long_name).where( self.table_abc.comp.eq(an_alias.alias_comp))) self.assertEqual( 'WITH an_alias AS (SELECT "fizz" FROM "efg") ' 'UPDATE "abc" SET "lname"="an_alias"."long_name" FROM an_alias ' 'WHERE "abc"."comp"="an_alias"."alias_comp"', str(q))