1#!/usr/bin/env php
2<?php
3
4echo "SabreDAV migrate script for version 3.0\n";
5
6if ($argc<2) {
7
8    echo <<<HELLO
9
10This script help you migrate from a pre-3.0 database to 3.0 and later
11
12Changes:
13  * The propertystorage table has changed to allow storage of complex
14    properties.
15  * the vcardurl field in the principals table is no more. This was moved to
16    the propertystorage table.
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
81echo "Upgrading 'propertystorage'\n";
82$addValueType = false;
83try {
84    $result = $pdo->query('SELECT * FROM propertystorage LIMIT 1');
85    $row = $result->fetch(\PDO::FETCH_ASSOC);
86
87    if (!$row) {
88        echo "No data in table. Going to re-create the table.\n";
89        $random = mt_rand(1000,9999);
90        echo "Renaming propertystorage -> propertystorage_old$random and creating new table.\n";
91
92        switch($driver) {
93
94            case 'mysql' :
95                $pdo->exec('RENAME TABLE propertystorage TO propertystorage_old' . $random);
96                $pdo->exec('
97    CREATE TABLE propertystorage (
98        id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
99        path VARBINARY(1024) NOT NULL,
100        name VARBINARY(100) NOT NULL,
101        valuetype INT UNSIGNED,
102        value MEDIUMBLOB
103    );
104                ');
105                $pdo->exec('CREATE UNIQUE INDEX path_property_' . $random . '  ON propertystorage (path(600), name(100));');
106                break;
107            case 'sqlite' :
108                $pdo->exec('ALTER TABLE propertystorage RENAME TO propertystorage_old' . $random);
109                $pdo->exec('
110CREATE TABLE propertystorage (
111    id integer primary key asc,
112    path text,
113    name text,
114    valuetype integer,
115    value blob
116);');
117
118                $pdo->exec('CREATE UNIQUE INDEX path_property_' . $random . ' ON propertystorage (path, name);');
119                break;
120
121        }
122    } elseif (array_key_exists('valuetype', $row)) {
123        echo "valuetype field exists. Assuming that this part of the migration has\n";
124        echo "Already been completed.\n";
125    } else {
126        echo "2.1 schema detected. Going to perform upgrade.\n";
127        $addValueType = true;
128    }
129
130} catch (Exception $e) {
131    echo "Could not find a propertystorage table. Skipping this part of the\n";
132    echo "upgrade.\n";
133    echo $e->getMessage(), "\n";
134}
135
136if ($addValueType) {
137
138    switch($driver) {
139        case 'mysql' :
140            $pdo->exec('ALTER TABLE propertystorage ADD valuetype INT UNSIGNED');
141            break;
142        case 'sqlite' :
143            $pdo->exec('ALTER TABLE propertystorage ADD valuetype INT');
144
145            break;
146    }
147
148    $pdo->exec('UPDATE propertystorage SET valuetype = 1 WHERE valuetype IS NULL ');
149
150}
151
152echo "Migrating vcardurl\n";
153
154$result = $pdo->query('SELECT id, uri, vcardurl FROM principals WHERE vcardurl IS NOT NULL');
155$stmt1 = $pdo->prepare('INSERT INTO propertystorage (path, name, valuetype, value) VALUES (?, ?, 3, ?)');
156
157while($row = $result->fetch(\PDO::FETCH_ASSOC)) {
158
159    // Inserting the new record
160    $stmt1->execute([
161        'addressbooks/' . basename($row['uri']),
162        '{http://calendarserver.org/ns/}me-card',
163        serialize(new Sabre\DAV\Xml\Property\Href($row['vcardurl']))
164    ]);
165
166    echo serialize(new Sabre\DAV\Xml\Property\Href($row['vcardurl']));
167
168}
169
170echo "Done.\n";
171echo "Upgrade to 3.0 schema completed.\n";
172