1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5/**
6 * Creates the SQL query using the QueryBuilder
7 *
8 * @internal This class is used by the Search class and should probably never be used directly
9 */
10class SearchSQLBuilder
11{
12    /** @var QueryBuilder */
13    protected $qb;
14
15    /** @var bool Include latest = 1 in select query */
16    protected $selectLatest = true;
17
18    /**
19     * SearchSQLBuilder constructor.
20     */
21    public function __construct()
22    {
23        $this->qb = new QueryBuilder();
24    }
25
26    /**
27     * Add the schemas to the query
28     *
29     * @param Schema[] $schemas Schema names to query
30     * @param bool $selectMeta Select meta columns (pid, rid, rev, assigned)
31     */
32    public function addSchemas($schemas, $selectMeta = true)
33    {
34        // basic tables
35        $first_table = '';
36        foreach ($schemas as $schema) {
37            $datatable = 'data_' . $schema->getTable();
38            if ($first_table) {
39                // follow up tables
40                $this->qb->addLeftJoin($first_table, $datatable, $datatable, "$first_table.pid = $datatable.pid");
41            } else {
42                // first table
43                $this->qb->addTable($datatable);
44
45                // add conditional page clauses if pid has a value
46                $subAnd = $this->qb->filters()->whereSubAnd();
47                $subAnd->whereAnd("$datatable.pid = ''");
48                $subOr = $subAnd->whereSubOr();
49                $subOr->whereAnd("GETACCESSLEVEL($datatable.pid) > 0");
50                $subOr->whereAnd("PAGEEXISTS($datatable.pid) = 1");
51                // make sure to check assignment for page data only
52                $subOr->whereAnd("($datatable.rid != 0 OR (ASSIGNED = 1 OR ASSIGNED IS NULL))");
53
54                // add conditional schema assignment check
55                $this->qb->addLeftJoin(
56                    $datatable,
57                    'schema_assignments',
58                    '',
59                    "$datatable.pid != ''
60                    AND $datatable.pid = schema_assignments.pid
61                    AND schema_assignments.tbl = '{$schema->getTable()}'"
62                );
63
64                if ($selectMeta) {
65                    $this->qb->addSelectColumn($datatable, 'rid');
66                    $this->qb->addSelectColumn($datatable, 'pid', 'PID');
67                    $this->qb->addSelectColumn($datatable, 'rev');
68                    $this->qb->addSelectColumn('schema_assignments', 'assigned', 'ASSIGNED');
69                    $this->qb->addGroupByColumn($datatable, 'pid');
70                    $this->qb->addGroupByColumn($datatable, 'rid');
71                }
72
73                $first_table = $datatable;
74            }
75            $this->qb->filters()->whereAnd($this->addPublishClauses($datatable));
76        }
77    }
78
79    /**
80     * Add the columns to select, handling multis
81     *
82     * @param Column[] $columns
83     */
84    public function addColumns($columns)
85    {
86        $sep = Search::CONCAT_SEPARATOR;
87        $n = 0;
88        foreach ($columns as $col) {
89            $CN = 'C' . $n++;
90
91            if ($col->isMulti()) {
92                $datatable = "data_{$col->getTable()}";
93                $multitable = "multi_{$col->getTable()}";
94                $MN = $this->qb->generateTableAlias('M');
95
96                $this->qb->addLeftJoin(
97                    $datatable,
98                    $multitable,
99                    $MN,
100                    "$datatable.pid = $MN.pid AND $datatable.rid = $MN.rid AND
101                     $datatable.rev = $MN.rev AND
102                     $MN.colref = {$col->getColref()}"
103                );
104
105                $col->getType()->select($this->qb, $MN, 'value', $CN);
106                $sel = $this->qb->getSelectStatement($CN);
107                $this->qb->addSelectStatement("GROUP_CONCAT_DISTINCT($sel, '$sep')", $CN);
108            } else {
109                $col->getType()->select($this->qb, 'data_' . $col->getTable(), $col->getColName(), $CN);
110                $this->qb->addGroupByStatement($CN);
111            }
112        }
113    }
114
115    /**
116     * Add the filters to the query
117     *
118     * All given filters are added to their own subclause.
119     *
120     * @param array[] $filters
121     */
122    public function addFilters($filters)
123    {
124        if (!$filters) return; // ignore empty filters
125
126        $subClause = $this->qb->filters()->where('AND');
127
128        foreach ($filters as $filter) {
129            /** @var Column $col */
130            [$col, $value, $comp, $op] = $filter;
131
132            $datatable = "data_{$col->getTable()}";
133            $multitable = "multi_{$col->getTable()}";
134
135            /** @var $col Column */
136            if ($col->isMulti()) {
137                $MN = $this->qb->generateTableAlias('MN');
138
139                $this->qb->addLeftJoin(
140                    $datatable,
141                    $multitable,
142                    $MN,
143                    "$datatable.pid = $MN.pid AND $datatable.rid = $MN.rid AND
144                     $datatable.rev = $MN.rev AND
145                     $MN.colref = {$col->getColref()}"
146                );
147                $coltbl = $MN;
148                $colnam = 'value';
149            } else {
150                $coltbl = $datatable;
151                $colnam = $col->getColName();
152            }
153
154            $col->getType()->filter($subClause, $coltbl, $colnam, $comp, $value, $op); // type based filter
155        }
156    }
157
158    /**
159     * Add the sort by clauses to the query
160     *
161     * We always sort by the single val column which contains a copy of the first value of the multi column
162     *
163     * @param array[] $sorts
164     */
165    public function addSorts($sorts)
166    {
167        foreach ($sorts as $sort) {
168            [$col, $asc, $nc] = $sort;
169            /** @var $col Column */
170            $colname = $col->getColName(false);
171            if ($nc) $colname .= ' COLLATE NOCASE';
172            $col->getType()->sort($this->qb, 'data_' . $col->getTable(), $colname, $asc ? 'ASC' : 'DESC');
173        }
174    }
175
176    /**
177     * @param string $datatable
178     * @return string
179     */
180    public function addPublishClauses($datatable)
181    {
182        $latestClause = "IS_PUBLISHER($datatable.pid)";
183        if ($this->selectLatest) {
184            $latestClause .= " AND $datatable.latest = 1";
185        }
186        $publishedClause = "IS_PUBLISHER($datatable.pid) !=1 AND $datatable.published = 1";
187
188        return "( ($latestClause) OR ($publishedClause) )";
189    }
190
191    /**
192     * Access to the underlying QueryBuilder
193     *
194     * @return QueryBuilder
195     */
196    public function getQueryBuilder()
197    {
198        return $this->qb;
199    }
200
201    /**
202     * Get the SQL query and parameters
203     *
204     * Shortcut for $this->getQueryBuilder()->getSQL()
205     *
206     * @return array ($sql, $params)
207     */
208    public function getSQL()
209    {
210        return $this->qb->getSQL();
211    }
212
213    /**
214     * Allows disabling default 'latest = 1' clause in select statement
215     *
216     * @param bool $selectLatest
217     */
218    public function setSelectLatest(bool $selectLatest)
219    {
220        $this->selectLatest = $selectLatest;
221    }
222}
223