<commit_before>from reroute import *


def tableGen(self):
    pass


def lastSeparator(self):
    return '\n' if len(self.split('\n  ')[-1]) > 200 else ''


def sql(self, display=True, reduce=True, subquery=False):
    
    # self = deepcopy(self)
    if type(self) is Table: return str(self)
    if reduce: reduceQuery(self)
        
    # if not subquery and reduce: relabel(self)
    
    alltables = self.getTables()
    fulltables = alltables.filter(lambda x: not x.leftjoin)
    ljtables = alltables.filter(lambda x: x.leftjoin)
    
    remainingcond, addedtables, wheres, havings = copy(self.joincond), L(), L(), L()
    selects = self.columns.items().sort(lambda x: x[1].isagg())
    exprs = selects.fmap(lambda x: x[1])
    
    # ==SELECT...
    showSql = lambda p: str(p[1]) + (f' AS {p[0]}' if p[0] is not None else '')
    selecttype1 = '' if self.isagg() else 'DISTINCT '
    res = f'SELECT {selecttype1}\n  ' + selects.fmap(showSql).intersperse(', \n  ')
    
    # ==FROM...
    while len(addedtables) < len(alltables):
        for table in fulltables + ljtables - addedtables:
            
            # get the properties that only depend on one table to put in the where clause
            # if not table.leftjoin or not fulltables:
            #     wheres += remainingcond._filter(table).children
            # else:
            wheres += remainingcond._filter(table).children.filter(lambda x: (x.iswhere or not table.leftjoin) and not x.isagg())
            havings += remainingcond.children.filter(lambda x: x.isagg())
            joins = remainingcond._filter(table, addedtables).children - wheres - havings
            
            if not addedtables:
                res += f'\n{lastSeparator(res)}FROM {sql(table, reduce=reduce, subquery=True)}'
            elif joins:
                jointype = 'LEFT JOIN ' if table.leftjoin and fulltables else 'JOIN '
                joinstr = str(AndExpr(joins)).replace('AND', '\n    AND')
                res += f"\n  {jointype}{sql(table, reduce=reduce, subquery=True)} ON {joinstr}"
            else:
                # if reduce: continue
                res += f'\n  CROSS JOIN {sql(table, reduce=reduce, subquery=True)}'
            
            remainingcond.children -= joins + wheres + havings
            addedtables.append(table)
            
    # ==WHERE...
    if wheres: res += f'\n{lastSeparator(res)}WHERE ' + wheres.intersperse('\n    AND ')
    
    # ==GROUP BY...
    # if self.isagg():
        # groupbys = L(*range(1, exprs.filter(lambda x: not x.isagg()).len() + 1))
    #     if subquery or not groupbys:
    #         groupbys += (self.groupbys + havings.bind(Expr.baseExprs)).filter(lambda x: x not in exprs)
    #     res += '\n\nGROUP BY ' + groupbys.intersperse(', ')
    groupbys = self.groupbys + havings.bind(Expr.havingGroups)
    res += f'\n{lastSeparator(res)}GROUP BY ' + groupbys.intersperse(', ')
    
    # ==HAVING...
    if havings: res += f'\nHAVING ' + havings.intersperse('\n    AND ')
    
    # ==ORDER BY / LIMIT...
    if self.ordervar: res += f'\nORDER BY ' + self.ordervar.intersperse(', ')
    if self.limitvar: res += f'\nLIMIT {self.limitvar}'
    
    # ADJUSTMENTS
    if subquery:
        indent = "\n             " if self.leftjoin else "\n        "
        res = f'(--━━━━━━━━━━ SUBQUERY ━━━━━━━━━━--\n{res}\n--━━━━━━━━━━━━━━━━━━━━━━━━━━━━━--\n) AS {self.abbrev}'.replace("\n", indent)
    elif display:
        res = f'SQL() = \'\'\'\n{res}\n\'\'\''
    <commit_msg>pulled out join function from sql<commit_after>from reroute import *


def tableGen(self):
    
    alltables = self.getTables()
    fulltables = alltables.filter(lambda x: not x.leftjoin)
    ljtables = alltables.filter(lambda x: x.leftjoin)
    
    res, remainingcond, addedtables, wheres, havings = '', copy(self.joincond), L(), L(), L()
    
    while len(addedtables) < len(alltables):
        for table in fulltables + ljtables - addedtables:
            
            # get the properties that only depend on one table to put in the where clause
            # if not table.leftjoin or not fulltables:
            #     wheres += remainingcond._filter(table).children
            # else:
            wheres += remainingcond._filter(table).children.filter(lambda x: (x.iswhere or not table.leftjoin) and not x.isagg())
            havings += remainingcond.children.filter(lambda x: x.isagg())
            joins = remainingcond._filter(table, addedtables).children - wheres - havings
            
            if not addedtables:
                res += f'\n{lastSeparator(res)}FROM {sql(table, reduce=reduce, subquery=True)}'
            elif joins:
                jointype = 'LEFT JOIN ' if table.leftjoin and fulltables else 'JOIN '
                joinstr = str(AndExpr(joins)).replace('AND', '\n    AND')
                res += f"\n  {jointype}{sql(table, reduce=reduce, subquery=True)} ON {joinstr}"
            else:
                # if reduce: continue
                res += f'\n  CROSS JOIN {sql(table, reduce=reduce, subquery=True)}'
            
            remainingcond.children -= joins + wheres + havings
            addedtables.append(table)
    
    return res, wheres, havings


def lastSeparator(self):
    return '\n' if len(self.split('\n  ')[-1]) > 200 else ''


def sql(self, display=True, reduce=True, subquery=False):
    
    # self = deepcopy(self)
    if type(self) is Table: return str(self)
    if reduce: reduceQuery(self)
        
    # if not subquery and reduce: relabel(self)
    
    selects = self.columns.items().sort(lambda x: x[1].isagg())
    exprs = selects.fmap(lambda x: x[1])
    
    # ==SELECT...
    showSql = lambda p: str(p[1]) + (f' AS {p[0]}' if p[0] is not None else '')
    selecttype1 = '' if self.isagg() else 'DISTINCT '
    res = f'SELECT {selecttype1}\n  ' + selects.fmap(showSql).intersperse(', \n  ')
    
    # FROM..self
    joinstr, wheres, havings = tableGen(self)
    res += joinstr
            
    # ==WHERE...
    if wheres: res += f'\n{lastSeparator(res)}WHERE ' + wheres.intersperse('\n    AND ')
    
    # ==GROUP BY...
    # if self.isagg():
        # groupbys = L(*range(1, exprs.filter(lambda x: not x.isagg()).len() + 1))
    #     if subquery or not groupbys:
    #         groupbys += (self.groupbys + havings.bind(Expr.baseExprs)).filter(lambda x: x not in exprs)
    #     res += '\n\nGROUP BY ' + groupbys.intersperse(', ')
    groupbys = self.groupbys + havings.bind(Expr.havingGroups)
    res += f'\n{lastSeparator(res)}GROUP BY ' + groupbys.intersperse(', ')
    
    # ==HAVING...
    if havings: res += f'\nHAVING ' + havings.intersperse('\n    AND ')
    
    # ==ORDER BY / LIMIT...
    if self.ordervar: res += f'\nORDER BY ' + self.ordervar.intersperse(', ')
    if self.limitvar: res += f'\nLIMIT {self.limitvar}'
    
    # ADJUSTMENTS
    if subquery:
        indent = "\n             " if self.leftjoin else "\n        "
        res = f'(--━━━━━━━━━━ SUBQUERY ━━━━━━━━━━--\n{res}\n--━━━━━━━━━━━━━━━━━━━━━━━━━━━━━--\n) AS {self.abbrev}'.replace("\n", indent)
    elif display:
        res = f'SQL() = \'\'\'\n{res}\n\'\'\''
    
{'fraction_non_alphanumeric': 0.16037073444740907, 'fraction_numerical': 0.003651172588119646, 'mean_word_length': 2.2624828218048556, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '147748', 'n_tokens_mistral': 2403, 'n_tokens_neox': 2427, 'n_words': 557}