FTN17: FutoIn Interface - Database
Version: 1.0
Date: 2017-11-27
Copyright: 2017 FutoIn Project (http://futoin.org)
Authors: Andrey Galkin

CHANGES

1. Intro

Database interface is fundamental part of almost any technology stack. The primary focus is interface for classical Relational Database Systems.

2. Concept

Interface is split into several levels which are combined in inheritance chain.

Fundamental difference from traditional interfaces is lack of large result set support, cursors and explicit transaction control. This is done by intention to forbid undesired database operation patterns.

2.1. Level 1

The very basic level for query execution with minimal safety requirements.

2.2. Level 2

Transaction execution abstraction with "single call" pattern.

The overall idea is to execute a list of statements on DB side in single transaction one-by-one. After each xfer, trivial validation is done like amount of affected rows or count of rows in result. This allows creating complex intermediate checks in native DB query. Such pattern avoids blocking on usually expensive DB connection and forces to execute transaction with no client-side delays. Also, proper release of connection to DB connection pool is ensured.

If at any step an error occurs then whole transaction is rolled back.

Note: internally, it's assumed that there is a limited number of simultaneous DB connection allowed which are managed in connection pool for performance reasons, but such details are absolutely hidden from clients.

2.3. Level 3

Large result streaming through BiDirectional channel. Database metadata and ORM-like abstraction. TBD.

2.4. Client-service design

For cross-operation support, client side should be as much neutral as possible and try to use QueryBuilder interface which should auto-detect actual database type. QueryBuilder is responsible to consistent behavior across databases. The same applies for XferBUilder in Level 2.

Client application should be able to run raw queries depending on L1.getType() result.

2.5. QueryBuilder security & escapes

QueryBuilder must enforce auto-escape of all values. Identifiers must be checked for valid [fully qualified] format to prevent possible injection attacks.

QueryBuilder must throw an error at build or prepare stage, if any of configured query parts are not used.

2.6. QueryBuilder conditions

Conditions can be set as: 1. string - must a be a plain expression used as-is: 2. map: - "field.name" => "value" or "field.name OP" pairs - value is auto-escaped 3. array - special cases for building complex conditions as tree - Optional "AND" (default) or "OR" string in first element indicate join type - any following element is recursive of these rules: string, map or another array

Multiple conditions and/or repeated calls assune "AND" join.

Simple Example:

    select('TableName')
        .where('SomeField IS NULL')
        .where({
            'OtherField' => $val,
            'AnotherField <' => $threshold,
        })

Complex Example:

    select('TableName')
        .where([
            "FieldOne IS NULL",
            [
                "OR",
                "FieldTwo <" => 2,
                "FieldThee IN" => [1, 2, 3], 
            ],
            {
                "FieldFour" => $val,
            }
        ])

2.7. QueryBuilder condition operators

For map conditions optional match operators are supported. The following standard ops are assumed:

Note: EXISTS, ANY and SOME are not supported by design due to known performance issues in many database implementations.

2.8. 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.

2.9. 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.

2.10. Transaction value back references

Very often, transaction requires values returning from previous queries inside the same transaction. As overall concept of this spec forbids transaction processing splitting across requests a special mechanism of placeholders for value back references is required.

  1. There must be database-specific placeholder format to be emdedded in raw query strings when run through L2 xfer() API.
  2. Placeholders must:
  3. An extra query option for template processing must be supported.
  4. If template processing is enabled, the placeholders must be replaced with actual values from previous queries.

2.11. Additional helpers

There are many uniqie database-specific features which may not be implemented in all types. A generic optional Helpers interface is provided. Each end user application must have full coverage unit test run to ensure that particular database flavour is supported, if helpers are used.

3. Interfaces

3.1. Level 1 - query interface

    {
        "iface" : "futoin.db.l1",
        "version" : "{ver}",
        "ftn3rev" : "1.7",
        "imports" : [
            "futoin.ping:1.0"
        ],
        "types" : {
            "Query" : {
                "type" : "string",
                "minlen" : 1,
                "maxlen" : 10000
            },
            "Identifier" : {
                "type" : "string",
                "maxlen" : 256
            },
            "Row" : "array",
            "Rows" : {
                "type" : "array",
                "elemtype" : "Row",
                "maxlen" : 1000
            },
            "Field" : {
                "type" : "string",
                "maxlen" : 256
            },
            "Fields" : {
                "type" : "array",
                "elemtype" : "Field",
                "desc" : "List of field named in order of related Row"
            },
            "Flavour" : {
                "type" : "Identifier",
                "desc" : "Actual actual database driver type"
            },
            "QueryResult" : {
                "type" : "map",
                "fields" : {
                    "rows" : "Rows",
                    "fields" : "Fields",
                    "affected" : "integer"
                }
            }
        },
        "funcs" : {
            "query" : {
                "params" : {
                    "q" : "Query"
                },
                "result" : "QueryResult",
                "throws" : [
                    "InvalidQuery",
                    "Duplicate",
                    "OtherExecError",
                    "LimitTooHigh"
                ]
            },
            "callStored" : {
                "params" : {
                    "name" : "Identifier",
                    "args" : "Row"
                },
                "result" : "QueryResult",
                "throws" : [
                    "InvalidQuery",
                    "Duplicate",
                    "OtherExecError",
                    "LimitTooHigh",
                    "DeadLock"
                ]
            },
            "getFlavour" : {
                "result" : "Flavour"
            }
        }
    }

3.1.1. Native interface extension

3.1.2. Native service extension

3.2. Level 2 - transaction interface

    {
        "iface" : "futoin.db.l2",
        "version" : "{ver}",
        "ftn3rev" : "1.7",
        "inherit" : "futoin.db.l1:1.0",
        "types" : {
            "IntOrBool" : ["integer", "boolean"],
            "XferQuery" : {
                "type" : "map",
                "fields" : {
                    "q" : "Query",
                    "affected" : {
                        "type" : "IntOrBool",
                        "optional" : true,
                        "desc" : "Require changed row count: specific or > 0, if true"
                    },
                    "selected" : {
                        "type" : "IntOrBool",
                        "optional" : true,
                        "desc" : "Require selected row count: specific or > 0, if true"
                    },
                    "result" : {
                        "type" : "boolean",
                        "optional" : true,
                        "desc" : "Return result of the statement"
                    },
                    "template" : {
                        "type" : "boolean",
                        "optional" : true,
                        "desc" : "Process value back references"
                    }
                }                    
            },
            "XferQueryList" : {
                "type" : "array",
                "elemtype" : "XferQuery",
                "minlen" : 1,
                "maxlen" : 100
            },
            "XferResult" : {
                "type" : "map",
                "fields" : {
                    "seq" : "integer",
                    "rows" : "Rows",
                    "fields" : "Fields",
                    "affected" : "integer"
                }
            },
            "XferResultList" : {
                "type" : "array",
                "elemtype" : "XferResult",
                "minlen" : 0,
                "maxlen" : 100
            },
            "IsolationLevel" : {
                "type" : "enum",
                "items" : ["RU", "RC", "RR", "SRL"],
                "desc" : "Refers to standard ISO isolation levels"
            }
        },
        "funcs" : {
            "xfer" : {
                "params" : {
                    "ql" : "XferQueryList",
                    "isol" : "IsolationLevel"
                },
                "result" : "XferResultList",
                "throws" : [
                    "InvalidQuery",
                    "Duplicate",
                    "OtherExecError",
                    "LimitTooHigh",
                    "DeadLock",
                    "XferCondition",
                    "XferBackRef"
                ]

            }            
        }
    }

3.2.1. Native interface extension

3.3. Level 3 - TBD

To be defined in later versions.

=END OF SPEC=