The attached patch against trunk (and 1.4.9 / 1.4.10) fixes 'roundup
migrate' for upgrading from roundup 0.6 to 1.4.

Here is a detailed description of the contained changes:

| --- roundup/backends/rdbms_common.py	(revision 4385)
| +++ roundup/backends/rdbms_common.py	(working copy)
| @@ -204,8 +204,11 @@
|              We should now confirm that the schema defined by our
"classes"
|              attribute actually matches the schema in the database.
|          """
| -        save = 0
|
| +        # upgrade the database for column type changes, new internal
| +        # tables, etc.
| +        save = self.upgrade_db()
| +
|          # handle changes in the schema
|          tables = self.database_schema['tables']
|          for classname, spec in self.classes.items():
| @@ -225,10 +228,6 @@
|                  del tables[classname]
|                  save = 1
|
| -        # now upgrade the database for column type changes, new internal
| -        # tables, etc.
| -        save = save | self.upgrade_db()
| -
|          # update the database version of the schema
|          if save:
|              self.save_dbschema()

The database version wasn't included in early 0.6 version, so it is assumed
'version 1'. To make self.database_schema['tables'] available, the
upgrade to
at least version 2 has to be done earlier, so I moved it further up.


| @@ -275,9 +274,11 @@
|              self.fix_version_2_tables()
|
|          if version < 4:
| +            self.log_info('upgrade to version 4')
|              self.fix_version_3_tables()
|
|          if version < 5:
| +            self.log_info('upgrade to version 5')
|              self.fix_version_4_tables()
|
|          self.database_schema['version'] = self.current_db_version

The code already writes "upgrade to version 2" and "upgrade to version 3",
so do it for 4 and 5, too.


| @@ -574,7 +575,12 @@
|      def add_class_key_required_unique_constraint(self, cn, key):
|          sql = '''create unique index _%s_key_retired_idx
|              on _%s(__retired__, _%s)'''%(cn, cn, key)
| -        self.sql(sql)
| +        try:
| +            self.sql(sql)
| +        except StandardError:
| +            # XXX catch e.g.:
| +            # _sqlite.DatabaseError: index _status_key_retired_idx
already exists
| +            pass
|
|      def drop_class_table_indexes(self, cn, key):
|          # drop the old table indexes first

During the early upgrade step the _status_key_retired_idx table is already
created to make calls to some methods happy. At a later point the code tries
to do it again, which obviously fails. Depending on the DB backend you get
different results. Additionally _sqlite.DatabaseError is not available in
rdbms_common.py so the StandardError exception is the only way to catch this
without restructuring large parts of the code.


| Index: roundup/backends/back_sqlite.py
| ===================================================================
| --- roundup/backends/back_sqlite.py	(revision 4385)
| +++ roundup/backends/back_sqlite.py	(working copy)
| @@ -255,7 +255,7 @@
|          self.create_class_table(spec)
|
|          if olddata:
| -            inscols = ['id', '_actor', '_activity', '_creation',
'_creator']
| +            inscols = ['id', '_actor', '_activity', '_creation',
'_creator', '__retired__']
|              for propname,x in new_spec[1]:
|                  prop = properties[propname]
|                  if isinstance(prop, hyperdb.Multilink):
| @@ -274,6 +274,7 @@
|              sql = 'insert into _%s (%s) values (%s)'%(cn, cols, args)
|              for entry in olddata:
|                  d = []
| +                retired_id = None
|                  for name in inscols:
|                      # generate the new value for the Interval int column
|                      if name.endswith('_int__'):
| @@ -296,6 +297,10 @@
|                          v = entry[name]
|                      else:
|                          v = None
| +                    if name == 'id':
| +                        retired_id = v
| +                    elif name == '__retired__' and retired_id and v
not in ['0', 0]:
| +                        v = retired_id
|                      d.append(v)
|                  self.sql(sql, tuple(d))
|

In old roundup versions (0.6 and before) the __retired__ column was not
filled
with the id of the retired item, but a constant value (string '1' or
number 1,
depending on the DB backend), while it was '0' or 0 when the item wasn't
retired.

When migrating to the current schema, a unique constraint for _username and
__retired__ is added to the _user table to make sure that usernames of users
who are not retired are unique. If the old roundup has two retired users
with
the same _username, the constraint will break for the duplicate retired
user.

The code makes sure, that the __retired__ column is filled with the
value used
by newer roundup versions, i.e. the item id.

Issue2550533 (upgrade to 1.4.8 + roundup-admin reindex -> crash
(postgresql backend)) discusses the exception in
add_class_key_required_unique_constraint() for postgresql
(psycopg2.ProgrammingError).

I just needed the patch again and therefore committed and pushed it:

changeset:   4774:3adff0fb0207
user:        Thomas Arendsen Hein <thomas@intevation.de>
date:        Fri Mar 22 15:53:27 2013 +0100
files:       CHANGES.txt roundup/backends/back_sqlite.py
roundup/backends/rdbms_common.py
description:
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4

All changes were required to make an upgrade from 0.6 to 1.4,
The changes affecting "retired" were required for an upgrade from 0.8 to
1.4.