Rajubhai Rathod
Reputation: 113
SQLite Database Upgrade Condition
I have an android Quote application in which I have used SQLite Database for storing quotes. I have launched the first version of the application with this DatabaseHelper.
public class DataBaseHandler extends SQLiteOpenHelper {
private static String DB_PATH;
private static String DB_NAME = "SuccessQuotes";
private SQLiteDatabase myDataBase;
private static int DATABASE_VERSION = 1;
private final Context myContext;
public DataBaseHandler(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
DB_PATH = context.getDatabasePath(DB_NAME).toString();
Log.e("path", DB_PATH);
}
// ==============================================================================
public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();
if (dbExist) {
// do nothing - database already exist
} else {
this.getReadableDatabase();
try {
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}
// ==============================================================================
private boolean checkDataBase() {
SQLiteDatabase checkDB = null;
try {
String myPath = DB_PATH;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {
// database does't exist yet.
}
if (checkDB != null) {
checkDB.close();
}
return checkDB != null ? true : false;
}
// ==============================================================================
private void copyDataBase() throws IOException {
// Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = DB_PATH;
// Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
// transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
// Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
// ==============================================================================
public void openDataBase() throws SQLException {
// Open the database
String myPath = DB_PATH;
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}
// ==============================================================================
@Override
public synchronized void close() {
if (myDataBase != null)
myDataBase.close();
super.close();
}
// ==============================================================================
@Override
public void onCreate(SQLiteDatabase db) {
}
// ==============================================================================
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
and my Database Activity is :
public class DAO {
// All Static variables
private SQLiteDatabase database;
private DataBaseHandler dbHandler;
private static final String TABLE_QUOTES = "quotes";
private static final String TABLE_AUTHORS = "authors";
private static final String TABLE_SETTINGS = "settings";
// Pages Table Columns names
private static final String QU_ID = "_quid";
private static final String QU_TEXT = "qu_text";
private static final String QU_AUTHOR = "qu_author";
private static final String QU_FAVORITE = "qu_favorite";
private static final String QU_WEB_ID = "qu_web_id";
private static final String AU_ID = "_auid";
private static final String AU_NAME = "au_name";
private static final String AU_PICTURE = "au_picture";
private static final String AU_PICTURE_SDCARD = "au_picture_sdcard";
private static final String AU_WEB_ID = "au_web_id";
// ==============================================================================
public DAO(Context context) {
dbHandler = new DataBaseHandler(context);
try {
dbHandler.createDataBase();
} catch (IOException ioe) {
throw new Error("Unable to create database");
}
try {
dbHandler.openDataBase();
} catch (SQLException sqle) {
throw sqle;
}
// Log.e("path2", context.getDatabasePath("SuccessQuotes").toString());
// open();
}
// ==============================================================================
// Getting All Quotes
public Cursor getQuotes(String start) {
// Select All Query
String limit = "15";
if (start.equals("5000")) {
String query_count = "SELECT COUNT(" + QU_ID + ") AS count FROM "
+ TABLE_QUOTES;
Cursor c_count = database.rawQuery(query_count, null);
c_count.moveToFirst();
Integer count = c_count.getInt(c_count.getColumnIndex("count"));
limit = String.valueOf(count);
}
String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN "
+ TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_WEB_ID
+ " ORDER BY " + QU_WEB_ID + " DESC "+ " LIMIT " + start + ", " + limit;
//Log.i("query",query);
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
return cursor;
}
// ==============================================================================
// Getting All Quotes
public Cursor getFavoriteQuotes(String start) {
// Select All Query
String limit = "15";
String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN "
+ TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_WEB_ID
+ " WHERE " + QU_FAVORITE + " = " + "1"+" ORDER BY " + QU_WEB_ID + " DESC "+ " LIMIT " + start + ", " + limit;
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
return cursor;
}
//======================================================================
// Getting Fav Quote from ID
public String getFavQuotes(String strkey_id) {
// Select All Query
String fav = "";
String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN "
+ TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_WEB_ID
+ " WHERE " + QU_FAVORITE + " = " + "1 AND " + QU_ID + " = " +strkey_id;
Cursor cursor = database.rawQuery(query, null);
if(cursor.getCount() != 0)
{
cursor.moveToFirst();
fav = cursor.getString(cursor.getColumnIndex(QU_FAVORITE));
}
return fav;
}
// ==============================================================================
// Getting All Author Quotes
public Cursor getAuthorQuotes(String authorID,String start) {
// Select All Query
String limit="15";
String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN "
+ TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_WEB_ID
+ " WHERE " + QU_AUTHOR + " = " + authorID + " ORDER BY "+ QU_WEB_ID +" DESC "+ " LIMIT " + start + ", " + limit;
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
return cursor;
}
// ==============================================================================
// Getting Selected Quote
public Cursor getOneQuote(String quoteID) {
// Select All Query
String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN "
+ TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_WEB_ID
+ " WHERE " + QU_ID + " = '" + quoteID + "'";
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
return cursor;
}
// ==============================================================================
public void addOrRemoveFavorites(String id, String value) {
ContentValues values = new ContentValues();
values.put(QU_FAVORITE, value);
// Update Row
// database.update(TABLE_QUOTES, values, QU_ID + "=?", new String[] { id });
database.update(TABLE_QUOTES, values, QU_ID + "=?", new String[] { id });
}
// ==============================================================================
// Getting All Authors
public Cursor getAllAuthors() {
// Select All Query
String query = "SELECT *, COUNT(" + QU_AUTHOR + ") AS count FROM "
+ TABLE_AUTHORS + " LEFT JOIN " + TABLE_QUOTES + " ON " + AU_WEB_ID
+ " = " + QU_AUTHOR + " GROUP BY " + AU_NAME ;
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
return cursor;
}
// ==============================================================================
// Getting Quotes Count
public Integer getQuotesCount() {
String query = "SELECT COUNT(" + QU_TEXT + ") AS count FROM "
+ TABLE_QUOTES;
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
Integer count = cursor.getInt(cursor.getColumnIndex("count"));
return count;
}
// ==============================================================================
// Getting Quote ID
public Integer getQotdId() {
String query = "SELECT " + QU_ID + " FROM " + TABLE_QUOTES
+ " ORDER BY RANDOM() LIMIT 1";
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
Integer id = cursor.getInt(cursor.getColumnIndex(QU_ID));
return id;
}
// ==============================================================================
public void updateSetting(String field, String value) {
open();
ContentValues values = new ContentValues();
values.put(field, value);
// Update Row
database.update(TABLE_SETTINGS, values, null, null);
}
// ==============================================================================
public Cursor getSettings() {
open();
String query = "SELECT * FROM " + TABLE_SETTINGS;
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
return cursor;
}
// ==============================================================================
public int getLastAuthor() {
String query = "SELECT " + AU_WEB_ID + " FROM " + TABLE_AUTHORS
+ " ORDER BY " + AU_WEB_ID + " DESC LIMIT 1";
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
return cursor.getInt(cursor.getColumnIndex(AU_WEB_ID));
}
// ==============================================================================
public int getLastQuote() {
String query = "SELECT " + QU_WEB_ID + " FROM " + TABLE_QUOTES
+ " ORDER BY " + QU_WEB_ID + " DESC LIMIT 1";
Cursor cursor = database.rawQuery(query, null);
cursor.moveToFirst();
return cursor.getInt(cursor.getColumnIndex(QU_WEB_ID));
}
// ==============================================================================
public void addAuthor(String au_name, String au_picture, int au_web_id) {
open();
ContentValues v = new ContentValues();
v.put(AU_NAME, au_name);
v.put(AU_PICTURE, au_picture);
v.put(AU_PICTURE_SDCARD, 1);
v.put(AU_WEB_ID, au_web_id);
database.insert(TABLE_AUTHORS, null, v);
}
// ==============================================================================
public void addQuote(String qu_text, int qu_author, int qu_web_id) {
open();
ContentValues v = new ContentValues();
v.put(QU_TEXT, qu_text);
v.put(QU_AUTHOR, qu_author);
v.put(QU_FAVORITE, "0");
v.put(QU_WEB_ID, qu_web_id);
database.insert(TABLE_QUOTES, null, v);
}
// ==============================================================================
public void open() throws SQLException {
// database = dbHandler.getReadableDatabase();
database = dbHandler.getWritableDatabase();
}
// ==============================================================================
public void closeDatabase() {
dbHandler.close();
}
Now if I want to update application with more quotes, which changes should I make so that the new and old users don't face any issue ?
I know That Database Version Should Increased, I will it, but What should I put in onUpgrade method ?
I am not expert Android developer, so Please explain me little more if possible, I will very thankful for it. Thanks
Thanks
Upvotes: 0
Views: 41
Answers (1)
saurav
Reputation: 5926
Well the Java doc pretty much explains what needs to be done.
From Java doc
Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.
What we do is , alter the tables whenever we have changed the table schema. In addition you can also perform some data migration if required. Basically its a hook which you got and you can perform any kind of compatibility logic.
cheers,
Saurav
Upvotes: 1
Related Questions
- Improve INSERT-per-second performance of SQLite
- How can I list the tables in a SQLite database file that was opened with ATTACH?
- How do I check in SQLite whether a table exists?
- What are the options for storing hierarchical data in a relational database?
- How can I avoid concurrency problems when using SQLite on Android?
- Why does my exception handler not trap Android SQLite insert error?
- Cursor for database on sdcard not closing or deactive
- Loadmanager onLoadFinished not called
- How to Delete item in ListView and Database - Android Studio