Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Native Development


Thank you for visiting the BlackBerry Support Community Forums.

BlackBerry will be closing the BlackBerry Support Community Forums Device Forums on April 1st (Developers, see below)

BlackBerry remains committed to providing excellent customer support to our customers. We are delighted to direct you to the CrackBerry Forums, a well-established and thorough support channel, for continued BlackBerry support. Please visit http://forums.crackberry.com or http://crackberry.com/ask. You can also continue to visit BlackBerry Support or the BlackBerry Knowledge Base for official support options available for your BlackBerry Smartphone.

"When we launched CrackBerry.com 10 years ago, we set out to make it a fun and useful destination where BlackBerry Smartphone owners could share their excitement and learn to unleash the full potential of their BlackBerry. A decade later, the CrackBerry community is as active and passionate as ever and I know our knowledgeable members and volunteers will be excited to welcome and assist more BlackBerry owners with their questions."

- Kevin Michaluk, Founder, CrackBerry.com

Developers, for more information about the BlackBerry Developer Community please review Join the Conversation on the BlackBerry Developer Community Forums found on Inside BlackBerry.


Reply
Developer
Posts: 325
Registered: ‎10-01-2012
My Device: Dev Alpha C, Playbook 32 GB
My Carrier: BleskMobil

Best approach for SQL Schema version upgrade

Hello,

 

I have DB schema in my app. It can be expected that it will be necessary to alter tables from time to time.

 

What is best approach to maintain DB schema version? Should I create table like SCHEMA_VERSION with one row and one integer column and manually upgrade value there?

 

Or is there some better approach? 

 

Tom

--------------------------------------------------------------------------------------------
-- My Blackberry 10 app at http://blackberry.hubalek.net
-- My Android apps at http://android.hubalek.net
Highlighted
Developer
Posts: 1,524
Registered: ‎12-18-2012
My Device: Z30, Z10 LE, DevAlpha C, PlayBook

Re: Best approach for SQL Schema version upgrade

Hi! You might want to take a look on how Android's SQLiteOpenHelper manages versions. The source code is available. It uses "PRAGMA user_version" for storing schema version. I think this is the best approach if you're working with sqlite.

 

There's also PRAGMA schema_version but it shouldn't be modified as sqlite uses it internally. More info here:

http://www.sqlite.org/pragma.html

Search for 'PRAGMA user_version'.


Andrey Fidrya, @zmeyc on twitter
Developer
Posts: 325
Registered: ‎10-01-2012
My Device: Dev Alpha C, Playbook 32 GB
My Carrier: BleskMobil

Re: Best approach for SQL Schema version upgrade

I have trouble reading this value. I'm trying stuff like this

 

int checkCurrentVersion(SqlDataAccess *sda) {
    QVariantresult = sda->execute("PRAGMA user_version");
    if (result.isNull()) {
       return 0;
    }
    return result.toInt();
}

 and it does not work. Maybe I'm doing something wrong.

--------------------------------------------------------------------------------------------
-- My Blackberry 10 app at http://blackberry.hubalek.net
-- My Android apps at http://android.hubalek.net
Developer
Posts: 6,473
Registered: ‎12-08-2010
My Device: PlayBook, Z10
My Carrier: none

Re: Best approach for SQL Schema version upgrade

Neat! Thanks Zmey, I don't think that was always in SQLite so it's nice to have it pointed out.

thubalek, in case input with other approaches may still be useful, I usually just make a "metadata" table that can hold arbitrary tidbits, including a schema version number.

CREATE TABLE IF NOT EXISTS metadata (
    name TEXT PRIMARY KEY,
    value TEXT
    )

Then any time my code starts up, it checks if that matches the current version the code expects (or is missing) and walks through the upgrades starting with the oldest one that hasn't been applied yet, cumulatively applying the changes.  Presumably the #pragma mentioned would work about as well, though there are environments where it wouldn't be exposed to you (e.g. in AIR) so relying on it wouldn't be quite as cross-platform as the table-based approach.


Peter Hansen -- (BB10 and dev-related blog posts at http://peterhansen.ca.)
Author of White Noise and Battery Guru for BB10 and for PlayBook | Get more from your battery!
Developer
Posts: 325
Registered: ‎10-01-2012
My Device: Dev Alpha C, Playbook 32 GB
My Carrier: BleskMobil

Re: Best approach for SQL Schema version upgrade

I like PRAGMA more but I'm affraid I have to use metadata table because it seems that SqlDataAccess does not support execution of PRAGMA.
--------------------------------------------------------------------------------------------
-- My Blackberry 10 app at http://blackberry.hubalek.net
-- My Android apps at http://android.hubalek.net
Developer
Posts: 1,524
Registered: ‎12-18-2012
My Device: Z30, Z10 LE, DevAlpha C, PlayBook

Re: Best approach for SQL Schema version upgrade

I like the metadata table approach too. Thanks for suggesting that one Peter!

 

As for the pragma, I don't have access to my PC right now so can't test this, but something like this should work:

 

SqlDataAccess sda("contacts.db");
QVariant v = sda.execute("PRAGMA user_version");
QVariantList l = v.value<QVariantList>();
if (!l.isEmpty())
  return l.front().toInt();
return 0;

 Try setting the user_version first otherwise it will return an empty result set:

 

PRAGMA user_version = 1;

Andrey Fidrya, @zmeyc on twitter
Developer
Posts: 64
Registered: ‎09-10-2009
My Device: Z10, Bold 9700
My Carrier: O2 Germany

Re: Best approach for SQL Schema version upgrade

I also tried to read a value using pragma and ran into the same problem with SqlDataAccess.

 

I got it working using QSqlQuery though.

 

You could try the following:

QSqlQuery sqlQuery(sqlDataAccess->connection());
sqlQuery.setForwardOnly(true);
sqlQuery.exec("PRAGMA user_version");
if (!sqlQuery.isActive()) {
    // error
}
if (sqlQuery.next()) {
    return sqlQuery.value(0).toInt();
}
return 0;

 

----

My Twitter: @hendrik_dev