How get a specific row where value is updated in android

Question

I am using SQLite database. There is continuous update coming in to table, each time 1 or 2 row values get updated.

For example: Now there are 20 rows in TABLE1 some of the rows values gets updated..

Can we know which rows got updated in TABLE1 ?

I have solution but the performance matters to me. Cursor registers data-observer with a select query that selects all rows, when there is an update I retrieve entire 20 rows with updated values, do my coding. So if there are 100 rows I need to loop all which is a tedious task for low end devices.

Kindly assist with a solution, that how to retrieve the updated row in a table in Android using a cursor.

Answer 1

If it is possible for you to alter the table then add a new column 'modified_time' of type TIMESTAMP. Update this column whenever a row is updated.

Now when you want to know which rows got updated, run this SQL query:

select _id from table2 where modified_time = (select max(modified_time) from table2)

The result of this query gives the id of all rows which got updated latest.

Edit: As you have mentioned in your comment "... server pushes the data to table with changed values.i need take the update values and update to other table", the best option for you is to create a Trigger in the DB which will update the second table based on the updates on first table. Here is the approach.

Let's say server pushes the data to 'table1' and you want to monitor the rows updated. Create another table2 (or use existing) which will automatically get updated.

Now, create a Trigger as follows:

CREATE TRIGGER trigger1 AFTER UPDATE ON table1
 BEGIN
  update table2 SET modified_time = datetime('now') WHERE row_id = old._id;
 END;

Of course your table2 will differ but the idea is that you can insert or update rows in table2 automatically using trigger. You may refer these SO posts for more details:

Does sqlite3 support a trigger to automatically update an 'updated_on' datetime field?

Using SQLite Trigger to update "LastModified" field

Sample Application: I have created a sample application to demonstrate the use of Trigger in an Android application based on the scenario you have described. You can download the Eclipse project here - trigger_example_eclipse_project.zip

In brief, the sample application has a product Table which receives updates on product price. So we create another updates Table which will automatically get updated based on the updates on product Table through a Trigger.

There is an UI to simulate updates on product Table by an EditText field and a Button. And to display the recent updates we have a ListView wich uses a Cursor to fetch data from updates Table.

Here some relevant code snippet from DbHelper.java

public class DbHelper extends SQLiteOpenHelper {

    public static final String TABLE1 = "product";
    public static final String TABLE2 = "updates";

    public static final String COL_ID = "_id";
    public static final String COL_NAME = "name";
    public static final String COL_PRICE = "price";
    public static final String COL_MODIFIED_TIME = "modified_time";

    public static final String KINDLE = "Kindle";

    private static final String SQL_CREATE_TABLE1 = 
            "CREATE TABLE product (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price INTEGER);";

    private static final String SQL_CREATE_TABLE2 = 
            "CREATE TABLE updates (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price INTEGER, modified_time TIMESTAMP);";

    private static final String SQL_CREATE_TRIGGER = 
            "CREATE TRIGGER price_update AFTER UPDATE OF price ON product"+
            "   BEGIN" +
            "       INSERT INTO updates(name,price,modified_time) VALUES(old.name,new.price,datetime('NOW'));"+
            "   END;";

    public DbHelper(Context context) {
        super(context, "sampledb", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_TABLE1);
        db.execSQL(SQL_CREATE_TABLE2);
        db.execSQL(SQL_CREATE_TRIGGER);

        ContentValues cv = new ContentValues();
        cv.put(COL_NAME, KINDLE);
        cv.put(COL_PRICE, 99);
        db.insert(TABLE1, null, cv);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
    }

}

Answer 2

You could keep a column like LAST_UPDATE_TIME, which is updated with the current time when updating.

Then you can just query for all rows where the LAST_UPDATE_TIME > required time.

If you cant alter the table, can you create a new table with the same columns ? Then you can update in both tables and when required fetch the values from the new table and delete them only from the new table when you are done with them.

Answer 3

If you implement your ContentProvider correctly an insert or update could return you the id of the row.

It is a bit complicated, but take a look at Google's IOSchedule app review the Provider and Contract class, look at Room class in the Contracts and at the "Room" cases in the Provider in the insert or update method.
You will see that an Uri will be returned, in this design approach the last element (after the last slash) is the id of the inserted/updated row.

I this approach could help you, and that you can understand Google's source code to get your work done (I found it a bit tricky to understand)