Finding table dependencies in a selectable

[Philip Martin]

I am trying to create a function that finds the dependent tables used in a select-able statement. My plan is to use this function in conjunction with t.add_is_dependent_on inside a modified version of the recipe to create views. Hopefully, this allows sorted_tables to use these dependency relationships. The function definitely seems to work when the selectable only involves base tables, but breaks when a view uses another view. I think if I modify the function to recursively cycle through elements that are Selectables it should do the trick, but I was wondering if there is a public method like _from_objects / already a function to do something similar to this in the library?

def find_selectable_dependents(selectable):

   dependents = set()
    for part in selectable.froms:
        for obj in part._from_objects:
            if hasattr(obj, 'element'):
                table_ = obj.element
                dependents.add(table_)

    return list(dependents)

def view(name, metadata, selectable):
    t = table(name)

    for c in selectable.c:
        c._make_proxy(t)

    CreateView(name, selectable).execute_at('after-create', metadata)
    DropView(name).execute_at('before-drop', metadata)


    dependents = find_selectable_dependents(selectable)
    for dependent in dependents:
        t.add_is_dependent_on(dependent)

    return t

[Mike Bayer]

On Thu, Oct 25, 2018 at 3:59 PM Philip Martin
<philip.m...@gmail.com> wrote:
>
> I am trying to create a function that finds the dependent tables used in a select-able statement. My plan is to use this function in conjunction with t.add_is_dependent_on inside a modified version of the recipe to create views. Hopefully, this allows sorted_tables to use these dependency relationships. The function definitely seems to work when the selectable only involves base tables, but breaks when a view uses another view. I think if I modify the function to recursively cycle through elements that are Selectables it should do the trick, but I was wondering if there is a public method like _from_objects / already a function to do something similar to this in the library?

right so you never need to dig into the private elements like
_from_objects for this kind of thing, there are traversal functions
that will give you whatever elements you need, the base of which is
sqlalchemy.sql.visitors.traverse. it has a plain iterator as well
which looks like:

from sqlalchemy.sql import visitors
for elem in visitors.iterate(some_statement, {}):
print(repr(elem))

most of the ORM internals use the traverse() function instead which is
given a set of functions that receive different types of elements,
like tables:

all_tables = set()
_visitors = {"table": all_tables.add}
visitors.traverse(statement, {'column_collections': False}, _visitors)

a whole lot of functions that use visitors can be seen in
sqlalchemy/sql/util.py so take a look in there, in particular take a
look at find_tables as well as surface_selectables.

>
>
> def find_selectable_dependents(selectable):
> dependents = set()
> for part in selectable.froms:
> for obj in part._from_objects:
> if hasattr(obj, 'element'):
> table_ = obj.element
> dependents.add(table_)
>
> return list(dependents)
>
> def view(name, metadata, selectable):
> t = table(name)
>
> for c in selectable.c:
> c._make_proxy(t)
>
> CreateView(name, selectable).execute_at('after-create', metadata)
> DropView(name).execute_at('before-drop', metadata)
>
>
> dependents = find_selectable_dependents(selectable)
> for dependent in dependents:
> t.add_is_dependent_on(dependent)
>
> return t
>
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Philip Martin]

Thanks Mike. It looks like util.find_tables is exactly what I was looking for.

On a related topic, it seems Base.metadata.sorted_tables is splitting out a different (and not correct) dependency order when a view depends on another view while schema.sort_tables([ view_depend_on_view_one, view_one]) is returning the views in proper order and the order of the DDL statements appears correct. Is that to be expected?

from sqlalchemy.schema import CreateColumn
from sqlalchemy.sql.ddl import _CreateDropBase
from sqlalchemy.sql.util import find_tables
from sqlalchemy.ext.compiler import compiles
from sqlalchemy import Column, event, MetaData, Table


def find_selectable_dependencies(selectable):
    """Find the tables used in a select query."""

    return list(set(find_tables(selectable)))


class _View(Table):
    __visit_name__ = 'view'
    is_view = True


def View(name, metadata, selectable, replace=True, cascade=False):

    v = _View(name, metadata)
    _meta = MetaData(schema=metadata.schema,
                     naming_convention=metadata.naming_convention)
    table = Table(name, _meta)

    for c in selectable.c:
        table.append_column(Column(c.name, c.type))

    event.listen(
        metadata, 'after_create', CreateView(v, selectable, replace=replace)
    )
    event.listen(
        metadata, 'before_drop', DropView(v, if_exists=True, cascade=cascade)
    )

    for dependency in find_selectable_dependencies(selectable):
        table.add_is_dependent_on(dependency)
        v.add_is_dependent_on(dependency)

    return table


class CreateView(_CreateDropBase):
    __visit_name__ = 'create_view'

    def __init__(self, element, selectable, on=None,
                 bind=None, replace=True):

        super(CreateView, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column) for column in element.columns]
        self.selectable = selectable
        self.replace = replace


@compiles(CreateView)
def visit_create_view(create, compiler, **kw):
    view = create.element
    preparer = compiler.dialect.identifier_preparer
    text = '\nCREATE '

    if create.replace:
        text += 'OR REPLACE '
    text += 'VIEW %s ' % preparer.format_table(view)

    if create.columns:
        column_names = [preparer.format_column(col.element)
                        for col in create.columns]
        text += '(%s)' % ', '.join(column_names)

    text += 'AS %s\n\n' % compiler.sql_compiler.process(create.selectable,
                                                        literal_binds=True)

    return text


class DropView(_CreateDropBase):
    __visit_name__ = 'drop_view'

    def __init__(self, element, on=None, bind=None, cascade=False, if_exists=False):
        super(DropView, self).__init__(element, on=on, bind=bind)
        self.cascade = cascade
        self.if_exists = if_exists


@compiles(DropView)
def compile(drop, compiler, **kw):
    text = "\nDROP VIEW "
    if drop.if_exists:
        text += "IF EXISTS "
    text += compiler.preparer.format_table(drop.element)
    if drop.cascade:
        text += " CASCADE"
    return text

[Mike Bayer]

On Fri, Oct 26, 2018 at 11:51 AM Philip Martin
<philip.m...@gmail.com> wrote:
>
> Thanks Mike. It looks like util.find_tables is exactly what I was looking for.
>
> On a related topic, it seems Base.metadata.sorted_tables is splitting out a different (and not correct) dependency order when a view depends on another view while schema.sort_tables([ view_depend_on_view_one, view_one]) is returning the views in proper order and the order of the DDL statements appears correct. Is that to be expected?

no, the dependencies are taken into account, here is a demo:

from sqlalchemy import *

m = MetaData()

t1, t2, t3 = Table('t1', m), Table('t2', m), Table('t3', m)

t1.add_is_dependent_on(t2)

t3.add_is_dependent_on(t1)

print([t.name for t in m.sorted_tables])

dependencies are consumed in
https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/ddl.py#L1105,
use pdb and make sure what's expected is present in there.