Extend the current DB layer with an SQLite interface

[akegalj]

Context

Our current implementation has db layer interface defined in https://github.com/input-output-hk/cardano-wallet/blob/master/src/Cardano/Wallet/DB.hs with MVar implementation of this interface defined in https://github.com/input-output-hk/cardano-wallet/blob/master/src/Cardano/Wallet/DB/MVar.hs . This MVar interface gives us ability to have in-memory db system. MVar was implemented because it was simplest thing to test the db layer design/interface. MVar db interface is also very useful because we can use it to run all db actions quick in memory which is useful for implementing tests.

To really have useful db system we have to implement db layer with one that will persist data to disk.

Decision

In a similar way we have added MVar implementation of db layer, add SQLite implementation of db layer. This implementation should use sqlite https://sqlite.org/index.html to persist data to disk. Research available haskell implementations of sqlite and decide which best suits our needs.

Acceptance Criteria

1. DB layer must be implemented with sqlite interface
2. SQLiteSpec module must contain tests for sqlite interface

---

Development Plan

• Write SQL Schema using Persistent DSL
• Implement DBLayer methods in using Persistent query functions
• Add functions for creating a database connection, and running database migrations.
• Add basic "sanity test" cases while implementing DBLayer methods, while DBLayer is not complete.
• Make the MVarSpec tests run against the Sqlite DBLayer.
• Fix test failures.
• Generate more DBLayer test cases using quickcheck-state-machine.
• Add performance testing to ensure the DBLayer still works with large numbers of wallets/addresses/transactions.
• Use the Sqlite DBLayer by default when running cardano-wallet.
• Command-line parameter to configure database filename.
• (@KtorZ) enable SQLite in integration tests
• (@KtorZ) Fix tx pending & UTxO selectors

PR

• SQL Schema #227
• Set up database connection and create tables #247
• Generate DBLayer tests with quickcheck-state-machine #259
• Relocate properties to DBSpec for further sharing #270
• Sqlite: add a test of cascading delete #276
• SQLite: Add put/readPrivateKey to DBLayer #282
• Sqlite: add checkpoints and transactions to DBLayer #283
• Sqlite: implement withLock from DBLayer #288
• Add indexes to certain SQLite table columns #293
• Sqlite: enable db property tests and fix failures #300
• test file-based db read/write operations #345
• Some small fixes to DB.StateMachine module. #349
• Sqlite: Fix deletion for larger numbers of checkpoints/transactions #360
• Make DB QSM tests fail if one or more tags are not covered. #367
• Recreate passing tests and file-based and in-memory runQuery #370
• cli: Add cardano-wallet --database option #374
• cli: Add "local" network to launcher, and --state-dir for the database #377
• Use SQLite in restoration benchmark (instead of MVar implementation) #379
• Additional tests checking if files exists when using --database and --state-dir options #413

QA

• Automated unit tests are in ./lib/core/test/unit/Cardano/Wallet/DB/SqliteSpec.hs and ./lib/core/test/unit/Cardano/Wallet/DB/StateMachine.hs.
• Run the DBLayer benchmarks with stack bench cardano-wallet-core:db --ba "--output bench.html"
• cardano-wallet now has a --database option. Without that option, it will use the SQLite in-memory database.
• cardano-launcher now has a --state-dir option.
• restoration benchmarks now also use the SQLite implementation instead of the MVar which allows for restoring in rather similar time but, with a much lower memory footprint (<350Mb for the 10% scheme on mainnet).
• Integration tests also run using the SQLite engine.

[jonathanknowles]

@KtorZ @rvl @akegalj

Regarding concurrency:

1. For the Sqlite implementation, are there any invariants or constraints that can only be maintained programmatically, in Haskell code, rather than by the underlying database implementation?
2. For the functions provided by DBLayer (createWallet, removeWallet, listWallets, and so on) what level of concurrency do we need to provide to consumers of this API? Is the intention to allow certain operations to proceed in parallel? Or do we intend to place requests into some serialized order?
3. Will we opt for a simple locking model (a big lock that must be taken out before performing an operation that could affect or inspect data affected by an invariant) or something more complex?

I imagine the answers to these questions might affect the design of our tests. For a simple locking model, it would mean that we only need to test linear sequences of arbitrary operations. However, if we need to allow greater levels of concurrency, then our testing would need to reflect this.

[KtorZ]

I just run into some unexpected issues while running the integration tests scenarios using the SQLite engine. It seems that the way the checkpoint UTxO is retrieved is wrong and also include pending transaction. I am currently investigating / fixing this.

[piotr-iohk]

This looks good 👍 .
I have added additional tests in #413 checking if --database and --state-dir produce files as expected.