Commits

Kirill Simonov committed e9cd0fe

Added `:where` macro.

  • Participants
  • Parent commits dea4088

Comments (0)

Files changed (8)

src/htsql/tr/fn/bind.py

                         ContainsSig, ExistsSig, CountSig, MinMaxSig,
                         SumSig, AvgSig, AggregateSig, QuantifySig,
                         QuotientSig, KernelSig, ComplementSig,
-                        AssignmentSig, DefineSig)
+                        AssignmentSig, DefineSig, WhereSig)
 import sys
 
 
         yield binding
 
 
+class BindWhere(BindMacro):
+
+    named('where')
+    signature = WhereSig
+
+    def expand(self, lop, rops):
+        binding = self.state.base
+        for op in rops:
+            assignment = self.state.bind(op)
+            if not isinstance(assignment, AssignmentBinding):
+                raise BindError("an assignment expected", op.mark)
+            name = assignment.identifiers[0].value
+            subnames = [identifier.value
+                        for identifier in assignment.identifiers[1:]]
+            arguments = None
+            if assignment.arguments is not None:
+                arguments = [argument.value
+                             for argument in assignment.arguments]
+            binding = DefinitionBinding(binding, name, subnames, arguments,
+                                        assignment.body, self.syntax)
+        return self.state.bind_all(lop, base=binding)
+
+
 class BindCast(BindFunction):
 
     signature = CastSig

src/htsql/tr/fn/signature.py

 
 
 from ..signature import (Signature, Slot, NullarySig, UnarySig, BinarySig,
-                         PolarSig, ConnectiveSig)
+                         PolarSig, ConnectiveSig, NArySig)
 
 
 class FiberSig(Signature):
     pass
 
 
+class WhereSig(NArySig):
+    pass
+
+

test/regress/input/translation.yaml

                    student.age():=age('2011-01-01'),
                    inactive_student:=student?!is_active)
             .inactive_student{name,dob,start_date,age(start_date),age()}
+    - uri: /school{code,{count(student),
+                         count(student?gender='m'),
+                         count(student?gender='f')}
+                        :where(student:=student?is_active)}
 
   - title: Projections
     tests:

test/regress/output/mssql.yaml

                FROM [ed].[student] AS [student]
                WHERE (NOT ([student].[is_active] <> 0))
                ORDER BY [student].[id] ASC
+          - uri: /school{code,{count(student), count(student?gender='m'), count(student?gender='f')}
+              :where(student:=student?is_active)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | school                                                                        |
+              -+-------------------------------------------------------------------------------+-
+               | code | count(student) | count(student?gender='m') | count(student?gender='f') |
+              -+------+----------------+---------------------------+---------------------------+-
+               | art  |              6 |                         5 |                         1 |
+               | bus  |              7 |                         3 |                         4 |
+               | edu  |              4 |                         1 |                         3 |
+               | eng  |             12 |                         9 |                         3 |
+               | la   |              7 |                         5 |                         2 |
+               | mus  |              0 |                         0 |                         0 |
+               | ns   |              8 |                         2 |                         6 |
+               | ph   |              0 |                         0 |                         0 |
+               | sc   |              0 |                         0 |                         0 |
+                                                                                        (9 rows)
+
+               ----
+               /school{code,{count(student),count(student?gender='m'),count(student?gender='f')}:where(student:=student?is_active)}
+               SELECT [school].[code],
+                      COALESCE([student_1].[count], 0),
+                      COALESCE([student_2].[count], 0),
+                      COALESCE([student_3].[count], 0)
+               FROM [ad].[school] AS [school]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [student].[school]
+                                     FROM [ed].[student] AS [student]
+                                     WHERE ([student].[is_active] <> 0)
+                                     GROUP BY [student].[school]) AS [student_1]
+                                    ON ([school].[code] = [student_1].[school])
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [student].[school]
+                                     FROM [ed].[student] AS [student]
+                                     WHERE ([student].[gender] = 'm')
+                                           AND ([student].[is_active] <> 0)
+                                     GROUP BY [student].[school]) AS [student_2]
+                                    ON ([school].[code] = [student_2].[school])
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [student].[school]
+                                     FROM [ed].[student] AS [student]
+                                     WHERE ([student].[gender] = 'f')
+                                           AND ([student].[is_active] <> 0)
+                                     GROUP BY [student].[school]) AS [student_3]
+                                    ON ([school].[code] = [student_3].[school])
+               ORDER BY 1 ASC
         - id: projections
           tests:
           - uri: /quotient(program, degree)

test/regress/output/mysql.yaml

                FROM `htsql_regress`.`student` AS `student`
                WHERE (NOT `student`.`is_active`)
                ORDER BY `student`.`id` ASC
+          - uri: /school{code,{count(student), count(student?gender='m'), count(student?gender='f')}
+              :where(student:=student?is_active)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | school                                                                        |
+              -+-------------------------------------------------------------------------------+-
+               | code | count(student) | count(student?gender='m') | count(student?gender='f') |
+              -+------+----------------+---------------------------+---------------------------+-
+               | art  |              6 |                         5 |                         1 |
+               | bus  |              7 |                         3 |                         4 |
+               | edu  |              4 |                         1 |                         3 |
+               | eng  |             12 |                         9 |                         3 |
+               | la   |              7 |                         5 |                         2 |
+               | mus  |              0 |                         0 |                         0 |
+               | ns   |              8 |                         2 |                         6 |
+               | ph   |              0 |                         0 |                         0 |
+               | sc   |              0 |                         0 |                         0 |
+                                                                                        (9 rows)
+
+               ----
+               /school{code,{count(student),count(student?gender='m'),count(student?gender='f')}:where(student:=student?is_active)}
+               SELECT `school`.`code`,
+                      COALESCE(`student_1`.`count`, 0),
+                      COALESCE(`student_2`.`count`, 0),
+                      COALESCE(`student_3`.`count`, 0)
+               FROM `htsql_regress`.`school` AS `school`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `student`.`school`
+                                     FROM `htsql_regress`.`student` AS `student`
+                                     WHERE `student`.`is_active`
+                                     GROUP BY 2) AS `student_1`
+                                    ON (`school`.`code` = `student_1`.`school`)
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `student`.`school`
+                                     FROM `htsql_regress`.`student` AS `student`
+                                     WHERE (`student`.`gender` = 'm')
+                                           AND `student`.`is_active`
+                                     GROUP BY 2) AS `student_2`
+                                    ON (`school`.`code` = `student_2`.`school`)
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `student`.`school`
+                                     FROM `htsql_regress`.`student` AS `student`
+                                     WHERE (`student`.`gender` = 'f')
+                                           AND `student`.`is_active`
+                                     GROUP BY 2) AS `student_3`
+                                    ON (`school`.`code` = `student_3`.`school`)
+               ORDER BY 1 ASC
         - id: projections
           tests:
           - uri: /quotient(program, degree)

test/regress/output/oracle.yaml

                FROM "HTSQL_REGRESS"."STUDENT" "STUDENT"
                WHERE (NOT ("STUDENT"."IS_ACTIVE" <> 0))
                ORDER BY "STUDENT"."ID" ASC
+          - uri: /school{code,{count(student), count(student?gender='m'), count(student?gender='f')}
+              :where(student:=student?is_active)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | school                                                                        |
+              -+-------------------------------------------------------------------------------+-
+               | code | count(student) | count(student?gender='m') | count(student?gender='f') |
+              -+------+----------------+---------------------------+---------------------------+-
+               | art  |              6 |                         5 |                         1 |
+               | bus  |              7 |                         3 |                         4 |
+               | edu  |              4 |                         1 |                         3 |
+               | eng  |             12 |                         9 |                         3 |
+               | la   |              7 |                         5 |                         2 |
+               | mus  |              0 |                         0 |                         0 |
+               | ns   |              8 |                         2 |                         6 |
+               | ph   |              0 |                         0 |                         0 |
+               | sc   |              0 |                         0 |                         0 |
+                                                                                        (9 rows)
+
+               ----
+               /school{code,{count(student),count(student?gender='m'),count(student?gender='f')}:where(student:=student?is_active)}
+               SELECT "SCHOOL"."CODE",
+                      COALESCE("STUDENT_1"."count", 0),
+                      COALESCE("STUDENT_2"."count", 0),
+                      COALESCE("STUDENT_3"."count", 0)
+               FROM "HTSQL_REGRESS"."SCHOOL" "SCHOOL"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "STUDENT"."SCHOOL"
+                                     FROM "HTSQL_REGRESS"."STUDENT" "STUDENT"
+                                     WHERE ("STUDENT"."IS_ACTIVE" <> 0)
+                                     GROUP BY "STUDENT"."SCHOOL") "STUDENT_1"
+                                    ON ("SCHOOL"."CODE" = "STUDENT_1"."SCHOOL")
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "STUDENT"."SCHOOL"
+                                     FROM "HTSQL_REGRESS"."STUDENT" "STUDENT"
+                                     WHERE ("STUDENT"."GENDER" = 'm')
+                                           AND ("STUDENT"."IS_ACTIVE" <> 0)
+                                     GROUP BY "STUDENT"."SCHOOL") "STUDENT_2"
+                                    ON ("SCHOOL"."CODE" = "STUDENT_2"."SCHOOL")
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "STUDENT"."SCHOOL"
+                                     FROM "HTSQL_REGRESS"."STUDENT" "STUDENT"
+                                     WHERE ("STUDENT"."GENDER" = 'f')
+                                           AND ("STUDENT"."IS_ACTIVE" <> 0)
+                                     GROUP BY "STUDENT"."SCHOOL") "STUDENT_3"
+                                    ON ("SCHOOL"."CODE" = "STUDENT_3"."SCHOOL")
+               ORDER BY 1 ASC
         - id: projections
           tests:
           - uri: /quotient(program, degree)

test/regress/output/pgsql.yaml

                FROM "ed"."student" AS "student"
                WHERE (NOT "student"."is_active")
                ORDER BY "student"."id" ASC
+          - uri: /school{code,{count(student), count(student?gender='m'), count(student?gender='f')}
+              :where(student:=student?is_active)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | school                                                                        |
+              -+-------------------------------------------------------------------------------+-
+               | code | count(student) | count(student?gender='m') | count(student?gender='f') |
+              -+------+----------------+---------------------------+---------------------------+-
+               | art  |              6 |                         5 |                         1 |
+               | bus  |              7 |                         3 |                         4 |
+               | edu  |              4 |                         1 |                         3 |
+               | eng  |             12 |                         9 |                         3 |
+               | la   |              7 |                         5 |                         2 |
+               | mus  |              0 |                         0 |                         0 |
+               | ns   |              8 |                         2 |                         6 |
+               | ph   |              0 |                         0 |                         0 |
+               | sc   |              0 |                         0 |                         0 |
+                                                                                        (9 rows)
+
+               ----
+               /school{code,{count(student),count(student?gender='m'),count(student?gender='f')}:where(student:=student?is_active)}
+               SELECT "school"."code",
+                      COALESCE("student_1"."count", 0),
+                      COALESCE("student_2"."count", 0),
+                      COALESCE("student_3"."count", 0)
+               FROM "ad"."school" AS "school"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "student"."school"
+                                     FROM "ed"."student" AS "student"
+                                     WHERE "student"."is_active"
+                                     GROUP BY 2) AS "student_1"
+                                    ON ("school"."code" = "student_1"."school")
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "student"."school"
+                                     FROM "ed"."student" AS "student"
+                                     WHERE ("student"."gender" = 'm')
+                                           AND "student"."is_active"
+                                     GROUP BY 2) AS "student_2"
+                                    ON ("school"."code" = "student_2"."school")
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "student"."school"
+                                     FROM "ed"."student" AS "student"
+                                     WHERE ("student"."gender" = 'f')
+                                           AND "student"."is_active"
+                                     GROUP BY 2) AS "student_3"
+                                    ON ("school"."code" = "student_3"."school")
+               ORDER BY 1 ASC
         - id: projections
           tests:
           - uri: /quotient(program, degree)

test/regress/output/sqlite.yaml

                FROM "student" AS "student"
                WHERE (NOT "student"."is_active")
                ORDER BY "student"."id" ASC
+          - uri: /school{code,{count(student), count(student?gender='m'), count(student?gender='f')}
+              :where(student:=student?is_active)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | school                                                                        |
+              -+-------------------------------------------------------------------------------+-
+               | code | count(student) | count(student?gender='m') | count(student?gender='f') |
+              -+------+----------------+---------------------------+---------------------------+-
+               | art  |              6 |                         5 |                         1 |
+               | bus  |              7 |                         3 |                         4 |
+               | edu  |              4 |                         1 |                         3 |
+               | eng  |             12 |                         9 |                         3 |
+               | la   |              7 |                         5 |                         2 |
+               | mus  |              0 |                         0 |                         0 |
+               | ns   |              8 |                         2 |                         6 |
+               | ph   |              0 |                         0 |                         0 |
+               | sc   |              0 |                         0 |                         0 |
+                                                                                        (9 rows)
+
+               ----
+               /school{code,{count(student),count(student?gender='m'),count(student?gender='f')}:where(student:=student?is_active)}
+               SELECT "school"."code",
+                      COALESCE("student_1"."count", 0),
+                      COALESCE("student_2"."count", 0),
+                      COALESCE("student_3"."count", 0)
+               FROM "school" AS "school"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "student"."school"
+                                     FROM "student" AS "student"
+                                     WHERE "student"."is_active"
+                                     GROUP BY 2) AS "student_1"
+                                    ON ("school"."code" = "student_1"."school")
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "student"."school"
+                                     FROM "student" AS "student"
+                                     WHERE ("student"."gender" = 'm')
+                                           AND "student"."is_active"
+                                     GROUP BY 2) AS "student_2"
+                                    ON ("school"."code" = "student_2"."school")
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "student"."school"
+                                     FROM "student" AS "student"
+                                     WHERE ("student"."gender" = 'f')
+                                           AND "student"."is_active"
+                                     GROUP BY 2) AS "student_3"
+                                    ON ("school"."code" = "student_3"."school")
+               ORDER BY 1 ASC
         - id: projections
           tests:
           - uri: /quotient(program, degree)