Package com.tccc.kos.commons.db

package com.tccc.kos.commons.db
Provides classes and interfaces that handle database creation, database access, and schema upgrading/downgrading.

Definitions

SQLite

SQLite is the database engine used by all kOS applications. SQLite:
  • Is "a small, fast, self-contained, high-reliability, full-featured" SQL database engine.
  • Is written in C as an "in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine".
  • Does not have a separate server process. Instead, it reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
  • Is the most-used database engine in the world, running on all mobile phones, most computers and televisions, and is used by many millions of software applications.
You can read about it at https://www.sqlite.org/

DB Browser for SQLite

"DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite." It's available on Linux, macOS, and Windows. You can read about and download it from https://sqlitebrowser.org/.

JDBI

JDBI is a set of classes designed to make working with JDBC (Java database connectivity) easier. "Jdbi provides convenient, idiomatic access to relational data in Java. Jdbi is built on top of JDBC. It improves JDBC's rough interface, providing a more natural Java database interface that is easy to bind to your domain data types." This is how you should access all of your database objects. You can read more about it at https://jdbi.org/.

DataSource

A DataSource object is a factory for connections to the physical data store (database file). All actions performed on a database are achieved by making SQL calls using one of these connections.

Database Schema

A database schema is the organization and structure of a database. A schema contains objects such as tables, columns, data types, views, stored procedures, relationships, primary keys, foreign keys, etc. The schema does NOT contain any actual data, but instead is simply the database design's blueprint.

Schema Numbering

A database's schema number represents a database's version. It starts at zero (initial database design), and then you increment it by one every time the db schema changes. So schema 0 is the initial database design; schema 1 is the first modification to the original; schema 2 is the second modification; etc.

Database File Naming

Every SQLite database in the kOS system has the following file name format: 
{database-name}_{schema-number}.sqlite
where:
  • database-name : the database's name passed into the getDataSource() method
  • schema-number : the database's schema version number
For example, if your database name is "frank" and the schema number is 8, then the database file name is frank_8.sqlite. The kOS system keeps any number of these versions on disk until you indicate which ones can be deleted.

Database Migration

Database migration is the act of either:
  1. upgrading the current database to the most recent schema version, or
  2. rolling back to a previous schema version.
The kOS system does this automatically for you: All you need to do is specify the differences between each successive schema version in the DatabaseMigrator migrateToSchema() method.

Startup Flowchart

The following operations are performed every time a kOS app is started. 
 
                 +---------+
                 |  Enter  |
                 +---------+
                      |
 +-----------------------------------------+  no (db already up-to-date)
 | Need to migrate?                        |------|
 +-----------------------------------------+      |
                      | yes (needs upgrading)     |
 +-----------------------------------------+      |
 | Copy starting schema to new schema      |      |
 +-----------------------------------------+      |
                      |                           |
 +-----------------------------------------+      |
 | Call *your* migrateToSchema() method    |      |
 +-----------------------------------------+      |
                      |                           |
 +-----------------------------------------+      |
 | Purge newer schemas, if desired         |<-----|
 +-----------------------------------------+
                      |
 +-----------------------------------------+
 | Purge older schemas, if desired         |
 +-----------------------------------------+
                      |
 +-----------------------------------------+
 | Prepare DataSource object               |
 +-----------------------------------------+
                      |
                 +---------+
                 |  Exit   |
                 +---------+
Since:
1.0
Version:
2022-10-21
See Also:
  • Class
    Description
    BaseDatabaseMigrator
    Base class for database migration.
    DatabaseFactory
    System component used to create a DataSource or Jdbi instance for a SQLite database.
    DatabaseMigrator
    Interface used to migrate database content across schema versions.
    MigrationException
    Exception thrown whenever an attempt to migrate (either upgrade or rollback) a database to a different schema fails.