#! /bin/bash
#$ -N DBQ-89
#$ -hard
#$ -l sqlprocs-s5=1
WIKI="dewiki"
SQL="
CREATE TEMPORARY TABLE temp (t_pid INT PRIMARY KEY, t_pt VARCHAR(255));
INSERT INTO temp
 SELECT page_id, page_title
  FROM ${WIKI}_p.page p
   LEFT JOIN ${WIKI}_p.pagelinks ON p.page_namespace = pl_namespace AND p.page_title=pl_title
   LEFT JOIN ${WIKI}_p.templatelinks ON p.page_namespace = tl_namespace AND p.page_title=tl_title
   LEFT JOIN ${WIKI}_p.redirect ON p.page_namespace = rd_namespace AND p.page_title=rd_title
  WHERE p.page_namespace=0 AND page_is_redirect=0 AND pl_namespace IS NULL AND tl_namespace IS NULL AND rd_namespace IS NULL;
CREATE TEMPORARY TABLE tempdab(dab_tltitle INT PRIMARY KEY);
INSERT INTO tempdab
 SELECT pl_title
  FROM ${WIKI}_p.page
   INNER JOIN ${WIKI}_p.pagelinks ON page_id=pl_from
  WHERE page_namespace=8 AND page_title='Disambiguationspage' AND pl_namespace=10;
CREATE TEMPORARY TABLE tempdel(td_pid INT PRIMARY KEY);
INSERT IGNORE INTO tempdel
 SELECT t_pid
  FROM temp
   INNER JOIN ${WIKI}_p.templatelinks ON t_pid=tl_from
   INNER JOIN tempdab ON tl_title = dab_tltitle
  WHERE tl_namespace=10;
DELETE a
 FROM temp a
  INNER JOIN tempdel b ON t_pid=td_pid;
SELECT REPLACE(t_pt,'_',' ')
 FROM temp
 ORDER BY t_pt;"
mysql -wBN -h$WIKI-p.db u_$USER -e "$SQL" > ${HOME}/public_html/sql/DBQ-89-${WIKI}-LonelyPages.txt
SQL="
CREATE TEMPORARY TABLE temp (pid INT, pt VARCHAR(255), plt VARCHAR(255), INDEX(pt), INDEX(plt));
INSERT INTO temp
 SELECT page_id, page_title, pl_title
 FROM ${WIKI}_p.page
  INNER JOIN ${WIKI}_p.pagelinks ON pl_from = page_id
 WHERE page_namespace = 0 AND page_is_redirect = 0 AND pl_namespace = 0;
SELECT CONCAT(REPLACE(plt,'_',' '),',',COUNT(*))
 FROM temp
  LEFT JOIN ${WIKI}_p.page ON page_namespace = 0 AND plt = page_title
 WHERE page_namespace IS NULL
 GROUP BY plt
 HAVING COUNT(*) > 300
 ORDER BY COUNT(*) DESC
 LIMIT 5000;"
mysql -wBN -h$WIKI-p.db u_$USER -e "$SQL"  > ${HOME}/public_html/sql/DBQ-89-${WIKI}-Redlinks.txt