Introduction

This is a summary of changes to the CiviCRM database schema between versions 4.4 and 4.5 . It excludes changes to comments, defaults or keys/indexes, in order to provide a compact summary. The intended audience is developers who want to see the main schema changes between major versions. If you are trying to check or fix a Civi database, see Ensuring Schema Integrity on Upgrades.

Method

I grabbed sql/civicrm.mysql from 4.4.0 and from 4.5.0, loaded each into a new database and used mysqldbcompare to compare them. This produced 1400 lines of output, which I've summarised below, ignoring changes which just affected comments, defaults or keys/indexes.

New tables

• civicrm_case_type
  - `id` int(10) unsigned AUTO_INCREMENT - 'Autoincremented type id' - PK,
  - `name` varchar(64) - 'Machine name for Case Type',
  - `title` varchar(64) - 'Natural language name for Case Type',
  - `description` varchar(255) - 'Description of the Case Type',
  - `is_active` tinyint(4) - 'Is this entry active?',
  - `is_reserved` tinyint(4) - 'Is this case type a predefined system type?',
  - `weight` int(11) - 'Ordering of the case types',
  - `definition` blob - 'xml definition of case type'.
• civicrm_system_log
  - `id` int(10) unsigned AUTO_INCREMENT - 'Primary key ID',
  - `message` varchar(128) - 'Standardized message',
  - `context` longtext - 'JSON encoded data',
  - `level` varchar(9) - 'error level per PSR3',
  - `timestamp` timestamp - 'Timestamp of when event occurred.',
  - `contact_id` int(10) unsigned - 'Optional Contact ID that created the log. Not an FK as we keep this regardless',
  - `hostname` varchar(128) - 'Optional Name of logging host'.

Modified tables

• civicrm_acl
  - `operation` changed from enum('All','View','Edit','Create','Delete','Grant','Revoke','Search') to varchar(8).
• civicrm_acl_contact_cache
  - `operation` changed from enum('All','View','Edit','Create','Delete','Grant','Revoke') to varchar(8).
• civicrm_action_schedule
  - `start_action_unit` changed from enum('hour','day','week','month','year') to varchar(8).
  - `repetition_frequency_unit` changed from enum('hour','day','week','month','year') to varchar(8).
  - `end_frequency_unit` changed from enum('hour','day','week','month','year') to varchar(8).
  - Added `sms_body_text` longtext - 'Content of the SMS text.'
  - Added `sms_template_id` int(10) unsigned - 'FK to the message template.'
  - Added `from_name` varchar(255) - 'Name in "from" field'.
  - Added `from_email` varchar(255) - 'Email address in "from" field'.
  - Added `mode` varchar(128) - 'Send the message as email or sms or both.'
  - Added `sms_provider_id` int(10) unsigned - FK to `civicrm_sms_provider` (`id`).
• civicrm_campaign_group
  - `group_type` changed from enum('Include','Exclude') to varchar(8).
• civicrm_case
  - `case_type_id` changed from varchar(128) to int(10) unsigned - FK to `civicrm_case_type` (`id`).
• civicrm_contact
  - `preferred_mail_format` changed from enum('Text','HTML','Both') to varchar(8).
  - Added `formal_title` varchar(64) - 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)'.
  - Added `communication_style_id` int(10) unsigned - 'Communication style (e.g. formal vs. familiar) to use with this contact. FK to communication styles in civicrm_option_value.'
• civicrm_contribution
  - Removed `honor_contact_id`.
  - Removed `honor_type_id`.
• civicrm_contribution_page
  - Removed `honor_block_is_active`.
  - Removed `honor_block_title`.
  - Removed `honor_block_text`.
• civicrm_contribution_recur
  - `frequency_unit` changed from enum('day','week','month','year') to varchar(8).
• civicrm_contribution_soft
  - Added `soft_credit_type_id` int(10) unsigned - 'Soft Credit Type ID.Implicit FK to civicrm_option_value where option_group = soft_credit_type.'
• civicrm_custom_field
  - `data_type` changed from enum('String','Int','Float','Money','Memo','Date','Boolean','StateProvince','Country','File','Link','ContactReference') to varchar(16).
  - `html_type` changed from 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') to varchar(32).
  - Added `in_selector` tinyint(4) - 'Should the multi-record custom field values be displayed in tab table listing'.
• civicrm_custom_group
  - `style` enum('Tab','Inline') changed to varchar(15).
• civicrm_dashboard
  - Added `name` varchar(64) - 'Internal name of dashlet.'
• civicrm_dedupe_rule_group
  - `contact_type` changed from enum('Individual','Organization','Household') changed to varchar(12).
  - `used` changed from enum('Unsupervised','Supervised','General') to varchar(12).
• civicrm_event
  - Added `is_confirm_enabled` tinyint(4) - 'If false, the event booking confirmation screen gets skipped'.
  - Added `dedupe_rule_group_id` int(10) unsigned - 'Rule to use when matching registrations for this event' - FK to `civicrm_dedupe_rule_group` (`id`).
• civicrm_extension
  - `type` changed from enum('payment','search','report','module','sms') to varchar(8).
• civicrm_group
  - `visibility` changed from enum('User and User Admin Only','Public Pages') to varchar(24).
  - Added `modified_id` int(10) unsigned - 'FK to contact table.'
• civicrm_group_contact
  - `status` changed from enum('Added','Removed','Pending') to varchar(8).
• civicrm_job
  - `run_frequency` changed from enum('Hourly','Daily','Always') to varchar(8).
• civicrm_line_item
  - Added `contribution_id` int(10) unsigned - 'FK to civicrm_contribution'.
• civicrm_mailing
  - `visibility` changed from enum('User and User Admin Only','Public Pages') to varchar(40).
  - Added `hash` varchar(16) - 'Key for validating requests related to this mailing.'
• civicrm_mailing_bounce_type
  - `name` changed from enum('AOL','Away','DNS','Host','Inactive','Invalid','Loop','Quota','Relay','Spam','Syntax','Unknown') to varchar(8).
• civicrm_mailing_component
  - `component_type` changed from enum('Header','Footer','Subscribe','Welcome','Unsubscribe','OptOut','Reply','Resubscribe') to varchar(12).
• civicrm_mailing_group
  - `group_type` changed from enum('Include','Exclude','Base') to varchar(8).
• civicrm_mailing_job
  - `status` changed from enum('Scheduled','Running','Complete','Paused','Canceled') to varchar(12).
• civicrm_managed
  - Added `cleanup` varchar(32) - 'Policy on when to cleanup entity (always, never, unused)'.
• civicrm_mapping_field
  - `operator` changed from enum('=','!=','>','<','>=','<=','IN','NOT IN','LIKE','NOT LIKE','IS NULL','IS NOT NULL') to varchar(16).
• civicrm_membership_status
  - `start_event` changed from enum('start_date','end_date','join_date') to varchar(12).
  - `start_event_adjust_unit` changed from enum('day','month','year') to varchar(8).
  - `end_event` changed from enum('start_date','end_date','join_date') to varchar(12).
  - `end_event_adjust_unit` changed from enum('day','month','year') to varchar(12).
• civicrm_membership_type
  - `duration_unit` changed from enum('day','month','year','lifetime') to varchar(8).
  - `period_type` changed from enum('rolling','fixed') to varchar(8).
• civicrm_msg_template
  - Added `is_sms` tinyint(4) - 'Is this message template used for sms?'
• civicrm_option_group
  - Added `is_locked` tinyint(4) - 'A lock to remove the ability to add new options via the UI.'
• civicrm_participant_status_type
  - `class` changed from enum('Positive','Pending','Waiting','Negative') to varchar(8).
• civicrm_payment_processor
  - `signature` changed from varchar(255) to text.
• civicrm_pledge
  - `frequency_unit` changed from enum('day','week','month','year') to varchar(8).
  - Removed `honor_contact_id`.
  - Removed `honor_type_id`.
• civicrm_price_field
  - `html_type` changed from enum('Text','Select','Radio','CheckBox') to varchar(12).
• civicrm_product
  - `period_type` changed from enum('rolling','fixed') to varchar(8).
  - `duration_unit` changed from enum('day','month','week','year') to varchar(8).
  - `frequency_unit` changed from enum('day','month','week','year') to varchar(8).
• civicrm_relationship_type
  - `contact_type_a` changed from enum('Individual','Organization','Household') to varchar(12).
  - `contact_type_b` changed to enum('Individual','Organization','Household') to varchar(12).
• civicrm_subscription_history
  - `method` changed from enum('Admin','Email','Web','API') to varchar(8).
  - `status` changed from enum('Added','Removed','Pending','Deleted') to varchar(8).
• civicrm_uf_field
  - `visibility` changed from enum('User and User Admin Only','Public Pages','Public Pages and Listings') to varchar(32).
  - Added `website_type_id` int(10) unsigned - 'Website Type Id, if required'
• civicrm_uf_join
  - Added `module_data` longtext - 'Json serialized array of data used by the ufjoin.module'.
• civicrm_word_replacement
  - `match_type` changed from enum('wildcardMatch','exactMatch') to varchar(16).