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