TimedText pages to be checked

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

# extension .srt or .vtt?
(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
# page empty or length < 33?
(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
# orphaned TT page?
(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
# orphaned TT talk page?
(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
# valid language code?
(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','afb','agq','ajp','ak','akz','ale','aln',
  'alq','als','alt','am','ami','an','ang', 'ann','anp','apc','apd','ar','arb','arc','arn','arp','arq','ars','arv','ary','arz',
  'as','asb','ase','ast','atj','aut','auz','av','avk','avl','awa','ay','ayh','ayl','ayn','ayp','az','az-arab','azb','azj',
  'ba','bag','bal','ban','ban-bali','bar','bas','bat-smg','bax','bbc','bbc-latn','bbj','bbz','bcc','bci','bcl','bcr','bdr','be',
  'be-tarask','be-x-old','bej-arab','bem','ber','ber-arab','bew','bfd','bft','bg','bgn','bgp','bh','bho','bi','bik','bjm','bjn',
  'bjn-arab','bkc','bkh','bkm','blc','blk','bm','bms-arab','bn','bnm','bo','bpy','bqi','bqz','br','brh','brh-arab','brx','bs',
  'bsk','btm','bto','bu','buc','bug','bxr','byp','byv',
  'ca','cak','cal','cbk-zam','ccp','cdo','ce','ceb','ch','chc','chn','cho','chr','chy','cic','ciw','cja-arab','cjm-arab','ckb',
  'cku','clc','cnh','cnr','co','coc','cok','cop','cps','cpx','cpx-hans','cpx-hant','cpx-latn','cr','crh','crh-arab','crh-cyrl',
  'crh-latn','crh-ro','crj','crk','crl','crm','cro','crs','cs','csb','cu','cv','cwd','cy',
  'da','dag','dak','de','de-at','de-ch','de-formal','deh','dga','dgo','dgo-arab','dhd','din','diq','dng-arab','doi','doi-arab',
  'dsb','dtp','dty','dua','dum','dv','dyu-arab','dz',
  'ee','egl','el','eml','en','en-ca','en-gb','en-ie','en-in','en-my','en-ph','en-us','en-za','enm','eo','es','es-419','es-es',
  'es-formal','es-mx','esu','et','eto','etu','eu','ewo','ext',
  'fa','fa-af','fak','fan','fat','ff','ff-arab','ffm-arab','fi','fil','fit','fiu-vro','fj','fkv','fmp','fo','fon','fr','fr-003',
  'fr-be','fr-ca','fr-ch','fr-fr','fr-lu','fr-mc','frc','frm','frp','frr','fub','fub-arab','fuc','fuc-arab','fuf','fuf-arab',
  'fuh','fuh-arab','fui','fui-arab','fur','fuv','fuv-arab','fy',
  'ga','gaa','gag','gan','gan-hans','gan-hant','gbz-arab','gcf','gcr','gd','gda','geh','gho','gil','gju-arab','gl','gld','glk',
  'gn','gom','gom-deva','gom-latn','gor','got','gpe','grc','grt','gsw','gu','guc','gur','guw','gv','gwc','gwi','gya',
  'ha','ha-arab','hac','hak','haw','hbo','he','hi','hid','hif','hif-deva','hif-latn','hil','hmn','hnd','hne','hno','ho','hoc',
  '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','jat','jbo','jut','jv',
  'ka','kaa','kab','kaj','kbd','kbd-arab','kbd-cyrl','kbd-latn','kbl-arab','kbp','kby-arab','kcg','kcn','kea','ker','kfr','kg',
  'kha','khn','khw','ki','kiu','kj','kjh','kjp','kk','kk-arab','kk-cn','kk-cyrl','kk-kz','kk-latn','kk-tr','kl','kls-arab','km',
  'kmb','kmr','kmr-arab','kmr-cyrl','kmr-latn','kmw','kn','knc-arab','ko','ko-kp','ko-kr','koi','kos','kr','kr-arab','krc','kri',
  'krj','krl','krt-arab','kru','ks','ks-arab','ks-deva','ksf','ksh','ksw','ktl','ku','ku-arab','ku-latn','kum','kus','kv','kw',
  'ky','ky-arab',
  'la','lad','lah','lb','lbe','lem','lep','lez','lfn','lg','li','lif','lij','liv','lki','lkt','lld','lmo','ln','lns','lo','loz',
  'lrc','lrk','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','ndo','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','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
                    36702269, #TimedText:National Anthem of Russia (2000), instrumental, one verse.ogg.ru-latn.srt, transcripted ru
                   136512973) #TimedText:Georgian national anthem.oga.Guiano (PT).srt, portuguese-based creole
)/*
UNION
(SELECT CONCAT('# [[TimedText:', REPLACE (p1.page_title,'_',' '), ']] → file page is redirecting') 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
# .srt or .vtt page not in TT namespace?
(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...
