summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorLukas Metzger2020-06-10 20:47:08 +0200
committerLukas Metzger2020-06-10 20:47:08 +0200
commite7232af4994e4dd863ef27c7c675527d67c474d2 (patch)
treef1834a8c774caad374b74475f8abfd2be5b94de3
parentUnified requirements file (diff)
downloadbwlp-statistics-e7232af4994e4dd863ef27c7c675527d67c474d2.tar.gz
bwlp-statistics-e7232af4994e4dd863ef27c7c675527d67c474d2.tar.xz
bwlp-statistics-e7232af4994e4dd863ef27c7c675527d67c474d2.zip
Added database structure
-rw-r--r--db_structure.sql144
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;
+