Btukfyl

Okay so my app database is completely stored on firebase,
and my app updates its database every day.

What procedure i used to follow everytime during updating the data:

1. Drop the current sqlite table


2. Create the table again


3. Fill in the data

This used to work perfectly, imagine a row is deleted so as i drop the whole table and rewrite the whole thing, the deleted row also get technically deleted from the local sqlite,But now there is a slight change in the table, there is a column, say "viewed" which stores if the particular row data is viewed or not, "true" or "false" (String)

Now if i drop the data while updating, it'll lose this column properties.
Then i thought of using UPDATE in sqlite and update the data of the columns of each row, but then there might be instances when a particular column is no more in the firebase, but if i do this, it will remain in my local database. (I mean that data is not edited but deleted). So how can i overcome this ?

Old Table :

db.execSQL("CREATE TABLE IF NOT EXISTS appdata_videos (id TEXT, link TEXT, title TEXT, subcode TEXT)");

New Table :

db.execSQL("CREATE TABLE IF NOT EXISTS appdata_videos (id TEXT, link TEXT, title TEXT, subcode TEXT, viewed TEXT)");

P.S. I am using firebase single time listener as i need to update data only once daily.

I used to run this function everytime i wanted to clear the database :

public void clearDB()

    {

        db.execSQL("DROP TABLE IF EXISTS subCodes");

        db.execSQL("CREATE TABLE IF NOT EXISTS subCodes (id TEXT, dbName TEXT, subName TEXT, tagline TEXT, pref INTEGER, hasInterviewQuestions TEXT, hasVideos TEXT, hasCodes TEXT)");

        db.execSQL("DROP TABLE IF EXISTS appdata_codes");

        db.execSQL("CREATE TABLE IF NOT EXISTS appdata_codes (id TEXT, question TEXT, code TEXT, tag TEXT, subcode TEXT, imglink TEXT, xlink TEXT)");

        db.execSQL("DROP TABLE IF EXISTS appdata_videos");

        db.execSQL("CREATE TABLE IF NOT EXISTS appdata_videos (id TEXT, link TEXT, title TEXT, subcode TEXT)");

        db.execSQL("DROP TABLE IF EXISTS appdata_interviewquestions");

        db.execSQL("CREATE TABLE IF NOT EXISTS appdata_interviewquestions (id TEXT, subcode TEXT, html TEXT)");

    }

Hope this solution helps you,

step1: add a new column date to the new table

db.execSQL("CREATE TABLE IF NOT EXISTS appdata_videos (id TEXT, link TEXT, title TEXT, subcode TEXT, viewed TEXT, date TEXT)");

step2:get ids from firebase and check if the ids exists in local database (loop through all the ids)
example:

if(isTheIdAvailableInLocalTable(idFromFirebase)){

//update the date column to todays date (dd-MM-yyyy)

}else{

//new entry so insert into local table with todays date (dd-MM-yyyy)

}

Step3:delete from local table where date not equal to tadays date.