Updating PhoneGap Databases
This post is more than 2 years old.
Before we get started, I should point out that today's blog entry isn't really PhoneGap specific. Any browser that supports WebSQL can make use of this entry. As WebSQL is a dead standard, I pretty much only use it in PhoneGap.
A commenter asked if I could talk about how you would update an existing database. I thought it was going to be a pretty easy answer, but it turned into a royal cluster-you-know-what. This is something that IndexedDB actually makes far easier. That aside, let's talk about the issue.
When you open a database in PhoneGap, you typically use something like this:
This is pretty much exactly what the PhoneGap docs show so I won't spend much time on it. That log() function there was just a way for me to quickly handle displaying information in the iOS Simulator.
This is what I've always done and it worked fine. But now we have to ask - what happens when you want to modify the DEMO table to include a new column?
You could use a SQL call to select the column - see if it throws an error - and then run an ALTER statement. That smells bad, but would work just fine. It's going to slow down your application startup but probably not noticeably. If you don't like try/catch, you could select * and see if the column shows up.
But - if you check the docs you'll notice a changeVersion method. Unfortunately the PhoneGap docs are a bit unclear about how you would actually use this.
Apple (surprise surprise), actually has a good doc on this (Working with Database Versions) and it is helpful, but I still struggled wrapping my head around the proper flow.
First - the proper API doc for changeVersion is:
db.changeVersion(oldversion, newversion, alterfunc, errorfunc, successfunc)
Where alterfunc is a method that you can use to alter your database. It's passed a transaction object. So far so good. So I tried this:
And it worked... once. The next time I ran the application it threw an error on the open statement. Why? Because it recognized the db I had now was version 2.
Um... ok. So luckily the Apple docs mentioned that when you open a database you can pass an empty string and it always works. Ok, cool, so let's try this:
This worked - again - once. It was also bad for multiple reasons. Notice I still call populateDB as a setup function. I'm running this after my database update call. populateDB only makes a table if it doesn't exist, but for new users it could run after the version call.
But wait - it gets better. I thought changeVersion(x,y) would be a cool way to say, "If I'm version x, change my database version to y". That would rock, right?
Nope. Not at all. changeVersion throws an error if the current version doesn't match old version. So to change from 1 to 2 you have to first check to see if your database is on version 1. The PhoneGap docs don't make this very clear, but your database variable does have a version number property.
So - I think we're good now. So let's pretend I'm building the app today. Here is the first version of the code.
Unlike my original version, I am now using an implicit version change that should always run for the first time users. This is actually better than the version above as we never run populateDB. To me it was an acceptable 'cost' but you can skip it using changeVersion. So - yeah that's good.
So imagine this in the wild now. Users have downloaded and installed it. I realized I forgot a column and need to add it. Here's how I handle that - and again - I have to handle both old users and new users.
You will notice that in the first changeVersion, we go from nothing to version 2, and the table contains a foo column.
The second changeVersion handles folks on v1. It runs just the alter.
And... that's it. I don't want to think about this anymore so I'm going to wrap it here. ;) Hope this helps.
Archived Comments
Thanks a lot ! All is clear, I'll take it a chance in a few days.
Just a question: where do you find this signature?
"db.changeVersion(oldversion, newversion, alterfunc, errorfunc, successfunc)"
In the official phonegap doc, there is no track of it.
[http://docs.phonegap.com/en...]
Follow the link to the Apple site. It had it documented better.
Slightly off-topic: I realized today that Web SQL doesn't actually use a DateTime field the way one would think it would. As far as I know. So I've been writing functions to convert a Date object into YYYY:MM:DD HH:NN:SS:L format so that I can SELECT Max(myDateTime).
I should write a blog post on that. Done. ;)
http://www.raymondcamden.co...
I've used persistence.js (http://persistencejs.org/) with a phonegap app, which is a nice bit of abstraction/ORM for WebSQL.
Persistence.js seems to support Rails style "migrations" now, which is a similar sort of thing, but I ended up rolling my own when I wrote the app over a year ago - didn't know the changeVersion thing existed!
This is actually the most useful thing I've read all year. I've been trying to sort out database updates in WebSQL for a while, my solution is far worse.
I clearly didn't read the docs properly.
Thanks Ray!
You are most welcome.
Dear Raymond -
I tried your simple notes and while it does allow entry, it does not save. I even tried just dropping the .apk you included into my Dropbox and installed it on my Android. No worka...
I'm on Android 2.3.5.
I'd love to get this to work, as I'm in need of understanding the storage system.
Also, should I be able to get this to work on Chrome? It doesn't work there either (thought Chrome could handle this now).
TIA!
-- John Kiernan
Did you mean to post this on another blog entry?
To your second question, yes, Chrome supports WebSQL, but if you are running my demo code for PG it is probably waiting for the deviceready event to fire, which won't in Chrome. If you want to test in Chrome, just skip that.
Thank you very much, I was looking for some clear article about this everywhere...found it! :)
It seems to not work on Android 4.3
*db = window.openDatabase("happinessdiary", "",
"The Happiness Diary", 200000);
console.log("version is: " + db.version);
if (db.version == "") {
console.log("db version empty string");
updateFinished = true; // Not an update
// EMPTY STRING -> VERY FIRST VISIT!
db.changeVersion("", "1.0", DBObject.createDB, DBTransactionsCbks.error, function() { console.log("empty to 0.9.2 done!"); });
} else if (db.version == "0.9.1") {
console.log("OK, changing version");
//db.changeVersion(db.version, "1.0", update.init, update.error, function() { console.log("empty to 0.9.2 done!"); });
db.changeVersion(db.version, "1.0", function() { console.log("here?"); })
} else {
// Nothing to do...
updateFinished = true;
}*
I can see _"OK, changing version"_ but it's never executed. I've tried even the 5 arguments version of changeVersion without luck.
Any hints?
From what I can see, your logic is:
if no version, set to 1
I don't see it ever being set to 0.91.
Hi.
db.version does not return anything for me with the latest version of phonegap.
Did you test *after* the db was used?
Hello Sir
I read through all the code above. But still have some confusion.
Now#1 - I develop a new app now and the code for db will be
db = window.openDatabase("Database3", "", "Cordova Demo", 200000);
if (db.version=="")
{
db.changeversion("","1", function(trans){
},function(e) {});
}
So does that mean, i do not specify version number in the window.opendatabase and then use it in if condition to test and change version and do the changes. So if it is prepopulated i need not do anything inside this if condition.
---------------------------------------------------------------------------------
Now when i put the next version of app , i need to use same code
db = window.openDatabase("Database3", "", "Cordova Demo", 200000)
if(db.version == "1") {
log('im a 1.0 user');
db.changeVersion("1", "2",
..........................
}
Here what does changeVersion ("1", "2" means
why are we referring to "1" & "2"
Thanks and sorry for the long code
"So does that mean, i do not specify version number in the window.opendatabase and then use it in if condition to test and change version and do the changes. So if it is prepopulated i need not do anything inside this if condition."
Um, right. I think. Kinda hard to parse your question.
"Now when i put the next version of app , i need to use same code
db = window.openDatabase("Database3", "", "Cordova Demo", 200000)
if(db.version == "1") {"
Well, no, not exactly. Note my code still has db.version=="" for folks w/o a db at all.
"Here what does changeVersion ("1", "2" means
why are we referring to "1" & "2""
Because the user had version 1, and now we want her to have version 2.
can we use IndexedDB in phonegap, and for android devices?
See the CanIUse.com stats for IndexedDB. Latest Android and latest iOS supports it.
Awesome, you saved my day
for the first update when db.version is empty when running the below statement I get this error
db.changeVersion("", "1",.....
current version of the database and `oldVersion` argument do not match
I seem to remember an issue with this. You may want to check the version first.