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