# HG changeset patch
# User Henry S. Thompson <ht@inf.ed.ac.uk>
# Date 1634744875 0
# Node ID ddff993994bed00d113663980ec996f9cdc14cd0
# Parent  8af4e9937799b82febcb2a1bc9901f09ea5647aa
too clever by half, keys won't work in parallel for e.g. media types

diff -r 8af4e9937799 -r ddff993994be results/cdx.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/results/cdx.sql	Wed Oct 20 15:47:55 2021 +0000
@@ -0,0 +1,57 @@
+-- Tables for storing index file counts
+-- Keyed names for various closed-class types
+CREATE TABLE scheme(
+  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
+  name  VARCHAR(16)
+);
+INSERT INTO scheme(name) VALUES ('http'),('https');
+
+CREATE TABLE mpt(
+  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
+  name  VARCHAR(11)
+);
+INSERT INTO mpt(name) VALUES
+  ('text'),('image'),('audio'),('video'),('application');
+
+CREATE TABLE mst(name)(
+  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
+  name  VARCHAR(11)
+);
+
+CREATE TABLE mediaType(
+  id INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
+  pt INTEGER NOT NULL,
+  st INTEGER NOT NULL,
+  FOREIGN KEY(pt) REFERENCES mpt(id),
+  FOREIGN KEY(st) REFERENCES mst(id)
+);
+
+CREATE TABLE lang(
+  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
+  code  CHAR(3) NOT NULL,
+  fname VARCHAR
+);
+
+CREATE TABLE langs(
+  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
+  count INTEGER NOT NULL,
+  primary INTEGER NOT NULL,
+  secondary INTEGER,
+  tertiary INTEGER,
+  FOREIGN KEY(primary) REFERENCES lang(id),
+  FOREIGN KEY(secondary) REFERENCES lang(id),
+  FOREIGN KEY(tertiary) REFERENCES lang(id)
+);
+
+CREATE TABLE props(
+  index     INTEGER NOT NULL, 
+  ftype CHAR(1), -- 'r', 'w', 'c'
+  scheme INTEGER NOT NULL,
+  ct INTEGER NOT NULL,
+  sniffed INTEGER NOT NULL,
+  langs INTEGER,
+  FOREIGN KEY(scheme) REFERENCES scheme(id),
+  FOREIGN KEY(ct) REFERENCES mediaType(id),
+  FOREIGN KEY(sniffed) REFERENCES mediaType(id),
+  FOREIGN KEY(langs) REFERENCES langs(id),
+);