CODEWITHSUNDEEP
androidsqldatabasesqlite
James
James

Reputation: 474

Android check when table was last updated

Hi there I'm starting to develop an Android app which will be using a SQLite DB that will hold several tables. What I would like to know is when specific tables were last updated as some data would not need to be updated for say a week where as other tables will need to be updated every few hours. Trying to save bandwidth and create a faster more responsive app. Not sure if there is a command for this (can't seem to find anything) or if I should add a field within each table with the current time and date and use System.currentTimeMillis() to work out how long it has been.

I know this was asked below but was never answered, any help would be awesome :)

Check when an SQLite database/table was last updated (PHP)

Upvotes: 7

Views: 5953

Answers (3)

Yoshi
Yoshi

Reputation: 103

I believe the operation could be achieved by the following steps

STEP 1:You could use sqlite trigger mechanism

STEP 2: invoke an sqlite command from the STEP 1 to write the updated record to a text file (Check Writing results to a file: http://www.sqlite.org/sqlite.html)

STEP 3: Perform File System Watcher operation as in C# or in PHP

STEP 4: Send data from php to android via sms gateway

yet another crazy frankenstein algo, as am working for my application ;-)

Any feasible and simpler methodology than this are welcome....

Upvotes: -2

Jens
Jens

Reputation: 17077

A little late, but what the hell - you can also put a bunch of TRIGGERs on your tables and maintain a modification table, like shown below. The most recent modification is logged with type and date + time in the modifications table. Creating TRIGGERs like that can be easily accomplished in a simple method & called for each table created in your SQLiteOpenHelper

CREATE TABLE table1 (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    text1 TEXT,
    text2 TEXT
);

CREATE TABLE table2 (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    text1 TEXT,
    int1 INTEGER
);

CREATE TABLE modifications (
    table_name TEXT NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
    action TEXT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER IF NOT EXISTS table1_ondelete AFTER DELETE ON table1
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table1','DELETE');
END;

CREATE TRIGGER IF NOT EXISTS table2_ondelete AFTER DELETE ON table2
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table2','DELETE');
END;

CREATE TRIGGER IF NOT EXISTS table1_onupdate AFTER UPDATE ON table1
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table1','UPDATE');
END;

CREATE TRIGGER IF NOT EXISTS table2_onupdate AFTER UPDATE ON table2
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table2','UPDATE');
END;

CREATE TRIGGER IF NOT EXISTS table1_oninsert AFTER INSERT ON table1
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table1','INSERT');
END;

CREATE TRIGGER IF NOT EXISTS table2_oninsert AFTER INSERT ON table2
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table2','INSERT');
END;

Upvotes: 11

Barak
Barak

Reputation: 16393

That I know of there is no SQL function to do it.

You could add a field to each record with a timestamp, then do a query to return the latest timestamp from the table.

Upvotes: 3

Related Questions