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