From 1313c474ebe91f5fbacf08fcc2302cb435ebb12b Mon Sep 17 00:00:00 2001
From: Ian Jackson <ian@liberator.relativity.greenend.org.uk>
Date: Sun, 26 Jul 2009 18:02:45 +0100
Subject: [PATCH] DB schema in sqlite format

---
 pctb/db-schema.sqlite | 59 +++++++++++++++++++++++++++++++++++++++++++
 pctb/schema-notes.txt | 39 ----------------------------
 2 files changed, 59 insertions(+), 39 deletions(-)
 create mode 100755 pctb/db-schema.sqlite
 delete mode 100644 pctb/schema-notes.txt

diff --git a/pctb/db-schema.sqlite b/pctb/db-schema.sqlite
new file mode 100755
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
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
-- 
2.30.2