Problem migrating with Tracforge

[anonymous]

I was attempting to convert convert sqlite databases to postgres with the sqlite2pg script and encountered the following error:

Traceback (most recent call last):
  File "sqlite2pg", line 337, in ?
    Main(opts)
  File "sqlite2pg", line 244, in Main
    pgenv = getPostgreSQLEnvironment(opts)
  File "sqlite2pg", line 223, in getPostgreSQLEnvironment
    env.upgrade()
  File "/usr/local/lib/python2.4/site-packages/trac/env.py", line 342, in upgrade
    participant.upgrade_environment(db)
  File "/usr/local/lib/python2.4/site-packages/TracForge-1.0-py2.4.egg/tracforge/admin/api.py", line 66, in upgrade_environment
    cursor.execute(sql)
  File "/usr/local/lib/python2.4/site-packages/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql)
  File "/usr/local/lib/python2.4/site-packages/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: syntax error at or near "user" at character 56

I am running Trac 0.10.1 with the following plugins: tracforge webadmin

I am attempting to convert the dbs to Postgres 8.1.4.

The script fails when trying to create a particular table in the DB, tracforge_members. The table schema follows:

CREATE TABLE tracforge_members (
    project text,
    user text,
    role text,
    CONSTRAINT tracforge_members_pk PRIMARY KEY (project,user)
)

The line "user text," causes the syntax error. I can find no indication that the string "user" is reserved as a keyword or function in this version of Postgres, but it's my guess that this is the case. I am able to add the same table as long as I change all instances of the string "user" in this CREATE statement to say something else.

I am not sure if this is an issue with TracForge or the sqlite2pg script; it could be that the Tracforge schema is ill-suited for Postgres, but it may also be that the script is misreading something. I'll post a link to this ticket with the TracForge developers.

Let me know if you need some more info.

Thanks! Mark

[anonymous]

I tried to set up a new project with Tracforge using Postgres 8.1 and it failed during the initenv with a similar error, so I don't think this is a sqlite2pg script problem.

I poked around some more and it looks like "user" is indeed a keyword as per the ​SQL standard. I've included a patch for the Tracforge code which replaces relevant instances of "user" with "tfuser" in two tracforge files (tracforge/admin/model.py and tracforge/admin/db_default.py):

--- db_default.py.orig  Thu Nov 16 16:16:04 2006
+++ db_default.py       Thu Nov 16 15:41:47 2006
@@ -6,9 +6,9 @@
         Column('name'),
         Column('env_path'),
     ],
-    Table('tracforge_members', key=('project', 'user'))[
+    Table('tracforge_members', key=('project', 'tfuser'))[
         Column('project'),
-        Column('user'),
+        Column('tfuser'),
         Column('role'),
     ],
     Table('tracforge_permission', key=('username', 'action'))[
--- model.py.orig       Thu Nov 16 16:16:04 2006
+++ model.py    Thu Nov 16 15:41:27 2006
@@ -28,12 +28,12 @@
     def __getitem__(self, key):
         cursor = self.db.cursor()
         
-        cursor.execute('SELECT role FROM tracforge_members WHERE project=%s AND user=%s',(self.name, key))
+        cursor.execute('SELECT role FROM tracforge_members WHERE project=%s AND tfuser=%s',(self.name, key))
         row = cursor.fetchone()
         if row:
             return row[0]
         else:
-            cursor.execute('SELECT role FROM tracforge_members WHERE project=%s AND user=%s',('*', key))
+            cursor.execute('SELECT role FROM tracforge_members WHERE project=%s AND tfuser=%s',('*', key))
             row = cursor.fetchone()
             if row:
                 return row[0]
@@ -43,9 +43,9 @@
     def __setitem__(self, key, val):
         cursor = self.db.cursor()
         
-        cursor.execute('UPDATE tracforge_members SET role=%s WHERE project=%s AND user=%s',(val, self.name, key))
+        cursor.execute('UPDATE tracforge_members SET role=%s WHERE project=%s AND tfuser=%s',(val, self.name, key))
         if not cursor.rowcount:
-            cursor.execute('INSERT INTO tracforge_members (project, user, role) VALUES (%s, %s, %s)', (self.name, key, val))
+            cursor.execute('INSERT INTO tracforge_members (project, tfuser, role) VALUES (%s, %s, %s)', (self.name, key, val))
             
         if self.handle_commit:
             self.db.commit()
@@ -53,7 +53,7 @@
     def __delitem__(self, key):
         cursor = self.db.cursor()
         
-        cursor.execute('DELETE FROM tracforge_members WHERE project=%s AND user=%s',(self.name, key))
+        cursor.execute('DELETE FROM tracforge_members WHERE project=%s AND tfuser=%s',(self.name, key))
         
         if self.handle_commit:
             self.db.commit()
@@ -61,7 +61,7 @@
     def keys(self):
         cursor = self.db.cursor()
         
-        cursor.execute('SELECT user FROM tracforge_members WHERE project=%s',(self.name,))
+        cursor.execute('SELECT tfuser FROM tracforge_members WHERE project=%s',(self.name,))
         for row in cursor:
             yield row[0]

After applying the patch, however, I've run into another error, traceback included below. Note this was caused after filling out by running the following command:

trac-admin /usr/local/www/trac/<project> initenv

Creating and Initializing Project
Failed to create environment. 'SubscriptionManager' object has no attribute 'found_db_version'
Traceback (most recent call last):
  File "/usr/local/lib/python2.4/site-packages/trac/scripts/admin.py", line 611, in do_initenv
    options=options)
  File "/usr/local/lib/python2.4/site-packages/trac/env.py", line 130, in __init__
    setup_participant.environment_created()
  File "/usr/local/lib/python2.4/site-packages/TracForge-1.0-py2.4.egg/tracforge/subscriptions/manager.py", line 73, in environment_created
    self.upgrade_environment(self.env.get_db_cnx())
  File "/usr/local/lib/python2.4/site-packages/TracForge-1.0-py2.4.egg/tracforge/subscriptions/manager.py", line 97, in upgrade_environment
    if self.found_db_version == None:
AttributeError: 'SubscriptionManager' object has no attribute 'found_db_version'
Failed to initialize environment. 1
Traceback (most recent call last):
  File "/usr/local/lib/python2.4/site-packages/trac/scripts/admin.py", line 615, in do_initenv
    sys.exit(1)
SystemExit: 1

I haven't had time to look into this error just yet and won't for a few days more, but will get back to you as soon as I can.

As for the Postgres schema issue, sqlite2pg may want to account for this change for legacy support, unless there's a better way around it.

In either case, please let me know if either developer plans to institute these changes. My organization is very interested in using TracForge, but will require the use of Postgres (or MySQL, pending stable support) and we do have some legacy SQLite-based tracs. If there's anything I can do to help speed up fixes as they become available, please let me know.

Thanks again, Mark

[Noah Kantrowitz]

You're right, user is a reserved keyword in Postres. I'll get this fixed up tomorrow.

[mathomas@…]

The second bug (the one involving 'SubscriptionManager') turns out to be caused by environment_needs_upgrade() in tracforge/subscriptions/manager.py not being called.

This is because (I think) trac/env.py will call manager.py's environment_created() which, in turn, just calls upgrade_environment(). The problem is that environment_needs_upgrade() instantiates found_db_version which upgrade_environment() requires, but tracforge/subscriptions/manager.py ends up calling upgrade_environment() immediately upon creation, before it calls environement_needs_upgrade().

Hence found_db_version is not instantiated and initializing a Trac environment with TracForge enabled in the global trac.ini throws this exception.

One fix would be to place a call to environment_needs_upgrade() just before the call to upgrade_environment() in manger.py's environment_created() routine (line 72ish). Here's a potential patch:

--- manager.py.orig     Mon Nov 20 17:23:55 2006
+++ manager.py  Mon Nov 20 17:58:02 2006
@@ -70,7 +70,9 @@
 
     # IEnvironmentSetupParticipant methods
     def environment_created(self):
-        self.upgrade_environment(self.env.get_db_cnx())
+       db = self.env.get_db_cnx()
+       self.environment_needs_upgrade(db)
+        self.upgrade_environment(db)
         
     def environment_needs_upgrade(self, db):
         cursor = db.cursor()

[Ryan J Ollos]

This plugin is deprecated. See the TracMigratePlugin.