1<?php 2 3namespace dokuwiki\plugin\struct\meta; 4 5/** 6 * Class QueryBuilder 7 * @package dokuwiki\plugin\struct\meta 8 */ 9class QueryBuilder { 10 11 /** @var array placeholder -> values */ 12 protected $values = array(); 13 /** @var array (alias -> statement */ 14 protected $select = array(); 15 /** @var array (alias -> statement) */ 16 protected $from = array(); 17 /** @var QueryBuilderWhere */ 18 protected $where; 19 20 /** 21 * QueryBuilder constructor. 22 */ 23 public function __construct() { 24 $this->where = new QueryBuilderWhere(); 25 } 26 27 /** 28 * Adds a column to select 29 * 30 * If the alias already exists, the current statement for that alias will be overwritten. 31 * 32 * @param string $tablealias The table to select from 33 * @param string $column The column to select 34 * @param string $alias Under whichname to slect the column. blank for column name 35 */ 36 public function addSelectColumn($tablealias, $column, $alias = '') { 37 if($alias === '') $alias = $column; 38 if(!isset($this->from[$tablealias])) throw new StructException('Table Alias does not exist'); 39 $this->select[$alias] = "$tablealias.$column AS $alias"; 40 } 41 42 /** 43 * Add a new select statement (the column part of it) 44 * 45 * Basically the same as @see addSelectColumn but accepts any statement. This is useful to 46 * select things like fixed strings or more complex function calls, but the correctness will not 47 * be checked. 48 * 49 * If the alias already exists, the current statement for that alias will be overwritten. 50 * 51 * @param string $statement 52 * @param string $alias 53 */ 54 public function addSelectStatement($statement, $alias) { 55 $this->select[$alias] = "$statement AS $alias"; 56 } 57 58 /** 59 * Adds the the table to the FROM statement part 60 * 61 * @param string $table the table to add 62 * @param string $alias alias for the table, blank for table name 63 */ 64 public function addTable($table, $alias = '') { 65 if($alias === '') $alias = $table; 66 if(isset($this->from[$alias])) throw new StructException('Table Alias exists'); 67 $this->from[$alias] = "$table AS $alias"; 68 } 69 70 /** 71 * Adds a LEFT JOIN clause to the FROM statement part, sorted at the correct spot 72 * 73 * @param string $leftalias the alias of the left table you're joining on, has to exist already 74 * @param string $righttable the right table to be joined 75 * @param string $rightalias an alias for the right table, blank for table name 76 * @param string $onclause the ON clause condition the join is based on 77 */ 78 public function addLeftJoin($leftalias, $righttable, $rightalias, $onclause) { 79 if($rightalias === '') $rightalias = $righttable; 80 if(!isset($this->from[$leftalias])) throw new StructException('Table Alias does not exist'); 81 if(isset($this->from[$rightalias])) throw new StructException('Table Alias already exists'); 82 83 $pos = array_search($leftalias, array_keys($this->from)); 84 $statement = "LEFT OUTER JOIN $righttable AS $rightalias ON $onclause"; 85 $this->from = $this->array_insert($this->from, array($rightalias => $statement), $pos+1); 86 } 87 88 /** 89 * Returns the current WHERE filters and allows to set new ones 90 * 91 * @return QueryBuilderWhere 92 */ 93 public function getFilters() { 94 return $this->where; 95 } 96 97 /** 98 * Adds a value to the statement 99 * 100 * This function returns the name of the placeholder you have to use in your statement 101 * 102 * @param mixed $value 103 * @return string 104 */ 105 public function addValue($value) { 106 static $count = 0; 107 $count++; 108 109 $placeholder = ":!!val$count!!:"; // sqlite plugin does not support named parameters, yet so we have simulate it 110 $this->values[$placeholder] = $value; 111 return $placeholder; 112 } 113 114 /** 115 * Creates a new table alias that has not been used before 116 * 117 * @return string 118 */ 119 public function generateTableAlias() { 120 static $count = 0; 121 $count++; 122 return "T$count"; 123 } 124 125 /** 126 * Returns the complete SQL statement and the values to apply 127 * 128 * @return array 129 */ 130 public function getSQL() { 131 $sql = 132 'SELECT ' . join(",\n", $this->select) . "\n" . 133 ' FROM ' . join(",\n", $this->from) . "\n" . 134 ' WHERE ' . $this->where->toSQL() . "\n"; 135 136 return $this->fixPlaceholders($sql); 137 } 138 139 /** 140 * Replaces the named placeholders with ? placeholders 141 * 142 * Until the sqlite plugin can use named placeholder properly 143 * 144 * @param string $sql 145 * @return array 146 */ 147 protected function fixPlaceholders($sql) { 148 $vals = array(); 149 150 while(preg_match('/(:!!val\d+!!:)/', $sql, $m)) { 151 $pl = $m[1]; 152 $sql = preg_replace("/$pl/", '?', 1); 153 $vals[] = $this->values[$pl]; 154 } 155 156 return array($sql, $vals); 157 } 158 159 /** 160 * Insert an array into another array at a given position in an associative array 161 * 162 * @param array $array The initial array 163 * @param array $pairs The array to insert 164 * @param string $key_pos The position at which to insert 165 * @link https://gist.github.com/scribu/588429 simplified 166 * @return array 167 */ 168 protected function array_insert($array, $pairs, $key_pos) { 169 $result = array_slice($array, 0, $key_pos); 170 $result = array_merge($result, $pairs); 171 $result = array_merge($result, array_slice($array, $key_pos)); 172 return $result; 173 } 174} 175 176