crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/CRM/Upgrade/Incremental/sql/3.3.alpha1.mysql.tpl

   1 -- CRM-6696
   2 ALTER TABLE civicrm_option_value {localize field='description'}MODIFY COLUMN description text{/localize};
   3
   4 -- CRM-6442
   5 SELECT @option_group_id_website := MAX(id) from civicrm_option_group where name = 'website_type';
   6 SELECT @max_value               := MAX(ROUND(value)) from civicrm_option_value where option_group_id = @option_group_id_website;
   7 SELECT @max_weight              := MAX(ROUND(weight)) from civicrm_option_value where option_group_id = @option_group_id_website;;
   8
   9 INSERT INTO civicrm_option_value
  10         (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)
  11 VALUES
  12   (@option_group_id_website, {localize}'Main'{/localize}, @max_value+1, 'Main', NULL, 0, NULL, @max_weight+1, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL);
  13
  14 -- CRM-6763
  15 UPDATE civicrm_option_group
  16    SET is_reserved = 0
  17  WHERE civicrm_option_group.name = 'encounter_medium';
  18
  19 -- CRM-6814
  20 ALTER TABLE `civicrm_note`
  21   ADD `privacy` INT( 10 ) NOT NULL COMMENT 'Foreign Key to Note Privacy Level (which is an option value pair and hence an implicit FK)';
  22
  23 UPDATE `civicrm_note` SET `privacy` = '0' WHERE 1;
  24
  25 INSERT INTO civicrm_option_group
  26       (name, {localize field='description'}description{/localize}, is_reserved, is_active)
  27 VALUES
  28       ('note_privacy', {localize}'Privacy levels for notes'{/localize}, 0, 1);
  29
  30 SELECT @option_group_id_notePrivacy := max(id) from civicrm_option_group where name = 'note_privacy';
  31
  32 INSERT INTO civicrm_option_value
  33       (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)
  34 VALUES
  35       (@option_group_id_notePrivacy, {localize}'None'{/localize}        , 0, '', NULL, 0, 1, 1, 0, 1, 1, NULL, NULL),
  36       (@option_group_id_notePrivacy, {localize}'Author Only'{/localize} , 1, '', NULL, 0, 0, 2, 0, 1, 1, NULL, NULL);
  37
  38 -- CRM-6748
  39 UPDATE civicrm_navigation SET url = 'civicrm/admin/contribute/add&reset=1&action=add'
  40         WHERE civicrm_navigation.name = 'New Contribution Page';
  41
  42 -- CRM-6507
  43 ALTER TABLE civicrm_participant
  44    CHANGE role_id role_id varchar(128) collate utf8_unicode_ci NULL default NULL COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.';
  45
  46 --
  47 -- Campaign upgrade.
  48 --
  49 -- CRM-6232
  50 CREATE TABLE `civicrm_campaign` (
  51   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Campaign ID.',
  52   `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of the Campaign.',
  53   `title` varchar(255) NULL DEFAULT NULL COMMENT 'Title of the Campaign.',
  54   `description` text collate utf8_unicode_ci default NULL COMMENT 'Full description of Campaign.',
  55   `start_date` datetime default NULL COMMENT 'Date and time that Campaign starts.',
  56   `end_date` datetime default NULL COMMENT 'Date and time that Campaign ends.',
  57   `campaign_type_id` int unsigned DEFAULT NULL COMMENT 'Campaign Type ID.Implicit FK to civicrm_option_value where option_group = campaign_type',
  58   `status_id` int unsigned DEFAULT NULL COMMENT 'Campaign status ID.Implicit FK to civicrm_option_value where option_group = campaign_status',
  59   `external_identifier` int unsigned NULL DEFAULT NULL COMMENT 'Unique trusted external ID (generally from a legacy app/datasource). Particularly useful for deduping operations.',
  60   `parent_id` int unsigned NULL DEFAULT NULL COMMENT 'Optional parent id for this Campaign.',
  61   `is_active` boolean NOT NULL DEFAULT 1 COMMENT 'Is this Campaign enabled or disabled/cancelled?',
  62   `created_id` int unsigned NULL DEFAULT NULL COMMENT 'FK to civicrm_contact, who created this Campaign.',
  63   `created_date` datetime default NULL COMMENT 'Date and time that Campaign was created.',
  64   `last_modified_id` int unsigned NULL DEFAULT NULL COMMENT 'FK to civicrm_contact, who recently edited this Campaign.',
  65   `last_modified_date` datetime default NULL COMMENT 'Date and time that Campaign was edited last time.',
  66   PRIMARY KEY ( id ),
  67   INDEX UI_campaign_type_id (campaign_type_id),
  68   INDEX UI_campaign_status_id (status_id),
  69   UNIQUE INDEX UI_external_identifier (external_identifier),
  70   CONSTRAINT FK_civicrm_campaign_created_id FOREIGN KEY (created_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL,
  71   CONSTRAINT FK_civicrm_campaign_last_modified_id FOREIGN KEY (last_modified_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL,
  72   CONSTRAINT FK_civicrm_campaign_parent_id FOREIGN KEY (parent_id) REFERENCES civicrm_campaign(id) ON DELETE SET NULL
  73 )ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  74
  75
  76 CREATE TABLE `civicrm_campaign_group` (
  77   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Campaign Group id.',
  78   `campaign_id` int unsigned NOT NULL COMMENT 'Foreign key to the activity Campaign.',
  79   `group_type` enum('Include','Exclude') NULL DEFAULT NULL COMMENT 'Type of Group.',
  80   `entity_table` varchar(64) NULL DEFAULT NULL COMMENT 'Name of table where item being referenced is stored.',
  81   `entity_id` int unsigned DEFAULT NULL COMMENT 'Entity id of referenced table.',
  82   PRIMARY KEY ( id ),
  83   CONSTRAINT FK_civicrm_campaign_group_campaign_id FOREIGN KEY (campaign_id) REFERENCES civicrm_campaign(id) ON DELETE CASCADE
  84 )ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  85
  86
  87 CREATE TABLE `civicrm_survey` (
  88   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Campaign Group id.',
  89   `title` varchar(255) NOT NULL COMMENT 'Title of the Survey.',
  90   `campaign_id` int unsigned DEFAULT NULL COMMENT 'Foreign key to the activity Campaign.',
  91   `activity_type_id` int unsigned DEFAULT NULL COMMENT 'Implicit FK to civicrm_option_value where option_group = activity_type',
  92   `recontact_interval` text collate utf8_unicode_ci DEFAULT NULL COMMENT 'Recontact intervals for each status.',
  93   `instructions` text collate utf8_unicode_ci DEFAULT NULL COMMENT 'Script instructions for volunteers to use for the survey.',
  94   `release_frequency` int unsigned DEFAULT NULL COMMENT 'Number of days for recurrence of release.',
  95   `max_number_of_contacts` int unsigned DEFAULT NULL COMMENT 'Maximum number of contacts to allow for survey.',
  96   `default_number_of_contacts` int unsigned DEFAULT NULL COMMENT 'Default number of contacts to allow for survey.',
  97   `is_active` boolean NOT NULL DEFAULT 1 COMMENT 'Is this survey enabled or disabled/cancelled?',
  98   `is_default` boolean NOT NULL DEFAULT 0 COMMENT 'Is this default survey?',
  99   `created_id` int unsigned NULL DEFAULT NULL COMMENT 'FK to civicrm_contact, who created this Survey.',
 100   `created_date` datetime default NULL COMMENT 'Date and time that Survey was created.',
 101   `last_modified_id` int unsigned NULL DEFAULT NULL COMMENT 'FK to civicrm_contact, who recently edited this Survey.',
 102   `last_modified_date` datetime default NULL COMMENT 'Date and time that Survey was edited last time.',
 103   `result_id` int unsigned NULL DEFAULT NULL COMMENT 'Used to store option group id.',
 104  PRIMARY KEY ( id ),
 105   CONSTRAINT FK_civicrm_survey_campaign_id FOREIGN KEY (campaign_id) REFERENCES civicrm_campaign(id) ON DELETE CASCADE,
 106   INDEX UI_activity_type_id (activity_type_id),
 107   CONSTRAINT FK_civicrm_survey_created_id FOREIGN KEY (created_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL,
 108   CONSTRAINT FK_civicrm_survey_last_modified_id FOREIGN KEY (last_modified_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL
 109 )ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 110
 111
 112 --add result column to activity table.
 113 ALTER TABLE `civicrm_activity` ADD `result` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT 'Currently being used to store result for survey activity. FK to option value.' AFTER `original_id`;
 114
 115 --insert campaign component.
 116 INSERT INTO civicrm_component (name, namespace) VALUES ('CiviCampaign'  , 'CRM_Campaign' );
 117
 118 INSERT INTO civicrm_option_group
 119          (`name`, {localize field='description'}description{/localize}, `is_active`)
 120 VALUES
 121   ('campaign_type'    , {localize}'Campaign Type'{/localize}     , 1 ),
 122      ('campaign_status'  , {localize}'Campaign Status'{/localize}   , 1 );
 123
 124 --insert values for Compaign Types, Campaign Status and Activity types.
 125
 126 SELECT @option_group_id_campaignType   := max(id) from civicrm_option_group where name = 'campaign_type';
 127 SELECT @option_group_id_campaignStatus := max(id) from civicrm_option_group where name = 'campaign_status';
 128 SELECT @option_group_id_act            := max(id) from civicrm_option_group where name = 'activity_type';
 129 SELECT @campaignCompId                 := max(id) FROM civicrm_component where name    = 'CiviCampaign';
 130 SELECT @max_campaign_act_val           := MAX(ROUND(value)) from civicrm_option_value where option_group_id = @option_group_id_act;
 131 SELECT @max_campaign_act_wt            := MAX(ROUND(weight)) from civicrm_option_value where option_group_id = @option_group_id_act;
 132
 133 INSERT INTO
 134    `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `weight`, `is_active`, `component_id` )
 135 VALUES
 136   (@option_group_id_campaignType, {localize}'Direct Mail'{/localize},      1, 'Direct Mail',       1,   1, NULL ),
 137   (@option_group_id_campaignType, {localize}'Referral Program'{/localize}, 2, 'Referral Program',  2,   1, NULL ),
 138   (@option_group_id_campaignType, {localize}'Voter Engagement'{/localize}, 3, 'Voter Engagement',  3,   1, NULL ),
 139
 140   (@option_group_id_campaignStatus, {localize}'Planned'{/localize},        1, 'Planned',           1,   1, NULL ),
 141   (@option_group_id_campaignStatus, {localize}'In Progress'{/localize},    2, 'In Progress',       2,   1, NULL ),
 142   (@option_group_id_campaignStatus, {localize}'Completed'{/localize},      3, 'Completed',         3,   1, NULL ),
 143   (@option_group_id_campaignStatus, {localize}'Cancelled'{/localize},      4, 'Cancelled',         4,   1, NULL ),
 144
 145   (@option_group_id_act, {localize}'Survey'{/localize},                   (SELECT @max_campaign_act_val := @max_campaign_act_val + 1 ), 'Survey',           (SELECT @max_campaign_act_wt := @max_campaign_act_wt + 1 ),   1, @campaignCompId ),
 146   (@option_group_id_act, {localize}'Canvass'{/localize},                  (SELECT @max_campaign_act_val := @max_campaign_act_val + 1 ), 'Canvass',          (SELECT @max_campaign_act_wt := @max_campaign_act_wt + 1 ),   1, @campaignCompId ),
 147   (@option_group_id_act, {localize}'PhoneBank'{/localize},                (SELECT @max_campaign_act_val := @max_campaign_act_val + 1 ), 'PhoneBank',        (SELECT @max_campaign_act_wt := @max_campaign_act_wt + 1 ),   1, @campaignCompId ),
 148   (@option_group_id_act, {localize}'WalkList'{/localize},                 (SELECT @max_campaign_act_val := @max_campaign_act_val + 1 ), 'WalkList',         (SELECT @max_campaign_act_wt := @max_campaign_act_wt + 1 ),   1, @campaignCompId ),
 149   (@option_group_id_act, {localize}'Petition'{/localize},                 (SELECT @max_campaign_act_val := @max_campaign_act_val + 1 ), 'Petition',         (SELECT @max_campaign_act_wt := @max_campaign_act_wt + 1 ),   1, @campaignCompId );
 150
 151 --campaign navigation.
 152 SELECT @domainID        := MIN(id) FROM civicrm_domain;
 153 SELECT @nav_other_id    := id FROM civicrm_navigation WHERE name = 'Other';
 154 SELECT @nav_other_wt    := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @nav_other_id;
 155
 156 --insert campaigns permissions in 'Other' navigation menu permissions.
 157 UPDATE  civicrm_navigation
 158    SET  permission = CONCAT( permission, ',administer CiviCampaign,manage campaign,reserve campaign contacts,release campaign contacts,interview campaign contacts' )
 159  WHERE  id = @nav_other_id;
 160
 161 INSERT INTO civicrm_navigation
 162     ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 163 VALUES
 164     ( @domainID, NULL, '{ts escape="sql"}Campaigns{/ts}', 'Campaigns', 'interview campaign contacts,release campaign contacts,reserve campaign contacts,manage campaign,administer CiviCampaign', 'OR', @nav_other_id, '1', NULL, (SELECT @nav_other_wt := @nav_other_wt + 1) );
 165
 166 SELECT @nav_campaign_id    := id FROM civicrm_navigation WHERE name = 'Campaigns';
 167
 168 INSERT INTO civicrm_navigation
 169     ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 170 VALUES
 171     ( @domainID, 'civicrm/campaign&reset=1',        '{ts escape="sql"}Dashboard{/ts}', 'Dashboard', 'administer CiviCampaign', '', @nav_campaign_id, '1', NULL, 1 );
 172
 173 SET @campaigndashboardlastID:=LAST_INSERT_ID();
 174
 175 INSERT INTO civicrm_navigation
 176     ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 177 VALUES
 178     ( @domainID, 'civicrm/campaign&reset=1&subPage=survey',        '{ts escape="sql"}Surveys{/ts}', 'Survey Dashboard', 'administer CiviCampaign', '', @campaigndashboardlastID, '1', NULL, 1 ),
 179     ( @domainID, 'civicrm/campaign&reset=1&subPage=petition',        '{ts escape="sql"}Petition{/ts}', 'Petition Dashboard', 'administer CiviCampaign', '', @campaigndashboardlastID, '1', NULL, 2 ),
 180     ( @domainID, 'civicrm/campaign&reset=1&subPage=campaign',        '{ts escape="sql"}Campaigns{/ts}', 'Campaign Dashboard', 'administer CiviCampaign', '', @campaigndashboardlastID, '1', NULL, 3 ),
 181     ( @domainID, 'civicrm/campaign/add&reset=1',        '{ts escape="sql"}New Campaign{/ts}', 'New Campaign', 'administer CiviCampaign', '', @nav_campaign_id, '1', NULL, 2 ),
 182     ( @domainID, 'civicrm/survey/add&reset=1',        '{ts escape="sql"}New Survey{/ts}', 'New Survey', 'administer CiviCampaign', '', @nav_campaign_id, '1', NULL, 3 ),
 183     ( @domainID, 'civicrm/petition/add&reset=1',        '{ts escape="sql"}New Petition{/ts}', 'New Petition', 'administer CiviCampaign', '', @nav_campaign_id, '1', NULL, 4 ),
 184     ( @domainID, 'civicrm/survey/search&reset=1&op=reserve', '{ts escape="sql"}Reserve Voters{/ts}', 'Reserve Voters', 'administer CiviCampaign,manage campaign,reserve campaign contacts', 'OR', @nav_campaign_id, '1', NULL, 5 ),
 185     ( @domainID, 'civicrm/survey/search&reset=1&op=interview', '{ts escape="sql"}Interview Voters{/ts}', 'Interview Voters', 'administer CiviCampaign,manage campaign,interview campaign contacts', 'OR', @nav_campaign_id, '1', NULL, 6 ),
 186     ( @domainID, 'civicrm/survey/search&reset=1&op=release', '{ts escape="sql"}Release Voters{/ts}', 'Release Voters', 'administer CiviCampaign,manage campaign,release campaign contacts', 'OR', @nav_campaign_id, '1', NULL, 7 ),
 187     ( @domainID, 'civicrm/campaign/gotv&reset=1', '{ts escape="sql"}Voter Listing{/ts}', 'Voter Listing', 'administer CiviCampaign,manage campaign', 'OR', @nav_campaign_id, '1', NULL, 8 ),
 188     ( @domainID, 'civicrm/campaign/vote&reset=1', '{ts escape="sql"}Conduct Survey{/ts}', 'Conduct Survey', 'administer CiviCampaign,manage campaign,reserve campaign contacts,interview campaign contacts', 'OR', @nav_campaign_id, '1', NULL, 9 );
 189
 190 --
 191 --Done w/ campaign db upgrade.
 192 --
 193
 194 -- CRM-6208
 195 insert into civicrm_option_group (name, is_active) values ('system_extensions', 1 );
 196
 197 -- CRM-6907
 198   ALTER TABLE  `civicrm_event`
 199           ADD  `currency` VARCHAR( 3 )
 200 CHARACTER SET  utf8 COLLATE utf8_unicode_ci NULL
 201       COMMENT  '3 character string, value from config setting or input via user.';
 202
 203       UPDATE   `civicrm_event` SET `currency` = '{$config->defaultCurrency}';
 204
 205   ALTER TABLE  `civicrm_contribution_page`
 206           ADD  `currency` VARCHAR( 3 )
 207 CHARACTER SET  utf8 COLLATE utf8_unicode_ci NOT NULL
 208    COMMENT '3  character string, value from config setting or input via user.';
 209
 210       UPDATE   `civicrm_contribution_page` SET `currency` = '{$config->defaultCurrency}';
 211
 212 -- CRM-6914
 213 ALTER TABLE civicrm_option_value MODIFY COLUMN value varchar(512);
 214
 215 INSERT INTO civicrm_option_group
 216          (`name`, {localize field='description'}description{/localize}, `is_active`)
 217 VALUES
 218   ('directory_preferences', {localize}'Directory Preferences'{/localize}     , 1 ),
 219      ('url_preferences'      , {localize}'URL Preferences'{/localize}   , 1 );
 220
 221 --insert values for Directory and URL preferences
 222
 223 SELECT @option_group_id_dirPref := max(id) from civicrm_option_group where name = 'directory_preferences';
 224 SELECT @option_group_id_urlPref := max(id) from civicrm_option_group where name = 'url_preferences';
 225
 226 INSERT INTO
 227    `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `name`, `value`, `weight`, `is_active`, `domain_id` )
 228 VALUES
 229   (@option_group_id_dirPref, {localize}'Temporary Files'{/localize}  , 'uploadDir'          , '', 1, 1, @domainID ),
 230   (@option_group_id_dirPref, {localize}'Images'{/localize}           , 'imageUploadDir'     , '', 2, 1, @domainID ),
 231   (@option_group_id_dirPref, {localize}'Custom Files'{/localize}     , 'customFileUploadDir', '', 3, 1, @domainID ),
 232   (@option_group_id_dirPref, {localize}'Custom Templates'{/localize} , 'customTemplateDir'  , '', 4, 1, @domainID ),
 233   (@option_group_id_dirPref, {localize}'Custom PHP'{/localize}       , 'customPHPPathDir'   , '', 5, 1, @domainID ),
 234   (@option_group_id_dirPref, {localize}'Custom Extensions'{/localize}, 'extensionsDir'      , '', 6, 1, @domainID ),
 235
 236   (@option_group_id_urlPref, {localize}'CiviCRM Resource URL'{/localize}  , 'userFrameworkResourceURL', '', 1, 1, @domainID ),
 237   (@option_group_id_urlPref, {localize}'Image Upload URL'{/localize}      , 'imageUploadURL'          , '', 2, 1, @domainID ),
 238   (@option_group_id_urlPref, {localize}'Custom CiviCRM CSS URL'{/localize}, 'customCSSURL'            , '', 3, 1, @domainID );
 239
 240
 241 -- CRM-6835
 242 ALTER TABLE civicrm_mailing_job ADD COLUMN `job_type` varchar(255) default NULL;
 243 ALTER TABLE civicrm_mailing_job ADD COLUMN `parent_id`  int(10)unsigned default NULL;
 244 ALTER TABLE civicrm_mailing_job ADD COLUMN `job_offset` int(20) default 0;
 245 ALTER TABLE civicrm_mailing_job ADD COLUMN `job_limit` int(20) default 0;
 246 ALTER TABLE civicrm_mailing_job ADD CONSTRAINT parent_id FOREIGN KEY (parent_id) REFERENCES civicrm_mailing_job (id);
 247
 248 -- CRM-6931
 249 SELECT @ogrID       := max(id) from civicrm_option_group where name = 'report_template';
 250 SELECT @max_weight  := max(ROUND(weight)) from civicrm_option_value WHERE option_group_id = @ogrID;
 251 SELECT @caseCompId  := max(id) FROM civicrm_component where name = 'CiviCase';
 252
 253 INSERT INTO civicrm_option_value
 254   (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 )
 255 VALUES
 256   (@ogrID, {localize}'{ts escape="sql"}Case Detail Report{/ts}'{/localize}, 'case/detail', 'CRM_Report_Form_Case_Detail', NULL, 0, 0, @max_weight+1, {localize}'{ts escape="sql"}Case Details{/ts}'{/localize}, 0, 0, 1, @caseCompId, NULL);
 257
 258 -- CRM-5718
 259 UPDATE civicrm_contribution_widget
 260    SET color_title         = CONCAT( '#', SUBSTRING( color_title, 3 ) ),
 261        color_button        = CONCAT( '#', SUBSTRING( color_button, 3 ) ),
 262        color_bar           = CONCAT( '#', SUBSTRING( color_bar, 3 ) ),
 263        color_main_text     = CONCAT( '#', SUBSTRING( color_main_text, 3 ) ),
 264        color_main          = CONCAT( '#', SUBSTRING( color_main, 3 ) ),
 265        color_main_bg       = CONCAT( '#', SUBSTRING( color_main_bg, 3 ) ),
 266        color_bg            = CONCAT( '#', SUBSTRING( color_bg, 3 ) ),
 267        color_about_link    = CONCAT( '#', SUBSTRING( color_about_link, 3 ) ),
 268        color_homepage_link = CONCAT( '#', SUBSTRING( color_homepage_link, 3 ) );
 269
 270
 271 --CRM-4572
 272
 273 ALTER TABLE civicrm_address ADD COLUMN master_id INT(10) unsigned default NULL COMMENT 'FK to Address ID';
 274 ALTER TABLE civicrm_address ADD CONSTRAINT FK_civicrm_address_master_id  FOREIGN KEY (master_id) REFERENCES civicrm_address (id) ON DELETE SET NULL;
 275
 276 UPDATE civicrm_address add1
 277 INNER JOIN civicrm_contact c1 ON ( c1.id = add1.contact_id AND c1.mail_to_household_id IS NOT NULL )
 278 INNER JOIN civicrm_address add2 ON ( c1.mail_to_household_id = add2.contact_id AND add2.is_primary = 1 )
 279 SET add1.master_id = add2.id;
 280
 281 UPDATE civicrm_contact SET mail_to_household_id = NULL;
 282
 283 ALTER TABLE civicrm_contact DROP  FOREIGN KEY FK_civicrm_contact_mail_to_household_id;
 284 ALTER TABLE civicrm_contact DROP mail_to_household_id;
 285
 286 -- added shared address profile.
 287 INSERT INTO civicrm_uf_group
 288     (name, group_type, {localize field='title'}title{/localize}, is_reserved ) VALUES
 289     ('shared_address', 'Contact',  {localize}'Shared Address'{/localize}, 1 );
 290
 291 SELECT @uf_group_id_sharedAddress   := max(id) from civicrm_uf_group where name = 'shared_address';
 292
 293 INSERT INTO civicrm_uf_join
 294    (is_active,module,entity_table,entity_id,weight,uf_group_id) VALUES
 295    (1, 'Profile', NULL, NULL, 7, @uf_group_id_sharedAddress );
 296
 297 INSERT INTO civicrm_uf_field
 298    (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, {localize field='help_post'}help_post{/localize}, phone_type_id ) VALUES
 299    (@uf_group_id_sharedAddress, 'street_address',  0, 0, 1, 'User and User Admin Only',  0, 0, 1, {localize}'Street Address (Home)'{/localize},     'Contact',     {localize}NULL{/localize},  NULL),
 300    (@uf_group_id_sharedAddress, 'city',            0, 0, 2, 'User and User Admin Only',  0, 0, 1, {localize}'City (Home)'{/localize},        'Contact',     {localize}NULL{/localize},  NULL),
 301    (@uf_group_id_sharedAddress, 'postal_code',     0, 0, 3, 'User and User Admin Only',  0, 0, 1, {localize}'Postal Code (Home)'{/localize}, 'Contact',     {localize}NULL{/localize},  NULL),
 302    (@uf_group_id_sharedAddress, 'country',         0, 0, 4, 'Public Pages and Listings', 0, 1, 1, {localize}'Country (Home)'{/localize},     'Contact',     {localize}NULL{/localize},  NULL),
 303    (@uf_group_id_sharedAddress, 'state_province',  0, 0, 5, 'Public Pages and Listings', 1, 1, 1, {localize}'State (Home)'{/localize},       'Contact',     {localize}NULL{/localize},  NULL);
 304
 305 -- CRM-6894
 306 CREATE TABLE `civicrm_batch` (
 307   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Address ID.',
 308   `name` varchar(64) DEFAULT NULL COMMENT 'Variable name/programmatic handle for this batch.',
 309   `label` varchar(64) DEFAULT NULL COMMENT 'Friendly Name.',
 310   `description` text COMMENT 'Description of this batch set.',
 311   `created_id` int(10) unsigned default NULL COMMENT 'FK to Contact ID',
 312   `created_date` datetime default NULL COMMENT 'When was this item created',
 313   `modified_id` int(10) unsigned default NULL COMMENT 'FK to Contact ID',
 314   `modified_date` datetime default NULL COMMENT 'When was this item created',
 315   PRIMARY KEY ( `id` ),
 316   CONSTRAINT FK_civicrm_batch_created_id FOREIGN KEY ( created_id ) REFERENCES civicrm_contact( id ) ON DELETE SET NULL,
 317   CONSTRAINT FK_civicrm_batch_modified_id FOREIGN KEY ( modified_id ) REFERENCES civicrm_contact( id ) ON DELETE SET NULL
 318 )ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 319
 320
 321 CREATE TABLE `civicrm_entity_batch` (
 322   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
 323   `entity_table` varchar(64) DEFAULT NULL COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
 324   `entity_id` int(10) unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
 325   `batch_id` int(10) unsigned NOT NULL COMMENT 'FK to civicrm_batch',
 326   PRIMARY KEY ( id ),
 327   INDEX index_entity ( entity_table, entity_id ),
 328   UNIQUE INDEX UI_batch_entity ( batch_id, entity_id, entity_table ),
 329   CONSTRAINT FK_civicrm_entity_batch_batch_id FOREIGN KEY ( batch_id ) REFERENCES civicrm_batch ( id ) ON DELETE CASCADE
 330  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 331
 332 -- CRM-3702
 333 CREATE TABLE `civicrm_dedupe_exception` (
 334   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique dedupe exception id.',
 335   `contact_id1` int(10) unsigned default NULL COMMENT 'FK to Contact ID',
 336   `contact_id2` int(10) unsigned default NULL COMMENT 'FK to Contact ID',
 337   PRIMARY KEY ( id ),
 338   UNIQUE INDEX UI_contact_id1_contact_id2 (`contact_id1`, `contact_id2`),
 339   CONSTRAINT FK_civicrm_dedupe_exception_contact_id1 FOREIGN KEY (`contact_id1`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,
 340   CONSTRAINT FK_civicrm_dedupe_exception_contact_id2 FOREIGN KEY (`contact_id2`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
 341 )ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 342