-- /******************************************************* -- * -- * Clean up the exisiting tables -- * -- *******************************************************/ DROP TABLE IF EXISTS civicrm_mailing_event_unsubscribe; DROP TABLE IF EXISTS civicrm_mailing_event_trackable_url_open; DROP TABLE IF EXISTS civicrm_mailing_event_reply; DROP TABLE IF EXISTS civicrm_mailing_event_opened; DROP TABLE IF EXISTS civicrm_mailing_event_forward; DROP TABLE IF EXISTS civicrm_mailing_event_delivered; DROP TABLE IF EXISTS civicrm_mailing_event_bounce; DROP TABLE IF EXISTS civicrm_mailing_event_queue; DROP TABLE IF EXISTS civicrm_group_contact; DROP TABLE IF EXISTS civicrm_im; DROP TABLE IF EXISTS civicrm_email; DROP TABLE IF EXISTS civicrm_address; DROP TABLE IF EXISTS civicrm_activity_history; DROP TABLE IF EXISTS civicrm_activity; DROP TABLE IF EXISTS civicrm_contribution_page; DROP TABLE IF EXISTS civicrm_contribution; DROP TABLE IF EXISTS civicrm_mailing_event_confirm; DROP TABLE IF EXISTS civicrm_mailing_event_subscribe; DROP TABLE IF EXISTS civicrm_relationship; DROP TABLE IF EXISTS civicrm_subscription_history; DROP TABLE IF EXISTS civicrm_group; DROP TABLE IF EXISTS civicrm_uf_match; DROP TABLE IF EXISTS civicrm_phonecall; DROP TABLE IF EXISTS civicrm_phone; DROP TABLE IF EXISTS civicrm_note; DROP TABLE IF EXISTS civicrm_module_profile; DROP TABLE IF EXISTS civicrm_meeting; DROP TABLE IF EXISTS civicrm_mapping_field; DROP TABLE IF EXISTS civicrm_location; DROP TABLE IF EXISTS civicrm_entity_tag; DROP TABLE IF EXISTS civicrm_email_history; DROP TABLE IF EXISTS civicrm_custom_value; DROP TABLE IF EXISTS civicrm_custom_field; DROP TABLE IF EXISTS civicrm_custom_group; DROP TABLE IF EXISTS civicrm_county; DROP TABLE IF EXISTS civicrm_activity_type; DROP TABLE IF EXISTS civicrm_accept_credit_card; DROP TABLE IF EXISTS civicrm_payment_instrument; DROP TABLE IF EXISTS civicrm_financial_trxn; DROP TABLE IF EXISTS civicrm_contribution_type; 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_saved_search; DROP TABLE IF EXISTS civicrm_relationship_type; DROP TABLE IF EXISTS civicrm_organization; DROP TABLE IF EXISTS civicrm_individual; DROP TABLE IF EXISTS civicrm_household; DROP TABLE IF EXISTS civicrm_contact; DROP TABLE IF EXISTS civicrm_dupe_match; DROP TABLE IF EXISTS civicrm_validation; 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_tag; DROP TABLE IF EXISTS civicrm_state_province; DROP TABLE IF EXISTS civicrm_mobile_provider; DROP TABLE IF EXISTS civicrm_mapping; DROP TABLE IF EXISTS civicrm_location_type; DROP TABLE IF EXISTS civicrm_individual_suffix; DROP TABLE IF EXISTS civicrm_individual_prefix; DROP TABLE IF EXISTS civicrm_im_provider; DROP TABLE IF EXISTS civicrm_gender; DROP TABLE IF EXISTS civicrm_geo_coord; DROP TABLE IF EXISTS civicrm_acl_group_join; DROP TABLE IF EXISTS civicrm_acl_group; DROP TABLE IF EXISTS civicrm_domain; DROP TABLE IF EXISTS civicrm_custom_option; DROP TABLE IF EXISTS civicrm_country; DROP TABLE IF EXISTS civicrm_acl; -- /******************************************************* -- * -- * Create new tables -- * -- *******************************************************/ -- /******************************************************* -- * -- * civicrm_acl -- * -- * Access Control List -- * -- *******************************************************/ CREATE TABLE civicrm_acl ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID', 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('View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke') 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' , PRIMARY KEY ( 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' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_iso_code( name , iso_code ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_custom_option -- * -- * Stores info about options for custom fields of type 'Select' and 'Radio' -- * -- *******************************************************/ CREATE TABLE civicrm_custom_option ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Custom Option 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.', label varchar(64) COMMENT 'Label for option', value varchar(64) COMMENT 'Value of the option (when form is submitted)', weight int NOT NULL DEFAULT 1 COMMENT 'Order in which the options are displayed', is_active tinyint COMMENT 'Is this option active ?' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_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.', contact_name varchar(64) COMMENT 'Name of the person responsible for this domain', email_domain varchar(64) COMMENT 'The domain from which outgoing email for this domain will appear to originate' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name( name ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_acl_group -- * -- * ACL Groups. Each group represents a set of of ACL entries which should be treated as a unit. -- * -- *******************************************************/ CREATE TABLE civicrm_acl_group ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this group.', is_active tinyint DEFAULT 1 COMMENT 'Is this ACL Group currently active?', title varchar(64) COMMENT 'ACL Group Title' , PRIMARY KEY ( id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_acl_group_join -- * -- * Join table for Contacts and Groups to ACL Groups -- * -- *******************************************************/ CREATE TABLE civicrm_acl_group_join ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID', acl_group_id int unsigned NOT NULL COMMENT 'Foreign Key to ACL Group', entity_table varchar(64) NOT NULL COMMENT 'Table of the object joined to the ACL Group (Contact or Group)', entity_id int unsigned NOT NULL COMMENT 'ID of the group/contact object being joined' , PRIMARY KEY ( id ) , FOREIGN KEY (acl_group_id) REFERENCES civicrm_acl_group(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_geo_coord -- * -- * Geo code coordinate system info. -- * -- *******************************************************/ CREATE TABLE civicrm_geo_coord ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Geo Coord ID', coord_type enum('LatLong', 'Projected') COMMENT 'Projected or unprojected coordinates - projected coordinates (e.g. UTM) may be treated as cartesian by some modules.', coord_units enum('Degree', 'Grad', 'Radian', 'Foot', 'Meter') COMMENT 'If the coord_type is LATLONG, indicate the unit of angular measure: Degree|Grad|Radian. If the coord_type is Projected, indicate unit of distance measure: Foot|Meter.', coord_ogc_wkt_string text COMMENT 'Coordinate sys description in Open GIS Consortium WKT (well known text) format - see http://www.opengeospatial.org/docs/01-009.pdf this is provided for the convenience of the user or third party modules.' , PRIMARY KEY ( id ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_gender -- * -- *******************************************************/ CREATE TABLE civicrm_gender ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Gender ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this gender.', name varchar(64) COMMENT 'Gender Name.', weight int NOT NULL DEFAULT 1 COMMENT 'Controls Gender order in the select box.', is_active tinyint COMMENT 'Is this property active?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_im_provider -- * -- * IM Provider catalogue. -- * -- *******************************************************/ CREATE TABLE civicrm_im_provider ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'IM Provider ID', name varchar(64) COMMENT 'Name of IM Provider.', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact', is_reserved tinyint COMMENT 'Is this entry a predefined system option?', is_active tinyint COMMENT 'Is this entry active?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_individual_prefix -- * -- *******************************************************/ CREATE TABLE civicrm_individual_prefix ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Individual Prefix ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this individual prefix.', name varchar(64) COMMENT 'Individual Prefix Name.', weight int NOT NULL DEFAULT 1 COMMENT 'Controls Individual Prefix order in the select box.', is_active tinyint COMMENT 'Is this property active?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_individual_suffix -- * -- *******************************************************/ CREATE TABLE civicrm_individual_suffix ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Individual Suffix ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this individual suffix.', name varchar(64) COMMENT 'Individual Suffix Name.', weight int NOT NULL DEFAULT 1 COMMENT 'Controls Individual Suffix order in the select box.', is_active tinyint COMMENT 'Is this property active?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this location type.', name varchar(64) COMMENT 'Location Type Name.', vcard_name varchar(4) 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_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(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', domain_id int unsigned NOT NULL COMMENT 'Domain to which this mapping belongs', name varchar(64) COMMENT 'Name of Mapping', description varchar(255) COMMENT 'Description of Mapping.', mapping_type enum('Export', 'Import', 'Export Contributions', 'Import Contributions') COMMENT 'Type of Mapping.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name( name ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_mobile_provider -- * -- * Mobile Provider catalogue. -- * -- *******************************************************/ CREATE TABLE civicrm_mobile_provider ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Mobile Provider ID', name varchar(64) COMMENT 'Name of Mobile Provider.', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact', is_reserved tinyint COMMENT 'Is this entry a predefined system option?', is_active tinyint COMMENT 'Is this entry active?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) 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 ) , FOREIGN KEY (country_id) REFERENCES civicrm_country(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_tag -- * -- * Provides support for flat or hierarchical classification of various types of entities (contacts, groups, actions...). -- * -- *******************************************************/ CREATE TABLE civicrm_tag ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Tag ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this tag', 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.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) , FOREIGN KEY (parent_id) REFERENCES civicrm_tag(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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this form.', is_active tinyint DEFAULT 1 COMMENT 'Is this form currently active? If false, hide all related fields for all sharing contexts.', form_type enum('CiviCRM Profile') COMMENT 'Type of form.', 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.' , PRIMARY KEY ( id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) 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 User Pages', 'Public User 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?', location_type_id int unsigned COMMENT 'Location type of this mapping, if required', phone_type varchar(64) COMMENT 'Phone type, if required' , PRIMARY KEY ( id ) , FOREIGN KEY (uf_group_id) REFERENCES civicrm_uf_group(id) , FOREIGN KEY (location_type_id) REFERENCES civicrm_location_type(id) ) 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 ) , FOREIGN KEY (uf_group_id) REFERENCES civicrm_uf_group(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_validation -- * -- * Stores core info about an extended (custom) property. Input form-related info is kept separately (in contact_form_field), so a property may be 'presented' in multiple form fields. -- * -- *******************************************************/ CREATE TABLE civicrm_validation ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Validation ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact', type enum('Email', 'Money', 'URL', 'Phone', 'Positive Integer', 'Variable Name', 'Range', 'Regular Expression Match', 'Regular Expression No Match') COMMENT 'List of rule built-in rule types. custom types may be added to ENUM via directory scan.', parameters varchar(255) COMMENT 'optional value(s) passed to validation function, e.g. a regular expression, min and max for Range, operator + number for Comparison type, etc.', function_name varchar(128) COMMENT 'custom validation function name. Class methods should be invoked using php syntax array(CLASS_NAME, FN_NAME)', description varchar(255) COMMENT 'Rule Description.' , PRIMARY KEY ( id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_dupe_match -- * -- * DupeMatch specific fields -- * -- *******************************************************/ CREATE TABLE civicrm_dupe_match ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique DupeMatch ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact', entity_table varchar(64) NOT NULL COMMENT 'Name Of Entity Table', rule varchar(255) NOT NULL COMMENT 'String that can Contains valid civicrm core or custom field name,parenthesis,,AND,OR ' , PRIMARY KEY ( id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_contact -- * -- * Three types of contacts are defined: Individual, Organization and Household. 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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact', contact_type enum('Individual', 'Organization', 'Household') COMMENT 'Type of Contact.', 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.', 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.', source varchar(255) COMMENT 'where domain_id contact come from, e.g. import, donate module insert...', preferred_communication_method enum('Phone', 'Email', 'Post') COMMENT 'What is the preferred mode of communication.', preferred_mail_format enum('Text', 'HTML', 'Both') DEFAULT "Text" COMMENT 'What is the preferred mode of sending an email.', do_not_phone tinyint DEFAULT 0 , do_not_email tinyint DEFAULT 0 , do_not_mail tinyint DEFAULT 0 , do_not_trade tinyint DEFAULT 0 , hash int unsigned NOT NULL COMMENT 'Key for validating requests related to this contact.', is_opt_out tinyint NOT NULL DEFAULT 0 COMMENT 'Has the contact opted out from the org?' , PRIMARY KEY ( id ) , INDEX index_sort_name( sort_name ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_household -- * -- * Define household specific properties -- * -- *******************************************************/ CREATE TABLE civicrm_household ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Household ID', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', household_name varchar(128) COMMENT 'Household Name.', primary_contact_id int unsigned COMMENT 'Optional FK to Primary Contact for this household.' , PRIMARY KEY ( id ) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (primary_contact_id) REFERENCES civicrm_contact(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_individual -- * -- * Define contact-individual specific properties. Extends civicrm_contact. -- * -- *******************************************************/ CREATE TABLE civicrm_individual ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Individual ID', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', 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', greeting_type enum('Formal', 'Informal', 'Honorific', 'Custom', 'Other') COMMENT 'Preferred greeting format.', custom_greeting varchar(128) COMMENT 'Custom greeting message.', job_title varchar(64) COMMENT 'Job Title', gender_id int unsigned COMMENT 'FK to gender ID', birth_date date COMMENT 'Date of birth', is_deceased tinyint DEFAULT 0 , phone_to_household_id int unsigned COMMENT 'OPTIONAL FK to civicrm_contact_household record. If NOT NULL, direct phone communications to household rather than individual location.', email_to_household_id int unsigned COMMENT 'OPTIONAL FK to civicrm_contact_household record. If NOT NULL, direct phone communications to household rather than individual location.', 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.' , PRIMARY KEY ( id ) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (prefix_id) REFERENCES civicrm_individual_prefix(id) , FOREIGN KEY (suffix_id) REFERENCES civicrm_individual_suffix(id) , FOREIGN KEY (gender_id) REFERENCES civicrm_gender(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_organization -- * -- * Define organization specific properties -- * -- *******************************************************/ CREATE TABLE civicrm_organization ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Organization ID', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', organization_name varchar(128) COMMENT 'Organization Name.', legal_name varchar(128) COMMENT 'Legal Name.', sic_code varchar(8) COMMENT 'Standard Industry Classification Code.', primary_contact_id int unsigned COMMENT 'Optional FK to Primary Contact for this organization.' , PRIMARY KEY ( id ) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (primary_contact_id) REFERENCES civicrm_contact(id) ) 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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact', name_a_b varchar(64) COMMENT 'name/label for relationship of contact_a to contact_b.', name_b_a varchar(64) COMMENT 'Optional name/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.', 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_domain_id( name_a_b , domain_id ) , UNIQUE INDEX UI_name_b_a_domain_id( name_b_a , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) 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', domain_id int unsigned NOT NULL COMMENT 'which organization/domain owns this search', query text COMMENT 'SQL query for this search', form_values text COMMENT 'Submitted form values for this search', is_active tinyint COMMENT 'Is this entry active?' , PRIMARY KEY ( id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(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 , domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this component', name varchar(64) COMMENT 'The name of this component', component_type enum('Header', 'Footer', 'Subscribe', 'Welcome', 'Unsubscribe', 'OptOut', 'Reply') 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 ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(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 , domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this mailing', header_id int unsigned NOT NULL COMMENT 'FK to the header component.', footer_id int unsigned NOT NULL COMMENT 'FK to the footer component.', reply_id int unsigned NOT NULL COMMENT 'FK to the auto-responder component.', unsubscribe_id int unsigned NOT NULL COMMENT 'FK to the unsubscribe component.', optout_id int unsigned NOT NULL 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 text COMMENT 'Body of the mailing in text format.', body_html text COMMENT 'Body of the mailing in html format.', is_template tinyint COMMENT 'Is this object a mailing template?', 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?' , PRIMARY KEY ( id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) , FOREIGN KEY (header_id) REFERENCES civicrm_mailing_component(id) , FOREIGN KEY (footer_id) REFERENCES civicrm_mailing_component(id) , FOREIGN KEY (reply_id) REFERENCES civicrm_mailing_component(id) , FOREIGN KEY (unsubscribe_id) REFERENCES civicrm_mailing_component(id) , FOREIGN KEY (optout_id) REFERENCES civicrm_mailing_component(id) ) 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') 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.' , PRIMARY KEY ( id ) , FOREIGN KEY (mailing_id) REFERENCES civicrm_mailing(id) ) 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 ) , FOREIGN KEY (mailing_id) REFERENCES civicrm_mailing(id) ) 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_retry tinyint COMMENT 'Is this job a retry?' , PRIMARY KEY ( id ) , FOREIGN KEY (mailing_id) REFERENCES civicrm_mailing(id) ) 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 ) , FOREIGN KEY (bounce_type_id) REFERENCES civicrm_mailing_bounce_type(id) ) 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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contribution type.', 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_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) 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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this gift class.', entity_table varchar(64) COMMENT 'physical tablename for entity being extended by this data, e.g. civicrm_contact', entity_id int unsigned NOT NULL COMMENT 'FK to record in the entity table specified by entity_table column.', 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 ) , INDEX index_entity( entity_table , entity_id ) , UNIQUE INDEX UI_ft_trxn_id_domain_id( trxn_id , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_payment_instrument -- * -- *******************************************************/ CREATE TABLE civicrm_payment_instrument ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT ' Payment Instrument ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this payment instrument.', name varchar(64) COMMENT ' Payment Instrument Name.', description varchar(255) COMMENT ' Payment Instrument Description.', 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_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_accept_credit_card -- * -- *******************************************************/ CREATE TABLE civicrm_accept_credit_card ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT ' Accept Credit Card ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this credit card.', name varchar(64) COMMENT ' Credit Card Type as defined by the payment processor.', title varchar(64) COMMENT 'Descriptive Credit Card Name.', is_reserved tinyint COMMENT 'Is this a predefined system object?', is_active tinyint COMMENT 'Is this property active?' , PRIMARY KEY ( id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_activity_type -- * -- *******************************************************/ CREATE TABLE civicrm_activity_type ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Activity Type ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this activity type.', name varchar(64) COMMENT 'Activity Type Name.', description varchar(255) COMMENT 'Activity Type Description.', is_reserved tinyint COMMENT 'Is this activity type a predefined system activity?', is_active tinyint COMMENT 'Is this property active?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_county -- * -- *******************************************************/ CREATE TABLE civicrm_county ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'County ID', name varchar(64) COMMENT 'Name of County', abbreviation varchar(4) COMMENT '2-4 Character Abbreviation of County', state_province_id int unsigned NOT NULL COMMENT 'ID of State / Province that County belongs' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_name_state_id( name , state_province_id ) , FOREIGN KEY (state_province_id) REFERENCES civicrm_state_province(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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact', 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', 'Phonecall', 'Meeting', 'Group') DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).', 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?' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_title_domain_id( title , domain_id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_custom_field -- * -- * Stores info about an extended (custom) property (data and form field info). -- * -- *******************************************************/ CREATE TABLE civicrm_custom_field ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Custom Field ID', custom_group_id int unsigned NOT NULL COMMENT 'FK to civicrm_custom_group.', name varchar(64) COMMENT 'Variable name/programmatic handle for this property.', label varchar(64) 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') COMMENT 'Controls location of data storage in extended_data table.', html_type enum('Text', 'TextArea', 'Select', 'Multi-Select', 'Radio', 'CheckBox', 'Select Date', 'Select State/Province', 'Select Country') 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.', weight int NOT NULL DEFAULT 1 COMMENT 'Controls field display order within an extended property group.', validation_id int unsigned COMMENT 'FK to civicrm_validation. Will be used for custom validation functions.', 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?', options_per_line int unsigned COMMENT 'number of options per line for checkbox and radio' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_label_custom_group_id( label , custom_group_id ) , FOREIGN KEY (custom_group_id) REFERENCES civicrm_custom_group(id) , FOREIGN KEY (validation_id) REFERENCES civicrm_validation(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_custom_value -- * -- * Data store for each extended properties. -- * -- *******************************************************/ CREATE TABLE civicrm_custom_value ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID', custom_field_id int unsigned NOT NULL COMMENT 'Foreign key to civicrm_ext_property.', entity_table varchar(64) COMMENT 'physical tablename for entity being extended by this data, e.g. civicrm_contact', entity_id int unsigned NOT NULL COMMENT 'FK to record in the entity table specified by entity_table column.', int_data int COMMENT 'stores data for ext property data_type = integer. This col supports signed integers.', float_data float COMMENT 'stores data for ext property data_type = float.', decimal_data decimal(20,2) COMMENT 'stores data for ext property data_type = money.', char_data varchar(255) COMMENT 'data for ext property data_type = text.', date_data date COMMENT 'data for ext property data_type = date.', memo_data text COMMENT 'data for ext property data_type = memo.' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , FOREIGN KEY (custom_field_id) REFERENCES civicrm_custom_field(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_email_history -- * -- * Email History can be linked to any object in the application. -- * -- *******************************************************/ CREATE TABLE civicrm_email_history ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Email History ID', subject varchar(255) COMMENT 'Subject of the email.', message text COMMENT 'Contents of the email.', contact_id int unsigned NOT NULL COMMENT 'FK to Contact who is sending this email', sent_date date COMMENT 'When was this email sent' , PRIMARY KEY ( id ) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(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', entity_table varchar(64) COMMENT 'physical tablename for entity being tagged, e.g. civicrm_contact', entity_id int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.', tag_id int unsigned NOT NULL COMMENT 'FK to civicrm_tag' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , FOREIGN KEY (tag_id) REFERENCES civicrm_tag(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_location -- * -- * Define location specific properties -- * -- *******************************************************/ CREATE TABLE civicrm_location ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Location 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.', location_type_id int unsigned NOT NULL COMMENT 'FK to Location Type ID', is_primary tinyint DEFAULT 0 COMMENT 'Is this the primary location for the contact. (allow only ONE primary location / contact.)' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , INDEX index_entity_location_type( entity_table , entity_id , location_type_id ) , FOREIGN KEY (location_type_id) REFERENCES civicrm_location_type(id) ) 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 varchar(64) COMMENT 'Phone type, if required', relationship_type_id int unsigned COMMENT 'Relationship type, if required' , PRIMARY KEY ( id ) , FOREIGN KEY (mapping_id) REFERENCES civicrm_mapping(id) , FOREIGN KEY (location_type_id) REFERENCES civicrm_location_type(id) , FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_meeting -- * -- * Meeting details stored here include contact, location, details. -- * -- *******************************************************/ CREATE TABLE civicrm_meeting ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Meeting ID', source_contact_id int unsigned NOT NULL COMMENT 'Contact ID of person scheduling or logging this meeting. This will generally an authenticated user.', target_entity_table varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.', target_entity_id int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.', subject varchar(64) COMMENT 'The subject/purpose of this meeting.', scheduled_date_time datetime COMMENT 'Date and time meeting is scheduled to occur.', duration_hours int unsigned COMMENT 'Planned or actual duration of meeting - hours.', duration_minutes int unsigned COMMENT 'Planned or actual duration of meeting - minutes.', location varchar(255) COMMENT 'Where will the meeting be held ?', details text COMMENT 'Details about the meeting (agenda, notes, etc).', status enum('Scheduled', 'Completed') COMMENT 'What is the status of this meeting? Completed meeting status results in activity history entry.', parent_id int unsigned COMMENT 'Parent meeting ID (if this is a follow-up item). This is not currently implemented' , PRIMARY KEY ( id ) , FOREIGN KEY (source_contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (parent_id) REFERENCES civicrm_meeting(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_module_profile -- * -- * This join table links modules (and sometimes module entities) to profiles (uf_group records). . -- * -- *******************************************************/ CREATE TABLE civicrm_module_profile ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this module profile entry.', module varchar(255) COMMENT 'Module Name.', entity_table varchar(64) COMMENT 'physical tablename for entity being extended by this data, e.g. civicrm_contact', entity_id int unsigned NOT NULL COMMENT 'FK to record in the entity table specified by entity_table column.', uf_group_id int unsigned NOT NULL COMMENT 'Which form does this field belong to.', weight int NOT NULL DEFAULT 1 COMMENT 'each internal or external module uses this to order multiple profiles associated with an entity_id' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) , FOREIGN KEY (uf_group_id) REFERENCES civicrm_uf_group(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 NOT NULL COMMENT 'FK to Contact ID creator', modified_date date COMMENT 'When was this note last modified/edited' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(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', location_id int unsigned NOT NULL COMMENT 'Which Location does this phone belong to.', phone varchar(32) COMMENT 'Complete phone number.', phone_type enum('Phone', 'Mobile', 'Fax', 'Pager') COMMENT 'What type of telecom device is this.', is_primary tinyint DEFAULT 0 COMMENT 'Is this the primary phone for this contact and location.', mobile_provider_id int unsigned COMMENT 'Which Mobile Provider does this phone belong to.' , PRIMARY KEY ( id ) , FOREIGN KEY (location_id) REFERENCES civicrm_location(id) , FOREIGN KEY (mobile_provider_id) REFERENCES civicrm_mobile_provider(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_phonecall -- * -- * Record details regarding phone call to a contact -- * -- *******************************************************/ CREATE TABLE civicrm_phonecall ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Phone call ID', source_contact_id int unsigned NOT NULL COMMENT 'Contact ID of person making the call. This will generally an authenticated user.', target_entity_table varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.', target_entity_id int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.', subject varchar(64) COMMENT 'Short description of the subject of this call.', scheduled_date_time datetime COMMENT 'Date and time phonecall is scheduled to be made.', duration_hours int unsigned COMMENT 'Planned or actual duration of call - hours.', duration_minutes int unsigned COMMENT 'Planned or actual duration of call - minutes.', 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 call.', status enum('Scheduled', 'Left Message', 'Unreachable', 'Completed') COMMENT 'What is the status of this phone call? Completed calls result in activity history entry.', parent_id int unsigned COMMENT 'Parent phone call ID (if this is a follow-up item). This is not currently implemented' , PRIMARY KEY ( id ) , FOREIGN KEY (source_contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (phone_id) REFERENCES civicrm_phone(id) , FOREIGN KEY (parent_id) REFERENCES civicrm_phonecall(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.', uf_id int unsigned NOT NULL COMMENT 'UF ID', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact (cached here for ease of use reasons)', email varchar(64) COMMENT 'Email address' , PRIMARY KEY ( id ) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ) 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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this group', name varchar(64) COMMENT 'Internal name of Group.', title varchar(64) COMMENT 'Name of Group.', description varchar(255) 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 User Pages', 'Public User Pages and Listings') DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_title_domain_id( title , domain_id ) , UNIQUE INDEX UI_name_domain_id( name , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(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 ) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (group_id) REFERENCES civicrm_group(id) ) 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 ?' , PRIMARY KEY ( id ) , FOREIGN KEY (contact_id_a) REFERENCES civicrm_contact(id) , FOREIGN KEY (contact_id_b) REFERENCES civicrm_contact(id) , FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id) ) 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 ) , FOREIGN KEY (group_id) REFERENCES civicrm_group(id) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ) 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 ) , FOREIGN KEY (event_subscribe_id) REFERENCES civicrm_mailing_event_subscribe(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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contribution class.', contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID', contribution_type_id int unsigned COMMENT 'FK to Contribution Type', 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.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_contrib_trxn_id_domain_id( trxn_id , domain_id ) , UNIQUE INDEX UI_contrib_invoice_id_domain_id( invoice_id , domain_id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id) , FOREIGN KEY (payment_instrument_id) REFERENCES civicrm_payment_instrument(id) ) 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', domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this page', 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', is_credit_card_only tinyint DEFAULT 0 COMMENT 'if true - processing logic must reject transaction at confirmation stage if pay method != credit card', 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 decimal(20,2) COMMENT 'the default amount allowed.', 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.', thankyou_title varchar(255) COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).', thankyou_text text COMMENT 'text and html allowed. displayed above result on success page', thankyou_footer text COMMENT 'Text and html allowed. displayed at the bottom of the success page. Common usage is to include link(s) to other pages such as tell-a-friend, etc.', is_email_receipt tinyint DEFAULT 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?' , PRIMARY KEY ( id ) , FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) , FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id) ) 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 NOT NULL COMMENT 'Contact ID of person scheduling or logging this Activity. This will generally an authenticated user.', activity_type_id int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.', target_entity_table varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.', target_entity_id int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.', subject varchar(64) COMMENT 'The subject/purpose of this meeting.', scheduled_date_time datetime COMMENT 'Date and time meeting is scheduled to occur.', duration_hours int unsigned COMMENT 'Planned or actual duration of meeting - hours.', duration_minutes int unsigned COMMENT 'Planned or actual duration of meeting - minutes.', location varchar(255) COMMENT 'Where will the meeting be held ?', details text COMMENT 'Details about the meeting (agenda, notes, etc).', status enum('Scheduled', 'Completed') COMMENT 'What is the status of this meeting? Completed meeting status results in activity history entry.', parent_id int unsigned COMMENT 'Parent meeting ID (if this is a follow-up item). This is not currently implemented' , PRIMARY KEY ( id ) , FOREIGN KEY (source_contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (activity_type_id) REFERENCES civicrm_activity_type(id) , FOREIGN KEY (parent_id) REFERENCES civicrm_activity(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_activity_history -- * -- * Record history for an entity in the crm module -- * -- *******************************************************/ CREATE TABLE civicrm_activity_history ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'table record id', entity_table varchar(64) COMMENT 'physical tablename for entity being tagged, e.g. civicrm_contact', entity_id int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column', activity_type varchar(64) COMMENT 'sortable label for this activity assigned be registering module or user (e.g. Phone Call)', module varchar(64) COMMENT 'Display name of module which registered this activity', callback varchar(64) COMMENT 'Function to call which will return URL for viewing details', activity_id int unsigned NOT NULL COMMENT 'FK to details item - passed to callback', activity_summary varchar(255) COMMENT 'brief description of activity for summary display - as populated by registering module', activity_date datetime DEFAULT 0 COMMENT 'when did this activity occur', relationship_id int unsigned COMMENT 'OPTIONAL FK to civicrm_relationship.id. Which relationship (of this contact) potentially triggered this activity, i.e. he donated because he was a Board Member of Org X / Employee of Org Y', group_id int unsigned COMMENT 'OPTIONAL FK to civicrm_group.id. Was this part of a group communication that triggered this activity?' , PRIMARY KEY ( id ) , INDEX index_entity( entity_table , entity_id ) , FOREIGN KEY (relationship_id) REFERENCES civicrm_relationship(id) , FOREIGN KEY (group_id) REFERENCES civicrm_group(id) ) 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', location_id int unsigned NOT NULL COMMENT 'Which Location does this address belong to.', 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 varchar(12) COMMENT 'Store both US (zip5) AND international postal codes. App is responsible for country/region appropriate validation.', postal_code_suffix varchar(12) COMMENT 'Store the suffix, like the +4 part in the USPS system.', usps_adc varchar(32) COMMENT 'USPS Bulk mailing code.', country_id int unsigned COMMENT 'Which Country does this address belong to.', geo_coord_id int unsigned COMMENT 'Which Geo_Coord does this address belong to.', geo_code_1 float COMMENT 'Latitude or UTM (Universal Transverse Mercator Grid) Northing.', geo_code_2 float COMMENT 'Longitude or UTM (Universal Transverse Mercator Grid) Easting.', timezone varchar(8) COMMENT 'Timezone expressed as a UTC offset - e.g. United States CST would be written as "UTC-6".', note varchar(255) COMMENT 'Optional misc info (e.g. delivery instructions) for this address.' , PRIMARY KEY ( id ) , INDEX index_street_name( street_name ) , INDEX index_city( city ) , FOREIGN KEY (location_id) REFERENCES civicrm_location(id) , FOREIGN KEY (county_id) REFERENCES civicrm_county(id) , FOREIGN KEY (state_province_id) REFERENCES civicrm_state_province(id) , FOREIGN KEY (country_id) REFERENCES civicrm_country(id) , FOREIGN KEY (geo_coord_id) REFERENCES civicrm_geo_coord(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', location_id int unsigned NOT NULL COMMENT 'Which Location does this email belong to.', email varchar(64) COMMENT 'Email address', is_primary tinyint DEFAULT 0 COMMENT 'Is this the primary email for this contact and location.', on_hold tinyint NOT NULL DEFAULT 0 COMMENT 'Is this address on bounce hold?', 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 ) , FOREIGN KEY (location_id) REFERENCES civicrm_location(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; -- /******************************************************* -- * -- * civicrm_im -- * -- * IM information for a specific location. -- * -- *******************************************************/ CREATE TABLE civicrm_im ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique IM ID', location_id int unsigned NOT NULL COMMENT 'Which Location does this IM identifier 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.' , PRIMARY KEY ( id ) , FOREIGN KEY (location_id) REFERENCES civicrm_location(id) , FOREIGN KEY (provider_id) REFERENCES civicrm_im_provider(id) ) 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 ) , FOREIGN KEY (group_id) REFERENCES civicrm_group(id) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) , FOREIGN KEY (location_id) REFERENCES civicrm_location(id) , FOREIGN KEY (email_id) REFERENCES civicrm_email(id) ) 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 ) , FOREIGN KEY (job_id) REFERENCES civicrm_mailing_job(id) , FOREIGN KEY (email_id) REFERENCES civicrm_email(id) , FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ) 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 ) , FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ) 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 ) , FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ) 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 NOT NULL COMMENT 'FK to EventQueue for destination', time_stamp datetime NOT NULL COMMENT 'When this forward event occurred.' , PRIMARY KEY ( id ) , FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) , FOREIGN KEY (dest_queue_id) REFERENCES civicrm_mailing_event_queue(id) ) 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 ) , FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ) 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 ) , FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ) 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 ) , FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) , FOREIGN KEY (trackable_url_id) REFERENCES civicrm_mailing_trackable_url(id) ) 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 ) , FOREIGN KEY (event_queue_id) REFERENCES civicrm_mailing_event_queue(id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;