1#!/usr/bin/env php
2<?php
3
4echo "SabreDAV migrate script for version 1.7\n";
5
6if ($argc<2) {
7
8    echo <<<HELLO
9
10This script help you migrate from a pre-1.7 database to 1.7 and later\n
11Both the 'calendarobjects' and 'calendars' tables will be upgraded.
12
13If you do not have this table, or don't use the default PDO CalDAV backend
14it's pointless to run this script.
15
16Keep in mind that some processing will be done on every single record of this
17table and in addition, ALTER TABLE commands will be executed.
18If you have a large calendarobjects table, this may mean that this process
19takes a while.
20
21Usage:
22
23php {$argv[0]} [pdo-dsn] [username] [password]
24
25For example:
26
27php {$argv[0]} "mysql:host=localhost;dbname=sabredav" root password
28php {$argv[0]} sqlite:data/sabredav.db
29
30HELLO;
31
32    exit();
33
34}
35
36// There's a bunch of places where the autoloader could be, so we'll try all of
37// them.
38$paths = array(
39    __DIR__ . '/../vendor/autoload.php',
40    __DIR__ . '/../../../autoload.php',
41);
42
43foreach($paths as $path) {
44    if (file_exists($path)) {
45        include $path;
46        break;
47    }
48}
49
50$dsn = $argv[1];
51$user = isset($argv[2])?$argv[2]:null;
52$pass = isset($argv[3])?$argv[3]:null;
53
54echo "Connecting to database: " . $dsn . "\n";
55
56$pdo = new PDO($dsn, $user, $pass);
57$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
58$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
59
60echo "Validating existing table layout\n";
61
62// The only cross-db way to do this, is to just fetch a single record.
63$row = $pdo->query("SELECT * FROM calendarobjects LIMIT 1")->fetch();
64
65if (!$row) {
66    echo "Error: This database did not have any records in the calendarobjects table, you should just recreate the table.\n";
67    exit(-1);
68}
69
70$requiredFields = array(
71    'id',
72    'calendardata',
73    'uri',
74    'calendarid',
75    'lastmodified',
76);
77
78foreach($requiredFields as $requiredField) {
79    if (!array_key_exists($requiredField,$row)) {
80        echo "Error: The current 'calendarobjects' table was missing a field we expected to exist.\n";
81        echo "For safety reasons, this process is stopped.\n";
82        exit(-1);
83    }
84}
85
86$fields17 = array(
87    'etag',
88    'size',
89    'componenttype',
90    'firstoccurence',
91    'lastoccurence',
92);
93
94$found = 0;
95foreach($fields17 as $field) {
96    if (array_key_exists($field, $row)) {
97        $found++;
98    }
99}
100
101if ($found === 0) {
102    echo "The database had the 1.6 schema. Table will now be altered.\n";
103    echo "This may take some time for large tables\n";
104
105    switch($pdo->getAttribute(PDO::ATTR_DRIVER_NAME)) {
106
107        case 'mysql' :
108
109            $pdo->exec(<<<SQL
110ALTER TABLE calendarobjects
111ADD etag VARCHAR(32),
112ADD size INT(11) UNSIGNED,
113ADD componenttype VARCHAR(8),
114ADD firstoccurence INT(11) UNSIGNED,
115ADD lastoccurence INT(11) UNSIGNED
116SQL
117        );
118            break;
119            case 'sqlite' :
120                $pdo->exec('ALTER TABLE calendarobjects ADD etag text');
121                $pdo->exec('ALTER TABLE calendarobjects ADD size integer');
122                $pdo->exec('ALTER TABLE calendarobjects ADD componenttype TEXT');
123                $pdo->exec('ALTER TABLE calendarobjects ADD firstoccurence integer');
124                $pdo->exec('ALTER TABLE calendarobjects ADD lastoccurence integer');
125                break;
126
127        default :
128            die('This upgrade script does not support this driver (' . $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) . ")\n");
129
130    }
131    echo "Database schema upgraded.\n";
132
133} elseif ($found === 5) {
134
135    echo "Database already had the 1.7 schema\n";
136
137} else {
138
139    echo "The database had $found out of 5 from the changes for 1.7. This is scary and unusual, so we have to abort.\n";
140    echo "You can manually try to upgrade the schema, and then run this script again.\n";
141    exit(-1);
142
143}
144
145echo "Now, we need to parse every record and pull out some information.\n";
146
147$result = $pdo->query('SELECT id, calendardata FROM calendarobjects');
148$stmt = $pdo->prepare('UPDATE calendarobjects SET etag = ?, size = ?, componenttype = ?, firstoccurence = ?, lastoccurence = ? WHERE id = ?');
149
150echo "Total records found: " . $result->rowCount() . "\n";
151$done = 0;
152$total = $result->rowCount();
153while($row = $result->fetch()) {
154
155    try {
156        $newData = getDenormalizedData($row['calendardata']);
157    } catch (Exception $e) {
158        echo "===\nException caught will trying to parser calendarobject.\n";
159        echo "Error message: " . $e->getMessage() . "\n";
160        echo "Record id: " . $row['id'] . "\n";
161        echo "This record is ignored, you should inspect it to see if there's anything wrong.\n===\n";
162        continue;
163    }
164    $stmt->execute(array(
165        $newData['etag'],
166        $newData['size'],
167        $newData['componentType'],
168        $newData['firstOccurence'],
169        $newData['lastOccurence'],
170        $row['id'],
171    ));
172    $done++;
173
174    if ($done % 500 === 0) {
175        echo "Completed: $done / $total\n";
176    }
177}
178echo "Completed: $done / $total\n";
179
180echo "Checking the calendars table needs changes.\n";
181$row = $pdo->query("SELECT * FROM calendars LIMIT 1")->fetch();
182
183if (array_key_exists('transparent', $row)) {
184
185    echo "The calendars table is already up to date\n";
186
187} else {
188
189    echo "Adding the 'transparent' field to the calendars table\n";
190
191    switch($pdo->getAttribute(PDO::ATTR_DRIVER_NAME)) {
192
193        case 'mysql' :
194            $pdo->exec("ALTER TABLE calendars ADD transparent TINYINT(1) NOT NULL DEFAULT '0'");
195            break;
196        case 'sqlite' :
197            $pdo->exec("ALTER TABLE calendars ADD transparent bool");
198            break;
199
200        default :
201            die('This upgrade script does not support this driver (' . $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) . ")\n");
202
203    }
204
205}
206
207echo "Process completed!\n";
208
209/**
210 * Parses some information from calendar objects, used for optimized
211 * calendar-queries.
212 *
213 * Blantently copied from Sabre\CalDAV\Backend\PDO
214 *
215 * Returns an array with the following keys:
216 *   * etag
217 *   * size
218 *   * componentType
219 *   * firstOccurence
220 *   * lastOccurence
221 *
222 * @param string $calendarData
223 * @return array
224 */
225function getDenormalizedData($calendarData) {
226
227    $vObject = \Sabre\VObject\Reader::read($calendarData);
228    $componentType = null;
229    $component = null;
230    $firstOccurence = null;
231    $lastOccurence = null;
232    foreach($vObject->getComponents() as $component) {
233        if ($component->name!=='VTIMEZONE') {
234            $componentType = $component->name;
235            break;
236        }
237    }
238    if (!$componentType) {
239        throw new \Sabre\DAV\Exception\BadRequest('Calendar objects must have a VJOURNAL, VEVENT or VTODO component');
240    }
241    if ($componentType === 'VEVENT') {
242        $firstOccurence = $component->DTSTART->getDateTime()->getTimeStamp();
243        // Finding the last occurence is a bit harder
244        if (!isset($component->RRULE)) {
245            if (isset($component->DTEND)) {
246                $lastOccurence = $component->DTEND->getDateTime()->getTimeStamp();
247            } elseif (isset($component->DURATION)) {
248                $endDate = clone $component->DTSTART->getDateTime();
249                $endDate->add(\Sabre\VObject\DateTimeParser::parse($component->DURATION->value));
250                $lastOccurence = $endDate->getTimeStamp();
251            } elseif (!$component->DTSTART->hasTime()) {
252                $endDate = clone $component->DTSTART->getDateTime();
253                $endDate->modify('+1 day');
254                $lastOccurence = $endDate->getTimeStamp();
255            } else {
256                $lastOccurence = $firstOccurence;
257            }
258        } else {
259            $it = new \Sabre\VObject\Recur\EventIterator($vObject, (string)$component->UID);
260            $maxDate = new DateTime(\Sabre\CalDAV\Backend\PDO::MAX_DATE);
261            if ($it->isInfinite()) {
262                $lastOccurence = $maxDate->getTimeStamp();
263            } else {
264                $end = $it->getDtEnd();
265                while($it->valid() && $end < $maxDate) {
266                    $end = $it->getDtEnd();
267                    $it->next();
268
269                }
270                $lastOccurence = $end->getTimeStamp();
271            }
272
273        }
274    }
275
276    return array(
277        'etag' => md5($calendarData),
278        'size' => strlen($calendarData),
279        'componentType' => $componentType,
280        'firstOccurence' => $firstOccurence,
281        'lastOccurence'  => $lastOccurence,
282    );
283
284}
285