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