Database Level 1

Protocol-level concept is very simple. Most complex logic is implemented on client side. Therefore, client can be updated to newer versions without server side changes. Also, there is minimal processing load on server side.

Assumptions

Environment neutrality requirements

1. Date/time must be forced to UTC in all database operations
2. Multiple statement execution must be forbidden in single query() call
3. Unicode charset is assumed
4. Date/time conversion to/from native objects is implementation defined, but ISO-like strings are preferred.
5. String representation should be used, unless there is a native runtime type which can represent DB type without doubt and side-effects.

Insert ID concept

As there is no single approach to retrieve last insert ID across popular database implementations, a special convention is required.

1. For database implementations which do not support select-like query on insert operation, last insert ID must be unconditionally returned as ‘$id’ field on first result row.
2. For other databases, user is responsible for adding RETURNING, OUTPUT or similar implementation-specific clause.
3. For neutral QueryBuilder as special method getInsertID(field) is to be used which always ensures ‘$id’ field in response of successful insert operation.

Protocol Level API

query(as, q)

Executes raw query as is. Result has two variables:

1. rows is array of field value arrays.
2. fields - is array of field names.
3. affected - count of affected rows.

Native associateResult() function can be used to get array of field=>value maps.

It is discouraged to use this function directly. QueryBuilder should be used instead.

Note: the result is packed this way for network size efficiency.

callStored( as, name, args)

Call stored procedure. Result is the same as in query().

Note: not all database engines support this feature.

getFlavour( as )

Get database engine type

Client-side native API

Below is list of additional native interface functions. This level also provides QueryBuilder and Helpers described separately.

• QueryBuilder queryBuilder(type, entity)

  • type - DELETE, INSERT, SELECT, UPDATE

  • entity -

    • table or view name
    • QueryBuilder object to use as sub-query
    • tuple of [entity, alias]
    • null - special case without SQL “FROM”
  • alias - alias to use for referencing

• QueryBuilder delete(entity)

  • calls queryBuilder('DELETE', entity)

• QueryBuilder insert(entity)

  • calls queryBuilder('INSERT', entity)

• QueryBuilder select(entity)

  • calls queryBuilder('SELECT', entity)

• QueryBuilder update(entity)

  • calls queryBuilder('UPDATE', entity)

• void paramQuery(as, String q, Map params={})

  • substitute :name placeholders in q with values from params.
  • then call normal raw query().

• Array associateResult(as_result)

  • process efficiently packed result to get array of associative Map

• Prepared getPrepared(unique_key, callback)

  • A feature for easy re-using prepared statements
  • checks if prepared statement has been already cached with unique key and retuns one if found
  • otherwise, calls callback, stores result and returns it

• Helpers helpers()

  • get associated Helpers instance

Prepared satements

Unlike some database engines which allow server-side statement preparation, this concept assumes preparation of client-side statement in native database engine format.

Typical benefit is removal of repeated complex Query or Xfer builder logic processing on each statement. Instead raw parametrized query is used.

Below is interface of Prepared statement:

• class Prepared:

  • void execute(AsyncSteps as, params=null)

    • params - name => value pairs for substitution
    • executes already built query with optional parameters

  • void executeAssoc(AsyncSteps as, params=null)

    • the same as execute(), but return associated result

Parametrized queries

Usually, such queries are created by builders, but it’s also possible to use custom raw queries with :name placeholders for named parameters to be substituted at execution time. Actual substitution happens on client side.

Examples

1. Raw query with result association

const db = ccm.db();
db.query('SELECT * FROM users');
as.add( (as, res) => {
    res = db.associateResult(res);
    
    for ( let r of res ) {
        const { id, first, last } = r;
        // ...
    }
} );

2. Simple use of QueryBuilder

// executeAssoc() calls associateResult() internally
ccm.db().select( 'users' ).executeAssoc( as );
as.add( (as, rows) => {
    for ( let r of rows ) {
        const { id, first, last } = r;
        // ...
    }
} );

3. Use of prepared statement

The benefit comes when very complex QueryBuilder or XferBuilder logic is used.

// Use as unique key defined once
const SYM_PREPARED = Symbol('SYM_PREPARED');
const db = ccm.db();

const pq = db.getPrapared( SYM_PREPARED, (db) => {
    return db.select( 'users' ).prepare();
} );

pq.executeAssoc( as );
as.add( (as, rows) => {
    for ( let r of rows ) {
        const { id, first, last } = r;
        // ...
    }
} );