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.