From 181fe43c48c7f4c83c617a147769f4c3eba4774c Mon Sep 17 00:00:00 2001
From: =?utf8?q?Karel=20Ko=C4=8D=C3=AD?= <cynerd@email.cz>
Date: Tue, 28 Jul 2015 10:26:51 +0200
Subject: [PATCH] Add database table linuxgit and fix prepare sql command

---
 scripts/database.py       | 43 +++++++++++++++++++++++++++++++--------
 scripts/databaseclean.sql |  1 +
 scripts/databaseinit.sql  | 22 ++++++++++++++------
 3 files changed, 51 insertions(+), 15 deletions(-)

diff --git a/scripts/database.py b/scripts/database.py
index 2e96b44..3db66fe 100644
--- 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 0eb3193..b4077ce 100644
--- 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 eee51fa..fb765ca 100644
--- 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 
 );
-- 
2.39.2