Thanks that is good to know. I threw this up very quickly, but if I did
intend to have the mixin also handle creating the stored function, would
adding an event to the table's metadata object be the best way to go?
Granted, I'm guessing its good practice to inspect the db to see if the
function already exists vs. auto-replacing, but would what's below
basically do the trick? Does adding once mean this event is only called
once for every DDL element bound to metadata?
```
UPDATE_AT_PROCEDURE = 'set_updated_at_timestamp()'
# DDL to create set_updated_at_timestamp function
UPDATE_AT_DDL_STATEMENT = """\
CREATE OR REPLACE FUNCTION {procedure}
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW() AT TIME ZONE 'utc';
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';""".format(procedure=UPDATE_AT_PROCEDURE)
class AuditMixin(object):
created_at = Column(
TIMESTAMP(timezone=True), server_default=utcnow(), nullable=False
)
updated_at = Column(
TIMESTAMP(timezone=True), server_default=utcnow(),
server_onupdate=FetchedValue(for_update=True), nullable=False
)
@classmethod
def __declare_first__(cls):
meta = getattr(cls, 'metadata', None)
table = getattr(cls, '__table__', None)
if meta is not None:
event.listen(
meta, 'before_create', DDL(UPDATE_AT_PROCEDURE), once=True
)
if table is not None:
event.listen(
table, 'after_create', CreateUpdateAtTrigger(table.fullname)
)
event.listen(
table, 'before_drop', DropUpdateAtTrigger(table.fullname)
)
On Wednesday, November 1, 2017 at 6:51:00 PM UTC-7, Mike Bayer wrote:
>
> On Wed, Nov 1, 2017 at 9:45 PM, Philip Martin
> <[email protected] <javascript:>> wrote:
> > Wow. Must have completely missing this in the docs. Thank you for the
> > sample, it definitely has helped.
> >
> > I was looking through the source code and noticed internally many of the
> DDL
> > elements inherit from _CreateDropBase versus DDLElement. It looks like
> in
> > these cases, the element object is something like a Table object, etc.
> being
> > passed in versus something like a table name. Would you say its best
> > practice to always subclass DDLElement?
>
> as opposed to _CreateDropBase, yes, because _CreateDropBase is marked
> for internal use right now meaning it might change someday.
>
> >
> > I didn't know about configure_mappers() but that is good to know. Is
> that
> > strictly something to be aware of if I am fully using the ORM?
>
> it is a thing that happens automatically as soon as you use any of
> your mappings, such as, you say session.query(MyClass), obj =
> MyClass(), etc. However if you don't do any of that,
> configure_mappers() will force the "setup" phase to occur. This is
> the phase where all the linkages between mappers are resolved, e.g.
> relationships, and then there's a bunch of event hooks that trigger as
> well including the __declare_first__(), __declare_last__() hooks.
> If you app is just running and is going to do metadata.create_all(),
> then it's possible that this configure step hasn't happened yet as it
> wasn't in my local testing case. It's easy to forget.
>
> > I ran the
> > example using the declarative style just to build the table query
> (executing
> > with an engine, not session) and it seemed to function as exactly as I
> had
> > intended, but I'm not very familiar the Session object/ORM.
> >
> >
> >
> > On Wednesday, November 1, 2017 at 8:03:51 AM UTC-7, Mike Bayer wrote:
> >>
> >> you can use a hook like __declare_first__ to make this happen:
> >>
> >> class AuditMixin(object):
> >> """created_at, updated_at, table audit mixin."""
> >>
> >> @classmethod
> >> def __declare_first__(cls):
> >> table = getattr(cls, '__table__', None)
> >>
> >> if table is not None:
> >> event.listen(
> >> table, 'after_create',
> >> CreateUpdateAtTrigger(table.name))
> >>
> >> event.listen(
> >> table, 'before_drop',
> >> DropUpdateAtTrigger(table.name))
> >>
> >> created_at = Column(
> >> TIMESTAMP(timezone=True), server_default=utcnow(),
> nullable=False
> >> )
> >> updated_at = Column(
> >> TIMESTAMP(timezone=True), server_default=utcnow(),
> >> server_onupdate=FetchedValue(for_update=True), nullable=False
> >> )
> >>
> >>
> >>
> >> however, don't go insane like I just did, and remember to call
> >> configure_mappers() before you try to create tables, which will
> >> trigger hooks like __declare_first__().
> >>
> >> There are a lot of other hooks to set these triggers up but since you
> >> are linking to a mixin, __declare_first__ is the simplest, but needs
> >> the configure_mappers() step to happen for it to be called.
> >>
> >> also your function recipe needs a @compiles, I'm assuming you got that
> >> from the documentation example.
> >>
> >>
> >>
> >>
> >> On Tue, Oct 31, 2017 at 10:49 PM, Philip Martin
> >> <[email protected]> wrote:
> >> > I was trying to embed the reference code, but here is the link to the
> >> > gist I
> >> > have so far.
> >> >
> >> > On Tuesday, October 31, 2017 at 7:46:30 PM UTC-7, Philip Martin
> wrote:
> >> >>
> >> >>
> >> >> I am attempting to create an audit mixin object that would use a
> server
> >> >> side column trigger to set the updated_at timestamp. Ideally,
> whenever
> >> >> a
> >> >> class inherits from a declarative base and this mixin class, the
> >> >> trigger
> >> >> statement would be created after the table is created and dropped
> prior
> >> >> to
> >> >> the table being dropped.
> >> >>
> >> >> So far, I have my column mixin class, AuditMixin, and I also have
> added
> >> >> classes CreateUpdateAtTrigger and DropUpdateAtTrigger to compile the
> >> >> trigger
> >> >> DDL create and drop statements. From here, I am a bit unsure how to
> >> >> proceed
> >> >> to mount an event to any table's that use this mixin. Any help for
> >> >> specific
> >> >> reference examples would be appreciated. Thanks.
> >> >>
> >> >>
> >> >> <script
> >> >>
> >> >> src="
> https://gist.github.com/pmart123/b6681e9d9c6ddc92582143e13c1c6941.js";></script>
>
>
> >> >
> >> > --
> >> > 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 [email protected].
> >> > To post to this group, send email to [email protected].
> >> > Visit this group at https://groups.google.com/group/sqlalchemy.
> >> > For more options, visit https://groups.google.com/d/optout.
> >
> > --
> > 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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
