TimedText pages to be checked

This query is marked as a draft This query has been published by Achim55.
SQL

(SELECT CONCAT('# [[TimedText:', REPLACE (page_title,'_',' '), ']] → bad extension') AS title
 FROM page
 WHERE page_namespace = 102
 AND page_title NOT LIKE '%.srt'
 AND page_title NOT LIKE '%.vtt'
)
UNION
(SELECT CONCAT('# [[TimedText:', REPLACE (page_title,'_',' '), ']] → ',
               IF (page_len = 0, 'empty', CONCAT('length ', page_len))) AS title
 FROM page
 WHERE page_namespace = 102
 AND page_len < 33
)
UNION
(SELECT CONCAT('# [[TimedText:', REPLACE (p1.page_title, '_',' '), ']] → orphaned') AS title
 FROM page p1
 WHERE p1.page_namespace = 102
 AND (p1.page_title LIKE '%.srt' OR p1.page_title LIKE '%.vtt')
 AND NOT EXISTS (SELECT 1 FROM page p2 WHERE p2.page_namespace = 6 AND p2.page_title = REGEXP_REPLACE(p1.page_title, '\\.[^\.]+\\.(?:srt|vtt)$', ''))
)
UNION
(SELECT CONCAT('# [[TimedText talk:', REPLACE (p1.page_title, '_',' '), ']] → orphaned') AS title
 FROM page p1
 WHERE p1.page_namespace = 103
 AND (p1.page_title LIKE '%.srt' OR p1.page_title LIKE '%.vtt')
 AND p1.page_id NOT IN (116686693, 117309098)
 AND NOT EXISTS (SELECT 1 FROM page p2 WHERE p2.page_namespace = 102 AND p2.page_title = p1.page_title)
)
UNION
(SELECT CONCAT('# [[TimedText:', REPLACE (page_title, '_',' '), ']] → language code', IF (page_is_redirect = 1, ' (redirected)', '')) AS title
FROM page
WHERE page_namespace = 102
AND (page_title LIKE '%.srt' OR page_title LIKE '%.vtt')
AND REGEXP_REPLACE((REGEXP_REPLACE(page_title, '\\.(?:srt|vtt)$', '')), '^.+\\.', '')
 NOT IN ('aa','aaf-arab','aao','aaq','ab','abe','abh','abq-arab','abs','abv','ace','ace-arab','acf','acm','acq','acw','acx','acy-arab','adf','ady','ady-arab','ady-cyrl','ae','aeb','aeb-arab','aeb-latn','aec','ahr','af','agq','ak','aln','als','alt','am','ami','an','ang','ann','anp','ar','arc','arn','arp','arq','arv','ary','arz','as','ase','ast','atj','av','avk','awa','ay','az','azb','ba','bag','ban','ban-bali','bar','bas','bat-smg','bax','bbc','bbc-latn','bbj','bcc','bci','bcl','bcr','bdr','be','be-tarask','be-x-old','bem','bew','bfd','bg','bgn','bh','bho','bi','bik','bjn','bkc','bkh','bkm','blk','bm','bn','bo','bpy','bqi','bqz','br','brh','bs','btm','bto','bug','bxr','byp','byv','ca','cak','cal','cbk-zam','cdo','ce','ceb','ch','chn','cho','chr','chy','cic','ckb','clc','cnh','cnr','co','cop','cps','cpx','cpx-hans','cpx-hant','cr','crh','crh-cyrl','crh-latn','crh-ro','cs','csb','cu','cv','cy','da','dag','de','de-at','de-ch','de-formal','dga','din','diq','dsb','dtp','dty','dua','dv','dz','ee','egl','el','eml','en','en-ca','en-gb','en-us','enm','eo','es','es-419','es-es','es-formal','es-mx','et','eto','etu','eu','ewo','ext','fa','fan','fat','ff','fi','fil','fit','fiu-vro','fj','fkv','fmp','fo','fon','fr','fr-ca','fr-fr','frc','frp','frr','fur','fy','ga','gaa','gag','gan','gan-hans','gan-hant','gcr','gd','gil','gl','gld','glk','gn','gom','gom-deva','gom-latn','gor','got','gpe','grc','gsw','gu','guc','gur','guw','gv','gya','ha','hak','haw','he','hi','hif','hif-deva','hif-latn','hil','hmn','hno','ho','hr','hrx','hsb','hsn','ht','hu','hu-formal','hy','hyw','hz','ia','id','ie','ig','igl','ii','ik','ike-cans','ike-latn','ikt','ilo','inh','io','is','isu','it','iu','izh','ja','jam','jbo','jut','jv','ka','kaa','kab','kaj','kbd','kbd-cyrl','kbp','kcg','kea','ker','kg','kha','khw','ki','kiu','kj','kjh','kjp','kk','kk-arab','kk-cn','kk-cyrl','kk-kz','kk-latn','kk-tr','kl','km','kn','ko','ko-kp','koi','kos','kr','krc','kri','krj','krl','ks','ks-arab','ks-deva','ksf','ksh','ksw','ku','ku-arab','ku-latn','kum','kus','kv','kw','ky','la','lad','lb','lbe','lem','lez','lfn','lg','li','lij','liv','lki','lld','lmo','ln','lns','lo','loz','lrc','lt','ltg','lu','luo','lus','luz','lv','lzh','lzz','mad','mag','mai','map-bms','mc','mcn','mcp','mdf','mg','mh','mhn','mhr','mi','min','mk','ml','mn','mni','mnw','mo','mos','mr','mrh','mrj','ms','ms-arab','mt','mua','mus','mwl','my','myv','mzn','na','nah','nan','nan-hani','nap','naq','nb','nd','nds','nds-nl','ne','new','ng','nge','nia','niu','nl','nl-be','nl-informal','nla','nmg','nmz','nn','nnh','nnz','no','nod','nog','non','nov','nqo','nr','nrm','nso','nv','ny','nyn','nys','oc','och','oj','ojb','olo','om','or','os','osa','osa-latn','ota','pa','pag','pam','pap','pap-aw','pau','pcd','pcm','pdc','pdt','pfl','pi','pih','pl','plm','pms','pnb','pnt','prg','ps','pt','pt-br','pt-pt','pwn','qu','quc','qug','rap','rar','rgn','rif','rki','rm','rmc','rmf','rmo','rmy','rn','ro','roa-rup','roa-tara','rsk','ru','rue','rup','ruq','ruq-cyrl','ruq-grek','ruq-latn','rw','rwr','ryu','sa','sah','sat','sc','scn','sco','sd','sdc','sdh','se','se-fi','se-no','se-se','sei','ses','sg','sgs','sh','sh-cyrl','sh-latn','shi','shi-latn','shi-tfng','shn','shy','shy-latn','si','sid','simple','sjd','sje','sju','sk','skr','skr-arab','sl','sli','sm','sma','smj','smn','sms','sn','snk','so','sq','sr','sr-ec','sr-el','srn','sro','srq','ss','st','stq','sty','su','sv','sw','swb','syc','syl','szl','szy','ta','tay','tcy','tdd','te','tet','tg','tg-cyrl','tg-latn','th','ti','tk','tl','tlh','tli','tly','tly-cyrl','tn','to','tog','tok','tokipona','tpi','tr','tru','trv','ts','tt','tt-cyrl','tt-latn','ttj','ttt','tum','tvl','tvu','tw','ty','tyv','tzl','tzm','udm','ug','ug-arab','ug-latn','uk','ur','uz','uz-cyrl','uz-latn','ve','vec','vep','vi','vlca','vls','vmf','vmw','vo','vot','vrm','vro','vut','wa','wal','war','wbp','wes','wls','wo','wuu','wuu-hans','wuu-hant','wya','xal','xh','xmf','xsy','yao','yap','yas','yat','yav','ybb','ydd','yi','yo','yrl','yue','yue-hans','yue-hant','yux','za','zap','zea','zgh','zh','zh-classical','zh-cn','zh-hans','zh-hant','zh-hk','zh-min-nan','zh-mo','zh-my','zh-sg','zh-tw','zh-yue','zu','zza')
AND page_id NOT IN (33184253, #TimedText:National Banner Song.ogg.Hanyu Pinyin.srt, chinese-based pinyin
                    136512973 #TimedText:Georgian national anthem.oga.Guiano (PT).srt, portuguese-based creole)
)/*
UNION
(SELECT CONCAT('# [[TimedText:', REPLACE (p1.page_title,'_',' '), "]] → file page is redirect, tt page isn't") AS title
 FROM page p1 JOIN page p2 ON p2.page_title = REGEXP_REPLACE(p1.page_title, '\.[^\.]+\.(?:srt|vtt)$', '')
 WHERE p1.page_namespace = 102
 AND p1.page_is_redirect = 0
 AND p2.page_is_redirect = 1
 AND p2.page_namespace = 6
)
UNION
(SELECT CONCAT('# [[:{{ns:', page_namespace, '}}:', REPLACE (page_title,'_',' '), ']] → wrong ns?') AS title
 FROM page
 WHERE (page_title LIKE '%.srt' OR page_title LIKE '%.vtt')
 AND page_title NOT LIKE 'Deletion\_requests/%'
 AND page_namespace NOT IN (2, 3, 102, 103)
)*/
ORDER BY title
LIMIT 500;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
