1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46:
47: class AlphaDAOProviderSQLite implements AlphaDAOProviderInterface {
48: 49: 50: 51: 52: 53:
54: private static $logger = null;
55:
56: 57: 58: 59: 60: 61:
62: private static $connection;
63:
64: 65: 66: 67: 68: 69:
70: private $BO;
71:
72: 73: 74: 75: 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: 86: 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: 104: 105:
106: public static function disconnect() {
107: if(isset(self::$connection)) {
108: self::$connection->close();
109: self::$connection = null;
110: }
111: }
112:
113: 114: 115: 116:
117: public static function getLastDatabaseError() {
118: self::$connection->lastErrorMsg();
119: }
120:
121: 122: 123: 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: 144: 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:
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:
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:
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:
203: if($prop->getRelationType() == 'ONE-TO-MANY') {
204: $this->BO->set($propObj->name, $this->BO->getOID());
205: }
206:
207:
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:
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: 236: 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:
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:
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:
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:
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:
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: 350: 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:
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:
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:
397: }
398: }
399:
400: self::$logger->debug('<<loadAll ['.count($objects).']');
401: return $objects;
402: }
403:
404: 405: 406: 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:
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:
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: 508: 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:
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:
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:
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:
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:
585: }
586: }
587:
588: $stmt->close();
589:
590: self::$logger->debug('<<loadAllByAttributes ['.count($objects).']');
591: return $objects;
592: }
593:
594: 595: 596: 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:
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: 637: 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:
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: 673: 674:
675: public function save() {
676: self::$logger->debug('>>save()');
677:
678:
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:
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:
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:
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);
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:
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:
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:
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:
829: if($prop->getRelationType() == 'MANY-TO-MANY') {
830: try {
831: try{
832:
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:
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:
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:
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:
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: 899: 900:
901: public function saveAttribute($attribute, $value) {
902: self::$logger->debug('>>saveAttribute(attribute=['.$attribute.'], value=['.$value.'])');
903:
904:
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: 939: 940:
941: public function saveHistory() {
942: self::$logger->debug('>>saveHistory()');
943:
944:
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: 1005: 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: 1031: 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:
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: 1073: 1074:
1075: public function makeTable() {
1076: self::$logger->debug('>>makeTable()');
1077:
1078: $sqlQuery = "CREATE TABLE ".$this->BO->getTableName()." (OID INTEGER PRIMARY KEY,";
1079:
1080:
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:
1095: if($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID')) {
1096:
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:
1165: $this->checkIndexes();
1166:
1167: if($this->BO->getMaintainHistory())
1168: $this->BO->makeHistoryTable();
1169:
1170: self::$logger->debug('<<makeTable');
1171: }
1172:
1173: 1174: 1175: 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:
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:
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: 1252: 1253:
1254: public function rebuildTable() {
1255: self::$logger->debug('>>rebuildTable()');
1256:
1257:
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: 1274: 1275:
1276: public function dropTable($tableName=null) {
1277: self::$logger->debug('>>dropTable()');
1278:
1279: if($tableName == null)
1280: $tableName = $this->BO->getTableName();
1281:
1282:
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: 1297: 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:
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: 1367: 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: 1396: 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:
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: 1437: 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: 1464: 1465: 1466: 1467: 1468:
1469: public function setEnumOptions() {
1470: throw new NotImplementedException('AlphaDAOProviderInterface::setEnumOptions() not implemented by the SQLite3 provider');
1471: }
1472:
1473: 1474: 1475: 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: 1508: 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: 1546: 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:
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:
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:
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: 1622: 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:
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:
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: 1683: 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: 1708: 1709: 1710:
1711: private function checkIndexes() {
1712: self::$logger->debug('>>checkIndexes()');
1713:
1714: $indexNames = $this->BO->getIndexes();
1715:
1716:
1717: foreach($this->BO->getUniqueAttributes() as $prop) {
1718:
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:
1754:
1755: self::$logger->debug('<<checkIndexes');
1756: }
1757:
1758: 1759: 1760: 1761: 1762: 1763:
1764: public function createForeignIndex($attributeName, $relatedClass, $relatedClassAttribute) {
1765: self::$logger->info('>>createForeignIndex(attributeName=['.$attributeName.'], relatedClass=['.$relatedClass.'], relatedClassAttribute=['.$relatedClassAttribute.']');
1766:
1767: 1768: 1769: 1770: 1771: 1772: 1773: 1774:
1775: try {
1776: AlphaDAO::begin($this->BO);
1777:
1778:
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:
1786: $this->BO->makeTable();
1787:
1788: self::$logger->info('Made a new copy of the table ['.$this->BO->getTableName().']');
1789:
1790:
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:
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: 1815: 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: 1844: 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: 1860: 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:
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: 1898: 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:
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:
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:
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: 1955: 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: 1970: 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: 1985: 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)
1998: throw new AlphaException('Error rolling back a transaction, error is ['.self::getLastDatabaseError().']');
1999: }
2000:
2001: self::$logger->debug('<<rollback');
2002: }
2003:
2004: 2005: 2006: 2007:
2008: public function setBO($BO) {
2009: $this->BO = $BO;
2010: }
2011: }
2012:
2013: ?>