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