CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion »
  • Scalability (Moderator: Donald Lobo) »
  • Importing Activities using MySQL stored function for bulk inserts
Pages: [1]

Author Topic: Importing Activities using MySQL stored function for bulk inserts  (Read 2744 times)

JoeMurray

  • Administrator
  • Ask me questions
  • *****
  • Posts: 578
  • Karma: 24
    • JMA Consulting
  • CiviCRM version: 4.4 and 4.5 (as of Nov 2014)
  • CMS version: Drupal, WordPress, Joomla
  • MySQL version: MySQL 5.5, 5.6, MariaDB 10.0 (as of Nov 2014)
Importing Activities using MySQL stored function for bulk inserts
September 15, 2010, 12:26:00 pm
I've been doing some work to speed up the import of a csv file containing changes and additions to contacts and activity information from foot canvassing. The approach is to see how much improvement could be gained by pushing all of the processing into mysql queries rather than doing it row by row in php. More on that another time.

One irritation in working with MySQL is that it doesn't support insertions into multiple tables in a single query. In some cases it seems you need to get the id of an insertion in one table in order to store that id in a record being inserted into another table before proceeding to the next insertion. For example, when creating an activity, you may need to use civicrm_activity.id when inserting related records in civicrm_activity_target, civicrm_activity_assignment, and any civicrm_value_xxx custom value tables extending the activity.  

However, stored functions provide an easy way to continue using queries to process large volumes of records with single query statements rather than reverting to sequential row-by-row processing, as the following example shows.

Code: [Select]
DELIMITER //
CREATE
Function insert_canvass
(Psource_contact_id int(10) unsigned,
 Psubject varchar(255),
 Pactivity_date_time datetime,
 Passignee_contact_id int(10) unsigned,
Ptarget_contact_id int(10) unsigned,
Pmark varchar(64),
Psign varchar(64),
Pdonor varchar(64),
Pvolunteer varchar(64)
)
RETURNS  int(10) unsigned

COMMENT 'Inserts into 4 tables'
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE Vactivity_id int(10) unsigned;
INSERT INTO civicrm_activity (source_contact_id, activity_type_id, subject, activity_date_time, status_id, priority_id)
VALUES (Psource_contact_id, 23, Psubject, Pactivity_date_time, 2, 2 );
SET Vactivity_id = LAST_INSERT_ID();
INSERT INTO civicrm_activity_assignment (activity_id, assignee_contact_id)
VALUES (Vactivity_id, Passignee_contact_id);
INSERT INTO civicrm_activity_target (activity_id, target_contact_id)
VALUES (Vactivity_id, Ptarget_contact_id);
INSERT INTO civicrm_value_canvass_results_1 (entity_id, mark_1, sign_2, donor_3, volunteer_4) VALUES (Vactivity_id, Pmark, Psign, Pdonor, Pvolunteer);
RETURN Vactivity_id;
END//
DELIMITER ;

This can be run to insert the activities from an import table using something like the following:
Code: [Select]
SELECT insert_canvass(2,i.short_comment,i.canvass_date,i.canvasser_id,i.civicrm_id,m.value,s.value, d.value, v.value)
from civicrm_import_job_ff52b66bb116f0f9f01916df9d08cc65 i
LEFT JOIN civicrm_option_value m on m.name=i.mark and m.option_group_id=80
LEFT JOIN civicrm_option_value s on s.name=i.sign and s.option_group_id=81
LEFT JOIN civicrm_option_value d on d.name=i.donor and d.option_group_id=82
LEFT JOIN civicrm_option_value v on v.name=i.volunteer and v.option_group_id=83

In the SELECT query, 2 is a hard-coded contact id for the current user who is creating the canvass activities (actually it's programmatically added to the sql at runtime but appears hard-coded here for convenience), and mark, sign, donor, and volunteer are custom fields in the civicrm_value_canvass_results_1 table.

If desired, an Update statement could be used to store the activity_id for each row, or perhaps a url that would display the activity, and that id or url returned to the user upon completion of the import.
« Last Edit: September 16, 2010, 10:54:38 am by JoeMurray »
Co-author of Using CiviCRM https://www.packtpub.com/using-civicrm/book

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion »
  • Scalability (Moderator: Donald Lobo) »
  • Importing Activities using MySQL stored function for bulk inserts

This forum was archived on 2017-11-26.