gacl/adodb/adodb-active-record.inc.php

   1 <?php
   2 /*
   3
   4 @version V4.92a 29 Aug 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
   5   Latest version is available at http://adodb.sourceforge.net
   6
   7   Released under both BSD license and Lesser GPL library license.
   8   Whenever there is any discrepancy between the two licenses,
   9   the BSD license will take precedence.
  10
  11   Active Record implementation. Superset of Zend Framework's.
  12
  13   Version 0.04
  14
  15   See http://www-128.ibm.com/developerworks/java/library/j-cb03076/?ca=dgr-lnxw01ActiveRecord
  16         for info on Ruby on Rails Active Record implementation
  17 */
  18
  19 global $_ADODB_ACTIVE_DBS;
  20 global $ADODB_ACTIVE_CACHESECS; // set to true to enable caching of metadata such as field info
  21
  22 // array of ADODB_Active_DB's, indexed by ADODB_Active_Record->_dbat
  23 $_ADODB_ACTIVE_DBS = array();
  24
  25
  26 class ADODB_Active_DB {
  27         var $db; // ADOConnection
  28         var $tables; // assoc array of ADODB_Active_Table objects, indexed by tablename
  29 }
  30
  31 class ADODB_Active_Table {
  32         var $name; // table name
  33         var $flds; // assoc array of adofieldobjs, indexed by fieldname
  34         var $keys; // assoc array of primary keys, indexed by fieldname
  35         var $_created; // only used when stored as a cached file
  36 }
  37
  38 // returns index into $_ADODB_ACTIVE_DBS
  39 function ADODB_SetDatabaseAdapter(&$db)
  40 {
  41         global $_ADODB_ACTIVE_DBS;
  42
  43                 foreach($_ADODB_ACTIVE_DBS as $k => $d) {
  44                         if ($d->db == $db) return $k;
  45                 }
  46
  47                 $obj = new ADODB_Active_DB();
  48                 $obj->db =& $db;
  49                 $obj->tables = array();
  50
  51                 $_ADODB_ACTIVE_DBS[] = $obj;
  52
  53                 return sizeof($_ADODB_ACTIVE_DBS)-1;
  54 }
  55
  56
  57 class ADODB_Active_Record {
  58         var $_dbat; // associative index pointing to ADODB_Active_DB eg. $ADODB_Active_DBS[_dbat]
  59         var $_table; // tablename, if set in class definition then use it as table name
  60         var $_tableat; // associative index pointing to ADODB_Active_Table, eg $ADODB_Active_DBS[_dbat]->tables[$this->_tableat]
  61         var $_where; // where clause set in Load()
  62         var $_saved = false; // indicates whether data is already inserted.
  63         var $_lasterr = false; // last error message
  64         var $_original = false; // the original values loaded or inserted, refreshed on update
  65
  66         // should be static
  67         function SetDatabaseAdapter(&$db)
  68         {
  69                 return ADODB_SetDatabaseAdapter($db);
  70         }
  71
  72         // php4 constructor
  73         function ADODB_Active_Record($table = false, $pkeyarr=false, $db=false)
  74         {
  75                 ADODB_Active_Record::__construct($table,$pkeyarr,$db);
  76         }
  77
  78         // php5 constructor
  79         function __construct($table = false, $pkeyarr=false, $db=false)
  80         {
  81         global $ADODB_ASSOC_CASE,$_ADODB_ACTIVE_DBS;
  82
  83                 if ($db == false && is_object($pkeyarr)) {
  84                         $db = $pkeyarr;
  85                         $pkeyarr = false;
  86                 }
  87
  88                 if (!$table) {
  89                         if (!empty($this->_table)) $table = $this->_table;
  90                         else $table = $this->_pluralize(get_class($this));
  91                 }
  92                 if ($db) {
  93                         $this->_dbat = ADODB_Active_Record::SetDatabaseAdapter($db);
  94                 } else
  95                         $this->_dbat = sizeof($_ADODB_ACTIVE_DBS)-1;
  96
  97
  98                 if ($this->_dbat < 0) $this->Error("No database connection set; use ADOdb_Active_Record::SetDatabaseAdapter(\$db)",'ADODB_Active_Record::__constructor');
  99
 100                 $this->_table = $table;
 101                 $this->_tableat = $table; # reserved for setting the assoc value to a non-table name, eg. the sql string in future
 102                 $this->UpdateActiveTable($pkeyarr);
 103         }
 104
 105         function __wakeup()
 106         {
 107                 $class = get_class($this);
 108                 new $class;
 109         }
 110
 111         function _pluralize($table)
 112         {
 113                 $ut = strtoupper($table);
 114                 $len = strlen($table);
 115                 $lastc = $ut[$len-1];
 116                 $lastc2 = substr($ut,$len-2);
 117                 switch ($lastc) {
 118                 case 'S':
 119                         return $table.'es';
 120                 case 'Y':
 121                         return substr($table,0,$len-1).'ies';
 122                 case 'X':
 123                         return $table.'es';
 124                 case 'H':
 125                         if ($lastc2 == 'CH' || $lastc2 == 'SH')
 126                                 return $table.'es';
 127                 default:
 128                         return $table.'s';
 129                 }
 130         }
 131
 132         //////////////////////////////////
 133
 134         // update metadata
 135         function UpdateActiveTable($pkeys=false,$forceUpdate=false)
 136         {
 137         global $ADODB_ASSOC_CASE,$_ADODB_ACTIVE_DBS , $ADODB_CACHE_DIR, $ADODB_ACTIVE_CACHESECS;
 138
 139                 $activedb =& $_ADODB_ACTIVE_DBS[$this->_dbat];
 140
 141                 $table = $this->_table;
 142                 $tables = $activedb->tables;
 143                 $tableat = $this->_tableat;
 144                 if (!$forceUpdate && !empty($tables[$tableat])) {
 145                         $tobj =& $tables[$tableat];
 146                         foreach($tobj->flds as $name => $fld)
 147                                 $this->$name = null;
 148                         return;
 149                 }
 150
 151                 $db =& $activedb->db;
 152                 $fname = $ADODB_CACHE_DIR . '/adodb_' . $db->databaseType . '_active_'. $table . '.cache';
 153                 if (!$forceUpdate && $ADODB_ACTIVE_CACHESECS && $ADODB_CACHE_DIR && file_exists($fname)) {
 154                         $fp = fopen($fname,'r');
 155                         @flock($fp, LOCK_SH);
 156                         $acttab = unserialize(fread($fp,100000));
 157                         fclose($fp);
 158                         if ($acttab->_created + $ADODB_ACTIVE_CACHESECS - (abs(rand()) % 16) > time()) {
 159                                 // abs(rand()) randomizes deletion, reducing contention to delete/refresh file
 160                                 // ideally, you should cache at least 32 secs
 161                                 $activedb->tables[$table] = $acttab;
 162
 163                                 //if ($db->debug) ADOConnection::outp("Reading cached active record file: $fname");
 164                                 return;
 165                         } else if ($db->debug) {
 166                                 ADOConnection::outp("Refreshing cached active record file: $fname");
 167                         }
 168                 }
 169                 $activetab = new ADODB_Active_Table();
 170                 $activetab->name = $table;
 171
 172
 173                 $cols = $db->MetaColumns($table);
 174                 if (!$cols) {
 175                         $this->Error("Invalid table name: $table",'UpdateActiveTable');
 176                         return false;
 177                 }
 178                 $fld = reset($cols);
 179                 if (!$pkeys) {
 180                         if (isset($fld->primary_key)) {
 181                                 $pkeys = array();
 182                                 foreach($cols as $name => $fld) {
 183                                         if (!empty($fld->primary_key)) $pkeys[] = $name;
 184                                 }
 185                         } else
 186                                 $pkeys = $this->GetPrimaryKeys($db, $table);
 187                 }
 188                 if (empty($pkeys)) {
 189                         $this->Error("No primary key found for table $table",'UpdateActiveTable');
 190                         return false;
 191                 }
 192
 193                 $attr = array();
 194                 $keys = array();
 195
 196                 switch($ADODB_ASSOC_CASE) {
 197                 case 0:
 198                         foreach($cols as $name => $fldobj) {
 199                                 $name = strtolower($name);
 200                                 $this->$name = null;
 201                                 $attr[$name] = $fldobj;
 202                         }
 203                         foreach($pkeys as $k => $name) {
 204                                 $keys[strtolower($name)] = strtolower($name);
 205                         }
 206                         break;
 207
 208                 case 1:
 209                         foreach($cols as $name => $fldobj) {
 210                                 $name = strtoupper($name);
 211                                 $this->$name = null;
 212                                 $attr[$name] = $fldobj;
 213                         }
 214
 215                         foreach($pkeys as $k => $name) {
 216                                 $keys[strtoupper($name)] = strtoupper($name);
 217                         }
 218                         break;
 219                 default:
 220                         foreach($cols as $name => $fldobj) {
 221                                 $name = ($name);
 222                                 $this->$name = null;
 223                                 $attr[$name] = $fldobj;
 224                         }
 225                         foreach($pkeys as $k => $name) {
 226                                 $keys[$name] = ($name);
 227                         }
 228                         break;
 229                 }
 230
 231                 $activetab->keys = $keys;
 232                 $activetab->flds = $attr;
 233
 234                 if ($ADODB_ACTIVE_CACHESECS && $ADODB_CACHE_DIR) {
 235                         $activetab->_created = time();
 236                         $s = serialize($activetab);
 237                         if (!function_exists('adodb_write_file')) include(ADODB_DIR.'/adodb-csvlib.inc.php');
 238                         adodb_write_file($fname,$s);
 239                 }
 240                 $activedb->tables[$table] = $activetab;
 241         }
 242
 243         function GetPrimaryKeys(&$db, $table)
 244         {
 245                 return $db->MetaPrimaryKeys($table);
 246         }
 247
 248         // error handler for both PHP4+5.
 249         function Error($err,$fn)
 250         {
 251         global $_ADODB_ACTIVE_DBS;
 252
 253                 $fn = get_class($this).'::'.$fn;
 254                 $this->_lasterr = $fn.': '.$err;
 255
 256                 if ($this->_dbat < 0) $db = false;
 257                 else {
 258                         $activedb = $_ADODB_ACTIVE_DBS[$this->_dbat];
 259                         $db =& $activedb->db;
 260                 }
 261
 262                 if (function_exists('adodb_throw')) {
 263                         if (!$db) adodb_throw('ADOdb_Active_Record', $fn, -1, $err, 0, 0, false);
 264                         else adodb_throw($db->databaseType, $fn, -1, $err, 0, 0, $db);
 265                 } else
 266                         if (!$db || $db->debug) ADOConnection::outp($this->_lasterr);
 267
 268         }
 269
 270         // return last error message
 271         function ErrorMsg()
 272         {
 273                 if (!function_exists('adodb_throw')) {
 274                         if ($this->_dbat < 0) $db = false;
 275                         else $db = $this->DB();
 276
 277                         // last error could be database error too
 278                         if ($db && $db->ErrorMsg()) return $db->ErrorMsg();
 279                 }
 280                 return $this->_lasterr;
 281         }
 282
 283         // retrieve ADOConnection from _ADODB_Active_DBs
 284         function &DB()
 285         {
 286         global $_ADODB_ACTIVE_DBS;
 287
 288                 if ($this->_dbat < 0) {
 289                         $false = false;
 290                         $this->Error("No database connection set: use ADOdb_Active_Record::SetDatabaseAdaptor(\$db)", "DB");
 291                         return $false;
 292                 }
 293                 $activedb = $_ADODB_ACTIVE_DBS[$this->_dbat];
 294                 $db =& $activedb->db;
 295                 return $db;
 296         }
 297
 298         // retrieve ADODB_Active_Table
 299         function &TableInfo()
 300         {
 301         global $_ADODB_ACTIVE_DBS;
 302
 303                 $activedb = $_ADODB_ACTIVE_DBS[$this->_dbat];
 304                 $table =& $activedb->tables[$this->_tableat];
 305                 return $table;
 306         }
 307
 308         // set a numeric array (using natural table field ordering) as object properties
 309         function Set(&$row)
 310         {
 311                 $db =& $this->DB();
 312
 313                 if (!$row) {
 314                         $this->_saved = false;
 315                         return false;
 316                 }
 317
 318                 $this->_saved = true;
 319
 320                 $table =& $this->TableInfo();
 321                 if (sizeof($table->flds) != sizeof($row)) {
 322                         $this->Error("Table structure of $this->_table has changed","Load");
 323                         return false;
 324                 }
 325
 326                 $cnt = 0;
 327                 foreach($table->flds as $name=>$fld) {
 328                         $this->$name = $row[$cnt];
 329                         $cnt += 1;
 330                 }
 331                 $this->_original = $row;
 332                 return true;
 333         }
 334
 335         // get last inserted id for INSERT
 336         function LastInsertID(&$db,$fieldname)
 337         {
 338                 if ($db->hasInsertID)
 339                         $val = $db->Insert_ID($this->_table,$fieldname);
 340                 else
 341                         $val = false;
 342
 343                 if (is_null($val) || $val === false) {
 344                         // this might not work reliably in multi-user environment
 345                         return $db->GetOne("select max(".$fieldname.") from ".$this->_table);
 346                 }
 347                 return $val;
 348         }
 349
 350         // quote data in where clause
 351         function doquote(&$db, $val,$t)
 352         {
 353                 switch($t) {
 354                 case 'D':
 355                 case 'T':
 356                         if (empty($val)) return 'null';
 357
 358                 case 'C':
 359                 case 'X':
 360                         if (is_null($val)) return 'null';
 361
 362                         if (strncmp($val,"'",1) != 0 && substr($val,strlen($val)-1,1) != "'") {
 363                                 return $db->qstr($val);
 364                                 break;
 365                         }
 366                 default:
 367                         return $val;
 368                         break;
 369                 }
 370         }
 371
 372         // generate where clause for an UPDATE/SELECT
 373         function GenWhere(&$db, &$table)
 374         {
 375                 $keys = $table->keys;
 376                 $parr = array();
 377
 378                 foreach($keys as $k) {
 379                         $f = $table->flds[$k];
 380                         if ($f) {
 381                                 $parr[] = $k.' = '.$this->doquote($db,$this->$k,$db->MetaType($f->type));
 382                         }
 383                 }
 384                 return implode(' and ', $parr);
 385         }
 386
 387
 388         //------------------------------------------------------------ Public functions below
 389
 390         function Load($where,$bindarr=false)
 391         {
 392                 $db =& $this->DB(); if (!$db) return false;
 393                 $this->_where = $where;
 394
 395                 $save = $db->SetFetchMode(ADODB_FETCH_NUM);
 396                 $row = $db->GetRow("select * from ".$this->_table.' WHERE '.$where,$bindarr);
 397                 $db->SetFetchMode($save);
 398
 399                 return $this->Set($row);
 400         }
 401
 402         // false on error
 403         function Save()
 404         {
 405                 if ($this->_saved) $ok = $this->Update();
 406                 else $ok = $this->Insert();
 407
 408                 return $ok;
 409         }
 410
 411         // false on error
 412         function Insert()
 413         {
 414                 $db =& $this->DB(); if (!$db) return false;
 415                 $cnt = 0;
 416                 $table =& $this->TableInfo();
 417
 418                 $valarr = array();
 419                 $names = array();
 420                 $valstr = array();
 421
 422                 foreach($table->flds as $name=>$fld) {
 423                         $val = $this->$name;
 424                         if(!is_null($val) || !array_key_exists($name, $table->keys)) {
 425                                 $valarr[] = $val;
 426                                 $names[] = $name;
 427                                 $valstr[] = $db->Param($cnt);
 428                                 $cnt += 1;
 429                         }
 430                 }
 431
 432                 if (empty($names)){
 433                         foreach($table->flds as $name=>$fld) {
 434                                 $valarr[] = null;
 435                                 $names[] = $name;
 436                                 $valstr[] = $db->Param($cnt);
 437                                 $cnt += 1;
 438                         }
 439                 }
 440                 $sql = 'INSERT INTO '.$this->_table."(".implode(',',$names).') VALUES ('.implode(',',$valstr).')';
 441                 $ok = $db->Execute($sql,$valarr);
 442
 443                 if ($ok) {
 444                         $this->_saved = true;
 445                         $autoinc = false;
 446                         foreach($table->keys as $k) {
 447                                 if (is_null($this->$k)) {
 448                                         $autoinc = true;
 449                                         break;
 450                                 }
 451                         }
 452                         if ($autoinc && sizeof($table->keys) == 1) {
 453                                 $k = reset($table->keys);
 454                                 $this->$k = $this->LastInsertID($db,$k);
 455                         }
 456                 }
 457
 458                 $this->_original = $valarr;
 459                 return !empty($ok);
 460         }
 461
 462         function Delete()
 463         {
 464                 $db =& $this->DB(); if (!$db) return false;
 465                 $table =& $this->TableInfo();
 466
 467                 $where = $this->GenWhere($db,$table);
 468                 $sql = 'DELETE FROM '.$this->_table.' WHERE '.$where;
 469                 $ok = $db->Execute($sql);
 470
 471                 return $ok ? true : false;
 472         }
 473
 474         // returns an array of active record objects
 475         function &Find($whereOrderBy,$bindarr=false,$pkeysArr=false)
 476         {
 477                 $db =& $this->DB(); if (!$db || empty($this->_table)) return false;
 478                 $arr =& $db->GetActiveRecordsClass(get_class($this),$this->_table, $whereOrderBy,$bindarr,$pkeysArr);
 479                 return $arr;
 480         }
 481
 482         // returns 0 on error, 1 on update, 2 on insert
 483         function Replace()
 484         {
 485         global $ADODB_ASSOC_CASE;
 486
 487                 $db =& $this->DB(); if (!$db) return false;
 488                 $table =& $this->TableInfo();
 489
 490                 $pkey = $table->keys;
 491
 492                 foreach($table->flds as $name=>$fld) {
 493                         $val = $this->$name;
 494                         /*
 495                         if (is_null($val)) {
 496                                 if (isset($fld->not_null) && $fld->not_null) {
 497                                         if (isset($fld->default_value) && strlen($fld->default_value)) continue;
 498                                         else {
 499                                                 $this->Error("Cannot update null into $name","Replace");
 500                                                 return false;
 501                                         }
 502                                 }
 503                         }*/
 504                         if (is_null($val) && !empty($fld->auto_increment)) {
 505                 continue;
 506             }
 507                         $t = $db->MetaType($fld->type);
 508                         $arr[$name] = $this->doquote($db,$val,$t);
 509                         $valarr[] = $val;
 510                 }
 511
 512                 if (!is_array($pkey)) $pkey = array($pkey);
 513
 514
 515                 if ($ADODB_ASSOC_CASE == 0)
 516                         foreach($pkey as $k => $v)
 517                                 $pkey[$k] = strtolower($v);
 518                 elseif ($ADODB_ASSOC_CASE == 0)
 519                         foreach($pkey as $k => $v)
 520                                 $pkey[$k] = strtoupper($v);
 521
 522                 $ok = $db->Replace($this->_table,$arr,$pkey);
 523                 if ($ok) {
 524                         $this->_saved = true; // 1= update 2=insert
 525                         if ($ok == 2) {
 526                                 $autoinc = false;
 527                                 foreach($table->keys as $k) {
 528                                         if (is_null($this->$k)) {
 529                                                 $autoinc = true;
 530                                                 break;
 531                                         }
 532                                 }
 533                                 if ($autoinc && sizeof($table->keys) == 1) {
 534                                         $k = reset($table->keys);
 535                                         $this->$k = $this->LastInsertID($db,$k);
 536                                 }
 537                         }
 538
 539                         $this->_original =& $valarr;
 540                 }
 541                 return $ok;
 542         }
 543
 544         // returns 0 on error, 1 on update, -1 if no change in data (no update)
 545         function Update()
 546         {
 547                 $db =& $this->DB(); if (!$db) return false;
 548                 $table =& $this->TableInfo();
 549
 550                 $where = $this->GenWhere($db, $table);
 551
 552                 if (!$where) {
 553                         $this->error("Where missing for table $table", "Update");
 554                         return false;
 555                 }
 556                 $valarr = array();
 557                 $neworig = array();
 558                 $pairs = array();
 559                 $i = -1;
 560                 $cnt = 0;
 561                 foreach($table->flds as $name=>$fld) {
 562                         $i += 1;
 563                         $val = $this->$name;
 564                         $neworig[] = $val;
 565
 566                         if (isset($table->keys[$name])) {
 567                                 continue;
 568                         }
 569
 570                         if (is_null($val)) {
 571                                 if (isset($fld->not_null) && $fld->not_null) {
 572                                         if (isset($fld->default_value) && strlen($fld->default_value)) continue;
 573                                         else {
 574                                                 $this->Error("Cannot set field $name to NULL","Update");
 575                                                 return false;
 576                                         }
 577                                 }
 578                         }
 579
 580                         if (isset($this->_original[$i]) && $val == $this->_original[$i]) {
 581                                 continue;
 582                         }
 583                         $valarr[] = $val;
 584                         $pairs[] = $name.'='.$db->Param($cnt);
 585                         $cnt += 1;
 586                 }
 587
 588
 589                 if (!$cnt) return -1;
 590                 $sql = 'UPDATE '.$this->_table." SET ".implode(",",$pairs)." WHERE ".$where;
 591                 $ok = $db->Execute($sql,$valarr);
 592                 if ($ok) {
 593                         $this->_original =& $neworig;
 594                         return 1;
 595                 }
 596                 return 0;
 597         }
 598
 599         function GetAttributeNames()
 600         {
 601                 $table =& $this->TableInfo();
 602                 if (!$table) return false;
 603                 return array_keys($table->flds);
 604         }
 605
 606 };
 607
 608 ?>