Author Topic: How to handle Database Triggers for custom tables created in civix (Read 1171 times)

awasson · December 09, 2012, 03:32:37 pm

Hi there,

I'm working on an extension that requires a couple of custom tables. They are quite simple and I'm using civix to stub out the module so a lot the scaffold is done. All I have to do is add a myinstall.sql file as follows to generate the tables when the module is enabled.

Code: [Select]

CREATE  TABLE IF NOT EXISTS civicrm_cpd_categories (
  id INT NULL AUTO_INCREMENT ,
  category VARCHAR(45) NOT NULL ,
  description VARCHAR(255) NOT NULL ,
  minimum DECIMAL(6,2) NULL ,
  maximum DECIMAL(6,2) NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB;




CREATE  TABLE IF NOT EXISTS civicrm_cpd_activities (
  contact_id INT NOT NULL ,
  category_id INT NOT NULL ,
  credit_date DATETIME NOT NULL ,
  credits DECIMAL(6,2) NOT NULL ,
  activity VARCHAR(45) NOT NULL ,
  notes VARCHAR(255) NULL ,
  PRIMARY KEY (contact_id, category_id) )
ENGINE = InnoDB;

The issue I am having is that I found it necessary to seed the civicrm_cpd_categories table with some categories for testing with MySQL Workbench but when I do, it complains with an error indicating that it needs a log table.

Quote

Error Code: 1146. Table 'civicrm.log_civicrm_cpd_categories' doesn't exist

I believe the insert is achieved but then MySQL tries to log the event using a trigger and when it doesn't find the civicrm.log_civicrm_cpd_categories table, it rolls back the transaction. I've suspect civix adds triggers for insert, update, delete. Is that correct?

I don't mind requiring a log table but I'll need to know what is expected of it and I would have suspected if this were the case that civix would create the log table when it creates the initial table.

Any thoughts?

Thanks,
Andrew

totten · December 10, 2012, 11:11:02 am

In this case, the relevant code predates civix and module-extensions -- but hasn't been updated to work nicely with them.

As a bit of background: CiviCRM supports optional, fine-grained logging of changes to (almost) every Civi table. This is fairly generic and automatic -- IIRC, it applies to any table named "civicrm_*", and it involves autogenerating both tables (log_civicrm_*) and triggers. It sounds like one half of that system is running against your custom table -- and one half isn't. So you wind up with the extra trigger but no log table.

One part of the equation is the trigger-building, CRM_Core_DAO::triggerRebuild(), which is called occasionally (e.g. after a cache-clear or after module-installation). It sounds like this is the one that runs:

https://fisheye2.atlassian.com/browse/CiviCRM/trunk/CRM/Core/DAO.php?hb=true#to1519

I'm less familiar with the other part. It looks like CRM_Logging_Schema::fixSchemaDifferences() and CRM_Core_BAO_SchemaHandler are involved in creating those tables. That logic also runs at various times, but they are *different* times from the trigger-rebuild. (e.g. Table-creation runs when the admin enables the logging feature, but it doesn't run during every cache-clear.)

https://fisheye2.atlassian.com/browse/CiviCRM/trunk/CRM/Logging/Schema.php?hb=true#to135

Possible solutions; I'm not confident which would work best:
* Use a different table prefix (eg "mymodule_*" rather than "civicrm_*") so that Civi doesn't try to do any logging against the table
* Use the module's enable/disable hooks to trigger updates to the log tables (eg call fixSchemaDifferences)
* Update the core code so that it calls both triggerRebuild() and fixSchemaDifferences() after installing/uninstalling/enabling/disabling modules.

awasson · December 10, 2012, 03:03:21 pm

Hi Totten,

Ah, that makes perfect sense...

I suppose what would be ideal is that the hook that creates the database table creates a log table at the same time after it checks the status of logging and checks for the civicrm prefix.

I thought about following your lead to call fixSchemaDifferences on install/uninstall but I think that might be too brittle since if I understand correctly it iterates through the entire database and I'm only adding a couple of tables. To resolve this, I just changed my table names to civi_cpd_categories and civi_cpd_activities. So far so good

Thanks a lot and boy is the civix tool handy!

CiviCRM Community Forums (archive)

News:

Author Topic: How to handle Database Triggers for custom tables created in civix (Read 1171 times)

awasson

How to handle Database Triggers for custom tables created in civix

totten

Re: How to handle Database Triggers for custom tables created in civix

awasson

Re: How to handle Database Triggers for custom tables created in civix