Cordova/PhoneGap sqlite storage adapter with extra features
Native SQLite component with API based on HTML5/Web SQL (DRAFT) API for the following platforms:
Android
iOS
macOS ("osx" platform)
Windows 10 (UWP) DESKTOP and MOBILE (see below for major limitations)
LICENSE: MIT, with Apache 2.0 option for Android and Windows platforms (see LICENSE.md for details, including third-party components used by this plugin)
error code will always be 0 (which is already the case on Windows); actual SQLite3 error code will be part of the error message member whenever possible (see xpbrew/cordova-sqlite-storage#821)
drop support for location: 0-2 values in openDatabase call (please use location: 'default' or iosDatabaseLocation setting in openDatabase as documented below)
throw an exception in case of androidDatabaseImplementation: 2 setting which is now superseded by androidDatabaseProvider: 'system' setting
IMPORTANT: Like with the other Cordova plugins your application must wait for the deviceready event. This is especially tricky in Angular/ngCordova/Ionic controller/factory/service callbacks which may be triggered before the deviceready event is fired.
Using DRAFT standard transaction API
To populate a database using the DRAFT standard transaction API:
db.transaction(function(tx){tx.executeSql('CREATE TABLE IF NOT EXISTS DemoTable (name, score)');tx.executeSql('INSERT INTO DemoTable VALUES (?,?)',['Alice',101]);tx.executeSql('INSERT INTO DemoTable VALUES (?,?)',['Betty',202]);},function(error){console.log('Transaction ERROR: '+error.message);},function(){console.log('Populated database OK');});
db.transaction(function(tx){tx.executeSql('CREATE TABLE IF NOT EXISTS DemoTable (name, score)');tx.executeSql('INSERT INTO DemoTable VALUES (?1,?2)',['Alice',101]);tx.executeSql('INSERT INTO DemoTable VALUES (?1,?2)',['Betty',202]);},function(error){console.log('Transaction ERROR: '+error.message);},function(){console.log('Populated database OK');});
To check the data using the DRAFT standard transaction API:
db.transaction(function(tx){tx.executeSql('SELECT count(*) AS mycount FROM DemoTable',[],function(tx,rs){console.log('Record count (expected to be 2): '+rs.rows.item(0).mycount);},function(tx,error){console.log('SELECT error: '+error.message);});});
Using plugin-specific API calls
To populate a database using the SQL batch API:
db.sqlBatch(['CREATE TABLE IF NOT EXISTS DemoTable (name, score)',['INSERT INTO DemoTable VALUES (?,?)',['Alice',101]],['INSERT INTO DemoTable VALUES (?,?)',['Betty',202]],],function(){console.log('Populated database OK');},function(error){console.log('SQL batch ERROR: '+error.message);});
db.sqlBatch(['CREATE TABLE IF NOT EXISTS DemoTable (name, score)',['INSERT INTO DemoTable VALUES (?1,?2)',['Alice',101]],['INSERT INTO DemoTable VALUES (?1,?2)',['Betty',202]],],function(){console.log('Populated database OK');},function(error){console.log('SQL batch ERROR: '+error.message);});
To check the data using the single SQL statement API:
db.executeSql('SELECT count(*) AS mycount FROM DemoTable',[],function(rs){console.log('Record count (expected to be 2): '+rs.rows.item(0).mycount);},function(error){console.log('SELECT SQL statement ERROR: '+error.message);});
More detailed sample
See the Sample section for a sample with a more detailed explanation (using the DRAFT standard transaction API).
Status
This plugin is not supported by PhoneGap Developer App or PhoneGap Desktop App.
A recent version of the Cordova CLI is recommended. Known issues with older versions of Cordova:
Cordova pre-7.0.0 do not automatically save the state of added plugins and platforms (--save flag is needed for Cordova pre-7.0.0)
It may be needed to use cordova prepare in case of cordova-ios pre-4.3.0 (Cordova CLI 6.4.0).
Cordova versions older than 6.0.0 are missing the cordova-ios@4.0.0 security fixes.
This plugin version uses a before_plugin_install hook to install sqlite3 library dependencies from cordova-sqlite-storage-dependencies via npm.
Use of other systems such as Cordova Plugman, PhoneGap CLI, PhoneGap Build, and Intel XDK is no longer supported by this plugin version since they do not honor the before_plugin_install hook. The supported solution is to use litehelpers / Cordova-sqlite-evcore-extbuild-free (GPL or commercial license terms); deprecated alternative with permissive license terms is available at: brodybits / cordova-sqlite-legacy-build-support (very limited testing, very limited updates).
This plugin version includes the following extra (non-standard) features:
Pre-populated database support for all platforms Android/iOS/Windows;
BLOB column values are NO LONGER automatically converted to Base64 format. MUST use SELECT BASE64(column) to return column value in Base64 format as documented below.
SQLite 3.32.3 included when building (all platforms), with the following compile-time definitions:
optional: Android system database implementation, using the androidDatabaseProvider: 'system' setting in sqlitePlugin.openDatabase() call as described in the Android database provider section below.
Amazon Fire-OS is dropped due to lack of support by Cordova. Android platform version should be used to deploy to Fire-OS 5.0(+) devices. For reference: cordova/cordova-discuss#32 (comment)
This plugin version branch has dependency on platform toolset libraries included by Visual Studio 2017 ref: xpbrew/cordova-sqlite-storage#580. Visual Studio 2015 is now supported by brodybits/cordova-sqlite-legacy (permissive license terms, no performance enhancements for Android) and brodybits/cordova-sqlite-evcore-legacy-ext-common-free (GPL or commercial license terms, with performance enhancements for Android). UNTESTED workaround for Visual Studio 2015: it may be possible to support this plugin version on Visual Studio 2015 Update 3 by installing platform toolset v141.)
Visual Studio components needed: Universal Windows Platform development, C++ Universal Windows Platform tools. A recent version of Visual Studio 2017 will offer to install any missing feature components.
It is NOT possible to use this plugin with the default "Any CPU" target. A specific target CPU type MUST be specified when building an app with this plugin.
ARM target CPU for Windows Mobile is no longer supported.
The SQLite3-WinRT component in src/windows/SQLite3-WinRT-sync is based on doo/SQLite3-WinRT commit f4b06e6 from 2012, which is missing the asynchronous C++ API improvements. There is no background processing on the Windows platform.
Truncation issue with UNICODE \u0000 character (same as \0)
INCONSISTENT error code (0) and INCORRECT error message (missing actual error info) in error callbacks ref: xpbrew/cordova-sqlite-storage#539
REGEXP is currently not supported on Windows.
Not possible to SELECT BLOB column values directly. It is recommended to use built-in HEX function to retrieve BLOB column values, which should work consistently across all platform implementations as well as (WebKit) Web SQL. Non-standard BASE64 function to SELECT BLOB column values in Base64 format is also supported by this plugin version.
Windows platform version uses UTF-16le internal database encoding while the other platform versions use UTF-8 internal encoding. (UTF-8 internal encoding is preferred ref: xpbrew/cordova-sqlite-storage#652)
Known issue with database names that contain certain US-ASCII punctuation and control characters (see below)
The macOS platform version ("osx" platform) is not tested in a release build and should be considered pre-alpha with known issues:
cordova prepare osx is needed before building and running from Xcode
This plugin version includes the following extra (non-standard) features: BASE 64 (all platforms Android/iOS/macOS/Windows), REGEXP (Android/iOS/macOS)
Using version of SQLite3 (...) with window functions and recent security updates:
Using SQLITE_DEFAULT_SYNCHRONOUS=3 (EXTRA DURABLE) build setting to be extra robust against possible database corruption ref: xpbrew/cordova-sqlite-storage#736
SQLITE_DBCONFIG_DEFENSIVE flag is used for extra SQL safety, as described above
Ionic 3 starter template is available at: iursevla / ionic3-PreDB, Unlicense (public domain)
Ionic 2 starter template is available at: iursevla / ionic2-PreDB, Unlicense (public domain)
Updated workaround solution to BUG 666 (xpbrew/cordova-sqlite-storage#666) (possible transaction issue after window.location change with possible data loss): close database if already open before opening again
This plugin version references Windows platform toolset v141 to support Visual Studio 2017 ref: xpbrew/cordova-sqlite-storage#580. (Visual Studio 2015 is now supported by brodybits/cordova-sqlite-legacy (permissive license terms, no performance enhancements for Android) and brodybits/cordova-sqlite-evcore-legacy-ext-common-free (GPL or commercial license terms, with performance enhancements for Android). UNTESTED workaround for Visual Studio 2015: it may be possible to support this plugin version on Visual Studio 2015 Update 3 by installing platform toolset v141.)
brodybits / cordova-sqlite-test-app is a CC0 (public domain) starting point to reproduce issues with this plugin and may be used as a quick way to start developing a new app.
New litehelpers / Cordova-sqlite-evcore-extbuild-free plugin version with Android JSON and SQL statement handling implemented in C, as well as support for PhoneGap Build, Intel XDK, etc. (GPL or commercial license terms). Handles large SQL batches in less than half the time as this plugin version. Also supports arbitrary database location on Android.
Drop-in replacement for HTML5/Web SQL (DRAFT) API: the only change should be to replace the static window.openDatabase() factory call with window.sqlitePlugin.openDatabase(), with parameters as documented below. Known deviations are documented in the deviations section below.
Pre-populated openDatabase option (usage described below)
Failure-safe nested transactions with batch processing optimizations (according to HTML5/Web SQL (DRAFT) API)
Transaction API (based on HTML5/Web SQL (DRAFT) API) is designed for maximum flexiblibility, does not allow any transactions to be left hanging open.
Keeps sqlite database in known, platform specific user data location on all supported platforms (Android/iOS/macOS/Windows), which can be reconfigured on iOS/macOS. Whether or not the database on the iOS platform is synchronized to iCloud depends on the selected database location.
Also validated for multi-page applications by internal test selfTest function.
This project is self-contained though with sqlite3 dependencies auto-fetched by npm. There are no dependencies on other plugins such as cordova-plugin-file.
Windows platform version uses a customized version of the performant doo / SQLite3-WinRT C++ component.
All source code is tracked to the original author in git, except for code in Android and iOS/macOS createFromResource functions for pre-populated databases which is marked as based on various sources.
Major authors are tracked in AUTHORS.md.
License of each component is tracked in LICENSE.md.
History of this project is also described in HISTORY.md.
TIP: It is possible to migrate from Cordova to a pure native solution and continue using the data stored by this plugin.
Getting started
Recommended prerequisites
Install a recent version of Cordova CLI, create a simple app with no plugins, and run it on the desired target platforms.
Add a very simple plugin such as cordova-plugin-dialogs or an echo plugin and get it working. Ideally you should be able to handle a callback with some data coming from a prompt.
These prereqisites are very well documented in a number of excellent resources including:
In addition, this guide assumes a basic knowledge of some key JavaScript concepts such as variables, function calls, and callback functions. There is an excellent explanation of JavaScript callbacks at http://cwbuecheler.com/web/tutorials/2013/javascript-callbacks/.
MAJOR TIPS: As described in the Installing section:
In case of extra-old Cordova CLI pre-7.0, it is recommended to use the --save flag when installing plugins to add them to config.xml / package.json. (This is automatic starting with Cordova CLI 7.0.)
Assuming that all plugins are added to config.xml or package.json, there is no need to commit the plugins subdirectory tree into the source repository.
In general it is not recommended to commit the platforms subdirectory tree into the source repository.
NOTICE: This plugin is only supported with the Cordova CLI. This plugin is not supported with other Cordova/PhoneGap systems such as PhoneGap CLI, PhoneGap Build, Plugman, Intel XDK, Webstorm, etc.
