projects / civicrm-core.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Merge pull request #4054 from eileenmcnaughton/CRM-15237
[civicrm-core.git] / CRM / Upgrade / Incremental / sql / 4.1.alpha1.mysql.tpl
CommitLineData
6a488035
TO		 1{include file='../CRM/Upgrade/4.1.alpha1.msg_template/civicrm_msg_template.tpl'}
2
10824d34 3-- get domain id
6a488035
TO		 4SELECT @domainID := min(id) FROM civicrm_domain;
5
6-- CRM-8356
7-- Add filter column 'filter' for 'civicrm_custom_field'
8ALTER TABLE `civicrm_custom_field` ADD `filter` VARCHAR(255) NULL COMMENT 'Stores Contact Get API params contact reference custom fields. May be used for other filters in the future.';
9
10-- CRM-8062
11ALTER TABLE `civicrm_subscription_history` CHANGE `status` `status` ENUM( 'Added', 'Removed', 'Pending', 'Deleted' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'The state of the contact within the group';
12
13-- CRM-8510
14ALTER TABLE civicrm_currency
15ADD UNIQUE INDEX UI_name ( name );
16
17-- CRM-8616
18DELETE FROM civicrm_currency WHERE name = 'EEK';
19
20-- CRM-8769
cff67e98 21INSERT IGNORE INTO civicrm_state_province
6a488035
TO		 22 (`name`, `abbreviation`, `country_id`)
23VALUES
24 ('Metropolitan Manila' , 'MNL', '1170');
10824d34 25
6a488035
TO		 26-- CRM-8902
27 UPDATE civicrm_navigation SET permission ='add cases,access all cases and activities', permission_operator = 'OR'
28 WHERE civicrm_navigation.name= 'New Case';
29
30-- CRM-8780
31
32-- add the settings table
33 CREATE TABLE `civicrm_setting` (
34 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
35 `group_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'group name for setting element, useful in caching setting elements',
36 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Unique name for setting',
37 `value` text COLLATE utf8_unicode_ci COMMENT 'data associated with this group / name combo',
38 `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this menu item for',
39 `contact_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Contact ID if the setting is localized to a contact',
40 `is_domain` tinyint(4) DEFAULT NULL COMMENT 'Is this setting a contact specific or site wide setting?',
41 `component_id` int(10) unsigned DEFAULT NULL COMMENT 'Component that this menu item belongs to',
42 `created_date` datetime DEFAULT NULL COMMENT 'When was the setting created',
43 `created_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_contact, who created this setting',
44 PRIMARY KEY (`id`),
45 KEY `index_group_name` (`group_name`,`name`),
46 KEY `FK_civicrm_setting_domain_id` (`domain_id`),
47 KEY `FK_civicrm_setting_contact_id` (`contact_id`),
48 KEY `FK_civicrm_setting_component_id` (`component_id`),
49 KEY `FK_civicrm_setting_created_id` (`created_id`),
50 CONSTRAINT `FK_civicrm_setting_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE,
51 CONSTRAINT `FK_civicrm_setting_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
52 CONSTRAINT `FK_civicrm_setting_component_id` FOREIGN KEY (`component_id`) REFERENCES `civicrm_component` (`id`),
53 CONSTRAINT `FK_civicrm_setting_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL
54) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
55
56-- CRM-8508
57 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
58
59 SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
60 SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_activity_type;
61 SELECT @max_wt := max(weight) from civicrm_option_value where option_group_id=@option_group_id_activity_type;
62
63 INSERT INTO civicrm_option_value
64 (option_group_id, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, value, name, weight, filter, component_id)
65 VALUES
66 (@option_group_id_activity_type, {localize}'Change Custom Data'{/localize},{localize}''{/localize}, (SELECT @max_val := @max_val+1), 'Change Custom Data', (SELECT @max_wt := @max_wt+1), 0, @caseCompId);
67
68-- CRM-8739
69 Update civicrm_menu set title = 'Cleanup Caches and Update Paths' where path = 'civicrm/admin/setting/updateConfigBackend';
10824d34 70
6a488035
TO		 71-- CRM-8855
72 SELECT @option_group_id_udOpt := max(id) from civicrm_option_group where name = 'user_dashboard_options';
73 SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_udOpt;
74 SELECT @max_wt := max(weight) from civicrm_option_value where option_group_id=@option_group_id_udOpt;
75
76 INSERT INTO civicrm_option_value
77 (option_group_id, {localize field='label'}label{/localize}, value, name, weight, filter, is_default, component_id)
78 VALUES
79 (@option_group_id_udOpt, {localize}'Assigned Activities'{/localize}, (SELECT @max_val := @max_val+1), 'Assigned Activities', (SELECT @max_wt := @max_wt+1), 0, NULL, NULL);
80
81-- CRM-8737
82 ALTER TABLE `civicrm_event` ADD `is_share` TINYINT( 4 ) NULL DEFAULT '1' COMMENT 'Can people share the event through social media?';
83 ALTER TABLE `civicrm_contribution_page` ADD `is_share` TINYINT(4) NULL DEFAULT '1' COMMENT 'Can people share the contribution page through social media?';
84
85-- CRM-8357
86ALTER TABLE `civicrm_contact` CHANGE `contact_sub_type` `contact_sub_type` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'May be used to over-ride contact view and edit templates.';
87
88UPDATE civicrm_contact SET contact_sub_type = CONCAT('\ 1', contact_sub_type, '\ 1');
89
90-- CRM-6811
10824d34 91INSERT INTO `civicrm_dashboard`
92 ( `domain_id`, {localize field='label'}`label`{/localize}, `url`, `permission`, `permission_operator`, `column_no`, `is_minimized`, `is_active`, `weight`, `fullscreen_url`, `is_fullscreen`, `is_reserved`)
93 VALUES
6a488035
TO		 94 ( @domainID, {localize}'Case Dashboard Dashlet'{/localize}, 'civicrm/dashlet/casedashboard&reset=1&snippet=4', 'access CiviCase', NULL , 0, 0, 1, 4, 'civicrm/dashlet/casedashboard&reset=1&snippet=4&context=dashletFullscreen', 1, 1);
95
96-- CRM-9059 Admin menu rebuild
97SELECT @domainID := min(id) FROM civicrm_domain;
c55ee104 98SELECT @adminlastID := id FROM civicrm_navigation where name = 'Administer' AND domain_id = @domainID;
99SELECT @customizeOld := id FROM civicrm_navigation where name = 'Customize' AND domain_id = @domainID;
100SELECT @configureOld := id FROM civicrm_navigation where name = 'Configure' AND domain_id = @domainID;
101SELECT @globalOld := id FROM civicrm_navigation where name = 'Global Settings' AND domain_id = @domainID;
102SELECT @manageOld := id FROM civicrm_navigation where name = 'Manage' AND domain_id = @domainID;
103SELECT @optionsOld := id FROM civicrm_navigation where name = 'Option Lists' AND domain_id = @domainID;
104SELECT @customizeOld := id FROM civicrm_navigation where name = 'Customize' AND domain_id = @domainID;
6a488035
TO		 105
106DELETE from civicrm_navigation WHERE parent_id = @globalOld;
107DELETE from civicrm_navigation WHERE parent_id IN (@customizeOld, @configureOld, @manageOld, @optionsOld);
108DELETE from civicrm_navigation WHERE id IN (@customizeOld, @configureOld, @manageOld, @optionsOld);
109UPDATE civicrm_navigation SET weight = 9 WHERE name = 'CiviCampaign' AND parent_id = @adminlastID;
110UPDATE civicrm_navigation SET weight = 10 WHERE name = 'CiviCase' AND parent_id = @adminlastID;
111UPDATE civicrm_navigation SET weight = 11 WHERE name = 'CiviContribute' AND parent_id = @adminlastID;
112UPDATE civicrm_navigation SET weight = 12 WHERE name = 'CiviEvent' AND parent_id = @adminlastID;
113UPDATE civicrm_navigation SET weight = 13 WHERE name = 'CiviGrant' AND parent_id = @adminlastID;
114UPDATE civicrm_navigation SET weight = 14 WHERE name = 'CiviMail' AND parent_id = @adminlastID;
115UPDATE civicrm_navigation SET weight = 15 WHERE name = 'CiviMember' AND parent_id = @adminlastID;
116UPDATE civicrm_navigation SET weight = 16 WHERE name = 'CiviReport' AND parent_id = @adminlastID;
117
118DELETE FROM civicrm_navigation WHERE name = 'Administration Console';
119
120INSERT INTO civicrm_navigation
121 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 122VALUES
6a488035
TO		 123 ( @domainID, 'civicrm/admin&reset=1', '{ts escape="sql" skip="true"}Administration Console{/ts}', 'Administration Console', 'administer CiviCRM', '', @adminlastID, '1', NULL, 1 );
124
125SET @adminConsolelastID:=LAST_INSERT_ID();
126INSERT INTO civicrm_navigation
127 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 128VALUES
6a488035
TO		 129 ( @domainID, 'civicrm/admin/configtask&reset=1', '{ts escape="sql" skip="true"}Configuration Checklist{/ts}', 'Configuration Checklist', 'administer CiviCRM', '', @adminConsolelastID, '1', NULL, 1 );
130
131INSERT INTO civicrm_navigation
132 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 133VALUES
6a488035
TO		 134 ( @domainID, NULL, '{ts escape="sql" skip="true"}Customize Data and Screens{/ts}', 'Customize Data and Screens', 'administer CiviCRM', '', @adminlastID, '1', NULL, 3 );
135
136SET @CustomizelastID:=LAST_INSERT_ID();
137INSERT INTO civicrm_navigation
138 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
139VALUES
10824d34 140 ( @domainID, 'civicrm/admin/custom/group&reset=1', '{ts escape="sql" skip="true"}Custom Fields{/ts}', 'Custom Fields', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 1 ),
141 ( @domainID, 'civicrm/admin/uf/group&reset=1', '{ts escape="sql" skip="true"}Profiles{/ts}', 'Profiles', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 2 ),
142 ( @domainID, 'civicrm/admin/tag&reset=1', '{ts escape="sql" skip="true"}Tags (Categories){/ts}', 'Tags (Categories)', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 3 ),
143 ( @domainID, 'civicrm/admin/options/activity_type&reset=1&group=activity_type', '{ts escape="sql" skip="true"}Activity Types{/ts}', 'Activity Types', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 4 ),
144 ( @domainID, 'civicrm/admin/reltype&reset=1', '{ts escape="sql" skip="true"}Relationship Types{/ts}', 'Relationship Types', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 5 ),
6a488035 145 ( @domainID, 'civicrm/admin/options/subtype&reset=1', '{ts escape="sql" skip="true"}Contact Types{/ts}','Contact Types', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 6 ),
10824d34 146 ( @domainID, 'civicrm/admin/setting/preferences/display&reset=1', '{ts escape="sql" skip="true"}Display Preferences{/ts}', 'Display Preferences', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 9 ),
147 ( @domainID, 'civicrm/admin/setting/search&reset=1', '{ts escape="sql" skip="true"}Search Preferences{/ts}', 'Search Preferences', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 10 ),
148 ( @domainID, 'civicrm/admin/menu&reset=1', '{ts escape="sql" skip="true"}Navigation Menu{/ts}', 'Navigation Menu', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 11 ),
6a488035
TO		 149 ( @domainID, 'civicrm/admin/options/wordreplacements&reset=1','{ts escape="sql" skip="true"}Word Replacements{/ts}','Word Replacements', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 12 ),
150 ( @domainID, 'civicrm/admin/options/custom_search&reset=1&group=custom_search', '{ts escape="sql" skip="true"}Manage Custom Searches{/ts}', 'Manage Custom Searches', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 13 ),
151 ( @domainID, 'civicrm/admin/extensions&reset=1', '{ts escape="sql" skip="true"}Manage Extensions{/ts}', 'Manage Extensions', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 14 );
152
153INSERT INTO civicrm_navigation
154 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 155VALUES
6a488035
TO		 156 ( @domainID, NULL, '{ts escape="sql" skip="true"}Dropdown Options{/ts}', 'Dropdown Options', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 8 );
157
158SET @optionListlastID:=LAST_INSERT_ID();
159INSERT INTO civicrm_navigation
160 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 161VALUES
162 ( @domainID, 'civicrm/admin/options/gender&reset=1&group=gender', '{ts escape="sql" skip="true"}Gender Options{/ts}', 'Gender Options', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 1 ),
163 ( @domainID, 'civicrm/admin/options/individual_prefix&group=individual_prefix&reset=1', '{ts escape="sql" skip="true"}Individual Prefixes (Ms, Mr...){/ts}', 'Individual Prefixes (Ms, Mr...)', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 2 ),
164 ( @domainID, 'civicrm/admin/options/individual_suffix&group=individual_suffix&reset=1', '{ts escape="sql" skip="true"}Individual Suffixes (Jr, Sr...){/ts}', 'Individual Suffixes (Jr, Sr...)', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 3 ),
165 ( @domainID, 'civicrm/admin/options/instant_messenger_service&group=instant_messenger_service&reset=1', '{ts escape="sql" skip="true"}Instant Messenger Services{/ts}', 'Instant Messenger Services', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 4 ),
166 ( @domainID, 'civicrm/admin/locationType&reset=1', '{ts escape="sql" skip="true"}Location Types (Home, Work...){/ts}', 'Location Types (Home, Work...)', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 5 ),
167 ( @domainID, 'civicrm/admin/options/mobile_provider&group=mobile_provider&reset=1', '{ts escape="sql" skip="true"}Mobile Phone Providers{/ts}', 'Mobile Phone Providers', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 6 ),
168 ( @domainID, 'civicrm/admin/options/phone_type&group=phone_type&reset=1', '{ts escape="sql" skip="true"}Phone Types{/ts}', 'Phone Types', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 7 ),
6a488035
TO		 169 ( @domainID, 'civicrm/admin/options/website_type&group=website_type&reset=1', '{ts escape="sql" skip="true"}Website Types{/ts}', 'Website Types', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 8 );
170
171INSERT INTO civicrm_navigation
172 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
173VALUES
174 ( @domainID, NULL, '{ts escape="sql" skip="true"}Communications{/ts}', 'Communications', 'administer CiviCRM', '', @adminlastID, '1', NULL, 4 );
175
176SET @communicationslastID:=LAST_INSERT_ID();
177INSERT INTO civicrm_navigation
178 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 179VALUES
180 ( @domainID, 'civicrm/admin/domain&action=update&reset=1', '{ts escape="sql" skip="true"}Organization Address and Contact Info{/ts}', 'Organization Address and Contact Info', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 1 ),
181 ( @domainID, 'civicrm/admin/options/from_email_address&group=from_email_address&reset=1', '{ts escape="sql" skip="true"}FROM Email Addresses{/ts}', 'FROM Email Addresses', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 2 ),
182 ( @domainID, 'civicrm/admin/messageTemplates&reset=1', '{ts escape="sql" skip="true"}Message Templates{/ts}', 'Message Templates', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 3 ),
6a488035
TO		 183 ( @domainID, 'civicrm/admin/scheduleReminders&reset=1', '{ts escape="sql" skip="true"}Schedule Reminders{/ts}', 'Schedule Reminders', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 4 ),
184 ( @domainID, 'civicrm/admin/options/preferred_communication_method&group=preferred_communication_method&reset=1', '{ts escape="sql" skip="true"}Preferred Communication Methods{/ts}', 'Preferred Communication Methods', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 5 ),
185 ( @domainID, 'civicrm/admin/labelFormats&reset=1', '{ts escape="sql" skip="true"}Label Formats{/ts}', 'Label Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 6 ),
10824d34 186 ( @domainID, 'civicrm/admin/pdfFormats&reset=1', '{ts escape="sql" skip="true"}Print Page (PDF) Formats{/ts}', 'Print Page (PDF) Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 7 ),
187 ( @domainID, 'civicrm/admin/options/email_greeting&group=email_greeting&reset=1', '{ts escape="sql" skip="true"}Email Greeting Formats{/ts}', 'Email Greeting Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 8 ),
188 ( @domainID, 'civicrm/admin/options/postal_greeting&group=postal_greeting&reset=1', '{ts escape="sql" skip="true"}Postal Greeting Formats{/ts}', 'Postal Greeting Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 9 ),
6a488035
TO		 189 ( @domainID, 'civicrm/admin/options/addressee&group=addressee&reset=1', '{ts escape="sql" skip="true"}Addressee Formats{/ts}', 'Addressee Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 10 );
190
191INSERT INTO civicrm_navigation
192 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 193VALUES
6a488035
TO		 194 ( @domainID, NULL, '{ts escape="sql" skip="true"}Localization{/ts}', 'Localization', 'administer CiviCRM', '', @adminlastID, '1', NULL, 6 );
195
196SET @locallastID:=LAST_INSERT_ID();
197INSERT INTO civicrm_navigation
198 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 199VALUES
200 ( @domainID, 'civicrm/admin/setting/localization&reset=1', '{ts escape="sql" skip="true"}Languages, Currency, Location{/ts}', 'Languages, Currency, Locations', 'administer CiviCRM', '', @locallastID, '1', NULL, 1 ),
201 ( @domainID, 'civicrm/admin/setting/preferences/address&reset=1', '{ts escape="sql" skip="true"}Address Settings{/ts}', 'Address Settings', 'administer CiviCRM', '', @locallastID, '1', NULL, 2 ),
6a488035
TO		 202 ( @domainID, 'civicrm/admin/setting/date&reset=1', '{ts escape="sql" skip="true"}Date Format{/ts}', 'Date Formats', 'administer CiviCRM', '', @locallastID, '1', NULL, 3 ),
203 ( @domainID, 'civicrm/admin/options/languages&group=languages&reset=1', '{ts escape="sql" skip="true"}Preferred Language Options{/ts}', 'Preferred Language Options', 'administer CiviCRM', '', @locallastID, '1', NULL, 4 );
204
205INSERT INTO civicrm_navigation
206 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 207VALUES
6a488035
TO		 208 ( @domainID, NULL, '{ts escape="sql" skip="true"}Users and Permissions{/ts}', 'Users and Permissions', 'administer CiviCRM', '', @adminlastID, '1', NULL, 7 );
209
210SET @usersPermslastID:=LAST_INSERT_ID();
211INSERT INTO civicrm_navigation
212 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 213VALUES
6a488035
TO		 214 ( @domainID, 'civicrm/admin/access&reset=1', '{ts escape="sql" skip="true"}Permissions (Access Control){/ts}', 'Permissions (Access Control)', 'administer CiviCRM', '', @usersPermslastID, '1', NULL, 1 ),
215 ( @domainID, 'civicrm/admin/synchUser&reset=1', '{ts escape="sql" skip="true"}Synchronize Users to Contacts{/ts}', 'Synchronize Users to Contacts', 'administer CiviCRM', '', @usersPermslastID, '1', NULL, 2 );
216
217INSERT INTO civicrm_navigation
218 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
219VALUES
220 ( @domainID, NULL, '{ts escape="sql" skip="true"}System Settings{/ts}', 'System Settings', 'administer CiviCRM', '', @adminlastID, '1', NULL, 8 );
221
222SET @systemSettingslastID:=LAST_INSERT_ID();
223INSERT INTO civicrm_navigation
224 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
225VALUES
10824d34 226 ( @domainID, 'civicrm/admin/setting/component&reset=1', '{ts escape="sql" skip="true"}Enable CiviCRM Components{/ts}', 'Enable Components', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 1 ),
227 ( @domainID, 'civicrm/admin/setting/smtp&reset=1', '{ts escape="sql" skip="true"}Outbound Email (SMTP/Sendmail){/ts}', 'Outbound Email', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 2 ),
228 ( @domainID, 'civicrm/admin/paymentProcessor&reset=1', '{ts escape="sql" skip="true"}Payment Processors{/ts}', 'Payment Processors', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 3 ),
229 ( @domainID, 'civicrm/admin/setting/mapping&reset=1', '{ts escape="sql" skip="true"}Mapping and Geocoding{/ts}', 'Mapping and Geocoding', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 4 ),
230 ( @domainID, 'civicrm/admin/setting/misc&reset=1', '{ts escape="sql" skip="true"}Undelete, Logging and ReCAPTCHA{/ts}', 'Undelete, Logging and ReCAPTCHA', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 5 ),
231 ( @domainID, 'civicrm/admin/setting/path&reset=1', '{ts escape="sql" skip="true"}Directories{/ts}', 'Directories', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 6 ),
232 ( @domainID, 'civicrm/admin/setting/url&reset=1', '{ts escape="sql" skip="true"}Resource URLs{/ts}', 'Resource URLs', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 7 ),
6a488035 233 ( @domainID, 'civicrm/admin/setting/updateConfigBackend&reset=1', '{ts escape="sql" skip="true"}Cleanup Caches and Update Paths{/ts}', 'Cleanup Caches and Update Paths', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 8 ),
10824d34 234 ( @domainID, 'civicrm/admin/setting/uf&reset=1', '{ts escape="sql" skip="true"}CMS Database Integration{/ts}', 'CMS Integration', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 9 ),
235 ( @domainID, 'civicrm/admin/options/safe_file_extension&group=safe_file_extension&reset=1', '{ts escape="sql" skip="true"}Safe File Extensions{/ts}', 'Safe File Extensions','administer CiviCRM', '',@systemSettingslastID, '1', NULL, 10 ),
236 ( @domainID, 'civicrm/admin/options?reset=1', '{ts escape="sql" skip="true"}Option Groups{/ts}', 'Option Groups', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 11 ),
237 ( @domainID, 'civicrm/admin/mapping&reset=1', '{ts escape="sql" skip="true"}Import/Export Mappings{/ts}', 'Import/Export Mappings', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 12 ),
6a488035 238 ( @domainID, 'civicrm/admin/setting/debug&reset=1', '{ts escape="sql" skip="true"}Debugging and Error Handling{/ts}','Debugging and Error Handling', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 13 ),
10824d34 239 ( @domainID, 'civicrm/admin/setting/preferences/multisite&reset=1', '{ts escape="sql" skip="true"}Multi Site Settings{/ts}', 'Multi Site Settings', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 14 );
6a488035
TO		 240
241SELECT @campaignAdminID := id FROM civicrm_navigation where name = 'CiviCampaign' AND parent_id = @adminlastID;
242SELECT @eventAdminID := id FROM civicrm_navigation where name = 'CiviEvent' AND parent_id = @adminlastID;
243SELECT @mailAdminID := id FROM civicrm_navigation where name = 'CiviMail' AND parent_id = @adminlastID;
244SELECT @memberAdminID := id FROM civicrm_navigation where name = 'CiviMember' AND parent_id = @adminlastID;
245
246INSERT INTO civicrm_navigation
247 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
248VALUES
249 ( @domainID, 'civicrm/admin/setting/preferences/campaign&reset=1', '{ts escape="sql" skip="true"}CiviCampaign Component Settings{/ts}', 'CiviCampaign Component Settings','administer CiviCampaign', '', @campaignAdminID, '1', NULL, 5 ),
10824d34 250 ( @domainID, 'civicrm/admin/setting/preferences/event&reset=1', '{ts escape="sql" skip="true"}CiviEvent Component Settings{/ts}', 'CiviEvent Component Settings','access CiviEvent,administer CiviCRM', 'AND', @eventAdminID, '1', NULL, 13 ),
251 ( @domainID, 'civicrm/admin/setting/preferences/mailing&reset=1', '{ts escape="sql" skip="true"}CiviMail Component Settings{/ts}', 'CiviMail Component Settings','access CiviMail,administer CiviCRM', 'AND', @mailAdminID, '1', NULL, 6 ),
252 ( @domainID, 'civicrm/admin/setting/preferences/member&reset=1', '{ts escape="sql" skip="true"}CiviMember Component Settings{/ts}', 'CiviMember Component Settings','access CiviMember,administer CiviCRM', 'AND', @memberAdminID, '1', NULL, 5 ),
253 ( @domainID, 'civicrm/admin/options/event_badge&group=event_badge&reset=1', '{ts escape="sql" skip="true"}Event Badge Formats{/ts}', 'Event Badge Formats', 'access CiviEvent,administer CiviCRM', 'AND', @eventAdminID, '1', NULL, 11 );
6a488035
TO		 254
255-- CRM-9113
256ALTER TABLE `civicrm_report_instance` ADD `grouprole` VARCHAR( 1024 ) NULL AFTER `permission`;
257
258-- CRM-8762 Fix option_group table
259ALTER TABLE civicrm_option_group CHANGE `is_reserved` `is_reserved` TINYINT DEFAULT 1;
260UPDATE civicrm_option_group SET `is_reserved` = 1;
261{if $multilingual}
262 {foreach from=$locales item=locale}
263 UPDATE civicrm_option_group SET label_{$locale} = description_{$locale} WHERE label_{$locale} IS NULL;
264 {/foreach}
265{else}
266 UPDATE civicrm_option_group SET `label` = `description` WHERE `label` IS NULL;
267{/if}
268
269-- CRM-9112
270 ALTER TABLE `civicrm_dedupe_rule_group` ADD `title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Label of the rule group';
271
272ALTER TABLE `civicrm_dedupe_rule_group` ADD `is_reserved` TINYINT( 4 ) NULL DEFAULT NULL COMMENT 'Is this a reserved rule - a rule group that has been optimized and cannot be changed by the admin';
273
274UPDATE `civicrm_dedupe_rule_group` SET `name` = CONCAT( REPLACE( `name`, '-', '' ), '-', id );
275
276-- the fuzzy default dedupe rules
277INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default, name, title, is_reserved)
278VALUES ('Individual', 20, 'Fuzzy', 1, 'IndividualFuzzy', 'Individual Fuzzy In-built', 1);
279
280SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
281INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
282VALUES (@drgid, 'civicrm_contact', 'first_name', 5),
283 (@drgid, 'civicrm_contact', 'last_name', 7),
284 (@drgid, 'civicrm_email' , 'email', 10);
285
286-- the strict dedupe rules
287INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default, name, title, is_reserved)
288VALUES ('Individual', 10, 'Strict', 1, 'IndividualStrict', 'Individual Strict In-built', 1);
289
290SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
291INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
292VALUES (@drgid, 'civicrm_email', 'email', 10);
293
294INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default, name, title, is_reserved)
295VALUES ('Individual', 15, 'Strict', 0, 'IndividualComplete', 'Individual Complete Inbuilt', 1);
296
297SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
298INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
299VALUES (@drgid, 'civicrm_contact', 'first_name', '5'),
300 (@drgid, 'civicrm_contact', 'last_name', '5'),
301 (@drgid, 'civicrm_address', 'street_address', '5'),
302 (@drgid, 'civicrm_contact', 'middle_name', '1'),
303 (@drgid, 'civicrm_contact', 'suffix_id', '1');
304
305-- CRM-9120
306{if $multilingual}
307 {foreach from=$locales item=locale}
308 ALTER TABLE `civicrm_location_type` ADD display_name_{$locale} VARCHAR( 64 ) DEFAULT NULL COMMENT 'Location Type Display Name.' AFTER `name`;
309 UPDATE `civicrm_location_type` SET display_name_{$locale} = `name`;
310 {/foreach}
311{else}
312 ALTER TABLE `civicrm_location_type` ADD `display_name` VARCHAR( 64 ) DEFAULT NULL COMMENT 'Location Type Display Name.' AFTER `name`;
313 UPDATE `civicrm_location_type` SET `display_name` = `name`;
314{/if}
315
316
317-- CRM-9125
318ALTER TABLE `civicrm_contribution_recur` ADD `contribution_type_id` int(10) unsigned NULL COMMENT 'FK to Contribution Type';
319ALTER TABLE `civicrm_contribution_recur` ADD CONSTRAINT `FK_civicrm_contribution_recur_contribution_type_id` FOREIGN KEY (`contribution_type_id`) REFERENCES `civicrm_contribution_type` (`id`) ON DELETE SET NULL;
320
321ALTER TABLE `civicrm_contribution_recur` ADD `payment_instrument_id` int(10) unsigned NULL COMMENT 'FK to Payment Instrument';
322ALTER TABLE `civicrm_contribution_recur` ADD INDEX UI_contribution_recur_payment_instrument_id ( payment_instrument_id );
323
324ALTER TABLE `civicrm_contribution_recur` ADD `campaign_id` int(10) unsigned NULL COMMENT 'The campaign for which this contribution has been triggered.';
325ALTER TABLE `civicrm_contribution_recur` ADD CONSTRAINT `FK_civicrm_contribution_recur_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL;
326
327UPDATE `civicrm_contribution_recur` ccr INNER JOIN `civicrm_contribution` cc ON ccr.id = cc.contribution_recur_id SET ccr.campaign_id = cc.campaign_id, ccr.payment_instrument_id = cc.payment_instrument_id, ccr.contribution_type_id = cc.contribution_type_id;
328
329-- CRM-8962
10824d34 330INSERT INTO civicrm_action_mapping ( entity, entity_value, entity_value_label, entity_status, entity_status_label, entity_date_start, entity_date_end, entity_recipient )
6a488035
TO		 331VALUES
332('civicrm_participant', 'event_type', 'Event Type', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts'),
333('civicrm_participant', 'civicrm_event', 'Event Name', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts');
334
335INSERT INTO civicrm_option_group
336 (name, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, is_reserved, is_active)
337VALUES
338 ('event_contacts', {localize}'{ts escape="sql"}Event Recipients{/ts}'{/localize}, {localize}'{ts escape="sql"}Event Recipients{/ts}'{/localize}, 1, 1),
339 ('contact_reference_options', {localize}'{ts escape="sql"}Contact Reference Autocomplete Options{/ts}'{/localize}, {localize}'{ts escape="sql"}Contact Reference Autocomplete Options{/ts}'{/localize}, 1, 1);
340
341SELECT @option_group_id_ere := max(id) from civicrm_option_group where name = 'event_contacts';
342
10824d34 343INSERT INTO civicrm_option_value
344 (option_group_id, {localize field='label'}label{/localize}, value, name, filter, weight, is_active )
6a488035
TO		 345VALUES
346 (@option_group_id_ere, {localize}'{ts escape="sql"}Participant Role{/ts}'{/localize}, 1, 'participant_role', 0, 1, 1 );
347
348ALTER TABLE civicrm_action_schedule ADD `absolute_date` date DEFAULT NULL COMMENT 'Date on which the reminder be sent.';
349ALTER TABLE civicrm_action_schedule ADD `recipient_listing` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'listing based on recipient field.';
10824d34 350
351
6a488035 352-- CRM-8534
10824d34 353ALTER TABLE civicrm_pcp_block
6a488035
TO		 354 DROP FOREIGN KEY FK_civicrm_pcp_block_entity_id,
355 DROP INDEX FK_civicrm_pcp_block_entity_id;
356
10824d34 357ALTER TABLE civicrm_pcp
6a488035
TO		 358 DROP FOREIGN KEY FK_civicrm_pcp_contribution_page_id,
359 DROP INDEX FK_civicrm_pcp_contribution_page_id;
360
10824d34 361ALTER TABLE `civicrm_pcp`
6a488035 362 ADD `page_type` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'contribute' AFTER `contribution_page_id`;
10824d34 363ALTER TABLE `civicrm_pcp`
6a488035
TO		 364 CHANGE `contribution_page_id` `page_id` INT( 10 ) UNSIGNED NOT NULL COMMENT 'The Page which triggered this pcp';
365ALTER TABLE `civicrm_pcp`
366 ADD `pcp_block_id` int(10) unsigned NOT NULL COMMENT 'The pcp block that this pcp page was created from' AFTER `page_type`;
367
368UPDATE `civicrm_pcp`
369 SET `page_type` = 'contribute' WHERE `page_type` = '' OR `page_type` IS NULL;
370UPDATE `civicrm_pcp` `pcp`
371 SET `pcp_block_id` = (SELECT `id` FROM `civicrm_pcp_block` `pb` WHERE `pb`.`entity_id` = `pcp`.`page_id`);
372
373ALTER TABLE `civicrm_pcp_block`
374 ADD `target_entity_type` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'contribute' AFTER `entity_id`;
375ALTER TABLE `civicrm_pcp_block`
376 ADD `target_entity_id` int(10) unsigned NOT NULL COMMENT 'The entity that this pcp targets' AFTER `target_entity_type`;
377UPDATE `civicrm_pcp_block`
378 SET `target_entity_id` = `entity_id` WHERE `target_entity_id` = '' OR `target_entity_id` IS NULL;
379
380ALTER TABLE `civicrm_pcp` DROP COLUMN `referer`;
381
382UPDATE civicrm_navigation SET url = 'civicrm/admin/pcp?reset=1&page_type=contribute' WHERE url = 'civicrm/admin/pcp&reset=1';
383
c55ee104 384SELECT @lastEventId := MAX(id) FROM civicrm_navigation where name = 'Events' AND domain_id = @domainID;
385SELECT @adminEventId := MAX(id) FROM civicrm_navigation where name = 'CiviEvent' AND domain_id = @domainID;
6a488035
TO		 386SELECT @lastEventIdWeight := MAX(weight)+1 FROM civicrm_navigation where parent_id = @lastEventId;
387SELECT @adminEventIdWeight := MAX(weight)+1 FROM civicrm_navigation where parent_id = @adminEventId;
388
389INSERT INTO civicrm_navigation
390 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
10824d34 391VALUES
6a488035
TO		 392 ( @domainID, 'civicrm/admin/pcp?reset=1&page_type=event', '{ts escape="sql" skip="true"}Personal Campaign Pages{/ts}', 'Personal Campaign Pages', 'access CiviEvent,administer CiviCRM', 'AND', @lastEventId, '1', 1, @lastEventIdWeight ),
393 ( @domainID, 'civicrm/admin/pcp?reset=1&page_type=event', '{ts escape="sql" skip="true"}Personal Campaign Pages{/ts}', 'Personal Campaign Pages', 'access CiviEvent,administer CiviCRM', 'AND', @adminEventId, '1', 1, @adminEventIdWeight );
394
395-- CRM-8358 - consolidated cron jobs
396
397CREATE TABLE `civicrm_job` (
398 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Job Id',
399 `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for',
400 `run_frequency` enum('Hourly','Daily','Always') COLLATE utf8_unicode_ci DEFAULT 'Daily' COMMENT 'Scheduled job run frequency.',
401 `last_run` datetime DEFAULT NULL COMMENT 'When was this cron entry last run',
402 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the job',
403 `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the job',
404 `api_prefix` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Prefix of the job api call',
405 `api_entity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity of the job api call',
406 `api_action` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Action of the job api call',
407 `parameters` text COLLATE utf8_unicode_ci COMMENT 'List of parameters to the command.',
408 `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this job active?',
409 PRIMARY KEY (`id`),
410 KEY `FK_civicrm_job_domain_id` (`domain_id`),
411 CONSTRAINT `FK_civicrm_job_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`)
412) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
413
414CREATE TABLE `civicrm_job_log` (
415 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Job log entry Id',
416 `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for',
417 `run_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Log entry date',
418 `job_id` int(10) unsigned DEFAULT NULL COMMENT 'Pointer to job id - not a FK though, just for logging purposes',
419 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the job',
420 `command` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Full path to file containing job script',
421 `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title line of log entry',
422 `data` text COLLATE utf8_unicode_ci COMMENT 'Potential extended data for specific job run (e.g. tracebacks).',
423 PRIMARY KEY (`id`),
424 KEY `FK_civicrm_job_log_domain_id` (`domain_id`),
425 CONSTRAINT `FK_civicrm_job_log_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`)
426) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
427
428INSERT INTO `civicrm_job`
10824d34 429 ( domain_id, run_frequency, last_run, name, description, api_prefix, api_entity, api_action, parameters, is_active )
430VALUES
6a488035
TO		 431 ( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Mailings scheduler{/ts}', '{ts escape="sql" skip="true"}Sends out scheduled mailings{/ts}', 'civicrm_api3', 'job', 'process_mailing', 'user=USERNAME\r\npassword=PASSWORD\r\nkey=SITE_KEY', 0),
432 ( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Bounces fetcher{/ts}', '{ts escape="sql" skip="true"}Fetches bounces from mailings and writes them to mailing statistics{/ts}', 'civicrm_api3', 'job', 'fetch_bounces', 'user=USERNAME\r\npassword=PASSWORD\r\nkey=SITE_KEY', 0),
433 ( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Activity processor{/ts}', '{ts escape="sql" skip="true"}{/ts}', 'civicrm_api3', 'job', 'fetch_activities', 'user=USERNAME\r\npassword=PASSWORD\r\nkey=SITE_KEY', 0),
434 ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Pledge record processor{/ts}', '{ts escape="sql" skip="true"}Updates pledge records and sends out reminders{/ts}', 'civicrm_api3', 'job', 'process_pledge', 'version=3\r\n', 0),
435 ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Address geocoder{/ts}', '{ts escape="sql" skip="true"}Goes through addresses and geocodes them (requires Geocoding API on){/ts}', 'civicrm_api3', 'job', 'geocode', 'version=3\r\n', 0),
436 ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Greeting updater{/ts}', '{ts escape="sql" skip="true"}Goes through contact records and updates greeting settings{/ts}', 'civicrm_api3', 'job', 'update_greeting', 'version=3\r\n', 0),
437 ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Report sender{/ts}', '{ts escape="sql" skip="true"}Generates and sends out reports via email{/ts}', 'civicrm_api3', 'job', 'mail_reports', 'version=3\r\n', 0),
438 ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Scheduled reminders sender{/ts}', '{ts escape="sql" skip="true"}Sends out scheduled reminders via email.{/ts}', 'civicrm_api3', 'job', 'send_reminder', 'version=3\r\n', 0),
439 ( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Participant status processor{/ts}','{ts escape="sql" skip="true"}Adjusts event participant statuses based on time{/ts}', 'civicrm_api3', 'job', 'process_participant', 'version=3\r\n', 0),
440 ( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Membership status processor{/ts}', '{ts escape="sql" skip="true"}Adjusts event membership statuses based on time{/ts}', 'civicrm_api3', 'job', 'process_membership', 'version=3\r\n', 0),
441 ( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Membership reminder date processor{/ts}','{ts escape="sql" skip="true"}Adjusts membership reminder dates based on time{/ts}', 'civicrm_api3', 'job', 'process_process_membership_reminder_date', 'version=3\r\n', 0);
442
443--CRM 9135
444ALTER TABLE civicrm_contribution_recur
445 ADD is_email_receipt TINYINT (4) COMMENT 'if true, receipt is automatically emailed to contact on each successful payment' AFTER payment_processor_id;
446
10824d34 447-- /***** Civicrm Multi-Event Registration ***********/
6a488035
TO		 448
449CREATE TABLE civicrm_event_carts (
450 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Cart Id',
451 user_id int unsigned COMMENT 'FK to civicrm_contact who created this cart',
452 coupon_code varchar(255) DEFAULT NULL,
453 completed tinyint DEFAULT 0,
454 PRIMARY KEY ( id ),
455 CONSTRAINT FK_civicrm_event_carts_user_id FOREIGN KEY (user_id)
456REFERENCES civicrm_contact(id) ON DELETE SET NULL
457) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
458
459CREATE TABLE civicrm_events_in_carts (
460 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Event In Cart Id',
461 event_id int unsigned COMMENT 'FK to Event ID',
462 event_cart_id int unsigned COMMENT 'FK to Event Cart ID',
463 PRIMARY KEY ( id ),
464 CONSTRAINT FK_civicrm_events_in_carts_event_id FOREIGN KEY (event_id)
465REFERENCES civicrm_event(id) ON DELETE CASCADE,
466 CONSTRAINT FK_civicrm_events_in_carts_event_cart_id FOREIGN KEY
467(event_cart_id) REFERENCES civicrm_event_carts(id) ON DELETE CASCADE
468) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
469
470
471ALTER TABLE civicrm_participant
472 ADD discount_amount int unsigned DEFAULT 0 COMMENT 'Discount Amount';
473ALTER TABLE civicrm_participant
474 ADD cart_id int unsigned DEFAULT NULL COMMENT 'FK to civicrm_event_carts';
475ALTER TABLE civicrm_participant
476 ADD CONSTRAINT FK_civicrm_participant_cart_id FOREIGN KEY (cart_id)
477 REFERENCES civicrm_event_carts(id) ON DELETE SET NULL;
478
479-- XXX a hint to the payment form. Can someone make this go away?
480ALTER TABLE civicrm_participant
481 ADD must_wait TINYINT DEFAULT 0 COMMENT 'On Waiting List';
482
483
484SELECT @pending_id := MAX(id) + 1 FROM civicrm_participant_status_type;
485INSERT INTO civicrm_participant_status_type
486 (id, name, {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id)
487VALUES
488 (@pending_id, 'Pending in cart', {localize}'{ts escape="sql"}Pending in cart{/ts}'{/localize}, 'Pending', 1, 1, 0, @pending_id, 2 );
489
490
491ALTER TABLE civicrm_event
492 ADD parent_event_id int unsigned DEFAULT NULL COMMENT 'Implicit FK to civicrm_event: parent event';
493ALTER TABLE civicrm_event
494 ADD slot_label_id int unsigned DEFAULT NULL COMMENT 'Subevent slot label. Implicit FK to civicrm_option_value where option_group = conference_slot.';
495
10824d34 496INSERT INTO
6a488035 497 `civicrm_option_group` (`name`, {localize field='label'}`label`{/localize}, {localize field='description'}`description`{/localize}, `is_reserved`, `is_active`)
10824d34 498VALUES
6a488035
TO		 499 ('conference_slot' , {localize}'{ts escape="sql"}Conference Slot{/ts}'{/localize}, {localize}'{ts escape="sql"}Conference Slot{/ts}'{/localize} , 1, 1);
500
501SELECT @option_group_id_conference_slot := max(id) from civicrm_option_group where name = 'conference_slot';
502
10824d34 503INSERT INTO
504 `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`)
6a488035
TO		 505VALUES
506 (@option_group_id_conference_slot, {localize}'{ts escape="sql"}Morning Sessions{/ts}'{/localize}, 1, '{ts escape="sql"}Morning Sessions{/ts}', NULL, 0, NULL, 1, {localize}'{ts escape="sql"}NULL{/ts}'{/localize}, 0, 0, 1, NULL, NULL),
507 (@option_group_id_conference_slot, {localize}'{ts escape="sql"}Evening Sessions{/ts}'{/localize}, 2, '{ts escape="sql"}Evening Sessions{/ts}', NULL, 0, NULL, 2, {localize}'{ts escape="sql"}NULL{/ts}'{/localize}, 0, 0, 1, NULL, NULL);
508
509SELECT @msg_tpl_workflow_event := MAX(id) FROM civicrm_option_group WHERE name = 'msg_tpl_workflow_event';
510SELECT @weight := MAX(weight) + 1 FROM civicrm_option_value WHERE option_group_id = @msg_tpl_workflow_event;
511
512INSERT INTO civicrm_option_value
513 (option_group_id, name, {localize field='label'}label{/localize}, value, weight)
514VALUES
515 (@msg_tpl_workflow_event, 'event_registration_receipt', {localize}'{ts escape="sql"}Events - Receipt only{/ts}'{/localize}, @weight, @weight);
516
517{* SELECT @tpl_ovid_$vName := MAX(id) FROM civicrm_option_value WHERE option_group_id = @tpl_ogid_$gName AND name = '$vName'; *}
518{* INSERT INTO civicrm_msg_template *}
519
520-- CRM-8532
521UPDATE `civicrm_dashboard` SET url = REPLACE( url, 'snippet=4', 'snippet=5' ), fullscreen_url = REPLACE( fullscreen_url, 'snippet=4', 'snippet=5' );
522
523{if $multilingual}
524 {foreach from=$locales item=locale}
525 ALTER TABLE civicrm_option_group CHANGE label_{$locale} title_{$locale} varchar(255);
526 {/foreach}
527{else}
528 ALTER TABLE civicrm_option_group CHANGE label title varchar(255);
529{/if}