Commits

Kirill Simonov committed ddaaca2 Merge

Merged updated README and the regression schema.

Comments (0)

Files changed (4)

 HTSQL -- A Query Language for the Accidental Programmer
 =======================================================
 
-HTSQL ("Hyper Text Structured Query Language") is a schema-driven URI-to-SQL
-translator that takes a request over HTTP, converts it to a SQL query,
-executes the query against a database, and returns the results in a format
-best suited for the user agent (CSV, HTML, etc.).
+HTSQL ("Hyper Text Structured Query Language") is a schema-driven
+URI-to-SQL translator that takes a request over HTTP, converts it to a
+SQL query, executes the query against a database, and returns the
+results in a format best suited for the user agent (CSV, HTML, etc.).
 
-HTSQL is copyright by Prometheus Research, LLC.  See the file ``LICENSE``
-for details.  HTSQL is written by Clark C. Evans <cce@clarkevans.com> and
-Kirill Simonov <xi@resolvent.net>.
+Use of HTSQL with open source databases (PostgreSQL, MySQL, SQLite) is
+royalty free under BSD-style conditions.  Usage of HTSQL with
+proprietary database systems (Oracle, Microsoft SQL) requires a
+commercial license.  See ``LICENSE`` for details.
 
-For installation instructions, see ``INSTALL``.  For list of new features
-in this release, see ``NEWS``.  HTSQL documentation is in the ``doc``
-directory.
+For installation instructions, see ``INSTALL``.  For list of new
+features in this release, see ``NEWS``.  HTSQL documentation is in the
+``doc`` directory.  
 
 Visit
 
     http://htsql.org/
         The HTSQL homepage;
 
+    http://htsql.org/tutorial.html
+        The HTSQL tutorial;
+
     http://bitbucket.org/prometheus/htsql
         HTSQL source code;
 
-    #htsql on freenode
-        Our IRC channel;
+    irc://irc.freenode.net#htsql
+        IRC chat in #htsql on freenode;
 
     http://lists.htsql.org/mailman/listinfo/htsql-users
         The mailing list for users of HTSQL.
 
+Generous support for HTSQL was provided by Prometheus Research, LLC and
+The Simons Foundation. This material is also based upon work supported
+by the National Science Foundation under Grant #0944460. Any opinions,
+findings, and conclusions or recommendations expressed in this material
+are those of the author(s) and do not necessarily reflect the views of
+the National Science Foundation. 
+
+HTSQL is copyright by Prometheus Research, LLC.  HTSQL is written by
+Clark C. Evans <cce@clarkevans.com> and Kirill Simonov <xi@resolvent.net>.
+

src/htsql/tr/fn/function.py

         return "(%s || %s)" % (left, right)
 
     def concat_wrapper(self, expr):
-        return "COALESCE(%s, '')"
+        return "COALESCE(%s, '')" % expr
 
     def count_fn(self, condition):
         return "COUNT(NULLIF(%s, FALSE))" % condition

test/output/pgsql.yaml

          | code   | name                   | school |
         -+--------+------------------------+--------+-
          | acc    | Accounting             | bus    |
+         | alumni | Alumni & Parents       |        |
          | arthis | Art History            | art    |
          | artstd | Studio Art             | art    |
          | astro  | Astronomy              | ns     |
          | be     | Bioengineering         | egn    |
+         | bursar | Bursar's Office        |        |
          | capmrk | Capital Markets        | bus    |
+         | career | Career Development     |        |
          | chem   | Chemistry              | ns     |
          | comp   | Computer Science       | egn    |
          | corpfi | Corporate Finance      | bus    |
          | tched  | Teacher Education      | edu    |
          | voc    | Vocals                 | mus    |
          | win    | Wind                   | mus    |
-                                            (24 rows)
+                                            (27 rows)
 
          ----
          /department
 
          ----
          /department?school.code='ns'
-         SELECT "department"."code", "department"."name", "department"."school" FROM "ad"."department" AS "department" INNER JOIN "ad"."school" AS "school_2" ON (("department"."school" = "school_2"."code")) WHERE ("school_2"."code" = 'ns') ORDER BY 1 ASC
+         SELECT "department"."code", "department"."name", "department"."school" FROM "ad"."department" AS "department" LEFT OUTER JOIN "ad"."school" AS "school_2" ON (("department"."school" = "school_2"."code")) WHERE ("school_2"."code" = 'ns') ORDER BY 1 ASC
     - uri: /program?school.code='ns'&code='uchem'
       status: 200 OK
       headers:
          | school.code | school.name                      | code   | name                   | school |
         -+-------------+----------------------------------+--------+------------------------+--------+-
          | bus         | School of Business               | acc    | Accounting             | bus    |
+         |             |                                  | alumni | Alumni & Parents       |        |
          | art         | School of Art and Design         | arthis | Art History            | art    |
          | art         | School of Art and Design         | artstd | Studio Art             | art    |
          | ns          | School of Natural Sciences       | astro  | Astronomy              | ns     |
          | egn         | School of Engineering            | be     | Bioengineering         | egn    |
+         |             |                                  | bursar | Bursar's Office        |        |
          | bus         | School of Business               | capmrk | Capital Markets        | bus    |
+         |             |                                  | career | Career Development     |        |
          | ns          | School of Natural Sciences       | chem   | Chemistry              | ns     |
          | egn         | School of Engineering            | comp   | Computer Science       | egn    |
          | bus         | School of Business               | corpfi | Corporate Finance      | bus    |
          | edu         | College of Education             | tched  | Teacher Education      | edu    |
          | mus         | Musical School                   | voc    | Vocals                 | mus    |
          | mus         | Musical School                   | win    | Wind                   | mus    |
-                                                                                             (24 rows)
+                                                                                             (27 rows)
 
          ----
          /department{school.*,*}
-         SELECT "school_2"."code", "school_2"."name", "department"."code", "department"."name", "department"."school" FROM "ad"."department" AS "department" INNER JOIN "ad"."school" AS "school_2" ON (("department"."school" = "school_2"."code")) ORDER BY 3 ASC
+         SELECT "school_2"."code", "school_2"."name", "department"."code", "department"."name", "department"."school" FROM "ad"."department" AS "department" LEFT OUTER JOIN "ad"."school" AS "school_2" ON (("department"."school" = "school_2"."code")) ORDER BY 3 ASC
     - uri: /department{school.name+' - '+name}
       status: 200 OK
       headers:
          | school.name+' - '+name           |
         -+----------------------------------+-
          | School of Business - Accounting  |
+         | " - Alumni & Parents"            |
          | School of Art and Design - Art   |
          : History                          :
          | School of Art and Design -       |
          : Astronomy                        :
          | School of Engineering -          |
          : Bioengineering                   :
+         | " - Bursar's Office"             |
          | School of Business - Capital     |
          : Markets                          :
+         | " - Career Development"          |
          | School of Natural Sciences -     |
          : Chemistry                        :
          | School of Engineering - Computer |
          : Education                        :
          | Musical School - Vocals          |
          | Musical School - Wind            |
-                                    (24 rows)
+                                    (27 rows)
 
          ----
          /department{school.name+' - '+name}
-         SELECT (("school"."name" || ' - ') || "department"."name") FROM "ad"."department" AS "department" INNER JOIN "ad"."school" AS "school" ON (("department"."school" = "school"."code")) ORDER BY "department"."code" ASC
+         SELECT (COALESCE((COALESCE("school"."name", '') || ' - '), '') || "department"."name") FROM "ad"."department" AS "department" LEFT OUTER JOIN "ad"."school" AS "school" ON (("department"."school" = "school"."code")) ORDER BY "department"."code" ASC
   - id: aggregates
     tests:
     - uri: /{count(school),count(department),count(course)}
         -+---------------------------------------------------+-
          | count(school) | count(department) | count(course) |
         -+---------------+-------------------+---------------+-
-         |             9 |                24 |           100 |
+         |             9 |                27 |           100 |
                                                        (1 row)
 
          ----
         -+------------------------------------------------------+-
          | count(department) | count(department?exists(course)) |
         -+-------------------+----------------------------------+-
-         |                24 |                               20 |
+         |                27 |                               20 |
                                                           (1 row)
 
          ----
          | code   | count(course{credits=3}) |
         -+--------+--------------------------+-
          | acc    |                        4 |
+         | alumni |                        0 |
          | arthis |                        5 |
          | artstd |                        2 |
          | astro  |                        2 |
          | be     |                        3 |
+         | bursar |                        0 |
          | capmrk |                        4 |
+         | career |                        0 |
          | chem   |                        4 |
          | comp   |                        6 |
          | corpfi |                        3 |
          | tched  |                        4 |
          | voc    |                        0 |
          | win    |                        0 |
-                                     (24 rows)
+                                     (27 rows)
 
          ----
          /department{code,count(course{credits=3})}
       body: |2
          | (department?every(course.credits=5)) |
         -+--------------------------------------+-
-         | code    | name           | school    |
-        -+---------+----------------+-----------+-
-         | mth     | Mathematics    | ns        |
-         | pia     | Piano          | mus       |
-         | str     | Strings        | mus       |
-         | voc     | Vocals         | mus       |
-         | win     | Wind           | mus       |
-                                         (5 rows)
+         | code   | name               | school |
+        -+--------+--------------------+--------+-
+         | alumni | Alumni & Parents   |        |
+         | bursar | Bursar's Office    |        |
+         | career | Career Development |        |
+         | mth    | Mathematics        | ns     |
+         | pia    | Piano              | mus    |
+         | str    | Strings            | mus    |
+         | voc    | Vocals             | mus    |
+         | win    | Wind               | mus    |
+                                         (8 rows)
 
          ----
          /department?every(course.credits=5)
          | code   | min(course.credits) | max(course.credits) |
         -+--------+---------------------+---------------------+-
          | acc    |                   2 |                   5 |
+         | alumni |                     |                     |
          | arthis |                   3 |                   4 |
          | artstd |                   0 |                   5 |
          | astro  |                   1 |                   3 |
          | be     |                   3 |                   8 |
+         | bursar |                     |                     |
          | capmrk |                   3 |                   3 |
+         | career |                     |                     |
          | chem   |                   2 |                   3 |
          | comp   |                   3 |                   4 |
          | corpfi |                   3 |                   3 |
          | tched  |                   3 |                   4 |
          | voc    |                     |                     |
          | win    |                     |                     |
-                                                      (24 rows)
+                                                      (27 rows)
 
          ----
          /department{code,min(course.credits),max(course.credits)}
          | code   | avg(course.credits) | sum(course.credits)/count(course.credits) |
         -+--------+---------------------+-------------------------------------------+-
          | acc    |  3.1666666666666667 |                        3.1666666666666667 |
+         | alumni |                     |                                           |
          | arthis |  3.1666666666666667 |                        3.1666666666666667 |
          | artstd |  3.1666666666666667 |                        3.1666666666666667 |
          | astro  |  2.2500000000000000 |                        2.2500000000000000 |
          | be     |  4.2500000000000000 |                        4.2500000000000000 |
+         | bursar |                     |                                           |
          | capmrk |  3.0000000000000000 |                        3.0000000000000000 |
+         | career |                     |                                           |
          | chem   |  2.8000000000000000 |                        2.8000000000000000 |
          | comp   |  3.1428571428571429 |                        3.1428571428571429 |
          | corpfi |  3.0000000000000000 |                        3.0000000000000000 |
          | tched  |  3.2000000000000000 |                        3.2000000000000000 |
          | voc    |                     |                                           |
          | win    |                     |                                           |
-                                                                            (24 rows)
+                                                                            (27 rows)
 
          ----
          /department{code,avg(course.credits),sum(course.credits)/count(course.credits)}

test/sql/regress-pgsql.sql

 There are two top-level tables, ``department`` and ``school`` having a
 single-column primary key ``code`` and a unique ``name``.  In this
 schema, we associate departments with exactly one school, although the
-``code`` for the department must be unique across schools.
+``code`` for the department must be unique across schools.  Non-acedemic
+departments are modeled with a ``NULL`` for their ``school``.
 
 Two second-tier tables, ``course`` and ``program`` have compound primary
 keys, consisting of a parent table and a second column.  For ``course``
   | DEPARTMENT         |              | SCHOOL              |     
   +--------------------+              +---------------------+     
   | code            PK |--\       /---| code             PK |----\
-  | school_code  NN,FK |>-|------/    | name          NN,UK |    |
+  | school          FK |>-|------/    | name          NN,UK |    |
   | name         NN,UK |  |    .      +---------------------+    |
   +--------------------+  |     .                              . |
                         . |  departments                      .  |
        a department    .  |  belong to                       .   |
-       offers one     .   |  exactly one       a school          |
-       or more course     |  school            administers one   |
+       offers zero or .   |  at most one       a school          |
+       more courses       |  school            administers zero  |
                           |                    or more programs  |
   +--------------------+  |                                      |
   | COURSE             |  |           +---------------------+    |
 CREATE TABLE ad.department (
     code        VARCHAR(16) NOT NULL,
     name        VARCHAR(64) NOT NULL,
-    school      VARCHAR(16) NOT NULL,
+    school      VARCHAR(16),
     CONSTRAINT department_pk
       PRIMARY KEY (code),
     CONSTRAINT department_name_uk
 ('voc', 'Vocals', 'mus'),
 ('pia', 'Piano', 'mus'),
 ('win', 'Wind', 'mus'),
-('str', 'Strings', 'mus')
+('str', 'Strings', 'mus'),
+-- Non-Academic Departments
+('alumni', 'Alumni & Parents', NULL),
+('bursar', 'Bursar''s Office', NULL),
+('career', 'Career Development', NULL)
 ;
 
 INSERT INTO ad.program (school, code, title, degree) VALUES