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 1572 2012-10-07 16:10:56Z 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:                 if($this->BO->getMaintainHistory())
 177:                     $this->BO->makeHistoryTable();
 178:                 throw new BONotFoundException('Failed to load object of OID ['.$OID.'], table ['.$this->BO->getTableName().'] did not exist so had to create!');
 179:             }
 180:             return;
 181:         }
 182: 
 183:         if(!isset($row['OID']) || $row['OID'] < 1) {
 184:             throw new BONotFoundException('Failed to load object of OID ['.$OID.'] not found in database.');
 185:             self::$logger->debug('<<load');
 186:             return;
 187:         }
 188: 
 189:         // get the class attributes
 190:         $reflection = new ReflectionClass(get_class($this->BO));
 191:         $properties = $reflection->getProperties();
 192: 
 193:         try {
 194:             foreach($properties as $propObj) {
 195:                 $propName = $propObj->name;
 196: 
 197:                 // filter transient attributes
 198:                 if(!in_array($propName, $this->BO->getTransientAttributes())) {
 199:                     $this->BO->set($propName, $row[$propName]);
 200:                 }elseif(!$propObj->isPrivate() && $this->BO->getPropObject($propName) instanceof Relation) {
 201:                     $prop = $this->BO->getPropObject($propName);
 202: 
 203:                     // handle the setting of ONE-TO-MANY relation values
 204:                     if($prop->getRelationType() == 'ONE-TO-MANY') {
 205:                         $this->BO->set($propObj->name, $this->BO->getOID());
 206:                     }
 207: 
 208:                     // handle the setting of MANY-TO-ONE relation values
 209:                     if($prop->getRelationType() == 'MANY-TO-ONE') {
 210:                         $this->BO->set($propObj->name, $row[$propName]);
 211:                     }
 212:                 }
 213:             }
 214:         }catch (IllegalArguementException $e) {
 215:             self::$logger->warn('Bad data stored in the table ['.$this->BO->getTableName().'], field ['.$propObj->name.'] bad value['.$row[$propObj->name].'], exception ['.$e->getMessage().']');
 216:         }catch (PHPException $e) {
 217:             // it is possible that the load failed due to the table not being up-to-date
 218:             if($this->BO->checkTableNeedsUpdate()) {
 219:                 $missingFields = $this->BO->findMissingFields();
 220: 
 221:                 $count = count($missingFields);
 222: 
 223:                 for($i = 0; $i < $count; $i++)
 224:                     $this->BO->addProperty($missingFields[$i]);
 225: 
 226:                 throw new BONotFoundException('Failed to load object of OID ['.$OID.'], table ['.$this->BO->getTableName().'] was out of sync with the database so had to be updated!');
 227:                 self::$logger->debug('<<load');
 228:                 return;
 229:             }
 230:         }
 231: 
 232:         self::$logger->debug('<<load');
 233:     }
 234: 
 235:     /**
 236:      * (non-PHPdoc)
 237:      * @see alpha/model/AlphaDAOProviderInterface::loadByAttribute()
 238:      */
 239:     public function loadByAttribute($attribute, $value, $ignoreClassType=false, $loadAttributes=array()) {
 240:         self::$logger->debug('>>loadByAttribute(attribute=['.$attribute.'], value=['.$value.'], ignoreClassType=['.$ignoreClassType.'], 
 241:             loadAttributes=['.var_export($loadAttributes, true).'])');
 242: 
 243:         if(count($loadAttributes) == 0)
 244:             $attributes = $this->BO->getPersistentAttributes();
 245:         else
 246:             $attributes = $loadAttributes;
 247: 
 248:         $fields = '';
 249:         foreach($attributes as $att)
 250:             $fields .= $att.',';
 251:         $fields = substr($fields, 0, -1);
 252: 
 253:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 254:             $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().' WHERE '.$attribute.' = :attribute AND classname = :classname LIMIT 1;';
 255:         else
 256:             $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().' WHERE '.$attribute.' = :attribute LIMIT 1;';
 257: 
 258:         self::$logger->debug('Query=['.$sqlQuery.']');
 259: 
 260:         $this->BO->setLastQuery($sqlQuery);
 261:         $stmt = self::getConnection()->prepare($sqlQuery);
 262: 
 263:         $row = array();
 264: 
 265:         if($stmt instanceof SQLite3Stmt) {
 266:             if($this->BO->getPropObject($attribute) instanceof Integer) {
 267:                 if(!$ignoreClassType && $this->BO->isTableOverloaded()) {
 268:                     $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
 269:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 270:                 }else{
 271:                     $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
 272:                 }
 273:             }else{
 274:                 if(!$ignoreClassType && $this->BO->isTableOverloaded()) {
 275:                     $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
 276:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 277:                 }else{
 278:                     $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
 279:                 }
 280:             }
 281: 
 282:             $result = $stmt->execute();
 283: 
 284:             // there should only ever be one (or none)
 285:             $row = $result->fetchArray(SQLITE3_ASSOC);
 286: 
 287:             $stmt->close();
 288:         }else{
 289:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
 290:             if(!$this->BO->checkTableExists()) {
 291:                 $this->BO->makeTable();
 292: 
 293:                 if($this->BO->getMaintainHistory())
 294:                     $this->BO->makeHistoryTable();
 295:                 throw new BONotFoundException('Failed to load object by attribute ['.$attribute.'] and value ['.$value.'], table did not exist so had to create!');
 296:             }
 297:             return;
 298:         }
 299: 
 300:         if(!isset($row['OID']) || $row['OID'] < 1) {
 301:             throw new BONotFoundException('Failed to load object by attribute ['.$attribute.'] and value ['.$value.'], not found in database.');
 302:             self::$logger->debug('<<loadByAttribute');
 303:             return;
 304:         }
 305: 
 306:         $this->OID = $row['OID'];
 307: 
 308:         // get the class attributes
 309:         $reflection = new ReflectionClass(get_class($this->BO));
 310:         $properties = $reflection->getProperties();
 311: 
 312:         try {
 313:             foreach($properties as $propObj) {
 314:                 $propName = $propObj->name;
 315: 
 316:                 if(isset($row[$propName])) {
 317:                     // filter transient attributes
 318:                     if(!in_array($propName, $this->BO->getTransientAttributes())) {
 319:                         $this->BO->set($propName, $row[$propName]);
 320:                     }elseif(!$propObj->isPrivate() && $this->BO->get($propName) != '' && $this->BO->getPropObject($propName) instanceof Relation) {
 321:                         $prop = $this->BO->getPropObject($propName);
 322: 
 323:                         // handle the setting of ONE-TO-MANY relation values
 324:                         if($prop->getRelationType() == 'ONE-TO-MANY') {
 325:                             $this->BO->set($propObj->name, $this->BO->getOID());
 326:                         }
 327:                     }
 328:                 }
 329:             }
 330:         }catch (IllegalArguementException $e) {
 331:             self::$logger->warn('Bad data stored in the table ['.$this->BO->getTableName().'], field ['.$propObj->name.'] bad value['.$row[$propObj->name].'], exception ['.$e->getMessage().']');
 332:         }catch (PHPException $e) {
 333:             // it is possible that the load failed due to the table not being up-to-date
 334:             if($this->BO->checkTableNeedsUpdate()) {
 335:                 $missingFields = $this->BO->findMissingFields();
 336: 
 337:                 $count = count($missingFields);
 338: 
 339:                 for($i = 0; $i < $count; $i++)
 340:                     $this->BO->addProperty($missingFields[$i]);
 341: 
 342:                 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!');
 343:                 self::$logger->debug('<<loadByAttribute');
 344:                 return;
 345:             }
 346:         }
 347: 
 348:         self::$logger->debug('<<loadByAttribute');
 349:     }
 350: 
 351:     /**
 352:      * (non-PHPdoc)
 353:      * @see alpha/model/AlphaDAOProviderInterface::loadAll()
 354:      */
 355:     public function loadAll($start=0, $limit=0, $orderBy='OID', $order='ASC', $ignoreClassType=false) {
 356:         self::$logger->debug('>>loadAll(start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 357: 
 358:         // ensure that the field name provided in the orderBy param is legit
 359:         try {
 360:             $field = $this->BO->get($orderBy);
 361:         }catch(AlphaException $e) {
 362:             throw new AlphaException('The field name ['.$orderBy.'] provided in the param orderBy does not exist on the class ['.get_class($this->BO).']');
 363:         }
 364: 
 365:         if(!$ignoreClassType && $this->BO->isTableOverloaded()) {
 366:             if($limit == 0) {
 367:                 $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE classname=\''.get_class($this->BO).'\' ORDER BY '.$orderBy.' '.$order.';';
 368:             }else{
 369:                 $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE classname=\''.get_class($this->BO).'\' ORDER BY '.$orderBy.' '.$order.' LIMIT '.
 370:                     $limit.' OFFSET '.$start.';';
 371:             }
 372:         }else{
 373:             if($limit == 0)
 374:                 $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' ORDER BY '.$orderBy.' '.$order.';';
 375:             else
 376:                 $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' ORDER BY '.$orderBy.' '.$order.' LIMIT '.$limit.' OFFSET '.$start.';';
 377:         }
 378: 
 379:         $this->BO->setLastQuery($sqlQuery);
 380: 
 381:         if(!$result = self::getConnection()->query($sqlQuery)) {
 382:             throw new BONotFoundException('Failed to load object OIDs, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 383:             self::$logger->debug('<<loadAll [0]');
 384:             return array();
 385:         }
 386: 
 387:         // now build an array of objects to be returned
 388:         $objects = array();
 389:         $count = 0;
 390:         $BO_Class = get_class($this->BO);
 391: 
 392:         while($row = $result->fetchArray()) {
 393:             try {
 394:                 $obj = new $BO_Class();
 395:                 $obj->load($row['OID']);
 396:                 $objects[$count] = $obj;
 397:                 $count++;
 398:             }catch(ResourceNotAllowedException $e) {
 399:                 // the resource not allowed will be absent from the list
 400:             }
 401:         }
 402: 
 403:         self::$logger->debug('<<loadAll ['.count($objects).']');
 404:         return $objects;
 405:     }
 406: 
 407:     /**
 408:      * (non-PHPdoc)
 409:      * @see alpha/model/AlphaDAOProviderInterface::loadAllByAttribute()
 410:      */
 411:     public function loadAllByAttribute($attribute, $value, $start=0, $limit=0, $orderBy="OID", $order="ASC", $ignoreClassType=false, $constructorArgs=array()) {
 412:         self::$logger->debug('>>loadAllByAttribute(attribute=['.$attribute.'], value=['.$value.'], start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.'], constructorArgs=['.print_r($constructorArgs, true).']');
 413: 
 414:         if ($start != 0 && $limit != 0)
 415:             $limit = ' LIMIT '.$limit.' OFFSET '.$start.';';
 416:         else
 417:             $limit = ';';
 418: 
 419:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 420:             $sqlQuery = "SELECT OID FROM ".$this->BO->getTableName()." WHERE $attribute = :attribute AND classname = :classname ORDER BY ".$orderBy." ".$order.$limit;
 421:         else
 422:             $sqlQuery = "SELECT OID FROM ".$this->BO->getTableName()." WHERE $attribute = :attribute ORDER BY ".$orderBy." ".$order.$limit;
 423: 
 424:         $this->BO->setLastQuery($sqlQuery);
 425:         self::$logger->debug($sqlQuery);
 426: 
 427:         $stmt = self::getConnection()->prepare($sqlQuery);
 428: 
 429:         $objects = array();
 430: 
 431:         if($stmt instanceof SQLite3Stmt) {
 432:             if($this->BO->getPropObject($attribute) instanceof Integer) {
 433:                 if($this->BO->isTableOverloaded()) {
 434:                     $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
 435:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 436:                 }else{
 437:                     $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
 438:                 }
 439:             }else{
 440:                 if($this->BO->isTableOverloaded()) {
 441:                     $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
 442:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 443:                 }else{
 444:                     $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
 445:                 }
 446:             }
 447: 
 448:             $result = $stmt->execute();
 449: 
 450:             // now build an array of objects to be returned
 451:             $count = 0;
 452:             $BO_Class = get_class($this->BO);
 453: 
 454:             while($row = $result->fetchArray(SQLITE3_ASSOC)){
 455:                 try {
 456:                     $argsCount = count($constructorArgs);
 457: 
 458:                     if($argsCount < 1) {
 459:                         $obj = new $BO_Class();
 460:                     }else{
 461:                         switch ($argsCount) {
 462:                             case 1:
 463:                                 $obj = new $BO_Class($constructorArgs[0]);
 464:                                 break;
 465:                             case 2:
 466:                                 $obj = new $BO_Class($constructorArgs[0],$constructorArgs[1]);
 467:                                 break;
 468:                             case 3:
 469:                                 $obj = new $BO_Class($constructorArgs[0],$constructorArgs[1],$constructorArgs[2]);
 470:                                 break;
 471:                             case 4:
 472:                                 $obj = new $BO_Class($constructorArgs[0],$constructorArgs[1],$constructorArgs[2],$constructorArgs[3]);
 473:                                 break;
 474:                             case 5:
 475:                                 $obj = new $BO_Class($constructorArgs[0],$constructorArgs[1],$constructorArgs[2],$constructorArgs[3],$constructorArgs[4]);
 476:                                 break;
 477:                             default:
 478:                                 throw new IllegalArguementException('Too many elements in the $constructorArgs array passed to the loadAllByAttribute method!');
 479:                                 break;
 480:                         }
 481:                     }
 482: 
 483:                     $obj->load($row['OID']);
 484:                     $objects[$count] = $obj;
 485:                     $count++;
 486:                 }catch(ResourceNotAllowedException $e) {
 487:                     // the resource not allowed will be absent from the list
 488:                 }
 489:             }
 490: 
 491:             $stmt->close();
 492:         }else{
 493:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
 494: 
 495:             if(!$this->BO->checkTableExists()) {
 496:                 $this->BO->makeTable();
 497:                 if($this->BO->getMaintainHistory())
 498:                     $this->BO->makeHistoryTable();
 499: 
 500:                 throw new BONotFoundException('Failed to load objects by attribute ['.$attribute.'] and value ['.$value.'], table did not exist so had to create!');
 501:             }
 502: 
 503:             self::$logger->debug('<<loadAllByAttribute []');
 504:             return array();
 505:         }
 506: 
 507:         self::$logger->debug('<<loadAllByAttribute ['.count($objects).']');
 508:         return $objects;
 509:     }
 510: 
 511:     /**
 512:      * (non-PHPdoc)
 513:      * @see alpha/model/AlphaDAOProviderInterface::loadAllByAttributes()
 514:      */
 515:     public function loadAllByAttributes($attributes=array(), $values=array(), $start=0, $limit=0, $orderBy='OID', $order='ASC', $ignoreClassType=false) {
 516:         self::$logger->debug('>>loadAllByAttributes(attributes=['.var_export($attributes, true).'], values=['.var_export($values, true).'], start=['.
 517:             $start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 518: 
 519:         $whereClause = ' WHERE';
 520: 
 521:         $count = count($attributes);
 522: 
 523:         for($i = 0; $i < $count; $i++) {
 524:             $whereClause .= ' '.$attributes[$i].' = :'.$attributes[$i].' AND';
 525:             self::$logger->debug($whereClause);
 526:         }
 527: 
 528:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 529:             $whereClause .= ' classname = :classname AND';
 530: 
 531:         // remove the last " AND"
 532:         $whereClause = substr($whereClause, 0, -4);
 533: 
 534:         if ($limit != 0)
 535:             $limit = ' LIMIT '.$limit.' OFFSET '.$start.';';
 536:         else
 537:             $limit = ';';
 538: 
 539:         $sqlQuery = "SELECT OID FROM ".$this->BO->getTableName().$whereClause." ORDER BY ".$orderBy." ".$order.$limit;
 540: 
 541:         $this->BO->setLastQuery($sqlQuery);
 542: 
 543:         $stmt = self::getConnection()->prepare($sqlQuery);
 544: 
 545:         if($stmt instanceof SQLite3Stmt) {
 546:             // bind params where required attributes are provided
 547:             if(count($attributes) > 0 && count($attributes) == count($values)) {
 548:                 for($i = 0; $i < count($attributes); $i++) {
 549:                     if (strcspn($values[$i], '0123456789') != strlen($values[$i])) {
 550:                         $stmt->bindValue(':'.$attributes[$i], $values[$i], SQLITE3_INTEGER);
 551:                     }else{
 552:                         $stmt->bindValue(':'.$attributes[$i], $values[$i], SQLITE3_TEXT);
 553:                     }
 554:                 }
 555:             }else{
 556:                 // we'll still need to bind the "classname" for overloaded BOs...
 557:                 if($this->BO->isTableOverloaded())
 558:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 559:             }
 560: 
 561:             $result = $stmt->execute();
 562: 
 563:         }else{
 564:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
 565: 
 566:             if(!$this->BO->checkTableExists()) {
 567:                 $this->BO->makeTable();
 568:                 if($this->BO->getMaintainHistory())
 569:                                         $this->BO->makeHistoryTable();
 570:                 throw new BONotFoundException('Failed to load objects by attributes ['.var_export($attributes, true).'] and values ['.
 571:                     var_export($values, true).'], table did not exist so had to create!');
 572:             }
 573: 
 574:             self::$logger->debug('<<loadAllByAttributes []');
 575:             return array();
 576:         }
 577: 
 578:         // now build an array of objects to be returned
 579:         $objects = array();
 580:         $count = 0;
 581:         $BO_Class = get_class($this->BO);
 582: 
 583:         while($row = $result->fetchArray(SQLITE3_ASSOC)){
 584:             try {
 585:                 $obj = new $BO_Class();
 586:                 $obj->load($row['OID']);
 587:                 $objects[$count] = $obj;
 588:                 $count++;
 589:             }catch(ResourceNotAllowedException $e) {
 590:                 // the resource not allowed will be absent from the list
 591:             }
 592:         }
 593: 
 594:         $stmt->close();
 595: 
 596:         self::$logger->debug('<<loadAllByAttributes ['.count($objects).']');
 597:         return $objects;
 598:     }
 599: 
 600:     /**
 601:      * (non-PHPdoc)
 602:      * @see alpha/model/AlphaDAOProviderInterface::loadAllByDayUpdated()
 603:      */
 604:     public function loadAllByDayUpdated($date, $start=0, $limit=0, $orderBy="OID", $order="ASC", $ignoreClassType=false) {
 605:         self::$logger->debug('>>loadAllByDayUpdated(date=['.$date.'], start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 606: 
 607:         if ($start != 0 && $limit != 0)
 608:             $limit = ' LIMIT '.$limit.' OFFSET '.$start.';';
 609:         else
 610:             $limit = ';';
 611: 
 612:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 613:             $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;
 614:         else
 615:             $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;
 616: 
 617:         $this->BO->setLastQuery($sqlQuery);
 618: 
 619:         if(!$result = self::getConnection()->query($sqlQuery)) {
 620:             throw new BONotFoundException('Failed to load object OIDs, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 621:             self::$logger->debug('<<loadAllByDayUpdated []');
 622:             return array();
 623:         }
 624: 
 625:         // now build an array of objects to be returned
 626:         $objects = array();
 627:         $count = 0;
 628:         $BO_Class = get_class($this->BO);
 629: 
 630:         while($row = $result->fetchArray()) {
 631:             $obj = new $BO_Class();
 632:             $obj->load($row['OID']);
 633:             $objects[$count] = $obj;
 634:             $count++;
 635:         }
 636: 
 637:         self::$logger->debug('<<loadAllByDayUpdated ['.count($objects).']');
 638:         return $objects;
 639:     }
 640: 
 641:     /**
 642:      * (non-PHPdoc)
 643:      * @see alpha/model/AlphaDAOProviderInterface::loadAllFieldValuesByAttribute()
 644:      */
 645:     public function loadAllFieldValuesByAttribute($attribute, $value, $returnAttribute, $order='ASC', $ignoreClassType=false) {
 646:         self::$logger->debug('>>loadAllFieldValuesByAttribute(attribute=['.$attribute.'], value=['.$value.'], returnAttribute=['.$returnAttribute.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
 647: 
 648:         if(!$ignoreClassType && $this->BO->isTableOverloaded())
 649:             $sqlQuery = "SELECT ".$returnAttribute." FROM ".$this->BO->getTableName()." WHERE $attribute = '$value' AND classname='".get_class($this->BO)."' ORDER BY OID ".$order.";";
 650:         else
 651:             $sqlQuery = "SELECT ".$returnAttribute." FROM ".$this->BO->getTableName()." WHERE $attribute = '$value' ORDER BY OID ".$order.";";
 652: 
 653:         $this->BO->setLastQuery($sqlQuery);
 654: 
 655:         self::$logger->debug('lastQuery ['.$sqlQuery.']');
 656: 
 657:         if(!$result = self::getConnection()->query($sqlQuery)) {
 658:             throw new BONotFoundException('Failed to load field ['.$returnAttribute.'] values, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 659:             self::$logger->debug('<<loadAllFieldValuesByAttribute []');
 660:             return array();
 661:         }
 662: 
 663:         // now build an array of attribute values to be returned
 664:         $values = array();
 665:         $count = 0;
 666:         $BO_Class = get_class($this->BO);
 667: 
 668:         while($row = $result->fetchArray()) {
 669:             $values[$count] = $row[$returnAttribute];
 670:             $count++;
 671:         }
 672: 
 673:         self::$logger->debug('<<loadAllFieldValuesByAttribute ['.count($values).']');
 674:         return $values;
 675:     }
 676: 
 677:     /**
 678:      * (non-PHPdoc)
 679:      * @see alpha/model/AlphaDAOProviderInterface::save()
 680:      */
 681:     public function save() {
 682:         self::$logger->debug('>>save()');
 683: 
 684:         // get the class attributes
 685:         $reflection = new ReflectionClass(get_class($this->BO));
 686:         $properties = $reflection->getProperties();
 687:         $sqlQuery = '';
 688:         $stmt = null;
 689: 
 690:         if($this->BO->getVersion() != $this->BO->getVersionNumber()->getValue()){
 691:             throw new LockingException('Could not save the object as it has been updated by another user.  Please try saving again.');
 692:             return;
 693:         }
 694: 
 695:         // set the "updated by" fields, we can only set the user id if someone is logged in
 696:         if(isset($_SESSION['currentUser']))
 697:             $this->BO->set('updated_by', $_SESSION['currentUser']->getOID());
 698: 
 699:         $this->BO->set('updated_ts', new Timestamp(date("Y-m-d H:i:s")));
 700: 
 701:         // check to see if it is a transient object that needs to be inserted
 702:         if($this->BO->isTransient()) {
 703:             $savedFields = array();
 704:             $sqlQuery = 'INSERT INTO '.$this->BO->getTableName().' (';
 705: 
 706:             foreach($properties as $propObj) {
 707:                 $propName = $propObj->name;
 708:                 if (!in_array($propName, $this->BO->getTransientAttributes())) {
 709:                     // Skip the OID, database auto number takes care of this.
 710:                     if($propName != 'OID' && $propName != 'version_num') {
 711:                         $sqlQuery .= "$propName,";
 712:                         $savedFields[] = $propName;
 713:                     }
 714: 
 715:                     if($propName == 'version_num') {
 716:                         $sqlQuery .= 'version_num,';
 717:                         $savedFields[] = 'version_num';
 718:                     }
 719:                 }
 720:             }
 721:             if($this->BO->isTableOverloaded())
 722:                 $sqlQuery .= 'classname,';
 723: 
 724:             $sqlQuery = rtrim($sqlQuery, ",");
 725: 
 726:             $sqlQuery .= ') VALUES (';
 727: 
 728:             foreach($savedFields as $savedField)
 729:                 $sqlQuery.= ':'.$savedField.',';
 730: 
 731:             if($this->BO->isTableOverloaded())
 732:                 $sqlQuery.= ':classname,';
 733: 
 734:             $sqlQuery = rtrim($sqlQuery, ',').')';
 735: 
 736:             $this->BO->setLastQuery($sqlQuery);
 737:             self::$logger->debug('Query ['.$sqlQuery.']');
 738: 
 739:             $stmt = self::getConnection()->prepare($sqlQuery);
 740: 
 741:             if($stmt instanceof SQLite3Stmt) {
 742: 
 743:                 foreach($savedFields as $savedField) {
 744:                     if($this->BO->get($savedField) instanceof Integer)
 745:                         $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_INTEGER);
 746:                     else
 747:                         $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_TEXT);
 748:                 }
 749: 
 750:                 if($this->BO->isTableOverloaded())
 751:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 752: 
 753:                 $stmt->bindValue(':version_num', 1, SQLITE3_INTEGER); // on an initial save, this will always be 1
 754:                 $this->BO->set('version_num', 1);
 755: 
 756:                 try {
 757:                     $stmt->execute();
 758:                 }catch (Exception $e) {
 759:                     if(self::getConnection()->lastErrorCode() == 19)
 760:                         throw new ValidationException('Unique key violation while trying to save object, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 761:                     else
 762:                         throw new FailedSaveException('Failed to save object, exception ['.$e->getMessage().'], DB error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 763:                 }
 764:             }else{
 765:                 throw new FailedSaveException('Failed to save object, exception ['.$e->getMessage().'], DB error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 766:             }
 767:         }else{
 768:             // assume that it is a persistent object that needs to be updated
 769:             $savedFields = array();
 770:             $sqlQuery = 'UPDATE '.$this->BO->getTableName().' SET ';
 771: 
 772:             foreach($properties as $propObj) {
 773:                 $propName = $propObj->name;
 774:                 if (!in_array($propName, $this->BO->getTransientAttributes())) {
 775:                     // Skip the OID, database auto number takes care of this.
 776:                     if($propName != 'OID' && $propName != 'version_num') {
 777:                         $sqlQuery .= "$propName = :$propName,";
 778:                         $savedFields[] = $propName;
 779:                     }
 780: 
 781:                     if($propName == 'version_num') {
 782:                         $sqlQuery .= 'version_num = :version_num,';
 783:                         $savedFields[] = 'version_num';
 784:                     }
 785:                 }
 786:             }
 787: 
 788:             if($this->BO->isTableOverloaded())
 789:                 $sqlQuery .= 'classname = :classname,';
 790: 
 791:             $sqlQuery = rtrim($sqlQuery, ",");
 792: 
 793:             $sqlQuery .= " WHERE OID=:OID;";
 794: 
 795:             $this->BO->setLastQuery($sqlQuery);
 796:             $stmt = self::getConnection()->prepare($sqlQuery);
 797: 
 798:             if($stmt instanceof SQLite3Stmt) {
 799: 
 800:                 foreach($savedFields as $savedField) {
 801:                     if($this->BO->get($savedField) instanceof Integer)
 802:                         $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_INTEGER);
 803:                     else
 804:                         $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_TEXT);
 805:                 }
 806: 
 807:                 if($this->BO->isTableOverloaded())
 808:                     $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 809: 
 810:                 $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
 811: 
 812:                 $temp = $this->BO->getVersionNumber()->getValue();
 813:                 $this->BO->set('version_num', $temp+1);
 814:                 $stmt->bindValue(':version_num', $temp+1, SQLITE3_INTEGER);
 815: 
 816:                 $stmt->execute();
 817:             }else{
 818:                 throw new FailedSaveException('Failed to save object, error is ['.$stmt->error.'], query ['.$this->BO->getLastQuery().']');
 819:             }
 820:         }
 821: 
 822:         if ($stmt != null && $stmt != false) {
 823:             // populate the updated OID in case we just done an insert
 824:             if($this->BO->isTransient())
 825:                 $this->BO->setOID(self::getConnection()->lastInsertRowID());
 826: 
 827:             try {
 828:                 foreach($properties as $propObj) {
 829:                     $propName = $propObj->name;
 830: 
 831:                     if($this->BO->getPropObject($propName) instanceof Relation) {
 832:                         $prop = $this->BO->getPropObject($propName);
 833: 
 834:                         // handle the saving of MANY-TO-MANY relation values
 835:                         if($prop->getRelationType() == 'MANY-TO-MANY') {
 836:                             try {
 837:                                 try{
 838:                                     // check to see if the rel is on this class
 839:                                     $side = $prop->getSide(get_class($this->BO));
 840:                                 }catch (IllegalArguementException $iae) {
 841:                                     $side = $prop->getSide(ucfirst($this->BO->getTableName()).'Object');
 842:                                 }
 843: 
 844:                                 $lookUp = $prop->getLookup();
 845: 
 846:                                 // first delete all of the old RelationLookup objects for this rel
 847:                                 try {
 848:                                     if($side == 'left')
 849:                                         $lookUp->deleteAllByAttribute('leftID', $this->BO->getOID());
 850:                                     else
 851:                                         $lookUp->deleteAllByAttribute('rightID', $this->BO->getOID());
 852:                                 }catch (Exception $e) {
 853:                                     throw new FailedSaveException('Failed to delete old RelationLookup objects on the table ['.$prop->getLookup()->getTableName().'], error is ['.$e->getMessage().']');
 854:                                 }
 855: 
 856:                                 if(isset($_POST[$propName]) && $_POST[$propName] != '00000000000')
 857:                                     $OIDs = explode(',', $_POST[$propName]);
 858: 
 859:                                 if(isset($OIDs) && !empty($OIDs[0])) {
 860:                                     // now for each posted OID, create a new RelationLookup record and save
 861:                                     foreach ($OIDs as $oid) {
 862:                                         $newLookUp = new RelationLookup($lookUp->get('leftClassName'), $lookUp->get('rightClassName'));
 863:                                         if($side == 'left') {
 864:                                             $newLookUp->set('leftID', $this->BO->getOID());
 865:                                             $newLookUp->set('rightID', $oid);
 866:                                         }else{
 867:                                             $newLookUp->set('rightID', $this->BO->getOID());
 868:                                             $newLookUp->set('leftID', $oid);
 869:                                         }
 870:                                         $newLookUp->save();
 871:                                     }
 872:                                 }
 873:                             }catch (Exception $e) {
 874:                                 throw new FailedSaveException('Failed to update a MANY-TO-MANY relation on the object, error is ['.$e->getMessage().']');
 875:                                 return;
 876:                             }
 877:                         }
 878: 
 879:                         // handle the saving of ONE-TO-MANY relation values
 880:                         if($prop->getRelationType() == 'ONE-TO-MANY') {
 881:                             $prop->setValue($this->BO->getOID());
 882:                         }
 883:                     }
 884:                 }
 885:             }catch (Exception $e) {
 886:                 throw new FailedSaveException('Failed to save object, error is ['.$e->getMessage().']');
 887:                 return;
 888:             }
 889: 
 890:             $stmt->close();
 891:         }else{
 892:             // there has been an error, so decrement the version number back
 893:             $temp = $this->BO->getVersionNumber()->getValue();
 894:             $this->BO->set('version_num', $temp-1);
 895: 
 896:             throw new FailedSaveException('Failed to save object, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 897:         }
 898: 
 899:         if($this->BO->getMaintainHistory())
 900:             $this->BO->saveHistory();
 901:     }
 902: 
 903:     /**
 904:      * (non-PHPdoc)
 905:      * @see alpha/model/AlphaDAOProviderInterface::saveAttribute()
 906:      */
 907:     public function saveAttribute($attribute, $value) {
 908:         self::$logger->debug('>>saveAttribute(attribute=['.$attribute.'], value=['.$value.'])');
 909: 
 910:         // assume that it is a persistent object that needs to be updated
 911:         $sqlQuery = 'UPDATE '.$this->BO->getTableName().' SET '.$attribute.'=:attribute WHERE OID=:OID;';
 912: 
 913:         $this->BO->setLastQuery($sqlQuery);
 914:         $stmt = self::getConnection()->prepare($sqlQuery);
 915: 
 916:         if($stmt instanceof SQLite3Stmt) {
 917:             if($this->BO->getPropObject($attribute) instanceof Integer)
 918:                 $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
 919:             else
 920:                 $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
 921: 
 922:             $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
 923: 
 924:             $stmt->execute();
 925:         }else{
 926:             throw new FailedSaveException('Failed to save attribute, error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
 927:         }
 928: 
 929:         $stmt->close();
 930: 
 931:         $this->{$attribute} = $value;
 932: 
 933:         if($this->BO->getMaintainHistory())
 934:             $this->BO->saveHistory();
 935: 
 936:         self::$logger->debug('<<saveAttribute');
 937:     }
 938: 
 939:     /**
 940:      * (non-PHPdoc)
 941:      * @see alpha/model/AlphaDAOProviderInterface::saveHistory()
 942:      */
 943:     public function saveHistory() {
 944:         self::$logger->debug('>>saveHistory()');
 945: 
 946:         // get the class attributes
 947:         $reflection = new ReflectionClass(get_class($this->BO));
 948:         $properties = $reflection->getProperties();
 949:         $sqlQuery = '';
 950:         $stmt = null;
 951: 
 952:         $savedFields = array();
 953:         $attributeNames = array();
 954:         $attributeValues = array();
 955: 
 956:         $sqlQuery = 'INSERT INTO '.$this->BO->getTableName().'_history (';
 957: 
 958:         foreach($properties as $propObj) {
 959:             $propName = $propObj->name;
 960:             if (!in_array($propName, $this->BO->getTransientAttributes())) {
 961:                 $sqlQuery .= "$propName,";
 962:                 $attributeNames[] = $propName;
 963:                 $attributeValues[] = $this->BO->get($propName);
 964:                 $savedFields[] = $propName;
 965:             }
 966:         }
 967: 
 968:         if($this->BO->isTableOverloaded())
 969:             $sqlQuery .= 'classname,';
 970: 
 971:         $sqlQuery = rtrim($sqlQuery, ",");
 972: 
 973:         $sqlQuery .= ') VALUES (';
 974: 
 975:         foreach($savedFields as $saveField)
 976:             $sqlQuery.= ':'.$savedField.',';
 977: 
 978:         if($this->BO->isTableOverloaded())
 979:             $sqlQuery.= ':classname,';
 980: 
 981:         $sqlQuery = rtrim($sqlQuery, ',').')';
 982: 
 983:         $this->BO->setLastQuery($sqlQuery);
 984:         self::$logger->debug('Query ['.$sqlQuery.']');
 985: 
 986:         $stmt = self::getConnection()->prepare($sqlQuery);
 987: 
 988:         if($stmt instanceof SQLite3Stmt) {
 989:             foreach($savedFields as $savedField) {
 990:                 if($this->BO->get($savedField) instanceof Integer)
 991:                     $stmt->bindValue(':'.$savedField, $savedField, SQLITE3_INTEGER);
 992:                 else
 993:                     $stmt->bindValue(':'.$savedField, $savedField, SQLITE3_TEXT);
 994:             }
 995: 
 996:             if($this->BO->isTableOverloaded())
 997:                 $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
 998: 
 999:             $stmt->execute();
1000:         }else{
1001:             throw new FailedSaveException('Failed to save object history, error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
1002:         }
1003:     }
1004: 
1005:     /**
1006:      * (non-PHPdoc)
1007:      * @see alpha/model/AlphaDAOProviderInterface::delete()
1008:      */
1009:     public function delete() {
1010:         self::$logger->debug('>>delete()');
1011: 
1012:         $sqlQuery = "DELETE FROM ".$this->BO->getTableName()." WHERE OID = :OID;";
1013: 
1014:         $this->BO->setLastQuery($sqlQuery);
1015: 
1016:         $stmt = self::getConnection()->prepare($sqlQuery);
1017: 
1018:         if($stmt instanceof SQLite3Stmt) {
1019:             $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
1020:             $stmt->execute();
1021:             self::$logger->debug('Deleted the object ['.$this->BO->getOID().'] of class ['.get_class($this->BO).']');
1022:         }else{
1023:             throw new FailedDeleteException('Failed to delete object ['.$this->BO->getOID().'], error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
1024:         }
1025: 
1026:         $stmt->close();
1027: 
1028:         self::$logger->debug('<<delete');
1029:     }
1030: 
1031:     /**
1032:      * (non-PHPdoc)
1033:      * @see alpha/model/AlphaDAOProviderInterface::getVersion()
1034:      */
1035:     public function getVersion() {
1036:         self::$logger->debug('>>getVersion()');
1037: 
1038:         $sqlQuery = 'SELECT version_num FROM '.$this->BO->getTableName().' WHERE OID = :OID;';
1039:         $this->BO->setLastQuery($sqlQuery);
1040: 
1041:         $stmt = self::getConnection()->prepare($sqlQuery);
1042: 
1043:         if($stmt instanceof SQLite3Stmt) {
1044:             $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
1045: 
1046:             $result = $stmt->execute();
1047: 
1048:             // there should only ever be one (or none)
1049:             $row = $result->fetchArray(SQLITE3_ASSOC);
1050: 
1051:             $stmt->close();
1052:         }else{
1053:             self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
1054:             if(!$this->BO->checkTableExists()) {
1055:                 $this->BO->makeTable();
1056:                 if($this->BO->getMaintainHistory())
1057:                                         $this->BO->makeHistoryTable();
1058:                 throw new BONotFoundException('Failed to get the version number, table did not exist so had to create!');
1059:             }
1060:             return;
1061:         }
1062: 
1063:         if(!isset($row['version_num']) || $row['version_num'] < 1) {
1064:             self::$logger->debug('<<getVersion [0]');
1065:             return 0;
1066:         }else{
1067:             $version_num = $row['version_num'];
1068: 
1069:             self::$logger->debug('<<getVersion ['.$version_num.']');
1070:             return $version_num;
1071:         }
1072:     }
1073: 
1074:     /**
1075:      * (non-PHPdoc)
1076:      * @see alpha/model/AlphaDAOProviderInterface::makeTable()
1077:      */
1078:     public function makeTable() {
1079:         self::$logger->debug('>>makeTable()');
1080: 
1081:         $sqlQuery = "CREATE TABLE ".$this->BO->getTableName()." (OID INTEGER PRIMARY KEY,";
1082: 
1083:         // get the class attributes
1084:         $reflection = new ReflectionClass(get_class($this->BO));
1085:         $properties = $reflection->getProperties();
1086: 
1087:         $foreignKeys = array();
1088: 
1089:         foreach($properties as $propObj) {
1090:             $propName = $propObj->name;
1091: 
1092:             if(!in_array($propName, $this->BO->getTransientAttributes()) && $propName != "OID") {
1093:                 $propClass = get_class($this->BO->getPropObject($propName));
1094: 
1095:                 switch (strtoupper($propClass)) {
1096:                     case "INTEGER":
1097:                         // special properties for RelationLookup OIDs
1098:                         if($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID')) {
1099:                             // TODO add a $foreignKeys entry here...
1100:                             $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize().") NOT NULL,";
1101:                         } else {
1102:                             $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize()."),";
1103:                         }
1104:                     break;
1105:                     case "DOUBLE":
1106:                         $sqlQuery .= "$propName REAL(".$this->BO->getPropObject($propName)->getSize(true)."),";
1107:                     break;
1108:                     case "STRING":
1109:                         $sqlQuery .= "$propName TEXT(".$this->BO->getPropObject($propName)->getSize()."),";
1110:                     break;
1111:                     case "TEXT":
1112:                         $sqlQuery .= "$propName TEXT,";
1113:                     break;
1114:                     case "BOOLEAN":
1115:                         $sqlQuery .= "$propName INTEGER(1) DEFAULT '0',";
1116:                     break;
1117:                     case "DATE":
1118:                         $sqlQuery .= "$propName TEXT,";
1119:                     break;
1120:                     case "TIMESTAMP":
1121:                         $sqlQuery .= "$propName TEXT,";
1122:                     break;
1123:                     case "ENUM":
1124:                         $sqlQuery .= "$propName TEXT,";
1125:                     break;
1126:                     case "DENUM":
1127:                         $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1128:                         $sqlQuery .= "$propName INTEGER(11),";
1129:                     break;
1130:                     case "RELATION":
1131:                         $sqlQuery .= "$propName INTEGER(11),";
1132: 
1133:                         $rel = $this->BO->getPropObject($propName);
1134: 
1135:                         $relatedField = $rel->getRelatedClassField();
1136:                         $relatedClass = $rel->getRelatedClass();
1137:                         $relatedBO = new $relatedClass;
1138:                         $tableName = $relatedBO->getTableName();
1139:                         $foreignKeys[$propName] = array($tableName, $relatedField);
1140:                     break;
1141:                     default:
1142:                         $sqlQuery .= "";
1143:                     break;
1144:                 }
1145:             }
1146:         }
1147:         if($this->BO->isTableOverloaded())
1148:             $sqlQuery .= "classname TEXT(100)";
1149:         else
1150:             $sqlQuery = substr($sqlQuery, 0, -1);
1151: 
1152:         if(count($foreignKeys) > 0 ) {
1153:             foreach ($foreignKeys as $field => $related) {
1154:                 $sqlQuery .= ', FOREIGN KEY ('.$field.') REFERENCES '.$related[0].'('.$related[1].')';
1155:             }
1156:         }
1157: 
1158:         $sqlQuery .= ');';
1159: 
1160:         $this->BO->setLastQuery($sqlQuery);
1161: 
1162:         if(!self::getConnection()->exec($sqlQuery)) {
1163:             throw new AlphaException('Failed to create the table ['.$this->BO->getTableName().'] for the class ['.get_class($this->BO).'], database error is ['.self::getLastDatabaseError().']');
1164:             self::$logger->debug('<<makeTable');
1165:         }
1166: 
1167:         // check the table indexes if any additional ones required
1168:         //$this->checkIndexes();
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: ?>