import logging import os.path import errno import prserv try: import sqlite3 except ImportError: from pysqlite2 import dbapi2 as sqlite3 logger = logging.getLogger("BitBake.PRserv") sqlversion = sqlite3.sqlite_version_info if sqlversion[0] < 3 or (sqlversion[0] == 3 and sqlversion[1] < 3): raise Exception("sqlite3 version 3.3.0 or later is required.") class PRTable(): def __init__(self, conn, table, nohist): self.conn = conn self.nohist = nohist if nohist: self.table = "%s_nohist" % table else: self.table = "%s_hist" % table self._execute("CREATE TABLE IF NOT EXISTS %s \ (version TEXT NOT NULL, \ pkgarch TEXT NOT NULL, \ checksum TEXT NOT NULL, \ value INTEGER, \ PRIMARY KEY (version, pkgarch, checksum));" % self.table) def _execute(self, *query): """Execute a query, waiting to acquire a lock if necessary""" count = 0 while True: try: return self.conn.execute(*query) except sqlite3.OperationalError as exc: if 'database is locked' in str(exc) and count < 500: count = count + 1 continue raise exc def _getValueHist(self, version, pkgarch, checksum): data=self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table, (version, pkgarch, checksum)) row=data.fetchone() if row != None: return row[0] else: #no value found, try to insert try: self._execute("BEGIN") self._execute("INSERT OR ROLLBACK INTO %s VALUES (?, ?, ?, (select ifnull(max(value)+1,0) from %s where version=? AND pkgarch=?));" % (self.table,self.table), (version,pkgarch, checksum,version, pkgarch)) self.conn.commit() except sqlite3.IntegrityError as exc: logger.error(str(exc)) data=self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table, (version, pkgarch, checksum)) row=data.fetchone() if row != None: return row[0] else: raise prserv.NotFoundError def _getValueNohist(self, version, pkgarch, checksum): data=self._execute("SELECT value FROM %s \ WHERE version=? AND pkgarch=? AND checksum=? AND \ value >= (select max(value) from %s where version=? AND pkgarch=?);" % (self.table, self.table), (version, pkgarch, checksum, version, pkgarch)) row=data.fetchone() if row != None: return row[0] else: #no value found, try to insert try: self._execute("BEGIN") self._execute("INSERT OR REPLACE INTO %s VALUES (?, ?, ?, (select ifnull(max(value)+1,0) from %s where version=? AND pkgarch=?));" % (self.table,self.table), (version, pkgarch, checksum, version, pkgarch)) self.conn.commit() except sqlite3.IntegrityError as exc: logger.error(str(exc)) self.conn.rollback() data=self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table, (version, pkgarch, checksum)) row=data.fetchone() if row != None: return row[0] else: raise prserv.NotFoundError def getValue(self, version, pkgarch, checksum): if self.nohist: return self._getValueNohist(version, pkgarch, checksum) else: return self._getValueHist(version, pkgarch, checksum) def _importHist(self, version, pkgarch, checksum, value): val = None data = self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table, (version, pkgarch, checksum)) row = data.fetchone() if row != None: val=row[0] else: #no value found, try to insert try: self._execute("BEGIN") self._execute("INSERT OR ROLLBACK INTO %s VALUES (?, ?, ?, ?);" % (self.table), (version, pkgarch, checksum, value)) self.conn.commit() except sqlite3.IntegrityError as exc: logger.error(str(exc)) data = self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table, (version, pkgarch, checksum)) row = data.fetchone() if row != None: val = row[0] return val def _importNohist(self, version, pkgarch, checksum, value): try: #try to insert self._execute("BEGIN") self._execute("INSERT OR ROLLBACK INTO %s VALUES (?, ?, ?, ?);" % (self.table), (version, pkgarch, checksum,value)) self.conn.commit() except sqlite3.IntegrityError as exc: #already have the record, try to update try: self._execute("BEGIN") self._execute("UPDATE %s SET value=? WHERE version=? AND pkgarch=? AND checksum=? AND value=?;" % self.table, (version,pkgarch,checksum,value)) row=data.fetchone() if row != None: return row[0] else: return None def importone(self, version, pkgarch, checksum, value): if self.nohist: return self._importNohist(version, pkgarch, checksum, value) else: return self._importHist(version, pkgarch, checksum, value) def export(self, version, pkgarch, checksum, colinfo): metainfo = {} #column info if colinfo: metainfo['tbl_name'] = self.table metainfo['core_ver'] = prserv.__version__ metainfo['col_info'] = [] data = self._execute("PRAGMA table_info(%s);" % self.table) for row in data: col = {} col['name'] = row['name'] col['type'] = row['type'] col['notnull'] = row['notnull'] col['dflt_value'] = row['dflt_value'] col['pk'] = row['pk'] metainfo['col_info'].append(col) #data info datainfo = [] if self.nohist: sqlstmt = "SELECT T1.version, T1.pkgarch, T1.checksum, T1.value FROM %s as T1, \ (SELECT version,pkgarch,max(value) as maxvalue FROM %s GROUP BY version,pkgarch) as T2 \ WHERE T1.version=T2.version AND T1.pkgarch=T2.pkgarch AND T1.value=T2.maxvalue " % (self.table, self.table) else: sqlstmt = "SELECT * FROM %s as T1 WHERE 1=1 " % self.table sqlarg = [] where = "" if version: where += "AND T1.version=? " sqlarg.append(str(version)) if pkgarch: where += "AND T1.pkgarch=? " sqlarg.append(str(pkgarch)) if checksum: where += "AND T1.checksum=? " sqlarg.append(str(checksum)) sqlstmt += where + ";" if len(sqlarg): data = self._execute(sqlstmt, tuple(sqlarg)) else: data = self._execute(sqlstmt) for row in data: if row['version']: col = {} col['version'] = row['version'] col['pkgarch'] = row['pkgarch'] col['checksum'] = row['checksum'] col['value'] = row['value'] datainfo.append(col) return (metainfo, datainfo) class PRData(object): """Object representing the PR database""" def __init__(self, filename, nohist=True): self.filename=os.path.abspath(filename) self.nohist=nohist #build directory hierarchy try: os.makedirs(os.path.dirname(self.filename)) except OSError as e: if e.errno != errno.EEXIST: raise e self.connection=sqlite3.connect(self.filename, isolation_level="DEFERRED") self.connection.row_factory=sqlite3.Row self._tables={} def __del__(self): self.connection.close() def __getitem__(self,tblname): if not isinstance(tblname, basestring): raise TypeError("tblname argument must be a string, not '%s'" % type(tblname)) if tblname in self._tables: return self._tables[tblname] else: tableobj = self._tables[tblname] = PRTable(self.connection, tblname, self.nohist) return tableobj def __delitem__(self, tblname): if tblname in self._tables: del self._tables[tblname] logger.info("drop table %s" % (tblname)) self.connection.execute("DROP TABLE IF EXISTS %s;" % tblname)