Author Topic: Issue with using CRM_Utils_File::sourceSQLFile in extension Upgrader (Read 356 times)

lolas · September 03, 2014, 08:07:14 am

I am testing creating an upgrade function for an extension schema and I am running into an issue that I cannot use SQL variables in the SQL file that gets "sourced" via CRM_Utils_File::sourceSQLFile. Since each query is executed separately the SQL variables are not still available to the session and are NULL in the next query.

Code: [Select]

  public function upgrade_4200() {
    $this->ctx->log->info('Applying update 4200');
    $this->executeSqlFile('sql/upgrade_4200.sql', TRUE);
    return TRUE;
  }

The SQL file is patterned on those in the core upgrade files:

Code: [Select]

  --
  -- Message templates for e-mail notifications
  -- Based on CRM/Upgrade/Incremental/sql/README.txt
  --

  SELECT @option_group_id_contribution := max(id) from civicrm_option_group where name = 'msg_tpl_workflow_contribution';
  SELECT @max_val    := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_contribution;
  SELECT @max_wt     := max(weight) from civicrm_option_value where option_group_id=@option_group_id_contribution;

  INSERT INTO civicrm_option_value (option_group_id, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, value, name, weight)
  VALUES (@option_group_id_contribution, {localize}'Contributions - Notification to PCP owner'{/localize},{localize}'Sends an e-mail notification to the PCP owner when he/she receives a     contribution.'{/localize}, (SELECT @max_val := @max_val+1), 'pcpteams_notification_contribution', (SELECT @max_wt := @max_wt+1));

  SELECT @tpl_ovid := MAX(id) FROM civicrm_option_value WHERE option_group_id = @option_group_id_contribution;

  {fetch assign=text file="$extDir/message_templates/pcpteams_notification_contribution_text.tpl"}
  {fetch assign=html file="$extDir/message_templates/pcpteams_notification_contribution_html.tpl"}

  INSERT INTO civicrm_msg_template (msg_title, msg_subject, msg_text, msg_html, workflow_id, is_default, is_reserved)
  VALUES ('Contributions - PCP contribution notification', '{literal}{$pcpName}{/literal}: new contribution', '{$text|escape:"quotes"}', '{$html|escape:"quotes"}', @tpl_ovid, 1, 0);

The contents of the user defined variables are null and are causing constraint violations.
Is there something that needs to be added to the CRM_Utils_File::sourceSQLFile function to ensure that all the SQL statements in the source file get called in one session?

totten · September 03, 2014, 11:35:16 pm

To reproduce, I took these makes:

* Make a new extension with an upgrader
* Install it.
* Save DB snapshot
* Copy in the snippets from this post
* Run the upgrader

This does confirm an error -- although I got a different error. It reported a SQL syntax error on the Smarty expressions ("{localize...}"). So I changed made these changes/additions:

Code: [Select]

// FILE: CRM/Foo/Upgrader.php
  public function upgrade_4200() {
    $this->ctx->log->info('Applying update 4200');
    $this->executeSmartySqlFile('sql/upgrade_4200.mysql.tpl');
    return TRUE;
  }

  public function executeSmartySqlFile($relativePath) {
    $smarty = CRM_Core_Smarty::singleton();
    $sqlCode = $smarty->fetch($relativePath);
    CRM_Utils_File::sourceSQLFile(
      CIVICRM_DSN,
      $sqlCode,
      NULL,
      TRUE
    );
    return TRUE;
  }

Code: [Select]

// FILE: templates/sql/upgrade_4200.mysql.tpl
  --
  -- Message templates for e-mail notifications
  -- Based on CRM/Upgrade/Incremental/sql/README.txt
  --

  SELECT @option_group_id_contribution := max(id) from civicrm_option_group where name = 'msg_tpl_workflow_contribution';
  SELECT @max_val    := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_contribution;
  SELECT @max_wt     := max(weight) from civicrm_option_value where option_group_id=@option_group_id_contribution;

  INSERT INTO civicrm_option_value (option_group_id, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, value, name, weight)
  VALUES (@option_group_id_contribution, {localize}'Contributions - Notification to PCP owner'{/localize},{localize}'Sends an e-mail notification to the PCP owner when he/she receives a     contribution.'{/localize}, (SELECT @max_val := @max_val+1), 'pcpteams_notification_contribution', (SELECT @max_wt := @max_wt+1));

  SELECT @tpl_ovid := MAX(id) FROM civicrm_option_value WHERE option_group_id = @option_group_id_contribution;

{* temporarily comment out this code so that we can test with simpler code

 {fetch assign=text file="$extDir/message_templates/pcpteams_notification_contribution_text.tpl"}
 {fetch assign=html file="$extDir/message_templates/pcpteams_notification_contribution_html.tpl"}

  INSERT INTO civicrm_msg_template (msg_title, msg_subject, msg_text, msg_html, workflow_id, is_default, is_reserved)
  VALUES ('Contributions - PCP contribution notification', '{literal}{$pcpName}{/literal}: new contribution', '{$text|escape:"quotes"}', '{$html|escape:"quotes"}', @tpl_ovid, 1, 0);
*}

And this worked. After running the upgrade, I can see that an option value for "Contributions - Notification to PCP owner" is registered (and it has values for option_group_id,weight,value) Two notes:

Not sure how $extdir gets exported to Smarty -- maybe you do that somewhere else?
I wasn't aware you can do {fetch} with an absolute file-path. That's potentially nicer than my example (which puts everything under "templates/**.tpl" so that it can be autoloaded).
In my example, one should avoid conflicts with other extensions by putting the extension name somewhere in the file's path (e.g. change the name to "templates/CRM/Foo/sql/upgrade_4200.mysql.tpl"). I don't know if that's needed using the $extDir/{fetch} approach.

lolas · September 04, 2014, 11:36:48 am

Hello Tim,

Thanks for looking at it. Hmm, I guess it must be something with my environment. Good to know that it is not a core problem.
I will try this in a different environment and let you know if I figure out the difference is.

CiviCRM Community Forums (archive)

News:

Author Topic: Issue with using CRM_Utils_File::sourceSQLFile in extension Upgrader (Read 356 times)

lolas

Issue with using CRM_Utils_File::sourceSQLFile in extension Upgrader

totten

Re: Issue with using CRM_Utils_File::sourceSQLFile in extension Upgrader

lolas

Re: Issue with using CRM_Utils_File::sourceSQLFile in extension Upgrader