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($this); 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 * @param string $prefix the prefix for the alias, helps with readability of the SQL 184 * @return string 185 */ 186 public function generateTableAlias($prefix = 'T') { 187 static $count = 0; 188 $count++; 189 return $prefix . $count; 190 } 191 192 /** 193 * Returns the complete SQL statement and the values to apply 194 * 195 * @return array ($sql, $vals) 196 */ 197 public function getSQL() { 198 // FROM needs commas only for tables, not joins 199 $from = ''; 200 foreach($this->from as $alias => $statement) { 201 if($this->type[$alias] == 'table' && $from) { 202 $from .= ",\n"; 203 } else { 204 $from .= "\n"; 205 } 206 207 $from .= $statement; 208 } 209 210 // prepare aliases for the select columns 211 $selects = array(); 212 foreach($this->select as $alias => $select) { 213 $selects[] = "$select AS $alias"; 214 } 215 216 $sql = 217 ' SELECT ' . join(",\n", $selects) . "\n" . 218 ' FROM ' . $from . "\n" . 219 ' WHERE ' . $this->where->toSQL() . "\n"; 220 221 if($this->groupby) { 222 $sql .= 223 'GROUP BY ' . join(",\n", $this->groupby) . "\n"; 224 } 225 226 if($this->orderby) { 227 $sql .= 228 'ORDER BY ' . join(",\n", $this->orderby) . "\n"; 229 } 230 231 return $this->fixPlaceholders($sql); 232 } 233 234 /** 235 * Replaces the named placeholders with ? placeholders 236 * 237 * Until the sqlite plugin can use named placeholder properly 238 * 239 * @param string $sql 240 * @return array 241 */ 242 protected function fixPlaceholders($sql) { 243 $vals = array(); 244 245 while(preg_match('/(:!!val\d+!!:)/', $sql, $m)) { 246 $pl = $m[1]; 247 248 if(!array_key_exists($pl, $this->values)) { 249 throw new StructException('Placeholder not found'); 250 } 251 252 $sql = preg_replace("/$pl/", '?', $sql, 1); 253 $vals[] = $this->values[$pl]; 254 } 255 256 return array($sql, $vals); 257 } 258 259 /** 260 * Insert an array into another array at a given position in an associative array 261 * 262 * @param array $array The initial array 263 * @param array $pairs The array to insert 264 * @param string $key_pos The position at which to insert 265 * @link https://gist.github.com/scribu/588429 simplified 266 * @return array 267 */ 268 protected function array_insert($array, $pairs, $key_pos) { 269 $result = array_slice($array, 0, $key_pos); 270 $result = array_merge($result, $pairs); 271 $result = array_merge($result, array_slice($array, $key_pos)); 272 return $result; 273 } 274} 275 276