hrwiki – nepatrolirane promjene po datumu

SQL

# promjene u danu iz sljedećeg parametra koje nisu ophođene i nisu bile uklonjene
SET @from = "20231015";


SET @to   = DATE_FORMAT(@from + INTERVAL 1 DAY, "%Y%m%d");
SET @patrol_max   = DATE_FORMAT(@from + INTERVAL 32 DAY, "%Y%m%d"); /*patrolirati se može samo tridesetak dana od promjene*/

SELECT DISTINCT page_title, actor_name, rev_id, rev_timestamp, rev_actor FROM revision
JOIN actor ON actor_id = rev_actor 
JOIN page ON page_id = rev_page
LEFT JOIN user_groups ON ug_user = actor_user

WHERE 
    /*actor_user is NOT NULL AND */
    rev_timestamp BETWEEN @from AND @to
AND ug_group is null
AND rev_id NOT IN (SELECT ct_rev_id FROM change_tag WHERE ct_rev_id=rev_id AND ct_tag_id IN (59, 7, 60)) -- 59:uklonjeno uređivanje; 7:brzo uklanjanje/globalni uklanjači; 60:global replace
AND rev_id NOT IN (SELECT SUBSTRING(log_params FROM 26 FOR 7) FROM logging WHERE log_timestamp>@from AND log_timestamp <= @patrol_max AND log_action="patrol")
;

/*
SELECT page_title, actor_name, rev_id, rev_timestamp, rev_actor FROM revision
JOIN actor ON actor_id = rev_actor 
JOIN page ON page_id = rev_page
LEFT JOIN user_groups ON ug_user = actor_user
WHERE 
    rev_timestamp BETWEEN @from AND @to
AND ug_group is null
AND rev_id NOT IN (SELECT SUBSTRING(log_params FROM 26 FOR 7) FROM logging WHERE log_timestamp>@from AND log_timestamp <= @patrol_max AND log_action="patrol")
;
*/
SQL

Checking query status...
