xref: /plugin/struct/meta/CSVImporter.php (revision 4e4edb41089a43de9c0e39821afa8a55fe8f03ee) 
1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5use dokuwiki\plugin\struct\types\Page;
6
7/**
8 * Class CSVImporter
9 *
10 * Imports CSV data into a lookup schema
11 *
12 * @package dokuwiki\plugin\struct\meta
13 */
14class CSVImporter
15{
16
17    /** @var  Schema */
18    protected $schema;
19
20    /** @var  resource */
21    protected $fh;
22
23    /** @var  \helper_plugin_sqlite */
24    protected $sqlite;
25
26    /** @var Column[] The single values to store index => col */
27    protected $columns = array();
28
29    /** @var int current line number */
30    protected $line = 0;
31
32    /** @var array list of headers */
33    protected $header;
34
35    /** @var  array list of validation errors */
36    protected $errors;
37
38    /**
39     * @var string data type, must be one of page, lookup, serial
40     */
41    protected $type;
42
43    /**
44     * CSVImporter constructor.
45     *
46     * @param string $table
47     * @param string $file
48     * @param string $type
49     */
50    public function __construct($table, $file, $type)
51    {
52        $this->type = $type;
53        $this->openFile($file);
54
55        $this->schema = new Schema($table);
56        if (!$this->schema->getId()) throw new StructException('Schema does not exist');
57
58        /** @var \helper_plugin_struct_db $db */
59        $db = plugin_load('helper', 'struct_db');
60        $this->sqlite = $db->getDB(true);
61    }
62
63    /**
64     * Import the data from file.
65     *
66     * @throws StructException
67     */
68    public function import()
69    {
70        // Do the import
71        $this->readHeaders();
72        $this->importCSV();
73    }
74
75    /**
76     * Open a given file path
77     *
78     * The main purpose of this method is to be overridden in a mock for testing
79     *
80     * @param string $file the file path
81     *
82     * @return void
83     */
84    protected function openFile($file)
85    {
86        $this->fh = fopen($file, 'rb');
87        if (!$this->fh) {
88            throw new StructException('Failed to open CSV file for reading');
89        }
90    }
91
92    /**
93     * Get a parsed line from the opened CSV file
94     *
95     * The main purpose of this method is to be overridden in a mock for testing
96     *
97     * @return array|false|null
98     */
99    protected function getLine()
100    {
101        return fgetcsv($this->fh);
102    }
103
104    /**
105     * Read the CSV headers and match it with the Schema columns
106     *
107     * @return array headers of file
108     */
109    protected function readHeaders()
110    {
111        $header = $this->getLine();
112        if (!$header) throw new StructException('Failed to read CSV');
113        $this->line++;
114
115        // FIXME we might have to create a page column first
116        if ($this->type !== 'lookup') {
117            $pageType = new Page(null, 'pid');
118            $pidCol = new Column(0, $pageType, 0, true, $this->schema->getTable());
119            $this->columns[] = $pidCol;
120        }
121
122        foreach ($header as $i => $head) {
123            $col = $this->schema->findColumn($head);
124            if (!$col) continue;
125            if (!$col->isEnabled()) continue;
126            $this->columns[$i] = $col;
127        }
128
129        if (!$this->columns) {
130            throw new StructException('None of the CSV headers matched any of the schema\'s fields');
131        }
132
133        $this->header = $header;
134    }
135
136    /**
137     * Creates the insert string for the single value table
138     *
139     * @return string
140     */
141    protected function getSQLforAllValues()
142    {
143        $colnames = array();
144        $placeholds = array();
145        foreach ($this->columns as $i => $col) {
146            $colnames[] = 'col' . $col->getColref();
147            $placeholds[] = '?';
148        }
149        $colnames = join(', ', $colnames);
150        $placeholds = join(', ', $placeholds);
151        $table = $this->schema->getTable();
152
153        return "INSERT INTO data_$table ($colnames) VALUES ($placeholds)";
154    }
155
156    /**
157     * Creates the insert string for the multi value table
158     *
159     * @return string
160     */
161    protected function getSQLforMultiValue()
162    {
163        $table = $this->schema->getTable();
164        /** @noinspection SqlResolve */
165        return "INSERT INTO multi_$table (pid, colref, row, value) VALUES (?,?,?,?)";
166    }
167
168    /**
169     * Walks through the CSV and imports
170     */
171    protected function importCSV()
172    {
173
174        // FIXME those are hopefully never used and can be removed soon
175        $single = $this->getSQLforAllValues();
176        $multi = $this->getSQLforMultiValue();
177
178        while (($data = $this->getLine()) !== false) {
179//            $this->sqlite->query('BEGIN TRANSACTION');
180            $this->line++;
181            $this->importLine($data, $single, $multi);
182//            $this->sqlite->query('COMMIT TRANSACTION');
183        }
184    }
185
186    /**
187     * The errors that occured during validation
188     *
189     * @return string[] already translated error messages
190     */
191    public function getErrors()
192    {
193        return $this->errors;
194    }
195
196    /**
197     * Validate a single value
198     *
199     * @param Column $col the column of that value
200     * @param mixed &$rawvalue the value, will be fixed according to the type
201     * @return bool true if the data validates, otherwise false
202     */
203    protected function validateValue(Column $col, &$rawvalue)
204    {
205        //by default no validation
206        return true;
207    }
208
209    /**
210     * Read and validate CSV parsed line
211     *
212     * @param &$line
213     */
214    protected function readLine(&$line)
215    {
216        // prepare values for single value table
217        $values = array();
218        foreach ($this->columns as $i => $column) {
219            if (!isset($line[$i])) throw new StructException('Missing field at CSV line %d', $this->line);
220
221            if (!$this->validateValue($column, $line[$i])) return false;
222
223            if ($column->isMulti()) {
224                // FIXME don't split JSON values, they contain commas! we need something more clever
225                // multi values get split on comma
226                $line[$i] = array_map('trim', explode(',', $line[$i]));
227                $values[] = $line[$i][0];
228            } else {
229                $values[] = $line[$i];
230            }
231        }
232        //if no ok don't import
233        return $values;
234    }
235
236    /**
237     * INSERT $values into data_* table
238     *
239     * @param string[] $values
240     * @param string $single SQL for single table
241     *
242     * @return string last_insert_rowid()
243     */
244    protected function insertIntoSingle($values, $single)
245    {
246        $this->sqlite->query($single, $values);
247        $res = $this->sqlite->query('SELECT last_insert_rowid()');
248        $pid = $this->sqlite->res2single($res);
249        $this->sqlite->res_close($res);
250
251        return $pid;
252    }
253
254    /**
255     * INSERT one row into multi_* table
256     *
257     * @param string $multi SQL for multi table
258     * @param $pid string
259     * @param $column string
260     * @param $row string
261     * @param $value string
262     */
263    protected function insertIntoMulti($multi, $pid, $column, $row, $value)
264    {
265        $this->sqlite->query($multi, array($pid, $column->getColref(), $row + 1, $value));
266    }
267
268    /**
269     * Save one CSV line into database
270     *
271     * @param string[] $values parsed line values
272     * @param string $single SQL for single table
273     * @param string $multi SQL for multi table
274     */
275    protected function saveLine($values, $line, $single, $multi)
276    {
277        // insert into single value table (and create pid)
278        $pid = $this->insertIntoSingle($values, $single);
279
280        // insert all the multi values
281        foreach ($this->columns as $i => $column) {
282            if (!$column->isMulti()) continue;
283            foreach ($line[$i] as $row => $value) {
284                $this->insertIntoMulti($multi, $pid, $column, $row, $value);
285            }
286        }
287    }
288
289    /**
290     * Imports one line into the schema
291     *
292     * @param string[] $line the parsed CSV line
293     * @param string $single SQL for single table
294     * @param string $multi SQL for multi table
295     */
296    protected function importLine($line, $single, $multi)
297    {
298        //read values, false if no validation
299        $values = $this->readLine($line);
300
301        if ($values) {
302            // FIXME trying to bypass another custom SQL query string
303//            $this->saveLine($values, $line, $single, $multi);
304
305            $data = array_combine($this->header, $values);
306            // pid is a non-data column and must be supplied to the AccessTable separately
307            $pid = isset($data['pid']) ? $data['pid'] : '';
308            unset($data['pid']);
309            $table = $this->schema->getTable();
310            $access = AccessTable::byTableName($table, $pid);
311
312            /** @var 'helper_plugin_struct $helper */
313            $helper = plugin_load('helper', 'struct');
314            $helper->saveLookupData($access, $data);
315        } else foreach ($this->errors as $error) {
316            msg($error, -1);
317        }
318    }
319}
320