Sqlite abstraction

[BenHenning]

Sqlite is the database storage option of choice, and given the large variety of data being stored in the Oppia app this is an ideal database system to use. However, the relational and join/query-based nature of the RDBMS should be abstracted into a simple RESTful interface that only exposes the shared data models used in higher layers of the app (see section 4.5 of the app architecture design document). This abstraction needs to force all operations to be performed off the main thread, with multithreading enabled.

If transactions are required here or in the future, this abstraction should expose them via a Closeable interface. The abstraction itself may need to enforce transactions if the regular RESTful interface is used instead.

The abstraction also needs to consider app upgrades: the database may need to be upgraded if a new structure is used in the later app version, and data may need to be appropriately moved over. This flow should be completely hidden from callers, though the cost of actually upgrading the database will likely be included in the futures waiting on the database operation to complete.

The abstraction needs to support approximate the size of certain entities despite them potentially being split across multiple tables.

This abstraction should be compatible with the data source system implemented in #6.

[BenHenning]

This is blocked on #4 and #6.

[BenHenning]

Recommend looking at introduce-data-module for the basis of infrastructure that we should use when designing this.

[BenHenning]

From meeting, we need a design document here that includes:

1. How we will be abstracting sqlite behind the data source infrastructure being introduced in introduce-data-module
2. An initial schema & CRUD API representing the new data objects being introduced in @rt4914's GAE abstractions

[BenHenning]

We decided to push this to MVP since we can just use #7 for local storage instead of a heavier weight sqlite solution.

[PrarabdhGarg]

@BenHenning if you are still considering sqllite abstraction, I would like to recommend the room library. It supports transactions, multi-threading, RESTful interface as well as migrations. It also automatically protects the app against SQL Injection attacks, and is recommended by Google itself for offline data caching.

[anandwana001]

Putting this here for reference as an option
https://android-developers.googleblog.com/2020/09/prefer-storing-data-with-jetpack.html?linkId=98693079&m=1

[BenHenning]

Proto DataStore is probably a viable replacement for PersistentCacheStore, but that may take some time for us to make the move. :) Probably better to wait.

@PrarabdhGarg we would definitely use Room if we went with Sqlite. However it seems more clear at this point that a relational system would be a substantial amount of work for little gain. This is no longer the plan for download storage, so closing this.