SQL
x
USE hewiki_p;
# page - from, rd - to
SELECT
CASE page_namespace WHEN -2 THEN "מדיה" WHEN -1 THEN "מיוחד" WHEN 0 THEN ":" WHEN 1 THEN "שיחה" WHEN 2 THEN "משתמש"
WHEN 3 THEN "שיחת משתמש" WHEN 4 THEN "ויקיפדיה" WHEN 5 THEN "שיחת ויקיפדיה" WHEN 6 THEN "קובץ" WHEN 7 THEN "שיחת קובץ"
WHEN 8 THEN "מדיה ויקי" WHEN 9 THEN "שיחת מדיה ויקי" WHEN 10 THEN "תבנית" WHEN 11 THEN "שיחת תבנית" WHEN 12 THEN "עזרה"
WHEN 13 THEN "שיחת עזרה" WHEN 14 THEN "קטגוריה" WHEN 15 THEN "שיחת קטגוריה" WHEN 100 THEN "פורטל" WHEN 101 THEN "שיחת פורטל"
WHEN 108 THEN "ספר" WHEN 109 THEN "שיחת ספר" WHEN 118 THEN "טיוטה" WHEN 119 THEN "שיחת טיוטה" WHEN 828 THEN "יחידה"
WHEN 829 THEN "שיחת יחידה" ELSE page_namespace END AS 'מרחב ההפניה',
CASE rd_namespace WHEN -2 THEN "מדיה" WHEN -1 THEN "מיוחד" WHEN 0 THEN ":" WHEN 1 THEN "שיחה" WHEN 2 THEN "משתמש"
WHEN 3 THEN "שיחת משתמש" WHEN 4 THEN "ויקיפדיה" WHEN 5 THEN "שיחת ויקיפדיה" WHEN 6 THEN "קובץ" WHEN 7 THEN "שיחת קובץ"
WHEN 8 THEN "מדיה ויקי" WHEN 9 THEN "שיחת מדיה ויקי" WHEN 10 THEN "תבנית" WHEN 11 THEN "שיחת תבנית" WHEN 12 THEN "עזרה"
WHEN 13 THEN "שיחת עזרה" WHEN 14 THEN "קטגוריה" WHEN 15 THEN "שיחת קטגוריה" WHEN 100 THEN "פורטל" WHEN 101 THEN "שיחת פורטל"
WHEN 108 THEN "ספר" WHEN 109 THEN "שיחת ספר" WHEN 118 THEN "טיוטה" WHEN 119 THEN "שיחת טיוטה" WHEN 828 THEN "יחידה"
WHEN 829 THEN "שיחת יחידה" ELSE rd_namespace END AS 'מרחב היעד'
FROM page
JOIN redirect ON rd_from = page_id
WHERE page_namespace <> rd_namespace
AND NOT (((page_title LIKE "וק:%") OR (page_title LIKE "WP:%"))
AND page_namespace = 0 AND (rd_namespace = 4 OR rd_namespace = -1 OR rd_namespace = 8 Or rd_namespace = 12)) # shortcuts
AND NOT (page_namespace = 4 AND (page_title LIKE 'מיזמי_ויקיפדיה/%') AND rd_namespace = 0) # wikipedia projects to articles
AND NOT (page_namespace = 4 AND rd_namespace = -1) # wikipedia to special
AND NOT (page_namespace = 0 AND page_title = 'Wikipedia_embassy') # 'embassy'
AND NOT (page_namespace = 4 AND rd_namespace = 5) # wikipedia to wilipedia talk
AND NOT (page_namespace = 4 AND rd_namespace = 12) # wikipedia to help
AND NOT (page_namespace = 5 AND rd_namespace = 13) # wikipedia talk to help talk
AND NOT (page_namespace = 4 AND rd_namespace = 100) # wikipedia to portal
AND NOT (page_namespace = 5 AND rd_namespace = 101) # wikipedia talk to portal talk
AND NOT (page_namespace = 2 OR page_namespace = 3) # from user and user talk
AND NOT (page_namespace = 12 AND rd_namespace = 4) # help to wikipedia
AND NOT (page_namespace = 12 AND rd_namespace = 9) # help to media wiki talk
AND NOT (page_namespace = 100 AND rd_namespace = 4) # portal to wikipedia
AND NOT (page_namespace = 4 AND rd_namespace = 8) # wikipedia to media wiki
AND NOT (page_namespace = 5 AND rd_namespace = 9) # wikipedia talk to media wiki talk
AND NOT (page_namespace = 9 AND rd_namespace = 12) # media wiki talk to help talk
AND NOT (page_namespace = 0 AND rd_namespace = 14) # articles to category
AND NOT (page_namespace = 0 AND rd_namespace = 100) # articles to portal
AND NOT (page_namespace = 10 AND rd_namespace = 100) # template to portal
AND NOT (page_namespace = 11 AND rd_namespace = 101) # template talk to portal talk
GROUP BY page_namespace, rd_namespace;
SELECT
CASE page_namespace WHEN -2 THEN "מדיה" WHEN -1 THEN "מיוחד" WHEN 0 THEN ":" WHEN 1 THEN "שיחה" WHEN 2 THEN "משתמש"
WHEN 3 THEN "שיחת משתמש" WHEN 4 THEN "ויקיפדיה" WHEN 5 THEN "שיחת ויקיפדיה" WHEN 6 THEN "קובץ" WHEN 7 THEN "שיחת קובץ"
WHEN 8 THEN "מדיה ויקי" WHEN 9 THEN "שיחת מדיה ויקי" WHEN 10 THEN "תבנית" WHEN 11 THEN "שיחת תבנית" WHEN 12 THEN "עזרה"
WHEN 13 THEN "שיחת עזרה" WHEN 14 THEN "קטגוריה" WHEN 15 THEN "שיחת קטגוריה" WHEN 100 THEN "פורטל" WHEN 101 THEN "שיחת פורטל"
WHEN 108 THEN "ספר" WHEN 109 THEN "שיחת ספר" WHEN 118 THEN "טיוטה" WHEN 119 THEN "שיחת טיוטה" WHEN 828 THEN "יחידה"
WHEN 829 THEN "שיחת יחידה" ELSE page_namespace END AS 'מרחב ההפניה', page_title AS 'שם ההפניה',
CASE rd_namespace WHEN -2 THEN "מדיה" WHEN -1 THEN "מיוחד" WHEN 0 THEN ":" WHEN 1 THEN "שיחה" WHEN 2 THEN "משתמש"
WHEN 3 THEN "שיחת משתמש" WHEN 4 THEN "ויקיפדיה" WHEN 5 THEN "שיחת ויקיפדיה" WHEN 6 THEN "קובץ" WHEN 7 THEN "שיחת קובץ"
WHEN 8 THEN "מדיה ויקי" WHEN 9 THEN "שיחת מדיה ויקי" WHEN 10 THEN "תבנית" WHEN 11 THEN "שיחת תבנית" WHEN 12 THEN "עזרה"
WHEN 13 THEN "שיחת עזרה" WHEN 14 THEN "קטגוריה" WHEN 15 THEN "שיחת קטגוריה" WHEN 100 THEN "פורטל" WHEN 101 THEN "שיחת פורטל"
WHEN 108 THEN "ספר" WHEN 109 THEN "שיחת ספר" WHEN 118 THEN "טיוטה" WHEN 119 THEN "שיחת טיוטה" WHEN 828 THEN "יחידה"
WHEN 829 THEN "שיחת יחידה" ELSE rd_namespace END AS 'מרחב היעד', rd_title AS 'שם היעד'
FROM page
JOIN redirect ON rd_from = page_id
WHERE page_namespace <> rd_namespace
AND NOT (((page_title LIKE "וק:%") OR (page_title LIKE "WP:%"))
AND page_namespace = 0 AND (rd_namespace = 4 OR rd_namespace = -1 OR rd_namespace = 8 Or rd_namespace = 12)) # shortcuts
AND NOT (page_namespace = 4 AND (page_title LIKE 'מיזמי_ויקיפדיה/%') AND rd_namespace = 0) # wikipedia projects to articles
AND NOT (page_namespace = 0 AND rd_namespace = 100) # articles to portal
AND NOT (page_namespace = 0 AND page_title = 'Wikipedia_embassy') # 'embassy'
AND NOT (page_namespace = 4 AND rd_namespace = 5) # wikipedia to wilipedia talk
AND NOT (page_namespace = 4 AND rd_namespace = 12) # wikipedia to help
AND NOT (page_namespace = 5 AND rd_namespace = 13) # wikipedia talk to help talk
AND NOT (page_namespace = 4 AND rd_namespace = 100) # wikipedia to portal
AND NOT (page_namespace = 5 AND rd_namespace = 101) # wikipedia talk to portal talk
AND NOT (page_namespace = 2 OR page_namespace = 3) # from user and user talk
AND NOT (page_namespace = 12 AND rd_namespace = 4) # help to wikipedia
AND NOT (page_namespace = 12 AND rd_namespace = 9) # help to media wiki talk
AND NOT (page_namespace = 100 AND rd_namespace = 4) # portal to wikipedia
AND NOT (page_namespace = 4 AND rd_namespace = 8) # wikipedia to media wiki
AND NOT (page_namespace = 5 AND rd_namespace = 9) # wikipedia talk to media wiki talk
AND NOT (page_namespace = 9 AND rd_namespace = 12) # media wiki talk to help talk
AND NOT (page_namespace = 4 AND rd_namespace = -1) # wikipedia to special
AND NOT (page_namespace = 0 AND rd_namespace = 14) # articles to category
AND NOT (page_namespace = 10 AND rd_namespace = 100) # template to portal
AND NOT (page_namespace = 11 AND rd_namespace = 101) # template talk to portal talk
###
AND NOT (page_namespace = 4 AND rd_namespace = 0)
AND NOT (page_namespace = 4 AND rd_namespace = 10)
AND NOT (page_namespace = 4 AND rd_namespace = 14)
AND NOT (page_namespace = 4 AND rd_namespace = 108)
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.