def findStringOrderbyAttributes(queryobj):
orderbyAttr = ""
if(queryobj is not None):
orderbyIdent = queryobj.getOrderbyIdent()
if (orderbyIdent is not None):
for oid in orderbyIdent:
# print oid
if (myhelper.isAggregate(oid)):
# print"agg"
orderbyAttr+= str(oid)
elif (myhelper.isOrderbyOperator(oid)):
# print "ops"
orderbyAttr = orderbyAttr.strip(", ")
orderbyAttr+= " " + myhelper.remAggregate(str(oid)) + ", "
elif(myhelper.isMathOperator(str(oid))):
# print "myops %s" %oid
orderbyAttr+=str(oid)
else:
# print "else"
orderbyAttr+= str(oid) + ", "
orderbyAttr = orderbyAttr.strip("_")
orderbyAttr = orderbyAttr.strip(", ")
# print "orderbyAttr: %s" %orderbyAttr
return orderbyAttr
def constructSubSelects (queryobj, groupobj, orderobj, selectAttr, distinctValues):
if (queryobj and selectAttr and distinctValues):
#Temporary data structures required
queryList = {}
selectList = {}
# selectIntoList = {}
whereList = {}
addBigWhere = ""
insertClause = " INSERT INTO finalOutputTable (" + selectAttr + ")"
selectAttrList = selectAttr.split(",")#contains list of attributes in the select clause of sub-queries
containsAggregate = queryobj.getSelectContainsAggregate() or orderobj.getOrderbyContainsAggregate()
orgWhere = queryobj.getWhereIdent()
# if (orgWhere is None): # the original query has no where clause
# orgWhere = ""
numAttr = len(selectAttrList)
numRows = len(distinctValues)
fromClause = " FROM " + myqueryconstructor.findStringFromAttributes(queryobj)
#To be used when order by has an aggregate in it
groupbyClause = " "
for attr in selectAttrList:
if("(" in attr):
addBigWhere += myhelper.remAggregate(attr) + " IS NOT NULL AND "
insertClause += myhelper.remAggregate(attr)
else:
groupbyClause += str(attr) + ", "
insertClause += str(attr) + ", "
insertClause = insertClause.rstrip(", ")
insertClause += ")"
addBigWhere = addBigWhere.rstrip(" AND ")
addBigWhere = " WHERE " + addBigWhere
#if the user entered an orderby attribute with an aggregate then you have a group by
if (orderobj.getOrderbyContainsAggregate()):
groupbyClause = " GROUP BY " + groupbyClause
groupbyClause = groupbyClause.rstrip(", ")
else:
groupbyClause = " "
iterations = 0
for tuple in distinctValues:
selectList[iterations] = " SELECT "
if(orgWhere is None):
whereList[iterations] = " WHERE "
else:
whereList [iterations] = " " + str(orgWhere) + " AND "
num = 0
while (num < numAttr):
attrName = str(selectAttrList[num])
if ("(" not in attrName):
attrValue = str(tuple[num])
if (containsAggregate):
if ("(" not in attrName):
selectList[iterations] += "'" + attrValue + "'::Text AS " + myhelper.remAggregate(attrName) + ", "
whereList[iterations] += attrName + " = '" + attrValue + "' AND "
else:
selectList[iterations] += attrName + " AS " + myhelper.remAggregate(attrName) + ", "
else:#select contains no aggregate
selectList[iterations] += attrName + ", "
if("(" not in attrName):
whereList[iterations] += attrName + " = '" + attrValue + "' AND "
num += 1
selectList[iterations] = selectList[iterations].rstrip(", ")
whereList[iterations] = whereList[iterations].rstrip(" AND ")
queryList[iterations] = selectList[iterations] + fromClause + whereList[iterations] + groupbyClause
print "Executing Sub-query: %s" % str(iterations + 1)
db.make_pquery(queryList[iterations])
# print queryList[iterations]
iterations += 1
# print "Done with typles"
# return (queryList, numRows, addBigWhere)
print "Error in creating subqueries"
return None
