Overview

A schema defines the storage structure of a relational database. A schema MUST have a name. The origin of the page and the schema name together uniquely identify a database instance during runtime.

A schema has a referential version number associated with it. Version number is an integer greater than zero and can be freely assigned. It has no effect on identifying a database. It is provided for the convenience of developers to version their schema without extra table.

A schema is structured in a hierachical way. A database schema contains one or more tables. A table contains one or more columns, zero or more indices, and zero or more constraints. The collection of columns in a table defines the fields carried by a row, which is the fundamental unit of data storage in relational database. Each column consists a name and an associated data type. The indices hint the database engine to build index structures so that subsequent queries can be acclerated. Index definitions MAY be honored if the database engine determined that they are necessary. The constraints define the relations of rows and ensures data integrity, and MUST be honored.

The database schema is constructed via builder pattern APIs. Schema can be changed through schema queries within implicit transactions, or explicit batched readwrite transactions.

Naming Rules

Names, also known as identifiers, are used to identify entities in schema, i.e. database, table, column, index, and constraint. All names used in the database schema MUST abide the following rules:

• Names are case sensitive
• A name MUST pass the following check: /^[A-Za-z][A-Za-z0-9_]*$/.test(name)
• A name MUST NOT conflict with properties and methods defined in Object.prototype and ITable.

Unlike SQL, names in RDB are case-sensitive.

Column Types

A table column MUST be associated with a given type. Valid types are defined in ColumnType enum:

    enum ColumnType {
      "blob",
      "boolean",
      "date",
      "integer",
      "number",
      "string",
      "object"
    };

    typedef (ArrayBuffer or boolean or Date or long long or double or DOMString or object) ValueType;
    typedef (boolean or Date or long or double or DOMString) IndexableValueType;
  

blob represents JavaScript ArrayBuffer, and date refers to Date object. boolean, number, string and object correpond to the types of the same names in JavaScript. The type number is the Web IDL type double.

integer is a special data type representing integers, corresponding to Web IDL type long long, but within the range of [Number#MIN_SAFE_INTEGER, Number#MAX_SAFE_INTEGER]. Integer columns will be converted to and from JavaScript number types using equivalent of Number#toPrecision(1). If the converted number failed test of Number#isSafeInteger(), a DataError will be thrown.

JavaScript types that can be used in a column is defined as ValueType. Only some value types are indexable, indicating that they can be compared, sorted, and used in indices and predicates. These types are defined as IndexableValueType. Non-indexable types, blob and object, MUST NOT be used to create indices and predicates, except the isNull() and isNotNull() predicates.

Ordering

There are two sorting orders defined in the Order enum:

    enum Order {
      "asc",
      "desc"
    };
  

The asc represents ascending sorting order, and is the default sorting order if none were provided. The desc represents descending sorting order.

Ascending sort order is the natural sort order, which means that if two fields are ordered as a, b, then (a <= b) === true. Since blob and object types are not indexable, they cannot be compared and thus the orders have no effect for these two types.

Schema Query

A database instance is created using schema queries. The schema of a database instance can also be altered via schema queries. Schema queries are run in the context of transaction and can be executed any time during the life time of the database.

IExecutionContext returned from ISchemaQueryProvider or its methods can only be used in implicit transactions or batch-executed explicit transactions, i.e. ITransaction.exec().

Any name used inside the schema not obeying Naming Rules will result in raising InvalidSchemaError during the execution of schema queries.

      interface ISchemaQueryProvider {
        IExecutionContext setVersion(unsigned short version);
        IExecutionContext setForeignKeyCheck(boolean value);
        IDatabaseSchema schema();
        ITableBuilder createTable(DOMString tableName);
        ITableChanger alterTable(DOMString tableName);
        IExecutionContext dropTable(DOMString tableName);
      };
    

      interface IDatabaseSchema {
        readonly attribute DOMString name;
        readonly attribute unsigned short version;
        ITable table(DOMString tableName);
      };
    

      interface ITableBuilder : IExecutionContext {
        ITableBuilder column(DOMString name, ColumnType type, optional boolean notNull = false);
        ITableBuilder primaryKey((DOMString or sequence< DOMString >) columns, optional boolean autoIncrement = false);
        ITableBuilder foreignKey(DOMString name, (DOMString or sequence< DOMString >) column, (DOMString or sequence< DOMString >) foreign, optional ForeignKeyAction action = "restrict", optional ForeignKeyTiming timing = "immediate");
        ITableBuilder index(DOMString name, IndexedColumnDefinition columns, optional boolean unique = false);
      };
    

      dictionary IndexedColumnSpec {
        DOMString name;
        Order order = "asc";
      };
      typedef (DOMString or sequence< DOMString > or IndexedColumnSpec or sequence< IndexedColumnSpec >) IndexedColumnDefinition;
    

      enum ForeignKeyAction {
        "restrict",
        "cascade"
      };
    

      enum ForeignKeyTiming {
        "immediate",
        "deferrable"
      };
    

      interface ITableChanger : IExecutionContext {
        ITableChanger rename(DOMString newTableName);
        ITableChanger addColumn(DOMString name, ColumnType type, optional boolean notNull = false, optional ValueType defaultValue = null);
        ITableChanger dropColumn(DOMString name);
        ITableChanger addPrimaryKey((DOMString or sequence< DOMString >) columns);
        ITableChanger addForeignKey(DOMString name, (DOMString or sequence< DOMString >) column, (DOMString or sequence< DOMString >) foreign, optional ForeignKeyAction action = "restrict", optional ForeignKeyTiming timing = "immediate");
        ITableChanger addIndex(DOMString name, IndexedColumnDefinition columns, optional boolean unique = false);
        ITableChanger dropConstraintOrIndex(DOMString name);
        IColumnChanger setColumn(DOMString name);
      };
    

      interface IColumnChanger {
        ITableChanger set(DOMString newColumnName, optional boolean notNull = true);
      };
    

let dbConnection;
let tx = dbConnection.createTransaction('readwrite');
let q1 = dbConnection
    .createTable('Dept')
    .column(/* name */ 'id', /* column_type */ 'string', /* not_null */ true)
    .column('name', 'string', true)
    .primaryKey('id');
let q2 = dbConnection
    .createTable('Emp')
    .column('id', 'number', true)
    .column('name', 'string', true)
    .column('deptId', 'string', true)
    .column('title', 'string')
    .primaryKey('id')
    .index('idx_Desc', { 'name': 'desc', 'order': 'desc' })
    .foreignKey('fk_DeptId', 'deptId', 'Dept.id');
tx.exec([q1, q2]).then(() => {
    // Table created, do something here.
});
    

let dbConnection;
let version = dbConnection.schema().version;
if (version < 2) {
  // Need DB upgrade.
  let tx = dbConnection.createTransaction('readwrite');
  let setVersion = dbConnection.setVersion(2);
  let createNewTable = dbConnection
      .createTable('NewTable')
      .column('id', 'string', true)
      .column('name', 'string', true);
    
  let alterTable = dbConnection
      .alterTable('Emp')
      .addColumn('location', 'string', true, 'LAX')
      .setColumn('title').set('title', true) // Change to NOT NULL
      .addIndex('idx_location', 'location');

  let dropTable = dbConnection.dropTable('Foo');

  // MUST use exec() instead of attach().
  return tx
      .exec([setVersion, createNewTable, alterTable, dropTable])
      .then(() => {
        // Schema change has finished, start work here.
      });
}

    

Overview

A database's origin is the same as the origin of the document or worker. Each origin has an associated set of databases.

The database origin is not affected by changes to document.domain.

Every database has a predefined layout of the data structures persisted, which is called a schema. The database name specified in schema identifies the database within a specific origin. The name MUST satisfy the Naming Rules, and stay constant for the lifetime of the database. Each database also has a version number for reference only.

Databases have a delete pending flag which is used during deletion. When a database is requested to be deleted the flag is set to true and all attempts at opening the database are stalled until database is deleted.

The act of opening a database creates a connection. There MAY be multiple connections to a given persistent database at any given time, where temporary database accepts only one connection. Each connection has a closePending flag which initially is set to false.

When a connection is initially created it is in opened state. The connection can be closed through several means. If the connection is garbage collected or the execution context where the connection was created is destroyed (for example due to the user navigating away from that page), the connection is closed. The connection can also be closed explicitly via the API. When the connection is closed the closePending flag is always set to true regardless of its previous state. All uncommitted transactions associated with a connection that is marked close pending will be canceled.

Users can create observers associated with a given database. Each observer consists of a select query. When the results of that select query change, the observer will be triggered. The change MAY come from other connections. When the connection associated with a registered observer is marked as close pending, the observer MUST be unregistered automatically.

Relational Database

The relational database is part of Navigator as defined in following WebIDL.

    partial interface Navigator {
      readonly attribute IRelationalDatabase db;
    };
  

IRelationalDatabase db

Provides the entry point of accessing relational database functions.

The relational database is defined as IRelationalDatabase:

    interface IRelationalDatabase {
      readonly attribute IDatabaseFunctionProvider fn;

      Promise< DatabaseConnection > open(DOMString name, OpenDatabaseOptions? opt);
      Promise< void > drop(DOMString name);
    };
  

IDatabaseFunctionProvider fn

Provides a namespace for aggregation functions that can be used in queries.

Promise< DatabaseConnection > open(DOMString name, OpenDatabaseOptions? opt)

Opens a database connection using provided name. If the database does not exist, create a new database instance and returns connection to it. If the database is marked delete pending, the promise will be rejected with BlockingError. If the OpenDatabaseOptions is not specified, a persistent database with the given name will be opened/created.

Promise< void > drop()

Deletes a database using provided name. This MUST follow the delete database algorithm. If the database does not exist, the returned promise will resolve immediately. If the database is marked delete pending, the promise will resolve immediately.

OpenDatabaseOptions is a dictionary object used to specify options for opening a database:

    dictionary OpenDatabaseOptions {
      RDBStorageType storageType;
    };
  

RDBStorageType storageType

Specifies the type of storage used to back the database. The storageType is fixed for the lifetime of returned database connection.

    enum RDBStorageType {
      "persistent",
      "temporary"
    };
  

persistent

Data will be persisted in permanent storage.

temporary

Data will be persisted in memory only: the relational database acts as in-memory cache of data and offers only the relational query capability.

Connection

The database connection is the means by which the browser session and underlying database communicate with each other. IDatabaseConnection models a database connection:

    interface IDatabaseConnection {
      ITransaction createTransaction(optional TransactionMode mode = "readonly");
      Promise< Error > close();
      IBindableValue bind(unsigned short index);
    };
  

ITransaction createTransaction(optional TransactionMode mode = "readonly");

Creates an explicit transaction. If the connection is marked as close pending, throws BlockingError.

Promise< Error > close();

Closes the connection. The connection will be marked as close pending immediately after this call, and no more new transaction can be created within this connection. It is a best efforts call and may resolve with error, or NULL if everything succeeded.

IBindableValue bind(unsigned short index);

Returns a bindable value that can be used in parameterized query.

The actual database connection returned from Navigator is a class object, DatabaseConnection, that implements four different interfaces:

    interface DatabaseConnection : IDatabaseConnection {
      readonly attribute DOMString name;
    };
    DatabaseConnection implements IDatabaseObserver;
    DatabaseConnection implements IDataQueryProvider;
    DatabaseConnection implements ISchemaQueryProvider;
  

name

Name of the database. It MUST be the same as the name field specified in schema.

The ISchemaQueryProvider provides database-level schema query and allows creating or changing the database schema. The IDataQueryProvider provides APIs to construct data manipulation query to be used to manipulate data within a transaction. The IDatabaseObserver provides change observation for the results of a select query, regardless the changes are from this connection or not.

Transaction

A transaction is an atomic unit of reading and writing data in a database. The term atomic means it is guaranteed that all operations inside this transaction to be committed as a whole, or none get committed. A committed transaction guarantees all data being written to storage. The operations inside a transaction are referred as queries. A query means an interaction with the database: data retrieval, data persistence, or change of schema.

Transaction execution behavior is modeled as the following: A TransactionResults to represent the return values from a finished transaction, and IExecutionContext to control the transaction lifetime.

    typedef (void or sequence< object >) TransactionResults;

    interface IExecutionContext {
      Promise< TransactionResults > commit();
      Promise< void > rollback();
    };
  

Promise< TransactionResults > commit();

Runs commit algorithm.

Promise< void > rollback();

Runs rollback algorithm.

All transactions are associated with a database connection, which is the connection that the transaction is created within. A transaction has a predetermined TransactionMode to indicate what access this transaction is asking for. The mode is set when the transaction is created and remains fixed for the life of the transaction. There are two transaction modes:

    enum TransactionMode {
      "readonly",
      "readwrite"
    };
  

readonly

Transaction intends to read data/schema only.

readwrite

Transaction intends to read/write data and/or read/change schema.

A transaction has a scope that determines the rows affected by this transaction. The scope is automatically calculated. The scope of a transaction MAY dynamically change during the lifetime of the transaction.

A transaction can be created explicitly or implicitly. An explicit transaction is created directly from IDatabaseConnection.createTransaction(). An implicit transaction is created when the commit algorithm needs one. The explicit transaction is defined as ITransaction interface:

    interface ITransaction : IExecutionContext {
      Promise< void > begin();
      Promise< TransactionResults > exec(sequence< IExecutionContext > queries);
      Promise< TransactionResults > attach(IExecutionContext query);
    };
  

Promise< void > begin();

Starts sequence mode execution. The transaction will be kept alive until commit() or rollback() of this transaction is triggered, or the transaction is timed out by implementation (which the implementation MUST throw TimeoutError and reject any further calls on this transaction object).

Promise< TransactionResults > exec(sequence< IExecutionContext > queries);

Starts batch mode execution. The queries will be executed in the order they appear in the sequence, and the transaction will be auto-committed after all sequence has run. If any of the query failed, or TimeoutError happens, the transaction will be automatically rolled back.

Promise< TransactionResults > attach(IExecutionContext query);

Schedules a query to existing sequence mode execution. The promise will be resolved with the results when the query is executed. The query MUST NOT be an explicit transaction, i.e. ITransaction object.

This specification does not support nested transactions.

The lifetime of an explicit transaction is controlled by the user. There are two ways to start execution: batch mode and sequence mode. In batch mode, an explicit transaction is started with exec(queriesArray) and returns a result Promise. All queries will be executed sequentially in the order received, and the results of last query in the transaction are returned in the promise. In sequence mode, user calls begin() to start the transaction, calls attach(query) to attach a query to the transaction's execution context. The user can call commit() to indicate the end of transaction, and a final result Promise will be returned; or the user can call rollback() to abort the transaction, and all modifications done by previous attached queries will be discarded.

When a transaction is committed or rolled back, it is said to be finished. A finished transaction MUST NOT be started nor rolled back. If a transaction's connection is lost before transaction being finished, the transaction is effectively rolled back and no change will be persisted.

Actual time out length for a given transaction is implementation dependent. Implementation MAY choose an appropriate value, or just set it to indefinite.

Observers

Observers monitor row changes inside a database. If the results from registered query change due to data changes in the database, the callback in corresponding observer will be invoked. Observer management are performed through IDatabaseObserver interface:

    callback observerCallback = void ();

    interface IDatabaseObserver {
      DOMString observe(ISelectQuery query, observerCallback callbackFn);
      void unobserve(DOMString observerKey);
    };
  

DOMString observe(ISelectQuery query, observerCallback callbackFn);

Registers a select query for observation, returns a unique observer key within the connection. When the results of the query changes due to other data manipulations, observerCallback defined by the user will be invoked.

void unobserve(DOMString observerKey);

Removes a registered observer in the connection.

Although observers are registered/unregistered via a database connection object, the changes they receive may come from other connections. The callback function is only a signal of query result change, and the users are responsible to re-execute observed query in a transaction if desired. Unlike trigger in relational databases, observers do not require implementations to execute the observed query.

The specification supports observations only for single select queries, not for readonly transactions.

Schema Representation

The fundamental part of data manipulation is to specify what to manipulate. In a relational database, this implies table and columns in the schema. The schema objects for data manipulation are acquired through IDatabaseSchema objects obtained from ISchemaQueryProvider.schema() offered by DatabaseConnection.

      interface ITable {
        ITable as(DOMString alias);
        DOMString getName();
        DOMString getAlias();
      };
    

      interface IColumn {
        readonly attribute DOMString name;
        readonly attribute DOMString table;
        readonly attribute ColumnType type;
        readonly attribute DOMString fullName;
        readonly attribute boolean nullable;
        IColumn as(DOMString alias);
      };
    

      interface Column : IColumn {
      };
      Column implements IComparisonPredicate;
      Column implements ITruthPredicate;
    

Bindable Values

Bindable values provides flexibilities for determining the actual values to be used in queries at run-time. They serve as placeholders of values. Bindable values are typed as IBindableValue:

    interface IBindableValue {
      readonly attribute unsigned short index;
    };
  

index

Index indicating which value to use in future bind() code. One can bind at most 255 bindable values for any given statement. This limitation is created for simpler implementation.

Type check of bound value is performed by query engine before executing the query. To assign value to IBindableValue, caller MUST use IQuery.bind prior to query execution. A query with any unassigned IBindableValue, or any assigned IBindableValue with mismatched type, MUST be rejected with BindingError during its execution.

The following example demonstrate how to perform parameterized query using data binding:

let dbConnection;
let query;
function init() {
    let dept = dbConnection.schema().table('Dept');
    let emp = dbConnection.schema().table('Employee');
    query = dbConnection
        .select(emp['name'].as('ename'), dept['name'])
        .from(dept, emp)
        .where(dept['id'].eq(emp['id']).and(emp['id'].eq(dbConnection.bind(0))));
}

function updateModel(employeeId) {
    query.bind(employeeId).commit().then(rows => {
        console.log(rows[0]['ename'], rows[0]['Department.name']);
    });
}

  

Common Query Behaviors

Data manipulation queries are created from the IDataQueryProvider interface, which is implemented by the DatabaseConnection object.

    interface IDataQueryProvider {
      ISelectQuery select(IColumn... columns);
      IInsertQuery insert();
      IInsertQuery insertOrReplace();
      IUpdateQuery update(ITable table);
      IDeleteQuery delete();
    };
  

ISelectQuery select(IColumn... columns);

Creates a select query to retrieve data from database. The parameters of this function forms the projection list of the generated select query. If no column is given, then the projection list will contain all columns in the select scope.

IInsertQuery insert();

Creates an insert query to insert data into database.

IInsertQuery insertOrReplace();

Similar to insert query, but behaves differently for primary key conflicts.

IUpdateQuery update(ITable table);

Creates an update query to update existing data in the database.

IDeleteQuery delete();

Creates a delete query to remove data from database.

All queries MUST inherit from this base IQuery interface.

    interface IQuery : IExecutionContext {
      Promise< DOMString > explain();
      IQuery bind(any... values);
      IQuery clone();
      DOMString toSql();
    };
  

Promise< DOMString > explain();

Explains how this query will be executed in transaction. The returned string representation will be implementation-dependent. The specification recommends implementations to return the query execution plan and other useful information for developers to do performance tuning of their queries.

IQuery bind(any... values);

Creates a clone of this query, assigns values to IBindableValue inside the cloned query, and returns the cloned query for execution. The order of values provided in the parameter of this function corresponds to the binding index provided in IDatabaseConnection.bind, i.e. the first parameter will be assigned to index 0, the second parameter to index 1, and so on. If the values provided cannot be assigned (e.g. obvious type mismatch), a BindingError MAY be thrown.

IQuery clone();

Creates a clone of this query with a new execution context. The returned query type will be the same as the original, for example, cloning an ISelectQuery will effectively return an ISelectQuery.

DOMString toSql();

Converts the query to equivalent SQL statements. The returned SQL string will also be implementation-dependent, or null if the implementation does not support this feature. The specification recommends SQL-03 standard for generating SQL statements.

Insert Query

Insert queries are used to insert rows into tables. There are two variations of insert queries: insert and insertOrReplace. Both variations support the IInsertQuery interface:

    interface IInsertQuery : IQuery {
      IInsertQuery into(ITable table);
      IInsertQuery values((object or sequence< object > or IBindableValue) rows);
    };
  

IInsertQuery into(ITable table);

Specifies the target table to insert rows into. For insertOrReplace, the table MUST have a primary key, otherwise IntegrityError will be thrown.

IInsertQuery values((object or sequence< object > or IBindableValue) rows);

Supplies the row(s) to be inserted. When given an object as parameter, or the bindable value resolved as an object, the object will act as a single row; otherwise, each element in the given array is treated as an individual row.

All methods of IInsertQuery returns the calling IInsertQuery object for cascade builder pattern. The methods MUST be called once and only once per query before its execution, otherwise SyntaxError MUST be raised.

If an IInsertQuery object is created from insert, and a row with duplicated primary key is given, ConstraintError MUST be thrown if query engine detected integrity violation. On the other hand, if the IInsertQuery object is from insertOrReplace, the offended row MUST be silently replaced by the new row provided, effectively as removing the offended row and insert the offending row.

If any of the inserted row causes integrity violation other than primary key, a ConstraintError MUST be thrown regardless.

If any of the given rows does not exactly match the table schema of the table specified, DataError MUST be raised. Exact match means that each column specified in the table schema MUST appear as a property of any object provided as row.

IBindableValue can also resolve as an array of objects to provide multiple rows in one call.

Delete Query

Delete queries are used to delete rows in a table. The rows to be deleted are specified via Search Condition. If the search condition is not provided, all rows in the table specified by from() will be deleted. The delete query implements the IDeleteQuery interface:

    interface IDeleteQuery : IQuery {
      IDeleteQuery from(ITable table);
      IDeleteQuery where(ILogicalPredicate searchCondition);
    };
  

IDeleteQuery from(ITable table);

Specifies the target table to delete rows from. This method MUST be called before execution, or SyntaxError will be thrown.

IDeleteQuery where(ILogicalPredicate searchCondition);

Specifies the search condition for filtering rows.

All methods of IDeleteQuery returns the calling IDeleteQuery object for cascade builder pattern. The methods can only be called once per query, otherwise SyntaxError MUST be raised.

If the table is empty, or the search condition returns no matching rows, the delete query will finish successfully without doing anything. If deleting rows cause data integrity violation (for example, deleting a row that is referred in a foreign key), a ConstraintError MUST be raised.

Search Condition

Search condition specifies how the query engine should search the rows for query. They are represented by logical predicates, will be evaluated for rows in the database during query execution, and returns true if the evaluated row is a match or false otherwise. Each logical predicate implicitly carries a scope, which defines the tables that the predicate shall be evaluated against. All rows in these tables will be evaluated. The query engine MAY apply speed up techniques to accelerate the evaluation, for example, using indices.

Logical predicates are obtained from either comparison, truth, or logical predicates. Comparison and truth predicates are obtained from IColumn objects. Complement of predicate is provided by the not function in IDatabaseFunctionProvider.

The comparison or truth predicates can only compare the column type of the column associated. An implicit typeof will be called against the provided value to get the value type. If the provided value type is not the same as the column type, a TypeError will be thrown.

This spec does not provide implicit type conversion. All types MUST be explicit.

      typedef (boolean or Date or number or DOMString) IndexableValueType;
      typedef (IndexableValueType or IBindableValue) ComparableValueType;

      interface IComparisonPredicate {
        ILogicalPredicate eq((ComparableValueType or IColumn) value);
        ILogicalPredicate neq((ComparableValueType or IColumn) value);
        ILogicalPredicate lt((ComparableValueType or IColumn) value);
        ILogicalPredicate lte((ComparableValueType or IColumn) value);
        ILogicalPredicate gt((ComparableValueType or IColumn) value);
        ILogicalPredicate gte((ComparableValueType or IColumn) value);
      };
    

      interface ITruthPredicate {
        ILogicalPredicate between(ComparableValueType lhs, ComparableValueType rhs);
        ILogicalPredicate startsWith((IBindableValue or DOMString) value);
        ILogicalPredicate endsWith((IBindableValue or DOMString) value);
        ILogicalPredicate in((sequence< ComparableValueType > or IBindableValue or ISelectQuery) values);
        ILogicalPredicate isNull();
        ILogicalPredicate isNotNull();
      };
    

      interface ILogicalPredicate {
        ILogicalPredicate and(ILogicalPredicate... childPredicate);
        ILogicalPredicate or(ILogicalPredicate... childPredicate);
        ILogicalPredicate clone();
      };
    

      interface IDatabaseFunctionProvider : IAggregateFunction {
        ILogicalPredicate not(ILogicalPredicate predicate);
      };
    

let dbConnection;
function getDepartmentsWithFounders() {
    let dept = dbConnection.schema().table('Department');
    let emp = dbConnection.schema().table('Employee');
    return dbConnection
        .select(dept.name)
        .from(dept)
        .where(dept['id'].in(
            dbConnection
                .select(emp['deptId'])
                .from(emp)
                .where(emp['id'].lt(12))))
        .commit();
}

    

Update Query

Update queries updates the value of a column in all rows that matches the search condition. If the search condition is not provided, then the update will be applied to all rows of the table. Update queries are modeled in IUpdateQuery interface:

    interface IUpdateQuery : IQuery {
      IUpdateQuery set(IColumn column, ValueType value);
      IUpdateQuery where(ILogicalPredicate searchCondition);
    };
  

IUpdateQuery set(IColumn column, ValueType value);

Set the column to specified value. If the value type is not the same as the column type, a TypeError will be thrown. If the updated value will result in integrity check failure, a ConstraintError will be thrown. This function can be called multiple times per query, but each column can only be used once, otherwise a SyntaxError will be thrown.

IUpdateQuery where(ILogicalPredicate searchCondition);

Specify the search condition of update. This function can only be called once per query, otherwise SyntaxError will be thrown.

Cartesian Product

The Cartesian product of two tables is calculated using the following algorithm.

1. Let T₁ and T₂ denote the tables involved in the Cartesian product, and the target is to have T₁ × T₂.
2. Let R₁ = [R₁₁, R₁₂, R₁₃, ..., R_1M] denote the rows of T₁.
3. Let R₂ = [R₂₁, R₂₂, R₂₃, ..., R_2N] denote the rows of T₂.
4. Cartesian product of T₁ and T₂ are performed in two steps.
1. Let R = R₁ × R₂. This makes R looks like [[R₁₁, R₂₁], [R₁₁, R₂₂], ..., [R₁₁, R_2N], [R₁₂, R₂₁], ..., [R_1M, R_1N]].
2. Flatten all pairs in R as a row, where T₁'s columns are expanded before T₂'s columns. T₁'s columns appear in the order of schema declaration, and the same rule applies to T₂'s column.

Select Scope Algorithm

The scope of a select query is determined by the following algorithm.

1. Let T₁ denote the set of tables specified by from()
2. If innerJoin() existed in query:
1. Let T₂ denote the table specified by inner join
2. Let S₂ denote the scope of the join condition
3. If S₂ ⊄ (T₁ ∪ T₂), throws SyntaxError
3. If leftOuterJoin() existed in query:
1. Let T₃ denote the table specified by left outer join
2. Let S₃ denote the scope of the join condition
3. If S₃ ⊄ (T₁ ∪ T₃), throws SyntaxError
4. Let T₄ denote valid projection scope: T₁ ∪ T₂ ∪ T₃
5. If where() existed in query:
1. Let S₄ denote the scope of search condition
2. If S₄ ⊄ T₄, throws SyntaxError
6. If any column in orderBy(), groupBy(), or the projection list passed in the constructor of ISelectQuery object is not in the valid projection scope, throw SyntaxError.

Sorting Algorithm

The sorting algorithm is represented in the pseudo JavaScript code below.

    var orderBySpecs;  // Array of orderBy() specs given in this query.
    filteredRows.sort(function(lhs, rhs) {
      for (var i = 0; i < orderBySpecs.length; ++i) {
        var spec = orderBySpecs[i];
        var col = spec.column;
        if (lhs[col] == rhs[col]) {
          continue;
        }
        if ((spec.order == 'asc' && lhs[col] < rhs[col]) ||
            (spec.order == 'desc' && lhs[col] > rhs[col])) {
          return -1;
        } else {
          return 1;
        }
      }
      return 0;
    });
  

Inner Join

Inner joins create a new result table by combining column values of two tables involved in the join based upon the predicate given as join condition. Inner joins MAY be specified either implicitly through search conditions, or explicitly via innerJoin(). The following example demonstrates two equivalent inner join syntaxes:

let dbConnection;
let tableA = dbConnection.schema().table('A');
let tableB = dbConnection.schema().table('B');
// Implicit inner join.
let q1 = dbConnection
    .select(tableA['key'], tableA['value'], tableB['timestamp'])
    .from(tableA, tableB)
    .where(tableA['key'].eq(tableB['key']));
// Explicit inner join.
let q2 = dbConnection
    .select(tableA['key'], tableA['value'], tableB['timestamp'])
    .from(tableA)
    .innerJoin(tableB, tableA['key'].eq(tableB['key']));
  

The results of inner join MUST be equivalent to the outcome of the following algorithm:

1. Let T₁ and T₂ denote tables involved in the inner join.
2. Let C denotes the Cartesian product of T₁ and T₂.
3. For each row in C, evaluate the predicate provided as join condition. Return the rows whose evaluation is true.

Conceptually the inner join will create a new table. Implementation MAY NOT create actual table as long as the returned results are correct.

The user is not allowed to mix implicit and explicit join. Implementations will throw either SyntaxError or RuntimeError for invalid queries violating this rule.

Left Outer Join

This specification supports only left outer join, which preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table. The algorithm is described below:

1. Let T_l and T_r denote the left and right table involved in the left-outer join. Let c_l denote columns of T_l, and c_r for T_r.
2. Create an empty result row array R.
3. For each row r in T_l
1. Find matching rows set R_m within T_r so that for each r_m in R the join condition is satisfied by the pair (r, r_m).
2. If R_m is empty, push a row with columns T_r and fill every column with null.
3. For each r_m in R_m, push a new row with columns c_l from r, followed by columns c_r from r_m.
4. Return result array R.

Aggregate Functions

Aggregate functions are used only in the projection list of a select query. The aggregation functions supported are defined in IAggregateFunction:

    interface IAggregateFunction {
      IColumn avg(IColumn col);
      IColumn count(optional IColumn col = null);
      IColumn distinct(IColumn... col);
      IColumn max(IColumn col);
      IColumn min(IColumn col);
      IColumn sum(IColumn col);
    };
  

IColumn avg(IColumn col);

Calculate the arithematic average of non-null values of column col for all rows grouped. If all rows are null, returns null. The returned column is always typed as number. If the col is not typed as number, TypeError will be thrown.

IColumn count(IColumn col);

Count the number of rows. If col is NOT null, returns number of rows in the group whose col is not null, otherwise returns the total number of rows in the group.

IColumn distinct(IColumn... col);

Returns distinct value tuples of the specified columns in all grouped rows. null is considered as a unique value.

IColumn max(IColumn col);

Returns the maximum value of the column col for all rows grouped. The maximum value is determined by the last value of the orderBy() performed on that column. The max() function returns null if and only if all values grouped are null. The type of col MUST be either number, string, or date, otherwise TypeError will be thrown.

IColumn min(IColumn col);

Returns the minimum value of the column col for all rows grouped. The minimum value is determined by the first value of the orderBy() performed on that column. The max() function returns null if and only if all values grouped are null. The type of col MUST be either number, string, or date, otherwise TypeError will be thrown.

IColumn sum(IColumn col);

Calculate the arithematic sum of non-null values of column col for all rows grouped. If all rows are null, returns null. The returned column is always typed as number. If the col is not typed as number, TypeError will be thrown.

The IColumn objects returned by IAggregateFunction MUST NOT be convertible to Column objects.

You cannot use min or max on blob, boolean, and object columns.

Union Projection

Union projection is different from joins. It just attempts to combine the projected results into rows using the following algorithm:

1. For any two different queries in union queries, check if their projected columns are compatible.
   1. Let Q_x and Q_y denote the queries to be checked.
   2. For any column C in Q_x, it MUST satisfy one of the following condition:
      1. No column in Q_y has the same name, and C is nullable.
      2. Another column C' in Q_y has the same name or alias as C, and the column has exactly the same data type.
      Otherwise a TypeError will be thrown.
2. Union all qualified columns as new projection list, execute the queries. Query engine CAN decide the execution orders. Final projected rows MUST be generated in the order of union queries, i.e. the rows from union() query first, then in the order of the parameters inside union().

let dbConnection;
function getOrderList() {
    let order = dbConnection.schema().table('Order');
    let archive = dbConnection.schema().table('ArchivedOrder');
    return dbConnection
        .select(order['item'], order['amount'])
        .from(order)
        .where(order['category'].eq('Clothing'))
        .union(dbConnection
            .select(archive['item'], archive['amount'])
            .from(archive)
            .where(archive.category.eq('Clothing')))
        .commit();
}

  

Intersect and Except

Intersect and except are different from joins, they just attempt to perform set operations on the projected results from involving queries. The set of projected columns from any two queries involved MUST be identical in name/alias and data type, otherwise a TypeError will be thrown.

Results returned from intersect() include all the rows that exist in all involved queries.

Results returned from except() exists only in the calling query.