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.