Added support for storing Linux distro in the database

4 files changed, 211 insertions, 1 deletions

diff --git a/server/Makefile.am b/server/Makefile.am
index 6e83451..e7392b3 100644
--- a/server/Makefile.am
+++ b/server/Makefile.am
@@ -25,6 +25,7 @@
 SUBDIRS = parser
 dist_doc_DATA = parser/README.parser 	\
 	sql/delta-1.0_1.1.sql 		\
+	sql/delta-1.1_1.2.sql 		\
 	sql/rteval-$(SQLSCHEMAVER).sql
 
 apache-rteval.conf:
diff --git a/server/parser/xmlparser.xsl b/server/parser/xmlparser.xsl
index b656e42..191fe87 100644
--- a/server/parser/xmlparser.xsl
+++ b/server/parser/xmlparser.xsl
@@ -85,7 +85,7 @@
             <xsl:text>The parameter 'report_filename' parameter cannot be empty</xsl:text>
           </xsl:message>
         </xsl:if>
-	<sqldata schemaver="1.0" table="rtevalruns">
+	<sqldata schemaver="1.2" table="rtevalruns">
 	  <fields>
             <field fid="0">syskey</field>
             <field fid="1">kernel_ver</field>
@@ -98,6 +98,7 @@
             <field fid="8">report_filename</field>
 	    <field fid="9">rterid</field>
 	    <field fid="10">submid</field>
+	    <field fid="11">distro</field>
 	  </fields>
 	  <records>
             <record>
@@ -118,6 +119,7 @@
               <value fid="8"><xsl:value-of select="$report_filename"/></value>
               <value fid="9"><xsl:value-of select="$rterid"/></value>
               <value fid="10"><xsl:value-of select="$submid"/></value>
+	      <value fid="11"><xsl:value-of select="uname/baseos"/></value>
             </record>
 	  </records>
 	</sqldata>
diff --git a/server/sql/delta-1.1_1.2.sql b/server/sql/delta-1.1_1.2.sql
new file mode 100644
index 0000000..45c69ad
--- /dev/null
+++ b/server/sql/delta-1.1_1.2.sql
@@ -0,0 +1,5 @@
+-- SQL delta update from rteval-1.1.sql to rteval-1.2.sql
+
+UPDATE rteval_info SET value = '1.2' WHERE key = 'sql_schema_ver';
+
+ALTER TABLE rtevalruns ADD COLUMN distro VARCHAR(128);
diff --git a/server/sql/rteval-1.2.sql b/server/sql/rteval-1.2.sql
new file mode 100644
index 0000000..c2f6427
--- /dev/null
+++ b/server/sql/rteval-1.2.sql
@@ -0,0 +1,202 @@
+-- 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 rtevaldb_parser;
+    INSERT INTO rteval_info (key, value) VALUES ('sql_schema_ver','1.2');
+
+-- 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,
+        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);