# HG changeset patch # User Thomas Pelle Jakobsen <[EMAIL PROTECTED]> # Date 1226016684 -3600 # Node ID e01382aea278689dde142f6461403e25b852f6d8 # Parent 1e8dec2648c1486537076da790745074218cdc78 Added database wrapper class.
diff -r 1e8dec2648c1 -r e01382aea278 apps/benchmark/database.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/apps/benchmark/database.py Fri Nov 07 01:11:24 2008 +0100 @@ -0,0 +1,304 @@ +# -*- coding: utf-8 -*- +# +# Copyright 2007, 2008 VIFF Development Team. +# +# This file is part of VIFF, the Virtual Ideal Functionality Framework. +# +# VIFF is free software: you can redistribute it and/or modify it +# under the terms of the GNU Lesser General Public License (LGPL) as +# published by the Free Software Foundation, either version 3 of the +# License, or (at your option) any later version. +# +# VIFF is distributed in the hope that it will be useful, but WITHOUT +# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General +# Public License for more details. +# +# You should have received a copy of the GNU Lesser General Public +# License along with VIFF. If not, see <http://www.gnu.org/licenses/>. + +import MySQLdb +from enum import Enum + +# TODO: Need to secure this against injection attacks? +# TODO: What about trancactions and auto_commit? +# TODO: Do rollback when catching exceptions if multiple inserts, updates, etc. +class Database: + + # TODO: Is the assignment of integers to strings for + # the Enum class "implementation independent"? + AttributeType = Enum('Integer', 'String', 'Enumeration') + + def __init__(self, host="localhost", db="benchmark", passwd="", user="root", + port=3306): + self.db_host = host + self.db_port = port + self.db_name = db + self.db_user = user + self.db_passwd = passwd + self.connection = MySQLdb.connect(host=host, db=db, passwd=passwd, + user=user, port=port) + self.cursor = self.connection.cursor() + + def sql_query(self, query): + self.cursor.execute(query) + self.connection.commit() + # TODO: Doesn't scale to large result sets. + return self.cursor.fetchall() + + + + """ Adds a host and returns it's id in the database as a long value.""" + def add_host(self, name): + self.cursor.execute("INSERT INTO Host(name) VALUES('%s')" % name) + host_id = self.connection.insert_id() + self.connection.commit() + return host_id + + def get_host_name(self, id): + self.cursor.execute("SELECT name FROM Host WHERE id = '%d'" % int(id)) + res = self.cursor.fetchone() + if not res: + raise Exception("No host exists with id %d" % int(id)) + return res[0] + + def get_host_id(self, name): + self.cursor.execute("SELECT id FROM Host WHERE name = '%s'" % name) + res = self.cursor.fetchone() + if not res: + raise Exception("No host exists with name %s" % name) + if len(res) > 1: + raise Exception("More than one host exists with name %s" % name) + return res[0] + + def report_result(self, benchmark_id, host_id, result): + for run in range(len(result)): + for attr, value in result[run].items(): + self.cursor.execute(""" + INSERT INTO Result(benchmark, host, run, attribute, value) + VALUES (%d, %d, %d, '%s', %d)""" % + (benchmark_id, host_id, run, attr, value)) + self.connection.commit() + + def create_suite(self, revision): + self.cursor.execute("INSERT INTO Suite(revision) VALUES ('%s')" % + (revision,)) + suite_id = self.connection.insert_id() + self.connection.commit() + return suite_id + + def add_benchmark(self, suite_id, benchmark_name): + self.cursor.execute("INSERT INTO VIFFBenchmark(suite, name) " + + "VALUES ('%s', '%s')" % + (suite_id, benchmark_name)) + benchmark_id = self.connection.insert_id() + self.connection.commit() + return benchmark_id + + def add_benchmark_attribute(self, benchmark_id, attribute, value): + + # Find out whether attribute type is integer or string + query = "SELECT type FROM Attribute WHERE name = '%s'" % attribute + self.cursor.execute(query) + res = self.cursor.fetchone() + if not res: + raise Exception("Unknown attribute: %s" % attribute) + type = int(res[0]) + if type == self.AttributeType.String.index: + self.cursor.execute("INSERT INTO StringBenchmarkAttribute(" \ + "benchmark, attribute, value) VALUES " \ + "('%d','%s','%s')" % + (benchmark_id, attribute, value)) + elif type == self.AttributeType.Integer.index: + self.cursor.execute("INSERT INTO IntegerBenchmarkAttribute(" \ + "benchmark, attribute, value) VALUES " + "('%d','%s','%d')" % + (benchmark_id, attribute, value)) + elif type == self.AttributeType.Enumeration.index: + query = "SELECT value FROM Enumeration WHERE name = " \ + "'%s' AND attribute = '%s'" % (value, attribute) + self.cursor.execute(query) + res = self.cursor.fetchone() + if not res: + raise Exception("Unknown enumeration value for %s: %s" % + (attribute, value)) + enum_value = int(res[0]) + self.cursor.execute("INSERT INTO IntegerBenchmarkAttribute(" \ + "benchmark, attribute, value) VALUES " \ + "('%d','%s','%d')" % + (benchmark_id, attribute, enum_value)) + else: + raise Exception("Unknown attribute type: " + str(type)) + self.connection.commit() + + + def add_attribute(self, name, type=AttributeType.Integer, label=None, + enum=[]): + try: + if not label: + label = "NULL" + self.cursor.execute("INSERT INTO Attribute(name, " \ + "type, label) VALUES ('%s','%d','%s')" % + (name, type.index, label)) + if type == self.AttributeType.Enumeration: + for i in range(len(enum)): + self.cursor.execute("INSERT INTO Enumeration(attribute, " \ + "value, name) VALUES ('%s','%d','%s')" % + (name, i, enum[i])) + except: + self.connection.rollback() + raise + self.connection.commit() + + def reset(self): + """Rebuilds the complete database. This causes all data to be lost!""" + + self.cursor.execute("DROP DATABASE " + self.db_name) + self.cursor.execute("CREATE DATABASE " + self.db_name) + self.cursor.execute("USE " + self.db_name) + + """A suite is a collection of benchmarks that is executed in one + batch.""" + self.cursor.execute("CREATE TABLE Suite ("\ + "id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,"\ + "revision VARCHAR(12) NOT NULL,"\ + "starttime TIMESTAMP NOT NULL DEFAULT NOW()) "\ + "ENGINE=INNODB") + + """A benchmark is one piece of code or a protocol that is to be tested + individually with regard to time, memory usage, etc.""" + # TODO: This should be called just Benchmark, but this seems not to + # be allowed in some SQL implementations if a field of the same name + # exists?! + self.cursor.execute("CREATE TABLE VIFFBenchmark ("\ + "id INTEGER UNSIGNED PRIMARY KEY NOT NULL " + " AUTO_INCREMENT,"\ + "suite INTEGER UNSIGNED NOT NULL,"\ + "name VARCHAR(50) NOT NULL,"\ + "INDEX baz(id),"\ + "FOREIGN KEY (suite) REFERENCES Suite(id) "\ + "ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=INNODB") + + """An attribute can either have integer values (type 0), string values + (type 1), or enumerations (type 2). Enumerations act like + integers, but the corresponding names can be looked up in the + Enumeration table. Label is e.g. 'seconds', 'bytes', + 'hours', 'name' etc. A NULL label means just a number.""" + self.cursor.execute("CREATE TABLE Attribute ("\ + "name VARCHAR(32) PRIMARY KEY NOT NULL,"\ + "type INTEGER UNSIGNED NOT NULL,"\ + "label VARCHAR(40)) ENGINE=INNODB") + + self.cursor.execute("CREATE TABLE Enumeration ("\ + "attribute VARCHAR(32) NOT NULL,"\ + "value SMALLINT NOT NULL,"\ + "name VARCHAR(50),"\ + "INDEX nsdfii (name),"\ + "PRIMARY KEY(attribute, value),"\ + "FOREIGN KEY(attribute) REFERENCES Attribute(name) "\ + "ON DELETE CASCADE ON UPDATE CASCADE"\ + ") ENGINE=INNODB") + + """Each benchmark can have a number of integer attributes associated.""" + self.cursor.execute("CREATE TABLE IntegerBenchmarkAttribute ("\ + "benchmark INTEGER UNSIGNED NOT NULL,"\ + "attribute VARCHAR(32) NOT NULL,"\ + "value BIGINT NOT NULL,"\ + "PRIMARY KEY(benchmark, attribute),"\ + "INDEX foo(benchmark),"\ + "INDEX bar(attribute),"\ + "FOREIGN KEY(benchmark) REFERENCES VIFFBenchmark(id) "\ + "ON DELETE CASCADE ON UPDATE CASCADE, "\ + "FOREIGN KEY(attribute) REFERENCES Attribute(name) "\ + "ON DELETE CASCADE ON UPDATE CASCADE"\ + ") ENGINE=INNODB") + + """Each benchmark can have a number of string attributes associated.""" + self.cursor.execute("CREATE TABLE StringBenchmarkAttribute ("\ + "benchmark INTEGER UNSIGNED NOT NULL,"\ + "attribute VARCHAR(32) NOT NULL,"\ + "value TEXT,"\ + "PRIMARY KEY(benchmark, attribute),"\ + "INDEX foo(benchmark),"\ + "INDEX bar(attribute),"\ + "FOREIGN KEY(benchmark) REFERENCES VIFFBenchmark(id) "\ + "ON DELETE CASCADE ON UPDATE CASCADE, "\ + "FOREIGN KEY(attribute) REFERENCES Attribute(name) "\ + "ON DELETE CASCADE ON UPDATE CASCADE"\ + ") ENGINE=INNODB") + + """ Defines the hosts on which the benchmarks are run.""" + self.cursor.execute("CREATE TABLE Host ("\ + "id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,"\ + "name VARCHAR(50) NOT NULL) ENGINE=INNODB") + + """ Attributes for individual hosts, such as CPU frequency, number of + cores, RAM size, IP, etc.""" + self.cursor.execute("CREATE TABLE HostAttribute ("\ + "host INTEGER UNSIGNED NOT NULL,"\ + "attribute VARCHAR(32) NOT NULL,"\ + "value TEXT,"\ + "PRIMARY KEY(host, attribute),"\ + "INDEX foobar (host), INDEX sdflkj (attribute),"\ + "FOREIGN KEY(host) REFERENCES Host(id) "\ + "ON DELETE CASCADE ON UPDATE CASCADE,"\ + "FOREIGN KEY(attribute) REFERENCES Attribute(name) "\ + "ON DELETE CASCADE ON UPDATE CASCADE"\ + ") ENGINE=INNODB") + + """ Each benchmark involves a number of hosts.""" + self.cursor.execute("CREATE TABLE BenchmarkHost ("\ + "host INTEGER UNSIGNED NOT NULL,"\ + "benchmark INTEGER UNSIGNED NOT NULL,"\ + "id INTEGER,"\ + "PRIMARY KEY(host, benchmark),"\ + "INDEX enskef (host), INDEX sewerwe (benchmark),"\ + "FOREIGN KEY(host) REFERENCES Host(id) "\ + "ON UPDATE CASCADE ON DELETE RESTRICT,"\ + "FOREIGN KEY(benchmark) REFERENCES VIFFBenchmark(id) "\ + "ON UPDATE CASCADE ON DELETE CASCADE"\ + ") ENGINE=INNODB") + + """ Contains the results of an benchmark on a specific host. Runs should + increase continously from 0 to the number of runs.""" + self.cursor.execute("CREATE TABLE Result ("\ + "benchmark INTEGER UNSIGNED NOT NULL,"\ + "host INTEGER UNSIGNED NOT NULL,"\ + "run INTEGER NOT NULL,"\ + "attribute VARCHAR(32) NOT NULL,"\ + "value BIGINT NOT NULL,"\ + "PRIMARY KEY(benchmark, host, run, attribute),"\ + "INDEX nslnln (benchmark), INDEX nlslsqwe (host), "\ + "INDEX nslenkeb (attribute),"\ + "FOREIGN KEY(benchmark) REFERENCES VIFFBenchmark(id) "\ + "ON UPDATE CASCADE ON DELETE CASCADE,"\ + "FOREIGN KEY(host) REFERENCES Host(id) "\ + "ON UPDATE CASCADE ON DELETE RESTRICT,"\ + "FOREIGN KEY(attribute) REFERENCES Attribute(name) "\ + "ON UPDATE CASCADE ON DELETE CASCADE"\ + ") ENGINE=INNODB") + + """ This table is for benchmarks where there are multiple measurements + during one benchmark, e.g. the CPU load or memory usage during a + double auction. Time is milliseconds since the start of the + benchmark on the specific host.""" + self.cursor.execute("CREATE TABLE TimedResult ("\ + "benchmark INTEGER UNSIGNED NOT NULL,"\ + "host INTEGER UNSIGNED NOT NULL,"\ + "run INTEGER NOT NULL,"\ + "attribute VARCHAR(32) NOT NULL,"\ + "time BIGINT UNSIGNED NOT NULL,"\ + "value BIGINT NOT NULL,"\ + "PRIMARY KEY(benchmark, host, run, attribute, time),"\ + "INDEX ndgdgnln (benchmark), INDEX dgdgdg (host), "\ + "INDEX llrollo (attribute),"\ + "FOREIGN KEY(benchmark) REFERENCES VIFFBenchmark(id) "\ + "ON UPDATE CASCADE ON DELETE CASCADE,"\ + "FOREIGN KEY(host) REFERENCES Host(id) "\ + "ON UPDATE CASCADE ON DELETE RESTRICT,"\ + "FOREIGN KEY(attribute) REFERENCES Attribute(name) "\ + "ON UPDATE CASCADE ON DELETE CASCADE"\ + ") ENGINE=INNODB") + + self.connection.commit() + _______________________________________________ viff-patches mailing list [email protected] http://lists.viff.dk/listinfo.cgi/viff-patches-viff.dk
