[SQLite] Database design

[joskuijpers]

I read the Andromeda topic again and revised the SQLite database classes I made.
I made them simpler, removed double sugar (such as both set(key,value) and set(object)) and made it more JavaScript-ish (by using set(object)).

Remember that SQLite is an extension and its availability must be verified using System.extensions["sqlite"]. The code below is shipped with the engine.

I wrote it down in short:

Database class // all native
    function Database(name)
    .query([sql]) -> new Database.Query

    .tables = {name : Table}
    .createTable(name,fieldsObject)

    ._performQuery(query,arguments) -> Array<Object>
    ._insertId() -> Number
    ._affectedRows() -> Number
    ._getLastError() -> String

Database.Table // all in JS
    .countAll() -> Number
    .truncate() -> Boolean
    .drop() -> Boolean

Database.Query // all in JS
    .select(column/columns) // string/array
    .selectSum(column)
    .selectAvg(column)
    .selectMin(column)
    .selectMax(column)

    .orderBy(column[,order])
    .limit(n)
    .offset(n)
    .distinct()
    .having(object)
    .orHaving(object)
    .groupBy(column/columns)
    .like(object[,wildcard])
    .orLike(…)
    .notlike(…)
    .orNotLike(…)

    .where(object/string)
    .whereIn(object) // object = {column: value} or {columns: [ value, value] }
    .whereNotIn…
    .orWhere…
    .orWhereIn…
    .orWhereNotIn…

    .join(table[, condition[, type]])

    .set(object) // kv’s

    .insert(table[, row-object]) -> row ID, 0 no id, -1 fail
    .update(table) -> num changes
    .delete(table) -> num changes
    .get(table) -> Array<Object>

The idea is that with the query builder, every action that allows for more changes (all but insert, update, delete, get) set some flag or variable, or add to some stack of query information, and then returns this.
When one of the final operations are called, the query builder is used to build an SQL query from the information available. It then calls Database._performQuery() (native function). If it was called by insert, it uses ._insertId() to get the insert id or for update and delete it uses ._affactedRows().
._performQuery() returns an array of rows, each row being an object, or null on failure. ._getError() can be used to get some SQL error from the native side.

This means the native side can remain small and all the sugarcoating is in JavaScript (which means it is much faster). Also, the whole query building code can be created by the Pegasus community.

I think this will work. 😃

[joskuijpers]

This API is only good for small tables because result sets are stored completely in an Array store, instead of using some .next() system.

[joskuijpers]

As per joskuijpers/Andromeda/914444faf8 I have an implementation of the Database and Table class, and a very very minimal sqlite.js file containing the Database.Query class. And this works 😄
I adjusted the OP to the stuff I discovered.

[joskuijpers]

If there are no further comments on this design, I will close the issue.