Catch database exceptions in a backend neutral way

[jruigrok]

When you, within trac-admin, add a permission to a user that already has that permission you will receive a Python traceback.

Trac [/usr/local/trac-rangaku]> permission list

User           Action
-------------------------------
asmodai        MILESTONE_ADMIN
asmodai        PERMISSION_ADMIN
asmodai        TICKET_ADMIN
asmodai        TRAC_ADMIN

Trac [/usr/local/trac-rangaku]> permission add asmodai MILESTONE_ADMIN
Traceback (most recent call last):
  File "/usr/local/bin/trac-admin", line 8, in <module>
    load_entry_point('Trac==0.11dev-r6153', 'console_scripts', 'trac-admin')()
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/admin/console.py", line 1190, in run
    admin.run()
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/admin/console.py", line 119, in run
    self.cmdloop()
  File "/usr/local/lib/python2.5/cmd.py", line 142, in cmdloop
    stop = self.onecmd(line)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/admin/console.py", line 102, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/local/lib/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/admin/console.py", line 393, in do_permission
    self._do_permission_add(user, action)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/admin/console.py", line 429, in _do_permission_add
    self._permsys.grant_permission(user, action)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/perm.py", line 274, in grant_permission
    self.store.grant_permission(username, action)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/perm.py", line 204, in grant_permission
    (username, action))
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/db/sqlite_backend.py", line 58, in execute
    args or [])
  File "/usr/local/lib/python2.5/site-packages/Trac-0.11dev_r6153-py2.5.egg/trac/db/sqlite_backend.py", line 50, in _rollback_on_error
    return function(self, *args, **kwargs)
pysqlite2.dbapi2.OperationalError: SQL logic error or missing database

Please note that the database does exist and initial permission assignments work as intended.

[cboos]

We should transform DB integrity exception in a backend independent TracIntegrityError that we could handle at higher levels.

The problem is that it's not always obvious to detect what is an integrity error.

In the above you had:

pysqlite2.dbapi2.OperationalError: SQL logic error or missing database

whereas on my side I have:

pysqlite2.dbapi2.IntegrityError: columns username, action are not unique

... which is more specific. What versions of SQLite / PySqlite do you use?

[jruigrok]

On FreeBSD 6.2-STABLE:

SQLite 3.4.1 PySQLite 2.5.1

[cboos]

#6095 closed as duplicate.

We must transform the SQL-specific exception into a TracIntegrityError that we can intercept at a higher level, see discussion in googlegroups:trac-dev:2f82a333d6b9eaa7.

[cboos]

See also #5445.

[osimons]

#6841 closed as duplicate.

[cboos]

See also #6808 (that one is not a duplicate though, as it's dedicated to fixing the way we can try to avoid the exception to happen in the first place).

[osimons]

#7351 closed as duplicate (Traceback when renaming a milestone to an existing name).

[cboos]

#7754 closed as duplicate.

Interestingly, the traceback is mostly identical as the one in the current description, but the exception is different:

pysqlite2.dbapi2.IntegrityError: columns username, action are not unique

vs.

pysqlite2.dbapi2.OperationalError: SQL logic error or missing database

which highlights the need to handle all those db exception in a more generic way.