From e7232af4994e4dd863ef27c7c675527d67c474d2 Mon Sep 17 00:00:00 2001
From: Lukas Metzger
Date: Wed, 10 Jun 2020 20:47:08 +0200
Subject: Added database structure

---
 db_structure.sql | 144 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 144 insertions(+)
 create mode 100644 db_structure.sql

(limited to 'db_structure.sql')

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;
+
-- 
cgit v1.2.3-55-g7522