-- +--------------------------------------------------------------------+ -- | CiviCRM version 3.1 | -- +--------------------------------------------------------------------+ -- | Copyright CiviCRM LLC (c) 2004-2010 | -- +--------------------------------------------------------------------+ -- | 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 | -- +--------------------------------------------------------------------+ -- +--------------------------------------------------------------------+ -- | CiviCRM version 3.1 | -- +--------------------------------------------------------------------+ -- | Copyright CiviCRM LLC (c) 2004-2010 | -- +--------------------------------------------------------------------+ -- | 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 | -- +--------------------------------------------------------------------+ -- /******************************************************* -- * -- * Clean up the exisiting tables -- * -- *******************************************************/ DROP TABLE IF EXISTS civicrm_pledge_payment; 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_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_uf_join; DROP TABLE IF EXISTS civicrm_uf_field; DROP TABLE IF EXISTS civicrm_uf_group; DROP TABLE IF EXISTS civicrm_county; DROP TABLE IF EXISTS civicrm_line_item; DROP TABLE IF EXISTS civicrm_price_field; DROP TABLE IF EXISTS civicrm_pledge; DROP TABLE IF EXISTS civicrm_case_activity; DROP TABLE IF EXISTS civicrm_activity_target; DROP TABLE IF EXISTS civicrm_activity_assignment; DROP TABLE IF EXISTS civicrm_activity; DROP TABLE IF EXISTS civicrm_membership_log; DROP TABLE IF EXISTS civicrm_membership_block; DROP TABLE IF EXISTS civicrm_membership; DROP TABLE IF EXISTS civicrm_pcp; DROP TABLE IF EXISTS civicrm_contribution_widget; DROP TABLE IF EXISTS civicrm_contribution_page; 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_confirm; DROP TABLE IF EXISTS civicrm_mailing_event_subscribe; 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_relationship; 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_dashboard_contact; DROP TABLE IF EXISTS civicrm_acl_cache; DROP TABLE IF EXISTS civicrm_openid; DROP TABLE IF EXISTS civicrm_timezone; DROP TABLE IF EXISTS civicrm_uf_match; DROP TABLE IF EXISTS civicrm_state_province; DROP TABLE IF EXISTS civicrm_preferences; DROP TABLE IF EXISTS civicrm_phone; DROP TABLE IF EXISTS civicrm_option_value; DROP TABLE IF EXISTS civicrm_note; DROP TABLE IF EXISTS civicrm_menu; DROP TABLE IF EXISTS civicrm_mapping_field; DROP TABLE IF EXISTS civicrm_log; DROP TABLE IF EXISTS civicrm_im; DROP TABLE IF EXISTS civicrm_entity_tag; DROP TABLE IF EXISTS civicrm_entity_file; DROP TABLE IF EXISTS civicrm_email; DROP TABLE IF EXISTS civicrm_dashboard; 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_price_set_entity; DROP TABLE IF EXISTS civicrm_price_set; DROP TABLE IF EXISTS civicrm_report_instance; DROP TABLE IF EXISTS civicrm_pledge_block; DROP TABLE IF EXISTS civicrm_openid_associations; DROP TABLE IF EXISTS civicrm_openid_nonces; DROP TABLE IF EXISTS civicrm_tell_friend; DROP TABLE IF EXISTS civicrm_grant; DROP TABLE IF EXISTS civicrm_case_contact; DROP TABLE IF EXISTS civicrm_case; DROP TABLE IF EXISTS civicrm_dedupe_rule; DROP TABLE IF EXISTS civicrm_dedupe_rule_group; DROP TABLE IF EXISTS civicrm_participant_status_type; DROP TABLE IF EXISTS civicrm_membership_status; DROP TABLE IF EXISTS civicrm_membership_type; DROP TABLE IF EXISTS civicrm_task_status; DROP TABLE IF EXISTS civicrm_task; DROP TABLE IF EXISTS civicrm_project; DROP TABLE IF EXISTS civicrm_premiums_product; DROP TABLE IF EXISTS civicrm_product; DROP TABLE IF EXISTS civicrm_premiums; DROP TABLE IF EXISTS civicrm_contribution_type; DROP TABLE IF EXISTS civicrm_contribution_recur; DROP TABLE IF EXISTS civicrm_mailing_spool; DROP TABLE IF EXISTS civicrm_mailing_bounce_pattern; DROP TABLE IF EXISTS civicrm_mailing_bounce_type; 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_mailing_component; 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_discount; DROP TABLE IF EXISTS civicrm_component; DROP TABLE IF EXISTS civicrm_worldregion; DROP TABLE IF EXISTS civicrm_tag; DROP TABLE IF EXISTS civicrm_preferences_date; DROP TABLE IF EXISTS civicrm_payment_processor_type; DROP TABLE IF EXISTS civicrm_payment_processor; DROP TABLE IF EXISTS civicrm_option_group; DROP TABLE IF EXISTS civicrm_navigation; DROP TABLE IF EXISTS civicrm_msg_template; DROP TABLE IF EXISTS civicrm_mapping; DROP TABLE IF EXISTS civicrm_mail_settings; DROP TABLE IF EXISTS civicrm_location_type; DROP TABLE IF EXISTS civicrm_file; DROP TABLE IF EXISTS civicrm_domain; DROP TABLE IF EXISTS civicrm_currency; -- /******************************************************* -- * -- * Create new tables -- * -- *******************************************************/ -- /******************************************************* -- * -- * 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_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', loc_block_id int unsigned COMMENT 'FK to Location Block 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)' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_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.', 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_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.' , PRIMARY KEY ( 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 text COMMENT 'Text formatted message', msg_html text 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?' , PRIMARY KEY ( 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', 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_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.', label varchar(255) COMMENT 'Option label.', description varchar(255) COMMENT 'Option group description.', is_reserved tinyint COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?', is_active tinyint COMMENT 'Is this option group active?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name( name ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_payment_processor -- * -- *******************************************************/ CREATE TABLE civicrm_payment_processor ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Payment Processor ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain is this match entry for', name varchar(64) COMMENT 'Payment Processor Name.', description varchar(255) COMMENT 'Payment Processor Description.', payment_processor_type varchar(255) COMMENT 'Payment Processor Type.', is_active tinyint COMMENT 'Is this processor active?', is_default tinyint COMMENT 'Is this processor the default?', is_test tinyint COMMENT 'Is this processor for a test site?', user_name varchar(255) , password varchar(255) , signature varchar(255) , url_site varchar(255) , url_api varchar(255) , url_recur varchar(255) , url_button varchar(255) , subject varchar(255) , class_name varchar(255) , billing_mode int unsigned NOT NULL COMMENT 'Billing Mode', is_recur tinyint COMMENT 'Can process recurring contributions', payment_type int unsigned DEFAULT 1 COMMENT 'Payment Type: Credit or Debit' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_test_domain_id( name , is_test , domain_id ) , CONSTRAINT FK_civicrm_payment_processor_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) 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(64) 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', is_recur tinyint COMMENT 'Can process recurring contributions', payment_type int unsigned DEFAULT 1 COMMENT 'Payment Type: Credit or Debit' , 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_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) 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' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name( name ) , CONSTRAINT FK_civicrm_tag_parent_id FOREIGN KEY (parent_id) REFERENCES civicrm_tag(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 namespace.' , PRIMARY KEY ( id ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_discount -- * -- * Stores discounts for events on the basis of date -- * -- *******************************************************/ CREATE TABLE civicrm_discount ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key', entity_table varchar(64) COMMENT 'physical tablename for entity being joined to discount, e.g. civicrm_event', entity_id int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.', option_group_id int unsigned NOT NULL COMMENT 'FK to civicrm_option_group', start_date date COMMENT 'Date when discount starts.', end_date date COMMENT 'Date when discount ends.' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , INDEX index_entity_option_id( entity_table , entity_id , option_group_id ) , CONSTRAINT FK_civicrm_discount_option_group_id FOREIGN KEY (option_group_id) REFERENCES civicrm_option_group(id) ON DELETE CASCADE ) 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 enum('All', 'View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke', 'Search') 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(64) 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.', external_identifier varchar(32) 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 'Nick Name.', legal_name varchar(128) COMMENT 'Legal Name.', home_URL varchar(128) COMMENT 'optional "home page" URL for this contact.', image_URL varchar(128) 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_mail_format enum('Text', 'HTML', 'Both') 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', 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', mail_to_household_id int unsigned COMMENT 'OPTIONAL FK to civicrm_contact_household record. If NOT NULL, direct mail communications to household rather than individual location.', 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.' , 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 UI_gender( gender_id ) , INDEX index_household_name( household_name ) , INDEX index_organization_name( organization_name ) , CONSTRAINT FK_civicrm_contact_mail_to_household_id FOREIGN KEY (mail_to_household_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL, 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 enum('All', 'View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke') 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 enum('Individual', 'Organization', 'Household') COMMENT 'If defined, contact_a in a relationship of this type must be a specific contact_type.', contact_type_b enum('Individual', 'Organization', 'Household') 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.', 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.', 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)?' , 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_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 enum('Header', 'Footer', 'Subscribe', 'Welcome', 'Unsubscribe', 'OptOut', 'Reply', 'Resubscribe') 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 -- * -- * Stores information about a mailing. -- * -- *******************************************************/ CREATE TABLE civicrm_mailing ( id int unsigned NOT NULL AUTO_INCREMENT , header_id int unsigned COMMENT 'FK to the header component.', footer_id int unsigned COMMENT 'FK to the footer component.', reply_id int unsigned COMMENT 'FK to the auto-responder component.', unsubscribe_id int unsigned COMMENT 'FK to the unsubscribe component.', resubscribe_id int unsigned , optout_id int unsigned COMMENT 'FK to the opt-out component.', name varchar(128) COMMENT 'Mailing Name.', from_name varchar(128) COMMENT 'From Header of mailing', from_email varchar(128) COMMENT 'From Email of mailing', replyto_email varchar(128) COMMENT 'Reply-To Email of mailing', subject varchar(128) COMMENT 'Subject of mailing', body_text longtext COMMENT 'Body of the mailing in text format.', body_html longtext COMMENT 'Body of the mailing in html format.', url_tracking tinyint COMMENT 'Should we track URL click-throughs for this mailing?', forward_replies tinyint COMMENT 'Should we forward replies back to the author?', auto_responder tinyint COMMENT 'Should we enable the auto-responder?', open_tracking tinyint COMMENT 'Should we track when recipients open/read this mailing?', is_completed tinyint COMMENT 'Has at least one job associated with this mailing finished?', msg_template_id int unsigned COMMENT 'FK to the message template.', override_verp tinyint DEFAULT 0 COMMENT 'Should we overrite VERP address in Reply-To', created_id int unsigned COMMENT 'FK to Contact ID who first created this mailing', created_date datetime COMMENT 'Date and time this mailing was created.', scheduled_id int unsigned COMMENT 'FK to Contact ID who scheduled this mailing', is_archived tinyint DEFAULT 0 COMMENT 'Is this mailing archived?' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_header_id FOREIGN KEY (header_id) REFERENCES civicrm_mailing_component(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_footer_id FOREIGN KEY (footer_id) REFERENCES civicrm_mailing_component(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_reply_id FOREIGN KEY (reply_id) REFERENCES civicrm_mailing_component(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_unsubscribe_id FOREIGN KEY (unsubscribe_id) REFERENCES civicrm_mailing_component(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_optout_id FOREIGN KEY (optout_id) REFERENCES civicrm_mailing_component(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_msg_template_id FOREIGN KEY (msg_template_id) REFERENCES civicrm_msg_template(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_created_id FOREIGN KEY (created_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_mailing_scheduled_id FOREIGN KEY (scheduled_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_group -- * -- * Stores information about the groups that participate in this mailing.. -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_group ( id int unsigned NOT NULL AUTO_INCREMENT , mailing_id int unsigned NOT NULL COMMENT 'The ID of a previous mailing to include/exclude recipients.', group_type enum('Include', 'Exclude', 'Base') COMMENT 'Are the members of the group included or excluded?.', entity_table varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.', entity_id int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.', search_id int COMMENT 'The filtering search. custom search id or -1 for civicrm api search', search_args text COMMENT 'The arguments to be sent to the search function' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_group_mailing_id FOREIGN KEY (mailing_id) REFERENCES civicrm_mailing(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_trackable_url -- * -- * Stores URLs for which we should track click-throughs from mailings -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_trackable_url ( id int unsigned NOT NULL AUTO_INCREMENT , url varchar(255) NOT NULL COMMENT 'The URL to be tracked.', mailing_id int unsigned NOT NULL COMMENT 'FK to the mailing' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_trackable_url_mailing_id FOREIGN KEY (mailing_id) REFERENCES civicrm_mailing(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_job -- * -- * Stores information about the job that executes this mailing -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_job ( id int unsigned NOT NULL AUTO_INCREMENT , mailing_id int unsigned NOT NULL COMMENT 'The ID of the mailing this Job will send.', scheduled_date datetime COMMENT 'date on which this job was scheduled.', start_date datetime COMMENT 'date on which this job was started.', end_date datetime COMMENT 'date on which this job ended.', status enum('Scheduled', 'Running', 'Complete', 'Paused', 'Canceled') COMMENT 'The state of this job', is_test tinyint DEFAULT 0 COMMENT 'Is this job for a test mail?' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_job_mailing_id FOREIGN KEY (mailing_id) REFERENCES civicrm_mailing(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_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 enum('AOL', 'Away', 'DNS', 'Host', 'Inactive', 'Invalid', 'Loop', 'Quota', 'Relay', 'Spam', 'Syntax', 'Unknown') 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_mailing_spool -- * -- * Stores the outbond mails -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_spool ( id int unsigned NOT NULL AUTO_INCREMENT , job_id int unsigned NOT NULL COMMENT 'The ID of the Job .', recipient_email text COMMENT 'The email of the receipients this mail is to be sent.', headers text COMMENT 'The header information of this mailing .', body text COMMENT 'The body of this mailing.', added_at datetime COMMENT 'date on which this job was added.', removed_at datetime COMMENT 'date on which this job was removed.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_spool_job_id FOREIGN KEY (job_id) REFERENCES civicrm_mailing_job(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_contribution_recur -- * -- *******************************************************/ CREATE TABLE civicrm_contribution_recur ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution Recur ID', contact_id int unsigned NOT NULL COMMENT 'Foreign key to civicrm_contact.id .', amount decimal(20,2) NOT NULL COMMENT 'Amount to be contributed or charged each recurrence.', frequency_unit enum('day', 'week', 'month', 'year') DEFAULT 'month' COMMENT 'Time units for recurrence of payment.', frequency_interval int unsigned NOT NULL COMMENT 'Number of time units for recurrence of payment.', installments int unsigned COMMENT 'Total number of payments to be made. Set this to 0 if this is an open-ended commitment i.e. no set end date.', start_date datetime NOT NULL COMMENT 'The date the first scheduled recurring contribution occurs.', create_date datetime NOT NULL COMMENT 'When this recurring contribution record was created.', modified_date datetime COMMENT 'Last updated date for this record. mostly the last time a payment was received', cancel_date datetime COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it', end_date datetime COMMENT 'Date this recurring contribution finished successfully', processor_id varchar(255) COMMENT 'Possibly needed to store a unique identifier for this recurring payment order - if this is available from the processor??', trxn_id varchar(255) COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method', invoice_id varchar(255) COMMENT 'unique invoice id, system generated or passed in', contribution_status_id int unsigned DEFAULT 1 , is_test tinyint DEFAULT 0 , cycle_day int unsigned NOT NULL DEFAULT 1 COMMENT 'Day in the period when the payment should be charged e.g. 1st of month, 15th etc.', next_sched_contribution datetime COMMENT 'At Groundspring this was used by the cron job which triggered payments. If we\'re not doing that but we know about payments, it might still be useful to store for display to org andor contributors.', failure_count int unsigned DEFAULT 0 COMMENT 'Number of failed charge attempts since last success. Business rule could be set to deactivate on more than x failures.', failure_retry_date datetime COMMENT 'At Groundspring we set a business rule to retry failed payments every 7 days - and stored the next scheduled attempt date there.', auto_renew tinyint NOT NULL DEFAULT 0 COMMENT 'Some systems allow contributor to set a number of installments - but then auto-renew the subscription or commitment if they do not cancel.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_contrib_trxn_id( trxn_id ) , UNIQUE INDEX UI_contrib_invoice_id( invoice_id ) , INDEX index_contribution_status( contribution_status_id ) , CONSTRAINT FK_civicrm_contribution_recur_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_contribution_type -- * -- *******************************************************/ CREATE TABLE civicrm_contribution_type ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution Type ID', name varchar(64) COMMENT 'Contribution Type Name.', accounting_code varchar(64) COMMENT 'Optional value for mapping contributions to accounting system codes for each type/category of contribution.', description varchar(255) COMMENT 'Contribution Type Description.', is_deductible tinyint DEFAULT 1 COMMENT 'Is this contribution 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_name( name ) ) 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
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.' , PRIMARY KEY ( id ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_product -- * -- * able - stores "product info" for premiums and can be used for non-incentive products -- * -- *******************************************************/ CREATE TABLE civicrm_product ( id int unsigned NOT NULL AUTO_INCREMENT , name varchar(255) NOT NULL COMMENT 'Required product/premium name', description text COMMENT 'Optional description of the product/premium.', sku varchar(50) COMMENT 'Optional product sku or code.', options text COMMENT 'Store comma-delimited list of color, size, etc. options for the product.', image varchar(255) COMMENT 'Full or relative URL to uploaded image - fullsize.', thumbnail varchar(255) COMMENT 'Full or relative URL to image thumbnail.', price decimal(20,2) COMMENT 'Sell price or market value for premiums. For tax-deductible contributions, this will be stored as non_deductible_amount in the contribution record.', min_contribution decimal(20,2) COMMENT 'Minimum contribution required to be eligible to select this premium.', cost decimal(20,2) COMMENT 'Actual cost of this product. Useful to determine net return from sale or using this as an incentive.', is_active tinyint NOT NULL COMMENT 'Disabling premium removes it from the premiums_premium join table below.', period_type enum('rolling', 'fixed') DEFAULT 'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month (e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) ', fixed_period_start_day int DEFAULT 0101 COMMENT 'Month and day (MMDD) that fixed period type subscription or membership starts.', duration_unit enum('day', 'month', 'week', 'year') DEFAULT 'year' , duration_interval int COMMENT 'Number of units for total duration of subscription, service, membership (e.g. 12 Months).', frequency_unit enum('day', 'month', 'week', 'year') DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.', frequency_interval int COMMENT 'Number of units for delivery frequency of subscription, service, membership (e.g. every 3 Months).' , PRIMARY KEY ( id ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_premiums_product -- * -- * joins premiums (settings) to individual product/premium items - determines which products are available for a given contribution page -- * -- *******************************************************/ CREATE TABLE civicrm_premiums_product ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution ID', premiums_id int unsigned NOT NULL COMMENT 'Foreign key to premiums settings record.', product_id int unsigned NOT NULL COMMENT 'Foreign key to each product object.', weight int unsigned NOT NULL , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_premiums_product_premiums_id FOREIGN KEY (premiums_id) REFERENCES civicrm_premiums(id) , CONSTRAINT FK_civicrm_premiums_product_product_id FOREIGN KEY (product_id) REFERENCES civicrm_product(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_project -- * -- * Instantiate projects, programs, campaigns, etc. -- * -- *******************************************************/ CREATE TABLE civicrm_project ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Project ID', title varchar(64) COMMENT 'Project name.', description text COMMENT 'Optional verbose description of the project. May be used for display - HTML allowed.', logo varchar(255) COMMENT 'Full or relative URL to optional uploaded logo image for project.', owner_entity_table varchar(64) NOT NULL COMMENT 'Name of table where project owner being referenced is stored (e.g. civicrm_contact or civicrm_group).', owner_entity_id int unsigned NOT NULL COMMENT 'Foreign key to project owner (contact, group, etc.).', start_date datetime COMMENT 'Project start date.', end_date datetime COMMENT 'Project end date.', is_active tinyint COMMENT 'Is this record active? For Projects: can tasks be created for it, does it appear on project listings, etc.', status_id int unsigned COMMENT 'Configurable status value (e.g. Planned, Active, Closed...). FK to civicrm_option_value.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_project_owner( id , owner_entity_table , owner_entity_id ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_task -- * -- * To-do items. Can be assigned to self or other entities. -- * -- *******************************************************/ CREATE TABLE civicrm_task ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Task ID', title varchar(64) COMMENT 'Task name.', description varchar(255) COMMENT 'Optional verbose description of the Task. May be used for display - HTML allowed.', task_type_id int unsigned COMMENT 'Configurable task type values (e.g. App Submit, App Review...). FK to civicrm_option_value.', owner_entity_table varchar(64) NOT NULL COMMENT 'Name of table where Task owner being referenced is stored (e.g. civicrm_contact or civicrm_group).', owner_entity_id int unsigned NOT NULL COMMENT 'Foreign key to Task owner (contact, group, etc.).', parent_entity_table varchar(64) COMMENT 'Name of table where optional Task parent is stored (e.g. civicrm_project, or civicrm_task for sub-tasks).', parent_entity_id int unsigned COMMENT 'Optional foreign key to Task Parent (project, another task, etc.).', due_date datetime COMMENT 'Task due date.', priority_id int unsigned COMMENT 'Configurable priority value (e.g. Critical, High, Medium...). FK to civicrm_option_value.', task_class varchar(255) COMMENT 'Optional key to a process class related to this task (e.g. CRM_Quest_PreApp).', is_active tinyint COMMENT 'Is this record active? For tasks: can it be assigned, does it appear on open task listings, etc.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_task_parent( id , parent_entity_table , parent_entity_id ) , UNIQUE INDEX UI_task_owner( id , owner_entity_table , owner_entity_id ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_task_status -- * -- * Tracks the responsible entity, optional target entity and status of a task. Tasks can have multiple task_status entries if assigned to multiple responsible entities and-or there are multiple targets. -- * -- *******************************************************/ CREATE TABLE civicrm_task_status ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Task ID', task_id int unsigned NOT NULL COMMENT 'Status is for which task.', responsible_entity_table varchar(64) NOT NULL COMMENT 'Entity responsible for this task_status instance (table where entity is stored e.g. civicrm_contact or civicrm_group).', responsible_entity_id int unsigned NOT NULL COMMENT 'Foreign key to responsible entity (contact, group, etc.).', target_entity_table varchar(64) NOT NULL COMMENT 'Optional target entity for this task_status instance, i.e. review this membership application-prospect member contact record is target (table where entity is stored e.g. civicrm_contact or civicrm_group).', target_entity_id int unsigned NOT NULL COMMENT 'Foreign key to target entity (contact, group, etc.).', status_detail text COMMENT 'Encoded array of status details used for programmatic progress reporting and tracking.', status_id int unsigned COMMENT 'Configurable status value (e.g. Not Started, In Progress, Completed, Deferred...). FK to civicrm_option_value.', create_date datetime COMMENT 'Date this record was created (date work on task started).', modified_date datetime COMMENT 'Date-time of last update to this task_status record.' , PRIMARY KEY ( id ) , INDEX index_task_status_responsible( task_id , responsible_entity_table , responsible_entity_id ) , INDEX index_task_status_target( task_id , target_entity_table , target_entity_id ) , CONSTRAINT FK_civicrm_task_status_task_id FOREIGN KEY (task_id) REFERENCES civicrm_task(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_membership_type -- * -- * Sites can configure multiple types of memberships. They encode the owner organization, fee, and the rules needed to set start and end (expire) dates when a member signs up for that type. -- * -- *******************************************************/ CREATE TABLE civicrm_membership_type ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id', domain_id int unsigned NOT NULL COMMENT 'Which Domain is this match entry for', name varchar(128) COMMENT 'Name of Membership Type', description varchar(255) COMMENT 'Description of Membership Type', member_of_contact_id int unsigned NOT NULL COMMENT 'Owner organization for this membership type. FK to Contact ID', contribution_type_id int unsigned NOT NULL COMMENT 'If membership is paid by a contribution - what contribution type should be used. FK to Contribution Type ID', minimum_fee decimal(20,2) DEFAULT 0 COMMENT 'Minimum fee for this membership (0 for free/complimentary memberships).', duration_unit enum('day', 'month', 'year', 'lifetime') COMMENT 'Unit in which membership period is expressed.', duration_interval int COMMENT 'Number of duration units in membership period (e.g. 1 year, 12 months).', period_type enum('rolling', 'fixed') COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.', fixed_period_start_day int COMMENT 'For fixed period memberships, month and day (mmdd) on which subscription/membership will start. Period start is back-dated unless after rollover day.', fixed_period_rollover_day int COMMENT 'For fixed period memberships, signups after this day (mmdd) rollover to next period.', relationship_type_id int unsigned COMMENT 'FK to Relationship Type ID', relationship_direction varchar(6) , visibility varchar(64) , weight int , renewal_msg_id int unsigned COMMENT 'FK to civicrm_msg_template.id', renewal_reminder_day int COMMENT 'Number of days prior to expiration to send renewal reminder', receipt_text_signup varchar(255) COMMENT 'Receipt Text for membership signup', receipt_text_renewal varchar(255) COMMENT 'Receipt Text for membership renewal', is_active tinyint DEFAULT 1 COMMENT 'Is this membership_type enabled' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_membership_type_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) , CONSTRAINT FK_civicrm_membership_type_member_of_contact_id FOREIGN KEY (member_of_contact_id) REFERENCES civicrm_contact(id) ON DELETE RESTRICT, CONSTRAINT FK_civicrm_membership_type_contribution_type_id FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id) , CONSTRAINT FK_civicrm_membership_type_relationship_type_id FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id) , CONSTRAINT FK_civicrm_membership_type_renewal_msg_id FOREIGN KEY (renewal_msg_id) REFERENCES civicrm_msg_template(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', start_event enum('start_date', 'end_date', 'join_date') COMMENT 'Event when this status starts.', start_event_adjust_unit enum('day', 'month', 'year') 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 enum('start_date', 'end_date', 'join_date') COMMENT 'Event after which this status ends.', end_event_adjust_unit enum('day', 'month', 'year') 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_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 enum('Positive', 'Pending', 'Waiting', 'Negative') 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_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 enum('Individual', 'Organization', 'Household') 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', level enum('Strict', 'Fuzzy') COMMENT 'Whether the rule should be used for cases where strict maching of the given contact type is required or a fuzzy one', is_default tinyint COMMENT 'Is this a default rule (one rule for every contact type + level combination should be default)', name varchar(64) COMMENT 'Name of the rule group' , 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 lenght 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_case -- * -- * This table stores information about cases grouping activities. -- * -- *******************************************************/ CREATE TABLE civicrm_case ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Case ID', case_type_id varchar(128) NOT NULL COMMENT 'Id of first case category.', subject varchar(128) COMMENT 'Short name of the case.', start_date date COMMENT 'Date on which given case starts.', end_date date COMMENT 'Date on which given case ends.', details text COMMENT 'Details about the meeting (agenda, notes, etc).', status_id int unsigned NOT NULL COMMENT 'Id of case status.', is_deleted tinyint DEFAULT 0 , PRIMARY KEY ( id ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_case_contact -- * -- * Joining table for case-contact associations. -- * -- *******************************************************/ CREATE TABLE civicrm_case_contact ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique case-contact association id', case_id int unsigned NOT NULL COMMENT 'Case ID of case-contact association.', contact_id int unsigned NOT NULL COMMENT 'Contact ID of contact record given case belongs to.' , PRIMARY KEY ( id ) , INDEX UI_case_contact_id( case_id , contact_id ) , CONSTRAINT FK_civicrm_case_contact_case_id FOREIGN KEY (case_id) REFERENCES civicrm_case(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_case_contact_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_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.', rationale text COMMENT 'Grant rationale.', status_id int unsigned NOT NULL COMMENT 'Id of Grant status.' , 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 ) 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_openid_nonces -- * -- * The OpenID nonce store for standalone login. -- * -- *******************************************************/ CREATE TABLE civicrm_openid_nonces ( id int unsigned NOT NULL AUTO_INCREMENT , server_url blob , timestamp int , salt char(40) , PRIMARY KEY ( id ) , UNIQUE INDEX nonce_index( server_url(255) , timestamp , salt ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_openid_associations -- * -- * The OpenID associations store for standalone login. -- * -- *******************************************************/ CREATE TABLE civicrm_openid_associations ( id int unsigned NOT NULL AUTO_INCREMENT , server_url blob , handle varchar(255) , secret blob , issued int , lifetime int , assoc_type varchar(64) , PRIMARY KEY ( id ) , UNIQUE INDEX server_url_handle_index( server_url(166) , handle(166) ) ) 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.' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_report_instance -- * -- * Users can save their report instance and put in a cron tab etc. -- * -- *******************************************************/ CREATE TABLE civicrm_report_instance ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Report Instance ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain is this instance for', title varchar(255) COMMENT 'Report Instance Title.', report_id varchar(64) NOT NULL COMMENT 'FK to civicrm_option_value for the report template', description varchar(255) COMMENT 'Report Instance description.', permission varchar(255) COMMENT 'permission required to be able to run this instance', form_values text COMMENT 'Submitted form values for this report', is_active tinyint COMMENT 'Is this entry active?', email_subject varchar(255) COMMENT 'Subject of email', email_to text COMMENT 'comma-separated list of email addresses to send the report to', email_cc text COMMENT 'comma-separated list of email addresses to send the report to', header text COMMENT 'comma-separated list of email addresses to send the report to', footer text COMMENT 'comma-separated list of email addresses to send the report to', navigation_id int unsigned COMMENT 'FK to navigation ID' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_report_instance_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) , CONSTRAINT FK_civicrm_report_instance_navigation_id FOREIGN KEY (navigation_id) REFERENCES civicrm_navigation(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_price_set -- * -- *******************************************************/ CREATE TABLE civicrm_price_set ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Set', domain_id int unsigned COMMENT 'Which Domain is this price-set for', name varchar(255) NOT NULL COMMENT 'Variable name/programmatic handle for this set of price fields.', title varchar(255) NOT NULL COMMENT 'Displayed title for the Price Set.', is_active tinyint DEFAULT 1 COMMENT 'Is this price set active', help_pre text COMMENT 'Description and/or help text to display before fields in form.', help_post text COMMENT 'Description and/or help text to display after fields in form.', javascript varchar(64) COMMENT 'Optional Javascript script function(s) included on the form with this price_set. Can be used for conditional', extends varchar(255) NOT NULL COMMENT 'What components are using this price set?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name( name ) , UNIQUE INDEX UI_title( title ) , CONSTRAINT FK_civicrm_price_set_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_price_set_entity -- * -- *******************************************************/ CREATE TABLE civicrm_price_set_entity ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Set Entity', entity_table varchar(64) NOT NULL COMMENT 'Table which uses this price set', entity_id int unsigned NOT NULL COMMENT 'Item in table', price_set_id int unsigned NOT NULL COMMENT 'price set being used' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_entity( entity_table , entity_id ) , CONSTRAINT FK_civicrm_price_set_entity_price_set_id FOREIGN KEY (price_set_id) REFERENCES civicrm_price_set(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_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(64) 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 datetime COMMENT 'When was the cache item created', expired_date datetime COMMENT 'When should the cache item expire' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_group_path( group_name , path ) , 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.', 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_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) COMMENT 'Friendly Name.', extends enum('Contact', 'Individual', 'Household', 'Organization', 'Location', 'Address', 'Contribution', 'Activity', 'Relationship', 'Group', 'Membership', 'Participant', 'Event', 'Grant', 'Pledge', 'Case') 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(64) COMMENT 'linking custom group for dynamic object', style enum('Tab', 'Inline') 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.' , 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.', label varchar(255) COMMENT 'Text for form field label (also friendly name for administering this custom property).', data_type enum('String', 'Int', 'Float', 'Money', 'Memo', 'Date', 'Boolean', 'StateProvince', 'Country', 'File', 'Link', 'ContactReference') NOT NULL COMMENT 'Controls location of data storage in extended_data table.', html_type enum('Text', 'TextArea', 'Select', 'Multi-Select', 'AdvMulti-Select', 'Radio', 'CheckBox', 'Select Date', 'Select State/Province', 'Select Country', 'Multi-Select Country', 'Multi-Select State/Province', 'File', 'Link', 'RichTextEditor', 'Autocomplete-Select') 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 unsigned COMMENT 'Date may be up to start_date_years years prior to tcurrent date ', end_date_years int unsigned COMMENT 'Date may be up to end_date_years years after to tcurrent 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' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_label_custom_group_id( label , 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_dashboard -- * -- * Table to store dashboard. -- * -- *******************************************************/ CREATE TABLE civicrm_dashboard ( id int unsigned NOT NULL AUTO_INCREMENT , domain_id int unsigned NOT NULL COMMENT 'Domain for dashboard', label varchar(255) COMMENT 'dashlet title', url varchar(255) COMMENT 'url in case of external dashlet', content text COMMENT 'dashlet content', permission varchar(255) COMMENT 'Permission for the dashlet', permission_operator varchar(3) COMMENT 'Permission Operator', column_no tinyint DEFAULT 0 COMMENT 'column no for this dashlet', is_minimized tinyint DEFAULT 0 COMMENT 'Is Minimized?', is_fullscreen tinyint DEFAULT 1 COMMENT 'Is Fullscreen?', is_active tinyint DEFAULT 0 COMMENT 'Is this dashlet active?', is_reserved tinyint DEFAULT 0 COMMENT 'Is this dashlet reserved?', weight int DEFAULT 0 COMMENT 'Ordering of the dashlets.', created_date datetime COMMENT 'When was content populated' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_dashboard_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_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(64) 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' , 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_entity_tag -- * -- * Tag entities (Contacts, Groups, Actions) to categories. -- * -- *******************************************************/ CREATE TABLE civicrm_entity_tag ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key', contact_id int unsigned NOT NULL COMMENT 'FK to contact table.', tag_id int unsigned NOT NULL COMMENT 'FK to civicrm_tag' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_contact_id_tag_id( contact_id , tag_id ) , CONSTRAINT FK_civicrm_entity_tag_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_entity_tag_tag_id FOREIGN KEY (tag_id) REFERENCES civicrm_tag(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_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_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_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(64) 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.', 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 mappings).', operator enum('=', '!=', '>', '<', '>=', '<=', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE', 'IS NULL', 'IS NOT NULL') 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) COMMENT 'Menu Title', 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' , 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_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' , 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(255) NOT NULL COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.', value varchar(64) 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(64) 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 varchar(255) 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 , PRIMARY KEY ( id ) , INDEX index_option_group_id_value( value , option_group_id ) , INDEX index_option_group_id_name( option_group_id , name ) , 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_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 ) , 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_preferences -- * -- * Define preferences for the site and users -- * -- *******************************************************/ CREATE TABLE civicrm_preferences ( id int unsigned NOT NULL AUTO_INCREMENT , domain_id int unsigned NOT NULL COMMENT 'Which Domain is this menu item for', contact_id int unsigned COMMENT 'FK to Contact ID', is_domain tinyint COMMENT 'Is this the record for the domain setting?', contact_view_options varchar(128) COMMENT 'What tabs are displayed in the contact summary', contact_edit_options varchar(128) COMMENT 'What tabs are displayed in the contact edit', advanced_search_options varchar(128) COMMENT 'What tabs are displayed in the advanced search screen', user_dashboard_options varchar(128) COMMENT 'What tabs are displayed in the contact edit', address_options varchar(128) COMMENT 'What fields are displayed from the address table', address_format text COMMENT 'Format to display the address', mailing_format text COMMENT 'Format to display a mailing label', address_standardization_provider varchar(64) COMMENT 'object name of provider for address standarization', address_standardization_userid varchar(64) COMMENT 'user id for provider login', address_standardization_url varchar(255) COMMENT 'url of address standardization service', editor_id int unsigned COMMENT 'ID of the editor', mailing_backend text COMMENT 'Smtp Backend configuration.', navigation text COMMENT 'Store navigation for the Contact', contact_autocomplete_options varchar(255) COMMENT 'What Autocomplete has to return' , PRIMARY KEY ( id ) , INDEX index_contact_view_options( contact_view_options ) , INDEX index_contact_edit_options( contact_edit_options ) , INDEX index_advanced_search_options( advanced_search_options ) , INDEX index_user_dashboard_options( user_dashboard_options ) , INDEX index_address_options( address_options ) , CONSTRAINT FK_civicrm_preferences_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_preferences_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_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 ) , 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_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 date 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_dashboard_contact -- * -- * Table to store dashboard for each contact. -- * -- *******************************************************/ CREATE TABLE civicrm_dashboard_contact ( id int unsigned NOT NULL AUTO_INCREMENT , dashboard_id int unsigned NOT NULL COMMENT 'Dashboard ID', contact_id int unsigned NOT NULL COMMENT 'Contact ID', column_no tinyint DEFAULT 0 COMMENT 'column no for this widget', is_minimized tinyint DEFAULT 0 COMMENT 'Is Minimized?', is_fullscreen tinyint DEFAULT 1 COMMENT 'Is Fullscreen?', is_active tinyint DEFAULT 0 COMMENT 'Is this widget active?', weight int DEFAULT 0 COMMENT 'Ordering of the widgets.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_dashboard_contact_dashboard_id FOREIGN KEY (dashboard_id) REFERENCES civicrm_dashboard(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_dashboard_contact_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_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 enum('User and User Admin Only', 'Public Pages') 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 datetime COMMENT 'Date when we created the cache for a smart group', 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?' , 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) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_subscription_history -- * -- * History information of subscribe/unsubscribe actions -- * -- *******************************************************/ CREATE TABLE civicrm_subscription_history ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Internal Id', contact_id int unsigned NOT NULL COMMENT 'Contact Id', group_id int unsigned COMMENT 'Group Id', date datetime NOT NULL COMMENT 'Date of the (un)subscription', method enum('Admin', 'Email', 'Web', 'API') COMMENT 'How the (un)subscription was triggered', status enum('Added', 'Removed', 'Pending') COMMENT 'The state of the contact within the group', tracking varchar(255) COMMENT 'IP address or other tracking info' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_subscription_history_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_subscription_history_group_id FOREIGN KEY (group_id) REFERENCES civicrm_group(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_group_contact_cache -- * -- * Join table cache for 'static' groups. -- * -- *******************************************************/ CREATE TABLE civicrm_group_contact_cache ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key', group_id int unsigned NOT NULL COMMENT 'FK to civicrm_group', contact_id int unsigned NOT NULL COMMENT 'FK to civicrm_contact' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_contact_group( contact_id , group_id ) , CONSTRAINT FK_civicrm_group_contact_cache_group_id FOREIGN KEY (group_id) REFERENCES civicrm_group(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_contact_cache_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_group_nesting -- * -- * Provide parent-child relationships for groups -- * -- *******************************************************/ CREATE TABLE civicrm_group_nesting ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relationship ID', child_group_id int unsigned NOT NULL COMMENT 'ID of the child group', parent_group_id int unsigned NOT NULL COMMENT 'ID of the parent group' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_group_nesting_child_group_id FOREIGN KEY (child_group_id) REFERENCES civicrm_group(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_nesting_parent_group_id FOREIGN KEY (parent_group_id) REFERENCES civicrm_group(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_group_organization -- * -- * Integrate Organization information into Groups -- * -- *******************************************************/ CREATE TABLE civicrm_group_organization ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relationship ID', group_id int unsigned NOT NULL COMMENT 'ID of the group', organization_id int unsigned NOT NULL COMMENT 'ID of the Organization Contact' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_group_organization( group_id , organization_id ) , CONSTRAINT FK_civicrm_group_organization_group_id FOREIGN KEY (group_id) REFERENCES civicrm_group(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_organization_organization_id FOREIGN KEY (organization_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_relationship -- * -- * Relationship between any 2 types of contacts. -- * -- *******************************************************/ CREATE TABLE civicrm_relationship ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relationship ID', contact_id_a int unsigned NOT NULL COMMENT 'id of the first contact', contact_id_b int unsigned NOT NULL COMMENT 'id of the second contact', relationship_type_id int unsigned NOT NULL COMMENT 'id of the relationship', start_date date COMMENT 'date when the relationship started', end_date date COMMENT 'date when the relationship ended', is_active tinyint DEFAULT 1 COMMENT 'is the relationship active ?', description varchar(255) COMMENT 'Optional verbose description for the relationship.', is_permission_a_b tinyint DEFAULT 0 COMMENT 'is contact a has permission to view / edit contact and related data for contact b ?', is_permission_b_a tinyint DEFAULT 0 COMMENT 'is contact b has permission to view / edit contact and related data for contact a ?', case_id int unsigned DEFAULT NULL COMMENT 'FK to civicrm_case' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_relationship_contact_id_a FOREIGN KEY (contact_id_a) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_relationship_contact_id_b FOREIGN KEY (contact_id_b) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_relationship_relationship_type_id FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_relationship_case_id FOREIGN KEY (case_id) REFERENCES civicrm_case(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_queue -- * -- * A collection of all intended recipients of a job -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_queue ( id int unsigned NOT NULL AUTO_INCREMENT , job_id int unsigned NOT NULL COMMENT 'FK to Job', email_id int unsigned NOT NULL COMMENT 'FK to Email', contact_id int unsigned NOT NULL COMMENT 'FK to Contact', hash varchar(255) NOT NULL COMMENT 'Security hash' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_queue_job_id FOREIGN KEY (job_id) REFERENCES civicrm_mailing_job(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_queue_email_id FOREIGN KEY (email_id) REFERENCES civicrm_email(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_queue_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_bounce -- * -- * Tracks when and why an email bounced. -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_bounce ( id int unsigned NOT NULL AUTO_INCREMENT , event_queue_id int unsigned NOT NULL COMMENT 'FK to EventQueue', bounce_type_id int unsigned COMMENT 'What type of bounce was it?', bounce_reason varchar(255) COMMENT 'The reason the email bounced.', time_stamp datetime NOT NULL COMMENT 'When this bounce event occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_bounce_event_queue_id FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_delivered -- * -- * Tracks when a queued email is actually delivered to the MTA -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_delivered ( id int unsigned NOT NULL AUTO_INCREMENT , event_queue_id int unsigned NOT NULL COMMENT 'FK to EventQueue', time_stamp datetime NOT NULL COMMENT 'When this delivery event occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_delivered_event_queue_id FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_forward -- * -- * Tracks when a contact forwards a mailing to a (new) contact -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_forward ( id int unsigned NOT NULL AUTO_INCREMENT , event_queue_id int unsigned NOT NULL COMMENT 'FK to EventQueue', dest_queue_id int unsigned COMMENT 'FK to EventQueue for destination', time_stamp datetime NOT NULL COMMENT 'When this forward event occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_forward_event_queue_id FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_forward_dest_queue_id FOREIGN KEY (dest_queue_id) REFERENCES civicrm_mailing_event_queue(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_opened -- * -- * Tracks when a delivered email is opened by the recipient -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_opened ( id int unsigned NOT NULL AUTO_INCREMENT , event_queue_id int unsigned NOT NULL COMMENT 'FK to EventQueue', time_stamp datetime NOT NULL COMMENT 'When this open event occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_opened_event_queue_id FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_reply -- * -- * Tracks when a contact replies to a mailing -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_reply ( id int unsigned NOT NULL AUTO_INCREMENT , event_queue_id int unsigned NOT NULL COMMENT 'FK to EventQueue', time_stamp datetime NOT NULL COMMENT 'When this reply event occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_reply_event_queue_id FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_subscribe -- * -- * Tracks when a (new) contact subscribes to a group by email -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_subscribe ( id int unsigned NOT NULL AUTO_INCREMENT , group_id int unsigned NOT NULL COMMENT 'FK to Group', contact_id int unsigned NOT NULL COMMENT 'FK to Contact', hash varchar(255) NOT NULL COMMENT 'Security hash', time_stamp datetime NOT NULL COMMENT 'When this subscription event occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_subscribe_group_id FOREIGN KEY (group_id) REFERENCES civicrm_group(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_subscribe_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_confirm -- * -- * Tracks when a subscription event is confirmed by email -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_confirm ( id int unsigned NOT NULL AUTO_INCREMENT , event_subscribe_id int unsigned NOT NULL COMMENT 'FK to civicrm_mailing_event_subscribe', time_stamp datetime NOT NULL COMMENT 'When this confirmation event occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_confirm_event_subscribe_id FOREIGN KEY (event_subscribe_id) REFERENCES civicrm_mailing_event_subscribe(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_trackable_url_open -- * -- * Tracks when a TrackableURL is clicked by a recipient. -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_trackable_url_open ( id int unsigned NOT NULL AUTO_INCREMENT , event_queue_id int unsigned NOT NULL COMMENT 'FK to EventQueue', trackable_url_id int unsigned NOT NULL COMMENT 'FK to TrackableURL', time_stamp datetime NOT NULL COMMENT 'When this trackable URL open occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_trackable_url_open_event_queue_id FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_mailing_event_trackable_url_open_trackable_url_id FOREIGN KEY (trackable_url_id) REFERENCES civicrm_mailing_trackable_url(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mailing_event_unsubscribe -- * -- * Tracks when a recipient unsubscribes from a group/domain -- * -- *******************************************************/ CREATE TABLE civicrm_mailing_event_unsubscribe ( id int unsigned NOT NULL AUTO_INCREMENT , event_queue_id int unsigned NOT NULL COMMENT 'FK to EventQueue', org_unsubscribe tinyint NOT NULL COMMENT 'Unsubscribe at org- or group-level', time_stamp datetime NOT NULL COMMENT 'When this delivery event occurred.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_mailing_event_unsubscribe_event_queue_id FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_contribution_page -- * -- * A Contribution object store meta information about a single customized contribution page -- * -- *******************************************************/ CREATE TABLE civicrm_contribution_page ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution Id', title varchar(255) COMMENT 'Contribution Page title. For top of page display', intro_text text COMMENT 'Text and html allowed. Displayed below title.', contribution_type_id int unsigned NOT NULL COMMENT 'default Contribution type assigned to contributions submitted via this page, e.g. Contribution, Campaign Contribution', payment_processor_id int unsigned COMMENT 'Payment Processor for this contribution Page ', is_credit_card_only tinyint DEFAULT 0 COMMENT 'if true - processing logic must reject transaction at confirmation stage if pay method != credit card', is_monetary tinyint DEFAULT 1 COMMENT 'if true - allows real-time monetary transactions otherwise non-monetary transactions', is_recur tinyint DEFAULT 0 COMMENT 'if true - allows recurring contributions, valid only for PayPal_Standard', recur_frequency_unit varchar(128) COMMENT 'Supported recurring frequency units.', is_recur_interval tinyint DEFAULT 0 COMMENT 'if true - supports recurring intervals', is_pay_later tinyint DEFAULT 0 COMMENT 'if true - allows the user to send payment directly to the org later', pay_later_text text COMMENT 'The text displayed to the user in the main form', pay_later_receipt text COMMENT 'The receipt sent to the user instead of the normal receipt text', is_allow_other_amount tinyint DEFAULT 0 COMMENT 'if true, page will include an input text field where user can enter their own amount', default_amount_id int unsigned COMMENT 'FK to civicrm_option_value.', min_amount decimal(20,2) COMMENT 'if other amounts allowed, user can configure minimum allowed.', max_amount decimal(20,2) COMMENT 'if other amounts allowed, user can configure maximum allowed.', goal_amount decimal(20,2) COMMENT 'The target goal for this page, allows people to build a goal meter', thankyou_title varchar(255) COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).', thankyou_text text COMMENT 'text and html allowed. displayed above result on success page', thankyou_footer text COMMENT 'Text and html allowed. displayed at the bottom of the success page. Common usage is to include link(s) to other pages such as tell-a-friend, etc.', is_for_organization tinyint DEFAULT 0 COMMENT 'if true, signup is done on behalf of an organization', for_organization text COMMENT 'This text field is shown when is_for_organization is checked. For example - I am contributing on behalf on an organization.', is_email_receipt tinyint DEFAULT 1 COMMENT 'if true, receipt is automatically emailed to contact on success', receipt_from_name varchar(255) COMMENT 'FROM email name used for receipts generated by contributions to this contribution page.', receipt_from_email varchar(255) COMMENT 'FROM email address used for receipts generated by contributions to this contribution page.', cc_receipt varchar(255) COMMENT 'comma-separated list of email addresses to cc each time a receipt is sent', bcc_receipt varchar(255) COMMENT 'comma-separated list of email addresses to bcc each time a receipt is sent', receipt_text text COMMENT 'text to include above standard receipt info on receipt email. emails are text-only, so do not allow html for now', is_active tinyint COMMENT 'Is this property active?', footer_text text COMMENT 'Text and html allowed. Displayed at the bottom of the first page of the contribution wizard.', amount_block_is_active tinyint DEFAULT 1 COMMENT 'Is this property active?', honor_block_is_active tinyint COMMENT 'Should this contribution have the honor block enabled?', honor_block_title varchar(255) COMMENT 'Title for honor block.', honor_block_text text COMMENT 'text for honor block.', start_date datetime COMMENT 'Date and time that this page starts.', end_date datetime COMMENT 'Date and time that this page ends. May be NULL if no defined end date/time', created_id int unsigned COMMENT 'FK to civicrm_contact, who created this contribution page', created_date datetime COMMENT 'Date and time that contribution page was created.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_contribution_page_contribution_type_id FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id) , CONSTRAINT FK_civicrm_contribution_page_payment_processor_id FOREIGN KEY (payment_processor_id) REFERENCES civicrm_payment_processor(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_page_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_contribution_widget -- * -- * A Widget object to store meta information about a single customized contribution widget -- * -- *******************************************************/ CREATE TABLE civicrm_contribution_widget ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution Id', contribution_page_id int unsigned COMMENT 'The Contribution Page which triggered this contribution', is_active tinyint COMMENT 'Is this property active?', title varchar(255) COMMENT 'Widget title.', url_logo varchar(255) COMMENT 'URL to Widget logo', button_title varchar(255) COMMENT 'Button title.', about text COMMENT 'About description.', url_homepage varchar(255) COMMENT 'URL to Homepage.', color_title varchar(10) , color_button varchar(10) , color_bar varchar(10) , color_main_text varchar(10) , color_main varchar(10) , color_main_bg varchar(10) , color_bg varchar(10) , color_about_link varchar(10) , color_homepage_link varchar(10) , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_contribution_widget_contribution_page_id FOREIGN KEY (contribution_page_id) REFERENCES civicrm_contribution_page(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_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 , contribution_page_id int unsigned NOT NULL COMMENT 'The Contribution Page which triggered this pcp', 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.', referer varchar(255) DEFAULT NULL , is_active tinyint DEFAULT 0 COMMENT 'Is Personal Campaign Page enabled/active?' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_pcp_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_pcp_contribution_page_id FOREIGN KEY (contribution_page_id) REFERENCES civicrm_contribution_page(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_membership -- * -- * Contact Membership records. -- * -- *******************************************************/ CREATE TABLE civicrm_membership ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', membership_type_id int unsigned NOT NULL COMMENT 'FK to Membership Type', join_date date COMMENT 'Beginning of initial membership period (member since...).', start_date date COMMENT 'Beginning of current uninterrupted membership period.', end_date date COMMENT 'Current membership period expire date.', source varchar(128) , status_id int unsigned NOT NULL COMMENT 'FK to Membership Status', is_override tinyint COMMENT 'Admin users may set a manual status which overrides the calculated status. When this flag is true, automated status update scripts should NOT modify status for the record.', reminder_date date COMMENT 'When should a reminder be sent.', owner_membership_id int unsigned COMMENT 'Optional FK to Parent Membership.', is_test tinyint DEFAULT 0 , is_pay_later tinyint DEFAULT 0 , PRIMARY KEY ( id ) , INDEX index_owner_membership_id( owner_membership_id ) , CONSTRAINT FK_civicrm_membership_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_membership_type_id FOREIGN KEY (membership_type_id) REFERENCES civicrm_membership_type(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_status_id FOREIGN KEY (status_id) REFERENCES civicrm_membership_status(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_owner_membership_id FOREIGN KEY (owner_membership_id) REFERENCES civicrm_membership(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_membership_block -- * -- * A Membership Block stores admin configurable status options and rules -- * -- *******************************************************/ CREATE TABLE civicrm_membership_block ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id', entity_table varchar(64) COMMENT 'Name for Membership Status', entity_id int unsigned NOT NULL COMMENT 'FK to civicrm_contribution_page.id', membership_types varchar(255) COMMENT 'Membership types to be exposed by this block', membership_type_default int unsigned COMMENT 'Optional foreign key to membership_type', display_min_fee tinyint DEFAULT 1 COMMENT 'Display minimum membership fee', is_separate_payment tinyint DEFAULT 1 COMMENT 'Should membership transactions be processed separately', new_title varchar(255) COMMENT 'Title to display at top of block', new_text text COMMENT 'Text to display below title', renewal_title varchar(255) COMMENT 'Title for renewal', renewal_text text COMMENT 'Text to display for member renewal', is_required tinyint DEFAULT 0 COMMENT 'Is membership sign up optional', is_active tinyint DEFAULT 1 COMMENT 'Is this membership_block enabled' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_membership_block_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id) , CONSTRAINT FK_civicrm_membership_block_membership_type_default FOREIGN KEY (membership_type_default) REFERENCES civicrm_membership_type(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_membership_log -- * -- * Logs actions which affect a Membership record (signup, status override, renewal, etc.) -- * -- *******************************************************/ CREATE TABLE civicrm_membership_log ( id int unsigned NOT NULL AUTO_INCREMENT , membership_id int unsigned NOT NULL COMMENT 'FK to Membership table', status_id int unsigned NOT NULL COMMENT 'New status assigned to membership by this action. FK to Membership Status', start_date date COMMENT 'New membership period start date', end_date date COMMENT 'New membership period expiration date.', modified_id int unsigned COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.', modified_date date COMMENT 'Date this membership modification action was logged.', renewal_reminder_date date COMMENT 'The day we sent a renewal reminder' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_membership_log_membership_id FOREIGN KEY (membership_id) REFERENCES civicrm_membership(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_log_status_id FOREIGN KEY (status_id) REFERENCES civicrm_membership_status(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_log_modified_id FOREIGN KEY (modified_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_activity -- * -- * Other Activity details stored here include contact, location, details. -- * -- *******************************************************/ CREATE TABLE civicrm_activity ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Other Activity ID', source_contact_id int unsigned COMMENT 'Contact ID of the person scheduling or logging this Activity. Usually the authenticated user.', source_record_id int unsigned COMMENT 'Artificial FK to original transaction (e.g. contribution) IF it is not an Activity. Table can be figured out through activity_type_id, and further through component registry.', activity_type_id int unsigned NOT NULL DEFAULT 1 COMMENT 'FK to civicrm_option_value.id, that has to be valid, registered activity type.', subject varchar(255) COMMENT 'The subject/purpose/short description of the activity.', activity_date_time datetime COMMENT 'Date and time this activity is scheduled to occur. Formerly named scheduled_date_time.', duration int unsigned COMMENT 'Planned or actual duration of activity expressed in minutes. Conglomerate of former duration_hours and duration_minutes.', location varchar(255) COMMENT 'Location of the activity (optional, open text).', phone_id int unsigned COMMENT 'Phone ID of the number called (optional - used if an existing phone number is selected).', phone_number varchar(64) COMMENT 'Phone number in case the number does not exist in the civicrm_phone table.', details text COMMENT 'Details about the activity (agenda, notes, etc).', status_id int unsigned COMMENT 'ID of the status this activity is currently in. Foreign key to civicrm_option_value.', priority_id int unsigned COMMENT 'ID of the priority given to this activity. Foreign key to civicrm_option_value.', parent_id int unsigned COMMENT 'Parent meeting ID (if this is a follow-up item). This is not currently implemented', is_test tinyint DEFAULT 0 , medium_id int unsigned DEFAULT NULL COMMENT 'Activity Medium, Implicit FK to civicrm_option_value where option_group = encounter_medium.', is_auto tinyint DEFAULT 0 , relationship_id int unsigned DEFAULT NULL COMMENT 'FK to Relationship ID', is_current_revision tinyint DEFAULT 1 , original_id int unsigned COMMENT 'Activity ID of the first activity record in versioning chain.', is_deleted tinyint DEFAULT 0 , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_activity_source_contact_id FOREIGN KEY (source_contact_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_activity_phone_id FOREIGN KEY (phone_id) REFERENCES civicrm_phone(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_activity_parent_id FOREIGN KEY (parent_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_activity_relationship_id FOREIGN KEY (relationship_id) REFERENCES civicrm_relationship(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_activity_original_id FOREIGN KEY (original_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_activity_assignment -- * -- * Activity assignments -- * -- *******************************************************/ CREATE TABLE civicrm_activity_assignment ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Activity assignment id', activity_id int unsigned NOT NULL COMMENT 'Foreign key to the activity for this assignment.', assignee_contact_id int unsigned NOT NULL COMMENT 'Foreign key to the contact for this assignment.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_activity_assignee_contact_id( assignee_contact_id , activity_id ) , CONSTRAINT FK_civicrm_activity_assignment_activity_id FOREIGN KEY (activity_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_activity_assignment_assignee_contact_id FOREIGN KEY (assignee_contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_activity_target -- * -- * Activity targets -- * -- *******************************************************/ CREATE TABLE civicrm_activity_target ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Activity target id', activity_id int unsigned NOT NULL COMMENT 'Foreign key to the activity for this target.', target_contact_id int unsigned NOT NULL COMMENT 'Foreign key to the contact for this target.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_activity_target_contact_id( target_contact_id , activity_id ) , CONSTRAINT FK_civicrm_activity_target_activity_id FOREIGN KEY (activity_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_activity_target_target_contact_id FOREIGN KEY (target_contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_case_activity -- * -- * Joining table for case-activity associations. -- * -- *******************************************************/ CREATE TABLE civicrm_case_activity ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique case-activity association id', case_id int unsigned NOT NULL COMMENT 'Case ID of case-activity association.', activity_id int unsigned NOT NULL COMMENT 'Activity ID of case-activity association.' , PRIMARY KEY ( id ) , INDEX UI_case_activity_id( case_id , activity_id ) , CONSTRAINT FK_civicrm_case_activity_case_id FOREIGN KEY (case_id) REFERENCES civicrm_case(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_case_activity_activity_id FOREIGN KEY (activity_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_pledge -- * -- *******************************************************/ CREATE TABLE civicrm_pledge ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Pledge ID', contact_id int unsigned NOT NULL COMMENT 'Foreign key to civicrm_contact.id .', contribution_type_id int unsigned COMMENT 'FK to Contribution Type. This is propagated to contribution record when pledge payments are made.', contribution_page_id int unsigned COMMENT 'The Contribution Page which triggered this contribution', amount decimal(20,2) NOT NULL COMMENT 'Total pledged amount.', frequency_unit enum('day', 'week', 'month', 'year') DEFAULT 'month' COMMENT 'Time units for recurrence of pledge payments.', frequency_interval int unsigned NOT NULL DEFAULT 1 COMMENT 'Number of time units for recurrence of pledge payments.', frequency_day int unsigned NOT NULL DEFAULT 3 COMMENT 'Day in the period when the pledge payment is due e.g. 1st of month, 15th etc. Use this to set the scheduled dates for pledge payments.', installments int unsigned DEFAULT 1 COMMENT 'Total number of payments to be made.', start_date datetime NOT NULL COMMENT 'The date the first scheduled pledge occurs.', create_date datetime NOT NULL COMMENT 'When this pledge record was created.', acknowledge_date datetime COMMENT 'When a pledge acknowledgement message was sent to the contributor.', modified_date datetime COMMENT 'Last updated date for this pledge record.', cancel_date datetime COMMENT 'Date this pledge was cancelled by contributor.', end_date datetime COMMENT 'Date this pledge finished successfully (total pledge payments equal to or greater than pledged amount).', honor_contact_id int unsigned COMMENT 'FK to contact ID. Used when pledge is made in honor of another contact. This is propagated to contribution records when pledge payments are made.', honor_type_id int unsigned COMMENT 'Implicit FK to civicrm_option_value.', max_reminders int unsigned DEFAULT 1 COMMENT 'The maximum number of payment reminders to send for any given payment.', initial_reminder_day int unsigned DEFAULT 5 COMMENT 'Send initial reminder this many days prior to the payment due date.', additional_reminder_day int unsigned DEFAULT 5 COMMENT 'Send additional reminder this many days after last one sent, up to maximum number of reminders.', status_id int unsigned COMMENT 'Implicit foreign key to civicrm_option_values in the contribution_status option group.', is_test tinyint DEFAULT 0 , PRIMARY KEY ( id ) , INDEX index_status( status_id ) , CONSTRAINT FK_civicrm_pledge_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_pledge_contribution_type_id FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_pledge_contribution_page_id FOREIGN KEY (contribution_page_id) REFERENCES civicrm_contribution_page(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_pledge_honor_contact_id FOREIGN KEY (honor_contact_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_price_field -- * -- *******************************************************/ CREATE TABLE civicrm_price_field ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Field', price_set_id int unsigned NOT NULL COMMENT 'FK to civicrm_price_set', name varchar(255) NOT NULL COMMENT 'Variable name/programmatic handle for this field.', label varchar(255) NOT NULL COMMENT 'Text for form field label (also friendly name for administering this field).', html_type enum('Text', 'Select', 'Radio', 'CheckBox') NOT NULL , is_enter_qty tinyint DEFAULT 0 COMMENT 'Enter a quantity for this field?', help_pre text COMMENT 'Description and/or help text to display before this field.', help_post text COMMENT 'Description and/or help text to display after this field.', weight int DEFAULT 1 COMMENT 'Order in which the fields should appear', is_display_amounts tinyint DEFAULT 1 COMMENT 'Should the price be displayed next to the label for each option?', options_per_line int unsigned DEFAULT 1 COMMENT 'number of options per line for checkbox and radio', is_active tinyint DEFAULT 1 COMMENT 'Is this price field active', is_required tinyint DEFAULT 1 COMMENT 'Is this price field required (value must be > 1)', active_on datetime DEFAULT NULL COMMENT 'If non-zero, do not show this field before the date specified', expire_on datetime DEFAULT NULL COMMENT 'If non-zero, do not show this field after the date specified', javascript varchar(255) COMMENT 'Optional scripting attributes for field' , PRIMARY KEY ( id ) , INDEX index_name( name ) , CONSTRAINT FK_civicrm_price_field_price_set_id FOREIGN KEY (price_set_id) REFERENCES civicrm_price_set(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_line_item -- * -- *******************************************************/ CREATE TABLE civicrm_line_item ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Line Item', entity_table varchar(64) NOT NULL COMMENT 'table which has the transaction', entity_id int unsigned NOT NULL COMMENT 'entry in table', price_field_id int unsigned NOT NULL COMMENT 'FK to price_field', option_group_id int unsigned NOT NULL COMMENT 'FK to option group', label varchar(255) NOT NULL COMMENT 'descriptive label for item - from custom_option.label', qty int unsigned NOT NULL COMMENT 'How many items ordered', unit_price decimal(20,2) NOT NULL COMMENT 'price of each item', line_total decimal(20,2) NOT NULL COMMENT 'qty * unit_price' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , CONSTRAINT FK_civicrm_line_item_price_field_id FOREIGN KEY (price_field_id) REFERENCES civicrm_price_field(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_county -- * -- *******************************************************/ CREATE TABLE civicrm_county ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'County ID', name varchar(64) COMMENT 'Name of County', abbreviation varchar(4) COMMENT '2-4 Character Abbreviation of County', state_province_id int unsigned NOT NULL COMMENT 'ID of State / Province that County belongs' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_state_id( name , state_province_id ) , CONSTRAINT FK_civicrm_county_state_province_id FOREIGN KEY (state_province_id) REFERENCES civicrm_state_province(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_uf_group -- * -- * User framework groups. Each group represents a form which encompasses a set of fields defined in civicrm_uf_fields table. Initially will be used for CiviCRM Profile form(s). Subsequently we anticipate using this to define other public facing forms (e.g. online donation solicitation forms, mailing list preferences, etc.). -- * -- *******************************************************/ CREATE TABLE civicrm_uf_group ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID', is_active tinyint DEFAULT 1 COMMENT 'Is this form currently active? If false, hide all related fields for all sharing contexts.', group_type varchar(255) COMMENT 'This column will store a comma separated list of the type(s) of profile fields.', title varchar(64) COMMENT 'Form title.', help_pre text COMMENT 'Description and/or help text to display before fields in form.', help_post text COMMENT 'Description and/or help text to display after fields in form.', limit_listings_group_id int unsigned COMMENT 'Group id, foriegn key from civicrm_group', post_URL varchar(255) COMMENT 'Redirect to URL.', add_to_group_id int unsigned COMMENT 'foreign key to civicrm_group_id', add_captcha tinyint DEFAULT 0 COMMENT 'Should a CAPTCHA widget be included this Profile form.', is_map tinyint DEFAULT 0 COMMENT 'Do we want to map results from this profile.', is_edit_link tinyint DEFAULT 0 COMMENT 'Should edit link display in profile selector', is_uf_link tinyint DEFAULT 0 COMMENT 'Should we display a link to the website profile in profile selector', is_update_dupe tinyint DEFAULT 0 COMMENT 'Should we update the contact record if we find a duplicate', cancel_URL varchar(255) COMMENT 'Redirect to URL when Cancle button clik .', is_cms_user tinyint DEFAULT 0 COMMENT 'Should we create a cms user for this profile ', notify varchar(255) , is_reserved tinyint COMMENT 'Is this group reserved for use by some other CiviCRM functionality?', name varchar(64) COMMENT 'Name of the UF group for directly addressing it in the codebase', created_id int unsigned COMMENT 'FK to civicrm_contact, who created this UF group', created_date datetime COMMENT 'Date and time this UF group was created.' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_uf_group_limit_listings_group_id FOREIGN KEY (limit_listings_group_id) REFERENCES civicrm_group(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_uf_group_add_to_group_id FOREIGN KEY (add_to_group_id) REFERENCES civicrm_group(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_uf_group_created_id FOREIGN KEY (created_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_uf_field -- * -- * User Framework fields and their properties. -- * -- *******************************************************/ CREATE TABLE civicrm_uf_field ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID', uf_group_id int unsigned NOT NULL COMMENT 'Which form does this field belong to.', field_name varchar(64) COMMENT 'Name for CiviCRM field which is being exposed for sharing.', is_active tinyint DEFAULT 1 COMMENT 'Is this field currently shareable? If false, hide the field for all sharing contexts.', is_view tinyint DEFAULT 0 COMMENT 'the field is view only and not editable in user forms.', is_required tinyint DEFAULT 0 COMMENT 'Is this field required when included in a user or registration form?', weight int NOT NULL DEFAULT 1 COMMENT 'Controls field display order when user framework fields are displayed in registration and account editing forms.', help_post text COMMENT 'Description and/or help text to display after this field.', visibility enum('User and User Admin Only', 'Public Pages', 'Public Pages and Listings') DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.', in_selector tinyint DEFAULT 0 COMMENT 'Is this field included as a column in the selector table?', is_searchable tinyint DEFAULT 0 COMMENT 'Is this field included search form of profile?', location_type_id int unsigned COMMENT 'Location type of this mapping, if required', phone_type_id int unsigned COMMENT 'Phone Type Id, if required', label varchar(255) COMMENT 'To save label for fields.', field_type varchar(255) COMMENT 'This field saves field type (ie individual,household.. field etc).', is_reserved tinyint COMMENT 'Is this field reserved for use by some other CiviCRM functionality?' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_uf_field_uf_group_id FOREIGN KEY (uf_group_id) REFERENCES civicrm_uf_group(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_uf_field_location_type_id FOREIGN KEY (location_type_id) REFERENCES civicrm_location_type(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_uf_join -- * -- * User framework join table. This links various internal civicrm object with a profile. Initial use cases are the donation object and the user module -- * -- *******************************************************/ CREATE TABLE civicrm_uf_join ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID', is_active tinyint DEFAULT 1 COMMENT 'Is this join currently active?', module varchar(64) NOT NULL COMMENT 'Module which owns this uf_join instance, e.g. User Registration, CiviDonate, etc.', entity_table varchar(64) COMMENT 'Name of table where item being referenced is stored. Modules which only need a single collection of uf_join instances may choose not to populate entity_table and entity_id.', entity_id int unsigned COMMENT 'Foreign key to the referenced item.', weight int NOT NULL DEFAULT 1 COMMENT 'Controls display order when multiple user framework groups are setup for concurrent display.', uf_group_id int unsigned NOT NULL COMMENT 'Which form does this field belong to.' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , CONSTRAINT FK_civicrm_uf_join_uf_group_id FOREIGN KEY (uf_group_id) REFERENCES civicrm_uf_group(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_pcp_block -- * -- * A Personal Campaign Page Block stores admin configurable status options and rules -- * -- *******************************************************/ CREATE TABLE civicrm_pcp_block ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'PCP block Id', entity_table varchar(64) , entity_id int unsigned NOT NULL COMMENT 'FK to civicrm_contribution_page.id', supporter_profile_id int unsigned DEFAULT NULL COMMENT 'FK to civicrm_uf_group.id. Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?', is_approval_needed tinyint DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?', is_tellfriend_enabled tinyint DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?', tellfriend_limit int unsigned DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend', link_text varchar(255) DEFAULT NULL COMMENT 'Link text for PCP.', is_active tinyint DEFAULT 1 COMMENT 'Is Personal Campaign Page Block enabled/active?', notify_email varchar(255) DEFAULT NULL COMMENT 'If set, notification is automatically emailed to this email-address on create/update Personal Campaign Page' , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_pcp_block_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id) , CONSTRAINT FK_civicrm_pcp_block_supporter_profile_id FOREIGN KEY (supporter_profile_id) REFERENCES civicrm_uf_group(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_address -- * -- * Stores the physical street / mailing address. This format should be capable of storing ALL international addresses. -- * -- *******************************************************/ CREATE TABLE civicrm_address ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Address ID', contact_id int unsigned COMMENT 'FK to Contact ID', location_type_id int unsigned COMMENT 'Which Location does this address belong to.', is_primary tinyint DEFAULT 0 COMMENT 'Is this the primary address.', is_billing tinyint DEFAULT 0 COMMENT 'Is this the billing address.', street_address varchar(96) COMMENT 'Concatenation of all routable street address components (prefix, street number, street name, suffix, unit number OR P.O. Box). Apps should be able to determine physical location with this data (for mapping, mail delivery, etc.).', street_number int COMMENT 'Numeric portion of address number on the street, e.g. For 112A Main St, the street_number = 112.', street_number_suffix varchar(8) COMMENT 'Non-numeric portion of address number on the street, e.g. For 112A Main St, the street_number_suffix = A', street_number_predirectional varchar(8) COMMENT 'Directional prefix, e.g. SE Main St, SE is the prefix.', street_name varchar(64) COMMENT 'Actual street name, excluding St, Dr, Rd, Ave, e.g. For 112 Main St, the street_name = Main.', street_type varchar(8) COMMENT 'St, Rd, Dr, etc.', street_number_postdirectional varchar(8) COMMENT 'Directional prefix, e.g. Main St S, S is the suffix.', street_unit varchar(16) COMMENT 'Secondary unit designator, e.g. Apt 3 or Unit # 14, or Bldg 1200', supplemental_address_1 varchar(96) COMMENT 'Supplemental Address Information, Line 1', supplemental_address_2 varchar(96) COMMENT 'Supplemental Address Information, Line 2', supplemental_address_3 varchar(96) COMMENT 'Supplemental Address Information, Line 3', city varchar(64) COMMENT 'City, Town or Village Name.', county_id int unsigned COMMENT 'Which County does this address belong to.', state_province_id int unsigned COMMENT 'Which State_Province does this address belong to.', postal_code_suffix varchar(12) COMMENT 'Store the suffix, like the +4 part in the USPS system.', postal_code varchar(12) COMMENT 'Store both US (zip5) AND international postal codes. App is responsible for country/region appropriate validation.', usps_adc varchar(32) COMMENT 'USPS Bulk mailing code.', country_id int unsigned COMMENT 'Which Country does this address belong to.', geo_code_1 double COMMENT 'Latitude', geo_code_2 double COMMENT 'Longitude', timezone varchar(8) COMMENT 'Timezone expressed as a UTC offset - e.g. United States CST would be written as "UTC-6".', name varchar(255) , PRIMARY KEY ( id ) , INDEX index_location_type( location_type_id ) , INDEX index_is_primary( is_primary ) , INDEX index_is_billing( is_billing ) , INDEX index_street_name( street_name ) , INDEX index_city( city ) , CONSTRAINT FK_civicrm_address_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_address_county_id FOREIGN KEY (county_id) REFERENCES civicrm_county(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_address_state_province_id FOREIGN KEY (state_province_id) REFERENCES civicrm_state_province(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_address_country_id FOREIGN KEY (country_id) REFERENCES civicrm_country(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_loc_block -- * -- * Define location specific properties -- * -- *******************************************************/ CREATE TABLE civicrm_loc_block ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID', address_id int unsigned , email_id int unsigned , phone_id int unsigned , im_id int unsigned , address_2_id int unsigned , email_2_id int unsigned , phone_2_id int unsigned , im_2_id int unsigned , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_loc_block_address_id FOREIGN KEY (address_id) REFERENCES civicrm_address(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_email_id FOREIGN KEY (email_id) REFERENCES civicrm_email(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_phone_id FOREIGN KEY (phone_id) REFERENCES civicrm_phone(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_im_id FOREIGN KEY (im_id) REFERENCES civicrm_im(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_address_2_id FOREIGN KEY (address_2_id) REFERENCES civicrm_address(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_email_2_id FOREIGN KEY (email_2_id) REFERENCES civicrm_email(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_phone_2_id FOREIGN KEY (phone_2_id) REFERENCES civicrm_phone(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_loc_block_im_2_id FOREIGN KEY (im_2_id) REFERENCES civicrm_im(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_group_contact -- * -- * Join table sets membership for 'static' groups. Also used to store 'opt-out' entries for 'query' type groups (status = 'OUT') -- * -- *******************************************************/ CREATE TABLE civicrm_group_contact ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key', group_id int unsigned NOT NULL COMMENT 'FK to civicrm_group', contact_id int unsigned NOT NULL COMMENT 'FK to civicrm_contact', status enum('Added', 'Removed', 'Pending') COMMENT 'status of contact relative to membership in group', location_id int unsigned COMMENT 'Optional location to associate with this membership', email_id int unsigned COMMENT 'Optional email to associate with this membership' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_contact_group( contact_id , group_id ) , CONSTRAINT FK_civicrm_group_contact_group_id FOREIGN KEY (group_id) REFERENCES civicrm_group(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_contact_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_group_contact_location_id FOREIGN KEY (location_id) REFERENCES civicrm_loc_block(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_group_contact_email_id FOREIGN KEY (email_id) REFERENCES civicrm_email(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_contribution -- * -- *******************************************************/ CREATE TABLE civicrm_contribution ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution ID', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', contribution_type_id int unsigned COMMENT 'FK to Contribution Type', contribution_page_id int unsigned COMMENT 'The Contribution Page which triggered this contribution', payment_instrument_id int unsigned COMMENT 'FK to Payment Instrument', receive_date datetime COMMENT 'when was gift received', non_deductible_amount decimal(20,2) DEFAULT 0 COMMENT 'Portion of total amount which is NOT tax deductible. Equal to total_amount for non-deductible contribution types.', total_amount decimal(20,2) NOT NULL COMMENT 'Total amount of this contribution. Use market value for non-monetary gifts.', fee_amount decimal(20,2) COMMENT 'actual processor fee if known - may be 0.', net_amount decimal(20,2) COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.', trxn_id varchar(255) COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method', invoice_id varchar(255) COMMENT 'unique invoice id, system generated or passed in', currency varchar(64) NOT NULL COMMENT '3 character string, value derived from payment processor config setting.', cancel_date datetime COMMENT 'when was gift cancelled', cancel_reason text , receipt_date datetime COMMENT 'when (if) receipt was sent. populated automatically for online donations w/ automatic receipting', thankyou_date datetime COMMENT 'when (if) was donor thanked', source varchar(255) COMMENT 'Origin of this Contribution.', amount_level text , contribution_recur_id int unsigned COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.', honor_contact_id int unsigned COMMENT 'FK to contact ID', is_test tinyint DEFAULT 0 , is_pay_later tinyint DEFAULT 0 , contribution_status_id int unsigned DEFAULT 1 , honor_type_id int unsigned COMMENT 'Implicit FK to civicrm_option_value.', address_id int unsigned COMMENT 'Conditional foreign key to civicrm_address.id. We insert an address record for each contribution when we have associated billing name and address data.', check_number varchar(255) , PRIMARY KEY ( id ) , INDEX UI_contrib_payment_instrument_id( payment_instrument_id ) , UNIQUE INDEX UI_contrib_trxn_id( trxn_id ) , UNIQUE INDEX UI_contrib_invoice_id( invoice_id ) , INDEX index_contribution_status( contribution_status_id ) , INDEX received_date( receive_date ) , INDEX check_number( check_number ) , CONSTRAINT FK_civicrm_contribution_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_contribution_type_id FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_contribution_page_id FOREIGN KEY (contribution_page_id) REFERENCES civicrm_contribution_page(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_contribution_recur_id FOREIGN KEY (contribution_recur_id) REFERENCES civicrm_contribution_recur(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_honor_contact_id FOREIGN KEY (honor_contact_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_address_id FOREIGN KEY (address_id) REFERENCES civicrm_address(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_contribution_product -- * -- *******************************************************/ CREATE TABLE civicrm_contribution_product ( id int unsigned NOT NULL AUTO_INCREMENT , product_id int unsigned NOT NULL , contribution_id int unsigned NOT NULL , product_option varchar(255) COMMENT 'Option value selected if applicable - e.g. color, size etc.', quantity int , fulfilled_date date COMMENT 'Optional. Can be used to record the date this product was fulfilled or shipped.', start_date date COMMENT 'Actual start date for a time-delimited premium (subscription, service or membership)', end_date date COMMENT 'Actual end date for a time-delimited premium (subscription, service or membership)', comment text , PRIMARY KEY ( id ) , CONSTRAINT FK_civicrm_contribution_product_contribution_id FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_contribution_soft -- * -- *******************************************************/ CREATE TABLE civicrm_contribution_soft ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Soft Contribution ID', contribution_id int unsigned NOT NULL COMMENT 'FK to contribution table.', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', amount decimal(20,2) NOT NULL COMMENT 'Amount of this soft contribution.', pcp_id int unsigned DEFAULT NULL COMMENT 'FK to civicrm_pcp.id', pcp_display_in_roll tinyint DEFAULT 0 , pcp_roll_nickname varchar(255) DEFAULT NULL , pcp_personal_note varchar(255) DEFAULT NULL , PRIMARY KEY ( id ) , INDEX index_id( pcp_id ) , CONSTRAINT FK_civicrm_contribution_soft_contribution_id FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_soft_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_soft_pcp_id FOREIGN KEY (pcp_id) REFERENCES civicrm_pcp(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_financial_trxn -- * -- *******************************************************/ CREATE TABLE civicrm_financial_trxn ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Gift ID', contribution_id int unsigned NOT NULL COMMENT 'FK to contribution table.', trxn_date datetime NOT NULL , trxn_type enum('Debit', 'Credit') NOT NULL , total_amount decimal(20,2) NOT NULL COMMENT 'amount of transaction', fee_amount decimal(20,2) COMMENT 'actual processor fee if known - may be 0.', net_amount decimal(20,2) COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.', currency varchar(64) NOT NULL COMMENT '3 character string, value derived from payment processor config setting.', payment_processor varchar(64) NOT NULL COMMENT 'derived from Processor setting in civicrm.settings.php.', trxn_id varchar(255) NOT NULL COMMENT 'unique processor transaction id, bank id + trans id,... depending on payment_method', trxn_result_code varchar(255) COMMENT 'processor result code' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_ft_trxn_id( trxn_id ) , CONSTRAINT FK_civicrm_financial_trxn_contribution_id FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_membership_payment -- * -- * Membership Payment -- * -- *******************************************************/ CREATE TABLE civicrm_membership_payment ( id int unsigned NOT NULL AUTO_INCREMENT , membership_id int unsigned NOT NULL COMMENT 'FK to Membership table', contribution_id int unsigned COMMENT 'FK to contribution table.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_contribution_membership( contribution_id , membership_id ) , CONSTRAINT FK_civicrm_membership_payment_membership_id FOREIGN KEY (membership_id) REFERENCES civicrm_membership(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_membership_payment_contribution_id FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_event -- * -- *******************************************************/ CREATE TABLE civicrm_event ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Event', title varchar(255) COMMENT 'Event Title (e.g. Fall Fundraiser Dinner)', summary text COMMENT 'Brief summary of event. Text and html allowed. Displayed on Event Registration form and can be used on other CMS pages which need an event summary.', description text COMMENT 'Full description of event. Text and html allowed. Displayed on built-in Event Information screens.', event_type_id int unsigned DEFAULT 0 COMMENT 'Event Type ID.Implicit FK to civicrm_option_value where option_group = event_type.', participant_listing_id int unsigned DEFAULT 0 COMMENT 'Should we expose the participant list? Implicit FK to civicrm_option_value where option_group = participant_listing.', is_public tinyint DEFAULT 1 COMMENT 'Public events will be included in the iCal feeds. Access to private event information may be limited using ACLs.', start_date datetime COMMENT 'Date and time that event starts.', end_date datetime COMMENT 'Date and time that event ends. May be NULL if no defined end date/time', is_online_registration tinyint DEFAULT 0 COMMENT 'If true, include registration link on Event Info page.', registration_link_text varchar(255) COMMENT 'Text for link to Event Registration form which is displayed on Event Information screen when is_online_registration is true.', registration_start_date datetime COMMENT 'Date and time that online registration starts.', registration_end_date datetime COMMENT 'Date and time that online registration ends.', max_participants int unsigned DEFAULT NULL COMMENT 'Maximum number of registered participants to allow. After max is reached, a custom Event Full message is displayed. If NULL, allow unlimited number of participants.', event_full_text text COMMENT 'Message to display on Event Information page and INSTEAD OF Event Registration form if maximum participants are signed up. Can include email address/info about getting on a waiting list, etc. Text and html allowed.', is_monetary tinyint DEFAULT 0 COMMENT 'Is this a PAID event? If true, one or more fee amounts must be set and a Payment Processor must be configured for Online Event Registration.', contribution_type_id int unsigned DEFAULT 0 COMMENT 'Contribution type assigned to paid event registrations for this event. Required if is_monetary is true.', payment_processor_id int unsigned COMMENT 'Payment Processor for this Event (if is_monetary is true)', is_map tinyint DEFAULT 0 COMMENT 'Include a map block on the Event Information page when geocode info is available and a mapping provider has been specified?', is_active tinyint DEFAULT 0 COMMENT 'Is this Event enabled or disabled/cancelled?', fee_label varchar(255) , is_show_location tinyint DEFAULT 1 COMMENT 'If true, show event location.', loc_block_id int unsigned COMMENT 'FK to Location Block ID', default_role_id int unsigned DEFAULT 1 COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.', intro_text text COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.', footer_text text COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.', confirm_title varchar(255) DEFAULT NULL COMMENT 'Title for Confirmation page.', confirm_text text COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.', confirm_footer_text text COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.', is_email_confirm tinyint DEFAULT 0 COMMENT 'If true, confirmation is automatically emailed to contact on successful registration.', confirm_email_text text COMMENT 'text to include above standard event info on confirmation email. emails are text-only, so do not allow html for now', confirm_from_name varchar(255) COMMENT 'FROM email name used for confirmation emails.', confirm_from_email varchar(255) COMMENT 'FROM email address used for confirmation emails.', cc_confirm varchar(255) COMMENT 'comma-separated list of email addresses to cc each time a confirmation is sent', bcc_confirm varchar(255) COMMENT 'comma-separated list of email addresses to bcc each time a confirmation is sent', default_fee_id int unsigned COMMENT 'FK to civicrm_option_value.', default_discount_fee_id int unsigned COMMENT 'FK to civicrm_option_value.', thankyou_title varchar(255) DEFAULT NULL COMMENT 'Title for ThankYou page.', thankyou_text text COMMENT 'ThankYou Text.', thankyou_footer_text text COMMENT 'Footer message.', is_pay_later tinyint DEFAULT 0 COMMENT 'if true - allows the user to send payment directly to the org later', pay_later_text text COMMENT 'The text displayed to the user in the main form', pay_later_receipt text COMMENT 'The receipt sent to the user instead of the normal receipt text', is_multiple_registrations tinyint DEFAULT 0 COMMENT 'if true - allows the user to register multiple participants for event', allow_same_participant_emails tinyint DEFAULT 0 COMMENT 'if true - allows the user to register multiple registrations from same email address.', has_waitlist tinyint COMMENT 'Whether the event has waitlist support.', requires_approval tinyint COMMENT 'Whether participants require approval before they can finish registering.', expiration_time int unsigned COMMENT 'Expire pending but unconfirmed registrations after this many hours.', waitlist_text text COMMENT 'Text to display when the event is full, but participants can signup for a waitlist.', approval_req_text text COMMENT 'Text to display when the approval is required to complete registration for an event.', is_template tinyint COMMENT 'whether the event has template', template_title varchar(255) COMMENT 'Event Template Title', created_id int unsigned COMMENT 'FK to civicrm_contact, who created this event', created_date datetime COMMENT 'Date and time that event was created.' , PRIMARY KEY ( id ) , INDEX index_event_type_id( event_type_id ) , INDEX index_participant_listing_id( participant_listing_id ) , CONSTRAINT FK_civicrm_event_payment_processor_id FOREIGN KEY (payment_processor_id) REFERENCES civicrm_payment_processor(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_event_loc_block_id FOREIGN KEY (loc_block_id) REFERENCES civicrm_loc_block(id) ON DELETE SET NULL, CONSTRAINT FK_civicrm_event_created_id FOREIGN KEY (created_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_participant -- * -- *******************************************************/ CREATE TABLE civicrm_participant ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Participant Id', contact_id int unsigned DEFAULT 0 COMMENT 'FK to Contact ID', event_id int unsigned DEFAULT 0 COMMENT 'FK to Event ID', status_id int unsigned NOT NULL DEFAULT 1 COMMENT 'Participant status ID. FK to civicrm_participant_status_type. Default of 1 should map to status = Registered.', role_id int unsigned DEFAULT NULL COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.', register_date datetime COMMENT 'When did contact register for event?', source varchar(128) COMMENT 'Source of this event registration.', fee_level text COMMENT 'Populate with the label (text) associated with a fee level for paid events with multiple levels. Note that we store the label value and not the key', is_test tinyint DEFAULT 0 , is_pay_later tinyint DEFAULT 0 , fee_amount decimal(20,2) COMMENT 'actual processor fee if known - may be 0.', registered_by_id int unsigned DEFAULT NULL COMMENT 'FK to Participant ID', discount_id int unsigned DEFAULT NULL COMMENT 'FK to Discount ID', fee_currency varchar(64) DEFAULT NULL COMMENT '3 character string, value derived from config setting.' , PRIMARY KEY ( id ) , INDEX index_status_id( status_id ) , INDEX index_role_id( role_id ) , CONSTRAINT FK_civicrm_participant_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_participant_event_id FOREIGN KEY (event_id) REFERENCES civicrm_event(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_participant_status_id FOREIGN KEY (status_id) REFERENCES civicrm_participant_status_type(id) , CONSTRAINT FK_civicrm_participant_registered_by_id FOREIGN KEY (registered_by_id) REFERENCES civicrm_participant(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_participant_discount_id FOREIGN KEY (discount_id) REFERENCES civicrm_discount(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_participant_payment -- * -- *******************************************************/ CREATE TABLE civicrm_participant_payment ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Participant Payment Id', participant_id int unsigned NOT NULL COMMENT 'Participant Id (FK)', contribution_id int unsigned NOT NULL COMMENT 'FK to contribution table.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_contribution_participant( contribution_id , participant_id ) , CONSTRAINT FK_civicrm_participant_payment_participant_id FOREIGN KEY (participant_id) REFERENCES civicrm_participant(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_participant_payment_contribution_id FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_pledge_payment -- * -- * Pledge Payment -- * -- *******************************************************/ CREATE TABLE civicrm_pledge_payment ( id int unsigned NOT NULL AUTO_INCREMENT , pledge_id int unsigned NOT NULL COMMENT 'FK to Pledge table', contribution_id int unsigned COMMENT 'FK to contribution table.', scheduled_amount decimal(20,2) NOT NULL COMMENT 'Pledged amount for this payment (the actual contribution amount might be different).', scheduled_date datetime NOT NULL COMMENT 'The date the pledge payment is supposed to happen.', reminder_date datetime COMMENT 'The date that the most recent payment reminder was sent.', reminder_count int unsigned DEFAULT 0 COMMENT 'The number of payment reminders sent.', status_id int unsigned , PRIMARY KEY ( id ) , INDEX index_contribution_pledge( contribution_id , pledge_id ) , INDEX index_status( status_id ) , CONSTRAINT FK_civicrm_pledge_payment_pledge_id FOREIGN KEY (pledge_id) REFERENCES civicrm_pledge(id) ON DELETE CASCADE, CONSTRAINT FK_civicrm_pledge_payment_contribution_id FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;