[aur-dev] [PATCH 2/3] Add "mergepkgid" argument to pkg_delete()

[Lukas Fleischer]

On Wed, Aug 10, 2011 at 06:23:56PM -0500, Dan McGee wrote:
> On Wed, Aug 10, 2011 at 6:08 PM, Dan McGee <dpmcgee at gmail.com> wrote:
> > On Wed, Aug 10, 2011 at 5:48 PM, Lukas Fleischer
> > <archlinux at cryptocrack.de> wrote:
> >> On Wed, Aug 10, 2011 at 05:35:46PM -0500, Dan McGee wrote:
> >>> -- only update votes that wouldn't exist after modification
> >>> UPDATE PackageVotes
> >>> SET PackageID = ?merge
> >>> WHERE PackageID IN (?others)
> >>> AND UsersID NOT IN (
> >>>         -- this silly double SELECT works around shitty MySQL not
> >>> letting you reference the updated table in the update, as it forces
> >>> materialization of the subquery
> >>>       SELECT * FROM (
> >>>               SELECT UsersID
> >>>               FROM PackageVotes
> >>>               WHERE PackageID = ?merge
> >>>       ) temp
> >>> );
> >>
> >> That's what I had when I wrote the query for the very first time but
> >> that won't work if we merge more than one package at once. Read my
> >> next-to-last email in this thread for some more details.
> >
> > Oh damn it, I thought I set up my test data right- I'll see what I can tweak.
> 
> Since deletion/merging isn't exactly something that is done every
> second, I'm less worried about perfection here. 90% of merges are
> going to involve one package anyway, so when we have more than one ID,
> why don't we just run the query once per to-be-deleted package ID?
> Doing it in sequence will mean each sees the result of the previous
> and thus dupes won't be created on the 2nd and following merges.
> 
> for each ID:
>     UPDATE PackageVotes
>     SET PackageID = ?merge
>     WHERE PackageID = ?todelete
>     AND UsersID NOT IN (
> 	SELECT * FROM (
> 		SELECT UsersID
> 		FROM PackageVotes
> 		WHERE PackageID = ?merge
> 	) temp
>     );
> 
> This will be cross-database, not do anything too funky, and should
> still be plenty fast.

Well, yeah. I guess this is the best thing we can do here. Either this
or using a temporary table. I already spent ~1h trying to construct a
proper query that is simple, fast and actually works. Probably, it isn't
worth that much work. I can live with a solution that is somewhat less
than perfect as well.