kehh/collectiveaccess_attributes_crosstab.sql

## collectiveaccess_attributes_crosstab.sql
-- This creates a mysql table which contains the values of
-- datamodel.conf so that we can use the values in constructing
-- queries

DROP TABLE IF EXISTS cmis_table_numbers;


CREATE TABLE cmis_table_numbers (
    table_name VARCHAR(64) NOT NULL UNIQUE,
    table_num  INT(11) PRIMARY KEY,
    enabled    BOOLEAN DEFAULT TRUE
);

INSERT INTO cmis_table_numbers (table_name, table_num, enabled) VALUES
    ('ca_acl', 1, TRUE),
    ('ca_application_vars', 2, TRUE),
    ('ca_attribute_values', 3, TRUE),
    ('ca_attributes', 4, TRUE),
    ('ca_change_log', 5, TRUE),
    ('ca_change_log_subjects', 6, TRUE),
    ('ca_representation_annotations_x_entities', 7, TRUE),
    ('ca_representation_annotations_x_objects', 8, TRUE),
    ('ca_representation_annotations_x_occurrences', 9, TRUE),
    ('ca_representation_annotations_x_places', 10, TRUE),
    ('ca_representation_annotations_x_vocabulary_terms', 11, TRUE),
    ('ca_collection_labels', 12, TRUE),
    ('ca_collections', 13, TRUE),
    ('ca_collections_x_collections', 14, TRUE),
    ('ca_collections_x_vocabulary_terms', 15, TRUE),
    ('ca_data_import_events', 17, TRUE),
    ('ca_data_import_items', 18, TRUE),
    ('ca_entities', 20, TRUE),
    ('ca_entities_x_collections', 21, TRUE),
    ('ca_entities_x_occurrences', 22, TRUE),
    ('ca_entities_x_places', 23, TRUE),
    ('ca_entities_x_vocabulary_terms', 24, TRUE),
    ('ca_entity_labels', 25, TRUE),
    ('ca_entities_x_entities', 26, TRUE),
    ('ca_eventlog', 27, FALSE),
    ('ca_groups_x_roles', 30, TRUE),
    ('ca_ips', 31, TRUE),
    ('ca_list_item_labels', 32, TRUE),
    ('ca_list_items', 33, TRUE),
    ('ca_list_items_x_list_items', 34, TRUE),
    ('ca_list_labels', 35, TRUE),
    ('ca_lists', 36, TRUE),
    ('ca_locales', 37, TRUE),
    ('ca_object_lot_labels', 40, TRUE),
    ('ca_metadata_element_labels', 41, TRUE),
    ('ca_metadata_elements', 42, TRUE),
    ('ca_metadata_type_restrictions', 43, TRUE),
    ('ca_multipart_idno_sequences', 44, TRUE),
    ('ca_object_labels', 50, TRUE),
    ('ca_object_lots', 51, TRUE),
    ('ca_object_lots_x_collections', 52, TRUE),
    ('ca_object_lots_x_entities', 53, TRUE),
    ('ca_object_lots_x_occurrences', 54, TRUE),
    ('ca_object_lots_x_places', 55, TRUE),
    ('ca_object_representations', 56, TRUE),
    ('ca_objects', 57, TRUE),
    ('ca_objects_x_collections', 58, TRUE),
    ('ca_objects_x_entities', 59, TRUE),
    ('ca_objects_x_object_representations', 61, TRUE),
    ('ca_objects_x_objects', 62, TRUE),
    ('ca_objects_x_occurrences', 63, TRUE),
    ('ca_objects_x_places', 64, TRUE),
    ('ca_objects_x_vocabulary_terms', 65, TRUE),
    ('ca_occurrence_labels', 66, TRUE),
    ('ca_occurrences', 67, TRUE),
    ('ca_occurrences_x_collections', 68, TRUE),
    ('ca_occurrences_x_occurrences', 69, TRUE),
    ('ca_occurrences_x_vocabulary_terms', 70, TRUE),
    ('ca_place_labels', 71, TRUE),
    ('ca_places', 72, TRUE),
    ('ca_places_x_collections', 73, TRUE),
    ('ca_places_x_occurrences', 74, TRUE),
    ('ca_places_x_places', 75, TRUE),
    ('ca_places_x_vocabulary_terms', 76, TRUE),
    ('ca_relationship_relationships', 77, TRUE),
    ('ca_relationship_type_labels', 78, TRUE),
    ('ca_relationship_types', 79, TRUE),
    ('ca_representation_annotations', 82, TRUE),
    ('ca_representation_annotation_labels', 83, TRUE),
    ('ca_object_representations_x_entities', 85, TRUE),
    ('ca_object_representations_x_occurrences', 86, TRUE),
    ('ca_object_representations_x_places', 87, TRUE),
    ('ca_storage_location_labels', 88, TRUE),
    ('ca_storage_locations', 89, TRUE),
    ('ca_task_queue', 90, TRUE),
    ('ca_user_groups', 91, TRUE),
    ('ca_user_roles', 92, TRUE),
    ('ca_users', 94, TRUE),
    ('ca_users_x_groups', 95, TRUE),
    ('ca_users_x_roles', 96, TRUE),
    ('ca_editor_ui_bundle_placements', 97, TRUE),
    ('ca_editor_ui_labels', 98, TRUE),
    ('ca_editor_ui_screen_labels', 99, TRUE),
    ('ca_editor_ui_screens', 100, TRUE),
    ('ca_editor_uis', 101, TRUE),
    ('ca_editor_uis_x_user_groups', 102, TRUE),
    ('ca_sets', 103, TRUE),
    ('ca_set_labels', 104, TRUE),
    ('ca_set_items', 105, TRUE),
    ('ca_set_item_labels', 106, TRUE),
    ('ca_sets_x_user_groups', 107, TRUE),
    ('ca_object_representation_labels', 108, TRUE),
    ('ca_item_comments', 109, TRUE),
    ('ca_item_tags', 110, TRUE),
    ('ca_items_x_tags', 111, TRUE),
    ('ca_item_views', 112, TRUE),
    ('ca_object_lots_x_vocabulary_terms', 115, TRUE),
    ('ca_object_representations_x_vocabulary_terms', 116, TRUE),
    ('ca_objects_x_storage_locations', 119, TRUE),
    ('ca_object_lots_x_storage_locations', 120, TRUE),
    ('ca_search_forms', 121, TRUE),
    ('ca_search_form_labels', 122, TRUE),
    ('ca_object_representation_multifiles', 123, TRUE),
    ('ca_bundle_displays', 124, TRUE),
    ('ca_bundle_display_placements', 125, TRUE),
    ('ca_bundle_display_labels', 126, TRUE),
    ('ca_bundle_displays_x_user_groups', 127, TRUE),
    ('ca_editor_ui_screen_type_restrictions', 131, TRUE),
    ('ca_collections_x_storage_locations', 132, TRUE),
    ('ca_loans', 133, TRUE),
    ('ca_loan_labels', 134, TRUE),
    ('ca_loans_x_objects', 135, TRUE),
    ('ca_loans_x_entities', 136, TRUE),
    ('ca_movements', 137, TRUE),
    ('ca_movement_labels', 138, TRUE),
    ('ca_movements_x_objects', 139, TRUE),
    ('ca_movements_x_object_lots', 140, TRUE),
    ('ca_movements_x_entities', 141, TRUE),
    ('ca_loans_x_movements', 143, TRUE),
    ('ca_watch_list', 144, TRUE),
    ('ca_user_notes', 145, TRUE),
    ('ca_data_import_event_log', 146, TRUE),
    ('ca_search_forms_x_user_groups', 147, TRUE),
    ('ca_search_forms_x_users', 148, TRUE),
    ('ca_bundle_displays_x_users', 149, TRUE),
    ('ca_sets_x_users', 150, TRUE),
    ('ca_editor_uis_x_users', 151, TRUE),
    ('ca_search_form_placements', 152, TRUE),
    ('ca_tours', 153, TRUE),
    ('ca_tour_labels', 154, TRUE),
    ('ca_tour_stops', 155, TRUE),
    ('ca_tour_stop_labels', 156, TRUE),
    ('ca_tour_stops_x_objects', 157, TRUE),
    ('ca_tour_stops_x_entities', 158, TRUE),
    ('ca_tour_stops_x_places', 159, TRUE),
    ('ca_tour_stops_x_occurrences', 160, TRUE),
    ('ca_tour_stops_x_collections', 161, TRUE),
    ('ca_tour_stops_x_vocabulary_terms', 162, TRUE),
    ('ca_tour_stops_x_tour_stops', 163, TRUE),
    ('ca_bookmark_folders', 164, TRUE),
    ('ca_bookmarks', 165, TRUE),
    ('ca_commerce_transactions', 166, TRUE),
    ('ca_commerce_communications', 167, TRUE),
    ('ca_commerce_orders', 169, TRUE),
    ('ca_commerce_order_items', 170, TRUE),
    ('ca_commerce_fulfillment_events', 171, TRUE),
    ('ca_object_representations_x_collections', 172, TRUE),
    ('ca_object_representations_x_storage_locations', 173, TRUE),
    ('ca_object_representations_x_object_representations', 174, TRUE),
    ('ca_entities_x_storage_locations', 177, TRUE),
    ('ca_loans_x_collections', 178, TRUE),
    ('ca_loans_x_object_lots', 179, TRUE),
    ('ca_loans_x_occurrences', 180, TRUE),
    ('ca_loans_x_places', 181, TRUE),
    ('ca_loans_x_storage_locations', 182, TRUE),
    ('ca_loans_x_vocabulary_terms', 183, TRUE),
    ('ca_loans_x_loans', 184, TRUE),
    ('ca_movements_x_collections', 185, TRUE),
    ('ca_movements_x_occurrences', 186, TRUE),
    ('ca_movements_x_places', 187, TRUE),
    ('ca_movements_x_storage_locations', 188, TRUE),
    ('ca_movements_x_vocabulary_terms', 189, TRUE),
    ('ca_movements_x_movements', 190, TRUE),
    ('ca_places_x_storage_locations', 191, TRUE),
    ('ca_occurrences_x_storage_locations', 192, TRUE),
    ('ca_storage_locations_x_vocabulary_terms', 193, TRUE),
    ('ca_storage_locations_x_storage_locations', 194, TRUE),
    ('ca_commerce_communications_read_log', 195, TRUE),
    ('ca_commerce_order_items_x_object_representations', 196, TRUE),
    ('ca_editor_ui_bundle_placement_type_restrictions', 197, TRUE),
    ('ca_editor_ui_type_restrictions', 198, TRUE),
    ('ca_object_lots_x_object_representations', 199, TRUE),
    ('ca_loans_x_object_representations', 200, TRUE),
    ('ca_movements_x_object_representations', 201, TRUE),
    ('ca_data_importers', 202, TRUE),
    ('ca_data_importer_labels', 203, TRUE),
    ('ca_data_importer_groups', 204, TRUE),
    ('ca_data_importer_items', 205, TRUE),
    ('ca_data_importer_log', 206, TRUE),
    ('ca_data_importer_log_items', 207, TRUE),
    ('ca_data_exporters', 208, TRUE),
    ('ca_data_exporter_labels', 209, TRUE),
    ('ca_data_exporter_items', 210, TRUE),
    ('ca_object_lots_x_object_lots', 211, TRUE),
    ('ca_bundle_display_type_restrictions', 212, TRUE),
    ('ca_object_representation_captions', 213, TRUE),
    ('ca_metadata_dictionary_entries', 214, TRUE),
    ('ca_metadata_dictionary_rules', 215, TRUE),
    ('ca_attribute_value_multifiles', 216, TRUE);

DROP TABLE IF EXISTS cmis_attribute_types;


CREATE TABLE cmis_attribute_types (
    datatype   TINYINT UNSIGNED PRIMARY KEY,
    type_name  VARCHAR(64) NOT NULL UNIQUE,
    enabled    BOOLEAN DEFAULT TRUE,
    source_table_name VARCHAR(64)
);
ALTER TABLE cmis_attribute_types ADD INDEX (type_name), ADD INDEX (source_table_name);

REPLACE INTO cmis_attribute_types (datatype, type_name, enabled, source_table_name) VALUES
    (0, 'Container', TRUE, NULL),
    (1, 'Text', TRUE, NULL),
    (2, 'DateRange', TRUE, NULL),
    (3, 'List', TRUE, NULL),
    (4, 'Geocode', TRUE, NULL),
    (5, 'Url', TRUE, NULL),
    (6, 'Currency', TRUE, NULL),
    (7, 'was ''Relationship'' attribute type', FALSE, NULL),
    (8, 'Length', TRUE, NULL),
    (9, 'Weight', TRUE, NULL),
    (10, 'TimeCode', TRUE, NULL),
    (11, 'Integer', TRUE, NULL),
    (12, 'Numeric', TRUE, NULL),
    (13, 'LCSH', TRUE, NULL),
    (14, 'GeoNames', TRUE, NULL),
    (15, 'File', TRUE, NULL),
    (16, 'Media', TRUE, NULL),
    (17, 'was ''Place'' attribute type', FALSE, NULL),
    (18, 'was ''Occurrence'' attribute type', FALSE, NULL),
    (19, 'Taxonomy', TRUE, NULL),
    (20, 'InformationService', TRUE, NULL),
    (21, 'ObjectRepresentations', TRUE, 'ca_object_representations'),
    (22, 'Entities', TRUE, 'ca_entities'),
    (23, 'Places', TRUE, 'ca_places'),
    (24, 'Occurrences', TRUE, 'ca_occurrences'),
    (25, 'Collections', TRUE, 'ca_collections'),
    (26, 'StorageLocations', TRUE, 'ca_storage_locations'),
    (27, 'Loans', TRUE, 'ca_loans'),
    (28, 'Movements', TRUE, 'ca_movements'),
    (29, 'Objects', TRUE, 'ca_objects'),
    (30, 'ObjectLots', TRUE, 'ca_object_lots');
DROP TABLE IF EXISTS cmis_wanted_columns;
CREATE TABLE cmis_wanted_columns (
    type_name   VARCHAR(64) NOT NULL,
    column_name VARCHAR(64) NULL NULL,
    source_table_name  VARCHAR(64)
);
ALTER TABLE cmis_wanted_columns ADD PRIMARY KEY (type_name, column_name, source_table_name);
REPLACE INTO cmis_wanted_columns (type_name, column_name, source_table_name) VALUES
    ('Container', 'value_longtext1', 'ca_attribute_values'),
    ('Text', 'value_longtext1', 'ca_attribute_values'),
    ('DateRange', 'value_longtext1', 'ca_attribute_values'),
    ('DateRange', 'value_decimal1', 'ca_attribute_values'),
    ('DateRange', 'value_decimal2', 'ca_attribute_values'),
    ('List', 'value_longtext1', 'ca_attribute_values'),
    ('List', 'item_id', 'ca_attribute_values'),
    ('List', 'idno', 'ca_list_items'),
    ('List', 'name_singular', 'ca_list_item_labels'),
    ('Geocode', 'value_longtext1', 'ca_attribute_values'),
    ('Geocode', 'value_longtext2', 'ca_attribute_values'),
    ('Geocode', 'value_decimal1', 'ca_attribute_values'),
    ('Geocode', 'value_decimal2', 'ca_attribute_values'),
    ('Url', 'value_longtext1', 'ca_attribute_values'),
    ('Currency', 'value_longtext1', 'ca_attribute_values'),
    ('Currency', 'value_decimal1', 'ca_attribute_values'),
    ('Length', 'value_longtext1', 'ca_attribute_values'),
    ('Length', 'value_longtext2', 'ca_attribute_values'),
    ('Length', 'value_decimal1', 'ca_attribute_values'),
    ('Length', 'value_decimal2', 'ca_attribute_values'),
    ('Weight', 'value_longtext1', 'ca_attribute_values'),
    ('Weight', 'value_longtext2', 'ca_attribute_values'),
    ('Weight', 'value_decimal1', 'ca_attribute_values'),
    ('Weight', 'value_decimal2', 'ca_attribute_values'),
    ('TimeCode', 'value_longtext1', 'ca_attribute_values'),
    ('TimeCode', 'value_decimal1', 'ca_attribute_values'),
    ('TimeCode', 'value_decimal2', 'ca_attribute_values'),
    ('Integer', 'value_longtext1', 'ca_attribute_values'),
    ('Integer', 'value_integer1', 'ca_attribute_values'),
    ('Numeric', 'value_longtext1', 'ca_attribute_values'),
    ('Numeric', 'value_decimal1', 'ca_attribute_values'),
    ('LCSH', 'value_longtext1', 'ca_attribute_values'),
    ('LCSH', 'value_decimal1', 'ca_attribute_values'),
    ('LCSH', 'value_decimal2', 'ca_attribute_values'),
    ('GeoNames', 'value_longtext1', 'ca_attribute_values'),
    ('GeoNames', 'value_longtext2', 'ca_attribute_values'),
    ('File', 'value_longtext2', 'ca_attribute_values'),
    ('Media', 'value_blob', 'ca_attribute_values'),
    ('Media', 'value_longtext1', 'ca_attribute_values'),
    ('Taxonomy', 'value_longtext1', 'ca_attribute_values'),
    ('Taxonomy', 'value_longtext2', 'ca_attribute_values'),
    ('Taxonomy', 'value_decimal1', 'ca_attribute_values'),
    ('InformationService', 'value_longtext1', 'ca_attribute_values'),
    ('InformationService', 'value_longtext2', 'ca_attribute_values'),
    ('InformationService', 'value_decimal1', 'ca_attribute_values'),
    ('ObjectRepresentations', 'value_longtext1', 'ca_attribute_values'),
    ('ObjectRepresentations', 'value_integer1', 'ca_attribute_values'),
    ('Entities', 'value_longtext1', 'ca_attribute_values'),
    ('Entities', 'value_integer1', 'ca_attribute_values'),
    ('Entities', 'idno', 'ca_entities'),
    ('Entities', 'displayname', 'ca_entity_labels'),
    ('Places', 'value_longtext1', 'ca_attribute_values'),
    ('Places', 'value_integer1', 'ca_attribute_values'),
    ('Places', 'value_integer1', 'ca_attribute_values'),
    ('Places', 'idno', 'ca_places'),
    ('Places', 'name', 'ca_place_labels'),
    ('Occurrences', 'value_longtext1', 'ca_attribute_values'),
    ('Occurrences', 'value_integer1', 'ca_attribute_values'),
    ('Occurrences', 'idno', 'ca_occurrences'),
    ('Occurrences', 'name', 'ca_occurrence_labels'),
    ('Collections', 'value_longtext1', 'ca_attribute_values'),
    ('Collections', 'value_integer1', 'ca_attribute_values'),
    ('Collections', 'idno', 'ca_collections'),
    ('Collections', 'name', 'ca_collection_labels'),
    ('StorageLocations', 'value_longtext1', 'ca_attribute_values'),
    ('StorageLocations', 'value_integer1', 'ca_attribute_values'),
    ('StorageLocations', 'idno', 'ca_storage_locations'),
    ('StorageLocations', 'name', 'ca_storage_location_labels'),
    ('Loans', 'value_longtext1', 'ca_attribute_values'),
    ('Loans', 'value_integer1', 'ca_attribute_values'),
    ('Loans', 'idno', 'ca_loans'),
    ('Loans', 'name', 'ca_loan_labels')
;


 -- https://gist.github.com/kehh/af864b9f69db48c63e1e

-- http://buysql.com/mysql/14-how-to-automate-pivot-tables.html
-- Creates a pivot table of all attributes
  SET @@group_concat_max_len = 500000000;
  SET @sql = NULL;
SELECT
  group_concat(DISTINCT CONCAT(
      'GROUP_CONCAT(IF(
         cfl.element_code = "',
      cfl.element_code,
      '", ',
          CONCAT_WS('.',
      CASE cwc.source_table_name
          WHEN 'ca_collections' THEN 'cc'
          WHEN 'ca_collection_labels' THEN 'ccl'
          WHEN 'ca_attribute_values' THEN 'cav'
          WHEN 'ca_entity_labels' THEN 'cel'
          WHEN 'ca_entities' THEN 'ce'
          WHEN 'ca_list_items' THEN 'cli'
          WHEN 'ca_list_item_labels' THEN 'clil'
          WHEN 'ca_loans' THEN 'cl'
          WHEN 'ca_loan_labels' THEN 'cll'
          WHEN 'ca_occurrences' THEN 'co'
          WHEN 'ca_occurrence_labels' THEN 'col'
          WHEN 'ca_places' THEN 'cp'
          WHEN 'ca_place_labels' THEN 'cpl'
          WHEN 'ca_storage_locations' THEN 'csl'
          WHEN 'ca_storage_location_labels' THEN 'csll'
          ELSE cwc.source_table_name
      END, column_name),
      ',
      NULL
    ) SEPARATOR "|") AS "',
      concat_ws('_', cfl.group_bundle, cfl.element_code, replace(column_name, 'value_','')), '"'
  )) AS query
INTO @sql
FROM
  cmis_field_list cfl JOIN cmis_wanted_columns cwc USING (type_name)
WHERE
  cfl.table_name = 'ca_objects'
ORDER BY group_id, element_id
  ;
SELECT @sql;
  SET @sql = CONCAT(
      'CREATE OR REPLACE VIEW cmis_object_pivot AS ',
      'SELECT ca.table_num, ca.row_id, ',
      @sql,
      ' FROM ca_attribute_values cav
        JOIN ca_attributes ca on cav.attribute_id = ca.attribute_id
        JOIN cmis_field_list cfl on (cfl.element_id = cav.element_id AND ca.table_num = cfl.table_num)
        LEFT JOIN ca_list_items cli  ON cav.item_id = cli.item_id
        LEFT JOIN ca_list_item_labels clil ON (cav.item_id = clil.item_id AND clil.is_preferred AND cfl.source_table_name = "ca_list_item_labels")
        LEFT JOIN ca_collections cc ON cav.value_integer1 = cc.collection_id
        LEFT JOIN ca_collection_labels ccl ON (cav.value_integer1 = ccl.collection_id AND ccl.is_preferred AND cfl.source_table_name = "ca_collection_labels")
        LEFT JOIN ca_entity_labels cel ON (cav.value_integer1 = cel.entity_id AND cel.is_preferred AND cfl.source_table_name = "ca_entity_labels")
        LEFT JOIN ca_entities ce ON cav.value_integer1 = ce.entity_id
        LEFT JOIN ca_loans cl ON cav.value_integer1 = cl.loan_id
        LEFT JOIN ca_loan_labels cll ON (cav.value_integer1 = cll.loan_id AND cll.is_preferred AND cfl.source_table_name = "ca_loan_labels")
        LEFT JOIN ca_occurrences co ON cav.value_integer1 = co.occurrence_id
        LEFT JOIN ca_occurrence_labels col ON (cav.value_integer1 = col.occurrence_id AND col.is_preferred AND cfl.source_table_name = "ca_occurrence_labels")
        LEFT JOIN ca_places cp ON cav.value_integer1 = cp.place_id
        LEFT JOIN ca_place_labels cpl ON (cav.value_integer1 = cpl.place_id AND cpl.is_preferred AND cfl.source_table_name = "ca_place_labels")
        LEFT JOIN ca_storage_locations csl ON cav.value_integer1 = csl.location_id
        LEFT JOIN ca_storage_location_labels csll ON (cav.value_integer1 = csll.location_id AND csll.is_preferred AND cfl.source_table_name = "ca_storage_location_labels")
WHERE cfl.table_name = "ca_objects"
GROUP BY cfl.table_num, ca.row_id

');
  SELECT @sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;

DEALLOCATE PREPARE stmt;
	-- This creates a mysql table which contains the values of
	-- datamodel.conf so that we can use the values in constructing
	-- queries

	DROP TABLE IF EXISTS cmis_table_numbers;


	CREATE TABLE cmis_table_numbers (
	table_name VARCHAR(64) NOT NULL UNIQUE,
	table_num INT(11) PRIMARY KEY,
	enabled BOOLEAN DEFAULT TRUE
	);

	INSERT INTO cmis_table_numbers (table_name, table_num, enabled) VALUES
	('ca_acl', 1, TRUE),
	('ca_application_vars', 2, TRUE),
	('ca_attribute_values', 3, TRUE),
	('ca_attributes', 4, TRUE),
	('ca_change_log', 5, TRUE),
	('ca_change_log_subjects', 6, TRUE),
	('ca_representation_annotations_x_entities', 7, TRUE),
	('ca_representation_annotations_x_objects', 8, TRUE),
	('ca_representation_annotations_x_occurrences', 9, TRUE),
	('ca_representation_annotations_x_places', 10, TRUE),
	('ca_representation_annotations_x_vocabulary_terms', 11, TRUE),
	('ca_collection_labels', 12, TRUE),
	('ca_collections', 13, TRUE),
	('ca_collections_x_collections', 14, TRUE),
	('ca_collections_x_vocabulary_terms', 15, TRUE),
	('ca_data_import_events', 17, TRUE),
	('ca_data_import_items', 18, TRUE),
	('ca_entities', 20, TRUE),
	('ca_entities_x_collections', 21, TRUE),
	('ca_entities_x_occurrences', 22, TRUE),
	('ca_entities_x_places', 23, TRUE),
	('ca_entities_x_vocabulary_terms', 24, TRUE),
	('ca_entity_labels', 25, TRUE),
	('ca_entities_x_entities', 26, TRUE),
	('ca_eventlog', 27, FALSE),
	('ca_groups_x_roles', 30, TRUE),
	('ca_ips', 31, TRUE),
	('ca_list_item_labels', 32, TRUE),
	('ca_list_items', 33, TRUE),
	('ca_list_items_x_list_items', 34, TRUE),
	('ca_list_labels', 35, TRUE),
	('ca_lists', 36, TRUE),
	('ca_locales', 37, TRUE),
	('ca_object_lot_labels', 40, TRUE),
	('ca_metadata_element_labels', 41, TRUE),
	('ca_metadata_elements', 42, TRUE),
	('ca_metadata_type_restrictions', 43, TRUE),
	('ca_multipart_idno_sequences', 44, TRUE),
	('ca_object_labels', 50, TRUE),
	('ca_object_lots', 51, TRUE),
	('ca_object_lots_x_collections', 52, TRUE),
	('ca_object_lots_x_entities', 53, TRUE),
	('ca_object_lots_x_occurrences', 54, TRUE),
	('ca_object_lots_x_places', 55, TRUE),
	('ca_object_representations', 56, TRUE),
	('ca_objects', 57, TRUE),
	('ca_objects_x_collections', 58, TRUE),
	('ca_objects_x_entities', 59, TRUE),
	('ca_objects_x_object_representations', 61, TRUE),
	('ca_objects_x_objects', 62, TRUE),
	('ca_objects_x_occurrences', 63, TRUE),
	('ca_objects_x_places', 64, TRUE),
	('ca_objects_x_vocabulary_terms', 65, TRUE),
	('ca_occurrence_labels', 66, TRUE),
	('ca_occurrences', 67, TRUE),
	('ca_occurrences_x_collections', 68, TRUE),
	('ca_occurrences_x_occurrences', 69, TRUE),
	('ca_occurrences_x_vocabulary_terms', 70, TRUE),
	('ca_place_labels', 71, TRUE),
	('ca_places', 72, TRUE),
	('ca_places_x_collections', 73, TRUE),
	('ca_places_x_occurrences', 74, TRUE),
	('ca_places_x_places', 75, TRUE),
	('ca_places_x_vocabulary_terms', 76, TRUE),
	('ca_relationship_relationships', 77, TRUE),
	('ca_relationship_type_labels', 78, TRUE),
	('ca_relationship_types', 79, TRUE),
	('ca_representation_annotations', 82, TRUE),
	('ca_representation_annotation_labels', 83, TRUE),
	('ca_object_representations_x_entities', 85, TRUE),
	('ca_object_representations_x_occurrences', 86, TRUE),
	('ca_object_representations_x_places', 87, TRUE),
	('ca_storage_location_labels', 88, TRUE),
	('ca_storage_locations', 89, TRUE),
	('ca_task_queue', 90, TRUE),
	('ca_user_groups', 91, TRUE),
	('ca_user_roles', 92, TRUE),
	('ca_users', 94, TRUE),
	('ca_users_x_groups', 95, TRUE),
	('ca_users_x_roles', 96, TRUE),
	('ca_editor_ui_bundle_placements', 97, TRUE),
	('ca_editor_ui_labels', 98, TRUE),
	('ca_editor_ui_screen_labels', 99, TRUE),
	('ca_editor_ui_screens', 100, TRUE),
	('ca_editor_uis', 101, TRUE),
	('ca_editor_uis_x_user_groups', 102, TRUE),
	('ca_sets', 103, TRUE),
	('ca_set_labels', 104, TRUE),
	('ca_set_items', 105, TRUE),
	('ca_set_item_labels', 106, TRUE),
	('ca_sets_x_user_groups', 107, TRUE),
	('ca_object_representation_labels', 108, TRUE),
	('ca_item_comments', 109, TRUE),
	('ca_item_tags', 110, TRUE),
	('ca_items_x_tags', 111, TRUE),
	('ca_item_views', 112, TRUE),
	('ca_object_lots_x_vocabulary_terms', 115, TRUE),
	('ca_object_representations_x_vocabulary_terms', 116, TRUE),
	('ca_objects_x_storage_locations', 119, TRUE),
	('ca_object_lots_x_storage_locations', 120, TRUE),
	('ca_search_forms', 121, TRUE),
	('ca_search_form_labels', 122, TRUE),
	('ca_object_representation_multifiles', 123, TRUE),
	('ca_bundle_displays', 124, TRUE),
	('ca_bundle_display_placements', 125, TRUE),
	('ca_bundle_display_labels', 126, TRUE),
	('ca_bundle_displays_x_user_groups', 127, TRUE),
	('ca_editor_ui_screen_type_restrictions', 131, TRUE),
	('ca_collections_x_storage_locations', 132, TRUE),
	('ca_loans', 133, TRUE),
	('ca_loan_labels', 134, TRUE),
	('ca_loans_x_objects', 135, TRUE),
	('ca_loans_x_entities', 136, TRUE),
	('ca_movements', 137, TRUE),
	('ca_movement_labels', 138, TRUE),
	('ca_movements_x_objects', 139, TRUE),
	('ca_movements_x_object_lots', 140, TRUE),
	('ca_movements_x_entities', 141, TRUE),
	('ca_loans_x_movements', 143, TRUE),
	('ca_watch_list', 144, TRUE),
	('ca_user_notes', 145, TRUE),
	('ca_data_import_event_log', 146, TRUE),
	('ca_search_forms_x_user_groups', 147, TRUE),
	('ca_search_forms_x_users', 148, TRUE),
	('ca_bundle_displays_x_users', 149, TRUE),
	('ca_sets_x_users', 150, TRUE),
	('ca_editor_uis_x_users', 151, TRUE),
	('ca_search_form_placements', 152, TRUE),
	('ca_tours', 153, TRUE),
	('ca_tour_labels', 154, TRUE),
	('ca_tour_stops', 155, TRUE),
	('ca_tour_stop_labels', 156, TRUE),
	('ca_tour_stops_x_objects', 157, TRUE),
	('ca_tour_stops_x_entities', 158, TRUE),
	('ca_tour_stops_x_places', 159, TRUE),
	('ca_tour_stops_x_occurrences', 160, TRUE),
	('ca_tour_stops_x_collections', 161, TRUE),
	('ca_tour_stops_x_vocabulary_terms', 162, TRUE),
	('ca_tour_stops_x_tour_stops', 163, TRUE),
	('ca_bookmark_folders', 164, TRUE),
	('ca_bookmarks', 165, TRUE),
	('ca_commerce_transactions', 166, TRUE),
	('ca_commerce_communications', 167, TRUE),
	('ca_commerce_orders', 169, TRUE),
	('ca_commerce_order_items', 170, TRUE),
	('ca_commerce_fulfillment_events', 171, TRUE),
	('ca_object_representations_x_collections', 172, TRUE),
	('ca_object_representations_x_storage_locations', 173, TRUE),
	('ca_object_representations_x_object_representations', 174, TRUE),
	('ca_entities_x_storage_locations', 177, TRUE),
	('ca_loans_x_collections', 178, TRUE),
	('ca_loans_x_object_lots', 179, TRUE),
	('ca_loans_x_occurrences', 180, TRUE),
	('ca_loans_x_places', 181, TRUE),
	('ca_loans_x_storage_locations', 182, TRUE),
	('ca_loans_x_vocabulary_terms', 183, TRUE),
	('ca_loans_x_loans', 184, TRUE),
	('ca_movements_x_collections', 185, TRUE),
	('ca_movements_x_occurrences', 186, TRUE),
	('ca_movements_x_places', 187, TRUE),
	('ca_movements_x_storage_locations', 188, TRUE),
	('ca_movements_x_vocabulary_terms', 189, TRUE),
	('ca_movements_x_movements', 190, TRUE),
	('ca_places_x_storage_locations', 191, TRUE),
	('ca_occurrences_x_storage_locations', 192, TRUE),
	('ca_storage_locations_x_vocabulary_terms', 193, TRUE),
	('ca_storage_locations_x_storage_locations', 194, TRUE),
	('ca_commerce_communications_read_log', 195, TRUE),
	('ca_commerce_order_items_x_object_representations', 196, TRUE),
	('ca_editor_ui_bundle_placement_type_restrictions', 197, TRUE),
	('ca_editor_ui_type_restrictions', 198, TRUE),
	('ca_object_lots_x_object_representations', 199, TRUE),
	('ca_loans_x_object_representations', 200, TRUE),
	('ca_movements_x_object_representations', 201, TRUE),
	('ca_data_importers', 202, TRUE),
	('ca_data_importer_labels', 203, TRUE),
	('ca_data_importer_groups', 204, TRUE),
	('ca_data_importer_items', 205, TRUE),
	('ca_data_importer_log', 206, TRUE),
	('ca_data_importer_log_items', 207, TRUE),
	('ca_data_exporters', 208, TRUE),
	('ca_data_exporter_labels', 209, TRUE),
	('ca_data_exporter_items', 210, TRUE),
	('ca_object_lots_x_object_lots', 211, TRUE),
	('ca_bundle_display_type_restrictions', 212, TRUE),
	('ca_object_representation_captions', 213, TRUE),
	('ca_metadata_dictionary_entries', 214, TRUE),
	('ca_metadata_dictionary_rules', 215, TRUE),
	('ca_attribute_value_multifiles', 216, TRUE);

	DROP TABLE IF EXISTS cmis_attribute_types;


	CREATE TABLE cmis_attribute_types (
	datatype TINYINT UNSIGNED PRIMARY KEY,
	type_name VARCHAR(64) NOT NULL UNIQUE,
	enabled BOOLEAN DEFAULT TRUE,
	source_table_name VARCHAR(64)
	);
	ALTER TABLE cmis_attribute_types ADD INDEX (type_name), ADD INDEX (source_table_name);

	REPLACE INTO cmis_attribute_types (datatype, type_name, enabled, source_table_name) VALUES
	(0, 'Container', TRUE, NULL),
	(1, 'Text', TRUE, NULL),
	(2, 'DateRange', TRUE, NULL),
	(3, 'List', TRUE, NULL),
	(4, 'Geocode', TRUE, NULL),
	(5, 'Url', TRUE, NULL),
	(6, 'Currency', TRUE, NULL),
	(7, 'was ''Relationship'' attribute type', FALSE, NULL),
	(8, 'Length', TRUE, NULL),
	(9, 'Weight', TRUE, NULL),
	(10, 'TimeCode', TRUE, NULL),
	(11, 'Integer', TRUE, NULL),
	(12, 'Numeric', TRUE, NULL),
	(13, 'LCSH', TRUE, NULL),
	(14, 'GeoNames', TRUE, NULL),
	(15, 'File', TRUE, NULL),
	(16, 'Media', TRUE, NULL),
	(17, 'was ''Place'' attribute type', FALSE, NULL),
	(18, 'was ''Occurrence'' attribute type', FALSE, NULL),
	(19, 'Taxonomy', TRUE, NULL),
	(20, 'InformationService', TRUE, NULL),
	(21, 'ObjectRepresentations', TRUE, 'ca_object_representations'),
	(22, 'Entities', TRUE, 'ca_entities'),
	(23, 'Places', TRUE, 'ca_places'),
	(24, 'Occurrences', TRUE, 'ca_occurrences'),
	(25, 'Collections', TRUE, 'ca_collections'),
	(26, 'StorageLocations', TRUE, 'ca_storage_locations'),
	(27, 'Loans', TRUE, 'ca_loans'),
	(28, 'Movements', TRUE, 'ca_movements'),
	(29, 'Objects', TRUE, 'ca_objects'),
	(30, 'ObjectLots', TRUE, 'ca_object_lots');
	DROP TABLE IF EXISTS cmis_wanted_columns;
	CREATE TABLE cmis_wanted_columns (
	type_name VARCHAR(64) NOT NULL,
	column_name VARCHAR(64) NULL NULL,
	source_table_name VARCHAR(64)
	);
	ALTER TABLE cmis_wanted_columns ADD PRIMARY KEY (type_name, column_name, source_table_name);
	REPLACE INTO cmis_wanted_columns (type_name, column_name, source_table_name) VALUES
	('Container', 'value_longtext1', 'ca_attribute_values'),
	('Text', 'value_longtext1', 'ca_attribute_values'),
	('DateRange', 'value_longtext1', 'ca_attribute_values'),
	('DateRange', 'value_decimal1', 'ca_attribute_values'),
	('DateRange', 'value_decimal2', 'ca_attribute_values'),
	('List', 'value_longtext1', 'ca_attribute_values'),
	('List', 'item_id', 'ca_attribute_values'),
	('List', 'idno', 'ca_list_items'),
	('List', 'name_singular', 'ca_list_item_labels'),
	('Geocode', 'value_longtext1', 'ca_attribute_values'),
	('Geocode', 'value_longtext2', 'ca_attribute_values'),
	('Geocode', 'value_decimal1', 'ca_attribute_values'),
	('Geocode', 'value_decimal2', 'ca_attribute_values'),
	('Url', 'value_longtext1', 'ca_attribute_values'),
	('Currency', 'value_longtext1', 'ca_attribute_values'),
	('Currency', 'value_decimal1', 'ca_attribute_values'),
	('Length', 'value_longtext1', 'ca_attribute_values'),
	('Length', 'value_longtext2', 'ca_attribute_values'),
	('Length', 'value_decimal1', 'ca_attribute_values'),
	('Length', 'value_decimal2', 'ca_attribute_values'),
	('Weight', 'value_longtext1', 'ca_attribute_values'),
	('Weight', 'value_longtext2', 'ca_attribute_values'),
	('Weight', 'value_decimal1', 'ca_attribute_values'),
	('Weight', 'value_decimal2', 'ca_attribute_values'),
	('TimeCode', 'value_longtext1', 'ca_attribute_values'),
	('TimeCode', 'value_decimal1', 'ca_attribute_values'),
	('TimeCode', 'value_decimal2', 'ca_attribute_values'),
	('Integer', 'value_longtext1', 'ca_attribute_values'),
	('Integer', 'value_integer1', 'ca_attribute_values'),
	('Numeric', 'value_longtext1', 'ca_attribute_values'),
	('Numeric', 'value_decimal1', 'ca_attribute_values'),
	('LCSH', 'value_longtext1', 'ca_attribute_values'),
	('LCSH', 'value_decimal1', 'ca_attribute_values'),
	('LCSH', 'value_decimal2', 'ca_attribute_values'),
	('GeoNames', 'value_longtext1', 'ca_attribute_values'),
	('GeoNames', 'value_longtext2', 'ca_attribute_values'),
	('File', 'value_longtext2', 'ca_attribute_values'),
	('Media', 'value_blob', 'ca_attribute_values'),
	('Media', 'value_longtext1', 'ca_attribute_values'),
	('Taxonomy', 'value_longtext1', 'ca_attribute_values'),
	('Taxonomy', 'value_longtext2', 'ca_attribute_values'),
	('Taxonomy', 'value_decimal1', 'ca_attribute_values'),
	('InformationService', 'value_longtext1', 'ca_attribute_values'),
	('InformationService', 'value_longtext2', 'ca_attribute_values'),
	('InformationService', 'value_decimal1', 'ca_attribute_values'),
	('ObjectRepresentations', 'value_longtext1', 'ca_attribute_values'),
	('ObjectRepresentations', 'value_integer1', 'ca_attribute_values'),
	('Entities', 'value_longtext1', 'ca_attribute_values'),
	('Entities', 'value_integer1', 'ca_attribute_values'),
	('Entities', 'idno', 'ca_entities'),
	('Entities', 'displayname', 'ca_entity_labels'),
	('Places', 'value_longtext1', 'ca_attribute_values'),
	('Places', 'value_integer1', 'ca_attribute_values'),
	('Places', 'value_integer1', 'ca_attribute_values'),
	('Places', 'idno', 'ca_places'),
	('Places', 'name', 'ca_place_labels'),
	('Occurrences', 'value_longtext1', 'ca_attribute_values'),
	('Occurrences', 'value_integer1', 'ca_attribute_values'),
	('Occurrences', 'idno', 'ca_occurrences'),
	('Occurrences', 'name', 'ca_occurrence_labels'),
	('Collections', 'value_longtext1', 'ca_attribute_values'),
	('Collections', 'value_integer1', 'ca_attribute_values'),
	('Collections', 'idno', 'ca_collections'),
	('Collections', 'name', 'ca_collection_labels'),
	('StorageLocations', 'value_longtext1', 'ca_attribute_values'),
	('StorageLocations', 'value_integer1', 'ca_attribute_values'),
	('StorageLocations', 'idno', 'ca_storage_locations'),
	('StorageLocations', 'name', 'ca_storage_location_labels'),
	('Loans', 'value_longtext1', 'ca_attribute_values'),
	('Loans', 'value_integer1', 'ca_attribute_values'),
	('Loans', 'idno', 'ca_loans'),
	('Loans', 'name', 'ca_loan_labels')
	;


	-- https://gist.github.com/kehh/af864b9f69db48c63e1e

	-- http://buysql.com/mysql/14-how-to-automate-pivot-tables.html
	-- Creates a pivot table of all attributes
	SET @@group_concat_max_len = 500000000;
	SET @sql = NULL;
	SELECT
	group_concat(DISTINCT CONCAT(
	'GROUP_CONCAT(IF(
	cfl.element_code = "',
	cfl.element_code,
	'", ',
	CONCAT_WS('.',
	CASE cwc.source_table_name
	WHEN 'ca_collections' THEN 'cc'
	WHEN 'ca_collection_labels' THEN 'ccl'
	WHEN 'ca_attribute_values' THEN 'cav'
	WHEN 'ca_entity_labels' THEN 'cel'
	WHEN 'ca_entities' THEN 'ce'
	WHEN 'ca_list_items' THEN 'cli'
	WHEN 'ca_list_item_labels' THEN 'clil'
	WHEN 'ca_loans' THEN 'cl'
	WHEN 'ca_loan_labels' THEN 'cll'
	WHEN 'ca_occurrences' THEN 'co'
	WHEN 'ca_occurrence_labels' THEN 'col'
	WHEN 'ca_places' THEN 'cp'
	WHEN 'ca_place_labels' THEN 'cpl'
	WHEN 'ca_storage_locations' THEN 'csl'
	WHEN 'ca_storage_location_labels' THEN 'csll'
	ELSE cwc.source_table_name
	END, column_name),
	',
	NULL
	) SEPARATOR "\|") AS "',
	concat_ws('_', cfl.group_bundle, cfl.element_code, replace(column_name, 'value_','')), '"'
	)) AS query
	INTO @sql
	FROM
	cmis_field_list cfl JOIN cmis_wanted_columns cwc USING (type_name)
	WHERE
	cfl.table_name = 'ca_objects'
	ORDER BY group_id, element_id
	;
	SELECT @sql;
	SET @sql = CONCAT(
	'CREATE OR REPLACE VIEW cmis_object_pivot AS ',
	'SELECT ca.table_num, ca.row_id, ',
	@sql,
	' FROM ca_attribute_values cav
	JOIN ca_attributes ca on cav.attribute_id = ca.attribute_id
	JOIN cmis_field_list cfl on (cfl.element_id = cav.element_id AND ca.table_num = cfl.table_num)
	LEFT JOIN ca_list_items cli ON cav.item_id = cli.item_id
	LEFT JOIN ca_list_item_labels clil ON (cav.item_id = clil.item_id AND clil.is_preferred AND cfl.source_table_name = "ca_list_item_labels")
	LEFT JOIN ca_collections cc ON cav.value_integer1 = cc.collection_id
	LEFT JOIN ca_collection_labels ccl ON (cav.value_integer1 = ccl.collection_id AND ccl.is_preferred AND cfl.source_table_name = "ca_collection_labels")
	LEFT JOIN ca_entity_labels cel ON (cav.value_integer1 = cel.entity_id AND cel.is_preferred AND cfl.source_table_name = "ca_entity_labels")
	LEFT JOIN ca_entities ce ON cav.value_integer1 = ce.entity_id
	LEFT JOIN ca_loans cl ON cav.value_integer1 = cl.loan_id
	LEFT JOIN ca_loan_labels cll ON (cav.value_integer1 = cll.loan_id AND cll.is_preferred AND cfl.source_table_name = "ca_loan_labels")
	LEFT JOIN ca_occurrences co ON cav.value_integer1 = co.occurrence_id
	LEFT JOIN ca_occurrence_labels col ON (cav.value_integer1 = col.occurrence_id AND col.is_preferred AND cfl.source_table_name = "ca_occurrence_labels")
	LEFT JOIN ca_places cp ON cav.value_integer1 = cp.place_id
	LEFT JOIN ca_place_labels cpl ON (cav.value_integer1 = cpl.place_id AND cpl.is_preferred AND cfl.source_table_name = "ca_place_labels")
	LEFT JOIN ca_storage_locations csl ON cav.value_integer1 = csl.location_id
	LEFT JOIN ca_storage_location_labels csll ON (cav.value_integer1 = csll.location_id AND csll.is_preferred AND cfl.source_table_name = "ca_storage_location_labels")
	WHERE cfl.table_name = "ca_objects"
	GROUP BY cfl.table_num, ca.row_id

	');
	SELECT @sql;
	PREPARE stmt FROM @sql;
	EXECUTE stmt;

	DEALLOCATE PREPARE stmt;