-- *
-- *******************************************************/
-INSERT INTO
- `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
-VALUES
+INSERT INTO
+ `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
+VALUES
(@domain_id, 'activity_type' , 'Activity Type' , 0, 1),
(@domain_id, 'gender' , 'Gender' , 0, 1),
(@domain_id, 'instant_messenger_service' , 'Instant Messenger (IM) screen-names', 0, 1),
SELECT @option_group_id_pi := max(id) from civicrm_option_group where name = 'payment_instrument';
SELECT @option_group_id_cs := max(id) from civicrm_option_group where name = 'contribution_status';
-INSERT INTO
- `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
+INSERT INTO
+ `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
VALUES
(@option_group_id_act, 'Meeting', 1, 'Meeting',NULL, 0, NULL, 1, 'Schedule a meeting', 0, 0, 1),
(@option_group_id_act, 'Phone Call', 2, 'Phone Call', NULL, 0, NULL, 2, 'Schedule a Phone Call', 0, 0, 1),
DROP TABLE IF EXISTS civicrm_accept_credit_card;
DROP TABLE IF EXISTS civicrm_activity_type;
-DROP TABLE IF EXISTS civicrm_acl_group;
+DROP TABLE IF EXISTS civicrm_acl_group;
DROP TABLE IF EXISTS civicrm_gender;
DROP TABLE IF EXISTS civicrm_individual_prefix;
DROP TABLE IF EXISTS civicrm_individual_suffix;
id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
contact_id int unsigned COMMENT 'Foreign Key to Contact',
acl_id int unsigned NOT NULL COMMENT 'Foreign Key to ACL',
- modified_date date COMMENT 'When was this cache entry last modified'
+ modified_date date COMMENT 'When was this cache entry last modified'
,
PRIMARY KEY ( id )
,
INDEX contact_id (contact_id)
,
FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
-,
+,
FOREIGN KEY (acl_id) REFERENCES civicrm_acl(id)
-
+
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
acl_role_id int unsigned NOT NULL COMMENT 'Foreign Key to ACL Role (which is an option value pair and hence an implicit FK)',
entity_table varchar(64) NOT NULL COMMENT 'Table of the object joined to the ACL Role (Contact or Group)',
entity_id int unsigned NOT NULL COMMENT 'ID of the group/contact object being joined',
- is_active tinyint COMMENT 'Is this property active?'
+ is_active tinyint COMMENT 'Is this property active?'
,
PRIMARY KEY ( id )
-
+
, INDEX index_role(
acl_role_id
)
, INDEX domain_id (
domain_id
)
- ,
+ ,
FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
-
+
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
msg_subject varchar(255) COMMENT 'Subject for email message.',
msg_text text COMMENT 'Text formatted message',
msg_html text COMMENT 'HTML formatted message',
- is_active tinyint DEFAULT 1
+ is_active tinyint DEFAULT 1
,
PRIMARY KEY ( id )
-
-
-,
+
+
+,
FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
-
+
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
ALTER TABLE `civicrm_acl` ADD domain_id int(10) unsigned NOT NULL COMMENT 'Which Domain owns this contact';
ALTER TABLE `civicrm_acl` ADD FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`);
- ALTER TABLE `civicrm_acl` ADD INDEX `domain_id` (`domain_id`);
+ ALTER TABLE `civicrm_acl` ADD INDEX `domain_id` (`domain_id`);
ALTER TABLE `civicrm_acl` ADD name varchar(64) COMMENT 'ACL Name.';
ALTER TABLE `civicrm_acl` CHANGE `operation` `operation` ENUM('All', 'View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke') NOT NULL COMMENT 'What operation does this ACL entry control?';
ALTER TABLE `civicrm_acl` ADD is_active tinyint(4) COMMENT 'Is this property active?' AFTER acl_id;
-
+
ALTER TABLE `civicrm_acl` ADD INDEX index_acl_id (acl_id);
-- /*******************************************************
-- * Join table for activities
-- *
-- *******************************************************/
-
+
ALTER TABLE `civicrm_activity` DROP FOREIGN KEY civicrm_activity_ibfk_2;
ALTER TABLE `civicrm_activity` ADD INDEX UI_activity_type_id (`activity_type_id`);
ALTER TABLE civicrm_contact ADD INDEX index_contact_type_domain (contact_type, domain_id);
ALTER TABLE civicrm_contact ADD INDEX index_contact_sub_type_domain (contact_sub_type, domain_id);
-
+
ALTER TABLE civicrm_contact ADD INDEX index_preferred_communication_method (preferred_communication_method);
-
- UPDATE civicrm_contact
- SET `preferred_communication_method` = CONCAT(char(1),`preferred_communication_method`,char(1))
+
+ UPDATE civicrm_contact
+ SET `preferred_communication_method` = CONCAT(char(1),`preferred_communication_method`,char(1))
WHERE civicrm_contact.preferred_communication_method IS NOT NULL;
-
+
-- /*******************************************************
-- *
-- * civicrm_contribution
ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_6`;
ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_7`;
-
+
ALTER TABLE `civicrm_contribution` DROP `recur_contribution_id`;
-
+
ALTER TABLE `civicrm_contribution` ADD `honor_contact_id` int unsigned COMMENT 'FK to contact ID';
ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`);
-
+
ALTER TABLE `civicrm_contribution` ADD contribution_recur_id int unsigned COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.' AFTER note;
ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`contribution_recur_id`) REFERENCES civicrm_contribution_recur(`id`);
-
+
ALTER TABLE `civicrm_contribution` ADD is_test tinyint DEFAULT 0;
ALTER TABLE `civicrm_contribution` ADD `contribution_status_id` int unsigned NULL DEFAULT '1';
ALTER TABLE `civicrm_contribution` ADD INDEX honor_contact_id (`honor_contact_id`);
ALTER TABLE `civicrm_contribution` ADD INDEX UI_contrib_payment_instrument_id (payment_instrument_id);
ALTER TABLE `civicrm_contribution` ADD INDEX index_contribution_status (contribution_status_id);
-
+
-- /*******************************************************
-- *
-- * civicrm_contribution_page
-- *
-- *******************************************************/
ALTER TABLE civicrm_contribution_recur DROP is_active;
-
+
ALTER TABLE civicrm_contribution_recur ADD domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contribution class.' AFTER id;
ALTER TABLE civicrm_contribution_recur ADD trxn_id varchar(255) COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method';
ALTER TABLE civicrm_contribution_recur ADD invoice_id varchar(255) COMMENT 'unique invoice id, system generated or passed in' AFTER trxn_id;
ALTER TABLE civicrm_contribution_recur ADD contribution_status_id int unsigned NULL DEFAULT '1' AFTER invoice_id;
ALTER TABLE civicrm_contribution_recur ADD end_date datetime NOT NULL COMMENT 'Date this recurring contribution finished successfully' AFTER cancel_date;
ALTER TABLE civicrm_contribution_recur ADD is_test tinyint NULL DEFAULT '0'AFTER contribution_status_id ;
-
+
ALTER TABLE civicrm_contribution_recur MODIFY start_date datetime NOT NULL COMMENT 'The date the first scheduled recurring contribution occurs.';
ALTER TABLE civicrm_contribution_recur MODIFY create_date datetime NOT NULL COMMENT 'When this recurring contribution record was created.';
ALTER TABLE civicrm_contribution_recur MODIFY modified_date datetime NOT NULL COMMENT 'Last updated date for this record. mostly the last time a payment was received';
ALTER TABLE civicrm_contribution_recur MODIFY cancel_date datetime NOT NULL COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it';
ALTER TABLE civicrm_contribution_recur MODIFY next_sched_contribution datetime NOT NULL COMMENT 'At Groundspring this was used by the cron job which triggered payments. If we\'re not doing that but we know about payments, it might still be useful to store for display to org andor contributors.';
ALTER TABLE civicrm_contribution_recur MODIFY failure_retry_date datetime COMMENT 'At Groundspring we set a business rule to retry failed payments every 7 days - and stored the next scheduled attempt date there.';
-
- ALTER TABLE civicrm_contribution_recur ADD INDEX index_contribution_status( contribution_status_id );
-
+
+ ALTER TABLE civicrm_contribution_recur ADD INDEX index_contribution_status( contribution_status_id );
+
ALTER TABLE civicrm_contribution_recur ADD FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id);
-
+
-- /*******************************************************
-- *
-- * civicrm_custom_group
-- * All extended (custom) properties are associated with a group. These are logical sets of related data.
-- *
-- *******************************************************/
-
+
ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_name varchar(64) COMMENT 'linking custom group for dynamic object';
ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_value varchar(64) COMMENT 'linking custom group for dynamic object';
ALTER TABLE `civicrm_custom_group` MODIFY extends enum('Contact','Individual','Household','Organization','Location','Address','Contribution','Activity','Relationship','Phonecall','Meeting','Group','Membership') NULL DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).';
-
+
UPDATE civicrm_custom_group
SET extends_entity_column_value=NULL
WHERE extends='Activity';
-
+
UPDATE civicrm_custom_group
SET extends='Activity', extends_entity_column_value='2'
WHERE extends='Phonecall';
-
+
UPDATE civicrm_custom_group
SET extends='Activity', extends_entity_column_value='1'
WHERE extends='Meeting';
-
+
UPDATE civicrm_custom_group
SET extends_entity_column_value=NULL
WHERE extends='Contact';
-
+
UPDATE civicrm_custom_group
SET extends_entity_column_value=NULL
WHERE extends='Relationship';
-
+
-- /*******************************************************
-- *
-- * civicrm_custom_value
-- * Data store for each extended properties.
-- *
-- *******************************************************/
-
+
ALTER TABLE civicrm_custom_value ADD INDEX index_int_field (int_data, custom_field_id);
ALTER TABLE civicrm_custom_value ADD INDEX index_float_field (float_data, custom_field_id);
ALTER TABLE civicrm_custom_value ADD INDEX index_decimal_field (decimal_data, custom_field_id);
ALTER TABLE civicrm_custom_value ADD INDEX index_char_field (char_data, custom_field_id);
ALTER TABLE civicrm_custom_value ADD INDEX index_date_field (date_data, custom_field_id);
-
+
-- /*******************************************************
-- *
-- * civicrm_domain
-- * Data store for uploaded (attached) files (pointer to file on disk OR blob). Maybe be joined to entities via custom_value.file_id or entity_file table.
-- *
-- *******************************************************/
-
+
ALTER TABLE `civicrm_file` DROP FOREIGN KEY `civicrm_file_ibfk_1`;
ALTER TABLE `civicrm_file` DROP `file_type_id`;
- ALTER TABLE `civicrm_file` ADD file_type_id int unsigned COMMENT 'Type of file (e.g. Transcript, Income Tax Return, etc). FK to civicrm_option_value.';
-
+ ALTER TABLE `civicrm_file` ADD file_type_id int unsigned COMMENT 'Type of file (e.g. Transcript, Income Tax Return, etc). FK to civicrm_option_value.';
+
-- /*******************************************************
-- *
-- * civicrm_group
-- * Define contact-individual specific properties. Extends civicrm_contact.
-- *
-- *******************************************************/
-
+
ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_1;
ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_2;
ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_3;
-- * Store field mappings in import or export for reuse
-- *
-- *******************************************************/
-
+
ALTER TABLE `civicrm_mapping` MODIFY mapping_type enum('Export','Import','Export Contributions','Import Contributions','Import Activity History','Search Builder','Import Memberships') COMMENT 'Type of Mapping.';
-- /*******************************************************
-- * Contact Membership records.
-- *
-- *******************************************************/
-
+
ALTER TABLE `civicrm_membership` ADD reminder_date date COMMENT 'When should a reminder be sent.' AFTER is_override;
-- /*******************************************************
-- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
-- *
-- *******************************************************/
-
+
ALTER TABLE civicrm_membership_log ADD renewal_reminder_date date COMMENT 'The day we sent a renewal reminder' AFTER modified_date;
-- /*******************************************************
-- * Sites can configure multiple types of memberships. They encode the owner organization, fee, and the rules needed to set start and end (expire) dates when a member signs up for that type.
-- *
-- *******************************************************/
-
+
ALTER TABLE civicrm_membership_type ADD renewal_msg_id int unsigned COMMENT 'FK to civicrm_msg_template.id' AFTER weight;
ALTER TABLE civicrm_membership_type ADD renewal_reminder_day int COMMENT 'Number of days prior to expiration to send renewal reminder' AFTER renewal_msg_id;
-
+
ALTER TABLE civicrm_membership_type ADD FOREIGN KEY (renewal_msg_id) REFERENCES civicrm_msg_template(id);
-
+
-- /*******************************************************
-- *
-- * civicrm_organization
-- * Define organization specific properties
-- *
-- *******************************************************/
-
+
ALTER TABLE civicrm_organization DROP FOREIGN KEY civicrm_organization_ibfk_1;
-
+
-- /*******************************************************
-- *
-- * civicrm_phone
ALTER TABLE `civicrm_saved_search` ADD select_tables text DEFAULT NULL COMMENT 'the tables to be included in a select data';
- ALTER TABLE `civicrm_saved_search` ADD where_tables text DEFAULT NULL COMMENT 'the tables to be included in the count statement';
+ ALTER TABLE `civicrm_saved_search` ADD where_tables text DEFAULT NULL COMMENT 'the tables to be included in the count statement';
-- /*******************************************************
-- *
ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_3;
ALTER TABLE `civicrm_task` DROP `priority_id`;
ALTER TABLE `civicrm_task` ADD priority_id int unsigned COMMENT 'Configurable priority value (e.g. Critical, High, Medium...). FK to civicrm_option_value.';
-
+
-- /*******************************************************
-- *
-- * civicrm_task_status
ALTER TABLE `civicrm_task_status` DROP FOREIGN KEY civicrm_task_status_ibfk_2;
ALTER TABLE `civicrm_task_status` DROP `status_id`;
ALTER TABLE `civicrm_task_status` ADD status_id int unsigned COMMENT 'Configurable status value (e.g. Not Started, In Progress, Completed, Deferred...). FK to civicrm_option_value.';
-
+
-- /*******************************************************
-- *
-- * civicrm_uf_group
-- *******************************************************/
ALTER TABLE `civicrm_uf_group` ADD is_edit_link tinyint NULL DEFAULT '0' COMMENT 'Should edit link display in profile selector';
-
+
-- /*******************************************************
-- *
-- * Add UNIQUE indexes to maintain data integrity