Add database table linuxgit and fix prepare sql command

diff --git a/scripts/database.py b/scripts/database.py
index 2e96b44d3d4572f08ac8d73a42894422a45f4807..3db66fe77dffd840bb5b7e506985c75c3062e35e 100644 (file)
--- a/scripts/database.py
+++ b/scripts/database.py
@@ -1,3 +1,4 @@
+import os
 import datetime
 import postgresql
 import collections
@@ -5,6 +6,7 @@ import collections
 import utils
 import exceptions
 from conf import conf
+from conf import sf
 
 def __git_describe__():
        return utils.callsubprocess('git_describe',
@@ -55,16 +57,38 @@ class database:
                ps(ds, cm)
                return self.check_toolsgit()
 
+       def check_linuxgit(self):
+               "Return id of linuxgit row. If missing, it is inserted."
+               wd = os.getcwd()
+               os.chdir(sf(conf.linux_sources))
+               ds = __git_describe__()
+               cm = __git_commit__()
+               os.chdir(wd)
+               ps = self.db.prepare("""SELECT id FROM linuxgit
+                                                         WHERE git_describe = $1 AND git_commit = $2
+                                                         """)
+               id = ps(ds, cm)
+               if id:
+                       return id[0][0]
+               ps = self.db.prepare("""INSERT INTO linuxgit
+                                                  (git_describe, git_commit)
+                                                  VALUES
+                                                  ($1, $2);
+                                                  """)
+               ps(ds, cm)
+               return self.check_linuxgit()
+
        def add_configuration(self, hash, cfile):
                "Add configuration to database."
                ps = self.db.prepare("""INSERT INTO configurations
-                                                               (hash, cfile, gtime, toolgit)
+                                                               (hash, cfile, gtime, toolgit, linuxgit)
                                                                VALUES
-                                                               ($1, $2, $3, $4);
+                                                               ($1, $2, $3, $4, $5);
                                                                """)
                gt = self.check_toolsgit()
+               lgt = self.check_linuxgit()
                tm = datetime.datetime.now()
-               ps(hash, cfile, tm, gt)
+               ps(hash, cfile, tm, gt, lgt)
 
        def get_configration(self, hash):
                "Return configration id for inserted hash."
@@ -78,13 +102,14 @@ class database:
        def add_measure(self, mfile, conf_id, value = None):
                "Add measurement."
                ps = self.db.prepare("""INSERT INTO measure
-                                                               (conf, mfile, value, mtime, toolgit)
+                                                               (conf, mfile, value, mtime, toolgit, linuxgit)
                                                                VALUES
-                                                               ($1, $2, $3, $4, $5);
+                                                               ($1, $2, $3, $4, $5, $6);
                                                                """)
                gt = self.check_toolsgit()
+               lgt = self.check_linuxgit()
                tm = datetime.datetime.now()
-               ps(conf_id, mfile, value, tm, gt)
+               ps(conf_id, mfile, value, tm, gt, lgt)
 
        def update_measure(self, measure_id, value):
                "Update measured value"
@@ -107,9 +132,9 @@ class database:
 
        def get_unmeasured(self):
                "Returns list of all unmeasured configurations."
-               ps = self.db.prepare("""SELECT c.id, c.hash, c.cfile
-                                                               FROM configurations AS c, measure AS m
-                                                               WHERE c.id NOT IN m.conf;
+               ps = self.db.prepare("""SELECT * FROM configurations
+                                                               WHERE NOT EXISTS
+                                                               (SELECT conf FROM measure)
                                                                """)
                rtn = []
                for dt in ps():

diff --git a/scripts/databaseclean.sql b/scripts/databaseclean.sql
index 0eb3193a8b29df0de162ee223d603843e9611197..b4077ce03be8cb85feb6fee2e8cbf47d144ee3e1 100644 (file)
--- a/scripts/databaseclean.sql
+++ b/scripts/databaseclean.sql
@@ -1,3 +1,4 @@
 DROP TABLE IF EXISTS measure;
 DROP TABLE IF EXISTS configurations;
 DROP TABLE IF EXISTS toolsgit;
+DROP TABLE IF EXISTS linuxgit;

diff --git a/scripts/databaseinit.sql b/scripts/databaseinit.sql
index eee51fa86e7e10b4f93e225e31b0fc5ff67c7356..fb765caa4f26bc9c4dfd89d90a7e870e756fe9ff 100644 (file)
--- a/scripts/databaseinit.sql
+++ b/scripts/databaseinit.sql
@@ -1,16 +1,24 @@
 -- In this table are tracked versions of tools in git
 CREATE TABLE toolsgit (
        id BIGSERIAL PRIMARY KEY, -- Id
-       git_describe text NOT NULL, -- Git describe string (--always --tags --dirty)
-       git_commit text NOT NULL -- Commit hash of version of tool used for generating
+       git_describe TEXT NOT NULL, -- Git describe string (--always --tags --dirty)
+       git_commit TEXT NOT NULL -- Commit hash of version of tool used for generating
+);
+
+-- In this table are tracked versions of measured Linux in git
+CREATE TABLE linuxgit (
+       id BIGSERIAL PRIMARY KEY, -- Id
+       git_describe TEXT NOT NULL, -- Git describe scring (--always --tags --dirty)
+       git_commit TEXT NOT NULL -- Commit hash of version of tool used for generating
 );
 
 -- In this table are stored all generated configurations
 CREATE TABLE configurations (
        id BIGSERIAL PRIMARY KEY, -- Id
-       hash char(34) NOT NULL, -- Hash of configuration
-       cfile text NOT NULL, -- File path with configuration
+       hash char(32) NOT NULL, -- Hash of configuration
+       cfile TEXT NOT NULL, -- File path with configuration
        gtime timestamp NOT NULL, -- Time and date of generation
+       linuxgit BIGINT REFERENCES linuxgit (id), -- Reference to git version of Linux
        toolgit BIGINT REFERENCES toolsgit (id) -- Reference to git version of tools 
 );
 
@@ -18,8 +26,10 @@ CREATE TABLE configurations (
 CREATE TABLE measure (
        id BIGSERIAL PRIMARY KEY, -- Id
        conf BIGINT REFERENCES configurations (id), -- Reference to configuration
-       mfile text NOT NULL, -- File with measuring output
-       value BIGINT DEFAULT null, -- Measured data value
+       measurement TEXT NOT NULL, -- Text identifivator of measuring tool
+       mfile TEXT NOT NULL, -- File with measuring output
+       value DOUBLE PRECISION DEFAULT null, -- Measured data value
        mtime timestamp NOT NULL, -- Time and date of measurement
+       linuxgit BIGINT REFERENCES linuxgit (id), -- Reference to git version of Linux
        toolgit BIGINT REFERENCES toolsgit (id) -- Reference to git version of tools 
 );