HTSQL

Hyper Text Structured Query Language

Clark C. Evans, Kirill Simonov
Prometheus Research LLC

An approch to schema-driven URL-to-SQL translation for rapid application development and custom report writing.

What is HTSQL

HTSQL is a schema-driven URL-to-SQL translator and query processing environment


/----------------\  HTTP Request    /------------------------\ 
| Web Browser    | ---------------> --.                      |
|  * Direct URLs |   URI, headers,  |  \      .--> Generated |
|  * HTML / XSLT |   post/put body  |   .    /     SQL Query |
|  * Javascript  |                  |   HTSQL         |      |
| GUI Front End  |  HTTP Response   |   /  ^          v      |
|  * Java / C#   | <--------------  <--.    \       DATABASE |
|  * Python, etc |   status code,   |    Query        |      |
| Excel /w Macro |   headers, and   |    Results <..../      |
| Command Line   |   formatted      |                        |
\----------------/   results        \------------------------/  

HTSQL & XSLT

/organization{*, division_of.*}/*project/*participation{*, person.*}.xml(stylesheet='/-/organization-project-person.xsl')

Goals for HTSQL

Walk Through

  1. Basic Queries
  2. Selectors and Functions
  3. Specifiers and Locators
  4. Aggregates and Projections
  5. Commands, Formatters
  6. Path and Forest Queries

Wrap-Up

+-------------------+              +-------------------+
| OP.PROJECT        |              | OP.ORGANIZATION   |
+-------------------+              +-------------------+
| prj_id         PK |--\       /---| org_id         PK |---\ ---\
| name        NN,UK |  |       |   | name           NN |   |    |
| status      NN,CK |  |       |   | is_active         |   |    |
| client         FK |>---------/   | division_of    FK |>--/    |
| start_date        |  |    .      +-------------------+  .     |
| description       |  |     .                           .      |
+-------------------+  |   project is        an organization    |
                      .|   related to        may be a division  |
  a project has zero . |   at most one       of a larger        |
  or more people who   |   organization      organization       |
  participate in it    |                                        |
                       |     +-------------------+              |
+-------------------+  |     | OP.PERSON         |              |
| OP.PARTICIPATION  |  |     +-------------------+              |
+-------------------+  |     | org_id     FK,PK1 |>-------------/
| project    FK,PK1 |>-/     | nickname   NN,PK2 |    .
| _ppl_seq   FK,PK2 |>-------| _ppl_seq    NN,UK |   .
| billing_rate      |   .    | full_name      NN |  each person
+-------------------+  .     | email          UK |  is part of
                      .      +-------------------+  exactly one
  a person participates in                          organization
  zero or more projects
                       .
PK - Primary Key      FK - Foreign Key          NN - Not NULL
UK - Unique Key       CK - Check Constraint

/organization

SELECT * FROM op.organization
ORDER BY org_id;
op:organization
---------------------------------------------------------------
org_id      | name                    | is_active | division_of
------------+-------------------------+-----------+------------
acorn       | Acorn Architecture      | true      |
lake-apts   | Lake Shore Apartments   | true      | lakeside
lake-carmen | Lake Carmen Towers      | true      | lakeside
lakeside    | Lake Side Partners, LLC |           |
meyers      | Meyers Construction     | true      |
meyers_elec | Meyers Electric         | false     | meyers
smith       | Rudgen, Taupe, & Smith  | false     |

/person?nickname='jack'

SELECT * FROM op.person
WHERE nickname = 'jack'
ORDER BY org_id,  nickname
op:person
------------------------------------------------------------
org_id | nickname | full_name      | email
-------+----------+----------------+------------------------
meyers | jack     | Jack C. Meyers | jack.meyers@example.com
smith  | jack     | Jack Taupe     | jack.taupe@example.com

/project?status!='abandoned'&name~'roof'

SELECT * FROM op.project
WHERE status != 'abandoned'
  AND LOWER(name) LIKE '%roof%'
ORDER BY prj_id;
project
-------------------------------------------------------+ ...
prj_id | name                            | status      |
-------+---------------------------------+-------------+ ...
la-334 | Siding / roof at 334 Ocean Blvd | completed   |
lt-802 | Toaster Re-Do and Roof Leak     | in-progress |

/project?status='planned'|status='in-progress'

SELECT * FROM op.project
 WHERE status = 'planned'
    OR status = 'in-progress'
ORDER BY proj_id
project
--------------------------------------------------+ ...
prj_id   | status      | client      | start_date |
---------+-------------+-------------+------------+ ...
lt-711   | planned     | lake-carmen |            |
lt-802   | in-progress | lake-carmen | 2006-11-23 |
overhead | in-progress |             | 2003-06-12 |
...

/organization?is_active&division_of!=='meyers'

SELECT * FROM op.organization
WHERE is_active
  AND division_of IS DISTINCT FROM 'meyers'
ORDER BY org_id;
organization
------------------------------------------------------------
org_id      | name                | is_active | division_of
------------+---------------------+-----------+-------------
acorn       | Acorn Architecture  | true      |
lake-carmen | Lake Carmen Towers  | true      | lakeside
meyers      | Meyers Construction | true      |
shoe        | Rwyler's Shoes      | true      |

/project?client->!start_date

SELECT * FROM op.project
 WHERE NOT (client IS NOT NULL AND client != '')
    OR (NOT (start_date IS NOT NULL))
ORDER BY prj_id
project
-------------------------------------------------------------
prj_id   | client      | start_date | name
---------+-------------+------------+------------------------
lt-711   | lake-carmen |            | Updating Fire Escape
overhead |             | 2003-06-12 | General Management Work

/project?(start_date<'2004-04-01'|start_date>'2006-09-01')&client

SELECT * FROM op.project
 WHERE (start_date < '2004-04-01'
     OR start_date > '2006-09-01')
   AND (client IS NOT NULL AND client != '')
ORDER BY prj_id
project
-----------------------------------------------
prj_id | start_date | client      | status
-------+------------+-------------+------------
la-102 | 2004-03-27 | lake-apts   | completed
lt-802 | 2006-11-23 | lake-carmen | in-progress

/project{status+,start_date-,name}

SELECT status, start_date, name
FROM op.project
ORDER BY status ASC, start_date DESC, prj_id ASC
project
-----------------------------------------------------------
status      | start_date | name
------------+------------+---------------------------------
abandoned   | 2006-08-03 | Smith Associate Window and Roof
completed   | 2005-08-09 | Smith Entry and Waiting Room
completed   | 2005-02-03 | Smith Balcony Expansion
...

/{today(),null(),true(),false(),pi()}

SELECT CURRENT_DATE, NULL, TRUE, FALSE, PI()
------------------------------------------------------
today()    | null() | true() | false() | pi()
------------+--------+--------+---------+-------------
2008-05-22 |        | true   | false   | 3.14159265359

/project{prj_id,string(start_date)[-5:].replace('-','/')}

SELECT prj_id,
  REPLACE(
    SUBSTRING(CAST(start_date AS TEXT)
      FROM (((- 5) + (
         CASE WHEN (- 5) < 0
         THEN CHARACTER_LENGTH(CAST(start_date AS TEXT))
         ELSE 0 END))+1)
  ), '-', '/')
FROM op.project
ORDER BY prj_id
project                                                |
-------------------------------------------------------+-
prj_id   | string(start_date)[(-5):].replace('-', '/') |
---------+---------------------------------------------+-
la-102   | 03/27                                       |
la-334   | 04/20                                       |
...

/project{prj_id,recode(status,'completed','done')}

SELECT prj_id,
       (CASE status
        WHEN 'completed' THEN 'done'
        ELSE status END)
FROM op.project
ORDER BY prj_id
project                                          |
-------------------------------------------------+-
prj_id     | recode(status, 'completed', 'done') |
-----------+-------------------------------------+-
Bowl-Shoes | planned                             |
la-102     | done                                |

/project{prj_id,start_date+time('03:30')+timedelta(365*4,1,5)}

SELECT prj_id, ((start_date + TIME '03:30:00')+
      CAST(( CAST(365 * 4 AS TEXT) || 'D '
          || LPAD(CAST(1 AS TEXT), 2, '0') || ':'
          || LPAD(CAST(5 AS TEXT), 2, '0') || ':'
          || '00'
      ) AS INTERVAL))
FROM op.project
ORDER BY prj_id
project
---------------------------------------------------------------
prj_id  | ((start_date+time('03:30'))+timedelta((365*4), 1, 5))
--------+------------------------------------------------------
la-102  | 2008-03-26 04:35:00
la-334  | 2008-04-19 04:35:00
...

/participation{floor(billing_rate div 2),(billing_rate mod 3)}

SELECT FLOOR(billing_rate / 3),
       billing_rate % 3
  FROM op.participation
ORDER BY ...
participation                                      |
---------------------------------------------------+-
floor((billing_rate div 3)) | (billing_rate mod 3) |
----------------------------+----------------------+-
18                          | 1.00                 |
23                          | 0.00                 |
...

/project?client.is_active

SELECT p.* FROM op.project AS p
  LEFT OUTER JOIN op.organization AS o
    ON (p.client = o.org_id)
 WHERE o.is_active IS TRUE
ORDER BY prj_id
project
------------------------------------------------- ...
prj_id | status      | client      | start_date |
-------+-------------+-------------+------------+ ...
lt-711 | planned     | lake-carmen |            |
lt-802 | in-progress | lake-carmen | 2006-11-23 |

/participation?person.organization.is_active

SELECT x.* FROM op.participation AS x
  LEFT OUTER JOIN op.person AS p
    ON (x._ppl_seq = p._ppl_seq)
  LEFT OUTER JOIN op.organization AS o
    ON (p.org_id = o.org_id)
 WHERE o.is_active IS TRUE
ORDER BY t.project, o.org_id, p.nickname
participation
-------------------------------------
project  | person      | billing_rate
---------+-------------+-------------
la-102   | meyers.hill | 55.00
la-102   | meyers.jack | 69.00
...

/person?organization.division_of.org_id='lakeside'

SELECT p.* FROM op.person AS p
  LEFT OUTER JOIN op.organization AS o1
    ON (p.org_id = o1.org_id)
  LEFT OUTER JOIN op.organization AS o2
    ON (o1.org_id = o2.org_id)
 WHERE o2.is_active IS TRUE
 ORDER BY p.org_id, p.nickname
person
---------------------------------------------+-
org_id    | nickname | full_name     | email |
----------+----------+---------------+-------+-
lake-apts | tom      | Tommy O'Mally |       |
...

/project{*,client.*}

SELECT p.*,
       o.org_id AS "organization.empl_code",
       e.name AS "organization.name",
       e.is_active AS "organization.is_active",
       e.division_of AS "organization.division_of"
  FROM op.project AS p
       LEFT OUTER JOIN op.organization AS o
         ON (o.org_id = p.client)
ORDER BY p.prj_id
project
------------------------ . --------------------- ...
prj_id   | status      | . |organization.org_id  ...
---------+-------------+ . +-------------------- ...
la-102   | completed   | . |lake-apts            ...
la-334   | completed   | . |lake-apts            ...
lt-711   | planned     | . |lake-carmen          ...
...

/project{prj_id,status,client{name,is_active}}

SELECT p.prj_id, p.status,
       o.name AS "client.name",
       o.is_active AS "client.is_active"
  FROM op.project AS p
       LEFT OUTER JOIN op.organization AS o
         ON (p.client = o.org_id)
ORDER BY p.prj_id
project
---------------------------------------------------------------
prj_id   | status      | client.name        | client.is_active
---------+-------------+--------------------+------------------
lt-711   | planned     | Lake Carmen Towers | true
lt-802   | in-progress | Lake Carmen Towers | true
...

/organization?project.status='completed'

SELECT o.* FROM op.organization AS o
 WHERE EXISTS
   (SELECT * FROM op.project AS p
     WHERE p.client = o.org_id
       AND p.status = 'completed')
ORDER BY e.org_id
organization
------------------------------------------------------------
org_id    | name                   | is_active | division_of
----------+------------------------+-----------+------------
lake-apts | Lake Shore Apartments  | false     | lakeside
smith     | Rudgen, Taupe, & Smith | false     |
...

/organization[meyers]

SELECT * FROM op.organization
WHERE org_id = 'meyers'
ORDER BY org_id
proj_id  | name               | description
---------+--------------------+-----------------------------
MEYERS   | Meyers' Residence  | insulation and winterizing

/person[meyers.jim]

SELECT p.* FROM op.person AS p
 WHERE p.org_id = 'meyers'
   AND p.nickname='jim'
 ORDER BY p.org_id, p.nickname
person
-------------------------------------------------------
org_id | nickname | full_name  | email
-------+----------+------------+-----------------------
meyers | jim      | Jim Meyers | jim.meyers@example.com

/person[meyers.jim,meyers.hill]

SELECT p.* FROM op.person AS p
 WHERE p.org_id = 'meyers'
   AND p.nickname IN ('jim','hill')
ORDER BY p.org_id, p.nickname
person
--------------------------------------------------------------+-
org_id | nickname | full_name        | email                  |
-------+----------+------------------+------------------------+-
meyers | hill     | Mark Thomas Hill | mark.hill@example.com  |
meyers | jim      | Jim Meyers       | jim.meyers@example.com |

/person[meyers.*]

SELECT p.* FROM op.person AS p
 WHERE p.org_id = 'meyers'
ORDER BY p.org_id, p.nickname
person                                                         |
---------------------------------------------------------------+-
org_id | nickname | full_name        | email                   |
-------+----------+------------------+-------------------------+-
meyers | hill     | Mark Thomas Hill | mark.hill@example.com   |
meyers | jack     | Jack C. Meyers   | jack.meyers@example.com |
meyers | jim      | Jim Meyers       | jim.meyers@example.com  |
...

/person{id(),*}

SELECT (p.org_id || '.' || p.nickname) AS "id()", p.*
  FROM op.person AS p
ORDER BY p.org_id, p.nickname
person
--------------------------------------------------------- ...
id()          | org_id    | nickname | full_name        |
--------------+-----------+----------+------------------+ ...
acorn.hideo   | acorn     | hideo    | WATANABE Hideo   |
lake-apts.tom | lake-apts | tom      | Tommy O'Mally    |
...

/participation{id(),project,_ppl_seq,person.id()}

SELECT (x.project || '.' ||
        (p.org_id || '.' || p.nickname)) AS "id()",
       x.project, x._ppl_seq,
       (p.org_id || '.' || p.nickname) AS "person.id()"
  FROM op.participation AS x
    LEFT OUTER JOIN ON op.person AS p
      ON (p._ppl_seq = x._ppl_seq)
ORDER BY x.project, p.org_id, p.nickname
participation
------------------------------------------------------------
id()                   | project  | _ppl_seq | person.id()
-----------------------+----------+----------+--------------
la-102.(lake-apts.tom) | la-102   | 33       | lake-apts.tom
la-102.(lakeside.dave) | la-102   | 31       | lakeside.dave
...

/organization{org_id,count(person)}

SELECT o.org_id, COUNT(p)
FROM op.organization AS o
LEFT OUTER JOIN op.person AS p ON (o.org_id = p.org_id)
GROUP BY o.org_id
ORDER BY o.org_id;
organization
---------------------------
org_id      | count(person)
------------+--------------
acorn       | 1
attic       | 2
...

/organization{org_id,count(person), max(project.participation.billing_rate)}

SELECT o.org_id, p_branch.c, pr_branch.m
FROM op.organization o
LEFT OUTER JOIN
    (SELECT o.org_id AS pk, COUNT(p) AS c
     FROM op.organization o
     LEFT OUTER JOIN op.person p ON (o.org_id = p.org_id)
     GROUP BY o.org_id) AS p_branch
  ON (o.org_id = p_branch.pk)
LEFT OUTER JOIN
    (SELECT o.org_id AS pk, MAX(pt.billing_rate) AS m
     FROM op.organization o
     LEFT OUTER JOIN op.project pr
          ON (o.org_id = pr.client)
     LEFT OUTER JOIN op.participation pt
          ON (pr.prj_id = pt.project)
     GROUP BY o.org_id) AS pr_branch
  ON (o.org_id = pr_branch.pk)
ORDER BY o.org_id;
organization
----------------------------------------------------------- ...
org_id      | count(person) | max(project.participation.bil ...
------------+---------------+------------------------------ ...
acorn       | 1             |
attic       | 2             | 23.50
...

/person{org_id^,count()}

SELECT p.org_id, COUNT(*)
FROM op.person AS p
GROUP BY p.org_id
ORDER BY p.org_id;
person
-------------------
org_id    | count()
----------+--------
acorn     | 1
attic     | 2
...

/person{org_id^,count()}

SELECT p.org_id, COUNT(*)
FROM op.person AS p
GROUP BY p.org_id
ORDER BY p.org_id;
person
-------------------
org_id    | count()
----------+--------
acorn     | 1
attic     | 2
...

/organization{org_id,count(person)}?person.nickname='jack'

SELECT o.org_id, COUNT(p)
FROM op.organization o
LEFT OUTER JOIN op.person p ON (o.org_id = p.org_id)
GROUP BY o.org_id
HAVING BOOL_OR(p.nickname = 'jack')
organization           |
-----------------------+-
org_id | count(person) |
-------+---------------+-
meyers | 6             |
smith  | 3             |
...

/person{org_id^,count()}?nickname='jack'

SELECT p.org_id, COUNT(*)
FROM op.person p
WHERE p.nickname = 'jack'
GROUP BY p.org_id
ORDER BY p.org_id;
(revise in new version)

/person/select(offset=3,limit=2)

SELECT * FROM op.person
ORDER BY org_id, nickname
OFFSET 3 LIMIT 2               /* PostgreSQL Syntax */
person
------------------------------------------------------------------+-
org_id   | nickname | full_name        | email                    |
---------+----------+------------------+--------------------------+-
lakeside | dave     | David Jones      | david.joines@example.com |
meyers   | hill     | Mark Thomas Hill | mark.hill@example.com    |

/project/insert()?prj_id:='albe'&name:='Alberca'

INSERT INTO op.project (proj_id, name)
  VALUES ('albe','Alberca');
204 No Content

/op:project[albe]{id(),name}/update()?description:='Leaky Pool'

UPDATE op.project
   SET description = 'Leaky Pool'
 WHERE prj_id = 'albe'
RETURNING prj_id, name
project
----------------
id()   | name
-------+--------
albe   | Alberca

/op:project/delete(expect=3)?description~'pool

DELETE FROM op.project
 WHERE LOWER(description) LIKE '%pool%';
204 No Content

/op:project[able]/merge()?name:='SouthWest%20Alberca

MERGE INTO op.project
USING op.project ON prj_id = 'albe'
WHEN MATCHED THEN UPDATE
  SET name = 'SouthWest Alberca'
WHEN NOT MATCHED THEN INSERT
  (prj_id, name) VALUES
  ('albe', 'SouthWest Alberca');
204 No Content

/person[smith.*].xml

<?xml version="1.0" encoding="utf-8"?>
<htsql:result xmlns:htsql="http://htsql.org/2006/">
  <person org_id="smith" nickname="jack"
          full_name="Jack Taupe"
          email="jack.taupe@example.com" />
  <person org_id="smith" nickname="jose"
          full_name="José N. Marteñes"
          email="jose.martenes@example.com" />
  <person org_id="smith" nickname="maggy"
          full_name="Margret N. Smith"
          email="" />
</htsql:result>

/organization{name}?org_id~'lake' /project{name,status}.xml

<?xml version="1.0" encoding="utf-8"?>
<htsql:result xmlns:htsql="http://htsql.org/2006/">
  <organization name="Lake Shore Apartments">
    <project name="Kitchen Remodel at 102 N. Ocean View"
             status="completed" />
    <project name="Siding / roof at 334 Ocean Blvd"
             status="completed" />
  </organization>
  <organization name="Lake Carmen Towers">
    <project name="Updating Fire Escape"
             status="planned" />
    <project name="Toaster Re-Do and Roof Leak"
             status="in-progress" />
  </organization>
  <organization name="Lake Side Partners, LLC" />
</htsql:result>

Experience Report

  1. Rapid, Schema-Driven Development
  2. Custom Client XSLT Reports
  3. Simple HTML-based Wizards
  4. 3rd Party Reporting, Data Extraction
  5. Success /w Stored Procedures
  6. Splitting of Complexity

DBGUI

Towards 2 1/2 Tier

  1. 90's RAD - Visual Basic & Oracle
  2. Business Logic in Client
  3. Emergence of 3-Tier Architecture
  4. Stable, Complex Systems
  5. Yet, Prevalence of PHP+MySQL
  6. Reporting vs Process

How to Contact Us

  1. http://htsql.org
  2. #htsql on freenode.net
  3. info@clarkevans.com

Special Thanks