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