protected function upgrade($sVersionFrom, $sVersionTo)
{
if (version_compare($sVersionFrom, '0.2.3', '<=')) {
throw new \Exception('This version of Baikal does not support upgrading from version 0.2.3 and older. Please request help on Github if this is a problem.');
}
$pdo = $GLOBALS['DB']->getPDO();
if (version_compare($sVersionFrom, '0.3.0', '<')) {
// Upgrading from sabre/dav 1.8 schema to 3.1 schema.
if (defined("PROJECT_DB_MYSQL") && PROJECT_DB_MYSQL === true) {
// MySQL upgrade
// sabre/dav 2.0 changes
foreach (['calendar', 'addressbook'] as $dataType) {
$tableName = $dataType . 's';
$pdo->exec("ALTER TABLE {$tableName} ADD synctoken INT(11) UNSIGNED NOT NULL DEFAULT '1'");
$this->aSuccess[] = 'synctoken was added to ' . $tableName;
$pdo->exec("ALTER TABLE {$tableName} DROP ctag");
$this->aSuccess[] = 'ctag was removed from ' . $tableName;
$changesTable = $dataType . 'changes';
$pdo->exec("\n CREATE TABLE {$changesTable} (\n id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,\n uri VARCHAR(200) NOT NULL,\n synctoken INT(11) UNSIGNED NOT NULL,\n {$dataType}id INT(11) UNSIGNED NOT NULL,\n operation TINYINT(1) NOT NULL,\n INDEX {$dataType}id_synctoken ({$dataType}id, synctoken)\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\n ");
$this->aSuccess[] = $changesTable . ' was created';
}
$pdo->exec("\n CREATE TABLE calendarsubscriptions (\n id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,\n uri VARCHAR(200) NOT NULL,\n principaluri VARCHAR(100) NOT NULL,\n source TEXT,\n displayname VARCHAR(100),\n refreshrate VARCHAR(10),\n calendarorder INT(11) UNSIGNED NOT NULL DEFAULT '0',\n calendarcolor VARCHAR(10),\n striptodos TINYINT(1) NULL,\n stripalarms TINYINT(1) NULL,\n stripattachments TINYINT(1) NULL,\n lastmodified INT(11) UNSIGNED,\n UNIQUE(principaluri, uri)\n );\n ");
$this->aSuccess[] = 'calendarsubscriptions was created';
$pdo->exec("\n ALTER TABLE cards\n ADD etag VARBINARY(32),\n ADD size INT(11) UNSIGNED NOT NULL;\n ");
$this->aSuccess[] = 'etag and size were added to cards';
// sabre/dav 2.1 changes;
$pdo->exec('ALTER TABLE calendarobjects ADD uid VARCHAR(200)');
$this->aSuccess[] = 'uid was added to calendarobjects';
$pdo->exec('
CREATE TABLE IF NOT EXISTS schedulingobjects
(
id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
principaluri VARCHAR(255),
calendardata MEDIUMBLOB,
uri VARCHAR(200),
lastmodified INT(11) UNSIGNED,
etag VARCHAR(32),
size INT(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
');
$this->aSuccess[] = 'schedulingobjects was created';
// sabre/dav 3.0 changes
$pdo->exec("\n CREATE TABLE propertystorage (\n id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,\n path VARBINARY(1024) NOT NULL,\n name VARBINARY(100) NOT NULL,\n valuetype INT UNSIGNED,\n value MEDIUMBLOB\n );\n ");
$pdo->exec('CREATE UNIQUE INDEX path_property ON propertystorage (path(600), name(100));');
$this->aSuccess[] = 'propertystorage was created';
} else {
// SQLite upgrade
// sabre/dav 2.0 changes
foreach (['calendar', 'addressbook'] as $dataType) {
$tableName = $dataType . 's';
// Note: we can't remove the ctag field in sqlite :(;
$pdo->exec("ALTER TABLE {$tableName} ADD synctoken integer");
$this->aSuccess[] = 'synctoken was added to ' . $tableName;
$changesTable = $dataType . 'changes';
$pdo->exec("\n CREATE TABLE {$changesTable} (\n id integer primary key asc,\n uri text,\n synctoken integer,\n {$dataType}id integer,\n operation bool\n );\n ");
$this->aSuccess[] = $changesTable . ' was created';
}
$pdo->exec("\n CREATE TABLE calendarsubscriptions (\n id integer primary key asc,\n uri text,\n principaluri text,\n source text,\n displayname text,\n refreshrate text,\n calendarorder integer,\n calendarcolor text,\n striptodos bool,\n stripalarms bool,\n stripattachments bool,\n lastmodified int\n );\n ");
$this->aSuccess[] = 'calendarsubscriptions was created';
$pdo->exec("CREATE INDEX principaluri_uri ON calendarsubscriptions (principaluri, uri);");
$pdo->exec("\n ALTER TABLE cards ADD etag text;\n ALTER TABLE cards ADD size integer;\n ");
$this->aSuccess[] = 'etag and size were added to cards';
// sabre/dav 2.1 changes;
$pdo->exec('ALTER TABLE calendarobjects ADD uid TEXT');
$this->aSuccess[] = 'uid was added to calendarobjects';
$pdo->exec('
CREATE TABLE IF NOT EXISTS schedulingobjects (
id integer primary key asc,
principaluri text,
calendardata blob,
uri text,
lastmodified integer,
etag text,
size integer
)
');
$this->aSuccess[] = 'schedulingobjects was created';
// sabre/dav 3.0 changes
$pdo->exec("\n CREATE TABLE propertystorage (\n id integer primary key asc,\n path text,\n name text,\n valuetype integer,\n value blob\n );\n ");
$pdo->exec('CREATE UNIQUE INDEX path_property ON propertystorage (path, name);');
$this->aSuccess[] = 'propertystorage was created';
}
// Statements for both SQLite and MySQL
$result = $pdo->query('SELECT id, carddata FROM cards');
$stmt = $pdo->prepare('UPDATE cards SET etag = ?, size = ? WHERE id = ?');
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
$stmt->execute([md5($row['carddata']), strlen($row['carddata']), $row['id']]);
}
$this->aSuccess[] = 'etag and size was recalculated for cards';
$result = $pdo->query('SELECT id, calendardata FROM calendarobjects');
$stmt = $pdo->prepare('UPDATE calendarobjects SET uid = ? WHERE id = ?');
$counter = 0;
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
try {
$vobj = \Sabre\VObject\Reader::read($row['calendardata']);
} catch (\Exception $e) {
$this->aSuccess[] = 'warning: skipped record ' . $row['id'] . '. Error: ' . $e->getMessage();
continue;
}
$uid = null;
$item = $vobj->getBaseComponent();
if (!isset($item->UID)) {
$vobj->destroy();
continue;
}
$uid = (string) $item->UID;
$stmt->execute([$uid, $row['id']]);
$counter++;
$vobj->destroy();
}
$this->aSuccess[] = 'uid was recalculated for calendarobjects';
$result = $pdo->query('SELECT id, uri, vcardurl FROM principals WHERE vcardurl IS NOT NULL');
$stmt1 = $pdo->prepare('INSERT INTO propertystorage (path, name, valuetype, value) VALUES (?, ?, 3, ?)');
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
// Inserting the new record
$stmt1->execute(['addressbooks/' . basename($row['uri']), '{http://calendarserver.org/ns/}me-card', serialize(new \Sabre\DAV\Xml\Property\Href($row['vcardurl']))]);
}
$this->aSuccess[] = 'vcardurl was migrated to the propertystorage system';
}
if (version_compare($sVersionFrom, '0.4.0', '<')) {
// The sqlite schema had issues with both the calendar and
// addressbooks tables. The tables didn't have a DEFAULT '1' for
// the synctoken column. So we're adding it now.
if (!defined("PROJECT_DB_MYSQL") || PROJECT_DB_MYSQL === false) {
$pdo->exec('UPDATE calendars SET synctoken = 1 WHERE synctoken IS NULL');
$tmpTable = '_' . time();
$pdo->exec('ALTER TABLE calendars RENAME TO calendars' . $tmpTable);
$pdo->exec('
CREATE TABLE calendars (
id integer primary key asc NOT NULL,
principaluri text NOT NULL,
displayname text,
uri text NOT NULL,
synctoken integer DEFAULT 1 NOT NULL,
description text,
calendarorder integer,
calendarcolor text,
timezone text,
components text NOT NULL,
transparent bool
);');
$pdo->exec('INSERT INTO calendars SELECT id, principaluri, displayname, uri, synctoken, description, calendarorder, calendarcolor, timezone, components, transparent FROM calendars' . $tmpTable);
$this->aSuccess[] = 'Updated calendars table';
}
}
if (version_compare($sVersionFrom, '0.4.5', '<=')) {
// Similar to upgrading from older than 0.4.5, there were still
// issues with a missing DEFAULT 1 for sthe synctoken field in the
// addressbook.
if (!defined("PROJECT_DB_MYSQL") || PROJECT_DB_MYSQL === false) {
$pdo->exec('UPDATE addressbooks SET synctoken = 1 WHERE synctoken IS NULL');
$tmpTable = '_' . time();
$pdo->exec('ALTER TABLE addressbooks RENAME TO addressbooks' . $tmpTable);
$pdo->exec('
CREATE TABLE addressbooks (
id integer primary key asc NOT NULL,
principaluri text NOT NULL,
displayname text,
uri text NOT NULL,
description text,
synctoken integer DEFAULT 1 NOT NULL
);
');
$pdo->exec('INSERT INTO addressbooks SELECT id, principaluri, displayname, uri, description, synctoken FROM addressbooks' . $tmpTable);
$this->aSuccess[] = 'Updated addressbooks table';
}
}
$this->updateConfiguredVersion($sVersionTo);
return true;
}
