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 1599 2012-11-29 12:31:15Z alphadevx $
  10:  * @license http://www.opensource.org/licenses/bsd-license.php The BSD License
  11:  * @copyright Copyright (c) 2012, John Collins (founder of Alpha Framework).
  12:  * All rights reserved.
  13:  *
  14:  * <pre>
  15:  * Redistribution and use in source and binary forms, with or
  16:  * without modification, are permitted provided that the
  17:  * following conditions are met:
  18:  *
  19:  * * Redistributions of source code must retain the above
  20:  *   copyright notice, this list of conditions and the
  21:  *   following disclaimer.
  22:  * * Redistributions in binary form must reproduce the above
  23:  *   copyright notice, this list of conditions and the
  24:  *   following disclaimer in the documentation and/or other
  25:  *   materials provided with the distribution.
  26:  * * Neither the name of the Alpha Framework nor the names
  27:  *   of its contributors may be used to endorse or promote
  28:  *   products derived from this software without specific
  29:  *   prior written permission.
  30:  *
  31:  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
  32:  * CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
  33:  * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
  34:  * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  35:  * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR
  36:  * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  37:  * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
  38:  * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  39:  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
  40:  * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
  41:  * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
  42:  * OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  43:  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  44:  * </pre>
  45:  *
  46:  */
  47: class 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 = 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 = 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) {
 511:         self::$logger->debug('>>loadAllByAttributes(attributes=['.var_export($attributes, true).'], values=['.var_export($values, true).'], start=['.
 512:             $start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 513: 
 514:         $whereClause = ' WHERE';
 515: 
 516:         $count = count($attributes);
 517: 
 518:         for($i = 0; $i < $count; $i++) {
 519:             $whereClause .= ' '.$attributes[$i].' = :'.$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 = 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:                 $obj = new $BO_Class();
 580:                 $obj->load($row['OID']);
 581:                 $objects[$count] = $obj;
 582:                 $count++;
 583:             }catch(ResourceNotAllowedException $e) {
 584:                 // the resource not allowed will be absent from the list
 585:             }
 586:         }
 587: 
 588:         $stmt->close();
 589: 
 590:         self::$logger->debug('<<loadAllByAttributes ['.count($objects).']');
 591:         return $objects;
 592:     }
 593: 
 594:     /**
 595:      * (non-PHPdoc)
 596:      * @see alpha/model/AlphaDAOProviderInterface::loadAllByDayUpdated()
 597:      */
 598:     public function loadAllByDayUpdated($date, $start=0, $limit=0, $orderBy="OID", $order="ASC", $ignoreClassType=false) {
 599:         self::$logger->debug('>>loadAllByDayUpdated(date=['.$date.'], start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 600: 
 601:         if ($start != 0 && $limit != 0)
 602:             $limit = ' LIMIT '.$limit.' OFFSET '.$start.';';
 603:         else
 604:             $limit = ';';
 605: 
 606:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 607:             $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;
 608:         else
 609:             $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;
 610: 
 611:         $this->BO->setLastQuery($sqlQuery);
 612: 
 613:         if(!$result = self::getConnection()->query($sqlQuery)) {
 614:             throw new BONotFoundException('Failed to load object OIDs, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 615:             self::$logger->debug('<<loadAllByDayUpdated []');
 616:             return array();
 617:         }
 618: 
 619:         // now build an array of objects to be returned
 620:         $objects = array();
 621:         $count = 0;
 622:         $BO_Class = get_class($this->BO);
 623: 
 624:         while($row = $result->fetchArray()) {
 625:             $obj = new $BO_Class();
 626:             $obj->load($row['OID']);
 627:             $objects[$count] = $obj;
 628:             $count++;
 629:         }
 630: 
 631:         self::$logger->debug('<<loadAllByDayUpdated ['.count($objects).']');
 632:         return $objects;
 633:     }
 634: 
 635:     /**
 636:      * (non-PHPdoc)
 637:      * @see alpha/model/AlphaDAOProviderInterface::loadAllFieldValuesByAttribute()
 638:      */
 639:     public function loadAllFieldValuesByAttribute($attribute, $value, $returnAttribute, $order='ASC', $ignoreClassType=false) {
 640:         self::$logger->debug('>>loadAllFieldValuesByAttribute(attribute=['.$attribute.'], value=['.$value.'], returnAttribute=['.$returnAttribute.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 641: 
 642:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 643:             $sqlQuery = "SELECT ".$returnAttribute." FROM ".$this->BO->getTableName()." WHERE $attribute = '$value' AND classname='".get_class($this->BO)."' ORDER BY OID ".$order.";";
 644:         else
 645:             $sqlQuery = "SELECT ".$returnAttribute." FROM ".$this->BO->getTableName()." WHERE $attribute = '$value' ORDER BY OID ".$order.";";
 646: 
 647:         $this->BO->setLastQuery($sqlQuery);
 648: 
 649:         self::$logger->debug('lastQuery ['.$sqlQuery.']');
 650: 
 651:         if(!$result = self::getConnection()->query($sqlQuery)) {
 652:             throw new BONotFoundException('Failed to load field ['.$returnAttribute.'] values, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 653:             self::$logger->debug('<<loadAllFieldValuesByAttribute []');
 654:             return array();
 655:         }
 656: 
 657:         // now build an array of attribute values to be returned
 658:         $values = array();
 659:         $count = 0;
 660:         $BO_Class = get_class($this->BO);
 661: 
 662:         while($row = $result->fetchArray()) {
 663:             $values[$count] = $row[$returnAttribute];
 664:             $count++;
 665:         }
 666: 
 667:         self::$logger->debug('<<loadAllFieldValuesByAttribute ['.count($values).']');
 668:         return $values;
 669:     }
 670: 
 671:     /**
 672:      * (non-PHPdoc)
 673:      * @see alpha/model/AlphaDAOProviderInterface::save()
 674:      */
 675:     public function save() {
 676:         self::$logger->debug('>>save()');
 677: 
 678:         // get the class attributes
 679:         $reflection = new ReflectionClass(get_class($this->BO));
 680:         $properties = $reflection->getProperties();
 681:         $sqlQuery = '';
 682:         $stmt = null;
 683: 
 684:         if($this->BO->getVersion() != $this->BO->getVersionNumber()->getValue()){
 685:             throw new LockingException('Could not save the object as it has been updated by another user.  Please try saving again.');
 686:             return;
 687:         }
 688: 
 689:         // set the "updated by" fields, we can only set the user id if someone is logged in
 690:         if(isset($_SESSION['currentUser']))
 691:             $this->BO->set('updated_by', $_SESSION['currentUser']->getOID());
 692: 
 693:         $this->BO->set('updated_ts', new Timestamp(date("Y-m-d H:i:s")));
 694: 
 695:         // check to see if it is a transient object that needs to be inserted
 696:         if($this->BO->isTransient()) {
 697:             $savedFields = array();
 698:             $sqlQuery = 'INSERT INTO '.$this->BO->getTableName().' (';
 699: 
 700:             foreach($properties as $propObj) {
 701:                 $propName = $propObj->name;
 702:                 if (!in_array($propName, $this->BO->getTransientAttributes())) {
 703:                     // Skip the OID, database auto number takes care of this.
 704:                     if($propName != 'OID' && $propName != 'version_num') {
 705:                         $sqlQuery .= "$propName,";
 706:                         $savedFields[] = $propName;
 707:                     }
 708: 
 709:                     if($propName == 'version_num') {
 710:                         $sqlQuery .= 'version_num,';
 711:                         $savedFields[] = 'version_num';
 712:                     }
 713:                 }
 714:             }
 715:             if($this->BO->isTableOverloaded())
 716:                 $sqlQuery .= 'classname,';
 717: 
 718:             $sqlQuery = rtrim($sqlQuery, ",");
 719: 
 720:             $sqlQuery .= ') VALUES (';
 721: 
 722:             foreach($savedFields as $savedField)
 723:                 $sqlQuery.= ':'.$savedField.',';
 724: 
 725:             if($this->BO->isTableOverloaded())
 726:                 $sqlQuery.= ':classname,';
 727: 
 728:             $sqlQuery = rtrim($sqlQuery, ',').')';
 729: 
 730:             $this->BO->setLastQuery($sqlQuery);
 731:             self::$logger->debug('Query ['.$sqlQuery.']');
 732: 
 733:             $stmt = self::getConnection()->prepare($sqlQuery);
 734: 
 735:             if($stmt instanceof SQLite3Stmt) {
 736: 
 737:                 foreach($savedFields as $savedField) {
 738:                     if($this->BO->get($savedField) instanceof Integer)
 739:                         $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_INTEGER);
 740:                     else
 741:                         $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_TEXT);
 742:                 }
 743: 
 744:                 if($this->BO->isTableOverloaded())
 745:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 746: 
 747:                 $stmt->bindValue(':version_num', 1, SQLITE3_INTEGER); // on an initial save, this will always be 1
 748:                 $this->BO->set('version_num', 1);
 749: 
 750:                 try {
 751:                     $stmt->execute();
 752:                 }catch (Exception $e) {
 753:                     if(self::getConnection()->lastErrorCode() == 19)
 754:                         throw new ValidationException('Unique key violation while trying to save object, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 755:                     else
 756:                         throw new FailedSaveException('Failed to save object, exception ['.$e->getMessage().'], DB error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 757:                 }
 758:             }else{
 759:                 throw new FailedSaveException('Failed to save object, exception ['.$e->getMessage().'], DB error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 760:             }
 761:         }else{
 762:             // assume that it is a persistent object that needs to be updated
 763:             $savedFields = array();
 764:             $sqlQuery = 'UPDATE '.$this->BO->getTableName().' SET ';
 765: 
 766:             foreach($properties as $propObj) {
 767:                 $propName = $propObj->name;
 768:                 if (!in_array($propName, $this->BO->getTransientAttributes())) {
 769:                     // Skip the OID, database auto number takes care of this.
 770:                     if($propName != 'OID' && $propName != 'version_num') {
 771:                         $sqlQuery .= "$propName = :$propName,";
 772:                         $savedFields[] = $propName;
 773:                     }
 774: 
 775:                     if($propName == 'version_num') {
 776:                         $sqlQuery .= 'version_num = :version_num,';
 777:                         $savedFields[] = 'version_num';
 778:                     }
 779:                 }
 780:             }
 781: 
 782:             if($this->BO->isTableOverloaded())
 783:                 $sqlQuery .= 'classname = :classname,';
 784: 
 785:             $sqlQuery = rtrim($sqlQuery, ",");
 786: 
 787:             $sqlQuery .= " WHERE OID=:OID;";
 788: 
 789:             $this->BO->setLastQuery($sqlQuery);
 790:             $stmt = self::getConnection()->prepare($sqlQuery);
 791: 
 792:             if($stmt instanceof SQLite3Stmt) {
 793: 
 794:                 foreach($savedFields as $savedField) {
 795:                     if($this->BO->get($savedField) instanceof Integer)
 796:                         $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_INTEGER);
 797:                     else
 798:                         $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_TEXT);
 799:                 }
 800: 
 801:                 if($this->BO->isTableOverloaded())
 802:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 803: 
 804:                 $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
 805: 
 806:                 $temp = $this->BO->getVersionNumber()->getValue();
 807:                 $this->BO->set('version_num', $temp+1);
 808:                 $stmt->bindValue(':version_num', $temp+1, SQLITE3_INTEGER);
 809: 
 810:                 $stmt->execute();
 811:             }else{
 812:                 throw new FailedSaveException('Failed to save object, error is ['.$stmt->error.'], query ['.$this->BO->getLastQuery().']');
 813:             }
 814:         }
 815: 
 816:         if ($stmt != null && $stmt != false) {
 817:             // populate the updated OID in case we just done an insert
 818:             if($this->BO->isTransient())
 819:                 $this->BO->setOID(self::getConnection()->lastInsertRowID());
 820: 
 821:             try {
 822:                 foreach($properties as $propObj) {
 823:                     $propName = $propObj->name;
 824: 
 825:                     if($this->BO->getPropObject($propName) instanceof Relation) {
 826:                         $prop = $this->BO->getPropObject($propName);
 827: 
 828:                         // handle the saving of MANY-TO-MANY relation values
 829:                         if($prop->getRelationType() == 'MANY-TO-MANY') {
 830:                             try {
 831:                                 try{
 832:                                     // check to see if the rel is on this class
 833:                                     $side = $prop->getSide(get_class($this->BO));
 834:                                 }catch (IllegalArguementException $iae) {
 835:                                     $side = $prop->getSide(ucfirst($this->BO->getTableName()).'Object');
 836:                                 }
 837: 
 838:                                 $lookUp = $prop->getLookup();
 839: 
 840:                                 // first delete all of the old RelationLookup objects for this rel
 841:                                 try {
 842:                                     if($side == 'left')
 843:                                         $lookUp->deleteAllByAttribute('leftID', $this->BO->getOID());
 844:                                     else
 845:                                         $lookUp->deleteAllByAttribute('rightID', $this->BO->getOID());
 846:                                 }catch (Exception $e) {
 847:                                     throw new FailedSaveException('Failed to delete old RelationLookup objects on the table ['.$prop->getLookup()->getTableName().'], error is ['.$e->getMessage().']');
 848:                                 }
 849: 
 850:                                 if(isset($_POST[$propName]) && $_POST[$propName] != '00000000000')
 851:                                     $OIDs = explode(',', $_POST[$propName]);
 852: 
 853:                                 if(isset($OIDs) && !empty($OIDs[0])) {
 854:                                     // now for each posted OID, create a new RelationLookup record and save
 855:                                     foreach ($OIDs as $oid) {
 856:                                         $newLookUp = new RelationLookup($lookUp->get('leftClassName'), $lookUp->get('rightClassName'));
 857:                                         if($side == 'left') {
 858:                                             $newLookUp->set('leftID', $this->BO->getOID());
 859:                                             $newLookUp->set('rightID', $oid);
 860:                                         }else{
 861:                                             $newLookUp->set('rightID', $this->BO->getOID());
 862:                                             $newLookUp->set('leftID', $oid);
 863:                                         }
 864:                                         $newLookUp->save();
 865:                                     }
 866:                                 }
 867:                             }catch (Exception $e) {
 868:                                 throw new FailedSaveException('Failed to update a MANY-TO-MANY relation on the object, error is ['.$e->getMessage().']');
 869:                                 return;
 870:                             }
 871:                         }
 872: 
 873:                         // handle the saving of ONE-TO-MANY relation values
 874:                         if($prop->getRelationType() == 'ONE-TO-MANY') {
 875:                             $prop->setValue($this->BO->getOID());
 876:                         }
 877:                     }
 878:                 }
 879:             }catch (Exception $e) {
 880:                 throw new FailedSaveException('Failed to save object, error is ['.$e->getMessage().']');
 881:                 return;
 882:             }
 883: 
 884:             $stmt->close();
 885:         }else{
 886:             // there has been an error, so decrement the version number back
 887:             $temp = $this->BO->getVersionNumber()->getValue();
 888:             $this->BO->set('version_num', $temp-1);
 889: 
 890:             throw new FailedSaveException('Failed to save object, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 891:         }
 892: 
 893:         if($this->BO->getMaintainHistory())
 894:             $this->BO->saveHistory();
 895:     }
 896: 
 897:     /**
 898:      * (non-PHPdoc)
 899:      * @see alpha/model/AlphaDAOProviderInterface::saveAttribute()
 900:      */
 901:     public function saveAttribute($attribute, $value) {
 902:         self::$logger->debug('>>saveAttribute(attribute=['.$attribute.'], value=['.$value.'])');
 903: 
 904:         // assume that it is a persistent object that needs to be updated
 905:         $sqlQuery = 'UPDATE '.$this->BO->getTableName().' SET '.$attribute.'=:attribute, version_num =:version WHERE OID=:OID;';
 906: 
 907:         $this->BO->setLastQuery($sqlQuery);
 908:         $stmt = self::getConnection()->prepare($sqlQuery);
 909: 
 910:         $newVersionNumber = $this->BO->getVersionNumber()->getValue()+1;
 911: 
 912:         if($stmt instanceof SQLite3Stmt) {
 913:             if($this->BO->getPropObject($attribute) instanceof Integer)
 914:                 $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
 915:             else
 916:                 $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
 917: 
 918:             $stmt->bindValue(':version', $newVersionNumber, SQLITE3_INTEGER);
 919:             $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
 920: 
 921:             $stmt->execute();
 922:         }else{
 923:             throw new FailedSaveException('Failed to save attribute, error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 924:         }
 925: 
 926:         $stmt->close();
 927: 
 928:         $this->BO->set($attribute, $value);
 929:         $this->BO->set('version_num', $newVersionNumber);
 930: 
 931:         if($this->BO->getMaintainHistory())
 932:             $this->BO->saveHistory();
 933: 
 934:         self::$logger->debug('<<saveAttribute');
 935:     }
 936: 
 937:     /**
 938:      * (non-PHPdoc)
 939:      * @see alpha/model/AlphaDAOProviderInterface::saveHistory()
 940:      */
 941:     public function saveHistory() {
 942:         self::$logger->debug('>>saveHistory()');
 943: 
 944:         // get the class attributes
 945:         $reflection = new ReflectionClass(get_class($this->BO));
 946:         $properties = $reflection->getProperties();
 947:         $sqlQuery = '';
 948:         $stmt = null;
 949: 
 950:         $savedFields = array();
 951:         $attributeNames = array();
 952:         $attributeValues = array();
 953: 
 954:         $sqlQuery = 'INSERT INTO '.$this->BO->getTableName().'_history (';
 955: 
 956:         foreach($properties as $propObj) {
 957:             $propName = $propObj->name;
 958:             if (!in_array($propName, $this->BO->getTransientAttributes())) {
 959:                 $sqlQuery .= "$propName,";
 960:                 $attributeNames[] = $propName;
 961:                 $attributeValues[] = $this->BO->get($propName);
 962:                 $savedFields[] = $propName;
 963:             }
 964:         }
 965: 
 966:         if($this->BO->isTableOverloaded())
 967:             $sqlQuery .= 'classname,';
 968: 
 969:         $sqlQuery = rtrim($sqlQuery, ",");
 970: 
 971:         $sqlQuery .= ') VALUES (';
 972: 
 973:         foreach($savedFields as $saveField)
 974:             $sqlQuery.= ':'.$savedField.',';
 975: 
 976:         if($this->BO->isTableOverloaded())
 977:             $sqlQuery.= ':classname,';
 978: 
 979:         $sqlQuery = rtrim($sqlQuery, ',').')';
 980: 
 981:         $this->BO->setLastQuery($sqlQuery);
 982:         self::$logger->debug('Query ['.$sqlQuery.']');
 983: 
 984:         $stmt = self::getConnection()->prepare($sqlQuery);
 985: 
 986:         if($stmt instanceof SQLite3Stmt) {
 987:             foreach($savedFields as $savedField) {
 988:                 if($this->BO->get($savedField) instanceof Integer)
 989:                     $stmt->bindValue(':'.$savedField, $savedField, SQLITE3_INTEGER);
 990:                 else
 991:                     $stmt->bindValue(':'.$savedField, $savedField, SQLITE3_TEXT);
 992:             }
 993: 
 994:             if($this->BO->isTableOverloaded())
 995:                 $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 996: 
 997:             $stmt->execute();
 998:         }else{
 999:             throw new FailedSaveException('Failed to save object history, error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
1000:         }
1001:     }
1002: 
1003:     /**
1004:      * (non-PHPdoc)
1005:      * @see alpha/model/AlphaDAOProviderInterface::delete()
1006:      */
1007:     public function delete() {
1008:         self::$logger->debug('>>delete()');
1009: 
1010:         $sqlQuery = "DELETE FROM ".$this->BO->getTableName()." WHERE OID = :OID;";
1011: 
1012:         $this->BO->setLastQuery($sqlQuery);
1013: 
1014:         $stmt = self::getConnection()->prepare($sqlQuery);
1015: 
1016:         if($stmt instanceof SQLite3Stmt) {
1017:             $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
1018:             $stmt->execute();
1019:             self::$logger->debug('Deleted the object ['.$this->BO->getOID().'] of class ['.get_class($this->BO).']');
1020:         }else{
1021:             throw new FailedDeleteException('Failed to delete object ['.$this->BO->getOID().'], error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
1022:         }
1023: 
1024:         $stmt->close();
1025: 
1026:         self::$logger->debug('<<delete');
1027:     }
1028: 
1029:     /**
1030:      * (non-PHPdoc)
1031:      * @see alpha/model/AlphaDAOProviderInterface::getVersion()
1032:      */
1033:     public function getVersion() {
1034:         self::$logger->debug('>>getVersion()');
1035: 
1036:         $sqlQuery = 'SELECT version_num FROM '.$this->BO->getTableName().' WHERE OID = :OID;';
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: 
1044:             $result = $stmt->execute();
1045: 
1046:             // there should only ever be one (or none)
1047:             $row = $result->fetchArray(SQLITE3_ASSOC);
1048: 
1049:             $stmt->close();
1050:         }else{
1051:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
1052:             if(!$this->BO->checkTableExists()) {
1053:                 $this->BO->makeTable();
1054: 
1055:                 throw new BONotFoundException('Failed to get the version number, table did not exist so had to create!');
1056:             }
1057:             return;
1058:         }
1059: 
1060:         if(!isset($row['version_num']) || $row['version_num'] < 1) {
1061:             self::$logger->debug('<<getVersion [0]');
1062:             return 0;
1063:         }else{
1064:             $version_num = $row['version_num'];
1065: 
1066:             self::$logger->debug('<<getVersion ['.$version_num.']');
1067:             return $version_num;
1068:         }
1069:     }
1070: 
1071:     /**
1072:      * (non-PHPdoc)
1073:      * @see alpha/model/AlphaDAOProviderInterface::makeTable()
1074:      */
1075:     public function makeTable() {
1076:         self::$logger->debug('>>makeTable()');
1077: 
1078:         $sqlQuery = "CREATE TABLE ".$this->BO->getTableName()." (OID INTEGER PRIMARY KEY,";
1079: 
1080:         // get the class attributes
1081:         $reflection = new ReflectionClass(get_class($this->BO));
1082:         $properties = $reflection->getProperties();
1083: 
1084:         $foreignKeys = array();
1085: 
1086:         foreach($properties as $propObj) {
1087:             $propName = $propObj->name;
1088: 
1089:             if(!in_array($propName, $this->BO->getTransientAttributes()) && $propName != "OID") {
1090:                 $propClass = get_class($this->BO->getPropObject($propName));
1091: 
1092:                 switch (strtoupper($propClass)) {
1093:                     case "INTEGER":
1094:                         // special properties for RelationLookup OIDs
1095:                         if($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID')) {
1096:                             // TODO add a $foreignKeys entry here...
1097:                             $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize().") NOT NULL,";
1098:                         } else {
1099:                             $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize()."),";
1100:                         }
1101:                     break;
1102:                     case "DOUBLE":
1103:                         $sqlQuery .= "$propName REAL(".$this->BO->getPropObject($propName)->getSize(true)."),";
1104:                     break;
1105:                     case "STRING":
1106:                         $sqlQuery .= "$propName TEXT(".$this->BO->getPropObject($propName)->getSize()."),";
1107:                     break;
1108:                     case "TEXT":
1109:                         $sqlQuery .= "$propName TEXT,";
1110:                     break;
1111:                     case "BOOLEAN":
1112:                         $sqlQuery .= "$propName INTEGER(1) DEFAULT '0',";
1113:                     break;
1114:                     case "DATE":
1115:                         $sqlQuery .= "$propName TEXT,";
1116:                     break;
1117:                     case "TIMESTAMP":
1118:                         $sqlQuery .= "$propName TEXT,";
1119:                     break;
1120:                     case "ENUM":
1121:                         $sqlQuery .= "$propName TEXT,";
1122:                     break;
1123:                     case "DENUM":
1124:                         $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1125:                         $sqlQuery .= "$propName INTEGER(11),";
1126:                     break;
1127:                     case "RELATION":
1128:                         $sqlQuery .= "$propName INTEGER(11),";
1129: 
1130:                         $rel = $this->BO->getPropObject($propName);
1131: 
1132:                         $relatedField = $rel->getRelatedClassField();
1133:                         $relatedClass = $rel->getRelatedClass();
1134:                         $relatedBO = new $relatedClass;
1135:                         $tableName = $relatedBO->getTableName();
1136:                         $foreignKeys[$propName] = array($tableName, $relatedField);
1137:                     break;
1138:                     default:
1139:                         $sqlQuery .= "";
1140:                     break;
1141:                 }
1142:             }
1143:         }
1144:         if($this->BO->isTableOverloaded())
1145:             $sqlQuery .= "classname TEXT(100)";
1146:         else
1147:             $sqlQuery = substr($sqlQuery, 0, -1);
1148: 
1149:         if(count($foreignKeys) > 0 ) {
1150:             foreach ($foreignKeys as $field => $related) {
1151:                 $sqlQuery .= ', FOREIGN KEY ('.$field.') REFERENCES '.$related[0].'('.$related[1].')';
1152:             }
1153:         }
1154: 
1155:         $sqlQuery .= ');';
1156: 
1157:         $this->BO->setLastQuery($sqlQuery);
1158: 
1159:         if(!self::getConnection()->exec($sqlQuery)) {
1160:             throw new AlphaException('Failed to create the table ['.$this->BO->getTableName().'] for the class ['.get_class($this->BO).'], database error is ['.self::getLastDatabaseError().']');
1161:             self::$logger->debug('<<makeTable');
1162:         }
1163: 
1164:         // check the table indexes if any additional ones required
1165:         $this->checkIndexes();
1166: 
1167:         if($this->BO->getMaintainHistory())
1168:             $this->BO->makeHistoryTable();
1169: 
1170:         self::$logger->debug('<<makeTable');
1171:     }
1172: 
1173:     /**
1174:      * (non-PHPdoc)
1175:      * @see alpha/model/AlphaDAOProviderInterface::makeHistoryTable()
1176:      */
1177:     public function makeHistoryTable() {
1178:         self::$logger->debug('>>makeHistoryTable()');
1179: 
1180:         $sqlQuery = "CREATE TABLE ".$this->BO->getTableName()."_history (OID INTEGER NOT NULL,";
1181: 
1182:         // get the class attributes
1183:         $reflection = new ReflectionClass(get_class($this->BO));
1184:         $properties = $reflection->getProperties();
1185: 
1186:         foreach($properties as $propObj) {
1187:             $propName = $propObj->name;
1188: 
1189:             if(!in_array($propName, $this->BO->getTransientAttributes()) && $propName != "OID") {
1190:                 $propClass = get_class($this->BO->getPropObject($propName));
1191: 
1192:                 switch (strtoupper($propClass)) {
1193:                     case "INTEGER":
1194:                         // special properties for RelationLookup OIDs
1195:                         if($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID'))
1196:                             $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize().") NOT NULL,";
1197:                         else
1198:                             $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize()."),";
1199:                     break;
1200:                     case "DOUBLE":
1201:                         $sqlQuery .= "$propName REAL(".$this->BO->getPropObject($propName)->getSize(true)."),";
1202:                     break;
1203:                     case "STRING":
1204:                         $sqlQuery .= "$propName TEXT(".$this->BO->getPropObject($propName)->getSize()."),";
1205:                     break;
1206:                     case "TEXT":
1207:                         $sqlQuery .= "$propName TEXT,";
1208:                     break;
1209:                     case "BOOLEAN":
1210:                         $sqlQuery .= "$propName INTEGER(1) DEFAULT '0',";
1211:                     break;
1212:                     case "DATE":
1213:                         $sqlQuery .= "$propName TEXT,";
1214:                     break;
1215:                     case "TIMESTAMP":
1216:                         $sqlQuery .= "$propName TEXT,";
1217:                     break;
1218:                     case "ENUM":
1219:                         $sqlQuery .= "$propName TEXT,";
1220:                     break;
1221:                     case "DENUM":
1222:                         $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1223:                         $sqlQuery .= "$propName INTEGER(11),";
1224:                     break;
1225:                     case "RELATION":
1226:                         $sqlQuery .= "$propName INTEGER(11),";
1227:                     break;
1228:                     default:
1229:                         $sqlQuery .= "";
1230:                     break;
1231:                 }
1232:             }
1233:         }
1234: 
1235:         if($this->BO->isTableOverloaded())
1236:             $sqlQuery .= "classname TEXT(100),";
1237: 
1238:         $sqlQuery .= "PRIMARY KEY (OID, version_num));";
1239: 
1240:         $this->BO->setLastQuery($sqlQuery);
1241: 
1242:         if(!$result = self::getConnection()->query($sqlQuery)) {
1243:             throw new AlphaException('Failed to create the table ['.$this->BO->getTableName().'_history] for the class ['.get_class($this->BO).'], database error is ['.self::getLastDatabaseError().']');
1244:             self::$logger->debug('<<makeHistoryTable');
1245:         }
1246: 
1247:         self::$logger->debug('<<makeHistoryTable');
1248:     }
1249: 
1250:     /**
1251:      * (non-PHPdoc)
1252:      * @see alpha/model/AlphaDAOProviderInterface::rebuildTable()
1253:      */
1254:     public function rebuildTable() {
1255:         self::$logger->debug('>>rebuildTable()');
1256: 
1257:         // the use of "IF EXISTS" here requires SQLite 3.3.0 or above.
1258:         $sqlQuery = 'DROP TABLE IF EXISTS '.$this->BO->getTableName().';';
1259: 
1260:         $this->BO->setLastQuery($sqlQuery);
1261: 
1262:         if(!$result = self::getConnection()->query($sqlQuery)) {
1263:             throw new AlphaException('Failed to drop the table ['.$this->BO->getTableName().'] for the class ['.get_class($this->BO).'], database error is ['.self::getLastDatabaseError().']');
1264:             self::$logger->debug('<<rebuildTable');
1265:         }
1266: 
1267:         $this->BO->makeTable();
1268: 
1269:         self::$logger->debug('<<rebuildTable');
1270:     }
1271: 
1272:     /**
1273:      * (non-PHPdoc)
1274:      * @see alpha/model/AlphaDAOProviderInterface::dropTable()
1275:      */
1276:     public function dropTable($tableName=null) {
1277:         self::$logger->debug('>>dropTable()');
1278: 
1279:         if($tableName == null)
1280:             $tableName = $this->BO->getTableName();
1281: 
1282:         // the use of "IF EXISTS" here requires SQLite 3.3.0 or above.
1283:         $sqlQuery = 'DROP TABLE IF EXISTS '.$tableName.';';
1284: 
1285:         $this->BO->setLastQuery($sqlQuery);
1286: 
1287:         if(!$result = self::getConnection()->query($sqlQuery)) {
1288:             throw new AlphaException('Failed to drop the table ['.$tableName.'] for the class ['.get_class($this->BO).'], query is ['.$this->BO->getLastQuery().']');
1289:             self::$logger->debug('<<dropTable');
1290:         }
1291: 
1292:         self::$logger->debug('<<dropTable');
1293:     }
1294: 
1295:     /**
1296:      * (non-PHPdoc)
1297:      * @see alpha/model/AlphaDAOProviderInterface::addProperty()
1298:      */
1299:     public function addProperty($propName) {
1300:         self::$logger->debug('>>addProperty(propName=['.$propName.'])');
1301: 
1302:         $sqlQuery = 'ALTER TABLE '.$this->BO->getTableName().' ADD ';
1303: 
1304:         if($this->isTableOverloaded() && $propName == 'classname') {
1305:             $sqlQuery .= 'classname TEXT(100)';
1306:         }else{
1307:             if(!in_array($propName, $this->BO->getDefaultAttributes()) && !in_array($propName, $this->BO->getTransientAttributes())) {
1308:                 $propClass = get_class($this->BO->getPropObject($propName));
1309: 
1310:                 switch (strtoupper($propClass)) {
1311:                     case "INTEGER":
1312:                         // special properties for RelationLookup OIDs
1313:                         if($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID'))
1314:                             $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize().") NOT NULL,";
1315:                         else
1316:                             $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize()."),";
1317:                     break;
1318:                     case "DOUBLE":
1319:                         $sqlQuery .= "$propName REAL(".$this->BO->getPropObject($propName)->getSize(true)."),";
1320:                     break;
1321:                     case "STRING":
1322:                         $sqlQuery .= "$propName TEXT(".$this->BO->getPropObject($propName)->getSize()."),";
1323:                     break;
1324:                     case "TEXT":
1325:                         $sqlQuery .= "$propName TEXT,";
1326:                     break;
1327:                     case "BOOLEAN":
1328:                         $sqlQuery .= "$propName INTEGER(1) DEFAULT '0',";
1329:                     break;
1330:                     case "DATE":
1331:                         $sqlQuery .= "$propName TEXT,";
1332:                     break;
1333:                     case "TIMESTAMP":
1334:                         $sqlQuery .= "$propName TEXT,";
1335:                     break;
1336:                     case "ENUM":
1337:                         $sqlQuery .= "$propName TEXT,";
1338:                     break;
1339:                     case "DENUM":
1340:                         $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1341:                         $sqlQuery .= "$propName INTEGER(11),";
1342:                     break;
1343:                     case "RELATION":
1344:                         $sqlQuery .= "$propName INTEGER(11),";
1345:                     break;
1346:                     default:
1347:                         $sqlQuery .= "";
1348:                     break;
1349:                 }
1350:             }
1351:         }
1352: 
1353:         $this->BO->setLastQuery($sqlQuery);
1354: 
1355:         if(!$result = self::getConnection()->query($sqlQuery)) {
1356:             throw new AlphaException('Failed to add the new attribute ['.$propName.'] to the table ['.$this->BO->getTableName().'], query is ['.$this->BO->getLastQuery().']');
1357:             self::$logger->debug('<<addProperty');
1358:         }else{
1359:             self::$logger->info('Successfully added the ['.$propName.'] column onto the ['.$this->BO->getTableName().'] table for the class ['.get_class($this->BO).']');
1360:         }
1361: 
1362:         self::$logger->debug('<<addProperty');
1363:     }
1364: 
1365:     /**
1366:      * (non-PHPdoc)
1367:      * @see alpha/model/AlphaDAOProviderInterface::getMAX()
1368:      */
1369:     public function getMAX() {
1370:         self::$logger->debug('>>getMAX()');
1371: 
1372:         $sqlQuery = 'SELECT MAX(OID) AS max_OID FROM '.$this->BO->getTableName();
1373: 
1374:         $this->BO->setLastQuery($sqlQuery);
1375: 
1376:         try {
1377:             $result = $this->BO->query($sqlQuery);
1378: 
1379:             $row = $result[0];
1380: 
1381:             if (isset($row['max_OID'])) {
1382:                 self::$logger->debug('<<getMAX ['.$row['max_OID'].']');
1383:                 return $row['max_OID'];
1384:             }else{
1385:                 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().']');
1386:             }
1387:         }catch (Exception $e) {
1388:             throw new AlphaException($e->getMessage());
1389:             self::$logger->debug('<<getMAX [0]');
1390:             return 0;
1391:         }
1392:     }
1393: 
1394:     /**
1395:      * (non-PHPdoc)
1396:      * @see alpha/model/AlphaDAOProviderInterface::getCount()
1397:      */
1398:     public function getCount($attributes=array(), $values=array()) {
1399:         self::$logger->debug('>>getCount(attributes=['.var_export($attributes, true).'], values=['.var_export($values, true).'])');
1400: 
1401:         if($this->BO->isTableOverloaded())
1402:             $whereClause = ' WHERE classname = \''.get_class($this->BO).'\' AND';
1403:         else
1404:             $whereClause = ' WHERE';
1405: 
1406:         $count = count($attributes);
1407: 
1408:         for($i = 0; $i < $count; $i++) {
1409:             $whereClause .= ' '.$attributes[$i].' = \''.$values[$i].'\' AND';
1410:             self::$logger->debug($whereClause);
1411:         }
1412:         // remove the last " AND"
1413:         $whereClause = substr($whereClause, 0, -4);
1414: 
1415:         if($whereClause != ' WHERE')
1416:             $sqlQuery = 'SELECT COUNT(OID) AS class_count FROM '.$this->BO->getTableName().$whereClause;
1417:         else
1418:             $sqlQuery = 'SELECT COUNT(OID) AS class_count FROM '.$this->BO->getTableName();
1419: 
1420:         $this->BO->setLastQuery($sqlQuery);
1421: 
1422:         if (!$result = self::getConnection()->query($sqlQuery)) {
1423:             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().']');
1424: 
1425:             self::$logger->debug('<<getCount [0]');
1426:             return 0;
1427:         }else{
1428:             $row = $result->fetchArray(SQLITE3_ASSOC);
1429: 
1430:             self::$logger->debug('<<getCount ['.$row['class_count'].']');
1431:             return $row['class_count'];
1432:         }
1433:     }
1434: 
1435:     /**
1436:      * (non-PHPdoc)
1437:      * @see alpha/model/AlphaDAOProviderInterface::getHistoryCount()
1438:      */
1439:     public function getHistoryCount() {
1440:         self::$logger->debug('>>getHistoryCount()');
1441: 
1442:         if(!$this->BO->getMaintainHistory())
1443:             throw new AlphaException('getHistoryCount method called on a DAO where no history is maintained!');
1444: 
1445:         $sqlQuery = 'SELECT COUNT(OID) AS object_count FROM '.$this->BO->getTableName().'_history WHERE OID='.$this->BO->getOID();
1446: 
1447:         $this->BO->setLastQuery($sqlQuery);
1448: 
1449:         if (!$result = self::getConnection()->query($sqlQuery)) {
1450:             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().']');
1451: 
1452:             self::$logger->debug('<<getHistoryCount [0]');
1453:             return 0;
1454:         }else{
1455:             $row = $result->fetchArray(SQLITE3_ASSOC);
1456: 
1457:             self::$logger->debug('<<getHistoryCount ['.$row['object_count'].']');
1458:             return $row['object_count'];
1459:         }
1460:     }
1461: 
1462:     /**
1463:      * Given that Enum values are not saved in the database for SQLite, an implementation is not required here.
1464:      *
1465:      * (non-PHPdoc)
1466:      * @see alpha/model/AlphaDAOProviderInterface::setEnumOptions()
1467:      * @throws NotImplementedException
1468:      */
1469:     public function setEnumOptions() {
1470:         throw new NotImplementedException('AlphaDAOProviderInterface::setEnumOptions() not implemented by the SQLite3 provider');
1471:     }
1472: 
1473:     /**
1474:      * (non-PHPdoc)
1475:      * @see alpha/model/AlphaDAOProviderInterface::checkTableExists()
1476:      */
1477:     public function checkTableExists($checkHistoryTable = false) {
1478:         self::$logger->debug('>>checkTableExists(checkHistoryTable=['.$checkHistoryTable.'])');
1479: 
1480:         global $config;
1481: 
1482:         $tableExists = false;
1483: 
1484:         $sqlQuery = 'SELECT name FROM sqlite_master WHERE type = "table";';
1485:         $this->BO->setLastQuery($sqlQuery);
1486: 
1487:         $result = self::getConnection()->query($sqlQuery);
1488: 
1489:         $tableName = ($checkHistoryTable ? $this->BO->getTableName().'_history' : $this->BO->getTableName());
1490: 
1491:         while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1492:             if (strtolower($row['name']) == strtolower($tableName))
1493:                 $tableExists = true;
1494:         }
1495: 
1496:         if ($result) {
1497:             self::$logger->debug('<<checkTableExists ['.$tableExists.']');
1498:             return $tableExists;
1499:         }else{
1500:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1501:             self::$logger->debug('<<checkTableExists [false]');
1502:             return false;
1503:         }
1504:     }
1505: 
1506:     /**
1507:      * (non-PHPdoc)
1508:      * @see alpha/model/AlphaDAOProviderInterface::checkBOTableExists()
1509:      */
1510:     public static function checkBOTableExists($BOClassName, $checkHistoryTable = false) {
1511:         if(self::$logger == null)
1512:             self::$logger = new Logger('AlphaDAOProviderSQLite');
1513:         self::$logger->debug('>>checkBOTableExists(BOClassName=['.$BOClassName.'], checkHistoryTable=['.$checkHistoryTable.'])');
1514: 
1515:         eval('$tableName = '.$BOClassName.'::TABLE_NAME;');
1516: 
1517:         if(empty($tableName))
1518:             $tableName = substr($BOClassName, 0, strpos($BOClassName, '_'));
1519: 
1520:         if($checkHistoryTable)
1521:             $tableName .= '_history';
1522: 
1523:         $tableExists = false;
1524: 
1525:         $sqlQuery = 'SELECT name FROM sqlite_master WHERE type = "table";';
1526: 
1527:         $result = self::getConnection()->query($sqlQuery);
1528: 
1529:         while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1530:                 if ($row['name'] == $tableName)
1531:                     $tableExists = true;
1532:         }
1533: 
1534:         if ($result) {
1535:             self::$logger->debug('<<checkBOTableExists ['.($tableExists ? 'true' : 'false').']');
1536:             return $tableExists;
1537:         }else{
1538:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1539:             self::$logger->debug('<<checkBOTableExists [false]');
1540:             return false;
1541:         }
1542:     }
1543: 
1544:     /**
1545:      * (non-PHPdoc)
1546:      * @see alpha/model/AlphaDAOProviderInterface::checkTableNeedsUpdate()
1547:      */
1548:     public function checkTableNeedsUpdate() {
1549:         self::$logger->debug('>>checkTableNeedsUpdate()');
1550: 
1551:         if(!$this->BO->checkTableExists())
1552:             return false;
1553: 
1554:         $updateRequired = false;
1555: 
1556:         $matchCount = 0;
1557: 
1558:         $query = 'PRAGMA table_info('.$this->BO->getTableName().')';
1559:         $result = self::getConnection()->query($query);
1560:         $this->BO->setLastQuery($query);
1561: 
1562:         // get the class attributes
1563:         $reflection = new ReflectionClass(get_class($this->BO));
1564:         $properties = $reflection->getProperties();
1565: 
1566:         foreach($properties as $propObj) {
1567:             $propName = $propObj->name;
1568:             if (!in_array($propName, $this->BO->getTransientAttributes())) {
1569: 
1570:                 $foundMatch = false;
1571: 
1572:                 while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1573:                     if ($propName == $row['name']) {
1574:                         $foundMatch = true;
1575:                         break;
1576:                     }
1577:                 }
1578: 
1579:                 if(!$foundMatch)
1580:                     $matchCount--;
1581: 
1582:                 $result->reset();
1583:             }
1584:         }
1585: 
1586:         // check for the "classname" field in overloaded tables
1587:         if($this->BO->isTableOverloaded()) {
1588:             $foundMatch = false;
1589: 
1590:             while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1591:                 if ('classname' == $row['name']) {
1592:                     $foundMatch = true;
1593:                         break;
1594:                 }
1595:             }
1596:             if(!$foundMatch)
1597:                 $matchCount--;
1598:         }
1599: 
1600:         if ($matchCount != 0)
1601:             $updateRequired = true;
1602: 
1603:         if (!$result) {
1604:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1605:             self::$logger->debug('<<checkTableNeedsUpdate [false]');
1606:             return false;
1607:         }else{
1608:             // check the table indexes
1609:             try {
1610:                 $this->checkIndexes();
1611:             }catch (AlphaException $ae) {
1612:                 self::$logger->warn("Error while checking database indexes:\n\n".$ae->getMessage());
1613:             }
1614: 
1615:             self::$logger->debug('<<checkTableNeedsUpdate ['.$updateRequired.']');
1616:             return $updateRequired;
1617:         }
1618:     }
1619: 
1620:     /**
1621:      * (non-PHPdoc)
1622:      * @see alpha/model/AlphaDAOProviderInterface::findMissingFields()
1623:      */
1624:     public function findMissingFields() {
1625:         self::$logger->debug('>>findMissingFields()');
1626: 
1627:         $missingFields = array();
1628:         $matchCount = 0;
1629: 
1630:         $sqlQuery = 'PRAGMA table_info('.$this->BO->getTableName().')';
1631:         $result = self::getConnection()->query($sqlQuery);
1632:         $this->BO->setLastQuery($sqlQuery);
1633: 
1634:         // get the class attributes
1635:         $reflection = new ReflectionClass(get_class($this->BO));
1636:         $properties = $reflection->getProperties();
1637: 
1638:         foreach($properties as $propObj) {
1639:             $propName = $propObj->name;
1640:             if (!in_array($propName, $this->BO->getTransientAttributes())) {
1641:                 while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1642:                         if ($propName == $row['name']) {
1643:                             $matchCount++;
1644:                             break;
1645:                         }
1646:                 }
1647:                 $result->seek(0);
1648:             }else{
1649:                 $matchCount++;
1650:             }
1651: 
1652:             if($matchCount==0) {
1653:                 array_push($missingFields, $propName);
1654:             }else{
1655:                 $matchCount = 0;
1656:             }
1657:         }
1658: 
1659:         // check for the "classname" field in overloaded tables
1660:         if($this->BO->isTableOverloaded()) {
1661:             $foundMatch = false;
1662: 
1663:             while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1664:                 if ('classname' == $row['name']) {
1665:                     $foundMatch = true;
1666:                         break;
1667:                 }
1668:             }
1669:             if(!$foundMatch)
1670:                 array_push($missingFields, 'classname');
1671:         }
1672: 
1673:         if (!$result) {
1674:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1675:         }
1676: 
1677:         self::$logger->debug('<<findMissingFields ['.var_export($missingFields, true).']');
1678:         return $missingFields;
1679:     }
1680: 
1681:     /**
1682:      * (non-PHPdoc)
1683:      * @see alpha/model/AlphaDAOProviderInterface::getIndexes()
1684:      */
1685:     public function getIndexes() {
1686:         self::$logger->debug('>>getIndexes()');
1687: 
1688:         $sqlQuery = "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='".$this->BO->getTableName()."'";
1689: 
1690:         $this->BO->setLastQuery($sqlQuery);
1691: 
1692:         $indexNames = array();
1693: 
1694:         if (!$result = self::getConnection()->query($sqlQuery)) {
1695:             throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1696:         }else{
1697:             while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1698:                 array_push($indexNames, $row['name']);
1699:             }
1700:         }
1701: 
1702:         self::$logger->debug('<<getIndexes');
1703:         return $indexNames;
1704:     }
1705: 
1706:     /**
1707:      * Checks to see if all of the indexes are in place for the BO's table, creates those that are missing.
1708:      *
1709:      * @since 1.2
1710:      */
1711:     private function checkIndexes() {
1712:         self::$logger->debug('>>checkIndexes()');
1713: 
1714:         $indexNames = $this->BO->getIndexes();
1715: 
1716:         // process unique keys
1717:         foreach($this->BO->getUniqueAttributes() as $prop) {
1718:             // check for composite indexes
1719:             if(strpos($prop, '+')) {
1720:                 $attributes = explode('+', $prop);
1721: 
1722:                 $index_exists = false;
1723:                 foreach ($indexNames as $index) {
1724:                     if ($attributes[0].'_'.$attributes[1].'_unq_idx' == $index) {
1725:                             $index_exists = true;
1726:                     }
1727:                     if(count($attributes) == 3) {
1728:                         if ($attributes[0].'_'.$attributes[1].'_'.$attributes[2].'_unq_idx' == $index) {
1729:                                 $index_exists = true;
1730:                         }
1731:                     }
1732:                 }
1733: 
1734:                 if(!$index_exists) {
1735:                     if(count($attributes) == 3)
1736:                         $this->BO->createUniqueIndex($attributes[0], $attributes[1], $attributes[2]);
1737:                     else
1738:                         $this->BO->createUniqueIndex($attributes[0], $attributes[1]);
1739:                 }
1740:             }else{
1741:                 $index_exists = false;
1742:                 foreach ($indexNames as $index) {
1743:                     if ($prop.'_unq_idx' == $index) {
1744:                             $index_exists = true;
1745:                     }
1746:                 }
1747: 
1748:                 if(!$index_exists)
1749:                     $this->createUniqueIndex($prop);
1750:             }
1751:         }
1752: 
1753:         // 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
1754: 
1755:         self::$logger->debug('<<checkIndexes');
1756:     }
1757: 
1758:     /**
1759:      * Note that SQLite 3.6.19 is requrired for foreign key support.
1760:      *
1761:      * (non-PHPdoc)
1762:      * @see alpha/model/AlphaDAOProviderInterface::createForeignIndex()
1763:      */
1764:     public function createForeignIndex($attributeName, $relatedClass, $relatedClassAttribute) {
1765:         self::$logger->info('>>createForeignIndex(attributeName=['.$attributeName.'], relatedClass=['.$relatedClass.'], relatedClassAttribute=['.$relatedClassAttribute.']');
1766: 
1767:         /*
1768:          * High-level approach
1769:          *
1770:          * 1. Rename the source table to [tablename]_temp
1771:          * 2. Creata a new [tablename] table, with the new FK in place.
1772:          * 3. Copy all of the data from [tablename]_temp to [tablename].
1773:          * 4. Drop [tablename]_temp.
1774:          */
1775:         try {
1776:                 AlphaDAO::begin($this->BO);
1777: 
1778:                 // rename the table to [tablename]_temp
1779:                 $query = 'ALTER TABLE '.$this->BO->getTableName().' RENAME TO '.$this->BO->getTableName().'_temp;';
1780:                 $this->BO->setLastQuery($query);
1781:                 self::getConnection()->query($query);
1782: 
1783:                 self::$logger->info('Renamed the table ['.$this->BO->getTableName().'] to ['.$this->BO->getTableName().'_temp]');
1784: 
1785:                 // now create the new table with the FK in place
1786:                 $this->BO->makeTable();
1787: 
1788:                 self::$logger->info('Made a new copy of the table ['.$this->BO->getTableName().']');
1789: 
1790:                 // copy all of the old data to the new table
1791:                 $query = 'INSERT INTO '.$this->BO->getTableName().' SELECT * FROM '.$this->BO->getTableName().'_temp;';
1792:                 $this->BO->setLastQuery($query);
1793:                 self::getConnection()->query($query);
1794: 
1795:                 self::$logger->info('Copied all of the data from ['.$this->BO->getTableName().'] to ['.$this->BO->getTableName().'_temp]');
1796: 
1797:                 // finally, drop the _temp table and commit the changes
1798:                 $this->BO->dropTable($this->BO->getTableName().'_temp');
1799: 
1800:                 self::$logger->info('Dropped the table ['.$this->BO->getTableName().'_temp]');
1801: 
1802:                 AlphaDAO::commit($this->BO);
1803: 
1804:         }catch (Exception $e) {
1805:             AlphaDAO::rollback($this->BO);
1806: 
1807:             throw new FailedIndexCreateException('Failed to create the index ['.$attributeName.'] on ['.$this->BO->getTableName().'], error is ['.$e->getMessage().'], query ['.$this->BO->getLastQuery().']');
1808:         }
1809: 
1810:         self::$logger->info('<<createForeignIndex');
1811:     }
1812: 
1813:     /**
1814:      * (non-PHPdoc)
1815:      * @see alpha/model/AlphaDAOProviderInterface::createUniqueIndex()
1816:      */
1817:     public function createUniqueIndex($attribute1Name, $attribute2Name = '', $attribute3Name = '') {
1818:         self::$logger->debug('>>createUniqueIndex(attribute1Name=['.$attribute1Name.'], attribute2Name=['.$attribute2Name.'], attribute3Name=['.$attribute3Name.'])');
1819: 
1820:         if($attribute2Name != '' && $attribute3Name != '')
1821:             $sqlQuery = 'CREATE UNIQUE INDEX IF NOT EXISTS '.$attribute1Name.'_'.$attribute2Name.'_'.$attribute3Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.','.$attribute2Name.','.$attribute3Name.');';
1822: 
1823:         if($attribute2Name != '' && $attribute3Name == '')
1824:             $sqlQuery = 'CREATE UNIQUE INDEX IF NOT EXISTS '.$attribute1Name.'_'.$attribute2Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.','.$attribute2Name.');';
1825: 
1826:         if($attribute2Name == '' && $attribute3Name == '')
1827:             $sqlQuery = 'CREATE UNIQUE INDEX IF NOT EXISTS '.$attribute1Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.');';
1828: 
1829:         $this->BO->setLastQuery($sqlQuery);
1830: 
1831:         $result = self::getConnection()->query($sqlQuery);
1832: 
1833:         if ($result) {
1834:             self::$logger->debug('Successfully created the unique index on ['.$this->BO->getTableName().']');
1835:         }else{
1836:             throw new FailedIndexCreateException('Failed to create the unique index on ['.$this->BO->getTableName().'], error is ['.self::getConnection()->error.']');
1837:         }
1838: 
1839:         self::$logger->debug('<<createUniqueIndex');
1840:     }
1841: 
1842:     /**
1843:      * (non-PHPdoc)
1844:      * @see alpha/model/AlphaDAOProviderInterface::reload()
1845:      */
1846:     public function reload() {
1847:         self::$logger->debug('>>reload()');
1848: 
1849:         if(!$this->BO->isTransient()) {
1850:             $this->BO->load($this->BO->getOID());
1851:         }else{
1852:             throw new AlphaException('Cannot reload transient object from database!');
1853:         }
1854: 
1855:         self::$logger->debug('<<reload');
1856:     }
1857: 
1858:     /**
1859:      * (non-PHPdoc)
1860:      * @see alpha/model/AlphaDAOProviderInterface::checkRecordExists()
1861:      */
1862:     public function checkRecordExists($OID) {
1863:         self::$logger->debug('>>checkRecordExists(OID=['.$OID.'])');
1864: 
1865:         $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE OID = :OID;';
1866:         $this->BO->setLastQuery($sqlQuery);
1867:         $stmt = self::getConnection()->prepare($sqlQuery);
1868: 
1869:         $row = array();
1870: 
1871:         if($stmt instanceof SQLite3Stmt) {
1872:             $stmt->bindValue(':OID', $OID, SQLITE3_INTEGER);
1873: 
1874:             $result = $stmt->execute();
1875: 
1876:             // there should only ever be one (or none)
1877:             $row = $result->fetchArray(SQLITE3_ASSOC);
1878: 
1879:             $stmt->close();
1880: 
1881:         }else{
1882:             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().']');
1883:             self::$logger->debug('<<checkRecordExists [false]');
1884:             return false;
1885:         }
1886: 
1887:         if(!isset($row['OID'])) {
1888:             self::$logger->debug('<<checkRecordExists [false]');
1889:             return false;
1890:         }else{
1891:             self::$logger->debug('<<checkRecordExists [true]');
1892:                         return true;
1893:         }
1894:     }
1895: 
1896:     /**
1897:      * (non-PHPdoc)
1898:      * @see alpha/model/AlphaDAOProviderInterface::isTableOverloaded()
1899:      */
1900:     public function isTableOverloaded() {
1901:         self::$logger->debug('>>isTableOverloaded()');
1902: 
1903:         $classname = get_class($this->BO);
1904:         $tablename = ucfirst($this->BO->getTableName()).'Object';
1905: 
1906:         // use reflection to check to see if we are dealing with a persistent type (e.g. DEnum) which are never overloaded
1907:         $reflection = new ReflectionClass($classname);
1908:         $implementedInterfaces = $reflection->getInterfaces();
1909: 
1910:         foreach ($implementedInterfaces as $interface) {
1911:             if ($interface->name == 'AlphaTypeInterface') {
1912:                 self::$logger->debug('<<isTableOverloaded [false]');
1913:                 return false;
1914:             }
1915:         }
1916: 
1917:         if($classname != $tablename) {
1918:             // loop over all BOs to see if there is one using the same table as this BO
1919: 
1920:             $BOclasses = AlphaDAO::getBOClassNames();
1921: 
1922:             foreach($BOclasses as $BOclassName) {
1923:                 if($tablename == $BOclassName) {
1924:                     self::$logger->debug('<<isTableOverloaded [true]');
1925:                     return true;
1926:                 }
1927:             }
1928:             throw new BadBOTableNameException('The table name ['.$tablename.'] for the class ['.$classname.'] is invalid as it does not match a BO definition in the system!');
1929:             self::$logger->debug('<<isTableOverloaded [false]');
1930:             return false;
1931:         }else{
1932:             // check to see if there is already a "classname" column in the database for this BO
1933:             $sqlQuery = 'PRAGMA table_info('.$this->BO->getTableName().')';
1934:             $result = self::getConnection()->query($sqlQuery);
1935:             $this->BO->setLastQuery($sqlQuery);
1936: 
1937:             if(!$result) {
1938:                 self::$logger->warn('Error during pragma table info lookup ['.self::getLastDatabaseError().']');
1939:             }else{
1940:                 while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1941:                     if ('classname' == $row['name']) {
1942:                         self::$logger->debug('<<isTableOverloaded [true]');
1943:                         return true;
1944:                     }
1945:                 }
1946:             }
1947: 
1948:             self::$logger->debug('<<isTableOverloaded [false]');
1949:             return false;
1950:         }
1951:     }
1952: 
1953:     /**
1954:      * (non-PHPdoc)
1955:      * @see alpha/model/AlphaDAOProviderInterface::begin()
1956:      */
1957:     public static function begin() {
1958:         if(self::$logger == null)
1959:             self::$logger = new Logger('AlphaDAOProviderSQLite');
1960:         self::$logger->debug('>>begin()');
1961: 
1962:         if (!self::getConnection()->exec('BEGIN'))
1963:             throw new AlphaException('Error beginning a new transaction, error is ['.self::getLastDatabaseError().']');
1964: 
1965:         self::$logger->debug('<<begin');
1966:     }
1967: 
1968:     /**
1969:      * (non-PHPdoc)
1970:      * @see alpha/model/AlphaDAOProviderInterface::commit()
1971:      */
1972:     public static function commit() {
1973:         if(self::$logger == null)
1974:             self::$logger = new Logger('AlphaDAOProviderSQLite');
1975:         self::$logger->debug('>>commit()');
1976: 
1977:         if (!self::getConnection()->exec('COMMIT'))
1978:             throw new AlphaException('Error commiting a transaction, error is ['.self::getLastDatabaseError().']');
1979: 
1980:         self::$logger->debug('<<commit');
1981:     }
1982: 
1983:     /**
1984:      * (non-PHPdoc)
1985:      * @see alpha/model/AlphaDAOProviderInterface::rollback()
1986:      */
1987:     public static function rollback() {
1988:         if(self::$logger == null)
1989:             self::$logger = new Logger('AlphaDAOProviderSQLite');
1990: 
1991:         self::$logger->debug('>>rollback()');
1992: 
1993:         try {
1994:             self::getConnection()->exec('ROLLBACK');
1995:             self::disconnect();
1996:         }catch (Exception $e) {
1997:             if(strpos($e->getMessage(), 'cannot rollback - no transaction is active') === false) // just filtering out errors where the rollback failed due to no current transaction
1998:                 throw new AlphaException('Error rolling back a transaction, error is ['.self::getLastDatabaseError().']');
1999:         }
2000: 
2001:         self::$logger->debug('<<rollback');
2002:     }
2003: 
2004:     /**
2005:      * (non-PHPdoc)
2006:      * @see alpha/model/AlphaDAOProviderInterface::setBO()
2007:      */
2008:     public function setBO($BO) {
2009:         $this->BO = $BO;
2010:     }
2011: }
2012: 
2013: ?>