xmldb and pl/sql

xmldb and pl/sql

by Hajer Oualha -
Number of replies: 7

Hello,

I am new to Moodle development. I am trying to develop a local addon with which I intend to create a table that triggers a local procedure. So basically my plugin will be using PL/SQL. The problem now is that as I am getting to learn how to use XMLDB by the editor as well as by understanding the install.xml file, I realized that through the editor it's not possible to create a local procedure neither to create a trigger for a table. I also looked at the xmldb.xsd file, and I couldn't see how we can add a local procedure manually to the install.xml file.

I may do that manually through phpPgAdmin (I am using postgresql database), but I want these local procedures and triggers to be created when I install the plugin.

I hope that my issue was understood, and I hope someone can give me a help on that.

Thanks,

Hajer

Average of ratings: -
In reply to Hajer Oualha

Re: xmldb and pl/sql

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Moodle is designed to work cleanly across all supported database engines, so the DB API does not support any features that are specific to one particular DB type.

That said, you can create an install.php file ( http://docs.moodle.org/dev/Upgrade_API#install.php ) and then, within the xmldb_PLUGINTYPE_PLUGINNAME_install() function you define there, you could write:

global $DB;
$dbman = $DB->get_manager();
$dbman->execute_sql('Whatever SQL statement you want to run to create your local procedure');

Once again - you are not advised to do this - it is usually far better to use the DB API to access SQL tables & update stuff, rather than write something that will restrict your plugin to a single DB type.

Average of ratings: Useful (2)
In reply to Davo Smith

Re: xmldb and pl/sql

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

To emphasise what Davo has said I had an SQL alter table statement in my upgrade.php which worked fine in all my testing in MySQL and then failed for someone using Postgres and I suspect it would fail with MS SQL Server. Try to avoid this approach. I have just looked up the right way to do it at 

http://docs.moodle.org/dev/DDL_functions

In future I will also test in at least one proper database.

 

Average of ratings: Useful (2)
In reply to Marcus Green

Re: xmldb and pl/sql

by Hajer Oualha -

Thank you all for you replies. I got the idea, it makes sense. My questioning now is, as I need to call a trigger that detects a change in a table (for an INSERT event), is that possible to be done with DDL functions? More precisely, I need to execute a code that inserts records in my plugin's tables whenever a new record is added to the mdl_log table.

Average of ratings: -
In reply to Hajer Oualha

Re: xmldb and pl/sql

by David Mudrák -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators

It looks like what you actually want to achieve is a handler attached to an event. Let me recommend to look at http://docs.moodle.org/dev/Event_2 and http://docs.moodle.org/dev/Logging_2 and explore the development in progress there.

Average of ratings: Useful (1)
In reply to David Mudrák

Re: xmldb and pl/sql

by Hajer Oualha -

Hi David, I tried to do as you suggested by working with events. Only problem is that not all events are yet implemented in Moodle 2.x and unfortunately almost all the events that I need to trigger are not implemented like the \mod_quiz\event\attempt_started

The events I want to handle all related to student's behaviour rather than instructor or admin. I want to handle the events when a student visit a resource or attempt a quiz. Those events are logged as actions in the log table, this is why I was thinking to create a local procedure in my database and trigger each inserted record in the log table, but then I understood that is not the right way to do it...

Can you suggest to me a temporary solution or a workaround to overcome that issue with clean coding?

Since I need this local plugin for a personal use, I may end up doing it in the PL/SQL way, but really as a last solution.

Thanks a lot for your help smile

Average of ratings: Useful (1)
In reply to Hajer Oualha

Re: xmldb and pl/sql

by David Mudrák -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators
If it's for your personal use only, why not to simply modify the add_to_log() in your Moodle code and catch the event there?
Average of ratings: Useful (1)