hesql - Haskell's embedded SQL

hesql is a preprocessor. It reads a file (module) with SQL-like functions and translates it to Haskell source code. It uses HDBC.

The result of a select statement is automatically converted to list of tuples. Also fromSql/toSql conversion are done.

The current implementation is just a proof of concept with narrow limits. It sticks to a subset of PostgreSQL's variant of SQL. Don't expect too much, but please send me patches to improve hesql.

An example is found under example/Account.hesql. It works with this tables:

CREATE TABLE Account (AccountID SERIAL PRIMARY KEY, Login TEXT NOT NULL, Password TEXT NOT NULL, RealName TEXT, Email TEXT NOT NULL, LoggedIn BOOL DEFAULT FALSE NOT NULL, Locked BOOL DEFAULT FALSE NOT NULL, LastLogout TIMESTAMP, LastRequest TimeStamp DEFAULT 'now' NOT NULL );

CREATE UNIQUE INDEX AccountIdx ON Account(Lower(Login));

There are four select variants to use HDBC's strict/lazy fetch functions and return a list or a maybe type.

select: lazy, list of rows select': strict, list of rows select1: lazy, maybe row select1': strict, maybe row

For example:

verifyLogin login' password' = select1' Accountid, Login, RealName, Email from Account where (password is null or password = md5(password')) and Lower(Login) = login' and not Locked

will generate code for a Haskell-Funktion with the type:

verifyLogin :: Stmts -> String -> String -> IO (Maybe (Int, String, String, String))

(To be honest, the type is simplified by me. hesql won't write any type annotation.)

Each module will have a function

init :: Connection -> IO Stmts

it prepares each statement for the later use.

TODO

• add a separate TODO file
• parse group by, having, column/table aliases, subselects
• verify table and column names
• resolve column names in SELECT * FROM ...
• fix command line argument parsing
• improve documentation
• make backup of old .hs-File

INSTALL $ make $ sudo make install

USAGE $ createdb account $ psql account

create the account table from above

$ cd example/ $ hesql Account.hesql dbname=account $ less Account.hs

DEPENDENCIES:

hesql depends on - ghc - postgresql - HDBC with PostgreSQL backend - haskell-src (to print the haskell code) - parsec (for the SQL parser) - do we have to call finish on Maybe-Statements?