DB schema in sqlite format

[ypp-sc-tools.db-test.git] / pctb / db-schema.sqlite

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)
+);
+