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

By the way, I'm tracking this problem in issue #366 now.  I do think it was 
better to make slow-but-accurate changes than to leave the code in its 
previous broken state.  But yeah, if we can find a better solution, let's.

C. Michael Pilato wrote:
> Larry Shatzer, Jr. wrote:
>> On Tue, Jul 8, 2008 at 9:23 AM, Larry Shatzer, Jr. <[email protected] 
>> <mailto:[email protected]>> wrote:
>>
>>     [...]
>>     My only problem now is this is SLOW. What used to take a minute or
>>     two is now taking HOURS. I'm not sure the best approach. The problem
>>     I think is partly the where clause (with its sub query to enforce a
>>     "foreign key") and that this delete statement is getting called a
>>     bunch of times. If any database gurus want to step in and code
>>     review this, I would be appreciative.
>>
>>     I'm working on a few tweaks to this, here is a potential patch
>>     below. The checkins.descid still needs an index (included in updated
>>     make-database), and I'm looking at where other indexes might be
>>     useful. I don't think us adding additional indexes will be too much
>>     of a hassle, since the underlying schema is the same. We can try to
>>     get this index rolled into the default Bonsai for future versions as
>>     well. The only problem would be to have an "upgrade" script, or if
>>     we have local modifications (while still maintaining compatability
>>     with Bonsai) would be in a seperate script of some sort, or created
>>     some otherway (automaticly?).
>>     [...]
>>
>>
>> Any thoughts on this? I hate having to add an index, but at least it 
>> will not interfere with Bonsai (as far as I can tell). The only issue 
>> will be people upgrading to the new version of ViewVC which will have 
>> to add an index by hand, or somehow it magically gets applied.
> 
> Sorry for the delay.  I'm not a MySQL guru, but I would assume that 
> tools that don't make use of the index aren't affected by its presence.  
> (Can someone confirm as much?)  If that's true, then I think it's fine 
> to add the index.
> 
> As for the upgrade problem, can we programmatically determine the right 
> way to behave?  Do we need to introduce a schema version somehow?  How 
> is that typically done?
> 
> Being a MySQL luser, I would:
> 
>    * make ViewVC code check for the presence of a 'properties' table,
>      which is a simple STRING(name) => STRING(value) table.  If not
>      found, assume schema version 0 (the current one).  If found,
>      lookup the key "version" in the table, and take its value (interpreted
>      as an integer) as the schema version.
> 
>    * make ViewVC code refuse to work with a schema version greater than
>      the latest one supported by the code.  (This won't protect against
>      problems with already-released ViewVC code, but would start us down
>      the right path.)
> 
>    * if the schema version == 0, we disallow 'purge' (and rebuild) as
>      dangerous, telling folks they need to upgrade their database schema.
>      Other write operations are allowed.
> 
>      if version > 0, we allow it, and populate your new index.
> 
>    * we provide a script which upgrades the database to the latest schema
>      (which similarly consults this new 'properties' table).
> 
> Of course, being such a luser, I might quickly run into Obvious Problem 
> #1, which is: ____________________  (fill in the blanks if you, the 
> reader, are wise in this domain of knowledge).
> 


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

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

iD8DBQFIoz1aokEGqRcG/W4RAhS8AKCgNNRLeadVXB2CxwlQC9CZCq3CxQCfdLDn
vypBIWijvC9t5RO9m9M43W8=
=diQo
-----END PGP SIGNATURE-----