Opened 10 years ago

Closed 9 years ago

# "User" is a reserved keyword in Postgres.

Reported by: Owned by: anonymous Colin Guthrie high WorkLogPlugin blocker ecdpalma@… 0.10

### Description

The work_log table contains a field named user. This is an SQL reserved keyword:

This means that when will not be able to upgrade the trac database after enable worklog, if you are using Postgres.

The upgrade pretends to succeed but is in fact failing silently:

[20071019-18:46:55]:[root@bugs]:[~]# trac-admin /var/trac/systems upgrade --no-backup
Creating work_log table
Creating work_log table


However:

systems=# CREATE TABLE work_log (
systems(#   user       TEXT,
systems(#   ticket     INTEGER,
systems(#   lastchange INTEGER,
systems(#   starttime  INTEGER,
systems(#   endtime    INTEGER,
systems(# );
ERROR:  syntax error at or near "user" at character 27
LINE 2:   user       TEXT,
^
systems=#


### Changed 10 years ago by Bryan Allen

SQL changes required for standards-compliant databases.

### comment:1 Changed 10 years ago by Carsten Fechtmann

Priority: normal → high normal → blocker

Patch above is not applicable for 0.11b1 and postgres-users at least not at revision r2741

Plugin is broken as is when using postgress as DB, and even manages to bring trac down, unless it is disabled again in trac.ini by hand. Also see ticket #2106

I tried to apply the patch manually (as in changing all the user -> username) but there seems to be more than that wrong. E.g. work_log table is not created at all, WorklogPlugin_Db_Version in system is not set / changed etc.

Even when work_log table and system entry are created separately, and although trac-admin upgrade stops complaining, work-log is still not working, i.e. only producing an error message.

I hope someone else got an idea how to solve this, as I just plain run out of ideas ... (or at least SQL knowledge)

### Changed 9 years ago by ecdpalma@…

Corrects the problem with the "user" column name and the creation of the work_log table, that was being affected by a previous statement.

### comment:2 Changed 9 years ago by ecdpalma@…

Cc: ecdpalma@… added; anonymous removed → fixed new → closed

I discovered that if I surround user with double quotes, it works. Here goes a patch that corrects another things also. I found that in api.py, the statement

cursor.select(SELECT * FROM work_log LIMIT 1)


leads to a strange state, so the table could not be created in the next statement. So I put a rollback in the exception.

### Modify Ticket

Change Properties