search

collectiveaccess / providence

Cataloguing and data/media management application
GNU General Public License v3.0
290 stars 167 forks source link

Large database migration delta between legacy install (1.6.x) and current install 1.8-dev #1477

Closed kehh closed 9 months ago

kehh commented 11 months ago

I've just run a database diff tool over an older providence install and a current install. It looks like a fairly large diff. A bunch of them relate to changing tables utf8mb4 and changing names of indexes. However there are other changes like data type changes (bigint vs int on ca_sql_search_word_index.index_id) which have consequences for the operation of the system.

I'm going to put them here but will try to wrap this up in a database migration - let me know if you want this all in one block or multiple migrations:

alter table ca_acl
    charset = utf8mb4;

alter table ca_application_vars
    charset = utf8mb4;

alter table ca_attribute_value_multifiles
    charset = utf8mb4;

alter table ca_attribute_value_multifiles
    add constraint fk_ca_attribute_value_multifiles_value_id
        foreign key (value_id) references ca_attribute_values (value_id);

alter table ca_attribute_values
    modify value_sortable varchar(100) null after value_integer1;

alter table ca_attribute_values
    charset = utf8mb4;

alter table ca_attributes
    charset = utf8mb4;

alter table ca_batch_log
    charset = utf8mb4;

alter table ca_batch_log_items
    charset = utf8mb4;

alter table ca_bookmark_folders
    charset = utf8mb4;

alter table ca_bookmark_folders
    add constraint fk_ca_bookmark_folders_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_bookmarks
    charset = utf8mb4;

alter table ca_bookmarks
    add constraint fk_ca_bookmarks_folder_id
        foreign key (folder_id) references ca_bookmark_folders (folder_id);

alter table ca_bundle_display_labels
    charset = utf8mb4;

alter table ca_bundle_display_labels
    add constraint fk_ca_bundle_display_labels_display_id
        foreign key (display_id) references ca_bundle_displays (display_id);

alter table ca_bundle_display_labels
    add constraint fk_ca_bundle_display_labels_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_bundle_display_placements
    charset = utf8mb4;

alter table ca_bundle_display_placements
    add constraint fk_ca_bundle_display_placements_display_id
        foreign key (display_id) references ca_bundle_displays (display_id);

alter table ca_bundle_display_type_restrictions
    charset = utf8mb4;

alter table ca_bundle_displays
    charset = utf8mb4;

alter table ca_bundle_displays
    add constraint fk_ca_bundle_displays_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_bundle_displays_x_user_groups
    charset = utf8mb4;

alter table ca_bundle_displays_x_user_groups
    add constraint fk_ca_bundle_displays_x_ug_display_id
        foreign key (display_id) references ca_bundle_displays (display_id);

alter table ca_bundle_displays_x_user_groups
    add constraint fk_ca_bundle_displays_x_ug_group_id
        foreign key (group_id) references ca_user_groups (group_id);

alter table ca_bundle_displays_x_users
    charset = utf8mb4;

alter table ca_bundle_displays_x_users
    add constraint fk_ca_bundle_displays_x_u_display_id
        foreign key (display_id) references ca_bundle_displays (display_id);

alter table ca_bundle_displays_x_users
    add constraint fk_ca_bundle_displays_x_u_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_change_log
    charset = utf8mb4;

alter table ca_change_log_snapshots
    charset = utf8mb4;

alter table ca_change_log_subjects
    charset = utf8mb4;

alter table ca_collection_labels
    charset = utf8mb4;

alter table ca_collections
    modify idno_sort_num bigint default 0 not null after idno_sort;

alter table ca_collections
    charset = utf8mb4;

alter table ca_collections
    add constraint fk_ca_collections_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_collections
    add constraint fk_ca_collections_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_collections
    add constraint fk_ca_collections_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_collections
    add constraint fk_ca_collections_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_collections_x_collections
    charset = utf8mb4;

alter table ca_collections_x_storage_locations
    charset = utf8mb4;

alter table ca_collections_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_data_exporter_items
    charset = utf8mb4;

alter table ca_data_exporter_labels
    charset = utf8mb4;

alter table ca_data_exporters
    charset = utf8mb4;

alter table ca_data_import_event_log
    charset = utf8mb4;

alter table ca_data_import_events
    charset = utf8mb4;

alter table ca_data_import_items
    charset = utf8mb4;

alter table ca_data_importer_groups
    charset = utf8mb4;

alter table ca_data_importer_items
    charset = utf8mb4;

alter table ca_data_importer_labels
    charset = utf8mb4;

alter table ca_data_importer_log
    charset = utf8mb4;

alter table ca_data_importer_log_items
    charset = utf8mb4;

alter table ca_data_importers
    charset = utf8mb4;

alter table ca_download_log
    charset = utf8mb4;

alter table ca_editor_ui_bundle_placements
    charset = utf8mb4;

alter table ca_editor_ui_bundle_placements
    add constraint fk_ca_editor_ui_bundle_placements_screen_id
        foreign key (screen_id) references ca_editor_ui_screens (screen_id);

alter table ca_editor_ui_labels
    modify locale_id smallint unsigned not null;

alter table ca_editor_ui_labels
    charset = utf8mb4;

alter table ca_editor_ui_labels
    add constraint fk_ca_editor_ui_labels_ca_locales
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_editor_ui_labels
    add constraint fk_ca_editor_ui_labels_ui_id
        foreign key (ui_id) references ca_editor_uis (ui_id);

alter table ca_editor_ui_screen_labels
    modify locale_id smallint unsigned not null;

alter table ca_editor_ui_screen_labels
    charset = utf8mb4;

alter table ca_editor_ui_screen_labels
    add constraint fk_ca_editor_ui_screen_labels_ca_locales
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_editor_ui_screen_labels
    add constraint fk_ca_editor_ui_screen_labels_screen_id
        foreign key (screen_id) references ca_editor_ui_screens (screen_id);

alter table ca_editor_ui_screen_type_restrictions
    charset = utf8mb4;

alter table ca_editor_ui_screens
    charset = utf8mb4;

alter table ca_editor_ui_screens
    add constraint fk_ca_editor_ui_screens_ui_id
        foreign key (ui_id) references ca_editor_uis (ui_id);

alter table ca_editor_ui_screens_x_roles
    modify role_id smallint unsigned not null;

alter table ca_editor_ui_screens_x_roles
    charset = utf8mb4;

alter table ca_editor_ui_screens_x_roles
    add constraint fk_ca_editor_ui_screens_x_r_role_id
        foreign key (role_id) references ca_user_roles (role_id);

alter table ca_editor_ui_screens_x_roles
    add constraint fk_ca_editor_ui_screens_x_r_screen_id
        foreign key (screen_id) references ca_editor_ui_screens (screen_id);

alter table ca_editor_ui_screens_x_user_groups
    charset = utf8mb4;

alter table ca_editor_ui_screens_x_user_groups
    add constraint fk_ca_editor_ui_screens_x_ug_group_id
        foreign key (group_id) references ca_user_groups (group_id);

alter table ca_editor_ui_screens_x_user_groups
    add constraint fk_ca_editor_ui_screens_x_ug_screen_id
        foreign key (screen_id) references ca_editor_ui_screens (screen_id);

alter table ca_editor_ui_screens_x_users
    charset = utf8mb4;

alter table ca_editor_ui_screens_x_users
    add constraint fk_ca_editor_ui_screens_x_u_screen_id
        foreign key (screen_id) references ca_editor_ui_screens (screen_id);

alter table ca_editor_ui_screens_x_users
    add constraint fk_ca_editor_ui_screens_x_u_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_editor_ui_type_restrictions
    charset = utf8mb4;

alter table ca_editor_uis
    charset = utf8mb4;

alter table ca_editor_uis
    add constraint fk_ca_editor_uis_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_editor_uis_x_roles
    modify role_id smallint unsigned not null;

alter table ca_editor_uis_x_roles
    charset = utf8mb4;

alter table ca_editor_uis_x_roles
    add constraint fk_ca_editor_uis_x_roles_ca_user_roles
        foreign key (role_id) references ca_user_roles (role_id);

alter table ca_editor_uis_x_roles
    add constraint fk_ca_editor_uis_x_roles_ui_id
        foreign key (ui_id) references ca_editor_uis (ui_id);

alter table ca_editor_uis_x_user_groups
    charset = utf8mb4;

alter table ca_editor_uis_x_user_groups
    add constraint fk_ca_editor_uis_x_user_groups_ca_user_groups
        foreign key (group_id) references ca_user_groups (group_id);

alter table ca_editor_uis_x_user_groups
    add constraint fk_ca_editor_uis_x_user_groups_ui_id
        foreign key (ui_id) references ca_editor_uis (ui_id);

alter table ca_editor_uis_x_users
    charset = utf8mb4;

alter table ca_editor_uis_x_users
    add constraint fk_ca_editor_uis_x_users_ca_users
        foreign key (user_id) references ca_users (user_id);

alter table ca_editor_uis_x_users
    add constraint fk_ca_editor_uis_x_users_ui_id
        foreign key (ui_id) references ca_editor_uis (ui_id);

alter table ca_entities
    modify idno_sort_num bigint default 0 not null after idno_sort;

alter table ca_entities
    charset = utf8mb4;

alter table ca_entities
    add constraint fk_ca_entities_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_entities
    add constraint fk_ca_entities_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_entities
    add constraint fk_ca_entities_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_entities
    add constraint fk_ca_entities_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_entities_x_collections
    charset = utf8mb4;

alter table ca_entities_x_entities
    charset = utf8mb4;

alter table ca_entities_x_occurrences
    charset = utf8mb4;

alter table ca_entities_x_places
    charset = utf8mb4;

alter table ca_entities_x_storage_locations
    charset = utf8mb4;

alter table ca_entities_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_entity_labels
    modify checked tinyint unsigned default '0' not null;

alter table ca_entity_labels
    charset = utf8mb4;

alter table ca_eventlog
    charset = utf8mb4;

alter table ca_groups_x_roles
    charset = utf8mb4;

alter table ca_guids
    charset = utf8mb4;

alter table ca_history_tracking_current_values
    charset = utf8mb4;

alter table ca_history_tracking_current_values
    add constraint u_all
        unique (row_id, table_num, policy, type_id, is_future);

alter table ca_ip_bans
    charset = utf8mb4;

alter table ca_ips
    charset = utf8mb4;

alter table ca_item_comments
    charset = utf8mb4;

alter table ca_item_comments
    add constraint fk_ca_item_comments_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_item_comments
    add constraint fk_ca_item_comments_moderated_by_user_id
        foreign key (moderated_by_user_id) references ca_users (user_id);

alter table ca_item_comments
    add constraint fk_ca_item_comments_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_item_tags
    charset = utf8mb4;

alter table ca_item_tags
    add constraint fk_ca_item_tags_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_items_x_tags
    charset = utf8mb4;

alter table ca_items_x_tags
    add constraint fk_ca_items_x_tags_moderated_by_user_id
        foreign key (moderated_by_user_id) references ca_users (user_id);

alter table ca_items_x_tags
    add constraint fk_ca_items_x_tags_tag_id
        foreign key (tag_id) references ca_item_tags (tag_id);

alter table ca_items_x_tags
    add constraint fk_ca_items_x_tags_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_list_item_labels
    charset = utf8mb4;

alter table ca_list_items
    modify idno_sort_num bigint default 0 not null after idno_sort;

alter table ca_list_items
    charset = utf8mb4;

alter table ca_list_items_x_list_items
    charset = utf8mb4;

alter table ca_list_labels
    charset = utf8mb4;

alter table ca_lists
    charset = utf8mb4;

alter table ca_loan_labels
    charset = utf8mb4;

alter table ca_loans
    modify idno_sort_num bigint default 0 not null after idno_sort;

drop index idno on ca_loans;

drop index idno_sort on ca_loans;

alter table ca_loans
    charset = utf8mb4;

create index i_idno
    on ca_loans (idno);

create index i_idno_sort
    on ca_loans (idno_sort);

alter table ca_loans
    add constraint fk_ca_loans_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_loans
    add constraint fk_ca_loans_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_loans
    add constraint fk_ca_loans_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_loans
    add constraint fk_ca_loans_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_loans_x_collections
    charset = utf8mb4;

alter table ca_loans_x_entities
    charset = utf8mb4;

alter table ca_loans_x_loans
    charset = utf8mb4;

alter table ca_loans_x_movements
    charset = utf8mb4;

alter table ca_loans_x_object_lots
    charset = utf8mb4;

alter table ca_loans_x_object_representations
    charset = utf8mb4;

alter table ca_loans_x_objects
    charset = utf8mb4;

alter table ca_loans_x_occurrences
    charset = utf8mb4;

alter table ca_loans_x_places
    charset = utf8mb4;

alter table ca_loans_x_storage_locations
    charset = utf8mb4;

alter table ca_loans_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_locales
    charset = utf8mb4;

alter table ca_media_content_locations
    charset = utf8mb4;

alter table ca_media_replication_status_check
    charset = utf8mb4;

alter table ca_media_upload_session_files
    charset = utf8mb4;

alter table ca_media_upload_session_files
    add constraint fk_ca_media_upload_session_files_session_id
        foreign key (session_id) references ca_media_upload_sessions (session_id);

alter table ca_media_upload_sessions
    charset = utf8mb4;

alter table ca_media_upload_sessions
    add constraint fk_ca_media_upload_sessions_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_metadata_alert_rule_labels
    charset = utf8mb4;

alter table ca_metadata_alert_rule_labels
    add constraint fk_ca_metadata_alert_rule_labels_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_metadata_alert_rule_labels
    add constraint fk_ca_metadata_alert_rule_labels_rule_id
        foreign key (rule_id) references ca_metadata_alert_rules (rule_id);

alter table ca_metadata_alert_rule_type_restrictions
    charset = utf8mb4;

alter table ca_metadata_alert_rules
    charset = utf8mb4;

alter table ca_metadata_alert_rules
    add constraint fk_ca_metadata_alert_rules_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_metadata_alert_rules_x_user_groups
    charset = utf8mb4;

alter table ca_metadata_alert_rules_x_user_groups
    add constraint fk_ca_metadata_alert_rules_x_ug_group_id
        foreign key (group_id) references ca_user_groups (group_id);

alter table ca_metadata_alert_rules_x_user_groups
    add constraint fk_ca_metadata_alert_rules_x_ug_rule_id
        foreign key (rule_id) references ca_metadata_alert_rules (rule_id);

alter table ca_metadata_alert_rules_x_users
    charset = utf8mb4;

alter table ca_metadata_alert_rules_x_users
    add constraint fk_ca_metadata_alert_rules_x_u_rule_id
        foreign key (rule_id) references ca_metadata_alert_rules (rule_id);

alter table ca_metadata_alert_rules_x_users
    add constraint fk_ca_metadata_alert_rules_x_u_user_id_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_metadata_alert_triggers
    charset = utf8mb4;

drop index i_name on ca_metadata_dictionary_entries;

alter table ca_metadata_dictionary_entries
    charset = utf8mb4;

create index i_bundle_name
    on ca_metadata_dictionary_entries (bundle_name);

alter table ca_metadata_dictionary_entry_labels
    charset = utf8mb4;

alter table ca_metadata_dictionary_entry_labels
    add constraint fk_ca_md_entry_labels_entry_id
        foreign key (entry_id) references ca_metadata_dictionary_entries (entry_id);

alter table ca_metadata_dictionary_entry_labels
    add constraint fk_ca_md_entry_labels_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_metadata_dictionary_rule_violations
    charset = utf8mb4;

alter table ca_metadata_dictionary_rules
    charset = utf8mb4;

alter table ca_metadata_element_labels
    alter column is_preferred drop default;

alter table ca_metadata_element_labels
    charset = utf8mb4;

alter table ca_metadata_elements
    modify deleted tinyint unsigned default '0' not null after `rank`;

alter table ca_metadata_elements
    charset = utf8mb4;

alter table ca_metadata_type_restrictions
    charset = utf8mb4;

alter table ca_movement_labels
    charset = utf8mb4;

alter table ca_movements
    modify idno_sort_num bigint default 0 not null after idno_sort;

drop index idno on ca_movements;

drop index idno_sort on ca_movements;

alter table ca_movements
    charset = utf8mb4;

create index i_idno
    on ca_movements (idno);

create index i_idno_sort
    on ca_movements (idno_sort);

alter table ca_movements
    add constraint fk_ca_movements_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_movements
    add constraint fk_ca_movements_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_movements
    add constraint fk_ca_movements_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_movements
    add constraint fk_ca_movements_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_movements_x_collections
    charset = utf8mb4;

alter table ca_movements_x_entities
    charset = utf8mb4;

alter table ca_movements_x_movements
    charset = utf8mb4;

alter table ca_movements_x_object_lots
    charset = utf8mb4;

alter table ca_movements_x_object_representations
    charset = utf8mb4;

alter table ca_movements_x_objects
    charset = utf8mb4;

alter table ca_movements_x_occurrences
    charset = utf8mb4;

alter table ca_movements_x_places
    charset = utf8mb4;

alter table ca_movements_x_storage_locations
    charset = utf8mb4;

alter table ca_movements_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_multipart_idno_sequences
    charset = utf8mb4;

alter table ca_notification_subjects
    modify read_on int unsigned null after was_read;

alter table ca_notification_subjects
    charset = utf8mb4;

alter table ca_notification_subjects
    add constraint fk_ca_notification_subjects_notification_id
        foreign key (notification_id) references ca_notifications (notification_id);

alter table ca_notifications
    charset = utf8mb4;

alter table ca_object_checkouts
    charset = utf8mb4;

alter table ca_object_labels
    charset = utf8mb4;

alter table ca_object_lot_labels
    charset = utf8mb4;

drop index i_idno_stub_sort_num on ca_object_lots;

alter table ca_object_lots
    charset = utf8mb4;

alter table ca_object_lots
    add constraint fk_ca_object_lots_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_object_lots
    add constraint fk_ca_object_lots_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_object_lots
    add constraint fk_ca_object_lots_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_object_lots
    add constraint fk_ca_object_lots_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_object_lots_x_collections
    charset = utf8mb4;

alter table ca_object_lots_x_entities
    charset = utf8mb4;

alter table ca_object_lots_x_object_lots
    charset = utf8mb4;

alter table ca_object_lots_x_object_representations
    charset = utf8mb4;

alter table ca_object_lots_x_occurrences
    charset = utf8mb4;

alter table ca_object_lots_x_places
    charset = utf8mb4;

alter table ca_object_lots_x_storage_locations
    charset = utf8mb4;

alter table ca_object_lots_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_object_representation_captions
    drop foreign key fk_ca_object_rep_captiopns_locale_id;

alter table ca_object_representation_captions
    charset = utf8mb4;

alter table ca_object_representation_captions
    add constraint fk_ca_object_rep_captions_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_object_representation_captions
    add constraint fk_ca_object_representation_cap_representation_id
        foreign key (representation_id) references ca_object_representations (representation_id);

alter table ca_object_representation_labels
    charset = utf8mb4;

create index i_locale_id
    on ca_object_representation_labels (locale_id);

create index i_name
    on ca_object_representation_labels (name(128));

create index i_name_sort
    on ca_object_representation_labels (name_sort);

create index i_representation_id
    on ca_object_representation_labels (representation_id);

create index i_type_id
    on ca_object_representation_labels (type_id);

alter table ca_object_representation_labels
    add constraint u_all
        unique (representation_id, name(255), type_id, locale_id);

alter table ca_object_representation_multifiles
    charset = utf8mb4;

alter table ca_object_representation_multifiles
    add constraint fk_ca_object_representation_mf_representation_id
        foreign key (representation_id) references ca_object_representations (representation_id);

alter table ca_object_representation_sidecars
    charset = utf8mb4;

alter table ca_object_representation_sidecars
    add constraint fk_ca_object_representation_sc_representation_id
        foreign key (representation_id) references ca_object_representations (representation_id);

alter table ca_object_representations
    modify idno_sort_num bigint default 0 not null after idno_sort;

alter table ca_object_representations
    charset = utf8mb4;

alter table ca_object_representations
    add constraint fk_ca_object_reps_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_object_representations
    add constraint fk_ca_object_reps_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_object_representations
    add constraint fk_ca_object_reps_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_object_representations
    add constraint fk_ca_object_reps_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_object_representations_x_collections
    charset = utf8mb4;

alter table ca_object_representations_x_entities
    charset = utf8mb4;

alter table ca_object_representations_x_object_representations
    charset = utf8mb4;

alter table ca_object_representations_x_occurrences
    charset = utf8mb4;

alter table ca_object_representations_x_places
    charset = utf8mb4;

alter table ca_object_representations_x_storage_locations
    charset = utf8mb4;

alter table ca_object_representations_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_objects
    modify idno_sort_num bigint default 0 not null after idno_sort;

alter table ca_objects
    charset = utf8mb4;

alter table ca_objects
    add constraint fk_ca_objects_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_objects
    add constraint fk_ca_objects_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_objects
    add constraint fk_ca_objects_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_objects
    add constraint fk_ca_objects_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_objects_x_collections
    charset = utf8mb4;

alter table ca_objects_x_entities
    charset = utf8mb4;

alter table ca_objects_x_object_representations
    charset = utf8mb4;

alter table ca_objects_x_objects
    charset = utf8mb4;

alter table ca_objects_x_occurrences
    charset = utf8mb4;

alter table ca_objects_x_places
    charset = utf8mb4;

alter table ca_objects_x_storage_locations
    charset = utf8mb4;

alter table ca_objects_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_occurrence_labels
    charset = utf8mb4;

alter table ca_occurrences
    modify idno_sort_num bigint default 0 not null after idno_sort;

alter table ca_occurrences
    charset = utf8mb4;

create index i_idno
    on ca_occurrences (idno);

create index i_idno_sort
    on ca_occurrences (idno_sort);

alter table ca_occurrences
    add constraint fk_ca_occurrences_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_occurrences
    add constraint fk_ca_occurrences_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_occurrences
    add constraint fk_ca_occurrences_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_occurrences
    add constraint fk_ca_occurrences_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_occurrences_x_collections
    charset = utf8mb4;

alter table ca_occurrences_x_occurrences
    charset = utf8mb4;

alter table ca_occurrences_x_storage_locations
    charset = utf8mb4;

alter table ca_occurrences_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_persistent_cache
    charset = utf8mb4;

alter table ca_place_labels
    charset = utf8mb4;

alter table ca_places
    modify idno_sort_num bigint default 0 not null after idno_sort;

alter table ca_places
    charset = utf8mb4;

alter table ca_places
    add constraint fk_ca_places_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_places
    add constraint fk_ca_places_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_places
    add constraint fk_ca_places_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_places
    add constraint fk_ca_places_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_places_x_collections
    charset = utf8mb4;

alter table ca_places_x_occurrences
    charset = utf8mb4;

alter table ca_places_x_places
    charset = utf8mb4;

alter table ca_places_x_storage_locations
    charset = utf8mb4;

alter table ca_places_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_relationship_relationships
    charset = utf8mb4;

alter table ca_relationship_type_labels
    charset = utf8mb4;

alter table ca_relationship_types
    charset = utf8mb4;

alter table ca_replication_log
    charset = utf8mb4;

alter table ca_representation_annotation_labels
    charset = utf8mb4;

alter table ca_representation_annotations
    charset = utf8mb4;

alter table ca_representation_annotations_x_entities
    charset = utf8mb4;

alter table ca_representation_annotations_x_objects
    charset = utf8mb4;

alter table ca_representation_annotations_x_occurrences
    charset = utf8mb4;

alter table ca_representation_annotations_x_places
    charset = utf8mb4;

alter table ca_representation_annotations_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_representation_transcriptions
    charset = utf8mb4;

alter table ca_representation_transcriptions
    add constraint fk_ca_representation_transcriptions_representation_id
        foreign key (representation_id) references ca_object_representations (representation_id);

alter table ca_representation_transcriptions
    add constraint fk_ca_representation_transcriptions_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_schema_updates
    charset = utf8mb4;

alter table ca_search_form_labels
    charset = utf8mb4;

alter table ca_search_form_labels
    add constraint fk_ca_search_form_labels_form_id
        foreign key (form_id) references ca_search_forms (form_id);

alter table ca_search_form_labels
    add constraint fk_ca_search_form_labels_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_search_form_placements
    charset = utf8mb4;

alter table ca_search_form_placements
    add constraint fk_ca_search_form_placements_form_id
        foreign key (form_id) references ca_search_forms (form_id);

alter table ca_search_form_type_restrictions
    charset = utf8mb4;

alter table ca_search_forms
    charset = utf8mb4;

alter table ca_search_forms
    add constraint fk_ca_search_forms_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_search_forms_x_user_groups
    charset = utf8mb4;

alter table ca_search_forms_x_user_groups
    add constraint fk_ca_search_forms_x_ug_form_id
        foreign key (form_id) references ca_search_forms (form_id);

alter table ca_search_forms_x_user_groups
    add constraint fk_ca_search_forms_x_ug_group_id
        foreign key (group_id) references ca_user_groups (group_id);

alter table ca_search_forms_x_users
    charset = utf8mb4;

alter table ca_search_forms_x_users
    add constraint fk_ca_search_forms_x_u_form_id
        foreign key (form_id) references ca_search_forms (form_id);

alter table ca_search_forms_x_users
    add constraint fk_ca_search_forms_x_u_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_search_indexing_queue
    charset = utf8mb4;

alter table ca_search_log
    charset = utf8mb4;

alter table ca_search_log
    add constraint fk_ca_search_log_form_id
        foreign key (form_id) references ca_search_forms (form_id);

alter table ca_search_log
    add constraint fk_ca_search_log_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_set_item_labels
    charset = utf8mb4;

alter table ca_set_item_labels
    add constraint fk_ca_set_item_labels_item_id
        foreign key (item_id) references ca_set_items (item_id);

alter table ca_set_item_labels
    add constraint fk_ca_set_item_labels_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_set_items
    charset = utf8mb4;

alter table ca_set_items
    add constraint fk_ca_set_items_set_id
        foreign key (set_id) references ca_sets (set_id);

alter table ca_set_labels
    charset = utf8mb4;

alter table ca_set_labels
    add constraint fk_ca_set_labels_locale_id
        foreign key (locale_id) references ca_locales (locale_id);

alter table ca_set_labels
    add constraint fk_ca_set_labels_set_id
        foreign key (set_id) references ca_sets (set_id);

alter table ca_sets
    charset = utf8mb4;

alter table ca_sets
    add constraint fk_ca_sets_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_sets_x_user_groups
    charset = utf8mb4;

alter table ca_sets_x_user_groups
    add constraint fk_ca_sets_x_ug_group_id
        foreign key (group_id) references ca_user_groups (group_id);

alter table ca_sets_x_user_groups
    add constraint fk_ca_sets_x_ug_set_id
        foreign key (set_id) references ca_sets (set_id);

alter table ca_sets_x_users
    modify pending_access tinyint unsigned null;

alter table ca_sets_x_users
    charset = utf8mb4;

alter table ca_sets_x_users
    add constraint fk_ca_sets_x_users_set_id
        foreign key (set_id) references ca_sets (set_id);

alter table ca_sets_x_users
    add constraint fk_ca_sets_x_users_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_site_page_media
    modify idno_sort_num bigint default 0 not null after idno_sort;

drop index i_idno_sort_num on ca_site_page_media;

alter table ca_site_page_media
    charset = utf8mb4;

create index idno_sort_num
    on ca_site_page_media (idno_sort_num);

alter table ca_site_page_media
    add constraint fk_ca_site_page_media_page_id
        foreign key (page_id) references ca_site_pages (page_id);

drop index i_locale_id on ca_site_pages;

alter table ca_site_pages
    charset = utf8mb4;

create index locale_id
    on ca_site_pages (locale_id);

alter table ca_site_pages
    add constraint fk_ca_site_pages_template_id
        foreign key (template_id) references ca_site_templates (template_id);

alter table ca_site_templates
    charset = utf8mb4;

alter table ca_sql_search_ngrams
    charset = utf8mb4;

alter table ca_sql_search_word_index
    modify index_id bigint unsigned auto_increment;

alter table ca_sql_search_word_index
    alter column field_num set default '';

alter table ca_sql_search_word_index
    charset = utf8mb4;

alter table ca_sql_search_words
    charset = utf8mb4;

alter table ca_storage_location_labels
    charset = utf8mb4;

alter table ca_storage_locations
    modify idno_sort_num bigint default 0 not null after idno_sort;

drop index idno on ca_storage_locations;

drop index idno_sort on ca_storage_locations;

alter table ca_storage_locations
    charset = utf8mb4;

create index i_idno
    on ca_storage_locations (idno);

create index i_idno_sort
    on ca_storage_locations (idno_sort);

alter table ca_storage_locations
    add constraint fk_ca_storage_locations_submission_group_id
        foreign key (submission_group_id) references ca_user_groups (group_id);

alter table ca_storage_locations
    add constraint fk_ca_storage_locations_submission_session_id
        foreign key (submission_session_id) references ca_media_upload_sessions (session_id);

alter table ca_storage_locations
    add constraint fk_ca_storage_locations_submission_status_id
        foreign key (submission_status_id) references ca_list_items (item_id);

alter table ca_storage_locations
    add constraint fk_ca_storage_locations_submission_user_id
        foreign key (submission_user_id) references ca_users (user_id);

alter table ca_storage_locations_x_storage_locations
    charset = utf8mb4;

alter table ca_storage_locations_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_task_queue
    charset = utf8mb4;

alter table ca_tour_labels
    charset = utf8mb4;

alter table ca_tour_stop_labels
    charset = utf8mb4;

alter table ca_tour_stops
    modify idno_sort_num bigint default 0 not null after idno_sort;

alter table ca_tour_stops
    charset = utf8mb4;

alter table ca_tour_stops_x_collections
    charset = utf8mb4;

alter table ca_tour_stops_x_entities
    charset = utf8mb4;

alter table ca_tour_stops_x_objects
    charset = utf8mb4;

alter table ca_tour_stops_x_occurrences
    charset = utf8mb4;

alter table ca_tour_stops_x_places
    charset = utf8mb4;

alter table ca_tour_stops_x_tour_stops
    charset = utf8mb4;

alter table ca_tour_stops_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_tours
    charset = utf8mb4;

alter table ca_user_groups
    modify for_public_use tinyint unsigned default '0' not null after description;

alter table ca_user_groups
    charset = utf8mb4;

alter table ca_user_groups
    add constraint fk_ca_user_groups_user_id
        foreign key (user_id) references ca_users (user_id);

alter table ca_user_notes
    charset = utf8mb4;

alter table ca_user_representation_annotation_labels
    charset = utf8mb4;

alter table ca_user_representation_annotations
    charset = utf8mb4;

alter table ca_user_representation_annotations_x_entities
    charset = utf8mb4;

alter table ca_user_representation_annotations_x_objects
    charset = utf8mb4;

alter table ca_user_representation_annotations_x_occurrences
    charset = utf8mb4;

alter table ca_user_representation_annotations_x_places
    charset = utf8mb4;

alter table ca_user_representation_annotations_x_vocabulary_terms
    charset = utf8mb4;

alter table ca_user_roles
    charset = utf8mb4;

alter table ca_user_sort_items
    charset = utf8mb4;

alter table ca_user_sorts
    charset = utf8mb4;

alter table ca_users
    drop foreign key fk_ca_entities_entity_id;

alter table ca_users
    charset = utf8mb4;

alter table ca_users
    add constraint fk_ca_users_entity_id
        foreign key (entity_id) references ca_entities (entity_id);

alter table ca_users_x_groups
    charset = utf8mb4;

alter table ca_users_x_roles
    charset = utf8mb4;

alter table ca_watch_list
    charset = utf8mb4;
kehh commented 11 months ago

(to be clear, this is after all current Providence database migrations up to 185 have been run on these databases.)

peterjanssens commented 11 months ago

can confirm I noticed this on our applications too, asked a question about it on gitter (Apr 26)

collectiveaccess commented 11 months ago

Looking through this there are four categories of misalignment:

  1. Table character set (UTFmb3 vs UTFmb4). This is expected. The application doesn't care which is used, but for users dealing with content containing 4 byte Unicode (such as some emojis), a migration to UTBmb4 is required. We've left this as a script that can be run optionally.
  2. Indexes. Some index names have changed but those name changes weren't always reflected in the migration, so the diff shows some dropping and recreating. This doesn't affect application function, but should be brought into alignment to avoid confusion. There aren't many of them.
  3. Field data types. Some of the fields in your diff are listed only because they are not in the same order as in the current database schema. This is not an issue and does not require modification. 9 fields do not align either by type, nullability or default value. With the exception of one, none should significantly affect application operation. The one that might is alter table ca_sql_search_word_index modify index_id bigint unsigned auto_increment, which is meant to prevent very large indexes from failing due to exhaustion of index keys. It was not pushed in a migration because changing the size of the key will trigger a really lengthy rebuild of the table, and in 10+ years we've only seen one system that needed it. I'd still hold this one back, but the others should be fixed.
  4. Constraints. Many constraints are missing in the migrated version. Because the application was originally designed to work with versions of MySQL that didn't support constraints, it does its own reference checking so this isn't critical and doesn't affect application operation. But I do think they should be added back for consistency.
kehh commented 11 months ago

I'm most of the way through a migration script to try and resolve these. We've just gotten bitten by the bigint issue on one system which is what led me here. Will send a PR a bit later this evening.

I'll do the bigint and UTFmb4 fixes in a separate migration tool if you don't want them in providence.

gautiermichelin commented 11 months ago
  1. I've migrated here some dbs under 1.8, this ca_sql_search_word_index migration to bigint can take a huge time to rebuild, had to bring back some mariabackups to do it locally before pushing back. could it be a good idea to have it as in Admin > Manage > ConfigurationCheck for example ? if still int, warning, if bigint ok ?
collectiveaccess commented 11 months ago

The utfmb4 and search index binging changes should not be done as migrations.