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]

I am trying to use server_onupdate in schema definition, but server_onupdate doesn't seem to work.
from sqlalchemy import *
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()
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

Post by Roy Shan

Post by Roy Shan
I am trying to use server_onupdate in schema definition, but

server_onupdate doesn't seem to work.

Post by Roy Shan
from sqlalchemy import *

echo =True)

Post by Roy Shan
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()
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.

[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.

Post by 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

I am trying to use server_onupdate in schema definition, but server_onupdate doesn't seem to work.
from sqlalchemy import *
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()
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.
--
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.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.