Kirill Simonov avatar Kirill Simonov committed 3df9882

top(): implementation for MySQL.

Comments (0)

Files changed (7)

src/htsql_mysql/core/tr/__init__.py

 #
 
 
-from . import dump, reduce
+from . import compile, dump, reduce, signature
 
 

src/htsql_mysql/core/tr/compile.py

+#
+# Copyright (c) 2006-2012, Prometheus Research, LLC
+#
+
+
+from htsql.core.domain import BooleanDomain, IntegerDomain
+from htsql.core.tr.term import (WrapperTerm, FilterTerm, OrderTerm,
+                                PermanentTerm, JoinTerm, ScalarTerm)
+from htsql.core.tr.flow import (LiteralCode, FormulaCode, ScalarUnit,
+                                CorrelationCode)
+from htsql.core.tr.coerce import coerce
+from htsql.core.tr.signature import IsEqualSig, AndSig, CompareSig
+from htsql.core.tr.fn.signature import AddSig
+from htsql.core.tr.compile import CompileCovering
+from .signature import (UserVariableSig, UserVariableAssignmentSig,
+                        NoOpConditionSig, IfSig)
+
+
+class MySQLCompileCovering(CompileCovering):
+
+    def clip(self, term, order, partition):
+        prefix = u"!htsql:%s" % term.tag
+        row_number = FormulaCode(UserVariableSig(u"%s:row_number" % prefix),
+                                 coerce(IntegerDomain()), self.flow.binding)
+        keys = []
+        for idx, code in enumerate(partition):
+            key = FormulaCode(UserVariableSig(u"%s:partition:%s"
+                                              % (prefix, idx+1)),
+                                              code.domain, self.flow.binding)
+            keys.append(key)
+        #term = PermanentTerm(self.state.tag(), term,
+        #                     term.flow, term.baseline, term.routes.copy())
+        zero_term = ScalarTerm(self.state.tag(),
+                               self.state.root, self.state.root, {})
+        zero_units = []
+        code = FormulaCode(UserVariableAssignmentSig(),
+                           row_number.domain, self.flow.binding,
+                           lop=row_number,
+                           rop=LiteralCode(None, row_number.domain,
+                                           self.flow.binding))
+        unit = ScalarUnit(code, self.state.root, code.binding)
+        zero_units.append(unit)
+        for key in keys:
+            code = FormulaCode(UserVariableAssignmentSig(),
+                               key.domain, self.flow.binding,
+                               lop=key,
+                               rop=LiteralCode(None, key.domain,
+                                               self.flow.binding))
+            unit = ScalarUnit(code, self.state.root, code.binding)
+            zero_units.append(unit)
+        tag = self.state.tag()
+        routes = {}
+        for unit in zero_units:
+            routes[unit] = tag
+        zero_term.routes = routes
+        zero_term = PermanentTerm(tag, zero_term,
+                                  zero_term.flow, zero_term.baseline, routes)
+        filters = [FormulaCode(NoOpConditionSig(), coerce(BooleanDomain()),
+                   self.flow.binding, op=unit) for unit in zero_units]
+        filter = FormulaCode(AndSig(), coerce(BooleanDomain()),
+                             self.flow.binding, ops=filters)
+        zero_term = FilterTerm(self.state.tag(), zero_term, filter,
+                               zero_term.flow, zero_term.baseline, {})
+        term = JoinTerm(self.state.tag(), term, zero_term, [],
+                        False, False, term.flow, term.baseline,
+                        term.routes.copy())
+        order = [(code, +1) for code in partition]+order
+        term = OrderTerm(self.state.tag(), term, order, None, None,
+                         term.flow, term.baseline, term.routes.copy())
+        term = PermanentTerm(self.state.tag(), term,
+                             term.flow, term.baseline, term.routes)
+        next_units = []
+        conditions = []
+        for lop, rop in zip(keys, partition):
+            condition = FormulaCode(IsEqualSig(+1), coerce(BooleanDomain()),
+                                    self.flow.binding, lop=lop, rop=rop)
+            conditions.append(condition)
+        if len(conditions) == 1:
+            [condition] = conditions
+        else:
+            condition = FormulaCode(AndSig(), coerce(BooleanDomain()),
+                                    self.flow.binding, ops=conditions)
+        one_literal = LiteralCode(1, coerce(IntegerDomain()),
+                                  self.flow.binding)
+        on_true = FormulaCode(AddSig(), coerce(IntegerDomain()),
+                              self.flow.binding,
+                              lop=row_number, rop=one_literal)
+        on_false = one_literal
+        value = FormulaCode(IfSig(), row_number.domain, self.flow.binding,
+                            condition=condition, on_true=on_true,
+                            on_false=on_false)
+        code = FormulaCode(UserVariableAssignmentSig(),
+                           row_number.domain, self.flow.binding,
+                           lop=row_number, rop=value)
+        row_number_unit = ScalarUnit(code, term.flow, code.binding)
+        next_units.append(row_number_unit)
+        for lop, rop in zip(keys, partition):
+            code = FormulaCode(UserVariableAssignmentSig(), lop.domain,
+                                    self.flow.binding, lop=lop, rop=rop)
+            unit = ScalarUnit(code, term.flow, code.binding)
+            next_units.append(unit)
+        tag = self.state.tag()
+        routes = term.routes.copy()
+        for unit in next_units:
+            routes[unit] = tag
+        term = PermanentTerm(tag, term, term.flow, term.baseline, routes)
+        left_bound = 1
+        if self.flow.offset is not None:
+            left_bound = self.flow.offset+1
+        right_bound = left_bound+1
+        if self.flow.limit is not None:
+            right_bound = left_bound+self.flow.limit
+        left_bound_code = LiteralCode(left_bound, coerce(IntegerDomain()),
+                                      term.flow.binding)
+        right_bound_code = LiteralCode(right_bound, coerce(IntegerDomain()),
+                                       term.flow.binding)
+        left_filter = FormulaCode(CompareSig('>='), coerce(BooleanDomain()),
+                                  term.flow.binding,
+                                  lop=row_number_unit, rop=left_bound_code)
+        right_filter = FormulaCode(CompareSig('<'), coerce(BooleanDomain()),
+                                   term.flow.binding,
+                                   lop=row_number_unit, rop=right_bound_code)
+        filters = [left_filter, right_filter]
+        filters += [FormulaCode(NoOpConditionSig(), coerce(BooleanDomain()),
+                    self.flow.binding, op=unit) for unit in next_units]
+        filter = FormulaCode(AndSig(), coerce(BooleanDomain()),
+                             self.flow.binding, ops=filters)
+        term = FilterTerm(self.state.tag(), term, filter,
+                          term.flow, term.baseline, term.routes)
+        return term
+
+

src/htsql_mysql/core/tr/dump.py

                                    DumpDateDifference, DumpExtractSecond,
                                    DumpConcatenate, DumpLike,
                                    DumpMakeDate, DumpMakeDateTime,
-                                   DumpCombineDateTime, DumpSum)
+                                   DumpCombineDateTime, DumpSum, DumpFunction)
+from .signature import UserVariableSig, UserVariableAssignmentSig, IfSig
 import math
 
 
             self.format("SUM({op})", self.arguments)
 
 
+class MySQLDumpUserVariable(DumpFunction):
+
+    adapt(UserVariableSig)
+
+    template = "@{name:name}"
+
+
+class MySQLDumpUserVariableAssignment(DumpFunction):
+
+    adapt(UserVariableAssignmentSig)
+
+    template = "{lop} := {rop}"
+
+
+class MySQLDumpIf(DumpFunction):
+
+    adapt(IfSig)
+
+    template = "IF({condition}, {on_true}, {on_false})"
+
+

src/htsql_mysql/core/tr/reduce.py

 #
 
 
-from htsql.core.tr.frame import ScalarFrame, LeadingAnchor
-from htsql.core.tr.reduce import ReduceBranch
+from htsql.core.adapter import adapt
+from htsql.core.tr.frame import ScalarFrame, LeadingAnchor, TruePhrase
+from htsql.core.tr.reduce import ReduceBranch, ReduceBySignature
+from .signature import NoOpConditionSig
 
 
 class MySQLReduceBranch(ReduceBranch):
         return frame
 
 
+class MySQLReduceNoOpCondition(ReduceBySignature):
+
+    adapt(NoOpConditionSig)
+
+    def __call__(self):
+        return TruePhrase(self.phrase.expression)
+
+

src/htsql_mysql/core/tr/signature.py

+#
+# Copyright (c) 2006-2012, Prometheus Research, LLC
+#
+
+
+from htsql.core.tr.signature import (Signature, Slot,
+                                     NullarySig, UnarySig, BinarySig)
+
+
+class UserVariableSig(NullarySig):
+
+    def __init__(self, name):
+        assert isinstance(name, unicode) and len(name) > 0
+        self.name = name
+
+    def __basis__(self):
+        return (self.name,)
+
+
+class UserVariableAssignmentSig(BinarySig):
+    pass
+
+
+class IfSig(Signature):
+
+    slots = [
+            Slot('condition'),
+            Slot('on_true'),
+            Slot('on_false'),
+    ]
+
+
+class NoOpConditionSig(UnarySig):
+    pass
+
+

test/input/translation.yaml

     - uri: /@school.@department.@program.limit(1)
 
   - title: Top
-    ifndef: [mysql]
     tests:
     - uri: /school :top
     - uri: /school :top(3)

test/output/mysql.yaml

                     CROSS JOIN `program`
                ORDER BY `school`.`code` ASC, `department`.`code` ASC, 1 ASC, 2 ASC
                LIMIT 1
+        - id: top
+          tests:
+          - uri: /school :top
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top                             |
+               +------+------------------------+--------+
+               | code | name                   | campus |
+              -+------+------------------------+--------+-
+               | art  | School of Art & Design | old    |
+
+               ----
+               /school:top
+               SELECT `school`.`code`,
+                      `school`.`name`,
+                      `school`.`campus`
+               FROM (SELECT `school`.`code`,
+                            `school`.`name`,
+                            `school`.`campus`
+                     FROM `school`
+                     ORDER BY 1 ASC
+                     LIMIT 1) AS `school`
+               ORDER BY 1 ASC
+          - uri: /school :top(3)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top(3)                          |
+               +------+------------------------+--------+
+               | code | name                   | campus |
+              -+------+------------------------+--------+-
+               | art  | School of Art & Design | old    |
+               | bus  | School of Business     | south  |
+               | edu  | College of Education   | old    |
+
+               ----
+               /school:top(3)
+               SELECT `school`.`code`,
+                      `school`.`name`,
+                      `school`.`campus`
+               FROM (SELECT `school`.`code`,
+                            `school`.`name`,
+                            `school`.`campus`
+                     FROM `school`
+                     ORDER BY 1 ASC
+                     LIMIT 3) AS `school`
+               ORDER BY 1 ASC
+          - uri: /school :top(3,2)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top(3,2)                               |
+               +------+-------------------------------+--------+
+               | code | name                          | campus |
+              -+------+-------------------------------+--------+-
+               | edu  | College of Education          | old    |
+               | eng  | School of Engineering         | north  |
+               | la   | School of Arts and Humanities | old    |
+
+               ----
+               /school:top(3,2)
+               SELECT `school`.`code`,
+                      `school`.`name`,
+                      `school`.`campus`
+               FROM (SELECT `school`.`code`,
+                            `school`.`name`,
+                            `school`.`campus`
+                     FROM `school`
+                     ORDER BY 1 ASC
+                     LIMIT 3
+                     OFFSET 2) AS `school`
+               ORDER BY 1 ASC
+          - uri: /top(school.sort(count(department)-)){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-)) |
+               +------------+-------------------------+
+               | code       | count(department)       |
+              -+------------+-------------------------+-
+               | la         |                       6 |
+
+               ----
+               /top(school.sort(count(department)-)){code,count(department)}
+               SELECT `school_1`.`code`,
+                      COALESCE(`department`.`count`, 0)
+               FROM (SELECT `school`.`code`
+                     FROM `school`
+                          LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                  `department`.`school_code`
+                                           FROM `department`
+                                           GROUP BY 2) AS `department`
+                                          ON (`school`.`code` = `department`.`school_code`)
+                     ORDER BY COALESCE(`department`.`count`, 0) DESC, 1 ASC
+                     LIMIT 1) AS `school_1`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `department`.`school_code`
+                                     FROM `department`
+                                     GROUP BY 2) AS `department`
+                                    ON (`school_1`.`code` = `department`.`school_code`)
+                    INNER JOIN (SELECT COALESCE(`department`.`count`, 0) AS `count`,
+                                       `school`.`code`
+                                FROM `school`
+                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                             `department`.`school_code`
+                                                      FROM `department`
+                                                      GROUP BY 2) AS `department`
+                                                     ON (`school`.`code` = `department`.`school_code`)
+                                ORDER BY 1 DESC, 2 ASC
+                                LIMIT 1) AS `school_2`
+                               ON (`school_1`.`code` = `school_2`.`code`)
+               ORDER BY `school_2`.`count` DESC, 1 ASC
+          - uri: /top(school.sort(count(department)-),3){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-),3) |
+               +-------------+--------------------------+
+               | code        | count(department)        |
+              -+-------------+--------------------------+-
+               | la          |                        6 |
+               | eng         |                        4 |
+               | mus         |                        4 |
+
+               ----
+               /top(school.sort(count(department)-),3){code,count(department)}
+               SELECT `school_1`.`code`,
+                      COALESCE(`department`.`count`, 0)
+               FROM (SELECT `school`.`code`
+                     FROM `school`
+                          LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                  `department`.`school_code`
+                                           FROM `department`
+                                           GROUP BY 2) AS `department`
+                                          ON (`school`.`code` = `department`.`school_code`)
+                     ORDER BY COALESCE(`department`.`count`, 0) DESC, 1 ASC
+                     LIMIT 3) AS `school_1`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `department`.`school_code`
+                                     FROM `department`
+                                     GROUP BY 2) AS `department`
+                                    ON (`school_1`.`code` = `department`.`school_code`)
+                    INNER JOIN (SELECT COALESCE(`department`.`count`, 0) AS `count`,
+                                       `school`.`code`
+                                FROM `school`
+                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                             `department`.`school_code`
+                                                      FROM `department`
+                                                      GROUP BY 2) AS `department`
+                                                     ON (`school`.`code` = `department`.`school_code`)
+                                ORDER BY 1 DESC, 2 ASC
+                                LIMIT 3) AS `school_2`
+                               ON (`school_1`.`code` = `school_2`.`code`)
+               ORDER BY `school_2`.`count` DESC, 1 ASC
+          - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-),3,2) |
+               +--------------+---------------------------+
+               | code         | count(department)         |
+              -+--------------+---------------------------+-
+               | mus          |                         4 |
+               | ns           |                         4 |
+               | bus          |                         3 |
+
+               ----
+               /top(school.sort(count(department)-),3,2){code,count(department)}
+               SELECT `school_1`.`code`,
+                      COALESCE(`department`.`count`, 0)
+               FROM (SELECT `school`.`code`
+                     FROM `school`
+                          LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                  `department`.`school_code`
+                                           FROM `department`
+                                           GROUP BY 2) AS `department`
+                                          ON (`school`.`code` = `department`.`school_code`)
+                     ORDER BY COALESCE(`department`.`count`, 0) DESC, 1 ASC
+                     LIMIT 3
+                     OFFSET 2) AS `school_1`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `department`.`school_code`
+                                     FROM `department`
+                                     GROUP BY 2) AS `department`
+                                    ON (`school_1`.`code` = `department`.`school_code`)
+                    INNER JOIN (SELECT COALESCE(`department`.`count`, 0) AS `count`,
+                                       `school`.`code`
+                                FROM `school`
+                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                             `department`.`school_code`
+                                                      FROM `department`
+                                                      GROUP BY 2) AS `department`
+                                                     ON (`school`.`code` = `department`.`school_code`)
+                                ORDER BY 1 DESC, 2 ASC
+                                LIMIT 3
+                                OFFSET 2) AS `school_2`
+                               ON (`school_1`.`code` = `school_2`.`code`)
+               ORDER BY `school_2`.`count` DESC, 1 ASC
+          - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program)                                      |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Post Baccalaureate in Art        |             16 |
+               :                               :                : History                          :                :
+               | College of Education          |              7 | Master of Arts in Education      |             10 |
+               :                               :                : Leadership                       :                :
+               | School of Arts and Humanities |              9 | Master of Arts in English        |             13 |
+               | School of Natural Sciences    |              6 | Masters of Science in            |             13 |
+               :                               :                : Mathematics                      :                :
+
+               ----
+               /school{name,count(program),top(program){title,count(student)}}?campus='old'
+               SELECT `school`.`name`,
+                      COALESCE(`program_1`.`count`, 0),
+                      `program_2`.`program`,
+                      `program_2`.`title`,
+                      `program_2`.`count`
+               FROM `school`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`school_code`
+                                     FROM `program`
+                                     GROUP BY 2) AS `program_1`
+                                    ON (`school`.`code` = `program_1`.`school_code`)
+                    LEFT OUTER JOIN (SELECT TRUE AS `program`,
+                                            `program`.`title`,
+                                            COALESCE(`student`.`count`, 0) AS `count`,
+                                            `program`.`school_code`
+                                     FROM (SELECT `program`.`title`,
+                                                  `program`.`school_code`,
+                                                  `program`.`code`,
+                                                  @`!htsql:9:row_number` := IF((@`!htsql:9:partition:1` = `program`.`school_code`), (@`!htsql:9:row_number` + 1), 1) AS `top_1`,
+                                                  @`!htsql:9:partition:1` := `program`.`school_code` AS `top_2`
+                                           FROM (SELECT `program`.`title`,
+                                                        `program`.`school_code`,
+                                                        `program`.`code`
+                                                 FROM `program`
+                                                      CROSS JOIN (SELECT TRUE AS `!`
+                                                                  FROM (SELECT @`!htsql:9:row_number` := NULL AS `top_1`,
+                                                                               @`!htsql:9:partition:1` := NULL AS `top_2`) AS `!`) AS `!`
+                                                 ORDER BY 2 ASC, 3 ASC) AS `program`) AS `program`
+                                          LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                                  `student`.`school_code`,
+                                                                  `student`.`program_code`
+                                                           FROM `student`
+                                                           GROUP BY 2, 3) AS `student`
+                                                          ON ((`program`.`school_code` = `student`.`school_code`) AND (`program`.`code` = `student`.`program_code`))
+                                     WHERE (`program`.`top_1` >= 1)
+                                           AND (`program`.`top_1` < 2)) AS `program_2`
+                                    ON (`school`.`code` = `program_2`.`school_code`)
+               WHERE (`school`.`campus` = 'old')
+               ORDER BY `school`.`code` ASC
+          - uri: /school{name, count(program), /top(program,3){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program,3)                                    |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Post Baccalaureate in Art        |             16 |
+               :                               :                : History                          :                :
+               :                               :                | Bachelor of Arts in Art History  |             20 |
+               :                               :                | Bachelor of Arts in Studio Art   |             26 |
+               | College of Education          |              7 | Master of Arts in Education      |             10 |
+               :                               :                : Leadership                       :                :
+               :                               :                | M.S. in Education                |             11 |
+               :                               :                | Master of Arts in Literacy       |             15 |
+               :                               :                : Education                        :                :
+               | School of Arts and Humanities |              9 | Master of Arts in English        |             13 |
+               :                               :                | Master of Arts in Modern         |             10 |
+               :                               :                : Languages                        :                :
+               :                               :                | Master of Arts in Science        |             11 |
+               :                               :                : Teaching                         :                :
+               | School of Natural Sciences    |              6 | Masters of Science in            |             13 |
+               :                               :                : Mathematics                      :                :
+               :                               :                | Doctorate of Science in          |             10 |
+               :                               :                : Mathematics                      :                :
+               :                               :                | Bachelor of Science in Astronomy |              5 |
+
+               ----
+               /school{name,count(program),/top(program,3){title,count(student)}}?campus='old'
+               SELECT `school`.`name`,
+                      COALESCE(`program`.`count`, 0),
+                      `school`.`code`
+               FROM `school`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`school_code`
+                                     FROM `program`
+                                     GROUP BY 2) AS `program`
+                                    ON (`school`.`code` = `program`.`school_code`)
+               WHERE (`school`.`campus` = 'old')
+               ORDER BY 3 ASC
+
+                 SELECT `program`.`title`,
+                        COALESCE(`student`.`count`, 0),
+                        `school`.`code`
+                 FROM `school`
+                      INNER JOIN (SELECT `program`.`title`,
+                                         `program`.`school_code`,
+                                         `program`.`code`
+                                  FROM (SELECT `program`.`title`,
+                                               `program`.`school_code`,
+                                               `program`.`code`,
+                                               @`!htsql:14:row_number` := IF((@`!htsql:14:partition:1` = `program`.`school_code`), (@`!htsql:14:row_number` + 1), 1) AS `top_1`,
+                                               @`!htsql:14:partition:1` := `program`.`school_code` AS `top_2`
+                                        FROM (SELECT `program`.`title`,
+                                                     `program`.`school_code`,
+                                                     `program`.`code`
+                                              FROM `program`
+                                                   CROSS JOIN (SELECT TRUE AS `!`
+                                                               FROM (SELECT @`!htsql:14:row_number` := NULL AS `top_1`,
+                                                                            @`!htsql:14:partition:1` := NULL AS `top_2`) AS `!`) AS `!`
+                                              ORDER BY 2 ASC, 3 ASC) AS `program`) AS `program`
+                                  WHERE (`program`.`top_1` >= 1)
+                                        AND (`program`.`top_1` < 4)) AS `program`
+                                 ON (`school`.`code` = `program`.`school_code`)
+                      LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                              `student`.`school_code`,
+                                              `student`.`program_code`
+                                       FROM `student`
+                                       GROUP BY 2, 3) AS `student`
+                                      ON ((`program`.`school_code` = `student`.`school_code`) AND (`program`.`code` = `student`.`program_code`))
+                 WHERE (`school`.`campus` = 'old')
+                 ORDER BY 3 ASC, `program`.`school_code` ASC, `program`.`code` ASC
+          - uri: /school{name, count(program), /top(program,3,2){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program,3,2)                                  |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Bachelor of Arts in Studio Art   |             26 |
+               | College of Education          |              7 | Master of Arts in Literacy       |             15 |
+               :                               :                : Education                        :                :
+               :                               :                | Master of Arts in Teaching       |             13 |
+               :                               :                | Certificate in Science Teaching  |              8 |
+               | School of Arts and Humanities |              9 | Master of Arts in Science        |             11 |
+               :                               :                : Teaching                         :                :
+               :                               :                | Science Writing                  |              5 |
+               :                               :                | Bachelor of Arts in English      |              8 |
+               | School of Natural Sciences    |              6 | Bachelor of Science in Astronomy |              5 |
+               :                               :                | Bachelor of Science in Chemistry |             11 |
+               :                               :                | Bachelor of Science in           |             13 |
+               :                               :                : Mathematics                      :                :
+
+               ----
+               /school{name,count(program),/top(program,3,2){title,count(student)}}?campus='old'
+               SELECT `school`.`name`,
+                      COALESCE(`program`.`count`, 0),
+                      `school`.`code`
+               FROM `school`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`school_code`
+                                     FROM `program`
+                                     GROUP BY 2) AS `program`
+                                    ON (`school`.`code` = `program`.`school_code`)
+               WHERE (`school`.`campus` = 'old')
+               ORDER BY 3 ASC
+
+                 SELECT `program`.`title`,
+                        COALESCE(`student`.`count`, 0),
+                        `school`.`code`
+                 FROM `school`
+                      INNER JOIN (SELECT `program`.`title`,
+                                         `program`.`school_code`,
+                                         `program`.`code`
+                                  FROM (SELECT `program`.`title`,
+                                               `program`.`school_code`,
+                                               `program`.`code`,
+                                               @`!htsql:14:row_number` := IF((@`!htsql:14:partition:1` = `program`.`school_code`), (@`!htsql:14:row_number` + 1), 1) AS `top_1`,
+                                               @`!htsql:14:partition:1` := `program`.`school_code` AS `top_2`
+                                        FROM (SELECT `program`.`title`,
+                                                     `program`.`school_code`,
+                                                     `program`.`code`
+                                              FROM `program`
+                                                   CROSS JOIN (SELECT TRUE AS `!`
+                                                               FROM (SELECT @`!htsql:14:row_number` := NULL AS `top_1`,
+                                                                            @`!htsql:14:partition:1` := NULL AS `top_2`) AS `!`) AS `!`
+                                              ORDER BY 2 ASC, 3 ASC) AS `program`) AS `program`
+                                  WHERE (`program`.`top_1` >= 3)
+                                        AND (`program`.`top_1` < 6)) AS `program`
+                                 ON (`school`.`code` = `program`.`school_code`)
+                      LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                              `student`.`school_code`,
+                                              `student`.`program_code`
+                                       FROM `student`
+                                       GROUP BY 2, 3) AS `student`
+                                      ON ((`program`.`school_code` = `student`.`school_code`) AND (`program`.`code` = `student`.`program_code`))
+                 WHERE (`school`.`campus` = 'old')
+                 ORDER BY 3 ASC, `program`.`school_code` ASC, `program`.`code` ASC
+          - uri: /school^campus{*, top(^)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school^campus                                   |
+               +--------+----------------------------------------+
+               |        | top(^)                                 |
+               |        +------+------------------------+--------+
+               | campus | code | name                   | campus |
+              -+--------+------+------------------------+--------+-
+               | north  | eng  | School of Engineering  | north  |
+               | old    | art  | School of Art & Design | old    |
+               | south  | bus  | School of Business     | south  |
+
+               ----
+               /school^campus{*,top(^)}
+               SELECT `school_1`.`campus`,
+                      `school_2`.`code`,
+                      `school_2`.`name`,
+                      `school_2`.`campus`
+               FROM (SELECT `school`.`campus`
+                     FROM `school`
+                     WHERE (`school`.`campus` IS NOT NULL)
+                     GROUP BY 1) AS `school_1`
+                    INNER JOIN (SELECT `school`.`code`,
+                                       `school`.`name`,
+                                       `school`.`campus`
+                                FROM (SELECT `school`.`code`,
+                                             `school`.`name`,
+                                             `school`.`campus`,
+                                             @`!htsql:10:row_number` := IF((@`!htsql:10:partition:1` = `school`.`campus`), (@`!htsql:10:row_number` + 1), 1) AS `top_1`,
+                                             @`!htsql:10:partition:1` := `school`.`campus` AS `top_2`
+                                      FROM (SELECT `school`.`code`,
+                                                   `school`.`name`,
+                                                   `school`.`campus`
+                                            FROM `school`
+                                                 CROSS JOIN (SELECT TRUE AS `!`
+                                                             FROM (SELECT @`!htsql:10:row_number` := NULL AS `top_1`,
+                                                                          @`!htsql:10:partition:1` := NULL AS `top_2`) AS `!`) AS `!`
+                                            ORDER BY 3 ASC, 1 ASC) AS `school`) AS `school`
+                                WHERE (`school`.`top_1` >= 1)
+                                      AND (`school`.`top_1` < 2)) AS `school_2`
+                               ON (`school_1`.`campus` = `school_2`.`campus`)
+               ORDER BY 1 ASC
+          - uri: /class^{year, season} {*, /top(^.sort(count(enrollment)-),3).course{title}}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | class^{year,season}                              |
+               +------+--------+----------------------------------+
+               |      |        | course                           |
+               |      |        +----------------------------------+
+               | year | season | title                            |
+              -+------+--------+----------------------------------+-
+               | 2007 | fall   | College Algebra I                |
+               :      :        | History of Art Criticism I       |
+               :      :        | Language Basics                  |
+               | 2007 | spring | College Algebra II               |
+               :      :        | Introduction to Education        |
+               :      :        | Islam History                    |
+               | 2008 | fall   | College Algebra I                |
+               :      :        | Art in Therapy                   |
+               :      :        | History of Art Criticism I       |
+               | 2008 | spring | College Algebra II               |
+               :      :        | History of Computing             |
+               :      :        | Asian Architecture               |
+               | 2009 | fall   | Museum and Gallery Management    |
+               :      :        | Introduction to Biomedical       |
+               :      :        : Engineering                      :
+               :      :        | Bioengineering Seminar           |
+               | 2009 | spring | Introduction to Education        |
+               :      :        | Historical Perspective on the    |
+               :      :        : Constitution                     :
+               :      :        | College Algebra II               |
+               | 2010 | fall   | History of Art Criticism I       |
+               :      :        | Arts of Asia                     |
+               :      :        | Contemporary Latin American Art  |
+               | 2010 | spring | Practical Bookkeeping            |
+               :      :        | Asian Architecture               |
+               :      :        | Materials and Construction in    |
+               :      :        : European Art                     :
+
+               ----
+               /class^{year,season}{*,/top(^.sort(count(enrollment)-),3).course{title}}
+               SELECT `class`.`year`,
+                      `class`.`season`
+               FROM `class`
+               WHERE (`class`.`year` IS NOT NULL)
+                     AND (`class`.`season` IS NOT NULL)
+               GROUP BY 1, 2
+               ORDER BY 1 ASC, 2 ASC
+
+                 SELECT `course`.`title`,
+                        `class_1`.`year`,
+                        `class_1`.`season`
+                 FROM (SELECT `class`.`year`,
+                              `class`.`season`
+                       FROM `class`
+                       WHERE (`class`.`year` IS NOT NULL)
+                             AND (`class`.`season` IS NOT NULL)
+                       GROUP BY 1, 2) AS `class_1`
+                      INNER JOIN (SELECT `class`.`department_code`,
+                                         `class`.`course_no`,
+                                         `class`.`year`,
+                                         `class`.`season`,
+                                         `class`.`section`
+                                  FROM (SELECT `class`.`department_code`,
+                                               `class`.`course_no`,
+                                               `class`.`year`,
+                                               `class`.`season`,
+                                               `class`.`section`,
+                                               @`!htsql:15:row_number` := IF(((@`!htsql:15:partition:1` = `class`.`year`) AND (@`!htsql:15:partition:2` = `class`.`season`)), (@`!htsql:15:row_number` + 1), 1) AS `top_1`,
+                                               @`!htsql:15:partition:1` := `class`.`year` AS `top_2`,
+                                               @`!htsql:15:partition:2` := `class`.`season` AS `top_3`
+                                        FROM (SELECT `class`.`department_code`,
+                                                     `class`.`course_no`,
+                                                     `class`.`year`,
+                                                     `class`.`season`,
+                                                     `class`.`section`
+                                              FROM `class`
+                                                   LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                                           `enrollment`.`class_seq`
+                                                                    FROM `enrollment`
+                                                                    GROUP BY 2) AS `enrollment`
+                                                                   ON (`class`.`class_seq` = `enrollment`.`class_seq`)
+                                                   CROSS JOIN (SELECT TRUE AS `!`
+                                                               FROM (SELECT @`!htsql:15:row_number` := NULL AS `top_1`,
+                                                                            @`!htsql:15:partition:1` := NULL AS `top_2`,
+                                                                            @`!htsql:15:partition:2` := NULL AS `top_3`) AS `!`) AS `!`
+                                              ORDER BY 3 ASC, 4 ASC, COALESCE(`enrollment`.`count`, 0) DESC, 1 ASC, 2 ASC, 5 ASC) AS `class`) AS `class`
+                                  WHERE (`class`.`top_1` >= 1)
+                                        AND (`class`.`top_1` < 4)) AS `class_2`
+                                 ON ((`class_1`.`year` = `class_2`.`year`) AND (`class_1`.`season` = `class_2`.`season`))
+                      INNER JOIN `course`
+                                 ON ((`class_2`.`department_code` = `course`.`department_code`) AND (`class_2`.`course_no` = `course`.`no`))
+                      INNER JOIN (SELECT COALESCE(`class`.`count`, 0) AS `count`,
+                                         `class`.`department_code`,
+                                         `class`.`course_no`,
+                                         `class`.`year`,
+                                         `class`.`season`,
+                                         `class`.`section`
+                                  FROM (SELECT `class`.`count`,
+                                               `class`.`department_code`,
+                                               `class`.`course_no`,
+                                               `class`.`year`,
+                                               `class`.`season`,
+                                               `class`.`section`,
+                                               @`!htsql:41:row_number` := IF(((@`!htsql:41:partition:1` = `class`.`year`) AND (@`!htsql:41:partition:2` = `class`.`season`)), (@`!htsql:41:row_number` + 1), 1) AS `top_1`,
+                                               @`!htsql:41:partition:1` := `class`.`year` AS `top_2`,
+                                               @`!htsql:41:partition:2` := `class`.`season` AS `top_3`
+                                        FROM (SELECT `enrollment`.`count`,
+                                                     `class`.`department_code`,
+                                                     `class`.`course_no`,
+                                                     `class`.`year`,
+                                                     `class`.`season`,
+                                                     `class`.`section`
+                                              FROM `class`
+                                                   LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                                                           `enrollment`.`class_seq`
+                                                                    FROM `enrollment`
+                                                                    GROUP BY 2) AS `enrollment`
+                                                                   ON (`class`.`class_seq` = `enrollment`.`class_seq`)
+                                                   CROSS JOIN (SELECT TRUE AS `!`
+                                                               FROM (SELECT @`!htsql:41:row_number` := NULL AS `top_1`,
+                                                                            @`!htsql:41:partition:1` := NULL AS `top_2`,
+                                                                            @`!htsql:41:partition:2` := NULL AS `top_3`) AS `!`) AS `!`
+                                              ORDER BY 4 ASC, 5 ASC, COALESCE(`enrollment`.`count`, 0) DESC, 2 ASC, 3 ASC, 6 ASC) AS `class`) AS `class`
+                                  WHERE (`class`.`top_1` >= 1)
+                                        AND (`class`.`top_1` < 4)) AS `class_3`
+                                 ON ((`class_2`.`department_code` = `class_3`.`department_code`) AND (`class_2`.`course_no` = `class_3`.`course_no`) AND (`class_2`.`year` = `class_3`.`year`) AND (`class_2`.`season` = `class_3`.`season`) AND (`class_2`.`section` = `class_3`.`section`))
+                 ORDER BY 2 ASC, 3 ASC, `class_3`.`count` DESC, `class_2`.`department_code` ASC, `class_2`.`course_no` ASC, `class_2`.`year` ASC, `class_2`.`season` ASC, `class_2`.`section` ASC
         - id: table-expressions
           tests:
           - uri: /(school?code='art').department
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.