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