xref: /plugin/struct/meta/Schema.php (revision 38edbfed4369e996ab2fd8e621d25df72db0d574) 
1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5use dokuwiki\plugin\struct\types\AbstractBaseType;
6
7if(!defined('JSON_PRETTY_PRINT')) define('JSON_PRETTY_PRINT', 0); // PHP 5.3 compatibility
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    /** @var \helper_plugin_sqlite|null */
22    protected $sqlite;
23
24    /** @var int The ID of this schema */
25    protected $id = 0;
26
27    /** @var string the user who last edited this schema */
28    protected $user = '';
29
30    /** @var string name of the associated table */
31    protected $table = '';
32
33    /**
34     * @var string the current checksum of this schema
35     */
36    protected $chksum = '';
37
38    /** @var Column[] all the colums */
39    protected $columns = array();
40
41    /** @var int */
42    protected $maxsort = 0;
43
44    /** @var int */
45    protected $ts = 0;
46
47    /** @var string struct version info */
48    protected $structversion = '?';
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        /** @var \helper_plugin_struct_db $helper */
58        $helper = plugin_load('helper', 'struct_db');
59        $info = $helper->getInfo();
60        $this->structversion = $info['date'];
61        $this->sqlite = $helper->getDB();
62        if(!$this->sqlite) return;
63
64        $table = self::cleanTableName($table);
65        $this->table = $table;
66        $this->ts = $ts;
67
68        // load info about the schema itself
69        if($ts) {
70            $sql = "SELECT *
71                      FROM schemas
72                     WHERE tbl = ?
73                       AND ts <= ?
74                  ORDER BY ts DESC
75                     LIMIT 1";
76            $opt = array($table, $ts);
77        } else {
78            $sql = "SELECT *
79                      FROM schemas
80                     WHERE tbl = ?
81                  ORDER BY ts DESC
82                     LIMIT 1";
83            $opt = array($table);
84        }
85        $res = $this->sqlite->query($sql, $opt);
86        if($this->sqlite->res2count($res)) {
87            $schema = $this->sqlite->res2arr($res);
88            $result = array_shift($schema);
89            $this->id = $result['id'];
90            $this->user = $result['user'];
91            $this->chksum = $result['chksum'];
92        }
93        $this->sqlite->res_close($res);
94        if(!$this->id) return;
95
96        // load existing columns
97        $sql = "SELECT SC.*, T.*
98                  FROM schema_cols SC,
99                       types T
100                 WHERE SC.sid = ?
101                   AND SC.tid = T.id
102              ORDER BY SC.sort";
103        $res = $this->sqlite->query($sql, $this->id);
104        $rows = $this->sqlite->res2arr($res);
105        $this->sqlite->res_close($res);
106
107        foreach($rows as $row) {
108            if($row['class'] == 'Integer') {
109                $row['class'] = 'Decimal';
110            }
111
112            $class = 'dokuwiki\\plugin\\struct\\types\\' . $row['class'];
113            if(!class_exists($class)) {
114                // This usually never happens, except during development
115                msg('Unknown type "' . hsc($row['class']) . '" falling back to Text', -1);
116                $class = 'dokuwiki\\plugin\\struct\\types\\Text';
117            }
118
119            $config = json_decode($row['config'], true);
120            /** @var AbstractBaseType $type */
121            $type = new $class($config, $row['label'], $row['ismulti'], $row['tid']);
122            $column = new Column(
123                $row['sort'],
124                $type,
125                $row['colref'],
126                $row['enabled'],
127                $table
128            );
129            $type->setContext($column);
130
131            $this->columns[] = $column;
132            if($row['sort'] > $this->maxsort) $this->maxsort = $row['sort'];
133        }
134    }
135
136    /**
137     * Cleans any unwanted stuff from table names
138     *
139     * @param string $table
140     * @return string
141     */
142    static public function cleanTableName($table) {
143        $table = strtolower($table);
144        $table = preg_replace('/[^a-z0-9_]+/', '', $table);
145        $table = preg_replace('/^[0-9_]+/', '', $table);
146        $table = trim($table);
147        return $table;
148    }
149
150    /**
151     * Gets a list of all available schemas
152     *
153     * @return string[]
154     */
155    static public function getAll() {
156        /** @var \helper_plugin_struct_db $helper */
157        $helper = plugin_load('helper', 'struct_db');
158        $db = $helper->getDB();
159        if(!$db) return array();
160
161        $res = $db->query("SELECT DISTINCT tbl FROM schemas ORDER BY tbl");
162        $tables = $db->res2arr($res);
163        $db->res_close($res);
164
165        $result = array();
166        foreach($tables as $row) {
167            $result[] = $row['tbl'];
168        }
169        return $result;
170    }
171
172    /**
173     * Delete all data associated with this schema
174     *
175     * This is really all data ever! Be careful!
176     */
177    public function delete() {
178        if(!$this->id) throw new StructException('can not delete unsaved schema');
179
180        $this->sqlite->query('BEGIN TRANSACTION');
181
182        $sql = "DROP TABLE ?";
183        $this->sqlite->query($sql, 'data_'.$this->table);
184        $this->sqlite->query($sql, 'multi_'.$this->table);
185
186        $sql = "DELETE FROM schema_assignments WHERE tbl = ?";
187        $this->sqlite->query($sql, $this->table);
188
189        $sql = "DELETE FROM schema_assignments_patterns WHERE tbl = ?";
190        $this->sqlite->query($sql, $this->table);
191
192        $sql = "SELECT T.id
193                  FROM types T, schema_cols SC, schemas S
194                 WHERE T.id = SC.tid
195                   AND SC.sid = S.id
196                   AND S.tbl = ?";
197        $sql = "DELETE FROM types WHERE id IN ($sql)";
198        $this->sqlite->query($sql, $this->table);
199
200        $sql = "SELECT id
201                  FROM schemas
202                 WHERE tbl = ?";
203        $sql = "DELETE FROM schema_cols WHERE sid IN ($sql)";
204        $this->sqlite->query($sql, $this->table);
205
206        $sql = "DELETE FROM schemas WHERE tbl = ?";
207        $this->sqlite->query($sql, $this->table);
208
209        $this->sqlite->query('COMMIT TRANSACTION');
210        $this->sqlite->query('VACUUM');
211
212        // a deleted schema should not be used anymore, but let's make sure it's somewhat sane anyway
213        $this->id = 0;
214        $this->chksum = '';
215        $this->columns = array();
216        $this->maxsort = 0;
217        $this->ts = 0;
218    }
219
220    /**
221     * @return string
222     */
223    public function getChksum() {
224        return $this->chksum;
225    }
226
227    /**
228     * @return int
229     */
230    public function getId() {
231        return $this->id;
232    }
233
234    /**
235     * @return string
236     */
237    public function getUser() {
238        return $this->user;
239    }
240
241    /**
242     * Returns a list of columns in this schema
243     *
244     * @param bool $withDisabled if false, disabled columns will not be returned
245     * @return Column[]
246     */
247    public function getColumns($withDisabled = true) {
248        if(!$withDisabled) {
249            return array_filter(
250                $this->columns,
251                function (Column $col) {
252                    return $col->isEnabled();
253                }
254            );
255        }
256
257        return $this->columns;
258    }
259
260    /**
261     * Find a column in the schema by its label
262     *
263     * Only enabled columns are returned!
264     *
265     * @param $name
266     * @return bool|Column
267     */
268    public function findColumn($name) {
269        foreach($this->columns as $col) {
270            if($col->isEnabled() && utf8_strtolower($col->getLabel()) == utf8_strtolower($name)) {
271                return $col;
272            }
273        }
274        return false;
275    }
276
277    /**
278     * @return string
279     */
280    public function getTable() {
281        return $this->table;
282    }
283
284    /**
285     * @return int the highest sort number used in this schema
286     */
287    public function getMaxsort() {
288        return $this->maxsort;
289    }
290
291    /**
292     * @return string the JSON representing this schema
293     */
294    public function toJSON() {
295        $data = array(
296            'structversion' => $this->structversion,
297            'schema' => $this->getTable(),
298            'id' => $this->getId(),
299            'user' => $this->getUser(),
300            'columns' => array()
301        );
302
303        foreach($this->columns as $column) {
304            $data['columns'][] = array(
305                'colref' => $column->getColref(),
306                'ismulti' => $column->isMulti(),
307                'isenabled' => $column->isEnabled(),
308                'sort' => $column->getSort(),
309                'label' => $column->getLabel(),
310                'class' => $column->getType()->getClass(),
311                'config' => $column->getType()->getConfig(),
312            );
313        }
314
315        return json_encode($data, JSON_PRETTY_PRINT);
316    }
317}
318