<|start_filename|>package.json<|end_filename|>
{
  "name": "cordova-plugin-websql",
  "version": "0.0.10",
  "description": "Cordova Websql Plugin",
  "cordova": {
    "id": "cordova-plugin-websql",
    "platforms": [
      "wp8",
      "windows8",
      "windows"
    ]
  },
  "repository": {
    "type": "git",
    "url": "https://github.com/MSOpenTech/cordova-plugin-websql.git"
  },
  "keywords": [
    "cordova",
    "websql",
    "ecosystem:cordova",
    "cordova-windows8",
    "cordova-windows",
    "cordova-wp8"
  ],
  "author": "Microsoft Open Technologies Inc",
  "license": "Apache 2.0"
}


<|start_filename|>src/windows/SQLite.Proxy/SQLiteProxy.cs<|end_filename|>
﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using Community.CsharpSqlite.SQLiteClient;

namespace SQLite.Proxy
{
    public sealed class SQLiteProxy
    {
        private static Dictionary<long, SqliteConnection> _dbConnections = new Dictionary<long, SqliteConnection>();
        private static int _retriesCount = 3;

        public static string ConnectToDb(string dbname)
        {
            var result = new ConnectionInfo();

            for (int i = 0; i < _retriesCount; i++)
            {
                try
                {
                    var connectionString = string.Format("Version=3,uri=file:{0}", dbname);
                    var connection = new SqliteConnection(connectionString);
                    connection.Open();

                    var newId = _dbConnections.Keys.DefaultIfEmpty(0).Max() + 1;
                    _dbConnections.Add(newId, connection);
                    result.Id = newId;

                    String[] emptyParams = { };
                    Object[] tempQuery = { "PRAGMA temp_store=MEMORY;", emptyParams };
                    executeSql(newId, tempQuery);

                    Object[] pageQuery = { "PRAGMA page_size=4096;", emptyParams };
                    executeSql(newId, pageQuery);

                    Object[] synchronousQuery = { "PRAGMA synchronous=off ;", emptyParams };
                    executeSql(newId, synchronousQuery);

                    break;
                }
                catch (Exception ex)
                {
                    if (i == _retriesCount - 1)
                        return Serialize(typeof(InvocationError), new InvocationError(ex));
                }
            }

            return Serialize(typeof(ConnectionInfo), result);
        }

        public static string getVersion(string dbname)
        {
            var result = new object();

            for (var i = 0; i < _retriesCount; i++)
            {
                try
                {
                    var connection = new SqliteConnection(string.Format("Version=3,uri=file:{0}", dbname));
                    connection.Open();

                    result = new SqliteCommand("PRAGMA user_version", connection).ExecuteScalar();

                    connection.Close();
                    break;
                }
                catch (Exception ex)
                {
                    if (i == _retriesCount - 1)
                        return Serialize(typeof(InvocationError), new InvocationError(ex));
                }
            }

            return Serialize(result.GetType(), result);
        }

        public static string setVersion(string dbname, int version)
        {
            var result = -1;

            for (int i = 0; i < _retriesCount; i++)
            {
                try
                {
                    var connection = new SqliteConnection(string.Format("Version=3,uri=file:{0}", dbname));
                    connection.Open();

                    result = new SqliteCommand(string.Format("PRAGMA user_version={0}", version), connection).ExecuteNonQuery();

                    connection.Close();
                    break;
                }
                catch (Exception ex)
                {
                    if (i == _retriesCount - 1)
                        return Serialize(typeof(InvocationError), new InvocationError(ex));
                }
            }

            return Serialize(result.GetType(), result);
        }

        public static string Disconnect(long connectionId)
        {
            try
            {
                var connection = _dbConnections[connectionId];
                connection.Close();
                _dbConnections.Remove(connectionId);
            }
            catch (Exception ex)
            {
                return Serialize(typeof(InvocationError), new InvocationError(ex));
            }
            return "{}";
        }

        public static string executeSql(long connectionId, [ReadOnlyArray()] object[] args)
        {
            try
            {
                var query = (string)args[0];
                var queryParams = (object[])args[1];

                var cmd = _dbConnections[connectionId].CreateCommand();
                cmd.CommandText = query;
                foreach (var queryParam in queryParams)
                {
                    var parameter = cmd.CreateParameter();
                    parameter.Value = queryParam;
                    cmd.Parameters.Add(parameter);
                }
               
                var reader = cmd.ExecuteReader();

                var resultSet = new SqlResultSet();
                while (reader.Read())
                {
                    resultSet.Rows.Add(ReadResultSetRow(reader));
                }

                resultSet.RowsAffected = reader.RecordsAffected;
                resultSet.InsertId = _dbConnections[connectionId].LastInsertRowId;
                return Serialize(typeof(SqlResultSet), resultSet);
            }
            catch (Exception ex)
            {
                // You can't access the original message text from JavaScript code.
                // http://msdn.microsoft.com/en-US/library/windows/apps/br230301.aspx#ThrowingExceptions
                // so we return it via custom object
                return Serialize(typeof(InvocationError), new InvocationError(ex));
            }
        }

        private static QueryRow ReadResultSetRow(DbDataReader reader)
        {
            var row = new QueryRow();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                row.Add(new QueryColumn(reader.GetName(i), reader.GetValue(i)));
            }

            return row;
        }

        private class QueryRow : List<QueryColumn> { }

        private class SqlResultSetRowList : List<QueryRow> { }

        [DataContract]
        private class ConnectionInfo
        {
            [DataMember(Name = "connectionId")]
            public long Id;
        }

        [DataContract]
        private class SqlResultSet
        {
            [DataMember(Name = "insertId")]
            public long InsertId;
            [DataMember(Name = "rowsAffected")]
            public long RowsAffected;
            [DataMember(Name = "rows")]
            public readonly SqlResultSetRowList Rows = new SqlResultSetRowList();
        };

        [DataContract]
        private class QueryColumn
        {
            [DataMember]
            public string Key;
            [DataMember]
            public object Value;

            public QueryColumn(string key, object value)
            {
                Key = key;
                Value = value;
            }
        }

        [DataContract]
        private class InvocationError
        {
            [DataMember(Name = "message")]
            private string Message;

            [DataMember(Name = "code")]
            private int Code;

            public InvocationError(Exception ex)
            {
                Message = ex.Message;

                if (ex is SqliteException)
                    Code = ((SqliteException)ex).SqliteErrorCode;
            }
        }

        private static string Serialize(Type type, object obj)
        {
            using (var stream = new MemoryStream())
            {
                var jsonSer = new DataContractJsonSerializer(type);
                jsonSer.WriteObject(stream, obj);
                stream.Position = 0;
                return new StreamReader(stream).ReadToEnd();
            }
        }
    }
}


<|start_filename|>test/js/index.js<|end_filename|>
﻿/*
 * Copyright (c) Microsoft Open Technologies, Inc. All Rights Reserved.
 * Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information.
 */
var app = {
    // based on http://www.html5rocks.com/en/tutorials/webdatabase/todo/ sample by <NAME>
    // Application Constructor
    initialize: function () {
        this.db = null;
        this.bindEvents();
    },

    onError: function (transaction, error) {
        console.log('Error: ' + error.message);
        document.getElementById("lblInfo").innerHTML = 'ERROR: ' + error.message;
    },

    onSuccess: function (transaction, resultSet) {
        var insertId = null;
        console.log('Operation completed successfully');
        try {
            insertId = resultSet.insertId;
        } catch (ex) {
            // If the statement did not insert a row, then the attribute must instead raise an INVALID_ACCESS_ERR exception.
            // http://www.w3.org/TR/webdatabase/#database-query-results
            console.log('insertId: null');
        }
        document.getElementById("lblTxInfo").innerHTML = 'RowsAffected: ' + resultSet.rowsAffected + '; InsertId: ' + insertId;
        app.getAllTodoItems(transaction);
    },

    openDatabase: function () {
        var dbSize = 5 * 1024 * 1024; // 5MB
        // open database
        app.db = openDatabase("Todo", "", "Todo manager", dbSize, function() {
            console.log('db successfully opened or created');
        });
    },

    createTable: function () {
        app.db.transaction(function (tx) {
            tx.executeSql("CREATE TABLE IF NOT EXISTS todo(ID INTEGER PRIMARY KEY ASC, todo TEXT, added_on TEXT)", [],
                app.onSuccess, app.onError);
        });
    },

    addTodo: function (text) {
        app.db.transaction(function (tx) {
            var ts = new Date().toUTCString();
            tx.executeSql("INSERT INTO todo(todo, added_on) VALUES (?,?)", [text, ts], app.onSuccess, app.onError);
        });
    },

    getAllTodoItems: function (tx) {
        tx.executeSql("SELECT * FROM todo", [], app.loadTodoItems, app.onError);
    },

    deleteTodo: function (id) {
        console.log('Delete item: ' + id);
        app.db.transaction(function (tx) {
            tx.executeSql("DELETE FROM todo WHERE ID=?", [id], app.onSuccess, app.onError);
        });
    },

    deleteAll: function () {
        console.log('Deleting all');
        app.db.transaction(function (tx) {
            tx.executeSql("DELETE FROM todo", [], app.onSuccess, app.onError);
        });
    },

    loadTodoItems: function (tx, rs) {
        var rowOutput = "",
            todoItems = document.getElementById("lblInfo"),
            row;

        for (var i = 0; i < rs.rows.length; i++) {
            row = rs.rows.item(i);
            rowOutput += "<li>" + row.todo + " [<a href='javascript:void(0);' onclick=\'app.deleteTodo(" + 
                row.ID + ");\'>Delete</a>]</li>";
        }
        if (typeof window.MSApp != 'undefined') {
            MSApp.execUnsafeLocalFunction(function () {
                todoItems.innerHTML = rowOutput;
            });
        } else {
            todoItems.innerHTML = rowOutput;
        }
    },

    dbError: function (error) {
        console.log('DB Error: ' + JSON.stringify(error));
        document.getElementById("lblDBError").innerHTML = 'DB ERROR: ' + JSON.stringify(error);
    },

    dbSuccess: function () {
        console.log('DB Operation completed successfully');
        document.getElementById("lblDBInfo").innerHTML += 'DB Operation completed successfully<br\>';
    },

    testEvents: function() {
        var db = openDatabase('testEvents.db', '1.0', 'testEvents', 2 * 1024);
        db.transaction (
            function (tx) {
                console.log('transaction 1');
                tx.executeSql('DROP TABLE IF EXISTS foo');
                tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');
                tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "foobar")');
                document.querySelector('#status').innerHTML = '<p>foo created and row inserted.</p>';
            },
            app.dbError,
            function () {
                db.transaction(function (tx) {
                    console.log('transaction 2');
                    tx.executeSql('DROP TABLE foo');

                    // known to fail - so should rollback the DROP statement
                    tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "foobar")');
                }, 
                function (err) {
                    console.log('transaction 2 err: ' + JSON.stringify(err));
                    document.querySelector('#status').innerHTML += '<p>should be rolling back caused by: <code>' + err + '</code></p>';

                    db.transaction(function (tx) {
                        console.log('transaction 3');
                        tx.executeSql('SELECT * FROM foo', [], function (tx, results) {
                            document.querySelector('#status').innerHTML += '<p>found rows (should be 1): ' + results.rows.length + '</p>';
                        }, function (tx, err) {
                            document.querySelector('#status').innerHTML += '<p>failed (rollback failed): <em>' + err.message + '</em></p>';
                            document.querySelector('#status').className = 'error';
                        });
                    });
                },
                function () {
                    console.log('transaction 2 unexpected success!');
                });
            });        
    },

    testJSONBlob: function () {
        var arr = [{name: 'Ivan', title: 'Mr.', age: 25}, 'some string', 42, [1, 2, 3], ['a', 'b', 1, 10], [[1, 2, 3], ['a', 'b', 'c']]];
        var db = openDatabase('testJSONBlob.db', '1.0', 'testJSONBlob', 2 * 1024);
        db.transaction(function (tx) {
            tx.executeSql('DROP TABLE IF EXISTS blob');
            tx.executeSql('CREATE TABLE IF NOT EXISTS blob (id unique, text)');
            tx.executeSql('INSERT INTO blob (id, text) VALUES (?, ?)', [1, JSON.stringify(arr)]);
            document.querySelector('#status').innerHTML = '<p>foo created and row inserted.</p>';

            tx.executeSql('SELECT * FROM blob', [], function (tx, results) {
                document.querySelector('#status').innerHTML += '<p>found rows (should be 1): ' + results.rows.length + '</p>';
                document.querySelector('#status').innerHTML += '<p>found row value: ' + results.rows[0].text + '</p>';
                var parsedArr = JSON.parse(results.rows[0].text);
                document.querySelector('#status').innerHTML += '<p>parsed array: ' + parsedArr + '</p>';
                document.querySelector('#status').innerHTML += '<p>parsed array (stringified): ' + JSON.stringify(parsedArr) + '</p>';
            }, function (tx, err) {
                document.querySelector('#status').innerHTML += '<p>blob select failed: <em>' + err.message + '</em></p>';
                document.querySelector('#status').className = 'error';
            });
        }, app.dbError, app.dbSuccess);
    },

    testRollbacksAfterFailures: function() {
       function log(message) {
                document.getElementById("console").innerHTML += message + "<br>";
            }
            // signal to testRunner when this reaches zero.
        var testCount = 4;
         // we first retrieve and store the number of rows already in our test database.
         // our goal is to keep the number unchanged through the tests.
        var initialRowCount = 0;
        var database;
        var successCallbackCalled;

        function finishTest() {
            if (--testCount)
                return;
            log("All Tests are complete.");
            if (window.testRunner)
                testRunner.notifyDone();
        }

        function successCallback() {
            successCallbackCalled = true;
        }

        function verifySuccess(msg) {
            database.transaction(function(tx) {
                tx.executeSql("SELECT count(*) AS count FROM ErrorCallbackTest", [], function(tx, rs) {
                    log(msg + " : " + (rs.rows.item(0).count == initialRowCount && !successCallbackCalled ? "SUCCESS" : "FAILURE"));
                    finishTest();
                });
            });
        }

        function failMidWay(errorCallback) {
            successCallbackCalled = false;
            database.transaction(function(tx) {
                tx.executeSql("INSERT INTO ErrorCallbackTest(someValue) VALUES(?);", [1]);
                tx.executeSql("MUTTER SOMETHING ILLEGIBLE");
            }, errorCallback, successCallback);
        }

        function statementCallbackThrowsException(errorCallback) {
            successCallbackCalled = false;
            database.transaction(function(tx) {
                tx.executeSql("INSERT INTO ErrorCallbackTest(someValue) VALUES(?);", [1], function() {
                    throw {};
                });
            });
        }

        function runTest() {
            database = openDatabase("testRollbacksAfterFailures.db", "1.0", "testRollbacksAfterFailures", 1);

            database.transaction(function(tx) {
                tx.executeSql("CREATE TABLE IF NOT EXISTS ErrorCallbackTest (someValue)", []);
                tx.executeSql("SELECT count(*) AS count FROM ErrorCallbackTest", [], function(tx, rs) {
                    initialRowCount = rs.rows.item(0).count;
                });
            });

            failMidWay(function() {
                return true;
            });
            verifySuccess("Testing transaction failing mid-way and error callback returning true");

            failMidWay(function() {
                return false;
            });
            verifySuccess("Testing transaction failing mid-way and error callback return false");

            statementCallbackThrowsException(function() {
                return true;
            });
            verifySuccess("Testing statement callback throwing exception and error callback returning true");

            statementCallbackThrowsException(function() {
                return false;
            });
            verifySuccess("Testing statement callback throwing exception and error callback returning false");
        } 

        runTest();
    },
            
    testLongTransactions: function () {
        //sleep function for simulate long transaction
        function sleep(milliseconds) {
            var start = new Date().getTime();
            for (var i = 0; i < 1e7; i++) {
                if ((new Date().getTime() - start) > milliseconds) {
                    break;
                }
            }
        }

        var db = openDatabase('testLongTransaction.db', '1.0', 'testLongTransaction', 2 * 1024);
        document.querySelector('#status').innerHTML += '<p>start1</p>';
        db.transaction(function (tx) {
            tx.executeSql('DROP TABLE IF EXISTS foo');
            tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');
            tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "foobar")');
        }, function() {
            document.querySelector('#status').innerHTML += '<p>Table created</p>';
        });

        document.querySelector('#status').innerHTML += '<p>start2.</p>';
        db.readTransaction(function (tx) {
            sleep(2000);
            tx.executeSql('SELECT * FROM foo', [], function(tx, results) {
                document.querySelector('#status').innerHTML += '<p>found rows (should be 1): ' + results.rows.length + ' (first)</p>';
            });
        });

        document.querySelector('#status').innerHTML += '<p>start3.</p>';
        db.readTransaction(function (tx) {
            sleep(1000);
            tx.executeSql('SELECT * FROM foo', [], function (tx, results) {
                document.querySelector('#status').innerHTML += '<p>found rows (should be 1): ' + results.rows.length + ' (second)</p>';
            });
        });

        db.transaction(function (tx) {
            sleep(500);
            tx.executeSql('INSERT INTO foo (id, text) VALUES(2, "foobar")');
        });

        db.readTransaction(function (tx) {
            tx.executeSql('SELECT * FROM foo', [], function (tx, results) {
                document.querySelector('#status').innerHTML += '<p>found rows (should be 2): ' + results.rows.length + ' (first)</p>';
            });
        });
    },

    testPrelightPostflight: function() {
        var db = openDatabase('testPrelightPostflight.db', '1.0', 'testPrelightPostflight', 2 * 1024);
        db.transaction(function (tx) {
            tx.executeSql('DROP TABLE IF EXISTS foo');
            tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');
            tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "foobar")');

            document.querySelector('#status').innerHTML += '<p>operations done</p>';
        }, null, function () {
            document.querySelector('#status').innerHTML += '<p>onSuccess run</p>';
        }, function() {
            document.querySelector('#status').innerHTML += '<p>Preflight run</p>';
        }, function() {
            document.querySelector('#status').innerHTML += '<p>Postflight run</p>';
        });
    },

    testNestedTransaction: function() {
        var db = openDatabase('testNestedTransaction.db', '1.0', 'testNestedTransaction', 2 * 1024);
        db.transaction(function(tx) {
            tx.executeSql('DROP TABLE IF EXISTS foo', [], function() {
                document.querySelector('#status').innerHTML += '<p>table dropped</p>';
            }, function() {
                document.querySelector('#status').innerHTML += '<p>error drop</p>';
            });
            tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)', [], function() {
                document.querySelector('#status').innerHTML += '<p>table created</p>';
            });
            tx.executeSql('SELECT * FROM foo', [], function(tx, res) {
                document.querySelector('#status').innerHTML += '<p>' + JSON.stringify(res.rows) + '</p>';
            });
            db.transaction(function(tx1) {
                tx1.executeSql('INSERT INTO foo (id, text) VALUES (1, "foobar")');
            }, function() {
                document.querySelector('#status').innerHTML += '<p>error nested</p>';
            }, function() {
                document.querySelector('#status').innerHTML += '<p>row inserted</p>';
            }, null, null, false, tx);
            tx.executeSql('SELECT * FROM foo', [], function(tx, res) {
                document.querySelector('#status').innerHTML += '<p>' + JSON.stringify(res.rows) + '</p>';
                document.querySelector('#status').innerHTML += '<p>Expected 1 row</p>';
            });
        });
    },

    openDatabaseMultipleTimes: function () {
        var dbSize = 5 * 1024 * 1024; // 5MB
        // open database        
        app.tempDb1 = openDatabase("TodoMultiple", "", "Todo manager", dbSize, function() {
            console.log('db successfully opened or created #1');
            app.tempDb1.transaction(function (tx) {
                tx.executeSql("DROP TABLE IF EXISTS todo", [], null, app.onError);
                tx.executeSql("CREATE TABLE IF NOT EXISTS todo(ID INTEGER PRIMARY KEY ASC, todo TEXT, added_on TEXT)", [],
                    null, app.onError);
                var text = "test1";
                var ts = new Date().toUTCString();
                tx.executeSql("INSERT INTO todo(todo, added_on) VALUES (?,?)", [text, ts], null, app.onError);
            }, app.dbError, function() {
                app.tempDb2 = openDatabase("TodoMultiple", "", "Todo manager", dbSize, function () {
                    console.log('db successfully opened or created #2');
                    app.tempDb2.transaction(function (tx) {
                        var text = "<PASSWORD>";
                        var ts = new Date().toUTCString();
                        tx.executeSql("INSERT INTO todo(todo, added_on) VALUES (?,?)", [text, ts], null, app.onError);

                        console.log('there should be 2 rows now:');
                        tx.executeSql("SELECT * FROM todo", [], function (tx, res) {
                            console.log(res.rows.length);
                            document.querySelector('#status').innerHTML += '<p>found rows (should be 2): ' + res.rows.length + '</p>';
                        }, app.onError);
                    });
                });
            });
        });
    },

    // Bind Event Listeners
    //
    // Bind any events that are required on startup. Common events are:
    // 'load', 'deviceready', 'offline', and 'online'.
    bindEvents: function () {
        document.addEventListener('deviceready', this.onDeviceReady, false);
        document.getElementById('btnOpenDb').addEventListener('click', this.openDatabase);
        document.getElementById('btnCreateTable').addEventListener('click', this.createTable);
        document.getElementById('btnAddItem').addEventListener('click', function () {
            var text = document.getElementById('lblTodoText').value;
            app.addTodo(text);
        });
        document.getElementById('btnDeleteAll').addEventListener('click', this.deleteAll);
        document.getElementById('btnTestEvents').addEventListener('click', this.testEvents);
        document.getElementById('btnTestJSONBlob').addEventListener('click', this.testJSONBlob);
        document.getElementById('btnTestRollbacksAfterFailures').addEventListener('click', this.testRollbacksAfterFailures);
        document.getElementById('bthTestLongTransactions').addEventListener('click', this.testLongTransactions);
        document.getElementById('btnTestPrelightPostflight').addEventListener('click', this.testPrelightPostflight);
        document.getElementById('btnTestNestedTransaction').addEventListener('click', this.testNestedTransaction);
        document.getElementById('btnOpenDbMul').addEventListener('click', this.openDatabaseMultipleTimes);
    },
    // deviceready Event Handler
    //
    // The scope of 'this' is the event. In order to call the 'receivedEvent'
    // function, we must explicity call 'app.receivedEvent(...);'
    onDeviceReady: function () {
        app.receivedEvent('deviceready');
    },
    // Update DOM on a Received Event
    receivedEvent: function (id) {
        var parentElement = document.getElementById(id);
        var listeningElement = parentElement.querySelector('.listening');
        var receivedElement = parentElement.querySelector('.received');

        listeningElement.setAttribute('style', 'display:none;');
        receivedElement.setAttribute('style', 'display:block;');

        console.log('Received Event: ' + id);
    }
};


<|start_filename|>src/wp/WebSql.cs<|end_filename|>
﻿/*
 * Copyright (c) Microsoft Open Technologies, Inc. All Rights Reserved.
 * Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information.
 */

using System;
using System.Globalization;
using System.Linq;
using System.Runtime.Serialization;
using System.Collections.Generic;
using SQLite;
using WPCordovaClassLib.Cordova;
using WPCordovaClassLib.Cordova.Commands;
using WPCordovaClassLib.Cordova.JSON;
using System.Text.RegularExpressions;

namespace Cordova.Extension.Commands
{
    /// <summary>
    /// Apache Cordova plugin for WebSql
    /// </summary>
    public class WebSql : BaseCommand
    {
        public class QueryRow : List<QueryColumn> {}

        public class SqlResultSetRowList : List<QueryRow> {}

        [DataContract]
        public class SqlResultSet
        {
            [DataMember(Name = "insertId")]
            public long InsertId;
            [DataMember(Name = "rowsAffected")]
            public long RowsAffected;
            [DataMember(Name = "rows")]
            public SqlResultSetRowList Rows = new SqlResultSetRowList();
        };
        [DataContract]
        public class QueryColumn
        {
            [DataMember]
            public string Key;
            [DataMember]
            public object Value;

            public QueryColumn(string key, object value)
            {
                Key = key;
                Value = value;
            }
        }

        [DataContract]
        private class ConnectionInfo
        {
            [DataMember(Name = "connectionId")]
            public long Id;
        }

        [DataContract]
        private class SQliteError
        {
            [DataMember(Name = "message")]
            public string Message;
            [DataMember(Name = "code")]
            public int Code;

            public SQliteError(Exception ex)
            {
                Message = ex.Message;
                if (ex is SQLiteException)
                    Code = (int)((SQLiteException)ex).Result;
            }
        }

        /// <summary>
        /// Represents database path.
        /// </summary>
        private string _dbName = "";

        /// <summary>
        /// Represents database connection instance.
        /// </summary>
        private static Dictionary<long, SQLiteConnection> _dbConnections = new Dictionary<long, SQLiteConnection>();
        private static readonly object _locker = new Object();
        private int _retriesCount = 3;

        /// <summary>
        /// We don't connect to the database here, we just save database name for further access.
        /// </summary>
        /// <param name="options"></param>
        // ReSharper disable once UnusedMember.Global, InconsistentNaming
        public void open(string options)
        {
            lock (_locker)
            {
                try
                {
                    var args = JsonHelper.Deserialize<List<string>>(options);

                    var dbName = args[0];
                    var dbVersion = args[1];
                    _dbName = dbName;

                    for (var i = 0; i < _retriesCount; i++)
                    {
                        try
                        {
                            using (var connection = new SQLiteConnection(dbName))
                            {
                                const string versionCommand = "PRAGMA user_version";
                                var getVersionCommand = new SQLiteCommand(connection) { CommandText = versionCommand };
                                var setVersionCommand = new SQLiteCommand(connection) { CommandText = versionCommand + "=" + dbVersion };

                                var actualDbVersion = getVersionCommand.ExecuteScalar<int>();

                                if (dbVersion == "0" || dbVersion == actualDbVersion.ToString(CultureInfo.InvariantCulture)) {
                                    // If we don't care of DB version or versions are matching then just report back actual version
                                    DispatchCommandResult(new PluginResult(PluginResult.Status.OK, actualDbVersion));
                                    break;
                                }

                                if (actualDbVersion == 0) {
                                    // If actual version is 0, then database either is just created or it's version hadn't been set yet.
                                    // In this case we're reporting back new version ASAP
                                    DispatchCommandResult(new PluginResult(PluginResult.Status.OK, dbVersion));

                                    // and update it's version to version, provided by user
                                    if (dbVersion != "0")
                                    {
                                        setVersionCommand.ExecuteScalar<int>();
                                    }

                                    break;
                                }

                                // Otherwise fail with version mismatch error
                                var errorMessage =
                                    string.Format(
                                        "Unable to open database, version mismatch, {0} does not match the currentVersion of {1}",
                                        actualDbVersion, dbVersion);

                                DispatchCommandResult(new PluginResult(PluginResult.Status.ERROR, errorMessage));
                                break;
                            }
                        }
                        catch (Exception)
                        {
                            if (i != _retriesCount - 1) continue;
                            throw;
                        }
                    }
                }
                catch (Exception ex)
                {
                    DispatchCommandResult(new PluginResult(PluginResult.Status.ERROR, new SQliteError(ex)));
                }
            }
        }

        /// <summary>
        /// Remove dbName.
        /// </summary>
        /// <param name="options"></param>
        public void close(string options)
        {
            lock (_locker)
            {
                _dbName = string.Empty;

                DispatchCommandResult(new PluginResult(PluginResult.Status.OK));
            }
        }

        public void connect(string options)
        {
            lock (_locker)
            {
                var args = JsonHelper.Deserialize<List<string>>(options);
                string dbName = args[0];
                var callbackId = args[1];

                for (int i = 0; i < _retriesCount; i++)
                {
                    try
                    {
                        var result = new ConnectionInfo();

                        var newId = _dbConnections.Keys.DefaultIfEmpty(0).Max() + 1;
                        _dbConnections.Add(newId, new SQLiteConnection(dbName));
                        result.Id = newId;
                        DispatchCommandResult(new PluginResult(PluginResult.Status.OK, result), callbackId);

                        return;
                    }
                    catch (Exception ex)
                    {
                        if (i == _retriesCount - 1)
                        {
                            DispatchCommandResult(new PluginResult(PluginResult.Status.ERROR, new SQliteError(ex)), callbackId);
                            return;
                        }
                    }
                }
            }
        }

        public void disconnect(string options)
        {
            lock (_locker)
            {
                var args = JsonHelper.Deserialize<List<string>>(options);
                var callbackId = args[1];
                try
                {
                    var connectionId = int.Parse(args[0]);
                    if (_dbConnections.ContainsKey(connectionId))
                    {
                        var connection = _dbConnections[connectionId];
                        connection.Dispose();
                        _dbConnections.Remove(connectionId);
                    }
                    else
                    {
                        throw new ArgumentException("No such connection! (connectionId = " + connectionId + ")");
                    }
                }
                catch (Exception ex)
                {
                    DispatchCommandResult(new PluginResult(PluginResult.Status.ERROR, new SQliteError(ex)), callbackId);
                    return;
                }

                DispatchCommandResult(new PluginResult(PluginResult.Status.OK), callbackId);
            }
        }

        /// <summary>
        /// Executes SQL query.
        /// </summary>
        /// <param name="options"></param>
        public void executeSql(string options)
        {
            lock (_locker)
            {
                var args = JsonHelper.Deserialize<List<string>>(options);
                var callbackId = args[3];
                try
                {
                    var connectionId = int.Parse(args[0]);

                    var query = args[1];
                    var queryParams = string.IsNullOrEmpty(args[2])
                        ? new object[0]
                        : JsonHelper.Deserialize<object[]>(args[2]);

                    var resultSet = new SqlResultSet();

                    foreach (var row in _dbConnections[connectionId].Query2(query, queryParams))
                    {
                        var resultRow = new QueryRow();
                        resultRow.AddRange(row.column.Select(column => new QueryColumn(column.Key, column.Value)));
                        resultSet.Rows.Add(resultRow);
                    }

                    resultSet.InsertId = SQLite3.LastInsertRowid(_dbConnections[connectionId].Handle);
                    resultSet.RowsAffected = SQLite3.Changes(_dbConnections[connectionId].Handle);
                    DispatchCommandResult(new PluginResult(PluginResult.Status.OK, resultSet), callbackId);
                }
                catch (Exception ex)
                {
                    DispatchCommandResult(new PluginResult(PluginResult.Status.ERROR, new SQliteError(ex)), callbackId);
                }
            }
        }
    }
}
