Dave Jarvis' Repositories

/**
 * Data Dictionary Update Script (3 of 3)
 * @author Dave Jarvis
 *
 * Ensure all tables (or views) and their columns are associated in
 * the as_dict.object_column_entity_relation table.
 *
 * PRECONDITIONS
 * The SQL generated using the previous data dictionary update scripts
 * has been executed.
 *
 * POSTCONDITIONS
 * The object_column_entity_relation table will have mapped all columns in
 * the object_column table to the entity_relation table, so long as those
 * mappings exist in the information_schema.
 */
INSERT INTO
  as_dict.object_column_entity_relation (object_column_id, entity_relation_id) 
SELECT
  oc.object_column_id,
  er.entity_relation_id
FROM
  as_dict.object_column oc,
  as_dict.entity_relation er,
  information_schema.columns isc
WHERE
  oc.column_name = isc.column_name AND
  er.object_schema = isc.table_schema AND
  er.object_name = isc.table_name AND
  NOT EXISTS (
    SELECT
      ocer.object_column_entity_relation_id
    FROM
      as_dict.object_column_entity_relation ocer
    WHERE
      ocer.object_column_id = oc.object_column_id AND
      ocer.entity_relation_id = er.entity_relation_id
  );