1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5use dokuwiki\plugin\sqlite\SQLiteDB;
6use dokuwiki\plugin\struct\types\AbstractBaseType;
7use dokuwiki\Utf8\PhpString;
8
9/**
10 * Class Schema
11 *
12 * Represents the schema of a single data table and all its properties. It defines what can be stored in
13 * the represented data table and how those contents are formatted.
14 *
15 * It can be initialized with a timestamp to access the schema as it looked at that particular point in time.
16 *
17 * @package dokuwiki\plugin\struct\meta
18 */
19class Schema
20{
21    use TranslationUtilities;
22
23    /** @var SQLiteDB|null */
24    protected $sqlite;
25
26    /** @var int The ID of this schema */
27    protected $id = 0;
28
29    /** @var string the user who last edited this schema */
30    protected $user = '';
31
32    /** @var string name of the associated table */
33    protected $table = '';
34
35    /** @var Column[] all the colums */
36    protected $columns = [];
37
38    /** @var int */
39    protected $maxsort = 0;
40
41    /** @var int */
42    protected $ts = 0;
43
44    /** @var string struct version info */
45    protected $structversion = '?';
46
47    /** @var array config array with label translations */
48    protected $config = [];
49
50    /**
51     * Schema constructor
52     *
53     * @param string $table The table this schema is for
54     * @param int $ts The timestamp for when this schema was valid, 0 for current
55     */
56    public function __construct($table, $ts = 0)
57    {
58        $baseconfig = ['allowed editors' => '', 'internal' => false];
59
60        /** @var \helper_plugin_struct_db $helper */
61        $helper = plugin_load('helper', 'struct_db');
62        $info = $helper->getInfo();
63        $this->structversion = $info['date'];
64        $this->sqlite = $helper->getDB();
65        $table = self::cleanTableName($table);
66        $this->table = $table;
67
68        $this->ts = $ts;
69
70        // load info about the schema itself
71        if ($ts) {
72            $sql = "SELECT *
73                      FROM schemas
74                     WHERE tbl = ?
75                       AND ts <= ?
76                  ORDER BY ts DESC
77                     LIMIT 1";
78            $opt = [$table, $ts];
79        } else {
80            $sql = "SELECT *
81                      FROM schemas
82                     WHERE tbl = ?
83                  ORDER BY ts DESC
84                     LIMIT 1";
85            $opt = [$table];
86        }
87        $schema = $this->sqlite->queryAll($sql, $opt);
88        $config = [];
89
90        if (!empty($schema)) {
91            $result = array_shift($schema);
92            $this->id = $result['id'];
93            $this->user = $result['user'];
94            $this->ts = $result['ts'];
95            $config = json_decode($result['config'], true, 512, JSON_THROW_ON_ERROR);
96        }
97        $this->config = array_merge($baseconfig, $config);
98        $this->initTransConfig(['label']);
99        if (!$this->id) return;
100
101        // load existing columns
102        $sql = "SELECT SC.*, T.*
103                  FROM schema_cols SC,
104                       types T
105                 WHERE SC.sid = ?
106                   AND SC.tid = T.id
107              ORDER BY SC.sort";
108        $rows = $this->sqlite->queryAll($sql, [$this->id]);
109
110        $typeclasses = Column::allTypes();
111        foreach ($rows as $row) {
112            if ($row['class'] == 'Integer') {
113                $row['class'] = 'Decimal';
114            }
115
116            $class = $typeclasses[$row['class']];
117            if (!class_exists($class)) {
118                // This usually never happens, except during development
119                msg('Unknown type "' . hsc($row['class']) . '" falling back to Text', -1);
120                $class = 'dokuwiki\\plugin\\struct\\types\\Text';
121            }
122
123            $config = json_decode($row['config'], true, 512, JSON_THROW_ON_ERROR);
124            /** @var AbstractBaseType $type */
125            $type = new $class($config, $row['label'], $row['ismulti'], $row['tid']);
126            $column = new Column(
127                $row['sort'],
128                $type,
129                $row['colref'],
130                $row['enabled'],
131                $table
132            );
133            $type->setContext($column);
134
135            $this->columns[] = $column;
136            if ($row['sort'] > $this->maxsort) $this->maxsort = $row['sort'];
137        }
138    }
139
140    /**
141     * @return string identifer for debugging purposes
142     */
143    public function __toString()
144    {
145        return self::class . ' ' . $this->table . ' (' . $this->id . ') ';
146    }
147
148    /**
149     * Cleans any unwanted stuff from table names
150     *
151     * @param string $table
152     * @return string
153     */
154    public static function cleanTableName($table)
155    {
156        $table = strtolower($table);
157        $table = preg_replace('/[^a-z0-9_]+/', '', $table);
158        $table = preg_replace('/^[0-9_]+/', '', $table);
159        $table = trim($table);
160        return $table;
161    }
162
163
164    /**
165     * Gets a list of all available schemas
166     *
167     * @return \string[]
168     */
169    public static function getAll()
170    {
171        /** @var \helper_plugin_struct_db $helper */
172        $helper = plugin_load('helper', 'struct_db');
173        $db = $helper->getDB(false);
174        if (!$db instanceof SQLiteDB) return [];
175
176        $tables = $db->queryAll("SELECT DISTINCT tbl FROM schemas ORDER BY tbl");
177
178        $result = [];
179        foreach ($tables as $row) {
180            $result[] = $row['tbl'];
181        }
182        return $result;
183    }
184
185    /**
186     * Delete all data associated with this schema
187     *
188     * This is really all data ever! Be careful!
189     */
190    public function delete()
191    {
192        if (!$this->id) throw new StructException('can not delete unsaved schema');
193
194        $this->sqlite->query('BEGIN TRANSACTION');
195
196        $sql = "DROP TABLE ";
197        $this->sqlite->query($sql . 'data_' . $this->table);
198        $this->sqlite->query($sql . 'multi_' . $this->table);
199
200        $sql = "DELETE FROM schema_assignments WHERE tbl = '$this->table'";
201        $this->sqlite->query($sql);
202
203        $sql = "DELETE FROM schema_assignments_patterns WHERE tbl = '$this->table'";
204        $this->sqlite->query($sql);
205
206        $sql = "SELECT T.id
207                  FROM types T, schema_cols SC, schemas S
208                 WHERE T.id = SC.tid
209                   AND SC.sid = S.id
210                   AND S.tbl = ?";
211        $sql = "DELETE FROM types WHERE id IN ($sql)";
212
213        $this->sqlite->query($sql, [$this->table]);
214
215        $sql = "SELECT id
216                  FROM schemas
217                 WHERE tbl = ?";
218        $sql = "DELETE FROM schema_cols WHERE sid IN ($sql)";
219
220        $this->sqlite->query($sql, [$this->table]);
221
222        $sql = "DELETE FROM schemas WHERE tbl = ?";
223        $this->sqlite->query($sql, [$this->table]);
224
225        $this->sqlite->query('COMMIT TRANSACTION');
226        $this->sqlite->query('VACUUM');
227
228        // a deleted schema should not be used anymore, but let's make sure it's somewhat sane anyway
229        $this->id = 0;
230        $this->columns = [];
231        $this->maxsort = 0;
232        $this->ts = 0;
233    }
234
235
236    /**
237     * Clear all data of a schema, but retain the schema itself
238     */
239    public function clear()
240    {
241        if (!$this->id) throw new StructException('can not clear data of unsaved schema');
242
243        $this->sqlite->query('BEGIN TRANSACTION');
244        $sql = 'DELETE FROM ';
245        $this->sqlite->query($sql . 'data_' . $this->table);
246        $this->sqlite->query($sql . 'multi_' . $this->table);
247        $this->sqlite->query('COMMIT TRANSACTION');
248        $this->sqlite->query('VACUUM');
249    }
250
251    /**
252     * @return int
253     */
254    public function getId()
255    {
256        return $this->id;
257    }
258
259    /**
260     * @return int returns the timestamp this Schema was created at
261     */
262    public function getTimeStamp()
263    {
264        return $this->ts;
265    }
266
267    /**
268     * @return string
269     */
270    public function getUser()
271    {
272        return $this->user;
273    }
274
275    public function getConfig()
276    {
277        return $this->config;
278    }
279
280    /**
281     * Returns the translated label for this schema
282     *
283     * Uses the current language as determined by $conf['lang']. Falls back to english
284     * and then to the Schema label
285     *
286     * @return string
287     */
288    public function getTranslatedLabel()
289    {
290        return $this->getTranslatedKey('label', $this->table);
291    }
292
293    /**
294     * Checks if the current user may edit data in this schema
295     *
296     * @return bool
297     */
298    public function isEditable()
299    {
300        global $USERINFO;
301        global $INPUT;
302        if ($this->config['allowed editors'] === '') return true;
303        if ($INPUT->server->str('REMOTE_USER') === '') return false;
304        if (auth_isadmin()) return true;
305        return auth_isMember($this->config['allowed editors'], $INPUT->server->str('REMOTE_USER'), $USERINFO['grps']);
306    }
307
308    /**
309     *
310     * @return bool
311     */
312    public function isInternal()
313    {
314        return (bool)$this->config['internal'];
315    }
316
317    /**
318     * Returns a list of columns in this schema
319     *
320     * @param bool $withDisabled if false, disabled columns will not be returned
321     * @return Column[]
322     */
323    public function getColumns($withDisabled = true)
324    {
325        if (!$withDisabled) {
326            return array_filter(
327                $this->columns,
328                static fn(Column $col) => $col->isEnabled()
329            );
330        }
331
332        return $this->columns;
333    }
334
335    /**
336     * Find a column in the schema by its label
337     *
338     * Only enabled columns are returned!
339     *
340     * @param $name
341     * @return bool|Column
342     */
343    public function findColumn($name)
344    {
345        foreach ($this->columns as $col) {
346            if ($col->isEnabled() && PhpString::strtolower($col->getLabel()) === PhpString::strtolower($name)) {
347                return $col;
348            }
349        }
350        return false;
351    }
352
353    /**
354     * @return string
355     */
356    public function getTable()
357    {
358        return $this->table;
359    }
360
361    /**
362     * @return int the highest sort number used in this schema
363     */
364    public function getMaxsort()
365    {
366        return $this->maxsort;
367    }
368
369    /**
370     * @return string the JSON representing this schema
371     */
372    public function toJSON()
373    {
374        $data = [
375            'structversion' => $this->structversion,
376            'schema' => $this->getTable(),
377            'id' => $this->getId(),
378            'user' => $this->getUser(),
379            'config' => $this->getConfig(),
380            'columns' => []
381        ];
382
383        foreach ($this->columns as $column) {
384            $data['columns'][] = [
385                'colref' => $column->getColref(),
386                'ismulti' => $column->isMulti(),
387                'isenabled' => $column->isEnabled(),
388                'sort' => $column->getSort(),
389                'label' => $column->getLabel(),
390                'class' => $column->getType()->getClass(),
391                'config' => $column->getType()->getConfig()
392            ];
393        }
394
395        return json_encode($data, JSON_PRETTY_PRINT);
396    }
397}
398