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
- Remote access to PostgreSQL
- Parsimonious, Consistent Syntax
- Users are Casual Software Developers
- Satisfy most Query Needs
- Use HTTP for Transport
- Leverage Information Schema
- Work Smoothly with XSLT, AJAX
- Be RESTFUL and HTML friendly
Walk Through
- Basic Queries
- Selectors and Functions
- Specifiers and Locators
- Aggregates and Projections
- Commands, Formatters
- Path and Forest Queries
Wrap-Up
- Adoption and Experience
- Towards 2 1/2 Tier?
- Questions & Answers
+-------------------+ +-------------------+
| 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
- Rapid, Schema-Driven Development
- Custom Client XSLT Reports
- Simple HTML-based Wizards
- 3rd Party Reporting, Data Extraction
- Success /w Stored Procedures
- Splitting of Complexity
DBGUI
Towards 2 1/2 Tier
- 90's RAD - Visual Basic & Oracle
- Business Logic in Client
- Emergence of 3-Tier Architecture
- Stable, Complex Systems
- Yet, Prevalence of PHP+MySQL
- Reporting vs Process
How to Contact Us
- http://htsql.org
- #htsql on freenode.net
- info@clarkevans.com
Special Thanks
- Simons Foundation
- PostgreSQL Community
- Python Community