66 lines
1.9 KiB
SQL
66 lines
1.9 KiB
SQL
BEGIN TRANSACTION;
|
|
|
|
CREATE TABLE library (
|
|
name TEXT PRIMARY KEY NOT NULL
|
|
);
|
|
|
|
CREATE TABLE work (
|
|
ark TEXT NOT NULL PRIMARY KEY
|
|
CHECK (ark LIKE 'ark:/%'),
|
|
author TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
read INTEGER NOT NULL
|
|
DEFAULT FALSE
|
|
CHECK (read IS TRUE OR read IS FALSE),
|
|
url TEXT NOT NULL
|
|
GENERATED ALWAYS AS ('https://catalogue.bm-grenoble.fr/' || ark) VIRTUAL
|
|
);
|
|
|
|
CREATE TABLE book (
|
|
ark TEXT NOT NULL
|
|
REFERENCES work (ark) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
library TEXT NOT NULL
|
|
REFERENCES library (name) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
location TEXT NOT NULL,
|
|
dewey TEXT NOT NULL,
|
|
borrowable INTEGER NOT NULL
|
|
DEFAULT TRUE
|
|
CHECK (borrowable IS TRUE OR borrowable IS FALSE),
|
|
PRIMARY KEY (ark, library)
|
|
);
|
|
|
|
CREATE TABLE weekdays (
|
|
dow INTEGER NOT NULL PRIMARY KEY
|
|
CHECK (dow BETWEEN 0 AND 6),
|
|
name TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE schedule (
|
|
library TEXT NOT NULL
|
|
REFERENCES library (name) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
dow INTEGER NOT NULL
|
|
REFERENCES weekdays (dow) ON UPDATE CASCADE ON DELETE RESTRICT,
|
|
holidays INTEGER
|
|
CHECK (holidays IS NULL OR holidays IS TRUE OR holidays IS FALSE),
|
|
start TEXT NOT NULL
|
|
CHECK (start GLOB '[01][0-9]:[0-5][0-9]:[0-5][0-9]' OR start GLOB '2[0-3]:[0-5][0-9]:[0-5][0-9]'),
|
|
end TEXT NOT NULL
|
|
CHECK (end GLOB '[01][0-9]:[0-5][0-9]:[0-5][0-9]' OR end GLOB '2[0-3]:[0-5][0-9]:[0-5][0-9]'),
|
|
UNIQUE (library, dow, start, end),
|
|
CONSTRAINT start_before_end CHECK (julianday(start) < julianday(end))
|
|
);
|
|
|
|
CREATE TABLE holidays (
|
|
start TEXT NOT NULL
|
|
CHECK (start GLOB '[0-9][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]'),
|
|
end TEXT NOT NULL
|
|
CHECK (end GLOB '[0-9][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]'),
|
|
closed INTEGER NOT NULL
|
|
DEFAULT FALSE
|
|
CHECK (closed IS FALSE OR closed IS TRUE),
|
|
PRIMARY KEY (start, end),
|
|
CONSTRAINT start_before_end CHECK (julianday(start) <= julianday(end))
|
|
);
|
|
|
|
COMMIT;
|