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