HTSQL

About me

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

irc.freenode.net #htsql: Kirill Simonov

This talk does not represent official Dell positions. I don't think Dell even knows I'm giving it...

Relational Databases

Getting data back out

Seeing Data

• SQL
• Dump to spreadsheet
• ODBC (spreadsheet, OpenOffice Base)
• GUI/web admin tool: phpMyAdmin, etc.
• Jasper, Crystal, Discoverer...
• Django, Rails, etc

HTSQL

SELECT *

• /school
• /program
• /department
• /course
• /student

A real query

"How many programs and department does each school have?"

SELECT sch.name,
   (SELECT COUNT(1) FROM program
     WHERE school = sch.code),
   (SELECT COUNT(1) FROM department
     WHERE school = sch.code)
   FROM school AS sch
 ORDER BY sch.code ASC

... in HTSQL

/school{name, count(program), count(department)}

HTSQL is...

• web access gateway for relational databases
• sophisticated navigational query language
• multi-target optimizing query compiler
• social data-set sharing & refinement tool

Written in Python

Virtues

• Open source
• Free*
• Web-based (or not)
• Optimized
• Simple
• Good-looking

Works With

• PostgreSQL
• SQLite
• MySQL
• Oracle ($)
• MS SQL Server ($)

Installing

http://htsql.org/doc/install.html

apt-get install python python-setuptools \
                python-yaml python-psycopg2 mercurial
hg clone http://bitbucket.org/prometheus/htsql
cd htsql
make build
make install

Running

htsql-ctl server pgsql://user:pass@host:port/database

Ten Tiny Tastes of HTSQL

#1 - Selecting Data

• curly brackets are selectors
• postfix operators are sorting (+/-)

An example (E1):

/course{department+, no, credits-, title}

#2 - Linking Data

• we use period to signify a link, or join
• wildcard * pulls all columns from a table

An example (E2):

/department{*, school.name}

#3 - Filtering Data

• question mark ? indicates filter
• single-quote literal strings

For example (E3):

/course?credits<3&department.school='ns'

#4 - Squiggly filters

/student?name~'y'

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

#5 - 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))}

#6 - Pluggable Formatters

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

For example (E6):

/course?credits<3&department.school='ns'/:txt

#7 - Table Expressions

• use limit(#, offset) to slice data
• use sort() to sort a table expression
• other forms of table expressions possible

An example (E7):

/department.sort(school).limit(10,5)

#8 - Clean it up

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

/school{code, avg(department.count(course)) :round 2 :as 'Average courses per department'}

#9 - Directly from Python

>>> from htsql import HTSQL
>>> from htsql.request import produce
>>> htsql = HTSQL('pgsql:htsql_regress')
>>> with htsql:
...     for row in produce('/school'):
...         print row
...

#10 - HTRAF Dashboarding

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

htraf.htsql.org

Development Status

HTSQL is quite usable currently, but it may have gaps for a given application. Particular items Prometheus is working on:

• a mechanism to override catalog configuration
• a way to add custom commands and formatters
• hierarchical queries
• streaming result sets
• more maturity and exciting features!

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.

Dual-License & Support

• license for use /w commercial database systems
• Prometheus sells support packages and consulting

htsql.com

More info

• htsql.org
• htraf.org
• demo.htsql.org
• htraf.htsql.org
• irc.freenode.net #htsql
• http://pycon.blip.tv/file/4883320/
• catherinedevlin.pythoneers.com/presentations/htsql/