Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Achim55
.
Toggle Highlighting
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 # TT 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 # TT talk page empty? (SELECT CONCAT('# [[TimedText talk:', REPLACE (page_title,'_',' '), ']] → empty') AS title FROM page WHERE page_namespace = 103 AND page_len < 3 ) 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','mad-arab','mag','mai','man-arab','map-bms','mc','mcn','mcp','mdf','mfe','mg','mge-arab','mgp','mh','mhj','mhn','mhr', 'mhv','mi','mic','mik','min','mk','mki-arab','ml','mn','mne-arab','mni','mnw','mo','mos','mqm','mr','mrh','mrj','mrq','ms', 'ms-arab','mt','mtr','mua','mus','mve','mvy','mwl','mwr','mwr-arab','my','myv','mzb','mzn', 'na','nah','nan','nan-hani','nap','naq','nb','nd','ndo','nds','nds-nl','ne','new','ng','nge','nia','niu','njo','nl','nl-be', 'nl-informal','nla','nmg','nmz','nn','nnh','nnz','no','nod','nog','non','nov','nqo','nr','nrf','nrf-fr','nrf-gg','nrf-je', 'nrm','nsk','nso','nv','ny','nyn','nys', 'oc','och','oj','ojb','ojc','ojg','ojs','ojw','oka','olo','om','oma','or','os','osa','osa-latn','ota','otw', 'pa','pa-arab','pag','pam','pap','pap-aw','pau','pbt','pbu','pcd','pcm','pdc','pdt','pfl','pga','phl','phn','phv','pi','pih', 'pl','plm','pms','pmt','pnb','pnt','prd','prg','prs','ps','pst','pt','pt-br','pt-cv','pt-pt','pwn', 'qu','quc','qug', 'raj-arab','rap','rar','ray','rcf','rgn','rif','rif-arab','rki','rkt','rm','rmc','rme','rmf','rmo','rmq','rmt-arab','rmy','rmz', 'rn','ro','roa-rup','roa-tara','rsk','ru','rue','rup','ruq','ruq-cyrl','ruq-grek','ruq-latn','rw','rwr','ryu', 'sa','sah','sak','sat','sc','scd','scl','scn','sco','sd','sdb','sdc','sdf','sdh','se','se-fi','se-no','se-se','sei','ses','sg', 'sgs','sh','sh-cyrl','sh-latn','shi','shi-arab','shi-latn','shi-tfng','shn','shu','shy','shy-arab','shy-latn','si','sid', 'simple','sip','sjd','sje','sju','sk','skr','skr-arab','sl','sli','sm','sma','smj','smn','sms','sn','snk','so','sq','sr', 'sr-cyrl','sr-ec','sr-el','sr-latn','srn','sro','srq','ss','st','sto','stq','sty','su','su-arab','suz','sv','sw','sw-arab', 'swb','swh','swv','syc','syl','szl','szy', 'ta','taq','tay','tcy','tdd','te','tet','tg','tg-arab','tg-cyrl','tg-latn','th','thl','thv','thz','ti','tig-arab','tk','tk-arab', 'tl','tlh','tli','tly','tly-arab','tly-cyrl','tmh','tn','to','tog','tok','tokipona','tpi','tr','trp','tru','trv','trw','ts','tt', 'tt-arab','tt-cyrl','tt-latn','ttj','ttq','ttt','tum','tun','tvl','tvu','tw','txj-arab','ty','tyv','tzl','tzm', 'udm','ug','ug-arab','ug-latn','uk','umb','ur','uve','uz','uz-arab','uz-cyrl','uz-latn','uzs', 've','vec','vep','vi','vic','vlca','vls','vmf','vmw','vo','vot','vrm','vro','vut', 'wa','wal','war','wbl','wbp','wes','win','wlc','wlc-arab','wls','wne','wni','wni-arab','wo','wo-arab','wry','wuu','wuu-hans', 'wuu-hant','wya', 'xac','xal','xh','xhe','xmf','xng-arab','xnr','xpq','xpu','xsy','xtg', 'yao','yap','yas','yat','yav','ybb','ydd','ydg','yi','yih','yo','yrl','yue','yue-hans','yue-hant','yux', 'za','zap','zdj','zdj-arab','zea','zen','zgh','zh','zh-classical','zh-cn','zh-hans','zh-hant','zh-hk','zh-min-nan','zh-mo', 'zh-my','zh-sg','zh-tw','zh-wuu','zh-yue','zu','zun','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
.
Checking query status...