Replacing WebSQL with a Relational Data Model.

We (www.fry-it.com) produce websites and mobile apps. We have been looking
at HTML5 for developing mobile apps, and for the kind of apps that we want
to produce the client side WebSQL API provided what we needed. The IndexedDB
is not suitable (no joins, no compound indexes etc...).

Having discussed the reasons for the lack of progress of the WebSQL
standard, I wanted to get a feeling for the willingness of browser
implementers to consider a different approach to SQL, something more like
HaskellDB (a Haskell database layer) and LINQ for C#. Rather than
implementing an SQL like syntax, what I am proposing is to implement
relational algebra directly.

I have a published peer reviewed paper (Haskell Workshop 2004) where we
implemented a type safe relational algebra in Haskell, and my idea is to
port this to JavaScript. The API can be formally defined to implement the
complete relational algebra, and hence have a standardised API that can be
implemented in all browsers, yet would be capable of having different
backends (SQLite, Microsoft SQL Server, MySQL, PostgreSQL, Oracle etc...)

Here is an example of using relational algebra as a Domain Specific Language
in Haskell:

{- This is how we would write the relational statement in SQL.
UPDATE Animal
   SET Animal.location=?newLocation?
 WHERE Animal.animalid IN
         SELECT Contaminated.animal
           FROM Contaminated,Animal
          WHERE Animal.type=?animalType?
           AND Contaminated.type=?contaminationType?
           AND Animal.animalid=Contaminated.animal;
-}

-- This is how we would write it in a Haskell DSL.
moveContaminatedAnimal :: DAnimalType -> DCntdType -> DFarmId -> Query ()
moveContaminatedAnimal animalType contaminationType newLocation = do
    a1 <- table animalTable
    a2 <- restrict a1 (\r -> r!AnimalType `SQL.eq` animalType)
    c1 <- table contaminatedTable
    c2 <- restrict c1 (\r -> r!CntdType `SQL.eq` contaminationType)
    j1 <- join SqlInnerJoin a2 c2 (\r -> r!AnimalId `SQL.eq` r!CntdAnimal)
    j2 <- project j1 (CntdAnimal .*. HNil)
    doUpdate animalTable
        (\_ -> AnimalLocation .=. toSqlType newLocation .*. HNil)
        (\r -> r!AnimalId `SQL.elem` relation j2)


The syntax would obviously be different, but I would expect something like
(It may end up looking nothing like this, as I need to think about the best
way of representing the Haskell Relation Monad in JavaScript).

-- this is something like what would be required in JavaScript
function moveContaminatedAnimal(animalType, contaminationType, newLocation)
{
    a1 = new Table(animalTable);
    a2 = new Restriction(a1, new Eq(function(row) {row[AnimalType]},
animalType));
    c1 = new Table(contaminatedTable);
    c2 = new Restriction(c1, new Eq(function(row) {row[CntdType]},
contaminationType));
    j1 = new InnerJoin(a2, c2, new Eq(function(row) {row[AnimalId]},
function(row) {row[CntdAnimal]}));
    j2 = new Projection(j1, [CntdAnimal]);
    update(animalTable, function(row) {row[AnimalLocation] = newLocation},
new Elem(function(row) {row[AnimalId], j2}));


This constructs the update Animal query from the "Farm" database, which is a
commonly used example schema in academic papers. NOTE: the implementers are
free to try to write a relational database in JavaScript, but most
implementations would construct the SQL query string and then execute it on
an SQL database backend (SQLite / PostgreSQL / MySQL).

Its pretty easy to define the complete relational algebra, the base Query
class would need the following sub-classes:

SqlBinaryOp = SqlAdd | SqlSub | SqlMul | SqlDiv | SqlMod | SqlEq
    | SqlGe | SqlGt | SqlLt | SqlLe | SqlNe | SqlAnd | SqlOr | SqlIn
SqlAggrOp = SqlCount | SqlSum | SqlMax | SqlMin | SqlAvg | SqlStdDev |
SqlVariance
SqlRelationOp = SqlInnerJoin | SqlLeftJoin | SqlRightJoin | SqlFullJoin
SqlSetOp = SqlUnion | SqlIntersection | SqlDifference
Table, Projection, Restriction, Extension, Limit, Order, Join, Union,
Intersect, Except
and the methods: select, update, insert, delete

This would pretty much represent a port of my Haskell work to JavaScript.

The Haskell code also includes schema management, enabling the entire schema
to be defined, as data, and allowing the database to be created or validated
against the schema.

Here is the schema for the Farms table in Haskell:

{- domains -}
newtype DFarmId = DFarmId Int deriving (Show,Eq,ToSqlType
SqlInteger,FromSqlType SqlInteger)
newtype DFarmName = DFarmName String deriving (Show,Eq,ToSqlType
SqlVarchar,FromSqlType SqlVarchar)
newtype DFarmCounty = DFarmCounty String deriving (Show,Eq,ToSqlType
SqlVarchar,FromSqlType SqlVarchar)
newtype DFarmerId = DFarmerId Int deriving (Show,Eq,ToSqlType
SqlInteger,FromSqlType SqlInteger)

data FarmId = FarmId deriving Show
data FarmName = FarmName deriving Show
data FarmCounty = FarmCounty deriving Show
data FarmOwner = FarmOwner deriving Show

type FarmTable = Table (
    FarmId :=: Attribute DFarmId SqlInteger :*:
    FarmName :=: Attribute DFarmName SqlVarchar :*:
    FarmCounty :=: Attribute DFarmCounty SqlVarchar :*:
    FarmOwner :=: Attribute DFarmerId SqlInteger :*:
    HNil)

farmTable :: FarmTable
farmTable = newTable "Farm" (
    FarmId .=. Attribute (attr { attrName="farmid", attrType="SERIAL" }) .*.
    FarmName .=. Attribute (attr { attrName="farmname", attrSize=20 }) .*.
    FarmCounty .=. Attribute (attr { attrName="county", attrSize=15 }) .*.
    FarmOwner .=. Attribute (attr { attrName="owner" }) .*.
    HNil)


We could do something similar in JavaScript for table creation and
validation with version control and selective automatic update (the code can
derive some updates automatically, like if you add a new column to a table
that is nullable or has a default value).

As this is a relational data model, it can be entirely defined as an
abstract API, with no reference to any SQL at all. I could provide a
reference implementation written in JavaScript on top of WebSQL, but the
idea would be for browser implementers to implement directly using SQLite
(for example).

I would appreciate any advice on how to proceed with this? If implementers
would be interested in implementing a relational API?


Cheers,
Keean.