From 165f6b3988a74bf3b02505fc49c64dfefb693adb Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Karel=20Ko=C4=8D=C3=AD?= <cynerd@email.cz>
Date: Wed, 22 Jul 2015 10:01:31 +0200
Subject: Implemented database

Database will be used for storing measured data.
---
 scripts/database.py      | 109 +++++++++++++++++++++++++++++++++++++++++++++++
 scripts/databaseinit.sql |  36 ++++++++++++++++
 2 files changed, 145 insertions(+)
 create mode 100644 scripts/database.py
 create mode 100644 scripts/databaseinit.sql

(limited to 'scripts')

diff --git a/scripts/database.py b/scripts/database.py
new file mode 100644
index 0000000..18ed724
--- /dev/null
+++ b/scripts/database.py
@@ -0,0 +1,109 @@
+import datetime
+import postgresql
+import collections
+
+import utils
+from conf import conf
+
+def __git_describe__():
+	return utils.callsubprocess('git_describe',
+			conf.git_describe_cmd, False).rstrip()
+
+def __git_commit__():
+	return utils.callsubprocess('git_rev_parse',
+			conf.git_commit_cmd, False).rstrip()
+
+def __timestamp__():
+	return datetime.datetime.now().strftime('%y-%m-%d-%H-%M-%S')
+
+Config = collections.namedtuple('Config', 'id hash cfile') # Named tuple for configuration
+Measure = collections.namedtuple('Measure', 'id conf_id mfile value') # Named tuple for measurement
+
+class database:
+	"Class used for accessing PostgreSQL project database."
+	def __init__(self):
+		self.db = postgresql.open(database = conf.db_database,
+				user = conf.db_user,
+				password = conf.db_password,
+				host = conf.db_host,
+				port = conf.db_port
+				)
+
+	def check_toolsgit(self):
+		"Return id of toolsgit row. If missing, it is inserted"
+		ds = __git_describe__()
+		cm = __git_commit__()
+		ps = self.db.prepare("""SELECT id FROM toolsgit
+							  WHERE git_describe = $1 AND git_commit = $2
+							  """)
+		id = ps(ds, cm)
+		if id:
+			return id[0][0]
+		ps = self.db.prepare("""INSERT INTO toolsgit
+						   (git_describe, git_commit)
+						   VALUES
+						   ($1, $2);
+						   """)
+		ps(ds, cm)
+		return self.check_toolsgit()
+
+	def add_configuration(self, hash, cfile):
+		"Add configuration to database."
+		ps = self.db.prepare("""INSERT INTO configurations
+								(hash, cfile, gtime, toolgit)
+								VALUES
+								($1, $2, $3, $4);
+								""")
+		gt = self.check_toolsgit()
+		tm = datetime.datetime.now()
+		ps(hash, cfile, tm, gt)
+
+	def get_configration(self, hash):
+		"Return configration id for inserted hash."
+		ps = self.db.prepare("""SELECT id, cfile FROM configurations
+								WHERE hash = $1""")
+		rtn = []
+		for dt in ps(hash):
+			rtn.append(Config(dt[0], hash, dt[1]))
+		return rtn
+
+	def add_measure(self, mfile, conf_id, value = None):
+		"Add measurement."
+		ps = self.db.prepare("""INSERT INTO measure
+								(conf, mfile, value, mtime, toolgit)
+								VALUES
+								($1, $2, $3, $4, $5);
+								""")
+		gt = self.check_toolsgit()
+		tm = datetime.datetime.now()
+		ps(conf_id, mfile, value, tm, gt)
+
+	def update_measure(self, measure_id, value):
+		"Update measured value"
+		ps = self.db.prepare("""UPDATE measure SET
+								(value) = ($2)
+								WHERE
+								id = $1;
+								""")
+		ps(measure_id, value)
+
+	def get_measures(self, conf_id):
+		"Get measures for configuration with conf_id id"
+		ps = self.db.prepare("""SELECT id, mfile, value FROM measure
+								WHERE conf = $1;
+								""")
+		rtn = []
+		for dt in ps(conf_id):
+			rtn.append(Measure(dt[0], conf_id, dt[1], dt[2]))
+		return rtn
+
+	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;
+								""")
+		rtn = []
+		for dt in ps():
+			rtn.append(Config(dt[0], dt[1], dt[2]))
+		return rtn
diff --git a/scripts/databaseinit.sql b/scripts/databaseinit.sql
new file mode 100644
index 0000000..95702fb
--- /dev/null
+++ b/scripts/databaseinit.sql
@@ -0,0 +1,36 @@
+--
+-- Drop all tables
+--
+DROP TABLE IF EXISTS measure;
+DROP TABLE IF EXISTS configurations;
+DROP TABLE IF EXISTS toolsgit;
+
+--
+-- Initialize database
+--
+
+-- 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
+);
+
+-- 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
+	gtime timestamp NOT NULL, -- Time and date of generation
+	toolgit BIGINT REFERENCES toolsgit (id) -- Reference to git version of tools 
+);
+
+-- This table stores measured data
+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
+	mtime timestamp NOT NULL, -- Time and date of measurement
+	toolgit BIGINT REFERENCES toolsgit (id) -- Reference to git version of tools 
+);
-- 
cgit v1.2.3