GaBuZoMeu/SQL/SQLite/create-db-subtag.sql
2023-09-30 17:31:16 +02:00

89 lines
2.5 KiB
SQL

-- Process with:
-- sqlite lsr < create-db-subtag.sql
DROP TABLE Descriptions_languages;
DROP TABLE Descriptions_scripts;
DROP TABLE Descriptions_regions;
DROP TABLE Descriptions_variants;
DROP TABLE Descriptions;
DROP TABLE Prefixes;
DROP TABLE Languages;
DROP TABLE Extlangs;
DROP TABLE Scripts;
DROP TABLE Regions;
DROP TABLE Variants;
-- A few things to remember about SQLite:
--
-- 1) If you declare a column as "INTEGER PRIMARY KEY", it will
-- autoincrement (http://www.sqlite.org/faq.html#q1)
--
-- 2) Types and constraints are almost always ignored
-- (http://www.sqlite.org/datatype3.html or
-- http://www.sqlite.org/faq.html#q2)
CREATE TABLE Scripts (
id INTEGER PRIMARY KEY NOT NULL,
code TEXT UNIQUE NOT NULL,
added DATE,
deprecated DATE,
comments TEXT);
CREATE TABLE Languages (
id INTEGER PRIMARY KEY NOT NULL,
code TEXT UNIQUE NOT NULL,
suppressscript TEXT REFERENCES Scripts(code),
preferredvalue TEXT, -- Yes, it should REFERENCES Languages(code), but
-- there is a chicken-and-egg problem.
added DATE,
deprecated DATE,
comments TEXT);
CREATE TABLE Extlangs (
id SERIAL PRIMARY KEY NOT NULL,
code TEXT UNIQUE NOT NULL, -- TODO: a length and content constraint?
added DATE,
prefix TEXT, -- references Languages(code)? Can a prefix be longer (a language and a script, for instance)?
comments TEXT);
CREATE TABLE Regions (
id INTEGER PRIMARY KEY NOT NULL,
code TEXT UNIQUE NOT NULL,
added DATE,
deprecated DATE,
comments TEXT);
CREATE TABLE Variants (
id INTEGER PRIMARY KEY NOT NULL,
code TEXT UNIQUE NOT NULL,
added DATE,
deprecated DATE,
comments TEXT);
CREATE TABLE Descriptions (
id INTEGER NOT NULL PRIMARY KEY,
description TEXT NOT NULL);
CREATE TABLE Descriptions_Languages (
description INTEGER NOT NULL REFERENCES Descriptions(id),
lang TEXT NOT NULL REFERENCES Languages(code));
CREATE TABLE Descriptions_Scripts (
description INTEGER NOT NULL REFERENCES Descriptions(id),
script TEXT NOT NULL REFERENCES Scripts(code));
CREATE TABLE Descriptions_Variants (
description INTEGER NOT NULL REFERENCES Descriptions(id),
variant TEXT NOT NULL REFERENCES Variants(code));
CREATE TABLE Descriptions_Regions (
description INTEGER NOT NULL REFERENCES Descriptions(id),
region TEXT NOT NULL REFERENCES Regions(code));
CREATE TABLE Prefixes (
variant TEXT NOT NULL REFERENCES Variants(code),
prefix TEXT NOT NULL REFERENCES Languages(code)
);