[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3  @version   v5.20.1  06-Dec-2015
   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 __construct()
  94      {
  95      }
  96  
  97  	function RSRecordCount()
  98      {
  99          if (!empty($this->_rezarray)) return sizeof($this->_rezarray);
 100  
 101          return sizeof($this->_origarray);
 102      }
 103  
 104  	function _insertid()
 105      {
 106              return false;
 107      }
 108  
 109  	function _affectedrows()
 110      {
 111              return false;
 112      }
 113  
 114          // returns true or false
 115  	function PConnect(&$array, $types = false, $colnames = false)
 116      {
 117          return $this->Connect($array, $types, $colnames);
 118      }
 119          // returns true or false
 120  	function Connect(&$array, $types = false, $colnames = false)
 121      {
 122          if (is_string($array) and $array === 'iluvphplens') return 'me2';
 123  
 124          if (!$array) {
 125              $this->_origarray = false;
 126              return true;
 127          }
 128          $row = $array[0];
 129          $cols = sizeof($row);
 130  
 131  
 132          if ($colnames) $this->_colnames = $colnames;
 133          else {
 134              $this->_colnames = $array[0];
 135              $this->_skiprow1 = true;
 136          }
 137          if (!$types) {
 138          // probe and guess the type
 139              $types = array();
 140              $firstrow = true;
 141              if ($this->_proberows > sizeof($array)) $max = sizeof($array);
 142              else $max = $this->_proberows;
 143              for ($j=($this->_skiprow1)?1:0;$j < $max; $j++) {
 144                  $row = $array[$j];
 145                  if (!$row) break;
 146                  $i = -1;
 147                  foreach($row as $v) {
 148                      $i += 1;
 149                      //print " ($i ".$types[$i]. "$v) ";
 150                      $v = trim($v);
 151                       if (!preg_match('/^[+-]{0,1}[0-9\.]+$/',$v)) {
 152                          $types[$i] = 'C'; // once C, always C
 153                          continue;
 154                      }
 155                      if (isset($types[$i]) && $types[$i]=='C') continue;
 156                      if ($firstrow) {
 157                      // If empty string, we presume is character
 158                      // test for integer for 1st row only
 159                      // after that it is up to testing other rows to prove
 160                      // that it is not an integer
 161                          if (strlen($v) == 0) $types[0] = 'C';
 162                          if (strpos($v,'.') !== false) $types[0] = 'N';
 163                          else  $types[$i] = 'I';
 164                          continue;
 165                      }
 166  
 167                      if (strpos($v,'.') !== false) $types[$i] = 'N';
 168  
 169                  }
 170                  $firstrow = false;
 171              }
 172          }
 173          //print_r($types);
 174          $this->_origarray = $array;
 175          $this->_types = $types;
 176          return true;
 177      }
 178  
 179  
 180  
 181      // returns queryID or false
 182      // We presume that the select statement is on the same table (what else?),
 183      // with the only difference being the order by.
 184      //You can filter by using $eval and each clause is stored in $arr .eg. $arr[1] == 'name'
 185      // also supports SELECT [DISTINCT] COL FROM ... -- only 1 col supported
 186  	function _query($sql,$input_arr,$eval=false)
 187      {
 188          if ($this->_origarray === false) return false;
 189  
 190          $eval = $this->evalAll;
 191          $usql = strtoupper(trim($sql));
 192          $usql = preg_replace("/[\t\n\r]/",' ',$usql);
 193          $usql = preg_replace('/ *BY/i',' BY',strtoupper($usql));
 194  
 195          $eregword ='([A-Z_0-9]*)';
 196          //print "<BR> $sql $eval ";
 197          if ($eval) {
 198              $i = 0;
 199              foreach($this->_colnames as $n) {
 200                  $n = strtoupper(trim($n));
 201                  $eval = str_replace("\$$n","\$arr[$i]",$eval);
 202  
 203                  $i += 1;
 204              }
 205  
 206              $i = 0;
 207              $eval = "\$rez=($eval);";
 208              //print "<p>Eval string = $eval </p>";
 209              $where_arr = array();
 210  
 211              reset($this->_origarray);
 212              while (list($k_arr,$arr) = each($this->_origarray)) {
 213  
 214                  if ($i == 0 && $this->_skiprow1)
 215                      $where_arr[] = $arr;
 216                  else {
 217                      eval($eval);
 218                      //print " $i: result=$rez arr[0]={$arr[0]} arr[1]={$arr[1]} <BR>\n ";
 219                      if ($rez) $where_arr[] = $arr;
 220                  }
 221                  $i += 1;
 222              }
 223              $this->_rezarray = $where_arr;
 224          }else
 225              $where_arr = $this->_origarray;
 226  
 227          // THIS PROJECTION CODE ONLY WORKS FOR 1 COLUMN,
 228          // OTHERWISE IT RETURNS ALL COLUMNS
 229          if (substr($usql,0,7) == 'SELECT ') {
 230              $at = strpos($usql,' FROM ');
 231              $sel = trim(substr($usql,7,$at-7));
 232  
 233              $distinct = false;
 234              if (substr($sel,0,8) == 'DISTINCT') {
 235                  $distinct = true;
 236                  $sel = trim(substr($sel,8,$at));
 237              }
 238  
 239              // $sel holds the selection clause, comma delimited
 240              // currently we only project if one column is involved
 241              // this is to support popups in PHPLens
 242              if (strpos(',',$sel)===false) {
 243                  $colarr = array();
 244  
 245                  preg_match("/$eregword/",$sel,$colarr);
 246                  $col = $colarr[1];
 247                  $i = 0;
 248                  $n = '';
 249                  reset($this->_colnames);
 250                  while (list($k_n,$n) = each($this->_colnames)) {
 251  
 252                      if ($col == strtoupper(trim($n))) break;
 253                      $i += 1;
 254                  }
 255  
 256                  if ($n && $col) {
 257                      $distarr = array();
 258                      $projarray = array();
 259                      $projtypes = array($this->_types[$i]);
 260                      $projnames = array($n);
 261  
 262                      reset($where_arr);
 263                      while (list($k_a,$a) = each($where_arr)) {
 264                          if ($i == 0 && $this->_skiprow1) {
 265                              $projarray[] = array($n);
 266                              continue;
 267                          }
 268  
 269                          if ($distinct) {
 270                              $v = strtoupper($a[$i]);
 271                              if (! $distarr[$v]) {
 272                                  $projarray[] = array($a[$i]);
 273                                  $distarr[$v] = 1;
 274                              }
 275                          } else
 276                              $projarray[] = array($a[$i]);
 277  
 278                      } //foreach
 279                      //print_r($projarray);
 280                  }
 281              } // check 1 column in projection
 282          }  // is SELECT
 283  
 284          if (empty($projarray)) {
 285              $projtypes = $this->_types;
 286              $projarray = $where_arr;
 287              $projnames = $this->_colnames;
 288          }
 289          $this->_rezarray = $projarray;
 290          $this->_reztypes = $projtypes;
 291          $this->_reznames = $projnames;
 292  
 293  
 294          $pos = strpos($usql,' ORDER BY ');
 295          if ($pos === false) return $this;
 296          $orderby = trim(substr($usql,$pos+10));
 297  
 298          preg_match("/$eregword/",$orderby,$arr);
 299          if (sizeof($arr) < 2) return $this; // actually invalid sql
 300          $col = $arr[1];
 301          $at = (integer) $col;
 302          if ($at == 0) {
 303              $i = 0;
 304              reset($projnames);
 305              while (list($k_n,$n) = each($projnames)) {
 306                  if (strtoupper(trim($n)) == $col) {
 307                      $at = $i+1;
 308                      break;
 309                  }
 310                  $i += 1;
 311              }
 312          }
 313  
 314          if ($at <= 0 || $at > sizeof($projarray[0])) return $this; // cannot find sort column
 315          $at -= 1;
 316  
 317          // generate sort array consisting of (sortval1, row index1) (sortval2, row index2)...
 318          $sorta = array();
 319          $t = $projtypes[$at];
 320          $num = ($t == 'I' || $t == 'N');
 321          for ($i=($this->_skiprow1)?1:0, $max = sizeof($projarray); $i < $max; $i++) {
 322              $row = $projarray[$i];
 323              $val = ($num)?(float)$row[$at]:$row[$at];
 324              $sorta[]=array($val,$i);
 325          }
 326  
 327          // check for desc sort
 328          $orderby = substr($orderby,strlen($col)+1);
 329          $arr == array();
 330          preg_match('/([A-Z_0-9]*)/i',$orderby,$arr);
 331  
 332          if (trim($arr[1]) == 'DESC') $sortf = 'adodb_cmpr';
 333          else $sortf = 'adodb_cmp';
 334  
 335          // hasta la sorta babe
 336          usort($sorta, $sortf);
 337  
 338          // rearrange original array
 339          $arr2 = array();
 340          if ($this->_skiprow1) $arr2[] = $projarray[0];
 341          foreach($sorta as $v) {
 342              $arr2[] = $projarray[$v[1]];
 343          }
 344  
 345          $this->_rezarray = $arr2;
 346          return $this;
 347      }
 348  
 349      /*    Returns: the last error message from previous database operation    */
 350  	function ErrorMsg()
 351      {
 352              return '';
 353      }
 354  
 355      /*    Returns: the last error number from previous database operation    */
 356  	function ErrorNo()
 357      {
 358          return 0;
 359      }
 360  
 361      // returns true or false
 362  	function _close()
 363      {
 364      }
 365  
 366  
 367  }
 368  
 369  /*--------------------------------------------------------------------------------------
 370       Class Name: Recordset
 371  --------------------------------------------------------------------------------------*/
 372  
 373  
 374  class ADORecordSet_text extends ADORecordSet_array
 375  {
 376  
 377      var $databaseType = "text";
 378  
 379  	function __construct(&$conn,$mode=false)
 380      {
 381          parent::__construct();
 382          $this->InitArray($conn->_rezarray,$conn->_reztypes,$conn->_reznames);
 383          $conn->_rezarray = false;
 384      }
 385  
 386  } // class ADORecordSet_text
 387  
 388  
 389  } // defined