Notes on different backends and their capabilities.

Documentation references:
-------------------------
- mysql   : http://dev.mysql.com/doc/refman/5.1/en
- sqlite  : http://sqlite.org/lang.html
- postgres: http://developer.postgresql.org/pgdocs/postgres/
- mssql   : http://msdn2.microsoft.com/en-us/library/ms189826.aspx
- oracle  : http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
- oracle  : http://www.fdi.ucm.es/profesor/hector/DB-II/SQL_Reference.pdf (the mother of all Oracle SQL language references)

VIEWS support:
-------------
- mysql   : yes, since 5.0
- sqlite  : yes, since forever, but read only (which is a good thing,  but a pain when you're migrating )
- postgres: yes, since 7.3 (at least)
- mssql   : yes, no idea version wise
- oracle  : yes, all versions since the mid-1990s

==> SUPPPORTED BY ALL, WORTH CONSIDERING
==> Usecase: Transposing Dynamic Data values.

    Suppose you have roles extended in your install. (or in general, items based on a fixed table)
    That means that the code has to go through quite a few hoops to get all the
    data together. The backend, with the help of a structured view can do this a lot faster.
    Suppose we have roles extended by adding 4 properties: address, postalcode, city, country 
    corresponding to property id's 1,2,3 and 4
    
    CREATE VIEW dd_roles AS
    SELECT xar_dd_itemid AS role_id,  
        MAX(CASE xar_dd_propid WHEN 59 THEN d.xar_dd_value ELSE null END) AS address,
        MAX(CASE xar_dd_propid WHEN 60 THEN d.xar_dd_value ELSE null END) AS postalcode,
        MAX(CASE xar_dd_propid WHEN 61 THEN d.xar_dd_value ELSE null END) AS city,
        MAX(CASE xar_dd_propid WHEN 70 THEN d.xar_dd_value ELSE null END) AS country,
    FROM xar_dynamic_data d WHERE xar_dd_propid IN (1,2,3,4)
    GROUP BY xar_dd_itemid;
    
    This results in the values being in columns rather than rows. It's trivial to
    add the columns from xar_roles by joining on role_id (or, in general the itemid of the extended table)
    
    The above views could be generated automatically by the dd module. 
    In general, the view will be read-only, i.e. a statement like UPDATE dd_roles SET.... wont work.

TRANSACTION support:
-------------------
- mysql   : yes, innodb engine only ( gracefull fallback provided by middleware?)
- sqlite  : yes, since forever
- postgres: yes
- mssql   : yes
- oracle  : yes

==> SUPPORTED BY ALL, BEING USED RIGHT NOW.

NESTED TRANSACTION support:
---------------------------
- mysql   : NO (see http://dev.mysql.com/doc/refman/5.1/en/commit.html )
- sqlite  : NO
- postgres: NOT BEFORE 7.5 at least
- mssql   : YES ( see http://www.codeproject.com/database/sqlservertransactions.asp )
- oracle  : YES

==> We have to make sure we have only one transaction active at any given time
Having multiple transactions active is hidden by creole by just not committing
until the transactioncount reached zero again. This will not work in cases
where the sequence of code doing database operations has a functional
dependency, unless these dependencies are all in the same transaction. This
can lead to very confusing errors while running the code. Seemingly
random/ad-hoc errors start to appear. The exact logic is hard to describe and
can differ between backends. (mysql does an auto commit for an active
transaction when a new one is started for example, while others do not)

We could consider: 
-> raising an exception during development phase when 2 transactions are active
-> keep current creole behaviour and watch logs (there are clearly greppable log statements issued)


FOREIGN KEY support:
-------------------
- mysql   : yes, innodb engine only
- sqlite  : no, accepts syntax, but doesnt act (triggers is the proposed alternative)
- postgres: yes
- mssql   : yes, no idea version wise
- oracle  : yes, no idea version wise

Foreign key constrained columns can be NULL?
--------------------------------------------
- mysql   : yes
- sqlite  : yes (n/a, needs to be handled by trigger)
- postgres: yes
- mssql   : ?
- oracle  : yes (handled by a trigger or a check-constraint)

[Note: this is important to know because we encode 'none' and 'empty' with null, so
if that meaning is not correct, the on update/delete etc. trigger either need to
prevent it or take another appropriate action to make sure if the action is valid the
constraints are met]

HASH index support:
------------------
- mysql   : yes, memory tables only, only used for equality tests (obviously)
- sqlite  : no
- postgres:
- mssql   :
- oracle  :

IN MEMORY TABLE support:
-----------------------
- mysql   : yes
- sqlite  : yes, in memory databases only
- postgres:
- mssql   :
- oracle  :

IN MEMORY DATABASE support:
--------------------------
- mysql   :
- sqlite  : yes
- postgres:
- mssql   : yes
- oracle  : yes, both shared structure (private data) and per-session temporary tables

TRIGGER support:
---------------
- mysql   : yes, since 5.0.2
- sqlite  : yes, since forever
- postgres: yes, at least since 7.3
- mssql   : yes, at least since mssql server 2000
- oracle  : yes, since mid-1990s

==> SUPPORTED BY ALL, WORTH CONSIDERING

Syntax notes:
    mysql: 
        CREATE TRIGGER someTriggerName BEFORE INSERT ON sometable
        FOR EACH ROW
        BEGIN
            SET NEW.guid = UUID();
        END
        --> no instead of trigger (no need)
        --> only before insert, not after insert

    sqlite: 
        // This trigger runs instead of the supplied statement to
        // workaround the read-only limitation of a view
        CREATE TRIGGER someTriggerName INSTEAD OF INSERT ON SomeView
        [FOR EACH ROW]
        BEGIN
            INSERT INTO RealTable
            VALUES( NEW.fieldName1, NEW.FieldName2,...,NEW.FieldNameN);
        END;

        // This trigger runs when an update occurs on sometable.somefield
        CREATE TRIGGER someTriggerName AFTER UPDATE OF somefield ON  sometable
        [FOR EACH ROW]
        BEGIN
            UPDATE someOtherTable SET someOtherField = NEW.somefield
            WHERE someOtherTable.YetAnotherField = OLD.AgainSomethingElse;
        END;

        The trick is the new an old designators obviously
        -> on update NEW and OLD are valid
        -> on insert NEW is valid
        -> on delete OLD is valid
        -> only FOR EACH ROW triggers (the default also)
        
    postgres: 
        TBD
        
CHECK constraint support:
------------------------
- mysql   : yes
- sqlite  : yes
- postgres: yes
- mssql   : yes
- oracle  : yes

==> SUPPORTED BY ALL, WORTH CONSIDERING
Note: the expression syntax is likely to differ wildly between the backends.
Value check might work, but it's hell to compare and document the different
syntaxes. Keep this in mind and look it up when a check is needed.

STORED procedures support:
------------------------
- mysql   : yes
- sqlite  : no
- postgres: yes
- mssql   : yes
- oracle  : yes



Native query preparation support
--------------------------------
- sqlite    : no
- mysql     : yes, since 4.1.3 at SQL level (no api)

              PREPARE <stmtname> FROM <query>
              SET @bindvar_1 = value_1
              ...
              SET @bindvar_n = value_n
              EXECUTE <stmtname> USING @bindvar_1,...,@bindvar_n

- mysqli    : php >= 5 has api

                $mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
                $stmt   = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
                $stmt->bind_param('sssd', $code, $language, $official, $percent);
                $stmt->execute();
                $stmt->close();

                Seems that the resultset returned with this new interface is not compatible
                with the old resultset!!!!!
- pgsql    :    yes, both at SQL and PHP level

                SQL level:
                    PREPARE <plan-name> AS <query without bindvars>
                    EXECUTE <plan-name>
                    PREPARE <plan-name> (datatype_1,...,datatyp_n) AS <query with bindvars>
                    EXECUTE <plan-name> value_1,...,value_n

                PHP level:
                    $conn = pg_connect("dbname=mary");

                    $result = pg_prepare($dbconn, "my_query", 'SELECT * FROM shops WHERE name = $1');
                    $result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));
                    $result = pg_execute($dbconn, "my_query", array("Clothes Clothes Clothes"));
- mssql     :    Unknown, it's possible in embedded SQL, no idea if transact SQL does support it.
- oracle    :    using PL/SQL or Java
              Can be done in stages (prepare, parse, execute) or in a single step.

              DECLARE
	          /* These variables could also come from trigger columns (e.g. :NEW.name) or be stored procedure parameters. */
	          l_id NUMBER;
	          l_name VARCHAR(60);
	      BEGIN
	          ...
                  EXECUTE IMMEDIATE 'UPDATE foobar SET name = :1 WHERE id = :2' USING l_name, l_id;
	      END;


Comments Syntax support for SQL:

- mysql   : /*..*/, --, #
- pgsql   : /*..*/, --
- sqlite  : /*..*/, --
- oracle  : /*..*/
- mssql   : /*..*/

==> Recommended : /*..*/

Datatype notes:

Acceptance in SQL:
(NOTE: means nothing about actual storage and how it behaves, just means SQL doesnt produce an error)

::

                                          mysql   pgsql   sqlite    oracle   mssql
    tinyint                                X       -        X         X        X
    smallint                               X       X        X         X        X
    mediumint                              X       -        X         -        -
    bigint                                 X       X        X         X        X
    int                                    X       X        X         -        X
    integer                                X       X        X         X        -

    bit                                    X       X        X         X        X
    bool                                   X       -        X         -        -
    boolean                                X       X        X         -        -

    float                                  X       -        X         X        X
    double [precision]                     X       X        X         X        X
    real                                   -       X        X         X        X

    decimal                                X       X        X         X        X
    numeric                                X       X        X         X        X
    dec                                    X       -        X         -        -
    fixed                                  X       -        X         -        -

    char                                   X       X        X         X        X
    varchar                                X       X        X         X        X
    longvarchar                            -       -        X         X        -
    text                                   X       X        X         -        X
    tinytext                               X       -        X         -        -
    mediumtext                             X       -        X         -        -
    longtext                               X       -        X         -        -

    money                                  -       X        X         -        X
    smallmoney                             -       -        X         -        X

    date                                   X       X        X         X        -
    datetime                               X       X        X         -        X
    smalldatetime                          -       -        X         -        X
    timestamp                              X       X        X         X        X
    interval                               -       X        X         -        -
    time                                   X       X        X         X        -
    year                                   X       -        X         -        -

    binary                                 X       -        X         X        X
    bytea                                  -       X        X         -        -
    varbinary                              X       -        X         X        X
    long varbinary                         X       -        X         X        -
    image                                  -       -        X         -        X
    blob                                   X       -        X         -        -
    tinyblob                               X       -        X         -        -
    mediumblob                             X       -        X         -        -
    longblob                               X       -        X         -        -

    rowid                                  -       -        X         X        -
    serial                                 -       X        X         -        -
    bigserial                              -       X        X         -        -




ID generation revisited
-----------------------
I've never understood our current solution for dealing with IDs and why it
evolved into what it is now. What follows is how I think it should be done. We
can revisit this at an appropriate time.

The main problem:
    Some databases have a native type to generate auto incrementing values
    transparently, while others use triggers or sequences to make it somewhat
    less transparent. The main problem lies in the question on how to abstract
    both in the same way.

Current concept:
    - call genID before an insert, that delivers something back (no matter
      what it is)
    - call a SQL insert statement, putting whatever genID delivered back in
      the place of the column which should hold the ID
    - when needed call PO_Insert_ID to retrieve the actual value (and pray it
      is the right one) of the ID used.

    [UPDATE: with a current Creole patch, the behaviour is now safe]

Problems with this concept:
    - the genID call is useless, it generates nothing for auto increment type
      systems and advances the sequence for sequence based systems too early,
      thus creating a dependency (i.e. you really cant call genID, wait for
      some user input for example and then go on, you basically need to do the
      insert right away and hope you do it fast enough)
    - the insert touches an ID column, allowed of course, and needed in our
      concept but it doesnt serve any purpose, as you cant influence it
      anyways
    - the PO_Insert_ID implementation is a hack and unreliable, especially in
      high concurrency environments. (it uses select max(id) which may have
      been advanced to 10 ids further on by the time you're calling it.)

    [UPDATE: this has now been adapted in Creole to do a better thing, so
    this argument is basically void now]


Alternative concept:
    The distinction between the two types of backends is nothing we can do
    about, it's a given.

    My ideal concept would be:
    - call a SQL insert statement
    - retrieve reliably the last inserted ID value if you need it.

    For this to work a number of things need to be taken care of:

    Let's use this example table [pseudo-ddl]::

        create table test {
            id the_autoinc_type,
            name string
            primary key(id)
        }

    which is roughly how we use it everywhere.

    1. the insert SQL should omit the ID column and responsibility of creating
    values for it transferred to the backend,

    Thus this::

        insert into test(id,name) values(genID(),'thename')

    becomes::

        insert into test(name) values ('thename')

    For this to work in both type of backends, both should be able to generate
    their id's automatically and transparently. For the auto increment types
    this is easy, because that type was invented to do just that if you omit
    the column or put a 0 or NULL into it.

    2. For the sequence based backends you explicitly have to tell the database
    to do it (same concept, but explicitly needed)

    Say, for postgres, which is sequence based, this translates to::

        create sequence test_id_seq;
        create table test {
            id integer NOT NULL DEFAULT nextval('test_id_seq'),
            name string
            primary key(id)
        }

        [in recent versions: use the serial native type, which is almost equivalent]

    3. the last insert id retrieval needs to be reliable.
    Again, for the auto increment type backend, there is usually an SQL construct
    or API function which does exactly that. Similarly, for the sequence based
    system, there are either SQL or API constructs to get the same information
    from the sequence.

    Examples::

            mysql: SELECT LAST_INSERT_ID()
            (not completely safe, because it is global on the session)

            postgres: SELECT curval('test_id_seq')

    For the sequence based systems this is safe, as it only considers the
    current session.

As we use the genID like a million times all over this saves a whole lot of
code and makes everything simpler and more reliable in my opinion.

Does this work for all databases? Here's an overview

- mysql :   no changes required to the db model in use now
- sqlite:   no changes required to the db model in use now
- postgres: either use the serial datatype where an autoincrement is needed (recommended)
            or define the sequence explicitly and the column too
- oracle:   define the sequence and either use a before insert trigger or
            the definition of the default value on the sequence (not sure if that is supported
            [UPDATE: it is NOT, damn] - the closest is to use trigger_name.nextval in the insert statement,
	    which is not portable or transparent, though should in theory be all that genID needs to return,
	    so long as it does not get quoted when put into the query, e.g. INSERT INTO foobar (f_id) VALUES(foobar_seq.NEXTVAL)
- mssql   : mssql uses the IDENTITY column, which does exactly what it says, i.e.
            provide an identifier. The catch is that that column can NOT be used explicitly
            unless the backend is explicitly told so with ``SET IDENTIY INSERT tablename ON;``
            which is what we do now. If the GenID construct we use now is migrated out, we
            theoretically can move that construct out. mssql is inflexible, but it just happens
            to fit the alternative concept ;-)


SQL Change Details:

Mysql:
------

::

    CREATE TABLE tablename (
        colname INTEGER AUTO_INCREMENT PRIMARY KEY;
    );

Postgres:
---------

::

    CREATE TABLE tablename (
        colname SERIAL
    );

    OR

    CREATE SEQUENCE tablename_colname_seq;
    CREATE TABLE tablename (
        colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
    );
    ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

    obviously the first is preferred.

Sqlite:
-------

::

    CREATE TABLE tablename (
        colname UNSIGNED INTEGER PRIMARY KEY
    );


Oracle:
-------
    /* Use the NOCACHE option to ensure contiguous sequences, otherwise
       they are fetched in batches of N (defaut 10) with any not used in
       the session being discarded. Alternatively use 'CACHE 1' to ensure
       only one is fetched each time and none are wasted. */
    CREATE SEQUENCE tablename_colname_seq START WITH 1 NOCACHE;
    CREATE TABLE tablename (
        colname NUMBER
    )
    CREATE TRIGGER tablename_insert BEFORE INSERT ON tablename
    FOR EACH ROW
    WHEN (NEW.colname IS NULL)
    BEGIN
     SELECT x.NEXTVAL INTO :new.colname FROM DUAL;
    END;

MS SQL:
-------

::

    CREATE TABLE tablename (
        colname IDENTITY
    )

    [
        Note: the middleware will have to execute
        'SET IDENTITY INSERT tablename ON';
        before every insert if you want to touch the colname explicitly
        i.e. the column colname is normally protected and should not be
        included in INSERT statements.
    ]


Relevant postgres changelog entries:

8.0
    - windows native server
    - alter table can change column type
8.1
    - multi machine prepares (two phase commit)
    - default values of columns as ::regclass not ::text, this means that if
      an expression in a default value uses a named object, the default value
      expression follows suite if that object gets renamed (we use this
      feature for sequences, hence the 8.1 min. required version) 8.1.4
    - unsafe usage of \' in string literals is rejected now


Other backends
==============
There are some other backends which have an API in PHP.

Firebird/Interbase:
    The old borland product which went open source a while ago. The server
    exists in 2 varieties if i recall correctly, something like a standard
    version and a superserver. No idea what the differences are. The database
    (from what i remember) is a bit weird to connect to, as the path on the
    server needs to be known, but other than that its a pretty standard
    database. It runs on linux and windows in any case. For this backend to be
    supported a driver section in Creole would have to be developed. There
    will also be an PDO driver for this database. (speaking of which, a PDO
    driver for Creole would be a good long term investment if you're doing
    creole work. It consolidates the driver interface and the innovative
    frameworks are moving towards it.)

IBM DB2 universal database
    Free as in beer. Available for many platforms but mostly a \*nix bases
    product if the types of platforms are a good criterium for it. I know next
    to nothing about this database

Informix
    No known information

Frontbase
    No known information

SAP DB / MaxDB
    No known information