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;
