1 #!/usr/bin/sqlite3 -init
4 CREATE TABLE IF NOT EXISTS buy (
5 commodid INTEGER NOT NULL,
6 islandid INTEGER NOT NULL,
7 stallid INTEGER NOT NULL,
10 PRIMARY KEY (commodid, islandid, stallid)
12 CREATE INDEX IF NOT EXISTS buy_by_island ON buy (commodid, islandid, price);
13 CREATE INDEX IF NOT EXISTS buy_by_price ON buy (commodid, price, islandid);
15 CREATE TABLE IF NOT EXISTS sell (
16 commodid INTEGER NOT NULL,
17 islandid INTEGER NOT NULL,
18 stallid INTEGER NOT NULL,
21 PRIMARY KEY (commodid, islandid, stallid)
23 CREATE INDEX IF NOT EXISTS sell_by_island ON sell (commodid, islandid, price);
24 CREATE INDEX IF NOT EXISTS sell_by_price ON sell (commodid, price, islandid);
26 CREATE TABLE IF NOT EXISTS commodities (
27 commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
28 commodname TEXT UNIQUE NOT NULL,
33 CREATE TABLE IF NOT EXISTS islands (
34 islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
35 islandname TEXT UNIQUE NOT NULL,
36 archipelago TEXT NOT NULL
39 CREATE TABLE IF NOT EXISTS stalls (
40 stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
41 islandid INTEGER NOT NULL,
42 stallname TEXT NOT NULL,
43 UNIQUE (islandid, stallname)
46 CREATE TABLE IF NOT EXISTS uploads (
47 islandid INTEGER PRIMARY KEY NOT NULL,
49 clientspec TEXT NOT NULL,
50 serverspec TEXT NOT NULL
53 CREATE TABLE IF NOT EXISTS distances (
54 aiid INTEGER NOT NULL,
55 biid INTEGER NOT NULL,
56 distance INTEGER NOT NULL,
57 PRIMARY KEY (aiid, biid)