-- /************************************************************************ -- * -- * MySQL Script for civicrm database/tables - upgradation from 2.1 -> 2.2 -- * -- *************************************************************************/ -- Please add script for all the schema / fixed-data related modifications to -- this sql script as you resolve 2.2 issues. Include the issue number which -- is the source of the change, as part of the comment. -- fix version column first ALTER TABLE `civicrm_domain` MODIFY version varchar(32) COMMENT 'The civicrm version this instance is running'; -- make the register_by_id cascade in civicrm_participant ALTER TABLE `civicrm_participant` DROP FOREIGN KEY `FK_civicrm_participant_registered_by_id`; ALTER TABLE `civicrm_participant` ADD CONSTRAINT `FK_civicrm_participant_registered_by_id` FOREIGN KEY (`registered_by_id`) REFERENCES `civicrm_participant` (`id`) ON DELETE CASCADE; -- merge civicrm_event_page to civicrm_event ALTER TABLE `civicrm_event` {if $multilingual} {foreach from=$locales item=locale} ADD `intro_text_{$locale}` text collate utf8_unicode_ci, ADD `footer_text_{$locale}` text collate utf8_unicode_ci, ADD `confirm_title_{$locale}` varchar(255) collate utf8_unicode_ci default NULL, ADD `confirm_text_{$locale}` text collate utf8_unicode_ci, ADD `confirm_footer_text_{$locale}` text collate utf8_unicode_ci, ADD `confirm_email_text_{$locale}` text collate utf8_unicode_ci, ADD `confirm_from_name_{$locale}` varchar(255) collate utf8_unicode_ci default NULL, ADD `thankyou_title_{$locale}` varchar(255) collate utf8_unicode_ci default NULL, ADD `thankyou_text_{$locale}` text collate utf8_unicode_ci, ADD `thankyou_footer_text_{$locale}` text collate utf8_unicode_ci, ADD `pay_later_text_{$locale}` text collate utf8_unicode_ci, ADD `pay_later_receipt_{$locale}` text collate utf8_unicode_ci, {/foreach} {else} ADD `intro_text` text collate utf8_unicode_ci COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.', ADD `footer_text` text collate utf8_unicode_ci COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.', ADD `confirm_title` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'Title for Confirmation page.', ADD `confirm_text` text collate utf8_unicode_ci COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.', ADD `confirm_footer_text` text collate utf8_unicode_ci COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.', ADD `confirm_email_text` text collate utf8_unicode_ci COMMENT 'text to include above standard event info on confirmation email. emails are text-only, so do not allow html for now', ADD `confirm_from_name` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'FROM email name used for confirmation emails.', ADD `thankyou_title` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'Title for ThankYou page.', ADD `thankyou_text` text collate utf8_unicode_ci COMMENT 'ThankYou Text.', ADD `thankyou_footer_text` text collate utf8_unicode_ci COMMENT 'Footer message.', ADD `pay_later_text` text collate utf8_unicode_ci COMMENT 'The text displayed to the user in the main form', ADD `pay_later_receipt` text collate utf8_unicode_ci COMMENT 'The receipt sent to the user instead of the normal receipt text', {/if} ADD `is_email_confirm` tinyint(4) default '0' COMMENT 'If true, confirmation is automatically emailed to contact on successful registration.', ADD `confirm_from_email` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'FROM email address used for confirmation emails.', ADD `cc_confirm` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'comma-separated list of email addresses to cc each time a confirmation is sent', ADD `bcc_confirm` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'comma-separated list of email addresses to bcc each time a confirmation is sent', ADD `default_fee_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_option_value.', ADD `default_discount_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_option_value.', ADD `is_pay_later` tinyint(4) default '0' COMMENT 'if true - allows the user to send payment directly to the org later', ADD `is_multiple_registrations` tinyint(4) default '0' COMMENT 'if true - allows the user to register multiple participants for event', ALTER `max_participants` DROP DEFAULT; UPDATE civicrm_event ce LEFT JOIN civicrm_event_page cp ON ce.id=cp.event_id SET {if $multilingual} {foreach from=$locales item=locale} ce.intro_text_{$locale} = cp.intro_text_{$locale}, ce.footer_text_{$locale} = cp.footer_text_{$locale}, ce.confirm_title_{$locale} = cp.confirm_title_{$locale}, ce.confirm_text_{$locale} = cp.confirm_text_{$locale}, ce.confirm_footer_text_{$locale} = cp.confirm_footer_text_{$locale}, ce.confirm_email_text_{$locale} = cp.confirm_email_text_{$locale}, ce.confirm_from_name_{$locale} = cp.confirm_from_name_{$locale}, ce.thankyou_title_{$locale} = cp.thankyou_title_{$locale}, ce.thankyou_text_{$locale} = cp.thankyou_text_{$locale}, ce.thankyou_footer_text_{$locale} = cp.thankyou_footer_text_{$locale}, ce.pay_later_text_{$locale} = cp.pay_later_text_{$locale}, ce.pay_later_receipt_{$locale} = cp.pay_later_receipt_{$locale}, {/foreach} {else} ce.intro_text = cp.intro_text, ce.footer_text = cp.footer_text, ce.confirm_title = cp.confirm_title, ce.confirm_text = cp.confirm_text, ce.confirm_footer_text = cp.confirm_footer_text, ce.confirm_email_text = cp.confirm_email_text, ce.confirm_from_name = cp.confirm_from_name, ce.thankyou_title = cp.thankyou_title, ce.thankyou_text = cp.thankyou_text, ce.thankyou_footer_text = cp.thankyou_footer_text, ce.pay_later_text = cp.pay_later_text, ce.pay_later_receipt = cp.pay_later_receipt, {/if} ce.is_email_confirm = cp.is_email_confirm, ce.confirm_from_email = cp.confirm_from_email, ce.cc_confirm = cp.cc_confirm, ce.bcc_confirm = cp.bcc_confirm, ce.default_fee_id = cp.default_fee_id, ce.default_discount_id = cp.default_discount_id, ce.is_pay_later = cp.is_pay_later, ce.is_multiple_registrations = cp.is_multiple_registrations; -- CRM-3391 -- Update table name in civicrm_option_group (Fee Level) -- cleanup for unused option group DELETE og.* FROM civicrm_option_group og LEFT JOIN civicrm_event_page ep ON ep.id = SUBSTRING_INDEX( SUBSTRING( og.name, 27 ) , '.discount', 1) WHERE og.name LIKE 'civicrm_event_page.amount%' AND ep.id IS NULL; {foreach from =$eventFees item=ogid} SELECT @option_group_id := id ,@event_page_id := SUBSTRING_INDEX( SUBSTRING( name, 27 ) , '.discount', 1 ) FROM civicrm_option_group WHERE `name` LIKE 'civicrm_event_page.amount%' LIMIT 1; SELECT @event_id := ep.event_id FROM civicrm_event_page ep WHERE ep.id = @event_page_id; UPDATE `civicrm_option_group` SET `name` = REPLACE( name, CONCAT_WS('.', 'civicrm_event_page.amount', @event_page_id ), CONCAT_WS('.', 'civicrm_event.amount', @event_id ) ) WHERE `id` = @option_group_id; {/foreach} -- Update table entity_table and entity_id civicrm_tell_friend UPDATE civicrm_tell_friend tf SET tf.`entity_table` = 'civicrm_event', tf.`entity_id` = (SELECT e.`event_id` FROM civicrm_event_page e WHERE e.`id` = tf.`entity_id`) WHERE tf.`entity_table`='civicrm_event_page' ; --CRM-4256 -- Update table name in civicrm_price_set_entity (Price Set) UPDATE civicrm_price_set_entity pse SET pse.`entity_table`='civicrm_event', pse.`entity_id` = (SELECT e.`event_id` FROM civicrm_event_page e WHERE e.`id` = pse.`entity_id`) WHERE pse.`entity_table`='civicrm_event_page'; -- /******************************************************* -- * -- * Drop civicrm_event_page table -- * -- *******************************************************/ DROP TABLE civicrm_event_page; -- CRM-3546 {if $multilingual} INSERT INTO civicrm_option_group (name, {foreach from=$locales item=locale}description_{$locale},{/foreach} is_reserved, is_active) VALUES ('visibility', {foreach from=$locales item=locale}'Visibility',{/foreach} 0, 1); {else} INSERT INTO civicrm_option_group (name, description, is_reserved, is_active) VALUES ('visibility', 'Visibility', 0, 1); {/if} SELECT @option_group_id_vis := max(id) from civicrm_option_group where name = 'visibility'; {if $multilingual} INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight) VALUES (@option_group_id_vis, {foreach from=$locales item=locale}'Public',{/foreach} 1, 'public', 1), (@option_group_id_vis, {foreach from=$locales item=locale}'Admin',{/foreach} 2, 'admin', 2); {else} INSERT INTO civicrm_option_value (option_group_id, label, value, name, weight) VALUES (@option_group_id_vis, 'Public', 1, 'public', 1), (@option_group_id_vis, 'Admin', 2, 'admin', 2); {/if} ALTER TABLE civicrm_option_value ADD visibility_id int unsigned default NULL; -- * A Personal Campaign Page Block stores admin configurable status options and rules CREATE TABLE civicrm_pcp_block ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'PCP block Id', entity_table varchar(64) , entity_id int unsigned NOT NULL COMMENT 'FK to civicrm_contribution_page.id', supporter_profile_id int unsigned DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?', is_approval_needed tinyint DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?', is_tellfriend_enabled tinyint DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?', tellfriend_limit int unsigned DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend', link_text varchar(255) DEFAULT NULL COMMENT 'Link text for PCP.', is_active tinyint DEFAULT 1 COMMENT 'Is Personal Campaign Page Block enabled/active?', PRIMARY KEY ( id ), CONSTRAINT FK_civicrm_pcp_block_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- * civicrm_pcp CREATE TABLE civicrm_pcp ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Personal Campaign Page ID', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', status_id int unsigned NOT NULL , title varchar(255) DEFAULT NULL , intro_text text DEFAULT NULL , page_text text DEFAULT NULL , donate_link_text varchar(255) DEFAULT NULL , contribution_page_id int unsigned NOT NULL COMMENT 'The Contribution Page which triggered this pcp', is_thermometer int unsigned DEFAULT 0 , is_honor_roll int unsigned DEFAULT 0 , goal_amount decimal(20,2) COMMENT 'Goal amount of this Personal Campaign Page.', referer varchar(255) DEFAULT NULL , is_active tinyint DEFAULT 0 COMMENT 'Is Personal Campaign Page enabled/active?', PRIMARY KEY ( id ), CONSTRAINT FK_civicrm_pcp_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_pcp_contribution_page_id FOREIGN KEY (contribution_page_id) REFERENCES civicrm_contribution_page(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- * civicrm_contribution_soft CREATE TABLE civicrm_contribution_soft ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Soft Contribution ID', contribution_id int unsigned NOT NULL COMMENT 'FK to contribution table.', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', amount decimal(20,2) NOT NULL COMMENT 'Amount of this soft contribution.', pcp_id int unsigned DEFAULT NULL , pcp_display_in_roll tinyint DEFAULT 0 , pcp_roll_nickname varchar(255) DEFAULT NULL , pcp_personal_note varchar(255) DEFAULT NULL , PRIMARY KEY ( id ) , INDEX index_id( pcp_id ) , CONSTRAINT FK_civicrm_contribution_soft_contribution_id FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_soft_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- fixed for CRM-2105 Greeting Type {if $multilingual} INSERT INTO civicrm_option_group (name, {foreach from=$locales item=locale}description_{$locale},{/foreach} is_reserved, is_active) VALUES ('greeting_type', {foreach from=$locales item=locale}'Greeting Type',{/foreach} 0, 1); {else} INSERT INTO civicrm_option_group (name, description, is_reserved, is_active ) VALUES ('greeting_type', 'Greeting Type', 0, 1 ); {/if} SELECT @option_group_id_gr := max(id) from civicrm_option_group where name = 'greeting_type'; {if $multilingual} INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, is_default, weight, is_reserved) VALUES (@option_group_id_gr, {foreach from=$locales item=locale}'Dear [first]',{/foreach} 1, 'Dear [first]', 1, 1, 0), (@option_group_id_gr, {foreach from=$locales item=locale}'Dear [prefix] [first] [last]',{/foreach} 2, 'Dear [prefix] [first] [last]', 0, 2, 0), (@option_group_id_gr, {foreach from=$locales item=locale}'Dear [prefix] [last]',{/foreach} 3, 'Dear [prefix] [last]', 0, 3, 0), (@option_group_id_gr, {foreach from=$locales item=locale}'Customized',{/foreach} 4, 'Customized', 0, 4, 1); {else} INSERT INTO civicrm_option_value (option_group_id, label, value, name, is_default, weight, is_reserved) VALUES (@option_group_id_gr, 'Dear [first]', 1, 'Dear [first]', 1, 1, 0), (@option_group_id_gr, 'Dear [prefix] [first] [last]', 2, 'Dear [prefix] [first] [last]', 0, 2, 0), (@option_group_id_gr, 'Dear [prefix] [last]', 3, 'Dear [prefix] [last]', 0, 3, 0), (@option_group_id_gr, 'Customized', 4, 'Customized', 0, 4, 1); {/if} ALTER TABLE `civicrm_contact` ADD `greeting_type_id` int(10) unsigned NULL DEFAULT NULL AFTER greeting_type; SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Dear [first]'; UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Informal'; SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Dear [prefix] [first] [last]'; UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Formal'; SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Dear [prefix] [last]'; UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Honorific'; SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Customized'; UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Custom'; ALTER TABLE `civicrm_contact` DROP `greeting_type`; -- Add 'Address Edit option IM & Open ID -- CRM- 3419 SELECT @option_group_id_ao := max(id) from civicrm_option_group where name = 'address_options'; {if $multilingual} INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight, is_reserved) VALUES (@option_group_id_ao, {foreach from=$locales item=locale}'Instant Messenger',{/foreach} 13, 'im', 13, 1), (@option_group_id_ao, {foreach from=$locales item=locale}'OpenID',{/foreach} 14, 'openid', 14, 1); {else} INSERT INTO civicrm_option_value (option_group_id, label, value, name, weight, is_reserved) VALUES (@option_group_id_ao, 'Instant Messenger', 13, 'im', 13, 1), (@option_group_id_ao, 'OpenID', 14, 'openid', 14, 1); {/if} UPDATE civicrm_preferences SET address_options = CONCAT(address_options, '1314'); -- * Fix for CRM-3248 {if $multilingual} INSERT INTO civicrm_option_group (name, {foreach from=$locales item=locale}description_{$locale},{/foreach} is_reserved, is_active ) VALUES ('phone_type', {foreach from=$locales item=locale}'Phone Type',{/foreach} 0, 1); {else} INSERT INTO civicrm_option_group (name, description, is_reserved, is_active) VALUES ('phone_type', 'Phone Type', 0, 1); {/if} SELECT @option_group_id_pt := max(id) from civicrm_option_group where name = 'phone_type'; {if $multilingual} INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight) VALUES (@option_group_id_pt, {foreach from=$locales item=locale}'Phone',{/foreach} 1, 'Phone', 1), (@option_group_id_pt, {foreach from=$locales item=locale}'Mobile',{/foreach} 2, 'Mobile', 2), (@option_group_id_pt, {foreach from=$locales item=locale}'Fax',{/foreach} 3, 'Fax', 3), (@option_group_id_pt, {foreach from=$locales item=locale}'Pager',{/foreach} 4, 'Pager', 4), (@option_group_id_pt, {foreach from=$locales item=locale}'Voicemail',{/foreach} 5, 'Voicemail', 5); {else} INSERT INTO civicrm_option_value (option_group_id, label, value, name, weight) VALUES (@option_group_id_pt, 'Phone' , 1, 'Phone', 1), (@option_group_id_pt, 'Mobile', 2, 'Mobile', 2), (@option_group_id_pt, 'Fax' , 3, 'Fax', 3), (@option_group_id_pt, 'Pager' , 4, 'Pager', 4), (@option_group_id_pt, 'Voicemail', 5, 'Voicemail', 5); {/if} ALTER TABLE `civicrm_phone` ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type; ALTER TABLE `civicrm_mapping_field` ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type; ALTER TABLE `civicrm_uf_field` ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type; SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Phone'; UPDATE civicrm_phone SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type = 'Phone'; UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Phone'; UPDATE civicrm_uf_field SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type = 'Phone'; SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Mobile'; UPDATE civicrm_phone SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type = 'Mobile'; UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Mobile'; UPDATE civicrm_uf_field SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type = 'Mobile'; SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Fax'; UPDATE civicrm_phone SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type = 'Fax'; UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Fax'; UPDATE civicrm_uf_field SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type = 'Fax'; SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Pager'; UPDATE civicrm_phone SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type = 'Pager'; UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Pager'; UPDATE civicrm_uf_field SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type = 'Pager'; ALTER TABLE `civicrm_phone` DROP `phone_type`; ALTER TABLE `civicrm_mapping_field` DROP `phone_type`; ALTER TABLE `civicrm_uf_field` DROP `phone_type`; -- custom Group table ALTER TABLE civicrm_custom_group ADD min_multiple int unsigned DEFAULT 0 COMMENT 'minimum number of multiple records (typically 0?)', ADD max_multiple int unsigned DEFAULT 0 COMMENT 'maximum number of multiple records, if 0 - no max'; ALTER TABLE civicrm_custom_field ADD text_length int unsigned COMMENT 'field length if alphanumeric' AFTER options_per_line; -- need to add update statement for site preference options to enable -- preferences for contact_type / groups / tags, CRM-2794 SELECT @option_group_id_aso := max(id) from civicrm_option_group where name = 'advanced_search_options'; {if $multilingual} INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight) VALUES (@option_group_id_aso, {foreach from=$locales item=locale}'Contact Type',{/foreach} 16, 'contactType', 16), (@option_group_id_aso, {foreach from=$locales item=locale}'Groups',{/foreach} 17, 'groups', 17), (@option_group_id_aso, {foreach from=$locales item=locale}'Tags',{/foreach} 18, 'tags', 18); {else} INSERT INTO civicrm_option_value (option_group_id, label, value, name, weight) VALUES (@option_group_id_aso, 'Contact Type', 16, 'contactType', 16), (@option_group_id_aso, 'Groups', 17, 'groups', 17), (@option_group_id_aso, 'Tags', 18, 'tags', 18); {/if} UPDATE civicrm_preferences SET advanced_search_options = CONCAT(advanced_search_options, '161718'); ALTER TABLE civicrm_preferences ADD mailing_backend text COMMENT 'Smtp Backend configuration.'; -- /** -- * add new mailing fields -- * CRM-3599 (created_id, scheduled_id) -- * CRM-3598 (search_id, search_args) -- **/ ALTER TABLE civicrm_mailing ADD override_verp tinyint DEFAULT 0 AFTER msg_template_id, ADD created_id int unsigned NULL DEFAULT NULL AFTER override_verp, ADD scheduled_id int unsigned NULL DEFAULT NULL AFTER created_id, ADD is_archived tinyint DEFAULT 0 COMMENT 'Is this mailing archived?', ADD CONSTRAINT FK_civicrm_mailing_created_id FOREIGN KEY (created_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, ADD CONSTRAINT FK_civicrm_mailing_scheduled_id FOREIGN KEY (scheduled_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE; ALTER TABLE civicrm_mailing_group ADD search_id int AFTER entity_id, ADD search_args text AFTER search_id, MODIFY `group_type` enum('Include','Exclude','Base') NULL DEFAULT NULL; -- CRM-3609 (used IGNORE as 2.1 post beta5 should have this already) INSERT IGNORE INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (5217, 1020, "BRU", "Brussels"); -- ======== CiviCase Related Upgrade ========== -- Insert the CiviCase Component INSERT INTO `civicrm_component` ( `name`, `namespace`) VALUES ( 'CiviCase','CRM_Case' ); SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase'; -- CRM-3667 case mapping SELECT @option_group_id_mt := max(id) from civicrm_option_group where name = 'mapping_type'; {if $multilingual} INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight, is_reserved) VALUES (@option_group_id_mt, {foreach from=$locales item=locale}'Export Case',{/foreach} 12, 'Export Case', 12, 1); {else} INSERT INTO civicrm_option_value (option_group_id, label, value, name, weight, is_reserved) VALUES (@option_group_id_mt, 'Export Case', 12, 'Export Case', 12, 1); {/if} -- * Case Status - Set names for Open and Closed and add Urgent status SELECT @csgId := max(id) FROM civicrm_option_group WHERE name = 'case_status'; {if $multilingual} {foreach from=$locales item=locale} UPDATE civicrm_option_value SET name = 'Open' WHERE option_group_id = @csgId AND label_{$locale} = 'Ongoing'; UPDATE civicrm_option_value SET name = 'Closed' WHERE option_group_id = @csgId AND label_{$locale} = 'Resolved'; {/foreach} INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight, is_reserved) VALUES (@csgId, {foreach from=$locales item=locale}'Urgent',{/foreach} 3, 'Urgent', 3, 1); {else} UPDATE civicrm_option_value SET name = 'Open' WHERE option_group_id = @csgId AND label = 'Ongoing'; UPDATE civicrm_option_value SET name = 'Closed' WHERE option_group_id = @csgId AND label = 'Resolved'; INSERT INTO civicrm_option_value (option_group_id, label, value, name, weight, is_reserved) VALUES (@csgId, 'Urgent', 3, 'Urgent', 3, 1); {/if} -- Relationship Types for cases INSERT INTO civicrm_relationship_type( name_a_b, name_b_a, description, contact_type_a, contact_type_b, is_reserved ) VALUES( 'Case Coordinator is', 'Case Coordinator', 'Case Coordinator', 'Individual', 'Individual', 0 ); INSERT INTO civicrm_relationship_type( name_a_b, name_b_a, description, contact_type_a, contact_type_b, is_reserved ) VALUES( 'Supervised by', 'Supervisor', 'Immediate workplace supervisor', 'Individual', 'Individual', 0 ); -- Activity Types for cases SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type'; SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_activity_type; {if $multilingual} INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight, component_id) VALUES (@option_group_id_activity_type, {foreach from=$locales item=locale}'Open Case',{/foreach} (SELECT @max_val := @max_val+1), 'Open Case', (SELECT @max_val := @max_val+1), @caseCompId), (@option_group_id_activity_type, {foreach from=$locales item=locale}'Follow up',{/foreach} (SELECT @max_val := @max_val+1), 'Follow up', (SELECT @max_val := @max_val+1), @caseCompId), (@option_group_id_activity_type, {foreach from=$locales item=locale}'Change Case Type',{/foreach} (SELECT @max_val := @max_val+1), 'Change Case Type', (SELECT @max_val := @max_val+1), @caseCompId), (@option_group_id_activity_type, {foreach from=$locales item=locale}'Change Case Status',{/foreach} (SELECT @max_val := @max_val+1), 'Change Case Status', (SELECT @max_val := @max_val+1), @caseCompId), (@option_group_id_activity_type, {foreach from=$locales item=locale}'Close Case',{/foreach} (SELECT @max_val := @max_val+1), 'Close Case', (SELECT @max_val := @max_val+1), @caseCompId); {else} INSERT INTO civicrm_option_value (option_group_id, label, value, name, weight, component_id) VALUES (@option_group_id_activity_type, 'Open Case', (SELECT @max_val := @max_val+1), 'Open Case', (SELECT @max_val := @max_val+1), @caseCompId), (@option_group_id_activity_type, 'Follow up', (SELECT @max_val := @max_val+1), 'Follow up', (SELECT @max_val := @max_val+1), @caseCompId), (@option_group_id_activity_type, 'Change Case Type', (SELECT @max_val := @max_val+1), 'Change Case Type', (SELECT @max_val := @max_val+1), @caseCompId), (@option_group_id_activity_type, 'Change Case Status', (SELECT @max_val := @max_val+1), 'Change Case Status', (SELECT @max_val := @max_val+1), @caseCompId), (@option_group_id_activity_type, 'Close Case', (SELECT @max_val := @max_val+1), 'Close Case', (SELECT @max_val := @max_val+1), @caseCompId); {/if} -- Encounter Medium Option Values for Case Activities {if $multilingual} INSERT INTO civicrm_option_group (name, {foreach from=$locales item=locale}label_{$locale}, description_{$locale},{/foreach} is_reserved, is_active) VALUES ('encounter_medium', {foreach from=$locales item=locale}'Encounter Medium', 'Encounter medium for case activities (e.g. In Person, By Phone, etc.)',{/foreach} 1, 1 ); SELECT @option_group_id_medium := max(id) FROM civicrm_option_group WHERE name = 'encounter_medium'; INSERT INTO civicrm_option_value (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, is_default, weight, is_reserved) VALUES (@option_group_id_medium, {foreach from=$locales item=locale}'In Person',{/foreach} 1, 'in_person', 0, 1, 1), (@option_group_id_medium, {foreach from=$locales item=locale}'Phone',{/foreach} 2, 'phone', 1, 2, 1), (@option_group_id_medium, {foreach from=$locales item=locale}'Email',{/foreach} 3, 'email', 0, 3, 1), (@option_group_id_medium, {foreach from=$locales item=locale}'Fax',{/foreach} 4, 'fax', 0, 4, 1), (@option_group_id_medium, {foreach from=$locales item=locale}'Letter Mail',{/foreach} 5, 'letter_mail', 0, 5, 1); {else} INSERT INTO civicrm_option_group (name, label, description, is_reserved, is_active ) VALUES ('encounter_medium', 'Encounter Medium', 'Encounter medium for case activities (e.g. In Person, By Phone, etc.)', 1, 1); SELECT @option_group_id_medium := max(id) FROM civicrm_option_group WHERE name = 'encounter_medium'; INSERT INTO civicrm_option_value (option_group_id, label, value, name, is_default, weight, is_reserved) VALUES (@option_group_id_medium, 'In Person', 1, 'in_person', 0, 1, 1), (@option_group_id_medium, 'Phone', 2, 'phone', 1, 2, 1), (@option_group_id_medium, 'Email', 3, 'email', 0, 3, 1), (@option_group_id_medium, 'Fax', 4, 'fax', 0, 4, 1), (@option_group_id_medium, 'Letter Mail', 5, 'letter_mail', 0, 5, 1); {/if} -- CRM-3573 -- added column case_id in civicrm_relationship table. -- added columns medium, is_auto, relationship_id fileds in civicrm_activity. -- added value 'Case' in civicrm_custom_group. ALTER TABLE `civicrm_relationship` ADD `case_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_case' AFTER is_permission_b_a, ADD CONSTRAINT FK_civicrm_case_id FOREIGN KEY (case_id) REFERENCES civicrm_case(id) ON DELETE CASCADE; ALTER TABLE `civicrm_case` ADD `is_deleted` tinyint DEFAULT 0; ALTER TABLE `civicrm_activity` ADD `medium_id` int(10) unsigned default NULL COMMENT 'Activity Medium, Implicit FK to civicrm_option_value where option_group = activity_medium.', ADD `is_auto` tinyint(4) default '0' COMMENT 'if true - activity is auto populated while case review', ADD `relationship_id` int(10) unsigned default NULL COMMENT 'FK to Relationship ID', ADD `is_current_revision` tinyint DEFAULT 1 , ADD `original_id` int unsigned COMMENT 'Activity ID of the first activity record in versioning chain.', ADD `is_deleted` tinyint DEFAULT 0, ADD CONSTRAINT FK_civicrm_activity_original_id FOREIGN KEY (original_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE, ADD CONSTRAINT FK_civicrm_relationship_id FOREIGN KEY (relationship_id) REFERENCES civicrm_relationship(id) ON DELETE SET NULL; ALTER TABLE `civicrm_custom_group` MODIFY `extends` enum('Contact','Individual','Household','Organization','Location','Address','Contribution','Activity','Relationship','Group','Membership','Participant','Event','Grant','Pledge','Case') collate utf8_unicode_ci default 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).'; -- schema change CRM-3337 ALTER TABLE `civicrm_custom_group` CHANGE `extends_entity_column_name` `extends_entity_column_id` INT( 10 ) UNSIGNED NULL DEFAULT NULL COMMENT 'reference to option value'; -- added new column 'skipBreadcrumb' to civicrm_menu. CRM-2699. ALTER TABLE `civicrm_menu` ADD `skipBreadcrumb` tinyint(4) COMMENT 'skip this url being exposed to breadcrumb'; -- CRM-3709 CREATE INDEX index_option_group_id_name ON civicrm_option_value( `option_group_id` , `name` ); -- fix constraint ALTER TABLE `civicrm_group_nesting` DROP FOREIGN KEY `FK_civicrm_group_nesting_child_group_id`; ALTER TABLE `civicrm_group_nesting` ADD CONSTRAINT `FK_civicrm_group_nesting_child_group_id` FOREIGN KEY (`child_group_id`) REFERENCES `civicrm_group` (`id`) ON DELETE CASCADE; ALTER TABLE `civicrm_group` ADD `is_hidden` tinyint DEFAULT 0 COMMENT 'Is this group hidden?'; -- New field for CRM-3494 (billing name and address for contributions) ALTER TABLE `civicrm_contribution` ADD `address_id` int(10) unsigned default NULL COMMENT 'Conditional foreign key to civicrm_address.id. We insert an address record for each contribution when we have associated billing name and address data.', ADD CONSTRAINT FK_civicrm_contribution_address_id FOREIGN KEY (address_id) REFERENCES civicrm_address(id) ON DELETE SET NULL; -- Removing solicitor_id as per CRM-3917 INSERT INTO civicrm_contribution_soft (contribution_id, contact_id, amount) SELECT id, solicitor_id, total_amount FROM civicrm_contribution WHERE solicitor_id IS NOT NULL; ALTER TABLE `civicrm_contribution` -- Added check_number as per CRM-3923 ADD `check_number` varchar(255) collate utf8_unicode_ci default NULL, DROP FOREIGN KEY `FK_civicrm_contribution_solicitor_id`; DROP INDEX FK_civicrm_contribution_solicitor_id ON civicrm_contribution; ALTER TABLE civicrm_contribution DROP solicitor_id; -- Make sure is_billing flag is true for all address records where location type is Billing -- Using hard-coded location_type_id since we don't have a non-translatable name for location types UPDATE `civicrm_address` SET `is_billing` = 1 WHERE `location_type_id` = 5; -- civicrm_note constraint fix ALTER TABLE civicrm_note DROP FOREIGN KEY `FK_civicrm_note_contact_id`; ALTER TABLE `civicrm_note` ADD CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL; -- CRM-3860 ALTER TABLE civicrm_contact ADD api_key varchar(32); CREATE INDEX index_api_key ON civicrm_contact(api_key); -- CRM-3863 SELECT @piOpt := id from civicrm_option_group where name = 'payment_instrument'; UPDATE civicrm_option_value SET is_reserved = 1 WHERE option_group_id = @piOpt AND name = 'Credit Card'; -- CRM-3851: migrate civicrm_domain.email_domain and .email_return_path to civicrm_mail_settings SELECT email_domain, email_return_path FROM civicrm_domain LIMIT 1 INTO @domain, @return_path; CREATE TABLE `civicrm_mail_settings` ( `id` int(10) unsigned NOT NULL auto_increment COMMENT 'primary key', `name` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'name of this group of settings', `is_default` tinyint(4) default NULL COMMENT 'whether this is the default set of settings for this domain', `domain` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'email address domain (the part after @)', `localpart` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'optional local part (like civimail+ for addresses like civimail+s.1.2@example.com)', `return_path` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'contents of the Return-Path header', `protocol` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'name of the protocol to use for polling (like IMAP, POP3 or Maildir)', `server` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'server to use when polling', `port` int(10) unsigned default NULL COMMENT 'port to use when polling', `username` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'username to use when polling', `password` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'password to use when polling', `is_ssl` tinyint(4) default NULL COMMENT 'whether to use SSL or not', `source` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'folder to poll from when using IMAP, path to poll from when using Maildir, etc.', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO civicrm_mail_settings (name, is_default, domain, return_path) VALUES ('default', true, @domain, @return_path); ALTER TABLE civicrm_domain DROP email_domain; ALTER TABLE civicrm_domain DROP email_return_path; -- CRM-4258, needs to be done before we add a unique index delete t1 from civicrm_entity_tag t1 inner join civicrm_entity_tag t2 where t1.tag_id = t2.tag_id AND t1.contact_id = t2.contact_id AND t1.id > t2.id; -- CRM-3696 ALTER TABLE civicrm_entity_tag ADD UNIQUE UI_contact_id_tag_id ( contact_id, tag_id ); SELECT @option_group_id_ps := id from civicrm_option_group where name = 'participant_status'; UPDATE civicrm_option_value SET visibility_id = CASE name WHEN 'Registered' THEN 1 ELSE 2 END WHERE option_group_id = @option_group_id_ps; -- CRM-3487 ALTER TABLE civicrm_uf_group DROP collapse_display; -- FIXED FOR CRM-3772 -- upgrading civicrm_country table for region id by taking reference of civicrm_worldregion -- Europe and Central Asia SELECT @region_id := max(id) from civicrm_worldregion where name = "Europe and Central Asia"; UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "AL","AD","AQ","AT","AZ","BY","BE","BA","BV","BG","HR","CY","CZ","DK","EE","FO","FI","FR","TF","DE","GI","GR","VA","HU","IS","IE","IT","KZ","KG","LV","LI","LT","LU","MK","MT","MD","MC","NL","NO","PL","PT","RO","RU","SM","SK","SI","ES","SJ","SE","CH","TR","TM","UA","GB","UZ","CS","AX","RS","ME" ) AND `civicrm_country`.`region_id` IS null; -- America South, Central, North and Carribean SELECT @region_id := max(id) from civicrm_worldregion where name = "America South, Central, North and Carribean"; UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "AS","AI","AG","AR","AW","BZ","BM","BO","BR","VG","CA","CL","CO","CR","CU","DM","DO","EC","SV","FK","GF","GL","GD","GP","GT","GY","HT","HN","JM","MQ","MX","MS","AN","NI","MP","PY","PE","PR","KN","LC","PM","VC","GS","SR","BS","TT","TC","UM","US","UY","VE","VI" ) AND `civicrm_country`.`region_id` IS null; -- Middle East and North Africa SELECT @region_id := max(id) from civicrm_worldregion where name = "Middle East and North Africa"; UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "DZ","BH","EG","IR","IQ","IL","JO","KW","LB","LY","MA","OM","PS","QA","SA","SY","TN","AE","EH","YE" ) AND `civicrm_country`.`region_id` IS null; -- Asia-Pacific SELECT @region_id := max(id) from civicrm_worldregion where name = "Asia-Pacific"; UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "AF","AM","AU","BD","BB","BT","IO","BN","MM","KH","CN","CX","CC","CK","TL","FJ","PF","GE","GU","HM","HK","IN","ID","JP","KI","KP","KR","LA","MO","MY","MV","MH","FM","MN","NR","NP","NC","NZ","NU","NF","PK","PW","PG","PH","PN","WS","SG","SB","LK","TW","TJ","TH","TK","TO","TV","VU","VN","WF" ) AND `civicrm_country`.`region_id` IS null; -- Africa West, East, Central and Southern SELECT @region_id := max(id) from civicrm_worldregion where name = "Africa West, East, Central and Southern"; UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "AO","BJ","BW","BF","BI","CM","CV","KY","CF","TD","KM","CD","CG","CI","DJ","GQ","ER","ET","GA","GH","GN","GW","KE","LS","LR","MG","MW","ML","MR","MU","YT","MZ","NA","NE","NG","PA","RW","RE","SH","SN","SC","SL","SO","ZA","SD","SZ","ST","TZ","GM","TG","UG","ZM","ZW" ) AND `civicrm_country`.`region_id` IS null; -- unassigned SELECT @region_id := max(id) from civicrm_worldregion where name = "unassigned"; UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "JE","GG","IM" ) AND `civicrm_country`.`region_id` IS null; -- update minute increment to 1 in activitydatetime in preferences_date UPDATE `civicrm_preferences_date` SET `minute_increment` = 1 WHERE `name` = 'activityDatetime'; -- CRM-3730 ALTER TABLE civicrm_price_field ALTER active_on SET DEFAULT NULL; ALTER TABLE civicrm_price_field ALTER expire_on SET DEFAULT NULL; -- ****************************************************** -- END OF THE UPGRADE