CiviAccounts Data Schema - Sample Before and After Transactions

This page will provide details on how specific transactions are represented in the 4.0 schema and how they should appear in the 4.1 Schema. Read operations will be examined on CiviAccounts Data Schema - BAO interfaces.

Create/Update/Delete a Payment Processor
1. Create 4.0:
  1. INSERT INTO `civicrm_payment_processor` (`id`, `domain_id`, `name`, `description`, `payment_processor_type`, `is_active`, `is_default`, `is_test`, `user_name`, `password`, `signature`, `url_site`, `url_api`, `url_recur`, `url_button`, `subject`, `class_name`, `billing_mode`, `is_recur`, `payment_type`) VALUES (1,1,'Dummy PP','','Dummy',1,1,0,'dummy',NULL,NULL,'http://dummy.com',NULL,NULL,NULL,NULL,'Payment_Dummy',1,NULL,1),(2,1,'Dummy PP','','Dummy',1,0,1,'dummytest',NULL,NULL,'http://dummytest.com',NULL,NULL,NULL,NULL,'Payment_Dummy',1,NULL,1);
2. Update
3. Delete
Contribution Page
1. Create 4.0:
  1. INSERT INTO `civicrm_contribution_page` (`id`, `title`, `intro_text`, `contribution_type_id`, `payment_processor_id`, `is_credit_card_only`, `is_monetary`, `is_recur`, `recur_frequency_unit`, `is_recur_interval`, `is_pay_later`, `pay_later_text`, `pay_later_receipt`, `is_allow_other_amount`, `default_amount_id`, `min_amount`, `max_amount`, `goal_amount`, `thankyou_title`, `thankyou_text`, `thankyou_footer`, `is_for_organization`, `for_organization`, `is_email_receipt`, `receipt_from_name`, `receipt_from_email`, `cc_receipt`, `bcc_receipt`, `receipt_text`, `is_active`, `footer_text`, `amount_block_is_active`, `honor_block_is_active`, `honor_block_title`, `honor_block_text`, `start_date`, `end_date`, `created_id`, `created_date`, `currency`, `campaign_id`) VALUES (1,'Donate to the CiviCRM Association',NULL,3,NULL,0,1,0,NULL,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,'I am contributing on behalf of an organization.',1,NULL,NULL,NULL,NULL,NULL,1,NULL,1,0,NULL,NULL,'2011-08-08 16:16:00',NULL,1,'2011-08-08 16:16:20','USD',NULL);
2. Fill in Amounts tab 4.0 (eg allow pledges, other amount, a few preset amounts):
  1. Modify the fields in the INSERT above to the following:
    1. INSERT INTO `civicrm_contribution_page` (`id`, `title`, `intro_text`, `contribution_type_id`, `payment_processor_id`, `is_credit_card_only`, `is_monetary`, `is_recur`, `recur_frequency_unit`, `is_recur_interval`, `is_pay_later`, `pay_later_text`, `pay_later_receipt`, `is_allow_other_amount`, `default_amount_id`, `min_amount`, `max_amount`, `goal_amount`, `thankyou_title`, `thankyou_text`, `thankyou_footer`, `is_for_organization`, `for_organization`, `is_email_receipt`, `receipt_from_name`, `receipt_from_email`, `cc_receipt`, `bcc_receipt`, `receipt_text`, `is_active`, `footer_text`, `amount_block_is_active`, `honor_block_is_active`, `honor_block_title`, `honor_block_text`, `start_date`, `end_date`, `created_id`, `created_date`, `currency`, `campaign_id`) VALUES (1,'Donate to the CiviCRM Association',NULL,3,1,0,1,0,NULL,0,1,'I will send payment by check','Send cheque to the office.',1,648,'10.00',NULL,NULL,NULL,NULL,NULL,0,'I am contributing on behalf of an organization.',1,NULL,NULL,NULL,NULL,NULL,1,NULL,1,0,NULL,NULL,'2011-08-08 16:16:00',NULL,1,'2011-08-08 16:16:20','USD',NULL);
  2. INSERT INTO `civicrm_pledge_block` (`id`, `entity_table`, `entity_id`, `pledge_frequency_unit`, `is_pledge_interval`, `max_reminders`, `initial_reminder_day`, `additional_reminder_day`) VALUES (1,'civicrm_contribution_page',1,'month',0,NULL,NULL,NULL);
  3. INSERT INTO `civicrm_option_group` (`id`, `name`, `label`, `description`, `is_reserved`, `is_active`) VALUES ... for preset amounts
  4. INSERT INTO `civicrm_option_value` (`id`, `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `domain_id`, `visibility_id`) VALUES ... for preset amounts
3. Update the Amounts tab 4.0:
  1. INSERT INTO `civicrm_contribution_page` (`id`, `title`, `intro_text`, `contribution_type_id`, `payment_processor_id`, `is_credit_card_only`, `is_monetary`, `is_recur`, `recur_frequency_unit`, `is_recur_interval`, `is_pay_later`, `pay_later_text`, `pay_later_receipt`, `is_allow_other_amount`, `default_amount_id`, `min_amount`, `max_amount`, `goal_amount`, `thankyou_title`, `thankyou_text`, `thankyou_footer`, `is_for_organization`, `for_organization`, `is_email_receipt`, `receipt_from_name`, `receipt_from_email`, `cc_receipt`, `bcc_receipt`, `receipt_text`, `is_active`, `footer_text`, `amount_block_is_active`, `honor_block_is_active`, `honor_block_title`, `honor_block_text`, `start_date`, `end_date`, `created_id`, `created_date`, `currency`, `campaign_id`) VALUES (1,'Donate to the CiviCRM Association',NULL,3,1,0,1,0,NULL,0,1,'I will send payment by check.','Send cheque to the office',1,651,'25.00',NULL,NULL,NULL,NULL,NULL,0,'I am contributing on behalf of an organization.',1,NULL,NULL,NULL,NULL,NULL,1,NULL,1,0,NULL,NULL,'2011-08-08 16:16:00',NULL,1,'2011-08-08 16:16:20','USD',NULL);
  2. similarly, update the civicrm_option_group and civicrm_option_value tables for the preset amounts.
Create/Update/Delete a Contribution Type
Create a Simple Contribution
1. 4.0 4.1 Comments in Blue:
  1. INSERT INTO `civicrm_activity` (`id`, `source_contact_id`, `source_record_id`, `activity_type_id`, `subject`, `activity_date_time`, `duration`, `location`, `phone_id`, `phone_number`, `details`, `status_id`, `priority_id`, `parent_id`, `is_test`, `medium_id`, `is_auto`, `relationship_id`, `is_current_revision`, `original_id`, `result`, `is_deleted`, `campaign_id`, `engagement_level`) VALUES (2,1,2,6,'$ 150.00 - Online Contribution: Donate to the CiviCRM Association','2011-08-08 16:37:03',NULL,NULL,NULL,NULL,NULL,2,2,NULL,0,NULL,0,NULL,1,NULL,NULL,0,NULL,NULL); UNCHANGED
  2. INSERT INTO `civicrm_activity_assignment` (`id`, `activity_id`, `assignee_contact_id`) VALUES (1,1,1),(2,2,1); UNCHANGED
  3. INSERT INTO `civicrm_contribution` (`id`, `contact_id`, `contribution_type_id`, `contribution_page_id`, `payment_instrument_id`, `receive_date`, `non_deductible_amount`, `total_amount`, `fee_amount`, `net_amount`, `trxn_id`, `invoice_id`, `currency`, `cancel_date`, `cancel_reason`, `receipt_date`, `thankyou_date`, `source`, `amount_level`, `contribution_recur_id`, `honor_contact_id`, `is_test`, `is_pay_later`, `contribution_status_id`, `honor_type_id`, `address_id`, `check_number`, `campaign_id`) VALUES (2,1,3,1,1,'2011-08-08 16:37:03','150.00','150.00',NULL,'150.00','live_00000002','515a4c49fad32d622e3ca71ab4cdd88b','USD',NULL,'0','2011-08-08 16:37:03',NULL,'Online Contribution: Donate to the CiviCRM Association',NULL,NULL,NULL,0,0,1,NULL,4,NULL,NULL); INSERT CONTRIBUTION excluding values for FINANCIAL_ITEM, THEN INSERT FINANCIAL_ITEM containing remaining information with CIVICRM_CONTRIBUTION as the ENTITY_TABLE. Put the two inserts into a db_transaction to ensure consistency. Andrew: previously you had wanted against the views of me and DaveG and DaveS to have an additional financial_item corresponding to a contribution_total amount when there were 6 items in the contribution. Have you abandoned that view, or are you making a special case where there is no total f_i if there is only one item in it?
  4. INSERT INTO `civicrm_entity_financial_trxn` (`id`, `entity_table`, `entity_id`, `financial_trxn_id`, `amount`, `currency`) VALUES (2,'civicrm_contribution',2,2,'150.00','USD'); UNCHANGED except link to civicrm_financial_item instead of civicrm_contribution.
  5. INSERT INTO `civicrm_financial_trxn` (`id`, `from_account_id`, `to_account_id`, `trxn_date`, `trxn_type`, `total_amount`, `fee_amount`, `net_amount`, `currency`, `payment_processor`, `trxn_id`, `trxn_result_code`) VALUES (2,NULL,NULL,'2011-08-08 16:37:03','Debit','150.00',NULL,'150.00','USD','Dummy','live_00000002',''); UNCHANGED
  6. INSERT INTO `civicrm_log` (`id`, `entity_table`, `entity_id`, `data`, `modified_id`, `modified_date`) VALUES (7,'civicrm_contact',1,'civicrm_contact,1',1,'2011-08-08 16:37:02'),(8,'civicrm_activity',2,'Activity created for source=1, assignee=1',1,'2011-08-08 16:37:03'); UNCHANGED
'Update'/'Delete' a Simple Contribution
Price Sets
1. Create 4.0:
  1. INSERT INTO `civicrm_option_group` (`id`, `name`, `label`, `description`, `is_reserved`, `is_active`) VALUES ... for the preset option values.
  2. INSERT INTO `civicrm_option_value` (`id`, `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `domain_id`, `visibility_id`) VALUES ... for the preset option values.
  3. INSERT INTO `civicrm_price_set` (`id`, `domain_id`, `name`, `title`, `is_active`, `help_pre`, `help_post`, `javascript`, `extends`) VALUES (1,NULL,'Sessions','Sessions',1,NULL,NULL,NULL,'1');
Create/Update/Delete a Price Field
1. Create 4.0:
  1. INSERT INTO `civicrm_price_field` (`id`, `price_set_id`, `name`, `label`, `html_type`, `is_enter_qty`, `help_pre`, `help_post`, `weight`, `is_display_amounts`, `options_per_line`, `is_active`, `is_required`, `active_on`, `expire_on`, `javascript`, `visibility_id`) VALUES (1,1,'Session_1','Session 1','Text',1,NULL,NULL,1,1,1,1,0,NULL,NULL,NULL,1);
  2. INSERT INTO `civicrm_price_field_value` (`id`, `price_field_id`, `name`, `label`, `description`, `amount`, `count`, `max_value`, `weight`, `is_default`, `is_active`) VALUES (1,1,'Session_1','Session 1',NULL,'100',NULL,NULL,1,0,1);
  3. Should PRICE_FIELD_VALUE line also be here for Session 2 used in example below? Would it be in PRICE_FIELD too? A: 7.a shows the creation of one Price Field. Examples elsewhere use a second whose creation is not shown here. The collection of INSERTs in each subsection corresponds to what happens during a single form submission.
Create a Price Set Contribution
A contribution using a three-field price set: one select field, one checkbox field with two options checked, and one Text / Numeric Quantity field with a quantity of 3.
1. Create with immediate credit card payment 4.0:
  1. INSERT INTO `civicrm_contribution` (`id`, `contact_id`, `contribution_type_id`, `contribution_page_id`, `payment_instrument_id`, `receive_date`, `non_deductible_amount`, `total_amount`, `fee_amount`, `net_amount`, `trxn_id`, `invoice_id`, `currency`, `cancel_date`, `cancel_reason`, `receipt_date`, `thankyou_date`, `source`, `amount_level`, `contribution_recur_id`, `honor_contact_id`, `is_test`, `is_pay_later`, `contribution_status_id`, `honor_type_id`, `address_id`, `check_number`, `campaign_id`) VALUES (23,102,1,1,1,'2012-01-30 16:38:38','0.00','5256.00',NULL,'5256.00','live_00000006','c9d40a169163e2c40c8f0007def19644','USD',NULL,'0','2012-01-30 16:38:38',NULL,'Online Contribution: Help Support CiviCRM!',NULL,NULL,NULL,0,0,1,NULL,96,NULL,NULL);
  2. INSERT INTO `civicrm_financial_trxn` (`id`, `from_account_id`, `to_account_id`, `trxn_date`, `trxn_type`, `total_amount`, `fee_amount`, `net_amount`, `currency`, `payment_processor`, `trxn_id`, `trxn_result_code`) VALUES (6,NULL,NULL,'2012-01-30 16:38:38','Debit','5256.00',NULL,'5256.00','USD','Dummy','live_00000006','');
  3. INSERT INTO `civicrm_entity_financial_trxn` (`id`, `entity_table`, `entity_id`, `financial_trxn_id`, `amount`, `currency`) VALUES (6,'civicrm_contribution',23,6,'5256.00','USD');
  4. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (19,'civicrm_contribution',23,2,'Supply one developer with coffee for a day',3,'12.00','36.00',0,4);
  5. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (20,'civicrm_contribution',23,3,'Platinum sponsor',1,'5000.00','5000.00',0,5);
  6. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (21,'civicrm_contribution',23,4,'Spring',1,'100.00','100.00',0,8);
  7. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (22,'civicrm_contribution',23,4,'Summer',1,'120.00','120.00',0,9);
  8. INSERT INTO `civicrm_price_set_entity` (`id`, `entity_table`, `entity_id`, `price_set_id`) VALUES (8,'civicrm_contribution',23,1);
  9. INSERT INTO `civicrm_activity` (`id`, `source_contact_id`, `source_record_id`, `activity_type_id`, `subject`, `activity_date_time`, `duration`, `location`, `phone_id`, `phone_number`, `details`, `status_id`, `priority_id`, `parent_id`, `is_test`, `medium_id`, `is_auto`, `relationship_id`, `is_current_revision`, `original_id`, `result`, `is_deleted`, `campaign_id`, `engagement_level`) VALUES (558,102,23,6,'$ 5,256.00 - Online Contribution: Help Support CiviCRM!','2012-01-30 16:38:38',NULL,NULL,NULL,NULL,NULL,2,2,NULL,0,NULL,0,NULL,1,NULL,NULL,0,NULL,NULL);
  10. INSERT INTO `civicrm_activity_assignment` (`id`, `activity_id`, `assignee_contact_id`) VALUES (136,558,102);
2. Create as pay later 4.0:
  1. INSERT INTO `civicrm_contribution` (`id`, `contact_id`, `contribution_type_id`, `contribution_page_id`, `payment_instrument_id`, `receive_date`, `non_deductible_amount`, `total_amount`, `fee_amount`, `net_amount`, `trxn_id`, `invoice_id`, `currency`, `cancel_date`, `cancel_reason`, `receipt_date`, `thankyou_date`, `source`, `amount_level`, `contribution_recur_id`, `honor_contact_id`, `is_test`, `is_pay_later`, `contribution_status_id`, `honor_type_id`, `address_id`, `check_number`, `campaign_id`) VALUES (24,102,1,1,NULL,'2012-01-30 17:54:22','0.00','5256.00',NULL,NULL,NULL,'b6e866da02975fe62431f73417887718','USD',NULL,'0',NULL,NULL,'Online Contribution: Help Support CiviCRM!',NULL,NULL,NULL,0,1,2,NULL,NULL,NULL,NULL);
  2. INSERT INTO `civicrm_price_set_entity` (`id`, `entity_table`, `entity_id`, `price_set_id`) VALUES (9,'civicrm_contribution',24,1);
  3. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (23,'civicrm_contribution',24,2,'Supply one developer with coffee for a day',3,'12.00','36.00',0,4);
  4. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (24,'civicrm_contribution',24,3,'Platinum sponsor',1,'5000.00','5000.00',0,5);
  5. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (25,'civicrm_contribution',24,4,'Spring',1,'100.00','100.00',0,8);
  6. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (26,'civicrm_contribution',24,4,'Summer',1,'120.00','120.00',0,9);
3. Create with immediate credit card payment 4.1:
  1. Insert a record into civicrm_contribution to record the contribution, with column values:
    id: [as in 4.0]
    contact_id: [as in 4.0]
    financial_type_id: [Proposed: remove/deprecate this column and store financial_type_id only as a property of price set fields and financial items.]
    contribution_page_id: [as in 4.0]
    payment_instrument_id: [Proposed: remove/deprecate this column and store payment instrument as a property of financial transactions.]
    receive_date: [as in 4.0]
    non_deductible_amount: [?? not sure where this value should come from]
    total_amount: [as in 4.0]
    fee_amount: [?? not sure where this value should come from]
    net_amount: [as in 4.0]
    trxn_id: [Proposed: remove/deprecate this column and store trxn_id as a property of financial transactions.]
    invoice_id: [Proposed: remove/deprecate this column, but not sure what we're doing about invoices.]
    currency: [as in 4.0]
    cancel_date: [as in 4.0]
    cancel_reason: [as in 4.0]
    receipt_date: [as in 4.0]
    thankyou_date: [as in 4.0]
    source: [as in 4.0]
    amount_level: [?? not sure what this column is for]
    contribution_recur_id: [as in 4.0]
    honor_contact_id: [as in 4.0]
    is_test: [as in 4.0]
    is_pay_later: [as in 4.0]
    contribution_status_id: [as in 4.0]
    honor_type_id: [as in 4.0]
    address_id: [Proposed: remove/deprecate this column and store billing address as a property of financial transactions.]
    check_number: [Proposed: remove/deprecate this column and store check number as a property of financial transactions.]
    campaign_id: [as in 4.0]
  2. For each selected item in the contribution (select field, first checkbox, second checkbox, and text/numeric-quantity field):
    Insert a record into civicrm_financial_item for the select field, with column values:
    id: (auto)
    date: (today's date)
    contact_id: [?? not sure what this column is for]
    contact_id_to: [?? not sure what this column is for]
    quantity: (quantity of this item included in the contribution)
    description: [?? not sure what this column is for]
    unit_amount: (the cost of one item)
    total_amount: (unit_amount multiplied by quantity)
    currency: (civicrm_contribution_page.currency for the page where the contribution came from)
    deductiblity_id: [?? not sure what this column is for: is this not a property of the financial_type?]
    financial_type_id: (civicrm_financial_type.id for the price field from which this item was added [NOTE: civicrm_price_field does not have a financial_type_id column; shouldn't it?])
    status_id: (one of: unpaid, partial paid, paid)
    entity_table: NULL
    entity_id: NULL
  3. Insert a record into civicrm_financial_trxn to record the payment, with column values:
    id: (auto)
    account_id: (civicrm_financial_account.id for the account into which payment is received)
    trxn_date: (date of the payment)
    status_id: (completed. could this have another value in this case?)
    trxn_type: (debit or credit, depending on the payment card)
    total_amount: (total amount of the payment, which in this case is the total amount of the contribution)
    fee_amount: (amount of transaction fee, if any)
    net_amount: (total_amount minus fee_amount)
    currency: (civicrm_contribution_page.currency for the page where the contribution came from)
    payment_processor: [Proposed: change this to payment_processor_id as FK to civicrm_payment_processor_id, copied from civicrm_contribution_page.payment_processor_id for the page where the contribution came from]
    trxn_id: (unique id from payment processor)
    trxn_result_code: (result code value from payment processor)
  4. Insert a record into civicrm_price_set_entity to record the price set used for this contribution
    [as in 4.0]
  5. Insert a record into civicrm_entity_financial_trxn to connect the payment to this contribution, with column values:
    id: (auto)
    entity_table: 'civicrm_contribution'
    entity_id: (civicrm_contribution.id for the contribution)
    financial_trxn_id: (civicrm_financial_trxn.id for the transaction)
    amount: (total amount of the payment)
  6. Insert a record into civicrm_activity for the payment
    [as in 4.0]
  7. Insert a record into civicrm_activity_assigment to connect the activity to the paying contact
    [as in 4.0]
4. Create as pay later 4.1:
  This is the same as "Create with immediate credit card payment 4.1", except that it doesn't create records in civicrm_financial_trxn, civicrm_entity_financial_trxn, civicrm_activity, or civicrm_activity_assigment tables.
  1. Insert a record into civicrm_contribution to record the contribution, with column values:
    id: [as in 4.0]
    contact_id: [as in 4.0]
    financial_type_id: [Proposed: remove/deprecate this column and store financial_type_id only as a property of price set fields and financial items.]
    contribution_page_id: [as in 4.0]
    payment_instrument_id: [Proposed: remove/deprecate this column and store payment instrument as a property of financial transactions.]
    receive_date: [as in 4.0]
    non_deductible_amount: [?? not sure where this value should come from]
    total_amount: [as in 4.0]
    fee_amount: [?? not sure where this value should come from]
    net_amount: [as in 4.0]
    trxn_id: [Proposed: remove/deprecate this column and store trxn_id as a property of financial transactions.]
    invoice_id: [Proposed: remove/deprecate this column, but not sure what we're doing about invoices.]
    currency: [as in 4.0]
    cancel_date: [as in 4.0]
    cancel_reason: [as in 4.0]
    receipt_date: [as in 4.0]
    thankyou_date: [as in 4.0]
    source: [as in 4.0]
    amount_level: [?? not sure what this column is for]
    contribution_recur_id: [as in 4.0]
    honor_contact_id: [as in 4.0]
    is_test: [as in 4.0]
    is_pay_later: [as in 4.0]
    contribution_status_id: [as in 4.0]
    honor_type_id: [as in 4.0]
    address_id: [Proposed: remove/deprecate this column and store billing address as a property of financial transactions.]
    check_number: [Proposed: remove/deprecate this column and store check number as a property of financial transactions.]
    campaign_id: [as in 4.0]
  2. For each selected item in the contribution (select field, first checkbox, second checkbox, and text/numeric-quantity field):
    Insert a record into civicrm_financial_item for the select field, with column values:
    id: (auto)
    date: (today's date)
    contact_id: [?? not sure what this column is for]
    contact_id_to: [?? not sure what this column is for]
    quantity: (quantity of this item included in the contribution)
    description: [?? not sure what this column is for]
    unit_amount: (the cost of one item)
    total_amount: (unit_amount multiplied by quantity)
    currency: (civicrm_contribution_page.currency for the page where the contribution came from)
    deductiblity_id: [?? not sure what this column is for: is this not a property of the financial_type?]
    financial_type_id: (civicrm_financial_type.id for the price field from which this item was added [NOTE: civicrm_price_field does not have a financial_type_id column; shouldn't it?])
    status_id: (one of: unpaid, partial paid, paid)
    entity_table: NULL
    entity_id: NULL
  3. Insert a record into civicrm_price_set_entity to record the price set used for this contribution
    [as in 4.0]
Update a Price Set Contribution
'Delete' a Price Set Contribution
Create a Recurring Contribution
Update a Recurring Contribution
'Delete' a Recurring Contribution
First recurring payment received
On-going recurring payment received
Final recurring payment received
Update the Status of a Contribution to reflect a final recurring payment
Event
1. Create 4.0:
  1. INSERT INTO `civicrm_event` (`id`, `title`, `summary`, `description`, `event_type_id`, `participant_listing_id`, `is_public`, `start_date`, `end_date`, `is_online_registration`, `registration_link_text`, `registration_start_date`, `registration_end_date`, `max_participants`, `event_full_text`, `is_monetary`, `contribution_type_id`, `payment_processor_id`, `is_map`, `is_active`, `fee_label`, `is_show_location`, `loc_block_id`, `default_role_id`, `intro_text`, `footer_text`, `confirm_title`, `confirm_text`, `confirm_footer_text`, `is_email_confirm`, `confirm_email_text`, `confirm_from_name`, `confirm_from_email`, `cc_confirm`, `bcc_confirm`, `default_fee_id`, `default_discount_fee_id`, `thankyou_title`, `thankyou_text`, `thankyou_footer_text`, `is_pay_later`, `pay_later_text`, `pay_later_receipt`, `is_multiple_registrations`, `allow_same_participant_emails`, `has_waitlist`, `requires_approval`, `expiration_time`, `waitlist_text`, `approval_req_text`, `is_template`, `template_title`, `created_id`, `created_date`, `currency`, `campaign_id`) VALUES (4,'AGM',NULL,NULL,1,NULL,0,'2011-08-08 15:45:00',NULL,0,NULL,NULL,NULL,NULL,'This event is currently full.',0,0,NULL,0,1,NULL,1,NULL,1,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,0,NULL,1,'2011-08-08 15:45:37',NULL,NULL);
2. Update to include fees from a price set:
  1. UPDATE `civicrm_event` SET `is_monetary`=1, `contribution_type_id`=4, `payment_processor_id`=1, `fee_label`='Event Fee(s)', `pay_later_text`='I will send payment by check', `currency`='USD' WHERE id=4;
Purchase a Price Set Event Registration with qty=1 for one session, qty=2 for second session:
1. 4.0 (not in correct order) 4.1 Comments in Blue:
  1. INSERT INTO `civicrm_activity` (`id`, `source_contact_id`, `source_record_id`, `activity_type_id`, `subject`, `activity_date_time`, `duration`, `location`, `phone_id`, `phone_number`, `details`, `status_id`, `priority_id`, `parent_id`, `is_test`, `medium_id`, `is_auto`, `relationship_id`, `is_current_revision`, `original_id`, `result`, `is_deleted`, `campaign_id`, `engagement_level`) VALUES (1,1,1,5,'AGM - August 8th, 2011 3:45 PM - Attendee - Registered','2011-08-08 16:01:33',NULL,NULL,NULL,NULL,NULL,2,2,NULL,0,NULL,0,NULL,1,NULL,NULL,0,NULL,NULL); UNCHANGED
  2. INSERT INTO `civicrm_activity_assignment` (`id`, `activity_id`, `assignee_contact_id`) VALUES (1,1,1); UNCHANGED
  3. INSERT INTO `civicrm_contribution` (`id`, `contact_id`, `contribution_type_id`, `contribution_page_id`, `payment_instrument_id`, `receive_date`, `non_deductible_amount`, `total_amount`, `fee_amount`, `net_amount`, `trxn_id`, `invoice_id`, `currency`, `cancel_date`, `cancel_reason`, `receipt_date`, `thankyou_date`, `source`, `amount_level`, `contribution_recur_id`, `honor_contact_id`, `is_test`, `is_pay_later`, `contribution_status_id`, `honor_type_id`, `address_id`, `check_number`, `campaign_id`) VALUES (1,1,4,NULL,1,'2011-08-08 16:01:33','0.00','250.00',NULL,'250.00','live_00000001','8bb2685a7b2d50108583de20c89e8eb5','USD',NULL,NULL,NULL,NULL,'Online Event Registration: AGM','Session 1 - 1Session 2 - 2',NULL,NULL,0,0,1,NULL,3,NULL,NULL); REMOVED
  4. INSERT INTO `civicrm_participant` (`id`, `contact_id`, `event_id`, `status_id`, `role_id`, `register_date`, `source`, `fee_level`, `is_test`, `is_pay_later`, `fee_amount`, `registered_by_id`, `discount_id`, `fee_currency`, `campaign_id`) VALUES (1,1,4,1,'1','2011-08-08 16:01:33','Online Event Registration: AGM','Session 1 - 1Session 2 - 2',0,0,'250.00',NULL,NULL,'USD',NULL); UNCHANGED
  5. INSERT INTO `civicrm_line_item` (`id`, `entity_table`, `entity_id`, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`) VALUES (1,'civicrm_participant',1,1,'Session 1',1,'100.00','100.00',0,1),(2,'civicrm_participant',1,2,'Session 2',2,'75.00','150.00',0,2); IN THEORY THIS WOULD BE REPLACED BY CREATING 2 FINANCIAL_ITEMS, (1) A FINANCIAL_ITEMS OF CIVICRM_PRICE_FIELD 1 AMOUNT $100, (2) A FINANCIAL_ITEMS OF CIVICRM_PRICE_FIELD 2 AMOUNT $75, A CIVICRM_FINANCIAL_QTY OF 2 x FINANCIAL_ITEM_ID=2 AND A FINANCIAL_ITEM OF CIVICRM_FINANCIAL_QTY AMOUNT $150, with THREE ROWS CREATED IN ENTITY_FINANCIAL_BATCH, WHERE CIVICRM_PARTICIPANT is the FINANCIAL_BATCH_ENTITY_TABLE and the FINANCIAL_BATCH_ENTITY_ID is 1, AND ENTER A LINE FOR THE CIVICRM_PARTICIPANT TOTAL AMOUNT IN FINANCIAL_ITEMS - but need to look at how to deal with participant counts - presumably from PRICE_FIELD_VALUE.COUNT - not clear if the FINANCIAL_ITEMS above should link to CIVICRM_PRICE_FIELD rows or CIVICRM_PRICE_FIELD_VALUE rows;
  6. INSERT INTO `civicrm_financial_trxn` (`id`, `from_account_id`, `to_account_id`, `trxn_date`, `trxn_type`, `total_amount`, `fee_amount`, `net_amount`, `currency`, `payment_processor`, `trxn_id`, `trxn_result_code`) VALUES (1,NULL,NULL,'2011-08-08 16:01:33','Debit','250.00',NULL,'250.00','USD','Dummy','live_00000001',NULL);UNCHANGED
  7. INSERT INTO `civicrm_entity_financial_trxn` (`id`, `entity_table`, `entity_id`, `financial_trxn_id`, `amount`, `currency`) VALUES (1,'civicrm_contribution',1,1,'250.00','USD'); CHANGE TO INSERT 2 SEPARATE records, linking $100 to FINANCIAL_ITEMS.ID = 1 and $150 to FINANCIAL_ITEMS.ID = 3 (the Qty line)
  8. INSERT INTO `civicrm_log` (`id`, `entity_table`, `entity_id`, `data`, `modified_id`, `modified_date`) VALUES (5,'civicrm_activity',1,'Activity created for source=1, assignee=1',1,'2011-08-08 16:01:33'),(6,'civicrm_participant',1,'Registered',1,'2011-08-08 00:00:00'); UNCHANGED
  9. INSERT INTO `civicrm_participant_payment` (`id`, `participant_id`, `contribution_id`) VALUES (1,1,1); REPLACED WITH FINANCIAL_ITEMS row link to PARTICIPANT set out above perhaps - need to look at how code interprets this.
  10. optionally create address, email, etc or update as is_billing=1 UNCHANGED?