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