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

Larry Shatzer, Jr. wrote:
> 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, I'm *really glad* you brought this up.  I recently ran into the 
side-effects of this problem, and was rather confused at those symptoms.  I 
half-suspected that what you describe here was the ultimate source of the 
problems, but never got around to checking it out.

A patch from you with resolves this would be *most* welcome.

Question: would adding these additional indexes cause problems for folks 
using ViewVC commits databases with Bonsai?  (The schemas were designed to 
match each other.)

-- 
C. Michael Pilato <[email protected]>
CollabNet   <>   www.collab.net   <>   Distributed Development On Demand

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIanlJokEGqRcG/W4RAr32AKCBZSwtunyroBDBGZ2xyoXGbwQ7IACfXWUB
ld2GEsUrEFhi8pvw67R3LBc=
=0sZ3
-----END PGP SIGNATURE-----