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