Export SQL and results for the GBIF redirects

export.sql

-- Create the view using this scrip and then issue the following SQL
-- SELECT source, target FROM view_export_redirects WHERE source!=target GROUP BY source,target;

CREATE VIEW view_export_redirects AS

-- Export redirects for data-use (they are all aliased)
SELECT concat('/',alias) AS source, replace(source, 'node', '/data-use') AS target
FROM url_alias where alias like 'news%uses%'

UNION ALL

-- Aliases for documents
SELECT concat('/',alias) AS source, replace(source, 'node', '/document') AS target
FROM url_alias WHERE source IN (
  SELECT concat('node/', entity_id) FROM field_data_gr_resource_type where gr_resource_type_tid=895
)

UNION ALL

-- Very old ORC URLs for documents
SELECT replace(gr_url_value, 'http://www.gbif.org', '') AS source, concat('/document/', entity_id) AS target
FROM field_data_gr_url
WHERE gr_url_value LIKE '%http://www.gbif.org/orc%' AND
entity_id IN (SELECT  entity_id FROM field_data_gr_resource_type where gr_resource_type_tid=895)

UNION ALL

-- News items using the old legacy IDs (http://www.gbif.org/page/3109)
SELECT concat('/page/', field_legacy_nid_value) AS source, concat('/news/', entity_id) AS target
FROM field_data_field_legacy_nid
WHERE bundle = 'news'

UNION ALL

-- News items using the aliases (http://www.gbif.org/news/83368/idigbio-joins-gbif-as-organizational-member)
SELECT concat('/',alias) AS source, replace(source, 'node', '/news') AS target
FROM url_alias
WHERE (alias LIKE '%newsroom%'
          AND alias NOT LIKE '%/uses/%'
          AND alias NOT LIKE '%/used/%'
          AND alias NOT IN ('newsroom/subscribe', 'newsroom/newsletter', 'newsroom/contact', 'newsroom/opportunities', 'newsroom/consultations'))
  OR (alias LIKE '%news/%')

UNION ALL

-- Presentations using the aliases
SELECT concat('/',alias) AS source, replace(source, 'node', '/document') AS target
FROM url_alias WHERE source IN (
  SELECT concat('node/', entity_id) FROM field_data_gr_resource_type where gr_resource_type_tid=987
)

UNION ALL

-- old ORC URLs for presentations
SELECT replace(gr_url_value, 'http://www.gbif.org', '') AS source, concat('/document/', entity_id) AS target
FROM field_data_gr_url
WHERE gr_url_value LIKE '%http://www.gbif.org/orc%' AND
entity_id IN (SELECT  entity_id FROM field_data_gr_resource_type where gr_resource_type_tid=987)

UNION ALL

-- Projects all seem to be nicely aliased
SELECT concat('/',alias) AS source, replace(source, 'node', '/project') AS target
FROM url_alias
WHERE alias LIKE '%project/%'

UNION ALL

-- Tools seemingly don't have ORC URLs but instead suggested citing by the original URL
SELECT concat('/',alias) AS source, replace(source, 'node', '/tool') AS target
FROM url_alias WHERE source IN (
  SELECT concat('node/', entity_id) FROM field_data_gr_resource_type where gr_resource_type_tid=1010
);

Results.tsv

a few have duplicate entries:

/orc/?doc_id=2724
/orc/?doc_id=2973
/orc/?doc_id=4507
/orc/?doc_id=4669
/orc/?doc_id=4824
/orc/?doc_id=4884

not sure why but pretty sure it might cause problems...

from	to	to do
/orc/?doc_id=2724	/document/80591	duplicate - stays
/orc/?doc_id=4669	/document/80767	duplicate - stays
/orc/?doc_id=4824	/document/80795	duplicate - stays
/orc/?doc_id=4884	/document/80798	duplicate - stays
/orc/?doc_id=2973	/document/81786	duplicate - stays
/orc/?doc_id=4507	/document/81852	duplicate - stays
/orc/?doc_id=2724	/document/80582	duplicate to be removed
/orc/?doc_id=4669	/document/80750	duplicate to be removed
/orc/?doc_id=4824	/document/80796	duplicate to be removed
/orc/?doc_id=2973	/document/81787	duplicate to be removed
/orc/?doc_id=2973	/document/81788	duplicate to be removed
/orc/?doc_id=4507	/document/81856	duplicate to be removed
/orc/?doc_id=4884	/document/81893	duplicate to be removed