xref: /plugin/struct/meta/Schema.php (revision fa7b96aac3c296355e7757cc16519768d467d548) 
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', true);
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            $class = 'dokuwiki\\plugin\\struct\\types\\' . $row['class'];
109            if(!class_exists($class)) {
110                // This usually never happens, except during development
111                msg('Unknown type "' . hsc($row['class']) . '" falling back to Text', -1);
112                $class = 'dokuwiki\\plugin\\struct\\types\\Text';
113            }
114
115            $config = json_decode($row['config'], true);
116            /** @var AbstractBaseType $type */
117            $type = new $class($config, $row['label'], $row['ismulti'], $row['tid']);
118            $column = new Column(
119                $row['sort'],
120                $type,
121                $row['colref'],
122                $row['enabled'],
123                $table
124            );
125            $type->setContext($column);
126
127            $this->columns[] = $column;
128            if($row['sort'] > $this->maxsort) $this->maxsort = $row['sort'];
129        }
130    }
131
132    /**
133     * Cleans any unwanted stuff from table names
134     *
135     * @param string $table
136     * @return string
137     */
138    static public function cleanTableName($table) {
139        $table = strtolower($table);
140        $table = preg_replace('/[^a-z0-9_]+/', '', $table);
141        $table = preg_replace('/^[0-9_]+/', '', $table);
142        $table = trim($table);
143        return $table;
144    }
145
146    /**
147     * Gets a list of all available schemas
148     *
149     * @return string[]
150     */
151    static public function getAll() {
152        /** @var \helper_plugin_struct_db $helper */
153        $helper = plugin_load('helper', 'struct_db');
154        $db = $helper->getDB();
155        if(!$db) return array();
156
157        $res = $db->query("SELECT DISTINCT tbl FROM schemas ORDER BY tbl");
158        $tables = $db->res2arr($res);
159        $db->res_close($res);
160
161        $result = array();
162        foreach($tables as $row) {
163            $result[] = $row['tbl'];
164        }
165        return $result;
166    }
167
168    /**
169     * Delete all data associated with this schema
170     *
171     * This is really all data ever! Be careful!
172     */
173    public function delete() {
174        if(!$this->id) throw new StructException('can not delete unsaved schema');
175
176        $this->sqlite->query('BEGIN TRANSACTION');
177
178        $sql = "DROP TABLE ?";
179        $this->sqlite->query($sql, 'data_'.$this->table);
180        $this->sqlite->query($sql, 'multi_'.$this->table);
181
182        $sql = "DELETE FROM schema_assignments WHERE tbl = ?";
183        $this->sqlite->query($sql, $this->table);
184
185        $sql = "DELETE FROM schema_assignments_patterns WHERE tbl = ?";
186        $this->sqlite->query($sql, $this->table);
187
188        $sql = "SELECT T.id
189                  FROM types T, schema_cols SC, schemas S
190                 WHERE T.id = SC.tid
191                   AND SC.sid = S.id
192                   AND S.tbl = ?";
193        $sql = "DELETE FROM types WHERE id IN ($sql)";
194        $this->sqlite->query($sql, $this->table);
195
196        $sql = "SELECT id
197                  FROM schemas
198                 WHERE tbl = ?";
199        $sql = "DELETE FROM schema_cols WHERE sid IN ($sql)";
200        $this->sqlite->query($sql, $this->table);
201
202        $sql = "DELETE FROM schemas WHERE tbl = ?";
203        $this->sqlite->query($sql, $this->table);
204
205        $this->sqlite->query('COMMIT TRANSACTION');
206        $this->sqlite->query('VACUUM');
207
208        // a deleted schema should not be used anymore, but let's make sure it's somewhat sane anyway
209        $this->id = 0;
210        $this->chksum = '';
211        $this->columns = array();
212        $this->maxsort = 0;
213        $this->ts = 0;
214    }
215
216    /**
217     * @return string
218     */
219    public function getChksum() {
220        return $this->chksum;
221    }
222
223    /**
224     * @return int
225     */
226    public function getId() {
227        return $this->id;
228    }
229
230    /**
231     * @return string
232     */
233    public function getUser() {
234        return $this->user;
235    }
236
237    /**
238     * Returns a list of columns in this schema
239     *
240     * @param bool $withDisabled if false, disabled columns will not be returned
241     * @return Column[]
242     */
243    public function getColumns($withDisabled = true) {
244        if(!$withDisabled) {
245            return array_filter(
246                $this->columns,
247                function (Column $col) {
248                    return $col->isEnabled();
249                }
250            );
251        }
252
253        return $this->columns;
254    }
255
256    /**
257     * Find a column in the schema by its label
258     *
259     * Only enabled columns are returned!
260     *
261     * @param $name
262     * @return bool|Column
263     */
264    public function findColumn($name) {
265        foreach($this->columns as $col) {
266            if($col->isEnabled() && utf8_strtolower($col->getLabel()) == utf8_strtolower($name)) {
267                return $col;
268            }
269        }
270        return false;
271    }
272
273    /**
274     * @return string
275     */
276    public function getTable() {
277        return $this->table;
278    }
279
280    /**
281     * @return int the highest sort number used in this schema
282     */
283    public function getMaxsort() {
284        return $this->maxsort;
285    }
286
287    /**
288     * @return string the JSON representing this schema
289     */
290    public function toJSON() {
291        $data = array(
292            'structversion' => $this->structversion,
293            'schema' => $this->getTable(),
294            'id' => $this->getId(),
295            'user' => $this->getUser(),
296            'columns' => array()
297        );
298
299        foreach($this->columns as $column) {
300            $data['columns'][] = array(
301                'colref' => $column->getColref(),
302                'ismulti' => $column->isMulti(),
303                'isenabled' => $column->isEnabled(),
304                'sort' => $column->getSort(),
305                'label' => $column->getLabel(),
306                'class' => $column->getType()->getClass(),
307                'config' => $column->getType()->getConfig(),
308            );
309        }
310
311        return json_encode($data, JSON_PRETTY_PRINT);
312    }
313}
314