1<?php
2
3namespace Sabre\DAV\PropertyStorage\Backend;
4
5use Sabre\DAV\PropFind;
6use Sabre\DAV\PropPatch;
7use Sabre\DAV\Xml\Property\Complex;
8
9/**
10 * PropertyStorage PDO backend.
11 *
12 * This backend class uses a PDO-enabled database to store webdav properties.
13 * Both sqlite and mysql have been tested.
14 *
15 * The database structure can be found in the examples/sql/ directory.
16 *
17 * @copyright Copyright (C) fruux GmbH (https://fruux.com/)
18 * @author Evert Pot (http://evertpot.com/)
19 * @license http://sabre.io/license/ Modified BSD License
20 */
21class PDO implements BackendInterface {
22
23    /**
24     * Value is stored as string.
25     */
26    const VT_STRING = 1;
27
28    /**
29     * Value is stored as XML fragment.
30     */
31    const VT_XML = 2;
32
33    /**
34     * Value is stored as a property object.
35     */
36    const VT_OBJECT = 3;
37
38    /**
39     * PDO
40     *
41     * @var \PDO
42     */
43    protected $pdo;
44
45    /**
46     * PDO table name we'll be using
47     *
48     * @var string
49     */
50    public $tableName = 'propertystorage';
51
52    /**
53     * Creates the PDO property storage engine
54     *
55     * @param \PDO $pdo
56     */
57    function __construct(\PDO $pdo) {
58
59        $this->pdo = $pdo;
60
61    }
62
63    /**
64     * Fetches properties for a path.
65     *
66     * This method received a PropFind object, which contains all the
67     * information about the properties that need to be fetched.
68     *
69     * Usually you would just want to call 'get404Properties' on this object,
70     * as this will give you the _exact_ list of properties that need to be
71     * fetched, and haven't yet.
72     *
73     * However, you can also support the 'allprops' property here. In that
74     * case, you should check for $propFind->isAllProps().
75     *
76     * @param string $path
77     * @param PropFind $propFind
78     * @return void
79     */
80    function propFind($path, PropFind $propFind) {
81
82        if (!$propFind->isAllProps() && count($propFind->get404Properties()) === 0) {
83            return;
84        }
85
86        $query = 'SELECT name, value, valuetype FROM ' . $this->tableName . ' WHERE path = ?';
87        $stmt = $this->pdo->prepare($query);
88        $stmt->execute([$path]);
89
90        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
91            if (gettype($row['value']) === 'resource') {
92                $row['value'] = stream_get_contents($row['value']);
93            }
94            switch ($row['valuetype']) {
95                case null :
96                case self::VT_STRING :
97                    $propFind->set($row['name'], $row['value']);
98                    break;
99                case self::VT_XML :
100                    $propFind->set($row['name'], new Complex($row['value']));
101                    break;
102                case self::VT_OBJECT :
103                    $propFind->set($row['name'], unserialize($row['value']));
104                    break;
105            }
106        }
107
108    }
109
110    /**
111     * Updates properties for a path
112     *
113     * This method received a PropPatch object, which contains all the
114     * information about the update.
115     *
116     * Usually you would want to call 'handleRemaining' on this object, to get;
117     * a list of all properties that need to be stored.
118     *
119     * @param string $path
120     * @param PropPatch $propPatch
121     * @return void
122     */
123    function propPatch($path, PropPatch $propPatch) {
124
125        $propPatch->handleRemaining(function($properties) use ($path) {
126
127
128            if ($this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME) === 'pgsql') {
129
130                $updateSql = <<<SQL
131INSERT INTO {$this->tableName} (path, name, valuetype, value)
132VALUES (:path, :name, :valuetype, :value)
133ON CONFLICT (path, name)
134DO UPDATE SET valuetype = :valuetype, value = :value
135SQL;
136
137
138            } else {
139                $updateSql = <<<SQL
140REPLACE INTO {$this->tableName} (path, name, valuetype, value)
141VALUES (:path, :name, :valuetype, :value)
142SQL;
143
144            }
145
146            $updateStmt = $this->pdo->prepare($updateSql);
147            $deleteStmt = $this->pdo->prepare("DELETE FROM " . $this->tableName . " WHERE path = ? AND name = ?");
148
149            foreach ($properties as $name => $value) {
150
151                if (!is_null($value)) {
152                    if (is_scalar($value)) {
153                        $valueType = self::VT_STRING;
154                    } elseif ($value instanceof Complex) {
155                        $valueType = self::VT_XML;
156                        $value = $value->getXml();
157                    } else {
158                        $valueType = self::VT_OBJECT;
159                        $value = serialize($value);
160                    }
161
162                    $updateStmt->bindParam('path', $path, \PDO::PARAM_STR);
163                    $updateStmt->bindParam('name', $name, \PDO::PARAM_STR);
164                    $updateStmt->bindParam('valuetype', $valueType, \PDO::PARAM_INT);
165                    $updateStmt->bindParam('value', $value, \PDO::PARAM_LOB);
166
167                    $updateStmt->execute();
168
169                } else {
170                    $deleteStmt->execute([$path, $name]);
171                }
172
173            }
174
175            return true;
176
177        });
178
179    }
180
181    /**
182     * This method is called after a node is deleted.
183     *
184     * This allows a backend to clean up all associated properties.
185     *
186     * The delete method will get called once for the deletion of an entire
187     * tree.
188     *
189     * @param string $path
190     * @return void
191     */
192    function delete($path) {
193
194        $stmt = $this->pdo->prepare("DELETE FROM " . $this->tableName . "  WHERE path = ? OR path LIKE ? ESCAPE '='");
195        $childPath = strtr(
196            $path,
197            [
198                '=' => '==',
199                '%' => '=%',
200                '_' => '=_'
201            ]
202        ) . '/%';
203
204        $stmt->execute([$path, $childPath]);
205
206    }
207
208    /**
209     * This method is called after a successful MOVE
210     *
211     * This should be used to migrate all properties from one path to another.
212     * Note that entire collections may be moved, so ensure that all properties
213     * for children are also moved along.
214     *
215     * @param string $source
216     * @param string $destination
217     * @return void
218     */
219    function move($source, $destination) {
220
221        // I don't know a way to write this all in a single sql query that's
222        // also compatible across db engines, so we're letting PHP do all the
223        // updates. Much slower, but it should still be pretty fast in most
224        // cases.
225        $select = $this->pdo->prepare('SELECT id, path FROM ' . $this->tableName . '  WHERE path = ? OR path LIKE ?');
226        $select->execute([$source, $source . '/%']);
227
228        $update = $this->pdo->prepare('UPDATE ' . $this->tableName . ' SET path = ? WHERE id = ?');
229        while ($row = $select->fetch(\PDO::FETCH_ASSOC)) {
230
231            // Sanity check. SQL may select too many records, such as records
232            // with different cases.
233            if ($row['path'] !== $source && strpos($row['path'], $source . '/') !== 0) continue;
234
235            $trailingPart = substr($row['path'], strlen($source) + 1);
236            $newPath = $destination;
237            if ($trailingPart) {
238                $newPath .= '/' . $trailingPart;
239            }
240            $update->execute([$newPath, $row['id']]);
241
242        }
243
244    }
245
246}
247