Easier way to tie custom DDL to table creation / drops

[jek]

It's possible to execute custom DDL for things like special indexes and vendor features with engine.execute(), but that's not tied into metadata.create_all() and is a hassle.

To tie that in, Table could have a bucket for user-specified DDL, and it can be run at the end of each Table's create() and drop(), like constraints.

Patch with an approach attached.

[Lele Gaifax]

This would be nice, as it would allow defining triggers on the table, for example.

But shouldn't the name of the constructor be a capitalized word like the others?

[jek]

That's what I was looking for feedback on. These objects are basically just expression holders, and I think it could go either way: schema.ddl() (as in sql.text()), or DDLStatement().

Capitalized felt out of place in the first cut, probably because these aren't first class database objects. But the lower case is jarring too.

[Mike Bayer]

I like it. I think DDL() is more appropriate on the schema side here and it would be nice if it could subclass SchemaItem like everything else in the package.

I wonder if people would want it to function at the per metadata level as well:

• to create/drop databases, schemas
• to create users, grant permissions

[jek]

Any thoughts on a syntax for linking them to metadata? One option is to change DDL to take a parent as arg 0 and move these outside of Table:

  #!python
  t = Table('foo', metadata, ...)
  DDL(t, 'ALTER TABLE foo ...')
  DDL(metadata, 'CREATE SCHEMA ...')

Also, choice of pre- or post-creation/drop is going to be useful. Could be in the name of the constructor (PreDDL) or as an argument.

[Mike Bayer]

ideas:

DDL(metadata, precreate="CREATE SCHEMA...")
DDL(metadata, postdrop="DROP SCHEMA....")

DDL(table, postcreate="ALTER TABLE foo...")

tokens?

DDL(table, postcreate="ALTER TABLE %(tablename)s...")

[jek]

i've got a revised patch nearly ready to commit for this. just finishing the test suite. it's a new approach, the DDL() function builds on top of a simple, callable-based ddl event system. there are hooks on before and after create / drop, on metadata and tables.

with the raw events, you can do cute tricks like insert baseline data after a table has been created:

def fixture(table, *data):
    def run(_, tbl, bind):
        to_insert = [for c in tbl.c](dict(zip([c.key), d)) for d in data]
        bind.execute(tbl.insert(), to_insert)
    tbl.events['after-create']('after-create').append(run)

choices = Table('choices', metadata,
                Column('id', Integer, primary_key=True),
                Column('label', String(16)))
fixture(choices, (1, 'Yes'), (2, 'No'), (3, 'Maybe'))

token support is still in there, along the lines of the original sketch patch. the table and schema names are run through the identity preparer and are ready to use.

[jek]

The latest has two loose ends:

1. metadata-level listeners fire even for single_table.create()
2. no support yet for engine.execute(DDL())

But all in all it seems like a fine start to me.

[jek]

6d843aeeb2915206e3e80a6b88d9ce8ed068a035. Decoupling sometable.create() from metadata.create() is still a todo.

[Mike Bayer]

Removing milestone: 0.4.3 (automated comment)