how to write custom DDL class
Petr Kobalíček
I have problem to understand which way should be used to write custom
DDL class. I'm generating some stored procedures for my tables. The
procedures are very similar and I'd like to use some generic way. Now
I'm nearly done, but I need some way how to extract some information
from table to use it in my DDL.
I discovered that for this I need the engine where DDL runs.
My custom class may look like this:
class CustomDDL(DDL):
def against(self, target):
# Is this the method I should generate the DDL? How can I access
engine from here?
self.target = target
There is also possibility to create a __call__ method where the engine
is, but I'm not sure if this is the right place.
So my questions:
- which method I should override to make my custom DDL substitution
- do I need to base class on DDL or DDLElement? I can create my own,
but I'm not sure if I can add it.
Currently I'm using something like this to inject some triggers/functions:
_DDL_MATCHER = re.compile(ur"\$\{(_\w+)\:{0,1}([^\}]*)\}")
class CustomDDL(object):
def __init__(self, event, ddl):
self.event = event
self.ddl = ddl
def inject(self, table):
DDL(self.compile(table)).execute_at(self.event, table)
def compile(self, table):
global _DDL_MATCHER
def repl(match):
func = match.group(1)
args = match.group(2)
return getattr(self, func)(table, args)
return _DDL_MATCHER.sub(repl, self.ddl);
def _TABLE(self, table, args):
return table.name + args
def _PREPARE_DECLARATION(self, table, args):
result = u""
if hasattr(table, "area"):
for column in table.area:
#result += u"condition_" + unicode(column.name) + u" " + \
# unicode(column.type.get_dbapi_type(engine.dialect.dbapi)) + u"; "
pass
return result
def _PREPARE_CONDITION(self, table, args):
return ""
def _WHERE(self, table, args):
if args:
return u"WHERE " + args
else:
return u""
This works for me, except I need to access the engine.
Thanks for any info about this
Best regards
Petr
Petr Kobalíček
_DDL_MATCHER = re.compile(ur"\$\{(_\w+)\:{0,1}([^\}]*)\}")
class CustomDDL(DDL):
def __init__(self, statement, on=None, context=None, bind=None):
super(CustomDDL, self).__init__(statement, on, context, bind)
def against(self, target):
global _DDL_MATCHER
def repl(match):
func = match.group(1)
args = match.group(2)
return getattr(self, func)(target, args)
return _DDL_MATCHER.sub(repl, self.statement);
This works for me.
I'm getting engine from target.metadata.bind and I hope that it's correct way.
Michael Bayer
On Aug 31, 2010, at 8:15 PM, Petr Kobalíček wrote:
> Hi,
>
> I have problem to understand which way should be used to write custom
> DDL class. I'm generating some stored procedures for my tables. The
> procedures are very similar and I'd like to use some generic way. Now
> I'm nearly done, but I need some way how to extract some information
> from table to use it in my DDL.
>
> I discovered that for this I need the engine where DDL runs.
>
> My custom class may look like this:
>
> class CustomDDL(DDL):
> def against(self, target):
> # Is this the method I should generate the DDL? How can I access
> engine from here?
> self.target = target
>
> There is also possibility to create a __call__ method where the engine
> is, but I'm not sure if this is the right place.
>
> So my questions:
>
> - which method I should override to make my custom DDL substitution
> - do I need to base class on DDL or DDLElement? I can create my own,
> but I'm not sure if I can add it.
Typically you'd be using DDLElement with @compiles - that's the "normal" way to create a custom DDL element. Overriding DDL() and providing its inner text is fine as well but that string is static in relation to its parent.
__call__ is where the DDLElement is executed with the current connection. But its a conflation of tasks to override __call__ on a DDL construct to make decisions based on current database state and then change the state of "self" to produce a different string. The DDL construct represents a fixed statement, and compilation at most would switch off of the general properties of the dialect (many of which are derived from the database when it first connects, like server version, default schema).
If you want some kind of code to run within create_all() that has access to the connection and makes some decisions about what to emit, just use append_ddl_listener() on Table or MetaData, pass in a callable that makes the decisions you're looking to make. That callable can pass off your custom DDLElement() to the connection's execute() method.
Michael Bayer
On Aug 31, 2010, at 8:51 PM, Petr Kobalíček wrote:
> I solved everything by overriding against()
>
> _DDL_MATCHER = re.compile(ur"\$\{(_\w+)\:{0,1}([^\}]*)\}")
>
> class CustomDDL(DDL):
> def __init__(self, statement, on=None, context=None, bind=None):
> super(CustomDDL, self).__init__(statement, on, context, bind)
>
> def against(self, target):
> global _DDL_MATCHER
> def repl(match):
> func = match.group(1)
> args = match.group(2)
> return getattr(self, func)(target, args)
> return _DDL_MATCHER.sub(repl, self.statement);
>
> This works for me.
>
> I'm getting engine from target.metadata.bind and I hope that it's correct way.
Its not the best way since the creation sequence runs on a single connection, which could potentially be on a single transaction. Working with "bind" pulls you out of that state which would be problematic if you were using transactions on a transactional DDL backend like Postgresql.
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Petr Kobalíček
many thanks for explanation.
I considered what you wrote and now I'm making my own class as a
callable and I'm going to use append_ddl_listener(). This means I
don't need DDLElement and DDL.
Best regards
Petr