Repbot Database Cleansing Policy

[inim]

Repbot redeployment is in the last stages of completion now and it is time to think about the database. I got may requests to purge it from abuse, and tried to find a feasible solution to do so. First of all, trying to "correct" it manually is a futile attempt. There currently are about 16000 users casting 80000 opinions, so the sheer amount of data is prohibitive. Furthermore I am a technician and not a judge or psychologist to resolve long running disputes.

My personal opinion is that any cleansing should reflect the new rules of allowing only opinions (:= vouches and complaints) for some grace period after a match was played. "Played" means started, resumed, finished or dropped. I furthermore think bots should not be allowed to have opinions, and non-playing bots obviously should not get any opinions on them - they simply can't play.

So here is what I can do technically. Patti was kind enough to provide me a list of tuples (PlayerA, PlayerB) which represent all matches ever finished on FIBS between any player pair at any time since Moses was a boy. My suggestion now is to do what mathematically is described as the intersection of those unordered tuples with all unordered tuples of opinion casters (that is (Voucher, Vouchee) and (Complainer, Complainee). Whatever the intersection of those two sets of tuples is, that would be in the new DB. All other opinions would be deleted.

Please understand that the approach above is about the best I can do without opening Pandora's box by listening to indiviual requests for deleting this or that. Furthermore note that I do not think there should be any a-posteriori punishment of 10K-gang members, as 10K was crappy but legal when they did it. The new rules will prevent future abuse of that type, of course.

The downside with the mechanical cleansing is that a significant amount of complaints against droppers will be lost. I only have data about finished matches, so if you complained against a dropper my purging software can not know about that. And you also can not re-complain because that required you to replay the dropper. I still feel that this is the best solution, because it will allow to carry over at least all rightful vouchers and complainers really based on finished matches.

Another drawback is that I have no timestamps, so an opinion (A,B, opionion) will be counted regardless when A and B played, i.e. whether or not that match was relevant for the opinion. If A ever played B, opinions of A about B and vice versa are in the new DB.

That said, I would like to hear what fibsters think about the proposed policy. It is imperfect by nature, but the best I can think of with the means I have at hand.

[don]

Purge.  It'll get rid of most of the abuse.  I'm not sure why you're even bothering with bots, as I see no reason they can't vouch/complain if they've played someone.  I am sure that, with no purge, you are just continuing the abuse of RepBot.

--
don

[inim]

In a recent shout someone said he has problems understanding what the "purge" option means, here is the shortest definition I can come up with

An opinion of A about B will remain in a purged database if and only if A and B have ever finished a match.

[PersianLord]

Purge.  It'll get rid of most of the abuse.  I'm not sure why you're even bothering with bots, as I see no reason they can't vouch/complain if they've played someone.  I am sure that, with no purge, you are just continuing the abuse of RepBot.

--
don

Actually I see no reason they CAN vouch/complain if they've played anyone.

The only reasonable explanation for letting bots to do that is to enable them to compain about bot-droppers as a means to punish these abusers, right?

But the point is that Patti has developed a new plan to punish them by docking 15 rating pts. Actually yesterday she announced docking of 5700 rating pts from bot-droppers. So they are getting punished by server adminstrator, and a very painfull one, thus no need to let bots to overdo it.

Also I do think that letting bots vouch/compain gives geissenpeter an extra-advantage in implementing his obnoxiois plans to abuse FIBS and settle the score with his dissidents. Actually when inim applied the no-bot-vouch/complaint rule, my reputation rose about 15000 pts which was caused by gBOTs' complaints.

Thus, yes, I think bots should not have a right to vouch/complaint.

Regards

[socksey]

I like the idea of Patti gouging the droppers of bots because I hate bot droppers, too, and not allowing the bots to complain themselves is a good idea as described by PersianLord.   

Just please don't lose my great rep and mookie the most popular fibster! 

socksey



Astronomers say the universe is finite, which is not a very comforting thought for those people who can't remember where they leave things. - Unknown

[Patti]

My first choice is to completely throw away the database and start from scratch.  This is largely because I hate the idea of having "mixed meaning" for data.

I'm OK with limiting the database to only complaints and vouches where the players can be documented as having played each other since 1992.

I see no reason at all that bots shouldn't be allowed to vouch and complain like any other user.  If nothing else, there's no "I'm a bot" flag on FIBS.

[inim]

This is largely because I hate the idea of having "mixed meaning" for data.

The point here is focus, I want the bot the be accepting new opinions ASAP, and that is easiest done with as little new features as possible. To not preclude further refinement, I will add a field to the DB so we can see the software version with which the opinion was created. That should be  handy for future refinements of the policy. But as per now, I just want it to be released.

I see no reason at all that bots shouldn't be allowed to vouch and complain like any other user.  If nothing else, there's no "I'm a bot" flag on FIBS.

The reason is that we had about 450 bot opinions which were all cast by geissenpeter or bonehead. Nobody but the known abusers seem to ever have considered bot vouches, i don't think this will be missed. Note that RepBot very well has a flag for "I am a bot" ever since, but I admit that maintaining such a list is manual work I am not sure I will like to do forever. For now I won't allow bot opinions but am open for deeper post-release debates.

[Großmaulfrosch]

For now I won't allow bot opinions but am open for deeper post-release debates.

Monday, June 09 11:52:22 CEST   ( Mon Jun  9 09:52:22 2008 UTC )
> t repbot l donzbot
** You tell RepBotNG: l donzbot
>
RepBotNG says: donzbot's complainers: abi, bassplayer, Biggles_two, Botox, brl, Buso, dfisher, dick*, doombot, harzard____, Honeygirl_ll, I_NEVER_LOSE_III, isthisme, jajalamaprataprik, jasonj
>
RepBotNG says: donzbot's complainers (cont'd): klinger, lambda_phage, MadGreek, MattsDad, McGrill, neljen, NIHILIST, Norlan, Rina, sackofjaweea, schindler, Slipshod, virgo, Zorba
>
RepBotNG says: donzbot's vouchers: anakin, conman, crueger, diane, gilesashton, griff, HiRoller, hypno, Johnny_Boy, karmapa, kenzo, Macgammon, maria, markhz, Mary_in_Florida
>
RepBotNG says: donzbot's vouchers (cont'd): Mort, nielsrock, OpenWheel, Ral, Shuggie_II, Sly, taylor, texan, Wallenstein
>
RepBotNG says: donzbot's complaints: <kdc>, _scardy_, bassesmor*, bbque, benzion, bigdani, boxcar, brl, Buso, cajuna, callipygian, celera, cmh, DameDicker_VI, deanlikeafrenchman
>
RepBotNG says: donzbot's complaints (cont'd): dick*, dloreg, Dodgy, dorndle, eeniemeenie, Feklhr, fokkus, fresh, gammon_gangsta, gaucho, gnaisse, gold_eagle, Greg, harzard____, hinkali
>
RepBotNG says: donzbot's complaints (cont'd): Hockey_Nite, hydra, igor, jajalamaprataprik, janana, jasonj, JeffK, juancho, Kalewu, KaySettes, kenb, Klaus_Bergmann, larrym, LAX_TO_TLV, leveler
>
RepBotNG says: donzbot's complaints (cont'd): linx, Ludvigsson, lynchpfc, macky, MadMatt, melzi, mradica, nerino, NIHolympic*, off_in_the_shower, oguil, ours, paymahn, PeterOZanne, phs
>
RepBotNG says: donzbot's complaints (cont'd): pipi, PLevy, rastaman, Ray, retrovertigo, rit, sackofjaweea, safety_matches, scribblehead, Sinatra, Slipshod, Sly, Speedypete, suz, tart
>
RepBotNG says: donzbot's complaints (cont'd): taylor, telnet, theprez, Tino, tonibarjau, warbenleo, weedshower, xinloi, zakasax
>
RepBotNG says: donzbot's vouches: ace, acumen, adambulldog, ade, adhock, aleko, alisima, alterboy, amarganth, AMETHYST, anakin, apc, Argine, Arneb, austin
>
RepBotNG says: donzbot's vouches (cont'd): Bach, Backwoods, baghira, BANQUE, bardancer, bassplayer, bat, bertie, Biggles_two, blackstar, Bobber, bonehead, Bonobo, BSinclair, bthax*
>
RepBotNG says: donzbot's vouches (cont'd): Chench, chk, Chris, cococaro, colonial, conman, cordero, DadoHead, deljs, diane, dicing_queen, don, donzaemon, doombot, dosenwurst
>
RepBotNG says: donzbot's vouches (cont'd): duracell, Ebenezer, elagace, erkani, evilomlap, fibsy, fooey, franlou, fri, funny, ganjaman, ggworld, ghoozis, gilesashton, Gjean
>
RepBotNG says: donzbot's vouches (cont'd): gnomysTwo, Godzilla, GrandKiller, Guera, HARINCOZ, HATTER, helemanu, hypno, hypnosa, ibt, Iceborg, informix, insomnia, isthisme, jcnotanewbie
>
RepBotNG says: donzbot's vouches (cont'd): JeanLouis, jechu, jemdh, Johnny_Boy, Jojo, jokirk, jozeval, juanz, jussi, KATO, KDP, kenzo, kinetic, ladymara, lego
>
RepBotNG says: donzbot's vouches (cont'd): lene, LeSinge, LinkMasterJoe, LynnL, Macgammon, mad_jack, MadGreek, Mafalda, magowiz, maison, marti, Mary_in_Florida, mau, maxilex, mefistofeles
>
RepBotNG says: donzbot's vouches (cont'd): meganmarshall, mibaleaba, mirager, mjt, MMQ, mocca, moose, Mort, MVH, myndlis, nailer, nielsrock, Nondas, Norlan, notverylucky
>
RepBotNG says: donzbot's vouches (cont'd): numb, okamoto, onlyonepointers, OpenWheel, Patrixia, Pickaroon, Ramses, reaper, resh_lakish, revans, Rick, rod, rolldice*, roller, roton
>
RepBotNG says: donzbot's vouches (cont'd): rtheo, RubberLass, Ryobi, samirah, scardy, Schigolch, schindler, scuba, secos, Sequit, seventy_one, sexmachine, Shaggy, Shuggie_II, sidie
>
RepBotNG says: donzbot's vouches (cont'd): silent_greek, silfides, skunk, slickhorse, slowhand, sonnenblume*, spielberg, stork_one, SuperNovice, svrstp, texan, TheGens, thekla, thenonsequitur, threem
>
RepBotNG says: donzbot's vouches (cont'd): Tide, tjking, trotteur, tucsonAZ, vales*, vegasvic, verystripeyzebra, vrstud, Wallenstein, webrunner, Wizzard_of_Ozz*, wunbungtung, XCal, xkont, yannis
>
RepBotNG says: donzbot's vouches (cont'd): YinYangMan*, Znodd, Zonker
>
RepBotNG says: donzbot has 89 saved games.
>
RepBotNG says: Note: donzbot is a bot.
>
>
> t repbot l gBOT
** You tell RepBotNG: l gBOT
>
RepBotNG says: gBOT's complainers: Alcibiades, Aldus, alef, BeautifulDice, bertinibu, bigben, burper, cassandra, Cosmo, frittenforke, Gioconda, gnubee, greenone, ibharry, inim
>
RepBotNG says: gBOT's complainers (cont'd): jodvm, lemaraudeur, manxcat, McGrill, Merlin, nuttydude, PersianLord, primetime, RiceKrispies, rinit, secos, shadowcaster, Sinderbox, sombra, sunsplash
>
RepBotNG says: gBOT's complainers (cont'd): truckergirl, twin, vegasvic
>
RepBotNG says: gBOT's vouchers: Aulus, betterstart, BigTop, boog, ciccione, eMMe, FatElvis, Giggles, griff, javahypist, karmapa, marksist, Mary_in_Florida, mickeymouse*, mon_mon
>
RepBotNG says: gBOT's vouchers (cont'd): Monoecus, nauthiz, nOOb, Pareto, PEIDER, Pepsi, planitzen, QuintacolumnistA, rossa, Schichtwechsel, Slee, sokobi, tenor, TROjanHORse, uzalla
>
RepBotNG says: gBOT's vouchers (cont'd): voucher, Wetterhexe, whynot, yorkes, zax
>
RepBotNG says: gBOT has no complaints.
>
RepBotNG says: gBOT has no vouches.
>
RepBotNG says: gBOT has 40 saved games.
>
RepBotNG says: Note: gBOT is a bot.
>


Seems that donzbot has more vouches and complaints than all the gBOTs together ever had.

[Patti]

Geissenpeter's bots are just another case of multi-account RepBot abuse.  If you eliminate vouches and complaints based on the match list I sent you, then you eliminate his multi abuse as well.  It's also solved going forward, since only the bot that played against a user is allowed to express an opinion.

[inim]

Monday, June 09 11:52:22 CEST   ( Mon Jun  9 09:52:22 2008 UTC )
> t repbot l donzbot
** You tell RepBotNG: l donzbot

Let me say this before anything, my personal attitude towards opinionated bots is the same as that of the character Laura Roslin in "Battlestar Galactica (2004)", 'Throw the thing out of the air lock!'.

However, the result of the query

Code Select Expand


select u.name, u.id, o.* from opinions o, users u where (u.name like "%bot%" AND o.originating_user_id = u.id) ORDER BY u.name;


yields 1164 "bot" opinions even after purging gbot* and bonehead before, which had about 450 opinions.

The respective "bots" are: bluebot, BotBuster, bothofus, botkiller, Botox, bot_named_sue, bot_named_sue_II, bot_named_sue_III, bot_named_sue_IV, donzbot, donzbot_II, donzbot_III, donzbot_IV, doombot, douchebot, fembot, i_hate_bots, lobot, MortsBot, NiceRollerBot, PlayBot, PlayBotBunny, roBOTron, sobotta, tdbot, tingeltangelbot, twinkybot.

Boomslang's bots (PlayBot, PlayBotBunny) cast 151 opinions, donzaemon's (bot_named_sue_X, donzbot*) cast 816 opinions, which makes indeed donzaemon the busiest bot opinion caster. And of course many of the hits aren't really bots.

This research convinced me, bots will be allowed to have opinions. I prefered it the other way, but I see that this policy creates a lot of maintainance work which isn't worth the gain, and probably never will catch all cases anyway. But there is a price for that, likely a lot of the gbot's 10K abuse will remain in the DB, because many players have played a gbot at least once.

[donzaemon]

I don't mind if bots are allowed to have opinions or not.

I've had mine doing different things at different times , (only vouches, only complaints, everything off, experimented with responding to dropper shouts etc.) but pretty much settled on just having them complain when they are dropped , and withdraw/vouch when a saved game is resumed.

Since the bots often get dropped numbers in the 120 range,  it seemed appropriate to give the bot droppers a complaint. 

It would not bother me, though, if you want to keep bots from  using repbot at all. I think with the new "recency and synchronization" policies of repbot, it will be much more credible whether or not the bots are contributing.

Have you considered a compromise ?  Something like giving a bot half credit. ie  half a point for a complaint from a bot , full point for a complaint from a human or something along those lines.  That might work out well because the bots will be busy giving out rep complaints and vouches , but , some drop bots easier than humans etc.   and you don't want the bots to overshadow and 'normalize' the dropper readings if it is epidemic ...

[socksey]

Dropping a Bot is as bad as dropping a human player imho.  They should be allowed to complain if they're dropped.   

socksey



"In general, the art of government consists of taking as much money as possible from one party of the citizens to give to the other." - Voltaire (1764)

[inim]

The RepBot database has been purged as announced earlier and voted for by a majority in this poll. The poll is now closed. The switch to the purged database took place on June 13th, 2008 16:00 UTC.

Any opinions and withdrawls based on match events after the reset are permanent again.

Any vouches, complaints or withdrawls done beetween around April 30th, 2008 14:00 UTC and the reset are lost and need to be recast under the new rules. Please note that bots are allowed to have opinions, so no special treatment of bot votes was done.

Your reputation may still change over the course of the next days. Continuous removal of expired users and their opinions (aka garbage collection, short GC) is responsible for that effect. RepBot has to catch up with 6 weeks of GC backlog for users expired on FIBS since April 30th. This effect will last for a while because the process is deliberately slow to avoid load peaks on the FIBS server.

Please do not ask me about anything not related to the mechanical process of purging itself, e.g. why particular opinions are still in the database or not. I haven't looked into the actual data for a minute, all I did is to ensure the agreed upon algorithm was executed as cleanly as possible.

Below is the transcript of the machine readable commands actually executed. I would be happy if SQL literate readers would spend a minute to verify them for correctness. If flaws are found within one month after this posting, I will accept corrections and commit myself to apply them. After this grace period the purged database will not be changed anymore even if errors in the purging process surface.

Code Select Expand


-- We start with a database as used by RepBot, schema is here:
-- http://openfibs.svn.sourceforge.net/viewvc/openfibs/modules
-- /repbot/scripts/schema.sql?revision=38&view=markup
-- The actual dump file is not available officially. The version
-- used was:

-- MySQL dump 10.11
--
-- Host: localhost    Database: repbot
-- ------------------------------------
-- Server version 5.0.48
-- ..
-- Dump completed on 2008-04-29 17:01:02

-- 69554 rows inserted into opinions
-- 12967 rows inserted into users

-- A new table is created into which the list of known match
-- pairings is imported. The pairs are in no particular order.

CREATE TABLE fibspairs (
  user1 varchar(255) NOT NULL,
  user2 varchar(255) NOT NULL,
);

-- 2112263 rows inserted into fibspairs

-- Remove users from that list who are unknown to repbot, e.g.
-- expired or simply not using it.

DELETE FROM fibspairs
WHERE NOT EXISTS (SELECT * FROM users u WHERE u.name = user1)
      OR NOT EXISTS (SELECT * FROM users u WHERE u.name = user2);

-- 1296615 rows deleted from fibspairs
--  815300 rows left in fibspairs

-- Remove self-complaints from opinions

DELETE FROM opinions WHERE originating_user_id = target_user_id;

--   172 rows deleted from opinions
-- 69335 rows left in opinions

-- Remove opinions from known non-playing bots. This is strictly
-- a size optimization because those bots never played any
-- matches, all opinions about them would be deleted in the next
-- steps anyway.

DELETE FROM opinions USING opinions, users
WHERE opinions.target_user_id = users.id AND (
users.name="monitor" OR
users.name="RepBot" OR
users.name="RepBotNG" OR
users.name="rePYbot" OR
users.name="TourneyBot" OR
users.name="tobgol" OR
users.name="ratebot" OR
users.name="TimeBot"
);

--   239 rows deleted from opinions
-- 69096 rows left in opinions

-- An empty table users2 is created which has an identical schema
-- structure as users. Into that new temporary table all users
-- are copied which have any opinion, or are target of any opinion.
-- That reduces the number the users to be looked at in the
-- following queries, another optimization.

INSERT INTO users2
SELECT DISTINCT u.* FROM opinions o, users u
WHERE u.id = o.target_user_id OR u.id = o.originating_user_id;

--  8158 of 12976 rows copied from user to user2

-- Based on that optimization more pairs can be eliminated from
-- the list of relevant pairs of players who played matches.
-- (Yup, I know this could have been done earlier but better late
-- then never. This is a more like blog than like a howto ...)

DELETE FROM fibspairs
WHERE NOT EXISTS (SELECT * FROM users2 u WHERE u.name = user1)
      OR NOT EXISTS (SELECT * FROM users2 u WHERE u.name = user2);

--  64202 rows deleted from fibspairs
-- 751446 rows left in fibspairs

-- A new table is created in which the match pairs are no longer
-- represented as String but as foreign key relation to the user
-- table. That will eliminate the need of expensive string matches
-- later.

CREATE TABLE fibspairids (
  user1 int(11) NOT NULL default '0',
  user2 int(11) NOT NULL default '0'
);

-- Now copy over the actual ids from fibspairs

INSERT INTO fibspairids (user1, user2)
SELECT u1.id, u2.id
FROM fibspairs f, users u1, users u2
WHERE f.user1 = u1.name AND f.user2 = u2.name;

-- 751446 rows copied from fibspairs to fibspairids

-- Finally, drop all opinions for which no corresponding match
-- exists.

DELETE o.* FROM opinions o
WHERE NOT EXISTS (
SELECT * FROM fibspairids f
WHERE o.originating_user_id = f.user1
AND o.target_user_id = f.user2)
AND NOT EXISTS (
SELECT * FROM fibspairids f
WHERE o.originating_user_id = f.user2
AND o.target_user_id = f.user1)
;

-- 13824 rows deleted from opinions
-- 55272 rows left in opinions

-- Done.


Enjoy RepBot, which by this is now back to regular operation. And I probably need a break

[Mookie]

I would simply like to be assured that those many players who would like to VOUCH MOOKIE are given every opportunity to do so.  This is no time to deny people their rights.

Mookie

[lewscannon]

I would simply like to be assured that those many players who would like to VOUCH MOOKIE are given every opportunity to do so.  This is no time to deny people their rights.

Mookie

Why vouch for Mookie when a tattoo is a more permanent display of your affection? Mine only reads 'Mo' til I get excited, and then the whole name is revealed.

[socksey]

  

socksey



It was morning, and lo, now it is evening, and nothing memorable is accomplished. -Thoreau

[Großmaulfrosch]

 


It was morning, and lo, now it is evening, and nothing memorable is accomplished. -Thoreau

inim finally did it

[inim]

One more optimization was done, I have dropped all users from the "users" table who are not having an opinion about anybody, and about whom nobody has any opinion. The table size was significantly reduced, with no impact on user visible output.

The benefit of this is that the round-robin GC for expired user now rotates over a significantly reduced set, and by this will detect expired users twice as fast. Also less "whois" load is produced on FIBS for users nobody is interested in anyway. I'll probably add this to the GC code so it takes place automatically from time to time.

Code Select Expand


DELETE FROM users
WHERE users.id NOT IN (SELECT target_user_id from opinions UNION SELECT originating_user_id from opinions)
AND alert=false;

--- Deleted 7178 of 13168 rows in users
--- 5990 rows left in users


[webrunner]

I reviewed your steps as we discussed and it all looks very good!