import os import sys if sys.version_info[:2] > (2,4): import sqlite3 as sqlite else: try: import sqlite except ImportError: raise SystemExit('Unable to determine sqlite, please make sure it is installed.') class CasStorageException(Exception): pass class CasStorage(object): def __init__(self, database): """ setup database connection and return db cursor for traversing database """ self.db = database self.conn = None self.cursor = None def connect(self): """ execute connection """ try: self.conn = sqlite.connect(self.db) self.cursor = self.conn.cursor() except: raise CasStorageException('Cannot connect to database') return def commit(self): self.conn.commit() return def buildTable(self): # We want to add jobs to a database just in case # there is no structured form of purging old data. self.cursor.execute("""create table jobs ( job_id integer primary key autoincrement, identifier integer, date text, email text) """) self.cursor.execute("""create table debuginfo ( debug_id integer primary key autoincrement, rpm text) """) self.cursor.execute("""create table timestamp ( timestamp_id integer primary key autoincrement, debugpath text, timestamp text, debug_id integer) """) self.cursor.execute("""create table server ( server_id integer primary key autoincrement, arch text, port text, hostname text) """) self.commit() return # DEBUGINFO METHODS def addDebuginfoRPM(self, debuginfo): debuginfo = (debuginfo,) self.cursor.execute('SELECT * from debuginfo where rpm="%s"' % debuginfo) if not self.cursor.fetchone(): self.cursor.execute('INSERT into debuginfo(rpm) values("%s")' % debuginfo) self.commit() return def getAllDebuginfoRPM(self): self.cursor.execute("SELECT * FROM debuginfo") return self.cursor.fetchall() # TIMESTAMP METHODS def addTimestamp(self, id, debug, timestamp): """ build relation to debuginfo rpm and add debug path, timestamp """ values = (id, debug, timestamp) self.cursor.execute('SELECT * FROM timestamp where timestamp="%s"' % (timestamp,)) if not self.cursor.fetchone(): self.cursor.execute('''INSERT into timestamp (debug_id,debugpath, timestamp) values(%d,"%s","%s")''' % values) self.commit() return def getTimestampDebug(self, timestamp): """ return timestamp based on extracted core timestamp """ values = (timestamp,) self.cursor.execute('''SELECT rpm,debugpath FROM debuginfo, timestamp where timestamp.timestamp LIKE "%%%s%%" AND debuginfo.debug_id = timestamp.debug_id''' % values) return self.cursor.fetchone() # JOB METHODS def getAllJobs(self): """ all jobs """ self.cursor.execute('SELECT * FROM jobs') return self.cursor.fetchall() def getJobById(self, id): """ single job """ self.cursor.execute('SELECT * FROM jobs where id=%d' % (id,)) return self.cursor.fetchone() def getJobRange(self, days): """ provides jobs based on creation date from $days back """ pass def addJob(self, identifier, date, email): """ add job to db """ if email is None: email = "cas@localhost" values = (str(date), int(identifier), str(email)) self.cursor.execute('''INSERT into jobs (date,identifier,email) values ("%s",%d, "%s")''' % values) self.commit() return # SERVER METHODS def addServer(self, hostname, port, arch): """ add server/arch to db """ values = (hostname, port, arch) self.cursor.execute('select * from server where hostname="%s"' % (hostname,)) if not self.cursor.fetchone(): self.cursor.execute('''INSERT into server (hostname, arch, port) values ("%s","%s","%s")''' % values) self.commit() return def getServers(self): self.cursor.execute('select hostname, port, arch from server') return self.cursor.fetchall()