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