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, so whenever 101 * you need to use a user value in a statement, call this first, then add the statement through the 102 * other functions using the returned placeholder. 103 * 104 * @param mixed $value 105 * @return string 106 */ 107 public function addValue($value) { 108 static $count = 0; 109 $count++; 110 111 $placeholder = ":!!val$count!!:"; // sqlite plugin does not support named parameters, yet so we have simulate it 112 $this->values[$placeholder] = $value; 113 return $placeholder; 114 } 115 116 /** 117 * Creates a new table alias that has not been used before 118 * 119 * @return string 120 */ 121 public function generateTableAlias() { 122 static $count = 0; 123 $count++; 124 return "T$count"; 125 } 126 127 /** 128 * Returns the complete SQL statement and the values to apply 129 * 130 * @return array 131 */ 132 public function getSQL() { 133 $sql = 134 'SELECT ' . join(",\n", $this->select) . "\n" . 135 ' FROM ' . join(",\n", $this->from) . "\n" . 136 ' WHERE ' . $this->where->toSQL() . "\n"; 137 138 return $this->fixPlaceholders($sql); 139 } 140 141 /** 142 * Replaces the named placeholders with ? placeholders 143 * 144 * Until the sqlite plugin can use named placeholder properly 145 * 146 * @param string $sql 147 * @return array 148 */ 149 protected function fixPlaceholders($sql) { 150 $vals = array(); 151 152 while(preg_match('/(:!!val\d+!!:)/', $sql, $m)) { 153 $pl = $m[1]; 154 $sql = preg_replace("/$pl/", '?', 1); 155 $vals[] = $this->values[$pl]; 156 } 157 158 return array($sql, $vals); 159 } 160 161 /** 162 * Insert an array into another array at a given position in an associative array 163 * 164 * @param array $array The initial array 165 * @param array $pairs The array to insert 166 * @param string $key_pos The position at which to insert 167 * @link https://gist.github.com/scribu/588429 simplified 168 * @return array 169 */ 170 protected function array_insert($array, $pairs, $key_pos) { 171 $result = array_slice($array, 0, $key_pos); 172 $result = array_merge($result, $pairs); 173 $result = array_merge($result, array_slice($array, $key_pos)); 174 return $result; 175 } 176} 177 178