1 <?php
  2 
  3 /* * * * * 
  4  *  MAGRATHEA QUERY
  5  *  version: 0.7
  6  *      last modified: 2013-07-29 by Paulo
  7  * * * * */
  8 
  9 /**
 10  * Creates queries making use of objects and tables
 11  */
 12 class MagratheaQuery{
 13 
 14     protected $select;
 15     protected $selectDefaultArr;
 16     protected $selectArr;
 17     protected $obj_base;
 18     protected $obj_array;
 19     protected $tables;
 20     protected $join;
 21     protected $joinArr;
 22     protected $where;
 23     protected $whereArr;
 24     protected $order;
 25     protected $page;
 26     protected $limit;
 27     protected $group;
 28 
 29     protected $sql;
 30 
 31     /**
 32      * private function
 33      *  basically, you give me anything and I shall send back to you a correct object of that thing
 34      *  (at least, in theory, I should work like that =P)
 35      * @param       type                $object     the object that I need is kind of this
 36      * @return      the correct object
 37      * @throws      MagratheaModelException         If Model does not exists...
 38      */
 39     private function GiveMeThisObjectCorrect($object){
 40         if(is_string($object)){
 41             if(class_exists($object)){
 42                 $object = new $object();
 43             } else {
 44                 throw new MagratheaModelException("Model does not exists: ".$object);
 45             }
 46         }
 47         return $object;
 48     }
 49 
 50     /**
 51      * constructor
 52      */
 53     public function __construct(){
 54         $this->obj_array = array();
 55         $this->select = "SELECT ";
 56         $this->selectArr = array();
 57         $this->selectExtra = array();
 58         $this->selectDefaultArr = array();
 59         $this->join = "";
 60         $this->joinArr = array();
 61         $this->joinType = array();
 62         $this->where = "";
 63         $this->whereArr = array();
 64         $this->order = "";
 65         $this->page = 0;
 66         $this->limit = null;
 67         $this->group = null;
 68         return $this;
 69     }
 70 
 71     /**
 72      * Cleans a value in order to avoid SQL injection
 73      * @param   string      $query      string to be cleared
 74      * @return  string      clean data
 75      */
 76     static public function Clean($query){
 77         $query = str_replace("'", "\'", $query);
 78         $query = str_replace('"', '\"', $query);
 79         return $query;
 80     }   
 81 
 82     /**
 83      * Creates. Just that. Just like God did.
 84      */
 85     static public function Create(){
 86         return new self();
 87     }
 88     /**
 89      * Generates a SELECT query
 90      * @param   string              $sel    string to be selected
 91      *                                      in a query SELECT [blablabla] FROM ...
 92      *                                      the [blablabla] should be send to this function. Got it?
 93      * @return  MagratheaQuery      
 94      */
 95     static public function Select($sel=""){
 96         $new_me = new self();
 97         $new_me->SelectStr($sel);
 98         return $new_me;
 99     }
100     /**
101      * Generates a DELETE query
102      * @return      MagratheaQueryDelete
103      */
104     static public function Delete(){
105         return new MagratheaQueryDelete();
106     }
107 
108     /**
109      * Generates a UPDATE query
110      * @return      MagratheaQueryUpdate
111      */
112     static public function Update(){
113         return new MagratheaQueryUpdate();
114     }
115 
116     /**
117      * Generates a INSERT query
118      * @return      MagratheaQueryInsert
119      */
120     static public function Insert(){
121         return new MagratheaQueryInsert();
122     }
123 
124     /**
125      * Set table
126      * @param   string      $t      Table to be selected from
127      * @return  itself
128      */
129     public function Table($t){
130         $this->tables = $t;
131         return $this;
132     }
133 
134     /**
135      * Alias for Obj
136      * @param   string or object    $obj    Object to query
137      * @return  itself
138      */
139     public function Object($obj){
140         return $this->Obj($obj);
141     }
142     /**
143      * Set object for getting information in query
144      * @param   object or string    $obj    object or string to be selected
145      * @return  itself
146      */
147     public function Obj($obj){
148         $obj = $this->GiveMeThisObjectCorrect($obj);
149         $this->obj_base = $obj;
150         $this->tables = $obj->GetDbTable();
151         $this->SelectObj($obj);
152         return $this;
153     }
154 
155     /**
156      * Fields to be included on the query
157      * @param   string or array     $fields     string or array that will be added to the fields in the *SELECT* built
158      * @return  itself
159      */
160     public function Fields($fields){
161         if(is_array($fields)){
162             $this->selectArr = array_merge($this->selectArr, $fields);
163         } else {
164             array_push($this->selectArr, $fields);
165         }
166         return $this;
167     }
168     /**
169      * String to be selected
170      * @param   string      $sel        string that will be used in the *SELECT* built
171      * @return  itself
172      */
173     public function SelectStr($sel) {
174         if(!empty($sel)){
175             array_push($this->selectArr, $sel);
176         }
177         return $this;
178     }
179     /**
180      * Includes a field to select query to be added in the end of the clause
181      * @param   string      $sel        string that will be added in the *SELECT* built
182      * @return  itself
183      */
184     public function SelectExtra($sel){
185         array_push($this->selectExtra, $sel);
186         return $this;
187     }
188     /**
189      * Selects all the fields for an object
190      * @param   object      $obj        object which its fields are going to be added in the *SELECT* built
191      * @return  itself
192      */
193     public function SelectObj($obj){
194         $fields = $obj->GetFieldsForSelect();
195         array_push($this->selectDefaultArr, $fields);
196         return $this;
197     }
198     /**
199      * Select multiple objects
200      * @param   array<objects>      $arrObj         an array of objects that are going to be selected
201      * @return  itself
202      */
203     public function SelectArrObj($arrObj){
204         foreach ($arrObj as $key => $value) {
205             $sThis = $value->GetFieldsForSelect();
206             array_push($this->selectDefaultArr, $sThis);
207         }
208         return $this;
209     }
210 
211     /**
212      * A Join to be included in the query
213      * @param   string          $joinGlue           join to be included in the query
214      * @return  itself
215      */
216     public function Join($joinGlue){
217         array_push($this->joinArr, $joinGlue);
218         return $this;
219     }
220     /**
221      * Gets automatically related object
222      * @param   object or string        $object         object or string that will be returned in the query
223      * @param   string                  $field          field that is responsible for the relation (from the main object)
224      * @return  itself
225      */ 
226     public function HasOne($object, $field){
227         try{
228             if(!$this->obj_base) throw new MagratheaModelException("Object Base is not an object");
229             $object = $this->GiveMeThisObjectCorrect($object);
230             $this->SelectObj($object);
231             $joinGlue = " INNER JOIN ".$object->GetDbTable()." ON ".$this->obj_base->GetDbTable().".".$field." = ".$object->GetDbTable().".".$object->GetPkName();
232         } catch(Exception $ex){
233             throw new MagratheaModelException("MagratheaQuery 'HasOne' must be used with MagratheaModels => ".$ex->getMessage());
234         }
235         array_push($this->joinArr, $joinGlue);
236         array_push($this->joinType, "has_one");
237         array_push($this->obj_array, $object);
238         return $this;
239     }
240     /**
241      * Gets automatically related object
242      * @param   object or string        $object         object or string that will be returned in the query
243      * @param   string                  $field          field that is responsible for the relation (from the main object)
244      * @return  itself
245      */ 
246     public function HasMany($object, $field){
247         try{
248             if(!$this->obj_base) throw new MagratheaModelException("Object Base is not an object");
249             $object = $this->GiveMeThisObjectCorrect($object);
250             $this->SelectObj($object);
251             $joinGlue = " INNER JOIN ".$object->GetDbTable()." ON ".$object->GetDbTable().".".$field." = ".$this->obj_base->GetDbTable().".".$this->obj_base->GetPkName();
252         } catch(Exception $ex){
253             throw new MagratheaModelException("MagratheaQuery 'HasMany' must be used with MagratheaModels => ".$ex->getMessage());
254         }
255         array_push($this->joinArr, $joinGlue);
256         array_push($this->joinType, "has_many");
257         array_push($this->obj_array, $object);
258         return $this;
259     }
260 
261     /**
262      * Gets automatically related object
263      * @param   object or string        $object         object or string that will be returned in the query
264      * @param   string                  $field          field that is responsible for the relation (from the other object)
265      * @return  itself
266      */ 
267     public function BelongsTo($object, $field){
268         try{
269             if(!$this->obj_base) throw new MagratheaModelException("Object Base is not an object");
270             $object = $this->GiveMeThisObjectCorrect($object);
271             $this->SelectObj($object);
272             $joinGlue = " INNER JOIN ".$object->GetDbTable()." ON ".$object->GetDbTable().".".$field." = ".$this->obj_base->GetDbTable().".".$this->obj_base->GetPkName();
273         } catch(Exception $ex){
274             throw new MagratheaModelException("MagratheaQuery 'BelongsTo' must be used with MagratheaModels => ".$ex->getMessage());
275         }
276         array_push($this->joinArr, $joinGlue);
277         array_push($this->joinType, "belongs_to");
278         array_push($this->obj_array, $object);
279         return $this;
280     }
281     /**
282      * Includes inner join
283      * @param   string      $table      Table for inner join
284      * @param   string      $clause     Clause for where in the join
285      * @return  itself
286      */ 
287     public function Inner($table, $clause){
288         try{
289             $joinGlue = " INNER JOIN ".$table." ON ".$clause;
290         } catch(Exception $ex){
291             throw new MagratheaModelException("MagratheaQuery Exception => ".$ex->getMessage());
292         }
293         array_push($this->joinArr, $joinGlue);
294         return $this;
295     }
296     /**
297      * Includes inner join with Object
298      * @param   MagratheaModel      $object     object for inner join
299      * @return  itself
300      */ 
301     public function InnerObject($object, $clause){
302         try{
303             $object = $this->GiveMeThisObjectCorrect($object);
304             $this->SelectObj($object);
305             $joinGlue = " INNER JOIN ".$object->GetDbTable()." ON ".$clause;
306         } catch(Exception $ex){
307             throw new MagratheaModelException("MagratheaQuery Exception => ".$ex->getMessage());
308         }
309         array_push($this->joinArr, $joinGlue);
310         return $this;
311     }
312 
313     /**
314      * all the objects that are used in this query
315      * @return      array   the objects that we have here...
316      */
317     public function GetObjArray(){
318         return $this->obj_array;
319     }
320     /**
321      * all the joins that were built in this query
322      * @return      array   the objects that we have here...
323      */
324     public function GetJoins(){
325         if(count($this->joinArr) == 0) return array();
326         $joins = array();
327         foreach ($this->joinArr as $key => $join) {
328             $j = array( 
329                 "type" => $this->joinType[$key], 
330                 "obj" => $this->obj_array[$key],
331                 "glue" => $this->joinArr[$key]
332             );
333             array_push($joins, $j);
334         }
335         return $joins;
336     }
337 
338     /**
339      * Builds where!
340      *  Is possible to send a string or an array, where the keys of the array will be the name of the fields which the query will be done
341      * @param   string or array     $whereSql       String or array for building the query with
342      * @param   string              $condition      glue condition ("AND" or "OR")
343      * @return  itself
344      */
345     public function Where($whereSql, $condition="AND"){
346         if(is_array($whereSql)){
347             return $this->WhereArray($whereSql, $condition);
348         }
349         array_push($this->whereArr, $whereSql);
350         return $this;
351     }
352     /**
353      * Builds where
354      *  Same as *Where*, but accepting only array
355      * @param   array       $arr        Array for building the query
356      * @param   string      $condition  glue condition ("AND" or "OR")
357      * @return  itself
358      */
359     public function WhereArray($arr, $condition = "AND"){
360         $newWhere = $this->BuildWhere($arr, $condition);
361         array_push($this->whereArr, $newWhere);
362         return $this;
363     }
364     /**
365      * Builds where, receiving the column and the value
366      * @param   string      $where          column
367      * @param   string      $field          value for column sent
368      * @param   string      $condition      glue condition ("AND" or "OR")
369      */
370     public function W($where, $field, $condition = "AND"){
371         $newWhere = $this->BuildWhere(array($where => $field), $condition);
372         array_push($this->whereArr, $newWhere);
373         return $this;
374     }
375 
376     /**
377      * alias for *Order*
378      * @param string $o Order by used in query
379      * @return  itself
380      */
381     public function OrderBy($o){ return $this->Order($o); }
382     /**
383      * Order by...
384      * @param string $o Order by used in query
385      * @return  itself
386      */
387     public function Order($o){
388         $this->order = $o;
389         return $this;
390     }
391 
392     /**
393      * Let's put a limit on it to help our database? Yes!
394      * @param   int     $l      limit
395      * @return  itself
396      */
397     public function Limit($l){
398         $this->limit = $l;
399         return $this;
400     }
401     /**
402      * Which page?
403      *  working altogether with *Limit*, to bring a specific page, with a specific amount of results
404      * @param int   $p      page to be retrieved
405      * @return  itself
406      */
407     public function Page($p){ // there is a page zero.
408         $this->page = $p;
409         return $this;
410     }
411 
412     /**
413      * Alias for *Group*
414      * @param   string      $g      String to build the group
415      * @return  itself
416      */
417     public function GroupBy($g){ return $this->Group($g); }
418     /**
419      * Groupping the results...
420      * @param   string      $g      String to build the group
421      * @return  itself
422      */
423     public function Group($g){
424         $this->group = $g;
425         return $this;
426     }
427 
428     /**
429      * ...and we're gonna build the query for you.
430      *  After gathering all the information, this function returns to you
431      *      a wonderful SQL query for be executed
432      *      or to be hang in the wall of a gallery art exhibition,
433      *      depending how good you are in building queries
434      * @return      string      Query!!!
435      */
436     public function SQL(){
437         $this->sql = "";
438         $sqlSelect = $this->select;
439         if(count($this->selectArr) > 0){
440             $sqlSelect .= implode(', ', $this->selectArr);
441         } else if(count($this->selectDefaultArr) > 0){
442             $sqlSelect .= implode(', ', $this->selectDefaultArr);
443         } else {
444             $sqlSelect .= "*";
445         }
446         if(count($this->selectExtra) > 0){
447             $sqlSelect .= ", ".implode(', ', $this->selectExtra);
448         }
449         $this->sql = $sqlSelect." FROM ".$this->tables;
450         if(count($this->joinArr) > 0){
451             $this->sql .= " ".implode(' ', $this->joinArr)." ";
452         }
453         $sqlWhere = $this->where;
454         if(count($this->whereArr) > 0){
455             $sqlWhere .= $this->where.implode(" AND ", $this->whereArr);
456         }
457         if(trim($sqlWhere)!=""){
458             $this->sql .= " WHERE ".$sqlWhere;
459         }
460         if(trim($this->group)!=""){
461             $this->sql .= " GROUP BY ".$this->group;
462         }
463         if(trim($this->order)!=""){
464             $this->sql .= " ORDER BY ".$this->order;
465         }
466         if(trim($this->limit)!=""){
467             $this->sql .= " LIMIT ".($this->page*$this->limit).", ".$this->limit;
468         }
469 
470         return $this->sql;
471     }
472 
473     /**
474      * How many? Tell me the amount!!!
475      *      We get all the information that you sent to the function
476      *          and, instead of returning the results,
477      *          we count how many rows you will have back
478      * @return  int     amount of rows
479      */
480     public function Count(){
481         $sqlCount = "SELECT COUNT(1) AS ok ";
482         $sqlCount .= " FROM ".$this->tables;
483         if(count($this->joinArr) > 0){
484             $sqlCount .= " ".implode(' ', $this->joinArr)." ";
485         }
486         $sqlWhere = $this->where;
487         if(count($this->whereArr) > 0){
488             $sqlWhere .= $this->where.implode(" AND ", $this->whereArr);
489         }
490         if(trim($sqlWhere)!=""){
491             $sqlCount .= " WHERE ".$sqlWhere;
492         }
493         if(trim($this->group)!=""){
494             $sqlCount .= " GROUP BY ".$this->group;
495         }
496         return $sqlCount;
497     }
498 
499     // STATIC AND HELPERS:
500     /**
501      * @access private
502      * *INTERNAL USE*
503      * Build *Where* clause with information sent
504      * @param   array   $arr        Array of conditions
505      * @param   string  $condition  glue of the conditions ("AND" or "OR")
506      */
507     public static function BuildWhere($arr, $condition){
508         $first = true;
509         $whereSql = "";
510         foreach($arr as $field => $value){
511             if( !$first ){ $whereSql .= " ".$condition; $first = false; }
512             if($value === null)
513                 $whereSql .= " ".$field." is null ";
514             else 
515                 $whereSql .= " ".$field." = '".$value."' ";
516             $first = false;
517         }
518         return $whereSql;
519     }
520     /**
521      * @access private
522      * *INTERNAL USE*
523      * gets an array with "fields" and returns it with "table.fields"
524      *  sample:
525      *      array_walk($joinObjDbValues, 'BuildSelect', $joinObjTable);
526      * @param [type] &$value    [description]
527      * @param [type] $key       [description]
528      * @param [type] $tableName [description]
529      */
530     public static function BuildSelect(&$value, $key, $tableName) { 
531         $value = $tableName.".".$key." AS '".$tableName."/".$key."'"; 
532     }
533     /**
534      * @access private
535      * *INTERNAL USE*
536      * Gets the result and splits into its specific array for each object
537      * @param   array   $arr    array to be splitted
538      */
539     public static function SplitArrayResult($arr){
540         $returnArray = array();
541         foreach ($arr as $key => $value) {
542             $position = strpos($key, '/');
543             if(!$position) continue;
544             $returnTable = substr($key, 0, $position);
545             $returnField = substr($key, $position+1);
546 
547             if( @is_null($returnArray[$returnTable]) ) $returnArray[$returnTable] = array();
548             $returnArray[$returnTable][$returnField] = $value;
549         }
550         return $returnArray;
551     }
552 
553     /**
554      * toString
555      * @return      string      SQL         (when applies)
556      */
557     public function __toString(){
558         return $this->SQL();
559     }
560 
561 }
562 
563 /**
564  * Extension of Magrathea Query for creating Insert queries
565  */
566 class MagratheaQueryInsert extends MagratheaQuery {
567 
568     private $fieldNames;
569     private $arrValues;
570 
571     public function __construct(){
572         $this->obj_array = array();
573         $this->fieldNames = array();
574         $this->arrValues = array();
575         return $this;
576     }
577 
578     /**
579      * Array with values
580      *  send an array which each key represents a field and the value
581      *      is the correspondent and we're gonna
582      *      build it nicely to you! =)
583      * @param   array   $vals       array with values
584      * @return  itself
585      */ 
586     public function Values($vals){
587         foreach ($vals as $key => $value) {
588             array_push($this->fieldNames, $key);
589             array_push($this->arrValues, $value);
590         }
591         return $this;
592     }
593 
594     /**
595      * ...and we're gonna build the query for you.
596      *  After gathering all the information, this function returns to you
597      *      a wonderful SQL query for be executed
598      *      or to be hang in the wall of a gallery art exhibition,
599      *      depending how good you are in building queries
600      * @return      string      Query!!!
601      */
602     public function SQL(){
603         $this->sql = "INSERT INTO ".$this->tables;
604         $this->sql .= " (".implode(', ', $this->fieldNames).") ";
605         $this->sql .= " VALUES ";
606         $this->sql .= " ('".implode('\', \'', $this->arrValues)."') ";
607         return $this->sql;
608     }
609 
610 }
611 
612 /**
613  * Extension of Magrathea Query for creating Update queries
614  */
615 class MagratheaQueryUpdate extends MagratheaQuery {
616 
617     private $fields;
618     private $rawFields;
619 
620     public function __construct(){
621         $this->obj_array = array();
622         $this->fields = array();
623         $this->rawFields = array();
624         $this->where = "";
625         $this->whereArr = array();
626         return $this;
627     }
628 
629     /**
630      * Set field for value
631      * @param   string      $field      field
632      * @param   string      $value      value for field sent
633      */
634     public function Set($field, $value){
635         $this->fields[$field] = $value;
636         return $this;
637     }
638 
639     /**
640      * Set raw condition for update
641      * @version 1.1 +
642      * @param   string      $field      field
643      * @param   string      $value      value for field sent
644      */
645     public function SetRaw($condition){
646         array_push($this->rawFields, $condition);
647         return $this;
648     }
649 
650     /**
651      * Set array of fields
652      * @param   array       $arr        array fields
653      * @todo  merge array instead of replacing it
654      */
655     public function SetArray($arr){
656         $this->field = $arr;
657         return $this;
658     }
659 
660     /**
661      * ...and we're gonna build the query for you.
662      *  After gathering all the information, this function returns to you
663      *      a wonderful SQL query for be executed
664      *      or to be hang in the wall of a gallery art exhibition,
665      *      depending how good you are in building queries
666      * @return      string      Query!!!
667      */
668     public function SQL(){
669         $this->sql = "UPDATE ".$this->tables." SET ";
670         $setsArray = array();
671         foreach ($this->rawFields as $field) {
672             array_push($setsArray, $field);
673         }
674         foreach ($this->fields as $field => $value) {
675             array_push($setsArray, $field." = '".$value."'");
676         }
677         $this->sql .= implode(", ", $setsArray);
678         $sqlWhere = $this->where;
679         if(count($this->whereArr) > 0){
680             $sqlWhere .= $this->where.implode(" AND ", $this->whereArr);
681         }
682         if(trim($sqlWhere)!=""){
683             $this->sql .= " WHERE ".$sqlWhere;
684         }
685         return $this->sql;
686     }
687 
688 }
689 
690 /**
691  * Extension of Magrathea Query for creating Delete queries
692  */
693 class MagratheaQueryDelete extends MagratheaQuery {
694 
695     public function __construct(){
696         $this->obj_array = array();
697         $this->join = "";
698         $this->joinArr = array();
699         $this->where = "";
700         $this->whereArr = array();
701         $this->order = "";
702         return $this;
703     }
704 
705     /**
706      * ...and we're gonna build the query for you.
707      *  After gathering all the information, this function returns to you
708      *      a wonderful SQL query for be executed
709      *      or to be hang in the wall of a gallery art exhibition,
710      *      depending how good you are in building queries
711      * @return      string      Query!!!
712      */
713     public function SQL(){
714         $this->sql = "DELETE FROM ".$this->tables;
715         if(count($this->joinArr) > 0){
716             $this->sql .= " ".implode(' ', $this->joinArr)." ";
717         }
718         $sqlWhere = $this->where;
719         if(count($this->whereArr) > 0){
720             $sqlWhere .= $this->where.implode(" AND ", $this->whereArr);
721         }
722         if(trim($sqlWhere)!=""){
723             $this->sql .= " WHERE ".$sqlWhere;
724         }
725         return $this->sql;
726     }
727 
728 }
729 
730 
731 
732 ?>