1: <?php
  2: /*
  3:  * Simpletools Framework.
  4:  * Copyright (c) 2009, Marcin Rosinski. (https://www.getsimpletools.com)
  5:  * All rights reserved.
  6:  * 
  7:  * LICENCE
  8:  *
  9:  * Redistribution and use in source and binary forms, with or without modification, 
 10:  * are permitted provided that the following conditions are met:
 11:  *
 12:  * -    Redistributions of source code must retain the above copyright notice, 
 13:  *      this list of conditions and the following disclaimer.
 14:  * 
 15:  * -    Redistributions in binary form must reproduce the above copyright notice, 
 16:  *      this list of conditions and the following disclaimer in the documentation and/or other 
 17:  *      materials provided with the distribution.
 18:  * 
 19:  * -    Neither the name of the Simpletools nor the names of its contributors may be used to 
 20:  *      endorse or promote products derived from this software without specific prior written permission.
 21:  * 
 22:  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR 
 23:  * IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY 
 24:  * AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR 
 25:  * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 
 26:  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 
 27:  * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER 
 28:  * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF 
 29:  * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 30:  * 
 31:  * @framework       Simpletools
 32:  * @copyright       Copyright (c) 2009 Marcin Rosinski. (http://www.getsimpletools.com)
 33:  * @license         http://www.opensource.org/licenses/bsd-license.php - BSD
 34:  * @version         Ver: 2.0.15 2014-12-31 10:45
 35:  * 
 36:  */
 37: 
 38:     namespace Simpletools\Db\Mysql;
 39: 
 40:     class QueryBuilder implements \Iterator
 41:     {
 42:         protected $_query   = '';
 43:         protected $_mysql   = '';
 44:         
 45:         protected $_result   = null;
 46: 
 47:         public function __construct($table,$mysql,$columns=array())
 48:         {
 49:             if (!($mysql instanceof \Simpletools\Db\Mysql\Client))
 50:             {
 51:                 throw new \Exception("2nd construct argument is not an instance of \Simpletools\Db\Mysql\Client", 404);
 52:             }
 53: 
 54:             if(count($columns))
 55:             {
 56:                 if(count($columns) == 1)
 57:                 {
 58:                     $columns = $columns[0];
 59:                 }
 60: 
 61:                 $this->_query['columns'] = $columns;
 62:             }
 63: 
 64:             $this->setTable($table);
 65:             $this->_mysql = $mysql;
 66:         }
 67: 
 68:         public function &columns()
 69:         {
 70:             $args = func_get_args();
 71: 
 72:             if(count($args) == 1)
 73:             {
 74:                 $args = $args[0];
 75:             }
 76: 
 77:             $this->_query['columns'] = $args;
 78: 
 79:             return $this;
 80:         }
 81: 
 82:         public function &db($db)
 83:         {
 84:             $this->_query['db'] = $db;
 85: 
 86:             return $this;
 87:         }
 88: 
 89:         public function &group()
 90:         {
 91:             $args = func_get_args();
 92: 
 93:             if(count($args) == 1)
 94:             {
 95:                 $args = $args[0];
 96:             }
 97: 
 98:             $this->_query['groupBy'] = $args;
 99: 
100:             return $this;
101:         }
102: 
103:         public function &sort()
104:         {
105:             $args = func_get_args();
106: 
107:             if(count($args) == 1)
108:             {
109:                 $args = $args[0];
110:             }
111: 
112:             $this->_query['sort'] = $args;
113: 
114:             return $this;
115:         }
116: 
117:         public function &insertIgnore($data)
118:         {
119:             $this->_query['type'] = "INSERT IGNORE";
120:             $this->_query['data'] = $data;
121: 
122:             return $this;
123:         }
124: 
125:         public function &insertDelayed($data)
126:         {
127:             $this->_query['type'] = "INSERT DELAYED";
128:             $this->_query['data'] = $data;
129: 
130:             return $this;
131:         }
132: 
133:         public function &delete()
134:         {
135:             $this->_query['type'] = "DELETE FROM";
136: 
137:             $args = func_get_args();
138:             if(count($args)==1) $args = $args[0];
139: 
140:             $this->_query['where'][]    = $args;
141:             
142:             return $this;
143:         }
144: 
145:         public function &insert($data)
146:         {
147:             $this->_query['type'] = "INSERT";
148:             $this->_query['data'] = $data;
149: 
150:             return $this;
151:         }
152: 
153:         public function &onDuplicate($data)
154:         {
155:             $this->_query['onDuplicateData'] = $data;
156: 
157:             return $this;
158:         }
159: 
160:         public function &update($data)
161:         {
162:             $this->_query['type'] = "UPDATE";
163:             $this->_query['data'] = $data;
164: 
165:             return $this;
166:         }
167: 
168:         public function run()
169:         {
170:             if($this->_result) return $this->_result;
171: 
172:             return $this->_result = $this->_mysql->query($this->getQuery());
173:         }
174: 
175:         public function get($id,$column='id')
176:         {
177:             $this->_query['type']       = "SELECT";
178:             $this->_query['where'][]    = array($column,$id);
179: 
180:             return $this->run();
181:         }
182: 
183:         public function _escape($value)
184:         {
185:             if($value instanceof \Simpletools\Db\Mysql\SQL)
186:             {
187:                 return (string) $value;
188:             }
189:             else
190:             {
191:                 return '"'.$this->_mysql->escape($value).'"';
192:             }   
193:         }
194: 
195:         private function _prepareQuery($query, array $args)
196:         {
197:             foreach($args as $arg)
198:             {
199:                 if(is_string($arg))
200:                 {
201:                     if(strpos($arg,'?') !== false)
202:                     {
203:                         $arg = str_replace('?','<--SimpleMySQL-QuestionMark-->',$arg);
204:                     }
205:                     
206:                     $arg = $this->_escape($arg);
207:                 }
208:                 elseif($arg instanceof \Simpletools\Db\Mysql\SQL)
209:                 {
210:                     $arg = (string) $arg;
211:                 }
212:                 
213:                 if($arg === null)
214:                 {
215:                     $arg = 'NULL';
216:                 }
217:                 
218:                 $query = $this->replace_first('?', $arg, $query);
219:             }
220:             
221:             if(strpos($query,'<--SimpleMySQL-QuestionMark-->') !== false)
222:             {
223:                 $query = str_replace('<--SimpleMySQL-QuestionMark-->','?',$query);
224:             }
225: 
226:             return $query;
227:         }
228:         
229:         public function replace_first($needle , $replace , $haystack)
230:         {           
231:             $pos = strpos($haystack, $needle);
232:                 
233:             if ($pos === false) 
234:             {
235:                 // Nothing found
236:                 return $haystack;
237:             }
238:                 
239:             return substr_replace($haystack, $replace, $pos, strlen($needle));
240:         }
241: 
242:         public function getQuery()
243:         {
244:             if(!isset($this->_query['type']))
245:                 $this->_query['type']       = "SELECT";
246: 
247:             if(!isset($this->_query['columns']))
248:                 $this->_query['columns']        = "*";
249: 
250:             $query      = array();
251:             $query[]    = $this->_query['type'];
252: 
253:             if($this->_query['type']=='SELECT')
254:             {
255:                 $query[] = is_array($this->_query['columns']) ? implode(', ',$this->_query['columns']) : $this->_query['columns'];
256:                 $query[] = 'FROM';
257:             }
258:             elseif($this->_query['type']=='INSERT' OR $this->_query['type']=='INSERT IGNORE')
259:             {
260:                 $query[] = 'INTO';
261:             }
262: 
263:             if(isset($this->_query['db']))
264:             {
265:                 $query[] = $this->_query['db'].'.'.$this->_query['table'];
266:             }
267:             else
268:             {
269:                 $query[] = $this->_query['table'];
270:             }
271: 
272:             if(
273:                 $this->_query['type']=='INSERT' OR
274:                 $this->_query['type']=='UPDATE' OR
275:                 $this->_query['type']=='INSERT IGNORE' OR
276:                 $this->_query['type']=='INSERT DELAYED'
277:             )
278:             {
279:                 $query[] = 'SET';
280: 
281:                 $set = array();
282: 
283:                 foreach($this->_query['data'] as $key => $value)
284:                 {
285:                     if(is_null($value))
286:                     {
287:                         $set[] = $key.' = NULL';
288:                     }
289:                     else
290:                     {
291:                         $set[] = $key.' = '.$this->_escape($value);
292:                     }
293:                 }
294: 
295:                 $query[] = implode(', ',$set);
296:             }
297: 
298:             if(isset($this->_query['onDuplicateData']))
299:             {
300:                 $query[] = 'ON DUPLICATE KEY UPDATE';
301: 
302:                 $set = array();
303: 
304:                 foreach($this->_query['onDuplicateData'] as $key => $value)
305:                 {
306:                     $set[] = $key.' = '.$this->_escape($value);
307:                 }
308: 
309:                 $query[] = implode(', ',$set);
310:             }
311: 
312:             if(isset($this->_query['where']))
313:             {
314:                 $query['WHERE'] = 'WHERE';
315: 
316:                 if(is_array($this->_query['where']))
317:                 {
318:                     foreach($this->_query['where'] as $operands)
319:                     {
320:                         if(!isset($operands[2]))
321:                             $query[] = $operands[0]." = ".$this->_escape($operands[1]);
322:                         else
323:                             $query[] = $operands[0]." ".$operands[1]." ".$this->_escape($operands[2]);
324:                     }
325:                 }
326:                 else
327:                 {
328:                     $query[] = 'id = '.$this->_escape($this->_query['where']);
329:                 }
330:             }
331: 
332:             if(isset($this->_query['whereSql']))
333:             {
334:                 if(!isset($query['WHERE'])) $query['WHERE'] = 'WHERE';
335: 
336:                 if($this->_query['whereSql']['vars'])
337:                 {
338:                     $query[] = $this->_prepareQuery($this->_query['whereSql']['statement'],$this->_query['whereSql']['vars']);
339:                 }
340:                 else
341:                 {
342:                     $query[] = $this->_query['whereSql']['statement'];
343:                 }
344:             }
345: 
346:             if(isset($this->_query['groupBy']))
347:             {
348:                 $query[] = 'GROUP BY';
349: 
350:                 if(!is_array($this->_query['groupBy']))
351:                 {
352:                     $query[] = $this->_query['groupBy'];
353:                 }
354:                 else
355:                 {
356:                     $groupBy = array();
357: 
358:                     foreach($this->_query['groupBy'] as $column)
359:                     {
360:                         $groupBy[] = $column;
361:                     }
362: 
363:                     $query[] = implode(', ',$groupBy);
364:                 }
365:             }
366: 
367:             if(isset($this->_query['sort']))
368:             {
369:                 $query[] = 'ORDER BY';
370: 
371:                 if(!is_array($this->_query['sort']))
372:                 {
373:                     $query[] = $this->_query['sort'];
374:                 }
375:                 else
376:                 {
377:                     $sort = array();
378: 
379:                     foreach($this->_query['sort'] as $column)
380:                     {
381:                         $sort[] = $column;
382:                     }
383: 
384:                     $query[] = implode(', ',$sort);
385:                 }
386:             }
387: 
388:             if(isset($this->_query['offset']))
389:             {
390:                 $query[] = 'OFFSET '.$this->_query['offset'];
391:             }
392: 
393:             if(isset($this->_query['limit']))
394:             {
395:                 $query[] = 'LIMIT '.$this->_query['limit'];
396:             }
397: 
398:             $this->_query = array();
399:             return implode(' ',$query);
400:         }
401: 
402:         public function &whereSql($statement,$vars=null)
403:         {
404:             $this->_query['whereSql'] = array('statement'=>$statement,'vars'=>$vars);
405: 
406:             return $this;
407:         }
408: 
409:         public function &select($columns)
410:         {
411:             $this->_query['type']       = "SELECT";
412:             $this->_query['columns']    = $columns;
413: 
414:             return $this;
415:         }
416: 
417:         public function &offset($offset)
418:         {
419:             $this->_query['offset']     = $offset;
420: 
421:             return $this;
422:         }
423: 
424:         public function &limit($limit)
425:         {
426:             $this->_query['limit']      = $limit;
427: 
428:             return $this;
429:         }
430: 
431:         public function &find()
432:         {
433:             $args = func_get_args();
434:             if(count($args)==1) $args = $args[0];
435: 
436:             $this->_query['where'][]    = $args;
437: 
438:             return $this;
439:         }
440: 
441:         public function &where()
442:         {
443:             $args = func_get_args();
444:             if(count($args)==1) $args = $args[0];
445: 
446:             $this->_query['where'][]    = $args;
447: 
448:             return $this;
449:         }
450: 
451:         public function &alternatively()
452:         {
453:             $args = func_get_args();
454:             $args[0] = 'OR '.$args[0];
455:             
456:             $this->_query['where'][]    = $args;
457: 
458:             return $this;
459:         }
460: 
461:         public function &also()
462:         {
463:             $args = func_get_args();
464:             $args[0] = 'AND '.$args[0];
465:             
466:             $this->_query['where'][]    = $args;
467: 
468:             return $this;
469:         }
470: 
471:         public function &setTable($table)
472:         {
473:             $this->_query['table']      = $table;
474: 
475:             return $this;
476:         }
477: 
478:         /*
479:         * AUTO RUNNNERS
480:         */
481: 
482:         public function __get($name)
483:         {
484:             $this->run();
485:             return $this->_result->{$name};
486:         }
487: 
488:         public function getAffectedRows()
489:         {
490:             $this->run();
491:             return $this->_result->getAffectedRows();
492:         }
493: 
494:         public function getInsertedId()
495:         {
496:             $this->run();
497:             return $this->_result->getInsertedId();
498:         }
499: 
500:         public function isEmpty()
501:         {
502:             $this->run();
503:             return $this->_result->isEmpty();
504:         }
505: 
506:         public function fetch()
507:         {
508:             $this->run();
509:             return $this->_result->fetch();
510:         }
511: 
512:         public function fetchAll()
513:         {
514:             $this->run();
515:             return $this->_result->fetchAll();
516:         }
517: 
518:         public function length()
519:         {
520:             $this->run();
521:             return $this->_result->length();
522:         }
523: 
524:         public function rewind()
525:         {
526:             $this->run();
527:             $this->_result->rewind();
528:         }
529: 
530:         public function current() 
531:         {
532:             return $this->_result->current();
533:         }
534: 
535:         public function key() 
536:         {
537:             return $this->_result->key();
538:         }
539: 
540:         public function next() 
541:         {
542:             return $this->_result->next();
543:         }
544: 
545:         public function valid() 
546:         {
547:             return $this->_result->valid();
548:         }
549: 
550:     }
551: 
552:     /*
553:     * Helper for raw queries, dependent on QueryBuilder
554:     */
555:     class Sql
556:     {
557:         protected $_statement = '';
558: 
559:         public function __construct($statement)
560:         {
561:             $this->_statement = $statement;
562:         }
563: 
564:         public function __toString()
565:         {
566:             return $this->_statement;
567:         }
568:     }
569:         
570: ?>