Native interface to sqlite in a Cordova/PhoneGap plugin for Android/iOS/WP(8), with HTML5 Web SQL API
License for Android & WP(8) versions: MIT or Apache 2.0
License for iOS version: MIT only
The automatic ".db
" database file extension is now removed for the Android version, for consistency with the other versions. For an existing app, you may have to open an existing database like:
var db = window.sqlitePlugin.openDatabase({name: "my.db"});
Also the threading model is changed as described below.
dbType
option is now removed;window.openDatabase()
--> sqlitePlugin.openDatabase()
NOTE: This documentation is out-of-date and to be replaced very soon.
The idea is to emulate the HTML5 SQL API as closely as possible. The only major change is to use window.sqlitePlugin.openDatabase() (or sqlitePlugin.openDatabase()) instead of window.openDatabase(). If you see any other major change please report it, it is probably a bug.
There are two options to open a database:
var db = window.sqlitePlugin.openDatabase({name: "my.db"});
var db = window.sqlitePlugin.openDatabase("myDatabase.db", "1.0", "Demo", -1);
IMPORTANT: Please wait for the "deviceready" event, as in the following example:
// Wait for Cordova to load
document.addEventListener("deviceready", onDeviceReady, false);
// Cordova is ready
function onDeviceReady() {
var db = window.sqlitePlugin.openDatabase({name: "my.db"});
// ...
}
NOTE: The database file name should include the extension, if desired.
The threading model depens on which version is used:
This is a pretty strong test: first we create a table and add a single entry, then query the count to check if the item was inserted as expected. Note that a new transaction is created in the middle of the first callback.
// Wait for Cordova to load
document.addEventListener("deviceready", onDeviceReady, false);
// Cordova is ready
function onDeviceReady() {
var db = window.sqlitePlugin.openDatabase({name: "my.db"});
db.transaction(function(tx) {
tx.executeSql('DROP TABLE IF EXISTS test_table');
tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)');
// demonstrate PRAGMA:
db.executeSql("pragma table_info (test_table);", [], function(res) {
console.log("PRAGMA res: " + JSON.stringify(res));
});
tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function(tx, res) {
console.log("insertId: " + res.insertId + " -- probably 1");
console.log("rowsAffected: " + res.rowsAffected + " -- should be 1");
db.transaction(function(tx) {
tx.executeSql("select count(id) as cnt from test_table;", [], function(tx, res) {
console.log("res.rows.length: " + res.rows.length + " -- should be 1");
console.log("res.rows.item(0).cnt: " + res.rows.item(0).cnt + " -- should be 1");
});
});
}, function(e) {
console.log("ERROR: " + e.message);
});
});
}
In this case, the same transaction in the first executeSql() callback is being reused to run executeSql() again.
// Wait for Cordova to load
document.addEventListener("deviceready", onDeviceReady, false);
// Cordova is ready
function onDeviceReady() {
var db = window.sqlitePlugin.openDatabase("Database", "1.0", "Demo", -1);
db.transaction(function(tx) {
tx.executeSql('DROP TABLE IF EXISTS test_table');
tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)');
tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function(tx, res) {
console.log("insertId: " + res.insertId + " -- probably 1");
console.log("rowsAffected: " + res.rowsAffected + " -- should be 1");
tx.executeSql("select count(id) as cnt from test_table;", [], function(tx, res) {
console.log("res.rows.length: " + res.rows.length + " -- should be 1");
console.log("res.rows.item(0).cnt: " + res.rows.item(0).cnt + " -- should be 1");
});
}, function(e) {
console.log("ERROR: " + e.message);
});
});
}
This case will also works with Safari (WebKit), assuming you replace window.sqlitePlugin.openDatabase with window.openDatabase.
window.sqlitePlugin.deleteDatabase("my.db", successcb, errorcb);
NOTE: This is not implemented for WP(8).
NOTE: This plugin is now prepared to be installed using the cordova
tool.
npm install -g cordova # if you don't have cordova
cordova create MyProjectFolder com.my.project MyProject && cd MyProjectFolder # if you are just starting
cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin
You can find more details at this writeup.
SQLitePlugin.coffee.md
: platform-independent (Literate coffee-script, can be read by recent coffee-script compiler)www
: SQLitePlugin.js
now platform-independentsrc
: Java plugin code for Android; Objective-C plugin code for iOS; C-sharp code & DLLs for WP(8)test-www
: simple testing in index.html
using qunit 1.5.0Lawnchair-adapter
: Lawnchair adaptor, based on the version from the Lawnchair repository, with the basic Lawnchair test suite in test-www
subdirectoryThese installation instructions are based on the Android example project from Cordova/PhoneGap 2.7.0. For your first time please unzip the PhoneGap 2.7 zipball and use the lib/android/example
subdirectory.
<plugin name="SQLitePlugin" value="org.pgsqlite.SQLitePlugin"/>
to res/xml/config.xmlSample change to res/xml/config.xml for Cordova/PhoneGap 2.x:
--- config.xml.orig 2013-07-23 13:48:09.000000000 +0200
+++ res/xml/config.xml 2013-07-23 13:48:26.000000000 +0200
@@ -36,6 +36,7 @@
<preference name="useBrowserHistory" value="true" />
<preference name="exit-on-suspend" value="false" />
<plugins>
+ <plugin name="SQLitePlugin" value="org.pgsqlite.SQLitePlugin"/>
<plugin name="App" value="org.apache.cordova.App"/>
<plugin name="Geolocation" value="org.apache.cordova.GeoBroker"/>
<plugin name="Device" value="org.apache.cordova.Device"/>
Before building for the first time, you have to update the project with the desired version of the Android SDK with a command like:
android update project --path $(pwd) --target android-17
(assume Android SDK 17, use the correct desired Android SDK number here)
NOTE: using this plugin on Cordova pre-3.0 requires the following change to SQLitePlugin.java:
--- src/android/org/pgsqlite/SQLitePlugin.java 2013-09-10 21:36:20.000000000 +0200
+++ SQLitePlugin.java.old 2013-09-10 21:35:14.000000000 +0200
@@ -17,8 +17,8 @@
import java.util.HashMap;
-import org.apache.cordova.CordovaPlugin;
-import org.apache.cordova.CallbackContext;
+import org.apache.cordova.api.CordovaPlugin;
+import org.apache.cordova.api.CallbackContext;
import android.database.Cursor;
In the Project "Build Phases" tab, select the first "Link Binary with Libraries" dropdown menu and add the library libsqlite3.dylib
or libsqlite3.0.dylib
.
NOTE: In the "Build Phases" there can be multiple "Link Binary with Libraries" dropdown menus. Please select the first one otherwise it will not work.
Drag .h and .m files into your project's Plugins folder (in xcode) -- I always just have "Create references" as the option selected.
Take the precompiled javascript file from build/, or compile the coffeescript file in src/ to javascript WITH the top-level function wrapper option (default).
Use the resulting javascript file in your HTML.
Enable the SQLitePlugin in config.xml
(Cordova/PhoneGap 2.x):
--- config.xml.old 2013-05-17 13:18:39.000000000 +0200
+++ config.xml 2013-05-17 13:18:49.000000000 +0200
@@ -39,6 +39,7 @@
<content src="index.html" />
<plugins>
+ <plugin name="SQLitePlugin" value="SQLitePlugin" />
<plugin name="Device" value="CDVDevice" />
<plugin name="Logger" value="CDVLogger" />
<plugin name="Compass" value="CDVLocation" />
TODO
Make a change like this to index.html (or use the sample code) verify proper installation:
--- index.html.old 2012-08-04 14:40:07.000000000 +0200
+++ assets/www/index.html 2012-08-04 14:36:05.000000000 +0200
@@ -24,7 +24,35 @@
<title>PhoneGap</title>
<link rel="stylesheet" href="master.css" type="text/css" media="screen" title="no title">
<script type="text/javascript" charset="utf-8" src="cordova-2.0.0.js"></script>
- <script type="text/javascript" charset="utf-8" src="main.js"></script>
+ <script type="text/javascript" charset="utf-8" src="SQLitePlugin.js"></script>
+
+
+ <script type="text/javascript" charset="utf-8">
+ document.addEventListener("deviceready", onDeviceReady, false);
+ function onDeviceReady() {
+ var db = window.sqlitePlugin.openDatabase("Database", "1.0", "Demo", -1);
+
+ db.transaction(function(tx) {
+ tx.executeSql('DROP TABLE IF EXISTS test_table');
+ tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)');
+
+ tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function(tx, res) {
+ console.log("insertId: " + res.insertId + " -- probably 1"); // check #18/#38 is fixed
+ alert("insertId: " + res.insertId + " -- should be valid");
+
+ db.transaction(function(tx) {
+ tx.executeSql("SELECT data_num from test_table;", [], function(tx, res) {
+ console.log("res.rows.length: " + res.rows.length + " -- should be 1");
+ alert("res.rows.item(0).data_num: " + res.rows.item(0).data_num + " -- should be 100");
+ });
+ });
+
+ }, function(e) {
+ console.log("ERROR: " + e.message);
+ });
+ });
+ }
+ </script>
</head>
<body onload="init();" id="stage" class="theme">
sqlitePlugin
object name starts with "sql" in small letters.If you have an issue with the plugin please check the following first:
config.xml
.If you still cannot get something to work:
Then you can post the issue to the Cordova-SQLitePlugin forum or raise a new issue.
If you have any questions about the plugin please post it to the Cordova-SQLitePlugin forum.
High priority:
Low priority: issues with the API or application integration will be given lower priority until the Cordova 3.0 integration is finished for Windows Phone 8. Pull requests are very welcome for these kinds of issues.
Available for integration with SQLCipher.
Unit testing is done in test-www/
. To run the tests, simply do either:
./bin/test.sh ios
or in Android:
./bin/test.sh android
Please look at the Lawnchair-adapter
tree that contains a common adapter, which should also work with the Android version, along with a test-www directory.
Include the following js files in your html:
The name
option will determine the sqlite filename. Optionally, you can change it using the db
option.
In this example, you would be using/creating the database at: Documents/kvstore.sqlite3 (all db's in SQLitePlugin are in the Documents folder)
kvstore = new Lawnchair { name: "kvstore" }, () ->
# do stuff
Using the db
option you can create multiple stores in one sqlite file. (There will be one table per store.)
recipes = new Lawnchair {db: "cookbook", name: "recipes", ...}
ingredients = new Lawnchair {db: "cookbook", name: "ingredients", ...}
It also supports bgType argument:
users = new Lawnchair {name: "users", bgType: 1, ...}
The adapter is now part of PouchDB thanks to @nolanlawson, see PouchDB FAQ.
IMPORTANT NOTE: It is better to push your change(s) from a separate branch. Sometimes they need to be reworked before acceptance. Otherwise your master
branch could become a real mess if rework is needed.
git mv
to move files & directories;common-src
- source for Android & iOS versionsmaster-src
- source for Android, iOS, & WP(8) versionsmaster-rc
- pre-release version, including source for CSharp-SQLite library classesmaster
- version for release, will be included in PhoneGap build.此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。
1. 开源生态
2. 协作、人、软件
3. 评估模型