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