Test your AI Agents with the all-new Agent to Agent Testing Platform.Learn More
How to use getTables method in fMBT
Best Python code snippet using fMBT_python
reroute.py
Source:reroute.py 
...4from itertools import product5def addParents(self, reset=False, debug=False):6 if reset:7 resetParents(self)8 for table in self.getTables():9 table.parents += L(self)10 addParents(table, debug=debug)11def resetParents(self):12 self.parents = L()13 for table in self.getTables():14 resetParents(table)15def ancestors(self):16 return copy(self.parents) + self.parents.bind(ancestors)17def toReroute(self):18 # if one of its parents is a child of another parent19 # if len(self.parents) <= 1:20 # return False21 for parent1, parent2 in product(self.parents, self.parents):22 parent1 in ancestors(parent2) or parent2 in ancestors(parent1)23 if parent1 in ancestors(parent2) or parent2 in ancestors(parent1):24 return True25 return False26def getRerouteTables(self):27 addParents(self, reset=True)28 return self.getTables().filter(toReroute)29def hasEqs(table, inner, outer):30 # (redundant) if the inner table is entirely replaceable 31 # ie if every baseexpr of the table to be moved out appears in an eqexpr32 # TODO: if one side of the equals is a funcexpr, then the func must be injective33 eqexprs = inner.joincond.children\34 .filter(lambda x: isinstance(x, EqExpr) 35 and x.getTables().len() > 136 and (x.children[0].table == table or37 x.children[1].table == table)38 )39 haseqs = inner.allExprs().bind(lambda x: L(x) + x.descendants())\40 .filter(lambda x: type(x) is BaseExpr)\41 .filter(lambda x: x.table == table) <= AndExpr(eqexprs).descendants()42 return haseqs43@baseFunc44def replaceTable(expr, basetable, parenttable, outertable=None):45 """46 (redundant)47 for all baseexprs in "parenttable", if its sourcetable is "basetable"48 then replace it with something that it's equal to, and return the replacement49 outertable is there to add a joincond at the end50 """51 if expr.table == basetable:52 res = expr.getEqs(parenttable.joincond)[0] # guaranteed to return something53 54 if outertable:55 # make a joincond on the outside56 dummylabel = 'out_move_' + parenttable.columns.getKey(res)57 58 # TODO: only do this if outertable doesn't already have an equivalent condition59 # existing = expr.getEq(AndExpr(outertable.joincond.getJoins().filter(lambda x: parenttable in x.getTables())))60 # if not (existing and EqExpr(existing.getRef(), expr) in parenttable.joincond.children):61 parenttable.columns[dummylabel] = res62 outertable.joincond.children += EqExpr(BaseExpr(dummylabel, parenttable), expr)63 return res64 return expr65# %% _______ ________ _______ ______ __ __ ________ ______ __ __ ______66# %% / \ / |/ \ / \ / | / |/ |/ |/ \ / | / \67# %% $$$$$$$ |$$$$$$$$/ $$$$$$$ |/$$$$$$ |$$ | $$ |$$$$$$$$/ $$$$$$/ $$ \ $$ |/$$$$$$ |68# %% $$ |__$$ |$$ |__ $$ |__$$ |$$ | $$ |$$ | $$ | $$ | $$ | $$$ \$$ |$$ | _$$/69# %% $$ $$< $$ | $$ $$< $$ | $$ |$$ | $$ | $$ | $$ | $$$$ $$ |$$ |/ |70# %% $$$$$$$ |$$$$$/ $$$$$$$ |$$ | $$ |$$ | $$ | $$ | $$ | $$ $$ $$ |$$ |$$$$ |71# %% $$ | $$ |$$ |_____ $$ | $$ |$$ \__$$ |$$ \__$$ | $$ | _$$ |_ $$ |$$$$ |$$ \__$$ |72# %% $$ | $$ |$$ |$$ | $$ |$$ $$/ $$ $$/ $$ | / $$ |$$ | $$$ |$$ $$/73# %% $$/ $$/ $$$$$$$$/ $$/ $$/ $$$$$$/ $$$$$$/ $$/ $$$$$$/ $$/ $$/ $$$$$$/74# %% ^âââââââââââââââ MOVING IN ââââââââââââââââââ^75def canMoveIn(expr, inner, outer):76 77 if inner.groupbys.getTables().filter(lambda x: x.leftjoin):78 # return expr in inner.joincond.children79 return expr.getTables() <= inner.baseTables()80 81 if inner.iswindow():82 return expr in inner.joincond.children83 84 if expr in inner.joincond.children:85 return True86 87 # if it's only part of a single subquery88 willmove = outer.getTables().filter(lambda x: x.isQuery() and expr.getTables() ^ x.getTables()).len() <= 189 90 # the tables of expr are a subset of inner's tables, 91 # plus everything that's joined by primary key to one of inner's tables92 canmove = expr.getTables() <= outer.joincond.children \93 .filter(lambda x: x.isJoin()94 and ((x.children[0].isPrimary() and 95 x.children[1].table in inner.getTables())96 or97 (x.children[1].isPrimary() and 98 x.children[0].table in inner.getTables())99 )100 ).getTables() + inner.getTables()101 102 res = canmove and willmove and not expr.isagg() and not expr.isExist()103 #and not (L(expr) + expr.descendants()).fmap(lambda x: x.exists()).any()104 return res105def moveIn(expr, inner, outer):106 inner.joincond.children += expr#.getRef(oldtables=L(inner))107 outer.joincond.children -= expr108# def moveIn(table, inner, outer):109# # aggregate exprs can get moved in, but the groupbys must move as well 110# # outer.joincond.children.filter(lambda x: table in x.getTables()).modify(lambda x: x.getRef())111 112# # DON'T MOVE IN ANYTHIN THAT'S CONNECTED TO A SUBQUERY, 113# # UNLESS you're already groupby by the primary key of whatever 114 115# # joinconds just get moved wholesale: this is allowed because of the canmovein condition116 117# # if not table.isTable():118 119# res = outer.joincond.children\120# .filter(lambda x: table in x.getTables())\121# .fmap(lambda x: (x, not (x.getRef().getTables() - inner.groupbys.getTables() - inner.columns.getTables()) 122# ))123# for expr in outer.joincond.children\124# .filter(lambda x: table in x.getTables())\125# .filter(lambda x: not (x.getRef().getTables() - inner.groupbys.getTables() - inner.columns.getTables()) 126# ):127# # .filter(lambda x: not x.getTables().filter(lambda y: not y.isTable()).exists())128# # .filter(lambda x: x.getTables() <= inner.getTables()):129 130 131# inner.joincond.children += expr.getRef()132# outer.joincond.children -= expr133def moveInHaving(inner, outer, debug=False):134 # move in the HAVING conditions, this facilitates a cleanup later on135 # move it in if the inner query is the only one of its tables.136 137 for outexpr in outer.joincond.children.filter(lambda x: x.getTables() <= (inner.getTables() + L(inner))138 and not x.isagg() # and x.getTables()[0] == inner 139 and x.getRef().isagg()140 ):141 142 havingexpr = outexpr.getRef(oldtables=L(inner))143 # TODO: the descendants of havingexpr are already grouped by144 # havingexpr.descendants.getTables() <= inner.groupbys145 146 if debug:147 print("MOVED_IN():", havingexpr)148 print(outer.sql(reduce=False, debug=True))149 150 inner.joincond.children += havingexpr151 outer.joincond.children -= outexpr152 153 # get rid of references that were only there to provide for the aggregate joincond (broken)154 # if delete:155 # for key, inexpr in inner.columns.items():156 # # if the expr doesn't get refered to by the outer table157 # if inexpr in havingexpr.descendants() and not inner.parents.bind(lambda x: x.allDescendants()).filter(lambda x: x.getRef() == inexpr).exists():158 # del inner.columns[key]159def moveInAll(inner, outer, debug=False):160 """161 the loop is to account for the case when a table isn't joined by primary key, 162 but it's joined to something that's joined by primary key 163 """164 finish = False165 moved = False166 # i=0167 168 while not finish:169 # print(i)170 finish = True171 for expr in outer.joincond.children:172 if canMoveIn(expr, inner, outer):173 finish = False174 moveIn(expr, inner, outer)175 if debug:176 print("MOVED_IN():", expr)177 moved = True178 179 if debug and moved:180 print(outer.sql(reduce=False, debug=True))181# %% ^ââââââââââââ MOVING OUT âââââââââââââââââââââââââââââââ^182def canMoveOut(table, inner, outer):183 184 if inner.groupbys.getTables().filter(lambda x: x.leftjoin):185 return table in outer.baseTables() and table not in inner.baseTables()186 187 # isisolated = inner.joincond._filter(table, getRerouteTables(outer)).getTables() <= L(table)188 189 # we ALWAYS want it IN unless the table appears in more than one subquery, in this case it's like we're "factorizing" it190 # shouldmove = table.parents.filter(lambda x: outer in x.parents).len() > 1191 192 # NEW: just move if it's either an outer groupby or joined to an outer groupby, and the same is not true for inner193 willmove = table in outer.groupbys.getTables() + outer.getTables().filter(lambda x: x.isJoined(outer.joincond.children))194 isgroupedinner = table in inner.groupbys.getTables() #+ inner.getTables().filter(lambda x: x.isJoined(inner.joincond.children))195 196 # can't move out anything that appears in an agg expr197 canmove = not inner.allExprs().filter(lambda x: x.isagg()).getTables()\198 <= inner.allExprs().filter(lambda x: table in x.getTables()).getTables()199 200 return willmove and not inner.leftjoin and (not isgroupedinner) and canmove # and isisolated 201def moveOut(table, inner, outer):202 203 for expr in inner.joincond.children.filter(lambda x: table in x.getTables()):204 outer.joincond.children += expr#.getRef(oldtables=L(inner))205 inner.joincond.children -= expr206 207 # # mutates HARD208 # inner.groupbys.modify(lambda x: replaceTable(x, table, inner))209 # inner.columns.modify(lambda x: replaceTable(x, table, inner))210 # inner.joincond.modify(lambda x: replaceTable(x, table, inner, outer))211 # # get rid of tautological exprs of the form x = x212 # inner.joincond.children = inner.joincond.children.filter(lambda x: not x.isRedundant())213 214def moveOutAll(inner, outer, debug=False):215 moveout = True216 while moveout:217 moveout = False218 for table in getRerouteTables(outer).filter(lambda x: x in inner.getTables()):219 if canMoveOut(table, inner=inner, outer=outer):220 moveOut(table, inner=inner, outer=outer)221 if debug:222 print(' # %% ^ââââââââââââââââââââââââââââââââââââââââââââââââââ^ ')223 print("MOVEOUT", table)224 print(outer.sql(reduce=False, debug=True))225 print(' # %% ^ââââââââââââââââââââââââââââââââââââââââââââââââââ^ ') 226 moveout = True227 break228# %% ^âââââââââââââââââ REROUTING ââââââââââââââââââââ^229def canReroute(expr, basetable, target, outer=None):230 # the target is the inner query231 # if the tables of expr are a subset of the tables of the target... and not aggregate and not constant232 # and we don't reroute exists exprs233 isexists = expr.isExist() or (type(expr) is FuncExpr and expr.func.__name__ in ['notnull_','isnull_'])234 235 return basetable in expr.getTables() and expr.getTables() <= target.getTables() \236 and type(expr) is not ConstExpr and (not expr.isagg()) \237 and not isexists and not expr.isBool() #and type(expr) is BaseExpr238def reroute(expr, basetable, target, outer=None):239 """240 target is the "intermediate" table241 for an expr, if it can be rerouted, then make it as a select of that target, 242 and return another expr that references it243 """244 if canReroute(expr, basetable, target):245 # print('REROUTED():', expr)246 dummylabel = target.columns.getKey(expr)247 if 'reroute_' not in dummylabel and dummylabel not in target.columns:248 dummylabel = 'reroute_' + dummylabel249 target.columns[dummylabel] = expr250 if not isGrouped(expr, target) and type(expr) is BaseExpr:251 # DON'T KNOW252 # add it as a groupby if it's not already253 target.groupbys += expr.table.primaryExpr()254 return BaseExpr(dummylabel, target)255 elif not expr.getTables() ^ target.getTables():256 return expr257 return expr.fmap(lambda x: reroute(x, basetable, target))258def rerouteAll(table, inner, outer):259 outer.joincond.modify(lambda x: reroute(x, basetable=table, target=inner))260 outer.columns.modify(lambda x: reroute(x, basetable=table, target=inner))261 outer.groupbys.modify(lambda x: reroute(x, basetable=table, target=inner))262def canRerouteTable(basetable, inner, outer):263 264 # all baseexprs of outer containing basetable265 allexprs = outer.allDescendants()\266 .filter(lambda x: type(x) is BaseExpr and basetable in x.getTables())267 268 isgrouped = allexprs.fmap(lambda x: isGrouped(x, inner)).all()269 270 # if not isgrouped:271 # print('not grouped:', basetable)272 273 # if the second condition is true then we just add basetable to the groupbys of inner274 return isgrouped or basetable in outer.groupbys.filter(lambda x: x.isPrimary()).getTables()275def isGrouped(expr, target):276 '''277 if there exists a unique value of expr for each row in target278 an expr is grouped if any of the following are true:279 1. expr is one of the target's groupbys280 2. the tables of expr are part of the target's primary grouped by tables281 2. expr is in the getEqs of the target's groupbys282 3. expr is joined by primary key to something thats's grouped283 4. expr is in the getEqs of something that's grouped284 '''285 286 # if expr is grouped by, or is joined to anything of the primary keys, 287 # everything equal to a groupby expr288 allgroups = getGroups(target)289 return expr in allgroups or expr.getTables() <= allgroups.filter(lambda x: x.isPrimary()).getTables() \290 or (not expr.isagg() and expr.getRef().isagg())291def getAllEqsRecursive(expr, jclist):292 """293 get everything in jclist that's equal to expr294 """295 jclistnew = L(expr)296 jclistold = None297 while jclistnew != jclistold:298 jclistold = copy(jclistnew)299 jclistnew += jclistnew.bind(lambda x: getAllEqs(x, jclist))300 return jclistnew301def getAllEqs(expr, jclist):302 """303 get everything in jclist that's equal to expr (1st order)304 differs from Expr.getEqs in that you're allowed something 305 like self.hide_ts == self.ts which has the same tables on both sides306 """307 res = L()308 for eqexpr in jclist.filter(lambda x: isinstance(x, EqExpr)):309 if expr == eqexpr.children[0] or eqexpr.children[0] in res:310 res += eqexpr.children[1]311 elif expr == eqexpr.children[1] or eqexpr.children[1] in res:312 res += eqexpr.children[0]313 return res314def getGroups(target):315 '''316 everything in target.joincond that has a unique value for each value of the groupbys317 a table is a primary table of target if target is grouped by the primary key of the table318 1. groupbys themselves319 2. anything equal to a groupby320 3. anything whose tables are a subset of the primary tables of target321 4. anything equal to this... etc322 '''323 324 res = target.groupbys325 resold = None326 327 while res != resold:328 resold = copy(res)329 res += res.bind(lambda x: getAllEqsRecursive(x, target.joincond.children))330 for eqexpr in getEqExprs(target):331 if eqexpr.children[0].getTables() <= res.filter(lambda x: x.isPrimary()).getTables():332 res += eqexpr.children[1]333 elif eqexpr.children[1].getTables() <= res.filter(lambda x: x.isPrimary()).getTables():334 res += eqexpr.children[0]335 return res336def getEqExprs(table):337 return table.joincond.children.filter(lambda x: type(x) is EqExpr)338# def getGroupJoins(target, groupbys):339# # everything joincond of target that's joined to a specified set of exprs340# return target.joincond.children.filter(lambda x: isinstance(x, EqExpr) 341# and x.children[0] in groupbys or x.children[0] in groupbys) \342# .fmap(lambda x: x.children[1] if x.children[0] in groupbys else x.children[0])343# def getGroups(target):344 345# # everything joined to a groupby of the target, or joined to one of those things, etc346# # or, if the groupby is a primary key of a table, everything joined to an expr of that table347 348# pkey_joins = target.joincond.children \349# .filter(lambda x: isinstance(x, EqExpr)) \350# .filter(lambda x: isGrouped(x.children[0], target) or isGrouped(x.children[1], target))351# other_groupbys = pkey_joins.fmap(lambda x: x.children[1] if x.children[0] in target.groupbys else x.children[0])352# return other_groupbys + target.groupbys353# %% ^âââââââââââââââââ OTHER âââââââââââââââ^354def isInvalid(expr, isjc=False):355 # an expr that compares a table to an aggregate of that table356 # ie an expr whose children contain the table, and an expr whose getref is an aggexpr containing the same table357 # if it's a joincond and it contains a left joined table then all bets are off358 btables = expr.getTables().filter(lambda x: x.isTable())359 innertables = expr.baseExprs()\360 .filter(lambda x: not x.table.isTable())\361 .fmap(lambda x: x.getRef())\362 .bind(lambda x: L(x) + x.descendants())\363 .filter(lambda x: isinstance(x, AggExpr)).getTables() 364 365 return expr.getRef().isagg() and (btables ^ innertables or (btables.filter(lambda x: x.leftjoin) and isjc))366def aggedTables(expr):367 return expr.children\368 .bind(lambda x: L(x) + x.descendants())\369 .filter(lambda x: isinstance(x, AggExpr)).getTables()370# .fmap(lambda x: x.getRef())371def hasExists(inner, outer):372 return outer.allDescendants().filter(lambda x: inner in x.getTables() and isinstance(x, FuncExistsExpr)).exists()373def canMerge(self, subquery=False):374 # it doesn't contain an aggexpr with a child whose getref is an aggexpr375 # the nonagg columns aren't aggs376 # OR it doesn't contain 377 # ie an expr that compares a table to an aggregate of that table378 canmerge = self.allDescendants()\379 .filter(lambda x: isinstance(x, AggExpr))\380 .bind(lambda x: x.baseExprs())\381 .fmap(lambda x: x.getRef())\382 .filter(lambda x: x.isagg())\383 .notExists()384 if not subquery and self.isagg():385 subq = self.columns.values().filter(lambda x: not x.isagg() and not x.isPrimary()).notExists()386 # subq = self.columns.values().filter(lambda x: not x.isagg() and x.getRef().isagg()).notExists()387 else:388 subq = True389 390 if self.subQueries().bind(lambda x: x.aggedTables()).len() > 1:391 return False392 393 return canmerge and self.joincond.children.filter(lambda x: isInvalid(x, isjc=True)).notExists() \394 and subq and (self.columns.values() + self.groupbys).filter(lambda x: isInvalid(x, isjc=False)).notExists()395def isSum(q0):396 return q0.allDescendants().filter(lambda y: type(y) is AggExpr and y.func.__name__ == 'sum_').exists()397def canMergeTable(tab0, tab1, subquery=False):398 if tab0.iswindow() or tab1.iswindow():399 return False400 # return tab1.joincond == tab0.joincond401 402 if (tab1 in tab0.getTables() and not canMerge(tab1)) or (tab0 in tab1.getTables() and not canMerge(tab0)):403 return False404 405 406 if tab1 not in tab0.getTables() and tab0 not in tab1.getTables():407 tab11 = copy(tab1)408 tab00 = copy(tab0)409 return (tab00 @ tab11).aggedTables() == tab00.aggedTables()410 411 go = (tab0.groupbys <= tab1.groupbys and tab0 in tab1.getTables() and not isSum(tab0) and not isSum(tab1)) or \412 (tab1.groupbys <= tab0.groupbys and tab1 in tab0.getTables() and not isSum(tab0) and not isSum(tab1)) or \413 ((isSum(tab0) or isSum(tab1)) and tab0.joincond == tab1.joincond and tab0.groupbys == tab1.groupbys) or \414 (tab0.groupbys == tab1.groupbys and not isSum(tab0) and not isSum(tab1))415 # or ((isSum(tab0) or isSum(tab1)) and (not tab0.isagg() or not tab1.isagg()) and tab0.groupbys == tab1.groupbys)416 # go &= (subquery or tab0.columns.values().filter(lambda x: not x.isagg() and not x.isPrimary()).notExists())417 418 return go and not tab1.joincond.children.filter(lambda x: isInvalid(x, isjc=True)) \419 and not tab0.joincond.children.filter(lambda x: isInvalid(x, isjc=True)) \420 and tab1.limitvar == tab0.limitvar and tab1.ordervar == tab0.ordervar421 # and not (not subquery and not tab0.isagg())422def mergeGrouped(self, debug=False, subquery=False):423 # merge tables that have the same groupbys, because only then does it make sense to merge them424 425 # checktables = self.subQueries()426 427 if canMerge(self, subquery=subquery):428 checktables = L(self) + self.subQueries()429 else:430 checktables = self.subQueries()431 432 # alltablegroups = L()433 434 alltablegroups = checktables.partition(lambda x, y: canMergeTable(x, y, subquery=subquery)).filter(lambda x: len(x) > 1)435 # if canMerge(self, subquery=subquery):436 # else:437 # alltablegroups = self.subQueries().partition(lambda x, y: canMergeTable(x, y, subquery=subquery)).filter(lambda x: len(x) > 1)438 # alltablegroups = L(L(self) + checktables.filter(lambda x: x.groupbys.exists() #and not hasExists(x, self)439 # and x.groupbys <= self.groupbys #.fmap(lambda x: x.getRef())440 # and not x.allDescendants().filter(lambda y: type(y) is AggExpr and y.func.__name__ == 'sum_')441 # and not x.limitvar442 # )).filter(lambda x: len(x) > 1)443 444 # if not alltablegroups:445 # alltablegroups = checktables.filter(lambda x: x.isQuery()446 # and not x.allDescendants().filter(lambda y: type(y) is AggExpr and y.func.__name__ == 'sum_')447 # )\448 # .groupby(lambda x: (x.groupbys, x.leftjoin))\449 # .filter(lambda x: len(x.value) > 1)\450 # .fmap(lambda x: x.value)451 452 453 454 for tablegroup in alltablegroups:455 456 # if self in tablegroup, then use the columns = 'left' option in addquery. 457 # then modify columns ith mergeexpr458 459 if self in tablegroup:460 merged = tablegroup.fold(lambda x, y: addQuery(x, y, addcols='left'))461 merged.modify(lambda x: mergeExpr(x, oldtables=tablegroup))462 self.__dict__.update(merged.__dict__)463 else:464 merged = tablegroup.fold(lambda x, y: x @ y)465 self.setSource(merged, oldtable=tablegroup)466 467 # if self not in tablegroup:468 469 # if self in tablegroup:470 471 # # moveInHaving(inner=table, outer=self)472 473 # # merge with a subquery and convert the agg exprs474 # merged.modify(lambda x: mergeExpr(x, oldtables=tablegroup))475 # # self.modify(lambda x: mergeExpr(x, oldtables=L(merged)))476 # # merged.columns = self.columns477 # self.__dict__.update(merged.__dict__)478 # break479 480 if alltablegroups and self.subQueries():481 mergeGrouped(self)482 483 # for subtable in self.subQueries():484 # mergeGrouped(subtable)485def mergeExpr(expr, oldtables):486 res = expr.getRef(oldtables=oldtables)487 488 489 490 # if AndExpr(expr.baseExprs().filter(lambda x: x.table.leftjoin).fmap(Expr._isnull_)).children: 491 return res492def canCleanUp(self):493 inner = self.getTables()[0]494 return len(self.getTables()) == 1 and type(inner) is not Table \495 and self.columns.values().fmap(lambda x: isinstance(x, BaseExpr)).all() \496 and not self.joincond.children # and self.groupbys.fmap(lambda x: x.getRef()) == inner.groupbys497def cleanUp(self):498 # get rid of the redundant outer query499 newquery = self.getTables()[0]500 for expr in self.joincond.children:501 newquery.joincond += expr.getRef()502 for key, expr in self.columns.items():503 newquery.columns[key] = newquery.columns[expr.fieldname]504 del newquery.columns[expr.fieldname]505 self.__dict__.update(newquery.__dict__)506def copyTable(table, parent, addcond=True, debug=False):507 # when a table can't be rerouted508 # also needed to clone join conditions into subqueries509 tablecopy = deepcopy(table)510 tablecopy.alias += '_copy'511 parent.setSource(tablecopy, oldtable=table)512 if addcond:513 # add condition to the parent that the two tables are the same514 eqexprs = tablecopy.primarynames().fmap(lambda x: EqExpr(BaseExpr(x, table), BaseExpr(x, tablecopy)))515 parent.joincond &= AndExpr(eqexprs)516 517 # if type(tablecopy) is Query:518 # eq_gpexprs = tablecopy.groupbys.zip(table.groupbys).fmap(lambda x: EqExpr(x[0], x[1]))519 # parent.joincond &= AndExpr(eq_gpexprs)520 521def connectTables(queries, parent, debug=False):522 523 commongps = queries.fmap(lambda x: x.groupbys).fold(lambda x, y: x ^ y)#.fmap(lambda x: x.getRefBase()))524 525 for i, q in enumerate(queries[1:]):526 for expr in commongps:527 q.columns.getKey(commongps)528 expr0 = reroute(expr, basetable=expr.getTables()[0], target=queries[i])529 expr1 = reroute(expr, basetable=expr.getTables()[0], target=q) 530 parent.joincond.children += EqExpr(expr0, expr1)531 532 # for i, parent in table.parents.enumerate():533 # if i == 0: continue534 # if debug: print(f'CONNECTED(): {table}')535 # tablecopy = copy(table)536 # parent.setSource(tablecopy, table)537 # for gparent in parent.parents:538 # # add a joincond539 # gparent.joincond.children += EqExpr(table.primaryExpr(), tablecopy.primaryExpr())540 # # now reroute it541 # gparent.joincond.modify(lambda x: reroute(x, basetable=table, target=table.parents[i-1]))542 # gparent.joincond.modify(lambda x: reroute(x, basetable=tablecopy, target=parent))543 # table = tablecopy544def copyJoinconds(basetable, source, target):545 res = AndExpr(source.joincond.children.filter(lambda x: x.isJoin() and basetable in x.getTables()))546 target.joincond &= res547# %% ^âââââââââââââââââââââââ REDUCE ââââââââââââââââââââââââââââââ^548def reduceQuery(self, debug=False, subquery=False):549 """550 make everything appear in scope - the dependency graph has to be a tree551 this means we have to redirect everything via its aggregate queries552 """553 554 # finish = False555 # while not finish:556 # finish = True557 558 if debug:559 print(' # %% ^âââââââââââââââââ REDUCING... ââââââââââââââââââââââââ^ ')560 print(self.sql(reduce=False, debug=True)) 561 562 # if the groupbys are left joined, then make the query left joined563 if self.isQuery() and self.groupbys and self.groupbys.getTables().fmap(lambda x: x.leftjoin).all():564 self.leftjoin = True 565 for table in self.groupbys.getTables():566 table.leftjoin = False567 for der in table.derivatives:568 der.leftjoin = False569 570 # STEP 1: MERGE SUBQUERIES WITH THE SAME GROUP BYS571 selfcopy = copy(self)572 573 if self.isagg() and not subquery:574 newgroupbys = self.columns.values().filter(lambda x: not x.isagg() and not x.isPrimary())575 self.groupbys = newgroupbys576 577 mergeGrouped(self, subquery=subquery)578 if debug:579 if str(selfcopy.__dict__) != str(self.__dict__):...
query.py
Source:query.py
...121 122 def setSource(self, newtable, oldtable=None):123 self.modify(lambda x: x.setSource(newtable, oldtable))124 125 def getTables(self):126 return self.allExprs().getTables()127 128 def isagg(self):129 return (self.columns.values() + self.joincond).any(lambda x: x.isagg())130 131 def aggedTables(self):132 return self.allDescendants().filter(lambda x: isinstance(x, AggExpr)).getTables()133 134 def iswindow(self):135 return self.columns.values().any(lambda x: x.iswindow())136 137 def isOuter(self):138 return self.subQueries().exists()139 140 def asQuery(self):141 # ALWAYS RETURN A GODDAMN COPY142 return copy(self)143 144 def subQueries(self):145 return self.getTables().filter(lambda x: not type(x) is Table)146 147 def joinM(self):148 res = copy(self)149 newjcs = res.columns.joincond150 res.columns = self.columns.clear()151 res @= self.columns.asQuery()152 153 # undo the columns added154 # for key in res.columns.keys():155 # if key not in self.columns.keys():156 # del res.columns[key]157 158 return res159 160 def dependents(self):161 # all tables joined by primary key to a table of its columns162 # res = L()163 # for table in self.getTables(): 164 # jclist = self.joincond.children.filter(lambda x: self.columns.getTables() <= x.getTables())165 # if table.isJoined(jclist) and table not in self.groupbys.getTables():166 # res += table167 # return res168 res = L()169 for k, v in self.columns.__dict__.items():170 if '_saved' in k:171 res += v.getTables()172 173 # ALSO: any table that's joined to it or its dependents, 174 # and which isn't something that is a dependent of something before it in the groupby list175 res += (self.columns.getTables()).bind(lambda x: x.derivatives)176 177 return res178 179 def baseTables(self):180 return self.groupbys.getTables().bind(lambda x: L(x) + x.derivatives)181 182 def queryTables(self):183 return (self.columns.getTables() + self.groupbys.getTables()).bind(lambda x: L(x) + x.derivatives)184 185 def ungroupby(self):186 # MUTATES187 grouptable = self.groupbys[-1].table188 189 primarygpby = False190 for gpbyexpr in reversed(self.groupbys):191 if gpbyexpr.isPrimary() and gpbyexpr.table == grouptable:192 selfgroupbys = copy(self.groupbys)193 selfgroupbys.pop()194 self.groupbys = selfgroupbys195 primarygpby = True196 elif gpbyexpr.table == grouptable and not primarygpby:197 selfgroupbys = copy(self.groupbys)198 selfgroupbys.pop()199 self.groupbys = selfgroupbys200 break201 else:202 break203 204 205 # %% ^ââââââââââââââ PUBLIC FUNCTIONS âââââââââââââââ^206 207 def aggregate(self, aggfunc=None, iswindow=False, existsq=False, lj=True, distinct=False, ungroup=True):208 # Aggregate a query into a single row209 if aggfunc is not None:210 return self.fmap(aggfunc).aggregate(ungroup=ungroup)211 res = Columns()212 newsource = copy(self)213 for key, expr in newsource.columns.items():214 dummylabel = key + '_' + memloc(expr)215 # dummylabel = 'agg_' + key #+ memloc(expr)216 res[key] = BaseExpr(dummylabel, newsource)217 del newsource.columns[key]218 newsource.columns[dummylabel] = expr219 # SUPER IMPORTANT POINT: REMOVE A GROUP BY HERE220 if ungroup: newsource.ungroupby()221 222 if len(res) == 1 and not newsource.groupbys and not res.values().filter(lambda x: type(x.getRef()) is WindowExpr):223 newsource = deepcopy(newsource)224 res = Columns({res.keys()[0]: SubQueryExpr(newsource)})225 226 return res227 228 229 def agg(self, *args, **kwargs):230 return self.aggregate(*args, **kwargs)231 232 def apply(self, *args, **kwargs):233 return self.aggregate(*args, **kwargs).asQuery()234 235 def distinct(self, *args):236 res = self.columns.deepcopy()237 source = copy(self)238 if type(res) is Columns:239 if not source.columns.values().filter(lambda x: x.isPrimary()).any():240 source.groupbys = source.columns.values()241 for key in res.keys():242 res[key] = BaseExpr(key, source)243 res.groupbys = res.values()244 else:245 for key, expr in res.items():246 res[key] = BaseExpr(expr.fieldname, source.columns.getTables()[0])#, source)247 # source.columns = BaseExpr('*', source.columns.getTables()[0]).asCols()248 source.columns = source.columns.getTables()[0].primaryExpr().asCols()249 res.groupbys = source.groupbys.fmap(lambda x: x.setSource(source, x.getTables()))250 return Query.unit(res)251 252 253 def sample(self, n=1000):254 return self.sort('RANDOM()').limit(n).distinct()255 256 257 def sort(self, *args):258 res = copy(self)259 if not hasattr(args[0], '__call__'):260 res.ordervar += L(*args)261 else:262 res.ordervar += args[0](self.columns).values() 263 return res264 265 # return lens.ordervar.modify(lambda x: x + cond(self))(self)266 267 268 def limit(self, n=50):269 # res = copy(self)270 # res.limitvar = n271 return lens.limitvar.set(n)(self)272 273 274 def fmap(self, ffunc, *args, **kwargs):275 res = copy(self)276 res.columns = ffunc(self.asCols(), *args, **kwargs)277 return res.joinM()278 # return lens.columns.modify(lambda x: ffunc(x, *args, **kwargs))(self).joinM()279 280 281 def exists(self, col=False, _func='notnull_', out='col'):282 283 alias = self.columns.__class__.__name__.lower()284 285 if out == 'col':286 # as a straight up (screws up one-to-one ness)287 return self.lj().asCols().firstCol().__getattr__(_func)().one.label('exists_' + alias).setExists()288 elif out == 'any':289 # as a bool any290 return self.lj().fmap(lambda x: x.firstCol()).any()291 elif out == 'exists':292 # as an exists expr293 return ExistsExpr(self).asCols()294 elif out == 'subquery':295 # # as a subquery (shouldn't be able to move into the subquery here)296 res = self.lj().fmap(lambda x: x.firstCol())297 res.alias = alias298 res.ungroupby()299 expr = Expr._notnull_(BaseExpr(res.columns.keys()[0], res))300 res = Columns({'exists_' + alias: expr})301 return res302 303 304 def notExists(self):305 return self.exists(_func='isnull_')306 307 def _groupby(self, exprlist=None):308 if not self.groupbys and exprlist is None:309 exprlist = L(self.columns.primary)310 if not isinstance(exprlist, list):311 exprlist = L(exprlist)312 res = copy(self)313 res.groupbys += L(*exprlist)314 return res315 # return self >> lens.groupbys.modify(lambda x: x + L(*exprlist))316 317 318 def bind(self, bfunc):319 res = copy(self)#.joinM()320 # hi = bfunc(self.columns)321 322 # res.columns = hi.asCols()323 qnew = bfunc(self.asCols())#bfunc(self.columns)324 assert not res.columns.joincond.children325 res @= qnew326 if qnew.columns.keys():327 res.columns = qnew.columns328 res.groupbys += qnew.groupbys329 return res.joinM()330 331 332 # def isDerived(self):333 # self.joincond.getJoins()334 # self.columns.getForeign()335 336 337 @classmethod338 def unit(cls, *args, filter=None, limit=None, sort=None, **kwargs):339 try:340 aa = args[0].asQuery()341 bb = args[1].asQuery()342 hh = addQuery(aa, bb) 343 except:344 pass345 346 kwargs = L(*kwargs.items()).fmap(lambda x: x[1].label(x[0]))347 res = L(*args, *kwargs).fmap(lambda x: x.asQuery()).fold(lambda x, y: x @ y)348 349 if filter is not None: res = res.filter(filter)350 if limit is not None: res = res.limit(limit)351 if sort is not None: res = res.sort(sort(res.columns).values())352 # res.joinM()353 354 return res355 356 357 def asCols(self, makecopy=True):358 res = copy(self.columns) if makecopy else self.columns359 res.joincond = self.joincond360 res.groupbys = self.groupbys361 # res.leftjoin = self.leftjoin362 return res363 # return self.columns >> lens.joincond.set(self.joincond) & lens.groupbys.set(self.groupbys)364 365 366 @property367 def one(self):368 res = self.asCols()369 resgroupbys = copy(res.groupbys)370 resgroupbys.pop()371 res.groupbys = resgroupbys372 return res373 374 375 def filter(self, cond=None):376 res = copy(self)377 378 if not cond: return res379 380 # newconds = cond(res.asCols())381 382 # we need to leave out the makecopy to save the derived383 # leaving out the makecopy mutates the columns which is why we have to joinm at the end384 newconds = cond(res.asCols(makecopy=False))385 386 # for key, derived in res.columns.getForeign(): 387 # if derived.groupbys == newconds.groupbys and derived.getTables()[0] in newconds.getTables():388 # # this mutates the derived col, be really careful389 # derived.joincond &= AndExpr(newconds.values())390 391 # newjc = AndExpr(newconds.values()).setWhere()392 # if not join: 393 # newjc = newjc.setWhere()394 395 # res @= AndExpr(newconds.values()).setWhere()396 # res @= newconds.joincond397 398 res.joincond &= AndExpr(newconds.values()).setWhere()399 res.joincond &= newconds.joincond400 401 return res.joinM()402 403 404 def lj(self):405 res = copy(self)406 407 # if res.columns.getTables() <= res.groupbys.getTables():408 # # a question: should subqueries be left joined? only if it's one of the base tables409 # res.leftjoin = True410 # return res411 412 ljtables = res.columns.fmap(lambda x: x.getRef()).getTables() + self.dependents()413 for tab in ljtables:414 tab.leftjoin = True415 416 res.modify(lambda x: x.setWhere(False) if x.getTables() ^ ljtables and not x.isExist() else x, 'joincond')417 for subq in res.subQueries():418 subq.modify(lambda x: x.setWhere(False) if x.getTables() ^ ljtables and not x.isExist() else x, 'joincond')419 420 # res.modify(lambda x: x.setWhere(False), 'joincond')421 # ifcond = res.joincond.children.filter(lambda x: x.getTables() <= res.dependents())422 423 # tricky part with dependent tables424 # if ifcond:425 426 depts = res.dependents().filter(lambda x: x in res.getTables())\427 .filter(lambda x: not self.joincond.children.filter(lambda y: y.iswhere and x in y.getTables()))428 if depts:429 notnullcond = depts.fmap(lambda x: Expr._notnull_(x.primaryExpr()))430 for key, expr in self.columns.items():431 rescolumns = copy(res.columns)432 rescolumns[key] = Expr._ifen_(expr, AndExpr(notnullcond))433 res.columns = rescolumns434 return res435 436 437 def groupby(self, *args):438 # explicit group by - add it one level back439 newgroups = L(*args).bind(lambda x: x(self.columns).values() if hasattr(x, '__call__') else x.values())440 res = copy(self)441 res.ungroupby()442 res.groupbys += newgroups + (self.groupbys - res.groupbys)443 # res.groupbys = newgroups + res.groupbys444 return res445 446 def group(self, *args):447 args = L(*args).fmap(lambda x: (lambda y: getattr(y, x)) if type(x) is str else x)448 return self.fmap(lambda x: args.fmap(lambda y: y(x)).fold(lambda y, z: y % z)).distinct()449 450# class FixedTable(Table):451# def __init__(self, q0):452# self.query = q0453# self.alias = q0.alias454# def __repr__(self):455# return repr(self.query)456# %% ^âââââââââââââââââââââ THE HEAVY LIFTER âââââââââââââââââââââââ^457def addQuery(self, other, addcols='both', debug=False):458 459 res, othercopy = copy(self), copy(other)460 461 tables0, tables1 = self.joincond.getTables(), other.joincond.getTables()462 jtables = tables0 & tables1463 464 # # start with the ones that are in common:465 # for tab1 in jtables:466 # cond0 = res.joincond._filter(tab1, jtables)467 # cond1 = other.joincond._filter(tab1, jtables)468 # if cond1 - cond0 and (cond0 | cond1).children.filter(lambda x: x.isJoin()):469 # moveJoins(cond0, cond1, tab1, tab1, res, other)470 471 # now for the ones that AREN'T in common:472 skiptable = False473 for tab1 in tables1 - tables0:474 cond1 = other.joincond._filter(tab1, jtables)475 for tab0 in tables0.filter(lambda x: x.isEq(tab1)):476 cond0 = res.joincond._filter(tab0, jtables)477 478 if identifyTable(cond0, cond1, tab0, tab1, res, other) and \479 (tab0 not in res.groupbys.getTables() 480 or (res.groupbys.fmap(lambda x: x.setSource(tab1, tab0)) == other.groupbys)):481 482 moveJoins(cond0, cond1, tab0, tab1, res, other)483 # other.setSource(tab0, tab1)484 485 skiptable = True486 jtables += L(tab0)487 break488 if not skiptable:489 res.joincond &= cond1490 jtables += L(tab1)491 492 # short circuit it493 # res.joincond &= other.joincond494 495 if addcols == 'both':496 res.columns %= other.columns497 elif addcols != 'left':498 res.columns = other.columns499 res.groupbys += other.groupbys500 return res501def identifyTable(cond0, cond1, tab0, tab1, res, other):502 # tab1 and tab0 are both joined to the same table by the same thing503 # the extras are just where conds504 505 # jtables = res.joincond.getTables() & other.joincond.getTables()506 # cond0 = res.joincond._filter(tab0, jtables)507 # cond1 = other.joincond._filter(tab1, jtables)508 509 andcond = cond0 & cond1510 511 cond1 = cond1.setSource(tab0, tab1)512 leftover0 = cond0 - cond1513 leftover1 = cond1 - cond0514 commontables = (cond0 | cond1).getTables()515 516 for expr0 in andcond.baseExprs().filter(lambda x: (x.table == tab0)):# & x.isPrimary()):517 for expr1 in andcond.baseExprs().filter(lambda x: (x.table == tab1)):# & x.isPrimary()):518 if expr0.fieldname == expr1.fieldname:519 if expr0.getEqs(andcond) ^ expr1.getEqs(andcond):520 if leftover0 and tab1.leftjoin and not tab0.leftjoin:521 return False522 if leftover1 and tab0.leftjoin and not tab1.leftjoin:523 return False524 # if leftover0.children.filter(lambda x: type(x) is FuncExistsExpr) \525 # or leftover0.children.filter(lambda x: type(x) is FuncExistsExpr):526 # return False527 528 if tab0.derivatives != tab1.derivatives:529 return False530 531 return leftover0.getTables() <= commontables and leftover1.getTables() <= commontables532 # return leftover0.getTables() <= L(tab0) and leftover1.getTables() <= L(tab0) + other.getTables()533 return False534def moveJoins(cond0, cond1, tab0, tab1, res, other):535 # MUTATES536 # given two conditions, replace the tables, or them, and add the joinconds to tab0 and tab1537 # subtract the joinconds and add remainders to case whens in the selects538 539 jtables = res.joincond.getTables() & other.joincond.getTables()540 541 if not tab1.leftjoin: tab0.leftjoin = False542 if not tab0.leftjoin: tab1.leftjoin = False543 544 other.setSource(tab0, tab1)545 # cond1.setSource(tab0, tab1)546 # res.setSource(tab0, tab1)547 cond1 = other.joincond._filter(tab0, jtables)548 leftover0 = cond0 - cond1549 leftover0.children = leftover0.children.filter(lambda x: not x.isJoin())550 leftover1 = (cond1 - cond0)551 leftover1.children = leftover1.children.filter(lambda x: not x.isJoin())552 553 554 555 @baseFunc556 def addJoins(expr, cond): 557 return Expr._ifen_(expr, cond) if (L(tab0) + tab0.derivatives) ^ expr.getTables() else expr558 559 if leftover0.children:560 res.modify(lambda x: addJoins(x, leftover0), 'columns')561 res.joincond >>= lens.children.modify(lambda x: x - leftover0.children)562 563 if leftover1.children:# <= 1:564 other.modify(lambda x: addJoins(x, leftover1), 'columns')565 other.joincond >>= lens.children.modify(lambda x: x - leftover1.children)...
users.py
Source:users.py
1from src.db import executeQuery2import src.constants3from psycopg2 import sql4def getTables():5 return {6 'usersTable': "test_users" if src.constants.testing else "users",7 'filtersTable': "test_filters" if src.constants.testing else "filters",8 'likesTable': "test_likes" if src.constants.testing else "likes",9 'dislikesTable': "test_dislikes" if src.constants.testing else "dislikes",10 'loginTable': "test_login_info" if src.constants.testing else "login_info"11 }12def createNewUser(data):13 username = data['username']14 firstname = data['firstname']15 lastname = data['lastname']16 nickname = data['nickname']17 phone = data['phone']18 email = data['email']19 bio = data['bio']20 return executeQuery(sql.SQL('INSERT INTO {} (username, firstname, lastname, nickname, phone, email, bio) VALUES (%s, %s, %s, %s, %s, %s, %s)')21 .format(sql.Identifier(getTables()['usersTable'])),22 [username, firstname, lastname, nickname, phone, email, bio], commit=True)23def updateUser(data):24 username = data['username']25 firstname = data['firstname']26 lastname = data['lastname']27 nickname = data['nickname']28 phone = data['phone']29 email = data['email']30 bio = data['bio']31 return executeQuery(sql.SQL('UPDATE {} SET firstname=%s, lastname=%s, nickname=%s, phone=%s, email=%s, bio=%s WHERE username=%s')32 .format(sql.Identifier(getTables()['usersTable'])),33 [firstname, lastname, nickname, phone, email, bio, username], commit=True)34def getNextMatchingRoomee(userId, filters):35 categoricalFilters = ""36 for key in filters:37 if filters[key].isdigit() is False and filters[key] != '':38 categoricalFilters += ' AND f.' + key + " = \'" + filters[key]+"\'"39 return executeQuery(sql.SQL('SELECT * \40 FROM {} u \41 JOIN {} AS f ON u.id=f.userId \42 WHERE \43 (f.age BETWEEN %s AND %s) AND \44 (f.graduation_year BETWEEN %s AND %s) AND \45 (f.clean BETWEEN %s AND %s) AND \46 (f.noise BETWEEN %s AND %s)' + categoricalFilters47 + ' AND u.id NOT IN ( \48 SELECT likeId \49 FROM {} \50 WHERE userId = %s \51 ) \52 AND u.id NOT IN ( \53 SELECT dislikeId \54 FROM {}\55 WHERE userId = %s \56 ) \57 AND u.id <> %s').format(sql.Identifier(getTables()['usersTable']),58 sql.Identifier(59 getTables()['filtersTable']),60 sql.Identifier(61 getTables()['likesTable']),62 sql.Identifier(getTables()['dislikesTable'])),63 [filters['min_age'], filters['max_age'],64 filters['min_graduation_year'], filters['max_graduation_year'],65 filters['min_clean'], filters['max_clean'],66 filters['min_noise'], filters['max_noise'],67 userId, userId, userId])68def getUserLikes(userId):69 likes = executeQuery(sql.SQL("SELECT {table}.id, firstname, lastname, bio \70 FROM {table} \71 JOIN {likes} ON {table}.id=likeId \72 WHERE userId=%s").format(table=sql.Identifier(getTables()['usersTable']),73 likes=sql.Identifier(getTables()['likesTable'])), [userId], fetchall=True)74 likes = [] if likes is None else likes75 return {"data": likes}76def getProfile(userId):77 return executeQuery(sql.SQL('SELECT * \78 FROM {users} \79 JOIN {filters} on id=filters.userId \80 JOIN {login} on id=login_info.userId \81 WHERE id=%s').format(users=sql.Identifier(getTables()['usersTable']),82 filters=sql.Identifier(83 getTables()['filtersTable']),84 login=sql.Identifier(getTables()['loginTable'])), [userId])85def deleteAllUsers():86 executeQuery('ALTER SEQUENCE userids RESTART WITH 1',87 [], commit=True)88 return executeQuery(sql.SQL('DELETE FROM {}')...
Automation Testing Tutorials
Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.
LambdaTest Learning Hubs:
- JUnit Tutorial
- TestNG Tutorial
- Webdriver Tutorial
- WebDriverIO Tutorial
- Protractor Tutorial
- Selenium 4 Tutorial
- Jenkins Tutorial
- NUnit Tutorial
- Jest Tutorial
- Playwright Tutorial
- Cypress Tutorial
- PyTest Tutorial
YouTube
You could also refer to video tutorials over LambdaTest YouTube channel to get step by step demonstration from industry experts.
Run fMBT automation tests on LambdaTest cloud grid
Perform automation testing on 3000+ real desktop and mobile devices online.
Try LambdaTest Now !!
Get 100 minutes of automation test minutes FREE!!
Was this article helpful?
