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