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