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