-
Notifications
You must be signed in to change notification settings - Fork 7
/
schema-sqlite.go
159 lines (134 loc) · 6.09 KB
/
schema-sqlite.go
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
package database
var sqliteSchema = `
--
-- sqlite.sql - part of amtc-web, part of amtc
-- https://github.com/schnoddelbotz/amtc
--
-- amtc-web SQLite schema-only dump
--
-- notifications: Short messages for dashboard
CREATE TABLE "notification" (
"id" INTEGER PRIMARY KEY,
"tstamp" INTEGER(4) DEFAULT (strftime('%s','now')),
"user_id" INT NOT NULL,
"ntype" VARCHAR(12),
"message" VARCHAR(64),
FOREIGN KEY(user_id) REFERENCES user(id)
);
-- organizational units / rooms
CREATE TABLE "ou" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"parent_id" INTEGER NULL,
"optionset_id" INT,
"name" VARCHAR(128) NOT NULL,
"description" VARCHAR(255),
"idle_power" REAL,
"logging" INT DEFAULT 1,
FOREIGN KEY(optionset_id) REFERENCES optionset(id),
FOREIGN KEY(parent_id) REFERENCES ou(id) ON DELETE RESTRICT
);
-- clients to be placed into ous
CREATE TABLE "user" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"ou_id" INTEGER NOT NULL, -- currently only one related (top) OU; no distinct permissions
"is_enabled" INTEGER DEFAULT 1,
"is_admin" INTEGER DEFAULT 1,
"can_control" INTEGER DEFAULT 1,
"name" VARCHAR(64) UNIQUE NOT NULL,
"fullname" VARCHAR(64) NOT NULL,
"password" VARCHAR(64) NOT NULL,
"passsalt" VARCHAR(64) NOT NULL,
FOREIGN KEY(ou_id) REFERENCES ou(id)
);
-- clients to be placed into ous
CREATE TABLE "host" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"ou_id" INTEGER NOT NULL,
"hostname" VARCHAR(64) NOT NULL,
"enabled" INTEGER DEFAULT 1,
FOREIGN KEY(ou_id) REFERENCES ou(id) ON DELETE RESTRICT
);
-- state logging of hosts. log occurs upon state change.
CREATE TABLE "statelog" (
"host_id" INTEGER NOT NULL,
"state_begin" INTEGER(4) DEFAULT (strftime('%s','now')),
"open_port" INTEGER DEFAULT NULL,
"state_amt" INTEGER(1),
"state_http" INTEGER(2),
FOREIGN KEY(host_id) REFERENCES host(id) ON DELETE CASCADE
);
CREATE INDEX "logdata_ld" ON "statelog" ("state_begin");
CREATE INDEX "logdata_pd" ON "statelog" ("host_id");
CREATE VIEW "logday" AS
SELECT DISTINCT(date(state_begin,'unixepoch','localtime')) AS id
FROM statelog;
-- amt(c) option sets
CREATE TABLE "optionset" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" VARCHAR(128) NOT NULL,
"description" VARCHAR(128),
"sw_v5" INTEGER DEFAULT 0,
"sw_dash" INTEGER DEFAULT 1,
"sw_scan22" INTEGER DEFAULT 1,
"sw_scan3389" INTEGER DEFAULT 1,
"sw_usetls" INTEGER DEFAULT 0,
"sw_skipcertchk" INTEGER DEFAULT 0,
"opt_timeout" INTEGER DEFAULT 10,
"opt_passfile" VARCHAR(128),
"opt_cacertfile" VARCHAR(128)
);
-- monitoring / scheduled tasks / interactive jobs
CREATE TABLE "job" (
"id" INTEGER NOT NULL PRIMARY KEY,
"job_type" INTEGER, -- 1=interactive, 2=scheduled, 3=monitor
"job_status" INTEGER DEFAULT '0',
"user_id" INTEGER NOT NULL,
"amtc_cmd" CHAR(1) NOT NULL, -- U/D/R/C
"amtc_delay" REAL,
"amtc_bootdevice" CHAR(1) DEFAULT NULL, -- tbd; no support in amtc yet
"amtc_hosts" TEXT, -- now ids of hosts...? FIXME tbd
"ou_id" INTEGER, -- req'd to determine optionset; allow override?
"start_time" INTEGER(4) DEFAULT NULL, -- start time at day; tbd= minutes?
"repeat_interval" INTEGER, -- minutes
"repeat_days" INTEGER, -- pow(2, getdate()[wday])
"last_started" INTEGER(4) DEFAULT NULL,
"last_done" INTEGER(4) DEFAULT NULL,
"proc_pid" INTEGER, -- process id of currently running job
"description" VARCHAR(32), -- to reference it e.g. in logs (insb. sched)
FOREIGN KEY(ou_id) REFERENCES ou(id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES user(id)
);
--
-- Minimal initial set of records to let amtc-web look ok after initial install
--
-- example OUs ...
INSERT INTO "ou" VALUES(1,NULL,NULL,'ROOT','root',0,0);
INSERT INTO "ou" VALUES(2,1,NULL,'Student labs','Computer rooms',0,0);
INSERT INTO "ou" VALUES(3,2,NULL,'E Floor','All rooms on E floor',0,0);
INSERT INTO "ou" VALUES(4,3,3,'E 19','An example room on E floor',24.5,1);
-- example notification that will show up in dashboard
INSERT INTO "notification" (user_id,ntype,message) values (1,'warning','Congrats, amtc-web installed!');
-- some amtc option sets
INSERT INTO "optionset" VALUES(1,'DASH / No TLS','Uses DASH',0,1,1,1,0,0,10,'amtpassword.txt','');
INSERT INTO "optionset" VALUES(2,'DASH / TLS / VerifyCertSkip','Skips TLS certificate verification',0,1,1,1,1,1,10,'amtpassword.txt','');
INSERT INTO "optionset" VALUES(3,'DASH / TLS / VerifyCert','Most secure optionset',0,1,1,1,1,0,15,'amtpassword.txt','my.ca.crt');
-- put some hosts into E19
INSERT INTO "host" VALUES(1,4,'labpc-e19-01',1);
INSERT INTO "host" VALUES(2,4,'labpc-e19-02',1);
INSERT INTO "host" VALUES(3,4,'labpc-e19-03',1);
INSERT INTO "host" VALUES(4,4,'labpc-e19-04',1);
INSERT INTO "host" VALUES(5,4,'labpc-e19-05',1);
INSERT INTO "host" VALUES(6,4,'labpc-e19-06',1);
INSERT INTO "host" VALUES(7,4,'labpc-e19-07',1);
INSERT INTO "host" VALUES(8,4,'labpc-e19-08',1);
INSERT INTO "host" VALUES(9,4,'labpc-e19-09',1);
INSERT INTO "host" VALUES(10,4,'labpc-e19-10',1);
INSERT INTO "host" VALUES(11,4,'labpc-e19-11',1);
INSERT INTO "host" VALUES(12,4,'labpc-e19-12',1);
INSERT INTO "host" VALUES(13,4,'labpc-e19-13',1);
INSERT INTO "host" VALUES(14,4,'labpc-e19-14',1);
INSERT INTO "host" VALUES(15,4,'labpc-e19-15',1);
INSERT INTO "job" VALUES(1,2,0,1,'U',2.5,NULL,NULL,4,480,NULL,127,NULL,NULL,NULL,'Power-Up E19 Mon-Sun');
INSERT INTO "job" VALUES(2,2,0,1,'D',1.0,NULL,NULL,4,1290,NULL,62,NULL,NULL,NULL,'Power-Down E19 Mon-Fri');
INSERT INTO "job" VALUES(3,2,0,1,'D',1.0,NULL,NULL,4,960,NULL,65,NULL,NULL,NULL,'Power-Down E19 Sat+Sun');
`