Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- +--------------------------------------------------------------------+
-- | 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
-- *