Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+12 votes
in Q2A Core by

Recently I deployed a script to remove unconfirmed users from the database after a couple hours (my config requires that registered users confirm their e-mail). After a number of cleanups I noticed that my user list got paginated, even though the user count wouldn't warrant pagination. A quick glance at the code revealed that new users get added to the table qa_userpoints as well as qa_users.

Can we add a foreign key constraint to qa_userpoints, so that deletion of a user from qa_users cascades to the other table?

CREATE TABLE qa_userpoints (
  FOREIGN KEY (userid)
    REFERENCES qa_users (userid) ON DELETE CASCADE

or (after table creation)

ALTER TABLE qa_userpoints

Or must entries in qa_userpoints be preserved for some reason?

Q2A version: 1.8.5
I'm not sure but this might be a problem for External Users.

1 Answer

+2 votes
selected by
Best answer

I haven't seen your script, but I have to assume it is mainly a delete statement with some conditions to try to detect spam users. In general, running UPDATE and DELETE statements against the framework is a bad practice. If my assumption was accurate, this case is not an exception.

The reason for this is that there is a significant amount of cached information in Q2A. If you want to run DML against the database, you will need to make sure you're leaving the rest of Q2A consistent. This means: checking the PHP code and extracting all SQL (which in many cases is generating dynamically). Even if you manage to do so, after each Q2A upgrade you will have to check for changes in that logic and apply the differences.

In order to avoid all this, several functions have been added to the core that would take care of everything. In this case you should be using qa_delete_user($userid). So keep the conditions from your DELETE statement and turn it into a SELECT statement that return user IDs in a PHP array. Then just iterate one by one and execute that function for each of them.

Extra comment. Now you should have deleted users that require firing some additional updates. In order to fix that, just reverse the logic and take all user IDs present in the ^userpoints table that are not in the ^users table and then try to delete them with the function above (I took a glance at the code and it seems it should work without throwing warnings). Take into account all this makes sense if you are not sharing the ^users table between two or more Q2A sites!

My delete statements currently look like this:

DELETE u,p FROM qa_users u
  INNER JOIN qa_userpoints p ON u.userid = p.userid
WHERE u.emailcode != '' AND date_sub(now(), INTERVAL 8 HOUR) > u.created;
UPDATE qa_options
SET content = (SELECT count(*) FROM qa_userpoints)
WHERE title = 'cache_userpointscount';

I haven't seen negative side effects from them (yet), but if there's a better approach, I'll certainly at least try it. Is there a helper function that would allow me to determine whether or not a user has already confirmed their e-mail?
Here is some relevant code: https://github.com/q2a/question2answer/blob/947a970beb76168a3ab1f4cbd0af468a168121c6/qa-include/pages/account.php#L50-L59

In short, there is not, but you can apply a bitwise `AND 1` to the FLAGS field in the ^users table, which would look like this in SQL `flags & 1`
Okay, I'll look into that. Thanks for the information.