xref: /plugin/struct/meta/CSVImporter.php (revision 8925ba298f12cecb837c2c249b3a592d289eabe2) 
1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5/**
6 * Class CSVImporter
7 *
8 * Imports CSV data into a lookup schema
9 *
10 * @package dokuwiki\plugin\struct\meta
11 */
12class CSVImporter {
13
14    /** @var  Schema */
15    protected $schema;
16
17    /** @var  resource */
18    protected $fh;
19
20    /** @var  \helper_plugin_sqlite */
21    protected $sqlite;
22
23    /** @var Column[] The single values to store index => col */
24    protected $columns = array();
25
26    /** @var int current line number */
27    protected $line = 0;
28
29    /**
30     * CSVImporter constructor.
31     *
32     * @throws StructException
33     * @param string $table
34     * @param string $file
35     */
36    public function __construct($table, $file) {
37        $this->fh = fopen($file, 'r');
38        if(!$this->fh) throw new StructException('Failed to open CSV file for reading');
39
40        $this->schema = new Schema($table);
41        if(!$this->schema->getId()) throw new StructException('Schema does not exist');
42
43        if(!$this->schema->isLookup()) throw new StructException('CSV import is only valid for Lookup Schemas');
44
45        /** @var \helper_plugin_struct_db $db */
46        $db = plugin_load('helper', 'struct_db');
47        $this->sqlite = $db->getDB(true);
48
49        // Do the import
50        $this->readHeaders();
51        $this->importCSV();
52    }
53
54    /**
55     * Read the CSV headers and match it with the Schema columns
56     */
57    protected function readHeaders() {
58        $header = fgetcsv($this->fh);
59        if(!$header) throw new StructException('Failed to read CSV');
60        $this->line++;
61
62        foreach($header as $i => $head) {
63            $col = $this->schema->findColumn($head);
64            if(!$col) continue;
65            if(!$col->isEnabled()) continue;
66            $this->columns[$i] = $col;
67        }
68
69        if(!$this->columns) {
70            throw new StructException('None of the CSV headers matched any of the schema\'s fields');
71        }
72    }
73
74    /**
75     * Creates the insert string for the single value table
76     *
77     * @return string
78     */
79    protected function getSQLforAllValues() {
80        $colnames = array();
81        $placeholds = array();
82        foreach($this->columns as $i => $col) {
83            $colnames[] = 'col' . $col->getColref();
84            $placeholds[] = '?';
85        }
86        $colnames = join(', ', $colnames);
87        $placeholds = join(', ', $placeholds);
88        $table = $this->schema->getTable();
89
90        return "INSERT INTO data_$table ($colnames) VALUES ($placeholds)";
91    }
92
93    /**
94     * Creates the insert string for the multi value table
95     *
96     * @return string
97     */
98    protected function getSQLforMultiValue() {
99        $table = $this->schema->getTable();
100        /** @noinspection SqlResolve */
101        return "INSERT INTO multi_$table (pid, colref, row, value) VALUES (?,?,?,?)";
102    }
103
104    /**
105     * Walks through the CSV and imports
106     */
107    protected function importCSV() {
108
109        $single = $this->getSQLforAllValues();
110        $multi = $this->getSQLforMultiValue();
111
112        $this->sqlite->query('BEGIN TRANSACTION');
113        while(($data = fgetcsv($this->fh)) !== false) {
114            $this->line++;
115            $this->importLine($data, $single, $multi);
116        }
117        $this->sqlite->query('COMMIT TRANSACTION');
118    }
119
120    /**
121     * Imports one line into the schema
122     *
123     * @param string[] $line the parsed CSV line
124     * @param string $single SQL for single table
125     * @param string $multi SQL for multi table
126     */
127    protected function importLine($line, $single, $multi) {
128        // prepare values for single value table
129        $values = array();
130        foreach($this->columns as $i => $column) {
131            if(!isset($line[$i])) throw new StructException('Missing field at CSV line %d', $this->line);
132
133            if($column->isMulti()) {
134                // multi values get split on comma
135                $line[$i] = array_map('trim', explode(',', $line[$i]));
136                $values[] = $line[$i][0];
137            } else {
138                $values[] = $line[$i];
139            }
140        }
141
142        // insert into single value table (and create pid)
143        $this->sqlite->query($single, $values);
144        $res = $this->sqlite->query('SELECT last_insert_rowid()');
145        $pid = $this->sqlite->res2single($res);
146        $this->sqlite->res_close($res);
147
148        // insert all the multi values
149        foreach($this->columns as $i => $column) {
150            if(!$column->isMulti()) continue;
151            foreach($line[$i] as $row => $value) {
152                $this->sqlite->query($multi, array($pid, $column->getColref(), $row + 1, $value));
153            }
154        }
155    }
156
157}
158