Support SQLite as default datastore

[milton0825]

Scope

In some scenarios, we want to use Plano as a standalone application without a remote database server (client/server). Plano should be able to work with an embedded database to store requests on local machine.

Note: SQLite will be the default data store.

[milton0825]

References

Spring jdbc:

• https://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html

• http://sivalabs.in/2016/03/springboot-working-with-jdbctemplate/

• http://stackoverflow.com/questions/6272272/batchsqlupdate-how-to-get-auto-generated-keys

Spring transaction:

• http://docs.spring.io/spring/docs/current/spring-framework-reference/html/transaction.html

SQLite transaction

• https://www.sqlite.org/sharedcache.html

[milton0825]

https://www.draw.io/#DPlano_Sqlite_ER ER diagram.

[milton0825]

Single table vs. multiple tables

Use multiple tables (normalized) Preferred

Pros

• Normalized schema is more scalable and abstraction of data increases readability.
• No duplicates. Save disk space.

Cons

• Hard to achieve atomic read, write, update, and delete operations?
• Higher read latency.

Use a single table (denormalized)

Pros

• Lower read latency.

Cons

• Duplicates. Duplicated payload will consume too much space.

[milton0825]

How to guarantee atomicity when interacting with multiple tables?

Lock each request. You will have to hold the lock before read, update, and delete.

• How to handle lock unavailability?
  • Throw exception when lock unavailable
  • Retry
• Optimistic locking
• Pessimistic locking

Transaction

• read/write to multiple table in single transaction.