Data: Database Data Types

This document describes the column datatypes used in the unobtanium databases.

By default unless indicated by a Null keyword in the documentation the datatypes are not nullable and in SQL use the NOT NULL keyword. In general NULL should mean unknown or unavailable data unless documented otherwise. The meaning of a NULL value must always be documented.

Columns that are marked an Unique only allow unique vlues using the UNIQUE SQL keyword, however they do allow multiple null values.

Unobtanium uses SQLite as its database, see the SQLite documentation on datatypes.

Note that some advice on this page regarding datatype usage isn't followed. These are probbly the tables where a mistake was made in the past resulting in the advice here.

Bool

Booleans are used to store true/false values represented using the SQLite BOOL datatype and 1/0 for the values.

Column names should be prefixed with is_ or has_.

Text

Text is represented using the TEXT SQLite datatype.

Text must be encoded as UTF-8.

Advice:

• If possible normalize empty strings to a NULL.
• Do not use VARCHAR.
• Check to see if there is already a table in the base schema that you can reference.

Integer

Integers are always stored using the SQLite INTEGER datatype, the rust equivlent is i64, while always encoded as a signed integer negative values don't always make sense here.

Integers are usually used to encode some other data.

Primary Key

As a database internal identifier, these may not be handed out via external interfaces.

The SQL for SQLite must be INTEGER NOT NULL PRIMARY KEY, the columns are called {table_name}_id.

Marked as Primary Key

To enforce 1:1 relationships this may double as a reference into another table, in this case the colum name follows the reference naming scheme.

Reference / Foregin Key

To reference another tables primary key.

The column must be named [(to|from)_]{table_name}[_{purpose}]_id.

Enumeration

In the case they are uised for enumerations there must be linked documentation on a list of valid values and what they mean.

Timestamp

To store a Timestamps in unix time since 1970-01-01 00:00:00 UTC in seconds, this is consistent with how SQLite expects timestamps to be stored.

Column names must end with _unix_utc

Duration

To store a length of time in some unit.

The column name must end with a unit mentioned here: _ms

UUID

UUIDs are represented as BLOB(16) types in SQLite. Use the hex() function to get the human reable representation.

UUIDs are used as indentifiers that can be generated without the database, are unique across multiple databases and can be handed out via APIs as external identifiers.

If a UUID colum fulfills a similar role as priamry key, the column name should be {table_name}_uuid, in this case it is marked using External Key.

Advice:

• If unsure generate v7 (timestmp+random, preferred) or v4 (random) UUIDs.
• Treat UUIDs as opaque identifiers.
• Use UUIDs to hnd out identifiers via interface outside the application.
• Do not use UUIDs for database internal references.

Blob

Blobs are represented as BLOB types in SQLite. They can be used to store arbitrary binary information.

The format must be documented.