SQLite database on PhoneGap
Solution 1:
We ended up using the PhoneGap SQLite plugin, here's why:
We started off using a plain old Web SQL Database in our PhoneGap app, but ran into the following limitations:
- Size of database is limited to 5MB (the user can be prompted to allow more, but we didn't want such prompts in our app)
- Pre-populating the database via javascript adds to initial app load time
We initially worked around this issue by copying a pre-populated sqlite3 database to a special directory where it would be picked up by WebKit (e.g. as described here)
However, in our latest attempt to submit the app, we were told the app violated iOS Data Storage Guidelines, presumably because it was copying the database file to a non-standard location.
So we went with using the PhoneGap SQLite plugin instead: This allowed us to include a large pre-populated sqlite3 database in our app and access it from javascript via the plugin. Unfortunately, the plugin doesn't provide exactly the same javascript interface as the browser, but it's fairly close.
Solution 2:
It's important to remember that PhoneGap is web apps packaged in a browser component. Everything that applies to mobile WebKit will apply to PhoneGap as well, and the environment in PhoneGap is also very similar to opening an HTML-file in a desktop browser.
You want what's called a 'Web SQL Database'.
http://www.w3.org/TR/webdatabase/
Edit: This specification has been marked as deprecated since the writing of this answer and it's now an officially Bad Idea™ to depend on it.
In reality, it's based on SQLite in most browsers that support it, but it won't be exactly the SQLite implementation. But it's close. In Chrome or Safari, you can go have a look at it's contents with your developer tools, look at the 'Resources' tab -> Databases (you want to test out basic functionality on a desktop browser before trying in PhoneGap).
It will work exactly the same in PhoneGap as in desktop browsers.
Web SQL databases are one implementations of what's more broadly referred to as "local storage". I think that the best introductionary text on that topic can be found in Mark Pilgrim's "Dive into HTML5":
http://diveintohtml5.info/storage.html
Still just as valid for PhoneGap as for desktop browsers.
Solution 3:
I have used this to implement data base in phonegap using sqlite :
document.addEventListener("deviceready",onDeviceReady,false);
function onDeviceReady()
{
initDB();
createTables();
}
errorHandler = function (transaction, error) {
// returns true to rollback the transaction
return true;
}
// null db data handler
nullDataHandler = function (transaction, results) { }
function initDB()
{
try {
if (!window.openDatabase) {
alert('not supported');
} else {
var shortName = 'WineDatabase';
var version = '1.0';
var displayName = 'PhoneGap Test Database';
var maxSize = 655367; // in bytes
mydb = openDatabase(shortName, version, displayName, maxSize);
}
} catch(e) {
// Error handling code goes here.
if (e == INVALID_STATE_ERR) {
// Version number mismatch.
alert("Invalid database version.");
} else {
alert("Unknown error "+e+".");
}
return;
}
}
// create tables for the database ************
function createTables()
{
try {
//alert("Create table");
mydb.transaction(
function(transaction)
{
var sqlC='CREATE TABLE IF NOT EXISTS Wine_Table(id INTEGER NOT NULL PRIMARY KEY, key TEXT, color TEXT,date TEXT,heading TEXT,img_Path TEXT,name TEXT,price TEXT,whr TEXT,notes TEXT);';
transaction.executeSql(sqlC, [], nullDataHandler, errorHandler);
});
} catch(e) {
/// alert(e.message);
return;
}
}
//insert into database
function insertIntoDataBase()
{
//Select Row for checking of duplicate Key.....*********
try
{
mydb.transaction(
function(transaction) {
transaction.executeSql('SELECT *FROM Wine_Table',[], InsertValues, errorHandler);
});
}
catch(e)
{
alert(e.message);
}
//...Selection ended..*****************
}
InsertValues=function(transaction, results)
{
var name=document.getElementById('name').value;
var imagesrc=$('#changeImageBtn').attr('src');
var where=document.getElementById('where').value;
var price=document.getElementById('price').value;
var datepickervalue=document.getElementById('date1').value;
var color;
//Checking Area.....
var len = results.rows.length;
var getKey;
if(len!=0)
{
for (var i=0; i<results.rows.length; i++)
{
getKey=results.rows.item(i).key;
if(getKey==name)
{
matchkey=1;
updateKey=results.rows.item(i).id;
// alert("update key= "+updateKey);
// alert("get key "+getKey);
}
}
}
//... Checking area closed...
//for insert in to database....
var sqlI = "INSERT INTO Wine_Table(key,color,date,heading,img_Path,name,price,whr,notes) VALUES('"
+ name
+ "','"
+ color
+ "','"
+ datepickervalue
+ "','"
+ name
+ "','"
+ imagesrc
+ "','"
+ name + "','" + price + "','" + where +"','"+notes+"')";
//for updating data base....
var sqlU='UPDATE Wine_Table SET key="'+name+'",color="'+color+'",date="'+datepickervalue+'",heading="'+name+'",img_Path="'+imagesrc+'",name="'+name+'",price="'+price+'",whr="'+where+'",notes="'+notes+'" WHERE id="'+updateKey+'"';
if(isEdit==0 && matchkey==0)
{
//insering........************
try
{
mydb.transaction(
function(transaction) {
transaction.executeSql(sqlI, [], nullDataHandler, errorHandler);
});
} catch(e)
{
/// alert(e.message);
return;
}
alert("inserted successfully");
}
else
{
//updating ........***********
try
{
mydb.transaction(
function(transaction) {
transaction.executeSql(sqlU, [], nullDataHandler, errorHandler);
});
} catch(e)
{
/// alert(e.message);
return;
}
alert("Update successfully");
matchkey=0;
}
}
celebsDataHandler=function(transaction, results)
{
// Handle the results
var name;
var imageFD;
var where;
var priceFD;
var dateFD;
var color;
var notes;
var headingFD;
var ID;
var UL;
var List;
var link;
var image;
var heading;
var date;
var price;
var len = results.rows.length;
if(len!=0)
{
for (var i=0; i<results.rows.length; i++)
{
resultArr[i]=results.rows.item(i);
ID=results.rows.item(i).id;
name=results.rows.item(i).key;
imageFD=results.rows.item(i).img_Path;
where=results.rows.item(i).whr;
priceFD=results.rows.item(i).price;
datepicker=results.rows.item(i).date;
color=results.rows.item(i).color;
notes=results.rows.item(i).notes;
headingFD=results.rows.item(i).heading;
dateFD=results.rows.item(i).date;
UL=document.getElementById("wineList");
List=document.createElement('li');
//List.setAttribute('data-icon','alert');
List.setAttribute('class','listShow');
if(deleterow==1)
{
List.setAttribute('data-icon','minus');
}
link=document.createElement('a');
if(deleterow==0)
link.setAttribute('href','#NewWine');
link.setAttribute('class','anchor');
image=document.createElement('img');
image.setAttribute('src',imageFD);
image.setAttribute('class','ui-li-thumb');
heading=document.createElement('h3');
heading.innerHTML=headingFD;
date=document.createElement('p');
date.innerHTML=dateFD;
price=document.createElement('p');
price.innerHTML=priceFD;
link.appendChild(image);
link.appendChild(heading);
heading.appendChild(date);
link.appendChild(price);
List.appendChild(link);
UL.appendChild(List);
addClickEvent(List,name,ID);
}
$('ul').listview('refresh');
}
}
I hope this will help u a lot It contains create,update,insert, handle results after transaction etc...!