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