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