Using SQLite in Windows and Windows Phone JavaScript applications

New to Windows Phone 8.1 is the ability to create and run applications written in JavaScript as well as on Windows 8.1. However, there are some differences in the specifics of the API available for applications on Windows Phone 8.1. One of these differences is the lack of IndexedDB on the phone. This presents difficulties for JavaScript developers of universal applications that need structured storage. In this article, we will look at how to create a WinRT component that allows using SQLite from JavaScript. We 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 need and whether their licenses are right for you. The decision about which there is a speech in this post, arose mainly to avoid problems with licensing.

• SQLite-WinRT on CodePlex : Read more about this in the post SQLite-WinRT .
• SQLite3-WinRT on GitHub : Read more about this on the github page.

Plan

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

1. Open the Visual Studio project for an existing universal Windows application in 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 SQLite wrapper.
5. Let's write common application code using the WinRT component for SQLite.

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

Installing the SQLite extension for Visual Studio

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

Also, the SQLite development team has released another VS extension - 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 along the following path: 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 references from the application projects to the project of the WinRT component. 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.

Now applications can use the WinRT component, but they still do not use the SQLite extension. Add references to SQLite in the WinRT component for Windows 8.1 and for Windows Phone 8.1. Extensions can be found in the Add Links dialog box in the Windows Extensions (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 that 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, so 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 the parameters of communication with SQL statements. To retrieve the requested data, we return an array of string objects from our executable method. All 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 allow you to execute queries asynchronously, one by one or in a transaction, and convert the results of the queries into JavaScript objects.

For your own project, additional SQLite APIs may be required; our example is just a sample for which no advanced SQLite functions are required.

Sample implementation details

Below is the WinRT code for SQLite.

C ++ / CX

The SQLite library API is written in C and mostly uses the UTF-8 char * and returns an error code when an error occurs. WinRT, on the contrary, usually uses UTF-16 Platform :: String when an error occurs, returns an exception. In the util. * Files, we implemented ValidateSQLiteResult , which turns error codes returned from SQLite functions into WinRT exceptions, or passes the return value in case an error did not occur. Also in the util. * Files there are two functions for converting between UTF-8 string types std :: string , and UTF-16, and Platform :: String string types.

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

public ref class Database sealed { public: static Windows::Foundation::IAsyncOperation<Database^> ^OpenDatabaseInFolderAsync(Windows::Storage::StorageFolder ^databaseFolder, Platform::String ^databaseFileName); virtual ~Database(); Windows::Foundation::IAsyncOperationWithProgress< Windows::Foundation::Collections::IVector<ExecuteResultRow^>^, ExecuteResultRow^> ^ExecuteAsync(Platform::String ^statementAsString); Windows::Foundation::IAsyncOperationWithProgress< Windows::Foundation::Collections::IVector<ExecuteResultRow^>^, ExecuteResultRow^> ^BindAndExecuteAsync(Platform::String ^statementAsString, Windows::Foundation::Collections::IVector<Platform::String^> ^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 OpenDatabaseInFolderAsync method is the only public method for creating a Database object. This asynchronous method returns an IAsyncOperation <Database ^> ^ created or open Database object. In the implementation, we make sure that the temporary path of SQLite is configured as described in the SQLite documentation, and then we call sqlite3_open_v2 , using 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^> ^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 ^> ^, ExecuteResultRow ^>, in which the asynchronous result is the vector of any ExecuteResultRows requested by the executable SQL statement and additionally providing execution notifications containing the same requested rows but provided only in case of simultaneous selection. We call sqlite3_exec , which uses a callback, to return the result of the query. Below is the implementation of the ExecuteAsync method and SQLiteExecCallback from the Database.cpp file:

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

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

Below is the definition of BindAndExecuteAsync from the Database.cpp file:

 Windows::Foundation::IAsyncOperationWithProgress< Windows::Foundation::Collections::IVector<ExecuteResultRow^>^, ExecuteResultRow^> ^Database::BindAndExecuteAsync( Platform::String ^statementAsString, Windows::Foundation::Collections::IVector<Platform::String^> ^parameterValuesAsPlatformVector) { sqlite3 *database = this->database; //     ,   // IVector      std::vector<Platform::String^> 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<SQLite::ExecuteResultRow^> reporter) -> Windows::Foundation::Collections::IVector<ExecuteResultRow^>^ { IVector<ExecuteResultRow^> ^results = ref new Vector<ExecuteResultRow^>(); 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<const char*>(sqlite3_column_text(statement, columnIndex)), sqlite3_column_name(statement, columnIndex)); } results->Append(currentRow); reporter.report(currentRow); } } ValidateSQLiteResult(sqlite3_finalize(statement)); return results; }); } 

In the 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 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 the other to simplify the launch of 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 a function, then closes the transaction. The only interesting aspect is that the function is asynchronous, 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 facilitate the 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);" ]); // ... 

Transition 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 a universal application out of it. To do this, you will need to change your code to use 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 preliminary schema and represent asynchronous execution with Promise objects. IndexedDB, on the other hand, 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 more resemble IndexedDB. We hope that the database code of your application is well separated from the rest of the code or is easy to convert.

Additional materials

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