1#!/usr/bin/env php
2<?php
3
4echo "SabreDAV migrate script for version 2.0\n";
5
6if ($argc<2) {
7
8    echo <<<HELLO
9
10This script help you migrate from a pre-2.0 database to 2.0 and later
11
12The 'calendars', 'addressbooks' and 'cards' tables will be upgraded, and new
13tables (calendarchanges, addressbookchanges, propertystorage) will be added.
14
15If you don't use the default PDO CalDAV or CardDAV backend, it's pointless to
16run this script.
17
18Keep in mind that ALTER TABLE commands will be executed. If you have a large
19dataset this may mean that this process takes a while.
20
21Lastly: Make a back-up first. This script has been tested, but the amount of
22potential variants are extremely high, so it's impossible to deal with every
23possible situation.
24
25In the worst case, you will lose all your data. This is not an overstatement.
26
27Usage:
28
29php {$argv[0]} [pdo-dsn] [username] [password]
30
31For example:
32
33php {$argv[0]} "mysql:host=localhost;dbname=sabredav" root password
34php {$argv[0]} sqlite:data/sabredav.db
35
36HELLO;
37
38    exit();
39
40}
41
42// There's a bunch of places where the autoloader could be, so we'll try all of
43// them.
44$paths = [
45    __DIR__ . '/../vendor/autoload.php',
46    __DIR__ . '/../../../autoload.php',
47];
48
49foreach($paths as $path) {
50    if (file_exists($path)) {
51        include $path;
52        break;
53    }
54}
55
56$dsn = $argv[1];
57$user = isset($argv[2])?$argv[2]:null;
58$pass = isset($argv[3])?$argv[3]:null;
59
60echo "Connecting to database: " . $dsn . "\n";
61
62$pdo = new PDO($dsn, $user, $pass);
63$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
64$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
65
66$driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
67
68switch($driver) {
69
70    case 'mysql' :
71        echo "Detected MySQL.\n";
72        break;
73    case 'sqlite' :
74        echo "Detected SQLite.\n";
75        break;
76    default :
77        echo "Error: unsupported driver: " . $driver . "\n";
78        die(-1);
79}
80
81foreach(['calendar', 'addressbook'] as $itemType) {
82
83    $tableName = $itemType . 's';
84    $tableNameOld = $tableName . '_old';
85    $changesTable = $itemType . 'changes';
86
87    echo "Upgrading '$tableName'\n";
88
89    // The only cross-db way to do this, is to just fetch a single record.
90    $row = $pdo->query("SELECT * FROM $tableName LIMIT 1")->fetch();
91
92    if (!$row) {
93
94        echo "No records were found in the '$tableName' table.\n";
95        echo "\n";
96        echo "We're going to rename the old table to $tableNameOld (just in case).\n";
97        echo "and re-create the new table.\n";
98
99        switch($driver) {
100
101            case 'mysql' :
102                $pdo->exec("RENAME TABLE $tableName TO $tableNameOld");
103                switch($itemType) {
104                    case 'calendar' :
105                        $pdo->exec("
106            CREATE TABLE calendars (
107                id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
108                principaluri VARCHAR(100),
109                displayname VARCHAR(100),
110                uri VARCHAR(200),
111                synctoken INT(11) UNSIGNED NOT NULL DEFAULT '1',
112                description TEXT,
113                calendarorder INT(11) UNSIGNED NOT NULL DEFAULT '0',
114                calendarcolor VARCHAR(10),
115                timezone TEXT,
116                components VARCHAR(20),
117                transparent TINYINT(1) NOT NULL DEFAULT '0',
118                UNIQUE(principaluri, uri)
119            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
120                        ");
121                        break;
122                    case 'addressbook' :
123                        $pdo->exec("
124            CREATE TABLE addressbooks (
125                id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
126                principaluri VARCHAR(255),
127                displayname VARCHAR(255),
128                uri VARCHAR(200),
129                description TEXT,
130                synctoken INT(11) UNSIGNED NOT NULL DEFAULT '1',
131                UNIQUE(principaluri, uri)
132            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
133                        ");
134                        break;
135                }
136
137            case 'sqlite' :
138
139                $pdo->exec("ALTER TABLE $tableName RENAME TO $tableNameOld");
140
141                switch($itemType) {
142                    case 'calendar' :
143                        $pdo->exec("
144            CREATE TABLE calendars (
145                id integer primary key asc,
146                principaluri text,
147                displayname text,
148                uri text,
149                synctoken integer,
150                description text,
151                calendarorder integer,
152                calendarcolor text,
153                timezone text,
154                components text,
155                transparent bool
156            );
157                        ");
158                        break;
159                    case 'addressbook' :
160                        $pdo->exec("
161            CREATE TABLE addressbooks (
162                id integer primary key asc,
163                principaluri text,
164                displayname text,
165                uri text,
166                description text,
167                synctoken integer
168            );
169                        ");
170
171                        break;
172                }
173
174        }
175        echo "Creation of 2.0 $tableName table is complete\n";
176
177    } else {
178
179        // Checking if there's a synctoken field already.
180        if (array_key_exists('synctoken', $row)) {
181            echo "The 'synctoken' field already exists in the $tableName table.\n";
182            echo "It's likely you already upgraded, so we're simply leaving\n";
183            echo "the $tableName table alone\n";
184        } else {
185
186            echo "1.8 table schema detected\n";
187            switch($driver) {
188
189                case 'mysql' :
190                    $pdo->exec("ALTER TABLE $tableName ADD synctoken INT(11) UNSIGNED NOT NULL DEFAULT '1'");
191                    $pdo->exec("ALTER TABLE $tableName DROP ctag");
192                    $pdo->exec("UPDATE $tableName SET synctoken = '1'");
193                    break;
194                case 'sqlite' :
195                    $pdo->exec("ALTER TABLE $tableName ADD synctoken integer");
196                    $pdo->exec("UPDATE $tableName SET synctoken = '1'");
197                    echo "Note: there's no easy way to remove fields in sqlite.\n";
198                    echo "The ctag field is no longer used, but it's kept in place\n";
199                    break;
200
201            }
202
203            echo "Upgraded '$tableName' to 2.0 schema.\n";
204
205        }
206
207    }
208
209    try {
210        $pdo->query("SELECT * FROM $changesTable LIMIT 1");
211
212        echo "'$changesTable' already exists. Assuming that this part of the\n";
213        echo "upgrade was already completed.\n";
214
215    } catch (Exception $e) {
216        echo "Creating '$changesTable' table.\n";
217
218        switch($driver) {
219
220            case 'mysql' :
221                $pdo->exec("
222    CREATE TABLE $changesTable (
223        id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
224        uri VARCHAR(200) NOT NULL,
225        synctoken INT(11) UNSIGNED NOT NULL,
226        {$itemType}id INT(11) UNSIGNED NOT NULL,
227        operation TINYINT(1) NOT NULL,
228        INDEX {$itemType}id_synctoken ({$itemType}id, synctoken)
229    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
230
231                ");
232                break;
233            case 'sqlite' :
234                $pdo->exec("
235
236    CREATE TABLE $changesTable (
237        id integer primary key asc,
238        uri text,
239        synctoken integer,
240        {$itemType}id integer,
241        operation bool
242    );
243
244                ");
245                $pdo->exec("CREATE INDEX {$itemType}id_synctoken ON $changesTable ({$itemType}id, synctoken);");
246                break;
247
248        }
249
250    }
251
252}
253
254try {
255    $pdo->query("SELECT * FROM calendarsubscriptions LIMIT 1");
256
257    echo "'calendarsubscriptions' already exists. Assuming that this part of the\n";
258    echo "upgrade was already completed.\n";
259
260} catch (Exception $e) {
261    echo "Creating calendarsubscriptions table.\n";
262
263    switch($driver) {
264
265        case 'mysql' :
266            $pdo->exec("
267CREATE TABLE calendarsubscriptions (
268    id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
269    uri VARCHAR(200) NOT NULL,
270    principaluri VARCHAR(100) NOT NULL,
271    source TEXT,
272    displayname VARCHAR(100),
273    refreshrate VARCHAR(10),
274    calendarorder INT(11) UNSIGNED NOT NULL DEFAULT '0',
275    calendarcolor VARCHAR(10),
276    striptodos TINYINT(1) NULL,
277    stripalarms TINYINT(1) NULL,
278    stripattachments TINYINT(1) NULL,
279    lastmodified INT(11) UNSIGNED,
280    UNIQUE(principaluri, uri)
281);
282            ");
283            break;
284        case 'sqlite' :
285            $pdo->exec("
286
287CREATE TABLE calendarsubscriptions (
288    id integer primary key asc,
289    uri text,
290    principaluri text,
291    source text,
292    displayname text,
293    refreshrate text,
294    calendarorder integer,
295    calendarcolor text,
296    striptodos bool,
297    stripalarms bool,
298    stripattachments bool,
299    lastmodified int
300);
301            ");
302
303            $pdo->exec("CREATE INDEX principaluri_uri ON calendarsubscriptions (principaluri, uri);");
304            break;
305
306    }
307
308}
309
310try {
311    $pdo->query("SELECT * FROM propertystorage LIMIT 1");
312
313    echo "'propertystorage' already exists. Assuming that this part of the\n";
314    echo "upgrade was already completed.\n";
315
316} catch (Exception $e) {
317    echo "Creating propertystorage table.\n";
318
319    switch($driver) {
320
321        case 'mysql' :
322            $pdo->exec("
323CREATE TABLE propertystorage (
324    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
325    path VARBINARY(1024) NOT NULL,
326    name VARBINARY(100) NOT NULL,
327    value MEDIUMBLOB
328);
329            ");
330            $pdo->exec("
331CREATE UNIQUE INDEX path_property ON propertystorage (path(600), name(100));
332            ");
333            break;
334        case 'sqlite' :
335            $pdo->exec("
336CREATE TABLE propertystorage (
337    id integer primary key asc,
338    path TEXT,
339    name TEXT,
340    value TEXT
341);
342            ");
343            $pdo->exec("
344CREATE UNIQUE INDEX path_property ON propertystorage (path, name);
345            ");
346
347            break;
348
349    }
350
351}
352
353echo "Upgrading cards table to 2.0 schema\n";
354
355try {
356
357    $create = false;
358    $row = $pdo->query("SELECT * FROM cards LIMIT 1")->fetch();
359    if (!$row) {
360        $random = mt_rand(1000,9999);
361        echo "There was no data in the cards table, so we're re-creating it\n";
362        echo "The old table will be renamed to cards_old$random, just in case.\n";
363
364        $create = true;
365
366        switch($driver) {
367            case 'mysql' :
368                $pdo->exec("RENAME TABLE cards TO cards_old$random");
369                break;
370            case 'sqlite' :
371                $pdo->exec("ALTER TABLE cards RENAME TO cards_old$random");
372                break;
373
374        }
375    }
376
377} catch (Exception $e) {
378
379    echo "Exception while checking cards table. Assuming that the table does not yet exist.\n";
380    echo "Debug: ", $e->getMessage(), "\n";
381    $create = true;
382
383}
384
385if ($create) {
386    switch($driver) {
387        case 'mysql' :
388            $pdo->exec("
389CREATE TABLE cards (
390    id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
391    addressbookid INT(11) UNSIGNED NOT NULL,
392    carddata MEDIUMBLOB,
393    uri VARCHAR(200),
394    lastmodified INT(11) UNSIGNED,
395    etag VARBINARY(32),
396    size INT(11) UNSIGNED NOT NULL,
397) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
398
399            ");
400            break;
401
402        case 'sqlite' :
403
404            $pdo->exec("
405CREATE TABLE cards (
406    id integer primary key asc,
407    addressbookid integer,
408    carddata blob,
409    uri text,
410    lastmodified integer,
411    etag text,
412    size integer
413);
414            ");
415            break;
416
417    }
418} else {
419    switch($driver) {
420        case 'mysql' :
421            $pdo->exec("
422                ALTER TABLE cards
423                ADD etag VARBINARY(32),
424                ADD size INT(11) UNSIGNED NOT NULL;
425            ");
426            break;
427
428        case 'sqlite' :
429
430            $pdo->exec("
431                ALTER TABLE cards ADD etag text;
432                ALTER TABLE cards ADD size integer;
433            ");
434            break;
435
436    }
437    echo "Reading all old vcards and populating etag and size fields.\n";
438    $result = $pdo->query('SELECT id, carddata FROM cards');
439    $stmt = $pdo->prepare('UPDATE cards SET etag = ?, size = ? WHERE id = ?');
440    while($row = $result->fetch(\PDO::FETCH_ASSOC)) {
441        $stmt->execute([
442            md5($row['carddata']),
443            strlen($row['carddata']),
444            $row['id']
445        ]);
446    }
447
448
449}
450
451echo "Upgrade to 2.0 schema completed.\n";
452