Restructure activtiy contact target & assignee into activity_contact table for better queries & tidier code

• Is MIH?:

  No
• Code Sprint:

  No

Branch svn.civicrm.org/civicrm/branches/activitycontact/

SQL will look something like this


Rationalise civicrm_activity_assignee & civicrm_activity_target for query perfomance / code simplicity


Just noting SQL


CREATE TABLE `civicrm_activity_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Activity contact id',
  `activity_id` int(10) unsigned NOT NULL COMMENT 'Foreign key to the activity for this record',
  `contact_id` int(10) unsigned NOT NULL COMMENT 'Foreign key to the contact for this record.',
  `type` ENUM('source','target','assignee') NOT NULL COMMENT 'type of contact relationship'
  PRIMARY KEY (`id`),
  UNIQUE KEY `UI_activity_assignee_contact_id` (`assignee_contact_id`,`activity_id`),
  KEY `FK_civicrm_activity_assignment_activity_id` (`activity_id`),
  CONSTRAINT `FK_civicrm_activity_assignment_activity_id` FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_civicrm_activity_assignment_assignee_contact_id` FOREIGN KEY (`assignee_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT
INTO civicrm_activity_contact (activity_id,contact_id, activity_contact_type)
SELECT activity_id, assignee_contact_id, 'assignee' as activity_contact_type
FROM civicrm_activity_assignment;

INSERT
INTO civicrm_activity_contact (activity_id,contact_id, activity_contact_type)
SELECT activity_id, target_contact_id, 'target' as activity_contact_type
FROM civicrm_activity_target;

INSERT
INTO civicrm_activity_contact (activity_id,contact_id, activity_contact_type)

SELECT id as activity_id, source_contact_id, 'source' as activity_contact_type
FROM civicrm_activity;
ALTER TABLE `civicrm_activity`
DROP INDEX `FK_civicrm_activity_source_contact_id`,
DROP FOREIGN KEY `FK_civicrm_activity_source_contact_id`;

ALTER TABLE `civicrm_activity` DROP COLUMN `source_contact_id`;
DROP table civicrm_activity_target;
DROP table civicrm_activity_assignee;

All Comments Work Log History Activity Commits Source Reviews

Vote (0)

Watch (0)