Greetings Rick,
Thank you for your kind reply. This morning I just wrapped up completely rebuilding thd database in question. I found instructions on the vendor's wiki for validating integrity of the DB schema, and going through that was able to export/import rebuilding the DB from the raw schema while retaining client specific schema additions.
The poor performance and large tmp file issue persists after all of that work! :-(
Oh, and I also discovered a simple export of 2379 contacts ALSO generates a 2GB /tmp file. That query happens to succeed on the new server whereas fails on the legacy/production server.
Quote
I got lost in our/peer/current/production/5.0/5.5/new/etc. Are you saying that 5.5 is generating a bigger tmp table and is taking longer?
The current server runs MySQL 5.0.95
The sever this client is moving to runs MySQL 5.5.37
Both servers have the same performance problems.
A peer on the CiviCRM forum ran the same query against a much larger data set, and had the significantly faster execution time and drastically smaller MySQL tmp file size.
Better?
So, here goes with materials you requested:
The query for the Contribution Dashboard:
SELECT civicrm_contribution.id,
contact_a.id as contact_id,
contact_a.contact_type as `contact_type`,
contact_a.contact_sub_type as `contact_sub_type`,
contact_a.sort_name as `sort_name`,
contact_a.display_name as `display_name`,
civicrm_contribution.id as contribution_id,
civicrm_contribution.currency as `currency`,
civicrm_contribution.receive_date as `receive_date`,
civicrm_contribution.non_deductible_amount as `non_deductible_amount`,
civicrm_contribution.total_amount as `total_amount`,
civicrm_contribution.fee_amount as `fee_amount`,
civicrm_contribution.net_amount as `net_amount`,
civicrm_contribution.trxn_id as `trxn_id`,
civicrm_contribution.invoice_id as `invoice_id`,
civicrm_contribution.cancel_date as `cancel_date`,
civicrm_contribution.cancel_reason as `cancel_reason`,
civicrm_contribution.receipt_date as `receipt_date`,
civicrm_contribution.thankyou_date as `thankyou_date`,
civicrm_contribution.source as `contribution_source`,
civicrm_contribution.amount_level as `amount_level`,
civicrm_contribution.is_test as `is_test`,
civicrm_contribution.is_pay_later as `is_pay_later`,
contribution_status.value as contribution_status_id,
civicrm_contribution.check_number as `check_number`,
civicrm_contribution.campaign_id as contribution_campaign_id,
civicrm_financial_type.id as financial_type_id,
civicrm_financial_type.name as financial_type,
payment_instrument.id as instrument_id,
payment_instrument.label as `payment_instrument`,
civicrm_product.id as product_id,
civicrm_product.name as `product_name`,
civicrm_product.sku as `sku`,
civicrm_contribution_product.id as contribution_product_id,
civicrm_contribution_product.product_option as `product_option`,
civicrm_contribution_product.fulfilled_date as `fulfilled_date`,
civicrm_contribution_product.start_date as `contribution_start_date`,
civicrm_contribution_product.end_date as `contribution_end_date`,
civicrm_contribution.contribution_recur_id as `contribution_recur_id`,
civicrm_financial_account.id as financial_account_id,
civicrm_financial_account.accounting_code as accounting_code,
civicrm_note.note as contribution_note,
civicrm_batch.title as contribution_batch,
contribution_status.label as contribution_status,
payment_instrument.name as contribution_payment_instrument,
civicrm_contribution.check_number as contribution_check_number
FROM civicrm_contact contact_a
LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id
LEFT JOIN civicrm_entity_financial_account ON civicrm_entity_financial_account.entity_id = civicrm_contribution.financial_type_id
AND civicrm_entity_financial_account.entity_table = 'civicrm_financial_type'
INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id
INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship
AND cov.name = 'Income Account is'
INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
AND cog.name = 'account_relationship'
LEFT JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id
LEFT JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id
LEFT JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_contribution'
AND civicrm_contribution.id = civicrm_entity_batch.entity_id )
LEFT JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id
LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution'
AND civicrm_contribution.id = civicrm_note.entity_id )
LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')
LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
AND option_group_payment_instrument.id = payment_instrument.option_group_id )
LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')
LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
AND option_group_contribution_status.id = contribution_status.option_group_id )
WHERE ( civicrm_contribution.is_test = 0 )
AND (contact_a.is_deleted = 0)
GROUP BY civicrm_contribution.id
ORDER BY receive_date desc,
contact_a.id
LIMIT 0, 10
Schema for each table involved in the query above:
-- /*******************************************************
-- *
-- * civicrm_contact
-- *
-- * Contact objects are defined by a civicrm_contact record plus a related civicrm_contact_type record.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contact` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Contact ID',
`contact_type` varchar(64) COMMENT 'Type of Contact.',
`contact_sub_type` varchar(255) COMMENT 'May be used to over-ride contact view and edit templates.',
`do_not_email` tinyint DEFAULT 0 ,
`do_not_phone` tinyint DEFAULT 0 ,
`do_not_mail` tinyint DEFAULT 0 ,
`do_not_sms` tinyint DEFAULT 0 ,
`do_not_trade` tinyint DEFAULT 0 ,
`is_opt_out` tinyint NOT NULL DEFAULT 0 COMMENT 'Has the contact opted out from receiving all bulk email from the organization or site domain?',
`legal_identifier` varchar(32) COMMENT 'May be used for SSN, EIN/TIN, Household ID (census) or other applicable unique legal/government ID.',
`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 'Nickname.',
`legal_name` varchar(128) COMMENT 'Legal Name.',
`image_URL` varchar(255) COMMENT 'optional URL for preferred image (photo, logo, etc.) to display for this contact.',
`preferred_communication_method` varchar(255) COMMENT 'What is the preferred mode of communication.',
`preferred_language` varchar(5) COMMENT 'Which language is preferred for communication. FK to languages in civicrm_option_value.',
`preferred_mail_format` enum('Text', 'HTML', 'Both') DEFAULT "Both" COMMENT 'What is the preferred mode of sending an email.',
`hash` varchar(32) COMMENT 'Key for validating requests related to this contact.',
`api_key` varchar(32) COMMENT 'API Key for validating requests related to this contact.',
`source` varchar(255) COMMENT 'where contact come from, e.g. import, donate module insert...',
`first_name` varchar(64) COMMENT 'First Name.',
`middle_name` varchar(64) COMMENT 'Middle Name.',
`last_name` varchar(64) COMMENT 'Last Name.',
`prefix_id` int unsigned COMMENT 'Prefix or Title for name (Ms, Mr...). FK to prefix ID',
`suffix_id` int unsigned COMMENT 'Suffix for name (Jr, Sr...). FK to suffix ID',
`email_greeting_id` int unsigned COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Email Greeting.',
`email_greeting_custom` varchar(128) COMMENT 'Custom Email Greeting.',
`email_greeting_display` varchar(255) COMMENT 'Cache Email Greeting.',
`postal_greeting_id` int unsigned COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Postal Greeting.',
`postal_greeting_custom` varchar(128) COMMENT 'Custom Postal greeting.',
`postal_greeting_display` varchar(255) COMMENT 'Cache Postal greeting.',
`addressee_id` int unsigned COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Addressee.',
`addressee_custom` varchar(128) COMMENT 'Custom Addressee.',
`addressee_display` varchar(255) COMMENT 'Cache Addressee.',
`job_title` varchar(255) COMMENT 'Job Title',
`gender_id` int unsigned COMMENT 'FK to gender ID',
`birth_date` date COMMENT 'Date of birth',
`is_deceased` tinyint DEFAULT 0 ,
`deceased_date` date COMMENT 'Date of deceased',
`household_name` varchar(128) COMMENT 'Household Name.',
`primary_contact_id` int unsigned COMMENT 'Optional FK to Primary Contact for this household.',
`organization_name` varchar(128) COMMENT 'Organization Name.',
`sic_code` varchar(8) COMMENT 'Standard Industry Classification Code.',
`user_unique_id` varchar(255) COMMENT 'the OpenID (or OpenID-style http://username.domain/) unique identifier for this contact mainly used for logging in to CiviCRM',
`employer_id` int unsigned COMMENT 'OPTIONAL FK to civicrm_contact record.',
`is_deleted` tinyint NOT NULL DEFAULT 0 ,
`created_date` timestamp NULL DEFAULT NULL COMMENT 'When was the contact was created.',
`modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the contact (or closely related entity) was created or modified or deleted.'
,
PRIMARY KEY ( `id` )
, INDEX `index_contact_type`(
contact_type
)
, INDEX `index_contact_sub_type`(
contact_sub_type
)
, UNIQUE INDEX `UI_external_identifier`(
external_identifier
)
, INDEX `index_sort_name`(
sort_name
)
, INDEX `index_preferred_communication_method`(
preferred_communication_method
)
, INDEX `index_hash`(
hash
)
, INDEX `index_api_key`(
api_key
)
, INDEX `index_first_name`(
first_name
)
, INDEX `index_last_name`(
last_name
)
, INDEX `UI_prefix`(
prefix_id
)
, INDEX `UI_suffix`(
suffix_id
)
, INDEX `UI_gender`(
gender_id
)
, INDEX `index_household_name`(
household_name
)
, INDEX `index_organization_name`(
organization_name
)
, INDEX `index_is_deleted`(
is_deleted
)
, CONSTRAINT FK_civicrm_contact_primary_contact_id FOREIGN KEY (`primary_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contact_employer_id FOREIGN KEY (`employer_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contribution
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution ID',
`contact_id` int unsigned NOT NULL COMMENT 'FK to Contact ID',
`financial_type_id` int unsigned COMMENT 'FK to Financial Type for (total_amount - non_deductible_amount).',
`contribution_page_id` int unsigned COMMENT 'The Contribution Page which triggered this contribution',
`payment_instrument_id` int unsigned COMMENT 'FK to Payment Instrument',
`receive_date` datetime COMMENT '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 financial types.',
`total_amount` decimal(20,2) NOT NULL COMMENT 'Total amount of this contribution. Use market value for non-monetary gifts.',
`fee_amount` decimal(20,2) COMMENT 'actual processor fee if known - may be 0.',
`net_amount` decimal(20,2) COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',
`trxn_id` varchar(255) COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method',
`invoice_id` varchar(255) COMMENT 'unique invoice id, system generated or passed in',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`cancel_date` datetime COMMENT 'when was gift cancelled',
`cancel_reason` text ,
`receipt_date` datetime COMMENT 'when (if) receipt was sent. populated automatically for online donations w/ automatic receipting',
`thankyou_date` datetime COMMENT 'when (if) was donor thanked',
`source` varchar(255) COMMENT 'Origin of this Contribution.',
`amount_level` text ,
`contribution_recur_id` int unsigned COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.',
`honor_contact_id` int unsigned COMMENT 'FK to contact ID',
`is_test` tinyint DEFAULT 0 ,
`is_pay_later` tinyint DEFAULT 0 ,
`contribution_status_id` int unsigned DEFAULT 1 ,
`honor_type_id` int unsigned COMMENT 'Implicit FK to civicrm_option_value.',
`address_id` int unsigned COMMENT 'Conditional foreign key to civicrm_address.id. We insert an address record for each contribution when we have associated billing name and address data.',
`check_number` varchar(255) ,
`campaign_id` int unsigned COMMENT 'The campaign for which this contribution has been triggered.'
,
PRIMARY KEY ( `id` )
, INDEX `UI_contrib_payment_instrument_id`(
payment_instrument_id
)
, UNIQUE INDEX `UI_contrib_trxn_id`(
trxn_id
)
, UNIQUE INDEX `UI_contrib_invoice_id`(
invoice_id
)
, INDEX `index_contribution_status`(
contribution_status_id
)
, INDEX `received_date`(
receive_date
)
, INDEX `check_number`(
check_number
)
, CONSTRAINT FK_civicrm_contribution_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) , CONSTRAINT FK_civicrm_contribution_contribution_page_id FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_contribution_recur_id FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_honor_contact_id FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_address_id FOREIGN KEY (`address_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_contribution_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_financial_type
-- *
-- * Formerly civicrm_contribution_type merged into this table in 4.3
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_type` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID of original financial_type so you can search this table by the financial_type.id and then select the relevant version based on the timestamp',
`name` varchar(64) NOT NULL COMMENT 'Financial Type Name.',
`description` varchar(255) COMMENT 'Financial Type Description.',
`is_deductible` tinyint DEFAULT 1 COMMENT 'Is this financial type tax-deductible? If true, contributions of this type may be fully OR partially deductible - non-deductible amount is stored in the Contribution record.',
`is_reserved` tinyint COMMENT 'Is this a predefined system object?',
`is_active` tinyint COMMENT 'Is this property active?'
,
PRIMARY KEY ( `id` )
, UNIQUE INDEX `UI_id`(
id
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_entity_financial_account
-- *
-- * Map between an entity and a financial account, where there is a specific relationship between the financial account and the entity, e.g. Income Account for or AR Account for
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_financial_account` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`entity_table` varchar(64) NOT NULL COMMENT 'Links to an entity_table like civicrm_financial_type',
`entity_id` int unsigned NOT NULL COMMENT 'Links to an id in the entity_table, such as vid in civicrm_financial_type',
`account_relationship` int unsigned NOT NULL COMMENT 'FK to a new civicrm_option_value (account_relationship)',
`financial_account_id` int unsigned NOT NULL COMMENT 'FK to the financial_account_id'
,
PRIMARY KEY ( `id` )
, CONSTRAINT FK_civicrm_entity_financial_account_financial_account_id FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_financial_account
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_account` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) NOT NULL COMMENT 'Financial Account Name.',
`contact_id` int unsigned COMMENT 'FK to Contact ID that is responsible for the funds in this account',
`financial_account_type_id` int unsigned NOT NULL DEFAULT 3 COMMENT 'pseudo FK into civicrm_option_value.',
`accounting_code` varchar(64) COMMENT 'Optional value for mapping monies owed and received to accounting system codes.',
`account_type_code` varchar(64) COMMENT 'Optional value for mapping account types to accounting system account categories (QuickBooks Account Type Codes for example).',
`description` varchar(255) COMMENT 'Financial Type Description.',
`parent_id` int unsigned COMMENT 'Parent ID in account hierarchy',
`is_header_account` tinyint DEFAULT 0 COMMENT 'Is this a header account which does not allow transactions to be posted against it directly, but only to its sub-accounts?',
`is_deductible` tinyint DEFAULT 1 COMMENT 'Is this account tax-deductible?',
`is_tax` tinyint DEFAULT 0 COMMENT 'Is this account for taxes?',
`tax_rate` decimal(10,8) COMMENT 'The percentage of the total_amount that is due for this tax.',
`is_reserved` tinyint COMMENT 'Is this a predefined system object?',
`is_active` tinyint COMMENT 'Is this property active?',
`is_default` tinyint COMMENT 'Is this account the default one (or default tax one) for its financial_account_type?'
,
PRIMARY KEY ( `id` )
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_financial_account_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_financial_account`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_option_value
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_value` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option ID',
`option_group_id` int unsigned NOT NULL COMMENT 'Group which this option belongs to.',
`label` varchar(255) NOT NULL COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
`value` varchar(512) NOT NULL COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
`name` varchar(255) COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.',
`grouping` varchar(255) COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
`filter` int unsigned COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
`is_default` tinyint DEFAULT 0 COMMENT 'Is this the default option for the group?',
`weight` int unsigned NOT NULL COMMENT 'Controls display sort order.',
`description` text COMMENT 'Optional description.',
`is_optgroup` tinyint DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
`is_reserved` tinyint DEFAULT 0 COMMENT 'Is this a predefined system object?',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this option active?',
`component_id` int unsigned COMMENT 'Component that this option value belongs/caters to.',
`domain_id` int unsigned COMMENT 'Which Domain is this option value for',
`visibility_id` int unsigned DEFAULT NULL
,
PRIMARY KEY ( `id` )
, INDEX `index_option_group_id_value`(
value(128)
, option_group_id
)
, INDEX `index_option_group_id_name`(
name(128)
, option_group_id
)
, CONSTRAINT FK_civicrm_option_value_option_group_id FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_option_value_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`) , CONSTRAINT FK_civicrm_option_value_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_option_group
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option Group ID',
`name` varchar(64) NOT NULL COMMENT 'Option group name. Used as selection key by class properties which lookup options in civicrm_option_value.',
`title` varchar(255) COMMENT 'Option Group title.',
`description` varchar(255) COMMENT 'Option group description.',
`is_reserved` tinyint DEFAULT 1 COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?',
`is_active` tinyint COMMENT 'Is this option group active?'
,
PRIMARY KEY ( `id` )
, UNIQUE INDEX `UI_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_contribution_product
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_product` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`product_id` int unsigned NOT NULL ,
`contribution_id` int unsigned NOT NULL ,
`product_option` varchar(255) COMMENT 'Option value selected if applicable - e.g. color, size etc.',
`quantity` int ,
`fulfilled_date` date COMMENT 'Optional. Can be used to record the date this product was fulfilled or shipped.',
`start_date` date COMMENT 'Actual start date for a time-delimited premium (subscription, service or membership)',
`end_date` date COMMENT 'Actual end date for a time-delimited premium (subscription, service or membership)',
`comment` text ,
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type(for membership price sets only).'
,
PRIMARY KEY ( `id` )
, CONSTRAINT FK_civicrm_contribution_product_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_contribution_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_product
-- *
-- * able - stores "product info" for premiums and can be used for non-incentive products
-- *
-- *******************************************************/
CREATE TABLE `civicrm_product` (
`id` int unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar(255) NOT NULL COMMENT 'Required product/premium name',
`description` text COMMENT 'Optional description of the product/premium.',
`sku` varchar(50) COMMENT 'Optional product sku or code.',
`options` text COMMENT 'Store comma-delimited list of color, size, etc. options for the product.',
`image` varchar(255) COMMENT 'Full or relative URL to uploaded image - fullsize.',
`thumbnail` varchar(255) COMMENT 'Full or relative URL to image thumbnail.',
`price` decimal(20,2) COMMENT 'Sell price or market value for premiums. For tax-deductible contributions, this will be stored as non_deductible_amount in the contribution record.',
`currency` varchar(3) DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
`financial_type_id` int unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
`min_contribution` decimal(20,2) COMMENT 'Minimum contribution required to be eligible to select this premium.',
`cost` decimal(20,2) COMMENT 'Actual cost of this product. Useful to determine net return from sale or using this as an incentive.',
`is_active` tinyint NOT NULL COMMENT 'Disabling premium removes it from the premiums_premium join table below.',
`period_type` enum('rolling', 'fixed') DEFAULT 'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month
(e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) ',
`fixed_period_start_day` int DEFAULT 0101 COMMENT 'Month and day (MMDD) that fixed period type subscription or membership starts.',
`duration_unit` enum('day', 'month', 'week', 'year') DEFAULT 'year' ,
`duration_interval` int COMMENT 'Number of units for total duration of subscription, service, membership (e.g. 12 Months).',
`frequency_unit` enum('day', 'month', 'week', 'year') DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.',
`frequency_interval` int COMMENT 'Number of units for delivery frequency of subscription, service, membership (e.g. every 3 Months).'
,
PRIMARY KEY ( `id` )
, CONSTRAINT FK_civicrm_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_entity_batch
-- *
-- * Batch entities (Contributions, Participants, Contacts) to a batch.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_batch` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`entity_table` varchar(64) COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
`entity_id` int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
`batch_id` int unsigned NOT NULL COMMENT 'FK to civicrm_batch'
,
PRIMARY KEY ( `id` )
, INDEX `index_entity`(
entity_table
, entity_id
)
, UNIQUE INDEX `UI_batch_entity`(
batch_id
, entity_id
, entity_table
)
, CONSTRAINT FK_civicrm_entity_batch_batch_id FOREIGN KEY (`batch_id`) REFERENCES `civicrm_batch`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_batch
-- *
-- * Stores the details of a batch operation Used primarily when doing batch operations with an external system.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_batch` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Address ID',
`name` varchar(64) COMMENT 'Variable name/programmatic handle for this batch.',
`title` varchar(64) COMMENT 'Friendly Name.',
`description` text COMMENT 'Description of this batch set.',
`created_id` int unsigned COMMENT 'FK to Contact ID',
`created_date` datetime COMMENT 'When was this item created',
`modified_id` int unsigned COMMENT 'FK to Contact ID',
`modified_date` datetime COMMENT 'When was this item created',
`saved_search_id` int unsigned COMMENT 'FK to Saved Search ID',
`status_id` int unsigned NOT NULL COMMENT 'fk to Batch Status options in civicrm_option_values',
`type_id` int unsigned COMMENT 'fk to Batch Type options in civicrm_option_values',
`mode_id` int unsigned COMMENT 'fk to Batch mode options in civicrm_option_values',
`total` decimal(20,2) COMMENT 'Total amount for this batch.',
`item_count` int unsigned COMMENT 'Number of items in a batch.',
`payment_instrument_id` int unsigned COMMENT 'fk to Payment Instrument options in civicrm_option_values',
`exported_date` datetime
,
PRIMARY KEY ( `id` )
, UNIQUE INDEX `UI_name`(
name
)
, CONSTRAINT FK_civicrm_batch_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_batch_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL, CONSTRAINT FK_civicrm_batch_saved_search_id FOREIGN KEY (`saved_search_id`) REFERENCES `civicrm_saved_search`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_note
-- *
-- * Notes can be linked to any object in the application.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_note` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Note ID',
`entity_table` varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.',
`entity_id` int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
`note` text COMMENT 'Note and/or Comment.',
`contact_id` int unsigned COMMENT 'FK to Contact ID creator',
`modified_date` date COMMENT 'When was this note last modified/edited',
`subject` varchar(255) COMMENT 'subject of note description',
`privacy` varchar(255) COMMENT 'Foreign Key to Note Privacy Level (which is an option value pair and hence an implicit FK)'
,
PRIMARY KEY ( `id` )
, INDEX `index_entity`(
entity_table
, entity_id
)
, CONSTRAINT FK_civicrm_note_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_option_group
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_group` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option Group ID',
`name` varchar(64) NOT NULL COMMENT 'Option group name. Used as selection key by class properties which lookup options in civicrm_option_value.',
`title` varchar(255) COMMENT 'Option Group title.',
`description` varchar(255) COMMENT 'Option group description.',
`is_reserved` tinyint DEFAULT 1 COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?',
`is_active` tinyint COMMENT 'Is this option group active?'
,
PRIMARY KEY ( `id` )
, UNIQUE INDEX `UI_name`(
name
)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- /*******************************************************
-- *
-- * civicrm_option_value
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_value` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option ID',
`option_group_id` int unsigned NOT NULL COMMENT 'Group which this option belongs to.',
`label` varchar(255) NOT NULL COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
`value` varchar(512) NOT NULL COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
`name` varchar(255) COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.',
`grouping` varchar(255) COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
`filter` int unsigned COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
`is_default` tinyint DEFAULT 0 COMMENT 'Is this the default option for the group?',
`weight` int unsigned NOT NULL COMMENT 'Controls display sort order.',
`description` text COMMENT 'Optional description.',
`is_optgroup` tinyint DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
`is_reserved` tinyint DEFAULT 0 COMMENT 'Is this a predefined system object?',
`is_active` tinyint DEFAULT 1 COMMENT 'Is this option active?',
`component_id` int unsigned COMMENT 'Component that this option value belongs/caters to.',
`domain_id` int unsigned COMMENT 'Which Domain is this option value for',
`visibility_id` int unsigned DEFAULT NULL
,
PRIMARY KEY ( `id` )
, INDEX `index_option_group_id_value`(
value(128)
, option_group_id
)
, INDEX `index_option_group_id_name`(
name(128)
, option_group_id
)
, CONSTRAINT FK_civicrm_option_value_option_group_id FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group`(`id`) ON DELETE CASCADE, CONSTRAINT FK_civicrm_option_value_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`) , CONSTRAINT FK_civicrm_option_value_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
I am thankful,
Michael Lueck
Lueck Data Systems
http://www.lueckdatasystems.com/