X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?p=ypp-sc-tools.db-test.git;a=blobdiff_plain;f=pctb%2Fdb-schema.sqlite;fp=pctb%2Fdb-schema.sqlite;h=8a42bb524810fff1aeef779330379f867076ccb3;hp=0000000000000000000000000000000000000000;hb=1313c474ebe91f5fbacf08fcc2302cb435ebb12b;hpb=a4acfa76a2d1401e734f8c335718379912a7f5d1

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