Implemented database

Database will be used for storing measured data.

diff --git a/conf.py b/conf.py
index e5a40595b130970938cb14af25cc1dfff3b50e1c..3909de7c4da4b164b3060e73bc2dec97a558fe6c 100644 (file)
--- a/conf.py
+++ b/conf.py
@@ -31,6 +31,29 @@ boot_command = ['scripts/novaboot/novaboot', nbscript] + novaboot_args
 # Arguments passed to PicoSAT.
 picosat_args = ['--all']
 
+# db_database
+# Database in PostgreSQL to be used for this tools
+db_database = 'lcp'
+# db_user
+# Define PostgreSQL user
+db_user = 'cynerd'
+# db_password
+# Define PostrgreSQL user password
+db_password = 'cynerd'
+# db_host
+# Address of PostgreSQL database server
+db_host = 'localhost'
+# db_port
+# Port of PotgreSQL database server
+db_port = 5432
+
+# git_describe_cmd
+# Command used for getting tools version and status from git
+git_describe_cmd = ['git', 'describe', '--always', '--tags', '--dirty']
+# git_commit_cmd
+# Command used for getting commit hash from git
+git_commit_cmd = ['git', 'rev-parse', '--verify', 'HEAD']
+
 ## Programs output show/hide
 # These options hides output of launched programs from terminal.
 # If variable is True, output is printed. Otherwise is hidden.

diff --git a/scripts/database.py b/scripts/database.py
new file mode 100644 (file)
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 (file)
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 
+);