enhance mysql update to take updates from other tables (UPDATE FROM)

[Former user]

One mysql table can be updated by the results of another table but the mysql UPDATE statement requires all tables referenced viz: UPDATE T1,T2 SET T1.col = T2.col WHERE T1.id = T2.id and not (as sqla currently does) UDPDATE T1 SET T1.col = T2.col WHERE T1.id = T2.id which produces an error

The attached python code uses the excellent sqla compiler to enhance the Update compiler but the changes are so trivial I think they should go into dialects/mysql/base.py

[Former user]

patch for dialects/mysql/base.py

[Mike Bayer]

This is a good idea and would be more appealing if it also took a stab at Postgresql's functionality: http://www.postgresql.org/docs/8.1/static/sql-update.html . Also some nose tests in test/sql/test_compiler.py, test/dialects/mysql.py, test/dialects/postgresql.py.

[Former user]

a more bomb proof version that allows updates from selects too.

[Former user]

Replying to zzzeek:

This is a good idea and would be more appealing if it also took a stab at Postgresql's functionality: http://www.postgresql.org/docs/8.1/static/sql-update.html . Also some nose tests in test/sql/test_compiler.py, test/dialects/mysql.py, test/dialects/postgresql.py.

OK. Does SQLA have a style/dev guide anywhere? Do I just clone hg.sqlalchemy.org make the changes and push it back and if all is sweet it may be merged into the trunk?

[Mike Bayer]

if you take a look at the existing unit tests, you'll see how they're done. Its most convenient for me if you just attach a patchfile here (so I can compare it to trunk with just one click), but I can also pull from an hg somewhere (like bitbucket).

[Former user]

added nose tests

[Former user]

I've expanded the UPDATE enhancements to include postgresql and added some nose tests. The tests in enhance2.py currently work against mysql ,mysql+zxjdbc, postgresql and postgresql+zxjdbc.

I tried to move the changes into dialects/mysql/base.py etc. but when I diffed against clone from http://hg.sqlalchemy.org/sqlalchemy I got a heap of new additions (things advance fast in SQLA land). So my questions are:

1. What version (and how do I get it) should I patch against?

2. I have some shared methods for the postgresql and the mysql visit_update methods. Should I just make copies when I place them in dialects/mysql/base.py and dialects/postgresql/base.py (ditto for the tests)?

3. While I'm at it, are there any other databases that can take implicit joins for UPDATE?

[Mike Bayer]

so you make a clone of the current tip via:

hg clone http://hg.sqlalchemy.org/sqlalchemy

if you're in your own repo already, merge in the latest changes:

hg pull http://hg.sqlalchemy.org/sqlalchemy hg merge (or hg update, it will give you a clue)

if you merge, then you need to "hg commit" the merge.

for visit_update, at the moment its likely better to put distinct copies in each base.py, though it depends on how much code we're talking about. I'm not aware offhand of other DBs that do implicit joins in UPDATE, but there could be.

[Former user]

hg diff

[Former user]

here is a patch for the changes I've made (patch -p1 < update.patch). I've run the complete suite of tests for mysql,postgresql plus the jython equivalents with zxjdbc and all looks OK. My changes for mssql look OK too but I've only run part of the test suite for 'mssql+pyodbc'.

For the life of me I can't get nose to complete for mssql. Either it hangs on some tests (e.g. test.orm.tests_session.SessionTest.test_autoflush) or it chokes on a Unicode or binary test (under FreeTDS,unixODBC). I've tried variations of pyodbc on Vista and with FreeTDS on Ubuntu plus jython and zxjdbc but no luck. I all seems to work almost but not quite. Would the fact that I'm running these tests through a ssh tunnel be the problem? Is there a magic connection string keyword I'm missing?

Also I think I could extend the Update syntax to Oracle also but I don't have access to a server I can do the tests on.

[Mike Bayer]

there's a lot here which we should use though it needs cleanup. the tests in particular need to have less sprawl, result round trip tests should probably be made generic for all databases which support the syntax (and just be less, we don't need to test the database itself, just that the construct is accepted) I'm really not thrilled about "visit_update()" being entirely rewritten three times, rather than finding the minimal amount that needs to be outside of compiler.py, stuff like that.

[Mike Bayer]

note #2166 , we would like to integrate these.

[Mike Bayer]

OK added a new patch to #2166 that keeps things fairly simple, while supporting most of what MySQL's pretty special case can do (can update multiple tables, though not yet a join()). We can extract some of the docs from the patches here.