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