Commit | Line | Data |
---|---|---|
296342b1 | 1 | {* file to handle db changes in 4.5.alpha1 during upgrade *} |
1421174e | 2 | {include file='../CRM/Upgrade/4.5.alpha1.msg_template/civicrm_msg_template.tpl'} |
3 | ||
e171748b OB |
4 | ALTER TABLE `civicrm_contact` |
5 | ADD COLUMN `formal_title` varchar(64) COMMENT 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)' AFTER `suffix_id`; | |
aa62b355 OB |
6 | |
7 | ALTER TABLE `civicrm_contact` | |
8 | ADD COLUMN `communication_style_id` int(10) unsigned COMMENT 'Communication style (e.g. formal vs. familiar) to use with this contact. FK to communication styles in civicrm_option_value.' AFTER `formal_title`, | |
9 | ADD INDEX `index_communication_style_id` (`communication_style_id`); | |
10 | ||
11 | INSERT INTO | |
12 | `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`) | |
13 | VALUES | |
14 | ('communication_style', {localize}'{ts escape="sql"}Communication Style{/ts}'{/localize}, 1, 1); | |
15 | ||
16 | SELECT @option_group_id_communication_style := max(id) from civicrm_option_group where name = 'communication_style'; | |
17 | ||
18 | INSERT INTO | |
6c559730 | 19 | `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) |
aa62b355 | 20 | VALUES |
6c559730 ML |
21 | (@option_group_id_communication_style, {localize}'{ts escape="sql"}Formal{/ts}'{/localize}, 1, 'formal' , NULL, 0, 1, 1, 0, 0, 1, NULL, NULL), |
22 | (@option_group_id_communication_style, {localize}'{ts escape="sql"}Familiar{/ts}'{/localize}, 2, 'familiar', NULL, 0, 0, 2, 0, 0, 1, NULL, NULL); | |
d4311a7a OB |
23 | |
24 | -- Insert menu item at Administer > Communications, above the various Greeting Formats | |
25 | ||
26 | SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Communications' AND `domain_id` = {$domainID}; | |
27 | SELECT @add_weight := MIN(`weight`) FROM `civicrm_navigation` WHERE `name` IN('Email Greeting Formats', 'Postal Greeting Formats', 'Addressee Formats') AND `parent_id` = @parent_id; | |
28 | ||
29 | UPDATE `civicrm_navigation` | |
30 | SET `weight` = `weight`+1 | |
31 | WHERE `parent_id` = @parent_id | |
32 | AND `weight` >= @add_weight; | |
33 | ||
34 | INSERT INTO `civicrm_navigation` | |
35 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
36 | VALUES | |
37 | ( {$domainID}, 'civicrm/admin/options/communication_style&group=communication_style&reset=1', '{ts escape="sql" skip="true"}Communication Style Options{/ts}', 'Communication Style Options', 'administer CiviCRM', '', @parent_id, '1', NULL, @add_weight ); | |
b97b088e | 38 | |
39 | -- CRM-9988 Change world region of Panama country to America South, Central, North and Caribbean | |
8b49cb50 OB |
40 | UPDATE `civicrm_country` SET `region_id` = 2 WHERE `id` = 1166; |
41 | ||
42 | SELECT @option_group_id_contact_edit_options := max(id) from civicrm_option_group where name = 'contact_edit_options'; | |
43 | ||
44 | INSERT INTO | |
6c559730 | 45 | `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) |
8b49cb50 | 46 | VALUES |
6c559730 ML |
47 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Prefix{/ts}'{/localize} , 12, 'Prefix' , NULL, 2, NULL, 12, 0, 0, 1, NULL, NULL), |
48 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Formal Title{/ts}'{/localize}, 13, 'Formal Title', NULL, 2, NULL, 13, 0, 0, 1, NULL, NULL), | |
49 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}First Name{/ts}'{/localize} , 14, 'First Name' , NULL, 2, NULL, 14, 0, 0, 1, NULL, NULL), | |
50 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Middle Name{/ts}'{/localize} , 15, 'Middle Name' , NULL, 2, NULL, 15, 0, 0, 1, NULL, NULL), | |
51 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Last Name{/ts}'{/localize} , 16, 'Last Name' , NULL, 2, NULL, 16, 0, 0, 1, NULL, NULL), | |
52 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Suffix{/ts}'{/localize} , 17, 'Suffix' , NULL, 2, NULL, 17, 0, 0, 1, NULL, NULL); | |
66055e1b | 53 | |
d0dfb649 PJ |
54 | -- CRM-13857 |
55 | ALTER TABLE civicrm_group | |
56 | ADD COLUMN `modified_id` INT(10) unsigned DEFAULT NULL COMMENT 'FK to contact table, modifier of the group.', | |
50d42431 TO |
57 | ADD CONSTRAINT `FK_civicrm_group_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL; |
58 | ||
59 | -- CRM-13913 | |
60 | ALTER TABLE civicrm_word_replacement | |
61 | ALTER COLUMN `is_active` SET DEFAULT 1; | |
51fa20cb | 62 | |
63 | --CRM-13833 Implement Soft Credit Type for Contribution | |
64 | INSERT INTO civicrm_option_group | |
65 | (name, {localize field='title'}title{/localize}, is_reserved, is_active) VALUES ('soft_credit_type', {localize}'{ts escape="sql"}Soft Credit Types{/ts}'{/localize}, 1, 1); | |
66 | ||
67 | SELECT @option_group_id_soft_credit_type := max(id) from civicrm_option_group where name = 'soft_credit_type'; | |
68 | ||
5fec2f6c | 69 | INSERT INTO `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `weight`, `is_default`, `is_active`, `is_reserved`) |
51fa20cb | 70 | VALUES |
71 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}In Honor of{/ts}'{/localize}, 1, 'in_honor_of', 1, 0, 1, 1), | |
72 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}In Memory of{/ts}'{/localize}, 2, 'in_memory_of', 2, 0, 1, 1), | |
73 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Solicited{/ts}'{/localize}, 3, 'solicited', 3, 0, 1, 1), | |
74 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Household{/ts}'{/localize}, 4, 'household', 4, 0, 1, 0), | |
75 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Workplace Giving{/ts}'{/localize}, 5, 'workplace', 5, 0, 1, 0), | |
76 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Foundation Affiliate{/ts}'{/localize}, 6, 'foundation_affiliate', 6, 0, 1, 0), | |
77 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}3rd-party Service{/ts}'{/localize}, 7, '3rd-party_service', 7, 0, 1, 0), | |
78 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Donor-advised Fund{/ts}'{/localize}, 8, 'donor-advised_fund', 8, 0, 1, 0), | |
79 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Matched Gift{/ts}'{/localize}, 9, 'matched_gift', 9, 0, 1, 0), | |
12792976 | 80 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Personal Campaign Page{/ts}'{/localize}, 10, 'pcp', 10, 0, 1, 1), |
81 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Gift{/ts}'{/localize}, 11, 'gift', 11, 0, 1, 1); | |
51fa20cb | 82 | |
83 | ALTER TABLE `civicrm_contribution_soft` | |
84 | ADD COLUMN `soft_credit_type_id` int(10) unsigned COMMENT 'Soft Credit Type ID.Implicit FK to civicrm_option_value where option_group = soft_credit_type.'; | |
85 | ||
86 | SELECT @sct_pcp_id := value from civicrm_option_value where name = 'pcp' and option_group_id = @option_group_id_soft_credit_type; | |
87 | ||
88 | UPDATE `civicrm_contribution_soft` | |
89 | SET soft_credit_type_id = @sct_pcp_id | |
90 | WHERE pcp_id IS NOT NULL; | |
61a46288 | 91 | |
92 | --CRM-13734 make basic Case Activity Types reserved | |
93 | SELECT @option_group_id_activity_type := id from civicrm_option_group where name = 'activity_type'; | |
94 | SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase'; | |
95 | ||
96 | UPDATE `civicrm_option_value` | |
97 | SET is_reserved = 1 | |
a3e3eea1 | 98 | WHERE is_reserved = 0 AND option_group_id = @option_group_id_activity_type AND component_id = @caseCompId; |
99 | ||
100 | -- CRM-13912 | |
101 | ALTER TABLE civicrm_action_schedule | |
102 | ADD COLUMN `mode` varchar(128) COLLATE utf8_unicode_ci DEFAULT 'Email' COMMENT 'Send the message as email or sms or both.'; | |
103 | ||
104 | INSERT INTO | |
105 | civicrm_option_group (name, {localize field='title'}title{/localize}, is_reserved, is_active) | |
106 | VALUES | |
107 | ('msg_mode', {localize}'{ts escape="sql"}Message Mode{/ts}'{/localize}, 1, 1); | |
108 | ||
109 | SELECT @option_group_id_msg_mode := max(id) from civicrm_option_group where name = 'msg_mode'; | |
110 | ||
111 | INSERT INTO | |
43d1a3df | 112 | civicrm_option_value (option_group_id, {localize field='label'}`label`{/localize}, value, name, is_default, weight, is_reserved, is_active) |
a3e3eea1 | 113 | VALUES |
114 | (@option_group_id_msg_mode, {localize}'{ts escape="sql"}Email{/ts}'{/localize}, 'Email', 'Email', 1, 1, 1, 1), | |
115 | (@option_group_id_msg_mode, {localize}'{ts escape="sql"}SMS{/ts}'{/localize},'SMS', 'SMS', 0, 2, 1, 1), | |
116 | (@option_group_id_msg_mode, {localize}'{ts escape="sql"}User Preference{/ts}'{/localize}, 'User_Preference', 'User Preference', 0, 3, 1, 1); | |
0ac434a3 | 117 | |
118 | ALTER TABLE civicrm_action_schedule ADD sms_provider_id int(10) unsigned NULL COMMENT 'FK to civicrm_sms_provider id '; | |
119 | ALTER TABLE civicrm_action_schedule ADD CONSTRAINT FK_civicrm_action_schedule_sms_provider_id FOREIGN KEY (`sms_provider_id`) REFERENCES `civicrm_sms_provider` (`id`) ON DELETE SET NULL; | |
133e2c99 | 120 | |
121 | --CRM-13981 migrate 'In Honor of' to Soft Credits | |
122 | INSERT INTO `civicrm_uf_group` | |
123 | (`name`, `group_type`, {localize field='title'}`title`{/localize}, `is_cms_user`, `is_reserved`) | |
124 | VALUES | |
125 | ('honoree_individual', 'Individual, Contact', {localize}'{ts escape="sql"}Honoree Individual{/ts}'{/localize}, 0, 1); | |
126 | ||
1421174e | 127 | SELECT @uf_group_id_honoree_individual := id from civicrm_uf_group where name = 'honoree_individual'; |
133e2c99 | 128 | |
129 | INSERT INTO `civicrm_uf_field` | |
8381af80 | 130 | (`uf_group_id`, `field_name`, `is_required`, `is_reserved`, `weight`, `visibility`, `in_selector`, `is_searchable`, `location_type_id`, {localize field='label'}`label`{/localize}, field_type) |
133e2c99 | 131 | VALUES |
6c559730 ML |
132 | (@uf_group_id_honoree_individual, 'prefix_id', 0, 1, 1, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}Individual Prefix{/ts}'{/localize}, 'Individual'), |
133 | (@uf_group_id_honoree_individual, 'first_name', 0, 1, 2, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}First Name{/ts}'{/localize}, 'Individual'), | |
134 | (@uf_group_id_honoree_individual, 'last_name', 0, 1, 3, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}Last Name{/ts}'{/localize}, 'Individual'), | |
135 | (@uf_group_id_honoree_individual, 'email', 0, 1, 4, 'User and User Admin Only', 0, 1, 1, {localize}'{ts escape="sql"}Email Address{/ts}'{/localize}, 'Individual'); | |
133e2c99 | 136 | |
137 | ALTER TABLE `civicrm_uf_join` | |
12792976 | 138 | ADD COLUMN `module_data` longtext COMMENT 'Json serialized array of data used by the ufjoin.module'; |
133e2c99 | 139 | |
140 | {if $multilingual} | |
141 | {foreach from=$locales item=loc} | |
142 | ALTER TABLE civicrm_contribution_page DROP honor_block_title_{$loc}; | |
143 | ALTER TABLE civicrm_contribution_page DROP honor_block_text_{$loc}; | |
144 | {/foreach} | |
145 | {else} | |
146 | ALTER TABLE civicrm_contribution_page DROP honor_block_title; | |
147 | ALTER TABLE civicrm_contribution_page DROP honor_block_text; | |
148 | {/if} | |
8af73472 | 149 | ALTER TABLE civicrm_contribution_page DROP honor_block_is_active; |
133e2c99 | 150 | |
1421174e | 151 | ALTER TABLE civicrm_contribution DROP FOREIGN KEY `FK_civicrm_contribution_honor_contact_id`; |
133e2c99 | 152 | ALTER TABLE civicrm_contribution DROP honor_contact_id; |
153 | ALTER TABLE civicrm_contribution DROP honor_type_id; | |
154 | ||
1421174e | 155 | ALTER TABLE civicrm_pledge DROP FOREIGN KEY `FK_civicrm_pledge_honor_contact_id`; |
133e2c99 | 156 | ALTER TABLE civicrm_pledge DROP honor_contact_id; |
157 | ALTER TABLE civicrm_pledge DROP honor_type_id; | |
5ce9cbe9 | 158 | |
0f602e3f | 159 | -- CRM-13964 and CRM-13965 |
59e44db1 PJ |
160 | SELECT @option_group_id_cs := max(id) from civicrm_option_group where name = 'contribution_status'; |
161 | SELECT @option_val_id_cs_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; | |
162 | SELECT @option_val_id_cs_val := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; | |
163 | ||
164 | INSERT INTO | |
165 | `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) | |
166 | VALUES | |
0f602e3f PJ |
167 | (@option_group_id_cs, {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, @option_val_id_cs_val+1, 'Partially paid', NULL, 0, NULL, @option_val_id_cs_wt+1, 0, 1, 1, NULL, NULL), |
168 | (@option_group_id_cs, {localize}'{ts escape="sql"}Pending refund{/ts}'{/localize}, @option_val_id_cs_val+2, 'Pending refund', NULL, 0, NULL, @option_val_id_cs_wt+2, 0, 1, 1, NULL, NULL); | |
169 | ||
59e44db1 PJ |
170 | -- participant status adding |
171 | SELECT @participant_status_wt := max(id) from civicrm_participant_status_type; | |
172 | ||
0f602e3f PJ |
173 | INSERT INTO civicrm_participant_status_type (name, {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id) |
174 | VALUES | |
175 | ('Partially paid', {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+1, 2), | |
176 | ('Pending refund', {localize}'{ts escape="sql"}Pending refund{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+2, 2); | |
177 | ||
178 | -- new activity types required for partial payments | |
179 | SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type'; | |
180 | SELECT @option_group_id_act_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; | |
181 | SELECT @option_group_id_act_val := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; | |
182 | SELECT @contributeCompId := max(id) FROM civicrm_component where name = 'CiviContribute'; | |
183 | ||
184 | INSERT INTO | |
185 | `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, {localize field='description'}`description`{/localize}, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) | |
59e44db1 | 186 | VALUES |
0f602e3f | 187 | (@option_group_id_act, {localize}'{ts escape="sql"}Payment{/ts}'{/localize}, @option_group_id_act_val+1, 'Payment', NULL, 1, NULL, @option_group_id_act_wt+1, {localize}'{ts escape="sql"}Additional payment recorded for event or membership fee.{/ts}'{/localize}, 0, 1, 1, @contributeCompId, NULL), |
2429e40c | 188 | (@option_group_id_act, {localize}'{ts escape="sql"}Refund{/ts}'{/localize}, @option_group_id_act_val+2, 'Refund', NULL, 1, NULL, @option_group_id_act_wt+2, {localize}'{ts escape="sql"}Refund recorded for event or membership fee.{/ts}'{/localize}, 0, 1, 1, @contributeCompId, NULL), |
5ce9cbe9 | 189 | (@option_group_id_act, {localize}'{ts escape="sql"}Change Registration{/ts}'{/localize}, @option_group_id_act_val+3, 'Change Registration', NULL, 1, NULL, @option_group_id_act_wt+3, {localize}'{ts escape="sql"}Changes to an existing event registration.{/ts}'{/localize}, 0, 1, 1, @eventCompId, NULL); |
190 | ||
6c2473d5 CW |
191 | -- CRM-13970 |
192 | UPDATE civicrm_navigation set url = 'civicrm/admin/options/from_email_address&reset=1' WHERE url LIKE 'civicrm/admin/options/from_email%'; | |
6c2473d5 | 193 | UPDATE civicrm_navigation set url = CONCAT(SUBSTRING_INDEX(url, '&', 1), '&reset=1') WHERE url LIKE 'civicrm/admin/options/%'; |
be622aca | 194 | |
195 | -- CRM-14181 | |
196 | ALTER TABLE civicrm_acl CHANGE operation operation VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'What operation does this ACL entry control?'; | |
197 | ALTER TABLE civicrm_campaign_group CHANGE group_type group_type VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Type of Group.'; | |
198 | ALTER TABLE `civicrm_acl_contact_cache` CHANGE `operation` `operation` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'What operation does this user have permission on?'; | |
199 | ALTER TABLE `civicrm_price_field` CHANGE `html_type` `html_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; | |
200 | ALTER TABLE `civicrm_pledge` CHANGE `frequency_unit` `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'month' COMMENT 'Time units for recurrence of pledge payments.'; | |
201 | ALTER TABLE `civicrm_membership_type` CHANGE `duration_unit` `duration_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Unit in which membership period is expressed.'; | |
202 | ALTER TABLE `civicrm_membership_type` CHANGE `period_type` `period_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.'; | |
203 | ALTER TABLE `civicrm_membership_status` CHANGE `start_event` `start_event` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Event when this status starts.'; | |
204 | ALTER TABLE `civicrm_membership_status` CHANGE `start_event_adjust_unit` `start_event_adjust_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Unit used for adjusting from start_event.'; | |
205 | ALTER TABLE `civicrm_membership_status` CHANGE `end_event` `end_event` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Event after which this status ends.'; | |
206 | ALTER TABLE `civicrm_membership_status` CHANGE `end_event_adjust_unit` `end_event_adjust_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Unit used for adjusting from the ending event.'; | |
f3800cf8 | 207 | ALTER TABLE `civicrm_mailing_job` CHANGE `status` `status` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'The state of this job'; |
208 | ALTER TABLE `civicrm_mailing_group` CHANGE `group_type` `group_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Are the members of the group included or excluded?.'; | |
209 | ALTER TABLE `civicrm_mailing` CHANGE `visibility` `visibility` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is the mailing contents visible (online viewing)'; | |
210 | ALTER TABLE `civicrm_mailing_component` CHANGE `component_type` `component_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Type of Component.'; | |
b1a243c1 | 211 | ALTER TABLE `civicrm_mailing_bounce_type` CHANGE `name` `name` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Type of bounce'; |
909ca5f6 | 212 | ALTER TABLE `civicrm_participant_status_type` CHANGE `class` `class` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'the general group of status type this one belongs to'; |
213 | ALTER TABLE `civicrm_dedupe_rule_group` CHANGE `contact_type` `contact_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'The type of contacts this group applies to'; | |
214 | ALTER TABLE `civicrm_dedupe_rule_group` CHANGE `used` `used` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Whether the rule should be used for cases where usage is Unsupervised, Supervised OR General(programatically)'; | |
215 | ALTER TABLE `civicrm_word_replacement` CHANGE `match_type` `match_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'wildcardMatch'; | |
216 | ALTER TABLE `civicrm_uf_field` CHANGE `visibility` `visibility` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.'; | |
217 | ALTER TABLE `civicrm_mapping_field` CHANGE `operator` `operator` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'SQL WHERE operator for search-builder mapping fields (search criteria).'; | |
d9cf711e | 218 | ALTER TABLE `civicrm_job` CHANGE `run_frequency` `run_frequency` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'Daily' COMMENT 'Scheduled job run frequency.'; |
219 | ALTER TABLE `civicrm_extension` CHANGE `type` `type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; | |
220 | ALTER TABLE `civicrm_custom_group` CHANGE `style` `style` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Visual relationship between this form and its parent.'; | |
221 | ALTER TABLE `civicrm_custom_field` CHANGE `data_type` `data_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Controls location of data storage in extended_data table.'; | |
222 | ALTER TABLE `civicrm_custom_field` CHANGE `html_type` `html_type` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'HTML types plus several built-in extended types.'; | |
223 | ALTER TABLE `civicrm_action_schedule` CHANGE `start_action_unit` `start_action_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Time units for reminder.'; | |
224 | ALTER TABLE `civicrm_action_schedule` CHANGE `repetition_frequency_unit` `repetition_frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Time units for repetition of reminder.'; | |
225 | ALTER TABLE `civicrm_action_schedule` CHANGE `end_frequency_unit` `end_frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Time units till repetition of reminder.'; | |
dc73c80d | 226 | ALTER TABLE `civicrm_product` CHANGE `period_type` `period_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month(e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) '; |
227 | ALTER TABLE `civicrm_product` CHANGE `duration_unit` `duration_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'year'; | |
228 | ALTER TABLE `civicrm_product` CHANGE `frequency_unit` `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.'; | |
229 | ALTER TABLE `civicrm_contribution_recur` CHANGE `frequency_unit` `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'month' COMMENT 'Time units for recurrence of payment.'; | |
230 | ALTER TABLE `civicrm_subscription_history` CHANGE `method` `method` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'How the (un)subscription was triggered'; | |
231 | ALTER TABLE `civicrm_subscription_history` CHANGE `status` `status` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'The state of the contact within the group'; | |
232 | ALTER TABLE `civicrm_relationship_type` CHANGE `contact_type_a` `contact_type_a` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'If defined, contact_a in a relationship of this type must be a specific contact_type.'; | |
233 | ALTER TABLE `civicrm_relationship_type` CHANGE `contact_type_b` `contact_type_b` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'If defined, contact_b in a relationship of this type must be a specific contact_type.'; | |
234 | ALTER TABLE `civicrm_group_contact` CHANGE `status` `status` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'status of contact relative to membership in group'; | |
235 | ALTER TABLE `civicrm_group` CHANGE `visibility` `visibility` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.'; | |
236 | ALTER TABLE `civicrm_contact` CHANGE `preferred_mail_format` `preferred_mail_format` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'Both' COMMENT 'What is the preferred mode of sending an email.'; | |
909ca5f6 | 237 | |
50d45c74 | 238 | -- CRM-14183 |
239 | INSERT INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1157, "PL", "Plateau"); | |
240 | UPDATE civicrm_state_province SET name = "Abuja Federal Capital Territory" WHERE name = "Abuja Capital Territory"; | |
5a205b89 PJ |
241 | |
242 | -- CRM-13992 | |
243 | ALTER TABLE `civicrm_custom_field` | |
244 | ADD COLUMN `in_selector` tinyint(4) DEFAULT '0' COMMENT 'Should the multi-record custom field values be displayed in tab table listing'; | |
245 | UPDATE civicrm_custom_field cf | |
246 | LEFT JOIN civicrm_custom_group cg | |
247 | ON cf.custom_group_id = cg.id | |
248 | SET cf.in_selector = 1 | |
b05a0fb6 PJ |
249 | WHERE cg.is_multiple = 1 AND cf.html_type != 'TextArea'; |
250 | ALTER TABLE `civicrm_custom_group` | |
12792976 | 251 | CHANGE COLUMN `style` `style` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Visual relationship between this form and its parent.'; |
ab4c268a CW |
252 | |
253 | -- Add "developer" help menu | |
254 | SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Help' AND `domain_id` = {$domainID}; | |
255 | ||
256 | INSERT INTO civicrm_navigation | |
257 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
258 | VALUES | |
259 | ( {$domainID}, NULL, '{ts escape="sql" skip="true"}Developer{/ts}', 'Developer', 'administer CiviCRM', '', @parent_id, '1', NULL, 5 ); | |
260 | ||
261 | SET @devellastID:=LAST_INSERT_ID(); | |
262 | INSERT INTO civicrm_navigation | |
263 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
264 | VALUES | |
265 | ( {$domainID}, 'civicrm/api/explorer', '{ts escape="sql" skip="true"}API Explorer{/ts}','API Explorer', 'administer CiviCRM', '', @devellastID, '1', NULL, 1 ), | |
266 | ( {$domainID}, 'http://wiki.civicrm.org/confluence/display/CRMDOC/Develop', '{ts escape="sql" skip="true"}Developer Docs{/ts}', 'Developer Docs', 'administer CiviCRM', '', @devellastID, '1', NULL, 3 ); | |
267 | ||
41876fd8 PJ |
268 | -- CRM-14435 |
269 | ALTER TABLE `civicrm_mail_settings` | |
52068ff5 | 270 | ADD CONSTRAINT `FK_civicrm_mail_settings_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE; |
c57f36a1 PJ |
271 | |
272 | -- CRM-14436 | |
273 | ALTER TABLE `civicrm_mailing` | |
274 | ADD COLUMN `hash` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Key for validating requests related to this mailing.', | |
4ab2e0d4 | 275 | ADD INDEX `index_hash` (`hash`); |
276 | ||
99f13d8d | 277 | -- CRM-14300 |
278 | UPDATE `civicrm_event` SET is_template = 0 WHERE is_template IS NULL; | |
279 | ALTER TABLE `civicrm_event` | |
280 | CHANGE is_template is_template tinyint(4) DEFAULT '0' COMMENT 'whether the event has template'; | |
7ad9c1df | 281 | |
282 | -- CRM-14493 | |
cede6590 DG |
283 | INSERT INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1085, "61", "Pieria"); |
284 | ||
285 | -- CRM-14445 | |
286 | ALTER TABLE `civicrm_option_group` | |
287 | ADD COLUMN `is_locked` int(1) DEFAULT 0 COMMENT 'A lock to remove the ability to add new options via the UI'; | |
288 | ||
5fec2f6c EM |
289 | UPDATE `civicrm_option_group` SET is_locked = 1 WHERE name IN ('contribution_status','activity_contacts','advanced_search_options','auto_renew_options','contact_autocomplete_options','batch_status','batch_type','batch_mode','contact_edit_options','contact_reference_options','contact_smart_group_display','contact_view_options','financial_item_status','mapping_type','pcp_status','user_dashboard_options','tag_used_for'); |
290 | ||
e2bef985 | 291 | |
5fec2f6c EM |
292 | -- CRM-14522 |
293 | ALTER TABLE `civicrm_msg_template` DROP FOREIGN KEY `FK_civicrm_msg_template_pdf_format_id`; | |
e2bef985 | 294 | |
295 | -- CRM-14449 | |
296 | CREATE TABLE `civicrm_system_log` ( | |
297 | `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: ID.', | |
298 | `message` VARCHAR(128) NOT NULL COMMENT 'Standardized message', | |
299 | `context` LONGTEXT NULL COMMENT 'JSON encoded data', | |
300 | `level` VARCHAR(9) NOT NULL DEFAULT 'info' COMMENT 'error level per PSR3', | |
301 | `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of when event occurred.', | |
302 | `contact_id` INT(11) NULL DEFAULT NULL COMMENT 'Optional Contact ID that created the log. Not an FK as we keep this regardless', | |
303 | hostname VARCHAR(128) NOT NULL COMMENT 'Optional Name of logging host', | |
304 | PRIMARY KEY (`id`), | |
305 | INDEX `message` (`message`), | |
306 | INDEX `contact_id` (`contact_id`), | |
307 | INDEX `level` (`level`) | |
308 | ) | |
309 | COMMENT='Table that contains logs of all system events.' | |
310 | COLLATE='utf8_general_ci'; | |
311 | ||
fc9e7e51 ARW |
312 | -- CRM-14473 civicrm_case_type table creation and migration |
313 | CREATE TABLE IF NOT EXISTS `civicrm_case_type` ( | |
314 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Autoincremented type id', | |
315 | `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Machine name for Case Type', | |
316 | `title` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Natural language name for Case Type', | |
317 | `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the Case Type', | |
318 | `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?', | |
319 | `is_reserved` tinyint(4) DEFAULT NULL COMMENT 'Is this case type a predefined system type?', | |
320 | `weight` int(11) NOT NULL DEFAULT '1' COMMENT 'Ordering of the case types', | |
321 | PRIMARY KEY (`id`), | |
322 | UNIQUE KEY `case_type_name` (`name`) | |
323 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; | |
324 | ||
325 | SELECT @option_group_id_case_type := max(id) from civicrm_option_group where name = 'case_type'; | |
326 | ||
327 | INSERT IGNORE INTO civicrm_case_type | |
328 | (name, title, description, is_active, is_reserved, weight) | |
329 | SELECT | |
330 | name, | |
331 | label, | |
332 | description, | |
333 | is_active, | |
334 | is_reserved, | |
335 | weight | |
336 | FROM civicrm_option_value | |
337 | WHERE | |
338 | option_group_id = @option_group_id_case_type; | |
339 | ||
340 | -- Replace the pseudo-fk to ov.value with a reference to civicrm_case_type.id | |
341 | UPDATE civicrm_case | |
342 | SET case_type_id = ( | |
343 | SELECT civicrm_case_type.id FROM civicrm_case_type | |
344 | JOIN civicrm_option_value | |
345 | ON civicrm_option_value.name = civicrm_case_type.name | |
346 | WHERE | |
347 | civicrm_option_value.option_group_id = @option_group_id_case_type | |
348 | AND civicrm_option_value.value = replace(civicrm_case.case_type_id, 0x01, '') | |
349 | ); | |
350 | ||
351 | ALTER TABLE civicrm_case | |
352 | MODIFY case_type_id int(10) unsigned COLLATE utf8_unicode_ci NOT NULL COMMENT 'FK to civicrm_case_type.name'; | |
353 | ||
354 | DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_case_type; | |
355 | ||
356 | DELETE FROM civicrm_option_group WHERE id = @option_group_id_case_type; | |
5d542dcd ML |
357 | |
358 | -- CRM-14611 | |
359 | {if $multilingual} | |
360 | {foreach from=$locales item=locale} | |
361 | ALTER TABLE civicrm_survey ADD title_{$locale} varchar(255); | |
362 | UPDATE civicrm_survey SET title_{$locale} = title; | |
363 | ||
364 | ALTER TABLE civicrm_survey ADD instructions_{$locale} TEXT; | |
365 | UPDATE civicrm_survey SET instructions_{$locale} = instructions; | |
366 | {/foreach} | |
367 | ||
368 | ALTER TABLE civicrm_survey DROP title; | |
369 | ALTER TABLE civicrm_survey DROP instructions; | |
370 | {/if} | |
1909126f | 371 | |
372 | -- CRM-11182 -- Make event confirmation page optional | |
373 | ALTER TABLE civicrm_event | |
374 | ADD COLUMN is_confirm_enabled tinyint(4) DEFAULT '1'; | |
375 | ||
376 | UPDATE civicrm_event | |
377 | SET is_confirm_enabled = 1 | |
378 | WHERE is_monetary = 1; | |
379 | ||
380 | UPDATE civicrm_event | |
381 | SET is_confirm_enabled = 0 |