DiscussionPlugin doesn't work for me with sqlite db backend

[Immanuel Scheerer]

When I try to use the DiscussionPlugin with project using sqlite, I get the following error (trac 0.10, python 2.3, apache2 + mod_python):

Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 356, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 224, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-x86_64/egg/tracdiscussion/core.py", line 70, in process_request
  File "build/bdist.linux-x86_64/egg/tracdiscussion/api.py", line 31, in render_discussion
  File "build/bdist.linux-x86_64/egg/tracdiscussion/api.py", line 366, in _do_action
  File "build/bdist.linux-x86_64/egg/tracdiscussion/api.py", line 809, in get_groups
  File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 48, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.3/site-packages/sqlite/main.py", line 244, in execute
    self.rs = self.con.db.execute(SQL)
DatabaseError: no such column: forum_group.id

I made sure, that the table is in the database. The output from sqlite (".schema forum_group"):

CREATE TABLE forum_group (
    id integer PRIMARY KEY,
    name text,
    description text
);

I can't see, what is wrong here. Curiously everything works fine for me with a MySQL backend.

[schuetze@…]

I also have this problem with TRAC 0.10 and SQLite. Please check this ... Thx!

[Radek Bartoň]

What version of sqlite is your pysqlite using?

[Radek Bartoň]

In changesets 1375 and 1376 are commited some changes in SQL statements which could help but I'm not sure if they could work. Please try and let me know.

[dimi3]

I have SQLite version 2.8.16, I've installed the Discussion Plugin and I have the appropriate tables in db, but I get the error.

[Radek Bartoň]

Try to upgrade to sqlite3 if you can, I'm not sure if Trac 0.10 can be run with sqlite2. If it can, I'll have to rewrite SQL statements somehow, but I don't know how to make them compatible with sqlite2 for now.

[Sergio Talens-Oliag]

I've attached the patch attachment:"discussion-plugin-0.5-sqlite-2.8.16.patch"​ to this ticket.

That fixes the problem for me using trac 0.10 with sqlite version 2.8.16, it seems that this version's sql parser is not as clever as expected.

[Sergio Talens-Oliag]

Revised patch, timeline.py was missing

[Radek Bartoň]

Thank you very much for your patch. I knew that problem was with that nested SELECTs but I did't see that solution is so simple. I've commited 1482 to repository, so please try if 0.10 branch works for you now.

[Sergio Talens-Oliag]

I've replaced my patched plugin by an export from svn and it works OK.

I've seen that I missed more query changes (i.e. for the wiki) but luckily the plugin author knows the code better than me ;)

I believe that you can close the bug.

[Radek Bartoň]

Ok, thank you.

[Sergio Talens-Oliag]

After using the patched version I've detected that the queries for the fields Last Topic, Last Reply, Topics and Replies are wrong (they always show the values for all forums).

Tomorrow I'll try to build a working patch, now it is time to go home.

[Sergio Talens-Oliag]

New patch, removes nested queries

[Sergio Talens-Oliag]

I've attached a new patch that removes nested SQL queries, now my tests succeed, but the SQL code looks a little bit uglier; use this patch or revert the old one, the previous patch is wrong for all SQL databases.

[Radek Bartoň]

Your patch seem to work well. I've commited it. Thank you.