1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5use dokuwiki\plugin\sqlite\SQLiteDB;
6use dokuwiki\Utf8\PhpString;
7
8/**
9 * Class SchemaBuilder
10 *
11 * This class builds and updates the schema definitions for our tables. This includes CREATEing and ALTERing
12 * the actual data tables as well as updating the meta information in our meta data tables.
13 *
14 * To use, simply instantiate a new object of the Builder and run the build() method on it.
15 *
16 * Note: even though data tables use a data_ prefix in the database, this prefix is internal only and should
17 *       never be passed as $table anywhere!
18 *
19 * @package dokuwiki\plugin\struct\meta
20 */
21class SchemaBuilder
22{
23    /**
24     * @var array The posted new data for the schema
25     * @see Schema::AdminEditor()
26     */
27    protected $data = [];
28
29    protected $user;
30
31    /**
32     * @var string The table name associated with the schema
33     */
34    protected $table = '';
35
36    /**
37     * @var Schema the previously valid schema for this table
38     */
39    protected $oldschema;
40
41    /** @var int the ID of the newly created schema */
42    protected $newschemaid = 0;
43
44    /** @var \helper_plugin_struct_db */
45    protected $helper;
46
47    /** @var SQLiteDB|null */
48    protected $sqlite;
49
50    /** @var int the time for which this schema should be created - default to time() can be overriden for tests */
51    protected $time = 0;
52
53    /**
54     * SchemaBuilder constructor.
55     *
56     * @param string $table The table's name
57     * @param array $data The defining of the table (basically what get's posted in the schema editor form)
58     * @see Schema::AdminEditor()
59     */
60    public function __construct($table, $data)
61    {
62        global $INPUT;
63
64        $this->table = $table;
65        $this->data = $data;
66        $this->oldschema = new Schema($table, 0);
67
68        $this->helper = plugin_load('helper', 'struct_db');
69        $this->sqlite = $this->helper->getDB();
70        $this->user = $_SERVER['REMOTE_USER'] ?? '';
71    }
72
73    /**
74     * Create the new schema
75     *
76     * @param int $time when to create this schema 0 for now
77     * @return int the new schema id on success
78     */
79    public function build($time = 0)
80    {
81        $this->time = $time;
82        $this->fixLabelUniqueness();
83
84        $this->sqlite->query('BEGIN TRANSACTION');
85        $ok = true;
86        // create the data table if new schema
87        if (!$this->oldschema->getId()) {
88            $ok = $this->newDataTable();
89        }
90
91        // create a new schema
92        $ok = $ok && $this->newSchema();
93
94        // update column info
95        $ok = $ok && $this->updateColumns();
96        $ok = $ok && $this->addColumns();
97
98        if (!$ok) {
99            $this->sqlite->query('ROLLBACK TRANSACTION');
100            return false;
101        }
102        $this->sqlite->query('COMMIT TRANSACTION');
103
104        return (int)$this->newschemaid;
105    }
106
107    /**
108     * Makes sure all labels in the schema to save are unique
109     */
110    protected function fixLabelUniqueness()
111    {
112        $labels = [];
113
114        if (isset($this->data['cols'])) foreach ($this->data['cols'] as $idx => $column) {
115            $this->data['cols'][$idx]['label'] = $this->fixLabel($column['label'], $labels);
116        }
117
118        if (isset($this->data['new'])) foreach ($this->data['new'] as $idx => $column) {
119            $this->data['new'][$idx]['label'] = $this->fixLabel($column['label'], $labels);
120        }
121    }
122
123    /**
124     * Creates a unique label from the given one
125     *
126     * @param string $wantedlabel
127     * @param array $labels list of already assigned labels (will be filled)
128     * @return string
129     */
130    protected function fixLabel($wantedlabel, &$labels)
131    {
132        $wantedlabel = trim($wantedlabel);
133        $fixedlabel = $wantedlabel;
134        $idx = 1;
135        while (isset($labels[PhpString::strtolower($fixedlabel)])) {
136            $fixedlabel = $wantedlabel . $idx++;
137        }
138        // did we actually do a rename? apply it.
139        if ($fixedlabel !== $wantedlabel) {
140            msg(sprintf($this->helper->getLang('duplicate_label'), $wantedlabel, $fixedlabel), -1);
141            $this->data['cols']['label'] = $fixedlabel;
142        }
143        $labels[PhpString::strtolower($fixedlabel)] = 1;
144        return $fixedlabel;
145    }
146
147    /**
148     * Creates a new schema
149     */
150    protected function newSchema()
151    {
152        if (!$this->time) $this->time = time();
153
154        $config = $this->data['config'] ?? '{}';
155
156        /** @noinspection SqlResolve */
157        $sql = "INSERT INTO schemas (tbl, ts, user, config) VALUES (?, ?, ?, ?)";
158        $this->sqlite->query($sql, [$this->table, $this->time, $this->user, $config]);
159        $this->newschemaid = $this->sqlite->queryValue('SELECT last_insert_rowid()');
160
161        if (!$this->newschemaid) return false;
162        return true;
163    }
164
165    /**
166     * Updates all the existing column infos and adds them to the new schema
167     */
168    protected function updateColumns()
169    {
170        foreach ($this->oldschema->getColumns() as $column) {
171            $oldEntry = $column->getType()->getAsEntry();
172            $oldTid = $column->getTid();
173            $newEntry = $oldEntry;
174            $newTid = $oldTid;
175            $sort = $column->getSort();
176            if (isset($this->data['cols'][$column->getColref()])) {
177                // todo I'm not too happy with this hardcoded here -
178                // we should probably have a list of fields at one place
179                $newEntry['config'] = $this->data['cols'][$column->getColref()]['config'];
180                $newEntry['label'] = $this->data['cols'][$column->getColref()]['label'];
181                $newEntry['ismulti'] = $this->data['cols'][$column->getColref()]['ismulti'] ?? 0;
182                $newEntry['class'] = $this->data['cols'][$column->getColref()]['class'];
183                $sort = $this->data['cols'][$column->getColref()]['sort'];
184                $enabled = (bool)($this->data['cols'][$column->getColref()]['isenabled'] ?? 0);
185
186                // when the type definition has changed, we create a new one
187                if (array_diff_assoc($oldEntry, $newEntry)) {
188                    $ok = $this->sqlite->saveRecord('types', $newEntry);
189                    if (!$ok) return false;
190                    $newTid = $this->sqlite->queryValue('SELECT last_insert_rowid()');
191                    if (!$newTid) return false;
192                    if ($oldEntry['ismulti'] == false && $newEntry['ismulti'] == '1') {
193                        $this->migrateSingleToMulti($this->oldschema->getTable(), $column->getColref());
194                    }
195                }
196            } else {
197                $enabled = false; // no longer there for some reason
198            }
199
200            // add this type to the schema columns
201            $schemaEntry = [
202                'sid' => $this->newschemaid,
203                'colref' => $column->getColref(),
204                'enabled' => $enabled,
205                'tid' => $newTid,
206                'sort' => $sort
207            ];
208            $ok = $this->sqlite->saveRecord('schema_cols', $schemaEntry);
209            if (!$ok) return false;
210        }
211        return true;
212    }
213
214    /**
215     * Write the latest value from an entry in a data_ table to the corresponding multi_table
216     *
217     * @param string $table
218     * @param int $colref
219     */
220    protected function migrateSingleToMulti($table, $colref)
221    {
222        /** @noinspection SqlResolve */
223        $sqlSelect = "SELECT pid, rev, published, col$colref AS value FROM data_$table WHERE latest = 1";
224        $valueSet = $this->sqlite->queryAll($sqlSelect);
225        $valueString = [];
226        $arguments = [];
227        foreach ($valueSet as $values) {
228            if (blank($values['value']) || trim($values['value']) == '') {
229                continue;
230            }
231            $valueString[] = "(?, ?, ?, ?, ?, ?)";
232            $arguments = [
233                ...$arguments,
234                $colref,
235                $values['pid'],
236                $values['rev'],
237                $values['published'],
238                1,
239                $values['value']
240            ];
241        }
242        if ($valueString === []) {
243            return;
244        }
245        $valueString = implode(',', $valueString);
246        /** @noinspection SqlResolve */
247        $sqlInsert = "INSERT OR REPLACE INTO multi_$table (colref, pid, rev, published, row, value) VALUES $valueString"; // phpcs:ignore
248        $this->sqlite->query($sqlInsert, $arguments);
249    }
250
251    /**
252     * Adds new columns to the new schema
253     *
254     * @return bool
255     */
256    protected function addColumns()
257    {
258        if (!isset($this->data['new'])) return true;
259
260        $colref = count($this->oldschema->getColumns()) + 1;
261
262        foreach ($this->data['new'] as $column) {
263            if (!$column['isenabled']) continue; // we do not add a disabled column
264
265            // todo this duplicates the hardcoding as in  the function above
266            $newEntry = [];
267            $newEntry['config'] = $column['config'] ?? '{}';
268            $newEntry['label'] = $column['label'];
269            $newEntry['ismulti'] = $column['ismulti'] ?? 0;
270            $newEntry['class'] = $column['class'];
271            $sort = $column['sort'];
272
273
274            // only save if the column got a name
275            if (!$newEntry['label']) continue;
276
277            // add new column to the data table
278            if (!$this->addDataTableColumn($colref)) {
279                return false;
280            }
281
282            // save the type
283            $ok = $this->sqlite->saveRecord('types', $newEntry);
284            if (!$ok) return false;
285            $newTid = $this->sqlite->queryValue('SELECT last_insert_rowid()');
286
287            if (!$newTid) return false;
288
289
290            // add this type to the schema columns
291            $schemaEntry = [
292                'sid' => $this->newschemaid,
293                'colref' => $colref,
294                'enabled' => true,
295                'tid' => $newTid,
296                'sort' => $sort
297            ];
298            $ok = $this->sqlite->saveRecord('schema_cols', $schemaEntry);
299            if (!$ok) return false;
300            $colref++;
301        }
302
303        return true;
304    }
305
306    /**
307     * Create a completely new data table with no columns yet also create the appropriate
308     * multi value table for the schema
309     *
310     * @return bool
311     * @todo how do we want to handle indexes?
312     */
313    protected function newDataTable()
314    {
315        $ok = true;
316
317        $tbl = 'data_' . $this->table;
318        $sql = "CREATE TABLE $tbl (
319                    pid TEXT DEFAULT '',
320                    rid INTEGER,
321                    rev INTEGER,
322                    latest BOOLEAN NOT NULL DEFAULT 0,
323                    published BOOLEAN DEFAULT NULL,
324                    PRIMARY KEY(pid, rid, rev)
325                )";
326        $ok = $ok && (bool)$this->sqlite->query($sql);
327
328        $tbl = 'multi_' . $this->table;
329        $sql = "CREATE TABLE $tbl (
330                    colref INTEGER NOT NULL,
331                    pid TEXT DEFAULT '',
332                    rid INTEGER,
333                    rev INTEGER,
334                    latest INTEGER NOT NULL DEFAULT 0,
335                    published BOOLEAN DEFAULT NULL,
336                    row INTEGER NOT NULL,
337                    value,
338                    PRIMARY KEY(colref, pid, rid, rev, row)
339                );";
340        $ok = $ok && (bool)$this->sqlite->query($sql);
341
342        return $ok;
343    }
344
345    /**
346     * Add an additional column to the existing data table
347     *
348     * @param int $index the new column index to add
349     * @return bool
350     */
351    protected function addDataTableColumn($index)
352    {
353        $tbl = 'data_' . $this->table;
354        $sql = " ALTER TABLE $tbl ADD COLUMN col$index DEFAULT ''";
355        if (!$this->sqlite->query($sql)) {
356            return false;
357        }
358        return true;
359    }
360
361    /**
362     * @param string $user
363     * @return SchemaBuilder
364     */
365    public function setUser($user)
366    {
367        $this->user = $user;
368        return $this;
369    }
370}
371