Your Database, Exposed: HTSQL

About me

• Database administrator (Dell KACE)
• Python enthusiast
• catherinedevlin.blogspot.com
• catherinedevlin.pythoneers.com/presentations/htsql/
• HTSQL fangirl

This talk does not represent official Dell positions. But Dell is cool anyway.

About HTSQL licensing

• BSD style license when used with FLOSS
• license for use w/ Oracle, MSSQL
• Prometheus sells support packages and consulting

Relational Databases

• PostgreSQL
• SQLite
• MySQL

SQL

SELECT name
FROM   department;

SQL

SELECT s.name,
       d.name
FROM   school s
JOIN   department d
ON       (s.code =
          d.school_code);

Piece of cake!

Seeing Data

• Spreadsheet
• ODBC (spreadsheet, OpenOffice Base)
• GUI/web admin tool: phpMyAdmin, etc.
• Web app: Django, Rails, Web2Py...

Specialized reporting tools

• JasperReports
• Crystal Reports
• Oracle Discoverer
• ...
• HTSQL

How to talk to a web service?

• SOAP
• XML-RPC
• REST

RESTful

REpresentational State Transfer

http://www.flickr.com/photos/lottadot/

http://www.flickr.com/photos/lottadot/240953464/

http://www.flickr.com/search/?q=linuxfest

Install and run

sudo apt-get install python-setuptools

sudo easy_install htsql

htsql-ctl serve postgres://username:password@host/db

RESTful query language

http://localhost:8080/school

http://demo.htsql.org/school

• Access
• Store
• Share
• Change

Filter

/school?campus='old'

/school?campus='old'&code='la'

/school?name~'ART'

/department?school_code={'eng','ns'}

/department?school_code={'eng','ns'}|name~'y'

Selectors

/school{code,campus}

/school{code,campus+}

/school{code,campus-,name+}

Joins

Every Department belongs to a School.

/department

/school

In SQL

SELECT d.*,
       s.name
FROM   department d
JOIN   school s
ON     s.code = d.school_code;

In HTSQL

/department{*, school.name}

/course{title, department.name, department.school.name}

/course{title, department.name :as 'department', department.school.name :as 'school'}

Aggregating Data

/course{title, credits, department.name}

/department{name, course.title, course.credits} (No!)

/department{name, sum(course.credits)}

/school{code, avg(department.count(course))}

Projections (DISTINCT)

/program

/program^degree

/program^degree{degree, count(program)}

Complexity

Add whitespace, it rocks:

http://demo.htsql.org/school
  {name, count(program),
   count(department.course),
   count(department)}
   ?name~'art'

Blah, blah, blah

• column functions like round(),
• table functions like sort(), limit()...
• parameterized calculations
• booleans, dates

Pluggable Formatters

• use /:format to specify formatter
• supports CSV, JSON, TXT, HTML
• XML, YAML and others in later releases

/:txt gives you SQL, too:

/school?exists(program)
  {name,avg(department.count(course?credits>3))
   }/:txt

Directly from Python

from htsql import HTSQL
from htsql.request import produce
htsql_inst = HTSQL('postgres://cat:cat@localhost/uni')
with htsql_inst:
    for row in produce('/school'):
        print row

HTRAF Dashboarding

• HTRAF toolkit (JQuery MIT)
• Automatically manages dependencies
• Uses HTSQL for data source
• via data-xxx attributes
• and $variable substitution

htraf.htsql.org

More to come

• Plugin configuration
• Schema introspection
• CRUD
• Custom commands
• Visual query builder

Credits

Written by Kirill Simonov and Clark Evans (Prometheus Research)

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.

More info

• htsql.org
• htraf.org
• demo.htsql.org
• htraf.htsql.org
• irc.freenode.net #htsql
• python.mirocommunity.org
• catherinedevlin.pythoneers.com/presentations/htsql-olf/