1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5use dokuwiki\Utf8\PhpString;
6
7/**
8 * Class SchemaBuilder
9 *
10 * This class builds and updates the schema definitions for our tables. This includes CREATEing and ALTERing
11 * the actual data tables as well as updating the meta information in our meta data tables.
12 *
13 * To use, simply instantiate a new object of the Builder and run the build() method on it.
14 *
15 * Note: even though data tables use a data_ prefix in the database, this prefix is internal only and should
16 *       never be passed as $table anywhere!
17 *
18 * @package dokuwiki\plugin\struct\meta
19 */
20class SchemaBuilder
21{
22    /**
23     * @var array The posted new data for the schema
24     * @see Schema::AdminEditor()
25     */
26    protected $data = array();
27
28    protected $user;
29
30    /**
31     * @var string The table name associated with the schema
32     */
33    protected $table = '';
34
35    /**
36     * @var Schema the previously valid schema for this table
37     */
38    protected $oldschema;
39
40    /** @var int the ID of the newly created schema */
41    protected $newschemaid = 0;
42
43    /** @var \helper_plugin_struct_db */
44    protected $helper;
45
46    /** @var \helper_plugin_sqlite|null */
47    protected $sqlite;
48
49    /** @var int the time for which this schema should be created - default to time() can be overriden for tests */
50    protected $time = 0;
51
52    /**
53     * SchemaBuilder constructor.
54     *
55     * @param string $table The table's name
56     * @param array $data The defining of the table (basically what get's posted in the schema editor form)
57     * @see Schema::AdminEditor()
58     */
59    public function __construct($table, $data)
60    {
61        global $INPUT;
62
63        $this->table = $table;
64        $this->data = $data;
65        $this->oldschema = new Schema($table, 0);
66
67        $this->helper = plugin_load('helper', 'struct_db');
68        $this->sqlite = $this->helper->getDB();
69        $this->user = $_SERVER['REMOTE_USER'] ?? '';
70    }
71
72    /**
73     * Create the new schema
74     *
75     * @param int $time when to create this schema 0 for now
76     * @return int the new schema id on success
77     */
78    public function build($time = 0)
79    {
80        $this->time = $time;
81        $this->fixLabelUniqueness();
82
83        $this->sqlite->query('BEGIN TRANSACTION');
84        $ok = true;
85        // create the data table if new schema
86        if (!$this->oldschema->getId()) {
87            $ok = $this->newDataTable();
88        }
89
90        // create a new schema
91        $ok = $ok && $this->newSchema();
92
93        // update column info
94        $ok = $ok && $this->updateColumns();
95        $ok = $ok && $this->addColumns();
96
97        if (!$ok) {
98            $this->sqlite->query('ROLLBACK TRANSACTION');
99            return false;
100        }
101        $this->sqlite->query('COMMIT TRANSACTION');
102
103        return (int)$this->newschemaid;
104    }
105
106    /**
107     * Makes sure all labels in the schema to save are unique
108     */
109    protected function fixLabelUniqueness()
110    {
111        $labels = array();
112
113        if (isset($this->data['cols'])) foreach ($this->data['cols'] as $idx => $column) {
114            $this->data['cols'][$idx]['label'] = $this->fixLabel($column['label'], $labels);
115        }
116
117        if (isset($this->data['new'])) foreach ($this->data['new'] as $idx => $column) {
118            $this->data['new'][$idx]['label'] = $this->fixLabel($column['label'], $labels);
119        }
120    }
121
122    /**
123     * Creates a unique label from the given one
124     *
125     * @param string $wantedlabel
126     * @param array $labels list of already assigned labels (will be filled)
127     * @return string
128     */
129    protected function fixLabel($wantedlabel, &$labels)
130    {
131        $wantedlabel = trim($wantedlabel);
132        $fixedlabel = $wantedlabel;
133        $idx = 1;
134        while (isset($labels[PhpString::strtolower($fixedlabel)])) {
135            $fixedlabel = $wantedlabel . $idx++;
136        }
137        // did we actually do a rename? apply it.
138        if ($fixedlabel != $wantedlabel) {
139            msg(sprintf($this->helper->getLang('duplicate_label'), $wantedlabel, $fixedlabel), -1);
140            $this->data['cols']['label'] = $fixedlabel;
141        }
142        $labels[PhpString::strtolower($fixedlabel)] = 1;
143        return $fixedlabel;
144    }
145
146    /**
147     * Creates a new schema
148     */
149    protected function newSchema()
150    {
151        if (!$this->time) $this->time = time();
152
153        $config = $this->data['config'] ?? '{}';
154
155        /** @noinspection SqlResolve */
156        $sql = "INSERT INTO schemas (tbl, ts, user, config) VALUES (?, ?, ?, ?)";
157        $this->sqlite->query($sql, $this->table, $this->time, $this->user, $config);
158        $res = $this->sqlite->query('SELECT last_insert_rowid()');
159        $this->newschemaid = $this->sqlite->res2single($res);
160        $this->sqlite->res_close($res);
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->storeEntry('types', $newEntry);
189                    if (!$ok) return false;
190                    $res = $this->sqlite->query('SELECT last_insert_rowid()');
191                    if (!$res) return false;
192                    $newTid = $this->sqlite->res2single($res);
193                    $this->sqlite->res_close($res);
194                    if ($oldEntry['ismulti'] == false && $newEntry['ismulti'] == '1') {
195                        $this->migrateSingleToMulti($this->oldschema->getTable(), $column->getColref());
196                    }
197                }
198            } else {
199                $enabled = false; // no longer there for some reason
200            }
201
202            // add this type to the schema columns
203            $schemaEntry = array(
204                'sid' => $this->newschemaid,
205                'colref' => $column->getColref(),
206                'enabled' => $enabled,
207                'tid' => $newTid,
208                'sort' => $sort
209            );
210            $ok = $this->sqlite->storeEntry('schema_cols', $schemaEntry);
211            if (!$ok) return false;
212        }
213        return true;
214    }
215
216    /**
217     * Write the latest value from an entry in a data_ table to the corresponding multi_table
218     *
219     * @param string $table
220     * @param int $colref
221     */
222    protected function migrateSingleToMulti($table, $colref)
223    {
224        /** @noinspection SqlResolve */
225        $sqlSelect = "SELECT pid, rev, published, col$colref AS value FROM data_$table WHERE latest = 1";
226        $res = $this->sqlite->query($sqlSelect);
227        $valueSet = $this->sqlite->res2arr($res);
228        $this->sqlite->res_close($res);
229        $valueString = array();
230        $arguments = array();
231        foreach ($valueSet as $values) {
232            if (blank($values['value']) || trim($values['value']) == '') {
233                continue;
234            }
235            $valueString[] = "(?, ?, ?, ?, ?, ?)";
236            $arguments = array_merge(
237                $arguments,
238                [$colref, $values['pid'], $values['rev'], $values['published'], 1, $values['value']]
239            );
240        }
241        if (empty($valueString)) {
242            return;
243        }
244        $valueString = join(',', $valueString);
245        /** @noinspection SqlResolve */
246        $sqlInsert = "INSERT OR REPLACE INTO multi_$table (colref, pid, rev, published, row, value) VALUES $valueString"; // phpcs:ignore
247        $this->sqlite->query($sqlInsert, $arguments);
248    }
249
250    /**
251     * Adds new columns to the new schema
252     *
253     * @return bool
254     */
255    protected function addColumns()
256    {
257        if (!isset($this->data['new'])) return true;
258
259        $colref = count($this->oldschema->getColumns()) + 1;
260
261        foreach ($this->data['new'] as $column) {
262            if (!$column['isenabled']) continue; // we do not add a disabled column
263
264            // todo this duplicates the hardcoding as in  the function above
265            $newEntry = array();
266            $newEntry['config'] = $column['config'] ?? '{}';
267            $newEntry['label'] = $column['label'];
268            $newEntry['ismulti'] = $column['ismulti'] ?? 0;
269            $newEntry['class'] = $column['class'];
270            $sort = $column['sort'];
271
272
273            // only save if the column got a name
274            if (!$newEntry['label']) continue;
275
276            // add new column to the data table
277            if (!$this->addDataTableColumn($colref)) {
278                return false;
279            }
280
281            // save the type
282            $ok = $this->sqlite->storeEntry('types', $newEntry);
283            if (!$ok) return false;
284            $res = $this->sqlite->query('SELECT last_insert_rowid()');
285            if (!$res) return false;
286            $newTid = $this->sqlite->res2single($res);
287            $this->sqlite->res_close($res);
288
289
290            // add this type to the schema columns
291            $schemaEntry = array(
292                'sid' => $this->newschemaid,
293                'colref' => $colref,
294                'enabled' => true,
295                'tid' => $newTid,
296                'sort' => $sort
297            );
298            $ok = $this->sqlite->storeEntry('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