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