Commits

Clark C. Evans  committed 272de63 Merge

merged /w othogonal code changes

  • Participants
  • Parent commits 09b5b0f, 5b7d98a

Comments (0)

Files changed (4)

+Examples
+========
+
+Let's assume we have a data model, with schools, departments, programs and
+courses.  Here it is::
+
+         +-------------+       +--------+
+    /---m| DEPARTMENT  |>-----o| SCHOOL |m----\
+    |.   +-------------+  .    +--------+    .|
+    | .                  .                  . |
+    |   department   department    a school   |
+    |   offers       may be part   has one or |
+    |   courses      of school     programs   |
+    |                                         |
+    |    +-------------+       +---------+    |
+    \---<| COURSE      |       | PROGRAM |>---/
+         +-------------+       +---------+
+
+
+List all schools
+----------------
+
+An HTSQL query::
+
+    /school
+
+An equivalent SQL query::
+
+    SELECT code, name
+    FROM ad.school
+    ORDER BY code;
+
+
+Programs ordered by the title
+-----------------------------
+
+HTSQL::
+
+    /program{title+}
+
+SQL::
+
+    SELECT title
+    FROM ad.program
+    ORDER BY title, school, code;
+
+
+All courses missing a description
+---------------------------------
+
+HTSQL::
+
+    /course?!description
+
+SQL::
+
+    SELECT department, number, title, credits, description
+    FROM ad.course
+    WHERE NULLIF(description, '') IS NULL
+    ORDER BY 1, 2;
+
+
+Departments that belong to any school with "art" in its name
+------------------------------------------------------------
+
+HTSQL::
+
+    /department?school.name~'art'
+
+SQL::
+
+    SELECT d.code, d.name, d.school
+    FROM ad.department AS d
+    LEFT OUTER JOIN
+         ad.school AS s ON (d.school = s.code)
+    WHERE s.name ILIKE '%art%'
+    ORDER BY 1;
+
+
+The number of schools
+---------------------
+
+HTSQL::
+
+    /count(school)
+
+SQL::
+
+    SELECT COUNT(TRUE)
+    FROM ad.school;
+
+
+Schools with programs
+---------------------
+
+HTSQL::
+
+    /school?exists(program)
+
+SQL::
+
+    SELECT s.code, s.name
+    FROM ad.school AS s
+    WHERE EXISTS(SELECT TRUE
+                 FROM ad.program AS p
+                 WHERE s.code = p.school)
+    ORDER BY 1;
+
+
+The number of schools with programs
+-----------------------------------
+
+HTSQL::
+
+    /count(school?exists(program))
+
+SQL::
+
+    SELECT COUNT(TRUE)
+    FROM ad.school AS s
+    WHERE EXISTS(SELECT TRUE
+                 FROM ad.program AS p
+                 WHERE (s.code = p.school));
+
+
+For each school, the number of programs and the number of departments
+---------------------------------------------------------------------
+
+HTSQL::
+
+    /school{name, count(program), count(department)}
+
+SQL::
+
+    SELECT s.name, COALESCE(p.cnt, 0), COALESCE(d.cnt, 0)
+    FROM ad.school AS s
+    LEFT OUTER JOIN
+         (SELECT COUNT(TRUE) AS cnt, p.school
+          FROM ad.program AS p
+          GROUP BY 2) AS p ON (s.code = p.school)
+    LEFT OUTER JOIN
+         (SELECT COUNT(TRUE) AS cnt, d.school
+          FROM ad.department AS d
+          GROUP BY 2) AS d ON (s.code = d.school)
+    ORDER BY s.code;
+
+
+For each school, the average number of courses offered by the departments
+-------------------------------------------------------------------------
+
+HTSQL::
+
+    /school{name, avg(department.count(course))}
+
+SQL::
+
+    SELECT s.name, d.av
+    FROM ad.school AS s
+    LEFT OUTER JOIN
+         (SELECT AVG(CAST(COALESCE(c.cnt, 0) AS NUMERIC)) AS av, d.school
+          FROM ad.department AS d
+          LEFT OUTER JOIN
+               (SELECT COUNT(TRUE) AS cnt, c.department
+               FROM ad.course AS c
+               GROUP BY 2) AS c ON (d.code = c.department)
+          GROUP BY 2) AS d ON (s.code = d.school)
+    ORDER BY s.code;
+
+
 from setuptools import setup, find_packages
 import os.path
 
-# We use the merged content of `README` and `NEWS` as the long
-# description of the package.
+# We use the merged content of `README`, `EXAMPLES` and `NEWS` as the
+# long description of the package.
 
 root = os.path.dirname(__file__)
 README = open(os.path.join(root, 'README')).read()
+EXAMPLES = open(os.path.join(root, 'EXAMPLES')).read()
 NEWS = open(os.path.join(root, 'NEWS')).read()
 
 # The distutils parameters are defined here.  Do not forget to update
 NAME = "HTSQL"
 VERSION = "2.0.0b2"
 DESCRIPTION = "Query language for the accidental programmer"
-LONG_DESCRIPTION = "\n".join([README, NEWS])
+LONG_DESCRIPTION = "\n".join([README, EXAMPLES, NEWS])
 AUTHOR = "Clark C. Evans and Kirill Simonov; Prometheus Research, LLC"
 AUTHOR_EMAIL = "cce@clarkevans.com"
 LICENSE = "Free To Use But Restricted"

File src/htsql/tr/fn/function.py

 from ..assemble import Evaluate
 from ..reduce import Reduce
 from ..frame import (FunctionPhrase, IsNullPhrase, NullIfPhrase, IfNullPhrase,
-                     LiteralPhrase, TruePhrase, FalsePhrase)
+                     LiteralPhrase, TruePhrase, FalsePhrase, NullPhrase)
 from ..serializer import Serializer, Format, Serialize
 from ..coerce import coerce
 from ..lookup import lookup
     def correlate(self, this, ops):
         domain = coerce(this.domain, *(op.domain for op in ops))
         if domain is None:
-            raise InvalidArgumentError("unexpected domain", op.mark)
+            raise InvalidArgumentError("unexpected domain", self.syntax.mark)
         this = CastBinding(this, domain, this.syntax)
         ops = [CastBinding(op, domain, op.syntax) for op in ops]
         yield IfNullBinding(domain, self.syntax, this=this, ops=ops)
         ContainsExpression, ContainsPhrase)
 ReduceContains = GenericReduce.factory(ContainsOperator,
         ContainsPhrase)
-SerializeContains = GenericSerialize.factory(ContainsOperator,
-        ContainsPhrase, "(POSITION(LOWER(%(right)s) IN LOWER(%(left)s)) > 0)")
+
+
+class SerializeContains(Serialize):
+
+    adapts(ContainsPhrase, Serializer)
+
+    def serialize(self):
+        left = self.serializer.serialize(self.phrase.left)
+        if isinstance(self.phrase.right, NullPhrase):
+            return self.serializer.serialize(self.phrase.right)
+        if isinstance(self.phrase.right, LiteralPhrase):
+            value = self.phrase.right.value
+            value = value.replace('%', '\\%').replace('_', '\\_')
+            value = "%%%s%%" % value
+            right = LiteralPhrase(value, self.phrase.right.domain,
+                                  self.phrase.right.expression)
+            right = self.serializer.serialize(right)
+            return self.format.ilike(left, right)
+        else:
+            right = self.serializer.serialize(self.phrase.right)
+            return self.format.contains(left, right)
 
 
 class FormatFunctions(Format):
     def not_among(self, lop, rops):
         return "(%s NOT IN (%s))" % (lop, ", ".join(rops))
 
+    def contains(self, left, right):
+        return "(POSITION(LOWER(%(right)s) IN LOWER(%(left)s)) > 0)" \
+                % (left, right)
+
+    def ilike(self, left, right):
+        return "(%s ILIKE %s)" % (left, right)
+
     def round_fn(self, value, digits=None):
         if digits is None:
             return "ROUND(%s)" % value

File test/output/pgsql.yaml

 
              ----
              /{'HTSQL'~'sql','HTSQL'~'HTTP','HTSQL'~'','HTSQL'~null(),null()~'HTSQL',null()~null()}
-             SELECT (POSITION(LOWER('sql') IN LOWER('HTSQL')) > 0), (POSITION(LOWER('HTTP') IN LOWER('HTSQL')) > 0), (POSITION(LOWER('') IN LOWER('HTSQL')) > 0), (POSITION(LOWER(NULL) IN LOWER('HTSQL')) > 0), (POSITION(LOWER('HTSQL') IN LOWER(NULL)) > 0), (POSITION(LOWER(NULL) IN LOWER(NULL)) > 0)
+             SELECT ('HTSQL' ILIKE '%sql%'), ('HTSQL' ILIKE '%HTTP%'), ('HTSQL' ILIKE '%%'), NULL, (NULL ILIKE '%HTSQL%'), NULL
       - id: date-functions-and-operators
         tests:
         - uri: /{date('2010-07-28'),date(string('2010-07-28'))}
 
          ----
          /school{code,name,this(){code}?name!~'art',root().school{code}?name!~'art'}
-         SELECT "school_1"."code", "school_1"."name", "school_2"."code", "school_2"."code" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT "school"."code" FROM "ad"."school" AS "school" WHERE (NOT (POSITION(LOWER('art') IN LOWER("school"."name")) > 0))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY 1 ASC
+         SELECT "school_1"."code", "school_1"."name", "school_2"."code", "school_2"."code" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT "school"."code" FROM "ad"."school" AS "school" WHERE (NOT ("school"."name" ILIKE '%art%'))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY 1 ASC
     - uri: /school{name,count(department),this(){count(department)}?name!~'art'}
       status: 200 OK
       headers:
 
          ----
          /school{name,count(department),this(){count(department)}?name!~'art'}
-         SELECT "school_1"."name", COALESCE("department"."!", 0), "school_2"."!" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "!", "department"."school" FROM "ad"."department" AS "department" GROUP BY 2) AS "department" ON (("school_1"."code" = "department"."school")) LEFT OUTER JOIN (SELECT COALESCE("department"."!", 0) AS "!", "school"."code" FROM "ad"."school" AS "school" LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "!", "department"."school" FROM "ad"."department" AS "department" GROUP BY 2) AS "department" ON (("school"."code" = "department"."school")) WHERE (NOT (POSITION(LOWER('art') IN LOWER("school"."name")) > 0))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY "school_1"."code" ASC
+         SELECT "school_1"."name", COALESCE("department"."!", 0), "school_2"."!" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "!", "department"."school" FROM "ad"."department" AS "department" GROUP BY 2) AS "department" ON (("school_1"."code" = "department"."school")) LEFT OUTER JOIN (SELECT COALESCE("department"."!", 0) AS "!", "school"."code" FROM "ad"."school" AS "school" LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "!", "department"."school" FROM "ad"."department" AS "department" GROUP BY 2) AS "department" ON (("school"."code" = "department"."school")) WHERE (NOT ("school"."name" ILIKE '%art%'))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY "school_1"."code" ASC
     - uri: /school{name,exists(department),this(){exists(department)}?name!~'art'}
       status: 200 OK
       headers:
 
          ----
          /school{name,exists(department),this(){exists(department)}?name!~'art'}
-         SELECT "school_1"."name", EXISTS((SELECT TRUE AS "!" FROM "ad"."department" AS "department" WHERE ("school_1"."code" = "department"."school"))), "school_2"."!" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT EXISTS((SELECT TRUE AS "!" FROM "ad"."department" AS "department" WHERE ("school"."code" = "department"."school"))) AS "!", "school"."code" FROM "ad"."school" AS "school" WHERE (NOT (POSITION(LOWER('art') IN LOWER("school"."name")) > 0))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY "school_1"."code" ASC
+         SELECT "school_1"."name", EXISTS((SELECT TRUE AS "!" FROM "ad"."department" AS "department" WHERE ("school_1"."code" = "department"."school"))), "school_2"."!" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT EXISTS((SELECT TRUE AS "!" FROM "ad"."department" AS "department" WHERE ("school"."code" = "department"."school"))) AS "!", "school"."code" FROM "ad"."school" AS "school" WHERE (NOT ("school"."name" ILIKE '%art%'))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY "school_1"."code" ASC
   - id: table-expressions
     tests:
     - uri: /(school?code='art').department
 
          ----
          /course?title~'lab'
-         SELECT "course"."department", "course"."number", "course"."title", "course"."credits", "course"."description" FROM "ad"."course" AS "course" WHERE (POSITION(LOWER('lab') IN LOWER("course"."title")) > 0) ORDER BY 1 ASC, 2 ASC
+         SELECT "course"."department", "course"."number", "course"."title", "course"."credits", "course"."description" FROM "ad"."course" AS "course" WHERE ("course"."title" ILIKE '%lab%') ORDER BY 1 ASC, 2 ASC
     - uri: /course?title!= 'Organic Chemistry Laboratory I'
       status: 200 OK
       headers: