Error: no such function: IF

[anonymous]

By using trac 0.11.1 and WikiStatsPlugin I'm getting the error when using the Stats macro:

no such function: IF

I think it's from the SQL query in stats.py line 56-67:

SUM(IF(version = 1, 1, 0)) as pages_created,
SUM(IF(version = 1, 0, 1)) as pages_edited,

I tried to simple remove this two lines, then I get the error:

no such column: pages_created

[anonymous]

Sqlite doesn't have any if function.

There's some fix as seen at: ​http://code.activestate.com/recipes/438802/

I've a small fix, using sign, but I got unexpected results. anyway, if you create the _sign function somewhere in the file (eg, near the begining, before the class definition), and replace the query by:

        db.create_function("sign", 1, _sign)
        sql = '''SELECT
                author,
                SUM(1-abs(sign(version-1))) as pages_created,
                SUM(abs(sign(version-1))) as pages_edited,
                MIN(time) as first_edit,
                MAX(time) as last_edit
                FROM wiki '''

It seems to work (I go 0 edit and tons of creations, so I'm a bit doubtful about the result anyway)...

[Ariel Balter]

I get the same problem. Looking forward to a real fix!

[Michael Ching]

SQLite does not support IF() syntax, but we should just be able to use a SELECT CASE to get the same result while continuing to work with other database backends.

In addition to the syntax error when using a SQLite backend, the output code refers to fields by their relative positions with row[1] showing up as "Edited" and row[2] as "Pages Created". I believe the order of these two fields should be swapped in the SELECT statement.

The following should work for the base statement:

        sql = '''SELECT
                author,
                SUM(CASE WHEN version = 1 THEN 0 ELSE 1 END) as pages_edited,
                SUM(CASE WHEN version = 1 THEN 1 ELSE 0 END) as pages_created,
                MIN(time) as first_edit,
                MAX(time) as last_edit
                FROM wiki '''

[Michael Ching]

Slightly related, for UserCount() to work properly in SQLite, the double quoted string should be single quoted to avoid it being interpreted as the field name:

            cursor.execute('''SELECT count(*)
                              FROM session_attribute
                              WHERE name = 'name' ''')

[anonymous]

function if(boolean, integer, integer) does not exist LINE 3: SUM(IF(version = 1, 1, 0)) as pages_created,

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Database: Postresql

[Steffen Hoffmann]

One of the misc no generic SQL issues.

Without looking into all details here, the single-best approach is generally to dump any special or even proprietary SQL specific to only 1 or 2 out of the three currently supported Trac db backends.

There's not much in the box with label SQL understood by any SQL db. So it boils down to very simple SQL (no SUM for that matter), some special functions from trac.db like db.cast(), db.like() or db.like_escape(), and the rest of processing must be done in the Python script itself.

Sure, that doesn't unleash the full power of some db backends, but this is unavoidable for the sake of compatibility. AMEN ;-)

[Steffen Hoffmann]

As mentioned before, this issue can't be fixed within the current SQL statements alone.

For a re-make inside of AccountManagerPlugin (see #9852) I'll postpone most of the processing from the db to the Python layer for compatibility. I'll reference this ticket, but for the reasons given before I suggest a wontfix within the boundaries of this plugin's code.

[Ryan J Ollos]

This plugin is deprecated, and a notice has been posted to the WikiStatsPlugin page with more details. Please comment here if you have any concerns.