Re: Replacing WebSQL with a Relational Data Model.

Hi,

On 26 October 2010 08:26, Mikeal Rogers <mikeal.rogers@gmail.com> wrote:

> On Tue, Oct 26, 2010 at 12:02 AM, Keean Schupke <keean@fry-it.com> wrote:
>
>> Hi,
>>
>> It will be a lot faster with SQLite as the backend.
>>
>
> BTree's are old hat in the database space. Modern storage engines are using
>> Fractal-Trees for a 50-80 times speedup in write performance. The browser
>> should not be trying to compete with experienced database coders.
>>
>
> SQLite uses a btree :\
>
> *most* databases still use btrees for all their primary indexes.
>

See TokuDB for MySQL, its possible for an implementer to choose this as a
backend - might give a competitive advantage.

My point is more about the years of research, and constant development
taking place in database apps. Maybe SQLite will implement a Fractal-Tree
index soon. Do the browser implementers really want to be implementing all
this DB code to stay up to date. Why so keen to create work? Why can't we
just re-use the available storage engines with a nice API?


>
>>
>> The relational storage model I am proposing will return a database table
>> as a list of objects, where each object represents a row, and the object has
>> a set of named properties representing each column.
>>
>> Part of the power of the Relational Data Model is that it abstracts data
>> into columns and tables, and this is precisely what we want.
>>
>
> Who is "we"?
>

We in this case is the company I work for (see my introductory email) and
some some of our customers that we have worked with.


> The relational data model doesn't fit the web very well which is why IDB
> was developed.
>

The relational model is great and used in native mobile apps. iPhone and
Android both provide SQLite and use it heavily. I think if you see how neat
the API is, you might change your mind.


>
> Most web developers I talk to do not want a relational database in the
> browser but they do want something better than what is currently there.
>

We would have been happy with WebSQL, but I can see the problems with
standardising it.


>
> If your relational API isn't fast enough built on top of IDB then you
> should post the performance metrics and efforts can be made to improve the
> performance.
>

Have you seen the ammount of code in SQLite? I don't think you understand
the ammount of work involved in implementing a decent relational database
engine.


>
> I built a CouchDB compatible datastore on top of IDB and parts don't
> perform as well as I would like (transaction overhead doesn't scale well and
> performance doesn't degrade linearly). Once Firefox is out of beta I might
> make a bigger deal out of it and do some comparison's against Chrome's
> recent impl.
>


Cheers,
Keean.



>
> -Mikeal
>
>
>> On 26 October 2010 01:50, Jonas Sicking <jonas@sicking.cc> wrote:
>>
>>> On Mon, Oct 25, 2010 at 10:24 AM, Keean Schupke <keean@fry-it.com>
>>> wrote:
>>> > 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?
>>>
>>> Hi Keean,
>>>
>>> This definitely sounds interesting, though admittedly I have a hard
>>> time following the details of it. However, like Jeremy, I don't see us
>>> doing yet another storage mechanism. But I do think this could work
>>> really well as additional functionality added to IndexedDB.
>>>
>>> Adding support for things like joins, filters and edit operators is
>>> something that I think would be interesting to look at for next
>>> version of IndexedDB. In the meantime I'd love to see prototypes
>>> implemented in javascript on top of IndexedDB. You can probably even
>>> prototype the exact API that you think this should have.
>>>
>>> One thing that I'm wary of is going the full relational route of
>>> requiring that values be split up into columns. It's really nice that
>>> javascript objects can be stored as-is, and then use key-paths to
>>> access specific values within an object. But I think that should be
>>> doable while still keeping the benefits of your proposal.
>>>
>>> / Jonas
>>>
>>
>>
>