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