def getSoftwareDublicates(self,
clustername,
_select="*",
_limitup=0,
_limitdown=0,
_where=None,
_orderby=None):
if _where == None:
_where = list()
# Quickhack to support mysql3
if type(_where) == list:
for i in range(len(_where)):
_where[i] = "t1." + _where[i]
limit = BaseDB.getLimit(_limitup, _limitdown)
self.log.debug("where: %s" % (_where))
_where.append("t1.clustername=\"%s\"" % (clustername))
# _where+=" AND ".join(_where)
whereclause = BaseDB.resolveWhere(_where)
orderbyclause = BaseDB.resolveOrderBy(_orderby)
self.log.debug("whereclause: %s" % (whereclause))
query="""SELECT DISTINCT %s FROM %s AS t1
LEFT JOIN %s AS t2
USING (clustername, name)
%s
AND (t1.version != t2.version OR t1.subversion != t2.subversion)
%s %s;""" \
%("t1."+", t1.".join(_select), self.tablename, self.tablename, whereclause, orderbyclause, limit)
self.log.debug("query: %s" % (query))
return self.selectQuery(query)
def getDiffsFromSourcesByMaster(self, sourcenames, master, colnames=None, limitup=0, limitdown=0, where=None, orderby=None, Diffs=True, NotInstalled=True, Installed=False):
"""
Returns a resultset of differences of the given sourcenames.
Parameter are the sourcesnames to compare
"""
orderbyclause=BaseDB.resolveOrderBy(orderby)
limit=BaseDB.getLimit(limitup, limitdown)
self.log.debug("where: %s" %(where))
self.log.debug("orderbyclause: %s, limit: %s, diffs: %s, notinstalled: %s, Installed: %s" %(orderbyclause, limit, Diffs, NotInstalled, Installed))
if not colnames:
self.log.debug("getting colnames")
colnames=self.SELECT_FOR_DIFFS_MASTER
j=0
# ComLog.getLogger().debug("query %s" % query)
queries=list()
for sourcename in sourcenames:
if Installed:
queries.append(self.selectQueryOnlyEqualInstalledByMaster(sourcename, master, colnames, where, Diffs or NotInstalled))
if Diffs:
queries.append(self.selectQueryOnlyDiffsByMaster(sourcename, master, colnames, where, Installed))
if NotInstalled:
queries.append(self.selectQueryNotInstalledByMaster(sourcename, master, colnames, where, True, Installed))
queries.append(self.selectQueryNotInstalledByMaster(sourcename, master, colnames, where, False, Installed))
union="\n UNION \n".join(queries)
if orderbyclause and orderbyclause!="":
union+="\n"+orderbyclause
if limit and limit != "":
union+="\n"+limit
self.log.debug("union: "+union)
return self.selectQuery(union)
def getDiffsFromSources(self,
sourcenames,
colnames=None,
limitup=0,
limitdown=0,
where=None,
orderby=None,
Diffs=True,
NotInstalled=True,
Installed=False):
"""
Returns a resultset of differences of the given sourcenames.
Parameter are the sourcesnames to compare
"""
if not sourcenames or type(sourcenames) != list or len(
sourcenames) <= 1:
return None
orderbyclause = BaseDB.resolveOrderBy(orderby)
limit = BaseDB.getLimit(limitup, limitdown)
self.log.debug("where: %s" % (where))
self.log.debug(
"orderbyclause: %s, limit: %s, diffs: %s, notinstalled: %s" %
(orderbyclause, limit, Diffs, NotInstalled))
self.log.debug("getting colnames")
if not colnames:
colnames = self.getColnamesForDiff(sourcenames)
j = 0
# ComLog.getLogger().debug("query %s" % query)
queries = list()
installed = None
if Installed:
if Diffs or NotInstalled:
installed = 0
queries.append(
self.selectQueryInstalled(sourcenames, colnames,
SoftwareCMDB.COMPARE_2_SOFTWARE,
where, installed))
if Diffs:
if Installed:
installed = 1
queries.append(
self.selectQueryOnlyDiffs(sourcenames, colnames,
SoftwareCMDB.COMPARE_2_SOFTWARE,
where, installed))
if NotInstalled:
queries += self.selectQueriesNotInstalled(
sourcenames, colnames, SoftwareCMDB.COMPARE_2_SOFTWARE, where,
Installed)
union = "\n UNION \n".join(queries)
if orderbyclause and orderbyclause != "":
union += "\n" + orderbyclause
if limit and limit != "":
union += "\n" + limit
self.log.debug("union: " + union)
return self.selectQuery(union)
def getSoftware(self, clustername, select="*", limitup=0, limitdown=0, where=None, orderby=None):
if where==None:
where=list()
limit=BaseDB.getLimit(limitup, limitdown)
where.append("clustername=\"%s\"" %(clustername))
self.log.debug("where: %s" %(where))
whereclause=BaseDB.resolveWhere(where)
orderbyclause=BaseDB.resolveOrderBy(orderby)
self.log.debug("whereclause: %s" %(whereclause))
query="SELECT DISTINCT %s FROM %s %s %s %s;" %(", ".join(select), self.tablename, whereclause, orderbyclause, limit)
self.log.debug("query: %s" %(query))
return self.selectQuery(query)
def getDiffsFromSourcesByMaster(self,
sourcenames,
master,
colnames=None,
limitup=0,
limitdown=0,
where=None,
orderby=None,
Diffs=True,
NotInstalled=True,
Installed=False):
"""
Returns a resultset of differences of the given sourcenames.
Parameter are the sourcesnames to compare
"""
orderbyclause = BaseDB.resolveOrderBy(orderby)
limit = BaseDB.getLimit(limitup, limitdown)
self.log.debug("where: %s" % (where))
self.log.debug(
"orderbyclause: %s, limit: %s, diffs: %s, notinstalled: %s, Installed: %s"
% (orderbyclause, limit, Diffs, NotInstalled, Installed))
if not colnames:
self.log.debug("getting colnames")
colnames = self.SELECT_FOR_DIFFS_MASTER
j = 0
# ComLog.getLogger().debug("query %s" % query)
queries = list()
for sourcename in sourcenames:
if Installed:
queries.append(
self.selectQueryOnlyEqualInstalledByMaster(
sourcename, master, colnames, where, Diffs
or NotInstalled))
if Diffs:
queries.append(
self.selectQueryOnlyDiffsByMaster(sourcename, master,
colnames, where,
Installed))
if NotInstalled:
queries.append(
self.selectQueryNotInstalledByMaster(
sourcename, master, colnames, where, True, Installed))
queries.append(
self.selectQueryNotInstalledByMaster(
sourcename, master, colnames, where, False, Installed))
union = "\n UNION \n".join(queries)
if orderbyclause and orderbyclause != "":
union += "\n" + orderbyclause
if limit and limit != "":
union += "\n" + limit
self.log.debug("union: " + union)
return self.selectQuery(union)
def getSoftware(self,
clustername,
select="*",
limitup=0,
limitdown=0,
where=None,
orderby=None):
if where == None:
where = list()
limit = BaseDB.getLimit(limitup, limitdown)
where.append("clustername=\"%s\"" % (clustername))
self.log.debug("where: %s" % (where))
whereclause = BaseDB.resolveWhere(where)
orderbyclause = BaseDB.resolveOrderBy(orderby)
self.log.debug("whereclause: %s" % (whereclause))
query = "SELECT DISTINCT %s FROM %s %s %s %s;" % (", ".join(
select), self.tablename, whereclause, orderbyclause, limit)
self.log.debug("query: %s" % (query))
return self.selectQuery(query)
def getSoftwareDublicates(self, clustername, _select="*", _limitup=0, _limitdown=0, _where=None, _orderby=None):
if _where==None:
_where=list()
# Quickhack to support mysql3
if type(_where)==list:
for i in range(len(_where)):
_where[i]="t1."+_where[i]
limit=BaseDB.getLimit(_limitup, _limitdown)
self.log.debug("where: %s" %(_where))
_where.append("t1.clustername=\"%s\"" %(clustername))
# _where+=" AND ".join(_where)
whereclause=BaseDB.resolveWhere(_where)
orderbyclause=BaseDB.resolveOrderBy(_orderby)
self.log.debug("whereclause: %s" %(whereclause))
query="""SELECT DISTINCT %s FROM %s AS t1
LEFT JOIN %s AS t2
USING (clustername, name)
%s
AND (t1.version != t2.version OR t1.subversion != t2.subversion)
%s %s;""" \
%("t1."+", t1.".join(_select), self.tablename, self.tablename, whereclause, orderbyclause, limit)
self.log.debug("query: %s" %(query))
return self.selectQuery(query)
def getDiffsFromSources(self, sourcenames, colnames=None, limitup=0, limitdown=0, where=None, orderby=None, Diffs=True, NotInstalled=True, Installed=False):
"""
Returns a resultset of differences of the given sourcenames.
Parameter are the sourcesnames to compare
"""
if not sourcenames or type(sourcenames)!=list or len(sourcenames)<=1:
return None
orderbyclause=BaseDB.resolveOrderBy(orderby)
limit=BaseDB.getLimit(limitup, limitdown)
self.log.debug("where: %s" %(where))
self.log.debug("orderbyclause: %s, limit: %s, diffs: %s, notinstalled: %s" %(orderbyclause, limit, Diffs, NotInstalled))
self.log.debug("getting colnames")
if not colnames:
colnames=self.getColnamesForDiff(sourcenames)
j=0
# ComLog.getLogger().debug("query %s" % query)
queries=list()
installed=None
if Installed:
if Diffs or NotInstalled:
installed=0
queries.append(self.selectQueryInstalled(sourcenames, colnames, SoftwareCMDB.COMPARE_2_SOFTWARE, where, installed))
if Diffs:
if Installed:
installed=1
queries.append(self.selectQueryOnlyDiffs(sourcenames, colnames, SoftwareCMDB.COMPARE_2_SOFTWARE, where, installed))
if NotInstalled:
queries+=self.selectQueriesNotInstalled(sourcenames, colnames, SoftwareCMDB.COMPARE_2_SOFTWARE, where, Installed)
union="\n UNION \n".join(queries)
if orderbyclause and orderbyclause!="":
union+="\n"+orderbyclause
if limit and limit != "":
union+="\n"+limit
self.log.debug("union: "+union)
return self.selectQuery(union)
def selectQueryOnlyDiffs(self,
sourcenames,
allcolnamesr,
colnames=COMPARE_2_SOFTWARE,
where=None,
withInstalled=None,
equals=["name", "architecture"],
unequals=["version", "subversion"]):
"""
Returns the select query that only filters differences between installed Software.
See selectNotInstalledQuery.
"""
if not unequals:
unequals = list()
j = 0
tablealias = "odrpms"
version_unequalcols = list()
subversion_unequalcols = list()
joins = list()
columns = list()
dbs = list()
wherelst = list()
dbs2 = list()
wherelst2 = list()
notexists = list()
joins2 = list()
equals2 = list(equals)
equals2 += unequals
unequalsmap = dict()
count_clusternames = list()
for unequal in unequals:
unequalsmap[unequal] = list()
for i in range(len(sourcenames)):
formatedname = self.formatToSQLCompat(sourcenames[i])
if j == 0:
columns.append(tablealias+str(i)+"."+colnames[1]+" AS \""+allcolnamesr[j+1]+\
"\", MAX("+tablealias+str(i)+"."+colnames[2]+") AS \""+allcolnamesr[j+2]+\
"\", MAX("+tablealias+str(i)+"."+colnames[3]+") AS \""+allcolnamesr[j+3]+"\"")
elif j + 3 < len(allcolnamesr):
columns.append(tablealias+str(i)+"."+colnames[1]+" AS \""+allcolnamesr[j+1]+"\", "+tablealias+str(i)+"."+colnames[2]+" AS \""+\
allcolnamesr[j+2]+"\", "+tablealias+str(i)+"."+colnames[3]+" AS \""+allcolnamesr[j+3]+"\"")
_db = tablealias + str(i)
dbs.append(self.tablename + " AS " + _db)
if i > 0:
joins.append(" INNER JOIN " + dbs[i] + " USING (%s) " %
(", ".join(equals)))
wherelst.append(tablealias + str(i) + ".clustername=\"" +
sourcenames[i] + "\"")
for unequal in unequals:
unequalsmap[unequal].append(tablealias + str(i) + "." +
unequal)
_db1 = tablealias + str(len(sourcenames) + i)
dbs2.append(self.tablename + " AS " + _db1)
if i > 0:
joins2.append(" LEFT JOIN " + dbs2[i] + " USING (%s) " %
(", ".join(equals2)))
wherelst2.append("\n " + _db1 + ".clustername=\"" +
sourcenames[i] + "\"")
for col in unequals:
wherelst2.append("\n " + _db1 + "." + col + "=" + _db +
"." + col)
_db2 = tablealias + str(len(sourcenames) * 2 + i)
_notexists="\n SELECT * FROM "+self.tablename+" AS "+_db2+\
" \n WHERE \n "+_db2+".clustername="+_db+".clustername"
# name and architecutre need to be equal
for col in equals:
_notexists += " AND \n " + _db2 + "." + col + "=" + _db + "." + col
_ors = list()
for k in range(len(sourcenames)):
if k == i:
continue
_ands = list()
for col in unequals:
_ands.append("%s.%s=%s.%s" %
(_db2, col, tablealias + str(k), col))
_ors.append("(" + " AND ".join(_ands) + ")")
if len(_ors) == 1:
_notexists += " AND \n %s " % (_ors[0])
else:
_notexists += " AND \n (%s) " % (
" OR \n ".join(_ors))
notexists.append("NOT EXISTS (%s)" % (_notexists))
count_clusternames.append("%s.clustername=" + _db + ".clustername")
j += 3
# If special names are filter that where clause is generated here
whererest = ""
if where and type(where) == str and where != "":
whererest = "\n AND " + tablealias + "0." + where
elif where and type(where) == list:
_tmpname = "\n AND " + tablealias + "0."
whererest = _tmpname + _tmpname.join(where)
unequalstr = ""
for unequal in unequals:
unequalstr += " OR %s" % (" OR ".join(
BaseDB.BinOperatorFromList(unequalsmap[unequal], "!=")))
if unequalstr != "":
unequalstr = " AND (%s)\n" % (unequalstr[4:])
self.log.debug("selectQueryOnlyDiffs %s" % withInstalled)
if withInstalled != None:
columns.append("%u AS %s" %
(withInstalled, SoftwareCMDB.DIFFS_COLNAME))
notexistsquery="NOT EXISTS ("+"\n SELECT * FROM "+dbs2[0]+"\n"+\
"\n ".join(joins2)+\
"\n WHERE "+" AND ".join(wherelst2)+")"
_countname = tablealias + str(len(joins) + len(notexists) + 2)
for k in range(len(count_clusternames)):
count_clusternames[k] = count_clusternames[k] % (_countname)
countquery="SELECT COUNT("+_countname+".name) FROM "+self.tablename+" AS "+_countname+\
"\n WHERE"+\
"\n ("+" OR ".join(count_clusternames)+")"+\
"\n AND "+_countname+".name="+tablealias+"0.name AND "+_countname+".architecture="+tablealias+"0.architecture GROUP BY "+_countname+".name"
if len(notexists) <= 2:
_notexist_op = "\n AND "
else:
_notexist_op = "\n OR "
query="SELECT DISTINCT "+tablealias+"0."+colnames[0]+" AS \""+allcolnamesr[0]+"\", "+','.join(columns)+"\n FROM "+dbs[0]+"\n"+\
"\n ".join(joins)+\
"\n WHERE "+" AND ".join(wherelst)+"\n"+\
unequalstr+" AND "+notexistsquery+"\n AND ("+\
_notexist_op.join(notexists)+")"\
"\n AND ("+countquery+") % "+str(len(sourcenames))+ "=0"\
" "+whererest+"\n GROUP BY "+tablealias+"0.version, "+tablealias+"0.subversion" # +" ORDER BY "+tablealias+"0.name"
return query