Re: [Savannah-hackers-public] savannah statistics

[Sylvain Beucler]

On Thu, Dec 11, 2008 at 09:29:18PM -0500, Nicodemo Alvaro wrote:
> On Thu, Dec 11, 2008 at 2:22 PM, Sylvain Beucler <address@hidden> wrote:
> > On Thu, Dec 11, 2008 at 07:38:10PM +0100, Sylvain Beucler wrote:
> >> On Wed, Dec 10, 2008 at 08:22:06PM -0500, Nicodemo Alvaro wrote:
> >> > >From the Savannah front page, I find there are + 171 registrations
> >> > pending. I took a look at the pending tasks in Savannah Administration
> >> > and found that there are 127 tasks pending not all of them are
> >> > registration submissions. I am curious if are there extra submissions
> >> > that I am not seeing.
> >>
> >> This means that a few projects were closed in the tracker, but not
> >> closing in the system.
> >>
> >> We'll have to search for them in the database :)
> >
> > Instead of manually checking, I went ahead and coded the SQL query
> > that does it for us:
> >
> > mysql> SELECT bug_id, unix_group_name FROM groups, task WHERE 
> > groups.status='P' AND task.summary LIKE CONCAT("Submission of ", 
> > groups.group_name) AND task.status_id=3;
<snip>
> > 68 rows in set (0.00 sec)
> >
> >
> > Do you think this is correct?
> >
> > If yes I'll automatically remove all these projects.
> 
> I think that the query you made may need some modification.
> 
> I don't know why this happened. Task 8580 had been accepted but showed
> up anyway in your list. Perhaps you are testing if I was looking.

This project was submitted twice with the same real name, with 2
different system names.

This is a problem if the submission was rejected and resubmitted (and
still open).


> Task 8733 had a new comment from the applicant in October a little
> after it was closed. Maybe it would be possible to include a date
> check of when it was closed and compare it with the last date of the
> comments.

When a project is closed, it's not supposed to be reopened, because
usually admins will close the project on the system.

In such case the user need to resubmit the project.


I reworked the thing:
SELECT groups.group_id, unix_group_name, groups.status, task.bug_id, 
task.status_id, groups.group_name FROM groups, task WHERE groups.status='P' AND 
task.status_id=3 AND task.group_id=5802 AND task.details LIKE CONCAT("%* System 
Name:  *", groups.unix_group_name, "*%") AND (SELECT count(*) FROM task WHERE 
task.group_id=5802 AND task.status_id=1 AND task.details LIKE CONCAT("%* System 
Name:  *", groups.unix_group_name, "*%")) = 0 GROUP BY groups.group_id ORDER BY 
bug_id;

DELETE groups FROM groups, task WHERE groups.status='P' AND task.status_id=3 
AND task.group_id=5802 AND task.details LIKE CONCAT("%* System Name:  *", 
groups.unix_group_name, "*%") AND (SELECT count(*) FROM task WHERE 
task.group_id=5802 AND task.status_id=1 AND task.details LIKE CONCAT("%* System 
Name:  *", groups.unix_group_name, "*%")) = 0;

It deleted 62 projects.
108 open task items + 62 projects = 170 pending projects - all good.

Thanks for testing!

-- 
Sylvain