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