DB schema in sqlite format

diff --git a/pctb/db-schema.sqlite b/pctb/db-schema.sqlite
new file mode 100755 (executable)
index 0000000..8a42bb5
--- /dev/null
+++ b/pctb/db-schema.sqlite
@@ -0,0 +1,59 @@
+#!/usr/bin/sqlite3 -init
+;
+
+CREATE TABLE IF NOT EXISTS buy (
+       commodid        INTEGER                 NOT NULL,
+       islandid        INTEGER                 NOT NULL,
+       stallid         INTEGER                 NOT NULL,
+       price           INTEGER,
+       qty             INTEGER,
+       PRIMARY KEY (commodid, islandid, stallid)
+);
+CREATE INDEX IF NOT EXISTS buy_by_island ON buy (commodid, islandid, price);
+CREATE INDEX IF NOT EXISTS buy_by_price  ON buy (commodid, price, islandid);
+
+CREATE TABLE IF NOT EXISTS sell (
+       commodid        INTEGER                 NOT NULL,
+       islandid        INTEGER                 NOT NULL,
+       stallid         INTEGER                 NOT NULL,
+       price           INTEGER,
+       qty             INTEGER,
+       PRIMARY KEY (commodid, islandid, stallid)
+);
+CREATE INDEX IF NOT EXISTS sell_by_island ON sell (commodid, islandid, price);
+CREATE INDEX IF NOT EXISTS sell_by_price  ON sell (commodid, price, islandid);
+
+CREATE TABLE IF NOT EXISTS commodities (
+       commodid        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+       commodname      TEXT    UNIQUE          NOT NULL,
+       unitmass        INTEGER,
+       unitvolume      INTEGER
+);
+
+CREATE TABLE IF NOT EXISTS islands (
+       islandid        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+       islandname      TEXT    UNIQUE          NOT NULL,
+       archipelago     TEXT                    NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS stalls (
+       stallid         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+       islandid        INTEGER                 NOT NULL,
+       stallname       TEXT                    NOT NULL,
+       UNIQUE (islandid, stallname)
+);
+
+CREATE TABLE IF NOT EXISTS uploads (
+       islandid        INTEGER PRIMARY KEY     NOT NULL,
+       age             INTEGER                 NOT NULL,
+       clientspec      TEXT                    NOT NULL,
+       serverspec      TEXT                    NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS distances (
+       aiid            INTEGER                 NOT NULL,
+       biid            INTEGER                 NOT NULL,
+       distance        INTEGER                 NOT NULL,
+       PRIMARY KEY (aiid, biid)
+);
+

diff --git a/pctb/schema-notes.txt b/pctb/schema-notes.txt
deleted file mode 100644 (file)
index 11a88dd..0000000
--- a/pctb/schema-notes.txt
+++ /dev/null
@@ -1,39 +0,0 @@
-
-BUY and SELL
-       commodity id    } indexed       } indexed       } unique
-       island id ??    }                               } indexed?
-       stall id                                        }
-       =>?
-       price           integer
-       qty             integer, 1001 means `>1000'
-
-COMMODITIES
-       commodity id
-       =>
-       commodity name
-       unit mass
-       unit volume
-
-ISLANDS
-       island id
-       =>
-       island name
-       archipelago name
-
-STALLS
-       stall id
-       =>
-       island id
-       stall name
-
-UPLOADS
-       island id
-    =>?
-       age             integer time_t
-       clientspec      us-ascii
-       serverspec      us-ascii
-
-DISTANCES
-       island a id
-       island b id
-       distance        integer leagues