Modify

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#9038 closed defect (fixed)

error in wiki.getRecentChanges()

Reported by: zitune Owned by: osimons
Priority: normal Component: XmlRpcPlugin
Severity: major Keywords:
Cc: olemis Trac Release: 0.12

Description

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.

Attachments (2)

wiki.patch (819 bytes) - added by zitune 3 years ago.
patch for wiki.py
wiki2.patch (1.1 KB) - added by txcraig 3 years ago.
patch that works for Postgre 9.0.4 and Sqlite

Download all attachments as: .zip

Change History (9)

Changed 3 years ago by zitune

patch for wiki.py

comment:1 Changed 3 years ago by 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.

comment:2 Changed 3 years ago by zitune

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

thx for the "how to" on functional test.

comment:3 Changed 3 years ago by 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

Changed 3 years ago by txcraig

patch that works for Postgre 9.0.4 and Sqlite

comment:4 Changed 3 years ago by 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

    a b  
    6666        wiki_realm = Resource('wiki') 
    6767        db = self.env.get_db_cnx() 
    6868        cursor = db.cursor() 
    69         cursor.execute('SELECT name, max(time), author, version, comment FROM wiki' 
    70                        ' WHERE time >= %s GROUP BY name ORDER BY max(time) DESC', (since,)) 
     69        cursor.execute('SELECT name, time, author, version, comment FROM wiki w1 ' 
     70                       'WHERE time >= %s ' 
     71                       'AND version = (SELECT MAX(version) ' 
     72                       '               FROM wiki w2 ' 
     73                       '               WHERE w2.name=w1.name) ' 
     74                       'ORDER BY time DESC', (since,)) 
    7175        result = [] 
    7276        for name, when, author, version, comment in cursor: 
    7377            if 'WIKI_VIEW' in req.perm(wiki_realm(id=name, version=version)): 

comment:5 Changed 3 years ago by 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!

comment:6 Changed 3 years ago by osimons

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:7 Changed 3 years ago by osimons

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

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from osimons. Next status will be 'closed'.
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.