From 165f6b3988a74bf3b02505fc49c64dfefb693adb Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Karel=20Ko=C4=8D=C3=AD?= 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