Structured tables. One for posts, one for comments, one or more for karma and so on, with appropriately typed columns for each attribute such things have. Alternatively if the data really is unstructured then I'd use a key-value store like Cassandra or something.
(For the record many modern key-value stores didn't exist when the Reddit code was originally written).
Seconding this. A proper relational database would look something like this:
= 27d567bc2d48d9f40e9ccc20ea370b15)
CREATE TABLE Users
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(250),
passwordHash VARCHAR(250),
firstname VARCHAR(250),
lastname VARCHAR(250),
description VARCHAR(MAX),
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
dateLoggedIn DATETIME NOT NULL DEFAULT GETDATE(),
active CHAR(1)
);
CREATE TABLE Themes
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(250),
description VARCHAR(MAX),
css VARCHAR(MAX),
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
dateEdited DATETIME NOT NULL DEFAULT GETDATE(),
active CHAR(1)
);
CREATE TABLE Forums
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(250),
description VARCHAR(MAX),
users_id_owner INT NOT NULL FOREIGN KEY REFERENCES Users(id),
themes_id INT NOT NULL FOREIGN KEY REFERENCES Themes(id),
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
dateEdited DATETIME NOT NULL DEFAULT GETDATE(),
active CHAR(1)
);
CREATE TABLE Posts
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
forums_id INT NOT NULL FOREIGN KEY REFERENCES Forums(id),
posts_id_parent INT NOT NULL FOREIGN KEY REFERENCES Posts(id),
users_id_poster INT NOT NULL FOREIGN KEY REFERENCES Users(id),
title VARCHAR(250) NOT NULL,
text VARCHAR(MAX) NOT NULL,
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
dateEdited DATETIME NOT NULL DEFAULT GETDATE(),
active CHAR(1)
);
CREATE TABLE Votes
(
value INT NOT NULL,
posts_id INT NOT NULL FOREIGN KEY REFERENCES Posts(id),
users_id_voter INT NOT NULL FOREIGN KEY REFERENCES Users(id),
dateCreated DATETIME NOT NULL DEFAULT GETDATE()
);
-- constraint: only one vote per post per user
ALTER TABLE Votes ADD CONSTRAINT pk_Votes PRIMARY KEY (posts_id,user_id)
With that schema, all you'd have to do to see someone's effect on another person's karma is:
SELECT SUM(VALUE) FROM Votes
WHERE users_id_voter = @Voter
AND posts_id IN
(SELECT id FROM Posts WHERE users_id_poster = @User)
EDIT: Wow, formatting is a pain.
Interesting- I don't think percentage is a useful metric (it can easily be a metric of controversy rather than thought quality). The really concerning thing about mass downvoting is what it can do to the perception of comments when they are being initially read- retroactive adjustments will not matter as much.
Yeah, I noticed a lot of that - if Eugine was the first person to get to one of my comments, it had a lot higher chance of being downvoted further, even if it was similar to other comments that got upvoted when he didn't get to them until later.
Herd mentality is scary.
Ah, I see. That's a reasonable request, I'll ask if there's anything that can be done about it.
Places to start looking:
I don't know how extensively this site's source code has been modified from the reddit default, but in r2/models/vote.py we have:
class VotesByAccount(tdb_cassandra.DenormalizedRelation)
class LinkVotesByAccount(VotesByAccount)
class CommentVotesByAccount(VotesByAccount)
Python isn't currently in my active language cache, so I'm a little rusty dragging through all the dependencies; I'll try to spend this weekend getting up to speed with Python and see if I can help sort out a generic "wipe out a user's full voting history" script that can be safely run.
Does the system keep track about individual downvotes (who downvoted what)? If yes, then it could be possible to simply revert all votes ever by Eugine. Which should solve all the problems: everyone would have the same total karma and comment karma as if this whole thing never happened.
It has to - otherwise you wouldn't be able to see what YOU upvoted/downvoted.
Also, otherwise you would be able to upvote or downvote something multiple times.
So clearly, it has to track somewhere.
If you guys need a SQL guy to help do some development work to make meta-moderation easier, let me know; I'll happily volunteer a few hours a week.
EDIT: AAAUUUGH REDDIT'S DB USES KEY-VALUE PAIRS AIIEEEE IT ONLY HAS TWO TABLES OH GOD WHY WHY SAVE ME YOG-SOTHOTH I HAVE GAZED INTO THE ABYSS AAAAAAAIIIIGH okay. I'll still do it. whimper
I seem to be the lone dissenter here, but I am unhappy about the ban. Not that it is unjustified, it definitely is. However, it does not address the main issue (until jackk fiddles with karma): preventing Eugine from mass downvoting. So this is mainly retribution, rather than remediation, which seems anti-rational to me, if emotionally satisfying, as one of the victims.
Imagine for a moment that Eugine did not engage in mass downvoting. He would be a valuable regular on this site. I recall dozens of insightful comments he made (and dozens of poor ones, of course, but who am I to point fingers), and I only stopped engaging him in the comments after his mass-downvoting habits were brought to light for the first time. So, I would rather see him exposed and dekarmified, but allowed to participate.
TL;DR: banning is a wrong decision, should have been exposed and stripped of the ability of downvote instead. Optionally, all his votes ever could have been reversed, unless it's hard.
EDIT: apparently not the lone dissenter, just the first to speak up.
I'm also unhappy with him being banned from commenting but not downvoting. While I frequently found his comments obnoxious and annoying in their connotations, they definitely served a net positive on the site.
That said, his moderation practices clearly served a larger net negative, so if there are technical reasons why it's difficult to undo his moderation and ban him from moderating in the future, I suppose this is the best we can get.
I don't feel even a little surprised - the one contrary hypothesis that seemed worth considering was someone personally close to, or weirdly obsessed with, Eugine Nier. But yes, this is good news.
Well, the last time I brought it up, there was quite a bit of controversy about whether I was imagining things... so I somehow feel vindicated. (But not TOO vindicated - it's important to note let those probabilities peg to 0 or 1)
Eugine's primary karma engine was karma-mining the Rationalist Quotes page
Nah. The quotes make up <1/5th of his top-ranked comments, and you can see for yourself: load http://www.ibiblio.org/weidai/lesswrong_user.php?u=Eugine_Nier , wait for it to fetch all his comments, "sort by: points", "hide parents", copy-paste down to, say, his comments with +9 karma, and then look at the composition:
$ xclip -o | fgrep -e 'In response to' | fgrep -i -e 'quote' | wc --lines
20
$ xclip -o | fgrep -e 'In response to' | wc --lines
108
Of his comments ranked >= 9 points, 20/108 or <1/5 were on rationality quote pages. I suppose he could be getting much more karma from masses of lower-ranked comments on quotes pages, but that seems a bit unlikely and more work than I want to do at the moment.
gwern: Testing our hypotheses since 2009.
Thanks for the info; I was not expecting the data to show that. It does indicate that the problem will be smaller than I feared.
Now rewrite what you said as an SQL query...
:D
The serious answer is that the people who were downvoted noticed that they were downvoted. That was the whole point. At that moment, they should contact a moderator and report a suspicion. And we should make this visible somehow...
Anyway, the main damage was from knowing that someone mass-downvotes you anonymously, and you don't know who, and you can't defend. (And that it keeps happening to multiple people, for months.) This shouldn't happen again, because it would be easier to fix the next time.
Now rewrite what you said as an SQL query...
Using reddit's database schema? Challenge accepted. I'm at work right now (writing SQL queries for my college, in fact), but I'll gladly contribute something useful when I get home.
EDIT: This is a lot more difficult than anticipated. :( I'm going to have to do some serious research before I can produce something useful, given reddit's flat kvp schema.
How are you going to deal with socks?
Not really a problem. To gain a lot of downvote power, short of creating a bunch of circle-upvote socks, you'd need to comment or write a lot, and longtime commenters like Eugine are generally easy to spot: everyone has idiosyncratic ideas, ways of phrasing things, writing styles, references and calculations... (without even getting into stylometrics). For example, if I were banned today and surfaced under another sock a month from now, I'd be spotted quickly - just look for the new account that uses lots of hyphens, semicolons, lists, quotations and paraphrases, etc in discussing topics like statistical & experimental methodology. Similarly, Eugine has a lot of idiosyncratic interests (global warming, the fall of the west, conservative family values and so on).
This is the same reason the worst special-interest trolls on Wikipedia didn't benefit much from socking: they had too clear a fingerprint in their arguments and writings.
True, but in my experience, Eugine's primary karma engine was karma-mining the Rationalist Quotes page; someone could simply commit to ONLY posting there, and build a pretty substantial resource pool rather quickly.
= f037147d6e6c911a85753b9abdedda8d){kind=logo}
= b715d02e85f7b3b4ae65ca3d3e450868)
Subscribe to RSS Feed
A question I would like advice from others on:
I frequently find myself in a brain-state where my emotions and morals fail to determine my actions in what most people call the 'usual way'. Essentially, at certain times I am "along for the ride", and have no capacity to influence my behavior until the ride has come to a full and complete stop. I assert that this is usually triggered by external stimulus, but I acknowledge, dear reader, that you have no reason to accept that excuse.
What is the correct thing to do in these situations, given that there is no possibility to choose what to do in these situations - and given that others have no reason to accept "sorry about my freak-out, I have PTSD" as anything but a craven attempt to turn bad behavior into a ploy for sympathy?