PGCon2008 - Final - we hope

PGCon 2008
The PostgreSQL Conference

Speakers
Clark Evans
Schedule
Day Talks - second day (2008-05-23)
Room A
Start time 15:00
Duration 01:00
Info
ID 95
Event type lecture
Track Applications
Language en
Feedback

HTSQL - a web-based front-end for PostgreSQL

An innovative URL-2-SQL translator for Web 2.0 applications.

With the proliferation of web-based applications and increased user technical ability, distributed applications with multiple front-ends becomes a viable, if not essential approach to software construction and deployment. This talk will describe an approach to web-based databases, specifically a URL-2-SQL translator which converts common assumptions about how web resources should work into SQL queries for a PostgreSQL backend. Interestingly enough, with a tool such as HTSQL together with the maturity of database-level constraints and stored procedures, the pendulum favoring 3-tier solutions swings back to a 2-tier approach.

HTSQL is a mature, and competently developed URL-2-SQL translator; it's installation essentially publishes an entire PostgreSQL database "on the web" with little effort. HTSQL intelligently maps URLs onto SQL queries, using information_schema for configuration (with manual overrides if needed), putting most databases on-the-web in less than a few hours. A bulk of SQL constructs are covered, permitting standard DML (and via work-in-progress DDL), and enabling a vast majority of complicated queries. HTSQL has been field-tested with over 5 years of refinement, through 3 complete rewrites; and is deployed at medical research laboratories at UofM, UIC, Harvard, Yale, UCLA, Emory, U Washington, and many others.

This talk will cover the following items:

  • A general discussion of web querying (by Clark Evans)
  • The vision for HTSQL, a preview of HTSQL and its regression test schema (5 min)
  • Key Concepts for HTSQL (10 min)
    • Path segments, commands, REST
    • Locators, aka Primary Key lookup-expressions
    • Selectors, columns, join specifiers, functions and the like
    • Projections and Aggregate Functions
    • Filters and Formatters
  • Configuration and End-User Details (5 min)
    • Perspectives (aka Roles) and Aspects (aka Tables)
    • Use of information_schema to infer relationships and details
    • Example configuration to "htsql-enable" Trac
    • Example queries on Trac
    • Making a plugin via Python
  • Implementation Goodness (by Kirill Simonov, 25 min)
    • Syntax Definition and Parser
    • Binding of query to meta-data
    • Internal query representation and macros
    • Translation to SQL
    • Handling aggregation and projections
    • Details on plugin-handling
    • Security concerns
  • A generic Web 2.0 GUI, "DBGUI" using QooxDoo+HTSQL (by Alexei Golovko, 5 min)
  • Summary, Q&A, and Conclusion (by Clark Evans, 10 min)

This is a very technical discussion. Moderately complicated SQL will be used, regularly and without apology, in order to illustrate key concepts.

Links