room/SQLite background scheduler/trigger that runs automatically

Question

I've got a sample table:

I have a Sample table which has a "creationDate" as an atribute. What I want is a way to increment(update) the "numOfTimesUpdated" attribute each 24h since the creationdate. so lets say "creationdate" is 01.01.2021 12:12 AM => numOfTimesUpdated=0, 02.01.2021 12:12 AM => numOfTimesUpdated=1, 03.01.2021 12:12 AM => numOfTimesUpdated=3.

How can I implement something like this in the best way?

Does SQLite has some kind of background scheduler/trigger where a UPDATE Query gets automatically called? Or Is my only chance the client side(application) using smth. like an ApplicationManager?

Answer 1

How can I implement something like this in the best way? You don't appear to even need a numberOfTimesUpdated column as the number of days since the creationDate can be calculated when required.

• If the date/time were stored in a supported format (e.g. YYYY-MM-DD HH:MM) it is very simple.

For example consider this :-

DROP TABLE IF EXISTS table1;
CREATE TABLE IF NOT EXISTS table1 (id INTEGER PRIMARY KEY, name TEXT, creationdate TEXT);
INSERT INTO table1 VALUES
    (null,'myname','2021-01-02'),(null,'anothername','2021-03-03'),(null,'andanother','2021-06-06')
;
SELECT *,strftime('%s','now')/(60 * 60 * 24) - strftime('%s',creationdate)/(60 * 60 * 24) AS numOfTimesUpdated FROM table1;
DROP TABLE IF EXISTS table1;

It :-

1. Drops the table if it exists
2. Creates the table
3. Inserts 3 rows with creation dates (21st Jan 2021, 3rd March 2021 and 6th June 2021)
4. Extracts all of the rows PLUS a calculated column with the number of days since the creation date.
5. Cleans up the test environment by deleting the table.

The results as run on 13th June 2021 are :-

Does SQLite has some kind of background scheduler/trigger where a UPDATE Query gets automatically called?

not time based.

Or Is my only chance the client side(application) using smth. like an ApplicationManager?

Yes, but again you don't appear to need this.

Working Room Example

The following is a working room example that implements the SQLite example above: -

The Table1 Entity :-

@Entity(tableName = "table1")
public class Table1 {
    @PrimaryKey
    Long id;
    String name;
    String creationDate;

    public Table1(){}
    @Ignore
    public Table1(String name, String creationDate) {
        this.name = name;
        this.creationDate = creationDate;
    }
}

• Note as in theory id's can be long long instead of int has been used. As long MUST have a value Long has been used to allow autogenerated id's (without the inefficient AUTOGENERATE).

A POJO Table1WithNumberOfUpdates to get the Table1 with the additional calculated column:-

public class Table1WithNumberOfUpdates {
    @Embedded
    Table1 table1;
    int numOfTimesUpdated;
}

A Dao AllDao to allow inserts and extracting a List of Table1WithNumberOfUpdates objects :-

@Dao
interface AllDao {

    @Insert
    long insert(Table1 table1);
    @Query("SELECT *, strftime('%s','now')/(60 * 60 * 24) - strftime('%s',creationdate)/(60 * 60 * 24) AS numOfTimesUpdated FROM table1")
    List<Table1WithNumberOfUpdates> getTable1WithNumberOfUpdatesList();
}

A standard @Database that returns an instance of the Database :-

@Database(entities = {Table1.class},exportSchema = false,version = 1)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDao getAllDao();

    private static volatile TheDatabase instance;

    public static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(
                    context,
                    TheDatabase.class,
                    "state.db"
            )
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

And finally some code in an Activity to add the three rows and extract the result outputting it to the log :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //Instantiate Database and get dao
        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();

        dao.insert(new Table1("myname","2021-01-02"));
        dao.insert(new Table1("anothername","2021-03-03"));
        dao.insert(new Table1("andanothername","2021-06-06"));
        for (Table1WithNumberOfUpdates t: dao.getTable1WithNumberOfUpdatesList()) {
            Log.d("TABLE1INFO","Name is " + t.table1.name + " Created = " + t.table1.creationDate + " Updates Since Creation = " + t.numOfTimesUpdated);
        }
    }
}

Result :-

2021-06-14 10:17:44.498 D/TABLE1INFO: Name is myname Created = 2021-01-02 Updates Since Creation = 163
2021-06-14 10:17:44.499 D/TABLE1INFO: Name is anothername Created = 2021-03-03 Updates Since Creation = 103
2021-06-14 10:17:44.499 D/TABLE1INFO: Name is andanothername Created = 2021-06-06 Updates Since Creation = 8

• Days are 1 extra due to device using local time zone as opposed to the SQLite Tool used above (Navicat for SQLite).