def test_temporal_select(self):
t = Table("abc")
with self.subTest("with system time as of"):
q = Query.from_(t.for_(
SYSTEM_TIME.as_of('2020-01-01'))).select("*")
self.assertEqual(
'SELECT * FROM "abc" FOR SYSTEM_TIME AS OF \'2020-01-01\'',
str(q))
with self.subTest("with system time between"):
q = Query.from_(
t.for_(SYSTEM_TIME.between('2020-01-01',
'2020-02-01'))).select("*")
self.assertEqual(
'SELECT * FROM "abc" FOR SYSTEM_TIME BETWEEN \'2020-01-01\' AND \'2020-02-01\'',
str(q))
with self.subTest("with system time from to"):
q = Query.from_(
t.for_(SYSTEM_TIME.from_to('2020-01-01',
'2020-02-01'))).select("*")
self.assertEqual(
'SELECT * FROM "abc" FOR SYSTEM_TIME FROM \'2020-01-01\' TO \'2020-02-01\'',
str(q))
with self.subTest("with ALL"):
q = Query.from_(t.for_(SYSTEM_TIME.all_())).select("*")
self.assertEqual('SELECT * FROM "abc" FOR SYSTEM_TIME ALL', str(q))
with self.subTest("with period between"):
q = Query.from_(
t.for_(t.valid_period.between('2020-01-01',
'2020-02-01'))).select("*")
self.assertEqual(
'SELECT * FROM "abc" FOR "valid_period" BETWEEN \'2020-01-01\' AND \'2020-02-01\'',
str(q))
with self.subTest("with period from to"):
q = Query.from_(
t.for_(t.valid_period.from_to('2020-01-01',
'2020-02-01'))).select("*")
self.assertEqual(
'SELECT * FROM "abc" FOR "valid_period" FROM \'2020-01-01\' TO \'2020-02-01\'',
str(q))
with self.subTest("with ALL"):
q = Query.from_(t.for_(t.valid_period.all_())).select("*")
self.assertEqual('SELECT * FROM "abc" FOR "valid_period" ALL',
str(q))
def test_temporal_join(self):
t0 = self.table0.for_(SYSTEM_TIME.as_of('2020-01-01'))
t1 = self.table1.for_(SYSTEM_TIME.as_of('2020-01-01'))
query = Query.from_(t0).join(t1).on(t0.foo == t1.bar).select("*")
self.assertEqual(
'SELECT * FROM "abc" FOR SYSTEM_TIME AS OF \'2020-01-01\' '
'JOIN "efg" FOR SYSTEM_TIME AS OF \'2020-01-01\' '
'ON "abc"."foo"="efg"."bar"',
str(query),
)
def test_temporal_join(self):
t0 = self.table0.for_(SYSTEM_TIME.as_of("2020-01-01"))
t1 = self.table1.for_(SYSTEM_TIME.as_of("2020-01-01"))
query = Query.from_(t0).join(t1).on(t0.foo == t1.bar).select("*")
self.assertEqual(
"SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01' "
"JOIN \"efg\" FOR SYSTEM_TIME AS OF '2020-01-01' "
'ON "abc"."foo"="efg"."bar"',
str(query),
)
def test_table_for_system_time_sql(self):
with self.subTest("with between criterion"):
table = Table("test_table").for_(
SYSTEM_TIME.between("2020-01-01", "2020-02-01"))
self.assertEqual(
"\"test_table\" FOR SYSTEM_TIME BETWEEN '2020-01-01' AND '2020-02-01'",
str(table))
with self.subTest("with as of criterion"):
table = Table("test_table").for_(SYSTEM_TIME.as_of("2020-01-01"))
self.assertEqual(
"\"test_table\" FOR SYSTEM_TIME AS OF '2020-01-01'",
str(table))
with self.subTest("with from to criterion"):
table = Table("test_table").for_(
SYSTEM_TIME.from_to("2020-01-01", "2020-02-01"))
self.assertEqual(
"\"test_table\" FOR SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'",
str(table))
def test_table_for_system_time_sql(self):
with self.subTest("with between criterion"):
table = Table("test_table").for_(
SYSTEM_TIME.between('2020-01-01', '2020-02-01'))
self.assertEqual(
'"test_table" FOR SYSTEM_TIME BETWEEN \'2020-01-01\' AND \'2020-02-01\'',
str(table))
with self.subTest("with as of criterion"):
table = Table("test_table").for_(SYSTEM_TIME.as_of('2020-01-01'))
self.assertEqual(
'"test_table" FOR SYSTEM_TIME AS OF \'2020-01-01\'',
str(table))
with self.subTest("with from to criterion"):
table = Table("test_table").for_(
SYSTEM_TIME.from_to('2020-01-01', '2020-02-01'))
self.assertEqual(
'"test_table" FOR SYSTEM_TIME FROM \'2020-01-01\' TO \'2020-02-01\'',
str(table))
def test_for_portion(self):
with self.subTest("with system time"):
q = Query.from_(self.table_abc.for_portion(SYSTEM_TIME.from_to('2020-01-01', '2020-02-01'))).delete()
self.assertEqual(
'DELETE FROM "abc" FOR PORTION OF SYSTEM_TIME FROM \'2020-01-01\' TO \'2020-02-01\'', str(q)
)
with self.subTest("with column"):
q = Query.from_(
self.table_abc.for_portion(self.table_abc.valid_period.from_to('2020-01-01', '2020-02-01'))
).delete()
self.assertEqual(
'DELETE FROM "abc" FOR PORTION OF "valid_period" FROM \'2020-01-01\' TO \'2020-02-01\'', str(q)
)
def test_for_portion(self):
with self.subTest("with system time"):
q = Query.update(self.table_abc.for_portion(SYSTEM_TIME.from_to('2020-01-01', '2020-02-01'))).set(
"foo", "bar"
)
self.assertEqual(
'UPDATE "abc" FOR PORTION OF SYSTEM_TIME FROM \'2020-01-01\' TO \'2020-02-01\' SET "foo"=\'bar\'',
str(q),
)
with self.subTest("with column"):
q = Query.update(
self.table_abc.for_portion(self.table_abc.valid_period.from_to('2020-01-01', '2020-02-01'))
).set("foo", "bar")
self.assertEqual(
'UPDATE "abc" FOR PORTION OF "valid_period" FROM \'2020-01-01\' TO \'2020-02-01\' SET "foo"=\'bar\'',
str(q),
)
def test_for_portion(self):
with self.subTest("with system time"):
q = Query.from_(
self.table_abc.for_portion(
SYSTEM_TIME.from_to("2020-01-01", "2020-02-01"))).delete()
self.assertEqual(
"DELETE FROM \"abc\" FOR PORTION OF SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'",
str(q),
)
with self.subTest("with column"):
q = Query.from_(
self.table_abc.for_portion(
self.table_abc.valid_period.from_to(
"2020-01-01", "2020-02-01"))).delete()
self.assertEqual(
"DELETE FROM \"abc\" FOR PORTION OF \"valid_period\" FROM '2020-01-01' TO '2020-02-01'",
str(q),
)
def test_for_portion(self):
with self.subTest("with system time"):
q = Query.update(
self.table_abc.for_portion(
SYSTEM_TIME.from_to("2020-01-01",
"2020-02-01"))).set("foo", "bar")
self.assertEqual(
"UPDATE \"abc\" FOR PORTION OF SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01' SET \"foo\"='bar'",
str(q),
)
with self.subTest("with column"):
q = Query.update(
self.table_abc.for_portion(
self.table_abc.valid_period.from_to(
"2020-01-01", "2020-02-01"))).set("foo", "bar")
self.assertEqual(
"UPDATE \"abc\" FOR PORTION OF \"valid_period\" FROM '2020-01-01' TO '2020-02-01' SET \"foo\"='bar'",
str(q),
)
def test_temporal_select(self):
t = Table("abc")
with self.subTest("with system time as of"):
q = Query.from_(t.for_(
SYSTEM_TIME.as_of("2020-01-01"))).select("*")
self.assertEqual(
"SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'",
str(q))
with self.subTest("with system time between"):
q = Query.from_(
t.for_(SYSTEM_TIME.between("2020-01-01",
"2020-02-01"))).select("*")
self.assertEqual(
"SELECT * FROM \"abc\" FOR SYSTEM_TIME BETWEEN '2020-01-01' AND '2020-02-01'",
str(q),
)
with self.subTest("with system time from to"):
q = Query.from_(
t.for_(SYSTEM_TIME.from_to("2020-01-01",
"2020-02-01"))).select("*")
self.assertEqual(
"SELECT * FROM \"abc\" FOR SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'",
str(q),
)
with self.subTest("with ALL"):
q = Query.from_(t.for_(SYSTEM_TIME.all_())).select("*")
self.assertEqual('SELECT * FROM "abc" FOR SYSTEM_TIME ALL', str(q))
with self.subTest("with period between"):
q = Query.from_(
t.for_(t.valid_period.between("2020-01-01",
"2020-02-01"))).select("*")
self.assertEqual(
"SELECT * FROM \"abc\" FOR \"valid_period\" BETWEEN '2020-01-01' AND '2020-02-01'",
str(q),
)
with self.subTest("with period from to"):
q = Query.from_(
t.for_(t.valid_period.from_to("2020-01-01",
"2020-02-01"))).select("*")
self.assertEqual(
"SELECT * FROM \"abc\" FOR \"valid_period\" FROM '2020-01-01' TO '2020-02-01'",
str(q),
)
with self.subTest("with ALL"):
q = Query.from_(t.for_(t.valid_period.all_())).select("*")
self.assertEqual('SELECT * FROM "abc" FOR "valid_period" ALL',
str(q))