""" This class contains all the functions for storing stuff in the database """ # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program 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 Library General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. # Copyright 2004 Dries Verachtert # if you want to have a pydar which works without a database or with another type of database # then you have to write a new subclass of Storage import sys sys.path.append("pydar") sys.path.append(".") from mastercommand import MasterCommand from log4py import Logger import config from storage import Storage import pgdb, posixpath, posix, string, time import rights # get the id of each specfile in rpmforge + the lastversionid #select sf.id, max(sv.id) as lastversionid from pydar2_specfile sf, pydar2_specrepo repo, pydar2_specfile_version sv where #repo.id=sf.specrepoid and repo.name='rpmforge' and sv.specfileid=sf.id group by sv.id, sf.id; class PostgresqlStorage(Storage): def __init__(self): self.__cat = Logger().get_instance(self) self.__cat.debug("initialized") def isBuildMachineId(self, id): retval = False conn = self.__getConnection() cursor = conn.cursor() sql = "select id from pydar2_distroarch where id='" + id + "'" self.__execute(cursor,sql) for row in cursor.fetchall(): retval = True cursor.close() self.__releaseConnection(conn) return retval def getNameSummaryAndGroupOfLastVersionsOfSpecFiles(self, specRepoName): retval = [] conn = self.__getConnection() cursor = conn.cursor() sql = "select current.id, current.lastversionid, tname.value as name, tsumm.value as summary, tgroup.value as group from (select sf.id, max(sv.id) as lastversionid from pydar2_specfile sf, pydar2_specrepo repo, pydar2_specfile_version sv where repo.id=sf.specrepoid and repo.name='" + specRepoName + "' and sv.specfileid=sf.id group by sf.id) as current, pydar2_specfile_tags tname, pydar2_specfile_tags tsumm, pydar2_specfile_tags tgroup where tname.versionid=current.lastversionid and tsumm.versionid=current.lastversionid and tgroup.versionid=current.lastversionid and tname.name='NAME' and tsumm.name='SUMMARY' and tgroup.name='GROUP' and tname.distroid=1 and tsumm.distroid=1 and tgroup.distroid=1 order by tgroup.value asc, tname.value asc" self.__execute(cursor,sql) for row in cursor.fetchall(): temp = {} temp['id'] = row[0] temp['lastversionid'] = row[1] temp['name'] = row[2] temp['summary'] = row[3] temp['group'] = row[4] retval.append(temp) cursor.close() self.__releaseConnection(conn) return retval def getAuthorityOfMostRecentVersion(self, specFileId): retval = None conn = self.__getConnection() cursor = conn.cursor() sql = "select t.value from pydar2_specfile_version sv, pydar2_specfile_tags t where t.versionid=sv.id and upper(t.name)='# AUTHORITY' and sv.specfileid=" + str(specFileId) + " order by sv.id desc limit 1" self.__cat.debug("sql:" + sql) self.__execute(cursor,sql) for row in cursor.fetchall(): retval = row[0] cursor.close() self.__releaseConnection(conn) return retval def getMostRecentSpecFiles(self, specRepoName): retval = [] conn = self.__getConnection() cursor = conn.cursor() sql = "select min(v.addtimestamp)::date as mintimestamp, tname.value, f.filename, f.subdir from pydar2_specfile_version v, pydar2_specfile f, pydar2_specfile_tags tname, pydar2_specrepo sr where tname.name='NAME' and tname.versionid=v.id and f.id=v.specfileid and f.specrepoid=sr.id and sr.name='" + specRepoName + "' group by f.id, tname.value, f.filename, f.subdir having min(v.addtimestamp) > (current_date - '1 month'::interval) order by mintimestamp asc" self.__execute(cursor,sql) for row in cursor.fetchall(): temphash = {} temphash['timestamp'] = row[0] # mintimestamp, first occurrence of this rpm with this version/release for this distroarchtag temphash['name'] = row[1] # short name: for example aget temphash['filename'] = row[2] # filename, for example: aget.spec temphash['subdir'] = row[3] retval.append(temphash) cursor.close() self.__releaseConnection(conn) return retval def getMostRecentBuilds(self, targetName): retval = [] conn = self.__getConnection() cursor = conn.cursor() sql = "select min(tf.addtimestamp)::date as mintimestamp, tf.filename, tf.name, tf.distroarchtag from pydar2_target_files tf, pydar2_targets targ, pydar2_specfile_version v, pydar2_specfile_tags tname, pydar2_distroarch distro where tf.version = v.version and tname.versionid=v.id and tname.name='NAME' and tname.value=tf.name and tname.distroid=distro.id and distro.distroarchtag=tf.distroarchtag and tf.targetid=targ.id and targ.name='" + targetName + "' group by tf.filename, tf.name, tf.distroarchtag having (min(tf.addtimestamp) > (current_date - '1 month'::interval)) order by mintimestamp asc, tf.name asc, tf.filename asc, tf.distroarchtag asc" self.__execute(cursor,sql) for row in cursor.fetchall(): temphash = {} temphash['timestamp'] = row[0] # mintimestamp, first occurrence of this rpm with this version/release for this distroarchtag temphash['filename'] = row[1] # filename, for example: aget/aget-0.200-5.1.el3.rf.i386.rpm temphash['name'] = row[2] # short name: for example aget temphash['distroarchtag'] = row[3] # distroarchtag retval.append(temphash) cursor.close() self.__releaseConnection(conn) return retval def getNameOfMostRecentVersion(self, specFileId): retval = None conn = self.__getConnection() cursor = conn.cursor() sql = "select t.value from pydar2_specfile_version sv, pydar2_specfile_tags t where t.versionid=sv.id and upper(t.name)='NAME' and sv.specfileid=" + str(specFileId) + " order by sv.id desc limit 1" self.__cat.debug("sql:" + sql) self.__execute(cursor,sql) for row in cursor.fetchall(): retval = row[0] cursor.close() self.__releaseConnection(conn) return retval def getSpecFileIdsToVersions(self, specRepository): retval = {} conn = self.__getConnection() cursor = conn.cursor() sql = "select sv.specfileid, max(version) as maxversion from pydar2_specfile_version sv, pydar2_specfile v where sv.specfileid=v.id and v.specrepoid=" + str(specRepository.getId()) + " group by sv.specfileid order by maxversion desc" self.__execute(cursor,sql) for row in cursor.fetchall(): retval[row[0]] = row[1] cursor.close() self.__releaseConnection(conn) return retval def getTargetRpmsSpecFileIdsToVersions(self,targetObj, datag,specRepository): retval = {} conn = self.__getConnection() cursor = conn.cursor() sql = "select specfileid, max(version) from (select sv.specfileid, sv.version from pydar2_specfile_version sv, pydar2_distroarch pd, pydar2_specfile_tags t1, pydar2_target_files tf, pydar2_specfile s where tf.name=t1.value and t1.name='NAME' and t1.versionid=sv.id and s.id=sv.specfileid and tf.version=sv.version and t1.distroid=pd.id and pd.distroarchtag=tf.distroarchtag and s.specrepoid=" + str(specRepository.getId())+ " and tf.targetid=" + str(targetObj.getId() )+ " and tf.distroarchtag='" + datag + "') as foo1 group by specfileid" self.__execute(cursor,sql) for row in cursor.fetchall(): retval[row[0]] = row[1] cursor.close() self.__releaseConnection(conn) return retval def updateTargetFile(self, targetObj, fileName, checksum, version, distroarchtag, name): self.__cat.debug("start, fileName:" + str(fileName) + ",checksum:" + str(checksum) + ",version:" + str(version) + ",distroarchtag:" + str(distroarchtag) + ",name:" + str(name)) conn = self.__getConnection() cursor = conn.cursor() sql = "update pydar2_target_files set targetid=" + str(targetObj.getId()) + ", version=" + str(version )+ ",name='"+ str(name) + "',checksum='"+ str(checksum) + "',distroarchtag='"+distroarchtag +"' where fileName='" + fileName + "'" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def insertTargetFile(self, targetObj, fileName, checksum, version, distroarchtag, name): self.__cat.debug("start, fileName:" + str(fileName) + ",checksum:" + str(checksum) + ",version:" + str(version) + ",distroarchtag:" + str(distroarchtag) + ",name:" + str(name)) conn = self.__getConnection() cursor = conn.cursor() sql = "insert into pydar2_target_files (targetid, filename, name, version, checksum, distroarchtag) values (" + str(targetObj.getId()) + ",'"+ fileName + "','"+ name + "'," + str(version) + ",'" + str(checksum) + "','"+ distroarchtag+ "')" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def getDistroArchTags(self): retval = [] conn = self.__getConnection() cursor = conn.cursor() sql = "select distroarchtag from pydar2_distroarch" self.__execute(cursor,sql) for row in cursor.fetchall(): retval.append(row[0]) cursor.close() self.__releaseConnection(conn) return retval def getSpecRepositoryFileListToIds(self,specRepo): retval = {} conn = self.__getConnection() cursor = conn.cursor() sql = "select subdir, filename, id from pydar2_specfile where specrepoid=" + str(specRepo.getId()) self.__execute(cursor,sql) if cursor.rowcount > 0: arr = cursor.fetchall() for row in arr: subdir = row[0] filename = row[1] id = row[2] retval[subdir + '/' + filename] = id cursor.close() self.__releaseConnection(conn) return retval # this gets the newest version of a spec file. This is not the same as the id of the newest version # the version might be for example the svnlatestcommitrevision of a file and is unique for 1 spec file # the version id is unique for all spec files for all spec repositories def getNewestVersion(self, specId): self.__cat.debug("start") retval = None conn = self.__getConnection() cursor = conn.cursor() sql = "select version from pydar2_specfile_version where specfileid=" + str(specId) self.__execute(cursor,sql) if cursor.rowcount > 0: row= cursor.fetchone() retval = row[0] cursor.close() self.__releaseConnection(conn) return retval def saveSpecRepositorySpecFile(self, specRepo, specRepoSpecFile): conn = self.__getConnection() cursor = conn.cursor() if specRepoSpecFile.getId() != None and specRepoSpecFile.getId() >= 0: # it should be in the database.. sql = "update pydar2_specfile set filename='" + str(specRepoSpecFile.getFileName()) + "', " sql = sql + " subdir='" + str(specRepoSpecFile.getSubDir()) + "', " sql = sql + " specrepoid=" + str(specRepo.getId()) + ", " sql = sql + " checksum='" + str(specRepoSpecFile.getCheckSum()) + "' " sql = sql + " where id=" + str(specRepoSpecFile.getId()) self.__execute(cursor,sql) conn.commit() else: # new spec file, insert it sql = "insert into pydar2_specfile (specrepoid, filename, subdir, checksum) values (" sql = sql + "" + str(specRepo.getId()) + "," sql = sql + "'" + str(specRepoSpecFile.getFileName()) + "'," sql = sql + "'" + str(specRepoSpecFile.getSubDir()) + "'," sql = sql + "'" + str(specRepoSpecFile.getCheckSum()) + "')" self.__execute(cursor,sql) conn.commit() # now get the id so we can return it at the end sql="select id from pydar2_specfile where filename='" + str(specRepoSpecFile.getFileName()) + "' and subdir='" + str(specRepoSpecFile.getSubDir()) + "' and specrepoid=" + str(specRepo.getId()) self.__execute(cursor,sql) retval = None if cursor.rowcount > 0: arr = cursor.fetchall() for row in arr: retval = row[0] cursor.close() self.__releaseConnection(conn) specRepoSpecFile.setId(retval) # now check if we also have to save all tags of this spec file if self.getConfig().getSaveSpecRepositorySpecFileTags(): self.__saveSpecRepositorySpecFileTags(specRepo,specRepoSpecFile) return retval def __isSpecRepositorySpecFileVersionInfoPresent(self,specfileid,currentversion): conn = self.__getConnection() cursor = conn.cursor() sql = "select version from pydar2_specfile_version where specfileid=" + str(specfileid) + " and version=" + str(currentversion) self.__cat.debug(sql) self.__execute(cursor,sql) retval = 0 if cursor.rowcount > 0: retval = 1 cursor.close() self.__releaseConnection(conn) return retval def __insertSpecRepositorySpecfileVersion(self,specfileid,currentversion): conn = self.__getConnection() cursor = conn.cursor() sql="insert into pydar2_specfile_version (specfileid,version) values (" + str(specfileid) + "," + str(currentversion) + ")" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def __saveSpecRepositorySpecFileTags(self,specRepo,specRepoSpecFile): self.__cat.debug("start") specfileid = specRepoSpecFile.getId() currentversion = specRepoSpecFile.getVersion() self.__cat.debug("current version is " + str(currentversion)) alreadyImported = self.__isSpecRepositorySpecFileVersionInfoPresent(specfileid,currentversion) if alreadyImported: # strange.. this function is normally only called when the file is changed # but the cvs or svn or .. version is not changed => somebody changed the file in the privat e self.__cat.warn("already imported in db, specRepo=" + specRepo.getName() + ",file id=" + str(specRepoSpecFile.getId())) else: self.__cat.debug("need to import all the variables..") self.__insertSpecRepositorySpecfileVersion(specfileid,currentversion) self.__insertSpecRepositorySpecFileTagsPerDistro(specRepo, specRepoSpecFile, currentversion) def addMissingSpecRepositorySpecFileTagsPerDistro(self, specRepo): specFiles = specRepo.getSpecRepositorySpecFiles() for i in specFiles: self.__addMissingSpecRepositorySpecFileTagsPerDistro(specRepo, i) def __addMissingSpecRepositorySpecFileTagsPerDistro(self, specRepo, specRepoSpecFile): self.__cat.debug("start, specRepoSpecFile id:" + str(specRepoSpecFile.getId())) # check if there are tags for each distro for this version # the specfile should already be in the db, so simply the most recent version is checked currentversion = specRepoSpecFile.getVersion() versionid=self.getVersionIdOfVersion(specRepoSpecFile,currentversion) sql = "select pd.id, pd.distroarchtag, defines, (select count(*) from pydar2_specfile_tags t, pydar2_specfile_version v where t.distroid=pd.id and t.versionid=v.id and v.specfileid=" + str(specRepoSpecFile.getId()) + " and v.version=" + str(currentversion) + ") as cnt from pydar2_distroarch pd" conn = self.__getConnection() cursor = conn.cursor() self.__execute(cursor,sql) rsdistros = cursor.fetchall() for row in rsdistros: distroid=row[0] distroarchtag = row[1] defines = row[2] count = row[3] if count <= 0: # need to import the tags sf = specRepoSpecFile.getSpecFileTags(defines) self.__insertSpecRepositorySpecFileTagsPerDistroDirect(versionid, sf, distroarchtag, cursor) conn.commit() cursor.close() self.__releaseConnection(conn) # returns a hash wich contains the fields code => defines from the db table pydar2_distro def __getDistroCodeToDefine(self): retval = {} conn = self.__getConnection() cursor = conn.cursor() sql = "select distroarchtag, defines from pydar2_distroarch where disabled='0'" self.__execute(cursor,sql) rsdistros = cursor.fetchall() for row in rsdistros: code = row[0] defines = row[1] print "code=" + code print "defines=" + defines retval[code] = defines cursor.close() self.__releaseConnection(conn) return retval def getVersionIdOfVersion(self, specRepoSpecFile, currentversion): specfileid= specRepoSpecFile.getId() conn = self.__getConnection() cursor = conn.cursor() # first get the versionid sql = "select id from pydar2_specfile_version where specfileid=" + str(specRepoSpecFile.getId()) + " and version=" + str(currentversion) versionid = None self.__execute(cursor,sql) versionid="" rstmp = cursor.fetchall() for row in rstmp: versionid = row[0] self.__cat.debug("versionid=" + str(versionid)) cursor.close() self.__releaseConnection(conn) return versionid def __insertSpecRepositorySpecFileTagsPerDistro(self,specRepo, specRepoSpecFile,currentversion): specfileid= specRepoSpecFile.getId() self.__cat.debug("start,specfileid=" + str(specRepoSpecFile.getId()) + ",currentversion=" + str(currentversion)) distros = self.__getDistroCodeToDefine() conn = self.__getConnection() cursor = conn.cursor() versionid=self.getVersionIdOfVersion(specRepoSpecFile,currentversion) for distrocode in distros.keys(): defines = distros[distrocode] sf = specRepoSpecFile.getSpecFileTags(defines) self.__insertSpecRepositorySpecFileTagsPerDistroDirect(versionid, sf, distrocode, cursor) conn.commit() cursor.close() self.__releaseConnection(conn) def __insertSpecRepositorySpecFileTagsPerDistroDirect(self, versionid, specFileTags, distrocode, cursor): sf = specFileTags for tag in sf.rpmforgetags: print 'rpmforge tag: ' + tag name = tag #print 'value: ' + sf.rpmforgetags[tag] value = string.replace(sf.rpmforgetags[tag],"'","''") value = string.rstrip(value,"\\") sql="insert into pydar2_specfile_tags (versionid,distroid,name,value) values (" + str(versionid) + ",(select id from pydar2_distroarch where distroarchtag='" + str(distrocode) + "'),'"+ str(name) + "','" + str(value) + "')" self.__execute(cursor,sql) for tag in sf.standardtags: print 'standard tag: ' + tag name = tag # print 'value: ' + string.replace(sf.standardtags[tag],"'","''") value = string.replace(sf.standardtags[tag],"'","''") value = string.rstrip(value,"\\") sql="insert into pydar2_specfile_tags (versionid,distroid,name,value) values (" + str(versionid) + ",(select id from pydar2_distroarch where distroarchtag='" + str(distrocode) + "'),'"+ str(name) + "','" + str(value) + "')" self.__execute(cursor,sql) def saveSpecRepository(self,specRepo): conn = self.__getConnection() cursor = conn.cursor() sql="select * from pydar2_specrepo where name='" + str(specRepo.getName()) + "'" self.__execute(cursor,sql) if cursor.rowcount > 0: # specrepo exists, update it sql = "update pydar2_specrepo set rootdirectory='" + str(specRepo.getRootDirectory()) + "', type='" sql = sql + str(specRepo.getType()) + "', deleted='0' where name='" + str(specRepo.getName()) + "'" self.__execute(cursor,sql) conn.commit() else: # specrepo does not exist, add it sql = "insert into pydar2_specrepo (name, rootdirectory, type) values ('" + str(specRepo.getName()) + "','" + str(specRepo.getRootDirectory()) + "','" + str(specRepo.getType()) + "')" self.__execute(cursor,sql) conn.commit() # now get the id again sql="select id from pydar2_specrepo where name='" + str(specRepo.getName()) + "'" self.__execute(cursor,sql) retval = None arr = cursor.fetchall() for row in arr: retval = row[0] cursor.close() self.__releaseConnection(conn) return retval # @todo add some type of connection pooling myConnection = None lastConnectionTimestamp = -1 #currentConnectionUser = None def __getConnection(self): if PostgresqlStorage.myConnection == None: PostgresqlStorage.myConnection = pgdb.connect(config.Config.getInstance().getPostgresqlConnectString()) #PostgresqlStorage.currentConnectionUser = name PostgresqlStorage.lastConnectionTimestamp = time.time() return PostgresqlStorage.myConnection else: if time.time() - PostgresqlStorage.lastConnectionTimestamp > 5*60: try: PostgresqlStorage.myConnection.close() except: self.__cat.debug("close of old connection failed") PostgresqlStorage.myConnection = pgdb.connect(config.Config.getInstance().getPostgresqlConnectString()) PostgresqlStorage.lastConnectionTimestamp = time.time() return PostgresqlStorage.myConnection def __releaseConnection(self,conn): closes = 0 #conn.close() def getTargetFileListToChecksums(self, targetObj): self.__cat.debug("start") retval = {} conn = self.__getConnection() cursor = conn.cursor() sql = "select filename, checksum from pydar2_target_files where targetid=" + str(targetObj.getId()) self.__execute(cursor,sql) if cursor.rowcount > 0: arr = cursor.fetchall() for row in arr: filename = row[0] checksum = row[1] retval[ filename] = checksum cursor.close() self.__releaseConnection(conn) return retval # returns a hash with ( __subDir + '/' + __fileName ) => ( checksum ) def getSpecRepositoryFileListToChecksum(self,specRepo): retval = {} conn = self.__getConnection() cursor = conn.cursor() sql = "select subdir, filename, checksum from pydar2_specfile where specrepoid " sql = sql + "in (select id from pydar2_specrepo where name='" + specRepo.getName() + "')" self.__execute(cursor,sql) if cursor.rowcount > 0: arr = cursor.fetchall() for row in arr: subdir = row[0] filename = row[1] checksum = row[2] retval[subdir + '/' + filename] = checksum cursor.close() self.__releaseConnection(conn) return retval # returns the command, but with the id set def createCommand(self,aCommand): self.__cat.debug("start") conn = self.__getConnection() cursor = conn.cursor() sql="select nextval('pydar2_commands_id_seq')" self.__execute(cursor,sql) id = -1 temparr = cursor.fetchall() for row in temparr: id = row[0] #commandName,userId,specrepoid,specfileid,toEmail,distroArchTag,priority,targetid sql="insert into pydar2_commands (id,commandname,userid,specrepoid,specfileid,toemail,distroarchtag,priority,targetid,version) values (" sql = sql + str(id) + ",'" sql = sql + aCommand.getCommandName() + "','" sql = sql + str(aCommand.getUserId()) + "'," sql = sql + str(aCommand.getSpecRepoId()) + "," sql = sql + str(aCommand.getSpecFileId()) + ",'" sql = sql + str(aCommand.getToEmail()) + "','" sql = sql + str(aCommand.getDistroArchTag()) + "'," sql = sql + str(aCommand.getPriority()) + "," sql = sql + str(aCommand.getTargetId()) + "," sql = sql + "(select max(version) from pydar2_specfile_version sv where sv.specfileid="+str(aCommand.getSpecFileId())+"))" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) aCommand.setId(id) return aCommand def checkIfValidUserId(self, userId, password, right): self.__cat.debug("start, userId=" + str(userId)) retval = 0 conn = self.__getConnection() cursor = conn.cursor() sql="select * from pydar2_users u, pydar2_rights r, pydar2_users_to_rights ur where u.id=ur.userid and ur.rightid=r.id and r.abbrev='"+ str(right) + "' and u.id='" + str(userId) + "' and u.password='" + str(password) + "'" self.__execute(cursor,sql) if cursor.rowcount > 0: retval = 1 cursor.close() self.__releaseConnection(conn) self.__cat.debug("retval=" + str(retval)) return retval def checkIfValidBuildMachineId(self, buildmachineid, password, right): self.__cat.debug("start, id=" + buildmachineid) conn = self.__getConnection() retval = 0 cursor = conn.cursor() sql="select * from pydar2_buildmachines u, pydar2_rights r, pydar2_buildmachines_to_rights ur where u.id=ur.buildmachineid and ur.rightid=r.id and r.abbrev='"+ str(right) + "' and u.id='" + str(buildmachineid) + "' and u.password='"+ str(password) + "'" self.__execute(cursor,sql) if cursor.rowcount > 0: retval = 1 cursor.close() self.__releaseConnection(conn) self.__cat.debug("retval=" + str(retval)) return retval def updateTarget(self, newTarget): self.__cat.debug("start") # check if exits conn = self.__getConnection() cursor = conn.cursor() sql = "select * from pydar2_targets where name='" + newTarget.getName() + "'" self.__execute(cursor,sql) if cursor.rowcount == 0: sql = "insert into pydar2_targets (name) values ('" + newTarget.getName() + "')" self.__execute(cursor,sql) conn.commit() # else: # nothing to update yet.. sql = "select id from pydar2_targets where name='" + newTarget.getName() + "'" self.__execute(cursor,sql) newId = None if cursor.rowcount > 0: rs = cursor.fetchone() newId = rs[0] newTarget.setId(newId) cursor.close() self.__releaseConnection(conn) def registerSlave(self, buildmachineid): self.__cat.debug("start, buildmachineid=" + buildmachineid) conn = self.__getConnection() cursor = conn.cursor() sql="insert into pydar2_buildmachine_registrations (id) values ('" + buildmachineid + "')" self.__execute(cursor,sql) sql="delete from pydar2_buildmachine_distroarchtags where buildmachineid='" + buildmachineid + "'" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def addDistroArchTag(self, buildmachineid, distroArchTag): self.__cat.debug("start, id=" + buildmachineid + ",distroArchTag=" + distroArchTag) conn = self.__getConnection() cursor = conn.cursor() sql="insert into pydar2_buildmachine_distroarchtags (buildmachineid,distroarchtag) values ('" + buildmachineid + "','" + distroArchTag + "')" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def addResultFileName(self,buildmachineid,commandId,fileName): # no check on buildmachineid yet... conn = self.__getConnection() cursor = conn.cursor() sql="insert into pydar2_actions_files (commandid,filename) values (" + str(commandId) + ",'" + fileName + "')" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def setBuildResult(self,buildmachineid,commandId,buildResult): conn = self.__getConnection() cursor = conn.cursor() sql="insert into pydar2_actions (buildmachineid,commandid,statusid) values ('" + buildmachineid + "'," + str(commandId) + "," + str(buildResult+1) + ")" self.__execute(cursor,sql) sql="update pydar2_commands set handled=true,inprogress=false where id=" + str(commandId) self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def getNumberOfNotYetStartedCommands(self): retval = None conn = self.__getConnection() cursor = conn.cursor() sql="select count(*) as total from pydar2_commands where not pc.handled and not pc.inprogress" self.__execute(cursor,sql) if cursor.rowcount > 0: row = cursor.fetchone() retval = row[0] conn.commit() cursor.close() self.__releaseConnection(conn) return retval def reserveCommand(self, buildmachineid): self.__cat.debug("start, buildmachineid=" + buildmachineid) conn = self.__getConnection() cursor = conn.cursor() sql = "select pc.id, pc.specfileid, pc.specrepoid, pc.toemail, pc.distroarchtag, pc.targetid, pc.commandname, pc.userid, pc.priority, pc.version from pydar2_commands pc where pc.commandname='BUILDSPECBYPATH' and not pc.handled and not pc.inprogress and pc.distroarchtag in (select distroarchtag from pydar2_buildmachine_distroarchtags where buildmachineid='" + buildmachineid + "') order by pc.priority desc, pc.distroarchtag desc, pc.id asc limit 1" self.__cat.debug("sql 1: " + sql) self.__execute(cursor,sql) retval = "" if cursor.rowcount > 0: rs = cursor.fetchone() self.__cat.debug("command found for buildmachineid=" + buildmachineid + ",rs=" + str(rs)) commandId = rs[0] specFileId = rs[1] specRepoId = rs[2] toEmail = rs[3] distroArchTag = rs[4] targetId = rs[5] commandName = rs[6] userId = rs[7] priority = rs[8] version = rs[9] aCommand = MasterCommand(commandName,userId,specRepoId,specFileId,toEmail,distroArchTag,priority,targetId) aCommand.setVersion(version) self.__cat.debug("command id: " + str(rs[0])) aCommand.setCommandId(rs[0]) self.__cat.debug("id according to command object: " + str(aCommand.getId())) sql="update pydar2_commands set inprogress=true where id=" + str(aCommand.getId()) self.__execute(cursor,sql) sql="insert into pydar2_actions (commandid, statusid, buildmachineid) values (" + str(aCommand.getId()) + ",0,'" + str(buildmachineid) + "')" self.__execute(cursor,sql) retval = aCommand else: self.__cat.debug("nothing to do for buildmachineid=" + buildmachineid) retval = "" conn.commit() cursor.close() self.__releaseConnection(conn) return retval def getCommand(self, commandId): self.__cat.debug("start, commandId=" + commandId) conn = self.__getConnection() cursor = conn.cursor() sql = "select pc.id, pc.specfileid, pc.specrepoid, pc.toemail, pc.distroarchtag, pc.targetid, pc.commandname, pc.userid, pc.priority, pc.version from pydar2_commands pc where pc.id=" + str(commandId) self.__cat.debug("sql 1: " + sql) self.__execute(cursor,sql) retval = "" if cursor.rowcount > 0: self.__cat.debug("rowcount > 0") rs = cursor.fetchone() self.__cat.debug("command found for commandId=" + str(commandId)) commandId = rs[0] specFileId = rs[1] specRepoId = rs[2] toEmail = rs[3] distroArchTag = rs[4] targetId = rs[5] commandName = rs[6] userId = rs[7] priority = rs[8] version = rs[9] aCommand = MasterCommand(commandName,userId,specRepoId,specFileId,toEmail,distroArchTag,priority,targetId) aCommand.setVersion(version) aCommand.setCommandId(rs[0]) retval = aCommand else: self.__cat.debug("rowcount <= 0") retval = None conn.commit() cursor.close() self.__releaseConnection(conn) return retval def getBuildMachineIdOfCommand(self, commandId): retval = None conn = self.__getConnection() cursor = conn.cursor() sql = "select buildmachineid, count(buildmachineid) as cnt from pydar2_actions where commandid=" + str(commandId) + " group by buildmachineid order by cnt desc" self.__execute(cursor,sql) if cursor.rowcount > 0: row = cursor.fetchone() retval = row[0] cursor.close() self.__releaseConnection(conn) return retval def getVersionIdsOfSpecRepoSpecFile(self, specRepoSpecFile): conn = self.__getConnection() cursor = conn.cursor() retval = [] sql = "select id from pydar2_specfile_version where specfileid=" + str(specRepoSpecFile.getId()) + " order by id desc" self.__execute(cursor,sql) all = cursor.fetchall() for row in all: retval.append(row[0]) cursor.close() self.__releaseConnection(conn) return retval # returns an array of hashes with keys name, value, distroarchtag def getTagsOfSpecFileVersion(self, versionId): conn = self.__getConnection() cursor = conn.cursor() retval = [] sql = "select t1.name, t1.value, d.distroarchtag from pydar2_specfile_tags t1, pydar2_distroarch d where t1.distroid=d.id and versionid=" + str(versionId) + " order by t1.name asc, t1.distroid asc" self.__execute(cursor,sql) for row in cursor.fetchall(): h = {} h["name"] = row[0] h["value"] = row[1] h["distroarchtag"] = row[2] retval.append(h) cursor.close() self.__releaseConnection(conn) return retval # returns a hash of hashes with first key distroarchtag with as value a hash with key = name and val= value def getTagsOfSpecFileVersionNew(self, versionId): conn = self.__getConnection() cursor = conn.cursor() retval = {} sql = "select t1.name, t1.value, d.distroarchtag from pydar2_specfile_tags t1, pydar2_distroarch d where t1.distroid=d.id and versionid=" + str(versionId) + " order by t1.distroid asc, t1.name asc" self.__execute(cursor,sql) for row in cursor.fetchall(): name = row[0] value = row[1] distroarchtag = row[2] if distroarchtag not in retval.keys(): retval[distroarchtag] = {} # autofill certain values retval[distroarchtag]['# Screenshot'] = "" retval[distroarchtag]['# Authority'] = "" retval[distroarchtag]['# Upstream'] = "" retval[distroarchtag][name] = value cursor.close() self.__releaseConnection(conn) return retval # get a mapping from something like fc3-i386 to --define fc3 1 --define dist fc3 for example def getDefinesByDistroArchTag(self,distroArchTag): conn = self.__getConnection() cursor = conn.cursor() sql = "select defines from pydar2_distroarch where distroarchtag='" + distroArchTag + "'" retval = "" self.__execute(cursor,sql) if cursor.rowcount > 0: rs = cursor.fetchone() retval = rs[0] cursor.close() self.__releaseConnection(conn) return retval def clearAssignedRights(self): conn = self.__getConnection() cursor = conn.cursor() sql = "delete from pydar2_users_to_rights" self.__execute(cursor,sql) sql = "delete from pydar2_buildmachines_to_rights" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def addAccount(self,name,type,fullname,passw,rights): conn = self.__getConnection() cursor = conn.cursor() # does the user already exist? tablename = "pydar2_users" if type=="BUILDMACHINE": tablename="pydar2_buildmachines" sql = "select * from " + tablename + " where id='" + name + "'" self.__execute(cursor,sql) if cursor.rowcount > 0: # update sql = "update " + tablename + " set name='" + fullname + "', password='" +str(passw)+ "' where id='" + name + "'" else: sql = "insert into " + tablename + " (id,name,password) values ('" + str(name) + "','" + str(fullname) + "','" + str(passw) + "')" self.__execute(cursor,sql) # add the rights tablename = "pydar2_users_to_rights" firstfield = "userid" if type=="BUILDMACHINE": tablename = "pydar2_buildmachines_to_rights" firstfield="buildmachineid" for r in rights: sql = "insert into " + str(tablename) + " (" + str(firstfield) + ",rightid) values ('" + str(name) + "',(select id from pydar2_rights where abbrev='" + str(r) + "'))" self.__execute(cursor,sql) conn.commit() cursor.close() self.__releaseConnection(conn) def printCounters(self): queryKeys = PostgresqlStorage.queryCounter.keys() queryKeys.sort(lambda x,y : PostgresqlStorage.queryCounter[x] > PostgresqlStorage.queryCounter[y]) for k in queryKeys: print "k:" + k + ",val:" + str(PostgresqlStorage.queryCounter[k]) def __execute(self,cursor,sql): if sql in PostgresqlStorage.queryCounter.keys(): PostgresqlStorage.queryCounter[sql] = PostgresqlStorage.queryCounter[sql] + 1 else: PostgresqlStorage.queryCounter[sql] = 1 cursor.execute(sql) queryCounter = {}