forked from dpabril/CS198
-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbtest.sql
94 lines (86 loc) · 3.07 KB
/
dbtest.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
.mode column
.headers on
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS QRTag;
DROP TABLE IF EXISTS Building;
DROP TABLE IF EXISTS Floor;
DROP TABLE IF EXISTS IndoorLocation;
DROP INDEX IF EXISTS FloorDex;
DROP INDEX IF EXISTS IndoorLocationDex;
CREATE TABLE QRTag (
url VARCHAR,
xcoord REAL NOT NULL,
ycoord REAL NOT NULL,
PRIMARY KEY (url)
);
CREATE TABLE Building (
alias VARCHAR,
name VARCHAR NOT NULL UNIQUE,
floors INTEGER NOT NULL,
delta REAL NOT NULL,
PRIMARY KEY (alias)
);
CREATE TABLE Floor (
bldg VARCHAR,
level INTEGER,
plan VARCHAR NOT NULL,
PRIMARY KEY (bldg, level),
FOREIGN KEY (bldg) REFERENCES Building--(alias)
ON UPDATE CASCADE
ON DELETE CASCADE
);
CREATE TABLE IndoorLocation (
bldg VARCHAR,
level INTEGER,
name VARCHAR,
xcoord REAL NOT NULL,
ycoord REAL NOT NULL,
PRIMARY KEY (bldg, level, name),
FOREIGN KEY (bldg, level) REFERENCES Floor--(bldg, level)
ON UPDATE CASCADE
ON DELETE CASCADE
);
CREATE UNIQUE INDEX FloorDex ON Floor(bldg, level);
CREATE UNIQUE INDEX IndoorLocationDex ON IndoorLocation(bldg, level, name);
INSERT INTO QRTag VALUES
-- Format: Building::FloorLevel::Point
("UP AECH::0::A", 0.0, 0.0),
("UP AECH::0::B", 0.0, 0.0),
("UP AECH::0::C", 0.0, 0.0),
("UP AECH::1::A", 1.0, 1.0),
("UP AECH::2::A", 0.0, 0.0),
("UP AECH::3::A", 0.0, 0.0),
("UP ITDC::1::A", 0.0, 0.0),
("UP ITDC::2::A", 0.0, 0.0),
("UP ITDC::3::A", 0.0, 0.0);
INSERT INTO Building VALUES
("UP AECH", "UP Alumni Engineering Centennial Hall", 4, 3.0),
("UP ITDC", "UP Information Technology Development Center", 3, 3.2);
INSERT INTO Floor VALUES
("UP AECH", 0, "basement_floorplan"),
("UP AECH", 1, "ground_floorplan"),
("UP AECH", 2, "second_floorplan"),
("UP AECH", 3, "third_floorplan"),
("UP ITDC", 1, "ground_floorplan"),
("UP ITDC", 2, "second_floorplan"),
("UP ITDC", 3, "third_floorplan");
INSERT INTO IndoorLocation VALUES
("UP AECH", 0, "Engineering Library 2", 0.0, 0.0),
("UP AECH", 1, "The Learning Commons", 0.0, 0.0),
("UP AECH", 2, "Rm 200 Web Science Laboratory", 0.0, 0.0),
("UP AECH", 3, "Administration Office", 0.0, 0.0),
("UP ITDC", 1, "Rm 101", 0.0, 0.0),
("UP ITDC", 2, "Conference Room", 0.0, 0.0),
("UP ITDC", 3, "Networks and Infrastructure", 0.0, 0.0);
-- Notes:
-- QRTag is for initialization and localization. URL attribute stores strings of the form
-- BuildingAlias::FloorLevel ->
-- BuildingAlias used to determine building
-- BuildingAlias::FloorLevel used to determine Floor
-- Building information important for altimeter updates
-- Floor rows store paths to floor plans for plane retexturing
-- IndoorLocation entries show up in UI; relocate pin marker upon selection
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Additional useful info:
-- - For Building, user marker rotation offset
-- - Don't forget to scale maps/user or both (not necessarily same time)