# HG changeset patch
# User Henry S. Thompson <ht@inf.ed.ac.uk>
# Date 1634750058 0
# Node ID 3abcb61e0bd90d2708d6e9dd928cde07d181c9b8
# Parent  ddff993994bed00d113663980ec996f9cdc14cd0
basic, works

diff -r ddff993994be -r 3abcb61e0bd9 results/cdx.sql
--- a/results/cdx.sql	Wed Oct 20 15:47:55 2021 +0000
+++ b/results/cdx.sql	Wed Oct 20 17:14:18 2021 +0000
@@ -1,57 +1,15 @@
--- Tables for storing index file counts
+-- Table 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 props(
+  segment INTEGER NOT NULL, 
+  ftype CHAR(1) NOT NULL, -- 'r', 'w', 'c'
+  https INTEGER NOT NULL, -- 0:http vs. 1:https
+  ct_primary VARCHAR(11) NOT NULL, -- 'text', 'image', 'audio', 'video', 'application'
+  ct_sub VARCHAR(32),
+  sniffed_primary VARCHAR(11) NOT NULL, -- as above
+  sniffed_sub VARCHAR(32),
+  nlangs INTEGER NOT NULL,
+  lang1 CHAR(3),
+  lang2 CHAR(3),
+  lang3 CHAR(3)
 );
-
-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),
-);