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