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

10 Aug 2011


      On Wed, Aug 10, 2011 at 06:23:56PM -0500, Dan McGee wrote:
...
On Wed, Aug 10, 2011 at 6:08 PM, Dan McGee  wrote:
...
On Wed, Aug 10, 2011 at 5:48 PM, Lukas Fleischer
 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.