Using SQLite in Windows and Windows Phone JavaScript Applications

Original author: Dave Risney
  • Transfer


New to Windows Phone 8.1 is the ability to create and run JavaScript applications just like on Windows 8.1. However, there are some differences in the specifics of the APIs available for applications on Windows Phone 8.1. One of these differences is the lack of IndexedDB on the phone. This presents challenges for JavaScript developers of generic applications that require structured storage. In this article, we will look at how to create a WinRT component that allows you to use SQLite from JavaScript. We have also prepared an example application for you .

Note: The following are two existing projects that wrap SQLite in WinRT. You can use them instead of writing your own wrapper. Before writing your wrappers, see if they provide the functionality you require and if their licenses are right for you. The solution discussed in this post arose mainly to avoid licensing issues.


Plan


We will stick to the following plan to learn how to use SQLite in a universal Windows JavaScript application:

  1. Let's open the Visual Studio project for the existing universal Windows application on JavaScript.
  2. Install the SQLite extensions for Visual Studio.
  3. Create a WinRT component for a universal application project.
  4. Let's write the code for wrapping SQLite.
  5. We will write the general application code using the WinRT component for SQLite.

We will follow this plan, parsing the application implementation and paying attention to changing the IndexedDB code to use SQLite. As a basis, we took the example of IndexedDB for Windows 8.1, made it a universal application, and went through the steps described below.

Install SQLite Extension for Visual Studio


The SQLite development team has released an extension for Visual Studio SQLite for Windows Runtime (Windows 8.1) , making it easy to add SQLite to Windows 8.1 applications. Follow the link above , click the Downloads link, and open the VSIX file to install the extension in Visual Studio.

The SQLite development team also released another extension for VS - SQLite for Windows Phone 8.1 . Follow the same steps to install the extension.

Creating a WinRT Component for a Universal Application Project


SQLite is written in C, and to use it in JavaScript applications, you need to wrap the SQLite API in the WinRT component.

Open your application in Visual Studio and add a new Windows Runtime Component project for universal applications, which can be found at: Visual C ++> Store Apps> Universal Apps. Windows, Windows Phone projects and shared files will be created in your solution for the new WinRT component.

To use the new WinRT component, you need to add links from application projects to the WinRT component project. In the Windows 8.1 project, add a link to the WinRT component for Windows 8.1, and in the Windows Phone 8.1 project, respectively, a link to the WinRT component for Windows Phone.

Applications can now use the WinRT component, but they still do not use the SQLite extension. Add SQLite links to the WinRT component for Windows 8.1 and for Windows Phone 8.1. Extensions can be found in the add links dialog in extensions for Windows (Phone) 8.1.

Writing SQLite wrapper code


For more information on creating C ++ / CX WinRT components, see the Creating Windows Runtime Components in C ++ document and Visual C ++ Language Reference (C ++ / CX) links . We will create a WinRT component with the minimum necessary functionality, which will allow it to be used for most JavaScript tasks.

In our case, the application needs to connect to the database, create tables, insert data and perform operations in transactions. The schema required for our example is very simple, therefore the WinRT wrapper contains only the Database object, which can open and close the database and execute SQL statements. In order to simplify the addition of data, we support communication parameters with SQL statements. To get the requested data, we return an array of row objects from our executable method. All of our methods are asynchronous, so they do not block the flow of the user interface of the application while using the database.

In JavaScript, we implement several functions that will allow us to execute requests asynchronously, one at a time or in a transaction, and convert the results of requests into JavaScript objects.

For your own project, additional SQLite APIs may be required; our example is just a demo that doesn’t require advanced SQLite features.

Implementation Details


Below is the WinRT code for SQLite.

C ++ / CX


The SQLite library API is written in C and primarily uses UTF-8 char * and returns an error code when an error occurs. WinRT, by contrast, typically uses the UTF-16 Platform :: String when an error occurs, returns an exception. In util. * Files, we implemented ValidateSQLiteResult , which turns error codes returned from SQLite functions into WinRT exceptions, or passes a return value if an error does not occur. Also, the util. * Files have two functions for converting between the UTF-8 string types std :: string , and UTF-16 and the Platform :: String string types.

In the Database. * Files, we implement the Database class for WinRT, which has several methods. The following is the code for the Database.h class:

public ref class Database sealed
{
public:
static Windows::Foundation::IAsyncOperation
^OpenDatabaseInFolderAsync(Windows::Storage::StorageFolder 
^databaseFolder,
	Platform::String ^databaseFileName);
virtual ~Database();
		Windows::Foundation::IAsyncOperationWithProgress<
Windows::Foundation::Collections::IVector^,
			ExecuteResultRow^> ^ExecuteAsync(Platform::String ^statementAsString);
		Windows::Foundation::IAsyncOperationWithProgress<
Windows::Foundation::Collections::IVector^,
			ExecuteResultRow^> 
^BindAndExecuteAsync(Platform::String ^statementAsString,
Windows::Foundation::Collections::IVector
			^parameterValues);
private:
		Database();
void CloseDatabase();
void EnsureInitializeTemporaryPath();
void OpenPath(const std::string &databasePath);
static int SQLiteExecCallback(void *context, int columnCount,
char **columnNames, char **columnValues);
		sqlite3 *database;
};

The static method OpenDatabaseInFolderAsync is the only public method for creating a Database object . This asynchronous method returns IAsyncOperation^ created or open Database object. In the implementation, we make sure that the SQLite temporary path is configured as described in the SQLite documentation, and then we call sqlite3_open_v2 using the functions from util. *. We implement an asynchronous operation using PPL create_async .

Here is the definition of the OpenDatabaseInFolderAsync method from the Database.cpp file:

Windows::Foundation::IAsyncOperation
    ^Database::OpenDatabaseInFolderAsync(Windows::Storage::StorageFolder ^databaseFolder,
    Platform::String ^databaseFileName)
{
return create_async([databaseFolder, databaseFileName]() -> Database^
    {
        Database ^database = ref new Database();
        string databasePath = PlatformStringToUtf8StdString(databaseFolder->Path);
        databasePath += "";
        databasePath += PlatformStringToUtf8StdString(databaseFileName);
        database->OpenPath(databasePath);
return database;
    });
}

Database :: ExecuteAsync is also asynchronous, this time returns IAsyncOperationWithProgress <IVector^, ExecuteResultRow ^>, in which the asynchronous result is a vector of any ExecuteResultRows requested by the executable SQL statement and additionally providing execution notifications containing the same requested rows, but provided only if selected simultaneously. We call sqlite3_exec , which uses the callback, to return the result of the query. The following is an implementation of the ExecuteAsync and SQLiteExecCallback methods from the Database.cpp file:

struct SQLiteExecCallbackContext
{
    Windows::Foundation::Collections::IVector ^rows;
    Concurrency::progress_reporter reporter;
};
Windows::Foundation::IAsyncOperationWithProgress<
    Windows::Foundation::Collections::IVector^,
    ExecuteResultRow^> ^Database::ExecuteAsync(Platform::String ^statementAsString)
{
    sqlite3 *database = this->database;
return create_async([database,
        statementAsString](Concurrency::progress_reporter
        reporter) -> Windows::Foundation::Collections::IVector^
        {
            SQLiteExecCallbackContext context = {ref new Vector(),
                reporter};
            ValidateSQLiteResult(sqlite3_exec(database,
                PlatformStringToUtf8StdString(statementAsString).c_str(),
                Database::SQLiteExecCallback, reinterpret_cast(&context),
                nullptr));
return context.rows;
        });
}
int Database::SQLiteExecCallback(void *contextAsVoid, int columnCount,
char **columnNames, char **columnValues)
{
    SQLiteExecCallbackContext *context = 
reinterpret_cast(contextAsVoid);
    ExecuteResultRow ^row = ref new ExecuteResultRow(columnCount,
        columnNames, columnValues);
    context->rows->Append(row);
    context->reporter.report(row);
return 0;
}

To ensure the binding of the SQL parameter, we implemented Database :: BindAndExecuteAsync , which returns the same value as Database :: ExecuteAsync. Database :: ExecuteAsync accepts a parameter that is a vector of rows that should be bound to SQL statements. Interesting to note: IVector parameter^ is bound to the calling thread, so we create a copy of the list of strings as std :: vector. We fix it in our lambda create_async expression and can use it in another thread. Because sqlite3_exec does not provide a parameter binding, we execute a sequence of explicit implementations of sqlite3_prepare, sqlite3_bind, sqlite3_step, and sqlite3_finalize .

The following is the definition of BindAndExecuteAsync from the Database.cpp file:

Windows::Foundation::IAsyncOperationWithProgress<
    Windows::Foundation::Collections::IVector^,
    ExecuteResultRow^> ^Database::BindAndExecuteAsync(
    Platform::String ^statementAsString,
    Windows::Foundation::Collections::IVector
    ^parameterValuesAsPlatformVector)
{
    sqlite3 *database = this->database;
// Создаем нашу собственную копию параметров, так как //предоставленный IVector не доступен на других потоках
    std::vector parameterValues;
for (unsigned int index = 0; index < parameterValuesAsPlatformVector->Size; ++index)
    {
        parameterValues.push_back(parameterValuesAsPlatformVector->GetAt(index));
    }
return create_async([database, statementAsString,
        parameterValues](Concurrency::progress_reporter
        reporter) -> Windows::Foundation::Collections::IVector^
    {
        IVector ^results = ref new Vector();
        sqlite3_stmt *statement = nullptr;
        ValidateSQLiteResult(sqlite3_prepare(database,
            PlatformStringToUtf8StdString(statementAsString).c_str(), -1,
            &statement, nullptr));
const size_t parameterValuesLength = parameterValues.size();
for (unsigned int parameterValueIndex = 0;
            parameterValueIndex < parameterValuesLength; ++parameterValueIndex)
        {
//Параметры связки индексированы 1ей
            ValidateSQLiteResult(sqlite3_bind_text(statement, parameterValueIndex + 1,
            PlatformStringToUtf8StdString(parameterValues[parameterValueIndex]).c_str(),
            -1, SQLITE_TRANSIENT));
        }
int stepResult = SQLITE_ROW;
while (stepResult != SQLITE_DONE)
        {
            stepResult = ValidateSQLiteResult(sqlite3_step(statement));
if (stepResult == SQLITE_ROW)
            {
const int columnCount = sqlite3_column_count(statement);
                ExecuteResultRow ^currentRow = ref new ExecuteResultRow();
for (int columnIndex = 0; columnIndex < columnCount; ++columnIndex)
                {
                    currentRow->Add(
reinterpret_cast(sqlite3_column_text(statement,
                        columnIndex)), sqlite3_column_name(statement, columnIndex));
                }
                results->Append(currentRow);
                reporter.report(currentRow);
            }
        }
        ValidateSQLiteResult(sqlite3_finalize(statement));
return results;
    });
}


In ExecuteResultRow. * Files , we implement ExecuteResultRow and ColumnEntry , which contain the results of database queries. This is necessary for using data in WinRT and there is no interaction with the SQLite API. The most interesting part of ExecuteResultRow is how it uses the methods of Database :: * ExecuteAsync.

Javascript


In the default.js file, we implement several methods to simplify the use of the WinRT component in a JavaScript application.

The runPromisesInSerial function accepts an array of Promise and Ensure objects that are run one after another to make it easier to run a series of asynchronous ExecuteAsync commands .

function runPromisesInSerial(promiseFunctions) {
return promiseFunctions.reduce(function (promiseChain, nextPromiseFunction) {
return promiseChain.then(nextPromiseFunction);
    },
    WinJS.Promise.wrap());
}

The executeAsTransactionAsync function opens a transaction, executes the function, then closes the transaction. The only interesting aspect is that the function is asynchronous, in order to complete the transaction, we need to wait for the asynchronous execution and get the result. Make sure that it still returns a successful result or returns an error value.

function executeAsTransactionAsync(database, workItemAsyncFunction) {
return database.executeAsync("BEGIN TRANSACTION").then(workItemAsyncFunction).then(
function (result) {
var successResult = result;
return database.executeAsync("COMMIT").then(function () {
return successResult;
            });
        },
function (error) {
var errorResult = error;
return database.executeAsync("COMMIT").then(function () {
throw errorResult;
            });
        });
}

ExecuteStatementsAsTransactionAsync and bindAndExecuteStatementsAsTransactionAsync combine the two previous functions to make it easier to work with queries and results.

function executeStatementsAsTransactionAsync(database, statements) {
var executeStatementPromiseFunctions = statements.map(function statementToPromiseFunction(statement) {
return database.executeAsync.bind(database, statement);
    });
return executeAsTransactionAsync(database, function () {
return runPromisesInSerial(executeStatementPromiseFunctions);
    });
}
function bindAndExecuteStatementsAsTransactionAsync(database, statementsAndParameters) {
var bindAndExecuteStatementPromiseFunctions = statementsAndParameters.map(
function (statementAndParameter) {
return database.bindAndExecuteAsync.bind(database,
                statementAndParameter.statement, statementAndParameter.parameters);
        });
return executeAsTransactionAsync(database, function () {
return runPromisesInSerial(bindAndExecuteStatementPromiseFunctions);
    });
}

Next, you can see how these functions are used to execute SQL queries asynchronously and sequentially:

SQLite.Database.openDatabaseInFolderAsync(
    Windows.Storage.ApplicationData.current.roamingFolder, "BookDB.sqlite").then(
function (openedOrCreatedDatabase) {
        database = openedOrCreatedDatabase;
return SdkSample.executeStatementsAsTransactionAsync(database, [
"CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY UNIQUE, title TEXT, authorid INTEGER);",
"CREATE TABLE IF NOT EXISTS authors (id INTEGER PRIMARY KEY UNIQUE, name TEXT);",
"CREATE TABLE IF NOT EXISTS checkout (id INTEGER PRIMARY KEY UNIQUE, status INTEGER);"
        ]);
// ...

Migrating from IndexedDB to SQLite


The reason for the transition may be that you have an application on Windows 8.1 that uses IndexedDB and you want to make it a universal application. To implement this, you will need to change your code to use the WinRT SQLite wrapper instead of IndexedDB.

Unfortunately, there is no simple answer what to do in this situation. For the application described in the example, we provide raw SQL contracts and use regular SQL tables that require a preliminary schema and represent asynchronous execution with Promise objects. IndexedDB, by contrast, reads and writes JavaScript objects. It focuses more on the use of SQL statements, and uses Event objects, unlike Promise.

The converted code in the sample application is very different from the original IndexedDB example. If you have a lot of IndexedDB code, you can write your WinRT wrapper so that its interface will look more like IndexedDB. We hope that your application database code is well separated from the rest of the code or that it is easy to convert.

Additional materials


SQLite library
Download SQLite for Windows Runtime
An example of a universal SQLite JavaScript application
Article about SQLite-WinRT
Microsoft Virtual Academy Training Courses (MVA)
Download a free or trial version of Visual Studio 2013

Also popular now: