4echo
"SabreDAV migrate script for version 2.0\n";
10This script help you migrate from a pre-2.0 database to 2.0 and later
12The
'calendars',
'addressbooks' and
'cards' tables will be upgraded, and
new
13tables (calendarchanges, addressbookchanges, propertystorage) will be added.
15If you don
't use the default PDO CalDAV or CardDAV backend, it's pointless to
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.
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
25In the worst case, you will lose all your data. This is not an overstatement.
29php {$argv[0]} [pdo-dsn] [username] [password]
33php {$argv[0]} "mysql:host=localhost;dbname=sabredav" root password
34php {$argv[0]} sqlite:data/sabredav.db
42// There's a bunch of places where the autoloader could be, so we
'll try all of
45 __DIR__ . '/../vendor/autoload.php
',
46 __DIR__ . '/../../../autoload.php
',
49foreach ($paths as $path) {
50 if (file_exists($path)) {
57$user = isset($argv[2]) ? $argv[2] : null;
58$pass = isset($argv[3]) ? $argv[3] : null;
60echo "Connecting to database: " . $dsn . "\n";
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);
66$driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
71 echo "Detected MySQL.\n";
74 echo "Detected SQLite.\n";
77 echo "Error: unsupported driver: " . $driver . "\n";
81foreach (['calendar
', 'addressbook
'] as $itemType) {
83 $tableName = $itemType . 's
';
84 $tableNameOld = $tableName . '_old
';
85 $changesTable = $itemType . 'changes
';
87 echo "Upgrading '$tableName
'\n";
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();
94 echo "No records were found in the '$tableName
' table.\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
";
102 $pdo->exec("RENAME TABLE $tableName TO $tableNameOld
");
106 CREATE TABLE calendars (
107 id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
108 principaluri VARCHAR(100),
109 displayname VARCHAR(100),
111 synctoken INT(11) UNSIGNED NOT NULL DEFAULT
'1',
113 calendarorder INT(11) UNSIGNED NOT NULL DEFAULT
'0',
114 calendarcolor VARCHAR(10),
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;
124 CREATE TABLE addressbooks (
125 id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
126 principaluri VARCHAR(255),
127 displayname VARCHAR(255),
130 synctoken INT(11) UNSIGNED NOT NULL DEFAULT
'1',
131 UNIQUE(principaluri, uri)
132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
140 $pdo->exec("ALTER TABLE $tableName RENAME TO $tableNameOld
");
145 CREATE TABLE calendars (
146 id integer primary key asc,
152 calendarorder integer,
162 CREATE TABLE addressbooks (
163 id integer primary key asc,
177 echo "Creation of 2.0 $tableName table is complete\n
";
181 // Checking if there's a synctoken field already.
182 if (array_key_exists('synctoken', $row)) {
183 echo "The
'synctoken' field already exists in the $tableName table.\n
";
184 echo "It
's likely you already upgraded, so we're simply leaving\n
";
185 echo "the $tableName table alone\n
";
188 echo "1.8 table schema detected\n
";
192 $pdo->exec("ALTER TABLE $tableName ADD synctoken INT(11) UNSIGNED NOT NULL DEFAULT '1'");
193 $pdo->exec("ALTER TABLE $tableName DROP ctag");
194 $pdo->exec("UPDATE $tableName SET synctoken = '1'");
197 $pdo->exec("ALTER TABLE $tableName ADD synctoken integer");
198 $pdo->exec("UPDATE $tableName SET synctoken = '1'");
199 echo "Note: there's no easy way to
remove fields in sqlite.\n";
200 echo "The ctag field is no longer used, but it's kept in place\n";
205 echo "Upgraded '$tableName' to 2.0 schema.\n";
212 $pdo->query(
"SELECT * FROM $changesTable LIMIT 1");
214 echo
"'$changesTable' already exists. Assuming that this part of the\n";
215 echo
"upgrade was already completed.\n";
217 }
catch (Exception $e) {
218 echo
"Creating '$changesTable' table.\n";
224 CREATE TABLE $changesTable (
225 id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
226 uri VARCHAR(200) NOT NULL,
227 synctoken INT(11) UNSIGNED NOT NULL,
228 {$itemType}id INT(11) UNSIGNED NOT NULL,
229 operation TINYINT(1) NOT NULL,
230 INDEX {$itemType}id_synctoken ({$itemType}id, synctoken)
231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
238 CREATE TABLE $changesTable (
239 id integer primary key asc,
242 {$itemType}id integer,
247 $pdo->exec(
"CREATE INDEX {$itemType}id_synctoken ON $changesTable ({$itemType}id, synctoken);");
257 $pdo->query(
"SELECT * FROM calendarsubscriptions LIMIT 1");
259 echo
"'calendarsubscriptions' already exists. Assuming that this part of the\n";
260 echo
"upgrade was already completed.\n";
262}
catch (Exception $e) {
263 echo
"Creating calendarsubscriptions table.\n";
269CREATE TABLE calendarsubscriptions (
270 id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
271 uri VARCHAR(200) NOT NULL,
272 principaluri VARCHAR(100) NOT NULL,
274 displayname VARCHAR(100),
275 refreshrate VARCHAR(10),
276 calendarorder INT(11) UNSIGNED NOT NULL DEFAULT '0',
277 calendarcolor VARCHAR(10),
278 striptodos TINYINT(1) NULL,
279 stripalarms TINYINT(1) NULL,
280 stripattachments TINYINT(1) NULL,
281 lastmodified INT(11) UNSIGNED,
282 UNIQUE(principaluri, uri)
289CREATE TABLE calendarsubscriptions (
290 id integer primary key asc,
296 calendarorder integer,
300 stripattachments bool,
305 $pdo->exec(
"CREATE INDEX principaluri_uri ON calendarsubscriptions (principaluri, uri);");
313 $pdo->query(
"SELECT * FROM propertystorage LIMIT 1");
315 echo
"'propertystorage' already exists. Assuming that this part of the\n";
316 echo
"upgrade was already completed.\n";
318}
catch (Exception $e) {
319 echo
"Creating propertystorage table.\n";
325CREATE TABLE propertystorage (
326 id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
327 path VARBINARY(1024) NOT NULL,
328 name VARBINARY(100) NOT NULL,
333CREATE UNIQUE INDEX path_property ON propertystorage (path(600), name(100));
338CREATE TABLE propertystorage (
339 id integer primary key asc,
346CREATE UNIQUE INDEX path_property ON propertystorage (path, name);
355echo
"Upgrading cards table to 2.0 schema\n";
360 $row =
$pdo->query(
"SELECT * FROM cards LIMIT 1")->fetch();
362 $random = mt_rand(1000, 9999);
363 echo
"There was no data in the cards table, so we're re-creating it\n";
364 echo
"The old table will be renamed to cards_old$random, just in case.\n";
370 $pdo->exec(
"RENAME TABLE cards TO cards_old$random");
373 $pdo->exec(
"ALTER TABLE cards RENAME TO cards_old$random");
379}
catch (Exception $e) {
381 echo
"Exception while checking cards table. Assuming that the table does not yet exist.\n";
382 echo
"Debug: ", $e->getMessage(),
"\n";
392 id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
393 addressbookid INT(11) UNSIGNED NOT NULL,
396 lastmodified INT(11) UNSIGNED,
398 size INT(11) UNSIGNED NOT NULL
399) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
408 id integer primary key asc,
409 addressbookid integer,
412 lastmodified integer,
425 ADD etag VARBINARY(32),
426 ADD size INT(11) UNSIGNED NOT NULL;
433 ALTER TABLE cards ADD etag text;
434 ALTER TABLE cards ADD size integer;
439 echo
"Reading all old vcards and populating etag and size fields.\n";
441 $stmt =
$pdo->prepare(
'UPDATE cards SET etag = ?, size = ? WHERE id = ?');
444 md5(
$row[
'carddata']),
445 strlen(
$row[
'carddata']),
453echo
"Upgrade to 2.0 schema completed.\n";
An exception for terminatinating execution or to throw for unit testing.
switch( $driver) foreach(['calendar', 'addressbook'] as $itemType) try