From bec02b969c63a267467b0afe10f9510cf763fd45 Mon Sep 17 00:00:00 2001 From: David Sommerseth Date: Mon, 10 Jan 2011 16:34:52 +0100 Subject: Moved annotation data into a separate table field in rtevalruns_details Signed-off-by: David Sommerseth --- server/parser/xmlparser.xsl | 6 +- server/sql/delta-1.2_1.3.sql | 5 ++ server/sql/rteval-1.3.sql | 206 +++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 215 insertions(+), 2 deletions(-) create mode 100644 server/sql/delta-1.2_1.3.sql create mode 100644 server/sql/rteval-1.3.sql diff --git a/server/parser/xmlparser.xsl b/server/parser/xmlparser.xsl index 4330fec..005d303 100644 --- a/server/parser/xmlparser.xsl +++ b/server/parser/xmlparser.xsl @@ -132,13 +132,14 @@ Invalid 'rterid' parameter value: - + rterid numa_nodes num_cpu_cores num_cpu_sockets xmldata + annotation @@ -159,12 +160,13 @@ - + + diff --git a/server/sql/delta-1.2_1.3.sql b/server/sql/delta-1.2_1.3.sql new file mode 100644 index 0000000..b869756 --- /dev/null +++ b/server/sql/delta-1.2_1.3.sql @@ -0,0 +1,5 @@ +-- SQL delta update from rteval-1.2.sql to rteval-1.3.sql + +UPDATE rteval_info SET value = '1.3' WHERE key = 'sql_schema_ver'; + +ALTER TABLE rtevalruns_details ADD COLUMN annotation TEXT; diff --git a/server/sql/rteval-1.3.sql b/server/sql/rteval-1.3.sql new file mode 100644 index 0000000..0723cc1 --- /dev/null +++ b/server/sql/rteval-1.3.sql @@ -0,0 +1,206 @@ +-- Create rteval database users +-- +CREATE USER rtevxmlrpc NOSUPERUSER ENCRYPTED PASSWORD 'rtevaldb'; +CREATE USER rtevparser NOSUPERUSER ENCRYPTED PASSWORD 'rtevaldb_parser'; + +-- Create rteval database +-- +CREATE DATABASE rteval ENCODING 'utf-8'; + +\c rteval + +-- TABLE: rteval_info +-- Contains information the current rteval XML-RPC and parser installation +-- + CREATE TABLE rteval_info ( + key varchar(32) NOT NULL, + value TEXT NOT NULL, + rtiid SERIAL, + PRIMARY KEY(rtiid) + ); + GRANT SELECT ON rteval_info TO rtevparser; + INSERT INTO rteval_info (key, value) VALUES ('sql_schema_ver','1.3'); + +-- Enable plpgsql. It is expected that this PL/pgSQL is available. + CREATE LANGUAGE 'plpgsql'; + +-- FUNCTION: trgfnc_submqueue_notify +-- Trigger function which is called on INSERT queries to the submissionqueue table. +-- It will send a NOTIFY rteval_submq on INSERTs. +-- + CREATE FUNCTION trgfnc_submqueue_notify() RETURNS TRIGGER + AS $BODY$ + DECLARE + BEGIN + NOTIFY rteval_submq; + RETURN NEW; + END + $BODY$ LANGUAGE 'plpgsql'; + + -- The user(s) which are allowed to do INSERT on the submissionqueue + -- must also be allowed to call this trigger function. + GRANT EXECUTE ON FUNCTION trgfnc_submqueue_notify() TO rtevxmlrpc; + +-- TABLE: submissionqueue +-- All XML-RPC clients registers their submissions into this table. Another parser thread +-- will pickup the records where parsestart IS NULL. +-- + CREATE TABLE submissionqueue ( + clientid varchar(128) NOT NULL, + filename VARCHAR(1024) NOT NULL, + status INTEGER DEFAULT '0', + received TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + parsestart TIMESTAMP WITH TIME ZONE, + parseend TIMESTAMP WITH TIME ZONE, + submid SERIAL, + PRIMARY KEY(submid) + ) WITH OIDS; + CREATE INDEX submissionq_status ON submissionqueue(status); + + CREATE TRIGGER trg_submissionqueue AFTER INSERT + ON submissionqueue FOR EACH STATEMENT + EXECUTE PROCEDURE trgfnc_submqueue_notify(); + + GRANT SELECT, INSERT ON submissionqueue TO rtevxmlrpc; + GRANT USAGE ON submissionqueue_submid_seq TO rtevxmlrpc; + GRANT SELECT, UPDATE ON submissionqueue TO rtevparser; + +-- TABLE: systems +-- Overview table over all systems which have sent reports +-- The dmidata column will keep the complete DMIdata available +-- for further information about the system. +-- + CREATE TABLE systems ( + syskey SERIAL NOT NULL, + sysid VARCHAR(64) NOT NULL, + dmidata xml NOT NULL, + PRIMARY KEY(syskey) + ) WITH OIDS; + + GRANT SELECT,INSERT ON systems TO rtevparser; + GRANT USAGE ON systems_syskey_seq TO rtevparser; + +-- TABLE: systems_hostname +-- This table is used to track the hostnames and IP addresses +-- a registered system have used over time +-- + CREATE TABLE systems_hostname ( + syskey INTEGER REFERENCES systems(syskey) NOT NULL, + hostname VARCHAR(256) NOT NULL, + ipaddr cidr + ) WITH OIDS; + CREATE INDEX systems_hostname_syskey ON systems_hostname(syskey); + CREATE INDEX systems_hostname_hostname ON systems_hostname(hostname); + CREATE INDEX systems_hostname_ipaddr ON systems_hostname(ipaddr); + + GRANT SELECT, INSERT ON systems_hostname TO rtevparser; + + +-- TABLE: rtevalruns +-- Overview over all rteval runs, when they were run and how long they ran. +-- + CREATE TABLE rtevalruns ( + rterid SERIAL NOT NULL, -- RTEval Run Id + submid INTEGER REFERENCES submissionqueue(submid) NOT NULL, + syskey INTEGER REFERENCES systems(syskey) NOT NULL, + kernel_ver VARCHAR(32) NOT NULL, + kernel_rt BOOLEAN NOT NULL, + arch VARCHAR(12) NOT NULL, + distro VARCHAR(64), + run_start TIMESTAMP WITH TIME ZONE NOT NULL, + run_duration INTEGER NOT NULL, + load_avg REAL NOT NULL, + version VARCHAR(4), -- Version of rteval + report_filename TEXT, + PRIMARY KEY(rterid) + ) WITH OIDS; + + GRANT SELECT,INSERT ON rtevalruns TO rtevparser; + GRANT USAGE ON rtevalruns_rterid_seq TO rtevparser; + +-- TABLE rtevalruns_details +-- More specific information on the rteval run. The data is stored +-- in XML for flexibility +-- +-- Tags being saved here includes: /rteval/clocksource, /rteval/hardware, +-- /rteval/loads and /rteval/cyclictest/command_line +-- + CREATE TABLE rtevalruns_details ( + rterid INTEGER REFERENCES rtevalruns(rterid) NOT NULL, + annotation TEXT, + num_cpu_cores INTEGER, + num_cpu_sockets INTEGER, + numa_nodes INTEGER, + xmldata xml NOT NULL, + PRIMARY KEY(rterid) + ); + GRANT INSERT ON rtevalruns_details TO rtevparser; + +-- TABLE: cyclic_statistics +-- This table keeps statistics overview over a particular rteval run +-- + CREATE TABLE cyclic_statistics ( + rterid INTEGER REFERENCES rtevalruns(rterid) NOT NULL, + coreid INTEGER, -- NULL=system + priority INTEGER, -- NULL=system + num_samples BIGINT NOT NULL, + lat_min REAL NOT NULL, + lat_max REAL NOT NULL, + lat_mean REAL NOT NULL, + mode REAL NOT NULL, + range REAL NOT NULL, + median REAL NOT NULL, + stddev REAL NOT NULL, + mean_abs_dev REAL NOT NULL, + variance REAL NOT NULL, + cstid SERIAL NOT NULL, -- unique record ID + PRIMARY KEY(cstid) + ) WITH OIDS; + CREATE INDEX cyclic_statistics_rterid ON cyclic_statistics(rterid); + + GRANT INSERT ON cyclic_statistics TO rtevparser; + GRANT USAGE ON cyclic_statistics_cstid_seq TO rtevparser; + +-- TABLE: cyclic_histogram +-- This table keeps the raw histogram data for each rteval run being +-- reported. +-- + CREATE TABLE cyclic_histogram ( + rterid INTEGER REFERENCES rtevalruns(rterid) NOT NULL, + core INTEGER, -- NULL=system + index INTEGER NOT NULL, + value BIGINT NOT NULL + ) WITHOUT OIDS; + CREATE INDEX cyclic_histogram_rterid ON cyclic_histogram(rterid); + + GRANT INSERT ON cyclic_histogram TO rtevparser; + +-- TABLE: cyclic_rawdata +-- This table keeps the raw data for each rteval run being reported. +-- Due to that it will be an enormous amount of data, we avoid using +-- OID on this table. +-- + CREATE TABLE cyclic_rawdata ( + rterid INTEGER REFERENCES rtevalruns(rterid) NOT NULL, + cpu_num INTEGER NOT NULL, + sampleseq INTEGER NOT NULL, + latency REAL NOT NULL + ) WITHOUT OIDS; + CREATE INDEX cyclic_rawdata_rterid ON cyclic_rawdata(rterid); + + GRANT INSERT ON cyclic_rawdata TO rtevparser; + +-- TABLE: notes +-- This table is purely to make notes, connected to different +-- records in the database +-- + CREATE TABLE notes ( + ntid SERIAL NOT NULL, + reftbl CHAR NOT NULL, -- S=systems, R=rtevalruns + refid INTEGER NOT NULL, -- reference id, to the corresponding table + notes TEXT NOT NULL, + createdby VARCHAR(48), + created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(ntid) + ) WITH OIDS; + CREATE INDEX notes_refid ON notes(reftbl,refid); -- cgit