ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
migrateto20.php
Go to the documentation of this file.
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 break;
137
138 case 'sqlite' :
139
140 $pdo->exec("ALTER TABLE $tableName RENAME TO $tableNameOld");
141
142 switch ($itemType) {
143 case 'calendar' :
144 $pdo->exec("
145 CREATE TABLE calendars (
146 id integer primary key asc,
147 principaluri text,
148 displayname text,
149 uri text,
150 synctoken integer,
151 description text,
152 calendarorder integer,
153 calendarcolor text,
154 timezone text,
155 components text,
156 transparent bool
157 );
158 ");
159 break;
160 case 'addressbook' :
161 $pdo->exec("
162 CREATE TABLE addressbooks (
163 id integer primary key asc,
164 principaluri text,
165 displayname text,
166 uri text,
167 description text,
168 synctoken integer
169 );
170 ");
171
172 break;
173 }
174 break;
175
176 }
177 echo "Creation of 2.0 $tableName table is complete\n";
178
179 } else {
180
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";
186 } else {
187
188 echo "1.8 table schema detected\n";
189 switch ($driver) {
190
191 case 'mysql' :
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'");
195 break;
196 case 'sqlite' :
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";
201 break;
202
203 }
204
205 echo "Upgraded '$tableName' to 2.0 schema.\n";
206
207 }
208
209 }
210
211 try {
212 $pdo->query("SELECT * FROM $changesTable LIMIT 1");
213
214 echo "'$changesTable' already exists. Assuming that this part of the\n";
215 echo "upgrade was already completed.\n";
216
217 } catch (Exception $e) {
218 echo "Creating '$changesTable' table.\n";
219
220 switch ($driver) {
221
222 case 'mysql' :
223 $pdo->exec("
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;
232
233 ");
234 break;
235 case 'sqlite' :
236 $pdo->exec("
237
238 CREATE TABLE $changesTable (
239 id integer primary key asc,
240 uri text,
241 synctoken integer,
242 {$itemType}id integer,
243 operation bool
244 );
245
246 ");
247 $pdo->exec("CREATE INDEX {$itemType}id_synctoken ON $changesTable ({$itemType}id, synctoken);");
248 break;
249
250 }
251
252 }
253
254}
255
256try {
257 $pdo->query("SELECT * FROM calendarsubscriptions LIMIT 1");
258
259 echo "'calendarsubscriptions' already exists. Assuming that this part of the\n";
260 echo "upgrade was already completed.\n";
261
262} catch (Exception $e) {
263 echo "Creating calendarsubscriptions table.\n";
264
265 switch ($driver) {
266
267 case 'mysql' :
268 $pdo->exec("
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,
273 source TEXT,
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)
283);
284 ");
285 break;
286 case 'sqlite' :
287 $pdo->exec("
288
289CREATE TABLE calendarsubscriptions (
290 id integer primary key asc,
291 uri text,
292 principaluri text,
293 source text,
294 displayname text,
295 refreshrate text,
296 calendarorder integer,
297 calendarcolor text,
298 striptodos bool,
299 stripalarms bool,
300 stripattachments bool,
301 lastmodified int
302);
303 ");
304
305 $pdo->exec("CREATE INDEX principaluri_uri ON calendarsubscriptions (principaluri, uri);");
306 break;
307
308 }
309
310}
311
312try {
313 $pdo->query("SELECT * FROM propertystorage LIMIT 1");
314
315 echo "'propertystorage' already exists. Assuming that this part of the\n";
316 echo "upgrade was already completed.\n";
317
318} catch (Exception $e) {
319 echo "Creating propertystorage table.\n";
320
321 switch ($driver) {
322
323 case 'mysql' :
324 $pdo->exec("
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,
329 value MEDIUMBLOB
330);
331 ");
332 $pdo->exec("
333CREATE UNIQUE INDEX path_property ON propertystorage (path(600), name(100));
334 ");
335 break;
336 case 'sqlite' :
337 $pdo->exec("
338CREATE TABLE propertystorage (
339 id integer primary key asc,
340 path TEXT,
341 name TEXT,
342 value TEXT
343);
344 ");
345 $pdo->exec("
346CREATE UNIQUE INDEX path_property ON propertystorage (path, name);
347 ");
348
349 break;
350
351 }
352
353}
354
355echo "Upgrading cards table to 2.0 schema\n";
356
357try {
358
359 $create = false;
360 $row = $pdo->query("SELECT * FROM cards LIMIT 1")->fetch();
361 if (!$row) {
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";
365
366 $create = true;
367
368 switch ($driver) {
369 case 'mysql' :
370 $pdo->exec("RENAME TABLE cards TO cards_old$random");
371 break;
372 case 'sqlite' :
373 $pdo->exec("ALTER TABLE cards RENAME TO cards_old$random");
374 break;
375
376 }
377 }
378
379} catch (Exception $e) {
380
381 echo "Exception while checking cards table. Assuming that the table does not yet exist.\n";
382 echo "Debug: ", $e->getMessage(), "\n";
383 $create = true;
384
385}
386
387if ($create) {
388 switch ($driver) {
389 case 'mysql' :
390 $pdo->exec("
391CREATE TABLE cards (
392 id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
393 addressbookid INT(11) UNSIGNED NOT NULL,
394 carddata MEDIUMBLOB,
395 uri VARCHAR(200),
396 lastmodified INT(11) UNSIGNED,
397 etag VARBINARY(32),
398 size INT(11) UNSIGNED NOT NULL
399) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
400
401 ");
402 break;
403
404 case 'sqlite' :
405
406 $pdo->exec("
407CREATE TABLE cards (
408 id integer primary key asc,
409 addressbookid integer,
410 carddata blob,
411 uri text,
412 lastmodified integer,
413 etag text,
414 size integer
415);
416 ");
417 break;
418
419 }
420} else {
421 switch ($driver) {
422 case 'mysql' :
423 $pdo->exec("
424 ALTER TABLE cards
425 ADD etag VARBINARY(32),
426 ADD size INT(11) UNSIGNED NOT NULL;
427 ");
428 break;
429
430 case 'sqlite' :
431
432 $pdo->exec("
433 ALTER TABLE cards ADD etag text;
434 ALTER TABLE cards ADD size integer;
435 ");
436 break;
437
438 }
439 echo "Reading all old vcards and populating etag and size fields.\n";
440 $result = $pdo->query('SELECT id, carddata FROM cards');
441 $stmt = $pdo->prepare('UPDATE cards SET etag = ?, size = ? WHERE id = ?');
442 while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
443 $stmt->execute([
444 md5($row['carddata']),
445 strlen($row['carddata']),
446 $row['id']
447 ]);
448 }
449
450
451}
452
453echo "Upgrade to 2.0 schema completed.\n";
back()
Definition: back.php:2
An exception for terminatinating execution or to throw for unit testing.
$result
$driver
Definition: migrateto20.php:66
$pdo
Definition: migrateto20.php:62
$row
switch( $driver) foreach(['calendar', 'addressbook'] as $itemType) try
$stmt
run()
Runs the loop.
Definition: functions.php:130
remove()
Definition: remove.php:2
$errors fields
Definition: imgupload.php:51
up()
Definition: up.php:2