AuroraQuest Software Android Development

How to Upgrade SQLite in Android without losing data!

noreply@blogger.com (Quest) — Wed, 06 Jul 2011 03:25:00 +0000

While writing a seemingly easy app that uses a database to keep track of contact info, I ran into a problem when adding columns to my SQLite table. The app would not keep the old data and just use a "null" for the new columns. I knew I would have to write an onUpgrade() Method that consisted of more than:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS contacts");
onCreate(db);
}
But where to start?

For my app this was a must. Frequent updates would need this method to work while keeping the users data, but all the examples I found of Android and SQLite just had the drop table method or something basically similar. Now I have read of a few different ways to tackle this problem. like exporting the data to XML and creating a new table then filling it in or building a temporary table to hold the data while I made the new one. I opted for the second option.

This I thought would be a no-brainier. I would just Google it and "Pow!" tons of people have working examples of this.

I was Wrong. I did find a couple of examples but they were either way too complex or just not workable in the Adapter class I had built for my database code. I couldn't find the answer on the Android developer guide site either. I found my answer instead at the SQLite documentation site.

This site gave me the crash course I needed on the types of calls and queries I could use on the SQL end, I just had to adapt it to fit my app.

I'm going to write a short mock-up of basically how I did it, Including the Adapter class I wrapped around the SQLiteOpenHelper:

Although my table had a considerable amount of columns to start with lets following the KISS rule and say The old table had only three columns named "_id, column_1, and column_2". My upgrade method will add a fourth column, "column_3" and save any user data for the new table:

//package here
//imports here

//Adapter class that wraps around the Helper class

public class FooDbAdapter
{
private static final String DATABASE_NAME = "fooHelperDb";
private static final String DATABASE_TABLE = "contacts";
private static final int SCHEMA_VERSION = 2; //was "1", make sure you change this by incrementing it by one or it will keep the old table. This makes the call to onUpgrade().

public static final String KEY_ID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_TYPE = "fooType";

private FooDbHelper fDbHelper;
private SQLiteDatabase fDb;
private final Context mCtx;

//Helper class uses SQLiteOpenHelper that does the actual database work
public static class FooDbHelper extends SQLiteOpenHelper
{

FooDbHelper(Context context) {
super(context, DATABASE_NAME, null, SCHEMA_VERSION);
}

//create table as you want it to be after onUpgrade() runs. ie: 4 columns.
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE contacts (_id INTEGER PRIMARY KEY AUTOINCREMENT, column_1 TEXT, column_2 TEXT, column_3 TEXT);" );
}

//this is the method I designed to solve the problem. I'm not sure if it's redundant in parts but it works great.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//create temp. table to hold data
db.execSQL("CREATE TEMPORARY TABLE contacts_backup (_id INTEGER PRIMARY KEY AUTOINCREMENT, column_1 TEXT, column_2 TEXT);");
//insert data from old table into temp table

db.execSQL("INSERT INTO contacts_backup SELECT _id, column_1, column_2 FROM contacts ");
//drop the old table now that your data is safe in the temporary one
db.execSQL("DROP TABLE contacts");
//recreate the table this time with all 4 columns
db.execSQL("CREATE TABLE contacts (_id INTEGER PRIMARY KEY AUTOINCREMENT, column_1 TEXT, column_2 TEXT, column_3 TEXT);");
//fill it up using null for the column_3
db.execSQL("INSERT INTO contacts SELECT _id, column_1, column_2, null FROM contacts_backup");
//then drop the temporary table
db.execSQL("DROP TABLE contacts_backup");

}

// other methods here such as:
public Cursor getAll() {
return(getWritableDatabase().rawQuery("SELECT _id, column_1, column_2, column_3 FROM contacts ORDER BY name", null));
}

public void insert(String column_1, String column_2, String column_3); {
ContentValues cv = new ContentValues();
cv.put("column_1", column_1);
cv.put("column_2", column_2);
cv.put("column_3", column_3);
getWritableDatabase().insert("contacts", "column_1", cv);
}

//getter methods

public String get_Id(Cursor c) {
return(c.getString(0));
}

public String getColumn1(Cursor c) {
return(c.getString(1));
}
public String getColumn2(Cursor c) {
return(c.getString(2));
}
public String getColumn3(Cursor c) {
return(c.getString(3));
}

}
//that's it for the wrapper then I just wrote a context and open method

public FooDbAdapter(Context ctx) {
this.mCtx = ctx;
}

public FooDbAdapter open() throws SQLException {
fDbHelper = new FooDbHelper(mCtx);
fDb = fDbHelper.getWritableDatabase();
return this;
}

public void close() {
fDbHelper.close();
}

So there it is. pretty simple eh? Just gave me a hard time. don't forget to update any references in the rest of your classes and stuff of course.

A few things I hope this post will achieve:
First of course is to help someone solve this problem.
Second is that I hope some more seasoned folks may share some other ways to attack this problem. That is how good design patterns are made and people like myself and others new to Android learn.
Third, is that instead of getting trolled people understand the purpose of this and keep this site a positive learning environment.

Go Android!

-Quest Graves

A Quick Word About Learning Android and Multiple Technologies.

noreply@blogger.com (Quest) — Tue, 05 Jul 2011 21:48:00 +0000

If your like me and new to programming you will quickly find that in order to develop complete applications you not only need to know more than one technology but at the very minimum a few that complement each other. One complex, object oriented "Do-all" language like Java or C++ isn't enough. You should, at minimum, know some easier stuff like XML for layouts and moving data, and some storage based technologies like SQL or SQLite. Even better would be to learn some HTML, PHP or even some Flash stuff too. That alone is enough to put a green programmer into overload but throw in the ability to make them work together through their respective API's, and I still think it's a miracle it all works. My next article shows how I overcame the technology boundary with a custom SQLite Adapter slightly modified so users don't lose data on an update -pretty important:) don't miss that!

Tips:

I will say try no to feel discouraged. When it all starts to make sense and come together, the different technologies are just equivalent to wording something a bit different. It gets easier.

Don't rob yourself the feeling you get when it does all work and work well either. That's what it's all about.

-Quest Graves

Gift Helper and Gift Helper Pro Updates!

noreply@blogger.com (Quest) — Sun, 03 Jul 2011 03:18:00 +0000

I know It's nothing spectacular just a small update to the help menu. I've changed some things like making it clearer on how to delete a record by long pressing a contact fro the list view. I also changed the text to reflect the new website(here) and that my other site is now independent of the .blogspot branding and is it's own domain!

So get on over to the Android Market and get your updates to Gift Helper or Gift Helper Pro!

Welcome to Android's newest developer site!

noreply@blogger.com (Quest) — Thu, 30 Jun 2011 03:44:00 +0000

This is one of a couple of sites I manage that feature the mobile OS Android. This one, however, focuses on my experiences developing apps in Java for the Android platform as well as any other types of Android related development topics that may arise. This is much more technical than my other site. Hope you enjoy your stay!

My other site ChicagolAndroid is all about the latest android news, app reviews and basic usage discussion. Feel free to add that to your bookmarks too. Always a lot of useful information there as well.

As my first post I'll start by describing AuroraQuest Software. We are a husband and wife run business devoted to making fun and usefull apps for Android as well as keeping these sites up for other people who are learning to develop or are interested in trying my applications. I am also interested in working on team projects or custom freelance work so never be afraid to ask me for help or run an idea by me.

I think it's very important to know about the people you deal with, so here is some back history on AuroraQuest Software:
Coming from working as a self employed tattoo artist, my wife and I had to relocate and things slowed down on that front, I had been involved in computers my whole life, loved to build gaming systems and such and got my hands on a T-Mobile G1. I cannot describe the amazement I felt at learning how powerful that little device was but I knew that my time to learn to program was now, It was to be in the Java language and specifically Android, and my life's true calling had revealed itself to me.

So a year and a half later here I am. My wife is a veterinary tech but is very supportive and helps pick up the slack on this side of things.

Hope to see you all soon and if you haven't yet, try my very first published app Gift Helper in the Android Market! or if you can lend a bit of support try my ad-free/donate version Gift Helper Pro!