Repository: kayak/pypika
Pull request #491 (2020-09-26 12:13:29)

Feature/temporal query support
I've added support for temporal table querying. I built this on top of my previous pull request BTW :(

I'm not sure how close this is to how you would like it done.

There are a bunch of examples in the README/tutorial near the bottom. The approach I've taken is to add the criteria to the `Table`. Here's an example:

```python
t = Table("abc")
q = Query.from_(t.for_(SYSTEM_TIME.as_of('2020-01-01'))).select("*")
```

This produces:

```sql
'SELECT * FROM "abc" FOR SYSTEM_TIME AS OF '2020-01-01'
```

There is support for all the valid operations including `UPDATE` and `DELETE`.

A side affect of applying the criteria to the `Table` is that joins are also supported.

Rob


Patch:
diff --git a/README.rst b/README.rst
index 6b35e4b..3a624b2 100644
--- a/README.rst
+++ b/README.rst
@@ -1073,6 +1073,150 @@ MSSQL:
 
 You can find out what parameter style is needed for DBAPI compliant drivers here: https://www.python.org/dev/peps/pep-0249/#paramstyle or in the DB driver documentation.
 
+Temporal support
+^^^^^^^^^^^^^^^^
+
+Temporal criteria can be added to the tables.
+
+Select
+""""""
+
+Here is a select using system time.
+
+.. code-block:: python
+
+    t = Table("abc")
+    q = Query.from_(t.for_(SYSTEM_TIME.as_of('2020-01-01'))).select("*")
+
+This produces:
+
+.. code-block:: sql
+
+    SELECT * FROM "abc" FOR SYSTEM_TIME AS OF '2020-01-01'
+
+You can also use between.
+
+.. code-block:: python
+
+    t = Table("abc")
+    q = Query.from_(
+        t.for_(SYSTEM_TIME.between('2020-01-01', '2020-02-01'))
+    ).select("*")
+
+This produces:
+
+.. code-block:: sql
+
+    SELECT * FROM "abc" FOR SYSTEM_TIME BETWEEN '2020-01-01' AND '2020-02-01'
+
+You can also use a period range.
+
+.. code-block:: python
+
+    t = Table("abc")
+    q = Query.from_(
+        t.for_(SYSTEM_TIME.from_to('2020-01-01', '2020-02-01'))
+    ).select("*")
+
+This produces:
+
+.. code-block:: sql
+
+    SELECT * FROM "abc" FOR SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'
+
+Finally you can select for all times:
+
+.. code-block:: python
+
+    t = Table("abc")
+    q = Query.from_(t.for_(SYSTEM_TIME.all_())).select("*")
+
+This produces:
+
+.. code-block:: sql
+
+    SELECT * FROM "abc" FOR SYSTEM_TIME ALL
+
+A user defined period can also be used in the following manner.
+
+.. code-block:: python
+
+    t = Table("abc")
+    q = Query.from_(
+        t.for_(t.valid_period.between('2020-01-01', '2020-02-01'))
+    ).select("*")
+
+This produces:
+
+.. code-block:: sql
+
+    SELECT * FROM "abc" FOR "valid_period" BETWEEN '2020-01-01' AND '2020-02-01'
+
+Joins
+"""""
+
+With joins, when the table object is used when specifying columns, it is
+important to use the table from which the temporal constraint was generated.
+This is because `Table("abc")` is not the same table as `Table("abc").for_(...)`.
+The following example demonstrates this.
+
+.. code-block:: python
+
+    t0 = Table("abc").for_(SYSTEM_TIME.as_of('2020-01-01'))
+    t1 = Table("efg").for_(SYSTEM_TIME.as_of('2020-01-01'))
+    query = (
+        Query.from_(t0)
+        .join(t1)
+        .on(t0.foo == t1.bar)
+        .select("*")
+    )
+
+This produces:
+
+.. code-block:: sql
+
+    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"
+
+Update & Deletes
+""""""""""""""""
+
+An update can be written as follows:
+
+.. code-block:: python
+
+    t = Table("abc")
+    q = Query.update(
+        t.for_portion(
+            SYSTEM_TIME.from_to('2020-01-01', '2020-02-01')
+        )
+    ).set("foo", "bar")
+
+This produces:
+
+.. code-block:: sql
+
+    UPDATE "abc"
+    FOR PORTION OF SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'
+    SET "foo"='bar'
+
+Here is a delete:
+
+.. code-block:: python
+
+    t = Table("abc")
+    q = Query.from_(
+        t.for_portion(t.valid_period.from_to('2020-01-01', '2020-02-01'))
+    ).delete()
+
+This produces:
+
+.. code-block:: sql
+
+    DELETE FROM "abc"
+    FOR PORTION OF "valid_period" FROM '2020-01-01' TO '2020-02-01'
+
 Creating Tables
 ^^^^^^^^^^^^^^^
 
diff --git a/pypika/__init__.py b/pypika/__init__.py
index dc33e71..e1225c1 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -75,6 +75,7 @@ from pypika.terms import (
     JSON,
     Not,
     NullValue,
+    SystemTimeValue,
     Parameter,
     Rollup,
     Tuple,
@@ -94,4 +95,7 @@ from pypika.utils import (
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
-__version__ = "0.41.0"
+__version__ = "0.40.0"
+
+NULL = NullValue()
+SYSTEM_TIME = SystemTimeValue()
diff --git a/pypika/enums.py b/pypika/enums.py
index b80445c..74df9aa 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -32,6 +32,7 @@ class Matching(Comparator):
     ilike = " ILIKE "
     regex = " REGEX "
     bin_regex = " REGEX BINARY "
+    as_of = " AS OF "
 
 
 class Boolean(Comparator):
diff --git a/pypika/queries.py b/pypika/queries.py
index b75a967..2b9ee81 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -16,6 +16,8 @@ from pypika.terms import (
     Term,
     Tuple,
     ValueWrapper,
+    Criterion,
+    PeriodCriterion,
 )
 from pypika.utils import (
     JoinException,
@@ -135,6 +137,8 @@ class Table(Selectable):
         self._table_name = name
         self._schema = self._init_schema(schema)
         self._query_cls = query_cls or Query
+        self._for = None
+        self._for_portion = None
         if not issubclass(self._query_cls, Query):
             raise TypeError("Expected 'query_cls' to be subclass of Query")
 
@@ -149,8 +153,31 @@ class Table(Selectable):
         if self._schema is not None:
             table_sql = "{schema}.{table}".format(schema=self._schema.get_sql(**kwargs), table=table_sql)
 
+        if self._for:
+            table_sql = "{table} FOR {criterion}".format(table=table_sql, criterion=self._for.get_sql(**kwargs))
+        elif self._for_portion:
+            table_sql = "{table} FOR PORTION OF {criterion}".format(
+                table=table_sql, criterion=self._for_portion.get_sql(**kwargs)
+            )
+
         return format_alias_sql(table_sql, self.alias, **kwargs)
 
+    @builder
+    def for_(self, temporal_criterion: Criterion) -> "Table":
+        if self._for:
+            raise AttributeError("'Query' object already has attribute for_")
+        if self._for_portion:
+            raise AttributeError("'Query' object already has attribute for_portion")
+        self._for = temporal_criterion
+
+    @builder
+    def for_portion(self, period_criterion: PeriodCriterion) -> "Table":
+        if self._for_portion:
+            raise AttributeError("'Query' object already has attribute for_portion")
+        if self._for:
+            raise AttributeError("'Query' object already has attribute for_")
+        self._for_portion = period_criterion
+
     def __str__(self) -> str:
         return self.get_sql(quote_char='"')
 
diff --git a/pypika/terms.py b/pypika/terms.py
index f7095aa..2cfb8e1 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -56,7 +56,7 @@ class Term(Node):
     @staticmethod
     def wrap_constant(
         val, wrapper_cls: Optional[Type["Term"]] = None
-    ) -> Union[ValueError, NodeT, "NullValue", "Array", "Tuple", "ValueWrapper"]:
+    ) -> Union[ValueError, NodeT, "LiteralValue", "Array", "Tuple", "ValueWrapper"]:
         """
         Used for wrapping raw inputs such as numbers in Criterions and Operator.
 
@@ -160,6 +160,15 @@ class Term(Node):
     def between(self, lower: Any, upper: Any) -> "BetweenCriterion":
         return BetweenCriterion(self, self.wrap_constant(lower), self.wrap_constant(upper))
 
+    def from_to(self, start: Any, end: Any) -> "PeriodCriterion":
+        return PeriodCriterion(self, self.wrap_constant(start), self.wrap_constant(end))
+
+    def as_of(self, expr: str) -> "BasicCriterion":
+        return BasicCriterion(Matching.as_of, self, self.wrap_constant(expr))
+
+    def all_(self) -> "All":
+        return All(self)
+
     def isin(self, arg: Union[list, tuple, set, "Term"]) -> "ContainsCriterion":
         if isinstance(arg, (list, tuple, set)):
             return ContainsCriterion(self, Tuple(*[self.wrap_constant(value) for value in arg]))
@@ -377,10 +386,23 @@ class Values(Term):
         return "VALUES({value})".format(value=self.field.get_sql(quote_char=quote_char, **kwargs))
 
 
-class NullValue(Term):
+class LiteralValue(Term):
+    def __init__(self, value, alias: Optional[str] = None) -> None:
+        super().__init__(alias)
+        self._value = value
+
     def get_sql(self, **kwargs: Any) -> str:
-        sql = "NULL"
-        return format_alias_sql(sql, self.alias, **kwargs)
+        return format_alias_sql(self._value, self.alias, **kwargs)
+
+
+class NullValue(LiteralValue):
+    def __init__(self, alias: Optional[str] = None) -> None:
+        super().__init__("NULL", alias)
+
+
+class SystemTimeValue(LiteralValue):
+    def __init__(self, alias: Optional[str] = None) -> None:
+        super().__init__("SYSTEM_TIME", alias)
 
 
 class Criterion(Term):
@@ -720,7 +742,7 @@ class ContainsCriterion(Criterion):
         self._is_negated = True
 
 
-class BetweenCriterion(Criterion):
+class RangeCriterion(Criterion):
     def __init__(self, term: Term, start: Any, end: Any, alias: Optional[str] = None) -> str:
         super().__init__(alias)
         self.term = term
@@ -737,6 +759,8 @@ class BetweenCriterion(Criterion):
     def is_aggregate(self) -> Optional[bool]:
         return self.term.is_aggregate
 
+
+class BetweenCriterion(RangeCriterion):
     @builder
     def replace_table(self, current_table: Optional["Table"], new_table: Optional["Table"]) -> "BetweenCriterion":
         """
@@ -761,6 +785,16 @@ class BetweenCriterion(Criterion):
         return format_alias_sql(sql, self.alias, **kwargs)
 
 
+class PeriodCriterion(RangeCriterion):
+    def get_sql(self, **kwargs: Any) -> str:
+        sql = "{term} FROM {start} TO {end}".format(
+            term=self.term.get_sql(**kwargs),
+            start=self.start.get_sql(**kwargs),
+            end=self.end.get_sql(**kwargs),
+        )
+        return format_alias_sql(sql, self.alias, **kwargs)
+
+
 class BitwiseAndCriterion(Criterion):
     def __init__(self, term: Term, value: Any, alias: Optional[str] = None) -> None:
         super().__init__(alias)
@@ -1034,6 +1068,20 @@ class Not(Criterion):
         self.term = self.term.replace_table(current_table, new_table)
 
 
+class All(Criterion):
+    def __init__(self, term: Any, alias: Optional[str] = None) -> None:
+        super().__init__(alias=alias)
+        self.term = term
+
+    def nodes_(self) -> Iterator[NodeT]:
+        yield self
+        yield from self.term.nodes_()
+
+    def get_sql(self, **kwargs: Any) -> str:
+        sql = "{term} ALL".format(term=self.term.get_sql(**kwargs))
+        return format_alias_sql(sql, self.alias, **kwargs)
+
+
 class CustomFunction:
     def __init__(self, name: str, params: Optional[Sequence] = None) -> None:
         self.name = name


Test patch:
diff --git a/pypika/tests/test_deletes.py b/pypika/tests/test_deletes.py
index 00926ba..61f1dbe 100644
--- a/pypika/tests/test_deletes.py
+++ b/pypika/tests/test_deletes.py
@@ -1,6 +1,6 @@
 import unittest
 
-from pypika import PostgreSQLQuery, Query, Table
+from pypika import Table, Query, PostgreSQLQuery, SYSTEM_TIME
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -26,6 +26,23 @@ class DeleteTests(unittest.TestCase):
         self.assertEqual('DELETE FROM "abc" WHERE "foo"="bar"', str(q1))
         self.assertEqual('DELETE FROM "abc" WHERE "foo"="bar"', str(q2))
 
+    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)
+            )
+
 
 class PostgresDeleteTests(unittest.TestCase):
     table_abc = Table("abc")
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index c02a525..8286ef9 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -11,6 +11,7 @@ from pypika import (
     Table,
     Tables,
     functions as fn,
+    SYSTEM_TIME,
 )
 
 __author__ = "Timothy Heys"
@@ -294,6 +295,18 @@ class SelectQueryJoinTests(unittest.TestCase):
         )
         self.assertEqual('SELECT "b"."ouch" FROM "a" JOIN "b" ON "a"."foo"="b"."boo"', str(q2))
 
+    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),
+        )
+
 
 class JoinBehaviorTests(unittest.TestCase):
     table_abc, table_efg, table_hij, table_klm = Tables("abc", "efg", "hij", "klm")
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index 1fa13a0..6ec10ab 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -21,6 +21,7 @@ from pypika import (
     Tables,
     VerticaQuery,
     functions as fn,
+    SYSTEM_TIME,
 )
 from pypika.terms import ValueWrapper
 
@@ -299,6 +300,44 @@ class SelectTests(unittest.TestCase):
             Query.from_("abc").select("foo")[10:10],
         )
 
+    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))
+
 
 class WhereTests(unittest.TestCase):
     t = Table("abc")
diff --git a/pypika/tests/test_tables.py b/pypika/tests/test_tables.py
index dfa22d1..f43e740 100644
--- a/pypika/tests/test_tables.py
+++ b/pypika/tests/test_tables.py
@@ -1,6 +1,7 @@
+# from pypika.terms import ValueWrapper, SystemTimeValue
 import unittest
 
-from pypika import Database, Dialects, Query, SQLLiteQuery, Schema, Table, Tables
+from pypika import Database, Dialects, Schema, SQLLiteQuery, Table, Tables, Query, SYSTEM_TIME
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -37,6 +38,41 @@ class TableStructureTests(unittest.TestCase):
 
         self.assertEqual('"x_db"."x_schema"."test_table"', 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_table_for_period_sql(self):
+        with self.subTest("with between criterion"):
+            table = Table("test_table")
+            table = table.for_(table.valid_period.between('2020-01-01', '2020-02-01'))
+
+            self.assertEqual('"test_table" FOR "valid_period" BETWEEN \'2020-01-01\' AND \'2020-02-01\'', str(table))
+
+        with self.subTest("with as of criterion"):
+            table = Table("test_table")
+            table = table.for_(table.valid_period.as_of('2020-01-01'))
+
+            self.assertEqual('"test_table" FOR "valid_period" AS OF \'2020-01-01\'', str(table))
+
+        with self.subTest("with from to criterion"):
+            table = Table("test_table")
+            table = table.for_(table.valid_period.from_to('2020-01-01', '2020-02-01'))
+
+            self.assertEqual('"test_table" FOR "valid_period" FROM \'2020-01-01\' TO \'2020-02-01\'', str(table))
+
 
 class TableEqualityTests(unittest.TestCase):
     def test_tables_equal_by_name(self):
diff --git a/pypika/tests/test_updates.py b/pypika/tests/test_updates.py
index 6790b42..b4ac2e4 100644
--- a/pypika/tests/test_updates.py
+++ b/pypika/tests/test_updates.py
@@ -1,6 +1,6 @@
 import unittest
 
-from pypika import AliasedQuery, PostgreSQLQuery, Query, SQLLiteQuery, Table
+from pypika import Table, Query, PostgreSQLQuery, AliasedQuery, SQLLiteQuery, SYSTEM_TIME
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -92,6 +92,27 @@ class UpdateTests(unittest.TestCase):
             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),
+            )
+
 
 class PostgresUpdateTests(unittest.TestCase):
     table_abc = Table("abc")