SQLite - kuotsanhsu/ccc GitHub Wiki

Datatypes In SQLite

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

The Advantages Of Flexible Typing

As of version 3.37.0 (2021-11-27), SQLite provides STRICT tables that do rigid type enforcement, for developers who prefer that kind of thing.

Database encoding (UTF-8, UTF-16BE or UTF-16LE)

StackOverflow, Check the encoding of text in SQlite

You can test the encoding with this pragma:

PRAGMA encoding;

You cannot change the encoding for an existing database. To create a new database with a specific encoding, open a SQLite connection to a blank file, run this pragma:

PRAGMA encoding = "UTF-8";

And then create your database.

Case-sensitivity

https://www.sqlite.org/lang_expr.html#:~:text=Important%20Note%3A%20SQLite%20only%20understands,LIKE%20'Æ'%20is%20FALSE.

SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE. The ICU extension to SQLite includes an enhanced version of the LIKE operator that does case folding across all unicode characters.

STRICT Tables

Primary key

Primary key as a table constraints can consist of more than 1 columns. The point of a primary key is to give a non-null unique handle to each row in the table.

Datetime

Date And Time Functions

SELECT date(); -- time now

2.2. Date and Time Datatype

1. TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
2. REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
3. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Generated column

3.4. The GENERATED ALWAYS AS clause

Generated Columns

There is no TIMESTAMP column type

Constraint enforcement

4. Constraint enforcement

Single quotes vs double quotes

1. Single quotes are for strings.
2. Double quotes are for identifiers.

3.8. NOT NULL constraints

NOT NULL constraints are not verified during queries, so a query of a column might produce a NULL value even though the column is marked as NOT NULL, if the database file is corrupt.

Efficiently checking if a TEXT column exclusively contains integers or floating point numbers (as strings)

SQLite Foreign Key Support

Example: bibliography

/*
- https://dx.doi.org

https://www.doi.org/doi-handbook/HTML/case-insensitivity.html
> DOI names are case insensitive, using ASCII case folding for comparison of text. (Case insensitivity for DOI names applies only to ASCII characters. DOI names which differ in the case of non-ASCII Unicode characters may be different identifiers.) 10.123/ABC is identical to 10.123/AbC. All DOI names are converted to upper case upon registration, which is a common practice for making any kind of service case insensitive. The same is true with resolution. If a DOI name were registered as 10.123/ABC, then 10.123/abc will resolve it and an attempt to register 10.123/AbC would be rejected with the error message that this DOI name already existed.

*/

CREATE TABLE reference(
    doi_prefix TEXT,
    doi_suffix TEXT,
    time_added INTEGER AS (unixepoch()) STORED
    PRIMARY KEY (doi_prefix, doi_suffix)
) STRICT;

INSERT INTO reference values('10.1017', 'CBO9781107590120');
-- Runtime error: non-deterministic use of unixepoch() in a generated column



CREATE TABLE work(
    doi_prefix TEXT,
    doi_suffix TEXT,
    time_added TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (doi_prefix, doi_suffix)
) STRICT;

INSERT INTO reference VALUES('10.1017', 'CBO9781107590120');
-- Parse error: table reference has 3 columns but 2 values were supplied

INSERT INTO reference(doi_prefix, doi_suffix) VALUES('10.1017', 'CBO9781107590120');
-- Good


/*********
 * FINAL *
 *********/


CREATE TABLE author(
    name TEXT PRIMARY KEY,
) STRICT;

CREATE TABLE work(
    doi_prefix TEXT,
    doi_suffix TEXT,
    kind TEXT, -- book, journal, etc.
    time_added TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (doi_prefix, doi_suffix)
) STRICT;

CREATE TABLE author_work(
    author_name TEXT REFERENCES author,
    doi_prefix TEXT,
    doi_suffix TEXT,
    FOREIGN KEY(doi_prefix, doi_suffix) REFERENCES work
) STRICT;

CREATE TABLE book(
    doi_prefix TEXT,
    doi_suffix TEXT,
    isbn13 TEXT CHECK (length(isbn13) = 13 AND rtrim(isbn,'0123456789') = ''),
    title TEXT,
    subtitle TEXT,
    volume INTEGER CHECK (volumn >= 1), -- NOT NULL ?
    edition INTEGER CHECK (edition >= 1), -- NOT NULL ?
    year INTEGER CHECK (year <> 0), -- NOT NULL ?
    PRIMARY KEY(doi_prefix, doi_suffix),
    FOREIGN KEY(doi_prefix, doi_suffix) REFERENCES work
) STRICT;

CREATE TABLE series(
    name TEXT PRIMARY KEY,
    publisher TEXT,
) STRICT;

CREATE TABLE book_series(
    doi_prefix TEXT,
    doi_suffix TEXT,
    series_name TEXT REFERENCES series,
    issue INTEGER CHECK (issue >= 1), -- NOT NULL ?
    FOREIGN KEY(doi_prefix, doi_suffix) REFERENCES book
) STRICT;

INSERT INTO author(name) VALUES(
    'David Williams',
    'Leonard Christopher Gordon Rogers',
);