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