Error when using code reviews function

[anonymous]

Using Trac 1.0 on Apache via PostgresSQL backend. When I click on the "Peer Review" navigation button, I receive the following error:

Trac detected an internal error:

ProgrammingError: relation "codereviews" does not exist
LINE 1: ...iew, Author, Status, DateCreate, Name, Notes FROM CodeReview...
                                                             ^
Python Traceback
Most recent call last:

    File "c:/docume~1/svchou~1/locals~1/temp/easy_install-tyu9e_/Trac-1.0-py2.7-win32.egg.tmp/trac/web/main.py", line 497, in _dispatch_request
    File "c:/docume~1/svchou~1/locals~1/temp/easy_install-tyu9e_/Trac-1.0-py2.7-win32.egg.tmp/trac/web/main.py", line 214, in dispatch
    File "build/bdist.win32/egg/codereview/peerReviewMain.py", line 74, in process_request
    File "build/bdist.win32/egg/codereview/dbBackend.py", line 37, in getMyCodeReviews
    File "build/bdist.win32/egg/codereview/dbBackend.py", line 177, in execCodeReviewQuery
    File "c:/docume~1/svchou~1/locals~1/temp/easy_install-tyu9e_/Trac-1.0-py2.7-win32.egg.tmp/trac/db/util.py", line 66, in execute 

[Ryan J Ollos]

#10822 closed as a duplicate.

[anonymous]

The cheap way to fix this is to change db_default.py to lowercase all the table and column names. The problem stems from the db tables being created with mixed/upper case letters that I assume are quoted during creation but then are not quoted when used (or possibly lower case names are used).

Once I did the change I was able to have the plugin run on both 0.12.2 and 1.0.1 (although it didn't run correctly e.g. no way to add files to a review).

An example of the change is:

#The tables for the Code Review Plugin
tables = [
    Table('codereviews', key='idreview')[
        Column('idreview', auto_increment=True, type='int'),
        Column('author'),
        Column('status'),
        Column('datecreate', type='int'),
        Column('name'),
        Column('notes'),
    ],
...

Note that the table names, key and all column names are lowercase.

Further info on case issues with databases can be found at:

​http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html

[Jun Omae]

#11456 was closed as a duplicate.

[Ryan J Ollos]

Replying to anonymous:

The cheap way to fix this is to change db_default.py to lowercase all the table and column names. The problem stems from the db tables being created with mixed/upper case letters that I assume are quoted during creation but then are not quoted when used (or possibly lower case names are used).

As noted in #11456, we'll also need an upgrade step.

There is string interpolation when creating the query strings in dbBackend.py, which should also be fixed.

Generally speaking, this plugin is a mess.

Once I did the change I was able to have the plugin run on both 0.12.2 and 1.0.1 (although it didn't run correctly e.g. no way to add files to a review).

That issue was likely addressed in #11184.

[Theodor Norup]

Replying to rjollos:

There is string interpolation when creating the query strings in dbBackend.py, which should also be fixed.

Yes, and not only there. E.g. in CodeReviewStruct.py lines 47-51, you find the following code:

cursor.execute("""
    INSERT INTO CodeReviews (Author, Status, DateCreate, Name, Notes)
    VALUES (%s, %s, %s, %s, %s)
           """, (self.Author, self.Status, self.DateCreate,
                 self.Name, self.Notes))
self.IDReview = db.get_last_id(cursor, 'CodeReviews', 'IDReview')

... where the first INSERT succeeds while the get_last_id fails as shown below. Note the superfluous double quotes.

ProgrammingError: relation "CodeReviews_IDReview_seq" does not exist
LINE 1: SELECT CURRVAL('"CodeReviews_IDReview_seq"')

This happens on Trac 1.0.9 on Postgresql/Ubuntu 14.04 even after the fix to db_default.py described above. Fixing is way over my head - which is a pity as the overall idea of the plugin is really good!

[Cinc-th]

In 15192:

Upgrade for database schema to version 2.

• all table and column names are lower case now
• table names are prepended by peer_ to prevent name clashes
• added some columns for future features
• uses Trac upgrade framework
• table contents is properly migrated

Note that the upgrade is only tested with SQLite.

Refs #10812
Refs #5395
Fixes #5808
Fixes #5401

[Cinc-th]

Replying to thenor:

Replying to rjollos:

There is string interpolation when creating the query strings in dbBackend.py, which should also be fixed.

Yes, and not only there. E.g. in CodeReviewStruct.py lines 47-51, you find the following code:

cursor.execute("""
    INSERT INTO CodeReviews (Author, Status, DateCreate, Name, Notes)
    VALUES (%s, %s, %s, %s, %s)
           """, (self.Author, self.Status, self.DateCreate,
                 self.Name, self.Notes))
self.IDReview = db.get_last_id(cursor, 'CodeReviews', 'IDReview')

... where the first INSERT succeeds while the get_last_id fails as shown below. Note the superfluous double quotes.

ProgrammingError: relation "CodeReviews_IDReview_seq" does not exist
LINE 1: SELECT CURRVAL('"CodeReviews_IDReview_seq"')

This happens on Trac 1.0.9 on Postgresql/Ubuntu 14.04 even after the fix to db_default.py described above. Fixing is way over my head - which is a pity as the overall idea of the plugin is really good!

 db.get_last_id(cursor, 'CodeReviews', 'IDReview')

Should be fixed with [15192] by changing to:

 db.get_last_id(cursor, 'peer_review', 'review_id')

The double quotes are added by the database backend.

If the problem persists open a new ticket.