xref: /plugin/struct/meta/AccessTable.php (revision 03307831818a6b3944e8c7fcb1cd59203584e9c4) 
1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5/**
6 * Class AccessTable
7 *
8 * Base class for data accessors
9 *
10 * @package dokuwiki\plugin\struct\meta
11 */
12abstract class AccessTable {
13
14    const DEFAULT_REV = 0;
15    const DEFAULT_LATEST = 1;
16
17    /** @var  Schema */
18    protected $schema;
19    protected $pid;
20    protected $rid;
21    protected $labels = [];
22    protected $ts     = 0;
23    /** @var \helper_plugin_sqlite */
24    protected $sqlite;
25
26    // options on how to retrieve data
27    protected $opt_skipempty = false;
28
29    /**
30     * @var string Name of single-value table
31     */
32    protected $stable;
33
34    /**
35     * @var string Name of multi-value table
36     */
37    protected $mtable;
38
39    /**
40     * @var array Column names for the single-value insert/update
41     */
42    protected $singleCols;
43
44    /**
45     * @var array Input values for the single-value insert/update
46     */
47    protected $singleValues;
48
49    /**
50     * @var array Input values for the multi-value inserts/updates
51     */
52    protected $multiValues;
53
54
55    /**
56     * Factory method returning the appropriate data accessor (page, lookup or serial)
57     *
58     * @param Schema $schema schema to load
59     * @param string $pid Page id to access
60     * @param int $ts Time at which the data should be read or written, 0 for now
61     * @param int $rid Row id, 0 for page type data, otherwise autoincrement
62     * @return AccessTableData|AccessTableLookup
63     */
64    public static function bySchema(Schema $schema, $pid, $ts = 0, $rid = 0) {
65        if (self::isTypePage($pid, $ts, $rid)) {
66            return new AccessTableData($schema, $pid, $ts, $rid);
67        }
68        return new AccessTableLookup($schema, $pid, $ts, $rid);
69    }
70
71    /**
72     * Factory Method to access data
73     *
74     * @param string $tablename schema to load
75     * @param string $pid Page id to access
76     * @param int $ts Time at which the data should be read or written, 0 for now
77     * @param int $rid Row id, 0 for page type data, otherwise autoincrement
78     * @return AccessTableData|AccessTableLookup
79     */
80    public static function byTableName($tablename, $pid, $ts = 0, $rid = 0) {
81        $schema = new Schema($tablename, $ts);
82        return self::bySchema($schema, $pid, $ts, $rid);
83    }
84
85    /**
86     * AccessTable constructor
87     *
88     * @param Schema $schema The schema valid at $ts
89     * @param string $pid Page id
90     * @param int $ts Time at which the data should be read or written, 0 for now
91     * @param int $rid Row id: 0 for pages, autoincremented for other types
92     */
93    public function __construct(Schema $schema, $pid, $ts = 0, $rid = 0) {
94        /** @var \helper_plugin_struct_db $helper */
95        $helper = plugin_load('helper', 'struct_db');
96        $this->sqlite = $helper->getDB();
97
98        if(!$schema->getId()) {
99            throw new StructException('Schema does not exist. Only data of existing schemas can be accessed');
100        }
101
102        $this->schema = $schema;
103        $this->pid = $pid;
104        $this->rid = $rid;
105        $this->setTimestamp($ts);
106        foreach($this->schema->getColumns() as $col) {
107            $this->labels[$col->getColref()] = $col->getType()->getLabel();
108        }
109    }
110
111    /**
112     * gives access to the schema
113     *
114     * @return Schema
115     */
116    public function getSchema() {
117        return $this->schema;
118    }
119
120    /**
121     * The current pid
122     *
123     * @return string
124     */
125    public function getPid() {
126        return $this->pid;
127    }
128
129    /**
130     * The current rid
131     *
132     * @return int
133     */
134    public function getRid() {
135        return $this->rid;
136    }
137
138    /**
139     * Should remove the current data, by either deleting or ovewriting it
140     *
141     * @return bool if the delete succeeded
142     */
143    abstract public function clearData();
144
145    /**
146     * Save the data to the database.
147     *
148     * We differentiate between single-value-column and multi-value-column by the value to the respective column-name,
149     * i.e. depending on if that is a string or an array, respectively.
150     *
151     * @param array $data typelabel => value for single fields or typelabel => array(value, value, ...) for multi fields
152     * @return bool success of saving the data to the database
153     */
154    public function saveData($data)
155    {
156        if (!$this->validateTypeData($data)) {
157            return false;
158        }
159
160        $this->stable = 'data_' . $this->schema->getTable();
161        $this->mtable = 'multi_' . $this->schema->getTable();
162
163        $colrefs = array_flip($this->labels);
164
165        foreach ($data as $colname => $value) {
166            if(!isset($colrefs[$colname])) {
167                throw new StructException("Unknown column %s in schema.", hsc($colname));
168            }
169
170            $this->singleCols[] = 'col' . $colrefs[$colname];
171            if (is_array($value)) {
172                foreach ($value as $index => $multivalue) {
173                    $this->multiValues[] = [$colrefs[$colname], $index + 1, $multivalue];
174                }
175                // copy first value to the single column
176                if(isset($value[0])) {
177                    $this->singleValues[] = $value[0];
178                } else {
179                    $this->singleValues[] = null;
180                }
181            } else {
182                $this->singleValues[] = $value;
183            }
184        }
185
186        $this->sqlite->query('BEGIN TRANSACTION');
187
188        $ok = $this->beforeSave();
189
190        // insert single values
191        $ok = $ok && $this->sqlite->query(
192                $this->getSingleSql(),
193            array_merge($this->getSingleNoninputValues(), $this->singleValues)
194            );
195
196        $ok = $ok && $this->afterSingleSave();
197
198        // insert multi values
199        if ($ok && $this->multiValues) {
200            $multisql = $this->getMultiSql();
201            $multiNoninputValues = $this->getMultiNoninputValues();
202            foreach ($this->multiValues as $value) {
203                $ok = $ok && $this->sqlite->query(
204                        $multisql,
205                        array_merge($multiNoninputValues, $value)
206                    );
207            }
208        }
209
210        if (!$ok) {
211            $this->sqlite->query('ROLLBACK TRANSACTION');
212            return false;
213        }
214        $this->sqlite->query('COMMIT TRANSACTION');
215        return true;
216    }
217
218    /**
219     * Check whether all required data is present
220     *
221     * @param array $data
222     * @return bool
223     */
224    abstract protected function validateTypeData($data);
225
226    /**
227     * Names of non-input columns to be inserted into SQL query
228     *
229     * @return array
230     */
231    abstract protected function getSingleNoninputCols();
232
233    /**
234     * Values for non-input columns to be inserted into SQL query
235     * for single-value tables
236     *
237     * @return array
238     */
239    abstract protected function getSingleNoninputValues();
240
241    /**
242     * String template for single-value table
243     *
244     * @return string
245     */
246    protected function getSingleSql()
247    {
248        $cols = array_merge($this->getSingleNoninputCols(), $this->singleCols);
249        $cols = join(',', $cols);
250        $vals = array_merge($this->getSingleNoninputValues(), $this->singleValues);
251
252        return "INSERT INTO $this->stable ($cols) VALUES (" . trim(str_repeat('?,', count($vals)),',') . ');';
253    }
254
255    /**
256     * Optional operations to be executed before saving data
257     *
258     * @return bool False if any of the operations failed and transaction should be rolled back
259     */
260    protected function beforeSave()
261    {
262        return true;
263    }
264
265    /**
266     * Optional operations to be executed after saving data to single-value table,
267     * before saving multivalues
268     *
269     * @return bool False if anything goes wrong and transaction should be rolled back
270     */
271    protected function afterSingleSave()
272    {
273        return true;
274    }
275
276    /**
277     * String template for multi-value table
278     *
279     * @return string
280     */
281    abstract protected function getMultiSql();
282
283    /**
284     * Values for non-input columns to be inserted into SQL query
285     * for multi-value tables
286     * @return array
287     */
288    abstract protected function getMultiNoninputValues();
289
290
291    /**
292     * Should empty or invisible (inpage) fields be returned?
293     *
294     * Defaults to false
295     *
296     * @param null|bool $set new value, null to read only
297     * @return bool current value (after set)
298     */
299    public function optionSkipEmpty($set = null) {
300        if(!is_null($set)) {
301            $this->opt_skipempty = $set;
302        }
303        return $this->opt_skipempty;
304    }
305
306    /**
307     * Get the value of a single column
308     *
309     * @param Column $column
310     * @return Value|null
311     */
312    public function getDataColumn($column) {
313        $data = $this->getData();
314        foreach($data as $value) {
315            if($value->getColumn() == $column) {
316                return $value;
317            }
318        }
319        return null;
320    }
321
322    /**
323     * returns the data saved for the page
324     *
325     * @return Value[] a list of values saved for the current page
326     */
327    public function getData() {
328        $data = $this->getDataFromDB();
329        $data = $this->consolidateData($data, false);
330        return $data;
331    }
332
333    /**
334     * returns the data saved for the page as associative array
335     *
336     * The array returned is in the same format as used in @see saveData()
337     *
338     * It always returns raw Values!
339     *
340     * @return array
341     */
342    public function getDataArray() {
343        $data = $this->getDataFromDB();
344        $data = $this->consolidateData($data, true);
345        return $data;
346    }
347
348    /**
349     * Return the data in pseudo syntax
350     */
351    public function getDataPseudoSyntax() {
352        $result = '';
353        $data = $this->getData();
354
355        foreach($data as $value) {
356            $key = $value->getColumn()->getFullQualifiedLabel();
357            $value = $value->getDisplayValue();
358            if(is_array($value)) $value = join(', ', $value);
359            $result .= sprintf("% -20s : %s\n", $key, $value);
360        }
361        return $result;
362    }
363
364    /**
365     * retrieve the data saved for the page from the database. Usually there is no need to call this function.
366     * Call @see SchemaData::getData instead.
367     */
368    protected function getDataFromDB() {
369        $idColumn = self::isTypePage($this->pid, $this->ts, $this->rid) ? 'pid' : 'rid';
370        list($sql, $opt) = $this->buildGetDataSQL($idColumn);
371
372        $res = $this->sqlite->query($sql, $opt);
373        $data = $this->sqlite->res2arr($res);
374        $this->sqlite->res_close($res);
375        return $data;
376    }
377
378    /**
379     * Creates a proper result array from the database data
380     *
381     * @param array $DBdata the data as it is retrieved from the database, i.e. by SchemaData::getDataFromDB
382     * @param bool $asarray return data as associative array (true) or as array of Values (false)
383     * @return array|Value[]
384     */
385    protected function consolidateData($DBdata, $asarray = false) {
386        $data = array();
387
388        $sep = Search::CONCAT_SEPARATOR;
389
390        foreach($this->schema->getColumns(false) as $col) {
391
392            // if no data saved yet, return empty strings
393            if($DBdata) {
394                $val = $DBdata[0]['out' . $col->getColref()];
395            } else {
396                $val = '';
397            }
398
399            // multi val data is concatenated
400            if($col->isMulti()) {
401                $val = explode($sep, $val);
402                $val = array_filter($val);
403            }
404
405            $value = new Value($col, $val);
406
407            if($this->opt_skipempty && $value->isEmpty()) continue;
408            if($this->opt_skipempty && !$col->isVisibleInPage()) continue; //FIXME is this a correct assumption?
409
410            // for arrays, we return the raw value only
411            if($asarray) {
412                $data[$col->getLabel()] = $value->getRawValue();
413            } else {
414                $data[$col->getLabel()] = $value;
415            }
416        }
417
418        return $data;
419    }
420
421    /**
422     * Builds the SQL statement to select the data for this page and schema
423     *
424     * @return array Two fields: the SQL string and the parameters array
425     */
426    protected function buildGetDataSQL($idColumn = 'pid') {
427        $sep = Search::CONCAT_SEPARATOR;
428        $stable = 'data_' . $this->schema->getTable();
429        $mtable = 'multi_' . $this->schema->getTable();
430
431        $QB = new QueryBuilder();
432        $QB->addTable($stable, 'DATA');
433        $QB->addSelectColumn('DATA', $idColumn, strtoupper($idColumn));
434        $QB->addGroupByStatement("DATA.$idColumn");
435
436        foreach($this->schema->getColumns(false) as $col) {
437
438            $colref = $col->getColref();
439            $colname = 'col' . $colref;
440            $outname = 'out' . $colref;
441
442            if($col->getType()->isMulti()) {
443                $tn = 'M' . $colref;
444                $QB->addLeftJoin(
445                    'DATA',
446                    $mtable,
447                    $tn,
448                    "DATA.$idColumn = $tn.$idColumn AND DATA.rev = $tn.rev AND $tn.colref = $colref"
449                );
450                $col->getType()->select($QB, $tn, 'value', $outname);
451                $sel = $QB->getSelectStatement($outname);
452                $QB->addSelectStatement("GROUP_CONCAT($sel, '$sep')", $outname);
453            } else {
454                $col->getType()->select($QB, 'DATA', $colname, $outname);
455                $QB->addGroupByStatement($outname);
456            }
457        }
458
459        $pl = $QB->addValue($this->{$idColumn});
460        $QB->filters()->whereAnd("DATA.$idColumn = $pl");
461        $pl = $QB->addValue($this->getLastRevisionTimestamp());
462        $QB->filters()->whereAnd("DATA.rev = $pl");
463
464        return $QB->getSQL();
465    }
466
467    /**
468     * @param int $ts
469     */
470    public function setTimestamp($ts) {
471        if($ts && $ts < $this->schema->getTimeStamp()) {
472            throw new StructException('Given timestamp is not valid for current Schema');
473        }
474
475        $this->ts = $ts;
476    }
477
478    /**
479     * Returns the timestamp from the current data
480     * @return int
481     */
482    public function getTimestamp()
483    {
484        return $this->ts;
485    }
486
487    /**
488     * Return the last time an edit happened for this table for the currently set
489     * time and pid. When the current timestamp is 0, the newest revision is
490     * returned. Used in @see buildGetDataSQL()
491     *
492     * @return int
493     */
494    abstract protected function getLastRevisionTimestamp();
495
496    /**
497     * Check if the given data validates against the current types.
498     *
499     * @param array $data
500     * @return AccessDataValidator
501     */
502    public function getValidator($data) {
503        return new AccessDataValidator($this, $data);
504    }
505
506    /**
507     * Returns true if data is of type "page"
508     *
509     * @param string $pid
510     * @param int $rev
511     * @param int $rid
512     * @return bool
513     */
514    public static function isTypePage($pid, $rev, $rid)
515    {
516        return $rev > 0;
517    }
518
519    /**
520     * Returns true if data is of type "lookup"
521     *
522     * @param string $pid
523     * @param int $rev
524     * @param int $rid
525     * @return bool
526     */
527    public static function isTypeLookup($pid, $rev, $rid)
528    {
529        return $pid === '';
530    }
531
532    /**
533     * Returns true if data is of type "serial"
534     *
535     * @param string $pid
536     * @param int $rev
537     * @param int $rid
538     * @return bool
539     */
540    public static function isTypeSerial($pid, $rev, $rid)
541    {
542        return $pid !== '' && $rev === 0;
543    }
544}
545
546
547