· 8 years ago · Jul 22, 2018, 04:30 PM
1<?php
2
3/**
4* System Abstract DB Class that is extended by each object's DB class
5*/
6abstract class System_Database_Base implements Iterator{
7 /**
8 * holds an the unique registration id of the class. This is used when the object is registered with the unit of work class
9 *
10 * @var string
11 * @access private
12 */
13 private $_registration_id;
14
15 /**
16 * holds an instance of the validator class
17 *
18 * @var object
19 * @access private
20 */
21 private $_validator;
22
23 /**
24 * tells the class to validate the input or not during setvalue() method call
25 *
26 * @var object
27 * @access private
28 */
29 private $_validate_input = true;
30
31 /**
32 * holds an instance of the system_log_controller class
33 *
34 * @var object
35 * @access private
36 */
37 private $_system_log;
38
39 /**
40 * holds an instance of the System_Database_Word class
41 *
42 * @var object
43 * @access private
44 */
45 private $_unit_of_work;
46
47 /**
48 * an array that holds the CRUD settings
49 *
50 * @var array
51 * @access protected
52 */
53 protected $_crud = array(
54 'create' => '*',
55 'read' => '*',
56 'update' => '*',
57 'delete' => '*'
58 );
59
60 /**
61 * variable to hold the database outline of all fields in that table
62 *
63 * @var array
64 * @access protected
65 */
66 protected $_data_frame = array();
67
68 /**
69 * holds the virtual frame created during joins
70 *
71 * @var array|boolean
72 * @access private
73 */
74 private $_v_data_frame = false;
75
76 /**
77 * variable to hold the database value fields for actions in the sql statement
78 *
79 * @var array
80 * @access private
81 */
82 private $_fields = array();
83
84 /**
85 * the fields that will go in the select statement
86 *
87 * @var array
88 * @access private
89 */
90 private $_select_fields = array();
91
92 /**
93 * holds an assocative array of fields that are required for a type of query action
94 * select, update, delete, insert
95 *
96 * @var array
97 * @access private
98 */
99 private $_required_fields = array(
100 'select' => array(),
101 'delete' => array(),
102 'insert' => array(),
103 'update' => array()
104 );
105
106 /**
107 * holds an array of fields that are marked as index
108 *
109 * @var array
110 * @access private
111 */
112 private $_index_fields = array();
113
114 /**
115 * An instance of the Database from the System_Database_Root Class
116 *
117 * @var object|boolean
118 * @access private
119 */
120 private $_db = false;
121
122 /**
123 * Number of rows affected by the running of a query
124 *
125 * @var integer
126 * @access private
127 */
128 private $_affected_rows = 0;
129
130 /**
131 * Any errors that happen when a query is run
132 *
133 * @var ?i'm not sure Pete can you define this
134 * @access private
135 */
136 private $_errors = false;
137
138 /**
139 * The status returned by the system_database_root
140 *
141 * @var boolean
142 * @access private
143 */
144 private $_status = false;
145
146 /**
147 * the table name of the query being affected. Used in building the SQL statement
148 *
149 * @var string
150 * @access private
151 */
152 private $_table_name;
153
154 /**
155 * holds the dataset
156 *
157 * @var array
158 * @access private
159 */
160 public $_data_set = array();
161
162 /**
163 * sets the current index for the retrieved data array stored in the _data_set property
164 *
165 * @var integer
166 * @access private
167 */
168 private $_current = 0;
169
170 /**
171 * holds the first part of the query
172 *
173 * @var boolean|string
174 * @access private
175 */
176 private $_sql_main = false;
177
178 /**
179 * holds the join portion for an sql select statement
180 *
181 * @var boolean|string
182 * @access private
183 */
184 private $_sql_join = false;
185
186 /**
187 * holds the where portion for an sql statement
188 *
189 * @var boolean|string
190 * @access private
191 */
192 private $_sql_where = false;
193
194 /**
195 * holds the order portion for an sql statement
196 *
197 * @var boolean|string
198 * @access private
199 */
200 private $_sql_order = false;
201
202 /**
203 * Holds the group by portion for an sql statement
204 *
205 * @var boolean|string
206 * @access private
207 */
208 private $_sql_group = false;
209
210 /**
211 * holds the limit portion for an sql statement
212 *
213 * @var boolean|string
214 * @access private
215 */
216 private $_sql_limit = false;
217
218 /**
219 * holds the entire query after it is built
220 *
221 * @var string
222 * @access private
223 */
224 private $_sql_full = '';
225
226 /**
227 * Holds the field that is set as the primary key
228 *
229 * @var string
230 * @access protected
231 */
232 protected $_primary_key_field;
233
234 /**
235 * holds the last insert id
236 *
237 * @var string
238 * @access private
239 */
240 private $_insert_id = '';
241
242 /**
243 * tells the sql function whether to check for a primary key or not for it's method.
244 *
245 * @var boolean
246 * @access private
247 */
248 private $_check_pk = true;
249
250 /**
251 * Class Constructor that gets an instance of the System_Database_Root
252 *
253 * @access public
254 * @return Object instance of this class
255 */
256 public function __construct(){
257 $this->_validator = System_Registry_Storage::get('System_Utility_Validate');
258 $this->_system_log = System_Registry_Storage::get('System_Log_Controller');
259 $this->_unit_of_work = System_Database_Work::getInstance();
260 $this->_registration_id = $this->defineRegistrationId();
261
262 $this->setIndexes();
263
264 return $this;
265 }
266
267 /**
268 * when the object is cloned request a new registration id
269 *
270 * @access public
271 * @return object, instance of this class
272 */
273 public function __clone(){
274 $this->_registration_id = $this->defineRegistrationId();
275 }
276
277 /**
278 * gets a database connection if one doesnt exist
279 *
280 * @access private
281 * @return object, instance of this class
282 */
283 private function connectDB(){
284 if(!$this->_db){
285 $this->_db = System_Database_Connection::getInstance()->getConnection('default');
286 }
287
288 return $this;
289 }
290
291 /**
292 * gets a registration_id
293 *
294 * @access private
295 * @return string
296 */
297 private function defineRegistrationId(){
298 $str_obj = new System_Utility_String();
299
300 return $str_obj->randomString(mt_rand(10, 40))->get();
301 }
302
303 /**
304 * abstract method to set the data_frame property
305 *
306 * @access protected
307 */
308 abstract protected function setDataFrame();
309
310 /**
311 * Sets the _primary_key_field to a string
312 *
313 * @access public
314 */
315 abstract public function setPrimaryKey($pk);
316
317 /**
318 * gets a connection to the database and sets it to the _db property
319 *
320 * @param string $connection_name, name of the connection to get
321 * @access public
322 * @return object, System_Database_Base
323 */
324 public function getConnection($connection_name){
325 $this->_db = System_Database_Connection::getInstance()->getConnection($connection_name);
326
327 return $this;
328 }
329
330 /**
331 * Set the Table Name of the Class
332 *
333 * @param $table String table name
334 * @access public
335 * @return Object Instance of this class
336 */
337 public function setTable($table){
338 $name = $this->quoteFields(array($table));
339 $this->_table_name = $name[0];
340
341 return $this;
342 }
343
344 /**
345 * Gets the last insert Id
346 * @access public
347 * @return mixed
348 */
349 public function getInsertId(){
350 return $this->_insert_id;
351 }
352
353 /**
354 * returns the full query (_sql_full property)
355 *
356 * @access public
357 * @return boolean|string
358 */
359 public function getQuery(){
360 return $this->_sql_full;
361 }
362
363 /**
364 * checks the _crud property against the current action to see if it is allowed
365 *
366 * @param sring $type, type of action to be checked
367 * @access private
368 * @return boolean
369 */
370 private function checkCrud($type){
371 $process = true;
372 $check = isset($this->_curd[$type]) ? $this->_curd[$type] : false;
373
374 /**
375 * TODO: Remove this block. it doesnt make sense and is never executed
376 * if the crud[type] is numeric, do an access level check
377 * if the user passes, logs the attemp and the error in the error class to be returned to the user
378 */
379 if(is_numeric($check)){
380 if(!System_Security_Controller::andThis($check, false)){
381
382 /**
383 * log error attempt and set process to false
384 */
385 $this->_system_log->setValue('database_access')->write('tried to run an '. $type .' on database '. $this->_table_name .'.');
386 $process = false;
387 }
388 }
389
390 return $process;
391 }
392
393 /**
394 * creates a string to insert a value into the database
395 *
396 * @access public
397 * @return Object Instance of this class
398 */
399 public function insert(){
400 /**
401 * if the user is allowed to create
402 */
403 if($this->checkCrud('create')){
404 $fields = array();
405 $values = array();
406
407 /**
408 * set the fields and values for the insert, also check to make sure the the fields being added to the insert statement
409 * are actually in the _data_frame property. We don't want to add fields to the insert for an object that was joined.
410 */
411 foreach ($this->_fields as $column => $value) {
412 if ($column != $this->_primary_key_field && isset($this->_data_frame[$column])) {
413 $fields[] = $column;
414 $values[] = $this->quoteValue($column, $this->scrub($value));
415 }
416 }
417
418 /**
419 * check to see if the required fields are met
420 * if they are, run the query
421 */
422 $required_fields = $this->requiredFieldCheck('insert', $fields);
423 $required_values = $this->requiredFieldValueCheck('insert', false);
424
425 if($required_fields && $required_values){
426 $field_string = '`'. join('`,`', $fields) .'`'; // TODO: replace with: $this->quoteFileds($fields);
427 $value_string = join(',', $values);
428 $this->_sql_main = 'INSERT INTO ' . $this->_table_name . ' (' . $field_string . ') VALUES (' . $value_string . ')';
429 }
430 }
431
432 return $this;
433 }
434
435 /**
436 * adds backticks to the elements in the array
437 *
438 * @param array $fields
439 * @access private
440 * @return array
441 */
442 private function quoteFields(array $fields = array()){
443 $ret = array();
444
445 foreach($fields as $field){
446 if(preg_match('/\./', $field)){
447 $parts = explode('.', $field);
448 $ret[] = '`'. implode('`.`', $parts) .'`';
449 }else{
450 $ret[] = '`'. $field .'`';
451 }
452 }
453
454 return $ret;
455 }
456
457 /**
458 * adds quotes around a vaule based on its datatype defined in the data_frame property
459 * this method should only be called from one of the existing CRUD methods as it does not throw an exception for invalid column entries
460 *
461 * @param String $column the name of the column to check against
462 * @param mixed $value the valued to be returned
463 * @access private
464 * @return mixed string|integer|boolean
465 */
466 private function quoteValue($column, $value){
467 $column_clean = $this->extractTableName($column);
468 $table = $column_clean['table'];
469 $column = $column_clean['column'];
470
471 $data_frame = pick($this->_v_data_frame, $this->_data_frame);
472 $type = strtolower($data_frame[$column]['type']);
473
474 if (preg_match('/^\(select/i', $value)) {
475 $type = 'sql';
476 }
477
478 switch($type){
479 case 'integer':
480 case 'boolean':
481 case 'float':
482 case 'sql':
483 break;
484
485 case 'text':
486 case 'string':
487 case 'email':
488 case 'date':
489 case 'datetime':
490 case 'password':
491 $value = "'". $value ."'";
492 }
493
494 return $value;
495 }
496
497 /**
498 * checks a val array for the required fields based on query type
499 *
500 * @param string $type, the type of query that will be checked
501 * @param array $fields,
502 * @access private
503 * @return boolean
504 */
505 private function requiredFieldCheck($type, $fields = false){
506 $fields = isset($fields) ? $fields : array_keys($this->_fields);
507 $required = $this->_required_fields[$type];
508 $difference = array_diff($required, $fields);
509 $error = System_Registry_Root::getInstance()->getSystemError();
510 $passed = true;
511
512 /**
513 * if there is no difference, return true
514 * else, loop through the difference and add those fields to the system_error object
515 */
516 if(count($difference)){
517 $passed = false;
518
519 foreach($difference as $field){
520 $proper = str_replace('_', ' ', $field);
521 $this->_errors = true;
522 $error->addEntry($field, $proper . ' is required for an ' . $type . ' statement when using the object associated with '. $this->_table_name .'.');
523 }
524 }
525
526 return $passed;
527 }
528
529 /**
530 * Checks the values of the required fields to make sure they are set and not empty
531 * @access private
532 * @return boolean
533 */
534 private function requiredFieldValueCheck($type, $fields = false){
535 $fields = ($fields !== false) ? $fields : $this->_fields;
536 $required = $this->_required_fields[$type];
537 $error = System_Registry_Root::getInstance()->getSystemError();
538 $passed = true;
539
540 /**
541 * Checks to see what fields have passed the required state, and then checks to see if their values are set
542 * If they are not set, then they will be added to the error stack.
543 */
544 if (count($required)) {
545 foreach ($required as $column) {
546 if (isset($fields[$column]) && trim($fields[$column]) === ''){
547 $proper = str_replace('_', ' ', $column);
548 $passed = false;
549 $this->_errors = true;
550 $error->addEntry($column, $proper . ' value is required.');
551 }
552 }
553 }
554
555 return $passed;
556 }
557
558 /**
559 * Get the primary key field
560 * @access public
561 * @return mixed
562 */
563 public function getPrimaryKey(){
564 return $this->_primary_key_field;
565 }
566
567 /**
568 * sets the _requried_fields property based on the _data_frame
569 *
570 * @access private
571 * @return object, instance of this class
572 */
573 public function setRequiredFields(){
574 $frame = $this->_data_frame;
575
576 foreach($frame as $column => $definition){
577 if(isset($definition['required'])){
578 $types = explode('|', $definition['required']);
579
580 foreach($types as $action){
581 $this->_required_fields[$action][] = $column;
582 }
583 }
584 }
585
586 return $this;
587 }
588
589 /**
590 * sets the index fields
591 *
592 * @access public
593 * @return Object System_Database_Base
594 */
595 public function setIndexes(){
596 $frame = pick($this->_v_data_frame, $this->_data_frame);
597
598 foreach($frame as $column => $definition){
599 if(isset($definition['index']) && $definition['index']){
600 $this->_index_fields[] = $column;
601 }
602 }
603
604 return $this;
605 }
606
607 /**
608 * returns the fields marked as index
609 *
610 * @access public
611 * @return array
612 */
613 public function getIndexes(){
614 return $this->_index_fields;
615 }
616
617 /**
618 * Updates a Record in a database table
619 *
620 * @access public
621 * @return object, instance of this class
622 */
623 public function update(){
624 /**
625 * if the user is allowed to update
626 */
627 if($this->checkCrud('update')){
628 //$this->getProcessValue();
629
630 $pairs = array();
631
632 foreach ($this->_fields as $column => $value) {
633 /**
634 * Omits the primary key from being set in the construction of the update clause
635 */
636 if ($column != $this->_primary_key_field) {
637 $pairs[] = "`" . $column . "` = " . $this->quoteValue($column, $this->scrub($value));
638 }
639 }
640
641 $pairs_string = join(', ', $pairs);
642 $this->_sql_main = "UPDATE " . $this->_table_name . " SET $pairs_string";
643
644 /**
645 * Automatically set the where clause for the update statement if the primary key field is set and the primary key check is set to true.
646 */
647 if (isset($this->_fields[$this->_primary_key_field]) && $this->_check_pk) {
648 $this->_sql_where = '';
649
650 $this->where(array($this->_primary_key_field => $this->_fields[$this->_primary_key_field]));
651 }
652 }
653
654 return $this;
655 }
656
657 /**
658 * Deletes a Record from a table in the database
659 *
660 * @access public
661 * @return object, instance of this class
662 */
663 public function delete(){
664 /**
665 * if the user is allowed to delete
666 */
667 if($this->checkCrud('delete')){
668
669 $this->_sql_main = "DELETE FROM " . $this->_table_name;
670
671 /**
672 * Automatically set the where clause for the delete statement if the primary key field is set.
673 */
674 if (isset($this->_fields[$this->_primary_key_field]) && $this->_check_pk) {
675 $this->where(array($this->_primary_key_field => $this->_fields[$this->_primary_key_field]));
676 }
677 }
678
679 return $this;
680 }
681
682 /**
683 * Selects a set of fields from the table
684 *
685 * @access public
686 * @return object, instance of this class
687 */
688 public function select(){
689 /**
690 * if the user is allowed to read
691 */
692 if($this->checkCrud('read')){
693 $select = array();
694
695 foreach ($this->_select_fields as $key => $val) {
696 $key = $key;
697 $val = $val;
698
699 if($val === '*'){
700 $select[] = $val;
701 }elseif(is_numeric($key)){
702 $select[] = $val;
703 }elseif(is_string($key)){
704 $select[] = $key ." AS ". $val;
705 }
706 }
707
708 if(is_array($select)){
709 $select = join(', ', $select);
710 }
711
712 $this->_sql_main = "SELECT $select FROM " . $this->_table_name;
713 }
714
715 return $this;
716 }
717
718 /**
719 * Creates the Order By clause
720 *
721 * @access public
722 * @return object, instance of this class
723 */
724 public function order(array $data = array()){
725 $pairs = array();
726
727 foreach ($data as $column => $direction) {
728 $pairs[] = $this->scrub($column) . " " . $this->scrub($direction);
729 }
730
731 $this->_sql_order = 'ORDER BY ' . join(', ', $pairs);
732
733 return $this;
734 }
735
736 /**
737 * creates the Limit for the sql_query
738 *
739 * @param array $data - an array where the key is the start of the limit and the val is the length
740 * @access public
741 * @return object, instance of this class
742 */
743 public function limit(array $data){
744 $paris = array();
745
746 foreach ($data as $key => $value) {
747 $pairs[] = "LIMIT " . $key . ", " . $value;
748 }
749
750 $this->_sql_limit = $pairs[0];
751
752 return $this;
753 }
754
755 /**
756 * Creates the Where statement
757 *
758 * @access public
759 * @return object, instance of this class
760 */
761 public function where(array $where, $condition_connector = 'AND'){
762 $where_array = array();
763
764 foreach ($where as $column => $value) {
765 if (!is_array($value)) {
766 $value = array($column => $value);
767 $column = "=";
768 }
769
770 /**
771 * added hack to allow for in array
772 */
773 switch(strtolower($column)){
774 case 'in':
775 foreach($value as $field => $in_array){
776 $where_part = $field .' '. $column .'(';
777 $val_array = array();
778
779 foreach($in_array as $in_val){
780 $val_array[] = $this->quoteValue($field, $this->scrub($in_val));
781 }
782
783 $where_array[] = $field . ' ' . $column . ' ('. implode(',', $val_array) .')';
784 }
785
786 break;
787
788 default:
789 foreach ($value as $field => $field_value) {
790 $where_array[] = $field . ' ' . $column . ' '. $this->quoteValue($field, $this->scrub($field_value));
791 }
792 }
793 }
794
795 $this->_sql_where .= 'WHERE ' . join(" $condition_connector ", $where_array);
796
797 return $this;
798 }
799
800 /**
801 * Manually sets the Where Clause
802 * @access public
803 * @return object this
804 */
805 public function setWhere($where){
806 if ($where != '') {
807 $this->_sql_where = 'WHERE ' . $where;
808 }
809
810 return $this;
811 }
812
813 /**
814 * This class will create a full-text searching match statement in the where clause.
815 * Passed a string of rows to search against and the search text.
816 * @access public
817 * @param array $rows An array of the columns that will be searched in the table.
818 * @param string $text_search The String that is being searched for.
819 * @return object System_Database_Base
820 */
821 public function whereMatch($rows, $text_search){
822 if (count($rows)) {
823 $row_string = implode(', ', $rows);
824
825 $sql = 'MATCH(' . $row_string . ')' . " AGAINST ('" . trim($text_search) . "')";
826 if (preg_match('/WHERE/' ,$this->_sql_where)) {
827 $this->_sql_where .= ' AND ' . $sql;
828 } else {
829 $this->_sql_where = 'WHERE ' . $sql;
830 }
831 }
832
833 return $this;
834 }
835
836
837 /**
838 * Allows joins
839 * **UNDER CONSTRUCTION**
840 *
841 * @access public
842 * @return Object, instance of this class
843 */
844 public function join(array $join = array(), $join_type = 'INNER JOIN'){
845 $join_array = array();
846
847 if(count($join)){
848 /**
849 * create the join clause and also append the _data_frame to this object's data frame
850 */
851 foreach($join as $table => $clause){
852 $join_array[] = '`'. $table .'` ON '. $clause;
853 $result = $this->getObjectByTableName($table);
854
855 $this->joinObject(new $result['class_name']);
856 }
857 }
858
859 $this->_sql_join .= ' '. $join_type .' '. implode(' '. $join_type .' ', $join_array);
860
861 return $this;
862 }
863
864 /**
865 * returns the object assoicated with a table name
866 * this will check for table objects with three naming conventions
867 * 1. Object_TableName(underscore to camel case)_Model.php. This assumes that your object lives in the object directory
868 * 2. Object_TableName(undescore to camel case).php. This assumes that your object lives in the object directory
869 * 3. Object_Table_Name(underscores defines the path to file).php. Your object can live in any path with this mehtod. However, YOU MUST DEFINE THE OBJECT PREFIX IN THE SYSTEM_REGISTRY_CONFIGURATION OBJECT **THIS IS THE PREFERRED METHOD TO OBJECT NAMING**
870 *
871 * @param string $table_name
872 * @access private
873 * @throws Exception -- if the object is not found
874 * @return array
875 */
876 private function getObjectByTableName($table_name = false){
877 $result = array('found' => false);
878 $string_obj = System_Registry_Storage::get('System_Utility_String');
879 $object = '';
880 $cc_name = $string_obj->setValue($table_name)->underscoreToCamelcase()->get();
881 $ucu_name = System_Registry_Configuration::get(System_Registry_Configuration::environment(), 'database', 'object_prefix') . $string_obj->reset()->setValue($table_name)->underscoreToCapitalUnderscore()->get();
882
883 /**
884 * type 1 check
885 */
886 $obj_path = 'Object_'. $cc_name .'_Model';
887 $result = classFileExists($obj_path);
888
889 /**
890 * type 2 check
891 */
892 if(!$result['found']){
893 $obj_path = 'Object_'. $cc_name;
894 $result = classFileExists($obj_path);
895 }
896
897 /**
898 * type 3 check
899 */
900 if(!$result['found']){
901 $result = classFileExists($ucu_name);
902 }
903
904 if(!$result['found']){
905 throw new Exception('An object representing the table ('. $table_name .') that you are trying to join does not exist.');
906 }
907
908 return $result;
909 }
910
911 /**
912 * cleans the data for insertion to the database
913 *
914 * @param mixed $value, the value to be cleaned
915 * @access public
916 * @return boolean|string|integer
917 */
918 public function scrub($value){
919
920 return mysql_real_escape_string($value);
921 }
922
923 /**
924 * Builds the SQL statement for the Query and sets it to _sql_main
925 *
926 * @access public
927 * @return object, instance of this class
928 */
929 public function build(){
930 $this->_sql_full = $this->_sql_main;
931
932 /**
933 * add the join if the sql statement is a select
934 */
935 if(preg_match('/^SELECT/i', $this->_sql_full) && $this->_sql_join){
936 $this->_sql_full .= ' '. $this->_sql_join;
937 }
938
939 if ($this->_sql_where) {
940 $this->_sql_full .= ' ' . $this->_sql_where;
941 }
942
943 if ($this->_sql_group) {
944 $this->_sql_full .= ' ' . $this->_sql_group;
945 }
946
947 if ($this->_sql_order) {
948 $this->_sql_full .= ' ' . $this->_sql_order;
949 }
950
951 if ($this->_sql_limit) {
952 $this->_sql_full .= ' ' . $this->_sql_limit;
953 }
954
955 $this->_sql_full = trim($this->_sql_full);
956
957 $this->validateSql();
958
959 return $this;
960 }
961
962 /**
963 * Checks a sql statement to see if it's valid
964 *
965 * @access public
966 * @throws System_Database_Exception
967 * @return null
968 */
969 public function validateSql(){
970 if ($this->_sql_main === '') {
971 $this->_errors = true;
972 throw new System_Database_Exception('Please select a method before building the Query, the sql main statement is not set.');
973 } else {
974 if (preg_match('/^UPDATE|DELETE/i', $this->_sql_main)) {
975 if ($this->_sql_where === ''){
976 $this->_errors = true;
977 }
978 }
979 }
980 }
981
982 /**
983 * Runs the Query and returns a Data Object
984 *
985 * @access public
986 * @return Object, instance of this class
987 */
988 public function run(){
989 /**
990 * connect to the database if not already connected
991 * then build the query
992 */
993 $this->connectDB()->build();
994
995 /**
996 * check to see if the _sql_full is a select statement
997 * if true, run System_Database_Root::getData
998 * else run System_Database_Root::Query
999 */
1000 if(preg_match('/^SELECT/i', $this->_sql_full)){
1001 $this->_data_set = $this->_db->runQuery($this->_sql_full)->resultSet();
1002 $data = pick($this->_data_set[0], array());
1003 $this->setValue($data, false, false);
1004 }else{
1005 $this->_db->runQuery($this->_sql_full);
1006 }
1007
1008 /**
1009 * if there was a db error, set the errors property to true
1010 */
1011 if($this->_db->error()) $this->_errors = true;
1012
1013 $this->_affected_rows = $this->_db->getNumRows();
1014 $this->_insert_id = $this->_db->getInsertId();
1015
1016 /**
1017 * if the insert_id property is set then we will rewrite the field[primary_key_field] to it
1018 */
1019 if((bool) $this->_insert_id){
1020 $this->setValue(array(
1021 $this->_primary_key_field => $this->_insert_id
1022 ));
1023 }
1024
1025 return $this;
1026 }
1027
1028
1029 /**
1030 * Gets the Total Number of Rows from the Query
1031 *
1032 * @access public
1033 * @return mixed
1034 */
1035 public function getTotal(){
1036 return $this->_affected_rows;
1037 }
1038
1039 /**
1040 * returns the data_Set property
1041 *
1042 * @access public
1043 * @return array
1044 */
1045 public function getAllData(){
1046 return $this->_data_set;
1047 }
1048
1049 /**
1050 * gets a value from the _frame property array based on key
1051 *
1052 * @access public
1053 * @return mixed String value of key passed in, if asterisk is passed array of values
1054 */
1055 public function getValue($key = '*'){
1056 $value = false;
1057 $run = true;
1058 $frame = pick($this->_v_data_frame, $this->_data_frame);
1059
1060 /**
1061 * check to see if an asterisk is passed as the key
1062 * if it is, return all the rows that are available to the current user
1063 * if not, return the requested row
1064 */
1065 if($key === '*'){
1066 $result = array();
1067
1068 foreach($this->_fields as $key => $val){
1069 $add = true;
1070
1071 /**
1072 * TO DO: implement access level check
1073 */
1074 if($add) $result[$key] = stripslashes($val);
1075 }
1076
1077 $value = count($result) ? $result : false;
1078 }else{
1079 if(isset($frame[$key]['get'])){
1080 if(!System_Security_Access::andThis($this->_data_frame[$key]['get'])) $run = false;
1081 }
1082
1083 if($run && array_key_exists($key, $frame) && array_key_exists($key, $this->_fields)){
1084 $value = stripslashes($this->_fields[$key]);
1085 }
1086 }
1087
1088 return $value;
1089 }
1090
1091 /**
1092 * returns a string of values based on the indexed fields for the object
1093 *
1094 * @access public
1095 * @return string
1096 */
1097 public function getIndexFieldValues(){
1098 $index_string = array();
1099
1100 foreach($this->_index_fields as $field){
1101 $index_string[] = $this->getValue($field);
1102 }
1103
1104 return implode(' ', $index_string);
1105 }
1106
1107 /**
1108 * sets the values for the fields property array
1109 * checks to see if the key exists in either the virtual data frame property (_v_data_frame) if set, or the default data frame (_data_frame)
1110 * does an access level set if the key has set restrictions
1111 * validates the input
1112 *
1113 * @param array, $val_array array of key value pairs to be set and validated against
1114 * @param boolean, $mysql_variable_check flag to tell the validator to run the variables through the mysqlVariable validation method. Defaults to false
1115 * @param boolean $validate_input Flag that tells the method to validate the input or not
1116 * @access public
1117 * @return Object, instance of the class
1118 */
1119 public function setValue(array $val_array = array(), $mysql_variable_check = false, $validate_input = true){
1120 $frame = pick($this->_v_data_frame, $this->_data_frame);
1121 $validation_errors = false;
1122
1123 foreach($val_array as $key => $val){
1124 $run = true;
1125
1126 /**
1127 * check to see if the key is in the _frame property array
1128 * if it is, check the _set_restrictions property array
1129 */
1130 if(array_key_exists($key, $frame)){
1131 if(isset($frame[$key]['set'])){
1132 if(!System_Security_Access::andThis($this->_data_frame[$key]['set'])) $run = false;
1133 }
1134
1135 /**
1136 * if the user can add the value the the _fields property
1137 * validate the data and if it is valid, add it
1138 * else, skip it
1139 */
1140 if($run){
1141 /**
1142 * get the rules and processed valued based on the type definition
1143 * if mysql_variable_check is set, redefine the rules to check against the mysqlVariable method
1144 */
1145 $rules = $mysql_variable_check ? array('type' => 'mysqlVariable') : $frame[$key];
1146 $value = $this->getProcessValue($frame[$key]['type'], $val);
1147
1148 /**
1149 * if the rules is an array and is greater than zero
1150 * validate the values against the rules
1151 * if there are validation errors, set run to false
1152 */
1153 if($validate_input && is_array($rules) && count($rules)){
1154
1155 $error = $this->validate($key, $value, $rules);
1156 $run = $error ? false : true;
1157
1158 if($error){
1159 $validation_errors = true;
1160 $this->_errors = true;
1161 }
1162 }
1163
1164 if($run){
1165 /**
1166 * HACK - encodes the password field
1167 */
1168 if($frame[$key]['type'] == 'password'){
1169 $value = System_Registry_Storage::get('System_Utility_String')->setValue($value)->encodePassword()->get();
1170 }
1171
1172 $this->_fields[$key] = $value;
1173 }
1174 }
1175 }
1176 }
1177
1178 return $this;
1179 }
1180
1181 /**
1182 * sets the validate_input property
1183 *
1184 * @param boolean $validate
1185 * @access public
1186 * @return System_Database_Base
1187 */
1188 public function setValidateInput($validate){
1189 $this->_validate_input = (bool) $validate;
1190
1191 return $this;
1192 }
1193
1194 /**
1195 * cleans a model out and return a cloned model with only the data rows needed for the page in question.
1196 * @param integer $start The start number in the array for the first record.
1197 * @param integer $per_page The number of results to leave.
1198 * @access public
1199 * @return object SYSTEM_DATABASE_BASE
1200 */
1201 public function pagination($start = 0, $per_page = 20){
1202 $new_model = clone $this;
1203 $new_model->_data_set = array_slice($this->_data_set, $start, $per_page);
1204 $new_model->rewind();
1205
1206 return $new_model;
1207 }
1208
1209
1210
1211 /**
1212 * gets the value of a certain object based on the type of object it is
1213 *
1214 * @access private
1215 * @return Object System_Database_Base
1216 */
1217 private function getProcessValue($type, $value){
1218 $return = trim($value);
1219
1220 /**
1221 * loop through the fields property and get the processed value for each value
1222 */
1223 if($type){
1224 switch($type){
1225 case 'date':
1226 case 'datetime':
1227 $method = 'mysql'. $type;
1228 $obj = new System_Utility_Date;
1229
1230 if (!$value) {
1231 $value = '0000-00-00 00:00:00';
1232 }
1233
1234 $return = $obj->setValue($value)->$method()->get();
1235 break;
1236
1237 case 'text':
1238 /**
1239 * just use the default method for now
1240 */
1241 //$obj = System_Registry_Storage::get('System_Utility_String');
1242 //$return = $object->setValue($value)->get('original');
1243 break;
1244 }
1245 }
1246
1247 return $return;
1248 }
1249
1250 /**
1251 * validates the data passed to the setValue method
1252 *
1253 * @access private
1254 * @return boolean, status of the validation
1255 */
1256 private function validate($name, $value, $rules){
1257 try {
1258 $this->_validator->reset()->setName($name)->setValue($value)->setRules($rules)->validate();
1259 } catch (Exception $e) {
1260 echo $e->getMessage();
1261 }
1262
1263 return $this->_validator->error();
1264 }
1265
1266 /**
1267 * sets the select statement fields
1268 *
1269 * @param Array $val_array - Array containing the names of the fields to be used in the select statement in this format: array(field, field2). If the array contains key => value paris, then the value becomes the "AS" to the key
1270 * @access public
1271 * @return Object, Instance of this class
1272 */
1273 public function selectFields(array $val_array = array()){
1274 foreach($val_array as $key => $val){
1275 $run = true;
1276 $index = is_string($key) ? $key : $val;
1277 $data_frame = is_array($this->_v_data_frame) ? $this->_v_data_frame : $this->_data_frame;
1278 $column_aggregate = $this->extractAggregateFunction($index);
1279 $column_clean = $this->extractTableName($column_aggregate['column']);
1280 $table = $column_clean['table'];
1281 $column = $column_clean['column'];
1282
1283 if(isset($column_aggregate['aggregate'])){
1284 $this->updateDataFrame($val);
1285
1286 $data_frame = is_array($this->_v_data_frame) ? $this->_v_data_frame : $this->_data_frame;
1287 $column = $val;
1288 }
1289
1290 /**
1291 * check to see if the key is in the _data_frame property array
1292 * if it is, check the get restrictions for the current key
1293 */
1294 if(array_key_exists($column, $data_frame) || $column === '*'){
1295 if(isset($data_frame[$key]['get'])){
1296 if(!System_Security_Access::andThis($data_frame[$key]['get'])) $run = false;
1297 }
1298
1299 if($run){
1300 $this->_select_fields[$key] = $val;
1301 }
1302 }
1303 }
1304
1305 return $this;
1306 }
1307
1308 /**
1309 * Sets the group_by Clause in the sql string.
1310 * @access public
1311 * @return mixed
1312 */
1313 public function group(array $data = array()){
1314
1315 if (count($data)) {
1316 $this->_sql_group = 'GROUP BY ' . join(", ", $data);
1317 }
1318
1319 return $this;
1320
1321 }
1322
1323 /**
1324 * this class will create a match series for full-text searching. a string of rows to search against and the text itself.
1325 * @access public
1326 * @param array $rows An array of the columns that will be searched in the table.
1327 * @param string $text_search The String that is being searched for.
1328 * @param string $as The name of the match results column in the returning data.
1329 * @return this
1330 */
1331 public function selectMatch($rows, $text_search, $as = 'match'){
1332 if (count($rows)) {
1333 $row_string = implode(', ', $rows);
1334
1335 $sql = 'MATCH(' . $row_string . ')' . "AGAINST ('" . trim($text_search) . "')";
1336 $this->_select_fields[$sql] = $as;
1337 }
1338
1339 return $this;
1340 }
1341
1342
1343
1344 /**
1345 * Joins a data_frame from another object_model to this one in the _v_data_frame property
1346 *
1347 * @access public
1348 * @return Object, instance of this class
1349 */
1350 public function joinObject(){
1351 if(func_num_args()){
1352 $args = func_get_args();
1353 $frame = $this->getDataFrame(true);
1354 $count = 0;
1355 var_dump($args);
1356 /**
1357 * get an instance of the object, get its data_frame and merge it with this class' data_frame
1358 */
1359 foreach($args as $object){
1360 if($object->getStatus() !== 'error'){
1361 $frame = array_merge($frame, $object->getDataFrame(true));
1362
1363 if($object_fields = $object->getValue('*')){
1364 $this->_fields = array_merge($this->_fields, $object_fields);
1365 }
1366
1367 $count++;
1368 }
1369 }
1370
1371 if($count) $this->_v_data_frame = $frame;
1372 }
1373
1374 $this->setRequiredFields()->setIndexes();
1375
1376 return $this;
1377 }
1378
1379 /**
1380 * returns the error property
1381 *
1382 * @access public
1383 * @return boolean
1384 */
1385 public function error(){
1386 return $this->_errors;
1387 }
1388
1389 /**
1390 * resets the fields property array, clears the _data_set, affected_rows, and sets _current to zero
1391 *
1392 * @access public
1393 * @return Object, instance of the class
1394 */
1395 public function reset(){
1396 $this->_validator;
1397 $this->_system_log;
1398 $this->_crud = array(
1399 'create' => '*',
1400 'read' => '*',
1401 'update' => '*',
1402 'delete' => '*'
1403 );
1404 $this->_data_frame = array();
1405 $this->_v_data_frame = false;
1406 $this->_fields = array();
1407 $this->_select_fields = array();
1408 $this->_required_fields = array(
1409 'select' => array(),
1410 'delete' => array(),
1411 'insert' => array(),
1412 'update' => array()
1413 );
1414 $this->_index_fields = array();
1415 $this->_db;
1416 $this->_affected_rows = 0;
1417 $this->_errors = null;
1418 $this->_status = false;
1419 $this->_table_name;
1420 $this->_data_set = array();
1421 $this->_current = 0;
1422 $this->_sql_main = false;
1423 $this->_sql_join = false;
1424 $this->_sql_where = false;
1425 $this->_sql_order = false;
1426 $this->_sql_limit = false;
1427 $this->_sql_full = '';
1428 $this->_primary_key_field;
1429 $this->_insert_id = '';
1430 $this->__construct();
1431
1432 $this->setIndexes();
1433
1434 return $this;
1435 }
1436
1437 /**
1438 * returns the object's data frame property
1439 *
1440 * @param boolean $virtual -- if the virtual data frame is available, grab it. Defaulted to false
1441 * @access public
1442 * @return array
1443 */
1444 public function getDataFrame($virtual = false){
1445 return $virtual ? pick($this->_v_data_frame, $this->_data_frame) : $this->_data_frame;
1446 }
1447
1448 /**
1449 * returns the object's virutal data frame property
1450 *
1451 * @access public
1452 * @return array
1453 */
1454 public function getVirtualDataFrame(){
1455 return $this->_v_data_frame;
1456 }
1457
1458 /**
1459 * returns the status of the last query ran
1460 *
1461 * @access public
1462 * @return string
1463 */
1464 public function getStatus(){
1465 return $this->_status;
1466 }
1467
1468 /**
1469 * returns the insatance of the current database
1470 *
1471 * @access public
1472 * @return object, instance of System_Database_Query
1473 */
1474 public function getDb(){
1475 return $this->_db;
1476 }
1477
1478 /**
1479 * returns a row for the _data_set property if it is set
1480 * sets the current state of the object to the _data_set row
1481 *
1482 * @var integer $row
1483 * @access public
1484 * @return Mixed Array of data, boolean
1485 */
1486 public function getRow($row){
1487 $row = (int) $row;
1488 $result = false;
1489
1490 /**
1491 * if there is data, set the index
1492 */
1493 if($this->_affected_rows && $this->valid()){
1494 $this->_fields = array();
1495 $this->_current = $row;
1496
1497 $this->setValue($this->_data_set[$this->_current], false, $this->_validate_input);
1498
1499 $result = $this->getValue('*');
1500 }
1501
1502 return $result;
1503 }
1504
1505 /**
1506 * sets the current pointer to zero
1507 * method inherited from the Iterator interface
1508 *
1509 * @access public
1510 * @return Object instance of this class
1511 */
1512 public function rewind(){
1513 $this->_current = 0;
1514 $this->getRow(0);
1515
1516
1517 return $this;
1518 }
1519
1520 /**
1521 * returns the key from the _current property
1522 *
1523 * @access public
1524 * @return integer
1525 */
1526 public function key(){
1527 return $this->_current;
1528 }
1529
1530 /**
1531 * Checks to see if the current key of the data_set is set
1532 *
1533 * @access public
1534 * @return boolean
1535 */
1536 public function current(){
1537 $return = false;
1538
1539 if($this->valid()){
1540 $return = true;
1541 }
1542
1543 return $return;
1544 }
1545
1546 /**
1547 * Checks to see if the next key of the data_set is set
1548 *
1549 * @access public
1550 * @return boolean
1551 */
1552 public function next(){
1553 $this->_validate_input = false;
1554 $return = false;
1555
1556 if($this->getRow(++$this->_current)){
1557 $return = true;
1558 }
1559
1560 return $return;
1561 }
1562
1563 /**
1564 * checks to see if the current index of the data_set is valid
1565 *
1566 * @access public
1567 * @return boolean
1568 */
1569 public function valid(){
1570 $return = false;
1571
1572 if(isset($this->_data_set[$this->_current])){
1573 $return = true;
1574 }
1575
1576 return $return;
1577 }
1578
1579 /**
1580 * resets the keys in the _data_set property to be consecutive digits
1581 *
1582 * @access public
1583 * @return Object System_Database_Base
1584 */
1585 public function resetDataSetKeys(){
1586 $arr = array();
1587
1588 foreach($this->_data_set as $key => $value){
1589 $arr[] = $value;
1590 }
1591
1592 $this->_data_set = $arr;
1593
1594 return $this;
1595 }
1596
1597 /**
1598 * Checks to see if a record exists in the table. Will return a true if it does exist, false of it doesn't.
1599 * This function is useful if you want to make sure that a user's input is unique, such as emails on registration.
1600 *
1601 * @param array $vals, the values to be used in the select statement
1602 * @access public
1603 * @return boolean
1604 */
1605 public function recordExist($field, $value){
1606 $return = false;
1607
1608 if (isset($field) && isset($value)) {
1609 $this->selectFields(array($this->_primary_key_field))->where(array($field => $value))->select()->run();
1610
1611 if ($this->_affected_rows > 0) {
1612 $this->reset();
1613 $return = true;
1614 }
1615 }
1616
1617 $this->reset();
1618
1619 return $return;
1620 }
1621
1622 /**
1623 * returns the registration_id for this class
1624 *
1625 * @access public
1626 * @return string
1627 */
1628 public function getRegistrationId(){
1629 return $this->_registration_id;
1630 }
1631
1632 /**
1633 * method used to register this object as new with the unit of work class
1634 *
1635 * @param array $dependancies, an array o dependent objects based on the stack that is present in the unit of work class
1636 * @access public
1637 * @return object, instance of System_Database_Work
1638 */
1639 public function registerNew(array $dependicies = array()){
1640 return $this->_unit_of_work->register($this, 'new', $dependicies);
1641 }
1642
1643 /**
1644 * method used to register this object as dirty with the unit of work class
1645 *
1646 * @param array $dependancies, an array o dependent objects based on the stack that is present in the unit of work class
1647 * @access public
1648 * @return object, instance of System_Database_Work
1649 */
1650 public function registerDirty(array $dependicies = array()){
1651 return $this->_unit_of_work->register($this, 'dirty', $dependicies);
1652 }
1653
1654 /**
1655 * method used to register this object as removed with the unit of work class
1656 *
1657 * @param array $dependancies, an array o dependent objects based on the stack that is present in the unit of work class
1658 * @access public
1659 * @return object, instance of System_Database_Work
1660 */
1661 public function registerRemoved(array $dependicies = array()){
1662 return $this->_unit_of_work->register($this, 'removed', $dependicies);
1663 }
1664
1665 /**
1666 * Returns an array of the table and column for a table.column format to $table => $column.
1667 *
1668 * @access public
1669 * @param string $column_name The column value being passed.
1670 * @return array
1671 */
1672 public function extractTableName($column_name){
1673 $return = array('table' => false, 'column' => $column_name);
1674
1675 if (stripos($column_name, '.')){
1676 list($table, $column) = explode('.', $column_name);
1677 $return['table'] = $table;
1678 $return['column'] = $column;
1679 }
1680
1681 return $return;
1682 }
1683
1684 /**
1685 * extract the aggregate function in the select field if one exists
1686 * such as count(), max(), min(), etc.
1687 * @param string $column_name the column name as set in selectFields()
1688 * @access public
1689 * @return array the array with the column name and the aggregate function if one is set.
1690 */
1691 public function extractAggregateFunction($column_name){
1692
1693 $return = array('column' => $column_name);
1694 $aggregates = array(
1695 'avg', 'bit_and', 'bit_or', 'bit_xor', 'count', 'group_concat', 'datediff', 'ifnull', 'max', 'min', 'std', 'stddev_pop', 'stddev_samp', 'stddev', 'sum', 'var_pop', 'var_samp', 'variance'
1696 );
1697
1698 if (preg_match_all('/('. implode('|', $aggregates) .')\((.*?)\)/', $column_name, $matches)) {
1699 $return['aggregate'] = $matches[1][0];
1700 $return['column'] = $matches[2][0];
1701 }
1702
1703 return $return;
1704 }
1705
1706 /**
1707 * Appends a field to the dataframe
1708 * @access public
1709 * @param string $column_name The name of the column being updated.
1710 * @param array $rules the rules for the column
1711 * @param boolean $overwrite If true will allow the function to overwrite an existing entry in the dataframe
1712 * @return System_Database_Base
1713 */
1714 public function updateDataFrame($column_name, array $rules = array('type' => ''), $overwrite = false){
1715 $proceed = false;
1716
1717 if ($this->_v_data_frame) {
1718 $data_frame = &$this->_v_data_frame;
1719 } else {
1720 $data_frame = &$this->_data_frame;
1721 }
1722
1723 if (isset($data_frame[$column_name])) {
1724 if ($overwrite) {
1725 $proceed = true;
1726 }
1727 } else {
1728 $proceed = true;
1729 }
1730
1731 if ($proceed) {
1732 $data_frame[$column_name] = $rules;
1733 }
1734
1735 return $this;
1736 }
1737
1738
1739 /**
1740 * Removes a row of data from the fields property
1741 *
1742 * @access public
1743 * @return System_Database_Base
1744 */
1745 public function removeRow(){
1746 unset($this->_data_set[$this->_current]);
1747
1748 $this->_affected_rows--;
1749
1750 return $this;
1751 }
1752
1753 /**
1754 * rewrites the data_set property
1755 *
1756 * @access public
1757 * @return Object System_Database_Base
1758 */
1759 public function resetDataSet(array $data){
1760 $this->_data_set = $data;
1761 $this->_affected_rows = count($this->_data_set);
1762
1763 return $this;
1764 }
1765
1766 /**
1767 * Sets the property $_check_pk = false
1768 * @access public
1769 * @return Object System_Database_Base
1770 */
1771 public function ignorePk(){
1772 $this->_check_pk = false;
1773
1774 return $this;
1775 }
1776
1777 /**
1778 * converts the complete dataset to an array
1779 *
1780 * @param mixed $args -- pass in a comma-dillemented string defining which columns to return
1781 * @access public
1782 * @return array
1783 */
1784 public function toArray(){
1785 $ret = array();
1786
1787 if((bool) func_num_args()){
1788 $fields = func_get_args();
1789
1790 foreach($this->_data_set as $set){
1791 $row = array();
1792
1793 foreach($set as $field => $value){
1794 if(in_array($field, $fields)){
1795 $row[$field] = $value;
1796 }
1797 }
1798
1799 $ret[] = $row;
1800 }
1801 }else{
1802 $ret = $this->_data_set;
1803 }
1804
1805 return $ret;
1806 }
1807
1808 /**
1809 * converts the dataset to a key value pair array. The key should be a field that is unique to each row in the result set
1810 *
1811 * @param string $key -- the name of the field to get the key from
1812 * @param string $val -- the name of the field to get the value from
1813 * @throws Exception -- if either the key or the val does not exist in the dataframe
1814 * @access public
1815 * @return array
1816 */
1817 public function toKeyValArray($key, $val){
1818 $ret = array();
1819 $data_frame = pick($this->_v_data_frame, $this->_data_frame);
1820
1821 if(!isset($data_frame[$key]) || !isset($data_frame[$val])){
1822 throw new Exception('You must use two fields that exist in the data frame. You passed in: '. $key .' and '. $val);
1823 }
1824
1825 if(count($this->_data_set) && (bool) $this->_data_set){
1826 foreach($this->_data_set as $set){
1827 $ret[$set[$key]] = $set[$val];
1828 }
1829 }
1830
1831 return $ret;
1832 }
1833}