text: \set ON_ERROR_STOP 1
  
  BEGIN;
  
  CREATE OR REPLACE VIEW release_event AS
      SELECT
          release, date_year, date_month, date_day, country
      FROM (
          SELECT release, date_year, date_month, date_day, country
          FROM release_country
          UNION ALL
          SELECT release, date_year, date_month, date_day, NULL
          FROM release_unknown_country
      ) as q;
  
  -- First, construct a table (recursively) of parent -> descendant connections
  -- for areas, including an array of the path (the 'descendants' array).
  
  -- Then, find the shortest path to each type of parent by joining to area,
  -- distinct on descendant, type, and order by the length of the array of descendants.
  
  -- link type de7cc874-8b1b-3a05-8272-f3834c968fb7 is the area-area 'parent of' relation
  CREATE OR REPLACE VIEW area_containment AS
          WITH RECURSIVE area_descendants AS (
              SELECT entity0 AS parent, entity1 AS descendant, ARRAY[entity1] AS descendants
              FROM   l_area_area laa
              JOIN   link ON laa.link = link.id
              JOIN   link_type ON link.link_type = link_type.id
              WHERE  link_type.gid = 'de7cc874-8b1b-3a05-8272-f3834c968fb7'
                  UNION ALL
              SELECT entity0 AS parent, descendant, descendants || entity1
              FROM   l_area_area laa
              JOIN   link ON laa.link=link.id
              JOIN   link_type ON link.link_type = link_type.id
              JOIN   area_descendants ON area_descendants.parent = laa.entity1
              WHERE  link_type.gid = 'de7cc874-8b1b-3a05-8272-f3834c968fb7'
              AND    NOT entity0 = ANY(descendants))
          SELECT DISTINCT ON (descendant, type) descendant, area_descendants.parent, area.type, area_type.name AS type_name, descendants || area_descendants.parent AS descendant_hierarchy
          FROM   area_descendants
          JOIN   area ON area_descendants.parent = area.id
          JOIN   area_type ON area.type = area_type.id
          WHERE  area.type IN (1, 2, 3)
          ORDER BY descendant, type, array_length(descendants, 1) ASC;
  
  
  CREATE OR REPLACE VIEW event_series AS
      SELECT entity0 AS event,
             entity1 AS series,
             lrs.id AS relationship,
             link_order,
             lrs.link,
             COALESCE(text_value, '') AS text_value
      FROM l_event_series lrs
      JOIN series s ON s.id = lrs.entity1
      JOIN link l ON l.id = lrs.link
      JOIN link_type lt ON (lt.id = l.link_type AND lt.gid = '707d947d-9563-328a-9a7d-0c5b9c3a9791')
      LEFT OUTER JOIN link_attribute_text_value latv ON (latv.attribute_type = s.ordering_attribute AND latv.link = l.id)
      ORDER BY series, link_order;
  
  CREATE OR REPLACE VIEW recording_series AS
      SELECT entity0 AS recording,
             entity1 AS series,
             lrs.id AS relationship,
             link_order,
             lrs.link,
             COALESCE(text_value, '') AS text_value
      FROM l_recording_series lrs
      JOIN series s ON s.id = lrs.entity1
      JOIN link l ON l.id = lrs.link
      JOIN link_type lt ON (lt.id = l.link_type AND lt.gid = 'ea6f0698-6782-30d6-b16d-293081b66774')
      LEFT OUTER JOIN link_attribute_text_value latv ON (latv.attribute_type = s.ordering_attribute AND latv.link = l.id)
      ORDER BY series, link_order;
  
  CREATE OR REPLACE VIEW release_series AS
      SELECT entity0 AS release,
             entity1 AS series,
             lrs.id AS relationship,
             link_order,
             lrs.link,
             COALESCE(text_value, '') AS text_value
      FROM l_release_series lrs
      JOIN series s ON s.id = lrs.entity1
      JOIN link l ON l.id = lrs.link
      JOIN link_type lt ON (lt.id = l.link_type AND lt.gid = '3fa29f01-8e13-3e49-9b0a-ad212aa2f81d')
      LEFT OUTER JOIN link_attribute_text_value latv ON (latv.attribute_type = s.ordering_attribute AND latv.link = l.id)
      ORDER BY series, link_order;
  
  CREATE OR REPLACE VIEW release_group_series AS
      SELECT entity0 AS release_group,
             entity1 AS series,
             lrgs.id AS relationship,
             link_order,
             lrgs.link,
             COALESCE(text_value, '') AS text_value
      FROM l_release_group_series lrgs
      JOIN series s ON s.id = lrgs.entity1
      JOIN link l ON l.id = lrgs.link
      JOIN link_type lt ON (lt.id = l.link_type AND lt.gid = '01018437-91d8-36b9-bf89-3f885d53b5bd')
      LEFT OUTER JOIN link_attribute_text_value latv ON (latv.attribute_type = s.ordering_attribute AND latv.link = l.id)
      ORDER BY series, link_order;
  
  CREATE OR REPLACE VIEW work_series AS
      SELECT entity1 AS work,
             entity0 AS series,
             lsw.id AS relationship,
             link_order,
             lsw.link,
             COALESCE(text_value, '') AS text_value
      FROM l_series_work lsw
      JOIN series s ON s.id = lsw.entity0
      JOIN link l ON l.id = lsw.link
      JOIN link_type lt ON (lt.id = l.link_type AND lt.gid = 'b0d44366-cdf0-3acb-bee6-0f65a77a6ef0')
      LEFT OUTER JOIN link_attribute_text_value latv ON (latv.attribute_type = s.ordering_attribute AND latv.link = l.id)
      ORDER BY series, link_order;
  
  COMMIT;
  
  -- vi: set ts=4 sw=4 et :
  