CTE + WITH RECURSIVE

[valq7711]

As proposal, here is my implementation of CTE + WITH RECURSIVE
(now even sqlite can do this)

Usage example

from pydal.objects import Expression
from whatever_see_below import CTE, XQuery

#just useful shortcut
exp = lambda e, as_, type = 'string': Expression(db, e, type = type).with_alias(as_)

works_for = db(org.name == 'Alica').for_CTE(
    org.id,
    org.name.with_alias('top_boss'),  # i.e. Alica is top_boss
    org.name,
    exp("1", as_ = 'xdepth', type = 'integer')
).with_alias('works_for')

# turn into CTE and add recursive term by .union() or .union_all() 
works_for = CTE(works_for).union(
    db(org.boss == works_for.id).for_CTE(
        org.id
        works_for.top_boss,
        org.name,  
        (works_for.xdepth + 1).with_alias('xdepth')  # how many bosses are up to Alice?
    )
)

# query with CTE should be in format
# XQuery(foo_cte, bar_cte, ..., baz_cte, regular_query).select(...)  - or ._select(...)     
XQuery( works_for, (works_for.name == 'John') & (works_for.xdepth < 4)).select(works_for.ALL)    

Implementation

# ---------------  monkey patch ------------------------
# this small monkey patch so anyone can try
# - could be placed in the db.py
def monkey_patch():
    '''
    The goal of the patch is to inject `for_CTE` method into pyDAL Set-class
    instance of which is returned by db(...)
    `for_CTE()` returns the same as `nested_select()`
    except that for_CTE().with_alias('foo').query_name(...)
    returns just 'foo' instead of '(SELECT ...) AS foo'
    '''
    def for_CTE(self, *a, **kw):
        # get nested_select
        sel = self.nested_select(*a, **kw)
        # get `with_alias` method itself
        _wa = sel.with_alias
        # may be already patched, if so - do nothing
        if not hasattr(_wa, '_patched'):
            # define patched `with_alias`
            def with_alias(s,  *a, **kw):
                # call original `with_alias` to get clone
                other = _wa(*a, **kw)
                # replace query_name in the clone
                other.query_name = lambda *a, **kw: (other.sql_shortref,)
                return other
            with_alias._patched = True
            # replace `with_alias` in `nested_select`-result
            setattr(sel,'with_alias', with_alias.__get__(sel, sel.__class__))
        return sel
    # inject `for_CTE` method into Set-class
    setattr(pydal.objects.Set, 'for_CTE' , for_CTE)

# don't forget to call it
monkey_patch()


# --------------- the core: CTE and XQuery  ------------------------
 class CTE(object):
    def __init__(self, select, recursive = None):
        '''
        select - must be db(...).for_CTE(...).with_alias(...)
        recursive - may be .nested_select() or .for_CTE(...)
        '''
        self.select = select
        self.recursive = recursive
        self.union_type = 'UNION ALL'

    def union(self, recursive):
        self.union_type = 'UNION'
        self.recursive = recursive
        return self

    def union_all(self, recursive):
        self.union_type = 'UNION ALL'
        self.recursive = recursive
        return self

    def __call__(self):
        sql = '{t}({fields}) AS ({select}) '
        recursive = ''
        if self.recursive:
            # we cant use just str(self.recursive), because it is in format '(SELECT ...) AS alias'
            # but there is _sql_cache that returns non-aliased nested_select definition!
            # force generate _sql_cache
            str(self.recursive)
            recursive = self.recursive._sql_cache[:-1] # [:-1] - need to remove ';' at the end
            sql = sql.replace('{select}', '{select} {union_type} {recursive}')
        str(self.select)
        return sql.format(
            t = self.select._tablename, # alias in fact
            fields = ','.join(self.select.fields),
            select = self.select._sql_cache[:-1],
            union_type = self.union_type,
            recursive = recursive,
        )
    def __getattribute__(self, k):
        sel = object.__getattribute__(self, 'select')
        if k == 'ALL':
            return sel.ALL
        elif k in sel.fields:
            return sel[k]
        return object.__getattribute__(self, k)

    @property
    def fields(self):
        return self.select.fields

    def __iter__(self):
        return self.select

class XQuery(object):
    def __init__(self, *args):
        self.ctes = []
        if len(args)>1:
            self.ctes = args[:-1]
        self.q = args[-1]
        self.db = self.q._db

    def _select(self, *fields, **kw):
        db = self.db
        sql = self.ctes and 'WITH RECURSIVE {ctes} {select}' or '{select}'
        return sql.format(
            ctes = ','.join([c() for c in self.ctes]),
            select = db(self.q)._select(*fields, **kw)[:-1]
        )

    def select(self,  *fields, **kw):
        db = self.db
        sql = self._select(*fields, **kw)
        colnames = []
        _fields = []
        for f in fields:
            if isinstance(f, SQLALL):
                for tf in f._table:
                    _fields.append(tf)
                    colnames.append(str(tf))
                continue

            _fields.append(f)
            c = str(f)
            if not isinstance(f, Field):
                as_alias = re.match('^.+? AS (\w+[\w0-9]*)$', c)
                if as_alias:
                    c = as_alias.group(1)
            colnames.append(c)
        return db.executesql(sql, fields = _fields, colnames = colnames)

[valq7711]

Implemented