/*****
*
* $Id: Html5Db.js 98 2008-08-15 16:52:44Z klaus $
* Partial implementation of client side database storage for Firefox 2+
* http://www.whatwg.org/specs/web-apps/current-work/multipage/structured.html#sql
* Due to security restrictions in FF this library is restricted to file:// usage
*
*****/

if (!window.openDatabase) {
    /* bail out if we are not FF or not called via file:// */
    if (navigator.userAgent.indexOf("Firefox") == -1) {
        alert('Error: you need Firefox to view this example');
        throw Error('INVALID_BROWSER_ERROR: you need Firefox to view this example');
    }
    if (window.location.protocol != 'file:') {
        alert('Error: this example must be run local with a file:// URL only');
        throw Error('INVALID_PROTOCOL_ERROR: this example must be run local with a file:// URL only');
    }

    /* define a few globals for profile-directory */
    PROFD_DATABASE_DIR = 'databases';           // name of database sub-directory
    PROFD_INDEX_DATABASE = 'Databases.db';  // name of index-database
    PROFD_USER_DATABASE = null;                 // name of requested user-database
    PROFD_USER_DATABASE_PATH = null;            // path to requested user-database

    /* use or create the requested database  */
    openDatabase = function(databaseName, databaseVersion, displayName, estimatedSize) {
        /* grant privileges for IO.js and storage, initialize storageService */
        netscape.security.PrivilegeManager.enablePrivilege('UniversalXPConnect');
        var storageService = Components.classes["@mozilla.org/storage/service;1"].getService(Components.interfaces.mozIStorageService);

        /* create sub-directory to hold databases in ProfD if it does not exist already */
        var dbDir = DirIO.open(DirIO.get('ProfD').path + DirIO.sep + PROFD_DATABASE_DIR);
        if (! dbDir.exists()) {
            DirIO.create(dbDir,0700);
            log('dir','IO: mkdir '+dbDir.path);
        }

        /* create index-database within databases sub-directory if it does not exist already */
        var indexDb = FileIO.open(dbDir.path + DirIO.sep + PROFD_INDEX_DATABASE);
        if (! indexDb.exists()) {
            FileIO.create(indexDb,0700);
            log('file','IO: touch '+indexDb.path);
            log('info','Index database: path set to '+indexDb.path);

            /* open index-database and initialize SQL-schema needed to keep track of user-databases
               webKit createsa  table Origins as well to keep track of quota by domain - this step is skipped here */
            var iConn = storageService.openDatabase(indexDb);
            iConn.executeSimpleSQL("CREATE TABLE Databases (guid INTEGER PRIMARY KEY AUTOINCREMENT, origin TEXT, name TEXT, displayName TEXT, estimatedSize INTEGER, path TEXT)");
            iConn.executeSimpleSQL("CREATE TABLE Origins (origin TEXT UNIQUE ON CONFLICT REPLACE, quota INTEGER NOT NULL ON CONFLICT FAIL)");
            iConn.close();
            log('SQL','Index database: CREATE TABLE Databases (...)');
            log('SQL','Index database: CREATE TABLE Origins (...)');
        }

        /* create domain-directory within databases sub-directory if it does not exist already
           directory name reflects "protocol_host_port" of the origin of the active document
           all user-databases for current domain will be stored here */
        var domainDir = DirIO.open(dbDir.path + DirIO.sep + _defineOrigin());
        if (! domainDir.exists()) {
            DirIO.create(domainDir,0700);
            log('dir','IO: mkdir '+domainDir.path);

            /* append domain to table Origins and assign initial quota */
            var iConn = storageService.openDatabase(indexDb);
            var iState = iConn.createStatement("INSERT INTO Origins (origin,quota) VALUES (?1,?2)");
            iState.bindUTF8StringParameter(0, _defineOrigin());
            iState.bindInt32Parameter(1,5242880);
            iState.execute();
            iState.finalize();
            iConn.close();
        }

        /* if a databaseName has been passed, see if it exists already and perform version check */
        if (databaseName != null) {
            /* open connection to index-database and query table Databases for requested user-database */
            var reqDb = null;
            var iConn = storageService.openDatabase(indexDb);
            var iState = iConn.createStatement("SELECT path FROM Databases WHERE origin=?1 AND name=?2");
            iState.bindUTF8StringParameter(0, _defineOrigin());
            iState.bindUTF8StringParameter(1, databaseName);
            log('SQL',"Index database: SELECT path FROM Databases WHERE origin='"+_defineOrigin()+"' AND name='"+databaseName+"'");
            while (iState.executeStep()) {
                reqDb = iState.getUTF8String(0)
                log('Info','Database '+databaseName+': path set to '+reqDb);
            }
            iState.reset();
            iState.finalize();  // prevent NS_ERROR_FILE_IS_LOCKED exception error on closing
            iConn.close();

            /* if databaseName is known already, check for version and throw error on mismatch */
            if (reqDb != null) {
                var curDb = FileIO.open(domainDir.path + DirIO.sep + reqDb);
                var uConn = storageService.openDatabase(curDb);
                uState = uConn.createStatement("SELECT revision FROM __version__");
                log('SQL','Database '+databaseName+': SELECT revision FROM __version__');
                while (uState.executeStep()) {
                    if (databaseVersion == uState.getString(0)) {
                        log('Info','Database '+databaseName+': Version matches');
                        /* update displayName and estimatedSize in Databases table - is this needed? Spec says nothing */
                        var iConn = storageService.openDatabase(indexDb);
                        var iState = iConn.createStatement("UPDATE Databases SET displayName=?1, estimatedSize=?2 WHERE origin=?3 AND name=?4");
                        iState.bindUTF8StringParameter(0, displayName != null ? displayName : databaseName);
                        iState.bindUTF8StringParameter(1, estimatedSize != null ? estimatedSize : 512000);
                        iState.bindUTF8StringParameter(2, _defineOrigin());
                        iState.bindUTF8StringParameter(3, databaseName);
                        iState.execute();
                        iState.finalize();  // do not reset() when execute() was used
                        iConn.close();

                        log('SQL',"Index database: UPDATE Databases SET displayName='"+displayName+"', estimatedSize="+estimatedSize+" WHERE origin='"+_defineOrigin()+"' AND name='"+databaseName+"'");
                        log('Info','Database '+databaseName+': Database ready');
                    }
                    else if (databaseVersion == '') {
                        /* no version provided - any version is fine */
                    }
                    else {
                        /* throw error */
                        uState.reset();
                        uState.finalize();
                        uConn.close();
                        alert('Error: versions do not match for database '+reqDb);
                        throw Error('INVALID_STATE_ERR: versions do not match for database '+reqDb);
                    }
                    // set name and path to current user-database
                    PROFD_USER_DATABASE = databaseName;
                    PROFD_USER_DATABASE_PATH = curDb.path
                }
                uState.reset();
                uState.finalize();
                uConn.close();
            }
            else {
                log('Info','Database '+databaseName+': extra steps needed');

                /* add record to index-database */
                var iConn = storageService.openDatabase(indexDb);
                iState = iConn.createStatement("INSERT INTO Databases (origin,name,displayName,estimatedSize) VALUES (?1,?2,?3,?4)");
                iState.bindUTF8StringParameter(0, _defineOrigin());
                iState.bindUTF8StringParameter(1, databaseName);
                iState.bindUTF8StringParameter(2, displayName != null ? displayName : databaseName);
                iState.bindInt32Parameter(3, estimatedSize != null ? estimatedSize : 512000);
                iState.execute();
                iState.reset();
                iState.finalize();
                log('SQL',"Index database: INSERT INTO Databases (origin,name,displayName,estimatedSize) VALUES ('"+_defineOrigin()+"','"+databaseName+"','"+displayName+"',"+estimatedSize+")");

                /* update path to user-database according to sequence */
                var lastId = iConn.lastInsertRowID;
                var curDbPath = _createPathId(iConn.lastInsertRowID)+".db"
                iState = iConn.createStatement("UPDATE Databases SET path=?1 WHERE guid=?2");
                iState.bindUTF8StringParameter(0, curDbPath);
                iState.bindInt32Parameter(1, lastId);
                iState.execute();
                iState.reset();
                iState.finalize();
                iConn.close();
                log('SQL',"Index database: UPDATE Databases SET path='"+curDbPath+"' WHERE guid="+lastId);

                // create current user-database with table __version__ and insert version
                var curDb = FileIO.open(domainDir.path + DirIO.sep + curDbPath);
                FileIO.create(curDb,0700);
                log('file','IO: touch '+curDb.path);
                log('info','Database '+databaseName+': path set to '+curDbPath);

                // set name and path to current user-database
                PROFD_USER_DATABASE = databaseName;
                PROFD_USER_DATABASE_PATH = curDb.path

                var uConn = storageService.openDatabase(curDb);
                uConn.executeSimpleSQL("CREATE TABLE __version__ (revision TEXT)");
                log('SQL','Database '+PROFD_USER_DATABASE+': CREATE TABLE __version__ (...)');
                if (databaseVersion != null) {
                    uState = uConn.createStatement("INSERT INTO __version__ VALUES (?1)");
                    uState.bindUTF8StringParameter(0,databaseVersion);
                    uState.execute();
                    uState.reset();
                    uState.finalize();
                    log('SQL','Database '+PROFD_USER_DATABASE+": INSERT INTO __version__ VALUES ('"+databaseVersion+"')");
                    log('Info','Database '+databaseName+': Extra steps OK. Database ready');
                }
                else {
                    // TODO SPEC: what if no version is provided? Asume empty string?
                    alert('Error: no version provided - use empty string at least');
                    throw Error('INVALID_STATE_ERR: no version provided - use empty string at least');
                }
                uConn.close();
            }
            return new Database();
        }
        else {
            // TODO SPEC: what if no databaseName is provided?
            alert('Error: no databaseName provided - use empty string at least');
            throw Error('INVALID_STATE_ERR: no databaseName provided - use empty string at least');
        }
    }

    _defineOrigin = function() {
        /* create dir-name for user-data sub-directory according to protocl, host and port */
        var oProt = (window.location.protocol || 'file:').replace(":","").toLowerCase();
        var oHost = (window.location.hostname || '').toLowerCase();
        var oPort = window.location.port || '0';
        return oProt+'_'+oHost+'_'+oPort;
    }

    _createPathId = function(n) {
        /* create user-database name by integer passed */
        var id = "0000000000000000"
        for (var i=0;i<n.toString().length;i++) {
            id = id.replace(/0$/,"");
        }
        return id+n.toString();
    }

    _completePlaceholders = function(sql) {
        /* FF requires placeholders to be writtn with ?1, ?2, etc instead of ? only as stated in the spec */
        if (sql.indexOf('?') != -1) {
            var n = 1;
            var l = [];
            for (var i in sql.split("")) {
                l[l.length] = sql[i];
                if (sql[i] == "?") {
                    l[l.length] = n;
                    n++;
                }
            }
            return l.join("");
        }
        return sql;
    }

    /*****
    *
    * implementation
    *
    *****/
    var txCount = 0;    // sequence for transactions, defined but not used right now
    function Database() {
        this.changeVersion = function(oldVersion, newVersion, callback, errorCallback, successCallback) {
            // TODO: implement changeVersion according to http://www.whatwg.org/specs/web-apps/current-work/multipage/structured.html#changeversion
        }
        this.transaction = function(callback, errorCallback, successCallback) {
            callback && (function() {
                var tx = new SQLTransaction();
                try {
                    callback(tx);
                    successCallback && successCallback(tx);
                }
                catch(e) {
                    alert(e.message);
                    log('Error', 'Error code '+e.code+': '+e.message);
                    errorCallback && errorCallback(tx,e);
                }
            })();
        };
        this.version = null;    // TODO: implement version, expected version

        /* TODO: implement transaction correctly with SQLTransactionCallback and SQLTransactionErrorCallback */
    }

    function SQLTransaction() {
        var tx = this;          // allow to pass on transaction-obeject to method executeSQL
        var txSeq = txCount++;  // increment transaction sequence - not used right now?

        this.executeSql = function(sqlStatement, sqlArgs, sqlStatementCallback, sqlStatementErrorCallback) {
            /* mozilla security does not allow to "reuse" a global connection,
               thus connect to the database each time executeSQL is invoked.
               PROFD_USER_DATABASE_PATH holds physical path to user-database */

            log('SQL','Database '+PROFD_USER_DATABASE+': '+sqlStatement);
            netscape.security.PrivilegeManager.enablePrivilege('UniversalXPConnect');
            var storageService = Components.classes["@mozilla.org/storage/service;1"].getService(Components.interfaces.mozIStorageService);
            var uConn = storageService.openDatabase(FileIO.open(PROFD_USER_DATABASE_PATH));

            /* create sqlStatment, bind parameters according to type if any
               it's unclear how to deal with types that are not available in SQLITE
               thus boolean types are mapped to 0 and 1 for now */
            try {
                uState = uConn.createStatement(_completePlaceholders(sqlStatement));
                if (sqlArgs) {
                    for (var i=0;i<sqlArgs.length;i++) {
                        switch((typeof sqlArgs[i])) {
                            case 'string'  : uState.bindUTF8StringParameter(i,sqlArgs[i]);
                            case 'number'  : sqlArgs[i].toString().indexOf(".") == -1
                                            ? uState.bindInt32Parameter(i,sqlArgs[i])
                                            : uState.bindDoubleParameter(i,sqlArgs[i]);
                            case 'boolean' : sqlArgs[i] == true
                                            ?  uState.bindInt32Parameter(i,1)
                                            :  uState.bindInt32Parameter(i,0);
                            case 'object'  : sqlArgs[i] == null
                                            ? uState.bindNullParameter
                                            : uState.bindUTF8StringParameter(i,sqlArgs[i]);
                        }
                    }
                }

                /* create new SQLResultSetRowList-object, execute statement and fill columns according to type */
                var rc = 0;
                var rows = new SQLResultSetRowList();
                while (uState.executeStep()) {
                    var row = {}
                    for(var i = 0; i < uState.numEntries; i++) {
                        var col = uState.getColumnName(i).toLowerCase();
                        switch(uState.getTypeOfIndex(i)) {
                            case 0 : row[col] = null;                    break;    // VALUE_TYPE_NULL
                            case 1 : row[col] = uState.getInt32(i);      break;    // VALUE_TYPE_INTEGER
                            case 2 : row[col] = uState.getDouble(i);     break;    // VALUE_TYPE_FLOAT
                            case 3 : row[col] = uState.getUTF8String(i); break;    // VALUE_TYPE_TEXT
                            case 4 : row[col] = uState.getBlob(i);       break;    // VALUE_TYPE_BLOB
                        }
                    }
                    rows[rc] = row;
                    rc++;
                }
                rows.length = rc; // set attribute length of SQLResultSetRowList-object

                /* set insertId and rowsAffected if any */
                insertId = uConn.lastInsertRowID ? uConn.lastInsertRowID : null;
                rowsAffected = null;    // mozStorage lacks this attribute

                /* reset statement and close connection */
                uState.reset();
                uState.finalize();
                uConn.close();

                /* invoke callbacks if any and pass insertId, rowsAffected and SQLResultSetRowList */
                if (sqlStatementCallback) {
                    /* TODO: sqlStatementCallback not used at it's supposed to be used as a SQLStatementCallback */
                    sqlStatementCallback(tx,new SQLResultSet(insertId, rowsAffected, rows));
                }
            }
            catch(e) {
                alert('Error: '+uConn.lastErrorString);
                if (sqlStatementErrorCallback) {
                    /* TODO: sqlStatementErrorCallback not used at it's supposed to be used as a SQLStatementErrorCallback */
                    sqlStatementErrorCallback(tx,new SQLError(1,uConn.lastErrorString));
                }

                /* reset statement and close connection */
                uState.reset();
                uState.finalize();
                uConn.close();

                /* notify */
                log('Error', 'Error code 1 '+uConn.lastErrorString);
                throw Error(uConn.lastErrorString);
            }
        }
    }

    function SQLResultSet(insertId, rowsAffected, rows) {
        this.insertId = insertId;
        this.rowsAffected = rowsAffected;
        this.rows = rows;
    }

    function SQLResultSetRowList() {
        this.length = 0;
        this.item = function(i) {
            if (! this[i]) {
                throw new SQLError(0,'INDEX_SIZE_ERR - requested item does not exist');
            }
            else {
                return this[i];
            }
        }
    }

    function SQLError(code, message) {
        this.code = code;
        this.message = message;
    }

    function log(ltype,lmsg) {
        if (! window.CONSOLE_MESSAGES) {
            window.CONSOLE_MESSAGES = [];
        }
        window.CONSOLE_MESSAGES[window.CONSOLE_MESSAGES.length] = [ltype,lmsg];
    }
}