Commits

Kirill Simonov committed cdb5fbd

Added `EXAMPLES` with examples of HTSQL and corresponding SQL queries;
included `EXAMPLES` to the long description of the package.

Comments (0)

Files changed (2)

+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;
+
+
+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;
+
+
+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 the astronomy department, the number of courses from 100 to 300
+-------------------------------------------------------------------
+
+HTSQL::
+
+    /department{name, count(course?number>=100&number<300)}?code='astro'
+
+SQL::
+
+    SELECT d.name, COALESCE(c.cnt, 0)
+    FROM ad.department AS d
+    LEFT OUTER JOIN
+         (SELECT COUNT(TRUE) AS cnt, c.department
+          FROM ad.course AS c
+          WHERE (c.number >= 100) AND (c.number < 300)
+          GROUP BY 2) AS c ON (d.code = c.department)
+    WHERE d.code = 'astro'
+    ORDER BY d.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"