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

[Dan McGee]

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.

-Dan