Added support for PostgreSQL arrays + store cpu_topology CPU spread as an array

Implemented a new value type for the internal <sqldata/> XML format, which will
store data in an PostgreSQL array.  This is used now to store the CPU core spread
of the socket in a sepearate field in rtevalruns_details.

Signed-off-by: David Sommerseth <davids@redhat.com>

6 files changed, 328 insertions, 8 deletions

diff --git a/server/configure.ac b/server/configure.ac
index fe5e334..4a58def 100644
--- a/server/configure.ac
+++ b/server/configure.ac
@@ -25,7 +25,7 @@
 #   To create the ./configure script you need to run 'autoreconf --install'
 #
 
-AC_INIT([rteval-xmlrpc], [1.4], [davids@redhat.com])
+AC_INIT([rteval-xmlrpc], [1.5], [davids@redhat.com])
 SQLSCHEMAVER=1.3
 AC_SUBST(SQLSCHEMAVER)
 
diff --git a/server/parser/xmlparser.c b/server/parser/xmlparser.c
index fb1330a..b6eb44d 100644
--- a/server/parser/xmlparser.c
+++ b/server/parser/xmlparser.c
@@ -84,6 +84,24 @@ static char *encapsInt(const unsigned int val) {
 
 
 /**
+ * Simple function to determine if the given string is a number or not
+ *
+ * @param str Pointer to the tring to be checked
+ *
+ * @returns Returns 0 if not a number and a non-null value if it is a number
+ */
+int isNumber(const char * str)
+{
+    char *ptr = NULL;
+
+    if (str == NULL || *str == '\0' || isspace(*str))
+      return 0;
+
+    strtod (str, &ptr);
+    return *ptr == '\0';
+}
+
+/**
  * Parses any XML input document into a sqldata XML format which can be used by pgsql_INSERT().
  * The transformation must be defined in the input XSLT template.
  *
@@ -170,16 +188,17 @@ xmlDoc *parseToSQLdata(LogContext *log, xsltStylesheet *xslt, xmlDoc *indata_d,
  * @return Returns a pointer to a new buffer containing the value on success, otherwise NULL.
  *         This memory buffer must be free'd after usage.
  */
-static inline char *sqldataValueHash(LogContext *log, xmlNode *sql_n) {
+static char *sqldataValueHash(LogContext *log, xmlNode *sql_n) {
 	const char *hash = NULL, *isnull = NULL;
 	SHA1Context shactx;
 	uint8_t shahash[SHA1_HASH_SIZE];
 	char *ret = NULL, *ptr = NULL;
 	int i;
 
-	if( !sql_n || (xmlStrcmp(sql_n->name, (xmlChar *) "value") != 0)
-	    || (xmlStrcmp(sql_n->parent->name, (xmlChar *) "record") != 0) ) {
-		    return NULL;
+	if( !(sql_n && (xmlStrcmp(sql_n->name, (xmlChar *) "value") == 0)
+              && (xmlStrcmp(sql_n->parent->name, (xmlChar *) "record") == 0)
+              || (xmlStrcmp(sql_n->parent->name, (xmlChar *) "value") == 0)) ) {
+                return NULL;
 	}
 
 	isnull = xmlGetAttrValue(sql_n->properties, "isnull");
@@ -214,6 +233,70 @@ static inline char *sqldataValueHash(LogContext *log, xmlNode *sql_n) {
 
 
 /**
+ * Extract the content of a //sqldata/records/record/value[@type='array']/value node set
+ * and format it as an PostgreSQL array
+ *
+ * @param log    Log context
+ * @param sql_n sqldata values node containing the value to extract and format as an array.
+ *
+ * @return Returns a pointer to a new memory buffer containing the value as a string.
+ *         On errors, NULL is returned.  This memory buffer must be free'd after usage.
+ */
+static char * sqldataValueArray(LogContext *log, xmlNode *sql_n)
+{
+        char *ret = NULL, *ptr = NULL;
+        xmlNode *node = NULL;
+        size_t retlen = 0;
+
+        ret = malloc_nullsafe(log, 2);
+        if( ret == NULL ) {
+                writelog(log, LOG_ERR,
+                         "Failed to allocate memory for a new PostgreSQL array");
+                return NULL;
+        }
+        strncat(ret, "{", 1);
+
+        /* Iterate all ./value/value elements and build up a PostgreSQL specific array */
+        foreach_xmlnode(sql_n->children, node) {
+                if( (node->type != XML_ELEMENT_NODE)
+		    || xmlStrcmp(node->name, (xmlChar *) "value") != 0 ) {
+			// Skip uninteresting nodes
+			continue;
+		}
+                ptr = sqldataValueHash(log, node);
+                if( ptr ) {
+                        retlen += strlen(ptr) + 4;
+                        ret = realloc(ret, retlen);
+                        if( ret == NULL ) {
+                                writelog(log, LOG_ERR,
+                                         "Failed to allocate memory to expand "
+                                         "array to include '%s'", ptr);
+                                free_nullsafe(ret);
+                                free_nullsafe(ptr);
+                                return NULL;
+                        }
+                        /* Newer PostgreSQL servers expects numbers to be without quotes */
+                        if( isNumber(ptr) == 0 ) {
+                                /* Data is a string */
+                                strncat(ret, "'", 1);
+                                strncat(ret, ptr, strlen(ptr));
+                                strncat(ret, "',", 2);
+                        } else {
+                                /* Data is a number */
+                                strncat(ret, ptr, strlen(ptr));
+                                strncat(ret, ",", 1);
+                        }
+                        free_nullsafe(ptr);
+                }
+        }
+        /* Replace the last comma with a close-array marker */
+        ret[strlen(ret)-1] = '}';
+        ret[strlen(ret)] = 0;
+        return ret;
+}
+
+
+/**
  * Extract the content of a '//sqldata/records/record/value' node.  It will consider
  * both the 'hash' and 'type' attributes of the 'value' tag.
  *
@@ -239,6 +322,8 @@ char *sqldataExtractContent(LogContext *log, xmlNode *sql_n) {
 			chld_n = chld_n->next;
 		}
 		return xmlNodeToString(log, chld_n);
+        } else if( valtype && (strcmp(valtype, "array") == 0) ) {
+                return sqldataValueArray(log, sql_n);
 	} else {
 		return sqldataValueHash(log, sql_n);
 	}
diff --git a/server/parser/xmlparser.xsl b/server/parser/xmlparser.xsl
index 005d303..2ee9370 100644
--- a/server/parser/xmlparser.xsl
+++ b/server/parser/xmlparser.xsl
@@ -23,6 +23,9 @@
 <xsl:stylesheet  version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
 
+  <!-- Used for iterating CPU topology information -->
+  <xsl:key name="pkgkey" match="cpu" use="@physical_package_id"/>
+
   <xsl:template match="/rteval">
     <xsl:choose>
       <!-- TABLE: systems -->
@@ -132,7 +135,7 @@
             <xsl:text>Invalid 'rterid' parameter value: </xsl:text><xsl:value-of select="$rterid"/>
           </xsl:message>
         </xsl:if>
-        <sqldata schemaver="1.3" table="rtevalruns_details">
+        <sqldata schemaver="1.4" table="rtevalruns_details">
           <fields>
             <field fid="0">rterid</field>
             <field fid="1">numa_nodes</field>
@@ -140,6 +143,7 @@
             <field fid="3">num_cpu_sockets</field>
             <field fid="4">xmldata</field>
             <field fid="5">annotation</field>
+            <field fid="6">cpu_core_spread</field>
           </fields>
           <records>
             <record>
@@ -167,6 +171,13 @@
                 </rteval_details>
               </value>
               <value fid="5"><xsl:value-of select="run_info/annotate"/></value>
+              <value fid="6" type="array">
+                <xsl:for-each select="hardware/cpu_topology/cpu[generate-id() = generate-id(key('pkgkey', @physical_package_id)[1])]">
+                  <xsl:call-template name="count_core_spread">
+                    <xsl:with-param name="pkgid" select="@physical_package_id"/>
+                  </xsl:call-template>
+                </xsl:for-each>
+              </value>
             </record>
           </records>
         </sqldata>
@@ -290,4 +301,12 @@
 	<value fid="3"><xsl:value-of select="@value"/></value>
       </record>
   </xsl:template>
+
+  <!-- Helper "function" for generating a core per physical socket spread overview -->
+  <xsl:template name="count_core_spread">
+    <xsl:param name="pkgid"/>
+    <value>
+      <xsl:value-of select="count(/rteval/hardware/cpu_topology/cpu[@physical_package_id = $pkgid])"/>
+    </value>
+  </xsl:template>
 </xsl:stylesheet>
diff --git a/server/rteval-parser.spec b/server/rteval-parser.spec
index 66c8d9d..a348b01 100644
--- a/server/rteval-parser.spec
+++ b/server/rteval-parser.spec
@@ -1,6 +1,6 @@
 Name:		rteval-parser
-Version:	1.4
-%define sqlschemaver 1.3
+Version:	1.5
+%define sqlschemaver 1.4
 Release:	1%{?dist}
 Summary:	Report parser daemon for  rteval XML-RPC
 %define pkgname rteval-xmlrpc-%{version}
@@ -87,6 +87,10 @@ rm -rf $RPM_BUILD_ROOT
 
 
 %changelog
+* Fri Oct  7 2011 David Sommerseth <dazo@users.sourceforge.net> - 1.5-1
+- Added support for storing data as arrays in PostgreSQL
+- Updated SQL schema to store CPU topology/core spread as an array in the database
+
 * Fri Feb  4 2011 David Sommerseth <dazo@users.sourceforge.net> - 1.4-1
 - Added support for mod_wsgi
 - Updated SQL schema, to add rteval annotations to an explicit database column
diff --git a/server/sql/delta-1.3_1.4.sql b/server/sql/delta-1.3_1.4.sql
new file mode 100644
index 0000000..bfa4152
--- /dev/null
+++ b/server/sql/delta-1.3_1.4.sql
@@ -0,0 +1,5 @@
+-- SQL delta update from rteval-1.3.sql to rteval-1.4.sql
+
+UPDATE rteval_info SET value = '1.4' WHERE key = 'sql_schema_ver';
+
+ALTER TABLE rtevalruns_details ADD COLUMN cpu_core_spread INTEGER[];
diff --git a/server/sql/rteval-1.4.sql b/server/sql/rteval-1.4.sql
new file mode 100644
index 0000000..048600c
--- /dev/null
+++ b/server/sql/rteval-1.4.sql
@@ -0,0 +1,207 @@
+-- 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.4');
+
+-- 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,
+        cpu_core_spread 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);