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(200) 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;