server_onupdate issue

[Roy Shan]

Hi, Michael:

I am trying to use server_onupdate in schema definition, but server_onupdate doesn't seem to work.

Here's an example:

from sqlalchemy import *
engine = create_engine('mysql://root:@localhost:3306/test?charset=utf8', echo =True)
m = MetaData(bind=engine)
t = Table('t', m,
    Column('a', Integer),
    Column('b', TIMESTAMP(), server_onupdate=text('current_stamp'))
)
m.drop_all()
m.create_all()

the output of this script is:

CREATE TABLE t (
        a INTEGER,
        b TIMESTAMP NULL
)

The server_onupdate has no effect.

I use SA 0.7.8. I traced into sql compiler but found server_onupdate was not checked at all in get_column_specification().

I wonder why server_onupdate doesn't work and how to fix this problem?

Thanks!

Roy

[Michael Bayer]

On Sep 30, 2012, at 9:48 PM, Roy Shan wrote:

> Hi, Michael:
>
> I am trying to use server_onupdate in schema definition, but server_onupdate doesn't seem to work.
>
> Here's an example:
>
> from sqlalchemy import *
> engine = create_engine('mysql://root:@localhost:3306/test?charset=utf8', echo =True)
> m = MetaData(bind=engine)
> t = Table('t', m,
> Column('a', Integer),
> Column('b', TIMESTAMP(), server_onupdate=text('current_stamp'))
> )
> m.drop_all()
> m.create_all()
>
> the output of this script is:
>
> CREATE TABLE t (
> a INTEGER,
> b TIMESTAMP NULL
> )
>
> The server_onupdate has no effect.

While it seems like it should, that's currently not what server_onupdate does. Most databases do not support an inline form of "ON UPDATE" within column definitions; MySQL is an exception to this but we do not support MySQL's syntax directly right now. server_onupdate's purpose is to place a marker on the column, so that the ORM knows that some separately defined trigger or rule has been established that will cause the column to receive a new value when an UPDATE statement executes.

To render MySQL's ON UPDATE you'd need to emit a separate ALTER instruction for now. 0.8 will offer a compile hook, but this is still not a first class "mysql_onupdate" feature as of yet.

[Roy Shan]

Michael,

Thanks for your clarification. I understand what server_onupdate does now.

It's weird that I saw some examples like this one: https://groups.google.com/d/topic/sqlalchemy/qYpPtgNXzAg/discussion

[Michael Bayer]

yeah in that thread I was forgetting/unaware that MySQL's TIMESTAMP generates a "default" and "on update" implicitly, so when he looked back at MySQL's rendering of the table, it included these defaults.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/VHxja_Pn7nsJ.
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.