[Summary view]

Differences Between: [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403]

   1  <?php
   2  /*
   3  @version   v5.21.0  2021-02-27
   4  @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
   5  @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   6     Set tabs to 4.
   7  */
   8  
   9  /*
  10  Setup:
  11  
  12   	 $db = NewADOConnection('text');
  13   	 $db->Connect($array,[$types],[$colnames]);
  14  
  15  	 Parameter $array is the 2 dimensional array of data. The first row can contain the
  16  	 column names. If column names is not defined in first row, you MUST define $colnames,
  17  	 the 3rd parameter.
  18  
  19  	 Parameter $types is optional. If defined, it should contain an array matching
  20  	 the number of columns in $array, with each element matching the correct type defined
  21  	 by MetaType: (B,C,I,L,N). If undefined, we will probe for $this->_proberows rows
  22  	 to guess the type. Only C,I and N are recognised.
  23  
  24  	 Parameter $colnames is optional. If defined, it is an array that contains the
  25  	 column names of $array. If undefined, we assume the first row of $array holds the
  26  	 column names.
  27  
  28   The Execute() function will return a recordset. The recordset works like a normal recordset.
  29   We have partial support for SQL parsing. We process the SQL using the following rules:
  30  
  31   1. SQL order by's always work for the first column ordered. Subsequent cols are ignored
  32  
  33   2. All operations take place on the same table. No joins possible. In fact the FROM clause
  34  	 is ignored! You can use any name for the table.
  35  
  36   3. To simplify code, all columns are returned, except when selecting 1 column
  37  
  38   	 $rs = $db->Execute('select col1,col2 from table'); // sql ignored, will generate all cols
  39  
  40  	 We special case handling of 1 column because it is used in filter popups
  41  
  42  	 $rs = $db->Execute('select col1 from table');
  43  	 // sql accepted and processed -- any table name is accepted
  44  
  45  	 $rs = $db->Execute('select distinct col1 from table');
  46  	 // sql accepted and processed
  47  
  48  4. Where clauses are ignored, but searching with the 3rd parameter of Execute is permitted.
  49     This has to use PHP syntax and we will eval() it. You can even use PHP functions.
  50  
  51  	  $rs = $db->Execute('select * from table',false,"\$COL1='abc' and $\COL2=3")
  52   	 // the 3rd param is searched -- make sure that $COL1 is a legal column name
  53  	 // and all column names must be in upper case.
  54  
  55  4. Group by, having, other clauses are ignored
  56  
  57  5. Expression columns, min(), max() are ignored
  58  
  59  6. All data is readonly. Only SELECTs permitted.
  60  */
  61  
  62  // security - hide paths
  63  if (!defined('ADODB_DIR')) die();
  64  
  65  if (! defined("_ADODB_TEXT_LAYER")) {
  66   define("_ADODB_TEXT_LAYER", 1 );
  67  
  68  // for sorting in _query()
  69  function adodb_cmp($a, $b) {
  70  	 if ($a[0] == $b[0]) return 0;
  71  	 return ($a[0] < $b[0]) ? -1 : 1;
  72  }
  73  // for sorting in _query()
  74  function adodb_cmpr($a, $b) {
  75  	 if ($a[0] == $b[0]) return 0;
  76  	 return ($a[0] > $b[0]) ? -1 : 1;
  77  }
  78  class ADODB_text extends ADOConnection {
  79  	 var $databaseType = 'text';
  80  
  81  	 var $_origarray; // original data
  82  	 var $_types;
  83  	 var $_proberows = 8;
  84  	 var $_colnames;
  85  	 var $_skiprow1=false;
  86  	 var $readOnly = true;
  87  	 var $hasTransactions = false;
  88  
  89  	 var $_rezarray;
  90  	 var $_reznames;
  91  	 var $_reztypes;
  92  
  93  	function RSRecordCount()
  94  	 {
  95  	 	 if (!empty($this->_rezarray)) return sizeof($this->_rezarray);
  96  
  97  	 	 return sizeof($this->_origarray);
  98  	 }
  99  
 100  	function _insertid()
 101  	 {
 102  	 	 	 return false;
 103  	 }
 104  
 105  	function _affectedrows()
 106  	 {
 107  	 	 	 return false;
 108  	 }
 109  
 110  	 	 // returns true or false
 111  	function PConnect(&$array, $types = false, $colnames = false)
 112  	 {
 113  	 	 return $this->Connect($array, $types, $colnames);
 114  	 }
 115  	 	 // returns true or false
 116  	function Connect(&$array, $types = false, $colnames = false)
 117  	 {
 118  	 	 if (is_string($array) and $array === 'iluvphplens') return 'me2';
 119  
 120  	 	 if (!$array) {
 121  	 	 	 $this->_origarray = false;
 122  	 	 	 return true;
 123  	 	 }
 124  	 	 $row = $array[0];
 125  	 	 $cols = sizeof($row);
 126  
 127  
 128  	 	 if ($colnames) $this->_colnames = $colnames;
 129  	 	 else {
 130  	 	 	 $this->_colnames = $array[0];
 131  	 	 	 $this->_skiprow1 = true;
 132  	 	 }
 133  	 	 if (!$types) {
 134  	 	 // probe and guess the type
 135  	 	 	 $types = array();
 136  	 	 	 $firstrow = true;
 137  	 	 	 if ($this->_proberows > sizeof($array)) $max = sizeof($array);
 138  	 	 	 else $max = $this->_proberows;
 139  	 	 	 for ($j=($this->_skiprow1)?1:0;$j < $max; $j++) {
 140  	 	 	 	 $row = $array[$j];
 141  	 	 	 	 if (!$row) break;
 142  	 	 	 	 $i = -1;
 143  	 	 	 	 foreach($row as $v) {
 144  	 	 	 	 	 $i += 1;
 145  	 	 	 	 	 //print " ($i ".$types[$i]. "$v) ";
 146  	 	 	 	 	 $v = trim($v);
 147  	  	 	 	 	 if (!preg_match('/^[+-]{0,1}[0-9\.]+$/',$v)) {
 148  	 	 	 	 	 	 $types[$i] = 'C'; // once C, always C
 149  	 	 	 	 	 	 continue;
 150  	 	 	 	 	 }
 151  	 	 	 	 	 if (isset($types[$i]) && $types[$i]=='C') continue;
 152  	 	 	 	 	 if ($firstrow) {
 153  	 	 	 	 	 // If empty string, we presume is character
 154  	 	 	 	 	 // test for integer for 1st row only
 155  	 	 	 	 	 // after that it is up to testing other rows to prove
 156  	 	 	 	 	 // that it is not an integer
 157  	 	 	 	 	 	 if (strlen($v) == 0) $types[0] = 'C';
 158  	 	 	 	 	 	 if (strpos($v,'.') !== false) $types[0] = 'N';
 159  	 	 	 	 	 	 else  $types[$i] = 'I';
 160  	 	 	 	 	 	 continue;
 161  	 	 	 	 	 }
 162  
 163  	 	 	 	 	 if (strpos($v,'.') !== false) $types[$i] = 'N';
 164  
 165  	 	 	 	 }
 166  	 	 	 	 $firstrow = false;
 167  	 	 	 }
 168  	 	 }
 169  	 	 //print_r($types);
 170  	 	 $this->_origarray = $array;
 171  	 	 $this->_types = $types;
 172  	 	 return true;
 173  	 }
 174  
 175  
 176  
 177  	 // returns queryID or false
 178  	 // We presume that the select statement is on the same table (what else?),
 179  	 // with the only difference being the order by.
 180  	 //You can filter by using $eval and each clause is stored in $arr .eg. $arr[1] == 'name'
 181  	 // also supports SELECT [DISTINCT] COL FROM ... -- only 1 col supported
 182  	function _query($sql,$input_arr,$eval=false)
 183  	 {
 184  	 	 if ($this->_origarray === false) return false;
 185  
 186  	 	 $eval = $this->evalAll;
 187  	 	 $usql = strtoupper(trim($sql));
 188  	 	 $usql = preg_replace("/[\t\n\r]/",' ',$usql);
 189  	 	 $usql = preg_replace('/ *BY/i',' BY',strtoupper($usql));
 190  
 191  	 	 $eregword ='([A-Z_0-9]*)';
 192  	 	 //print "<BR> $sql $eval ";
 193  	 	 if ($eval) {
 194  	 	 	 $i = 0;
 195  	 	 	 foreach($this->_colnames as $n) {
 196  	 	 	 	 $n = strtoupper(trim($n));
 197  	 	 	 	 $eval = str_replace("\$$n","\$arr[$i]",$eval);
 198  
 199  	 	 	 	 $i += 1;
 200  	 	 	 }
 201  
 202  	 	 	 $i = 0;
 203  	 	 	 $eval = "\$rez=($eval);";
 204  	 	 	 //print "<p>Eval string = $eval </p>";
 205  	 	 	 $where_arr = array();
 206  
 207  	 	 	 reset($this->_origarray);
 208  	 	 	 foreach ($this->_origarray as $arr) {
 209  
 210  	 	 	 	 if ($i == 0 && $this->_skiprow1)
 211  	 	 	 	 	 $where_arr[] = $arr;
 212  	 	 	 	 else {
 213  	 	 	 	 	 eval($eval);
 214  	 	 	 	 	 //print " $i: result=$rez arr[0]={$arr[0]} arr[1]={$arr[1]} <BR>\n ";
 215  	 	 	 	 	 if ($rez) $where_arr[] = $arr;
 216  	 	 	 	 }
 217  	 	 	 	 $i += 1;
 218  	 	 	 }
 219  	 	 	 $this->_rezarray = $where_arr;
 220  	 	 }else
 221  	 	 	 $where_arr = $this->_origarray;
 222  
 223  	 	 // THIS PROJECTION CODE ONLY WORKS FOR 1 COLUMN,
 224  	 	 // OTHERWISE IT RETURNS ALL COLUMNS
 225  	 	 if (substr($usql,0,7) == 'SELECT ') {
 226  	 	 	 $at = strpos($usql,' FROM ');
 227  	 	 	 $sel = trim(substr($usql,7,$at-7));
 228  
 229  	 	 	 $distinct = false;
 230  	 	 	 if (substr($sel,0,8) == 'DISTINCT') {
 231  	 	 	 	 $distinct = true;
 232  	 	 	 	 $sel = trim(substr($sel,8,$at));
 233  	 	 	 }
 234  
 235  	 	 	 // $sel holds the selection clause, comma delimited
 236  	 	 	 // currently we only project if one column is involved
 237  	 	 	 // this is to support popups in PHPLens
 238  	 	 	 if (strpos(',',$sel)===false) {
 239  	 	 	 	 $colarr = array();
 240  
 241  	 	 	 	 preg_match("/$eregword/",$sel,$colarr);
 242  	 	 	 	 $col = $colarr[1];
 243  	 	 	 	 $i = 0;
 244  	 	 	 	 $n = '';
 245  	 	 	 	 reset($this->_colnames);
 246  	 	 	 	 foreach ($this->_colnames as $n) {
 247  
 248  	 	 	 	 	 if ($col == strtoupper(trim($n))) break;
 249  	 	 	 	 	 $i += 1;
 250  	 	 	 	 }
 251  
 252  	 	 	 	 if ($n && $col) {
 253  	 	 	 	 	 $distarr = array();
 254  	 	 	 	 	 $projarray = array();
 255  	 	 	 	 	 $projtypes = array($this->_types[$i]);
 256  	 	 	 	 	 $projnames = array($n);
 257  
 258  	 	 	 	 	 foreach ($where_arr as $a) {
 259  	 	 	 	 	 	 if ($i == 0 && $this->_skiprow1) {
 260  	 	 	 	 	 	 	 $projarray[] = array($n);
 261  	 	 	 	 	 	 	 continue;
 262  	 	 	 	 	 	 }
 263  
 264  	 	 	 	 	 	 if ($distinct) {
 265  	 	 	 	 	 	 	 $v = strtoupper($a[$i]);
 266  	 	 	 	 	 	 	 if (! $distarr[$v]) {
 267  	 	 	 	 	 	 	 	 $projarray[] = array($a[$i]);
 268  	 	 	 	 	 	 	 	 $distarr[$v] = 1;
 269  	 	 	 	 	 	 	 }
 270  	 	 	 	 	 	 } else
 271  	 	 	 	 	 	 	 $projarray[] = array($a[$i]);
 272  
 273  	 	 	 	 	 } //foreach
 274  	 	 	 	 	 //print_r($projarray);
 275  	 	 	 	 }
 276  	 	 	 } // check 1 column in projection
 277  	 	 }  // is SELECT
 278  
 279  	 	 if (empty($projarray)) {
 280  	 	 	 $projtypes = $this->_types;
 281  	 	 	 $projarray = $where_arr;
 282  	 	 	 $projnames = $this->_colnames;
 283  	 	 }
 284  	 	 $this->_rezarray = $projarray;
 285  	 	 $this->_reztypes = $projtypes;
 286  	 	 $this->_reznames = $projnames;
 287  
 288  
 289  	 	 $pos = strpos($usql,' ORDER BY ');
 290  	 	 if ($pos === false) return $this;
 291  	 	 $orderby = trim(substr($usql,$pos+10));
 292  
 293  	 	 preg_match("/$eregword/",$orderby,$arr);
 294  	 	 if (sizeof($arr) < 2) return $this; // actually invalid sql
 295  	 	 $col = $arr[1];
 296  	 	 $at = (integer) $col;
 297  	 	 if ($at == 0) {
 298  	 	 	 $i = 0;
 299  	 	 	 reset($projnames);
 300  	 	 	 foreach ($projnames as $n) {
 301  	 	 	 	 if (strtoupper(trim($n)) == $col) {
 302  	 	 	 	 	 $at = $i+1;
 303  	 	 	 	 	 break;
 304  	 	 	 	 }
 305  	 	 	 	 $i += 1;
 306  	 	 	 }
 307  	 	 }
 308  
 309  	 	 if ($at <= 0 || $at > sizeof($projarray[0])) return $this; // cannot find sort column
 310  	 	 $at -= 1;
 311  
 312  	 	 // generate sort array consisting of (sortval1, row index1) (sortval2, row index2)...
 313  	 	 $sorta = array();
 314  	 	 $t = $projtypes[$at];
 315  	 	 $num = ($t == 'I' || $t == 'N');
 316  	 	 for ($i=($this->_skiprow1)?1:0, $max = sizeof($projarray); $i < $max; $i++) {
 317  	 	 	 $row = $projarray[$i];
 318  	 	 	 $val = ($num)?(float)$row[$at]:$row[$at];
 319  	 	 	 $sorta[]=array($val,$i);
 320  	 	 }
 321  
 322  	 	 // check for desc sort
 323  	 	 $orderby = substr($orderby,strlen($col)+1);
 324  	 	 $arr = array();
 325  	 	 preg_match('/([A-Z_0-9]*)/i',$orderby,$arr);
 326  
 327  	 	 if (trim($arr[1]) == 'DESC') $sortf = 'adodb_cmpr';
 328  	 	 else $sortf = 'adodb_cmp';
 329  
 330  	 	 // hasta la sorta babe
 331  	 	 usort($sorta, $sortf);
 332  
 333  	 	 // rearrange original array
 334  	 	 $arr2 = array();
 335  	 	 if ($this->_skiprow1) $arr2[] = $projarray[0];
 336  	 	 foreach($sorta as $v) {
 337  	 	 	 $arr2[] = $projarray[$v[1]];
 338  	 	 }
 339  
 340  	 	 $this->_rezarray = $arr2;
 341  	 	 return $this;
 342  	 }
 343  
 344  	 /*	 Returns: the last error message from previous database operation	 */
 345  	function ErrorMsg()
 346  	 {
 347  	 	 	 return '';
 348  	 }
 349  
 350  	 /*	 Returns: the last error number from previous database operation	 */
 351  	function ErrorNo()
 352  	 {
 353  	 	 return 0;
 354  	 }
 355  
 356  	 // returns true or false
 357  	function _close()
 358  	 {
 359  	 }
 360  
 361  
 362  }
 363  
 364  /*--------------------------------------------------------------------------------------
 365  	  Class Name: Recordset
 366  --------------------------------------------------------------------------------------*/
 367  
 368  
 369  class ADORecordSet_text extends ADORecordSet_array
 370  {
 371  
 372  	 var $databaseType = "text";
 373  
 374  	function __construct( $conn,$mode=false)
 375  	 {
 376  	 	 parent::__construct();
 377  	 	 $this->InitArray($conn->_rezarray,$conn->_reztypes,$conn->_reznames);
 378  	 	 $conn->_rezarray = false;
 379  	 }
 380  
 381  } // class ADORecordSet_text
 382  
 383  
 384  } // defined