error in wiki.getRecentChanges()

[Olivier ANDRE]

Hello,

Context :

• debian squeeze
• trac 0.12
• postgres 8.4

Problem :

• the call to wiki.getRecentChanges() return this error :

'column "wiki.author" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT name, max(time), author, version, comment FROM wiki W...

the problem come from the wiki.py file, line 69

cursor.execute('SELECT name, max(time), author, version, comment FROM wiki'
' WHERE time >= %s GROUP BY name ORDER BY max(time) DESC', (since,))

Postgres is unableto perform this request.

cursor.execute('SELECT name, time, author, version, comment FROM wiki w1 WHERE time >= %s AND (name,version) = (SELECT w2.name, MAX(version) FROM wiki w2 WHERE w2.name=w1.name GROUP BY w2.name) ORDER BY time DESC', (since,))

do the same (even if less elegantly) and work in both mysql and postgres.

i attach a patch to this ticket.

[Olivier ANDRE]

patch for wiki.py

[osimons]

Applied the patch, but running the functional tests on SQLite makes the wiki.getRecentChanges test case fail: ERROR: near ",": syntax error

I'm unable to test postres and mysql, but I'd like the SQL to work across backends. It has to be readable, but doesn't have to be pretty :-)

BTW, you can run the functional tests by:

1. Install Trac from source (like python setup.py develop install) - we need to reuse the Trac testing infrastructure that is not part of runtime egg installations.
2. cd into RPC trunk and running python setup.py test or just for wiki test module you can run it as python -m tracrpc.tests.wiki.

[Olivier ANDRE]

I'll try to make a patch working on SQLite as well.

thx for the "how to" on functional test.

[txcraig]

I don't have a Trac source installation at the moment so I can't run the functional tests, but I do have a mixed Sqlite/Postgresql installation where I needed to have this fix. Here is the patch I used: attachment:wiki2.patch​

[txcraig]

patch that works for Postgre 9.0.4 and Sqlite

[osimons]

Thanks for the new patch! I've finally gotten around to configure Postgres for own development, and started playing with your changes to see if I could find one SQL to rule them all...

Could you please try the following patch - it works for me in Postgres and SQLite (Mysql untested), and tests pass:

trunk/tracrpc/wiki.py

@@ -66 +66 @@
         wiki_realm = Resource('wiki')
         db = self.env.get_db_cnx()
         cursor = db.cursor()
-        cursor.execute('SELECT name, max(time), author, version, comment FROM wiki'
-                       ' WHERE time >= %s GROUP BY name ORDER BY max(time) DESC', (since,))
+        cursor.execute('SELECT name, time, author, version, comment FROM wiki w1 '
+                       'WHERE time >= %s '
+                       'AND version = (SELECT MAX(version) '
+                       '               FROM wiki w2 '
+                       '               WHERE w2.name=w1.name) '
+                       'ORDER BY time DESC', (since,))
         result = []
         for name, when, author, version, comment in cursor:
             if 'WIKI_VIEW' in req.perm(wiki_realm(id=name, version=version)):

[txcraig]

I applied the patch and verified it works both Postgres and SQLite - edited a wiki page on each and getRecentChanges picked it up perfect. Thanks to zitune and osimons!

[osimons]

(In [10706]) XmlRpcPlugin: Fix wiki.getRecentChanges() for Postgres with new compatible SQL (that hopefully also works for Mysql).

Closes #9038. Thanks to zitune for report and patches.

[osimons]

Thanks to zitune AND txcraig for patches. Keep the patches coming for other issues, and I'll get it right next time. Promise :-)