Triggers Oracle para Moodle 2.0

Este bug está registrado y comprobado en Moodle Tracker: http://tracker.moodle.org/browse/MDL-14567

En Moodle 2.0 sobre Oracle podemos crear triggers directamente sobre la base de datos para cualquier implementación que estemos haciendo (módulos, bloques...). Sin embargo, debemos tener en cuenta los siguientes aspectos:

1.Al contrario que en MySQL, en Oracle no podemos especificar un campo de una tabla como autoincrementable, lo que resulta útil para la genernación de identificadores de las tuplas (en Moodle siempre se les llama ID). Esto implica el uso de triggers, creados por defecto, que, cada vez que se hace una inserción, se disparan generando un nuevo identificador a partir de una secuencia.
2.Moodle se abstrae del tipo de la base de datos sobre la que trabaja y maneja las inserciones con objetos XMLDBTable. Con esto el sistema puede trabajar sobre cualquier base de datos que pueda no ser MySQL (la que exige por defecto).

Sabiendo esto, aparentemente la creación de nuestros propios triggers no implica ningún problema sobre el funcionamiento del sistema, pues el manejo de triggers es un trabajo del SGBD Oracle y no de Moodle.

Sin embargo, sí existe un problema al crear triggers: Cuando queremos hacer una inserción sobre una tabla del sistema (por ejemplo, moodle_course) a la que le hemos asociado un trigger propio, la función encargada de la inserción genera el siguiente problema:

Esta función trabaja con un objeto XMLDBTable con el que realiza la inserción. A este se le asocia la nueva tupla a insertar asignándole además el identificador real que tendrá la nueva tupla en la base de datos. Para conocer dicho identificador, se lanza una consulta sobre la base de datos para tomar el cuerpo del trigger asociado a la tabla y así manejar desde el código PHP la sentencia encargada de la creación del nuevo ID. Concretamente, la función es “getSequenceFromDB” de moodle/lib/xmldb/classes/generator/oci8po/oci8po.class.php:

function getSequenceFromDB($xmldb_table) {
$tablename = strtoupper($this->getTableName($xmldb_table));
$sequencename = false;
if ($trigger = get_record_sql("SELECT trigger_name, trigger_body
FROM user_triggers
WHERE table_name = '{$tablename}'")) {

/// If trigger found, regexp it looking for the sequence name
preg_match('/.*SELECT (.*)\.nextval/i', $trigger->trigger_body, $matches);
if (isset($matches[1])) {
$sequencename = $matches[1];
}
}
return $sequencename;
}


Si la observamos, vemos que se usa la función “get_record_sql” que solo devuelve la primera tupla encontrada por la consulta lanzada. Esto es un problema para nosotros, pues estamos en la situación en la que tenemos una tabla con dos triggers asociados: el asignador por defecto para la generación de ID y el que nosotros hemos creado. Entonces, esta consulta podría devolver el cuerpo del trigger que hemos creado y no el que existe por defecto. Lo que probocaría un error (página en blanco) dado que es implosible generar un nuevo identificador.

¿Qué solución le damos a esto?

Necesitamos distinguir el trigger encargado de la generación de IDs del resto. Esto se puede hacer de muchísimas maneras, pero la que yo propongo es esta:

Modificamos la consulta lanzada con la función “get_record_sql” de manera que solo se obtenga el trigger correspondiente a la creación del nuevo ID. Esto lo podemos hacer porque sabemos que Moodle asigna al nombre de los triggers de este tipo un nombre de la forma “*_ID*_TRG”. Así nos quedaría la consulta de la siguiente forma:

SELECT trigger_name, trigger_body
FROM user_triggers
WHERE table_name = '{$tablename}'
AND trigger_name like upper('{$CFG->prefix}')||'%ID%_TRG'

Con esto podemos crear Triggers Oracle en Moodle 2.0 sin problemas. La solución es muy cómoda aunque para aplicarla hay que suponer que Moodle siempre asignará a los triggers de este tipo un nombre de la forma “*_ID*_TRG”, además de, evidentemente, nosotros tener siempre presente que no debemos crear triggers cuyo nombre termine como estos. Seguro que hay soluciones mejores y menos “arriesgadas”, algo de lo que espero tener constancia.

Notas:

1.Con esta solución, podemos crear triggers de tipo AFTER y BEFORE sin problemas.

2.Para el caso concreto de la inserción de un nuevo curso por parte de Moodle una vez rellenado el formulario, la secuencia completa de invocaciones hasta obtener el nombre del trigger generador es:
1.edit.php: create_course($data), línea 98 aprox.
2.lib.php: insert_record('course', $data), línea 2819 aprox.
3.dmllib.php: find_sequence_name($xmldb_table);, línea 1496 aprox.
4.ddllib.php: getSequenceFromDB($CFG->dbtype, $CFG->prefix); línea 590 aprox
5.XMLDBTable.class.php: getSequenceFromDB($this); línea 1076 aprox.
6.oci8po.class.php: get_record_sql("SELECT...”); línea 541 aprox.

3.En el intérprete SQL podemos imprimir los errores al crear un trigger con la sentencia “show errors” inmediatamente debajo del código del trigger.

4.Podríamos no tocar la consulta comentada. Dejar el código tal y como está. Pero esto nos obliga a incluir código a nuestro trigger para desde este generar el nuevo ID. Sin embargo, esta solución solo sirve si el nuestro es de tipo BEFORE INSERT.
Ésto tendríamos que añadir:
IF :NEW.id IS NULL THEN
SELECT .nextval INTO :NEW.id FROM dual;
END IF;
Para el caso de inserciones en la tabla de cursos, la secuencia es: _cour_id_seq

5.Para conocer los triggers asociados a una determinada tabla:
SELECT trigger_name, trigger_type, trigger_body
FROM user_triggers
WHERE table_name = '';