Open - Moderation problems - Issues #86, #46, #179, #210, #105, #168, #266, #274, #300

[Vlansix]

@Enverex - Sadly, we rejoiced too quickly. I was right initially, I'd just forgotten why. The database columns can be null, the data that contains submitted requests cannot. Just figured I'd update you. We'll need to geet keep (how the hell did I write geet instead of keep?) slodging through corrupt date entries until I fix the frontend to prevent them from being submitted in the first place. 

[Enverex]

Is there a bunch of stuff created in various places linked to those requests that stops you being able to remove the data field then (or nuke the requests entirely)? Sorry, I do lots of DBA work so I'm just curious how that data gets passed through there in the first place.

[Vlansix]

Here's the long and short of it-

New game submissions create a parent change request in one table, which is then used to refer to all other associated requests that are stored in a separate table. (i.e. name, rating, wikipedia url, etc). Once loaded up into the moderation view, the next available parent is pulled and all associated change requests for that parent are displayed for the moderator to go through and approve/deny. Once any action is performed, the parent and/or individual requests are sent to a different moderation table to calculate approval/refusals for each item and action them appropriately, if needed. Same deal with images, with some extra logic. Also the same deal with developers and genres. All of these items have PK/FK relationships.

So, the issue isn't with the database really, as nuking a bunch of rows isn't a big deal, as long as we're doing it in a way that makes sense with the PK/FKs setup. The issue is with the frontend ;). 

Basically, as the requests have already gone into the moderation queue by the point I see them, I'm too late to stop them from being submitted (which is the real fix). I now have to catch them as exceptions coming out of SQL server through the frontend app. The exception handler is a last-step error correction system, so I only see the errors after "Accept" or "Reject" is clicked on the moderation view, which causes the following issues:

1) since I'm not working inside a stored procedure at that point, I can't modify the data and continue with the request before returning to the frontend app seamlessly.

2) Since I'm at the end of the "approve/reject" process, the webpage is awaiting some form of reply and is currently setup in a manner to accept only that reply (or an exception - see internal server error).

3) As the prior request encountered an error in SQL server and stopped it being executed, throwing an exception to the calling function in the frontend, my options are either to let it crash (internal server error), or catch that exception and try to fix it there.

4) Since, by that point, I'm at the end of a very long line of logic in the frontend, if I want to make it seamless to end-users, I have three options: 

1.  Correct the erroneous field and try to return the proper SqlDataReader object that the function calling *this* function expects (which is what we're doing now)
2. recode most of the frontend to not use this means of communication, hence letting me null out the erroneous request before resubmitting.
3. reconstruct the entire submission manually, resend it to whichever stored procedure that was being called, catch exceptions from that one (if there are any, in case), and then return to normal frontend execution.

Basically, the solution is to prevent the frontend from submitting erroneous data in the first place :). All I'm doing now is a last-ditch effort to prevent users from seeing internal server errors while bad data is being worked out of the system, as those specific issues will be scrubbed out before they get to the DB in the future ;).

All in all, if I were simply showing DB rows, erasing them wouldn't be difficult, but, as I'm showing DB rows that have tons of metadata linked to them, at the end of a process where every row is expected to be there and not be null, I can't just remove them :).

Why am I not validating the data inside stored procedures instead. Ergh. Jesus. You make for a good rubber duck, Enverex. Thanks.

[Vlansix]

Perfect. Erroneous date fields will now simply be removed from the entire process in the backend, so, if any do get through, they'll just disappear ;). Woot woot.

[Enverex]

Haha, I have to unbreak people's data all the time, I'm used to it.

Awesome that it's all sorted now too, nice job.

[Vlansix]

Bad news. The frontend still tries to push the nonexistent data through (as all the changes go through multiple processing passes). Good news, it doesn't prevent anyone but the first user who falls on a submission from orocessing it normally. Same deal for all other corrupt fields. 

So, all in all, about 50% done :P.

[Enverex]

The big one I'm running in to right now (other than generally very slow operation when using the moderation part of the DB) is that every time I accept a deletion request, I get internal server error. I don't appear to be getting that any other time now.

[Vlansix]

Thanks for the feedback. I'll look into deletions as well.

As for the slowness, a fix is in the works. Shouldn't be too long :).

[Enverex]

I forgot to mention, we can't reject the ones with invalid years. If you try and reject them, you get an Internal Server Error and (unlike in the case of the deletions, which despite the error do seem to save) it doesn't appear to have actually actioned it, as refreshing the moderation page will put you back on the same new game entry (with an invalid year). The only way to progress is to use the Ignore for 24 Hours option.

[Vlansix]

Aye, the invalid years issue is mostly sorted in the dev build. Once the server encounters an erroneous date field, it will delete it. This will still require one user to encounter an error (which is what I'm working on as I'd prefer it be seamless), but if I can't get it completely sorted by today, I'll at least upload that so we can get the submissions out of the moderation queue.

[Vlansix]

So. Fixes are live now-

1. Some fields were being sent with invalid dates. This is still possible through the frontend, but the database engine will now automatically delete these erroneous entries once they are found. 

2. The Wikipedia URL field has a maximum character count of 255 characters. Some users are submitting URLs longer than The Great Gatsby, which causes crashes. This is also being caught and deleted. Looking into Google's URL Shortener as a solution in the future.

3. Nameless games bug has been fixed, the issue was related to some changes being partially approved and, as such, losing their name as that seems to have been the only request that actually got through moderation properly.

4. Performance tweaks: All requests older than 31 days will now automatically be rejected.

5. Performance tweak: The tables containing the change requests, their indexes, their moderation status and the skipped games will now automatically archive anything older than 31 days. Both of these changes should dramatically increase moderation speeds.

6. Famicom merged with NES

7. Zombie requests are  problematic (i.e. bugged requests that don't go away), these will automatically be rejected by the system now.

8. Internal server error on deletion: Currently working on it. Will advise once corrected.

[Enverex]

Oh god it's so fast, I can't moderate that quickly!

[Jakatsu]

Indeed, what a difference in speed! Before this I usually had plenty of time to do other stuff after accepting/rejecting a submission and waiting for a new one, but now everything moves at lighting speed. So awesome.

[Enverex]

Oh, just in case anyone isn't aware (because I only noticed this last week):

If you need to replace an image, don't delete the old one and upload a new one. Click on the old one, to go Edit and then you can select a replacement image there instead. It means that the action only has to be approved once rather than two (apparently unrelated) actions of delete and then add. Also makes it clearer what's being replaced and why.

[Jason Carr]

I am absolutely loving this Vlansix. It's sooooooo much quicker now.

[Vlansix]

Hold onto your hat. It's about to get even more awesome.

[Selymax]

Hi All,  Thanks for asking me to be a Moderator, love being able to help the community and help Launchbox.

I have found one frustration though..  Most of the submissions I am approving lack any real detail other than name of game + box picture in most cases.

This is fine, I understand most people just want their games showing up correctly in Launchbox.

My issue here is that I am in most cases happy to dig a little deeper and add the wiki links, Publish, Description, Year etc.. the things that really make the database worthwhile,  but there is no way to easily edit any of the moderation I do, without having to submit these changes also for moderation.  Which means of course I am having to search for the game I have just approved and then adding these details in, thus also adding to someone else's moderation workload.

So in short, is there a way to add an extra approve button like "approve with edits"?  

I think this will make moderation a lot easier for perfectionists like me

Thanks

M 

[Enverex]

Moderation is a cumulative effort. Those details can be added once the game itself has been added. You may want to look into adding content rather than moderating then,

[Selymax]

Hey Enverex,

I understand its a group effort and adding the details after is how I have been doing it so far.   I am just trying to streamline the process a little so it can be done in one step rather than multiple is all.

Have been adding games to the database as well where I can,  like I say, I love being able to help Launchbox and the community any way I can.  No harm in making it a little easier

With addons now allowing the uploading of missing games direct from program, I suspect the amount of moderation needed has massively jumped in the last month, so anything that helps keep this in check must be a good thing?

[Massatomic]

its been talked about a few times before

Even if you were to make edits from the moderation cue those edits would then have to be re submitted anyway. Starting from the back of the que anyway

You can edit those details when ever you want from the DB side of things, the main reason editing in the moderation que has been talked about before is for when you see a spelling error, or bad punctuation to make small fixes like that