adm_program/installation/db_scripts/update_4_1.xml
<?xml version="1.0" encoding="UTF-8"?>
<update>
<step id="10">UPDATE %PREFIX%_preferences SET prf_value = '0' WHERE prf_name = 'registration_adopt_all_data'</step>
<step id="20">UPDATE %PREFIX%_categories SET cat_name = 'SYS_BASIC_DATA' WHERE cat_name LIKE 'SYS_MASTER_DATA'</step>
<step id="30">UPDATE %PREFIX%_categories SET cat_name_INTERN = 'BASIC_DATA' WHERE cat_name_intern LIKE 'MASTER_DATA'</step>
<step id="33">DELETE FROM %PREFIX%_messages_content WHERE msc_msg_id IN (SELECT msg_id FROM %PREFIX%_messages WHERE msg_type = 'CHAT')</step>
<step id="35">DELETE FROM %PREFIX%_messages WHERE msg_type = 'CHAT'</step>
<step id="40">CREATE TABLE %PREFIX%_messages_recipients
(
msr_id integer unsigned NOT NULL AUTO_INCREMENT,
msr_msg_id integer unsigned NOT NULL,
msr_rol_id integer unsigned,
msr_usr_id integer unsigned,
msr_role_mode smallint NOT NULL DEFAULT 0,
PRIMARY KEY (msr_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci</step>
<step id="50">ALTER TABLE %PREFIX%_messages_recipients
ADD CONSTRAINT %PREFIX%_fk_msr_msg_id FOREIGN KEY (msr_msg_id) REFERENCES %PREFIX%_messages (msg_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_msr_rol_id FOREIGN KEY (msr_rol_id) REFERENCES %PREFIX%_roles (rol_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_msr_usr_id FOREIGN KEY (msr_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT</step>
<step id="60">ComponentUpdateSteps::updateStep41MigrateMessageRecipients</step>
<step id="70">ALTER TABLE %PREFIX%_messages_content DROP COLUMN msc_part_id</step>
<step id="80">CREATE TABLE %PREFIX%_messages_attachments
(
msa_id integer unsigned NOT NULL AUTO_INCREMENT,
msa_msg_id integer unsigned NOT NULL,
msa_file_name varchar(256) NOT NULL,
msa_original_file_name varchar(256) NOT NULL,
PRIMARY KEY (msa_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;</step>
<step id="90">ALTER TABLE %PREFIX%_messages_attachments
ADD CONSTRAINT %PREFIX%_fk_msa_msg_id FOREIGN KEY (msa_msg_id) REFERENCES %PREFIX%_messages (msg_id) ON DELETE RESTRICT ON UPDATE RESTRICT</step>
<step id="100">DELETE FROM %PREFIX%_preferences WHERE prf_name IN ('enable_chat_module')</step>
<step id="110">UPDATE %PREFIX%_menu SET men_description = 'SYS_EMAIL_DESC' WHERE men_description = 'MAI_EMAIL_DESC'</step>
<step id="120">ALTER TABLE %PREFIX%_users DROP COLUMN usr_new_password</step>
<step id="130">ALTER TABLE %PREFIX%_users DROP COLUMN usr_activation_code</step>
<step id="140">ALTER TABLE %PREFIX%_users ADD COLUMN usr_pw_reset_id varchar(50)</step>
<step id="150">ALTER TABLE %PREFIX%_users ADD COLUMN usr_pw_reset_timestamp timestamp NULL DEFAULT NULL</step>
<step id="160">ComponentUpdateSteps::updateStep41AddSystemmailText</step>
<step id="170">DELETE FROM %PREFIX%_texts WHERE txt_name = 'SYSMAIL_ACTIVATION_LINK'</step>
<step id="180">ComponentUpdateSteps::updateStep41AddMembersManagementDefaultList</step>
<step id="190">UPDATE %PREFIX%_components SET com_name = 'SYS_WEBLINKS' WHERE com_name = 'LNK_WEBLINKS'</step>
<step id="200">UPDATE %PREFIX%_menu SET men_name = 'SYS_WEBLINKS', men_description = 'SYS_WEBLINKS_DESC' WHERE men_name_intern = 'weblinks'</step>
<step id="210">UPDATE %PREFIX%_components SET com_name = 'SYS_USER_MANAGEMENT' WHERE com_name = 'MEM_USER_MANAGEMENT'</step>
<step id="220">UPDATE %PREFIX%_menu SET men_name = 'SYS_USER_MANAGEMENT', men_description = 'SYS_MEMBERS_DESC' WHERE men_name_intern = 'usrmgt'</step>
<step id="230">UPDATE %PREFIX%_menu SET men_name = 'SYS_EMAIL', men_description = 'SYS_EMAIL_DESC' WHERE men_name_intern = 'mail'</step>
<step id="240">UPDATE %PREFIX%_menu SET men_name = 'SYS_NEW_REGISTRATIONS', men_description = 'SYS_MANAGE_NEW_REGISTRATIONS_DESC' WHERE men_name_intern = 'newreg'</step>
<step id="250">ALTER TABLE %PREFIX%_user_fields ADD COLUMN usf_description_inline boolean NOT NULL DEFAULT '0'</step>
<step id="260">ALTER TABLE %PREFIX%_users ADD COLUMN usr_uuid varchar(36)</step>
<step id="265">ALTER TABLE %PREFIX%_roles ADD COLUMN rol_uuid varchar(36)</step>
<step id="270">ALTER TABLE %PREFIX%_announcements ADD COLUMN ann_uuid varchar(36)</step>
<step id="275">ALTER TABLE %PREFIX%_categories ADD COLUMN cat_uuid varchar(36)</step>
<step id="280">ALTER TABLE %PREFIX%_dates ADD COLUMN dat_uuid varchar(36)</step>
<step id="285">ALTER TABLE %PREFIX%_files ADD COLUMN fil_uuid varchar(36)</step>
<step id="290">ALTER TABLE %PREFIX%_folders ADD COLUMN fol_uuid varchar(36)</step>
<step id="295">ALTER TABLE %PREFIX%_guestbook ADD COLUMN gbo_uuid varchar(36)</step>
<step id="300">ALTER TABLE %PREFIX%_guestbook_comments ADD COLUMN gbc_uuid varchar(36)</step>
<step id="305">ALTER TABLE %PREFIX%_links ADD COLUMN lnk_uuid varchar(36)</step>
<step id="310">ALTER TABLE %PREFIX%_photos ADD COLUMN pho_uuid varchar(36)</step>
<step id="315">ALTER TABLE %PREFIX%_lists ADD COLUMN lst_uuid varchar(36)</step>
<step id="320">ALTER TABLE %PREFIX%_menu ADD COLUMN men_uuid varchar(36)</step>
<step id="325">ALTER TABLE %PREFIX%_messages ADD COLUMN msg_uuid varchar(36)</step>
<step id="330">ALTER TABLE %PREFIX%_organizations ADD COLUMN org_uuid varchar(36)</step>
<step id="335">ALTER TABLE %PREFIX%_rooms ADD COLUMN room_uuid varchar(36)</step>
<step id="340">ALTER TABLE %PREFIX%_user_fields ADD COLUMN usf_uuid varchar(36)</step>
<step id="350">ALTER TABLE %PREFIX%_user_relation_types ADD COLUMN urt_uuid varchar(36)</step>
<step id="360">ALTER TABLE %PREFIX%_members ADD COLUMN mem_uuid varchar(36)</step>
<step id="400">ComponentUpdateSteps::updateStep41AddUuid</step>
<step id="410" database="mysql">ALTER TABLE %PREFIX%_users MODIFY COLUMN usr_uuid varchar(36) NOT NULL</step>
<step id="415" database="pgsql">ALTER TABLE %PREFIX%_users ALTER COLUMN usr_uuid SET NOT NULL</step>
<step id="420">CREATE UNIQUE INDEX %PREFIX%_idx_usr_uuid ON %PREFIX%_users (usr_uuid)</step>
<step id="430" database="mysql">ALTER TABLE %PREFIX%_roles MODIFY COLUMN rol_uuid varchar(36) NOT NULL</step>
<step id="435" database="pgsql">ALTER TABLE %PREFIX%_roles ALTER COLUMN rol_uuid SET NOT NULL</step>
<step id="440">CREATE UNIQUE INDEX %PREFIX%_idx_rol_uuid ON %PREFIX%_roles (rol_uuid)</step>
<step id="450" database="mysql">ALTER TABLE %PREFIX%_announcements MODIFY COLUMN ann_uuid varchar(36) NOT NULL</step>
<step id="455" database="pgsql">ALTER TABLE %PREFIX%_announcements ALTER COLUMN ann_uuid SET NOT NULL</step>
<step id="460">CREATE UNIQUE INDEX %PREFIX%_idx_ann_uuid ON %PREFIX%_announcements (ann_uuid)</step>
<step id="470" database="mysql">ALTER TABLE %PREFIX%_categories MODIFY COLUMN cat_uuid varchar(36) NOT NULL</step>
<step id="475" database="pgsql">ALTER TABLE %PREFIX%_categories ALTER COLUMN cat_uuid SET NOT NULL</step>
<step id="480">CREATE UNIQUE INDEX %PREFIX%_idx_cat_uuid ON %PREFIX%_categories (cat_uuid)</step>
<step id="490" database="mysql">ALTER TABLE %PREFIX%_dates MODIFY COLUMN dat_uuid varchar(36) NOT NULL</step>
<step id="495" database="pgsql">ALTER TABLE %PREFIX%_dates ALTER COLUMN dat_uuid SET NOT NULL</step>
<step id="500">CREATE UNIQUE INDEX %PREFIX%_idx_dat_uuid ON %PREFIX%_dates (dat_uuid)</step>
<step id="510" database="mysql">ALTER TABLE %PREFIX%_files MODIFY COLUMN fil_uuid varchar(36) NOT NULL</step>
<step id="515" database="pgsql">ALTER TABLE %PREFIX%_files ALTER COLUMN fil_uuid SET NOT NULL</step>
<step id="520">CREATE UNIQUE INDEX %PREFIX%_idx_fil_uuid ON %PREFIX%_files (fil_uuid)</step>
<step id="530" database="mysql">ALTER TABLE %PREFIX%_folders MODIFY COLUMN fol_uuid varchar(36) NOT NULL</step>
<step id="535" database="pgsql">ALTER TABLE %PREFIX%_folders ALTER COLUMN fol_uuid SET NOT NULL</step>
<step id="540">CREATE UNIQUE INDEX %PREFIX%_idx_fol_uuid ON %PREFIX%_folders (fol_uuid)</step>
<step id="550" database="mysql">ALTER TABLE %PREFIX%_guestbook MODIFY COLUMN gbo_uuid varchar(36) NOT NULL</step>
<step id="555" database="pgsql">ALTER TABLE %PREFIX%_guestbook ALTER COLUMN gbo_uuid SET NOT NULL</step>
<step id="560">CREATE UNIQUE INDEX %PREFIX%_idx_gbo_uuid ON %PREFIX%_guestbook (gbo_uuid)</step>
<step id="570" database="mysql">ALTER TABLE %PREFIX%_guestbook_comments MODIFY COLUMN gbc_uuid varchar(36) NOT NULL</step>
<step id="575" database="pgsql">ALTER TABLE %PREFIX%_guestbook_comments ALTER COLUMN gbc_uuid SET NOT NULL</step>
<step id="580">CREATE UNIQUE INDEX %PREFIX%_idx_gbc_uuid ON %PREFIX%_guestbook_comments (gbc_uuid)</step>
<step id="590" database="mysql">ALTER TABLE %PREFIX%_links MODIFY COLUMN lnk_uuid varchar(36) NOT NULL</step>
<step id="595" database="pgsql">ALTER TABLE %PREFIX%_links ALTER COLUMN lnk_uuid SET NOT NULL</step>
<step id="600">CREATE UNIQUE INDEX %PREFIX%_idx_lnk_uuid ON %PREFIX%_links (lnk_uuid)</step>
<step id="610" database="mysql">ALTER TABLE %PREFIX%_photos MODIFY COLUMN pho_uuid varchar(36) NOT NULL</step>
<step id="615" database="pgsql">ALTER TABLE %PREFIX%_photos ALTER COLUMN pho_uuid SET NOT NULL</step>
<step id="620">CREATE UNIQUE INDEX %PREFIX%_idx_pho_uuid ON %PREFIX%_photos (pho_uuid)</step>
<step id="630" database="mysql">ALTER TABLE %PREFIX%_lists MODIFY COLUMN lst_uuid varchar(36) NOT NULL</step>
<step id="635" database="pgsql">ALTER TABLE %PREFIX%_lists ALTER COLUMN lst_uuid SET NOT NULL</step>
<step id="640">CREATE UNIQUE INDEX %PREFIX%_idx_lst_uuid ON %PREFIX%_lists (lst_uuid)</step>
<step id="650" database="mysql">ALTER TABLE %PREFIX%_menu MODIFY COLUMN men_uuid varchar(36) NOT NULL</step>
<step id="655" database="pgsql">ALTER TABLE %PREFIX%_menu ALTER COLUMN men_uuid SET NOT NULL</step>
<step id="660">CREATE UNIQUE INDEX %PREFIX%_idx_men_uuid ON %PREFIX%_menu (men_uuid)</step>
<step id="670" database="mysql">ALTER TABLE %PREFIX%_messages MODIFY COLUMN msg_uuid varchar(36) NOT NULL</step>
<step id="675" database="pgsql">ALTER TABLE %PREFIX%_messages ALTER COLUMN msg_uuid SET NOT NULL</step>
<step id="680">CREATE UNIQUE INDEX %PREFIX%_idx_msg_uuid ON %PREFIX%_messages (msg_uuid)</step>
<step id="690" database="mysql">ALTER TABLE %PREFIX%_organizations MODIFY COLUMN org_uuid varchar(36) NOT NULL</step>
<step id="695" database="pgsql">ALTER TABLE %PREFIX%_organizations ALTER COLUMN org_uuid SET NOT NULL</step>
<step id="700">CREATE UNIQUE INDEX %PREFIX%_idx_org_uuid ON %PREFIX%_organizations (org_uuid)</step>
<step id="710" database="mysql">ALTER TABLE %PREFIX%_rooms MODIFY COLUMN room_uuid varchar(36) NOT NULL</step>
<step id="715" database="pgsql">ALTER TABLE %PREFIX%_rooms ALTER COLUMN room_uuid SET NOT NULL</step>
<step id="720">CREATE UNIQUE INDEX %PREFIX%_idx_room_uuid ON %PREFIX%_rooms (room_uuid)</step>
<step id="730" database="mysql">ALTER TABLE %PREFIX%_user_fields MODIFY COLUMN usf_uuid varchar(36) NOT NULL</step>
<step id="735" database="pgsql">ALTER TABLE %PREFIX%_user_fields ALTER COLUMN usf_uuid SET NOT NULL</step>
<step id="740">CREATE UNIQUE INDEX %PREFIX%_idx_usf_uuid ON %PREFIX%_user_fields (usf_uuid)</step>
<step id="750" database="mysql">ALTER TABLE %PREFIX%_user_relation_types MODIFY COLUMN urt_uuid varchar(36) NOT NULL</step>
<step id="755" database="pgsql">ALTER TABLE %PREFIX%_user_relation_types ALTER COLUMN urt_uuid SET NOT NULL</step>
<step id="760">CREATE UNIQUE INDEX %PREFIX%_idx_urt_uuid ON %PREFIX%_user_relation_types (urt_uuid)</step>
<step id="770" database="mysql">ALTER TABLE %PREFIX%_roles MODIFY COLUMN rol_name varchar(100)</step>
<step id="780" database="pgsql">ALTER TABLE %PREFIX%_roles ALTER COLUMN rol_name TYPE varchar(100)</step>
<step id="790" database="mysql">ALTER TABLE %PREFIX%_members MODIFY COLUMN mem_uuid varchar(36) NOT NULL</step>
<step id="800">CREATE UNIQUE INDEX %PREFIX%_idx_mem_uuid ON %PREFIX%_members (mem_uuid)</step>
<step id="810">ALTER TABLE %PREFIX%_sessions ADD COLUMN ses_reload boolean NOT NULL DEFAULT '0'</step>
<step id="820">ALTER TABLE %PREFIX%_sessions DROP COLUMN ses_renew</step>
<step id="825">ComponentUpdateSteps::updateStep41MoveEcardTemplates</step>
<step id="830">ALTER TABLE %PREFIX%_messages DROP COLUMN msg_usr_id_receiver</step>
<step id="840" database="pgsql">ComponentUpdateSteps::updateStep41PostgreSqlSetBoolean</step>
<step id="850">INSERT INTO %PREFIX%_components (com_type, com_name, com_name_intern, com_version, com_beta)
VALUES ('MODULE', 'SYS_CATEGORY_REPORT', 'CATEGORY-REPORT', '4.1.0', 0)</step>
<step id="860">INSERT INTO %PREFIX%_menu (men_com_id, men_men_id_parent, men_uuid, men_node, men_order, men_standard, men_name_intern, men_url, men_icon, men_name, men_description)
VALUES ((SELECT com_id FROM %PREFIX%_components WHERE com_name_intern = 'CATEGORY-REPORT'), 1, %UUID%, false, 9, true, 'category-report', '/adm_program/modules/category-report/category_report.php', 'fa-list-ul', 'SYS_CATEGORY_REPORT', 'SYS_CATEGORY_REPORT_DESC')</step>
<step id="870">CREATE TABLE %PREFIX%_category_report
(
crt_id integer unsigned NOT NULL AUTO_INCREMENT,
crt_org_id integer unsigned,
crt_name varchar(100) NOT NULL,
crt_col_fields text,
crt_selection_role varchar(100),
crt_selection_cat varchar(100),
crt_number_col boolean NOT NULL DEFAULT '0',
PRIMARY KEY (crt_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci</step>
<step id="880">ALTER TABLE %PREFIX%_category_report ADD CONSTRAINT %PREFIX%_fk_crt_org FOREIGN KEY (crt_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT</step>
<step id="890">ComponentUpdateSteps::updateStep41CategoryReportMigration</step>
<step id="900">UPDATE %PREFIX%_components SET com_name = 'SYS_MEMBERS' WHERE com_name = 'SYS_USER_MANAGEMENT'</step>
<step id="910">UPDATE %PREFIX%_menu SET men_name = 'SYS_MEMBERS', men_description = 'SYS_MEMBERS_DESC' WHERE men_name_intern = 'usrmgt'</step>
<step id="920">UPDATE %PREFIX%_user_fields SET usf_value_list = 'fa-mars|SYS_MALE
fa-venus|SYS_FEMALE
fa-mars-stroke-v|SYS_DIVERSE' WHERE usf_name_intern = 'GENDER' AND usf_value_list LIKE 'fa-mars|%'</step>
<step id="930">DELETE FROM %PREFIX%_list_columns WHERE lsc_usf_id IS NULL AND lsc_special_field IS NULL</step>
<step id="950" database="mysql">ALTER TABLE %PREFIX%_menu MODIFY COLUMN men_url varchar(2000)</step>
<step id="960" database="pgsql">ALTER TABLE %PREFIX%_menu ALTER COLUMN men_url TYPE varchar(2000)</step>
<step id="970" database="mysql">ALTER TABLE %PREFIX%_menu MODIFY COLUMN men_icon varchar(100)</step>
<step id="980" database="pgsql">ALTER TABLE %PREFIX%_menu ALTER COLUMN men_icon TYPE varchar(100)</step>
<step id="990" database="mysql">ALTER TABLE %PREFIX%_user_fields MODIFY COLUMN usf_icon varchar(100)</step>
<step id="1000" database="pgsql">ALTER TABLE %PREFIX%_user_fields ALTER COLUMN usf_icon TYPE varchar(100)</step>
<step id="1010">ComponentUpdateSteps::updateStep41CleanUpInternalNameProfileFields</step>
<step id="1020">ComponentUpdateSteps::updateStep41CleanUpRoleNames</step>
<step id="1030">update %PREFIX%_preferences set prf_value = 0 WHERE prf_name IN ('enable_announcements_module', 'enable_photo_module', 'enable_guestbook_module', 'enable_dates_module', 'enable_weblinks_module') and prf_value LIKE '' or prf_value is null</step>
<step>stop</step>
</update>