[viewvc-dev] Bug in purge in db admin scripts

The purge part of the dbadmin scripts casts a wide net, deleting rows in
dirs and files (possibly other places) that are still refered to from other
tables.

Consider the following example:

Two repositories with files (or directories) named the same as other
repositories. A good example of this is a directory named build, or a file
named build.xml. Currently in the dirs or files table only one entry will
exist for each. When you purge a repository it will delete all files and
dirs entrys that corrolate to the checkins table (. If you purge one
repository you can corrupt your database for any other repository that might
have directories or files named the same. This causes a nice stack trace
when you try to use the query function on a repository that now has a fileid
or dirid that no longer exists.

I think the rebuild should not execute purge by defualt, maybe make it a
flag (or a flag to exclude it), and the purge functionality should do this:

delete from repositories where repository = '/path';
delete from checkins where repositoryid not in (select id from
repositories);
delete from descs where id not in (select descid from checkins);
delete from files where id not in (select fileid from checkins);
delete from dirs where id not in (select dirid from checkins);

This is a bit slower, and I had to add an index on checkins for descid to
speed up the 3rd step. There may be other indexes that could be added to
speed these up as well.

I can start working on a patch for this, but thought I would bring it up on
the list for some discussion, and the best way to approch this. This is an
edge case, and should not come up that often, but can result in data loss in
the database (which is not TOO critical, but it might be to some).

-- Larry