MDL-32365 oracle: ensure table has 'before each row' trigger to

determine if the 'id' column is sequence based or no

diff --git a/lib/dml/oci_native_moodle_database.php b/lib/dml/oci_native_moodle_database.php
index a529277..71a38ee 100644 (file)
--- a/lib/dml/oci_native_moodle_database.php
+++ b/lib/dml/oci_native_moodle_database.php
@@ -493,9 +493,13 @@ class oci_native_moodle_database extends moodle_database {
 
         // We give precedence to CHAR_LENGTH for VARCHAR2 columns over WIDTH because the former is always
         // BYTE based and, for cross-db operations, we want CHAR based results. See MDL-29415
-        $sql = "SELECT CNAME, COLTYPE, nvl(CHAR_LENGTH, WIDTH) AS WIDTH, SCALE, PRECISION, NULLS, DEFAULTVAL
+        // Instead of guessing sequence based exclusively on name, check tables against user_triggers to
+        // ensure the table has a 'before each row' trigger to assume 'id' is auto_increment. MDL-32365
+        $sql = "SELECT CNAME, COLTYPE, nvl(CHAR_LENGTH, WIDTH) AS WIDTH, SCALE, PRECISION, NULLS, DEFAULTVAL,
+                  DECODE(NVL(TRIGGER_NAME, '0'), '0', '0', '1') HASTRIGGER
                   FROM COL c
              LEFT JOIN USER_TAB_COLUMNS u ON (u.TABLE_NAME = c.TNAME AND u.COLUMN_NAME = c.CNAME AND u.DATA_TYPE = 'VARCHAR2')
+             LEFT JOIN USER_TRIGGERS t ON (t.TABLE_NAME = c.TNAME AND TRIGGER_TYPE = 'BEFORE EACH ROW' AND c.CNAME = 'ID')
                  WHERE TNAME = UPPER('{" . $table . "}')
               ORDER BY COLNO";
 
@@ -517,6 +521,7 @@ class oci_native_moodle_database extends moodle_database {
 
             $info = new stdClass();
             $info->name = strtolower($rawcolumn->CNAME);
+            $info->auto_increment = ((int)$rawcolumn->HASTRIGGER) ? true : false;
             $matches = null;
 
             if ($rawcolumn->COLTYPE === 'VARCHAR2'
@@ -550,7 +555,6 @@ class oci_native_moodle_database extends moodle_database {
                 $info->primary_key   = false;
                 $info->binary        = false;
                 $info->unsigned      = null;
-                $info->auto_increment= false;
                 $info->unique        = null;
 
             } else if ($rawcolumn->COLTYPE === 'NUMBER') {
@@ -563,13 +567,11 @@ class oci_native_moodle_database extends moodle_database {
                         $info->primary_key   = true;
                         $info->meta_type     = 'R';
                         $info->unique        = true;
-                        $info->auto_increment= true;
                         $info->has_default   = false;
                     } else {
                         $info->primary_key   = false;
                         $info->meta_type     = 'I';
                         $info->unique        = null;
-                        $info->auto_increment= false;
                     }
                     $info->scale = null;
 
@@ -578,7 +580,6 @@ class oci_native_moodle_database extends moodle_database {
                     $info->meta_type     = 'N';
                     $info->primary_key   = false;
                     $info->unsigned      = null;
-                    $info->auto_increment= false;
                     $info->unique        = null;
                     $info->scale         = $rawcolumn->SCALE;
                 }
@@ -596,7 +597,6 @@ class oci_native_moodle_database extends moodle_database {
                 $info->primary_key   = false;
                 $info->meta_type     = 'N';
                 $info->unique        = null;
-                $info->auto_increment= false;
                 $info->not_null      = ($rawcolumn->NULLS === 'NOT NULL');
                 $info->has_default   = !is_null($rawcolumn->DEFAULTVAL);
                 if ($info->has_default) {
@@ -632,7 +632,6 @@ class oci_native_moodle_database extends moodle_database {
                 $info->primary_key   = false;
                 $info->binary        = false;
                 $info->unsigned      = null;
-                $info->auto_increment= false;
                 $info->unique        = null;
 
             } else if ($rawcolumn->COLTYPE === 'BLOB') {
@@ -661,7 +660,6 @@ class oci_native_moodle_database extends moodle_database {
                 $info->primary_key   = false;
                 $info->binary        = true;
                 $info->unsigned      = null;
-                $info->auto_increment= false;
                 $info->unique        = null;
 
             } else {