From d1c21c8f31ca55c71a6bdad8ba60c26b16143213 Mon Sep 17 00:00:00 2001
From: stronk7 <stronk7>
Date: Sun, 27 Aug 2006 09:02:41 +0000
Subject: [PATCH] Very, very, very, very initial support of insert_record for
 oracle. I shouldn't interfere with the rest at all. I needed it in CVS now.

---
 lib/dmllib.php | 77 +++++++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 76 insertions(+), 1 deletion(-)

diff --git a/lib/dmllib.php b/lib/dmllib.php
index a78ecd2df7..765d792f46 100644
--- a/lib/dmllib.php
+++ b/lib/dmllib.php
@@ -602,7 +602,10 @@ function get_recordset_sql($sql, $limitfrom=null, $limitnum=null) {
 
     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 
-    if ($limitfrom && $limitnum) {
+    if ($limitfrom || $limitnum) {
+        ///Special case, 0 must be -1 for ADOdb
+        $limitfrom = empty($limitfrom) ? -1 : $limitfrom;
+        $limitnum  = empty($limitnum) ? -1 : $limitnum;
         $rs = $db->SelectLimit($sql, $limitnum, $limitfrom);
     } else {
         $rs = $db->Execute($sql);
@@ -975,6 +978,12 @@ function insert_record($table, $dataobject, $returnid=true, $primarykey='id') {
 
     global $db, $CFG, $empty_rs_cache;
 
+/// Implement one cache to store meta data (needed by Oracle inserts)
+/// TODO: Possibly make it global to benefit other functions needing it (update_record...)
+    if (!isset($metadatacache)) {
+        $metadatacache = array();
+    }
+
     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 
 /// In Moodle we always use auto-numbering fields for the primary key
@@ -1002,6 +1011,72 @@ function insert_record($table, $dataobject, $returnid=true, $primarykey='id') {
         } 
     }
 
+/// First basic support of insert for Oracle. As it doesn't 
+/// support autogenerated fields, we rely on the corresponding
+/// sequence. It will work automatically, unless we need to
+/// return the primary from the function, in this case we
+/// get the next sequence value here and insert it manually.
+    if ( $CFG->dbtype === 'oci8po' && $returnid == true) {
+    /// We need this here (move this function to dmlib?)
+        include_once($CFG->libdir . '/xmldb/classes/generators/XMLDBGenerator.class.php');
+        include_once($CFG->libdir . '/xmldb/classes/generators/oci8po/oci8po.class.php');
+        $generator = new XMLDBoci8po();
+        $generator->setPrefix($CFG->prefix);
+        $seqname = $generator->getNameForObject($table, $primarykey, 'seq');
+        if ($nextval = (int)get_field_sql("SELECT $seqname.NEXTVAL from dual")) {
+            $dataobject->{$primarykey} = $nextval;            
+        }
+    }
+/// Also, for Oracle DB, empty strings are converted to NULLs in DB
+/// and this breaks a lot of NOT NULL columns currenty Moodle. In the future it's
+/// planned to move some of them to NULL, if they must accept empty values and this
+/// piece of code will become less and less used. But, for now, we need it.
+/// What we are going to do is to examine all the data being inserted and if it's
+/// an empty string (NULL for Oracle) and the field is defined as NOT NULL, we'll modify
+/// such data in the best form possible ("0" for booleans and numbers and " " for the
+/// rest of strings. It isn't optimal, but the only way to do so. 
+/// In the oppsite, when retrieving records from Oracle, we'll decode " " back to
+/// empty strings to allow everything to work properly.
+    if ( $CFG->dbtype === 'oci8po') {
+    /// Get Meta info to know what to change, using the cached meta if exists
+        if (!isset($metadatacache[$table])) {
+            $metadatacache[$table] = array_change_key_case($db->MetaColumns($CFG->prefix . $table), CASE_LOWER);
+        }
+        $columns = $metadatacache[$table];
+    /// Iterate over all the fields in the insert, transforming values
+    /// in the best possible form
+        foreach ($dataobject as $fieldname => $fieldvalue) {
+        /// If the field doesn't exist in metadata, skip
+            if (!isset($columns[strtolower($fieldname)])) {
+                continue;
+            }
+        /// If the field ins't VARCHAR or CLOB, skip
+            if ($columns[strtolower($fieldname)]->type != 'VARCHAR2' && $columns[strtolower($fieldname)]->type != 'CLOB') {
+                continue;
+            }
+        /// If the field isn't NOT NULL, skip (it's nullable, so accept empty values)
+            if (!$columns[strtolower($fieldname)]->not_null) {
+                continue;
+            }
+        /// If the value isn't empty, skip
+            if (!empty($fieldvalue)) {
+                continue;
+            }
+        /// Now, we have one empty value, going to be inserted to one NOT NULL, VARCHAR2 or CLOB field
+        /// Try to get the best value to be inserted
+            if (gettype($fieldvalue) == 'boolean') {
+                $dataobject->$fieldname = '0'; /// Transform false to '0' that evaluates the same for PHP
+            } else if (gettype($fieldvalue) == 'integer') {
+                $dataobject->$fieldname = '0'; /// Transform 0 to '0' that evaluates the same for PHP
+            } else if (gettype($fieldvalue) == 'NULL') {
+                $dataobject->$fieldname = '0'; /// Transform NULL to '0' that evaluates the same for PHP
+            } else {
+                $dataobject->$fieldname = ' '; /// Transform '' to ' ' that DONT'T EVALUATE THE SAME
+                                               /// (we'll transform back again on get_records_XXX functions)!!
+            }
+        }
+    }
+
 /// Get the correct SQL from adoDB
     if (!$insertSQL = $db->GetInsertSQL($rs, (array)$dataobject, true)) {
         return false;
-- 
2.39.5