Merge branch 'MDL-47322-master' of https://github.com/sammarshallou/moodle
[moodle.git] / lib / adodb / adodb-lib.inc.php
1 <?php
2 // security - hide paths
3 if (!defined('ADODB_DIR')) die();
5 global $ADODB_INCLUDED_LIB;
6 $ADODB_INCLUDED_LIB = 1;
8 /*
9   @version V5.19  23-Apr-2014  (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved.
10   Released under both BSD license and Lesser GPL library license.
11   Whenever there is any discrepancy between the two licenses,
12   the BSD license will take precedence. See License.txt.
13   Set tabs to 4 for best viewing.
15   Less commonly used functions are placed here to reduce size of adodb.inc.php.
16 */
18 function adodb_strip_order_by($sql)
19 {
20         $rez = preg_match('/(\sORDER\s+BY\s(?:[^)](?!limit))*)(?:\sLIMIT\s+[0-9]+)?/is', $sql, $arr);
21         if ($arr)
22                 if (strpos($arr[1], '(') !== false) {
23                         $at = strpos($sql, $arr[1]);
24                         $cntin = 0;
25                         for ($i=$at, $max=strlen($sql); $i < $max; $i++) {
26                                 $ch = $sql[$i];
27                                 if ($ch == '(') {
28                                         $cntin += 1;
29                                 } elseif($ch == ')') {
30                                         $cntin -= 1;
31                                         if ($cntin < 0) {
32                                                 break;
33                                         }
34                                 }
35                         }
36                         $sql = substr($sql,0,$at).substr($sql,$i);
37                 } else {
38                         $sql = str_replace($arr[1], '', $sql);
39                 }
40         return $sql;
41  }
43 if (false) {
44         $sql = 'select * from (select a from b order by a(b),b(c) desc)';
45         $sql = '(select * from abc order by 1)';
46         die(adodb_strip_order_by($sql));
47 }
49 function adodb_probetypes(&$array,&$types,$probe=8)
50 {
51 // probe and guess the type
52         $types = array();
53         if ($probe > sizeof($array)) $max = sizeof($array);
54         else $max = $probe;
57         for ($j=0;$j < $max; $j++) {
58                 $row = $array[$j];
59                 if (!$row) break;
60                 $i = -1;
61                 foreach($row as $v) {
62                         $i += 1;
64                         if (isset($types[$i]) && $types[$i]=='C') continue;
66                         //print " ($i ".$types[$i]. "$v) ";
67                         $v = trim($v);
69                         if (!preg_match('/^[+-]{0,1}[0-9\.]+$/',$v)) {
70                                 $types[$i] = 'C'; // once C, always C
72                                 continue;
73                         }
74                         if ($j == 0) {
75                         // If empty string, we presume is character
76                         // test for integer for 1st row only
77                         // after that it is up to testing other rows to prove
78                         // that it is not an integer
79                                 if (strlen($v) == 0) $types[$i] = 'C';
80                                 if (strpos($v,'.') !== false) $types[$i] = 'N';
81                                 else  $types[$i] = 'I';
82                                 continue;
83                         }
85                         if (strpos($v,'.') !== false) $types[$i] = 'N';
87                 }
88         }
90 }
92 function  adodb_transpose(&$arr, &$newarr, &$hdr, &$fobjs)
93 {
94         $oldX = sizeof(reset($arr));
95         $oldY = sizeof($arr);
97         if ($hdr) {
98                 $startx = 1;
99                 $hdr = array('Fields');
100                 for ($y = 0; $y < $oldY; $y++) {
101                         $hdr[] = $arr[$y][0];
102                 }
103         } else
104                 $startx = 0;
106         for ($x = $startx; $x < $oldX; $x++) {
107                 if ($fobjs) {
108                         $o = $fobjs[$x];
109                         $newarr[] = array($o->name);
110                 } else
111                         $newarr[] = array();
113                 for ($y = 0; $y < $oldY; $y++) {
114                         $newarr[$x-$startx][] = $arr[$y][$x];
115                 }
116         }
119 // Force key to upper.
120 // See also http://www.php.net/manual/en/function.array-change-key-case.php
121 function _array_change_key_case($an_array)
123         if (is_array($an_array)) {
124                 $new_array = array();
125                 foreach($an_array as $key=>$value)
126                         $new_array[strtoupper($key)] = $value;
128                 return $new_array;
129    }
131         return $an_array;
134 function _adodb_replace(&$zthis, $table, $fieldArray, $keyCol, $autoQuote, $has_autoinc)
136                 if (count($fieldArray) == 0) return 0;
137                 $first = true;
138                 $uSet = '';
140                 if (!is_array($keyCol)) {
141                         $keyCol = array($keyCol);
142                 }
143                 foreach($fieldArray as $k => $v) {
144                         if ($v === null) {
145                                 $v = 'NULL';
146                                 $fieldArray[$k] = $v;
147                         } else if ($autoQuote && /*!is_numeric($v) /*and strncmp($v,"'",1) !== 0 -- sql injection risk*/ strcasecmp($v,$zthis->null2null)!=0) {
148                                 $v = $zthis->qstr($v);
149                                 $fieldArray[$k] = $v;
150                         }
151                         if (in_array($k,$keyCol)) continue; // skip UPDATE if is key
153                         if ($first) {
154                                 $first = false;
155                                 $uSet = "$k=$v";
156                         } else
157                                 $uSet .= ",$k=$v";
158                 }
160                 $where = false;
161                 foreach ($keyCol as $v) {
162                         if (isset($fieldArray[$v])) {
163                                 if ($where) $where .= ' and '.$v.'='.$fieldArray[$v];
164                                 else $where = $v.'='.$fieldArray[$v];
165                         }
166                 }
168                 if ($uSet && $where) {
169                         $update = "UPDATE $table SET $uSet WHERE $where";
171                         $rs = $zthis->Execute($update);
174                         if ($rs) {
175                                 if ($zthis->poorAffectedRows) {
176                                 /*
177                                  The Select count(*) wipes out any errors that the update would have returned.
178                                 http://phplens.com/lens/lensforum/msgs.php?id=5696
179                                 */
180                                         if ($zthis->ErrorNo()<>0) return 0;
182                                 # affected_rows == 0 if update field values identical to old values
183                                 # for mysql - which is silly.
185                                         $cnt = $zthis->GetOne("select count(*) from $table where $where");
186                                         if ($cnt > 0) return 1; // record already exists
187                                 } else {
188                                         if (($zthis->Affected_Rows()>0)) return 1;
189                                 }
190                         } else
191                                 return 0;
192                 }
194         //      print "<p>Error=".$this->ErrorNo().'<p>';
195                 $first = true;
196                 foreach($fieldArray as $k => $v) {
197                         if ($has_autoinc && in_array($k,$keyCol)) continue; // skip autoinc col
199                         if ($first) {
200                                 $first = false;
201                                 $iCols = "$k";
202                                 $iVals = "$v";
203                         } else {
204                                 $iCols .= ",$k";
205                                 $iVals .= ",$v";
206                         }
207                 }
208                 $insert = "INSERT INTO $table ($iCols) VALUES ($iVals)";
209                 $rs = $zthis->Execute($insert);
210                 return ($rs) ? 2 : 0;
213 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
214 function _adodb_getmenu(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
215                         $size=0, $selectAttr='',$compareFields0=true)
217         $hasvalue = false;
219         if ($multiple or is_array($defstr)) {
220                 if ($size==0) $size=5;
221                 $attr = ' multiple size="'.$size.'"';
222                 if (!strpos($name,'[]')) $name .= '[]';
223         } else if ($size) $attr = ' size="'.$size.'"';
224         else $attr ='';
226         $s = '<select name="'.$name.'"'.$attr.' '.$selectAttr.'>';
227         if ($blank1stItem)
228                 if (is_string($blank1stItem))  {
229                         $barr = explode(':',$blank1stItem);
230                         if (sizeof($barr) == 1) $barr[] = '';
231                         $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
232                 } else $s .= "\n<option></option>";
234         if ($zthis->FieldCount() > 1) $hasvalue=true;
235         else $compareFields0 = true;
237         $value = '';
238     $optgroup = null;
239     $firstgroup = true;
240     $fieldsize = $zthis->FieldCount();
241         while(!$zthis->EOF) {
242                 $zval = rtrim(reset($zthis->fields));
244                 if ($blank1stItem && $zval=="") {
245                         $zthis->MoveNext();
246                         continue;
247                 }
249         if ($fieldsize > 1) {
250                         if (isset($zthis->fields[1]))
251                                 $zval2 = rtrim($zthis->fields[1]);
252                         else
253                                 $zval2 = rtrim(next($zthis->fields));
254                 }
255                 $selected = ($compareFields0) ? $zval : $zval2;
257         $group = '';
258                 if ($fieldsize > 2) {
259             $group = rtrim($zthis->fields[2]);
260         }
261 /*
262         if ($optgroup != $group) {
263             $optgroup = $group;
264             if ($firstgroup) {
265                 $firstgroup = false;
266                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
267             } else {
268                 $s .="\n</optgroup>";
269                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
270             }
271                 }
272 */
273                 if ($hasvalue)
274                         $value = " value='".htmlspecialchars($zval2)."'";
276                 if (is_array($defstr))  {
278                         if (in_array($selected,$defstr))
279                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
280                         else
281                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
282                 }
283                 else {
284                         if (strcasecmp($selected,$defstr)==0)
285                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
286                         else
287                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
288                 }
289                 $zthis->MoveNext();
290         } // while
292     // closing last optgroup
293     if($optgroup != null) {
294         $s .= "\n</optgroup>";
295         }
296         return $s ."\n</select>\n";
299 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
300 function _adodb_getmenu_gp(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
301                         $size=0, $selectAttr='',$compareFields0=true)
303         $hasvalue = false;
305         if ($multiple or is_array($defstr)) {
306                 if ($size==0) $size=5;
307                 $attr = ' multiple size="'.$size.'"';
308                 if (!strpos($name,'[]')) $name .= '[]';
309         } else if ($size) $attr = ' size="'.$size.'"';
310         else $attr ='';
312         $s = '<select name="'.$name.'"'.$attr.' '.$selectAttr.'>';
313         if ($blank1stItem)
314                 if (is_string($blank1stItem))  {
315                         $barr = explode(':',$blank1stItem);
316                         if (sizeof($barr) == 1) $barr[] = '';
317                         $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
318                 } else $s .= "\n<option></option>";
320         if ($zthis->FieldCount() > 1) $hasvalue=true;
321         else $compareFields0 = true;
323         $value = '';
324     $optgroup = null;
325     $firstgroup = true;
326     $fieldsize = sizeof($zthis->fields);
327         while(!$zthis->EOF) {
328                 $zval = rtrim(reset($zthis->fields));
330                 if ($blank1stItem && $zval=="") {
331                         $zthis->MoveNext();
332                         continue;
333                 }
335         if ($fieldsize > 1) {
336                         if (isset($zthis->fields[1]))
337                                 $zval2 = rtrim($zthis->fields[1]);
338                         else
339                                 $zval2 = rtrim(next($zthis->fields));
340                 }
341                 $selected = ($compareFields0) ? $zval : $zval2;
343         $group = '';
344                 if (isset($zthis->fields[2])) {
345             $group = rtrim($zthis->fields[2]);
346         }
348         if ($optgroup != $group) {
349             $optgroup = $group;
350             if ($firstgroup) {
351                 $firstgroup = false;
352                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
353             } else {
354                 $s .="\n</optgroup>";
355                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
356             }
357                 }
359                 if ($hasvalue)
360                         $value = " value='".htmlspecialchars($zval2)."'";
362                 if (is_array($defstr))  {
364                         if (in_array($selected,$defstr))
365                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
366                         else
367                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
368                 }
369                 else {
370                         if (strcasecmp($selected,$defstr)==0)
371                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
372                         else
373                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
374                 }
375                 $zthis->MoveNext();
376         } // while
378     // closing last optgroup
379     if($optgroup != null) {
380         $s .= "\n</optgroup>";
381         }
382         return $s ."\n</select>\n";
386 /*
387         Count the number of records this sql statement will return by using
388         query rewriting heuristics...
390         Does not work with UNIONs, except with postgresql and oracle.
392         Usage:
394         $conn->Connect(...);
395         $cnt = _adodb_getcount($conn, $sql);
397 */
398 function _adodb_getcount(&$zthis, $sql,$inputarr=false,$secs2cache=0)
400         $qryRecs = 0;
402          if (!empty($zthis->_nestedSQL) || preg_match("/^\s*SELECT\s+DISTINCT/is", $sql) ||
403                 preg_match('/\s+GROUP\s+BY\s+/is',$sql) ||
404                 preg_match('/\s+UNION\s+/is',$sql)) {
406                 $rewritesql = adodb_strip_order_by($sql);
408                 // ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias
409                 // but this is only supported by oracle and postgresql...
410                 if ($zthis->dataProvider == 'oci8') {
411                         // Allow Oracle hints to be used for query optimization, Chris Wrye
412                         if (preg_match('#/\\*+.*?\\*\\/#', $sql, $hint)) {
413                                 $rewritesql = "SELECT ".$hint[0]." COUNT(*) FROM (".$rewritesql.")";
414                         } else
415                                 $rewritesql = "SELECT COUNT(*) FROM (".$rewritesql.")";
417                 } else if (strncmp($zthis->databaseType,'postgres',8) == 0 || strncmp($zthis->databaseType,'mysql',5) == 0)  {
418                         $rewritesql = "SELECT COUNT(*) FROM ($rewritesql) _ADODB_ALIAS_";
419                 } else {
420                         $rewritesql = "SELECT COUNT(*) FROM ($rewritesql)";
421                 }
422         } else {
423                 // now replace SELECT ... FROM with SELECT COUNT(*) FROM
424                 $rewritesql = preg_replace(
425                                         '/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$sql);
426                 // fix by alexander zhukov, alex#unipack.ru, because count(*) and 'order by' fails
427                 // with mssql, access and postgresql. Also a good speedup optimization - skips sorting!
428                 // also see http://phplens.com/lens/lensforum/msgs.php?id=12752
429                 $rewritesql = adodb_strip_order_by($rewritesql);
430         }
432         if (isset($rewritesql) && $rewritesql != $sql) {
433                 if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
435                 if ($secs2cache) {
436                         // we only use half the time of secs2cache because the count can quickly
437                         // become inaccurate if new records are added
438                         $qryRecs = $zthis->CacheGetOne($secs2cache/2,$rewritesql,$inputarr);
440                 } else {
441                         $qryRecs = $zthis->GetOne($rewritesql,$inputarr);
442                 }
443                 if ($qryRecs !== false) return $qryRecs;
444         }
445         //--------------------------------------------
446         // query rewrite failed - so try slower way...
449         // strip off unneeded ORDER BY if no UNION
450         if (preg_match('/\s*UNION\s*/is', $sql)) $rewritesql = $sql;
451         else $rewritesql = $rewritesql = adodb_strip_order_by($sql);
453         if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
455         if ($secs2cache) {
456                 $rstest = $zthis->CacheExecute($secs2cache,$rewritesql,$inputarr);
457                 if (!$rstest) $rstest = $zthis->CacheExecute($secs2cache,$sql,$inputarr);
458         } else {
459                 $rstest = $zthis->Execute($rewritesql,$inputarr);
460                 if (!$rstest) $rstest = $zthis->Execute($sql,$inputarr);
461         }
462         if ($rstest) {
463                         $qryRecs = $rstest->RecordCount();
464                 if ($qryRecs == -1) {
465                 global $ADODB_EXTENSION;
466                 // some databases will return -1 on MoveLast() - change to MoveNext()
467                         if ($ADODB_EXTENSION) {
468                                 while(!$rstest->EOF) {
469                                         adodb_movenext($rstest);
470                                 }
471                         } else {
472                                 while(!$rstest->EOF) {
473                                         $rstest->MoveNext();
474                                 }
475                         }
476                         $qryRecs = $rstest->_currentRow;
477                 }
478                 $rstest->Close();
479                 if ($qryRecs == -1) return 0;
480         }
481         return $qryRecs;
484 /*
485         Code originally from "Cornel G" <conyg@fx.ro>
487         This code might not work with SQL that has UNION in it
489         Also if you are using CachePageExecute(), there is a strong possibility that
490         data will get out of synch. use CachePageExecute() only with tables that
491         rarely change.
492 */
493 function _adodb_pageexecute_all_rows(&$zthis, $sql, $nrows, $page,
494                                                 $inputarr=false, $secs2cache=0)
496         $atfirstpage = false;
497         $atlastpage = false;
498         $lastpageno=1;
500         // If an invalid nrows is supplied,
501         // we assume a default value of 10 rows per page
502         if (!isset($nrows) || $nrows <= 0) $nrows = 10;
504         $qryRecs = false; //count records for no offset
506         $qryRecs = _adodb_getcount($zthis,$sql,$inputarr,$secs2cache);
507         $lastpageno = (int) ceil($qryRecs / $nrows);
508         $zthis->_maxRecordCount = $qryRecs;
512         // ***** Here we check whether $page is the last page or
513         // whether we are trying to retrieve
514         // a page number greater than the last page number.
515         if ($page >= $lastpageno) {
516                 $page = $lastpageno;
517                 $atlastpage = true;
518         }
520         // If page number <= 1, then we are at the first page
521         if (empty($page) || $page <= 1) {
522                 $page = 1;
523                 $atfirstpage = true;
524         }
526         // We get the data we want
527         $offset = $nrows * ($page-1);
528         if ($secs2cache > 0)
529                 $rsreturn = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
530         else
531                 $rsreturn = $zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
534         // Before returning the RecordSet, we set the pagination properties we need
535         if ($rsreturn) {
536                 $rsreturn->_maxRecordCount = $qryRecs;
537                 $rsreturn->rowsPerPage = $nrows;
538                 $rsreturn->AbsolutePage($page);
539                 $rsreturn->AtFirstPage($atfirstpage);
540                 $rsreturn->AtLastPage($atlastpage);
541                 $rsreturn->LastPageNo($lastpageno);
542         }
543         return $rsreturn;
546 // Iv├ín Oliva version
547 function _adodb_pageexecute_no_last_page(&$zthis, $sql, $nrows, $page, $inputarr=false, $secs2cache=0)
550         $atfirstpage = false;
551         $atlastpage = false;
553         if (!isset($page) || $page <= 1) {      // If page number <= 1, then we are at the first page
554                 $page = 1;
555                 $atfirstpage = true;
556         }
557         if ($nrows <= 0) $nrows = 10;   // If an invalid nrows is supplied, we assume a default value of 10 rows per page
559         // ***** Here we check whether $page is the last page or whether we are trying to retrieve a page number greater than
560         // the last page number.
561         $pagecounter = $page + 1;
562         $pagecounteroffset = ($pagecounter * $nrows) - $nrows;
563         if ($secs2cache>0) $rstest = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
564         else $rstest = $zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
565         if ($rstest) {
566                 while ($rstest && $rstest->EOF && $pagecounter>0) {
567                         $atlastpage = true;
568                         $pagecounter--;
569                         $pagecounteroffset = $nrows * ($pagecounter - 1);
570                         $rstest->Close();
571                         if ($secs2cache>0) $rstest = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
572                         else $rstest = $zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
573                 }
574                 if ($rstest) $rstest->Close();
575         }
576         if ($atlastpage) {      // If we are at the last page or beyond it, we are going to retrieve it
577                 $page = $pagecounter;
578                 if ($page == 1) $atfirstpage = true;    // We have to do this again in case the last page is the same as the first
579                         //... page, that is, the recordset has only 1 page.
580         }
582         // We get the data we want
583         $offset = $nrows * ($page-1);
584         if ($secs2cache > 0) $rsreturn = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
585         else $rsreturn = $zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
587         // Before returning the RecordSet, we set the pagination properties we need
588         if ($rsreturn) {
589                 $rsreturn->rowsPerPage = $nrows;
590                 $rsreturn->AbsolutePage($page);
591                 $rsreturn->AtFirstPage($atfirstpage);
592                 $rsreturn->AtLastPage($atlastpage);
593         }
594         return $rsreturn;
597 function _adodb_getupdatesql(&$zthis,&$rs, $arrFields,$forceUpdate=false,$magicq=false,$force=2)
599         global $ADODB_QUOTE_FIELDNAMES;
601                 if (!$rs) {
602                         printf(ADODB_BAD_RS,'GetUpdateSQL');
603                         return false;
604                 }
606                 $fieldUpdatedCount = 0;
607                 $arrFields = _array_change_key_case($arrFields);
609                 $hasnumeric = isset($rs->fields[0]);
610                 $setFields = '';
612                 // Loop through all of the fields in the recordset
613                 for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++) {
614                         // Get the field from the recordset
615                         $field = $rs->FetchField($i);
617                         // If the recordset field is one
618                         // of the fields passed in then process.
619                         $upperfname = strtoupper($field->name);
620                         if (adodb_key_exists($upperfname,$arrFields,$force)) {
622                                 // If the existing field value in the recordset
623                                 // is different from the value passed in then
624                                 // go ahead and append the field name and new value to
625                                 // the update query.
627                                 if ($hasnumeric) $val = $rs->fields[$i];
628                                 else if (isset($rs->fields[$upperfname])) $val = $rs->fields[$upperfname];
629                                 else if (isset($rs->fields[$field->name])) $val =  $rs->fields[$field->name];
630                                 else if (isset($rs->fields[strtolower($upperfname)])) $val =  $rs->fields[strtolower($upperfname)];
631                                 else $val = '';
634                                 if ($forceUpdate || strcmp($val, $arrFields[$upperfname])) {
635                                         // Set the counter for the number of fields that will be updated.
636                                         $fieldUpdatedCount++;
638                                         // Based on the datatype of the field
639                                         // Format the value properly for the database
640                                         $type = $rs->MetaType($field->type);
643                                         if ($type == 'null') {
644                                                 $type = 'C';
645                                         }
647                                         if ((strpos($upperfname,' ') !== false) || ($ADODB_QUOTE_FIELDNAMES)) {
648                                                 switch ($ADODB_QUOTE_FIELDNAMES) {
649                                                 case 'LOWER':
650                                                         $fnameq = $zthis->nameQuote.strtolower($field->name).$zthis->nameQuote;break;
651                                                 case 'NATIVE':
652                                                         $fnameq = $zthis->nameQuote.$field->name.$zthis->nameQuote;break;
653                                                 case 'UPPER':
654                                                 default:
655                                                         $fnameq = $zthis->nameQuote.$upperfname.$zthis->nameQuote;break;
656                                                 }
657                                         } else
658                                                 $fnameq = $upperfname;
660                 //********************************************************//
661                 if (is_null($arrFields[$upperfname])
662                                         || (empty($arrFields[$upperfname]) && strlen($arrFields[$upperfname]) == 0)
663                     || $arrFields[$upperfname] === $zthis->null2null
664                     )
665                 {
666                     switch ($force) {
668                         //case 0:
669                         //    //Ignore empty values. This is allready handled in "adodb_key_exists" function.
670                         //break;
672                         case 1:
673                             //Set null
674                             $setFields .= $field->name . " = null, ";
675                         break;
677                         case 2:
678                             //Set empty
679                             $arrFields[$upperfname] = "";
680                             $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,$arrFields, $magicq);
681                         break;
682                                                 default:
683                         case 3:
684                             //Set the value that was given in array, so you can give both null and empty values
685                             if (is_null($arrFields[$upperfname]) || $arrFields[$upperfname] === $zthis->null2null) {
686                                 $setFields .= $field->name . " = null, ";
687                             } else {
688                                 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,$arrFields, $magicq);
689                             }
690                         break;
691                     }
692                 //********************************************************//
693                 } else {
694                                                 //we do this so each driver can customize the sql for
695                                                 //DB specific column types.
696                                                 //Oracle needs BLOB types to be handled with a returning clause
697                                                 //postgres has special needs as well
698                                                 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,
699                                                                                                                   $arrFields, $magicq);
700                                         }
701                                 }
702                         }
703                 }
705                 // If there were any modified fields then build the rest of the update query.
706                 if ($fieldUpdatedCount > 0 || $forceUpdate) {
707                                         // Get the table name from the existing query.
708                         if (!empty($rs->tableName)) $tableName = $rs->tableName;
709                         else {
710                                 preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName);
711                                 $tableName = $tableName[1];
712                         }
713                         // Get the full where clause excluding the word "WHERE" from
714                         // the existing query.
715                         preg_match('/\sWHERE\s(.*)/is', $rs->sql, $whereClause);
717                         $discard = false;
718                         // not a good hack, improvements?
719                         if ($whereClause) {
720                         #var_dump($whereClause);
721                                 if (preg_match('/\s(ORDER\s.*)/is', $whereClause[1], $discard));
722                                 else if (preg_match('/\s(LIMIT\s.*)/is', $whereClause[1], $discard));
723                                 else if (preg_match('/\s(FOR UPDATE.*)/is', $whereClause[1], $discard));
724                                 else preg_match('/\s.*(\) WHERE .*)/is', $whereClause[1], $discard); # see http://sourceforge.net/tracker/index.php?func=detail&aid=1379638&group_id=42718&atid=433976
725                         } else
726                                 $whereClause = array(false,false);
728                         if ($discard)
729                                 $whereClause[1] = substr($whereClause[1], 0, strlen($whereClause[1]) - strlen($discard[1]));
731                         $sql = 'UPDATE '.$tableName.' SET '.substr($setFields, 0, -2);
732                         if (strlen($whereClause[1]) > 0)
733                                 $sql .= ' WHERE '.$whereClause[1];
735                         return $sql;
737                 } else {
738                         return false;
739         }
742 function adodb_key_exists($key, &$arr,$force=2)
744         if ($force<=0) {
745                 // the following is the old behaviour where null or empty fields are ignored
746                 return (!empty($arr[$key])) || (isset($arr[$key]) && strlen($arr[$key])>0);
747         }
749         if (isset($arr[$key])) return true;
750         ## null check below
751         if (ADODB_PHPVER >= 0x4010) return array_key_exists($key,$arr);
752         return false;
755 /**
756  * There is a special case of this function for the oci8 driver.
757  * The proper way to handle an insert w/ a blob in oracle requires
758  * a returning clause with bind variables and a descriptor blob.
759  *
760  *
761  */
762 function _adodb_getinsertsql(&$zthis,&$rs,$arrFields,$magicq=false,$force=2)
764 static $cacheRS = false;
765 static $cacheSig = 0;
766 static $cacheCols;
767         global $ADODB_QUOTE_FIELDNAMES;
769         $tableName = '';
770         $values = '';
771         $fields = '';
772         $recordSet = null;
773         $arrFields = _array_change_key_case($arrFields);
774         $fieldInsertedCount = 0;
776         if (is_string($rs)) {
777                 //ok we have a table name
778                 //try and get the column info ourself.
779                 $tableName = $rs;
781                 //we need an object for the recordSet
782                 //because we have to call MetaType.
783                 //php can't do a $rsclass::MetaType()
784                 $rsclass = $zthis->rsPrefix.$zthis->databaseType;
785                 $recordSet = new $rsclass(-1,$zthis->fetchMode);
786                 $recordSet->connection = $zthis;
788                 if (is_string($cacheRS) && $cacheRS == $rs) {
789                         $columns = $cacheCols;
790                 } else {
791                         $columns = $zthis->MetaColumns( $tableName );
792                         $cacheRS = $tableName;
793                         $cacheCols = $columns;
794                 }
795         } else if (is_subclass_of($rs, 'adorecordset')) {
796                 if (isset($rs->insertSig) && is_integer($cacheRS) && $cacheRS == $rs->insertSig) {
797                         $columns = $cacheCols;
798                 } else {
799                         for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++)
800                                 $columns[] = $rs->FetchField($i);
801                         $cacheRS = $cacheSig;
802                         $cacheCols = $columns;
803                         $rs->insertSig = $cacheSig++;
804                 }
805                 $recordSet = $rs;
807         } else {
808                 printf(ADODB_BAD_RS,'GetInsertSQL');
809                 return false;
810         }
812         // Loop through all of the fields in the recordset
813         foreach( $columns as $field ) {
814                 $upperfname = strtoupper($field->name);
815                 if (adodb_key_exists($upperfname,$arrFields,$force)) {
816                         $bad = false;
817                         if ((strpos($upperfname,' ') !== false) || ($ADODB_QUOTE_FIELDNAMES)) {
818                                 switch ($ADODB_QUOTE_FIELDNAMES) {
819                                 case 'LOWER':
820                                         $fnameq = $zthis->nameQuote.strtolower($field->name).$zthis->nameQuote;break;
821                                 case 'NATIVE':
822                                         $fnameq = $zthis->nameQuote.$field->name.$zthis->nameQuote;break;
823                                 case 'UPPER':
824                                 default:
825                                         $fnameq = $zthis->nameQuote.$upperfname.$zthis->nameQuote;break;
826                                 }
827                         } else
828                                 $fnameq = $upperfname;
830                         $type = $recordSet->MetaType($field->type);
832             /********************************************************/
833             if (is_null($arrFields[$upperfname])
834                 || (empty($arrFields[$upperfname]) && strlen($arrFields[$upperfname]) == 0)
835                 || $arrFields[$upperfname] === $zthis->null2null
836                                 )
837                {
838                     switch ($force) {
840                         case 0: // we must always set null if missing
841                                                         $bad = true;
842                                                         break;
844                         case 1:
845                             $values  .= "null, ";
846                         break;
848                         case 2:
849                             //Set empty
850                             $arrFields[$upperfname] = "";
851                             $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq,$arrFields, $magicq);
852                         break;
854                                                 default:
855                         case 3:
856                             //Set the value that was given in array, so you can give both null and empty values
857                                                         if (is_null($arrFields[$upperfname]) || $arrFields[$upperfname] === $zthis->null2null) {
858                                                                 $values  .= "null, ";
859                                                         } else {
860                                         $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq, $arrFields, $magicq);
861                                         }
862                                 break;
863                         } // switch
865             /*********************************************************/
866                         } else {
867                                 //we do this so each driver can customize the sql for
868                                 //DB specific column types.
869                                 //Oracle needs BLOB types to be handled with a returning clause
870                                 //postgres has special needs as well
871                                 $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq,
872                                                                                            $arrFields, $magicq);
873                         }
875                         if ($bad) continue;
876                         // Set the counter for the number of fields that will be inserted.
877                         $fieldInsertedCount++;
880                         // Get the name of the fields to insert
881                         $fields .= $fnameq . ", ";
882                 }
883         }
886         // If there were any inserted fields then build the rest of the insert query.
887         if ($fieldInsertedCount <= 0)  return false;
889         // Get the table name from the existing query.
890         if (!$tableName) {
891                 if (!empty($rs->tableName)) $tableName = $rs->tableName;
892                 else if (preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName))
893                         $tableName = $tableName[1];
894                 else
895                         return false;
896         }
898         // Strip off the comma and space on the end of both the fields
899         // and their values.
900         $fields = substr($fields, 0, -2);
901         $values = substr($values, 0, -2);
903         // Append the fields and their values to the insert query.
904         return 'INSERT INTO '.$tableName.' ( '.$fields.' ) VALUES ( '.$values.' )';
908 /**
909  * This private method is used to help construct
910  * the update/sql which is generated by GetInsertSQL and GetUpdateSQL.
911  * It handles the string construction of 1 column -> sql string based on
912  * the column type.  We want to do 'safe' handling of BLOBs
913  *
914  * @param string the type of sql we are trying to create
915  *                'I' or 'U'.
916  * @param string column data type from the db::MetaType() method
917  * @param string the column name
918  * @param array the column value
919  *
920  * @return string
921  *
922  */
923 function _adodb_column_sql_oci8(&$zthis,$action, $type, $fname, $fnameq, $arrFields, $magicq)
925     $sql = '';
927     // Based on the datatype of the field
928     // Format the value properly for the database
929     switch($type) {
930     case 'B':
931         //in order to handle Blobs correctly, we need
932         //to do some magic for Oracle
934         //we need to create a new descriptor to handle
935         //this properly
936         if (!empty($zthis->hasReturningInto)) {
937             if ($action == 'I') {
938                 $sql = 'empty_blob(), ';
939             } else {
940                 $sql = $fnameq. '=empty_blob(), ';
941             }
942             //add the variable to the returning clause array
943             //so the user can build this later in
944             //case they want to add more to it
945             $zthis->_returningArray[$fname] = ':xx'.$fname.'xx';
946         } else if (empty($arrFields[$fname])){
947             if ($action == 'I') {
948                 $sql = 'empty_blob(), ';
949             } else {
950                 $sql = $fnameq. '=empty_blob(), ';
951             }
952         } else {
953             //this is to maintain compatibility
954             //with older adodb versions.
955             $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
956         }
957         break;
959     case "X":
960         //we need to do some more magic here for long variables
961         //to handle these correctly in oracle.
963         //create a safe bind var name
964         //to avoid conflicts w/ dupes.
965        if (!empty($zthis->hasReturningInto)) {
966             if ($action == 'I') {
967                 $sql = ':xx'.$fname.'xx, ';
968             } else {
969                 $sql = $fnameq.'=:xx'.$fname.'xx, ';
970             }
971             //add the variable to the returning clause array
972             //so the user can build this later in
973             //case they want to add more to it
974             $zthis->_returningArray[$fname] = ':xx'.$fname.'xx';
975         } else {
976             //this is to maintain compatibility
977             //with older adodb versions.
978             $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
979         }
980         break;
982     default:
983         $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq,  $arrFields, $magicq,false);
984         break;
985     }
987     return $sql;
990 function _adodb_column_sql(&$zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq, $recurse=true)
993         if ($recurse) {
994                 switch($zthis->dataProvider)  {
995                 case 'postgres':
996                         if ($type == 'L') $type = 'C';
997                         break;
998                 case 'oci8':
999                         return _adodb_column_sql_oci8($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq);
1001                 }
1002         }
1004         switch($type) {
1005                 case "C":
1006                 case "X":
1007                 case 'B':
1008                         $val = $zthis->qstr($arrFields[$fname],$magicq);
1009                         break;
1011                 case "D":
1012                         $val = $zthis->DBDate($arrFields[$fname]);
1013                         break;
1015                 case "T":
1016                         $val = $zthis->DBTimeStamp($arrFields[$fname]);
1017                         break;
1019                 case "N":
1020                     $val = $arrFields[$fname];
1021                         if (!is_numeric($val)) $val = str_replace(',', '.', (float)$val);
1022                     break;
1024                 case "I":
1025                 case "R":
1026                     $val = $arrFields[$fname];
1027                         if (!is_numeric($val)) $val = (integer) $val;
1028                     break;
1030                 default:
1031                         $val = str_replace(array("'"," ","("),"",$arrFields[$fname]); // basic sql injection defence
1032                         if (empty($val)) $val = '0';
1033                         break;
1034         }
1036         if ($action == 'I') return $val . ", ";
1039         return $fnameq . "=" . $val  . ", ";
1045 function _adodb_debug_execute(&$zthis, $sql, $inputarr)
1047         $ss = '';
1048         if ($inputarr) {
1049                 foreach($inputarr as $kk=>$vv) {
1050                         if (is_string($vv) && strlen($vv)>64) $vv = substr($vv,0,64).'...';
1051                         if (is_null($vv)) $ss .= "($kk=>null) ";
1052                         else $ss .= "($kk=>'$vv') ";
1053                 }
1054                 $ss = "[ $ss ]";
1055         }
1056         $sqlTxt = is_array($sql) ? $sql[0] : $sql;
1057         /*str_replace(', ','##1#__^LF',is_array($sql) ? $sql[0] : $sql);
1058         $sqlTxt = str_replace(',',', ',$sqlTxt);
1059         $sqlTxt = str_replace('##1#__^LF', ', ' ,$sqlTxt);
1060         */
1061         // check if running from browser or command-line
1062         $inBrowser = isset($_SERVER['HTTP_USER_AGENT']);
1064         $dbt = $zthis->databaseType;
1065         if (isset($zthis->dsnType)) $dbt .= '-'.$zthis->dsnType;
1066         if ($inBrowser) {
1067                 if ($ss) {
1068                         $ss = '<code>'.htmlspecialchars($ss).'</code>';
1069                 }
1070                 if ($zthis->debug === -1)
1071                         ADOConnection::outp( "<br>\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<br>\n",false);
1072                 else if ($zthis->debug !== -99)
1073                         ADOConnection::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<hr>\n",false);
1074         } else {
1075                 $ss = "\n   ".$ss;
1076                 if ($zthis->debug !== -99)
1077                         ADOConnection::outp("-----<hr>\n($dbt): ".$sqlTxt." $ss\n-----<hr>\n",false);
1078         }
1080         $qID = $zthis->_query($sql,$inputarr);
1082         /*
1083                 Alexios Fakios notes that ErrorMsg() must be called before ErrorNo() for mssql
1084                 because ErrorNo() calls Execute('SELECT @ERROR'), causing recursion
1085         */
1086         if ($zthis->databaseType == 'mssql') {
1087         // ErrorNo is a slow function call in mssql, and not reliable in PHP 4.0.6
1089                 if($emsg = $zthis->ErrorMsg()) {
1090                         if ($err = $zthis->ErrorNo()) {
1091                                 if ($zthis->debug === -99)
1092                                         ADOConnection::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<hr>\n",false);
1094                                 ADOConnection::outp($err.': '.$emsg);
1095                         }
1096                 }
1097         } else if (!$qID) {
1099                 if ($zthis->debug === -99)
1100                                 if ($inBrowser) ADOConnection::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<hr>\n",false);
1101                                 else ADOConnection::outp("-----<hr>\n($dbt): ".$sqlTxt."$ss\n-----<hr>\n",false);
1103                 ADOConnection::outp($zthis->ErrorNo() .': '. $zthis->ErrorMsg());
1104         }
1106         if ($zthis->debug === 99) _adodb_backtrace(true,9999,2);
1107         return $qID;
1110 # pretty print the debug_backtrace function
1111 function _adodb_backtrace($printOrArr=true,$levels=9999,$skippy=0,$ishtml=null)
1113         if (!function_exists('debug_backtrace')) return '';
1115         if ($ishtml === null) $html =  (isset($_SERVER['HTTP_USER_AGENT']));
1116         else $html = $ishtml;
1118         $fmt =  ($html) ? "</font><font color=#808080 size=-1> %% line %4d, file: <a href=\"file:/%s\">%s</a></font>" : "%% line %4d, file: %s";
1120         $MAXSTRLEN = 128;
1122         $s = ($html) ? '<pre align=left>' : '';
1124         if (is_array($printOrArr)) $traceArr = $printOrArr;
1125         else $traceArr = debug_backtrace();
1126         array_shift($traceArr);
1127         array_shift($traceArr);
1128         $tabs = sizeof($traceArr)-2;
1130         foreach ($traceArr as $arr) {
1131                 if ($skippy) {$skippy -= 1; continue;}
1132                 $levels -= 1;
1133                 if ($levels < 0) break;
1135                 $args = array();
1136                 for ($i=0; $i < $tabs; $i++) $s .=  ($html) ? ' &nbsp; ' : "\t";
1137                 $tabs -= 1;
1138                 if ($html) $s .= '<font face="Courier New,Courier">';
1139                 if (isset($arr['class'])) $s .= $arr['class'].'.';
1140                 if (isset($arr['args']))
1141                  foreach($arr['args'] as $v) {
1142                         if (is_null($v)) $args[] = 'null';
1143                         else if (is_array($v)) $args[] = 'Array['.sizeof($v).']';
1144                         else if (is_object($v)) $args[] = 'Object:'.get_class($v);
1145                         else if (is_bool($v)) $args[] = $v ? 'true' : 'false';
1146                         else {
1147                                 $v = (string) @$v;
1148                                 $str = htmlspecialchars(str_replace(array("\r","\n"),' ',substr($v,0,$MAXSTRLEN)));
1149                                 if (strlen($v) > $MAXSTRLEN) $str .= '...';
1150                                 $args[] = $str;
1151                         }
1152                 }
1153                 $s .= $arr['function'].'('.implode(', ',$args).')';
1156                 $s .= @sprintf($fmt, $arr['line'],$arr['file'],basename($arr['file']));
1158                 $s .= "\n";
1159         }
1160         if ($html) $s .= '</pre>';
1161         if ($printOrArr) print $s;
1163         return $s;
1165 /*
1166 function _adodb_find_from($sql)
1169         $sql = str_replace(array("\n","\r"), ' ', $sql);
1170         $charCount = strlen($sql);
1172         $inString = false;
1173         $quote = '';
1174         $parentheseCount = 0;
1175         $prevChars = '';
1176         $nextChars = '';
1179         for($i = 0; $i < $charCount; $i++) {
1181         $char = substr($sql,$i,1);
1182             $prevChars = substr($sql,0,$i);
1183         $nextChars = substr($sql,$i+1);
1185                 if((($char == "'" || $char == '"' || $char == '`') && substr($prevChars,-1,1) != '\\') && $inString === false) {
1186                         $quote = $char;
1187                         $inString = true;
1188                 }
1190                 elseif((($char == "'" || $char == '"' || $char == '`') && substr($prevChars,-1,1) != '\\') && $inString === true && $quote == $char) {
1191                         $quote = "";
1192                         $inString = false;
1193                 }
1195                 elseif($char == "(" && $inString === false)
1196                         $parentheseCount++;
1198                 elseif($char == ")" && $inString === false && $parentheseCount > 0)
1199                         $parentheseCount--;
1201                 elseif($parentheseCount <= 0 && $inString === false && $char == " " && strtoupper(substr($prevChars,-5,5)) == " FROM")
1202                         return $i;
1204         }
1206 */