Personal/drupal-civicrm
1
0
Fork 0
Code Issues Pull requests Releases Wiki Activity
drupal-civicrm/sites/all/modules/civicrm/sql/civicrm.mysql

4986 lines
259 KiB
Plaintext
Raw Permalink Normal View History

First commit
2018-01-14 15:10:16 +02:00
-- +--------------------------------------------------------------------+
-- | CiviCRM version 4.7 |
-- +--------------------------------------------------------------------+
-- | Copyright CiviCRM LLC (c) 2004-2017 |
-- +--------------------------------------------------------------------+
-- | This file is a part of CiviCRM. |
-- | |
-- | CiviCRM is free software; you can copy, modify, and distribute it |
-- | under the terms of the GNU Affero General Public License |
-- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
-- | |
-- | CiviCRM is distributed in the hope that it will be useful, but |
-- | WITHOUT ANY WARRANTY; without even the implied warranty of |
-- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
-- | See the GNU Affero General Public License for more details. |
-- | |
-- | You should have received a copy of the GNU Affero General Public |
-- | License and the CiviCRM Licensing Exception along |
-- | with this program; if not, contact CiviCRM LLC |
-- | at info[AT]civicrm[DOT]org. If you have questions about the |
-- | GNU Affero General Public License or the licensing of CiviCRM, |
-- | see the CiviCRM license FAQ at http://civicrm.org/licensing |
-- +--------------------------------------------------------------------+
--
-- Generated from schema.tpl
-- DO NOT EDIT. Generated by CRM_Core_CodeGen
--
-- +--------------------------------------------------------------------+
-- | CiviCRM version 4.7 |
-- +--------------------------------------------------------------------+
-- | Copyright CiviCRM LLC (c) 2004-2017 |
-- +--------------------------------------------------------------------+
-- | This file is a part of CiviCRM. |
-- | |
-- | CiviCRM is free software; you can copy, modify, and distribute it |
-- | under the terms of the GNU Affero General Public License |
-- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
-- | |
-- | CiviCRM is distributed in the hope that it will be useful, but |
-- | WITHOUT ANY WARRANTY; without even the implied warranty of |
-- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
-- | See the GNU Affero General Public License for more details. |
-- | |
-- | You should have received a copy of the GNU Affero General Public |
-- | License and the CiviCRM Licensing Exception along |
-- | with this program; if not, contact CiviCRM LLC |
-- | at info[AT]civicrm[DOT]org. If you have questions about the |
-- | GNU Affero General Public License or the licensing of CiviCRM, |
-- | see the CiviCRM license FAQ at http://civicrm.org/licensing |
-- +--------------------------------------------------------------------+
--
-- Generated from drop.tpl
-- DO NOT EDIT. Generated by CRM_Core_CodeGen
--
-- /*******************************************************
-- *
-- * Clean up the exisiting tables
-- *
-- *******************************************************/
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `civicrm_line_item`;
DROP TABLE IF EXISTS `civicrm_pledge_payment`;
DROP TABLE IF EXISTS `civicrm_events_in_carts`;
DROP TABLE IF EXISTS `civicrm_participant_payment`;
DROP TABLE IF EXISTS `civicrm_participant`;
DROP TABLE IF EXISTS `civicrm_event`;
DROP TABLE IF EXISTS `civicrm_membership_payment`;
DROP TABLE IF EXISTS `civicrm_entity_financial_trxn`;
DROP TABLE IF EXISTS `civicrm_contribution_soft`;
DROP TABLE IF EXISTS `civicrm_contribution_product`;
DROP TABLE IF EXISTS `civicrm_contribution`;
DROP TABLE IF EXISTS `civicrm_group_contact`;
DROP TABLE IF EXISTS `civicrm_loc_block`;
DROP TABLE IF EXISTS `civicrm_address`;
DROP TABLE IF EXISTS `civicrm_pcp_block`;
DROP TABLE IF EXISTS `civicrm_price_field_value`;
DROP TABLE IF EXISTS `civicrm_price_field`;
DROP TABLE IF EXISTS `civicrm_case_activity`;
DROP TABLE IF EXISTS `civicrm_activity_contact`;
DROP TABLE IF EXISTS `civicrm_activity`;
DROP TABLE IF EXISTS `civicrm_membership_log`;
DROP TABLE IF EXISTS `civicrm_membership`;
DROP TABLE IF EXISTS `civicrm_financial_trxn`;
DROP TABLE IF EXISTS `civicrm_contribution_recur`;
DROP TABLE IF EXISTS `civicrm_mailing_event_unsubscribe`;
DROP TABLE IF EXISTS `civicrm_mailing_event_trackable_url_open`;
DROP TABLE IF EXISTS `civicrm_mailing_event_reply`;
DROP TABLE IF EXISTS `civicrm_mailing_event_opened`;
DROP TABLE IF EXISTS `civicrm_mailing_event_forward`;
DROP TABLE IF EXISTS `civicrm_mailing_event_delivered`;
DROP TABLE IF EXISTS `civicrm_mailing_event_bounce`;
DROP TABLE IF EXISTS `civicrm_mailing_event_queue`;
DROP TABLE IF EXISTS `civicrm_mailing_spool`;
DROP TABLE IF EXISTS `civicrm_mailing_recipients`;
DROP TABLE IF EXISTS `civicrm_mailing_job`;
DROP TABLE IF EXISTS `civicrm_mailing_trackable_url`;
DROP TABLE IF EXISTS `civicrm_mailing_group`;
DROP TABLE IF EXISTS `civicrm_mailing`;
DROP TABLE IF EXISTS `civicrm_relationship`;
DROP TABLE IF EXISTS `civicrm_dashboard_contact`;
DROP TABLE IF EXISTS `civicrm_action_log`;
DROP TABLE IF EXISTS `civicrm_action_schedule`;
DROP TABLE IF EXISTS `civicrm_uf_join`;
DROP TABLE IF EXISTS `civicrm_uf_field`;
DROP TABLE IF EXISTS `civicrm_uf_group`;
DROP TABLE IF EXISTS `civicrm_entity_tag`;
DROP TABLE IF EXISTS `civicrm_discount`;
DROP TABLE IF EXISTS `civicrm_dashboard`;
DROP TABLE IF EXISTS `civicrm_county`;
DROP TABLE IF EXISTS `civicrm_price_set_entity`;
DROP TABLE IF EXISTS `civicrm_price_set`;
DROP TABLE IF EXISTS `civicrm_report_instance`;
DROP TABLE IF EXISTS `civicrm_pledge`;
DROP TABLE IF EXISTS `civicrm_case_contact`;
DROP TABLE IF EXISTS `civicrm_case`;
DROP TABLE IF EXISTS `civicrm_membership_block`;
DROP TABLE IF EXISTS `civicrm_membership_type`;
DROP TABLE IF EXISTS `civicrm_sms_provider`;
DROP TABLE IF EXISTS `civicrm_payment_token`;
DROP TABLE IF EXISTS `civicrm_payment_processor`;
DROP TABLE IF EXISTS `civicrm_contribution_widget`;
DROP TABLE IF EXISTS `civicrm_premiums_product`;
DROP TABLE IF EXISTS `civicrm_product`;
DROP TABLE IF EXISTS `civicrm_contribution_page`;
DROP TABLE IF EXISTS `civicrm_mailing_event_confirm`;
DROP TABLE IF EXISTS `civicrm_mailing_event_subscribe`;
DROP TABLE IF EXISTS `civicrm_group_organization`;
DROP TABLE IF EXISTS `civicrm_group_nesting`;
DROP TABLE IF EXISTS `civicrm_group_contact_cache`;
DROP TABLE IF EXISTS `civicrm_subscription_history`;
DROP TABLE IF EXISTS `civicrm_group`;
DROP TABLE IF EXISTS `civicrm_acl_cache`;
DROP TABLE IF EXISTS `civicrm_status_pref`;
DROP TABLE IF EXISTS `civicrm_word_replacement`;
DROP TABLE IF EXISTS `civicrm_print_label`;
DROP TABLE IF EXISTS `civicrm_setting`;
DROP TABLE IF EXISTS `civicrm_website`;
DROP TABLE IF EXISTS `civicrm_openid`;
DROP TABLE IF EXISTS `civicrm_timezone`;
DROP TABLE IF EXISTS `civicrm_uf_match`;
DROP TABLE IF EXISTS `civicrm_tag`;
DROP TABLE IF EXISTS `civicrm_state_province`;
DROP TABLE IF EXISTS `civicrm_phone`;
DROP TABLE IF EXISTS `civicrm_option_value`;
DROP TABLE IF EXISTS `civicrm_note`;
DROP TABLE IF EXISTS `civicrm_navigation`;
DROP TABLE IF EXISTS `civicrm_menu`;
DROP TABLE IF EXISTS `civicrm_mapping_field`;
DROP TABLE IF EXISTS `civicrm_mail_settings`;
DROP TABLE IF EXISTS `civicrm_log`;
DROP TABLE IF EXISTS `civicrm_job_log`;
DROP TABLE IF EXISTS `civicrm_job`;
DROP TABLE IF EXISTS `civicrm_im`;
DROP TABLE IF EXISTS `civicrm_entity_file`;
DROP TABLE IF EXISTS `civicrm_email`;
DROP TABLE IF EXISTS `civicrm_domain`;
DROP TABLE IF EXISTS `civicrm_custom_field`;
DROP TABLE IF EXISTS `civicrm_custom_group`;
DROP TABLE IF EXISTS `civicrm_country`;
DROP TABLE IF EXISTS `civicrm_cache`;
DROP TABLE IF EXISTS `civicrm_cxn`;
DROP TABLE IF EXISTS `civicrm_pcp`;
DROP TABLE IF EXISTS `civicrm_queue_item`;
DROP TABLE IF EXISTS `civicrm_pledge_block`;
DROP TABLE IF EXISTS `civicrm_tell_friend`;
DROP TABLE IF EXISTS `civicrm_grant`;
DROP TABLE IF EXISTS `civicrm_case_type`;
DROP TABLE IF EXISTS `civicrm_dedupe_exception`;
DROP TABLE IF EXISTS `civicrm_dedupe_rule`;
DROP TABLE IF EXISTS `civicrm_dedupe_rule_group`;
DROP TABLE IF EXISTS `civicrm_event_carts`;
DROP TABLE IF EXISTS `civicrm_participant_status_type`;
DROP TABLE IF EXISTS `civicrm_survey`;
DROP TABLE IF EXISTS `civicrm_campaign_group`;
DROP TABLE IF EXISTS `civicrm_campaign`;
DROP TABLE IF EXISTS `civicrm_membership_status`;
DROP TABLE IF EXISTS `civicrm_financial_item`;
DROP TABLE IF EXISTS `civicrm_entity_financial_account`;
DROP TABLE IF EXISTS `civicrm_financial_type`;
DROP TABLE IF EXISTS `civicrm_payment_processor_type`;
DROP TABLE IF EXISTS `civicrm_financial_account`;
DROP TABLE IF EXISTS `civicrm_currency`;
DROP TABLE IF EXISTS `civicrm_premiums`;
DROP TABLE IF EXISTS `civicrm_mailing_bounce_pattern`;
DROP TABLE IF EXISTS `civicrm_mailing_bounce_type`;
DROP TABLE IF EXISTS `civicrm_mailing_abtest`;
DROP TABLE IF EXISTS `civicrm_mailing_component`;
DROP TABLE IF EXISTS `civicrm_entity_batch`;
DROP TABLE IF EXISTS `civicrm_batch`;
DROP TABLE IF EXISTS `civicrm_contact_type`;
DROP TABLE IF EXISTS `civicrm_saved_search`;
DROP TABLE IF EXISTS `civicrm_relationship_type`;
DROP TABLE IF EXISTS `civicrm_acl_contact_cache`;
DROP TABLE IF EXISTS `civicrm_contact`;
DROP TABLE IF EXISTS `civicrm_acl_entity_role`;
DROP TABLE IF EXISTS `civicrm_acl`;
DROP TABLE IF EXISTS `civicrm_recurring_entity`;
DROP TABLE IF EXISTS `civicrm_action_mapping`;
DROP TABLE IF EXISTS `civicrm_prevnext_cache`;
DROP TABLE IF EXISTS `civicrm_persistent`;
DROP TABLE IF EXISTS `civicrm_component`;
DROP TABLE IF EXISTS `civicrm_worldregion`;
DROP TABLE IF EXISTS `civicrm_system_log`;
DROP TABLE IF EXISTS `civicrm_preferences_date`;
DROP TABLE IF EXISTS `civicrm_option_group`;
DROP TABLE IF EXISTS `civicrm_msg_template`;
DROP TABLE IF EXISTS `civicrm_mapping`;
DROP TABLE IF EXISTS `civicrm_managed`;
DROP TABLE IF EXISTS `civicrm_location_type`;
DROP TABLE IF EXISTS `civicrm_file`;
DROP TABLE IF EXISTS `civicrm_extension`;
DROP TABLE IF EXISTS `civicrm_address_format`;
SET FOREIGN_KEY_CHECKS=1;
-- /*******************************************************
-- *
-- * Create new tables
-- *
-- *******************************************************/
-- /*******************************************************
-- *
-- * civicrm_address_format
-- *
-- *******************************************************/
CREATE TABLE `civicrm_address_format` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Address Format Id',
`format` text COMMENT 'The format of an address'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_extension
-- *
-- *******************************************************/
CREATE TABLE `civicrm_extension` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Local Extension ID',
`type` varchar(8) NOT NULL ,
`full_name` varchar(255) NOT NULL COMMENT 'Fully qualified extension name',
`name` varchar(255) COMMENT 'Short name',
`label` varchar(255) COMMENT 'Short, printable name',
`file` varchar(255) COMMENT 'Primary PHP file',
`schema_version` varchar(63) COMMENT 'Revision code of the database schema; the format is module-defined',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this extension active?'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_extension_full_name`(
full_name
)
, INDEX `UI_extension_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_file
-- *
-- * 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.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_file` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID',
`file_type_id` int unsigned COMMENT 'Type of file (e.g. Transcript, Income Tax Return, etc). FK to civicrm_option_value.',
`mime_type` varchar(255) COMMENT 'mime type of the document',
`uri` varchar(255) COMMENT 'uri of the file on disk',
`document` mediumblob COMMENT 'contents of the document',
`description` varchar(255) COMMENT 'Additional descriptive text regarding this attachment (optional).',
`upload_date` datetime COMMENT 'Date and time that this attachment was uploaded or written to server.'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_location_type
-- *
-- *******************************************************/
CREATE TABLE `civicrm_location_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Location Type ID',
`name` varchar(64) COMMENT 'Location Type Name.',
`display_name` varchar(64) COMMENT 'Location Type Display Name.',
`vcard_name` varchar(64) COMMENT 'vCard Location Type Name.',
`description` varchar(255) COMMENT 'Location Type Description.',
`is_reserved` tinyint COMMENT 'Is this location type a predefined system location?',
`is_active` tinyint COMMENT 'Is this property active?',
`is_default` tinyint COMMENT 'Is this location type the default?'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_managed
-- *
-- * List of declaratively managed objects
-- *
-- *******************************************************/
CREATE TABLE `civicrm_managed` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Surrogate Key',
`module` varchar(127) NOT NULL COMMENT 'Name of the module which declared this object',
`name` varchar(127) COMMENT 'Symbolic name used by the module to identify the object',
`entity_type` varchar(64) NOT NULL COMMENT 'API entity type',
`entity_id` int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
`cleanup` varchar(32) COMMENT 'Policy on when to cleanup entity (always, never, unused)'
,
PRIMARY KEY (`id`)
, INDEX `UI_managed_module_name`(
module
, name
)
, INDEX `UI_managed_entity`(
entity_type
, entity_id
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mapping
-- *
-- * Store field mappings in import or export for reuse
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mapping` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Mapping ID',
`name` varchar(64) COMMENT 'Name of Mapping',
`description` varchar(255) COMMENT 'Description of Mapping.',
`mapping_type_id` int unsigned COMMENT 'Mapping Type'
,
PRIMARY KEY (`id`)
, INDEX `UI_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_msg_template
-- *
-- * Users will need a way to save and retrieve templates with tokens for use in recurring email communication tasks
-- *
-- *******************************************************/
CREATE TABLE `civicrm_msg_template` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Message Template ID',
`msg_title` varchar(255) COMMENT 'Descriptive title of message',
`msg_subject` text COMMENT 'Subject for email message.',
`msg_text` longtext COMMENT 'Text formatted message',
`msg_html` longtext COMMENT 'HTML formatted message',
`is_active` tinyint DEFAULT 1 ,
`workflow_id` int unsigned COMMENT 'a pseudo-FK to civicrm_option_value',
`is_default` tinyint DEFAULT 1 COMMENT 'is this the default message template for the workflow referenced by workflow_id?',
`is_reserved` tinyint COMMENT 'is this the reserved message template which we ship for the workflow referenced by workflow_id?',
`is_sms` tinyint DEFAULT 0 COMMENT 'Is this message template used for sms?',
`pdf_format_id` int unsigned COMMENT 'a pseudo-FK to civicrm_option_value containing PDF Page Format.'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_option_group
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option Group ID',
`name` varchar(64) NOT NULL COMMENT 'Option group name. Used as selection key by class properties which lookup options in civicrm_option_value.',
`title` varchar(255) COMMENT 'Option Group title.',
`description` varchar(255) COMMENT 'Option group description.',
`data_type` varchar(128) COMMENT 'Option group description.',
`is_reserved` tinyint DEFAULT 1 COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?',
`is_active` tinyint COMMENT 'Is this option group active?',
`is_locked` tinyint COMMENT 'A lock to remove the ability to add new options via the UI.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_preferences_date
-- *
-- * Define date preferences for the site
-- *
-- *******************************************************/
CREATE TABLE `civicrm_preferences_date` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar(64) NOT NULL COMMENT 'The meta name for this date (fixed in code)',
`description` varchar(255) COMMENT 'Description of this date type.',
`start` int NOT NULL COMMENT 'The start offset relative to current year',
`end` int NOT NULL COMMENT 'The end offset relative to current year, can be negative',
`date_format` varchar(64) COMMENT 'The date type',
`time_format` varchar(64) COMMENT 'time format'
,
PRIMARY KEY (`id`)
, INDEX `index_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_system_log
-- *
-- *******************************************************/
CREATE TABLE `civicrm_system_log` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
`message` varchar(128) NOT NULL COMMENT 'Standardized message',
`context` longtext COMMENT 'JSON encoded data',
`level` varchar(9) DEFAULT 'info' COMMENT 'error level per PSR3',
`timestamp` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of when event occurred.',
`contact_id` int unsigned COMMENT 'Optional Contact ID that created the log. Not an FK as we keep this regardless',
`hostname` varchar(128) COMMENT 'Optional Name of logging host'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_worldregion
-- *
-- *******************************************************/
CREATE TABLE `civicrm_worldregion` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Country Id',
`name` varchar(128) COMMENT 'Region name to be associated with countries'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_component
-- *
-- *******************************************************/
CREATE TABLE `civicrm_component` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Component ID',
`name` varchar(64) NOT NULL COMMENT 'Name of the component.',
`namespace` varchar(128) COMMENT 'Path to components main directory in a form of a class\n namespace.\n '
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_persistent
-- *
-- * DB Template Customization strings
-- *
-- *******************************************************/
CREATE TABLE `civicrm_persistent` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Persistent Record Id',
`context` varchar(255) NOT NULL COMMENT 'Context for which name data pair is to be stored',
`name` varchar(255) NOT NULL COMMENT 'Name of Context',
`data` longtext COMMENT 'data associated with name',
`is_config` tinyint NOT NULL DEFAULT 0 COMMENT 'Config Settings'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_prevnext_cache
-- *
-- * Table to cache items for navigation on civicrm searched results.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_prevnext_cache` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`entity_table` varchar(64) COMMENT 'physical tablename for entity being joined to discount, e.g. civicrm_event',
`entity_id1` int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
`entity_id2` int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
`cacheKey` varchar(255) COMMENT 'Unique path name for cache element of the searched item',
`data` longtext COMMENT 'cached snapshot of the serialized data',
`is_selected` tinyint DEFAULT 0
,
PRIMARY KEY (`id`)
, INDEX `index_all`(
cacheKey
, entity_id1
, entity_id2
, entity_table
, is_selected
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_action_mapping
-- *
-- * Table to store mapping for the reminder.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_action_mapping` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`entity` varchar(64) COMMENT 'Entity for which the reminder is created',
`entity_value` varchar(64) COMMENT 'Entity value',
`entity_value_label` varchar(64) COMMENT 'Entity value label',
`entity_status` varchar(64) COMMENT 'Entity status',
`entity_status_label` varchar(64) COMMENT 'Entity status label',
`entity_date_start` varchar(64) COMMENT 'Entity date',
`entity_date_end` varchar(64) COMMENT 'Entity date',
`entity_recipient` varchar(64) COMMENT 'Entity recipient'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_recurring_entity
-- *
-- *******************************************************/
CREATE TABLE `civicrm_recurring_entity` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`parent_id` int unsigned NOT NULL COMMENT 'Recurring Entity Parent ID',
`entity_id` int unsigned COMMENT 'Recurring Entity Child ID',
`entity_table` varchar(64) NOT NULL COMMENT 'Physical tablename for entity, e.g. civicrm_event',
`mode` tinyint NOT NULL DEFAULT 1 COMMENT '1-this entity, 2-this and the following entities, 3-all the entities'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_acl
-- *
-- * Access Control List
-- *
-- *******************************************************/
CREATE TABLE `civicrm_acl` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
`name` varchar(64) COMMENT 'ACL Name.',
`deny` tinyint NOT NULL DEFAULT 0 COMMENT 'Is this ACL entry Allow (0) or Deny (1) ?',
`entity_table` varchar(64) NOT NULL COMMENT 'Table of the object possessing this ACL entry (Contact, Group, or ACL Group)',
`entity_id` int unsigned COMMENT 'ID of the object possessing this ACL',
`operation` varchar(8) NOT NULL COMMENT 'What operation does this ACL entry control?',
`object_table` varchar(64) COMMENT 'The table of the object controlled by this ACL entry',
`object_id` int unsigned COMMENT 'The ID of the object controlled by this ACL entry',
`acl_table` varchar(64) COMMENT 'If this is a grant/revoke entry, what table are we granting?',
`acl_id` int unsigned COMMENT 'ID of the ACL or ACL group being granted/revoked',
`is_active` tinyint COMMENT 'Is this property active?'
,
PRIMARY KEY (`id`)
, INDEX `index_acl_id`(
acl_id
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_acl_entity_role
-- *
-- * Join table for Contacts and Groups to ACL Roles
-- *
-- *******************************************************/
CREATE TABLE `civicrm_acl_entity_role` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
`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?'
,
PRIMARY KEY (`id`)
, INDEX `index_role`(
acl_role_id
)
, INDEX `index_entity`(
entity_table
, entity_id
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contact
-- *
-- * Contact objects are defined by a civicrm_contact record plus a related civicrm_contact_type record.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contact` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Contact ID',
`contact_type` varchar(64) COMMENT 'Type of Contact.',
`contact_sub_type` varchar(255) COMMENT 'May be used to over-ride contact view and edit templates.',
`do_not_email` tinyint DEFAULT 0 ,
`do_not_phone` tinyint DEFAULT 0 ,
`do_not_mail` tinyint DEFAULT 0 ,
`do_not_sms` tinyint DEFAULT 0 ,
`do_not_trade` tinyint DEFAULT 0 ,
`is_opt_out` tinyint NOT NULL DEFAULT 0 COMMENT 'Has the contact opted out from receiving all bulk email from the organization or site domain?',
`legal_identifier` varchar(32) COMMENT 'May be used for SSN, EIN/TIN, Household ID (census) or other applicable unique legal/government ID.\n ',
`external_identifier` varchar(64) COMMENT 'Unique trusted external ID (generally from a legacy app/datasource). Particularly useful for deduping operations.',
`sort_name` varchar(128) COMMENT 'Name used for sorting different contact types',
`display_name` varchar(128) COMMENT 'Formatted name representing preferred format for display/print/other output.',
`nick_name` varchar(128) COMMENT 'Nickname.',
`legal_name` varchar(128) COMMENT 'Legal Name.',
`image_URL` text COMMENT 'optional URL for preferred image (photo, logo, etc.) to display for this contact.',
`preferred_communication_method` varchar(255) COMMENT 'What is the preferred mode of communication.',
`preferred_language` varchar(5) COMMENT 'Which language is preferred for communication. FK to languages in civicrm_option_value.',
`preferred_mail_format` varchar(8) DEFAULT "Both" COMMENT 'What is the preferred mode of sending an email.',
`hash` varchar(32) COMMENT 'Key for validating requests related to this contact.',
`api_key` varchar(32) COMMENT 'API Key for validating requests related to this contact.',
`source` varchar(255) COMMENT 'where contact come from, e.g. import, donate module insert...',
`first_name` varchar(64) COMMENT 'First Name.',
`middle_name` varchar(64) COMMENT 'Middle Name.',
`last_name` varchar(64) COMMENT 'Last Name.',
`prefix_id` int unsigned COMMENT 'Prefix or Title for name (Ms, Mr...). FK to prefix ID',
`suffix_id` int unsigned COMMENT 'Suffix for name (Jr, Sr...). FK to suffix ID',
`formal_title` varchar(64) COMMENT 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)',
`communication_style_id` int unsigned COMMENT 'Communication style (e.g. formal vs. familiar) to use with this contact. FK to communication styles in civicrm_option_value.',
`email_greeting_id` int unsigned COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Email Greeting.',
`email_greeting_custom` varchar(128) COMMENT 'Custom Email Greeting.',
`email_greeting_display` varchar(255) COMMENT 'Cache Email Greeting.',
`postal_greeting_id` int unsigned COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Postal Greeting.',
`postal_greeting_custom` varchar(128) COMMENT 'Custom Postal greeting.',
`postal_greeting_display` varchar(255) COMMENT 'Cache Postal greeting.',
`addressee_id` int unsigned COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Addressee.',
`addressee_custom` varchar(128) COMMENT 'Custom Addressee.',
`addressee_display` varchar(255) COMMENT 'Cache Addressee.',
`job_title` varchar(255) COMMENT 'Job Title',
`gender_id` int unsigned COMMENT 'FK to gender ID',
`birth_date` date COMMENT 'Date of birth',
`is_deceased` tinyint DEFAULT 0 ,
`deceased_date` date COMMENT 'Date of deceased',
`household_name` varchar(128) COMMENT 'Household Name.',
`primary_contact_id` int unsigned COMMENT 'Optional FK to Primary Contact for this household.',
`organization_name` varchar(128) COMMENT 'Organization Name.',
`sic_code` varchar(8) COMMENT 'Standard Industry Classification Code.',
`user_unique_id` varchar(255) COMMENT 'the OpenID (or OpenID-style http://username.domain/) unique identifier for this contact mainly used for logging in to CiviCRM',
`employer_id` int unsigned COMMENT 'OPTIONAL FK to civicrm_contact record.',
`is_deleted` tinyint NOT NULL DEFAULT 0 ,
`created_date` timestamp NULL DEFAULT NULL COMMENT 'When was the contact was created.',
`modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the contact (or closely related entity) was created or modified or deleted.'
,
PRIMARY KEY (`id`)
, INDEX `index_contact_type`(
contact_type
)
, INDEX `index_contact_sub_type`(
contact_sub_type
)
, UNIQUE INDEX `UI_external_identifier`(
external_identifier
)
, INDEX `index_sort_name`(
sort_name
)
, INDEX `index_preferred_communication_method`(
preferred_communication_method
)
, INDEX `index_hash`(
hash
)
, INDEX `index_api_key`(
api_key
)
, INDEX `index_first_name`(
first_name
)
, INDEX `index_last_name`(
last_name
)
, INDEX `UI_prefix`(
prefix_id
)
, INDEX `UI_suffix`(
suffix_id
)
, INDEX `index_communication_style_id`(
communication_style_id
)
, INDEX `UI_gender`(
gender_id
)
, INDEX `index_is_deceased`(
is_deceased
)
, INDEX `index_household_name`(
household_name
)
, INDEX `index_organization_name`(
organization_name
)
, INDEX `index_is_deleted_sort_name`(
is_deleted
, sort_name
, id
)
, CONSTRAINT FK_civicrm_contact_primary_contact_id FOREIGN KEY (`primary_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contact_employer_id FOREIGN KEY (`employer_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_acl_contact_cache
-- *
-- * Join table cache for contacts that a user has permission on.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_acl_contact_cache` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`user_id` int unsigned COMMENT 'FK to civicrm_contact (could be null for anon user)',
`contact_id` int unsigned NOT NULL COMMENT 'FK to civicrm_contact',
`operation` varchar(8) NOT NULL COMMENT 'What operation does this user have permission on?'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_user_contact_operation`(
user_id
, contact_id
, operation
)
, CONSTRAINT FK_civicrm_acl_contact_cache_user_id FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_acl_contact_cache_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_relationship_type
-- *
-- * Relationship types s/b structured with contact_a as the 'subject/child' contact and contact_b as the 'object/parent' contact (e.g. Individual A is Employee of Org B).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_relationship_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
`name_a_b` varchar(64) COMMENT 'name for relationship of contact_a to contact_b.',
`label_a_b` varchar(64) COMMENT 'label for relationship of contact_a to contact_b.',
`name_b_a` varchar(64) COMMENT 'Optional name for relationship of contact_b to contact_a.',
`label_b_a` varchar(64) COMMENT 'Optional label for relationship of contact_b to contact_a.',
`description` varchar(255) COMMENT 'Optional verbose description of the relationship type.',
`contact_type_a` varchar(12) COMMENT 'If defined, contact_a in a relationship of this type must be a specific contact_type.',
`contact_type_b` varchar(12) COMMENT 'If defined, contact_b in a relationship of this type must be a specific contact_type.',
`contact_sub_type_a` varchar(64) COMMENT 'If defined, contact_sub_type_a in a relationship of this type must be a specific contact_sub_type.\n ',
`contact_sub_type_b` varchar(64) COMMENT 'If defined, contact_sub_type_b in a relationship of this type must be a specific contact_sub_type.\n ',
`is_reserved` tinyint COMMENT 'Is this relationship type a predefined system type (can not be changed or de-activated)?',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this relationship type currently active (i.e. can be used when creating or editing relationships)?\n '
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name_a_b`(
name_a_b
)
, UNIQUE INDEX `UI_name_b_a`(
name_b_a
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_saved_search
-- *
-- * Users can save their complex SQL queries and use them later.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_saved_search` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Saved Search ID',
`form_values` text COMMENT 'Submitted form values for this search',
`mapping_id` int unsigned COMMENT 'Foreign key to civicrm_mapping used for saved search-builder searches.',
`search_custom_id` int unsigned COMMENT 'Foreign key to civicrm_option value table used for saved custom searches.',
`where_clause` text COMMENT 'the sql where clause if a saved search acl',
`select_tables` text COMMENT 'the tables to be included in a select data',
`where_tables` text COMMENT 'the tables to be included in the count statement'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_saved_search_mapping_id FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contact_type
-- *
-- * Provide type information for contacts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contact_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contact Type ID',
`name` varchar(64) COMMENT 'Internal name of Contact Type (or Subtype).',
`label` varchar(64) COMMENT 'localized Name of Contact Type.',
`description` text COMMENT 'localized Optional verbose description of the type.',
`image_URL` varchar(255) COMMENT 'URL of image if any.',
`parent_id` int unsigned COMMENT 'Optional FK to parent contact type.',
`is_active` tinyint COMMENT 'Is this entry active?',
`is_reserved` tinyint COMMENT 'Is this contact type a predefined system type'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `contact_type`(
name
)
, CONSTRAINT FK_civicrm_contact_type_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_contact_type`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_batch
-- *
-- * Stores the details of a batch operation Used primarily when doing batch operations with an external system.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_batch` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Address ID',
`name` varchar(64) COMMENT 'Variable name/programmatic handle for this batch.',
`title` varchar(255) COMMENT 'Friendly Name.',
`description` text COMMENT 'Description of this batch set.',
`created_id` int unsigned COMMENT 'FK to Contact ID',
`created_date` datetime COMMENT 'When was this item created',
`modified_id` int unsigned COMMENT 'FK to Contact ID',
`modified_date` datetime COMMENT 'When was this item created',
`saved_search_id` int unsigned COMMENT 'FK to Saved Search ID',
`status_id` int unsigned NOT NULL COMMENT 'fk to Batch Status options in civicrm_option_values',
`type_id` int unsigned COMMENT 'fk to Batch Type options in civicrm_option_values',
`mode_id` int unsigned COMMENT 'fk to Batch mode options in civicrm_option_values',
`total` decimal(20,2) COMMENT 'Total amount for this batch.',
`item_count` int unsigned COMMENT 'Number of items in a batch.',
`payment_instrument_id` int unsigned COMMENT 'fk to Payment Instrument options in civicrm_option_values',
`exported_date` datetime ,
`data` longtext COMMENT 'cache entered data'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_batch_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_batch_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_batch_saved_search_id FOREIGN KEY (`saved_search_id`) REFERENCES `civicrm_saved_search`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_entity_batch
-- *
-- * Batch entities (Contributions, Participants, Contacts) to a batch.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_batch` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`entity_table` varchar(64) COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
`entity_id` int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
`batch_id` int unsigned NOT NULL COMMENT 'FK to civicrm_batch'
,
PRIMARY KEY (`id`)
, INDEX `index_entity`(
entity_table
, entity_id
)
, UNIQUE INDEX `UI_batch_entity`(
batch_id
, entity_id
, entity_table
)
, CONSTRAINT FK_civicrm_entity_batch_batch_id FOREIGN KEY (`batch_id`) REFERENCES `civicrm_batch`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_component
-- *
-- * Stores information about the mailing components (header/footer).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_component` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar(64) COMMENT 'The name of this component',
`component_type` varchar(12) COMMENT 'Type of Component.',
`subject` varchar(255) ,
`body_html` text COMMENT 'Body of the component in html format.',
`body_text` text COMMENT 'Body of the component in text format.',
`is_default` tinyint DEFAULT 0 COMMENT 'Is this the default component for this component_type?',
`is_active` tinyint COMMENT 'Is this property active?'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_abtest
-- *
-- * Stores information about abtesting
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_abtest` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar(128) COMMENT 'Name of the A/B test',
`status` varchar(32) COMMENT 'Status',
`mailing_id_a` int unsigned COMMENT 'The first experimental mailing (\"A\" condition)',
`mailing_id_b` int unsigned COMMENT 'The second experimental mailing (\"B\" condition)',
`mailing_id_c` int unsigned COMMENT 'The final, general mailing (derived from A or B)',
`domain_id` int unsigned COMMENT 'Which site is this mailing for',
`testing_criteria` varchar(32) ,
`winner_criteria` varchar(32) ,
`specific_url` varchar(255) COMMENT 'What specific url to track',
`declare_winning_time` datetime COMMENT 'In how much time to declare winner',
`group_percentage` int unsigned ,
`created_id` int unsigned COMMENT 'FK to Contact ID',
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When was this item created'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_abtest_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_bounce_type
-- *
-- * Table to index the various bounce types and their properties
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_bounce_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar(24) NOT NULL COMMENT 'Type of bounce',
`description` varchar(255) COMMENT 'A description of this bounce type',
`hold_threshold` int unsigned NOT NULL COMMENT 'Number of bounces of this type required before the email address is put on bounce hold'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_bounce_pattern
-- *
-- * Pseudo-constant table of patterns for bounce classification
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_bounce_pattern` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`bounce_type_id` int unsigned NOT NULL COMMENT 'Type of bounce',
`pattern` varchar(255) COMMENT 'A regexp to match a message to a bounce type'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_bounce_pattern_bounce_type_id FOREIGN KEY (`bounce_type_id`) REFERENCES `civicrm_mailing_bounce_type`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_premiums
-- *
-- * table - settings for the Premiums features for a given contribution page
-- *
-- *******************************************************/
CREATE TABLE `civicrm_premiums` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`entity_table` varchar(64) NOT NULL COMMENT 'Joins these premium settings to another object. Always civicrm_contribution_page for now.',
`entity_id` int unsigned NOT NULL ,
`premiums_active` tinyint NOT NULL DEFAULT 0 COMMENT 'Is the Premiums feature enabled for this page?',
`premiums_intro_title` varchar(255) COMMENT 'Title for Premiums section.',
`premiums_intro_text` text COMMENT 'Displayed in <div> at top of Premiums section of page. Text and HTML allowed.',
`premiums_contact_email` varchar(100) COMMENT 'This email address is included in receipts if it is populated and a premium has been selected.',
`premiums_contact_phone` varchar(50) COMMENT 'This phone number is included in receipts if it is populated and a premium has been selected.',
`premiums_display_min_contribution` tinyint NOT NULL COMMENT 'Boolean. Should we automatically display minimum contribution amount text after the premium descriptions.',
`premiums_nothankyou_label` varchar(255) COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)',
`premiums_nothankyou_position` int unsigned DEFAULT 1
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_currency
-- *
-- *******************************************************/
CREATE TABLE `civicrm_currency` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Currency Id',
`name` varchar(64) COMMENT 'Currency Name',
`symbol` varchar(8) COMMENT 'Currency Symbol',
`numeric_code` varchar(3) COMMENT 'Numeric currency code',
`full_name` varchar(64) COMMENT 'Full currency name'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_financial_account
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_account` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) NOT NULL COMMENT 'Financial Account Name.',
`contact_id` int unsigned COMMENT 'FK to Contact ID that is responsible for the funds in this account',
`financial_account_type_id` int unsigned NOT NULL DEFAULT 3 COMMENT 'pseudo FK into civicrm_option_value.',
`accounting_code` varchar(64) COMMENT 'Optional value for mapping monies owed and received to accounting system codes.',
`account_type_code` varchar(64) COMMENT 'Optional value for mapping account types to accounting system account categories (QuickBooks Account Type Codes for example).',
`description` varchar(255) COMMENT 'Financial Type Description.',
`parent_id` int unsigned COMMENT 'Parent ID in account hierarchy',
`is_header_account` tinyint DEFAULT 0 COMMENT 'Is this a header account which does not allow transactions to be posted against it directly, but only to its sub-accounts?',
`is_deductible` tinyint DEFAULT 1 COMMENT 'Is this account tax-deductible?',
`is_tax` tinyint DEFAULT 0 COMMENT 'Is this account for taxes?',
`tax_rate` decimal(10,8) COMMENT 'The percentage of the total_amount that is due for this tax.',
`is_reserved` tinyint COMMENT 'Is this a predefined system object?',
`is_active` tinyint COMMENT 'Is this property active?',
`is_default` tinyint COMMENT 'Is this account the default one (or default tax one) for its financial_account_type?'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_financial_account_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_financial_account`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_payment_processor_type
-- *
-- *******************************************************/
CREATE TABLE `civicrm_payment_processor_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Payment Processor Type ID',
`name` varchar(64) COMMENT 'Payment Processor Name.',
`title` varchar(127) COMMENT 'Payment Processor Name.',
`description` varchar(255) COMMENT 'Payment Processor Description.',
`is_active` tinyint COMMENT 'Is this processor active?',
`is_default` tinyint COMMENT 'Is this processor the default?',
`user_name_label` varchar(255) ,
`password_label` varchar(255) ,
`signature_label` varchar(255) ,
`subject_label` varchar(255) ,
`class_name` varchar(255) ,
`url_site_default` varchar(255) ,
`url_api_default` varchar(255) ,
`url_recur_default` varchar(255) ,
`url_button_default` varchar(255) ,
`url_site_test_default` varchar(255) ,
`url_api_test_default` varchar(255) ,
`url_recur_test_default` varchar(255) ,
`url_button_test_default` varchar(255) ,
`billing_mode` int unsigned NOT NULL COMMENT 'Billing Mode (deprecated)',
`is_recur` tinyint COMMENT 'Can process recurring contributions',
`payment_type` int unsigned DEFAULT 1 COMMENT 'Payment Type: Credit or Debit (deprecated)',
`payment_instrument_id` int unsigned DEFAULT 1 COMMENT 'Payment Instrument ID'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_financial_type
-- *
-- * Formerly civicrm_contribution_type merged into this table in 4.3
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID of original financial_type so you can search this table by the financial_type.id and then select the relevant version based on the timestamp',
`name` varchar(64) NOT NULL COMMENT 'Financial Type Name.',
`description` varchar(255) COMMENT 'Financial Type Description.',
`is_deductible` tinyint DEFAULT 1 COMMENT 'Is this financial type tax-deductible? If true, contributions of this type may be fully OR partially deductible - non-deductible amount is stored in the Contribution record.',
`is_reserved` tinyint COMMENT 'Is this a predefined system object?',
`is_active` tinyint COMMENT 'Is this property active?'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_id`(
id
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_entity_financial_account
-- *
-- * Map between an entity and a financial account, where there is a specific relationship between the financial account and the entity, e.g. Income Account for or AR Account for
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_financial_account` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`entity_table` varchar(64) NOT NULL COMMENT 'Links to an entity_table like civicrm_financial_type',
`entity_id` int unsigned NOT NULL COMMENT 'Links to an id in the entity_table, such as vid in civicrm_financial_type',
`account_relationship` int unsigned NOT NULL COMMENT 'FK to a new civicrm_option_value (account_relationship)',
`financial_account_id` int unsigned NOT NULL COMMENT 'FK to the financial_account_id'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `index_entity_id_entity_table_account_relationship`(
entity_id
, entity_table
, account_relationship
)
, CONSTRAINT FK_civicrm_entity_financial_account_financial_account_id FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_financial_item
-- *
-- * Financial data for civicrm_line_item, etc.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_item` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the item was created',
`transaction_date` datetime NOT NULL COMMENT 'Date and time of the source transaction',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact ID of contact the item is from',
`description` varchar(255) COMMENT 'Human readable description of this item, to ease display without lookup of source item.',
`amount` decimal(20,2) NOT NULL DEFAULT 0 COMMENT 'Total amount of this item',
`currency` varchar(3) COMMENT 'Currency for the amount',
`financial_account_id` int unsigned COMMENT 'FK to civicrm_financial_account',
`status_id` int unsigned COMMENT 'Payment status: test, paid, part_paid, unpaid (if empty assume unpaid)',
`entity_table` varchar(64) COMMENT 'The table providing the source of this item such as civicrm_line_item',
`entity_id` int unsigned COMMENT 'The specific source item that is responsible for the creation of this financial_item'
,
PRIMARY KEY (`id`)
, INDEX `IX_created_date`(
created_date
)
, INDEX `IX_transaction_date`(
transaction_date
)
, INDEX `index_entity_id_entity_table`(
entity_id
, entity_table
)
, CONSTRAINT FK_civicrm_financial_item_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_financial_item_financial_account_id FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_membership_status
-- *
-- * Membership Status stores admin configurable rules for assigning status to memberships.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_status` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
`name` varchar(128) COMMENT 'Name for Membership Status',
`label` varchar(128) COMMENT 'Label for Membership Status',
`start_event` varchar(12) COMMENT 'Event when this status starts.',
`start_event_adjust_unit` varchar(8) COMMENT 'Unit used for adjusting from start_event.',
`start_event_adjust_interval` int COMMENT 'Status range begins this many units from start_event.',
`end_event` varchar(12) COMMENT 'Event after which this status ends.',
`end_event_adjust_unit` varchar(8) COMMENT 'Unit used for adjusting from the ending event.',
`end_event_adjust_interval` int COMMENT 'Status range ends this many units from end_event.',
`is_current_member` tinyint COMMENT 'Does this status aggregate to current members (e.g. New, Renewed, Grace might all be TRUE... while Unrenewed, Lapsed, Inactive would be FALSE).',
`is_admin` tinyint COMMENT 'Is this status for admin/manual assignment only.',
`weight` int ,
`is_default` tinyint COMMENT 'Assign this status to a membership record if no other status match is found.',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this membership_status enabled.',
`is_reserved` tinyint DEFAULT 0 COMMENT 'Is this membership_status reserved.'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_campaign
-- *
-- * Campaign Details.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_campaign` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Campaign ID.',
`name` varchar(255) NOT NULL COMMENT 'Name of the Campaign.',
`title` varchar(255) COMMENT 'Title of the Campaign.',
`description` text COMMENT 'Full description of Campaign.',
`start_date` datetime COMMENT 'Date and time that Campaign starts.',
`end_date` datetime COMMENT 'Date and time that Campaign ends.',
`campaign_type_id` int unsigned DEFAULT NULL COMMENT 'Campaign Type ID.Implicit FK to civicrm_option_value where option_group = campaign_type',
`status_id` int unsigned DEFAULT NULL COMMENT 'Campaign status ID.Implicit FK to civicrm_option_value where option_group = campaign_status',
`external_identifier` varchar(32) COMMENT 'Unique trusted external ID (generally from a legacy app/datasource). Particularly useful for deduping operations.',
`parent_id` int unsigned DEFAULT NULL COMMENT 'Optional parent id for this Campaign.',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this Campaign enabled or disabled/cancelled?',
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this Campaign.',
`created_date` datetime COMMENT 'Date and time that Campaign was created.',
`last_modified_id` int unsigned COMMENT 'FK to civicrm_contact, who recently edited this Campaign.',
`last_modified_date` datetime COMMENT 'Date and time that Campaign was edited last time.',
`goal_general` text COMMENT 'General goals for Campaign.',
`goal_revenue` decimal(20,2) COMMENT 'The target revenue for this campaign.'
,
PRIMARY KEY (`id`)
, INDEX `UI_campaign_type_id`(
campaign_type_id
)
, INDEX `UI_campaign_status_id`(
status_id
)
, UNIQUE INDEX `UI_external_identifier`(
external_identifier
)
, CONSTRAINT FK_civicrm_campaign_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_campaign_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_campaign_last_modified_id FOREIGN KEY (`last_modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_campaign_group
-- *
-- * Campaign Group Details.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_campaign_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Campaign Group id.',
`campaign_id` int unsigned NOT NULL COMMENT 'Foreign key to the activity Campaign.',
`group_type` varchar(8) DEFAULT NULL COMMENT 'Type of Group.',
`entity_table` varchar(64) DEFAULT NULL COMMENT 'Name of table where item being referenced is stored.',
`entity_id` int unsigned DEFAULT NULL COMMENT 'Entity id of referenced table.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_campaign_group_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_survey
-- *
-- * Campaign Survey Details.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_survey` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Survey id.',
`title` varchar(255) NOT NULL COMMENT 'Title of the Survey.',
`campaign_id` int unsigned DEFAULT NULL COMMENT 'Foreign key to the Campaign.',
`activity_type_id` int unsigned DEFAULT NULL COMMENT 'Implicit FK to civicrm_option_value where option_group = activity_type',
`recontact_interval` text COMMENT 'Recontact intervals for each status.',
`instructions` text COMMENT 'Script instructions for volunteers to use for the survey.',
`release_frequency` int unsigned DEFAULT NULL COMMENT 'Number of days for recurrence of release.',
`max_number_of_contacts` int unsigned DEFAULT NULL COMMENT 'Maximum number of contacts to allow for survey.',
`default_number_of_contacts` int unsigned DEFAULT NULL COMMENT 'Default number of contacts to allow for survey.',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this survey enabled or disabled/cancelled?',
`is_default` tinyint DEFAULT 0 COMMENT 'Is this default survey?',
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this Survey.',
`created_date` datetime COMMENT 'Date and time that Survey was created.',
`last_modified_id` int unsigned COMMENT 'FK to civicrm_contact, who recently edited this Survey.',
`last_modified_date` datetime COMMENT 'Date and time that Survey was edited last time.',
`result_id` int unsigned DEFAULT NULL COMMENT 'Used to store option group id.',
`bypass_confirm` tinyint DEFAULT 0 COMMENT 'Bypass the email verification.',
`thankyou_title` varchar(255) COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).',
`thankyou_text` text COMMENT 'text and html allowed. displayed above result on success page',
`is_share` tinyint DEFAULT 1 COMMENT 'Can people share the petition through social media?'
,
PRIMARY KEY (`id`)
, INDEX `UI_activity_type_id`(
activity_type_id
)
, CONSTRAINT FK_civicrm_survey_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_survey_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_survey_last_modified_id FOREIGN KEY (`last_modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_participant_status_type
-- *
-- * various types of CiviEvent participant statuses
-- *
-- *******************************************************/
CREATE TABLE `civicrm_participant_status_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique participant status type id',
`name` varchar(64) COMMENT 'non-localized name of the status type',
`label` varchar(255) COMMENT 'localized label for display of this status type',
`class` varchar(8) COMMENT 'the general group of status type this one belongs to',
`is_reserved` tinyint COMMENT 'whether this is a status type required by the system',
`is_active` tinyint DEFAULT 1 COMMENT 'whether this status type is active',
`is_counted` tinyint COMMENT 'whether this status type is counted against event size limit',
`weight` int unsigned NOT NULL COMMENT 'controls sort order',
`visibility_id` int unsigned COMMENT 'whether the status type is visible to the public, an implicit foreign key to option_value.value related to the `visibility` option_group'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_event_carts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_event_carts` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Cart Id',
`user_id` int unsigned COMMENT 'FK to civicrm_contact who created this cart',
`completed` tinyint DEFAULT 0
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_event_carts_user_id FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_dedupe_rule_group
-- *
-- * Dedupe rule groups
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dedupe_rule_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique dedupe rule group id',
`contact_type` varchar(12) COMMENT 'The type of contacts this group applies to',
`threshold` int NOT NULL COMMENT 'The weight threshold the sum of the rule weights has to cross to consider two contacts the same',
`used` varchar(12) NOT NULL COMMENT 'Whether the rule should be used for cases where usage is Unsupervised, Supervised OR General(programatically)',
`name` varchar(64) COMMENT 'Name of the rule group',
`title` varchar(255) COMMENT 'Label of the rule group',
`is_reserved` tinyint COMMENT 'Is this a reserved rule - a rule group that has been optimized and cannot be changed by the admin'
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_dedupe_rule
-- *
-- * Dedupe rules for use by rule groups
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dedupe_rule` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique dedupe rule id',
`dedupe_rule_group_id` int unsigned NOT NULL COMMENT 'The id of the rule group this rule belongs to',
`rule_table` varchar(64) NOT NULL COMMENT 'The name of the table this rule is about',
`rule_field` varchar(64) NOT NULL COMMENT 'The name of the field of the table referenced in rule_table',
`rule_length` int unsigned COMMENT 'The length of the matching substring',
`rule_weight` int NOT NULL COMMENT 'The weight of the rule'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_dedupe_rule_dedupe_rule_group_id FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_dedupe_exception
-- *
-- * Dedupe exceptions
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dedupe_exception` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique dedupe exception id',
`contact_id1` int unsigned COMMENT 'FK to Contact ID',
`contact_id2` int unsigned COMMENT 'FK to Contact ID'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_contact_id1_contact_id2`(
contact_id1
, contact_id2
)
, CONSTRAINT FK_civicrm_dedupe_exception_contact_id1 FOREIGN KEY (`contact_id1`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_dedupe_exception_contact_id2 FOREIGN KEY (`contact_id2`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_case_type
-- *
-- * Case type definition
-- *
-- *******************************************************/
CREATE TABLE `civicrm_case_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Autoincremented type id',
`name` varchar(64) NOT NULL COMMENT 'Machine name for Case Type',
`title` varchar(64) NOT NULL COMMENT 'Natural language name for Case Type',
`description` varchar(255) COMMENT 'Description of the Case Type',
`is_active` tinyint COMMENT 'Is this entry active?',
`is_reserved` tinyint COMMENT 'Is this case type a predefined system type?',
`weight` int NOT NULL DEFAULT 1 COMMENT 'Ordering of the case types',
`definition` blob COMMENT 'xml definition of case type'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `case_type_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_grant
-- *
-- * This table stores information about grants given to a contact.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_grant` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Grant id',
`contact_id` int unsigned NOT NULL COMMENT 'Contact ID of contact record given grant belongs to.',
`application_received_date` date COMMENT 'Date on which grant application was received by donor.',
`decision_date` date COMMENT 'Date on which grant decision was made.',
`money_transfer_date` date COMMENT 'Date on which grant money transfer was made.',
`grant_due_date` date COMMENT 'Date on which grant report is due.',
`grant_report_received` tinyint COMMENT 'Yes/No field stating whether grant report was received by donor.',
`grant_type_id` int unsigned NOT NULL COMMENT 'Type of grant. Implicit FK to civicrm_option_value in grant_type option_group.',
`amount_total` decimal(20,2) NOT NULL COMMENT 'Requested grant amount, in default currency.',
`amount_requested` decimal(20,2) COMMENT 'Requested grant amount, in original currency (optional).',
`amount_granted` decimal(20,2) COMMENT 'Granted amount, in default currency.',
`currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.',
`rationale` text COMMENT 'Grant rationale.',
`status_id` int unsigned NOT NULL COMMENT 'Id of Grant status.',
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type.'
,
PRIMARY KEY (`id`)
, INDEX `index_grant_type_id`(
grant_type_id
)
, INDEX `index_status_id`(
status_id
)
, CONSTRAINT FK_civicrm_grant_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_grant_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_tell_friend
-- *
-- *******************************************************/
CREATE TABLE `civicrm_tell_friend` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Friend ID',
`entity_table` varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.',
`entity_id` int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
`title` varchar(255) ,
`intro` text COMMENT 'Introductory message to contributor or participant displayed on the Tell a Friend form.',
`suggested_message` text COMMENT 'Suggested message to friends, provided as default on the Tell A Friend form.',
`general_link` varchar(255) COMMENT 'URL for general info about the organization - included in the email sent to friends.',
`thankyou_title` varchar(255) COMMENT 'Text for Tell a Friend thank you page header and HTML title.',
`thankyou_text` text COMMENT 'Thank you message displayed on success page.',
`is_active` tinyint
,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_pledge_block
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pledge_block` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Pledge ID',
`entity_table` varchar(64) COMMENT 'physical tablename for entity being joined to pledge, e.g. civicrm_contact',
`entity_id` int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
`pledge_frequency_unit` varchar(128) COMMENT 'Delimited list of supported frequency units',
`is_pledge_interval` tinyint DEFAULT 0 COMMENT 'Is frequency interval exposed on the contribution form.',
`max_reminders` int unsigned DEFAULT 1 COMMENT 'The maximum number of payment reminders to send for any given payment.',
`initial_reminder_day` int unsigned DEFAULT 5 COMMENT 'Send initial reminder this many days prior to the payment due date.',
`additional_reminder_day` int unsigned DEFAULT 5 COMMENT 'Send additional reminder this many days after last one sent, up to maximum number of reminders.',
`pledge_start_date` varchar(64) COMMENT 'The date the first scheduled pledge occurs.',
`is_pledge_start_date_visible` tinyint NOT NULL DEFAULT 0 COMMENT 'If true - recurring start date is shown.',
`is_pledge_start_date_editable` tinyint NOT NULL DEFAULT 0 COMMENT 'If true - recurring start date is editable.'
,
PRIMARY KEY (`id`)
, INDEX `index_entity`(
entity_table
, entity_id
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_queue_item
-- *
-- * Stores a list of queue items
-- *
-- *******************************************************/
CREATE TABLE `civicrm_queue_item` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`queue_name` varchar(64) NOT NULL COMMENT 'Name of the queue which includes this item',
`weight` int NOT NULL ,
`submit_time` datetime NOT NULL COMMENT 'date on which this item was submitted to the queue',
`release_time` datetime COMMENT 'date on which this job becomes available; null if ASAP',
`data` text COMMENT 'Serialized queue'
,
PRIMARY KEY (`id`)
, INDEX `index_queueids`(
queue_name
, weight
, id
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_pcp
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pcp` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Personal Campaign Page ID',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact ID',
`status_id` int unsigned NOT NULL ,
`title` varchar(255) DEFAULT NULL ,
`intro_text` text DEFAULT NULL ,
`page_text` text DEFAULT NULL ,
`donate_link_text` varchar(255) DEFAULT NULL ,
`page_id` int unsigned NOT NULL COMMENT 'The Contribution or Event Page which triggered this pcp',
`page_type` varchar(64) DEFAULT 'contribute' COMMENT 'The type of PCP this is: contribute or event',
`pcp_block_id` int unsigned NOT NULL COMMENT 'The pcp block that this pcp page was created from',
`is_thermometer` int unsigned DEFAULT 0 ,
`is_honor_roll` int unsigned DEFAULT 0 ,
`goal_amount` decimal(20,2) COMMENT 'Goal amount of this Personal Campaign Page.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`is_active` tinyint DEFAULT 0 COMMENT 'Is Personal Campaign Page enabled/active?',
`is_notify` tinyint DEFAULT 0 COMMENT 'Notify owner via email when someone donates to page?'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_pcp_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_cxn
-- *
-- *******************************************************/
CREATE TABLE `civicrm_cxn` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Connection ID',
`app_guid` varchar(128) COMMENT 'Application GUID',
`app_meta` text COMMENT 'Application Metadata (JSON)',
`cxn_guid` varchar(128) COMMENT 'Connection GUID',
`secret` text COMMENT 'Shared secret',
`perm` text COMMENT 'Permissions approved for the service (JSON)',
`options` text COMMENT 'Options for the service (JSON)',
`is_active` tinyint DEFAULT 1 COMMENT 'Is connection currently enabled?',
`created_date` timestamp NULL DEFAULT NULL COMMENT 'When was the connection was created.',
`modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When the connection was created or modified.',
`fetched_date` timestamp NULL DEFAULT NULL COMMENT 'The last time the application metadata was fetched.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_appid`(
app_guid
)
, UNIQUE INDEX `UI_keypair_cxnid`(
cxn_guid
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_cache
-- *
-- * Table to cache items for civicrm components.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_cache` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`group_name` varchar(32) NOT NULL COMMENT 'group name for cache element, useful in cleaning cache elements',
`path` varchar(255) COMMENT 'Unique path name for cache element',
`data` longtext COMMENT 'data associated with this path',
`component_id` int unsigned COMMENT 'Component that this menu item belongs to',
`created_date` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'When was the cache item created',
`expired_date` timestamp NULL DEFAULT NULL COMMENT 'When should the cache item expire'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_group_path_date`(
group_name
, path
, created_date
)
, CONSTRAINT FK_civicrm_cache_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_country
-- *
-- *******************************************************/
CREATE TABLE `civicrm_country` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Country Id',
`name` varchar(64) COMMENT 'Country Name',
`iso_code` char(2) COMMENT 'ISO Code',
`country_code` varchar(4) COMMENT 'National prefix to be used when dialing TO this country.',
`address_format_id` int unsigned COMMENT 'Foreign key to civicrm_address_format.id.',
`idd_prefix` varchar(4) COMMENT 'International direct dialing prefix from within the country TO another country',
`ndd_prefix` varchar(4) COMMENT 'Access prefix to call within a country to a different area',
`region_id` int unsigned NOT NULL COMMENT 'Foreign key to civicrm_worldregion.id.',
`is_province_abbreviated` tinyint DEFAULT 0 COMMENT 'Should state/province be displayed as abbreviation for contacts from this country?'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name_iso_code`(
name
, iso_code
)
, CONSTRAINT FK_civicrm_country_address_format_id FOREIGN KEY (`address_format_id`) REFERENCES `civicrm_address_format`(`id`) , CONSTRAINT FK_civicrm_country_region_id FOREIGN KEY (`region_id`) REFERENCES `civicrm_worldregion`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_custom_group
-- *
-- * All extended (custom) properties are associated with a group. These are logical sets of related data.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_custom_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Custom Group ID',
`name` varchar(64) COMMENT 'Variable name/programmatic handle for this group.',
`title` varchar(64) NOT NULL COMMENT 'Friendly Name.',
`extends` varchar(255) DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).',
`extends_entity_column_id` int unsigned DEFAULT NULL COMMENT 'FK to civicrm_option_value.id (for option group custom_data_type.)',
`extends_entity_column_value` varchar(255) COMMENT 'linking custom group for dynamic object',
`style` varchar(15) COMMENT 'Visual relationship between this form and its parent.',
`collapse_display` int unsigned DEFAULT 0 COMMENT 'Will this group be in collapsed or expanded mode on initial display ?',
`help_pre` text COMMENT 'Description and/or help text to display before fields in form.',
`help_post` text COMMENT 'Description and/or help text to display after fields in form.',
`weight` int NOT NULL DEFAULT 1 COMMENT 'Controls display order when multiple extended property groups are setup for the same class.',
`is_active` tinyint COMMENT 'Is this property active?',
`table_name` varchar(255) COMMENT 'Name of the table that holds the values for this group.',
`is_multiple` tinyint COMMENT 'Does this group hold multiple values?',
`min_multiple` int unsigned COMMENT 'minimum number of multiple records (typically 0?)',
`max_multiple` int unsigned COMMENT 'maximum number of multiple records, if 0 - no max',
`collapse_adv_display` int unsigned DEFAULT 0 COMMENT 'Will this group be in collapsed or expanded mode on advanced search display ?',
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this custom group',
`created_date` datetime COMMENT 'Date and time this custom group was created.',
`is_reserved` tinyint DEFAULT 0 COMMENT 'Is this a reserved Custom Group?',
`is_public` tinyint DEFAULT 1 COMMENT 'Is this property public?'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_title_extends`(
title
, extends
)
, UNIQUE INDEX `UI_name_extends`(
name
, extends
)
, CONSTRAINT FK_civicrm_custom_group_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_custom_field
-- *
-- * Stores info about an extended (custom) property (data and form field info).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_custom_field` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Custom Field ID',
`custom_group_id` int unsigned NOT NULL COMMENT 'FK to civicrm_custom_group.',
`name` varchar(64) COMMENT 'Variable name/programmatic handle for this group.',
`label` varchar(255) NOT NULL COMMENT 'Text for form field label (also friendly name for administering this custom property).',
`data_type` varchar(16) NOT NULL COMMENT 'Controls location of data storage in extended_data table.',
`html_type` varchar(32) NOT NULL COMMENT 'HTML types plus several built-in extended types.',
`default_value` varchar(255) COMMENT 'Use form_options.is_default for field_types which use options.',
`is_required` tinyint COMMENT 'Is a value required for this property.',
`is_searchable` tinyint COMMENT 'Is this property searchable.',
`is_search_range` tinyint DEFAULT 0 COMMENT 'Is this property range searchable.',
`weight` int NOT NULL DEFAULT 1 COMMENT 'Controls field display order within an extended property group.',
`help_pre` text COMMENT 'Description and/or help text to display before this field.',
`help_post` text COMMENT 'Description and/or help text to display after this field.',
`mask` varchar(64) COMMENT 'Optional format instructions for specific field types, like date types.',
`attributes` varchar(255) COMMENT 'Store collection of type-appropriate attributes, e.g. textarea needs rows/cols attributes',
`javascript` varchar(255) COMMENT 'Optional scripting attributes for field.',
`is_active` tinyint COMMENT 'Is this property active?',
`is_view` tinyint COMMENT 'Is this property set by PHP Code? A code field is viewable but not editable',
`options_per_line` int unsigned COMMENT 'number of options per line for checkbox and radio',
`text_length` int unsigned COMMENT 'field length if alphanumeric',
`start_date_years` int COMMENT 'Date may be up to start_date_years years prior to the current date.',
`end_date_years` int COMMENT 'Date may be up to end_date_years years after the current date.',
`date_format` varchar(64) COMMENT 'date format for custom date',
`time_format` int unsigned COMMENT 'time format for custom date',
`note_columns` int unsigned COMMENT ' Number of columns in Note Field ',
`note_rows` int unsigned COMMENT ' Number of rows in Note Field ',
`column_name` varchar(255) COMMENT 'Name of the column that holds the values for this field.',
`option_group_id` int unsigned COMMENT 'For elements with options, the option group id that is used',
`filter` varchar(255) COMMENT 'Stores Contact Get API params contact reference custom fields. May be used for other filters in the future.',
`in_selector` tinyint DEFAULT 0 COMMENT 'Should the multi-record custom field values be displayed in tab table listing'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_label_custom_group_id`(
label
, custom_group_id
)
, UNIQUE INDEX `UI_name_custom_group_id`(
name
, custom_group_id
)
, CONSTRAINT FK_civicrm_custom_field_custom_group_id FOREIGN KEY (`custom_group_id`) REFERENCES `civicrm_custom_group`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_domain
-- *
-- * Top-level hierarchy to support multi-org/domain installations. Define domains for multi-org installs, else all contacts belong to one domain.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_domain` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Domain ID',
`name` varchar(64) COMMENT 'Name of Domain / Organization',
`description` varchar(255) COMMENT 'Description of Domain.',
`config_backend` text COMMENT 'Backend configuration.',
`version` varchar(32) COMMENT 'The civicrm version this instance is running',
`contact_id` int unsigned COMMENT 'FK to Contact ID. This is specifically not an FK to avoid circular constraints',
`locales` text COMMENT 'list of locales supported by the current db state (NULL for single-lang install)',
`locale_custom_strings` text COMMENT 'Locale specific string overrides'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_domain_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_email
-- *
-- * Email information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_email` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Email ID',
`contact_id` int unsigned COMMENT 'FK to Contact ID',
`location_type_id` int unsigned COMMENT 'Which Location does this email belong to.',
`email` varchar(254) COMMENT 'Email address',
`is_primary` tinyint DEFAULT 0 COMMENT 'Is this the primary?',
`is_billing` tinyint DEFAULT 0 COMMENT 'Is this the billing?',
`on_hold` tinyint NOT NULL DEFAULT 0 COMMENT 'Is this address on bounce hold?',
`is_bulkmail` tinyint NOT NULL DEFAULT 0 COMMENT 'Is this address for bulk mail ?',
`hold_date` datetime COMMENT 'When the address went on bounce hold',
`reset_date` datetime COMMENT 'When the address bounce status was last reset',
`signature_text` text DEFAULT NULL COMMENT 'Text formatted signature for the email.',
`signature_html` text DEFAULT NULL COMMENT 'HTML formatted signature for the email.'
,
PRIMARY KEY (`id`)
, INDEX `index_location_type`(
location_type_id
)
, INDEX `UI_email`(
email
)
, INDEX `index_is_primary`(
is_primary
)
, INDEX `index_is_billing`(
is_billing
)
, CONSTRAINT FK_civicrm_email_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_entity_file
-- *
-- * Attaches (joins) uploaded files (images, documents, etc.) to entities (Contacts, Groups, Actions).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_file` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`entity_table` varchar(64) COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
`entity_id` int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
`file_id` int unsigned NOT NULL COMMENT 'FK to civicrm_file'
,
PRIMARY KEY (`id`)
, INDEX `index_entity`(
entity_table
, entity_id
)
, INDEX `index_entity_file_id`(
entity_table
, entity_id
, file_id
)
, CONSTRAINT FK_civicrm_entity_file_file_id FOREIGN KEY (`file_id`) REFERENCES `civicrm_file`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_im
-- *
-- * IM information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_im` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique IM ID',
`contact_id` int unsigned COMMENT 'FK to Contact ID',
`location_type_id` int unsigned COMMENT 'Which Location does this email belong to.',
`name` varchar(64) COMMENT 'IM screen name',
`provider_id` int unsigned COMMENT 'Which IM Provider does this screen name belong to.',
`is_primary` tinyint DEFAULT 0 COMMENT 'Is this the primary IM for this contact and location.',
`is_billing` tinyint DEFAULT 0 COMMENT 'Is this the billing?'
,
PRIMARY KEY (`id`)
, INDEX `index_location_type`(
location_type_id
)
, INDEX `UI_provider_id`(
provider_id
)
, INDEX `index_is_primary`(
is_primary
)
, INDEX `index_is_billing`(
is_billing
)
, CONSTRAINT FK_civicrm_im_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_job
-- *
-- * Scheduled job.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_job` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Job Id',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for',
`run_frequency` varchar(8) DEFAULT "Daily" COMMENT 'Scheduled job run frequency.',
`last_run` timestamp NULL DEFAULT NULL COMMENT 'When was this cron entry last run',
`scheduled_run_date` timestamp NULL DEFAULT NULL COMMENT 'When is this cron entry scheduled to run',
`name` varchar(255) COMMENT 'Title of the job',
`description` varchar(255) COMMENT 'Description of the job',
`api_entity` varchar(255) COMMENT 'Entity of the job api call',
`api_action` varchar(255) COMMENT 'Action of the job api call',
`parameters` text COMMENT 'List of parameters to the command.',
`is_active` tinyint COMMENT 'Is this job active?'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_job_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_job_log
-- *
-- * Scheduled jobs log.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_job_log` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Job log entry Id',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for',
`run_time` timestamp COMMENT 'Log entry date',
`job_id` int unsigned COMMENT 'Pointer to job id - not a FK though, just for logging purposes',
`name` varchar(255) COMMENT 'Title of the job',
`command` varchar(255) COMMENT 'Full path to file containing job script',
`description` varchar(255) COMMENT 'Title line of log entry',
`data` text COMMENT 'Potential extended data for specific job run (e.g. tracebacks).'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_job_log_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_log
-- *
-- * Log can be linked to any object in the application.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_log` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Log ID',
`entity_table` varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.',
`entity_id` int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
`data` text COMMENT 'Updates does to this object if any.',
`modified_id` int unsigned COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.',
`modified_date` datetime COMMENT 'When was the referenced entity created or modified or deleted.'
,
PRIMARY KEY (`id`)
, INDEX `index_entity`(
entity_table
, entity_id
)
, CONSTRAINT FK_civicrm_log_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mail_settings
-- *
-- * Various email accounts for use by CiviMail (and its processor)
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mail_settings` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this match entry for',
`name` varchar(255) COMMENT 'name of this group of settings',
`is_default` tinyint COMMENT 'whether this is the default set of settings for this domain',
`domain` varchar(255) COMMENT 'email address domain (the part after @)',
`localpart` varchar(255) COMMENT 'optional local part (like civimail+ for addresses like civimail+s.1.2@example.com)',
`return_path` varchar(255) COMMENT 'contents of the Return-Path header',
`protocol` varchar(255) COMMENT 'name of the protocol to use for polling (like IMAP, POP3 or Maildir)',
`server` varchar(255) COMMENT 'server to use when polling',
`port` int unsigned COMMENT 'port to use when polling',
`username` varchar(255) COMMENT 'username to use when polling',
`password` varchar(255) COMMENT 'password to use when polling',
`is_ssl` tinyint COMMENT 'whether to use SSL or not',
`source` varchar(255) COMMENT 'folder to poll from when using IMAP, path to poll from when using Maildir, etc.',
`activity_status` varchar(255) COMMENT 'Name of status to use when creating email to activity.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mail_settings_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mapping_field
-- *
-- * Individual field mappings for Mapping
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mapping_field` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Mapping Field ID',
`mapping_id` int unsigned NOT NULL COMMENT 'Mapping to which this field belongs',
`name` varchar(255) COMMENT 'Mapping field key',
`contact_type` varchar(64) COMMENT 'Contact Type in mapping',
`column_number` int unsigned NOT NULL COMMENT 'Column number for mapping set',
`location_type_id` int unsigned COMMENT 'Location type of this mapping, if required',
`phone_type_id` int unsigned COMMENT 'Which type of phone does this number belongs.',
`im_provider_id` int unsigned COMMENT 'Which type of IM Provider does this name belong.',
`website_type_id` int unsigned COMMENT 'Which type of website does this site belong',
`relationship_type_id` int unsigned COMMENT 'Relationship type, if required',
`relationship_direction` varchar(6) ,
`grouping` int unsigned DEFAULT 1 COMMENT 'Used to group mapping_field records into related sets (e.g. for criteria sets in search builder\n mappings).\n ',
`operator` varchar(16) COMMENT 'SQL WHERE operator for search-builder mapping fields (search criteria).',
`value` varchar(255) COMMENT 'SQL WHERE value for search-builder mapping fields.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mapping_field_mapping_id FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping`(`id`) , CONSTRAINT FK_civicrm_mapping_field_location_type_id FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type`(`id`) , CONSTRAINT FK_civicrm_mapping_field_relationship_type_id FOREIGN KEY (`relationship_type_id`) REFERENCES `civicrm_relationship_type`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_menu
-- *
-- * Table to store menu items for all civicrm components.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_menu` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this menu item for',
`path` varchar(255) COMMENT 'Path Name',
`path_arguments` text COMMENT 'Arguments to pass to the url',
`title` varchar(255) ,
`access_callback` varchar(255) COMMENT 'Function to call to check access permissions',
`access_arguments` text COMMENT 'Arguments to pass to access callback',
`page_callback` varchar(255) COMMENT 'function to call for this url',
`page_arguments` text COMMENT 'Arguments to pass to page callback',
`breadcrumb` text COMMENT 'Breadcrumb for the path.',
`return_url` varchar(255) COMMENT 'Url where a page should redirected to, if next url not known.',
`return_url_args` varchar(255) COMMENT 'Arguments to pass to return_url',
`component_id` int unsigned COMMENT 'Component that this menu item belongs to',
`is_active` tinyint COMMENT 'Is this menu item active?',
`is_public` tinyint COMMENT 'Is this menu accessible to the public?',
`is_exposed` tinyint COMMENT 'Is this menu exposed to the navigation system?',
`is_ssl` tinyint COMMENT 'Should this menu be exposed via SSL if enabled?',
`weight` int NOT NULL DEFAULT 1 COMMENT 'Ordering of the menu items in various blocks.',
`type` int NOT NULL DEFAULT 1 COMMENT 'Drupal menu type.',
`page_type` int NOT NULL DEFAULT 1 COMMENT 'CiviCRM menu type.',
`skipBreadcrumb` tinyint COMMENT 'skip this url being exposed to breadcrumb',
`module_data` text COMMENT 'All other menu metadata not stored in other fields'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_path_domain_id`(
path
, domain_id
)
, CONSTRAINT FK_civicrm_menu_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) , CONSTRAINT FK_civicrm_menu_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_navigation
-- *
-- * Table to store navigation.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_navigation` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this navigation item for',
`label` varchar(255) COMMENT 'Navigation Title',
`name` varchar(255) COMMENT 'Internal Name',
`url` varchar(255) COMMENT 'url in case of custom navigation link',
`icon` varchar(255) NULL DEFAULT NULL COMMENT 'CSS class name for an icon',
`permission` varchar(255) COMMENT 'Permission for menu item',
`permission_operator` varchar(3) COMMENT 'Permission Operator',
`parent_id` int unsigned COMMENT 'Parent navigation item, used for grouping',
`is_active` tinyint COMMENT 'Is this navigation item active?',
`has_separator` tinyint COMMENT 'If separator needs to be added after this menu item',
`weight` int COMMENT 'Ordering of the navigation items in various blocks.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_navigation_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) , CONSTRAINT FK_civicrm_navigation_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_navigation`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_note
-- *
-- * Notes can be linked to any object in the application.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_note` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Note ID',
`entity_table` varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.',
`entity_id` int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
`note` text COMMENT 'Note and/or Comment.',
`contact_id` int unsigned COMMENT 'FK to Contact ID creator',
`modified_date` date COMMENT 'When was this note last modified/edited',
`subject` varchar(255) COMMENT 'subject of note description',
`privacy` varchar(255) COMMENT 'Foreign Key to Note Privacy Level (which is an option value pair and hence an implicit FK)'
,
PRIMARY KEY (`id`)
, INDEX `index_entity`(
entity_table
, entity_id
)
, CONSTRAINT FK_civicrm_note_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_option_value
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_value` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option ID',
`option_group_id` int unsigned NOT NULL COMMENT 'Group which this option belongs to.',
`label` varchar(512) NOT NULL COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
`value` varchar(512) NOT NULL COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
`name` varchar(255) COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.',
`grouping` varchar(255) COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
`filter` int unsigned COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
`is_default` tinyint DEFAULT 0 COMMENT 'Is this the default option for the group?',
`weight` int unsigned NOT NULL COMMENT 'Controls display sort order.',
`description` text COMMENT 'Optional description.',
`is_optgroup` tinyint DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
`is_reserved` tinyint DEFAULT 0 COMMENT 'Is this a predefined system object?',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this option active?',
`component_id` int unsigned COMMENT 'Component that this option value belongs/caters to.',
`domain_id` int unsigned COMMENT 'Which Domain is this option value for',
`visibility_id` int unsigned DEFAULT NULL ,
`icon` varchar(255) DEFAULT NULL COMMENT 'crm-i icon class',
`color` varchar(255) DEFAULT NULL COMMENT 'Hex color value e.g. #ffffff'
,
PRIMARY KEY (`id`)
, INDEX `index_option_group_id_value`(
value(128)
, option_group_id
)
, INDEX `index_option_group_id_name`(
name(128)
, option_group_id
)
, CONSTRAINT FK_civicrm_option_value_option_group_id FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_option_value_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`) , CONSTRAINT FK_civicrm_option_value_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_phone
-- *
-- * Phone information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_phone` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Phone ID',
`contact_id` int unsigned COMMENT 'FK to Contact ID',
`location_type_id` int unsigned COMMENT 'Which Location does this phone belong to.',
`is_primary` tinyint DEFAULT 0 COMMENT 'Is this the primary phone for this contact and location.',
`is_billing` tinyint DEFAULT 0 COMMENT 'Is this the billing?',
`mobile_provider_id` int unsigned COMMENT 'Which Mobile Provider does this phone belong to.',
`phone` varchar(32) COMMENT 'Complete phone number.',
`phone_ext` varchar(16) COMMENT 'Optional extension for a phone number.',
`phone_numeric` varchar(32) COMMENT 'Phone number stripped of all whitespace, letters, and punctuation.',
`phone_type_id` int unsigned COMMENT 'Which type of phone does this number belongs.'
,
PRIMARY KEY (`id`)
, INDEX `index_location_type`(
location_type_id
)
, INDEX `index_is_primary`(
is_primary
)
, INDEX `index_is_billing`(
is_billing
)
, INDEX `UI_mobile_provider_id`(
mobile_provider_id
)
, INDEX `index_phone_numeric`(
phone_numeric
)
, CONSTRAINT FK_civicrm_phone_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_state_province
-- *
-- *******************************************************/
CREATE TABLE `civicrm_state_province` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'State/Province ID',
`name` varchar(64) COMMENT 'Name of State/Province',
`abbreviation` varchar(4) COMMENT '2-4 Character Abbreviation of State/Province',
`country_id` int unsigned NOT NULL COMMENT 'ID of Country that State/Province belong'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name_country_id`(
name
, country_id
)
, CONSTRAINT FK_civicrm_state_province_country_id FOREIGN KEY (`country_id`) REFERENCES `civicrm_country`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_tag
-- *
-- * Provides support for flat or hierarchical classification of various types of entities (contacts, groups, actions...).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_tag` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Tag ID',
`name` varchar(64) NOT NULL COMMENT 'Name of Tag.',
`description` varchar(255) COMMENT 'Optional verbose description of the tag.',
`parent_id` int unsigned DEFAULT NULL COMMENT 'Optional parent id for this tag.',
`is_selectable` tinyint DEFAULT 1 COMMENT 'Is this tag selectable / displayed',
`is_reserved` tinyint DEFAULT 0 ,
`is_tagset` tinyint DEFAULT 0 ,
`used_for` varchar(64) DEFAULT NULL ,
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this tag',
`color` varchar(255) DEFAULT NULL COMMENT 'Hex color value e.g. #ffffff',
`created_date` datetime COMMENT 'Date and time that tag was created.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_tag_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_tag`(`id`) , CONSTRAINT FK_civicrm_tag_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_uf_match
-- *
-- * The mapping from an user framework (UF) object to a CRM object.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_uf_match` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'System generated ID.',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this match entry for',
`uf_id` int unsigned NOT NULL COMMENT 'UF ID',
`uf_name` varchar(128) COMMENT 'UF Name',
`contact_id` int unsigned COMMENT 'FK to Contact ID',
`language` varchar(5) COMMENT 'UI language preferred by the given user/contact'
,
PRIMARY KEY (`id`)
, INDEX `I_civicrm_uf_match_uf_id`(
uf_id
)
, UNIQUE INDEX `UI_uf_name_domain_id`(
uf_name
, domain_id
)
, UNIQUE INDEX `UI_contact_domain_id`(
contact_id
, domain_id
)
, CONSTRAINT FK_civicrm_uf_match_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) , CONSTRAINT FK_civicrm_uf_match_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_timezone
-- *
-- *******************************************************/
CREATE TABLE `civicrm_timezone` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Timezone Id',
`name` varchar(64) COMMENT 'Timezone full name',
`abbreviation` char(3) COMMENT 'ISO Code for timezone abbreviation',
`gmt` varchar(64) COMMENT 'GMT name of the timezone',
`offset` int ,
`country_id` int unsigned NOT NULL COMMENT 'Country Id'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_timezone_country_id FOREIGN KEY (`country_id`) REFERENCES `civicrm_country`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_openid
-- *
-- * OpenID information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_openid` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique OpenID ID',
`contact_id` int unsigned COMMENT 'FK to Contact ID',
`location_type_id` int unsigned COMMENT 'Which Location does this email belong to.',
`openid` varchar(255) COMMENT 'the OpenID (or OpenID-style http://username.domain/) unique identifier for this contact mainly used for logging in to CiviCRM',
`allowed_to_login` tinyint NOT NULL DEFAULT 0 COMMENT 'Whether or not this user is allowed to login',
`is_primary` tinyint DEFAULT 0 COMMENT 'Is this the primary email for this contact and location.'
,
PRIMARY KEY (`id`)
, INDEX `index_location_type`(
location_type_id
)
, UNIQUE INDEX `UI_openid`(
openid
)
, CONSTRAINT FK_civicrm_openid_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_website
-- *
-- * Website information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_website` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Website ID',
`contact_id` int unsigned COMMENT 'FK to Contact ID',
`url` varchar(128) COMMENT 'Website',
`website_type_id` int unsigned COMMENT 'Which Website type does this website belong to.'
,
PRIMARY KEY (`id`)
, INDEX `UI_website_type_id`(
website_type_id
)
, CONSTRAINT FK_civicrm_website_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_setting
-- *
-- * Table to store civicrm settings for civicrm core and components.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_setting` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar(255) COMMENT 'Unique name for setting',
`value` text COMMENT 'data associated with this group / name combo',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this menu item for',
`contact_id` int unsigned COMMENT 'FK to Contact ID if the setting is localized to a contact',
`is_domain` tinyint COMMENT 'Is this setting a contact specific or site wide setting?',
`component_id` int unsigned COMMENT 'Component that this menu item belongs to',
`created_date` datetime COMMENT 'When was the setting created',
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this setting'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `index_domain_contact_name`(
domain_id
, contact_id
, name
)
, CONSTRAINT FK_civicrm_setting_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_setting_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_setting_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`) , CONSTRAINT FK_civicrm_setting_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_print_label
-- *
-- * Table to store the labels created by user.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_print_label` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`title` varchar(255) COMMENT 'User title for for this label layout',
`name` varchar(255) COMMENT 'variable name/programmatic handle for this field.',
`description` text COMMENT 'Description of this label layout',
`label_format_name` varchar(255) COMMENT 'This refers to name column of civicrm_option_value row in name_badge option group',
`label_type_id` int unsigned COMMENT 'Implicit FK to civicrm_option_value row in NEW label_type option group',
`data` longtext COMMENT 'contains json encode configurations options',
`is_default` tinyint DEFAULT 1 COMMENT 'Is this default?',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this option active?',
`is_reserved` tinyint DEFAULT 1 COMMENT 'Is this reserved label?',
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this label layout'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_print_label_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_word_replacement
-- *
-- * Top-level hierarchy to support word replacement.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_word_replacement` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Word replacement ID',
`find_word` varchar(255) COLLATE utf8_bin COMMENT 'Word which need to be replaced',
`replace_word` varchar(255) COLLATE utf8_bin COMMENT 'Word which will replace the word in find',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this entry active?',
`match_type` varchar(16) DEFAULT "wildcardMatch" ,
`domain_id` int unsigned COMMENT 'FK to Domain ID. This is for Domain specific word replacement'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_domain_find`(
domain_id
, find_word
)
, CONSTRAINT FK_civicrm_word_replacement_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_status_pref
-- *
-- * Preferences controlling status checks called in system.check.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_status_pref` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Status Preference ID',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this Status Preference for',
`name` varchar(255) NOT NULL COMMENT 'Name of the status check this preference references.',
`hush_until` date DEFAULT NULL COMMENT 'expires ignore_severity. NULL never hushes.',
`ignore_severity` int unsigned DEFAULT 1 COMMENT 'Hush messages up to and including this severity.',
`prefs` varchar(255) COMMENT 'These settings are per-check, and can\'t be compared across checks.',
`check_info` varchar(255) COMMENT 'These values are per-check, and can\'t be compared across checks.'
,
PRIMARY KEY (`id`)
, INDEX `UI_status_pref_name`(
name
)
, CONSTRAINT FK_civicrm_status_pref_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_acl_cache
-- *
-- * Cache for acls and contacts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_acl_cache` (
`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` timestamp NULL COMMENT 'When was this cache entry last modified'
,
PRIMARY KEY (`id`)
, INDEX `index_acl_id`(
acl_id
)
, CONSTRAINT FK_civicrm_acl_cache_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_acl_cache_acl_id FOREIGN KEY (`acl_id`) REFERENCES `civicrm_acl`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_group
-- *
-- * Provide grouping of related contacts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Group ID',
`name` varchar(64) COMMENT 'Internal name of Group.',
`title` varchar(64) COMMENT 'Name of Group.',
`description` text COMMENT 'Optional verbose description of the group.',
`source` varchar(64) COMMENT 'Module or process which created this group.',
`saved_search_id` int unsigned COMMENT 'FK to saved search table.',
`is_active` tinyint COMMENT 'Is this entry active?',
`visibility` varchar(24) DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.',
`where_clause` text COMMENT 'the sql where clause if a saved search acl',
`select_tables` text COMMENT 'the tables to be included in a select data',
`where_tables` text COMMENT 'the tables to be included in the count statement',
`group_type` varchar(128) COMMENT 'FK to group type',
`cache_date` timestamp NULL COMMENT 'Date when we created the cache for a smart group',
`refresh_date` timestamp NULL COMMENT 'Date and time when we need to refresh the cache next.',
`parents` text COMMENT 'IDs of the parent(s)',
`children` text COMMENT 'IDs of the child(ren)',
`is_hidden` tinyint DEFAULT 0 COMMENT 'Is this group hidden?',
`is_reserved` tinyint DEFAULT 0 ,
`created_id` int unsigned COMMENT 'FK to contact table.',
`modified_id` int unsigned COMMENT 'FK to contact table.'
,
PRIMARY KEY (`id`)
, INDEX `index_group_type`(
group_type
)
, UNIQUE INDEX `UI_title`(
title
)
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_group_saved_search_id FOREIGN KEY (`saved_search_id`) REFERENCES `civicrm_saved_search`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_group_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_group_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_subscription_history
-- *
-- * History information of subscribe/unsubscribe actions
-- *
-- *******************************************************/
CREATE TABLE `civicrm_subscription_history` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Internal Id',
`contact_id` int unsigned NOT NULL COMMENT 'Contact Id',
`group_id` int unsigned COMMENT 'Group Id',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date of the (un)subscription',
`method` varchar(8) COMMENT 'How the (un)subscription was triggered',
`status` varchar(8) COMMENT 'The state of the contact within the group',
`tracking` varchar(255) COMMENT 'IP address or other tracking info'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_subscription_history_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_subscription_history_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_group_contact_cache
-- *
-- * Join table cache for 'static' groups.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group_contact_cache` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`group_id` int unsigned NOT NULL COMMENT 'FK to civicrm_group',
`contact_id` int unsigned NOT NULL COMMENT 'FK to civicrm_contact'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_contact_group`(
contact_id
, group_id
)
, CONSTRAINT FK_civicrm_group_contact_cache_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_contact_cache_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_group_nesting
-- *
-- * Provide parent-child relationships for groups
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group_nesting` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relationship ID',
`child_group_id` int unsigned NOT NULL COMMENT 'ID of the child group',
`parent_group_id` int unsigned NOT NULL COMMENT 'ID of the parent group'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_group_nesting_child_group_id FOREIGN KEY (`child_group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_nesting_parent_group_id FOREIGN KEY (`parent_group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_group_organization
-- *
-- * Integrate Organization information into Groups
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group_organization` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relationship ID',
`group_id` int unsigned NOT NULL COMMENT 'ID of the group',
`organization_id` int unsigned NOT NULL COMMENT 'ID of the Organization Contact'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_group_organization`(
group_id
, organization_id
)
, CONSTRAINT FK_civicrm_group_organization_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_organization_organization_id FOREIGN KEY (`organization_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_subscribe
-- *
-- * Tracks when a (new) contact subscribes to a group by email
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_subscribe` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`group_id` int unsigned NOT NULL COMMENT 'FK to Group',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact',
`hash` varchar(255) NOT NULL COMMENT 'Security hash',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this subscription event occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_subscribe_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_subscribe_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_confirm
-- *
-- * Tracks when a subscription event is confirmed by email
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_confirm` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`event_subscribe_id` int unsigned NOT NULL COMMENT 'FK to civicrm_mailing_event_subscribe',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this confirmation event occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_confirm_event_subscribe_id FOREIGN KEY (`event_subscribe_id`) REFERENCES `civicrm_mailing_event_subscribe`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contribution_page
-- *
-- * A Contribution object store meta information about a single customized contribution page
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_page` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution Id',
`title` varchar(255) COMMENT 'Contribution Page title. For top of page display',
`intro_text` text COMMENT 'Text and html allowed. Displayed below title.',
`financial_type_id` int unsigned COMMENT 'default financial type assigned to contributions submitted via this page, e.g. Contribution, Campaign Contribution',
`payment_processor` varchar(128) COMMENT 'Payment Processors configured for this contribution Page',
`is_credit_card_only` tinyint DEFAULT 0 COMMENT 'if true - processing logic must reject transaction at confirmation stage if pay method != credit card',
`is_monetary` tinyint DEFAULT 1 COMMENT 'if true - allows real-time monetary transactions otherwise non-monetary transactions',
`is_recur` tinyint DEFAULT 0 COMMENT 'if true - allows recurring contributions, valid only for PayPal_Standard',
`is_confirm_enabled` tinyint DEFAULT 1 COMMENT 'if false, the confirm page in contribution pages gets skipped',
`recur_frequency_unit` varchar(128) COMMENT 'Supported recurring frequency units.',
`is_recur_interval` tinyint DEFAULT 0 COMMENT 'if true - supports recurring intervals',
`is_recur_installments` tinyint DEFAULT 0 COMMENT 'if true - asks user for recurring installments',
`adjust_recur_start_date` tinyint DEFAULT 0 COMMENT 'if true - user is able to adjust payment start date',
`is_pay_later` tinyint DEFAULT 0 COMMENT 'if true - allows the user to send payment directly to the org later',
`pay_later_text` text COMMENT 'The text displayed to the user in the main form',
`pay_later_receipt` text COMMENT 'The receipt sent to the user instead of the normal receipt text',
`is_partial_payment` tinyint DEFAULT 0 COMMENT 'is partial payment enabled for this online contribution page',
`initial_amount_label` varchar(255) COMMENT 'Initial amount label for partial payment',
`initial_amount_help_text` text COMMENT 'Initial amount help text for partial payment',
`min_initial_amount` decimal(20,2) COMMENT 'Minimum initial amount for partial payment',
`is_allow_other_amount` tinyint DEFAULT 0 COMMENT 'if true, page will include an input text field where user can enter their own amount',
`default_amount_id` int unsigned COMMENT 'FK to civicrm_option_value.',
`min_amount` decimal(20,2) COMMENT 'if other amounts allowed, user can configure minimum allowed.',
`max_amount` decimal(20,2) COMMENT 'if other amounts allowed, user can configure maximum allowed.',
`goal_amount` decimal(20,2) COMMENT 'The target goal for this page, allows people to build a goal meter',
`thankyou_title` varchar(255) COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).',
`thankyou_text` text COMMENT 'text and html allowed. displayed above result on success page',
`thankyou_footer` text COMMENT 'Text and html allowed. displayed at the bottom of the success page. Common usage is to include link(s) to other pages such as tell-a-friend, etc.',
`is_email_receipt` tinyint DEFAULT 0 COMMENT 'if true, receipt is automatically emailed to contact on success',
`receipt_from_name` varchar(255) COMMENT 'FROM email name used for receipts generated by contributions to this contribution page.',
`receipt_from_email` varchar(255) COMMENT 'FROM email address used for receipts generated by contributions to this contribution page.',
`cc_receipt` varchar(255) COMMENT 'comma-separated list of email addresses to cc each time a receipt is sent',
`bcc_receipt` varchar(255) COMMENT 'comma-separated list of email addresses to bcc each time a receipt is sent',
`receipt_text` text COMMENT 'text to include above standard receipt info on receipt email. emails are text-only, so do not allow html for now',
`is_active` tinyint COMMENT 'Is this property active?',
`footer_text` text COMMENT 'Text and html allowed. Displayed at the bottom of the first page of the contribution wizard.',
`amount_block_is_active` tinyint DEFAULT 1 COMMENT 'Is this property active?',
`start_date` datetime COMMENT 'Date and time that this page starts.',
`end_date` datetime COMMENT 'Date and time that this page ends. May be NULL if no defined end date/time',
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this contribution page',
`created_date` datetime COMMENT 'Date and time that contribution page was created.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`campaign_id` int unsigned COMMENT 'The campaign for which we are collecting contributions with this page.',
`is_share` tinyint DEFAULT 1 COMMENT 'Can people share the contribution page through social media?',
`is_billing_required` tinyint DEFAULT 0 COMMENT 'if true - billing block is required for online contribution page'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_contribution_page_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) , CONSTRAINT FK_civicrm_contribution_page_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_page_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_product
-- *
-- * able - stores "product info" for premiums and can be used for non-incentive products
-- *
-- *******************************************************/
CREATE TABLE `civicrm_product` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar(255) NOT NULL COMMENT 'Required product/premium name',
`description` text COMMENT 'Optional description of the product/premium.',
`sku` varchar(50) COMMENT 'Optional product sku or code.',
`options` text COMMENT 'Store comma-delimited list of color, size, etc. options for the product.',
`image` varchar(255) COMMENT 'Full or relative URL to uploaded image - fullsize.',
`thumbnail` varchar(255) COMMENT 'Full or relative URL to image thumbnail.',
`price` decimal(20,2) COMMENT 'Sell price or market value for premiums. For tax-deductible contributions, this will be stored as non_deductible_amount in the contribution record.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
`min_contribution` decimal(20,2) COMMENT 'Minimum contribution required to be eligible to select this premium.',
`cost` decimal(20,2) COMMENT 'Actual cost of this product. Useful to determine net return from sale or using this as an incentive.',
`is_active` tinyint NOT NULL COMMENT 'Disabling premium removes it from the premiums_premium join table below.',
`period_type` varchar(8) DEFAULT 'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month\n (e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) ',
`fixed_period_start_day` int DEFAULT 0101 COMMENT 'Month and day (MMDD) that fixed period type subscription or membership starts.',
`duration_unit` varchar(8) DEFAULT 'year' ,
`duration_interval` int COMMENT 'Number of units for total duration of subscription, service, membership (e.g. 12 Months).',
`frequency_unit` varchar(8) DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.',
`frequency_interval` int COMMENT 'Number of units for delivery frequency of subscription, service, membership (e.g. every 3 Months).'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_premiums_product
-- *
-- * joins premiums (settings) to individual product/premium items - determines which products are available for a given contribution page
-- *
-- *******************************************************/
CREATE TABLE `civicrm_premiums_product` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution ID',
`premiums_id` int unsigned NOT NULL COMMENT 'Foreign key to premiums settings record.',
`product_id` int unsigned NOT NULL COMMENT 'Foreign key to each product object.',
`weight` int unsigned NOT NULL ,
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_premiums_product_premiums_id FOREIGN KEY (`premiums_id`) REFERENCES `civicrm_premiums`(`id`) , CONSTRAINT FK_civicrm_premiums_product_product_id FOREIGN KEY (`product_id`) REFERENCES `civicrm_product`(`id`) , CONSTRAINT FK_civicrm_premiums_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contribution_widget
-- *
-- * A Widget object to store meta information about a single customized contribution widget
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_widget` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution Id',
`contribution_page_id` int unsigned COMMENT 'The Contribution Page which triggered this contribution',
`is_active` tinyint COMMENT 'Is this property active?',
`title` varchar(255) COMMENT 'Widget title.',
`url_logo` varchar(255) COMMENT 'URL to Widget logo',
`button_title` varchar(255) COMMENT 'Button title.',
`about` text COMMENT 'About description.',
`url_homepage` varchar(255) COMMENT 'URL to Homepage.',
`color_title` varchar(10) ,
`color_button` varchar(10) ,
`color_bar` varchar(10) ,
`color_main_text` varchar(10) ,
`color_main` varchar(10) ,
`color_main_bg` varchar(10) ,
`color_bg` varchar(10) ,
`color_about_link` varchar(10) ,
`color_homepage_link` varchar(10)
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_contribution_widget_contribution_page_id FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_payment_processor
-- *
-- *******************************************************/
CREATE TABLE `civicrm_payment_processor` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Payment Processor ID',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this match entry for',
`name` varchar(64) COMMENT 'Payment Processor Name.',
`description` varchar(255) COMMENT 'Payment Processor Description.',
`payment_processor_type_id` int unsigned ,
`is_active` tinyint COMMENT 'Is this processor active?',
`is_default` tinyint COMMENT 'Is this processor the default?',
`is_test` tinyint COMMENT 'Is this processor for a test site?',
`user_name` varchar(255) ,
`password` varchar(255) ,
`signature` text ,
`url_site` varchar(255) ,
`url_api` varchar(255) ,
`url_recur` varchar(255) ,
`url_button` varchar(255) ,
`subject` varchar(255) ,
`class_name` varchar(255) ,
`billing_mode` int unsigned NOT NULL COMMENT 'Billing Mode (deprecated)',
`is_recur` tinyint COMMENT 'Can process recurring contributions',
`payment_type` int unsigned DEFAULT 1 COMMENT 'Payment Type: Credit or Debit (deprecated)',
`payment_instrument_id` int unsigned DEFAULT 1 COMMENT 'Payment Instrument ID',
`accepted_credit_cards` text DEFAULT NULL COMMENT 'array of accepted credit card types'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name_test_domain_id`(
name
, is_test
, domain_id
)
, CONSTRAINT FK_civicrm_payment_processor_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) , CONSTRAINT FK_civicrm_payment_processor_payment_processor_type_id FOREIGN KEY (`payment_processor_type_id`) REFERENCES `civicrm_payment_processor_type`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_payment_token
-- *
-- * Payment Token
-- *
-- *******************************************************/
CREATE TABLE `civicrm_payment_token` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Payment Token ID',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact ID for the owner of the token',
`payment_processor_id` int unsigned NOT NULL ,
`token` varchar(255) NOT NULL COMMENT 'Externally provided token string',
`created_date` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'Date created',
`created_id` int unsigned COMMENT 'Contact ID of token creator',
`expiry_date` datetime COMMENT 'Date this token expires',
`email` varchar(255) COMMENT 'Email at the time of token creation. Useful for fraud forensics',
`billing_first_name` varchar(255) COMMENT 'Billing first name at the time of token creation. Useful for fraud forensics',
`billing_middle_name` varchar(255) COMMENT 'Billing middle name at the time of token creation. Useful for fraud forensics',
`billing_last_name` varchar(255) COMMENT 'Billing last name at the time of token creation. Useful for fraud forensics',
`masked_account_number` varchar(255) COMMENT 'Holds the part of the card number or account details that may be retained or displayed',
`ip_address` varchar(255) COMMENT 'IP used when creating the token. Useful for fraud forensics'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_payment_token_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_payment_token_payment_processor_id FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor`(`id`) ON DELETE RESTRICT, CONSTRAINT FK_civicrm_payment_token_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_sms_provider
-- *
-- * Table to add different sms providers
-- *
-- *******************************************************/
CREATE TABLE `civicrm_sms_provider` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'SMS Provider ID',
`name` varchar(64) COMMENT 'Provider internal name points to option_value of option_group sms_provider_name',
`title` varchar(64) COMMENT 'Provider name visible to user',
`username` varchar(255) ,
`password` varchar(255) ,
`api_type` int unsigned NOT NULL COMMENT 'points to value in civicrm_option_value for group sms_api_type',
`api_url` varchar(128) ,
`api_params` text COMMENT 'the api params in xml, http or smtp format',
`is_default` tinyint DEFAULT 0 ,
`is_active` tinyint DEFAULT 0 ,
`domain_id` int unsigned COMMENT 'Which Domain is this sms provider for'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_sms_provider_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_membership_type
-- *
-- * 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.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this match entry for',
`name` varchar(128) COMMENT 'Name of Membership Type',
`description` varchar(255) COMMENT 'Description of Membership Type',
`member_of_contact_id` int unsigned NOT NULL COMMENT 'Owner organization for this membership type. FK to Contact ID',
`financial_type_id` int unsigned NOT NULL COMMENT 'If membership is paid by a contribution - what financial type should be used. FK to civicrm_financial_type.id',
`minimum_fee` decimal(18,9) DEFAULT 0 COMMENT 'Minimum fee for this membership (0 for free/complimentary memberships).',
`duration_unit` varchar(8) COMMENT 'Unit in which membership period is expressed.',
`duration_interval` int COMMENT 'Number of duration units in membership period (e.g. 1 year, 12 months).',
`period_type` varchar(8) COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.',
`fixed_period_start_day` int COMMENT 'For fixed period memberships, month and day (mmdd) on which subscription/membership will start. Period start is back-dated unless after rollover day.',
`fixed_period_rollover_day` int COMMENT 'For fixed period memberships, signups after this day (mmdd) rollover to next period.',
`relationship_type_id` varchar(64) COMMENT 'FK to Relationship Type ID',
`relationship_direction` varchar(128) ,
`max_related` int COMMENT 'Maximum number of related memberships.',
`visibility` varchar(64) ,
`weight` int ,
`receipt_text_signup` varchar(255) COMMENT 'Receipt Text for membership signup',
`receipt_text_renewal` varchar(255) COMMENT 'Receipt Text for membership renewal',
`auto_renew` tinyint DEFAULT 0 COMMENT '0 = No auto-renew option; 1 = Give option, but not required; 2 = Auto-renew required;',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this membership_type enabled'
,
PRIMARY KEY (`id`)
, INDEX `index_relationship_type_id`(
relationship_type_id
)
, CONSTRAINT FK_civicrm_membership_type_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) , CONSTRAINT FK_civicrm_membership_type_member_of_contact_id FOREIGN KEY (`member_of_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE RESTRICT, CONSTRAINT FK_civicrm_membership_type_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_membership_block
-- *
-- * A Membership Block stores admin configurable status options and rules
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_block` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
`entity_table` varchar(64) COMMENT 'Name for Membership Status',
`entity_id` int unsigned NOT NULL COMMENT 'FK to civicrm_contribution_page.id',
`membership_types` varchar(1024) COMMENT 'Membership types to be exposed by this block',
`membership_type_default` int unsigned COMMENT 'Optional foreign key to membership_type',
`display_min_fee` tinyint DEFAULT 1 COMMENT 'Display minimum membership fee',
`is_separate_payment` tinyint DEFAULT 1 COMMENT 'Should membership transactions be processed separately',
`new_title` varchar(255) COMMENT 'Title to display at top of block',
`new_text` text COMMENT 'Text to display below title',
`renewal_title` varchar(255) COMMENT 'Title for renewal',
`renewal_text` text COMMENT 'Text to display for member renewal',
`is_required` tinyint DEFAULT 0 COMMENT 'Is membership sign up optional',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this membership_block enabled'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_membership_block_entity_id FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contribution_page`(`id`) , CONSTRAINT FK_civicrm_membership_block_membership_type_default FOREIGN KEY (`membership_type_default`) REFERENCES `civicrm_membership_type`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_case
-- *
-- * This table stores information about cases grouping activities.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_case` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Case ID',
`case_type_id` int unsigned NOT NULL COMMENT 'FK to civicrm_case_type.id',
`subject` varchar(128) COMMENT 'Short name of the case.',
`start_date` date COMMENT 'Date on which given case starts.',
`end_date` date COMMENT 'Date on which given case ends.',
`details` text COMMENT 'Details about the meeting (agenda, notes, etc).',
`status_id` int unsigned NOT NULL COMMENT 'Id of case status.',
`is_deleted` tinyint DEFAULT 0 ,
`created_date` timestamp NULL DEFAULT NULL COMMENT 'When was the case was created.',
`modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the case (or closely related entity) was created or modified or deleted.'
,
PRIMARY KEY (`id`)
, INDEX `index_case_type_id`(
case_type_id
)
, INDEX `index_is_deleted`(
is_deleted
)
, CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (`case_type_id`) REFERENCES `civicrm_case_type`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_case_contact
-- *
-- * Joining table for case-contact associations.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_case_contact` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique case-contact association id',
`case_id` int unsigned NOT NULL COMMENT 'Case ID of case-contact association.',
`contact_id` int unsigned NOT NULL COMMENT 'Contact ID of contact record given case belongs to.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_case_contact_id`(
case_id
, contact_id
)
, CONSTRAINT FK_civicrm_case_contact_case_id FOREIGN KEY (`case_id`) REFERENCES `civicrm_case`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_case_contact_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_pledge
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pledge` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Pledge ID',
`contact_id` int unsigned NOT NULL COMMENT 'Foreign key to civicrm_contact.id .',
`financial_type_id` int unsigned COMMENT 'FK to Financial Type',
`contribution_page_id` int unsigned COMMENT 'The Contribution Page which triggered this contribution',
`amount` decimal(20,2) NOT NULL COMMENT 'Total pledged amount.',
`original_installment_amount` decimal(20,2) NOT NULL COMMENT 'Original amount for each of the installments.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`frequency_unit` varchar(8) DEFAULT 'month' COMMENT 'Time units for recurrence of pledge payments.',
`frequency_interval` int unsigned NOT NULL DEFAULT 1 COMMENT 'Number of time units for recurrence of pledge payments.',
`frequency_day` int unsigned NOT NULL DEFAULT 3 COMMENT 'Day in the period when the pledge payment is due e.g. 1st of month, 15th etc. Use this to set the scheduled dates for pledge payments.',
`installments` int unsigned DEFAULT 1 COMMENT 'Total number of payments to be made.',
`start_date` datetime NOT NULL COMMENT 'The date the first scheduled pledge occurs.',
`create_date` datetime NOT NULL COMMENT 'When this pledge record was created.',
`acknowledge_date` datetime COMMENT 'When a pledge acknowledgement message was sent to the contributor.',
`modified_date` datetime COMMENT 'Last updated date for this pledge record.',
`cancel_date` datetime COMMENT 'Date this pledge was cancelled by contributor.',
`end_date` datetime COMMENT 'Date this pledge finished successfully (total pledge payments equal to or greater than pledged amount).',
`max_reminders` int unsigned DEFAULT 1 COMMENT 'The maximum number of payment reminders to send for any given payment.',
`initial_reminder_day` int unsigned DEFAULT 5 COMMENT 'Send initial reminder this many days prior to the payment due date.',
`additional_reminder_day` int unsigned DEFAULT 5 COMMENT 'Send additional reminder this many days after last one sent, up to maximum number of reminders.',
`status_id` int unsigned COMMENT 'Implicit foreign key to civicrm_option_values in the pledge_status option group.',
`is_test` tinyint DEFAULT 0 ,
`campaign_id` int unsigned COMMENT 'The campaign for which this pledge has been initiated.'
,
PRIMARY KEY (`id`)
, INDEX `index_status`(
status_id
)
, CONSTRAINT FK_civicrm_pledge_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_pledge_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) , CONSTRAINT FK_civicrm_pledge_contribution_page_id FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_pledge_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_report_instance
-- *
-- * Users can save their report instance and put in a cron tab etc.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_report_instance` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Report Instance ID',
`domain_id` int unsigned NOT NULL COMMENT 'Which Domain is this instance for',
`title` varchar(255) COMMENT 'Report Instance Title.',
`report_id` varchar(512) NOT NULL COMMENT 'FK to civicrm_option_value for the report template',
`name` varchar(255) COMMENT 'when combined with report_id/template uniquely identifies the instance',
`args` varchar(255) COMMENT 'arguments that are passed in the url when invoking the instance',
`description` varchar(255) COMMENT 'Report Instance description.',
`permission` varchar(255) COMMENT 'permission required to be able to run this instance',
`grouprole` varchar(1024) COMMENT 'role required to be able to run this instance',
`form_values` text COMMENT 'Submitted form values for this report',
`is_active` tinyint COMMENT 'Is this entry active?',
`created_id` int unsigned COMMENT 'FK to contact table.',
`owner_id` int unsigned COMMENT 'FK to contact table.',
`email_subject` varchar(255) COMMENT 'Subject of email',
`email_to` text COMMENT 'comma-separated list of email addresses to send the report to',
`email_cc` text COMMENT 'comma-separated list of email addresses to send the report to',
`header` text COMMENT 'comma-separated list of email addresses to send the report to',
`footer` text COMMENT 'comma-separated list of email addresses to send the report to',
`navigation_id` int unsigned COMMENT 'FK to navigation ID',
`drilldown_id` int unsigned COMMENT 'FK to instance ID drilldown to',
`is_reserved` tinyint DEFAULT 0
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_report_instance_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) , CONSTRAINT FK_civicrm_report_instance_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_report_instance_owner_id FOREIGN KEY (`owner_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_report_instance_navigation_id FOREIGN KEY (`navigation_id`) REFERENCES `civicrm_navigation`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_report_instance_drilldown_id FOREIGN KEY (`drilldown_id`) REFERENCES `civicrm_report_instance`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_price_set
-- *
-- *******************************************************/
CREATE TABLE `civicrm_price_set` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Set',
`domain_id` int unsigned COMMENT 'Which Domain is this price-set for',
`name` varchar(255) NOT NULL COMMENT 'Variable name/programmatic handle for this set of price fields.',
`title` varchar(255) NOT NULL COMMENT 'Displayed title for the Price Set.',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this price set active',
`help_pre` text COMMENT 'Description and/or help text to display before fields in form.',
`help_post` text COMMENT 'Description and/or help text to display after fields in form.',
`javascript` varchar(64) COMMENT 'Optional Javascript script function(s) included on the form with this price_set. Can be used for conditional',
`extends` varchar(255) NOT NULL COMMENT 'What components are using this price set?',
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type(for membership price sets only).',
`is_quick_config` tinyint DEFAULT 0 COMMENT 'Is set if edited on Contribution or Event Page rather than through Manage Price Sets',
`is_reserved` tinyint DEFAULT 0 COMMENT 'Is this a predefined system price set (i.e. it can not be deleted, edited)?',
`min_amount` int unsigned DEFAULT 0 COMMENT 'Minimum Amount required for this set.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_price_set_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) , CONSTRAINT FK_civicrm_price_set_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_price_set_entity
-- *
-- *******************************************************/
CREATE TABLE `civicrm_price_set_entity` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Set Entity',
`entity_table` varchar(64) NOT NULL COMMENT 'Table which uses this price set',
`entity_id` int unsigned NOT NULL COMMENT 'Item in table',
`price_set_id` int unsigned NOT NULL COMMENT 'price set being used'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_entity`(
entity_table
, entity_id
)
, CONSTRAINT FK_civicrm_price_set_entity_price_set_id FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_county
-- *
-- *******************************************************/
CREATE TABLE `civicrm_county` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'County ID',
`name` varchar(64) COMMENT 'Name of County',
`abbreviation` varchar(4) COMMENT '2-4 Character Abbreviation of County',
`state_province_id` int unsigned NOT NULL COMMENT 'ID of State/Province that County belongs'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name_state_id`(
name
, state_province_id
)
, CONSTRAINT FK_civicrm_county_state_province_id FOREIGN KEY (`state_province_id`) REFERENCES `civicrm_state_province`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_dashboard
-- *
-- * Table to store dashboard.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dashboard` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`domain_id` int unsigned NOT NULL COMMENT 'Domain for dashboard',
`name` varchar(64) COMMENT 'Internal name of dashlet.',
`label` varchar(255) COMMENT 'dashlet title',
`url` varchar(255) COMMENT 'url in case of external dashlet',
`permission` varchar(255) COMMENT 'Permission for the dashlet',
`permission_operator` varchar(3) COMMENT 'Permission Operator',
`fullscreen_url` varchar(255) COMMENT 'fullscreen url for dashlet',
`is_active` tinyint DEFAULT 0 COMMENT 'Is this dashlet active?',
`is_reserved` tinyint DEFAULT 0 COMMENT 'Is this dashlet reserved?',
`cache_minutes` int unsigned NOT NULL DEFAULT 60 COMMENT 'Number of minutes to cache dashlet content in browser localStorage.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_dashboard_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_discount
-- *
-- * Stores discounts for events on the basis of date
-- *
-- *******************************************************/
CREATE TABLE `civicrm_discount` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`entity_table` varchar(64) COMMENT 'physical tablename for entity being joined to discount, e.g. civicrm_event',
`entity_id` int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
`price_set_id` int unsigned NOT NULL COMMENT 'FK to civicrm_price_set',
`start_date` date COMMENT 'Date when discount starts.',
`end_date` date COMMENT 'Date when discount ends.'
,
PRIMARY KEY (`id`)
, INDEX `index_entity`(
entity_table
, entity_id
)
, INDEX `index_entity_option_id`(
entity_table
, entity_id
, price_set_id
)
, CONSTRAINT FK_civicrm_discount_price_set_id FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_entity_tag
-- *
-- * Tag entities (Contacts, Groups, Actions) to categories.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_tag` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`entity_table` varchar(64) COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
`entity_id` int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
`tag_id` int unsigned NOT NULL COMMENT 'FK to civicrm_tag'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_entity_id_entity_table_tag_id`(
entity_id
, entity_table
, tag_id
)
, CONSTRAINT FK_civicrm_entity_tag_tag_id FOREIGN KEY (`tag_id`) REFERENCES `civicrm_tag`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_uf_group
-- *
-- * User framework groups. Each group represents a form which encompasses a set of fields defined in civicrm_uf_fields table. Initially will be used for CiviCRM Profile form(s). Subsequently we anticipate using this to define other public facing forms (e.g. online donation solicitation forms, mailing list preferences, etc.).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_uf_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this form currently active? If false, hide all related fields for all sharing contexts.',
`group_type` varchar(255) COMMENT 'This column will store a comma separated list of the type(s) of profile fields.',
`title` varchar(64) NOT NULL COMMENT 'Form title.',
`description` text COMMENT 'Optional verbose description of the profile.',
`help_pre` text COMMENT 'Description and/or help text to display before fields in form.',
`help_post` text COMMENT 'Description and/or help text to display after fields in form.',
`limit_listings_group_id` int unsigned COMMENT 'Group id, foreign key from civicrm_group',
`post_URL` varchar(255) COMMENT 'Redirect to URL.',
`add_to_group_id` int unsigned COMMENT 'foreign key to civicrm_group_id',
`add_captcha` tinyint DEFAULT 0 COMMENT 'Should a CAPTCHA widget be included this Profile form.',
`is_map` tinyint DEFAULT 0 COMMENT 'Do we want to map results from this profile.',
`is_edit_link` tinyint DEFAULT 0 COMMENT 'Should edit link display in profile selector',
`is_uf_link` tinyint DEFAULT 0 COMMENT 'Should we display a link to the website profile in profile selector',
`is_update_dupe` tinyint DEFAULT 0 COMMENT 'Should we update the contact record if we find a duplicate',
`cancel_URL` varchar(255) COMMENT 'Redirect to URL when Cancle button clik .',
`is_cms_user` tinyint DEFAULT 0 COMMENT 'Should we create a cms user for this profile ',
`notify` text ,
`is_reserved` tinyint COMMENT 'Is this group reserved for use by some other CiviCRM functionality?',
`name` varchar(64) COMMENT 'Name of the UF group for directly addressing it in the codebase',
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this UF group',
`created_date` datetime COMMENT 'Date and time this UF group was created.',
`is_proximity_search` tinyint DEFAULT 0 COMMENT 'Should we include proximity search feature in this profile search form?',
`cancel_button_text` varchar(64) DEFAULT NULL COMMENT 'Custom Text to display on the Cancel button when used in create or edit mode',
`submit_button_text` varchar(64) DEFAULT NULL COMMENT 'Custom Text to display on the submit button on profile edit/create screens'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_uf_group_limit_listings_group_id FOREIGN KEY (`limit_listings_group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_uf_group_add_to_group_id FOREIGN KEY (`add_to_group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_uf_group_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_uf_field
-- *
-- * User Framework fields and their properties.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_uf_field` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
`uf_group_id` int unsigned NOT NULL COMMENT 'Which form does this field belong to.',
`field_name` varchar(64) NOT NULL COMMENT 'Name for CiviCRM field which is being exposed for sharing.',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this field currently shareable? If false, hide the field for all sharing contexts.',
`is_view` tinyint DEFAULT 0 COMMENT 'the field is view only and not editable in user forms.',
`is_required` tinyint DEFAULT 0 COMMENT 'Is this field required when included in a user or registration form?',
`weight` int NOT NULL DEFAULT 1 COMMENT 'Controls field display order when user framework fields are displayed in registration and account editing forms.',
`help_post` text COMMENT 'Description and/or help text to display after this field.',
`help_pre` text COMMENT 'Description and/or help text to display before this field.',
`visibility` varchar(32) DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.',
`in_selector` tinyint DEFAULT 0 COMMENT 'Is this field included as a column in the selector table?',
`is_searchable` tinyint DEFAULT 0 COMMENT 'Is this field included search form of profile?',
`location_type_id` int unsigned COMMENT 'Location type of this mapping, if required',
`phone_type_id` int unsigned COMMENT 'Phone Type Id, if required',
`website_type_id` int unsigned COMMENT 'Website Type Id, if required',
`label` varchar(255) NOT NULL COMMENT 'To save label for fields.',
`field_type` varchar(255) COMMENT 'This field saves field type (ie individual,household.. field etc).',
`is_reserved` tinyint COMMENT 'Is this field reserved for use by some other CiviCRM functionality?',
`is_multi_summary` tinyint DEFAULT 0 COMMENT 'Include in multi-record listing?'
,
PRIMARY KEY (`id`)
, INDEX `IX_website_type_id`(
website_type_id
)
, CONSTRAINT FK_civicrm_uf_field_uf_group_id FOREIGN KEY (`uf_group_id`) REFERENCES `civicrm_uf_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_uf_field_location_type_id FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_uf_join
-- *
-- * User framework join table. This links various internal civicrm object with a profile. Initial use cases are the donation object and the user module
-- *
-- *******************************************************/
CREATE TABLE `civicrm_uf_join` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this join currently active?',
`module` varchar(64) NOT NULL COMMENT 'Module which owns this uf_join instance, e.g. User Registration, CiviDonate, etc.',
`entity_table` varchar(64) COMMENT 'Name of table where item being referenced is stored. Modules which only need a single collection of uf_join instances may choose not to populate entity_table and entity_id.',
`entity_id` int unsigned COMMENT 'Foreign key to the referenced item.',
`weight` int NOT NULL DEFAULT 1 COMMENT 'Controls display order when multiple user framework groups are setup for concurrent display.',
`uf_group_id` int unsigned NOT NULL COMMENT 'Which form does this field belong to.',
`module_data` longtext COMMENT 'Json serialized array of data used by the ufjoin.module'
,
PRIMARY KEY (`id`)
, INDEX `index_entity`(
entity_table
, entity_id
)
, CONSTRAINT FK_civicrm_uf_join_uf_group_id FOREIGN KEY (`uf_group_id`) REFERENCES `civicrm_uf_group`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_action_schedule
-- *
-- * Table to store the reminders.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_action_schedule` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar(64) COMMENT 'Name of the action(reminder)',
`title` varchar(64) COMMENT 'Title of the action(reminder)',
`recipient` varchar(64) COMMENT 'Recipient',
`limit_to` tinyint COMMENT 'Is this the recipient criteria limited to OR in addition to?',
`entity_value` varchar(255) COMMENT 'Entity value',
`entity_status` varchar(64) COMMENT 'Entity status',
`start_action_offset` int unsigned COMMENT 'Reminder Interval.',
`start_action_unit` varchar(8) COMMENT 'Time units for reminder.',
`start_action_condition` varchar(64) COMMENT 'Reminder Action',
`start_action_date` varchar(64) COMMENT 'Entity date',
`is_repeat` tinyint DEFAULT 0 ,
`repetition_frequency_unit` varchar(8) COMMENT 'Time units for repetition of reminder.',
`repetition_frequency_interval` int unsigned COMMENT 'Time interval for repeating the reminder.',
`end_frequency_unit` varchar(8) COMMENT 'Time units till repetition of reminder.',
`end_frequency_interval` int unsigned COMMENT 'Time interval till repeating the reminder.',
`end_action` varchar(32) COMMENT 'Reminder Action till repeating the reminder.',
`end_date` varchar(64) COMMENT 'Entity end date',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this option active?',
`recipient_manual` varchar(128) COMMENT 'Contact IDs to which reminder should be sent.',
`recipient_listing` varchar(128) COMMENT 'listing based on recipient field.',
`body_text` longtext COMMENT 'Body of the mailing in text format.',
`body_html` longtext COMMENT 'Body of the mailing in html format.',
`sms_body_text` longtext COMMENT 'Content of the SMS text.',
`subject` varchar(128) COMMENT 'Subject of mailing',
`record_activity` tinyint DEFAULT NULL COMMENT 'Record Activity for this reminder?',
`mapping_id` varchar(64) COMMENT 'Name/ID of the mapping to use on this table',
`group_id` int unsigned COMMENT 'FK to Group',
`msg_template_id` int unsigned COMMENT 'FK to the message template.',
`sms_template_id` int unsigned COMMENT 'FK to the message template.',
`absolute_date` date COMMENT 'Date on which the reminder be sent.',
`from_name` varchar(255) COMMENT 'Name in \"from\" field',
`from_email` varchar(255) COMMENT 'Email address in \"from\" field',
`mode` varchar(128) DEFAULT "Email" COMMENT 'Send the message as email or sms or both.',
`sms_provider_id` int unsigned ,
`used_for` varchar(64) COMMENT 'Used for repeating entity',
`filter_contact_language` varchar(128) COMMENT 'Used for multilingual installation',
`communication_language` varchar(8) COMMENT 'Used for multilingual installation'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_action_schedule_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_action_schedule_msg_template_id FOREIGN KEY (`msg_template_id`) REFERENCES `civicrm_msg_template`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_action_schedule_sms_template_id FOREIGN KEY (`sms_template_id`) REFERENCES `civicrm_msg_template`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_action_schedule_sms_provider_id FOREIGN KEY (`sms_provider_id`) REFERENCES `civicrm_sms_provider`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_action_log
-- *
-- * Table to store log for the reminder.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_action_log` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`contact_id` int unsigned COMMENT 'FK to Contact ID',
`entity_id` int unsigned NOT NULL COMMENT 'FK to id of the entity that the action was performed on. Pseudo - FK.',
`entity_table` varchar(255) COMMENT 'name of the entity table for the above id, e.g. civicrm_activity, civicrm_participant',
`action_schedule_id` int unsigned NOT NULL COMMENT 'FK to the action schedule that this action originated from.',
`action_date_time` datetime COMMENT 'date time that the action was performed on.',
`is_error` tinyint DEFAULT 0 COMMENT 'Was there any error sending the reminder?',
`message` text COMMENT 'Description / text in case there was an error encountered.',
`repetition_number` int unsigned COMMENT 'Keeps track of the sequence number of this repetition.',
`reference_date` date DEFAULT NULL COMMENT 'Stores the date from the entity which triggered this reminder action (e.g. membership.end_date for most membership renewal reminders)'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_action_log_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_action_log_action_schedule_id FOREIGN KEY (`action_schedule_id`) REFERENCES `civicrm_action_schedule`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_dashboard_contact
-- *
-- * Table to store dashboard for each contact.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dashboard_contact` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`dashboard_id` int unsigned NOT NULL COMMENT 'Dashboard ID',
`contact_id` int unsigned NOT NULL COMMENT 'Contact ID',
`column_no` tinyint DEFAULT 0 COMMENT 'column no for this widget',
`is_active` tinyint DEFAULT 0 COMMENT 'Is this widget active?',
`weight` int DEFAULT 0 COMMENT 'Ordering of the widgets.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `index_dashboard_id_contact_id`(
dashboard_id
, contact_id
)
, CONSTRAINT FK_civicrm_dashboard_contact_dashboard_id FOREIGN KEY (`dashboard_id`) REFERENCES `civicrm_dashboard`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_dashboard_contact_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_relationship
-- *
-- * Relationship between any 2 types of contacts.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_relationship` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relationship ID',
`contact_id_a` int unsigned NOT NULL COMMENT 'id of the first contact',
`contact_id_b` int unsigned NOT NULL COMMENT 'id of the second contact',
`relationship_type_id` int unsigned NOT NULL COMMENT 'id of the relationship',
`start_date` date COMMENT 'date when the relationship started',
`end_date` date COMMENT 'date when the relationship ended',
`is_active` tinyint DEFAULT 1 COMMENT 'is the relationship active ?',
`description` varchar(255) COMMENT 'Optional verbose description for the relationship.',
`is_permission_a_b` tinyint DEFAULT 0 COMMENT 'is contact a has permission to view / edit contact and\n related data for contact b ?\n ',
`is_permission_b_a` tinyint DEFAULT 0 COMMENT 'is contact b has permission to view / edit contact and\n related data for contact a ?\n ',
`case_id` int unsigned DEFAULT NULL COMMENT 'FK to civicrm_case'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_relationship_contact_id_a FOREIGN KEY (`contact_id_a`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_relationship_contact_id_b FOREIGN KEY (`contact_id_b`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_relationship_relationship_type_id FOREIGN KEY (`relationship_type_id`) REFERENCES `civicrm_relationship_type`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_relationship_case_id FOREIGN KEY (`case_id`) REFERENCES `civicrm_case`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing
-- *
-- * Stores information about a mailing.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`domain_id` int unsigned COMMENT 'Which site is this mailing for',
`header_id` int unsigned COMMENT 'FK to the header component.',
`footer_id` int unsigned COMMENT 'FK to the footer component.',
`reply_id` int unsigned COMMENT 'FK to the auto-responder component.',
`unsubscribe_id` int unsigned COMMENT 'FK to the unsubscribe component.',
`resubscribe_id` int unsigned ,
`optout_id` int unsigned COMMENT 'FK to the opt-out component.',
`name` varchar(128) COMMENT 'Mailing Name.',
`mailing_type` varchar(32) COMMENT 'differentiate between standalone mailings, A/B tests, and A/B final-winner',
`from_name` varchar(128) COMMENT 'From Header of mailing',
`from_email` varchar(128) COMMENT 'From Email of mailing',
`replyto_email` varchar(128) COMMENT 'Reply-To Email of mailing',
`template_type` varchar(64) NOT NULL DEFAULT 'traditional' COMMENT 'The language/processing system used for email templates.',
`template_options` longtext COMMENT 'Advanced options used by the email templating system. (JSON encoded)',
`subject` varchar(128) COMMENT 'Subject of mailing',
`body_text` longtext COMMENT 'Body of the mailing in text format.',
`body_html` longtext COMMENT 'Body of the mailing in html format.',
`url_tracking` tinyint COMMENT 'Should we track URL click-throughs for this mailing?',
`forward_replies` tinyint COMMENT 'Should we forward replies back to the author?',
`auto_responder` tinyint COMMENT 'Should we enable the auto-responder?',
`open_tracking` tinyint COMMENT 'Should we track when recipients open/read this mailing?',
`is_completed` tinyint COMMENT 'Has at least one job associated with this mailing finished?',
`msg_template_id` int unsigned COMMENT 'FK to the message template.',
`override_verp` tinyint DEFAULT 0 COMMENT 'Should we overrite VERP address in Reply-To',
`created_id` int unsigned COMMENT 'FK to Contact ID who first created this mailing',
`created_date` timestamp NULL DEFAULT NULL COMMENT 'Date and time this mailing was created.',
`modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When the mailing (or closely related entity) was created or modified or deleted.',
`scheduled_id` int unsigned COMMENT 'FK to Contact ID who scheduled this mailing',
`scheduled_date` timestamp NULL DEFAULT NULL COMMENT 'Date and time this mailing was scheduled.',
`approver_id` int unsigned COMMENT 'FK to Contact ID who approved this mailing',
`approval_date` timestamp NULL DEFAULT NULL COMMENT 'Date and time this mailing was approved.',
`approval_status_id` int unsigned COMMENT 'The status of this mailing. Values: none, approved, rejected',
`approval_note` longtext COMMENT 'Note behind the decision.',
`is_archived` tinyint DEFAULT 0 COMMENT 'Is this mailing archived?',
`visibility` varchar(40) DEFAULT 'Public Pages' COMMENT 'In what context(s) is the mailing contents visible (online viewing)',
`campaign_id` int unsigned COMMENT 'The campaign for which this mailing has been initiated.',
`dedupe_email` tinyint DEFAULT 0 COMMENT 'Remove duplicate emails?',
`sms_provider_id` int unsigned ,
`hash` varchar(16) COMMENT 'Key for validating requests related to this mailing.',
`location_type_id` int unsigned COMMENT 'With email_selection_method, determines which email address to use',
`email_selection_method` varchar(20) DEFAULT 'automatic' COMMENT 'With location_type_id, determine how to choose the email address to use.',
`language` varchar(5) COMMENT 'Language of the content of the mailing. Useful for tokens.'
,
PRIMARY KEY (`id`)
, INDEX `index_hash`(
hash
)
, CONSTRAINT FK_civicrm_mailing_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_header_id FOREIGN KEY (`header_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_footer_id FOREIGN KEY (`footer_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_reply_id FOREIGN KEY (`reply_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_unsubscribe_id FOREIGN KEY (`unsubscribe_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_optout_id FOREIGN KEY (`optout_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_msg_template_id FOREIGN KEY (`msg_template_id`) REFERENCES `civicrm_msg_template`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_scheduled_id FOREIGN KEY (`scheduled_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_approver_id FOREIGN KEY (`approver_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_sms_provider_id FOREIGN KEY (`sms_provider_id`) REFERENCES `civicrm_sms_provider`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_location_type_id FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_group
-- *
-- * Stores information about the groups that participate in this mailing..
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`mailing_id` int unsigned NOT NULL COMMENT 'The ID of a previous mailing to include/exclude recipients.',
`group_type` varchar(8) COMMENT 'Are the members of the group included or excluded?.',
`entity_table` varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.',
`entity_id` int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
`search_id` int COMMENT 'The filtering search. custom search id or -1 for civicrm api search',
`search_args` text COMMENT 'The arguments to be sent to the search function'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_group_mailing_id FOREIGN KEY (`mailing_id`) REFERENCES `civicrm_mailing`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_trackable_url
-- *
-- * Stores URLs for which we should track click-throughs from mailings
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_trackable_url` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`url` text NOT NULL COMMENT 'The URL to be tracked.',
`mailing_id` int unsigned NOT NULL COMMENT 'FK to the mailing'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_trackable_url_mailing_id FOREIGN KEY (`mailing_id`) REFERENCES `civicrm_mailing`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_job
-- *
-- * Stores information about the job that executes this mailing
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_job` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`mailing_id` int unsigned NOT NULL COMMENT 'The ID of the mailing this Job will send.',
`scheduled_date` timestamp NULL DEFAULT NULL COMMENT 'date on which this job was scheduled.',
`start_date` timestamp NULL DEFAULT NULL COMMENT 'date on which this job was started.',
`end_date` timestamp NULL DEFAULT NULL COMMENT 'date on which this job ended.',
`status` varchar(12) COMMENT 'The state of this job',
`is_test` tinyint DEFAULT 0 COMMENT 'Is this job for a test mail?',
`job_type` varchar(255) COMMENT 'Type of mailling job: null | child ',
`parent_id` int unsigned DEFAULT NULL COMMENT 'Parent job id',
`job_offset` int DEFAULT 0 COMMENT 'Offset of the child job',
`job_limit` int DEFAULT 0 COMMENT 'Queue size limit for each child job'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_job_mailing_id FOREIGN KEY (`mailing_id`) REFERENCES `civicrm_mailing`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_job_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_mailing_job`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_recipients
-- *
-- * Stores information about the recipients of a mailing.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_recipients` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`mailing_id` int unsigned NOT NULL COMMENT 'The ID of the mailing this Job will send.',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact',
`email_id` int unsigned DEFAULT NULL COMMENT 'FK to Email',
`phone_id` int unsigned DEFAULT NULL COMMENT 'FK to Phone'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_recipients_mailing_id FOREIGN KEY (`mailing_id`) REFERENCES `civicrm_mailing`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_recipients_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_recipients_email_id FOREIGN KEY (`email_id`) REFERENCES `civicrm_email`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_recipients_phone_id FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_spool
-- *
-- * Stores the outbond mails
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_spool` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`job_id` int unsigned NOT NULL COMMENT 'The ID of the Job .',
`recipient_email` text COMMENT 'The email of the receipients this mail is to be sent.',
`headers` text COMMENT 'The header information of this mailing .',
`body` text COMMENT 'The body of this mailing.',
`added_at` timestamp NULL DEFAULT NULL COMMENT 'date on which this job was added.',
`removed_at` timestamp NULL DEFAULT NULL COMMENT 'date on which this job was removed.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_spool_job_id FOREIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_queue
-- *
-- * A collection of all intended recipients of a job
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_queue` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`job_id` int unsigned NOT NULL COMMENT 'FK to Job',
`email_id` int unsigned DEFAULT NULL COMMENT 'FK to Email',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact',
`hash` varchar(255) NOT NULL COMMENT 'Security hash',
`phone_id` int unsigned DEFAULT NULL COMMENT 'FK to Phone'
,
PRIMARY KEY (`id`)
, INDEX `index_hash`(
hash
)
, CONSTRAINT FK_civicrm_mailing_event_queue_job_id FOREIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_queue_email_id FOREIGN KEY (`email_id`) REFERENCES `civicrm_email`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_event_queue_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_queue_phone_id FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_bounce
-- *
-- * Tracks when and why an email bounced.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_bounce` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`event_queue_id` int unsigned NOT NULL COMMENT 'FK to EventQueue',
`bounce_type_id` int unsigned COMMENT 'What type of bounce was it?',
`bounce_reason` varchar(255) COMMENT 'The reason the email bounced.',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this bounce event occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_bounce_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_delivered
-- *
-- * Tracks when a queued email is actually delivered to the MTA
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_delivered` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`event_queue_id` int unsigned NOT NULL COMMENT 'FK to EventQueue',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this delivery event occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_delivered_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_forward
-- *
-- * Tracks when a contact forwards a mailing to a (new) contact
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_forward` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`event_queue_id` int unsigned NOT NULL COMMENT 'FK to EventQueue',
`dest_queue_id` int unsigned COMMENT 'FK to EventQueue for destination',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this forward event occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_forward_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_forward_dest_queue_id FOREIGN KEY (`dest_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_opened
-- *
-- * Tracks when a delivered email is opened by the recipient
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_opened` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`event_queue_id` int unsigned NOT NULL COMMENT 'FK to EventQueue',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this open event occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_opened_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_reply
-- *
-- * Tracks when a contact replies to a mailing
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_reply` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`event_queue_id` int unsigned NOT NULL COMMENT 'FK to EventQueue',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this reply event occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_reply_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_trackable_url_open
-- *
-- * Tracks when a TrackableURL is clicked by a recipient.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_trackable_url_open` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`event_queue_id` int unsigned NOT NULL COMMENT 'FK to EventQueue',
`trackable_url_id` int unsigned NOT NULL COMMENT 'FK to TrackableURL',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this trackable URL open occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_trackable_url_open_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_trackable_url_open_trackable_url_id FOREIGN KEY (`trackable_url_id`) REFERENCES `civicrm_mailing_trackable_url`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_mailing_event_unsubscribe
-- *
-- * Tracks when a recipient unsubscribes from a group/domain
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_unsubscribe` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`event_queue_id` int unsigned NOT NULL COMMENT 'FK to EventQueue',
`org_unsubscribe` tinyint NOT NULL COMMENT 'Unsubscribe at org- or group-level',
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When this delivery event occurred.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_mailing_event_unsubscribe_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contribution_recur
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_recur` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution Recur ID',
`contact_id` int unsigned NOT NULL COMMENT 'Foreign key to civicrm_contact.id .',
`amount` decimal(20,2) NOT NULL COMMENT 'Amount to be contributed or charged each recurrence.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`frequency_unit` varchar(8) DEFAULT 'month' COMMENT 'Time units for recurrence of payment.',
`frequency_interval` int unsigned NOT NULL COMMENT 'Number of time units for recurrence of payment.',
`installments` int unsigned COMMENT 'Total number of payments to be made. Set this to 0 if this is an open-ended commitment i.e. no set end date.',
`start_date` datetime NOT NULL COMMENT 'The date the first scheduled recurring contribution occurs.',
`create_date` datetime NOT NULL COMMENT 'When this recurring contribution record was created.',
`modified_date` datetime COMMENT 'Last updated date for this record. mostly the last time a payment was received',
`cancel_date` datetime COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it',
`end_date` datetime COMMENT 'Date this recurring contribution finished successfully',
`processor_id` varchar(255) COMMENT 'Possibly needed to store a unique identifier for this recurring payment order - if this is available from the processor??',
`payment_token_id` int unsigned COMMENT 'Optionally used to store a link to a payment token used for this recurring contribution.',
`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',
`invoice_id` varchar(255) COMMENT 'unique invoice id, system generated or passed in',
`contribution_status_id` int unsigned DEFAULT 1 ,
`is_test` tinyint DEFAULT 0 ,
`cycle_day` int unsigned NOT NULL DEFAULT 1 COMMENT 'Day in the period when the payment should be charged e.g. 1st of month, 15th etc.',
`next_sched_contribution_date` datetime COMMENT 'Next scheduled date',
`failure_count` int unsigned DEFAULT 0 COMMENT 'Number of failed charge attempts since last success. Business rule could be set to deactivate on more than x failures.',
`failure_retry_date` datetime COMMENT 'Date to retry failed attempt',
`auto_renew` tinyint NOT NULL DEFAULT 0 COMMENT 'Some systems allow contributor to set a number of installments - but then auto-renew the subscription or commitment if they do not cancel.',
`payment_processor_id` int unsigned COMMENT 'Foreign key to civicrm_payment_processor.id',
`financial_type_id` int unsigned COMMENT 'FK to Financial Type',
`payment_instrument_id` int unsigned COMMENT 'FK to Payment Instrument',
`campaign_id` int unsigned COMMENT 'The campaign for which this contribution has been triggered.',
`is_email_receipt` tinyint DEFAULT 1 COMMENT 'if true, receipt is automatically emailed to contact on each successful payment'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_contrib_trxn_id`(
trxn_id
)
, UNIQUE INDEX `UI_contrib_invoice_id`(
invoice_id
)
, INDEX `index_contribution_status`(
contribution_status_id
)
, INDEX `UI_contribution_recur_payment_instrument_id`(
payment_instrument_id
)
, CONSTRAINT FK_civicrm_contribution_recur_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_recur_payment_token_id FOREIGN KEY (`payment_token_id`) REFERENCES `civicrm_payment_token`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_recur_payment_processor_id FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_recur_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_recur_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_financial_trxn
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_trxn` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`from_financial_account_id` int unsigned COMMENT 'FK to financial_account table.',
`to_financial_account_id` int unsigned COMMENT 'FK to financial_financial_account table.',
`trxn_date` datetime DEFAULT NULL COMMENT 'date transaction occurred',
`total_amount` decimal(20,2) NOT NULL COMMENT 'amount of transaction',
`fee_amount` decimal(20,2) COMMENT 'actual processor fee if known - may be 0.',
`net_amount` decimal(20,2) COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`is_payment` tinyint DEFAULT 0 COMMENT 'Is this entry either a payment or a reversal of a payment?',
`trxn_id` varchar(255) COMMENT 'Transaction id supplied by external processor. This may not be unique.',
`trxn_result_code` varchar(255) COMMENT 'processor result code',
`status_id` int unsigned COMMENT 'pseudo FK to civicrm_option_value of contribution_status_id option_group',
`payment_processor_id` int unsigned COMMENT 'Payment Processor for this financial transaction',
`payment_instrument_id` int unsigned COMMENT 'FK to payment_instrument option group values',
`card_type_id` int unsigned COMMENT 'FK to accept_creditcard option group values',
`check_number` varchar(255) COMMENT 'Check number',
`pan_truncation` varchar(4) COMMENT 'Last 4 digits of credit card'
,
PRIMARY KEY (`id`)
, INDEX `UI_ftrxn_trxn_id`(
trxn_id
)
, INDEX `UI_ftrxn_payment_instrument_id`(
payment_instrument_id
)
, INDEX `UI_ftrxn_check_number`(
check_number
)
, CONSTRAINT FK_civicrm_financial_trxn_from_financial_account_id FOREIGN KEY (`from_financial_account_id`) REFERENCES `civicrm_financial_account`(`id`) , CONSTRAINT FK_civicrm_financial_trxn_to_financial_account_id FOREIGN KEY (`to_financial_account_id`) REFERENCES `civicrm_financial_account`(`id`) , CONSTRAINT FK_civicrm_financial_trxn_payment_processor_id FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_membership
-- *
-- * Contact Membership records.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact ID',
`membership_type_id` int unsigned NOT NULL COMMENT 'FK to Membership Type',
`join_date` date COMMENT 'Beginning of initial membership period (member since...).',
`start_date` date COMMENT 'Beginning of current uninterrupted membership period.',
`end_date` date COMMENT 'Current membership period expire date.',
`source` varchar(128) ,
`status_id` int unsigned NOT NULL COMMENT 'FK to Membership Status',
`is_override` tinyint COMMENT 'Admin users may set a manual status which overrides the calculated status. When this flag is true, automated status update scripts should NOT modify status for the record.',
`owner_membership_id` int unsigned COMMENT 'Optional FK to Parent Membership.',
`max_related` int COMMENT 'Maximum number of related memberships (membership_type override).',
`is_test` tinyint DEFAULT 0 ,
`is_pay_later` tinyint DEFAULT 0 ,
`contribution_recur_id` int unsigned COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each membership in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.',
`campaign_id` int unsigned COMMENT 'The campaign for which this membership is attached.'
,
PRIMARY KEY (`id`)
, INDEX `index_owner_membership_id`(
owner_membership_id
)
, CONSTRAINT FK_civicrm_membership_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_membership_type_id FOREIGN KEY (`membership_type_id`) REFERENCES `civicrm_membership_type`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_status_id FOREIGN KEY (`status_id`) REFERENCES `civicrm_membership_status`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_owner_membership_id FOREIGN KEY (`owner_membership_id`) REFERENCES `civicrm_membership`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_membership_contribution_recur_id FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_membership_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_membership_log
-- *
-- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_log` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`membership_id` int unsigned NOT NULL COMMENT 'FK to Membership table',
`status_id` int unsigned NOT NULL COMMENT 'New status assigned to membership by this action. FK to Membership Status',
`start_date` date COMMENT 'New membership period start date',
`end_date` date COMMENT 'New membership period expiration date.',
`modified_id` int unsigned COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.',
`modified_date` date COMMENT 'Date this membership modification action was logged.',
`membership_type_id` int unsigned COMMENT 'FK to Membership Type.',
`max_related` int COMMENT 'Maximum number of related memberships.'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_membership_log_membership_id FOREIGN KEY (`membership_id`) REFERENCES `civicrm_membership`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_log_status_id FOREIGN KEY (`status_id`) REFERENCES `civicrm_membership_status`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_log_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_membership_log_membership_type_id FOREIGN KEY (`membership_type_id`) REFERENCES `civicrm_membership_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_activity
-- *
-- * Other Activity details stored here include contact, location, details.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_activity` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Other Activity ID',
`source_record_id` int unsigned COMMENT 'Artificial FK to original transaction (e.g. contribution) IF it is not an Activity. Table can be figured out through activity_type_id, and further through component registry.',
`activity_type_id` int unsigned NOT NULL DEFAULT 1 COMMENT 'FK to civicrm_option_value.id, that has to be valid, registered activity type.',
`subject` varchar(255) COMMENT 'The subject/purpose/short description of the activity.',
`activity_date_time` datetime COMMENT 'Date and time this activity is scheduled to occur. Formerly named scheduled_date_time.',
`duration` int unsigned COMMENT 'Planned or actual duration of activity expressed in minutes. Conglomerate of former duration_hours and duration_minutes.',
`location` varchar(255) COMMENT 'Location of the activity (optional, open text).',
`phone_id` int unsigned COMMENT 'Phone ID of the number called (optional - used if an existing phone number is selected).',
`phone_number` varchar(64) COMMENT 'Phone number in case the number does not exist in the civicrm_phone table.',
`details` longtext COMMENT 'Details about the activity (agenda, notes, etc).',
`status_id` int unsigned COMMENT 'ID of the status this activity is currently in. Foreign key to civicrm_option_value.',
`priority_id` int unsigned COMMENT 'ID of the priority given to this activity. Foreign key to civicrm_option_value.',
`parent_id` int unsigned COMMENT 'Parent meeting ID (if this is a follow-up item). This is not currently implemented',
`is_test` tinyint DEFAULT 0 ,
`medium_id` int unsigned DEFAULT NULL COMMENT 'Activity Medium, Implicit FK to civicrm_option_value where option_group = encounter_medium.',
`is_auto` tinyint DEFAULT 0 ,
`relationship_id` int unsigned DEFAULT NULL COMMENT 'FK to Relationship ID',
`is_current_revision` tinyint DEFAULT 1 ,
`original_id` int unsigned COMMENT 'Activity ID of the first activity record in versioning chain.',
`result` varchar(255) COMMENT 'Currently being used to store result id for survey activity, FK to option value.',
`is_deleted` tinyint DEFAULT 0 ,
`campaign_id` int unsigned COMMENT 'The campaign for which this activity has been triggered.',
`engagement_level` int unsigned COMMENT 'Assign a specific level of engagement to this activity. Used for tracking constituents in ladder of engagement.',
`weight` int ,
`is_star` tinyint DEFAULT 0 COMMENT 'Activity marked as favorite.',
`created_date` timestamp NULL DEFAULT NULL COMMENT 'When was the activity was created.',
`modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the activity (or closely related entity) was created or modified or deleted.'
,
PRIMARY KEY (`id`)
, INDEX `UI_source_record_id`(
source_record_id
)
, INDEX `UI_activity_type_id`(
activity_type_id
)
, INDEX `index_activity_date_time`(
activity_date_time
)
, INDEX `index_status_id`(
status_id
)
, INDEX `index_medium_id`(
medium_id
)
, INDEX `index_is_current_revision`(
is_current_revision
)
, INDEX `index_is_deleted`(
is_deleted
)
, CONSTRAINT FK_civicrm_activity_phone_id FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_activity_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_activity`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_activity_relationship_id FOREIGN KEY (`relationship_id`) REFERENCES `civicrm_relationship`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_activity_original_id FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_activity_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_activity_contact
-- *
-- * Activity Contact
-- *
-- *******************************************************/
CREATE TABLE `civicrm_activity_contact` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Activity contact id',
`activity_id` int unsigned NOT NULL COMMENT 'Foreign key to the activity for this record.',
`contact_id` int unsigned NOT NULL COMMENT 'Foreign key to the contact for this record.',
`record_type_id` int unsigned COMMENT 'Nature of this contact\'s role in the activity: 1 assignee, 2 creator, 3 focus or target.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_activity_contact`(
contact_id
, activity_id
, record_type_id
)
, INDEX `index_record_type`(
activity_id
, record_type_id
)
, CONSTRAINT FK_civicrm_activity_contact_activity_id FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_activity_contact_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_case_activity
-- *
-- * Joining table for case-activity associations.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_case_activity` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique case-activity association id',
`case_id` int unsigned NOT NULL COMMENT 'Case ID of case-activity association.',
`activity_id` int unsigned NOT NULL COMMENT 'Activity ID of case-activity association.'
,
PRIMARY KEY (`id`)
, INDEX `UI_case_activity_id`(
case_id
, activity_id
)
, CONSTRAINT FK_civicrm_case_activity_case_id FOREIGN KEY (`case_id`) REFERENCES `civicrm_case`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_case_activity_activity_id FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_price_field
-- *
-- *******************************************************/
CREATE TABLE `civicrm_price_field` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Field',
`price_set_id` int unsigned NOT NULL COMMENT 'FK to civicrm_price_set',
`name` varchar(255) NOT NULL COMMENT 'Variable name/programmatic handle for this field.',
`label` varchar(255) NOT NULL COMMENT 'Text for form field label (also friendly name for administering this field).',
`html_type` varchar(12) NOT NULL ,
`is_enter_qty` tinyint DEFAULT 0 COMMENT 'Enter a quantity for this field?',
`help_pre` text COMMENT 'Description and/or help text to display before this field.',
`help_post` text COMMENT 'Description and/or help text to display after this field.',
`weight` int DEFAULT 1 COMMENT 'Order in which the fields should appear',
`is_display_amounts` tinyint DEFAULT 1 COMMENT 'Should the price be displayed next to the label for each option?',
`options_per_line` int unsigned DEFAULT 1 COMMENT 'number of options per line for checkbox and radio',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this price field active',
`is_required` tinyint DEFAULT 1 COMMENT 'Is this price field required (value must be > 1)',
`active_on` datetime DEFAULT NULL COMMENT 'If non-zero, do not show this field before the date specified',
`expire_on` datetime DEFAULT NULL COMMENT 'If non-zero, do not show this field after the date specified',
`javascript` varchar(255) COMMENT 'Optional scripting attributes for field',
`visibility_id` int unsigned DEFAULT 1 COMMENT 'Implicit FK to civicrm_option_group with name = \'visibility\''
,
PRIMARY KEY (`id`)
, INDEX `index_name`(
name
)
, CONSTRAINT FK_civicrm_price_field_price_set_id FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_price_field_value
-- *
-- *******************************************************/
CREATE TABLE `civicrm_price_field_value` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Field Value',
`price_field_id` int unsigned NOT NULL COMMENT 'FK to civicrm_price_field',
`name` varchar(255) COMMENT 'Price field option name',
`label` varchar(255) COMMENT 'Price field option label',
`description` text DEFAULT NULL COMMENT '>Price field option description.',
`help_pre` text DEFAULT NULL COMMENT 'Price field option pre help text.',
`help_post` text DEFAULT NULL COMMENT 'Price field option post field help.',
`amount` decimal(18,9) NOT NULL COMMENT 'Price field option amount',
`count` int unsigned DEFAULT NULL COMMENT 'Number of participants per field option',
`max_value` int unsigned DEFAULT NULL COMMENT 'Max number of participants per field options',
`weight` int DEFAULT 1 COMMENT 'Order in which the field options should appear',
`membership_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Membership Type',
`membership_num_terms` int unsigned DEFAULT NULL COMMENT 'Number of terms for this membership',
`is_default` tinyint DEFAULT 0 COMMENT 'Is this default price field option',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this price field value active',
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
`non_deductible_amount` decimal(20,2) NOT NULL DEFAULT 0.0 COMMENT 'Portion of total amount which is NOT tax deductible.',
`visibility_id` int unsigned DEFAULT 1 COMMENT 'Implicit FK to civicrm_option_group with name = \'visibility\''
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_price_field_value_price_field_id FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field`(`id`) , CONSTRAINT FK_civicrm_price_field_value_membership_type_id FOREIGN KEY (`membership_type_id`) REFERENCES `civicrm_membership_type`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_price_field_value_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_pcp_block
-- *
-- * A Personal Campaign Page Block stores admin configurable status options and rules
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pcp_block` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'PCP block Id',
`entity_table` varchar(64) ,
`entity_id` int unsigned NOT NULL COMMENT 'FK to civicrm_contribution_page.id OR civicrm_event.id',
`target_entity_type` varchar(255) NOT NULL DEFAULT 'contribute' COMMENT 'The type of entity that this pcp targets',
`target_entity_id` int unsigned NOT NULL COMMENT 'The entity that this pcp targets',
`supporter_profile_id` int unsigned DEFAULT NULL COMMENT 'FK to civicrm_uf_group.id. Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
`owner_notify_id` int unsigned DEFAULT 0 COMMENT 'FK to civicrm_option_group with name = PCP owner notifications',
`is_approval_needed` tinyint DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
`is_tellfriend_enabled` tinyint DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?',
`tellfriend_limit` int unsigned DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend',
`link_text` varchar(255) DEFAULT NULL COMMENT 'Link text for PCP.',
`is_active` tinyint DEFAULT 1 COMMENT 'Is Personal Campaign Page Block enabled/active?',
`notify_email` varchar(255) DEFAULT NULL COMMENT 'If set, notification is automatically emailed to this email-address on create/update Personal Campaign Page'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_pcp_block_supporter_profile_id FOREIGN KEY (`supporter_profile_id`) REFERENCES `civicrm_uf_group`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_address
-- *
-- * Stores the physical street / mailing address. This format should be capable of storing ALL international addresses.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_address` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Address ID',
`contact_id` int unsigned COMMENT 'FK to Contact ID',
`location_type_id` int unsigned COMMENT 'Which Location does this address belong to.',
`is_primary` tinyint DEFAULT 0 COMMENT 'Is this the primary address.',
`is_billing` tinyint DEFAULT 0 COMMENT 'Is this the billing address.',
`street_address` varchar(96) COMMENT 'Concatenation of all routable street address components (prefix, street number, street name, suffix, unit\n number OR P.O. Box). Apps should be able to determine physical location with this data (for mapping, mail\n delivery, etc.).\n ',
`street_number` int COMMENT 'Numeric portion of address number on the street, e.g. For 112A Main St, the street_number = 112.',
`street_number_suffix` varchar(8) COMMENT 'Non-numeric portion of address number on the street, e.g. For 112A Main St, the street_number_suffix = A\n ',
`street_number_predirectional` varchar(8) COMMENT 'Directional prefix, e.g. SE Main St, SE is the prefix.',
`street_name` varchar(64) COMMENT 'Actual street name, excluding St, Dr, Rd, Ave, e.g. For 112 Main St, the street_name = Main.',
`street_type` varchar(8) COMMENT 'St, Rd, Dr, etc.',
`street_number_postdirectional` varchar(8) COMMENT 'Directional prefix, e.g. Main St S, S is the suffix.',
`street_unit` varchar(16) COMMENT 'Secondary unit designator, e.g. Apt 3 or Unit # 14, or Bldg 1200',
`supplemental_address_1` varchar(96) COMMENT 'Supplemental Address Information, Line 1',
`supplemental_address_2` varchar(96) COMMENT 'Supplemental Address Information, Line 2',
`supplemental_address_3` varchar(96) COMMENT 'Supplemental Address Information, Line 3',
`city` varchar(64) COMMENT 'City, Town or Village Name.',
`county_id` int unsigned COMMENT 'Which County does this address belong to.',
`state_province_id` int unsigned COMMENT 'Which State_Province does this address belong to.',
`postal_code_suffix` varchar(12) COMMENT 'Store the suffix, like the +4 part in the USPS system.',
`postal_code` varchar(64) COMMENT 'Store both US (zip5) AND international postal codes. App is responsible for country/region appropriate validation.',
`usps_adc` varchar(32) COMMENT 'USPS Bulk mailing code.',
`country_id` int unsigned COMMENT 'Which Country does this address belong to.',
`geo_code_1` double COMMENT 'Latitude',
`geo_code_2` double COMMENT 'Longitude',
`manual_geo_code` tinyint DEFAULT 0 COMMENT 'Is this a manually entered geo code',
`timezone` varchar(8) COMMENT 'Timezone expressed as a UTC offset - e.g. United States CST would be written as \"UTC-6\".',
`name` varchar(255) ,
`master_id` int unsigned COMMENT 'FK to Address ID'
,
PRIMARY KEY (`id`)
, INDEX `index_location_type`(
location_type_id
)
, INDEX `index_is_primary`(
is_primary
)
, INDEX `index_is_billing`(
is_billing
)
, INDEX `index_street_name`(
street_name
)
, INDEX `index_city`(
city
)
, CONSTRAINT FK_civicrm_address_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_address_county_id FOREIGN KEY (`county_id`) REFERENCES `civicrm_county`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_address_state_province_id FOREIGN KEY (`state_province_id`) REFERENCES `civicrm_state_province`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_address_country_id FOREIGN KEY (`country_id`) REFERENCES `civicrm_country`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_address_master_id FOREIGN KEY (`master_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_loc_block
-- *
-- * Define location specific properties
-- *
-- *******************************************************/
CREATE TABLE `civicrm_loc_block` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID',
`address_id` int unsigned ,
`email_id` int unsigned ,
`phone_id` int unsigned ,
`im_id` int unsigned ,
`address_2_id` int unsigned ,
`email_2_id` int unsigned ,
`phone_2_id` int unsigned ,
`im_2_id` int unsigned
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_loc_block_address_id FOREIGN KEY (`address_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_email_id FOREIGN KEY (`email_id`) REFERENCES `civicrm_email`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_phone_id FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_im_id FOREIGN KEY (`im_id`) REFERENCES `civicrm_im`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_address_2_id FOREIGN KEY (`address_2_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_email_2_id FOREIGN KEY (`email_2_id`) REFERENCES `civicrm_email`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_phone_2_id FOREIGN KEY (`phone_2_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_im_2_id FOREIGN KEY (`im_2_id`) REFERENCES `civicrm_im`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_group_contact
-- *
-- * Join table sets membership for 'static' groups. Also used to store 'opt-out' entries for 'query' type groups (status = 'OUT')
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group_contact` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`group_id` int unsigned NOT NULL COMMENT 'FK to civicrm_group',
`contact_id` int unsigned NOT NULL COMMENT 'FK to civicrm_contact',
`status` varchar(8) COMMENT 'status of contact relative to membership in group',
`location_id` int unsigned COMMENT 'Optional location to associate with this membership',
`email_id` int unsigned COMMENT 'Optional email to associate with this membership'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_contact_group`(
contact_id
, group_id
)
, CONSTRAINT FK_civicrm_group_contact_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_contact_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_contact_location_id FOREIGN KEY (`location_id`) REFERENCES `civicrm_loc_block`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_group_contact_email_id FOREIGN KEY (`email_id`) REFERENCES `civicrm_email`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contribution
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution ID',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact ID',
`financial_type_id` int unsigned COMMENT 'FK to Financial Type for (total_amount - non_deductible_amount).',
`contribution_page_id` int unsigned COMMENT 'The Contribution Page which triggered this contribution',
`payment_instrument_id` int unsigned COMMENT 'FK to Payment Instrument',
`receive_date` datetime COMMENT 'Date contribution was received - not necessarily the creation date of the record',
`non_deductible_amount` decimal(20,2) DEFAULT 0 COMMENT 'Portion of total amount which is NOT tax deductible. Equal to total_amount for non-deductible financial types.',
`total_amount` decimal(20,2) NOT NULL COMMENT 'Total amount of this contribution. Use market value for non-monetary gifts.',
`fee_amount` decimal(20,2) COMMENT 'actual processor fee if known - may be 0.',
`net_amount` decimal(20,2) COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',
`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',
`invoice_id` varchar(255) COMMENT 'unique invoice id, system generated or passed in',
`invoice_number` varchar(255) COMMENT 'Human readable invoice number',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`cancel_date` datetime COMMENT 'when was gift cancelled',
`cancel_reason` text ,
`receipt_date` datetime COMMENT 'when (if) receipt was sent. populated automatically for online donations w/ automatic receipting',
`thankyou_date` datetime COMMENT 'when (if) was donor thanked',
`source` varchar(255) COMMENT 'Origin of this Contribution.',
`amount_level` text ,
`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.',
`is_test` tinyint DEFAULT 0 ,
`is_pay_later` tinyint DEFAULT 0 ,
`contribution_status_id` int unsigned DEFAULT 1 ,
`address_id` int unsigned COMMENT 'Conditional foreign key to civicrm_address.id. We insert an address record for each contribution when we have associated billing name and address data.',
`check_number` varchar(255) ,
`campaign_id` int unsigned COMMENT 'The campaign for which this contribution has been triggered.',
`creditnote_id` varchar(255) COMMENT 'unique credit note id, system generated or passed in',
`tax_amount` decimal(20,2) COMMENT 'Total tax amount of this contribution.',
`revenue_recognition_date` datetime COMMENT 'Stores the date when revenue should be recognized.'
,
PRIMARY KEY (`id`)
, INDEX `UI_contrib_payment_instrument_id`(
payment_instrument_id
)
, INDEX `index_total_amount_receive_date`(
total_amount
, receive_date
)
, INDEX `index_source`(
source
)
, UNIQUE INDEX `UI_contrib_trxn_id`(
trxn_id
)
, UNIQUE INDEX `UI_contrib_invoice_id`(
invoice_id
)
, INDEX `index_contribution_status`(
contribution_status_id
)
, INDEX `received_date`(
receive_date
)
, INDEX `check_number`(
check_number
)
, INDEX `index_creditnote_id`(
creditnote_id
)
, CONSTRAINT FK_civicrm_contribution_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) , CONSTRAINT FK_civicrm_contribution_contribution_page_id FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_contribution_recur_id FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_address_id FOREIGN KEY (`address_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contribution_product
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_product` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`product_id` int unsigned NOT NULL ,
`contribution_id` int unsigned NOT NULL ,
`product_option` varchar(255) COMMENT 'Option value selected if applicable - e.g. color, size etc.',
`quantity` int ,
`fulfilled_date` date COMMENT 'Optional. Can be used to record the date this product was fulfilled or shipped.',
`start_date` date COMMENT 'Actual start date for a time-delimited premium (subscription, service or membership)',
`end_date` date COMMENT 'Actual end date for a time-delimited premium (subscription, service or membership)',
`comment` text ,
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type(for membership price sets only).'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_contribution_product_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contribution_soft
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_soft` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Soft Contribution ID',
`contribution_id` int unsigned NOT NULL COMMENT 'FK to contribution table.',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact ID',
`amount` decimal(20,2) NOT NULL COMMENT 'Amount of this soft contribution.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`pcp_id` int unsigned DEFAULT NULL COMMENT 'FK to civicrm_pcp.id',
`pcp_display_in_roll` tinyint DEFAULT 0 ,
`pcp_roll_nickname` varchar(255) DEFAULT NULL ,
`pcp_personal_note` varchar(255) DEFAULT NULL ,
`soft_credit_type_id` int unsigned DEFAULT NULL COMMENT 'Soft Credit Type ID.Implicit FK to civicrm_option_value where option_group = soft_credit_type.'
,
PRIMARY KEY (`id`)
, INDEX `index_id`(
pcp_id
)
, CONSTRAINT FK_civicrm_contribution_soft_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_soft_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_soft_pcp_id FOREIGN KEY (`pcp_id`) REFERENCES `civicrm_pcp`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_entity_financial_trxn
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_financial_trxn` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`entity_table` varchar(64) NOT NULL COMMENT 'May contain civicrm_financial_item, civicrm_contribution, civicrm_financial_trxn, civicrm_grant, etc',
`entity_id` int unsigned NOT NULL ,
`financial_trxn_id` int unsigned ,
`amount` decimal(20,2) NOT NULL COMMENT 'allocated amount of transaction to this entity'
,
PRIMARY KEY (`id`)
, INDEX `UI_entity_financial_trxn_entity_table`(
entity_table
)
, INDEX `UI_entity_financial_trxn_entity_id`(
entity_id
)
, CONSTRAINT FK_civicrm_entity_financial_trxn_financial_trxn_id FOREIGN KEY (`financial_trxn_id`) REFERENCES `civicrm_financial_trxn`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_membership_payment
-- *
-- * Membership Payment
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_payment` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`membership_id` int unsigned NOT NULL COMMENT 'FK to Membership table',
`contribution_id` int unsigned COMMENT 'FK to contribution table.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_contribution_membership`(
contribution_id
, membership_id
)
, CONSTRAINT FK_civicrm_membership_payment_membership_id FOREIGN KEY (`membership_id`) REFERENCES `civicrm_membership`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_payment_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_event
-- *
-- *******************************************************/
CREATE TABLE `civicrm_event` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Event',
`title` varchar(255) COMMENT 'Event Title (e.g. Fall Fundraiser Dinner)',
`summary` text COMMENT 'Brief summary of event. Text and html allowed. Displayed on Event Registration form and can be used on other CMS pages which need an event summary.',
`description` text COMMENT 'Full description of event. Text and html allowed. Displayed on built-in Event Information screens.',
`event_type_id` int unsigned DEFAULT 0 COMMENT 'Event Type ID.Implicit FK to civicrm_option_value where option_group = event_type.',
`participant_listing_id` int unsigned DEFAULT 0 COMMENT 'Should we expose the participant list? Implicit FK to civicrm_option_value where option_group = participant_listing.',
`is_public` tinyint DEFAULT 1 COMMENT 'Public events will be included in the iCal feeds. Access to private event information may be limited using ACLs.',
`start_date` datetime COMMENT 'Date and time that event starts.',
`end_date` datetime COMMENT 'Date and time that event ends. May be NULL if no defined end date/time',
`is_online_registration` tinyint DEFAULT 0 COMMENT 'If true, include registration link on Event Info page.',
`registration_link_text` varchar(255) COMMENT 'Text for link to Event Registration form which is displayed on Event Information screen when is_online_registration is true.',
`registration_start_date` datetime COMMENT 'Date and time that online registration starts.',
`registration_end_date` datetime COMMENT 'Date and time that online registration ends.',
`max_participants` int unsigned DEFAULT NULL COMMENT 'Maximum number of registered participants to allow. After max is reached, a custom Event Full message is displayed. If NULL, allow unlimited number of participants.',
`event_full_text` text COMMENT 'Message to display on Event Information page and INSTEAD OF Event Registration form if maximum participants are signed up. Can include email address/info about getting on a waiting list, etc. Text and html allowed.',
`is_monetary` tinyint DEFAULT 0 COMMENT 'If true, one or more fee amounts must be set and a Payment Processor must be configured for Online Event Registration.',
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'Financial type assigned to paid event registrations for this event. Required if is_monetary is true.',
`payment_processor` varchar(128) COMMENT 'Payment Processors configured for this Event (if is_monetary is true)',
`is_map` tinyint DEFAULT 0 COMMENT 'Include a map block on the Event Information page when geocode info is available and a mapping provider has been specified?',
`is_active` tinyint DEFAULT 0 COMMENT 'Is this Event enabled or disabled/cancelled?',
`fee_label` varchar(255) ,
`is_show_location` tinyint DEFAULT 1 COMMENT 'If true, show event location.',
`loc_block_id` int unsigned COMMENT 'FK to Location Block ID',
`default_role_id` int unsigned DEFAULT 1 COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.',
`intro_text` text COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
`footer_text` text COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
`confirm_title` varchar(255) DEFAULT NULL COMMENT 'Title for Confirmation page.',
`confirm_text` text COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
`confirm_footer_text` text COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
`is_email_confirm` tinyint DEFAULT 0 COMMENT 'If true, confirmation is automatically emailed to contact on successful registration.',
`confirm_email_text` text COMMENT 'text to include above standard event info on confirmation email. emails are text-only, so do not allow html for now',
`confirm_from_name` varchar(255) COMMENT 'FROM email name used for confirmation emails.',
`confirm_from_email` varchar(255) COMMENT 'FROM email address used for confirmation emails.',
`cc_confirm` varchar(255) COMMENT 'comma-separated list of email addresses to cc each time a confirmation is sent',
`bcc_confirm` varchar(255) COMMENT 'comma-separated list of email addresses to bcc each time a confirmation is sent',
`default_fee_id` int unsigned COMMENT 'FK to civicrm_option_value.',
`default_discount_fee_id` int unsigned COMMENT 'FK to civicrm_option_value.',
`thankyou_title` varchar(255) DEFAULT NULL COMMENT 'Title for ThankYou page.',
`thankyou_text` text COMMENT 'ThankYou Text.',
`thankyou_footer_text` text COMMENT 'Footer message.',
`is_pay_later` tinyint DEFAULT 0 COMMENT 'if true - allows the user to send payment directly to the org later',
`pay_later_text` text COMMENT 'The text displayed to the user in the main form',
`pay_later_receipt` text COMMENT 'The receipt sent to the user instead of the normal receipt text',
`is_partial_payment` tinyint DEFAULT 0 COMMENT 'is partial payment enabled for this event',
`initial_amount_label` varchar(255) COMMENT 'Initial amount label for partial payment',
`initial_amount_help_text` text COMMENT 'Initial amount help text for partial payment',
`min_initial_amount` decimal(20,2) COMMENT 'Minimum initial amount for partial payment',
`is_multiple_registrations` tinyint DEFAULT 0 COMMENT 'if true - allows the user to register multiple participants for event',
`max_additional_participants` int unsigned DEFAULT 0 COMMENT 'Maximum number of additional participants that can be registered on a single booking',
`allow_same_participant_emails` tinyint DEFAULT 0 COMMENT 'if true - allows the user to register multiple registrations from same email address.',
`has_waitlist` tinyint COMMENT 'Whether the event has waitlist support.',
`requires_approval` tinyint COMMENT 'Whether participants require approval before they can finish registering.',
`expiration_time` int unsigned COMMENT 'Expire pending but unconfirmed registrations after this many hours.',
`allow_selfcancelxfer` tinyint DEFAULT 0 COMMENT 'Allow self service cancellation or transfer for event?',
`selfcancelxfer_time` int unsigned DEFAULT 0 COMMENT 'Number of hours prior to event start date to allow self-service cancellation or transfer.',
`waitlist_text` text COMMENT 'Text to display when the event is full, but participants can signup for a waitlist.',
`approval_req_text` text COMMENT 'Text to display when the approval is required to complete registration for an event.',
`is_template` tinyint NOT NULL DEFAULT 0 COMMENT 'whether the event has template',
`template_title` varchar(255) COMMENT 'Event Template Title',
`created_id` int unsigned COMMENT 'FK to civicrm_contact, who created this event',
`created_date` datetime COMMENT 'Date and time that event was created.',
`currency` varchar(3) COMMENT '3 character string, value from config setting or input via user.',
`campaign_id` int unsigned COMMENT 'The campaign for which this event has been created.',
`is_share` tinyint DEFAULT 1 COMMENT 'Can people share the event through social media?',
`is_confirm_enabled` tinyint DEFAULT 1 COMMENT 'If false, the event booking confirmation screen gets skipped',
`parent_event_id` int unsigned DEFAULT NULL COMMENT 'Implicit FK to civicrm_event: parent event',
`slot_label_id` int unsigned DEFAULT NULL COMMENT 'Subevent slot label. Implicit FK to civicrm_option_value where option_group = conference_slot.',
`dedupe_rule_group_id` int unsigned DEFAULT NULL COMMENT 'Rule to use when matching registrations for this event',
`is_billing_required` tinyint DEFAULT 0 COMMENT 'if true than billing block is required this event'
,
PRIMARY KEY (`id`)
, INDEX `index_event_type_id`(
event_type_id
)
, INDEX `index_participant_listing_id`(
participant_listing_id
)
, INDEX `index_parent_event_id`(
parent_event_id
)
, CONSTRAINT FK_civicrm_event_loc_block_id FOREIGN KEY (`loc_block_id`) REFERENCES `civicrm_loc_block`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_event_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_event_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_event_dedupe_rule_group_id FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_participant
-- *
-- *******************************************************/
CREATE TABLE `civicrm_participant` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Participant Id',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact ID',
`event_id` int unsigned NOT NULL COMMENT 'FK to Event ID',
`status_id` int unsigned NOT NULL DEFAULT 1 COMMENT 'Participant status ID. FK to civicrm_participant_status_type. Default of 1 should map to status = Registered.',
`role_id` varchar(128) DEFAULT NULL COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.',
`register_date` datetime COMMENT 'When did contact register for event?',
`source` varchar(128) COMMENT 'Source of this event registration.',
`fee_level` text COMMENT 'Populate with the label (text) associated with a fee level for paid events with multiple levels. Note that\n we store the label value and not the key\n ',
`is_test` tinyint DEFAULT 0 ,
`is_pay_later` tinyint DEFAULT 0 ,
`fee_amount` decimal(20,2) COMMENT 'actual processor fee if known - may be 0.',
`registered_by_id` int unsigned DEFAULT NULL COMMENT 'FK to Participant ID',
`discount_id` int unsigned DEFAULT NULL COMMENT 'FK to Discount ID',
`fee_currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value derived from config setting.',
`campaign_id` int unsigned COMMENT 'The campaign for which this participant has been registered.',
`discount_amount` int unsigned COMMENT 'Discount Amount',
`cart_id` int unsigned COMMENT 'FK to civicrm_event_carts',
`must_wait` int COMMENT 'On Waiting List',
`transferred_to_contact_id` int unsigned DEFAULT NULL COMMENT 'FK to Contact ID'
,
PRIMARY KEY (`id`)
, INDEX `index_status_id`(
status_id
)
, INDEX `index_role_id`(
role_id
)
, CONSTRAINT FK_civicrm_participant_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_participant_event_id FOREIGN KEY (`event_id`) REFERENCES `civicrm_event`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_participant_status_id FOREIGN KEY (`status_id`) REFERENCES `civicrm_participant_status_type`(`id`) , CONSTRAINT FK_civicrm_participant_registered_by_id FOREIGN KEY (`registered_by_id`) REFERENCES `civicrm_participant`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_participant_discount_id FOREIGN KEY (`discount_id`) REFERENCES `civicrm_discount`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_participant_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_participant_cart_id FOREIGN KEY (`cart_id`) REFERENCES `civicrm_event_carts`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_participant_transferred_to_contact_id FOREIGN KEY (`transferred_to_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_participant_payment
-- *
-- *******************************************************/
CREATE TABLE `civicrm_participant_payment` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Participant Payment Id',
`participant_id` int unsigned NOT NULL COMMENT 'Participant Id (FK)',
`contribution_id` int unsigned NOT NULL COMMENT 'FK to contribution table.'
,
PRIMARY KEY (`id`)
, UNIQUE INDEX `UI_contribution_participant`(
contribution_id
, participant_id
)
, CONSTRAINT FK_civicrm_participant_payment_participant_id FOREIGN KEY (`participant_id`) REFERENCES `civicrm_participant`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_participant_payment_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_events_in_carts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_events_in_carts` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Event In Cart Id',
`event_id` int unsigned COMMENT 'FK to Event ID',
`event_cart_id` int unsigned COMMENT 'FK to Event Cart ID'
,
PRIMARY KEY (`id`)
, CONSTRAINT FK_civicrm_events_in_carts_event_id FOREIGN KEY (`event_id`) REFERENCES `civicrm_event`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_events_in_carts_event_cart_id FOREIGN KEY (`event_cart_id`) REFERENCES `civicrm_event_carts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_pledge_payment
-- *
-- * Pledge Payment
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pledge_payment` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`pledge_id` int unsigned NOT NULL COMMENT 'FK to Pledge table',
`contribution_id` int unsigned COMMENT 'FK to contribution table.',
`scheduled_amount` decimal(20,2) NOT NULL COMMENT 'Pledged amount for this payment (the actual contribution amount might be different).',
`actual_amount` decimal(20,2) COMMENT 'Actual amount that is paid as the Pledged installment amount.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`scheduled_date` datetime NOT NULL COMMENT 'The date the pledge payment is supposed to happen.',
`reminder_date` datetime COMMENT 'The date that the most recent payment reminder was sent.',
`reminder_count` int unsigned DEFAULT 0 COMMENT 'The number of payment reminders sent.',
`status_id` int unsigned
,
PRIMARY KEY (`id`)
, INDEX `index_contribution_pledge`(
contribution_id
, pledge_id
)
, INDEX `index_status`(
status_id
)
, CONSTRAINT FK_civicrm_pledge_payment_pledge_id FOREIGN KEY (`pledge_id`) REFERENCES `civicrm_pledge`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_pledge_payment_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_line_item
-- *
-- *******************************************************/
CREATE TABLE `civicrm_line_item` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Line Item',
`entity_table` varchar(64) NOT NULL COMMENT 'table which has the transaction',
`entity_id` int unsigned NOT NULL COMMENT 'entry in table',
`contribution_id` int unsigned COMMENT 'FK to civicrm_contribution',
`price_field_id` int unsigned COMMENT 'FK to civicrm_price_field',
`label` varchar(255) DEFAULT NULL COMMENT 'descriptive label for item - from price_field_value.label',
`qty` decimal(20,2) NOT NULL COMMENT 'How many items ordered',
`unit_price` decimal(20,2) NOT NULL COMMENT 'price of each item',
`line_total` decimal(20,2) NOT NULL COMMENT 'qty * unit_price',
`participant_count` int unsigned DEFAULT NULL COMMENT 'Participant count for field',
`price_field_value_id` int unsigned DEFAULT NULL COMMENT 'FK to civicrm_price_field_value',
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
`non_deductible_amount` decimal(20,2) NOT NULL DEFAULT 0.0 COMMENT 'Portion of total amount which is NOT tax deductible.',
`tax_amount` decimal(20,2) COMMENT 'tax of each item'
,
PRIMARY KEY (`id`)
, INDEX `index_entity`(
entity_table
, entity_id
)
, UNIQUE INDEX `UI_line_item_value`(
entity_table
, entity_id
, contribution_id
, price_field_value_id
, price_field_id
)
, CONSTRAINT FK_civicrm_line_item_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_line_item_price_field_id FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_line_item_price_field_value_id FOREIGN KEY (`price_field_value_id`) REFERENCES `civicrm_price_field_value`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_line_item_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
Reference in a new issue Copy permalink