diff options
| -rw-r--r-- | db_structure.sql | 144 |
1 files changed, 144 insertions, 0 deletions
diff --git a/db_structure.sql b/db_structure.sql new file mode 100644 index 0000000..5803111 --- /dev/null +++ b/db_structure.sql @@ -0,0 +1,144 @@ +CREATE TABLE dozmod ( + report bigint(20) NOT NULL, + days bigint(20) NOT NULL, + vms_total bigint(20) NOT NULL, + vms_new bigint(20) NOT NULL, + vms_updated bigint(20) NOT NULL, + vms_valid bigint(20) NOT NULL, + lectures_total bigint(20) NOT NULL, + lectures_new bigint(20) NOT NULL, + lectures_updated bigint(20) NOT NULL, + lectures_valid bigint(20) NOT NULL, + users_total bigint(20) NOT NULL, + users_organizations bigint(20) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +CREATE TABLE machine ( + report bigint(20) NOT NULL, + days bigint(20) NOT NULL, + property varchar(30) COLLATE utf8_bin NOT NULL, + value varchar(200) COLLATE utf8_bin NOT NULL, + count bigint(20) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +CREATE TABLE perLocation ( + report bigint(20) NOT NULL, + days bigint(20) NOT NULL, + locationname varchar(40) COLLATE utf8_bin NOT NULL, + totalTime bigint(20) NOT NULL, + totalOffTime bigint(20) NOT NULL, + totalSessionTime bigint(20) NOT NULL, + totalStandbyTime bigint(20) NOT NULL, + longSessions bigint(20) NOT NULL, + shortSessions bigint(20) NOT NULL, + totalIdleTime bigint(20) NOT NULL, + medianSessionLength bigint(20) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +CREATE TABLE perVM ( + report bigint(20) NOT NULL, + days bigint(20) NOT NULL, + vm varchar(40) COLLATE utf8_bin NOT NULL, + sessions bigint(20) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +CREATE TABLE reports ( + id bigint(20) NOT NULL, + date date NOT NULL, + ip varchar(20) COLLATE utf8_bin NOT NULL, + version varchar(100) COLLATE utf8_bin NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +CREATE TABLE server ( + report bigint(20) NOT NULL, + cpuCount bigint(20) NOT NULL, + cpuModel varchar(200) COLLATE utf8_bin NOT NULL, + uptime bigint(20) NOT NULL, + memTotal bigint(20) NOT NULL, + memFree bigint(20) NOT NULL, + swapTotal bigint(20) NOT NULL, + swapUsed bigint(20) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +CREATE TABLE timeframe ( + report bigint(20) NOT NULL, + days bigint(20) NOT NULL, + tsFrom datetime NOT NULL, + tsTo datetime NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +CREATE TABLE total ( + report bigint(20) NOT NULL, + days bigint(20) NOT NULL, + totalTime bigint(20) NOT NULL, + totalOffTime bigint(20) NOT NULL, + totalSessionTime bigint(20) NOT NULL, + totalStandbyTime bigint(20) NOT NULL, + longSessions bigint(20) NOT NULL, + shortSessions bigint(20) NOT NULL, + totalIdleTime bigint(20) NOT NULL, + medianSessionLength bigint(20) NOT NULL, + uniqueUsers bigint(20) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +CREATE VIEW reports_newest AS + SELECT r.id AS id, r.date AS date, r.ip AS ip, r.version AS version + FROM ( + reports r + JOIN ( + SELECT reports.ip AS ip, max(reports.date) AS date + FROM reports + GROUP BY reports.ip + ) rm + ON (((r.ip = rm.ip) and (r.date = rm.date))) + ); + +ALTER TABLE dozmod + ADD PRIMARY KEY (report,days); + +ALTER TABLE machine + ADD PRIMARY KEY (report,days,property,value); + +ALTER TABLE perLocation + ADD PRIMARY KEY (report,days,locationname); + +ALTER TABLE perVM + ADD PRIMARY KEY (report,days,vm); + +ALTER TABLE reports + ADD PRIMARY KEY (id), + ADD UNIQUE KEY date_ip (date,ip) USING BTREE; + +ALTER TABLE server + ADD PRIMARY KEY (report); + +ALTER TABLE timeframe + ADD PRIMARY KEY (report,days); + +ALTER TABLE total + ADD PRIMARY KEY (report,days); + +ALTER TABLE reports + MODIFY id bigint(20) NOT NULL AUTO_INCREMENT; + +ALTER TABLE dozmod + ADD CONSTRAINT dozmod_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE machine + ADD CONSTRAINT machine_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE perLocation + ADD CONSTRAINT perLocation_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE perVM + ADD CONSTRAINT perVM_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE server + ADD CONSTRAINT server_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE timeframe + ADD CONSTRAINT timeframe_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE total + ADD CONSTRAINT total_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE; + |