An approch to schema-driven URL-to-SQL translation for rapid application development and custom report writing.
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 \------------------------/
+-------------------+ +-------------------+ | 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>