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