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 array (alias -> "table"|"join") keeps how tables were added, as table or join */ 18 protected $type = array(); 19 /** @var QueryBuilderWhere */ 20 protected $where; 21 /** @var string[] */ 22 protected $orderby = array(); 23 /** @var string[] */ 24 protected $groupby = array(); 25 26 /** 27 * QueryBuilder constructor. 28 */ 29 public function __construct() { 30 $this->where = new QueryBuilderWhere(); 31 } 32 33 /** 34 * Adds a column to select 35 * 36 * If the alias already exists, the current statement for that alias will be overwritten. 37 * 38 * @param string $tablealias The table to select from 39 * @param string $column The column to select 40 * @param string $alias Under whichname to slect the column. blank for column name 41 */ 42 public function addSelectColumn($tablealias, $column, $alias = '') { 43 if($alias === '') $alias = $column; 44 if(!isset($this->from[$tablealias])) { 45 throw new StructException('Table Alias does not exist'); 46 } 47 $this->select[$alias] = "$tablealias.$column"; 48 } 49 50 /** 51 * Add a new column selection statement 52 * 53 * Basically the same as @see addSelectColumn() but accepts any statement. This is useful to 54 * select things like fixed strings or more complex function calls, but the correctness will not 55 * be checked. 56 * 57 * If the alias already exists, the current statement for that alias will be overwritten. 58 * 59 * @param string $statement 60 * @param string $alias 61 */ 62 public function addSelectStatement($statement, $alias) { 63 $this->select[$alias] = $statement; 64 } 65 66 /** 67 * Return an already defined column selection statement based on the alias 68 * 69 * @param string $alias 70 * @return string 71 * @throws StructException when the alias does not exist 72 */ 73 public function getSelectStatement($alias) { 74 if(!isset($this->select[$alias])) { 75 throw new StructException('No such select alias'); 76 } 77 78 return $this->select[$alias]; 79 } 80 81 /** 82 * Adds the the table to the FROM statement part 83 * 84 * @param string $table the table to add 85 * @param string $alias alias for the table, blank for table name 86 */ 87 public function addTable($table, $alias = '') { 88 if($alias === '') $alias = $table; 89 if(isset($this->from[$alias])) { 90 throw new StructException('Table Alias exists'); 91 } 92 $this->from[$alias] = "$table AS $alias"; 93 $this->type[$alias] = 'table'; 94 } 95 96 /** 97 * Adds a LEFT JOIN clause to the FROM statement part, sorted at the correct spot 98 * 99 * @param string $leftalias the alias of the left table you're joining on, has to exist already 100 * @param string $righttable the right table to be joined 101 * @param string $rightalias an alias for the right table, blank for table name 102 * @param string $onclause the ON clause condition the join is based on 103 */ 104 public function addLeftJoin($leftalias, $righttable, $rightalias, $onclause) { 105 if($rightalias === '') $rightalias = $righttable; 106 if(!isset($this->from[$leftalias])) { 107 throw new StructException('Table Alias does not exist'); 108 } 109 if(isset($this->from[$rightalias])) { 110 throw new StructException('Table Alias already exists'); 111 } 112 113 $pos = array_search($leftalias, array_keys($this->from)); 114 $statement = "LEFT OUTER JOIN $righttable AS $rightalias ON $onclause"; 115 $this->from = $this->array_insert($this->from, array($rightalias => $statement), $pos + 1); 116 $this->type[$rightalias] = 'join'; 117 } 118 119 /** 120 * Returns the current WHERE filters and allows to set new ones 121 * 122 * @return QueryBuilderWhere 123 */ 124 public function filters() { 125 return $this->where; 126 } 127 128 /** 129 * Add an ORDER BY clause 130 * 131 * @param string $sort a single sorting condition 132 */ 133 public function addOrderBy($sort) { 134 $this->orderby[] = $sort; 135 } 136 137 /** 138 * Add an GROUP BY clause 139 * 140 * @param string $tablealias 141 * @param string $column 142 */ 143 public function addGroupByColumn($tablealias, $column) { 144 if(!isset($this->from[$tablealias])) { 145 throw new StructException('Table Alias does not exist'); 146 } 147 $this->groupby[] = "$tablealias.$column"; 148 } 149 150 /** 151 * Add an GROUP BY clause 152 * 153 * Like @see addGroupByColumn but accepts an arbitrary statement 154 * 155 * @param string $statement a single grouping clause 156 */ 157 public function addGroupByStatement($statement) { 158 $this->groupby[] = $statement; 159 } 160 161 /** 162 * Adds a value to the statement 163 * 164 * This function returns the name of the placeholder you have to use in your statement, so whenever 165 * you need to use a user value in a statement, call this first, then add the statement through the 166 * other functions using the returned placeholder. 167 * 168 * @param mixed $value 169 * @return string 170 */ 171 public function addValue($value) { 172 static $count = 0; 173 $count++; 174 175 $placeholder = ":!!val$count!!:"; // sqlite plugin does not support named parameters, yet so we have simulate it 176 $this->values[$placeholder] = $value; 177 return $placeholder; 178 } 179 180 /** 181 * Creates a new table alias that has not been used before 182 * 183 * @return string 184 */ 185 public function generateTableAlias() { 186 static $count = 0; 187 $count++; 188 return "T$count"; 189 } 190 191 /** 192 * Returns the complete SQL statement and the values to apply 193 * 194 * @return array ($sql, $vals) 195 */ 196 public function getSQL() { 197 // FROM needs commas only for tables, not joins 198 $from = ''; 199 foreach($this->from as $alias => $statement) { 200 if($this->type[$alias] == 'table' && $from) { 201 $from .= ",\n"; 202 } else { 203 $from .= "\n"; 204 } 205 206 $from .= $statement; 207 } 208 209 // prepare aliases for the select columns 210 $selects = array(); 211 foreach($this->select as $alias => $select) { 212 $selects[] = "$select AS $alias"; 213 } 214 215 $sql = 216 ' SELECT ' . join(",\n", $selects) . "\n" . 217 ' FROM ' . $from . "\n" . 218 ' WHERE ' . $this->where->toSQL() . "\n"; 219 220 if($this->groupby) { 221 $sql .= 222 'GROUP BY ' . join(",\n", $this->groupby) . "\n"; 223 } 224 225 if($this->orderby) { 226 $sql .= 227 'ORDER BY ' . join(",\n", $this->orderby) . "\n"; 228 } 229 230 return $this->fixPlaceholders($sql); 231 } 232 233 /** 234 * Replaces the named placeholders with ? placeholders 235 * 236 * Until the sqlite plugin can use named placeholder properly 237 * 238 * @param string $sql 239 * @return array 240 */ 241 protected function fixPlaceholders($sql) { 242 $vals = array(); 243 244 while(preg_match('/(:!!val\d+!!:)/', $sql, $m)) { 245 $pl = $m[1]; 246 247 if(!array_key_exists($pl, $this->values)) { 248 throw new StructException('Placeholder not found'); 249 } 250 251 $sql = preg_replace("/$pl/", '?', $sql, 1); 252 $vals[] = $this->values[$pl]; 253 } 254 255 return array($sql, $vals); 256 } 257 258 /** 259 * Insert an array into another array at a given position in an associative array 260 * 261 * @param array $array The initial array 262 * @param array $pairs The array to insert 263 * @param string $key_pos The position at which to insert 264 * @link https://gist.github.com/scribu/588429 simplified 265 * @return array 266 */ 267 protected function array_insert($array, $pairs, $key_pos) { 268 $result = array_slice($array, 0, $key_pos); 269 $result = array_merge($result, $pairs); 270 $result = array_merge($result, array_slice($array, $key_pos)); 271 return $result; 272 } 273} 274 275