bKWrUdMM

1CREATE TABLE IF NOT EXISTS wifi_node (
2  id          SERIAL PRIMARY KEY,
3  ssid        TEXT NOT NULL,
4  mac_addr    TEXT NOT NULL UNIQUE,
5  frequency   INTEGER NOT NULL,
6  channel     INTEGER NOT NULL
7);
8
9CREATE TABLE IF NOT EXISTS wifi_strength (
10  id            SERIAL PRIMARY KEY,
11  strength      INTEGER NOT NULL UNIQUE
12);
13
14CREATE TABLE IF NOT EXISTS gps_data (
15  id                SERIAL PRIMARY KEY,
16  latitude          REAL NOT NULL,
17  longitude         REAL NOT NULL,
18  UNIQUE (latitude, longitude)
19);
20
21CREATE TABLE IF NOT EXISTS wifi_node_strength (
22  wifi_strength_id    INTEGER NOT NULL,
23  wifi_node_id        INTEGER NOT NULL,
24  PRIMARY KEY         (wifi_strength_id, wifi_node_id),
25  FOREIGN KEY (wifi_strength_id) REFERENCES wifi_strength (id) ON UPDATE CASCADE,
26  FOREIGN KEY (wifi_node_id) REFERENCES wifi_node (id) ON UPDATE CASCADE
27);
28
29CREATE TABLE IF NOT EXISTS wifi_strength_gps (
30  wifi_strength_id    INTEGER NOT NULL,
31  gps_data_id         INTEGER NOT NULL,
32  PRIMARY KEY         (wifi_strength_id, gps_data_id),
33  FOREIGN KEY (wifi_strength_id) REFERENCES wifi_strength (id) ON UPDATE CASCADE,
34  FOREIGN KEY (gps_data_id) REFERENCES gps_data (id) ON UPDATE CASCADE
35);