1: <?php
   2: 
   3: /**
   4:  * MySQL DAO provider (uses the MySQLi native API in PHP).
   5:  * 
   6:  * @package alpha::model
   7:  * @since 1.1
   8:  * @author John Collins <dev@alphaframework.org>
   9:  * @version $Id: AlphaDAOProviderMySQL.inc 1561 2012-08-03 21:47:25Z alphadevx $
  10:  * @license http://www.opensource.org/licenses/bsd-license.php The BSD License
  11:  * @copyright Copyright (c) 2012, John Collins (founder of Alpha Framework).  
  12:  * All rights reserved.
  13:  * 
  14:  * <pre>
  15:  * Redistribution and use in source and binary forms, with or 
  16:  * without modification, are permitted provided that the 
  17:  * following conditions are met:
  18:  * 
  19:  * * Redistributions of source code must retain the above 
  20:  *   copyright notice, this list of conditions and the 
  21:  *   following disclaimer.
  22:  * * Redistributions in binary form must reproduce the above 
  23:  *   copyright notice, this list of conditions and the 
  24:  *   following disclaimer in the documentation and/or other 
  25:  *   materials provided with the distribution.
  26:  * * Neither the name of the Alpha Framework nor the names 
  27:  *   of its contributors may be used to endorse or promote 
  28:  *   products derived from this software without specific 
  29:  *   prior written permission.
  30:  *   
  31:  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND 
  32:  * CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, 
  33:  * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
  34:  * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 
  35:  * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR 
  36:  * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 
  37:  * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT 
  38:  * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
  39:  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 
  40:  * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
  41:  * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE 
  42:  * OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 
  43:  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  44:  * </pre>
  45:  *  
  46:  */
  47: class AlphaDAOProviderMySQL implements AlphaDAOProviderInterface {
  48:     /**
  49:      * Trace logger
  50:      * 
  51:      * @var Logger
  52:      * @since 1.1
  53:      */
  54:     private static $logger = null;
  55:     
  56:     /**
  57:      * Datebase connection
  58:      * 
  59:      * @var mysqli
  60:      * @since 1.1
  61:      */
  62:     private static $connection;
  63:     
  64:     /**
  65:      * The business object that we are mapping back to
  66:      * 
  67:      * @var AlphaDAO
  68:      * @since 1.1
  69:      */
  70:     private $BO;
  71:     
  72:     /**
  73:      * The constructor
  74:      * 
  75:      * @since 1.1
  76:      */
  77:     public function __construct() {
  78:         self::$logger = new Logger('AlphaDAOProviderMySQL');
  79:         self::$logger->debug('>>__construct()');
  80:         
  81:         self::$logger->debug('<<__construct');
  82:     }
  83:     
  84:     /**
  85:      * (non-PHPdoc)
  86:      * @see alpha/model/AlphaDAOProviderInterface::getConnection()
  87:      */
  88:     public static function getConnection() {
  89:         global $config;
  90:         
  91:         if (!isset(self::$connection)) {
  92:             self::$connection = new mysqli($config->get('db.hostname'), $config->get('db.username'), $config->get('db.password'), $config->get('db.name'));
  93:                 
  94:             if (mysqli_connect_error()) {
  95:                 self::$logger->fatal('Could not connect to database: ['.mysqli_connect_errno().'] '.mysqli_connect_error());
  96:             }
  97:         }
  98:         
  99:         return self::$connection;
 100:     }
 101:     
 102:     /**
 103:      * (non-PHPdoc)
 104:      * @see alpha/model/AlphaDAOProviderInterface::disconnect()
 105:      */
 106:     public static function disconnect() {
 107:         if (isset(self::$connection)) {
 108:             self::$connection->close();
 109:             self::$connection = null;
 110:         }
 111:     }
 112:     
 113:     /**
 114:      * (non-PHPdoc)
 115:      * @see alpha/model/AlphaDAOProviderInterface::getLastDatabaseError()
 116:      */
 117:     public static function getLastDatabaseError() {
 118:         return self::getConnection()->error;
 119:     }
 120:     
 121:     /**
 122:      * (non-PHPdoc)
 123:      * @see alpha/model/AlphaDAOProviderInterface::query()
 124:      */
 125:     public function query($sqlQuery) {
 126:         $this->BO->setLastQuery($sqlQuery);
 127:         
 128:         $resultArray = array();
 129:         
 130:         if(!$result = self::getConnection()->query($sqlQuery)) {
 131:             throw new CustomQueryException('Failed to run the custom query, MySql error is ['.self::getConnection()->error.'], query ['.$sqlQuery.']');
 132:             return array();
 133:         }else{
 134:             while($row = $result->fetch_array(MYSQLI_ASSOC)) {          
 135:                 array_push($resultArray, $row);
 136:             }
 137:                 
 138:             return $resultArray;
 139:         }
 140:     }
 141:     
 142:     /**
 143:      * (non-PHPdoc)
 144:      * @see alpha/model/AlphaDAOProviderInterface::load()
 145:      */
 146:     public function load($OID) {
 147:         self::$logger->debug('>>load(OID=['.$OID.'])');
 148: 
 149:         global $config;
 150:         
 151:         $attributes = $this->BO->getPersistentAttributes();
 152:         $fields = '';
 153:         foreach($attributes as $att)
 154:             $fields .= $att.',';
 155:         $fields = substr($fields, 0, -1);
 156:             
 157:         $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().' WHERE OID = ? LIMIT 1;';
 158:         $this->BO->setLastQuery($sqlQuery);
 159:         $stmt = self::getConnection()->stmt_init();
 160:     
 161:         $row = array();
 162:             
 163:         if($stmt->prepare($sqlQuery)) {
 164:             $stmt->bind_param('i', $OID);
 165:             $stmt->execute();
 166:                 
 167:             $result = $this->bindResult($stmt);
 168:             if(isset($result[0]))
 169:                 $row = $result[0];
 170:                 
 171:             $stmt->close();
 172:         }else{
 173:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
 174:             if(!$this->BO->checkTableExists()) {
 175:                 $this->BO->makeTable();
 176:                 if($this->BO->getMaintainHistory())
 177:                     $this->BO->makeHistoryTable();
 178:                 throw new BONotFoundException('Failed to load object of OID ['.$OID.'], table ['.$this->BO->getTableName().'] did not exist so had to create!');
 179:             }
 180:             return;
 181:         }
 182:             
 183:         if(!isset($row['OID']) || $row['OID'] < 1) {
 184:             throw new BONotFoundException('Failed to load object of OID ['.$OID.'] not found in database.');            
 185:             self::$logger->debug('<<load');
 186:             return;
 187:         }
 188:             
 189:         // get the class attributes
 190:         $reflection = new ReflectionClass(get_class($this->BO));
 191:         $properties = $reflection->getProperties();
 192:     
 193:         try {
 194:             foreach($properties as $propObj) {
 195:                 $propName = $propObj->name;
 196:                                                 
 197:                 // filter transient attributes
 198:                 if(!in_array($propName, $this->BO->getTransientAttributes())) {
 199:                     $this->BO->set($propName, $row[$propName]);
 200:                 }elseif(!$propObj->isPrivate() && $this->BO->getPropObject($propName) instanceof Relation) {
 201:                     $prop = $this->BO->getPropObject($propName);
 202:                         
 203:                     // handle the setting of ONE-TO-MANY relation values
 204:                     if($prop->getRelationType() == 'ONE-TO-MANY') {
 205:                         $this->BO->set($propObj->name, $this->BO->getOID());
 206:                     }
 207:                     
 208:                     // handle the setting of MANY-TO-ONE relation values
 209:                     if($prop->getRelationType() == 'MANY-TO-ONE') {
 210:                         $this->BO->set($propObj->name, $row[$propName]);
 211:                     }
 212:                 }
 213:             }
 214:         }catch (IllegalArguementException $e) {
 215:             self::$logger->warn('Bad data stored in the table ['.$this->BO->getTableName().'], field ['.$propObj->name.'] bad value['.$row[$propObj->name].'], exception ['.$e->getMessage().']');
 216:         }catch (PHPException $e) {
 217:             // it is possible that the load failed due to the table not being up-to-date
 218:             if($this->BO->checkTableNeedsUpdate()) {                
 219:                 $missingFields = $this->BO->findMissingFields();
 220:                 
 221:                 $count = count($missingFields);
 222:                     
 223:                 for($i = 0; $i < $count; $i++)
 224:                     $this->BO->addProperty($missingFields[$i]);
 225:                         
 226:                 throw new BONotFoundException('Failed to load object of OID ['.$OID.'], table ['.$this->BO->getTableName().'] was out of sync with the database so had to be updated!');
 227:                 self::$logger->debug('<<load');
 228:                 return;
 229:             }
 230:         }
 231:         
 232:         self::$logger->debug('<<load');
 233:     }
 234:     
 235:     /**
 236:      * (non-PHPdoc)
 237:      * @see alpha/model/AlphaDAOProviderInterface::loadByAttribute()
 238:      */
 239:     public function loadByAttribute($attribute, $value, $ignoreClassType=false, $loadAttributes=array()) {
 240:         self::$logger->debug('>>loadByAttribute(attribute=['.$attribute.'], value=['.$value.'], ignoreClassType=['.$ignoreClassType.'], 
 241:             loadAttributes=['.var_export($loadAttributes, true).'])');
 242:         
 243:         if(count($loadAttributes) == 0)
 244:             $attributes = $this->BO->getPersistentAttributes();
 245:         else
 246:             $attributes = $loadAttributes;
 247:         
 248:         $fields = '';
 249:         foreach($attributes as $att)
 250:             $fields .= $att.',';
 251:         $fields = substr($fields, 0, -1);
 252:         
 253:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 254:             $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().' WHERE '.$attribute.' = ? AND classname = ? LIMIT 1;';
 255:         else
 256:             $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().' WHERE '.$attribute.' = ? LIMIT 1;';
 257:         
 258:         self::$logger->debug('Query=['.$sqlQuery.']');
 259:         
 260:         $this->BO->setLastQuery($sqlQuery);
 261:         $stmt = self::getConnection()->stmt_init();
 262: 
 263:         $row = array();
 264:         
 265:         if($stmt->prepare($sqlQuery)) {
 266:             if($this->BO->getPropObject($attribute) instanceof Integer) {
 267:                 if(!$ignoreClassType && $this->BO->isTableOverloaded()) {
 268:                     $stmt->bind_param('is', $value, get_class($this->BO));
 269:                 }else{
 270:                     $stmt->bind_param('i', $value);
 271:                 }
 272:             }else{
 273:                 if(!$ignoreClassType && $this->BO->isTableOverloaded()) {
 274:                     $stmt->bind_param('ss', $value, get_class($this->BO));
 275:                 }else{
 276:                     $stmt->bind_param('s', $value);
 277:                 }
 278:             }
 279:             
 280:             $stmt->execute();
 281:             
 282:             $result = $this->bindResult($stmt);
 283:             
 284:             if(isset($result[0]))
 285:                 $row = $result[0];
 286:                 
 287:             $stmt->close();
 288:         }else{
 289:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
 290:             if(!$this->BO->checkTableExists()) {
 291:                 $this->BO->makeTable();
 292:                 if($this->BO->getMaintainHistory())
 293:                                         $this->BO->makeHistoryTable();
 294:                 throw new BONotFoundException('Failed to load object by attribute ['.$attribute.'] and value ['.$value.'], table did not exist so had to create!');
 295:             }
 296:             return;
 297:         }
 298:         
 299:         if(!isset($row['OID']) || $row['OID'] < 1) {
 300:             throw new BONotFoundException('Failed to load object by attribute ['.$attribute.'] and value ['.$value.'], not found in database.');            
 301:             self::$logger->debug('<<loadByAttribute');
 302:             return;
 303:         }
 304:         
 305:         $this->OID = $row['OID'];
 306:         
 307:         // get the class attributes
 308:         $reflection = new ReflectionClass(get_class($this->BO));
 309:         $properties = $reflection->getProperties();
 310: 
 311:         try {
 312:             foreach($properties as $propObj) {
 313:                 $propName = $propObj->name;
 314: 
 315:                 if(isset($row[$propName])) {
 316:                     // filter transient attributes
 317:                     if(!in_array($propName, $this->BO->getTransientAttributes())) {
 318:                         $this->BO->set($propName, $row[$propName]);
 319:                     }elseif(!$propObj->isPrivate() && $this->BO->get($propName) != '' && $this->BO->getPropObject($propName) instanceof Relation) {
 320:                         $prop = $this->BO->getPropObject($propName);
 321:                         
 322:                         // handle the setting of ONE-TO-MANY relation values
 323:                         if($prop->getRelationType() == 'ONE-TO-MANY') {
 324:                             $this->BO->set($propObj->name, $this->BO->getOID());
 325:                         }
 326:                     }
 327:                 }
 328:             }
 329:         }catch (IllegalArguementException $e) {
 330:             self::$logger->warn('Bad data stored in the table ['.$this->BO->getTableName().'], field ['.$propObj->name.'] bad value['.$row[$propObj->name].'], exception ['.$e->getMessage().']');
 331:         }catch (PHPException $e) {
 332:             // it is possible that the load failed due to the table not being up-to-date
 333:             if($this->BO->checkTableNeedsUpdate()) {                
 334:                 $missingFields = $this->BO->findMissingFields();
 335:             
 336:                 $count = count($missingFields);
 337:                 
 338:                 for($i = 0; $i < $count; $i++)
 339:                     $this->BO->addProperty($missingFields[$i]);
 340:                     
 341:                 throw new BONotFoundException('Failed to load object by attribute ['.$attribute.'] and value ['.$value.'], table ['.$this->BO->getTableName().'] was out of sync with the database so had to be updated!');
 342:                 self::$logger->debug('<<loadByAttribute');
 343:                 return;
 344:             }
 345:         }
 346:         
 347:         self::$logger->debug('<<loadByAttribute');
 348:     }
 349:     
 350:     /**
 351:      * (non-PHPdoc)
 352:      * @see alpha/model/AlphaDAOProviderInterface::loadAll()
 353:      */
 354:     public function loadAll($start=0, $limit=0, $orderBy='OID', $order='ASC', $ignoreClassType=false) {
 355:         self::$logger->debug('>>loadAll(start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 356:         
 357:         // ensure that the field name provided in the orderBy param is legit
 358:         try {
 359:             $field = $this->BO->get($orderBy);
 360:         }catch(AlphaException $e) {
 361:             throw new AlphaException('The field name ['.$orderBy.'] provided in the param orderBy does not exist on the class ['.get_class($this->BO).']');
 362:         }
 363:         
 364:         if(!$ignoreClassType && $this->BO->isTableOverloaded()) {
 365:             if($limit == 0) {
 366:                 $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE classname=\''.get_class($this->BO).'\' ORDER BY '.$orderBy.' '.$order.';';
 367:             }else{
 368:                 $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE classname=\''.get_class($this->BO).'\' ORDER BY '.$orderBy.' '.$order.' LIMIT '.
 369:                     $start.', '.$limit.';';
 370:             }
 371:         }else{              
 372:             if($limit == 0)
 373:                 $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' ORDER BY '.$orderBy.' '.$order.';';
 374:             else
 375:                 $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' ORDER BY '.$orderBy.' '.$order.' LIMIT '.$start.', '.$limit.';';
 376:         }
 377:         
 378:         $this->BO->setLastQuery($sqlQuery);
 379:         
 380:         if(!$result = AlphaDAOProviderMySQL::getConnection()->query($sqlQuery)) {
 381:             throw new BONotFoundException('Failed to load object OIDs, MySql error is ['.AlphaDAOProviderMySQL::getConnection()->error.'], query ['.$this->BO->getLastQuery().']');
 382:             self::$logger->debug('<<loadAll [0]');
 383:             return array();
 384:         }
 385:         
 386:         // now build an array of objects to be returned
 387:         $objects = array();
 388:         $count = 0;
 389:         $BO_Class = get_class($this->BO);
 390:         
 391:         while($row = $result->fetch_array(MYSQLI_ASSOC)) {
 392:             try {
 393:                 $obj = new $BO_Class();
 394:                 $obj->load($row['OID']);
 395:                 $objects[$count] = $obj;
 396:                 $count++;
 397:             }catch(ResourceNotAllowedException $e) {
 398:                 // the resource not allowed will be absent from the list
 399:             }
 400:         }
 401:         
 402:         self::$logger->debug('<<loadAll ['.count($objects).']');
 403:         return $objects;
 404:     }
 405:     
 406:     /**
 407:      * (non-PHPdoc)
 408:      * @see alpha/model/AlphaDAOProviderInterface::loadAllByAttribute()
 409:      */
 410:     public function loadAllByAttribute($attribute, $value, $start=0, $limit=0, $orderBy="OID", $order="ASC", $ignoreClassType=false, $constructorArgs=array()) {
 411:         self::$logger->debug('>>loadAllByAttribute(attribute=['.$attribute.'], value=['.$value.'], start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.'], constructorArgs=['.print_r($constructorArgs, true).']');
 412:         
 413:         if ($start != 0 && $limit != 0)
 414:             $limit = ' LIMIT '.$start.', '.$limit.';';
 415:         else
 416:             $limit = ';';
 417:         
 418:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 419:             $sqlQuery = "SELECT OID FROM ".$this->BO->getTableName()." WHERE $attribute = ? AND classname = ? ORDER BY ".$orderBy." ".$order.$limit;
 420:         else
 421:             $sqlQuery = "SELECT OID FROM ".$this->BO->getTableName()." WHERE $attribute = ? ORDER BY ".$orderBy." ".$order.$limit;          
 422:             
 423:         $this->BO->setLastQuery($sqlQuery);
 424:         self::$logger->debug($sqlQuery);
 425:         
 426:         $stmt = AlphaDAOProviderMySQL::getConnection()->stmt_init();
 427: 
 428:         $row = array();
 429:         
 430:         if($stmt->prepare($sqlQuery)) {
 431:             if($this->BO->getPropObject($attribute) instanceof Integer) {
 432:                 if($this->BO->isTableOverloaded()) {
 433:                     $stmt->bind_param('is', $value, get_class($this->BO));
 434:                 }else{
 435:                     $stmt->bind_param('i', $value);
 436:                 }
 437:             }else{
 438:                 if($this->BO->isTableOverloaded()) {
 439:                     $stmt->bind_param('ss', $value, get_class($this->BO));
 440:                 }else{
 441:                     $stmt->bind_param('s', $value);
 442:                 }
 443:             }
 444:             
 445:             $stmt->execute();
 446:             
 447:             $result = $this->bindResult($stmt);
 448:                 
 449:             $stmt->close();
 450:         }else{
 451:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
 452:             if(!$this->BO->checkTableExists()) {
 453:                 $this->BO->makeTable();
 454:                 if($this->BO->getMaintainHistory())
 455:                                         $this->BO->makeHistoryTable();
 456:                 throw new BONotFoundException('Failed to load objects by attribute ['.$attribute.'] and value ['.$value.'], table did not exist so had to create!');
 457:             }
 458:             self::$logger->debug('<<loadAllByAttribute []');
 459:             return array();
 460:         }
 461:         
 462:         // now build an array of objects to be returned
 463:         $objects = array();
 464:         $count = 0;
 465:         $BO_Class = get_class($this->BO);
 466:         
 467:         foreach($result as $row) {
 468:             try {
 469:                 $argsCount = count($constructorArgs);
 470:                 
 471:                 if($argsCount < 1) {
 472:                     $obj = new $BO_Class();
 473:                 }else{
 474:                     switch ($argsCount) {
 475:                         case 1:
 476:                             $obj = new $BO_Class($constructorArgs[0]);
 477:                         break;
 478:                         case 2:
 479:                             $obj = new $BO_Class($constructorArgs[0],$constructorArgs[1]);
 480:                         break;
 481:                         case 3:
 482:                             $obj = new $BO_Class($constructorArgs[0],$constructorArgs[1],$constructorArgs[2]);
 483:                         break;
 484:                         case 4:
 485:                             $obj = new $BO_Class($constructorArgs[0],$constructorArgs[1],$constructorArgs[2],$constructorArgs[3]);
 486:                         break;
 487:                         case 5:
 488:                             $obj = new $BO_Class($constructorArgs[0],$constructorArgs[1],$constructorArgs[2],$constructorArgs[3],$constructorArgs[4]);
 489:                         break;                      
 490:                         default:
 491:                             throw new IllegalArguementException('Too many elements in the $constructorArgs array passed to the loadAllByAttribute method!');
 492:                         break;
 493:                     }
 494:                 }
 495:                 
 496:                 $obj->load($row['OID']);
 497:                 $objects[$count] = $obj;
 498:                 $count++;
 499:             }catch(ResourceNotAllowedException $e) {
 500:                 // the resource not allowed will be absent from the list
 501:             }
 502:         }
 503:         
 504:         self::$logger->debug('<<loadAllByAttribute ['.count($objects).']');
 505:         return $objects;    
 506:     }
 507:     
 508:     /**
 509:      * (non-PHPdoc)
 510:      * @see alpha/model/AlphaDAOProviderInterface::loadAllByAttributes()
 511:      */
 512:     public function loadAllByAttributes($attributes=array(), $values=array(), $start=0, $limit=0, $orderBy='OID', $order='ASC', $ignoreClassType=false) {
 513:         self::$logger->debug('>>loadAllByAttributes(attributes=['.var_export($attributes, true).'], values=['.var_export($values, true).'], start=['.
 514:             $start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 515:         
 516:         $whereClause = ' WHERE';
 517:         
 518:         $count = count($attributes);
 519:         
 520:         for($i = 0; $i < $count; $i++) {
 521:             $whereClause .= ' '.$attributes[$i].' = ? AND';
 522:             self::$logger->debug($whereClause);
 523:         }
 524:         
 525:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 526:             $whereClause .= ' classname = ? AND';
 527:         
 528:         // remove the last " AND"
 529:         $whereClause = substr($whereClause, 0, -4);
 530:         
 531:         if ($limit != 0)
 532:             $limit = ' LIMIT '.$start.', '.$limit.';';
 533:         else
 534:             $limit = ';';
 535:         
 536:         $sqlQuery = "SELECT OID FROM ".$this->BO->getTableName().$whereClause." ORDER BY ".$orderBy." ".$order.$limit;
 537:             
 538:         $this->BO->setLastQuery($sqlQuery);
 539:         
 540:         $stmt = AlphaDAOProviderMySQL::getConnection()->stmt_init();
 541:         
 542:         if($stmt->prepare($sqlQuery)) {         
 543:             // bind params where required attributes are provided
 544:             if(count($attributes) > 0 && count($attributes) == count($values)) {
 545:                 $stmt = $this->bindParams($stmt, $attributes, $values);
 546:             }else{
 547:                 // we'll still need to bind the "classname" for overloaded BOs...
 548:                 if($this->BO->isTableOverloaded())
 549:                     $stmt->bind_param('s', get_class($this->BO));
 550:             }
 551:             $stmt->execute();
 552:             
 553:             $result = $this->bindResult($stmt);
 554:                 
 555:             $stmt->close();
 556:         }else{
 557:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
 558:             
 559:             if(!$this->BO->checkTableExists()) {
 560:                 $this->BO->makeTable();
 561:                 if($this->BO->getMaintainHistory())
 562:                                         $this->BO->makeHistoryTable();
 563:                 throw new BONotFoundException('Failed to load objects by attributes ['.var_export($attributes, true).'] and values ['.
 564:                     var_export($values, true).'], table did not exist so had to create!');
 565:             }
 566:             
 567:             self::$logger->debug('<<loadAllByAttributes []');
 568:             return array();
 569:         }
 570:         
 571:         // now build an array of objects to be returned
 572:         $objects = array();
 573:         $count = 0;
 574:         $BO_Class = get_class($this->BO);
 575:         
 576:         foreach($result as $row) {
 577:             try {
 578:                 $obj = new $BO_Class();
 579:                 $obj->load($row['OID']);
 580:                 $objects[$count] = $obj;
 581:                 $count++;
 582:             }catch(ResourceNotAllowedException $e) {
 583:                 // the resource not allowed will be absent from the list
 584:             }
 585:         }
 586:         
 587:         self::$logger->debug('<<loadAllByAttributes ['.count($objects).']');
 588:         return $objects;    
 589:     }
 590:     
 591:     /**
 592:      * (non-PHPdoc)
 593:      * @see alpha/model/AlphaDAOProviderInterface::loadAllByDayUpdated()
 594:      */
 595:     public function loadAllByDayUpdated($date, $start=0, $limit=0, $orderBy="OID", $order="ASC", $ignoreClassType=false) {
 596:         self::$logger->debug('>>loadAllByDayUpdated(date=['.$date.'], start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 597:         
 598:         if ($start != 0 && $limit != 0)
 599:             $limit = ' LIMIT '.$start.', '.$limit.';';
 600:         else
 601:             $limit = ';';
 602:         
 603:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 604:             $sqlQuery = "SELECT OID FROM ".$this->BO->getTableName()." WHERE updated_ts >= '".$date." 00:00:00' AND updated_ts <= '".$date." 23:59:59' AND classname='".get_class($this->BO)."' ORDER BY ".$orderBy." ".$order.$limit;
 605:         else
 606:             $sqlQuery = "SELECT OID FROM ".$this->BO->getTableName()." WHERE updated_ts >= '".$date." 00:00:00' AND updated_ts <= '".$date." 23:59:59' ORDER BY ".$orderBy." ".$order.$limit;
 607:             
 608:         $this->BO->setLastQuery($sqlQuery);
 609: 
 610:         if(!$result = AlphaDAOProviderMySQL::getConnection()->query($sqlQuery)) {
 611:             throw new BONotFoundException('Failed to load object OIDs, MySql error is ['.self::getConnection()->error.'], query ['.$this->BO->getLastQuery().']');
 612:             self::$logger->debug('<<loadAllByDayUpdated []');
 613:             return array();
 614:         }
 615:         
 616:         // now build an array of objects to be returned
 617:         $objects = array();
 618:         $count = 0;
 619:         $BO_Class = get_class($this->BO);
 620:         
 621:         while($row = $result->fetch_array(MYSQLI_ASSOC)) {
 622:             $obj = new $BO_Class();
 623:             $obj->load($row['OID']);
 624:             $objects[$count] = $obj;
 625:             $count++;
 626:         }
 627:         
 628:         self::$logger->debug('<<loadAllByDayUpdated ['.count($objects).']');
 629:         return $objects;    
 630:     }
 631:     
 632:     /**
 633:      * (non-PHPdoc)
 634:      * @see alpha/model/AlphaDAOProviderInterface::loadAllFieldValuesByAttribute()
 635:      */
 636:     public function loadAllFieldValuesByAttribute($attribute, $value, $returnAttribute, $order='ASC', $ignoreClassType=false) {
 637:         self::$logger->debug('>>loadAllFieldValuesByAttribute(attribute=['.$attribute.'], value=['.$value.'], returnAttribute=['.$returnAttribute.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 638:         
 639:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 640:             $sqlQuery = "SELECT ".$returnAttribute." FROM ".$this->BO->getTableName()." WHERE $attribute = '$value' AND classname='".get_class($this->BO)."' ORDER BY OID ".$order.";";
 641:         else
 642:             $sqlQuery = "SELECT ".$returnAttribute." FROM ".$this->BO->getTableName()." WHERE $attribute = '$value' ORDER BY OID ".$order.";";
 643:             
 644:         $this->BO->setLastQuery($sqlQuery);
 645:         
 646:         self::$logger->debug('lastQuery ['.$sqlQuery.']');
 647: 
 648:         if(!$result = AlphaDAOProviderMySQL::getConnection()->query($sqlQuery)) {
 649:             throw new BONotFoundException('Failed to load field ['.$returnAttribute.'] values, MySql error is ['.AlphaDAOProviderMySQL::getConnection()->error.'], query ['.$this->getLastQuery().']');
 650:             self::$logger->debug('<<loadAllFieldValuesByAttribute []');
 651:             return array();
 652:         }
 653:         
 654:         // now build an array of attribute values to be returned
 655:         $values = array();
 656:         $count = 0;
 657:         $BO_Class = get_class($this->BO);
 658:         
 659:         while($row = $result->fetch_array(MYSQLI_ASSOC)) {
 660:             $values[$count] = $row[$returnAttribute];
 661:             $count++;
 662:         }
 663:         
 664:         self::$logger->debug('<<loadAllFieldValuesByAttribute ['.count($values).']');
 665:         return $values;
 666:     }
 667: 
 668:     /**
 669:      * (non-PHPdoc)
 670:      * @see alpha/model/AlphaDAOProviderInterface::save()
 671:      */
 672:     public function save() {
 673:         self::$logger->debug('>>save()');
 674:             
 675:         // get the class attributes
 676:         $reflection = new ReflectionClass(get_class($this->BO));
 677:         $properties = $reflection->getProperties();
 678:         $sqlQuery = '';
 679:         $stmt = null;
 680: 
 681:         if($this->BO->getVersion() != $this->BO->getVersionNumber()->getValue()){
 682:             throw new LockingException('Could not save the object as it has been updated by another user.  Please try saving again.');
 683:             return;
 684:         }
 685: 
 686:         // set the "updated by" fields, we can only set the user id if someone is logged in
 687:         if(isset($_SESSION['currentUser']))
 688:             $this->BO->set('updated_by', $_SESSION['currentUser']->getOID());
 689:             
 690:         $this->BO->set('updated_ts', new Timestamp(date("Y-m-d H:i:s")));
 691:             
 692:         // check to see if it is a transient object that needs to be inserted
 693:         if($this->BO->isTransient()) {
 694:             $savedFieldsCount = 0;
 695:             $sqlQuery = 'INSERT INTO '.$this->BO->getTableName().' (';
 696:     
 697:             foreach($properties as $propObj) {
 698:                 $propName = $propObj->name;
 699:                 if (!in_array($propName, $this->BO->getTransientAttributes())) {
 700:                     // Skip the OID, database auto number takes care of this.
 701:                     if($propName != 'OID' && $propName != 'version_num') {
 702:                         $sqlQuery .= "$propName,";
 703:                         $savedFieldsCount++;
 704:                     }
 705:                     
 706:                     if($propName == 'version_num') {
 707:                         $sqlQuery .= 'version_num,';
 708:                         $savedFieldsCount++;
 709:                     }
 710:                 }
 711:             }
 712:             if($this->BO->isTableOverloaded())
 713:                 $sqlQuery .= 'classname,';
 714:     
 715:             $sqlQuery = rtrim($sqlQuery, ",");
 716:     
 717:             $sqlQuery .= ') VALUES (';
 718:                 
 719:             for($i = 0; $i < $savedFieldsCount; $i++)
 720:                 $sqlQuery.= '?,';
 721:                 
 722:             if($this->BO->isTableOverloaded())
 723:                 $sqlQuery.= '?,';
 724:                 
 725:             $sqlQuery = rtrim($sqlQuery, ',').')';
 726:                 
 727:             $this->BO->setLastQuery($sqlQuery);
 728:             self::$logger->debug('Query ['.$sqlQuery.']');
 729:             
 730:             $stmt = AlphaDAOProviderMySQL::getConnection()->stmt_init();
 731:         
 732:             if($stmt->prepare($sqlQuery)) {         
 733:                 $stmt = $this->bindParams($stmt);                   
 734:                 $stmt->execute();                   
 735:             }else{
 736:                 throw new FailedSaveException('Failed to save object, error is ['.$stmt->error.'], query ['.$this->BO->getLastQuery().']');
 737:             }
 738:         }else{
 739:             // assume that it is a persistent object that needs to be updated
 740:             $savedFieldsCount = 0;
 741:             $sqlQuery = 'UPDATE '.$this->BO->getTableName().' SET ';
 742:     
 743:             foreach($properties as $propObj) {
 744:                 $propName = $propObj->name;
 745:                 if (!in_array($propName, $this->BO->getTransientAttributes())) {
 746:                     // Skip the OID, database auto number takes care of this.
 747:                     if($propName != 'OID' && $propName != 'version_num') {                          
 748:                         $sqlQuery .= "$propName = ?,";
 749:                         $savedFieldsCount++;
 750:                     }
 751:                         
 752:                     if($propName == 'version_num') {
 753:                         $sqlQuery .= 'version_num = ?,';
 754:                         $savedFieldsCount++;
 755:                     }
 756:                 }
 757:             }
 758:             
 759:             if($this->BO->isTableOverloaded())
 760:                 $sqlQuery .= 'classname = ?,';
 761:     
 762:             $sqlQuery = rtrim($sqlQuery, ",");
 763:     
 764:             $sqlQuery .= " WHERE OID=?;";
 765:                 
 766:             $this->BO->setLastQuery($sqlQuery);
 767:             $stmt = AlphaDAOProviderMySQL::getConnection()->stmt_init();
 768:                 
 769:             if($stmt->prepare($sqlQuery)) {                         
 770:                 $this->bindParams($stmt);
 771:                 $stmt->execute();                   
 772:             }else{
 773:                 throw new FailedSaveException('Failed to save object, error is ['.$stmt->error.'], query ['.$this->BO->getLastQuery().']');             
 774:             }               
 775:         }
 776:     
 777:         if ($stmt != null && $stmt->error == '') {
 778:             // populate the updated OID in case we just done an insert              
 779:             if($this->BO->isTransient())
 780:                 $this->BO->setOID(AlphaDAOProviderMySQL::getConnection()->insert_id);
 781: 
 782:             try {
 783:                 foreach($properties as $propObj) {
 784:                     $propName = $propObj->name;
 785:                     
 786:                     if($this->BO->getPropObject($propName) instanceof Relation) {
 787:                         $prop = $this->BO->getPropObject($propName);
 788:                             
 789:                         // handle the saving of MANY-TO-MANY relation values
 790:                         if($prop->getRelationType() == 'MANY-TO-MANY') {
 791:                             try {
 792:                                 try{
 793:                                     // check to see if the rel is on this class
 794:                                     $side = $prop->getSide(get_class($this->BO));                                           
 795:                                 }catch (IllegalArguementException $iae) {
 796:                                     $side = $prop->getSide(ucfirst($this->BO->getTableName()).'Object');
 797:                                 }
 798:                                         
 799:                                 $lookUp = $prop->getLookup();                                   
 800:                                         
 801:                                 // first delete all of the old RelationLookup objects for this rel
 802:                                 try {
 803:                                     if($side == 'left')
 804:                                         $lookUp->deleteAllByAttribute('leftID', $this->BO->getOID());
 805:                                     else
 806:                                         $lookUp->deleteAllByAttribute('rightID', $this->BO->getOID());                          
 807:                                 }catch (Exception $e) {
 808:                                     throw new FailedSaveException('Failed to delete old RelationLookup objects on the table ['.$prop->getLookup()->getTableName().'], error is ['.$e->getMessage().']');
 809:                                 }
 810:                                             
 811:                                 if(isset($_POST[$propName]) && $_POST[$propName] != '00000000000')
 812:                                     $OIDs = explode(',', $_POST[$propName]);
 813:                                                                                     
 814:                                 if(isset($OIDs) && !empty($OIDs[0])) {                                      
 815:                                     // now for each posted OID, create a new RelationLookup record and save
 816:                                     foreach ($OIDs as $oid) {                                           
 817:                                         $newLookUp = new RelationLookup($lookUp->get('leftClassName'), $lookUp->get('rightClassName'));
 818:                                         if($side == 'left') {
 819:                                             $newLookUp->set('leftID', $this->BO->getOID());
 820:                                             $newLookUp->set('rightID', $oid);
 821:                                         }else{
 822:                                             $newLookUp->set('rightID', $this->BO->getOID());
 823:                                             $newLookUp->set('leftID', $oid);
 824:                                         }
 825:                                         $newLookUp->save();
 826:                                     }                                   
 827:                                 }
 828:                             }catch (Exception $e) {
 829:                                 throw new FailedSaveException('Failed to update a MANY-TO-MANY relation on the object, error is ['.$e->getMessage().']');
 830:                                 return;
 831:                             }
 832:                         }
 833:                             
 834:                         // handle the saving of ONE-TO-MANY relation values
 835:                         if($prop->getRelationType() == 'ONE-TO-MANY') {
 836:                             $prop->setValue($this->BO->getOID());
 837:                         }
 838:                     }                       
 839:                 }
 840:             }catch (Exception $e) {
 841:                 throw new FailedSaveException('Failed to save object, error is ['.$e->getMessage().']');
 842:                 return;
 843:             }
 844:                 
 845:             $stmt->close();
 846:         }else{
 847:             // there has been an error, so decrement the version number back
 848:             $temp = $this->BO->getVersionNumber()->getValue();                  
 849:             $this->BO->set('version_num', $temp-1); 
 850:                 
 851:             // check for unique violations          
 852:             if(AlphaDAOProviderMySQL::getConnection()->errno == '1062') {                   
 853:                 throw new ValidationException('Failed to save, the value '.$this->findOffendingValue(AlphaDAOProviderMySQL::getConnection()->error).' is already in use!');
 854:                 return;
 855:             }else{                  
 856:                 throw new FailedSaveException('Failed to save object, MySql error is ['.AlphaDAOProviderMySQL::getConnection()->error.'], query ['.$this->BO->getLastQuery().']');
 857:             }
 858:         }
 859: 
 860:         if($this->BO->getMaintainHistory())
 861:             $this->BO->saveHistory();
 862:     }
 863:     
 864:     /**
 865:      * (non-PHPdoc)
 866:      * @see alpha/model/AlphaDAOProviderInterface::saveAttribute()
 867:      */
 868:     public function saveAttribute($attribute, $value) {
 869:         self::$logger->debug('>>saveAttribute(attribute=['.$attribute.'], value=['.$value.'])');
 870:         
 871:         // assume that it is a persistent object that needs to be updated
 872:         $sqlQuery = 'UPDATE '.$this->BO->getTableName().' SET '.$attribute.'=? WHERE OID=?;';
 873:                 
 874:         $this->BO->setLastQuery($sqlQuery);
 875:         $stmt = self::getConnection()->stmt_init();
 876:                 
 877:         if($stmt->prepare($sqlQuery)) {
 878:             if($this->BO->getPropObject($attribute) instanceof Integer)
 879:                 $bindingsType = 'i';
 880:             else
 881:                 $bindingsType = 's';
 882:             $stmt->bind_param($bindingsType.'i', $value, $this->BO->getOID());
 883:             self::$logger->debug('Binding params ['.$bindingsType.'i, '.$value.', '.$this->BO->getOID().']');
 884:             $stmt->execute();
 885:         }else{
 886:             throw new FailedSaveException('Failed to save attribute, error is ['.$stmt->error.'], query ['.$this->BO->getLastQuery().']');              
 887:         }
 888:                 
 889:         $stmt->close();
 890: 
 891:         $this->{$attribute} = $value;
 892: 
 893:         if($this->BO->getMaintainHistory())
 894:             $this->BO->saveHistory();
 895:             
 896:         self::$logger->debug('<<saveAttribute');
 897:     }
 898: 
 899:     /**
 900:      * (non-PHPdoc)
 901:      * @see alpha/model/AlphaDAOProviderInterface::saveHistory()
 902:      */
 903:     public function saveHistory() {
 904:         self::$logger->debug('>>saveHistory()');
 905: 
 906:         // get the class attributes
 907:         $reflection = new ReflectionClass(get_class($this->BO));
 908:         $properties = $reflection->getProperties();
 909:         $sqlQuery = '';
 910:         $stmt = null;
 911: 
 912:         $savedFieldsCount = 0;
 913:         $attributeNames = array();
 914:         $attributeValues = array();
 915: 
 916:         $sqlQuery = 'INSERT INTO '.$this->BO->getTableName().'_history (';
 917: 
 918:         foreach($properties as $propObj) {
 919:             $propName = $propObj->name;
 920:             if (!in_array($propName, $this->BO->getTransientAttributes())) {
 921:                 $sqlQuery .= "$propName,";
 922:                 $attributeNames[] = $propName;
 923:                 $attributeValues[] = $this->BO->get($propName);
 924:                 $savedFieldsCount++;
 925:             }
 926:         }
 927: 
 928:         if($this->BO->isTableOverloaded())
 929:             $sqlQuery .= 'classname,';
 930: 
 931:         $sqlQuery = rtrim($sqlQuery, ",");  
 932: 
 933:         $sqlQuery .= ') VALUES (';
 934: 
 935:         for($i = 0; $i < $savedFieldsCount; $i++)
 936:             $sqlQuery.= '?,';
 937: 
 938:         if($this->BO->isTableOverloaded())
 939:             $sqlQuery.= '?,';
 940: 
 941:         $sqlQuery = rtrim($sqlQuery, ',').')';
 942: 
 943:         $this->BO->setLastQuery($sqlQuery);
 944:         self::$logger->debug('Query ['.$sqlQuery.']');
 945: 
 946:         $stmt = AlphaDAOProviderMySQL::getConnection()->stmt_init();
 947: 
 948:         if($stmt->prepare($sqlQuery)) {
 949:             $stmt = $this->bindParams($stmt, $attributeNames, $attributeValues);
 950:             $stmt->execute();
 951:         }else{
 952:             throw new FailedSaveException('Failed to save object history, error is ['.$stmt->error.'], query ['.$this->BO->getLastQuery().']');
 953:         }
 954:     }
 955:     
 956:     /**
 957:      * (non-PHPdoc)
 958:      * @see alpha/model/AlphaDAOProviderInterface::delete()
 959:      */
 960:     public function delete() {
 961:         self::$logger->debug('>>delete()');
 962:                 
 963:         $sqlQuery = "DELETE FROM ".$this->BO->getTableName()." WHERE OID = ?;";
 964:             
 965:         $this->BO->setLastQuery($sqlQuery);
 966:         
 967:         $stmt = self::getConnection()->stmt_init();
 968:                 
 969:         if($stmt->prepare($sqlQuery)) {
 970:             $stmt->bind_param('i', $this->BO->getOID());            
 971:             $stmt->execute();
 972:             self::$logger->debug('Deleted the object ['.$this->BO->getOID().'] of class ['.get_class($this->BO).']');                   
 973:         }else{
 974:             throw new FailedDeleteException('Failed to delete object ['.$this->BO->getOID().'], error is ['.$stmt->error.'], query ['.$this->BO->getLastQuery().']');               
 975:         }
 976:                 
 977:         $stmt->close();
 978:         
 979:         self::$logger->debug('<<delete');
 980:     }
 981:     
 982:     /**
 983:      * (non-PHPdoc)
 984:      * @see alpha/model/AlphaDAOProviderInterface::getVersion()
 985:      */
 986:     public function getVersion() {
 987:         self::$logger->debug('>>getVersion()');
 988:         
 989:         $sqlQuery = 'SELECT version_num FROM '.$this->BO->getTableName().' WHERE OID = ?;';
 990:         $this->BO->setLastQuery($sqlQuery);
 991:             
 992:         $stmt = AlphaDAOProviderMySQL::getConnection()->stmt_init();
 993:             
 994:         if($stmt->prepare($sqlQuery)) {
 995:             $stmt->bind_param('i', $this->BO->getOID());
 996:                 
 997:             $stmt->execute();
 998:                 
 999:             $result = $this->bindResult($stmt);
1000:             if(isset($result[0]))
1001:                 $row = $result[0];
1002:                     
1003:             $stmt->close();
1004:         }else{
1005:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
1006:             if(!$this->BO->checkTableExists()) {
1007:                 $this->BO->makeTable();
1008:                 if($this->BO->getMaintainHistory())
1009:                                         $this->BO->makeHistoryTable();
1010:                 throw new BONotFoundException('Failed to get the version number, table did not exist so had to create!');
1011:             }
1012:             return;
1013:         }
1014:         
1015:         if(!isset($row['version_num']) || $row['version_num'] < 1) {
1016:             self::$logger->debug('<<getVersion [0]');
1017:             return 0;
1018:         }else{
1019:             $version_num = $row['version_num'];
1020:             
1021:             self::$logger->debug('<<getVersion ['.$version_num.']');
1022:             return $version_num;
1023:         }
1024:     }
1025: 
1026:     /**
1027:      * (non-PHPdoc)
1028:      * @see alpha/model/AlphaDAOProviderInterface::makeTable()
1029:      */
1030:     public function makeTable() {
1031:         self::$logger->debug('>>makeTable()');
1032:         
1033:         $sqlQuery = "CREATE TABLE ".$this->BO->getTableName()." (OID INT(11) ZEROFILL NOT NULL AUTO_INCREMENT,";
1034:         
1035:         // get the class attributes
1036:         $reflection = new ReflectionClass(get_class($this->BO));
1037:         $properties = $reflection->getProperties();
1038:         
1039:         foreach($properties as $propObj) {
1040:             $propName = $propObj->name;
1041:             
1042:             if(!in_array($propName, $this->BO->getTransientAttributes()) && $propName != "OID") {
1043:                 $propClass = get_class($this->BO->getPropObject($propName));
1044: 
1045:                 switch (strtoupper($propClass)) {
1046:                     case "INTEGER":
1047:                         // special properties for RelationLookup OIDs
1048:                         if($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID'))
1049:                             $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize().") ZEROFILL NOT NULL,";
1050:                         else
1051:                             $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize()."),";
1052:                     break;
1053:                     case "DOUBLE":
1054:                         $sqlQuery .= "$propName DOUBLE(".$this->BO->getPropObject($propName)->getSize(true)."),";
1055:                     break;
1056:                     case "STRING":
1057:                         $sqlQuery .= "$propName VARCHAR(".$this->BO->getPropObject($propName)->getSize()."),";
1058:                     break;
1059:                     case "TEXT":
1060:                         $sqlQuery .= "$propName TEXT,";
1061:                     break;
1062:                     case "BOOLEAN":
1063:                         $sqlQuery .= "$propName CHAR(1) DEFAULT '0',";
1064:                     break;
1065:                     case "DATE":
1066:                         $sqlQuery .= "$propName DATE,";
1067:                     break;
1068:                     case "TIMESTAMP":
1069:                         $sqlQuery .= "$propName DATETIME,";
1070:                     break;
1071:                     case "ENUM":
1072:                         $sqlQuery .= "$propName ENUM(";
1073:                         $enumVals = $this->BO->getPropObject($propName)->getOptions();
1074:                         foreach($enumVals as $val) {
1075:                             $sqlQuery .= "'".$val."',";
1076:                         }
1077:                         $sqlQuery = rtrim($sqlQuery, ",");
1078:                         $sqlQuery .= "),";
1079:                     break;
1080:                     case "DENUM":
1081:                         $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1082:                         $sqlQuery .= "$propName INT(11) ZEROFILL,";
1083:                     break;
1084:                     case "RELATION":                        
1085:                         $sqlQuery .= "$propName INT(11) ZEROFILL UNSIGNED,";
1086:                     break;
1087:                     default:
1088:                         $sqlQuery .= "";
1089:                     break;
1090:                 }
1091:             }           
1092:         }
1093:         if($this->BO->isTableOverloaded())
1094:             $sqlQuery .= "classname VARCHAR(100),";
1095:         
1096:         $sqlQuery .= "PRIMARY KEY (OID)) ENGINE=InnoDB;";
1097:         
1098:         $this->BO->setLastQuery($sqlQuery);
1099:         
1100:         if(!$result = AlphaDAOProviderMySQL::getConnection()->query($sqlQuery)) {
1101:             throw new AlphaException('Failed to create the table ['.$this->BO->getTableName().'] for the class ['.get_class($this->BO).'], database error is ['.AlphaDAOProviderMySQL::getConnection()->error.']');
1102:             self::$logger->debug('<<makeTable');
1103:         }
1104:                 
1105:         // check the table indexes if any additional ones required
1106:         $this->checkIndexes();
1107:         
1108:         self::$logger->debug('<<makeTable');
1109:     }
1110: 
1111:     /**
1112:      * (non-PHPdoc)
1113:      * @see alpha/model/AlphaDAOProviderInterface::makeHistoryTable()
1114:      */
1115:     public function makeHistoryTable() {
1116:         self::$logger->debug('>>makeHistoryTable()');
1117: 
1118:         $sqlQuery = "CREATE TABLE ".$this->BO->getTableName()."_history (OID INT(11) ZEROFILL NOT NULL,";
1119: 
1120:         // get the class attributes
1121:         $reflection = new ReflectionClass(get_class($this->BO));
1122:         $properties = $reflection->getProperties();
1123: 
1124:         foreach($properties as $propObj) {
1125:             $propName = $propObj->name;
1126: 
1127:             if(!in_array($propName, $this->BO->getTransientAttributes()) && $propName != "OID") {
1128:                 $propClass = get_class($this->BO->getPropObject($propName));
1129: 
1130:                 switch (strtoupper($propClass)) {
1131:                     case "INTEGER":
1132:                         // special properties for RelationLookup OIDs
1133:                         if($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID'))
1134:                             $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize().") ZEROFILL NOT NULL,";
1135:                         else
1136:                             $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize()."),";
1137:                     break;
1138:                     case "DOUBLE":
1139:                         $sqlQuery .= "$propName DOUBLE(".$this->BO->getPropObject($propName)->getSize(true)."),";
1140:                     break;
1141:                     case "STRING":
1142:                         $sqlQuery .= "$propName VARCHAR(".$this->BO->getPropObject($propName)->getSize()."),";
1143:                     break;
1144:                     case "TEXT":
1145:                         $sqlQuery .= "$propName TEXT,";
1146:                     break;
1147:                     case "BOOLEAN":
1148:                         $sqlQuery .= "$propName CHAR(1) DEFAULT '0',";
1149:                     break;
1150:                     case "DATE":
1151:                         $sqlQuery .= "$propName DATE,";
1152:                     break;
1153:                     case "TIMESTAMP":
1154:                         $sqlQuery .= "$propName DATETIME,";
1155:                     break;
1156:                     case "ENUM":
1157:                         $sqlQuery .= "$propName ENUM(";
1158: 
1159:                         $enumVals = $this->BO->getPropObject($propName)->getOptions();
1160: 
1161:                         foreach($enumVals as $val) {
1162:                             $sqlQuery .= "'".$val."',";
1163:                         }
1164: 
1165:                         $sqlQuery = rtrim($sqlQuery, ",");
1166:                         $sqlQuery .= "),";
1167:                     break;
1168:                     case "DENUM":
1169:                         $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1170:                         $sqlQuery .= "$propName INT(11) ZEROFILL,";
1171:                     break;
1172:                     case "RELATION":
1173:                         $sqlQuery .= "$propName INT(11) ZEROFILL UNSIGNED,";
1174:                     break;
1175:                     default:
1176:                         $sqlQuery .= "";
1177:                     break;
1178:                 }
1179:             }
1180:         }
1181: 
1182:         if($this->BO->isTableOverloaded())
1183:             $sqlQuery .= "classname VARCHAR(100),";
1184: 
1185:         $sqlQuery .= "PRIMARY KEY (OID, version_num)) ENGINE=MyISAM;";
1186: 
1187:         $this->BO->setLastQuery($sqlQuery);
1188: 
1189:         if(!$result = AlphaDAOProviderMySQL::getConnection()->query($sqlQuery)) {
1190:             throw new AlphaException('Failed to create the table ['.$this->BO->getTableName().'_history] for the class ['.get_class($this->BO).'], database error is ['.AlphaDAOProviderMySQL::getConnection()->error.']');
1191:             self::$logger->debug('<<makeHistoryTable');
1192:         }
1193: 
1194:         self::$logger->debug('<<makeHistoryTable');
1195:     }
1196: 
1197:     /**
1198:      * (non-PHPdoc)
1199:      * @see alpha/model/AlphaDAOProviderInterface::rebuildTable()
1200:      */
1201:     public function rebuildTable() {
1202:         self::$logger->debug('>>rebuildTable()');
1203:         
1204:         $sqlQuery = 'DROP TABLE IF EXISTS '.$this->BO->getTableName().';';
1205: 
1206:         $this->BO->setLastQuery($sqlQuery);
1207: 
1208:         if(!$result = AlphaDAOProviderMySQL::getConnection()->query($sqlQuery)) {
1209:             throw new AlphaException('Failed to drop the table ['.$this->BO->getTableName().'] for the class ['.get_class($this->BO).'], database error is ['.AlphaDAOProviderMySQL::getConnection()->error.']');
1210:             self::$logger->debug('<<rebuildTable');
1211:         }
1212: 
1213:         $this->BO->makeTable();
1214:         
1215:         self::$logger->debug('<<rebuildTable');
1216:     }
1217:     
1218:     /**
1219:      * (non-PHPdoc)
1220:      * @see alpha/model/AlphaDAOProviderInterface::dropTable()
1221:      */
1222:     public function dropTable($tableName=null) {
1223:         self::$logger->debug('>>dropTable()');
1224:         
1225:         if($tableName == null)
1226:             $tableName = $this->BO->getTableName();
1227:             
1228:         $sqlQuery = 'DROP TABLE IF EXISTS '.$tableName.';';
1229: 
1230:         $this->BO->setLastQuery($sqlQuery);
1231: 
1232:         if(!$result = AlphaDAOProviderMySQL::getConnection()->query($sqlQuery)) {
1233:             throw new AlphaException('Failed to drop the table ['.$tableName.'] for the class ['.get_class($this->BO).'], query is ['.$this->BO->getLastQuery().']');
1234:             self::$logger->debug('<<dropTable');
1235:         }
1236:         
1237:         self::$logger->debug('<<dropTable');
1238:     }
1239: 
1240:     /**
1241:      * (non-PHPdoc)
1242:      * @see alpha/model/AlphaDAOProviderInterface::addProperty()
1243:      */
1244:     public function addProperty($propName) {
1245:         self::$logger->debug('>>addProperty(propName=['.$propName.'])');
1246:         
1247:         $sqlQuery = 'ALTER TABLE '.$this->BO->getTableName().' ADD ';
1248:         
1249:         if($this->isTableOverloaded() && $propName == 'classname') {
1250:             $sqlQuery .= 'classname VARCHAR(100)';
1251:         }else{
1252:             if(!in_array($propName, $this->BO->getDefaultAttributes()) && !in_array($propName, $this->BO->getTransientAttributes())) {
1253:                 $propClass = get_class($this->BO->getPropObject($propName));
1254:     
1255:                 switch (strtoupper($propClass)) {
1256:                     case 'INTEGER':
1257:                         $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize().")";
1258:                     break;
1259:                     case 'DOUBLE':
1260:                         $sqlQuery .= "$propName DOUBLE(".$this->BO->getPropObject($propName)->getSize(true).")";
1261:                     break;
1262:                     case 'STRING':
1263:                         $sqlQuery .= "$propName VARCHAR(".$this->BO->getPropObject($propName)->getSize().")";
1264:                     break;
1265:                     case 'SEQUENCE':
1266:                         $sqlQuery .= "$propName VARCHAR(".$this->BO->getPropObject($propName)->getSize().")";
1267:                     break;
1268:                     case 'TEXT':
1269:                         $sqlQuery .= "$propName TEXT";
1270:                     break;
1271:                     case 'BOOLEAN':
1272:                         $sqlQuery .= "$propName CHAR(1) DEFAULT '0'";
1273:                     break;
1274:                     case 'DATE':
1275:                         $sqlQuery .= "$propName DATE";
1276:                     break;
1277:                     case 'TIMESTAMP':
1278:                         $sqlQuery .= "$propName DATETIME";
1279:                     break;
1280:                     case 'ENUM':
1281:                         $sqlQuery .= "$propName ENUM(";
1282:                         $enumVals = $this->BO->getPropObject($propName)->getOptions();
1283:                         foreach($enumVals as $val) {
1284:                             $sqlQuery .= "'".$val."',";
1285:                         }
1286:                         $sqlQuery = rtrim($sqlQuery, ",");
1287:                         $sqlQuery .= ')';
1288:                     break;
1289:                     case 'DENUM':
1290:                         $tmp = new DEnum(get_class($this->BO).'::'.$propName);                      
1291:                         $tmp->save();
1292:                         $sqlQuery .= "$propName INT(11) ZEROFILL";
1293:                     break;
1294:                     case 'RELATION':                        
1295:                         $sqlQuery .= "$propName INT(11) ZEROFILL UNSIGNED";
1296:                     break;
1297:                     default:
1298:                         $sqlQuery .= '';
1299:                     break;
1300:                 }
1301:             }
1302:         }
1303: 
1304:         $this->BO->setLastQuery($sqlQuery);
1305: 
1306:         if(!$result = self::getConnection()->query($sqlQuery)) {
1307:             throw new AlphaException('Failed to add the new attribute ['.$propName.'] to the table ['.$this->BO->getTableName().'], query is ['.$this->BO->getLastQuery().']');
1308:             self::$logger->debug('<<addProperty');
1309:         }else{
1310:             self::$logger->info('Successfully added the ['.$propName.'] column onto the ['.$this->BO->getTableName().'] table for the class ['.get_class($this->BO).']');
1311:         }
1312: 
1313:         self::$logger->debug('<<addProperty');
1314:     }
1315: 
1316:     /**
1317:      * (non-PHPdoc)
1318:      * @see alpha/model/AlphaDAOProviderInterface::getMAX()
1319:      */
1320:     public function getMAX() {
1321:         self::$logger->debug('>>getMAX()');
1322:         
1323:         $sqlQuery = 'SELECT MAX(OID) AS max_OID FROM '.$this->BO->getTableName();
1324: 
1325:         $this->BO->setLastQuery($sqlQuery);
1326:         
1327:         try {
1328:             $result = $this->BO->query($sqlQuery);
1329:     
1330:             $row = $result[0];
1331:     
1332:             if (isset($row['max_OID'])) {   
1333:                 self::$logger->debug('<<getMAX ['.$row['max_OID'].']');
1334:                 return $row['max_OID'];
1335:             }else{          
1336:                 throw new AlphaException('Failed to get the MAX ID for the class ['.get_class($this->BO).'] from the table ['.$this->BO->getTableName().'], query is ['.$this->BO->getLastQuery().']');
1337:             }
1338:         }catch (Exception $e) {
1339:             throw new AlphaException($e->getMessage());
1340:             self::$logger->debug('<<getMAX [0]');
1341:             return 0;
1342:         }
1343:     }
1344:     
1345:     /**
1346:      * (non-PHPdoc)
1347:      * @see alpha/model/AlphaDAOProviderInterface::getCount()
1348:      */
1349:     public function getCount($attributes=array(), $values=array()) {
1350:         self::$logger->debug('>>getCount(attributes=['.var_export($attributes, true).'], values=['.var_export($values, true).'])');
1351:         
1352:         if($this->BO->isTableOverloaded())
1353:             $whereClause = ' WHERE classname = \''.get_class($this->BO).'\' AND';
1354:         else
1355:             $whereClause = ' WHERE';
1356:             
1357:         $count = count($attributes);
1358:         
1359:         for($i = 0; $i < $count; $i++) {
1360:             $whereClause .= ' '.$attributes[$i].' = \''.$values[$i].'\' AND';
1361:             self::$logger->debug($whereClause);
1362:         }
1363:         // remove the last " AND"
1364:         $whereClause = substr($whereClause, 0, -4);
1365:         
1366:         if($whereClause != ' WHERE')
1367:             $sqlQuery = 'SELECT COUNT(OID) AS class_count FROM '.$this->BO->getTableName().$whereClause;
1368:         else
1369:             $sqlQuery = 'SELECT COUNT(OID) AS class_count FROM '.$this->BO->getTableName();
1370: 
1371:         $this->BO->setLastQuery($sqlQuery);
1372:         
1373:         $result = self::getConnection()->query($sqlQuery);
1374: 
1375:         if ($result) {              
1376:             $row = $result->fetch_array(MYSQLI_ASSOC);
1377: 
1378:             self::$logger->debug('<<getCount ['.$row['class_count'].']');
1379:             return $row['class_count'];
1380:         }else{
1381:             throw new AlphaException('Failed to get the count for the class ['.get_class($this->BO).'] from the table ['.$this->BO->getTableName().'], query is ['.$this->BO->getLastQuery().']');          
1382:             self::$logger->debug('<<getCount [0]');
1383:             return 0;
1384:         }
1385:     }
1386: 
1387:     /**
1388:          * (non-PHPdoc)
1389:          * @see alpha/model/AlphaDAOProviderInterface::getHistoryCount()
1390:          */
1391:     public function getHistoryCount() {
1392:         self::$logger->debug('>>getHistoryCount()');
1393: 
1394:         if(!$this->BO->getMaintainHistory())
1395:             throw new AlphaException('getHistoryCount method called on a DAO where no history is maintained!');
1396: 
1397:         $sqlQuery = 'SELECT COUNT(OID) AS object_count FROM '.$this->BO->getTableName().'_history WHERE OID='.$this->BO->getOID();
1398: 
1399:                 $this->BO->setLastQuery($sqlQuery);
1400: 
1401:                 $result = self::getConnection()->query($sqlQuery);
1402: 
1403:                 if ($result) {
1404:                         $row = $result->fetch_array(MYSQLI_ASSOC);
1405: 
1406:                         self::$logger->debug('<<getHistoryCount ['.$row['object_count'].']');
1407:                         return $row['object_count'];
1408:                 }else{
1409:                         throw new AlphaException('Failed to get the history count for the business object ['.$this->BO->getOID().'] from the table ['.$this->BO->getTableName().'_history], query is ['.$this->BO->getLastQuery().']');
1410:                         self::$logger->debug('<<getHistoryCount [0]');
1411:                         return 0;
1412:                 }
1413:     }
1414: 
1415:     /**
1416:      * (non-PHPdoc)
1417:      * @see alpha/model/AlphaDAOProviderInterface::setEnumOptions()
1418:      * @since 1.1
1419:      */
1420:     public function setEnumOptions() {
1421:         self::$logger->debug('>>setEnumOptions()');
1422:         
1423:         // get the class attributes
1424:         $reflection = new ReflectionClass(get_class($this->BO));
1425:         $properties = $reflection->getProperties();
1426:         
1427:         // flag for any database errors
1428:         $dbError = false;
1429:         
1430:         foreach($properties as $propObj) {
1431:             $propName = $propObj->name;
1432:             if(!in_array($propName, $this->BO->getDefaultAttributes()) && !in_array($propName, $this->BO->getTransientAttributes())) {
1433:                 $propClass = get_class($this->BO->getPropObject($propName));
1434:                 if ($propClass == 'Enum') {
1435:                     $sqlQuery = "SHOW COLUMNS FROM ".$this->BO->getTableName()." LIKE '$propName'";
1436:                     
1437:                     $this->BO->setLastQuery($sqlQuery);
1438:                     
1439:                     $result = AlphaDAOProviderMySQL::getConnection()->query($sqlQuery);
1440:                     
1441:                     if ($result) {                          
1442:                         $row = $result->fetch_array(MYSQLI_NUM);
1443:                         $options = explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2",$row[1]));
1444:                         
1445:                         $this->BO->getPropObject($propName)->setOptions($options);                      
1446:                     }else{
1447:                         $dbError = true;
1448:                         break;
1449:                     }
1450:                 }
1451:             }
1452:         }
1453:         
1454:         if (!$dbError) {
1455:             if(method_exists($this, 'after_setEnumOptions_callback'))
1456:                 $this->after_setEnumOptions_callback();
1457:         }else{          
1458:             throw new AlphaException('Failed to load enum options correctly for object instance of class ['.get_class($this).']');
1459:         }
1460:         self::$logger->debug('<<setEnumOptions');
1461:     }
1462:     
1463:     /**
1464:      * (non-PHPdoc)
1465:      * @see alpha/model/AlphaDAOProviderInterface::checkTableExists()
1466:      */
1467:     public function checkTableExists($checkHistoryTable = false) {
1468:         self::$logger->debug('>>checkTableExists(checkHistoryTable=['.$checkHistoryTable.'])');
1469:         
1470:         global $config;
1471:                 
1472:         $tableExists = false;
1473:         
1474:         $sqlQuery = 'SHOW TABLES;';
1475:         $this->BO->setLastQuery($sqlQuery);
1476:         
1477:         $result = self::getConnection()->query($sqlQuery);
1478: 
1479:         $tableName = ($checkHistoryTable ? $this->BO->getTableName().'_history' : $this->BO->getTableName());
1480:         
1481:         while ($row = $result->fetch_array(MYSQLI_NUM)) {
1482:                 if (strtolower($row[0]) == strtolower($tableName))
1483:                     $tableExists = true;
1484:         }       
1485:         
1486:         if ($result) {          
1487:             self::$logger->debug('<<checkTableExists ['.$tableExists.']');
1488:             return $tableExists;
1489:         }else{          
1490:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getConnection()->error.']');
1491:             self::$logger->debug('<<checkTableExists [false]');
1492:             return false;
1493:         }
1494:     }
1495: 
1496:     /**
1497:      * (non-PHPdoc)
1498:      * @see alpha/model/AlphaDAOProviderInterface::checkBOTableExists()
1499:      */
1500:     public static function checkBOTableExists($BOClassName, $checkHistoryTable = false) {
1501:         if(self::$logger == null)
1502:             self::$logger = new Logger('AlphaDAOProvidermySQL');
1503:         self::$logger->debug('>>checkBOTableExists(BOClassName=['.$BOClassName.'], checkHistoryTable=['.$checkHistoryTable.'])');
1504:         
1505:         eval('$tableName = '.$BOClassName.'::TABLE_NAME;');
1506: 
1507:         if(empty($tableName))
1508:             $tableName = substr($BOClassName, 0, strpos($BOClassName, '_'));
1509: 
1510:         if($checkHistoryTable)
1511:                         $tableName .= '_history';
1512:                 
1513:         $tableExists = false;
1514:         
1515:         $sqlQuery = 'SHOW TABLES;';
1516:         
1517:         $result = self::getConnection()->query($sqlQuery);
1518:         
1519:         while ($row = $result->fetch_array(MYSQLI_NUM)) {           
1520:                 if ($row[0] == $tableName)
1521:                     $tableExists = true;
1522:         }           
1523:         
1524:         if ($result) {          
1525:             self::$logger->debug('<<checkBOTableExists ['.($tableExists ? 'true' : 'false').']');
1526:             return $tableExists;
1527:         }else{          
1528:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getConnection()->error.']');
1529:             self::$logger->debug('<<checkBOTableExists [false]');
1530:             return false;
1531:         }
1532:     }
1533:     
1534:     /**
1535:      * (non-PHPdoc)
1536:      * @see alpha/model/AlphaDAOProviderInterface::checkTableNeedsUpdate()
1537:      */
1538:     public function checkTableNeedsUpdate() {
1539:         self::$logger->debug('>>checkTableNeedsUpdate()');
1540:                 
1541:         $updateRequired = false;
1542:             
1543:         $matchCount = 0;
1544:             
1545:         $query = 'SHOW COLUMNS FROM '.$this->BO->getTableName();
1546:         $result = self::getConnection()->query($query);
1547:         $this->BO->setLastQuery($query);
1548:             
1549:         // get the class attributes
1550:         $reflection = new ReflectionClass(get_class($this->BO));
1551:         $properties = $reflection->getProperties();
1552:             
1553:         foreach($properties as $propObj) {
1554:             $propName = $propObj->name;
1555:             if (!in_array($propName, $this->BO->getTransientAttributes())) {
1556:                 
1557:                 $foundMatch = false;    
1558:                     
1559:                 while ($row = $result->fetch_array(MYSQLI_ASSOC)) {                     
1560:                     if ($propName == $row['Field']) {
1561:                         $foundMatch = true;
1562:                         break;
1563:                     }
1564:                 }
1565:                     
1566:                 if(!$foundMatch)
1567:                     $matchCount--;                      
1568:                     
1569:                 $result->data_seek(0);                  
1570:             }
1571:         }
1572:             
1573:         // check for the "classname" field in overloaded tables
1574:         if($this->BO->isTableOverloaded()) {
1575:             $foundMatch = false;
1576:             
1577:             while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
1578:                 if ('classname' == $row['Field']) {
1579:                     $foundMatch = true;
1580:                     break;
1581:                 }
1582:             }
1583:             if(!$foundMatch)                        
1584:                 $matchCount--;
1585:         }
1586:             
1587:         if ($matchCount != 0)
1588:             $updateRequired = true;
1589:             
1590:         if ($result) {
1591:             // check the table indexes
1592:             try {
1593:                 $this->checkIndexes();
1594:             }catch (AlphaException $ae) {
1595:                 self::$logger->warn("Error while checking database indexes:\n\n".$ae->getMessage());
1596:             }
1597:                 
1598:             self::$logger->debug('<<checkTableNeedsUpdate ['.$updateRequired.']');
1599:             return $updateRequired;
1600:         }else{              
1601:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getConnection()->error.']');
1602:             self::$logger->debug('<<checkTableNeedsUpdate [false]');
1603:             return false;
1604:         }
1605:     }
1606:     
1607:     /**
1608:      * (non-PHPdoc)
1609:      * @see alpha/model/AlphaDAOProviderInterface::findMissingFields()
1610:      */
1611:     public function findMissingFields() {
1612:         self::$logger->debug('>>findMissingFields()');
1613:         
1614:         $missingFields = array();           
1615:         $matchCount = 0;
1616:             
1617:         $sqlQuery = 'SHOW COLUMNS FROM '.$this->BO->getTableName();
1618:         
1619:         $result = self::getConnection()->query($sqlQuery);
1620:         
1621:         $this->BO->setLastQuery($sqlQuery);
1622:             
1623:         // get the class attributes
1624:         $reflection = new ReflectionClass(get_class($this->BO));
1625:         $properties = $reflection->getProperties();
1626:         
1627:         foreach($properties as $propObj) {
1628:             $propName = $propObj->name;
1629:             if (!in_array($propName, $this->BO->getTransientAttributes())) {
1630:                 while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
1631:                     if ($propName == $row['Field']) {
1632:                         $matchCount++;                      
1633:                         break;
1634:                     }
1635:                 }
1636:                 $result->data_seek(0);
1637:             }else{
1638:                 $matchCount++;
1639:             }
1640:             
1641:             if($matchCount==0) {                    
1642:                 array_push($missingFields, $propName);
1643:             }else{
1644:                 $matchCount = 0;
1645:             }
1646:         }
1647:         
1648:         // check for the "classname" field in overloaded tables
1649:         if($this->BO->isTableOverloaded()) {
1650:             $foundMatch = false;
1651:             
1652:             while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
1653:                 if ('classname' == $row['Field']) {
1654:                     $foundMatch = true;
1655:                     break;
1656:                 }
1657:             }
1658:             if(!$foundMatch)                        
1659:                 array_push($missingFields, 'classname');
1660:         }
1661:         
1662:         if (!$result) {
1663:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getConnection()->error.']');
1664:         }
1665:         
1666:         self::$logger->debug('<<findMissingFields ['.var_export($missingFields, true).']');
1667:         return $missingFields;  
1668:     }
1669:     
1670:     /**
1671:      * (non-PHPdoc)
1672:      * @see alpha/model/AlphaDAOProviderInterface::getIndexes()
1673:      */
1674:     public function getIndexes() {
1675:         self::$logger->debug('>>getIndexes()');
1676:         
1677:         $query = 'SHOW INDEX FROM '.$this->BO->getTableName();
1678:         
1679:         $result = self::getConnection()->query($query);
1680:         
1681:         $this->BO->setLastQuery($query);
1682:         
1683:         $indexNames = array();
1684:         
1685:         if (!$result) {
1686:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getConnection()->error.']');
1687:         }else{
1688:             while ($row = $result->fetch_array(MYSQLI_ASSOC)) {             
1689:                 array_push($indexNames, $row['Key_name']);
1690:             }
1691:         }
1692:         
1693:         self::$logger->debug('<<getIndexes');
1694:         return $indexNames;
1695:     }
1696:     
1697:     /**
1698:      * Checks to see if all of the indexes are in place for the BO's table, creates those that are missing.
1699:      * 
1700:      * @since 1.1
1701:      */
1702:     private function checkIndexes() {
1703:         self::$logger->debug('>>checkIndexes()');       
1704:         
1705:         $indexNames = $this->getIndexes();
1706:         
1707:         // process unique keys
1708:         foreach($this->BO->getUniqueAttributes() as $prop) {
1709:             // check for composite indexes
1710:             if(strpos($prop, '+')) {
1711:                 $attributes = explode('+', $prop);
1712:                 
1713:                 $index_exists = false;
1714:                 foreach ($indexNames as $index) {
1715:                     if ($attributes[0].'_'.$attributes[1].'_unq_idx' == $index) {
1716:                         $index_exists = true;
1717:                     }
1718:                     if(count($attributes) == 3) {
1719:                         if ($attributes[0].'_'.$attributes[1].'_'.$attributes[2].'_unq_idx' == $index) {
1720:                             $index_exists = true;
1721:                         }
1722:                     }
1723:                 }
1724:     
1725:                 if(!$index_exists) {
1726:                     if(count($attributes) == 3)
1727:                         $this->BO->createUniqueIndex($attributes[0], $attributes[1], $attributes[2]);
1728:                     else
1729:                         $this->BO->createUniqueIndex($attributes[0], $attributes[1]);
1730:                 }
1731:             }else{
1732:                 $index_exists = false;
1733:                 foreach ($indexNames as $index) {                   
1734:                     if ($prop.'_unq_idx' == $index) {
1735:                         $index_exists = true;
1736:                     }
1737:                 }
1738:     
1739:                 if(!$index_exists)
1740:                     $this->createUniqueIndex($prop);
1741:             }
1742:         }       
1743:         
1744:         // process foreign-key indexes
1745:         // get the class attributes
1746:         $reflection = new ReflectionClass(get_class($this->BO));
1747:         $properties = $reflection->getProperties();
1748:         
1749:         foreach($properties as $propObj) {
1750:             $propName = $propObj->name;         
1751:             $prop = $this->BO->getPropObject($propName);
1752:             if($prop instanceof Relation) {
1753:                                 
1754:                 if($prop->getRelationType() == 'MANY-TO-ONE') {
1755:                     $indexExists = false;
1756:                     foreach ($indexNames as $index) {
1757:                         if ($propName.'_fk_idx' == $index) {
1758:                             $indexExists = true;
1759:                         }
1760:                     }
1761:         
1762:                     if(!$indexExists) {
1763:                         $this->createForeignIndex($propName, $prop->getRelatedClass(), $prop->getRelatedClassField());
1764:                     }
1765:                 }
1766:                 
1767:                 if($prop->getRelationType() == 'MANY-TO-MANY') {                    
1768:                     $lookup = $prop->getLookup();
1769:                     
1770:                     if($lookup != null) {
1771:                         try {                   
1772:                             $lookupIndexNames = $lookup->getIndexes();
1773:                             
1774:                             // handle index check/creation on left side of Relation
1775:                             $indexExists = false;
1776:                             foreach ($lookupIndexNames as $index) {
1777:                                 if ('leftID_fk_idx' == $index) {
1778:                                     $indexExists = true;
1779:                                 }
1780:                             }
1781:                             
1782:                             if(!$indexExists) {
1783:                                 $lookup->createForeignIndex('leftID', $prop->getRelatedClass('left'), 'OID');
1784:                             }
1785:                             
1786:                             // handle index check/creation on right side of Relation
1787:                             $indexExists = false;
1788:                             foreach ($lookupIndexNames as $index) {
1789:                                 if ('rightID_fk_idx' == $index) {
1790:                                     $indexExists = true;
1791:                                 }
1792:                             }
1793:                             
1794:                             if(!$indexExists) {
1795:                                 $lookup->createForeignIndex('rightID', $prop->getRelatedClass('right'), 'OID');
1796:                             }
1797:                         }catch(AlphaException $e) {
1798:                             self::$logger->error($e->getMessage());
1799:                         }
1800:                     }
1801:                 }
1802:                 
1803:             }
1804:         }
1805:         
1806:         self::$logger->debug('<<checkIndexes');
1807:     }
1808:     
1809:     /**
1810:      * (non-PHPdoc)
1811:      * @see alpha/model/AlphaDAOProviderInterface::createForeignIndex()
1812:      */
1813:     public function createForeignIndex($attributeName, $relatedClass, $relatedClassAttribute) {
1814:         self::$logger->debug('>>createForeignIndex(attributeName=['.$attributeName.'], relatedClass=['.$relatedClass.'], relatedClassAttribute=['.$relatedClassAttribute.']');
1815:         
1816:         AlphaDAO::loadClassDef($relatedClass);      
1817:         $relatedBO = new $relatedClass;     
1818:         $tableName = $relatedBO->getTableName();
1819:         
1820:         $result = false;
1821:         
1822:         if(self::checkBOTableExists(ucfirst($tableName).'Object')) {
1823:             $sqlQuery = '';
1824:             
1825:             if($attributeName == 'leftID')
1826:                 $sqlQuery = 'ALTER TABLE '.$this->BO->getTableName().' ADD INDEX leftID_fk_idx (leftID);';
1827:             if($attributeName == 'rightID')
1828:                 $sqlQuery = 'ALTER TABLE '.$this->BO->getTableName().' ADD INDEX rightID_fk_idx (rightID);';
1829:                 
1830:             if(!empty($sqlQuery)) {
1831:                 $this->BO->setLastQuery($sqlQuery);
1832:     
1833:                 $result = self::getConnection()->query($sqlQuery);
1834:     
1835:                 if (!$result) {
1836:                     throw new FailedIndexCreateException('Failed to create an index on ['.$this->BO->getTableName().'], error is ['.self::getConnection()->error.'], query ['.$this->BO->getLastQuery().']');
1837:                 }
1838:             }
1839: 
1840:             $sqlQuery = 'ALTER TABLE '.$this->BO->getTableName().' ADD FOREIGN KEY '.$attributeName.'_fk_idx ('.$attributeName.') REFERENCES '.$tableName.' ('.$relatedClassAttribute.') ON DELETE SET NULL;';
1841:             
1842:             $this->BO->setLastQuery($sqlQuery); 
1843:             $result = self::getConnection()->query($sqlQuery);
1844:         }
1845:     
1846:         if ($result) {
1847:             self::$logger->debug('Successfully created the foreign key index ['.$attributeName.'_fk_idx]');
1848:         }else{          
1849:             throw new FailedIndexCreateException('Failed to create the index ['.$attributeName.'_fk_idx] on ['.$this->BO->getTableName().'], error is ['.self::getConnection()->error.'], query ['.$this->BO->getLastQuery().']');
1850:         }
1851:         
1852:         self::$logger->debug('<<createForeignIndex');
1853:     }
1854:     
1855:     /**
1856:      * (non-PHPdoc)
1857:      * @see alpha/model/AlphaDAOProviderInterface::createUniqueIndex()
1858:      */
1859:     public function createUniqueIndex($attribute1Name, $attribute2Name = '', $attribute3Name = '') {
1860:         self::$logger->debug('>>createUniqueIndex(attribute1Name=['.$attribute1Name.'], attribute2Name=['.$attribute2Name.'], attribute3Name=['.$attribute3Name.'])');
1861:         
1862:         if($attribute2Name != '' && $attribute3Name != '')
1863:             $sqlQuery = 'CREATE UNIQUE INDEX '.$attribute1Name.'_'.$attribute2Name.'_'.$attribute3Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.','.$attribute2Name.','.$attribute3Name.');';
1864:         
1865:         if($attribute2Name != '' && $attribute3Name == '')
1866:             $sqlQuery = 'CREATE UNIQUE INDEX '.$attribute1Name.'_'.$attribute2Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.','.$attribute2Name.');';
1867:             
1868:         if($attribute2Name == '' && $attribute3Name == '')
1869:             $sqlQuery = 'CREATE UNIQUE INDEX '.$attribute1Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.');';
1870:         
1871:         $this->BO->setLastQuery($sqlQuery);
1872:         
1873:         $result = self::getConnection()->query($sqlQuery);
1874:         
1875:         if ($result) {
1876:             self::$logger->debug('Successfully created the unique index on ['.$this->BO->getTableName().']');
1877:         }else{
1878:             throw new FailedIndexCreateException('Failed to create the unique index on ['.$this->BO->getTableName().'], error is ['.self::getConnection()->error.']');
1879:         }
1880:         
1881:         self::$logger->debug('<<createUniqueIndex');
1882:     }
1883:     
1884:     /**
1885:      * (non-PHPdoc)
1886:      * @see alpha/model/AlphaDAOProviderInterface::reload()
1887:      */
1888:     public function reload() {
1889:         self::$logger->debug('>>reload()');
1890:         
1891:         if(!$this->isTransient()) {
1892:             $this->load($this->getOID());           
1893:         }else{
1894:             throw new AlphaException('Cannot reload transient object from database!');
1895:         }       
1896:         self::$logger->debug('<<reload');
1897:     }
1898:     
1899:     /**
1900:      * (non-PHPdoc)
1901:      * @see alpha/model/AlphaDAOProviderInterface::checkRecordExists()
1902:      */
1903:     public function checkRecordExists($OID) {
1904:         self::$logger->debug('>>checkRecordExists(OID=['.$OID.'])');
1905:         
1906:         $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE OID = ?;';
1907: 
1908:         $this->BO->setLastQuery($sqlQuery);
1909:         
1910:         $stmt = self::getConnection()->stmt_init();
1911:         
1912:         if($stmt->prepare($sqlQuery)) {
1913:             $stmt->bind_param('i', $OID);
1914:             
1915:             $stmt->execute();
1916:             
1917:             $result = $this->bindResult($stmt);
1918:                 
1919:             $stmt->close();
1920: 
1921:             if ($result) {                  
1922:                 if(count($result) > 0) {
1923:                     self::$logger->debug('<<checkRecordExists [true]');
1924:                     return true;
1925:                 }else{
1926:                     self::$logger->debug('<<checkRecordExists [false]');
1927:                     return false;
1928:                 }
1929:             }else{
1930:                 throw new AlphaException('Failed to check for the record ['.$OID.'] on the class ['.get_class($this->BO).'] from the table ['.$this->BO->getTableName().'], query is ['.$this->BO->getLastQuery().']');         
1931:                 self::$logger->debug('<<checkRecordExists [false]');
1932:                 return false;
1933:             }
1934:         }else{
1935:             throw new AlphaException('Failed to check for the record ['.$OID.'] on the class ['.get_class($this->BO).'] from the table ['.$this->BO->getTableName().'], query is ['.$this->BO->getLastQuery().']');         
1936:             self::$logger->debug('<<checkRecordExists [false]');
1937:             return false;
1938:         }
1939:     }
1940:     
1941:     /**
1942:      * (non-PHPdoc)
1943:      * @see alpha/model/AlphaDAOProviderInterface::isTableOverloaded()
1944:      */
1945:     public function isTableOverloaded() {
1946:         self::$logger->debug('>>isTableOverloaded()');
1947:         
1948:         $classname = get_class($this->BO);
1949:         $tablename = ucfirst($this->BO->getTableName()).'Object';
1950:         
1951:         // use reflection to check to see if we are dealing with a persistent type (e.g. DEnum) which are never overloaded
1952:         $reflection = new ReflectionClass($classname);
1953:         $implementedInterfaces = $reflection->getInterfaces();
1954:         
1955:         foreach ($implementedInterfaces as $interface) {
1956:             if ($interface->name == 'AlphaTypeInterface') {
1957:                 self::$logger->debug('<<isTableOverloaded [false]');
1958:                 return false;
1959:             }
1960:         }
1961:         
1962:         if($classname != $tablename) {
1963:             // loop over all BOs to see if there is one using the same table as this BO
1964:             
1965:             $BOclasses = AlphaDAO::getBOClassNames();
1966:             
1967:             foreach($BOclasses as $BOclassName) {
1968:                 if($tablename == $BOclassName) {
1969:                     self::$logger->debug('<<isTableOverloaded [true]');
1970:                     return true;
1971:                 }
1972:             }
1973:             throw new BadBOTableNameException('The table name ['.$tablename.'] for the class ['.$classname.'] is invalid as it does not match a BO definition in the system!');
1974:             self::$logger->debug('<<isTableOverloaded [false]');
1975:             return false;
1976:         }else{
1977:             // check to see if there is already a "classname" column in the database for this BO
1978:             
1979:             $query = 'SHOW COLUMNS FROM '.$this->BO->getTableName();
1980:             
1981:             $result = self::getConnection()->query($query);
1982:             
1983:             if($result) {
1984:                 while ($row = $result->fetch_array(MYSQLI_ASSOC)) {                     
1985:                     if ('classname' == $row['Field']) {
1986:                         self::$logger->debug('<<isTableOverloaded [true]');
1987:                         return true;
1988:                     }
1989:                 }
1990:             }else{
1991:                 self::$logger->warn('Error during show columns ['.self::getConnection()->error.']');
1992:             }
1993:             
1994:             self::$logger->debug('<<isTableOverloaded [false]');
1995:             return false;
1996:         }
1997:     }
1998:     
1999:     /**
2000:      * (non-PHPdoc)
2001:      * @see alpha/model/AlphaDAOProviderInterface::begin()
2002:      */
2003:     public static function begin() {
2004:         if(self::$logger == null)
2005:             self::$logger = new Logger('AlphaDAOProviderMySQL');
2006:         self::$logger->debug('>>begin()');      
2007:         
2008:         if (!self::getConnection()->autocommit(false))
2009:             throw new AlphaException('Error beginning a new transaction, error is ['.self::getConnection()->error.']');
2010:         
2011:         self::$logger->debug('<<begin');
2012:     }
2013:     
2014:     /**
2015:      * (non-PHPdoc)
2016:      * @see alpha/model/AlphaDAOProviderInterface::commit()
2017:      */
2018:     public static function commit() {
2019:         if(self::$logger == null)
2020:             self::$logger = new Logger('AlphaDAOProviderMySQL');
2021:         self::$logger->debug('>>commit()');
2022:         
2023:         if (!self::getConnection()->commit())
2024:             throw new FailedSaveException('Error commiting a transaction, error is ['.self::getConnection()->error.']');
2025:         
2026:         self::$logger->debug('<<commit');
2027:     }
2028:     
2029:     /**
2030:      * (non-PHPdoc)
2031:      * @see alpha/model/AlphaDAOProviderInterface::rollback()
2032:      */
2033:     public static function rollback() {
2034:         if(self::$logger == null)
2035:             self::$logger = new Logger('AlphaDAOProviderMySQL');
2036:         self::$logger->debug('>>rollback()');
2037:         
2038:         if (!self::getConnection()->rollback())
2039:             throw new AlphaException('Error rolling back a transaction, error is ['.self::getConnection()->error.']');
2040:         
2041:         self::$logger->debug('<<rollback');
2042:     }
2043:     
2044:     /**
2045:      * (non-PHPdoc)
2046:      * @see alpha/model/AlphaDAOProviderInterface::setBO()
2047:      */
2048:     public function setBO($BO) {
2049:         $this->BO = $BO;
2050:     }
2051:     
2052:     /**
2053:      * Dynamically binds all of the attributes for the current BO to the supplied prepared statement 
2054:      * parameters.  If arrays of attribute names and values are provided, only those will be bound to
2055:      * the supplied statement.
2056:      *  
2057:      * @param mysqli_stmt $stmt The SQL statement to bind to.
2058:      * @param array Optional array of BO attributes.
2059:      * @param array Optional array of BO values.
2060:      * @return mysqli_stmt
2061:      * @since 1.1
2062:      */
2063:     private function bindParams($stmt, $attributes=array(), $values=array()) {
2064:         self::$logger->debug('>>bindParams(stmt=['.var_export($stmt, true).'])');
2065:         
2066:         $bindingsTypes = '';
2067:         $params = array();
2068:         
2069:         // here we are only binding the supplied attributes
2070:         if(count($attributes) > 0 && count($attributes) == count($values)) {
2071:     
2072:             $count = count($values);
2073:             
2074:             for($i = 0; $i < $count; $i++) {
2075:                 if (AlphaValidator::isInteger($values[$i]))
2076:                     $bindingsTypes .= 'i';
2077:                 else
2078:                     $bindingsTypes .= 's';
2079:                 array_push($params, $values[$i]);
2080:             }
2081:                 
2082:             if($this->BO->isTableOverloaded()) {
2083:                 if(isset($this->classname)) {
2084:                     $bindingsTypes .= 's';
2085:                     array_push($params, $this->classname);                  
2086:                 }else{                  
2087:                     $bindingsTypes .= 's';                  
2088:                     array_push($params, get_class($this->BO));                  
2089:                 }
2090:             }
2091:         }else{ // bind all attributes on the business object
2092:         
2093:             // get the class attributes
2094:             $reflection = new ReflectionClass(get_class($this->BO));
2095:             $properties = $reflection->getProperties();
2096:     
2097:             foreach($properties as $propObj) {
2098:                 $propName = $propObj->name;
2099:                 if (!in_array($propName, $this->BO->getTransientAttributes())) {                
2100:                     // Skip the OID, database auto number takes care of this.
2101:                     if($propName != 'OID' && $propName != 'version_num') {
2102:                         if($this->BO->getPropObject($propName) instanceof Integer)
2103:                             $bindingsTypes .= 'i';
2104:                         else
2105:                             $bindingsTypes .= 's';
2106:                         array_push($params, $this->BO->get($propName));
2107:                     }
2108:                                 
2109:                     if($propName == 'version_num') {
2110:                         $temp = $this->BO->getVersionNumber()->getValue();
2111:                         $this->BO->set('version_num', $temp+1);
2112:                         $bindingsTypes .= 'i';
2113:                         array_push($params, $this->BO->getVersionNumber()->getValue());
2114:                     }
2115:                 }
2116:             }
2117:                 
2118:             if($this->BO->isTableOverloaded()) {
2119:                 if(isset($this->classname)) {
2120:                     $bindingsTypes .= 's';
2121:                     array_push($params, $this->classname);                  
2122:                 }else{                  
2123:                     $bindingsTypes .= 's';                  
2124:                     array_push($params, get_class($this->BO));                  
2125:                 }
2126:             }
2127:             
2128:             // the OID may be on the WHERE clause for UPDATEs and DELETEs
2129:             if(!$this->BO->isTransient()) {                 
2130:                 $bindingsTypes .= 'i';
2131:                 array_push($params, $this->BO->getOID());
2132:             }
2133:         }
2134:         
2135:         self::$logger->debug('bindingsTypes=['.$bindingsTypes.'], count: ['.strlen($bindingsTypes).']');
2136:         self::$logger->debug('params ['.var_export($params, true).']');
2137:                     
2138:         if ($params != null) {       
2139:             $bind_names[] = $bindingsTypes;
2140: 
2141:             $count = count($params);
2142:             
2143:             for ($i = 0; $i < $count; $i++) {
2144:                 $bind_name = 'bind'.$i;
2145:                 $$bind_name = $params[$i];
2146:                 $bind_names[] = &$$bind_name;
2147:             }        
2148: 
2149:             call_user_func_array(array($stmt,'bind_param'), $bind_names);
2150:         }
2151:         
2152:         self::$logger->debug('<<bindParams ['.var_export($stmt, true).']');
2153:         return $stmt;
2154:     }
2155:     
2156:     /**
2157:      * Dynamically binds the result of the supplied prepared statement to a 2d array, where each element in the array is another array
2158:      * representing a database row.
2159:      * 
2160:      * @param mysqli_stmt $stmt
2161:      * @return array A 2D array containing the query result.
2162:      * @since 1.1
2163:      */
2164:     private function bindResult($stmt) {
2165:         $result = array();
2166:      
2167:         $metadata = $stmt->result_metadata();
2168:         $fields = $metadata->fetch_fields();
2169: 
2170:         while(true) {
2171:             $pointers = array();
2172:             $row = array();
2173:        
2174:             $pointers[] = $stmt;
2175:             foreach ($fields as $field) {
2176:                 $fieldname = $field->name;
2177:                 $pointers[] = &$row[$fieldname];
2178:             }
2179:        
2180:             call_user_func_array('mysqli_stmt_bind_result', $pointers);
2181:        
2182:             if (!$stmt->fetch())
2183:                 break;
2184:        
2185:             $result[] = $row;
2186:         }
2187:      
2188:         $metadata->free();
2189:      
2190:         return $result;
2191:     }
2192:     
2193:     /**
2194:      * Parses a MySQL error for the value that violated a unique constraint.
2195:      * 
2196:      * @param string $error The MySQL error string.
2197:      * @since 1.1
2198:      */
2199:     private function findOffendingValue($error) {
2200:         self::$logger->debug('>>findOffendingValue(error=['.$error.'])');
2201:                 
2202:         $singleQuote1 = strpos($error,"'");
2203:         $singleQuote2 = strrpos($error,"'");
2204:         
2205:         $value = substr($error, $singleQuote1, ($singleQuote2-$singleQuote1)+1);
2206:         self::$logger->debug('<<findOffendingValue ['.$value.'])');
2207:         return $value;
2208:     }
2209: }
2210: 
2211: ?>
2212: