User:SQL/ACC

From Wikipedia, the free encyclopedia

This is my roadmap, and status report, for the WP:ACC replacement program. This is where I am right now. (Program is presently 959 lines, and 45K!)

[graph/hist] Toolserver status
Last update 06:00, 12 June 2008 (UTC)
MySQL vandale up
MySQL zedler up
MySQL yarrow up
Replag s1 0h 0m 1s
Replag s2 0h 0m 0s
Replag s3 0h 0m 7s
More information...
Tools
{{Fixed}} Fixed
{{Done}}Y Done
{{Notdone}}N Not done
{{Wontfix}} Won't fix
{{Doing}} Doing...
WP:ACC
WT:ACC
Turn ACC off!

Contents

[edit] TODO

  • User profiles
  • Who's online
  • Page the logfiles
  • Editable (or not) FAQ

[edit] URGENT Bugs

[edit] What works

  • Login/Logout
  • Request handling / validation
  • Defer to user/admin
  • Defer logging
  • User registration ETA This morning, 4/3/08 SQLQuery me! 02:18, 3 April 2008 (UTC) Y Done SQLQuery me! 03:00, 3 April 2008 (UTC)
  • User moderation ETA Today, 4/3/07 SQLQuery me! 03:04, 3 April 2008 (UTC) Y Done SQLQuery me! 03:35, 3 April 2008 (UTC)
  • Request close as done Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Request close as username policy Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Request close as technical Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Request close as dup Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Request close as similar Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Request close as invalid Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Request re-opening Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Re-open logging Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Close logging Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • Moderation logging Y Done SQLQuery me! 04:44, 3 April 2008 (UTC)
  • SQL Queries sanitized against SQL Injection attacks. Y Done SQLQuery me! 05:40, 3 April 2008 (UTC)
  • Populated acc_emails, and message management now works. (Regular users may view the messages to be sent, admins may modify them) Y Done SQLQuery me! 06:20, 3 April 2008 (UTC)
  • Last 5 closed requests (on main page) Y Done SQLQuery me! 06:44, 3 April 2008 (UTC)
  • Error message cleanup Doing... 11:27, 4 April 2008 (UTC) Y Done SQLQuery me! 11:51, 4 April 2008 (UTC)
  • Dup detection (another open request for the same e-mail addy or username, or by the same IP) Y Done SQLQuery me! 12:02, 4 April 2008 (UTC)
  • E-mail system Doing... 12:12, 4 April 2008 (UTC) Y Done SQLQuery me! 12:28, 4 April 2008 (UTC)
  • The entire request page, and all messages are admin-customizable, via messagemgmt. SQLQuery me! 12:32, 4 April 2008 (UTC)
  • IP Ban control Y Done SQLQuery me! 14:06, 4 April 2008 (UTC)
  • E-Mail ban control Y Done SQLQuery me! 14:06, 4 April 2008 (UTC)
  • Periodic status e-mail to the mail list Y Done SQLQuery me! 06:54, 5 April 2008 (UTC)
  • Welcoming (and welcome preferences. I expect to allow each user to individually specify wether and how they want the users they create to be welcomed). Y Done SQLQuery me! 16:12, 9 April 2008 (UTC)

[edit] Feature Requests

[edit] Open

PLEASE DO NOT ADD REQUESTS HERE. PLEASE USE MY TS JIRA TRACKER! https://jira.ts.wikimedia.org/browse/SQLDB

  • "Custom" Close reasons (User-supplied) SQLQuery me! 12:11, 4 April 2008 (UTC)
  • The ability to send global notices to users when they log on to the system - Unsure about the use, but it could be useful to tell people of changes, and for example to set the welcome preferences etc etc. --The Helpful One (Review) 13:05, 11 April 2008 (UTC)
    • Y Done SQLQuery me! 05:15, 11 May 2008 (UTC)
      • Y Done' with an added bonus:' I have successfully added a javascript dismiss button to the sitenotice. While this has made editing the sitenotice a little tricky, instructions are at the top of the sitenotice editing form. --FastLizard4 (Talk•Index•Sign) 21:56, 11 May 2008 (UTC)
  • A link back to the main page from any of the subpages (IE: Tool pages, welcome management, etc). Q T C 00:34, 12 April 2008 (UTC)
    Click "Request maintenence"... I ought to rename that to "home" :P SQLQuery me! 12:55, 14 April 2008 (UTC)
  • Kinda in addition to the above, non-idempotent actions shouldn't use GET. 1 Person with GWA would completely hose this tool. Q T C 00:41, 12 April 2008 (UTC)
    Yeah, actually I've had to disable my squid for that URL, as it was causing minor issues. I'll work on that in a moment. However, the "done" etc links, I'm not sure how else to do those, but with _GET.... Suggestions? (__without__ using a form....) SQLQuery me! 00:44, 12 April 2008 (UTC)
    forms and radio boxes? Would require a minor script change. Q T C 01:13, 12 April 2008 (UTC)
    Oops, why no forms? Q T C 04:45, 12 April 2008 (UTC)
    Because, they prevent a user from 'working in tabs', which, for me is much more efficient. SQLQuery me! 12:55, 14 April 2008 (UTC)
  • Time that requests were submitted, useful for backlog length checking. GDonato (talk) 21:32, 29 April 2008 (UTC)
  • When closing request in a certain section, jump back to that section after closing the request, so the user does not have to automatically jump back. Stwalkerster [ talk ] 22:04, 9 May 2008 (UTC)

[edit] Completed

  • Reverse the sorting of the logs, and put them in pages (something like they are on enwiki), as the current log page is ridiculous Stwalkerster [ talk ] 17:24, 10 May 2008 (UTC)
  • System to automatically reject any wikimedia-based email addresses - if they have a wikimedia email, it's likely they are either office or a dev, both of which should have global accounts already. Stwalkerbot 23:07, 2 May 2008 (UTC)
  • "Forgotten password" system Y Done SQLQuery me! 16:30, 10 April 2008 (UTC)
  • Links to sections at the top of the page, ie. A link at the top which which will jump to a part of the page, like MediaWiki's tables of contents work. Stwalkerster [ talk ] 11:52, 3 April 2008 (UTC)
    I'll implement this soon. SQLQuery me! 12:02, 4 April 2008 (UTC)
    Y Done SQLQuery me! 12:43, 4 April 2008 (UTC)

[edit] Bug reports

PLEASE DO NOT ADD REQUESTS HERE. PLEASE USE MY TS JIRA TRACKER! https://jira.ts.wikimedia.org/browse/SQLDB

  • The logs appear to not be working correctly currently (nothing appears to be logged since yesterday). FunPika 18:09, 10 May 2008 (UTC)
    • Actually, since 10 mins ago :( In converting over to a new format, I accidentally nuked all the log dates. Good news is, log sorting works now. Bad news is, date/time's prior to today are gone forever. SQLQuery me! 19:04, 10 May 2008 (UTC)

[edit] Closed bugs

  • Possibility to create accounts for the admin interface with no username/password/email it appears. Stwalkerster [ talk ] 19:56, 4 April 2008 (UTC)
  • :Could you provide me with more detail? (By e-mail is fine, or, on IRC later) SQL's Alternate Account (talk) 02:44, 5 April 2008 (UTC)Figured it out, I'll have it do basic sanity checks on reg, thanks! SQL's Alternate Account (talk) 02:44, 5 April 2008 (UTC)
    Fixed, register form now requires that data be supplied. SQLQuery me! 05:22, 5 April 2008 (UTC)
  • Perhaps to prevent accidentally closing a request twice and sending duplicate e-mails (e.g. if a request is closed and the resulting page is later refreshed for whatever reason), the URL for closing the requests should include the current status, so that an already-closed request cannot be closed again without re-opening it first. Tra (Talk) 01:26, 5 April 2008 (UTC)
    I'll fix this when I get home in a couple hours. I'll have it query the acc_pend table for pend_status "Open" and refuse to close anything not marked as "Open". Thanks! SQL's Alternate Account (talk) 02:44, 5 April 2008 (UTC)
    Fixed, No more duplicate closes. One must now re-open the ticket, in order to close it again. SQLQuery me! 05:16, 5 April 2008 (UTC)
  • Defer to admins was broken. I missed the control key, when I saved last time, and changed "action=defer" to "action=sdefer". Fixed SQLQuery me! 05:16, 5 April 2008 (UTC)
  • This tool suffers from replication lag. If anyone knows of a way to confirm that an account exists, and ip block status, without using the TS databases, please let me know. (Parsing the creation log is out of the question, as it misses accounts created before X date. Parsing the block log is, too, due to it being a pain in the butt.) SQLQuery me! 07:04, 5 April 2008 (UTC)
    You can check if an account exists with a query similar to [1] which gives missing="" when a username is free. [2] will give information about active blocks for an IP address. Tra (Talk) 11:24, 5 April 2008 (UTC)
  • I got the following messages when approving and suspending an account:

Warning: fsockopen() [function.fsockopen]: unable to connect to 127.0.0.1:9001 (Connection refused) in /home/sql/public_html/acc/acc.php on line 452

Warning: fwrite(): supplied argument is not a valid stream resource in /home/sql/public_html/acc/acc.php on line 453

Warning: fclose(): supplied argument is not a valid stream resource in /home/sql/public_html/acc/acc.php on line 454 Stwalkerster [ talk ] 15:42, 5 April 2008 (UTC)

  • Ooops - it appears that actually, the bot died. Stwalkerster [ talk ] 15:44, 5 April 2008 (UTC)
    Fixed by disabling the bot hooks until I find a better way to do it. SQLQuery me! 18:51, 5 April 2008 (UTC)
    Fixed the rest of the way, by implementing the bot and transmissions via UDP SQLQuery me! 15:01, 7 April 2008 (UTC)
  • Per this, WODUP needs account creation fixed. Therefore, may I suggest an implementation feature of password reset? Thanks, The Helpful One (Review) 17:43, 5 April 2008 (UTC)
    Indeed, I'm pretty sure the issue is password-based. I'm going to add a 'bad password' trap when I get home (no TS access from work), then hopefully implement 'forgotten password'. May be that I need to urlencode the password string (don't presently do so). SQL's Alternate Account (talk) 00:58, 6 April 2008 (UTC)
    Fixed with the implementation on the new password and reset system. SQLQuery me! 16:30, 10 April 2008 (UTC)

[edit] SQL Structure

mysql> SHOW TABLES;
+-----------------+
| Tables_in_u_sql |
+-----------------+
| acc_emails      | 
| acc_log         | 
| acc_pend        | 
| acc_user        | 
+-----------------+
4 rows in set (0.00 sec)

[edit] acc_emails

This table is used to store the canned e-mail responses.

mysql> DESCRIBE acc_emails;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| mail_id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| mail_text  | blob         | NO   |     | NULL    |                | 
| mail_count | int(11)      | NO   |     | NULL    |                | 
| mail_desc  | varchar(255) | NO   |     | NULL    |                | 
| mail_type  | varchar(255) | NO   |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
  • mail_id - Table key
  • mail_text - The text of the canned mail
  • mail_count - Per-mail usage counters
  • mail_desc - Message description
  • mail_type - Type of message. May be used to specify email or interface presently.

[edit] acc_log

This table contains logging data.

mysql> DESCRIBE acc_log;   
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| log_id     | int(11)      | NO   | PRI | NULL    | auto_increment | 
| log_pend   | varchar(255) | NO   |     | NULL    |                | 
| log_user   | varchar(255) | NO   |     | NULL    |                | 
| log_action | varchar(255) | NO   |     | NULL    |                | 
| log_time   | varchar(255) | NO   |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
  • log_id - Table key
  • log_pend - pend_id linked to the logged event, or, user_id of the affected system user
  • log_user - Username of the user performing the action
  • log_action - The action performed
  • log_time - Timestamp

[edit] acc_pend

This table contains pending requests.

mysql> DESCRIBE acc_pend;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| pend_id     | int(11)       | NO   | PRI | NULL    | auto_increment | 
| pend_email  | varchar(512)  | NO   |     | NULL    |                | 
| pend_ip     | varchar(255)  | NO   |     | NULL    |                | 
| pend_name   | varchar(512)  | NO   |     | NULL    |                | 
| pend_cmt    | varchar(1024) | NO   |     | NULL    |                | 
| pend_status | varchar(255)  | NO   |     | NULL    |                | 
+-------------+---------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
  • pend_id - Table key
  • pend_email - E-mail supplied by requesting user
  • pend_ip - IP of the requesting user
  • pend_name - Requested username
  • pend_cmt - Comments from requesting user
  • pend_status - Status of this request

[edit] acc_user

This table contains all internal user data.

mysql> describe acc_user;
+-----------------------+---------------+------+-----+---------+----------------+
| Field                 | Type          | Null | Key | Default | Extra          |
+-----------------------+---------------+------+-----+---------+----------------+
| user_id               | int(11)       | NO   | PRI | NULL    | auto_increment | 
| user_name             | varchar(255)  | NO   |     | NULL    |                | 
| user_email            | varchar(255)  | NO   |     | NULL    |                | 
| user_pass             | varchar(255)  | NO   |     | NULL    |                | 
| user_level            | varchar(255)  | NO   |     | NULL    |                | 
| user_onwikiname       | varchar(255)  | NO   |     | NULL    |                | 
| user_welcome          | int(11)       | NO   |     | 0       |                | 
| user_welcome_sig      | varchar(4096) | NO   |     | NULL    |                | 
| user_welcome_template | varchar(1024) | NO   |     | NULL    |                | 
+-----------------------+---------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
  • user_id - Table key
  • user_name - The user's chosen name
  • user_email - The user's e-mail address
  • user_pass - The user's password (stored in a one-way cipher)
  • user_level - The user's access level (New, User, Admin, Suspended presently)
  • user_onwikiname - The user's on-wiki name.
  • user_welcome - Set welcoming on or off. 0 for off, 1 for on. Default 0
  • user_welcome_sig - The user's preferred welcome sig
  • user_welcome_template - The user's preferred welcoming template

[edit] acc_ban

This table holds e-mail addresses and IP's that are banned from the request system.

mysql> DESCRIBE acc_ban;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| ban_id     | int(11)       | NO   | PRI | NULL    | auto_increment | 
| ban_type   | varchar(255)  | NO   |     | NULL    |                | 
| ban_target | varchar(1024) | NO   |     | NULL    |                | 
| ban_user   | varchar(255)  | NO   |     | NULL    |                | 
| ban_reason | varchar(4096) | NO   |     | NULL    |                | 
| ban_date   | varchar(1024) | NO   |     | NULL    |                | 
+------------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
  • ban_id - Table key
  • ban_type - Type of ban? E-mail or IP presently
  • ban_target - IP or E-mail addy of ban target
  • ban_user - Username of admin setting ban
  • ban_reason - Why?
  • ban_date - Time ban was set

[edit] acc_welcome

This table holds pending and closed welcomes.

mysql> describe acc_welcome;
+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| welcome_id       | int(11)       | NO   | PRI | NULL    | auto_increment | 
| welcome_uid      | int(11)       | NO   |     | NULL    |                | 
| welcome_user     | varchar(1024) | NO   |     | NULL    |                | 
| welcome_sig      | varchar(4096) | NO   |     | NULL    |                | 
| welcome_status   | varchar(96)   | NO   |     | NULL    |                | 
| welcome_pend     | int(11)       | NO   |     | NULL    |                | 
| welcome_template | varchar(2048) | NO   |     | NULL    |                | 
+------------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
  • welcome_id - Table key
  • welcome_uid - acc_user / user_id of the user that requested the operation
  • welcome_user - Username of the user to be welcomed
  • welcome_sig - The welcoming user's signature
  • welcome_status - The status of this operation
  • welcome_template - The template to use
  • welcome_pend - acc_pend / pend_id of the closed request.