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