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