Skip to content

#105_Select_and_finalize_BCF_database_technology

Jump to bottom
Shane edited this page May 9, 2019 · 2 revisions

Spike #105 - Select and finalize BCF database technology

Shane Vincent - 02/05/2019

Goals / Deliverables

Find a suitable database technology for the BCF to use for handling data.

  • Finalize a list of requirements for the database
  • Find a suitable database and consider its advantages/disadvantages

Technologies, Tools and Resources Used

What we found out

Requirements

  • Do you want a hard fixed data structure (fixed schemas, like an accounting sheet)?

Fixed Schema is appropriate.

  • Do you want flexibility in the structure of the data persisted to the your database (schemaless, multi-level nesting)?

Flexibility in how data is stored is not required for this prototype and offers no purpose in the outcome of the project.

  • Will you be handling small or large quantities of data?

The data being handled by this prototype will be be quite small, and it is important to avoid overheads in terms of memory.

  • The volatility of your data

The data being stored must be retained, so non-volatile.

  • Will you need atomicity or not? (Atomicity is is one of the ACID transaction properties. In an atomic transaction, a series of database operations either all occur, or nothing occurs.)

Atomicity is not necessarily required for this prototype.

  • How strict are you with invalid data being sent to your database? (Ideally you are very strict and do server side data validation before persisting it to your database)

In this prototype I believe an assumption made is that data going into the database will always be good data, so we do not need to prepare for bad data in the database.

Findings

Database of choide: SQLite

SQLite is a transactional SQL database engine that is entirely self-contained, serverless and very lightweight. Pros:

  • Very popular with long-term support and stability
  • The entire database is a single file
  • Public domain
  • Faster than direct file I/O
  • ZERO configuration

Cons:

  • Some SQL features are missing
  • Can only reasonably handle low-to-medium traffic

Concurrency

I have found the following information about the possible concurrent access issue with SQLite:

  • By default, multiple processes can have the same SQLite database open at the same time, and several read accesses can be satisfied in parallel.
  • In case of writing, a single write to the database locks the database for a short time, nothing, even reading, can access the database file at all.
  • Beginning with version 3.7.0, a new “Write Ahead Logging” (WAL) option is available, in which reading and writing can proceed concurrently.
  • By default, WAL is not enabled. To turn WAL on, refer to the SQLite documentation.

Interfacing with the database

I have found a possible library to use with SQLite that works with .NET:

SQLite-net Repo: https://github.com/praeclarum/sqlite-net License: MIT

Quick library review:

  • It seems this library is still active (last issue post on the github was <24 hours ago)
  • Very brief look over the codebase shows that this is somewhat legit, though I am entirely unsure what red flags to look out for.

Possible future changes

If the project will be furthered in the future and that means increasing the scope of the database, there are tools available to easily export the schema and contents from an SQLite database and import them into an SQL database. See here for a simple tool to do so, otherwise look here for a more hands on method that can be done in the command line.

Home

Spike reports

About

Documents

Clone this wiki locally