2

My app reads an XML file on the internet, takes note of the time and creates/writes an SQLite database. The next time data is required, if the time is >24hrs the database is updated (xml downloaded again).

The problem is that whenever I relaunch the app in AVD it has to re-download and so I notice that all the data in the database is written again (duplicated). So instead of 10 items, I have 20 (10+10 duplicates). If I relaunch again I get another 10 items duplicated.

I thought about how I could prevent the duplication of the database (or delete the old entries), so I decided to increment the database version every time the content is downloaded. I thought this would trigger the onUpgrade() method so the data would be cleared but nothing changes.

Now I am clueless. How should I go about this?

Ozzy
  • 8,244
  • 7
  • 55
  • 95
  • 1
    What about a `DELETE FROM MyTable` before you feed the database with the downloaded data? I can't figure out what the actual problem is. Version change is made for a database scheme upgrade (if you add new tables or columns). – Knickedi Dec 15 '11 at 23:09
  • well this is exactly what i wanted to do (and have done) so thanks – Ozzy Dec 15 '11 at 23:31

2 Answers2

5

On your database create you'll want to use the UNIQUE constraint. You may not want the ON CONFLICT REPLACE that i use, but you should get the idea.

For Ex:

private static final String DATABASE_CREATE_NEWS= "create table news (_id integer primary key autoincrement, "title text not null, description text not null, date text not null, LastModified text not null, UNIQUE(title, date) ON CONFLICT REPLACE);";

Here is another solid thread that talks about it as well. SQLite table constraint - unique on multiple columns

Here is some more info on the android sqlite: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

Community
  • 1
  • 1
spaceMonkey
  • 141
  • 1
  • 2
  • 3
  • thanks for the info, this is what i'll do later when i'm using a real table (not this test-case one). – Ozzy Dec 15 '11 at 23:37
3

You should create an index on the columns that represent a unique identifier.

see this article on SQLite's website.

CREATE INDEX ix_tblexample ON TableName ( Column1, Column2, Column3 [, Column4, etc..])

Or (as per your comment) you can select the table into a cursor and check for each one.

String sql = "select * from " + tableName + "where column1 = " + param1 + "and column2 = " + param2;
Cursor cur = _db.rawQuery( sql, new String[0] );

if(cur.getCount() == 0)
{
    //upload
}
skeryl
  • 5,225
  • 4
  • 26
  • 28
  • At the moment its a very simple table with 3 columns for testing purpose and the unique identifier is the _id field. Even if I were to create unique identifiers the duplicates would fail but they shouldn't be created in the first place - thats what i'm trying to avoid here – Ozzy Dec 15 '11 at 23:13
  • oh yes thats a nice way to find out if its empty or not, but this would prevent the updates from occuring (my database is updated every 24hours). I did what @Knickedi suggested which is to explicitly delete the database before updating and it doesnt duplicate now. Thanks anyway. – Ozzy Dec 15 '11 at 23:35
  • @user1031312 ahhh okay. I didn't know that you didn't care about retaining only specific data records. buut since you don't care then delete from [TableName] is the best way to go. Gotcha. – skeryl Dec 15 '11 at 23:38