Talk:Wiki labels/Work log/2015-10-04

Sunday, October 4, 2015

Today, I'm cleaning up old tasks from stale worksets for trwiki.

u_wikilabels=> \dt
                List of relations
 Schema |       Name       | Type  |    Owner     
--------+------------------+-------+--------------
 public | campaign         | table | u_wikilabels
 public | event            | table | u_wikilabels
 public | label            | table | u_wikilabels
 public | task             | table | u_wikilabels
 public | workset          | table | u_wikilabels
 public | workset_task     | table | u_wikilabels
 public | workset_task_bak | table | u_wikilabels
(7 rows)

u_wikilabels=> select * from campaign where active;

Looks like we're campaign_id = 5

u_wikilabels=> select task.* from task where campaign_id = 5 limit 10;
   id   | campaign_id |         data         
--------+-------------+----------------------
 160077 |           5 | {"rev_id": 15061842}
 160078 |           5 | {"rev_id": 15237598}
 160079 |           5 | {"rev_id": 14360243}
 160080 |           5 | {"rev_id": 15365128}
 160081 |           5 | {"rev_id": 15157926}
 160082 |           5 | {"rev_id": 14825444}
 160083 |           5 | {"rev_id": 14456948}
 160084 |           5 | {"rev_id": 14385133}
 160085 |           5 | {"rev_id": 14970202}
 160086 |           5 | {"rev_id": 15131011}
(10 rows)

u_wikilabels=> select task.* from task LEFT JOIN label ON task_id = task.id where campaign_id = 5 and task_id IS NULL limit 10;
   id   | campaign_id |         data         
--------+-------------+----------------------
 160120 |           5 | {"rev_id": 15298398}
 160365 |           5 | {"rev_id": 15032400}
 160499 |           5 | {"rev_id": 15066232}
 160530 |           5 | {"rev_id": 14323534}
 160587 |           5 | {"rev_id": 14822989}
 160622 |           5 | {"rev_id": 15363599}
 160955 |           5 | {"rev_id": 15128638}
 161205 |           5 | {"rev_id": 14586423}
 161578 |           5 | {"rev_id": 14635788}
 161829 |           5 | {"rev_id": 14843210}
(10 rows)

u_wikilabels=> select task.* from task LEFT JOIN label ON task_id = task.id where campaign_id = 5 and task_id IS NULL;
u_wikilabels=> select count(*) from task LEFT JOIN label ON task_id = task.id where campaign_id = 5 and task_id IS NULL;
 count 
-------
    76
(1 row)

So we have 76 outstanding revisions to label. How many of them are claimed in worksets?

                                             ^
u_wikilabels=> \d workset
                                      Table "public.workset"
   Column    |            Type             |                      Modifiers                       
-------------+-----------------------------+------------------------------------------------------
 id          | integer                     | not null default nextval('workset_id_seq'::regclass)
 campaign_id | integer                     | 
 user_id     | integer                     | 
 created     | timestamp without time zone | 
 expires     | timestamp without time zone | 
Indexes:
    "workset_pkey" PRIMARY KEY, btree (id)
    "workset_user" btree (user_id)

u_wikilabels=> select count(*) from task LEFT JOIN label ON label.task_id = task.id INNER JOIN workset_task ON workset.task_id = task.id where campaign_id = 5 and label.task_id IS NULL;
ERROR:  missing FROM-clause entry for table "workset"
LINE 1: ...abel.task_id = task.id INNER JOIN workset_task ON workset.ta...
                                                             ^
u_wikilabels=> select count(*) from task LEFT JOIN label ON label.task_id = task.id INNER JOIN workset_task ON workset_task.task_id = task.id where campaign_id = 5 and label.task_id IS NULL;
 count 
-------
    66
(1 row)

66. OK. Let's clean those up.

u_wikilabels=> DELETE FROM workset_task WHERE task_id IN (select task.id from task LEFT JOIN label ON label.task_id = task.id INNER JOIN workset_task ON workset_task.task_id = task.id where campaign_id = 5 and label.task_id IS NULL);
DELETE 66
u_wikilabels=> select task.id from task LEFT JOIN label ON label.task_id = task.id INNER JOIN workset_task ON workset_task.task_id = task.id where campaign_id = 5 and label.task_id IS NULL;
 id 
----
(0 rows)

OK. That should do it. We really need to get an admin interface together for this. --EpochFail (talk) 14:49, 4 October 2015 (UTC)