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

10 Aug 2011

      On Wed, Aug 10, 2011 at 6:08 PM, Dan McGee <dpmcgee@gmail.com> wrote:
...
On Wed, Aug 10, 2011 at 5:48 PM, Lukas Fleischer
<archlinux@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