1: <?php
2:
3: namespace Alpha\Model;
4:
5: use Alpha\Model\Type\Integer;
6: use Alpha\Model\Type\Timestamp;
7: use Alpha\Model\Type\DEnum;
8: use Alpha\Model\Type\Relation;
9: use Alpha\Model\Type\RelationLookup;
10: use Alpha\Util\Config\ConfigProvider;
11: use Alpha\Util\Logging\Logger;
12: use Alpha\Util\Http\Session\SessionProviderFactory;
13: use Alpha\Exception\AlphaException;
14: use Alpha\Exception\FailedSaveException;
15: use Alpha\Exception\FailedDeleteException;
16: use Alpha\Exception\FailedIndexCreateException;
17: use Alpha\Exception\LockingException;
18: use Alpha\Exception\ValidationException;
19: use Alpha\Exception\CustomQueryException;
20: use Alpha\Exception\RecordNotFoundException;
21: use Alpha\Exception\BadTableNameException;
22: use Alpha\Exception\NotImplementedException;
23: use Alpha\Exception\PHPException;
24: use Exception;
25: use SQLite3Stmt;
26: use SQLite3;
27: use ReflectionClass;
28:
29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70:
71: class ActiveRecordProviderSQLite implements ActiveRecordProviderInterface
72: {
73: 74: 75: 76: 77: 78: 79:
80: private static $logger = null;
81:
82: 83: 84: 85: 86: 87: 88:
89: private static $connection;
90:
91: 92: 93: 94: 95: 96: 97:
98: private $BO;
99:
100: 101: 102: 103: 104: 105: 106:
107: private $foreignKeys = array();
108:
109: 110: 111: 112: 113:
114: public function __construct()
115: {
116: self::$logger = new Logger('ActiveRecordProviderSQLite');
117: self::$logger->debug('>>__construct()');
118:
119: self::$logger->debug('<<__construct');
120: }
121:
122: 123: 124: 125: 126:
127: public static function getConnection()
128: {
129: $config = ConfigProvider::getInstance();
130:
131: if (!isset(self::$connection)) {
132: try {
133: self::$connection = new SQLite3($config->get('db.file.path'));
134: } catch (\Exception $e) {
135: self::$logger->fatal('Could not open SQLite database: ['.$e->getMessage().']');
136: }
137: }
138:
139: return self::$connection;
140: }
141:
142: 143: 144: 145: 146:
147: public static function disconnect()
148: {
149: if (isset(self::$connection)) {
150: self::$connection->close();
151: self::$connection = null;
152: }
153: }
154:
155: 156: 157: 158: 159:
160: public static function getLastDatabaseError()
161: {
162: self::$connection->lastErrorMsg();
163: }
164:
165: 166: 167: 168: 169:
170: public function query($sqlQuery)
171: {
172: $this->BO->setLastQuery($sqlQuery);
173:
174: $resultArray = array();
175:
176: if (!$result = self::getConnection()->query($sqlQuery)) {
177: throw new CustomQueryException('Failed to run the custom query, SQLite error is ['.self::getLastDatabaseError().'], query ['.$sqlQuery.']');
178:
179: return array();
180: } else {
181: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
182: array_push($resultArray, $row);
183: }
184:
185: return $resultArray;
186: }
187: }
188:
189: 190: 191: 192: 193:
194: public function load($OID, $version = 0)
195: {
196: self::$logger->debug('>>load(OID=['.$OID.'], version=['.$version.'])');
197:
198: $config = ConfigProvider::getInstance();
199:
200: $attributes = $this->BO->getPersistentAttributes();
201: $fields = '';
202: foreach ($attributes as $att) {
203: $fields .= $att.',';
204: }
205: $fields = mb_substr($fields, 0, -1);
206:
207: if ($version > 0) {
208: $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().'_history WHERE OID = :OID AND version_num = :version LIMIT 1;';
209: } else {
210: $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().' WHERE OID = :OID LIMIT 1;';
211: }
212: $this->BO->setLastQuery($sqlQuery);
213:
214: try {
215: $stmt = self::getConnection()->prepare($sqlQuery);
216:
217: $row = array();
218:
219: if ($version > 0) {
220: $stmt->bindValue(':version', $version, SQLITE3_INTEGER);
221: }
222:
223: $stmt->bindValue(':OID', $OID, SQLITE3_INTEGER);
224:
225: $result = $stmt->execute();
226:
227:
228: $row = $result->fetchArray(SQLITE3_ASSOC);
229:
230: $stmt->close();
231: } catch (PHPException $e) {
232: self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
233: if (!$this->BO->checkTableExists()) {
234: $this->BO->makeTable();
235:
236: throw new RecordNotFoundException('Failed to load object of OID ['.$OID.'], table ['.$this->BO->getTableName().'] did not exist so had to create!');
237: }
238:
239: return;
240: }
241:
242: if (!isset($row['OID']) || $row['OID'] < 1) {
243: throw new RecordNotFoundException('Failed to load object of OID ['.$OID.'] not found in database.');
244: self::$logger->debug('<<load');
245:
246: return;
247: }
248:
249:
250: $reflection = new ReflectionClass(get_class($this->BO));
251: $properties = $reflection->getProperties();
252:
253: try {
254: foreach ($properties as $propObj) {
255: $propName = $propObj->name;
256:
257:
258: if (!in_array($propName, $this->BO->getTransientAttributes())) {
259: $this->BO->set($propName, $row[$propName]);
260: } elseif (!$propObj->isPrivate() && $this->BO->getPropObject($propName) instanceof Relation) {
261: $prop = $this->BO->getPropObject($propName);
262:
263:
264: if ($prop->getRelationType() == 'ONE-TO-MANY') {
265: $this->BO->set($propObj->name, $this->BO->getOID());
266: }
267:
268:
269: if ($prop->getRelationType() == 'MANY-TO-ONE' && isset($row[$propName])) {
270: $this->BO->set($propObj->name, $row[$propName]);
271: }
272: }
273: }
274: } catch (IllegalArguementException $e) {
275: self::$logger->warn('Bad data stored in the table ['.$this->BO->getTableName().'], field ['.$propObj->name.'] bad value['.$row[$propObj->name].'], exception ['.$e->getMessage().']');
276: } catch (PHPException $e) {
277:
278: if ($this->BO->checkTableNeedsUpdate()) {
279: $missingFields = $this->BO->findMissingFields();
280:
281: $count = count($missingFields);
282:
283: for ($i = 0; $i < $count; ++$i) {
284: $this->BO->addProperty($missingFields[$i]);
285: }
286:
287: throw new RecordFoundException('Failed to load object of OID ['.$OID.'], table ['.$this->BO->getTableName().'] was out of sync with the database so had to be updated!');
288: self::$logger->debug('<<load');
289:
290: return;
291: }
292: }
293:
294: self::$logger->debug('<<load');
295: }
296:
297: 298: 299: 300: 301:
302: public function loadAllOldVersions($OID)
303: {
304: self::$logger->debug('>>loadAllOldVersions(OID=['.$OID.'])');
305:
306: $config = ConfigProvider::getInstance();
307:
308: if (!$this->BO->getMaintainHistory()) {
309: throw new RecordFoundException('loadAllOldVersions method called on an active record where no history is maintained!');
310: }
311:
312: $sqlQuery = 'SELECT version_num FROM '.$this->BO->getTableName().'_history WHERE OID = \''.$OID.'\' ORDER BY version_num;';
313:
314: $this->BO->setLastQuery($sqlQuery);
315:
316: if (!$result = self::getConnection()->query($sqlQuery)) {
317: throw new RecordNotFoundException('Failed to load object versions, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
318: self::$logger->debug('<<loadAllOldVersions [0]');
319:
320: return array();
321: }
322:
323:
324: $objects = array();
325: $count = 0;
326: $RecordClass = get_class($this->BO);
327:
328: while ($row = $result->fetchArray()) {
329: try {
330: $obj = new $RecordClass();
331: $obj->load($OID, $row['version_num']);
332: $objects[$count] = $obj;
333: ++$count;
334: } catch (ResourceNotAllowedException $e) {
335:
336: }
337: }
338:
339: self::$logger->warn('<<loadAllOldVersions ['.count($objects).']');
340:
341: return $objects;
342: }
343:
344: 345: 346: 347: 348:
349: public function loadByAttribute($attribute, $value, $ignoreClassType = false, $loadAttributes = array())
350: {
351: self::$logger->debug('>>loadByAttribute(attribute=['.$attribute.'], value=['.$value.'], ignoreClassType=['.$ignoreClassType.'],
352: loadAttributes=['.var_export($loadAttributes, true).'])');
353:
354: if (count($loadAttributes) == 0) {
355: $attributes = $this->BO->getPersistentAttributes();
356: } else {
357: $attributes = $loadAttributes;
358: }
359:
360: $fields = '';
361: foreach ($attributes as $att) {
362: $fields .= $att.',';
363: }
364: $fields = mb_substr($fields, 0, -1);
365:
366: if (!$ignoreClassType && $this->BO->isTableOverloaded()) {
367: $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().' WHERE '.$attribute.' = :attribute AND classname = :classname LIMIT 1;';
368: } else {
369: $sqlQuery = 'SELECT '.$fields.' FROM '.$this->BO->getTableName().' WHERE '.$attribute.' = :attribute LIMIT 1;';
370: }
371:
372: self::$logger->debug('Query=['.$sqlQuery.']');
373:
374: $this->BO->setLastQuery($sqlQuery);
375: $stmt = self::getConnection()->prepare($sqlQuery);
376:
377: $row = array();
378:
379: if ($stmt instanceof SQLite3Stmt) {
380: if ($this->BO->getPropObject($attribute) instanceof Integer) {
381: if (!$ignoreClassType && $this->BO->isTableOverloaded()) {
382: $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
383: $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
384: } else {
385: $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
386: }
387: } else {
388: if (!$ignoreClassType && $this->BO->isTableOverloaded()) {
389: $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
390: $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
391: } else {
392: $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
393: }
394: }
395:
396: $result = $stmt->execute();
397:
398:
399: $row = $result->fetchArray(SQLITE3_ASSOC);
400:
401: $stmt->close();
402: } else {
403: self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
404: if (!$this->BO->checkTableExists()) {
405: $this->BO->makeTable();
406:
407: throw new RecordNotFoundException('Failed to load object by attribute ['.$attribute.'] and value ['.$value.'], table did not exist so had to create!');
408: }
409:
410: return;
411: }
412:
413: if (!isset($row['OID']) || $row['OID'] < 1) {
414: throw new RecordNotFoundException('Failed to load object by attribute ['.$attribute.'] and value ['.$value.'], not found in database.');
415: self::$logger->debug('<<loadByAttribute');
416:
417: return;
418: }
419:
420: $this->OID = $row['OID'];
421:
422:
423: $reflection = new ReflectionClass(get_class($this->BO));
424: $properties = $reflection->getProperties();
425:
426: try {
427: foreach ($properties as $propObj) {
428: $propName = $propObj->name;
429:
430: if (isset($row[$propName])) {
431:
432: if (!in_array($propName, $this->BO->getTransientAttributes())) {
433: $this->BO->set($propName, $row[$propName]);
434: } elseif (!$propObj->isPrivate() && $this->BO->get($propName) != '' && $this->BO->getPropObject($propName) instanceof Relation) {
435: $prop = $this->BO->getPropObject($propName);
436:
437:
438: if ($prop->getRelationType() == 'ONE-TO-MANY') {
439: $this->BO->set($propObj->name, $this->BO->getOID());
440: }
441: }
442: }
443: }
444: } catch (IllegalArguementException $e) {
445: self::$logger->warn('Bad data stored in the table ['.$this->BO->getTableName().'], field ['.$propObj->name.'] bad value['.$row[$propObj->name].'], exception ['.$e->getMessage().']');
446: } catch (PHPException $e) {
447:
448: if ($this->BO->checkTableNeedsUpdate()) {
449: $missingFields = $this->BO->findMissingFields();
450:
451: $count = count($missingFields);
452:
453: for ($i = 0; $i < $count; ++$i) {
454: $this->BO->addProperty($missingFields[$i]);
455: }
456:
457: throw new RecordNotFoundException('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!');
458: self::$logger->debug('<<loadByAttribute');
459:
460: return;
461: }
462: }
463:
464: self::$logger->debug('<<loadByAttribute');
465: }
466:
467: 468: 469: 470: 471:
472: public function loadAll($start = 0, $limit = 0, $orderBy = 'OID', $order = 'ASC', $ignoreClassType = false)
473: {
474: self::$logger->debug('>>loadAll(start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
475:
476:
477: try {
478: $field = $this->BO->get($orderBy);
479: } catch (AlphaException $e) {
480: throw new AlphaException('The field name ['.$orderBy.'] provided in the param orderBy does not exist on the class ['.get_class($this->BO).']');
481: }
482:
483: if (!$ignoreClassType && $this->BO->isTableOverloaded()) {
484: if ($limit == 0) {
485: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE classname = \''.addslashes(get_class($this->BO)).'\' ORDER BY '.$orderBy.' '.$order.';';
486: } else {
487: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE classname = \''.addslashes(get_class($this->BO)).'\' ORDER BY '.$orderBy.' '.$order.' LIMIT '.
488: $limit.' OFFSET '.$start.';';
489: }
490: } else {
491: if ($limit == 0) {
492: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' ORDER BY '.$orderBy.' '.$order.';';
493: } else {
494: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' ORDER BY '.$orderBy.' '.$order.' LIMIT '.$limit.' OFFSET '.$start.';';
495: }
496: }
497:
498: $this->BO->setLastQuery($sqlQuery);
499:
500: if (!$result = self::getConnection()->query($sqlQuery)) {
501: throw new RecordNotFoundException('Failed to load object OIDs, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
502: self::$logger->debug('<<loadAll [0]');
503:
504: return array();
505: }
506:
507:
508: $objects = array();
509: $count = 0;
510: $RecordClass = get_class($this->BO);
511:
512: while ($row = $result->fetchArray()) {
513: try {
514: $obj = new $RecordClass();
515: $obj->load($row['OID']);
516: $objects[$count] = $obj;
517: ++$count;
518: } catch (ResourceNotAllowedException $e) {
519:
520: }
521: }
522:
523: self::$logger->debug('<<loadAll ['.count($objects).']');
524:
525: return $objects;
526: }
527:
528: 529: 530: 531: 532:
533: public function loadAllByAttribute($attribute, $value, $start = 0, $limit = 0, $orderBy = 'OID', $order = 'ASC', $ignoreClassType = false, $constructorArgs = array())
534: {
535: self::$logger->debug('>>loadAllByAttribute(attribute=['.$attribute.'], value=['.$value.'], start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.'], constructorArgs=['.print_r($constructorArgs, true).']');
536:
537: if ($start != 0 && $limit != 0) {
538: $limit = ' LIMIT '.$limit.' OFFSET '.$start.';';
539: } else {
540: $limit = ';';
541: }
542:
543: if (!$ignoreClassType && $this->BO->isTableOverloaded()) {
544: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName()." WHERE $attribute = :attribute AND classname = :classname ORDER BY ".$orderBy.' '.$order.$limit;
545: } else {
546: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName()." WHERE $attribute = :attribute ORDER BY ".$orderBy.' '.$order.$limit;
547: }
548:
549: $this->BO->setLastQuery($sqlQuery);
550: self::$logger->debug($sqlQuery);
551:
552: $stmt = self::getConnection()->prepare($sqlQuery);
553:
554: $objects = array();
555:
556: if ($stmt instanceof SQLite3Stmt) {
557: if ($this->BO->getPropObject($attribute) instanceof Integer) {
558: if ($this->BO->isTableOverloaded()) {
559: $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
560: $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
561: } else {
562: $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
563: }
564: } else {
565: if ($this->BO->isTableOverloaded()) {
566: $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
567: $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
568: } else {
569: $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
570: }
571: }
572:
573: $result = $stmt->execute();
574:
575:
576: $count = 0;
577: $RecordClass = get_class($this->BO);
578:
579: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
580: try {
581: $argsCount = count($constructorArgs);
582:
583: if ($argsCount < 1) {
584: $obj = new $RecordClass();
585: } else {
586: switch ($argsCount) {
587: case 1:
588: $obj = new $RecordClass($constructorArgs[0]);
589: break;
590: case 2:
591: $obj = new $RecordClass($constructorArgs[0], $constructorArgs[1]);
592: break;
593: case 3:
594: $obj = new $RecordClass($constructorArgs[0], $constructorArgs[1], $constructorArgs[2]);
595: break;
596: case 4:
597: $obj = new $RecordClass($constructorArgs[0], $constructorArgs[1], $constructorArgs[2], $constructorArgs[3]);
598: break;
599: case 5:
600: $obj = new $RecordClass($constructorArgs[0], $constructorArgs[1], $constructorArgs[2], $constructorArgs[3], $constructorArgs[4]);
601: break;
602: default:
603: throw new IllegalArguementException('Too many elements in the $constructorArgs array passed to the loadAllByAttribute method!');
604: break;
605: }
606: }
607:
608: $obj->load($row['OID']);
609: $objects[$count] = $obj;
610: ++$count;
611: } catch (ResourceNotAllowedException $e) {
612:
613: }
614: }
615:
616: $stmt->close();
617: } else {
618: self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
619:
620: if (!$this->BO->checkTableExists()) {
621: $this->BO->makeTable();
622:
623: throw new RecordFoundException('Failed to load objects by attribute ['.$attribute.'] and value ['.$value.'], table did not exist so had to create!');
624: }
625:
626: self::$logger->debug('<<loadAllByAttribute []');
627:
628: return array();
629: }
630:
631: self::$logger->debug('<<loadAllByAttribute ['.count($objects).']');
632:
633: return $objects;
634: }
635:
636: 637: 638: 639: 640:
641: public function loadAllByAttributes($attributes = array(), $values = array(), $start = 0, $limit = 0, $orderBy = 'OID', $order = 'ASC', $ignoreClassType = false, $constructorArgs = array())
642: {
643: self::$logger->debug('>>loadAllByAttributes(attributes=['.var_export($attributes, true).'], values=['.var_export($values, true).'], start=['.
644: $start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.'], constructorArgs=['.print_r($constructorArgs, true).']');
645:
646: $whereClause = ' WHERE';
647:
648: $count = count($attributes);
649:
650: for ($i = 0; $i < $count; ++$i) {
651: $whereClause .= ' '.$attributes[$i].' = :'.$attributes[$i].' AND';
652: self::$logger->debug($whereClause);
653: }
654:
655: if (!$ignoreClassType && $this->BO->isTableOverloaded()) {
656: $whereClause .= ' classname = :classname AND';
657: }
658:
659:
660: $whereClause = mb_substr($whereClause, 0, -4);
661:
662: if ($limit != 0) {
663: $limit = ' LIMIT '.$limit.' OFFSET '.$start.';';
664: } else {
665: $limit = ';';
666: }
667:
668: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().$whereClause.' ORDER BY '.$orderBy.' '.$order.$limit;
669:
670: $this->BO->setLastQuery($sqlQuery);
671:
672: $stmt = self::getConnection()->prepare($sqlQuery);
673:
674: if ($stmt instanceof SQLite3Stmt) {
675:
676: if (count($attributes) > 0 && count($attributes) == count($values)) {
677: for ($i = 0; $i < count($attributes); ++$i) {
678: if (strcspn($values[$i], '0123456789') != strlen($values[$i])) {
679: $stmt->bindValue(':'.$attributes[$i], $values[$i], SQLITE3_INTEGER);
680: } else {
681: $stmt->bindValue(':'.$attributes[$i], $values[$i], SQLITE3_TEXT);
682: }
683: }
684: } else {
685:
686: if ($this->BO->isTableOverloaded()) {
687: $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
688: }
689: }
690:
691: $result = $stmt->execute();
692: } else {
693: self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
694:
695: if (!$this->BO->checkTableExists()) {
696: $this->BO->makeTable();
697:
698: throw new RecordFoundException('Failed to load objects by attributes ['.var_export($attributes, true).'] and values ['.
699: var_export($values, true).'], table did not exist so had to create!');
700: }
701:
702: self::$logger->debug('<<loadAllByAttributes []');
703:
704: return array();
705: }
706:
707:
708: $objects = array();
709: $count = 0;
710: $RecordClass = get_class($this->BO);
711:
712: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
713: try {
714: $argsCount = count($constructorArgs);
715:
716: if ($argsCount < 1) {
717: $obj = new $RecordClass();
718: } else {
719: switch ($argsCount) {
720: case 1:
721: $obj = new $RecordClass($constructorArgs[0]);
722: break;
723: case 2:
724: $obj = new $RecordClass($constructorArgs[0], $constructorArgs[1]);
725: break;
726: case 3:
727: $obj = new $RecordClass($constructorArgs[0], $constructorArgs[1], $constructorArgs[2]);
728: break;
729: case 4:
730: $obj = new $RecordClass($constructorArgs[0], $constructorArgs[1], $constructorArgs[2], $constructorArgs[3]);
731: break;
732: case 5:
733: $obj = new $RecordClass($constructorArgs[0], $constructorArgs[1], $constructorArgs[2], $constructorArgs[3], $constructorArgs[4]);
734: break;
735: default:
736: throw new IllegalArguementException('Too many elements in the $constructorArgs array passed to the loadAllByAttribute method!');
737: break;
738: }
739: }
740:
741: $obj->load($row['OID']);
742: $objects[$count] = $obj;
743: ++$count;
744: } catch (ResourceNotAllowedException $e) {
745:
746: }
747: }
748:
749: $stmt->close();
750:
751: self::$logger->debug('<<loadAllByAttributes ['.count($objects).']');
752:
753: return $objects;
754: }
755:
756: 757: 758: 759: 760:
761: public function loadAllByDayUpdated($date, $start = 0, $limit = 0, $orderBy = 'OID', $order = 'ASC', $ignoreClassType = false)
762: {
763: self::$logger->debug('>>loadAllByDayUpdated(date=['.$date.'], start=['.$start.'], limit=['.$limit.'], orderBy=['.$orderBy.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
764:
765: if ($start != 0 && $limit != 0) {
766: $limit = ' LIMIT '.$limit.' OFFSET '.$start.';';
767: } else {
768: $limit = ';';
769: }
770:
771: if (!$ignoreClassType && $this->BO->isTableOverloaded()) {
772: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName()." WHERE updated_ts >= '".$date." 00:00:00' AND updated_ts <= '".$date." 23:59:59' AND classname = '".addslashes(get_class($this->BO))."' ORDER BY ".$orderBy.' '.$order.$limit;
773: } else {
774: $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;
775: }
776:
777: $this->BO->setLastQuery($sqlQuery);
778:
779: if (!$result = self::getConnection()->query($sqlQuery)) {
780: throw new RecordNotFoundException('Failed to load object OIDs, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
781: self::$logger->debug('<<loadAllByDayUpdated []');
782:
783: return array();
784: }
785:
786:
787: $objects = array();
788: $count = 0;
789: $RecordClass = get_class($this->BO);
790:
791: while ($row = $result->fetchArray()) {
792: $obj = new $RecordClass();
793: $obj->load($row['OID']);
794: $objects[$count] = $obj;
795: ++$count;
796: }
797:
798: self::$logger->debug('<<loadAllByDayUpdated ['.count($objects).']');
799:
800: return $objects;
801: }
802:
803: 804: 805: 806: 807:
808: public function loadAllFieldValuesByAttribute($attribute, $value, $returnAttribute, $order = 'ASC', $ignoreClassType = false)
809: {
810: self::$logger->debug('>>loadAllFieldValuesByAttribute(attribute=['.$attribute.'], value=['.$value.'], returnAttribute=['.$returnAttribute.'], order=['.$order.'], ignoreClassType=['.$ignoreClassType.']');
811:
812: if (!$ignoreClassType && $this->BO->isTableOverloaded()) {
813: $sqlQuery = 'SELECT '.$returnAttribute.' FROM '.$this->BO->getTableName()." WHERE $attribute = '$value' AND classname = '".addslashes(get_class($this->BO))."' ORDER BY OID ".$order.';';
814: } else {
815: $sqlQuery = 'SELECT '.$returnAttribute.' FROM '.$this->BO->getTableName()." WHERE $attribute = '$value' ORDER BY OID ".$order.';';
816: }
817:
818: $this->BO->setLastQuery($sqlQuery);
819:
820: self::$logger->debug('lastQuery ['.$sqlQuery.']');
821:
822: if (!$result = self::getConnection()->query($sqlQuery)) {
823: throw new RecordNotFoundException('Failed to load field ['.$returnAttribute.'] values, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
824: self::$logger->debug('<<loadAllFieldValuesByAttribute []');
825:
826: return array();
827: }
828:
829:
830: $values = array();
831: $count = 0;
832: $RecordClass = get_class($this->BO);
833:
834: while ($row = $result->fetchArray()) {
835: $values[$count] = $row[$returnAttribute];
836: ++$count;
837: }
838:
839: self::$logger->debug('<<loadAllFieldValuesByAttribute ['.count($values).']');
840:
841: return $values;
842: }
843:
844: 845: 846: 847: 848:
849: public function save()
850: {
851: self::$logger->debug('>>save()');
852:
853: $config = ConfigProvider::getInstance();
854: $sessionProvider = $config->get('session.provider.name');
855: $session = SessionProviderFactory::getInstance($sessionProvider);
856:
857:
858: $reflection = new ReflectionClass(get_class($this->BO));
859: $properties = $reflection->getProperties();
860: $sqlQuery = '';
861: $stmt = null;
862:
863: if ($this->BO->getVersion() != $this->BO->getVersionNumber()->getValue()) {
864: throw new LockingException('Could not save the object as it has been updated by another user. Please try saving again.');
865:
866: return;
867: }
868:
869:
870: if ($session->get('currentUser') != null) {
871: $this->BO->set('updated_by', $session->get('currentUser')->getOID());
872: }
873:
874: $this->BO->set('updated_ts', new Timestamp(date('Y-m-d H:i:s')));
875:
876:
877: if ($this->BO->isTransient()) {
878: $savedFields = array();
879: $sqlQuery = 'INSERT INTO '.$this->BO->getTableName().' (';
880:
881: foreach ($properties as $propObj) {
882: $propName = $propObj->name;
883: if (!in_array($propName, $this->BO->getTransientAttributes())) {
884:
885: if ($propName != 'OID' && $propName != 'version_num') {
886: $sqlQuery .= "$propName,";
887: $savedFields[] = $propName;
888: }
889:
890: if ($propName == 'version_num') {
891: $sqlQuery .= 'version_num,';
892: $savedFields[] = 'version_num';
893: }
894: }
895: }
896: if ($this->BO->isTableOverloaded()) {
897: $sqlQuery .= 'classname,';
898: }
899:
900: $sqlQuery = rtrim($sqlQuery, ',');
901:
902: $sqlQuery .= ') VALUES (';
903:
904: foreach ($savedFields as $savedField) {
905: $sqlQuery .= ':'.$savedField.',';
906: }
907:
908: if ($this->BO->isTableOverloaded()) {
909: $sqlQuery .= ':classname,';
910: }
911:
912: $sqlQuery = rtrim($sqlQuery, ',').')';
913:
914: $this->BO->setLastQuery($sqlQuery);
915: self::$logger->debug('Query ['.$sqlQuery.']');
916:
917: $stmt = self::getConnection()->prepare($sqlQuery);
918:
919: if ($stmt instanceof SQLite3Stmt) {
920: foreach ($savedFields as $savedField) {
921: if ($this->BO->get($savedField) instanceof Integer) {
922: $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_INTEGER);
923: } else {
924: $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_TEXT);
925: }
926: }
927:
928: if ($this->BO->isTableOverloaded()) {
929: $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
930: }
931:
932: $stmt->bindValue(':version_num', 1, SQLITE3_INTEGER);
933: $this->BO->set('version_num', 1);
934:
935: try {
936: $stmt->execute();
937: } catch (Exception $e) {
938: if (self::getConnection()->lastErrorCode() == 19) {
939: throw new ValidationException('Unique key violation while trying to save object, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
940: } else {
941: throw new FailedSaveException('Failed to save object, exception ['.$e->getMessage().'], DB error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
942: }
943: }
944: } else {
945: throw new FailedSaveException('Failed to save object, exception ['.$e->getMessage().'], DB error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
946: }
947: } else {
948:
949: $savedFields = array();
950: $sqlQuery = 'UPDATE '.$this->BO->getTableName().' SET ';
951:
952: foreach ($properties as $propObj) {
953: $propName = $propObj->name;
954: if (!in_array($propName, $this->BO->getTransientAttributes())) {
955:
956: if ($propName != 'OID' && $propName != 'version_num') {
957: $sqlQuery .= "$propName = :$propName,";
958: $savedFields[] = $propName;
959: }
960:
961: if ($propName == 'version_num') {
962: $sqlQuery .= 'version_num = :version_num,';
963: $savedFields[] = 'version_num';
964: }
965: }
966: }
967:
968: if ($this->BO->isTableOverloaded()) {
969: $sqlQuery .= 'classname = :classname,';
970: }
971:
972: $sqlQuery = rtrim($sqlQuery, ',');
973:
974: $sqlQuery .= ' WHERE OID=:OID;';
975:
976: $this->BO->setLastQuery($sqlQuery);
977: $stmt = self::getConnection()->prepare($sqlQuery);
978:
979: if ($stmt instanceof SQLite3Stmt) {
980: foreach ($savedFields as $savedField) {
981: if ($this->BO->get($savedField) instanceof Integer) {
982: $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_INTEGER);
983: } else {
984: $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_TEXT);
985: }
986: }
987:
988: if ($this->BO->isTableOverloaded()) {
989: $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
990: }
991:
992: $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
993:
994: $temp = $this->BO->getVersionNumber()->getValue();
995: $this->BO->set('version_num', $temp + 1);
996: $stmt->bindValue(':version_num', $temp + 1, SQLITE3_INTEGER);
997:
998: $stmt->execute();
999: } else {
1000: throw new FailedSaveException('Failed to save object, error is ['.$stmt->error.'], query ['.$this->BO->getLastQuery().']');
1001: }
1002: }
1003:
1004: if ($stmt != null && $stmt != false) {
1005:
1006: if ($this->BO->isTransient()) {
1007: $this->BO->setOID(self::getConnection()->lastInsertRowID());
1008: }
1009:
1010: try {
1011: foreach ($properties as $propObj) {
1012: $propName = $propObj->name;
1013:
1014: if ($this->BO->getPropObject($propName) instanceof Relation) {
1015: $prop = $this->BO->getPropObject($propName);
1016:
1017:
1018: if ($prop->getRelationType() == 'MANY-TO-MANY' && count($prop->getRelatedOIDs()) > 0) {
1019: try {
1020: try {
1021:
1022: $side = $prop->getSide(get_class($this->BO));
1023: } catch (IllegalArguementException $iae) {
1024: $side = $prop->getSide(get_parent_class($this->BO));
1025: }
1026:
1027: $lookUp = $prop->getLookup();
1028:
1029:
1030: try {
1031: if ($side == 'left') {
1032: $lookUp->deleteAllByAttribute('leftID', $this->BO->getOID());
1033: } else {
1034: $lookUp->deleteAllByAttribute('rightID', $this->BO->getOID());
1035: }
1036: } catch (Exception $e) {
1037: throw new FailedSaveException('Failed to delete old RelationLookup objects on the table ['.$prop->getLookup()->getTableName().'], error is ['.$e->getMessage().']');
1038: }
1039:
1040: $OIDs = $prop->getRelatedOIDs();
1041:
1042: if (isset($OIDs) && !empty($OIDs[0])) {
1043:
1044: foreach ($OIDs as $oid) {
1045: $newLookUp = new RelationLookup($lookUp->get('leftClassName'), $lookUp->get('rightClassName'));
1046: if ($side == 'left') {
1047: $newLookUp->set('leftID', $this->BO->getOID());
1048: $newLookUp->set('rightID', $oid);
1049: } else {
1050: $newLookUp->set('rightID', $this->BO->getOID());
1051: $newLookUp->set('leftID', $oid);
1052: }
1053: $newLookUp->save();
1054: }
1055: }
1056: } catch (Exception $e) {
1057: throw new FailedSaveException('Failed to update a MANY-TO-MANY relation on the object, error is ['.$e->getMessage().']');
1058:
1059: return;
1060: }
1061: }
1062:
1063:
1064: if ($prop->getRelationType() == 'ONE-TO-MANY') {
1065: $prop->setValue($this->BO->getOID());
1066: }
1067: }
1068: }
1069: } catch (Exception $e) {
1070: throw new FailedSaveException('Failed to save object, error is ['.$e->getMessage().']');
1071:
1072: return;
1073: }
1074:
1075: $stmt->close();
1076: } else {
1077:
1078: $temp = $this->BO->getVersionNumber()->getValue();
1079: $this->BO->set('version_num', $temp - 1);
1080:
1081: throw new FailedSaveException('Failed to save object, SQLite error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
1082: }
1083:
1084: if ($this->BO->getMaintainHistory()) {
1085: $this->BO->saveHistory();
1086: }
1087: }
1088:
1089: 1090: 1091: 1092: 1093:
1094: public function saveAttribute($attribute, $value)
1095: {
1096: self::$logger->debug('>>saveAttribute(attribute=['.$attribute.'], value=['.$value.'])');
1097:
1098:
1099: $sqlQuery = 'UPDATE '.$this->BO->getTableName().' SET '.$attribute.'=:attribute, version_num =:version WHERE OID=:OID;';
1100:
1101: $this->BO->setLastQuery($sqlQuery);
1102: $stmt = self::getConnection()->prepare($sqlQuery);
1103:
1104: $newVersionNumber = $this->BO->getVersionNumber()->getValue() + 1;
1105:
1106: if ($stmt instanceof SQLite3Stmt) {
1107: if ($this->BO->getPropObject($attribute) instanceof Integer) {
1108: $stmt->bindValue(':attribute', $value, SQLITE3_INTEGER);
1109: } else {
1110: $stmt->bindValue(':attribute', $value, SQLITE3_TEXT);
1111: }
1112:
1113: $stmt->bindValue(':version', $newVersionNumber, SQLITE3_INTEGER);
1114: $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
1115:
1116: $stmt->execute();
1117: } else {
1118: throw new FailedSaveException('Failed to save attribute, error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
1119: }
1120:
1121: $stmt->close();
1122:
1123: $this->BO->set($attribute, $value);
1124: $this->BO->set('version_num', $newVersionNumber);
1125:
1126: if ($this->BO->getMaintainHistory()) {
1127: $this->BO->saveHistory();
1128: }
1129:
1130: self::$logger->debug('<<saveAttribute');
1131: }
1132:
1133: 1134: 1135: 1136: 1137:
1138: public function saveHistory()
1139: {
1140: self::$logger->debug('>>saveHistory()');
1141:
1142:
1143: $reflection = new ReflectionClass(get_class($this->BO));
1144: $properties = $reflection->getProperties();
1145: $sqlQuery = '';
1146: $stmt = null;
1147:
1148: $savedFields = array();
1149: $attributeNames = array();
1150: $attributeValues = array();
1151:
1152: $sqlQuery = 'INSERT INTO '.$this->BO->getTableName().'_history (';
1153:
1154: foreach ($properties as $propObj) {
1155: $propName = $propObj->name;
1156: if (!in_array($propName, $this->BO->getTransientAttributes())) {
1157: $sqlQuery .= "$propName,";
1158: $attributeNames[] = $propName;
1159: $attributeValues[] = $this->BO->get($propName);
1160: $savedFields[] = $propName;
1161: }
1162: }
1163:
1164: if ($this->BO->isTableOverloaded()) {
1165: $sqlQuery .= 'classname,';
1166: }
1167:
1168: $sqlQuery = rtrim($sqlQuery, ',');
1169:
1170: $sqlQuery .= ') VALUES (';
1171:
1172: foreach ($savedFields as $savedField) {
1173: $sqlQuery .= ':'.$savedField.',';
1174: }
1175:
1176: if ($this->BO->isTableOverloaded()) {
1177: $sqlQuery .= ':classname,';
1178: }
1179:
1180: $sqlQuery = rtrim($sqlQuery, ',').')';
1181:
1182: $this->BO->setLastQuery($sqlQuery);
1183: self::$logger->debug('Query ['.$sqlQuery.']');
1184:
1185: $stmt = self::getConnection()->prepare($sqlQuery);
1186:
1187: if ($stmt instanceof SQLite3Stmt) {
1188: foreach ($savedFields as $savedField) {
1189: if ($this->BO->get($savedField) instanceof Integer) {
1190: $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_INTEGER);
1191: } else {
1192: $stmt->bindValue(':'.$savedField, $this->BO->get($savedField), SQLITE3_TEXT);
1193: }
1194: }
1195:
1196: if ($this->BO->isTableOverloaded()) {
1197: $stmt->bindValue(':classname', get_class($this->BO), SQLITE3_TEXT);
1198: }
1199:
1200: $result = $stmt->execute();
1201: } else {
1202: throw new FailedSaveException('Failed to save object history, error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
1203: }
1204: }
1205:
1206: 1207: 1208: 1209: 1210:
1211: public function delete()
1212: {
1213: self::$logger->debug('>>delete()');
1214:
1215: $sqlQuery = 'DELETE FROM '.$this->BO->getTableName().' WHERE OID = :OID;';
1216:
1217: $this->BO->setLastQuery($sqlQuery);
1218:
1219: $stmt = self::getConnection()->prepare($sqlQuery);
1220:
1221: if ($stmt instanceof SQLite3Stmt) {
1222: $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
1223: $stmt->execute();
1224: self::$logger->debug('Deleted the object ['.$this->BO->getOID().'] of class ['.get_class($this->BO).']');
1225: } else {
1226: throw new FailedDeleteException('Failed to delete object ['.$this->BO->getOID().'], error is ['.self::getLastDatabaseError().'], query ['.$this->BO->getLastQuery().']');
1227: }
1228:
1229: $stmt->close();
1230:
1231: self::$logger->debug('<<delete');
1232: }
1233:
1234: 1235: 1236: 1237: 1238:
1239: public function getVersion()
1240: {
1241: self::$logger->debug('>>getVersion()');
1242:
1243: $sqlQuery = 'SELECT version_num FROM '.$this->BO->getTableName().' WHERE OID = :OID;';
1244: $this->BO->setLastQuery($sqlQuery);
1245:
1246: $stmt = self::getConnection()->prepare($sqlQuery);
1247:
1248: if ($stmt instanceof SQLite3Stmt) {
1249: $stmt->bindValue(':OID', $this->BO->getOID(), SQLITE3_INTEGER);
1250:
1251: $result = $stmt->execute();
1252:
1253:
1254: $row = $result->fetchArray(SQLITE3_ASSOC);
1255:
1256: $stmt->close();
1257: } else {
1258: self::$logger->warn('The following query caused an unexpected result ['.$sqlQuery.']');
1259: if (!$this->BO->checkTableExists()) {
1260: $this->BO->makeTable();
1261:
1262: throw new RecordNotFoundException('Failed to get the version number, table did not exist so had to create!');
1263: }
1264:
1265: return;
1266: }
1267:
1268: if (!isset($row['version_num']) || $row['version_num'] < 1) {
1269: self::$logger->debug('<<getVersion [0]');
1270:
1271: return 0;
1272: } else {
1273: $version_num = $row['version_num'];
1274:
1275: self::$logger->debug('<<getVersion ['.$version_num.']');
1276:
1277: return $version_num;
1278: }
1279: }
1280:
1281: 1282: 1283: 1284: 1285:
1286: public function makeTable()
1287: {
1288: self::$logger->debug('>>makeTable()');
1289:
1290: $sqlQuery = 'CREATE TABLE '.$this->BO->getTableName().' (OID INTEGER PRIMARY KEY,';
1291:
1292:
1293: $reflection = new ReflectionClass(get_class($this->BO));
1294: $properties = $reflection->getProperties();
1295:
1296: $foreignKeys = array();
1297:
1298: foreach ($properties as $propObj) {
1299: $propName = $propObj->name;
1300:
1301: if (!in_array($propName, $this->BO->getTransientAttributes()) && $propName != 'OID') {
1302: $propReflect = new ReflectionClass($this->BO->getPropObject($propName));
1303: $propClass = $propReflect->getShortName();
1304:
1305: switch (mb_strtoupper($propClass)) {
1306: case 'INTEGER':
1307:
1308: if ($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID')) {
1309: $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize().') NOT NULL,';
1310: } else {
1311: $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize().'),';
1312: }
1313: break;
1314: case 'DOUBLE':
1315: $sqlQuery .= "$propName REAL(".$this->BO->getPropObject($propName)->getSize(true).'),';
1316: break;
1317: case 'STRING':
1318: $sqlQuery .= "$propName TEXT(".$this->BO->getPropObject($propName)->getSize().'),';
1319: break;
1320: case 'TEXT':
1321: $sqlQuery .= "$propName TEXT,";
1322: break;
1323: case 'BOOLEAN':
1324: $sqlQuery .= "$propName INTEGER(1) DEFAULT '0',";
1325: break;
1326: case 'DATE':
1327: $sqlQuery .= "$propName TEXT,";
1328: break;
1329: case 'TIMESTAMP':
1330: $sqlQuery .= "$propName TEXT,";
1331: break;
1332: case 'ENUM':
1333: $sqlQuery .= "$propName TEXT,";
1334: break;
1335: case 'DENUM':
1336: $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1337: $sqlQuery .= "$propName INTEGER(11),";
1338: break;
1339: case 'RELATION':
1340: $sqlQuery .= "$propName INTEGER(11),";
1341:
1342: $rel = $this->BO->getPropObject($propName);
1343:
1344: $relatedField = $rel->getRelatedClassField();
1345: $relatedClass = $rel->getRelatedClass();
1346: $relatedBO = new $relatedClass();
1347: $tableName = $relatedBO->getTableName();
1348: $foreignKeys[$propName] = array($tableName, $relatedField);
1349: break;
1350: default:
1351: $sqlQuery .= '';
1352: break;
1353: }
1354: }
1355: }
1356: if ($this->BO->isTableOverloaded()) {
1357: $sqlQuery .= 'classname TEXT(100)';
1358: } else {
1359: $sqlQuery = mb_substr($sqlQuery, 0, -1);
1360: }
1361:
1362: if (count($foreignKeys) > 0) {
1363: foreach ($foreignKeys as $field => $related) {
1364: $sqlQuery .= ', FOREIGN KEY ('.$field.') REFERENCES '.$related[0].'('.$related[1].')';
1365: }
1366: }
1367:
1368: if (count($this->foreignKeys) > 0) {
1369: foreach ($this->foreignKeys as $field => $related) {
1370: $sqlQuery .= ', FOREIGN KEY ('.$field.') REFERENCES '.$related[0].'('.$related[1].')';
1371: }
1372: }
1373:
1374: $sqlQuery .= ');';
1375:
1376: $this->BO->setLastQuery($sqlQuery);
1377:
1378: if (!self::getConnection()->exec($sqlQuery)) {
1379: throw new AlphaException('Failed to create the table ['.$this->BO->getTableName().'] for the class ['.get_class($this->BO).'], database error is ['.self::getLastDatabaseError().']');
1380: self::$logger->debug('<<makeTable');
1381: }
1382:
1383:
1384: $this->checkIndexes();
1385:
1386: if ($this->BO->getMaintainHistory()) {
1387: $this->BO->makeHistoryTable();
1388: }
1389:
1390: self::$logger->debug('<<makeTable');
1391: }
1392:
1393: 1394: 1395: 1396: 1397:
1398: public function makeHistoryTable()
1399: {
1400: self::$logger->debug('>>makeHistoryTable()');
1401:
1402: $sqlQuery = 'CREATE TABLE '.$this->BO->getTableName().'_history (OID INTEGER NOT NULL,';
1403:
1404:
1405: $reflection = new ReflectionClass(get_class($this->BO));
1406: $properties = $reflection->getProperties();
1407:
1408: foreach ($properties as $propObj) {
1409: $propName = $propObj->name;
1410:
1411: if (!in_array($propName, $this->BO->getTransientAttributes()) && $propName != 'OID') {
1412: $propReflect = new ReflectionClass($this->BO->getPropObject($propName));
1413: $propClass = $propReflect->getShortName();
1414:
1415: switch (mb_strtoupper($propClass)) {
1416: case 'INTEGER':
1417:
1418: if ($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID')) {
1419: $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize().') NOT NULL,';
1420: } else {
1421: $sqlQuery .= "$propName INT(".$this->BO->getPropObject($propName)->getSize().'),';
1422: }
1423: break;
1424: case 'DOUBLE':
1425: $sqlQuery .= "$propName REAL(".$this->BO->getPropObject($propName)->getSize(true).'),';
1426: break;
1427: case 'STRING':
1428: $sqlQuery .= "$propName TEXT(".$this->BO->getPropObject($propName)->getSize().'),';
1429: break;
1430: case 'TEXT':
1431: $sqlQuery .= "$propName TEXT,";
1432: break;
1433: case 'BOOLEAN':
1434: $sqlQuery .= "$propName INTEGER(1) DEFAULT '0',";
1435: break;
1436: case 'DATE':
1437: $sqlQuery .= "$propName TEXT,";
1438: break;
1439: case 'TIMESTAMP':
1440: $sqlQuery .= "$propName TEXT,";
1441: break;
1442: case 'ENUM':
1443: $sqlQuery .= "$propName TEXT,";
1444: break;
1445: case 'DENUM':
1446: $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1447: $sqlQuery .= "$propName INTEGER(11),";
1448: break;
1449: case 'RELATION':
1450: $sqlQuery .= "$propName INTEGER(11),";
1451: break;
1452: default:
1453: $sqlQuery .= '';
1454: break;
1455: }
1456: }
1457: }
1458:
1459: if ($this->BO->isTableOverloaded()) {
1460: $sqlQuery .= 'classname TEXT(100),';
1461: }
1462:
1463: $sqlQuery .= 'PRIMARY KEY (OID, version_num));';
1464:
1465: $this->BO->setLastQuery($sqlQuery);
1466:
1467: if (!$result = self::getConnection()->query($sqlQuery)) {
1468: throw new AlphaException('Failed to create the table ['.$this->BO->getTableName().'_history] for the class ['.get_class($this->BO).'], database error is ['.self::getLastDatabaseError().']');
1469: self::$logger->debug('<<makeHistoryTable');
1470: }
1471:
1472: self::$logger->debug('<<makeHistoryTable');
1473: }
1474:
1475: 1476: 1477: 1478: 1479:
1480: public function rebuildTable()
1481: {
1482: self::$logger->debug('>>rebuildTable()');
1483:
1484:
1485: $sqlQuery = 'DROP TABLE IF EXISTS '.$this->BO->getTableName().';';
1486:
1487: $this->BO->setLastQuery($sqlQuery);
1488:
1489: if (!$result = self::getConnection()->query($sqlQuery)) {
1490: throw new AlphaException('Failed to drop the table ['.$this->BO->getTableName().'] for the class ['.get_class($this->BO).'], database error is ['.self::getLastDatabaseError().']');
1491: self::$logger->debug('<<rebuildTable');
1492: }
1493:
1494: $this->BO->makeTable();
1495:
1496: self::$logger->debug('<<rebuildTable');
1497: }
1498:
1499: 1500: 1501: 1502: 1503:
1504: public function dropTable($tableName = null)
1505: {
1506: self::$logger->debug('>>dropTable()');
1507:
1508: if ($tableName == null) {
1509: $tableName = $this->BO->getTableName();
1510: }
1511:
1512:
1513: $sqlQuery = 'DROP TABLE IF EXISTS '.$tableName.';';
1514:
1515: $this->BO->setLastQuery($sqlQuery);
1516:
1517: if (!$result = self::getConnection()->query($sqlQuery)) {
1518: throw new AlphaException('Failed to drop the table ['.$tableName.'] for the class ['.get_class($this->BO).'], query is ['.$this->BO->getLastQuery().']');
1519: self::$logger->debug('<<dropTable');
1520: }
1521:
1522: if ($this->BO->getMaintainHistory()) {
1523: $sqlQuery = 'DROP TABLE IF EXISTS '.$tableName.'_history;';
1524:
1525: $this->BO->setLastQuery($sqlQuery);
1526:
1527: if (!$result = self::getConnection()->query($sqlQuery)) {
1528: throw new AlphaException('Failed to drop the table ['.$tableName.'_history] for the class ['.get_class($this->BO).'], query is ['.$this->BO->getLastQuery().']');
1529: self::$logger->debug('<<dropTable');
1530: }
1531: }
1532:
1533: self::$logger->debug('<<dropTable');
1534: }
1535:
1536: 1537: 1538: 1539: 1540:
1541: public function addProperty($propName)
1542: {
1543: self::$logger->debug('>>addProperty(propName=['.$propName.'])');
1544:
1545: $sqlQuery = 'ALTER TABLE '.$this->BO->getTableName().' ADD ';
1546:
1547: if ($this->isTableOverloaded() && $propName == 'classname') {
1548: $sqlQuery .= 'classname TEXT(100)';
1549: } else {
1550: if (!in_array($propName, $this->BO->getDefaultAttributes()) && !in_array($propName, $this->BO->getTransientAttributes())) {
1551: $reflection = new ReflectionClass($this->BO->getPropObject($propName));
1552: $propClass = $reflection->getShortName();
1553:
1554: switch (mb_strtoupper($propClass)) {
1555: case 'INTEGER':
1556:
1557: if ($this->BO instanceof RelationLookup && ($propName == 'leftID' || $propName == 'rightID')) {
1558: $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize().') NOT NULL,';
1559: } else {
1560: $sqlQuery .= "$propName INTEGER(".$this->BO->getPropObject($propName)->getSize().'),';
1561: }
1562: break;
1563: case 'DOUBLE':
1564: $sqlQuery .= "$propName REAL(".$this->BO->getPropObject($propName)->getSize(true).'),';
1565: break;
1566: case 'STRING':
1567: $sqlQuery .= "$propName TEXT(".$this->BO->getPropObject($propName)->getSize().'),';
1568: break;
1569: case 'TEXT':
1570: $sqlQuery .= "$propName TEXT,";
1571: break;
1572: case 'BOOLEAN':
1573: $sqlQuery .= "$propName INTEGER(1) DEFAULT '0',";
1574: break;
1575: case 'DATE':
1576: $sqlQuery .= "$propName TEXT,";
1577: break;
1578: case 'TIMESTAMP':
1579: $sqlQuery .= "$propName TEXT,";
1580: break;
1581: case 'ENUM':
1582: $sqlQuery .= "$propName TEXT,";
1583: break;
1584: case 'DENUM':
1585: $tmp = new DEnum(get_class($this->BO).'::'.$propName);
1586: $sqlQuery .= "$propName INTEGER(11),";
1587: break;
1588: case 'RELATION':
1589: $sqlQuery .= "$propName INTEGER(11),";
1590: break;
1591: default:
1592: $sqlQuery .= '';
1593: break;
1594: }
1595: }
1596: }
1597:
1598: $this->BO->setLastQuery($sqlQuery);
1599:
1600: if (!$result = self::getConnection()->query($sqlQuery)) {
1601: throw new AlphaException('Failed to add the new attribute ['.$propName.'] to the table ['.$this->BO->getTableName().'], query is ['.$this->BO->getLastQuery().']');
1602: self::$logger->debug('<<addProperty');
1603: } else {
1604: self::$logger->info('Successfully added the ['.$propName.'] column onto the ['.$this->BO->getTableName().'] table for the class ['.get_class($this->BO).']');
1605: }
1606:
1607: if ($this->BO->getMaintainHistory()) {
1608: $sqlQuery = str_replace($this->BO->getTableName(), $this->BO->getTableName().'_history', $sqlQuery);
1609:
1610: if (!$result = self::getConnection()->query($sqlQuery)) {
1611: throw new AlphaException('Failed to add the new attribute ['.$propName.'] to the table ['.$this->BO->getTableName().'_history], query is ['.$this->BO->getLastQuery().']');
1612: self::$logger->debug('<<addProperty');
1613: } else {
1614: self::$logger->info('Successfully added the ['.$propName.'] column onto the ['.$this->BO->getTableName().'_history] table for the class ['.get_class($this->BO).']');
1615: }
1616: }
1617:
1618: self::$logger->debug('<<addProperty');
1619: }
1620:
1621: 1622: 1623: 1624: 1625:
1626: public function getMAX()
1627: {
1628: self::$logger->debug('>>getMAX()');
1629:
1630: $sqlQuery = 'SELECT MAX(OID) AS max_OID FROM '.$this->BO->getTableName();
1631:
1632: $this->BO->setLastQuery($sqlQuery);
1633:
1634: try {
1635: $result = $this->BO->query($sqlQuery);
1636:
1637: $row = $result[0];
1638:
1639: if (isset($row['max_OID'])) {
1640: self::$logger->debug('<<getMAX ['.$row['max_OID'].']');
1641:
1642: return $row['max_OID'];
1643: } else {
1644: 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().']');
1645: }
1646: } catch (Exception $e) {
1647: throw new AlphaException($e->getMessage());
1648: self::$logger->debug('<<getMAX [0]');
1649:
1650: return 0;
1651: }
1652: }
1653:
1654: 1655: 1656: 1657: 1658:
1659: public function getCount($attributes = array(), $values = array())
1660: {
1661: self::$logger->debug('>>getCount(attributes=['.var_export($attributes, true).'], values=['.var_export($values, true).'])');
1662:
1663: if ($this->BO->isTableOverloaded()) {
1664: $whereClause = ' WHERE classname = \''.addslashes(get_class($this->BO)).'\' AND';
1665: } else {
1666: $whereClause = ' WHERE';
1667: }
1668:
1669: $count = count($attributes);
1670:
1671: for ($i = 0; $i < $count; ++$i) {
1672: $whereClause .= ' '.$attributes[$i].' = \''.$values[$i].'\' AND';
1673: self::$logger->debug($whereClause);
1674: }
1675:
1676: $whereClause = mb_substr($whereClause, 0, -4);
1677:
1678: if ($whereClause != ' WHERE') {
1679: $sqlQuery = 'SELECT COUNT(OID) AS class_count FROM '.$this->BO->getTableName().$whereClause;
1680: } else {
1681: $sqlQuery = 'SELECT COUNT(OID) AS class_count FROM '.$this->BO->getTableName();
1682: }
1683:
1684: $this->BO->setLastQuery($sqlQuery);
1685:
1686: if (!$result = self::getConnection()->query($sqlQuery)) {
1687: 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().']');
1688:
1689: self::$logger->debug('<<getCount [0]');
1690:
1691: return 0;
1692: } else {
1693: $row = $result->fetchArray(SQLITE3_ASSOC);
1694:
1695: self::$logger->debug('<<getCount ['.$row['class_count'].']');
1696:
1697: return $row['class_count'];
1698: }
1699: }
1700:
1701: 1702: 1703: 1704: 1705:
1706: public function getHistoryCount()
1707: {
1708: self::$logger->debug('>>getHistoryCount()');
1709:
1710: if (!$this->BO->getMaintainHistory()) {
1711: throw new AlphaException('getHistoryCount method called on a DAO where no history is maintained!');
1712: }
1713:
1714: $sqlQuery = 'SELECT COUNT(OID) AS object_count FROM '.$this->BO->getTableName().'_history WHERE OID='.$this->BO->getOID();
1715:
1716: $this->BO->setLastQuery($sqlQuery);
1717: self::$logger->debug('query ['.$sqlQuery.']');
1718:
1719: if (!$result = self::getConnection()->query($sqlQuery)) {
1720: 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().']');
1721:
1722: self::$logger->debug('<<getHistoryCount [0]');
1723:
1724: return 0;
1725: } else {
1726: $row = $result->fetchArray(SQLITE3_ASSOC);
1727:
1728: self::$logger->debug('<<getHistoryCount ['.$row['object_count'].']');
1729:
1730: return $row['object_count'];
1731: }
1732: }
1733:
1734: 1735: 1736: 1737: 1738: 1739: 1740: 1741: 1742:
1743: public function setEnumOptions()
1744: {
1745: throw new NotImplementedException('ActiveRecordProviderInterface::setEnumOptions() not implemented by the SQLite3 provider');
1746: }
1747:
1748: 1749: 1750: 1751: 1752:
1753: public function checkTableExists($checkHistoryTable = false)
1754: {
1755: self::$logger->debug('>>checkTableExists(checkHistoryTable=['.$checkHistoryTable.'])');
1756:
1757: $config = ConfigProvider::getInstance();
1758:
1759: $tableExists = false;
1760:
1761: $sqlQuery = 'SELECT name FROM sqlite_master WHERE type = "table";';
1762: $this->BO->setLastQuery($sqlQuery);
1763:
1764: $result = self::getConnection()->query($sqlQuery);
1765:
1766: $tableName = ($checkHistoryTable ? $this->BO->getTableName().'_history' : $this->BO->getTableName());
1767:
1768: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1769: if (strtolower($row['name']) == mb_strtolower($tableName)) {
1770: $tableExists = true;
1771: }
1772: }
1773:
1774: if ($result) {
1775: self::$logger->debug('<<checkTableExists ['.$tableExists.']');
1776:
1777: return $tableExists;
1778: } else {
1779: throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1780: self::$logger->debug('<<checkTableExists [false]');
1781:
1782: return false;
1783: }
1784: }
1785:
1786: 1787: 1788: 1789: 1790:
1791: public static function checkBOTableExists($BOClassName, $checkHistoryTable = false)
1792: {
1793: if (self::$logger == null) {
1794: self::$logger = new Logger('ActiveRecordProviderSQLite');
1795: }
1796: self::$logger->debug('>>checkBOTableExists(BOClassName=['.$BOClassName.'], checkHistoryTable=['.$checkHistoryTable.'])');
1797:
1798: if (!class_exists($BOClassName)) {
1799: throw new IllegalArguementException('The classname provided ['.$checkHistoryTable.'] is not defined!');
1800: }
1801:
1802: $tableName = $BOClassName::TABLE_NAME;
1803:
1804: if (empty($tableName)) {
1805: $tableName = mb_substr($BOClassName, 0, mb_strpos($BOClassName, '_'));
1806: }
1807:
1808: if ($checkHistoryTable) {
1809: $tableName .= '_history';
1810: }
1811:
1812: $tableExists = false;
1813:
1814: $sqlQuery = 'SELECT name FROM sqlite_master WHERE type = "table";';
1815:
1816: $result = self::getConnection()->query($sqlQuery);
1817:
1818: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1819: if ($row['name'] == $tableName) {
1820: $tableExists = true;
1821: }
1822: }
1823:
1824: if ($result) {
1825: self::$logger->debug('<<checkBOTableExists ['.($tableExists ? 'true' : 'false').']');
1826:
1827: return $tableExists;
1828: } else {
1829: throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1830: self::$logger->debug('<<checkBOTableExists [false]');
1831:
1832: return false;
1833: }
1834: }
1835:
1836: 1837: 1838: 1839: 1840:
1841: public function checkTableNeedsUpdate()
1842: {
1843: self::$logger->debug('>>checkTableNeedsUpdate()');
1844:
1845: if (!$this->BO->checkTableExists()) {
1846: return false;
1847: }
1848:
1849: $updateRequired = false;
1850:
1851: $matchCount = 0;
1852:
1853: $query = 'PRAGMA table_info('.$this->BO->getTableName().')';
1854: $result = self::getConnection()->query($query);
1855: $this->BO->setLastQuery($query);
1856:
1857:
1858: $reflection = new ReflectionClass(get_class($this->BO));
1859: $properties = $reflection->getProperties();
1860:
1861: foreach ($properties as $propObj) {
1862: $propName = $propObj->name;
1863: if (!in_array($propName, $this->BO->getTransientAttributes())) {
1864: $foundMatch = false;
1865:
1866: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1867: if ($propName == $row['name']) {
1868: $foundMatch = true;
1869: break;
1870: }
1871: }
1872:
1873: if (!$foundMatch) {
1874: --$matchCount;
1875: }
1876:
1877: $result->reset();
1878: }
1879: }
1880:
1881:
1882: if ($this->BO->isTableOverloaded()) {
1883: $foundMatch = false;
1884:
1885: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1886: if ('classname' == $row['name']) {
1887: $foundMatch = true;
1888: break;
1889: }
1890: }
1891: if (!$foundMatch) {
1892: --$matchCount;
1893: }
1894: }
1895:
1896: if ($matchCount != 0) {
1897: $updateRequired = true;
1898: }
1899:
1900: if (!$result) {
1901: throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1902: self::$logger->debug('<<checkTableNeedsUpdate [false]');
1903:
1904: return false;
1905: } else {
1906:
1907: try {
1908: $this->checkIndexes();
1909: } catch (AlphaException $ae) {
1910: self::$logger->warn("Error while checking database indexes:\n\n".$ae->getMessage());
1911: }
1912:
1913: self::$logger->debug('<<checkTableNeedsUpdate ['.$updateRequired.']');
1914:
1915: return $updateRequired;
1916: }
1917: }
1918:
1919: 1920: 1921: 1922: 1923:
1924: public function findMissingFields()
1925: {
1926: self::$logger->debug('>>findMissingFields()');
1927:
1928: $missingFields = array();
1929: $matchCount = 0;
1930:
1931: $sqlQuery = 'PRAGMA table_info('.$this->BO->getTableName().')';
1932: $result = self::getConnection()->query($sqlQuery);
1933: $this->BO->setLastQuery($sqlQuery);
1934:
1935:
1936: $reflection = new ReflectionClass(get_class($this->BO));
1937: $properties = $reflection->getProperties();
1938:
1939: foreach ($properties as $propObj) {
1940: $propName = $propObj->name;
1941: if (!in_array($propName, $this->BO->getTransientAttributes())) {
1942: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1943: if ($propName == $row['name']) {
1944: ++$matchCount;
1945: break;
1946: }
1947: }
1948: $result->reset();
1949: } else {
1950: ++$matchCount;
1951: }
1952:
1953: if ($matchCount == 0) {
1954: array_push($missingFields, $propName);
1955: } else {
1956: $matchCount = 0;
1957: }
1958: }
1959:
1960:
1961: if ($this->BO->isTableOverloaded()) {
1962: $foundMatch = false;
1963:
1964: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
1965: if ('classname' == $row['name']) {
1966: $foundMatch = true;
1967: break;
1968: }
1969: }
1970: if (!$foundMatch) {
1971: array_push($missingFields, 'classname');
1972: }
1973: }
1974:
1975: if (!$result) {
1976: throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
1977: }
1978:
1979: self::$logger->debug('<<findMissingFields ['.var_export($missingFields, true).']');
1980:
1981: return $missingFields;
1982: }
1983:
1984: 1985: 1986: 1987: 1988:
1989: public function getIndexes()
1990: {
1991: self::$logger->debug('>>getIndexes()');
1992:
1993: $sqlQuery = "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='".$this->BO->getTableName()."'";
1994:
1995: $this->BO->setLastQuery($sqlQuery);
1996:
1997: $indexNames = array();
1998:
1999: if (!$result = self::getConnection()->query($sqlQuery)) {
2000: throw new AlphaException('Failed to access the system database correctly, error is ['.self::getLastDatabaseError().']');
2001: } else {
2002: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
2003: array_push($indexNames, $row['name']);
2004: }
2005: }
2006:
2007:
2008: $sqlQuery = 'PRAGMA foreign_key_list('.$this->BO->getTableName().')';
2009:
2010: $this->BO->setLastQuery($sqlQuery);
2011:
2012: if (!$result = self::getConnection()->query($sqlQuery)) {
2013: self::$logger->warn('Error during pragma table foreign key lookup ['.self::getLastDatabaseError().']');
2014: } else {
2015: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
2016:
2017: $fakeIndexName = $this->BO->getTableName().'_'.$row['from'].'_fk_idx';
2018: array_push($indexNames, $fakeIndexName);
2019: }
2020: }
2021:
2022: self::$logger->debug('<<getIndexes');
2023:
2024: return $indexNames;
2025: }
2026:
2027: 2028: 2029: 2030: 2031:
2032: private function checkIndexes()
2033: {
2034: self::$logger->debug('>>checkIndexes()');
2035:
2036: $indexNames = $this->BO->getIndexes();
2037:
2038:
2039: foreach ($this->BO->getUniqueAttributes() as $prop) {
2040:
2041: if (mb_strpos($prop, '+')) {
2042: $attributes = explode('+', $prop);
2043:
2044: $index_exists = false;
2045: foreach ($indexNames as $index) {
2046: if ($attributes[0].'_'.$attributes[1].'_unq_idx' == $index) {
2047: $index_exists = true;
2048: }
2049: if (count($attributes) == 3) {
2050: if ($attributes[0].'_'.$attributes[1].'_'.$attributes[2].'_unq_idx' == $index) {
2051: $index_exists = true;
2052: }
2053: }
2054: }
2055:
2056: if (!$index_exists) {
2057: if (count($attributes) == 3) {
2058: $this->BO->createUniqueIndex($attributes[0], $attributes[1], $attributes[2]);
2059: } else {
2060: $this->BO->createUniqueIndex($attributes[0], $attributes[1]);
2061: }
2062: }
2063: } else {
2064: $index_exists = false;
2065: foreach ($indexNames as $index) {
2066: if ($prop.'_unq_idx' == $index) {
2067: $index_exists = true;
2068: }
2069: }
2070:
2071: if (!$index_exists) {
2072: $this->createUniqueIndex($prop);
2073: }
2074: }
2075: }
2076:
2077: self::$logger->debug('<<checkIndexes');
2078: }
2079:
2080: 2081: 2082: 2083: 2084: 2085: 2086:
2087: public function createForeignIndex($attributeName, $relatedClass, $relatedClassAttribute, $indexName = null)
2088: {
2089: self::$logger->info('>>createForeignIndex(attributeName=['.$attributeName.'], relatedClass=['.$relatedClass.'], relatedClassAttribute=['.$relatedClassAttribute.'], indexName=['.$indexName.']');
2090:
2091: 2092: 2093: 2094: 2095: 2096: 2097: 2098:
2099: try {
2100: ActiveRecord::begin($this->BO);
2101:
2102:
2103: $query = 'ALTER TABLE '.$this->BO->getTableName().' RENAME TO '.$this->BO->getTableName().'_temp;';
2104: $this->BO->setLastQuery($query);
2105: self::getConnection()->query($query);
2106:
2107: self::$logger->info('Renamed the table ['.$this->BO->getTableName().'] to ['.$this->BO->getTableName().'_temp]');
2108:
2109:
2110: $record = new $relatedClass();
2111: $tableName = $record->getTableName();
2112: $this->foreignKeys[$attributeName] = array($tableName, $relatedClassAttribute);
2113:
2114: $this->makeTable();
2115:
2116: self::$logger->info('Made a new copy of the table ['.$this->BO->getTableName().']');
2117:
2118:
2119: $query = 'INSERT INTO '.$this->BO->getTableName().' SELECT * FROM '.$this->BO->getTableName().'_temp;';
2120: $this->BO->setLastQuery($query);
2121: self::getConnection()->query($query);
2122:
2123: self::$logger->info('Copied all of the data from ['.$this->BO->getTableName().'] to ['.$this->BO->getTableName().'_temp]');
2124:
2125:
2126: $this->BO->dropTable($this->BO->getTableName().'_temp');
2127:
2128: self::$logger->info('Dropped the table ['.$this->BO->getTableName().'_temp]');
2129:
2130: ActiveRecord::commit($this->BO);
2131: } catch (Exception $e) {
2132: ActiveRecord::rollback($this->BO);
2133:
2134: throw new FailedIndexCreateException('Failed to create the index ['.$attributeName.'] on ['.$this->BO->getTableName().'], error is ['.$e->getMessage().'], query ['.$this->BO->getLastQuery().']');
2135: }
2136:
2137: self::$logger->info('<<createForeignIndex');
2138: }
2139:
2140: 2141: 2142: 2143: 2144:
2145: public function createUniqueIndex($attribute1Name, $attribute2Name = '', $attribute3Name = '')
2146: {
2147: self::$logger->debug('>>createUniqueIndex(attribute1Name=['.$attribute1Name.'], attribute2Name=['.$attribute2Name.'], attribute3Name=['.$attribute3Name.'])');
2148:
2149: if ($attribute2Name != '' && $attribute3Name != '') {
2150: $sqlQuery = 'CREATE UNIQUE INDEX IF NOT EXISTS '.$attribute1Name.'_'.$attribute2Name.'_'.$attribute3Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.','.$attribute2Name.','.$attribute3Name.');';
2151: }
2152:
2153: if ($attribute2Name != '' && $attribute3Name == '') {
2154: $sqlQuery = 'CREATE UNIQUE INDEX IF NOT EXISTS '.$attribute1Name.'_'.$attribute2Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.','.$attribute2Name.');';
2155: }
2156:
2157: if ($attribute2Name == '' && $attribute3Name == '') {
2158: $sqlQuery = 'CREATE UNIQUE INDEX IF NOT EXISTS '.$attribute1Name.'_unq_idx ON '.$this->BO->getTableName().' ('.$attribute1Name.');';
2159: }
2160:
2161: $this->BO->setLastQuery($sqlQuery);
2162:
2163: $result = self::getConnection()->query($sqlQuery);
2164:
2165: if ($result) {
2166: self::$logger->debug('Successfully created the unique index on ['.$this->BO->getTableName().']');
2167: } else {
2168: throw new FailedIndexCreateException('Failed to create the unique index on ['.$this->BO->getTableName().'], error is ['.self::getConnection()->error.']');
2169: }
2170:
2171: self::$logger->debug('<<createUniqueIndex');
2172: }
2173:
2174: 2175: 2176: 2177: 2178:
2179: public function reload()
2180: {
2181: self::$logger->debug('>>reload()');
2182:
2183: if (!$this->BO->isTransient()) {
2184: $this->BO->load($this->BO->getOID());
2185: } else {
2186: throw new AlphaException('Cannot reload transient object from database!');
2187: }
2188:
2189: self::$logger->debug('<<reload');
2190: }
2191:
2192: 2193: 2194: 2195: 2196:
2197: public function checkRecordExists($OID)
2198: {
2199: self::$logger->debug('>>checkRecordExists(OID=['.$OID.'])');
2200:
2201: $sqlQuery = 'SELECT OID FROM '.$this->BO->getTableName().' WHERE OID = :OID;';
2202: $this->BO->setLastQuery($sqlQuery);
2203: $stmt = self::getConnection()->prepare($sqlQuery);
2204:
2205: $row = array();
2206:
2207: if ($stmt instanceof SQLite3Stmt) {
2208: $stmt->bindValue(':OID', $OID, SQLITE3_INTEGER);
2209:
2210: $result = $stmt->execute();
2211:
2212:
2213: $row = $result->fetchArray(SQLITE3_ASSOC);
2214:
2215: $stmt->close();
2216: } else {
2217: 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().']');
2218: self::$logger->debug('<<checkRecordExists [false]');
2219:
2220: return false;
2221: }
2222:
2223: if (!isset($row['OID'])) {
2224: self::$logger->debug('<<checkRecordExists [false]');
2225:
2226: return false;
2227: } else {
2228: self::$logger->debug('<<checkRecordExists [true]');
2229:
2230: return true;
2231: }
2232: }
2233:
2234: 2235: 2236: 2237: 2238:
2239: public function isTableOverloaded()
2240: {
2241: self::$logger->debug('>>isTableOverloaded()');
2242:
2243: $reflection = new ReflectionClass($this->BO);
2244: $classname = $reflection->getShortName();
2245: $tablename = ucfirst($this->BO->getTableName());
2246:
2247:
2248: $implementedInterfaces = $reflection->getInterfaces();
2249:
2250: foreach ($implementedInterfaces as $interface) {
2251: if ($interface->name == 'Alpha\Model\Type\TypeInterface') {
2252: self::$logger->debug('<<isTableOverloaded [false]');
2253:
2254: return false;
2255: }
2256: }
2257:
2258: if ($classname != $tablename) {
2259:
2260:
2261: $BOclasses = ActiveRecord::getBOClassNames();
2262:
2263: foreach ($BOclasses as $BOclassName) {
2264: $reflection = new ReflectionClass($BOclassName);
2265: $classname = $reflection->getShortName();
2266: if ($tablename == $classname) {
2267: self::$logger->debug('<<isTableOverloaded [true]');
2268:
2269: return true;
2270: }
2271: }
2272: throw new BadTableNameException('The table name ['.$tablename.'] for the class ['.$classname.'] is invalid as it does not match a BO definition in the system!');
2273: self::$logger->debug('<<isTableOverloaded [false]');
2274:
2275: return false;
2276: } else {
2277:
2278: $sqlQuery = 'PRAGMA table_info('.$this->BO->getTableName().')';
2279: $result = self::getConnection()->query($sqlQuery);
2280: $this->BO->setLastQuery($sqlQuery);
2281:
2282: if (!$result) {
2283: self::$logger->warn('Error during pragma table info lookup ['.self::getLastDatabaseError().']');
2284: } else {
2285: while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
2286: if ('classname' == $row['name']) {
2287: self::$logger->debug('<<isTableOverloaded [true]');
2288:
2289: return true;
2290: }
2291: }
2292: }
2293:
2294: self::$logger->debug('<<isTableOverloaded [false]');
2295:
2296: return false;
2297: }
2298: }
2299:
2300: 2301: 2302: 2303: 2304:
2305: public static function begin()
2306: {
2307: if (self::$logger == null) {
2308: self::$logger = new Logger('ActiveRecordProviderSQLite');
2309: }
2310: self::$logger->debug('>>begin()');
2311:
2312: if (!self::getConnection()->exec('BEGIN')) {
2313: throw new AlphaException('Error beginning a new transaction, error is ['.self::getLastDatabaseError().']');
2314: }
2315:
2316: self::$logger->debug('<<begin');
2317: }
2318:
2319: 2320: 2321: 2322: 2323:
2324: public static function commit()
2325: {
2326: if (self::$logger == null) {
2327: self::$logger = new Logger('ActiveRecordProviderSQLite');
2328: }
2329: self::$logger->debug('>>commit()');
2330:
2331: if (!self::getConnection()->exec('COMMIT')) {
2332: throw new AlphaException('Error commiting a transaction, error is ['.self::getLastDatabaseError().']');
2333: }
2334:
2335: self::$logger->debug('<<commit');
2336: }
2337:
2338: 2339: 2340: 2341: 2342:
2343: public static function rollback()
2344: {
2345: if (self::$logger == null) {
2346: self::$logger = new Logger('ActiveRecordProviderSQLite');
2347: }
2348:
2349: self::$logger->debug('>>rollback()');
2350:
2351: try {
2352: self::getConnection()->exec('ROLLBACK');
2353: self::disconnect();
2354: } catch (Exception $e) {
2355: if (mb_strpos($e->getMessage(), 'cannot rollback - no transaction is active') === false) {
2356: throw new AlphaException('Error rolling back a transaction, error is ['.self::getLastDatabaseError().']');
2357: }
2358: }
2359:
2360: self::$logger->debug('<<rollback');
2361: }
2362:
2363: 2364: 2365: 2366: 2367:
2368: public function setBO($BO)
2369: {
2370: $this->BO = $BO;
2371: }
2372:
2373: 2374: 2375: 2376: 2377:
2378: public static function checkDatabaseExists()
2379: {
2380: $config = ConfigProvider::getInstance();
2381:
2382: return file_exists($config->get('db.file.path'));
2383: }
2384:
2385: 2386: 2387: 2388: 2389:
2390: public static function createDatabase()
2391: {
2392: $config = ConfigProvider::getInstance();
2393:
2394: if (!self::checkDatabaseExists()) {
2395: fopen($config->get('db.file.path'), 'x+');
2396: }
2397: }
2398:
2399: 2400: 2401: 2402: 2403:
2404: public static function dropDatabase()
2405: {
2406: $config = ConfigProvider::getInstance();
2407:
2408: if (self::checkDatabaseExists()) {
2409: unlink($config->get('db.file.path'));
2410: }
2411: }
2412: }
2413: