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

On Mon, Jul 7, 2008 at 11:16 AM, C. Michael Pilato <[email protected]>
wrote:

> Larry Shatzer, Jr. wrote:
>
>> It is issue 360, and I have added a patch using the idea of using the
>> extra where condition checking the checkins table if other rows exist for
>> that id. This works since the checkins table is cleared for that repository
>> first. Also, I added some cleanup for the people table in the purge, and
>> updated the output for rebuilds, that it WILL run a purge before doing a
>> rebuild.
>>
>
> As you might have seen already, I've reviewed and committed that patch.
> Thanks, Larry.
>

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?).

[[[
Index: lib/cvsdb.py
===================================================================
--- lib/cvsdb.py        (revision 1974)
+++ lib/cvsdb.py        (working copy)
@@ -457,35 +457,25 @@
           sql_args = (value, value)
         cursor = self.db.cursor()
         cursor.execute(sql, sql_args)
-
+
+    def sql_purge(self, table, key, fkey, ftable):
+        sql = "DELETE FROM %s WHERE %s NOT IN (SELECT %s FROM %s)"\
+               % (table, key, fkey, ftable)
+        cursor = self.db.cursor()
+        cursor.execute(sql)
+
     def PurgeRepository(self, repository):
         rep_id = self.GetRepositoryID(repository)
         if not rep_id:
             raise Exception, "Unknown repository '%s'" % (repository)
-
-        sql = "SELECT * FROM checkins WHERE repositoryid=%s"
-        sql_args = (rep_id, )
-        cursor = self.db.cursor()
-        cursor.execute(sql, sql_args)
-        checkins = []
-        while 1:
-            try:
-                (ci_type, ci_when, who_id, repository_id,
-                 dir_id, file_id, revision, sticky_tag, branch_id,
-                 plus_count, minus_count, description_id) =
cursor.fetchone()
-            except TypeError:
-                break
-            checkins.append([file_id, dir_id, branch_id, description_id,
who_id])
+        self.sql_delete('repositories', 'id', rep_id)
+        self.sql_purge('checkins', 'repositoryid', 'id', 'repositories')
+        self.sql_purge('files', 'id', 'fileid', 'checkins')
+        self.sql_purge('dirs', 'id', 'dirid', 'checkins')
+        self.sql_purge('branches', 'id', 'branchid', 'checkins')
+        self.sql_purge('descs', 'id', 'descid', 'checkins')
+        self.sql_purge('people', 'id', 'whoid', 'checkins')

-        #self.sql_delete('repositories', 'id', rep_id)
-        self.sql_delete('checkins', 'repositoryid', rep_id)
-        for checkin in checkins:
-            self.sql_delete('files', 'id', checkin[0], 'fileid')
-            self.sql_delete('dirs', 'id', checkin[1], 'dirid')
-            self.sql_delete('branches', 'id', checkin[2], 'branchid')
-            self.sql_delete('descs', 'id', checkin[3], 'descid')
-            self.sql_delete('people', 'id', checkin[4], 'whoid')
-
 ## the Commit class holds data on one commit, the representation is as
 ## close as possible to how it should be committed and retrieved to the
 ## database engine
Index: bin/make-database
===================================================================
--- bin/make-database   (revision 1974)
+++ bin/make-database   (working copy)
@@ -62,7 +62,8 @@
   KEY repositoryid_2 (repositoryid),
   KEY dirid (dirid),
   KEY fileid (fileid),
-  KEY branchid (branchid)
+  KEY branchid (branchid),
+  KEY descid (descid)
 ) TYPE=MyISAM;

 DROP TABLE IF EXISTS descs;
]]]