19 November 2015, 9:56

I'm creating an artefact plugin to add surveys to Mahara from the Qualtrics survey API (https://survey.qualtrics.com/WRAPI/ControlPanel/docs.php) and I created a join table to capture the many to many relationship between surveys and user/artefacts. It is a simple table with just an id primary key, artefact_id foreign key into the artefact table and survey_id into my (new) artefact_qualtrics_surveys table. 

I don't see anywhere in Mahara where cascading delete constraints are being defined on child tables and I was wondering if there is some underlying reason for this? I could delete records from my join table in my artefact delete method but having the db do it for me seems easier.

Also I added the constraints in db/upgrade.php using the execute_sql statement as there doesn't seem to be a way to do this from the XMLDB code. ( which is fine by me but if there is code I've missed ...? )

Just to be complete here is the code and thanks for any pointers

  try {
    $table = new XMLDBTable('artefact_qualtrics_artefacts_surveys');
    $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, 10, XMLDB_UNSIGNED,XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
    $table->addFieldInfo('artefact_idfk',XMLDB_TYPE_INTEGER,10, XMLDB_UNSIGNED);
    $table->addFieldInfo('survey_idfk',XMLDB_TYPE_INTEGER,10, XMLDB_UNSIGNED);
    $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
    $success = $success && create_table($table);
 } catch ( SQLException $e) {
    $success = false;
 }
 if ($success) {
    //add cascading delete constraints
    try {
        execute_sql('ALTER TABLE {artefact_qualtrics_artefacts_surveys} add constraint artefact_idfk_fkey FOREIGN KEY(artefact_idfk) REFERENCES {artefact}(id) ON DELETE CASCADE');
        execute_sql('ALTER TABLE {artefact_qualtrics_artefacts_surveys} add constraint survey_idfk_fkey FOREIGN KEY(survey_idfk) REFERENCES {artefact_qualtrics_surveys}(id) ON DELETE CASCADE');
        $success = true;
    } catch (SQLException $e) {
        $success = false;
 }
 }
 return $success;

19 November 2015, 11:56

Hi Gary,

To be honest, not using "DELETE CASCADE" on our foreign keys has just been the practice on the Mahara project since before I joined the project, and I've never considered changing it.

I can see advantages to using them, but I think in our case, being an open-source project with a lot of developers coming and going, it is probably safer for us to stick to explicit deletes. Cascading deletes are sort of hidden away on the database side, and it would be relatively easy for someone to set one up incorrectly, or for the usage of a table to change through subsequent Mahara versions, such that automatic deletion would wind up causing mysterious problems rather than solving them.

Feel free to use them in your plugin, though! I think you may be right, however, that our XMLDB library does not support them, so you'll need to create them manually. We've done this in Mahara core for a few uniqueness constraints that were more complex than XMLDB can handle.

The recommended way to do it for a plugin, is that you specify everything you can about your table in your plugin's install.xml file. Then, in your plugin's postinst($prevversion) method, you put in SQL code to add the extra stuff XMLDB couldn't do, at install time (when $prevversion==0).

The postinst() method is a public static method that the plugin can define, which will be called after the plugin is installed or upgraded. This makes it usually the right place for post-installation steps, unlike the db/upgrade.php file, which only gets executed after an upgrade, but NOT after installation.

And if the SQL syntax is different in mysql & postgres, you'll need to use the is_mysql() and is_postgres() methods to specify different forms of the query for both databases.

See PluginBlocktypeExternalfeed::postinst($prevversion) in htdocs/blocktype/externalfeed/lib.php for a good example.

Cheers,

Aaron

19 November 2015, 13:31

Hi Aaron,

     Thanks for the detailed response and tips. Great work on Mahara as well, it's a very clean, nice codebase. I'm enjoying getting to 'know' it.

Gary

20 November 2015, 11:07

Hi Gary,

Thanks! Some parts are good. Some parts... not so good. ;) We've got a large code base with about 10 years of history, which was initially written to support PHP 4. So, there are quite a few odd bits scattered about.

I've been (very gradually) filling in our missing developer documentation. There are some articles about different APIs within Mahara on our wiki: https://wiki.mahara.org/wiki/Developer_Area#API_Documentation

Cheers,

Aaron