Migrationsscript für Sally 0.5/0.6/0.7-Updates

migrate.php

<?php
/*
 * Copyright (c) 2012, webvariants GbR, http://www.webvariants.de
 *
 * This file is released under the terms of the MIT license. You can find the
 * complete text in the attached LICENSE file or online at:
 *
 * http://www.opensource.org/licenses/mit-license.php
 */

// Migrationsscript für Sally 0.4/0.5/0.7 -> 0.7
// http://docs.webvariants.de/sallycms/latest/appendix/0.6/migrate.html

// Warnung!
// Durch die Schema-Änderungen in 0.6 liegen nun alle Slice-Werte im
// gleichen Namensraum. Wenn es im Projekt innerhalb eines Moduls
// sowohl eine Medialiste foo und ein Input-Feld namens foo gab, so
// tritt nun ein Konflikt auf. Um diesen zu bereinigen müssen alle
// Duplikate einfach vorher umbenannt (z.B. die Werte der Medialiste
// in foo_media) werden.
// Vor den Schema-Änderungen hier im Script ist ein Bereich, in dem
// via SQL die Namenskonflikte gelöst werden können, indem die finder
// von Slice-Werten umbenannt werden.



// Konfiguration.
// Hier müssen die Zugangsdaten der Projektdaten eintragen werden. Dieses
// Script wird im Verlauf der Arbeit die Datenbank *in-place* migrieren.
// Da sie aber eh auf dem Master-Dump basiert, kann sie im Fehlerfall leicht
// wiederhergestellt werden.

$dbuser = '<<NUTZERNAME>>';
$dbpass = '<<PASSWORT>>';
$dbname = '<<NAME DER ZIELDATENBANK>>';

// Quellversion
// Setze diese Variable auf 0.4, 0.5 oder 0.6, um die automatische
// Erkennung anhand des Datenbank-Schemas zu umgehen. In den meisten Fällen
// sollte die autom. Erkennung aktiviert sein.

$srcVersion = null;

// Zielversion
// Setze diese Variable auf 0.6 oder 0.7, je nach dem, zu welchem Stand die
// Datenbank migriert werden soll.

$dstVersion = 0.7;

// UTF-8-Kodierung (0.5 -> 0.6).
// Liste aller Tabellen, deren Inhalte nach UTF-8 rekodiert werden müssen.
// Jedes Element im Array nutzt als Key den Tabellennamen und besteht
// aus einer Liste der Spalten, die den Primärschlüssel darstellen ('pk')
// und eine Liste aller Spalten, deren Inhalte re-kodiert werden sollen
// ('cols'). Numerische Spalten müssen beispielsweise nicht in 'cols'
// auftauchen.
// Die Liste kann beliebig auf weitere AddOn-Tabellen ausgeweitet werden.

$utf8Tables = array(
	'sly_article' => array(
		'pk'   => array('id', 'clang'),
		'cols' => array('name', 'catname')
	),
	'sly_clang' => array(
		'pk'   => array('id'),
		'cols' => array('name')
	),
	'sly_file' => array(
		'pk'   => array('id'),
		'cols' => array('originalname', 'title')
	),
	'sly_file_category' => array(
		'pk'   => array('id'),
		'cols' => array('name')
	),
	'sly_slice_value' => array(
		'pk'   => array('id'),
		'cols' => array('value')
	),
	'sly_registry' => array(
		'pk'   => array('name'),
		'cols' => array('value')
	),
	'sly_user' => array(
		'pk'   => array('id'),
		'cols' => array('name', 'description', 'login')
	),

	// ADDONS

	'sly_wv2_meta' => array( // metainfo
		'pk'   => array('object_id', 'metainfo', 'clang', 'meta_type'),
		'cols' => array('value'),
		'trx'  => true
	),

	'sly_wv19_guestbook_values' => array( // guestbook (old name), gets renamed later on
		'pk'   => array('id'),
		'cols' => array('value'),
		'trx'  => true
	),

	'sly_wv19_entries_values' => array( // guestbook (new name)
		'pk'   => array('id'),
		'cols' => array('value'),
		'trx'  => true
	),

	'sly_wv32_categories' => array( // varilog
		'pk'   => array('id', 'clang'),
		'cols' => array('label')
	),

	'sly_wv32_categories' => array( // varilog
		'pk'   => array('id', 'clang'),
		'cols' => array('label')
	),

	'sly_wv32_linkbacks_in' => array( // varilog
		'pk'   => array('article_id', 'url', 'type'),
		'cols' => array('blog_name', 'title', 'excerpt')
	)
);

// Umstellung auf native Datums-Typen (0.6 -> 0.7).
// Liste aller Tabellen und ihrer Spalten, die von UNIX-Timestamps auf DATETIME
// umgebaut werden sollen. Die Spaltenliste pro Tabelle ist jeweils ein Mapping
// vom Namen der Spalte auf $allowNull, also entweder auf true oder auf false.
// Die Liste kann beliebig auf weitere AddOn-Tabellen ausgeweitet werden.

$datetimeTables = array(
	'sly_article'       => array('createdate' => false, 'updatedate' => false),
	'sly_article_slice' => array('createdate' => false, 'updatedate' => false),
	'sly_file'          => array('createdate' => false, 'updatedate' => false),
	'sly_file_category' => array('createdate' => false, 'updatedate' => false),
	'sly_user'          => array('createdate' => false, 'updatedate' => false, 'lasttry' => true),
);

////////////////////////////////////////////////////////////////////////////////
// mini library

$_rowsLeft  = null;
$_lastPrint = null;
$_step      = null;

function latin() {
	mysql_query('SET NAMES latin1');
}

function utf8() {
	mysql_query('SET NAMES utf8');
}

function escape($s) {
	return mysql_real_escape_string($s);
}

function quote($s) {
	if ($s === null) return 'NULL';

	if (!preg_match('/^[0-9]+$/', $s)) {
		$s = "'".escape($s)."'";
	}

	return $s;
}

function initCountdown($result) {
	global $_rowsLeft, $_step, $_lastPrint;

	$_rowsLeft  = is_int($result) ? $result : mysql_num_rows($result);
	$_step      = ceil($_rowsLeft / 10.0);
	$_lastPrint = $_rowsLeft; // assume the starting number has already been printed

	return $_rowsLeft;
}

function countdown() {
	global $_rowsLeft, $_step, $_lastPrint;

	--$_rowsLeft;

	$diff = $_lastPrint - $_rowsLeft;

	if ($diff >= $_step || $_rowsLeft === 0) {
		$_lastPrint = $_rowsLeft;
		print " $_rowsLeft";
	}
}

////////////////////////////////////////////////////////////////////////////////
// connect

mysql_connect('localhost', $dbuser, $dbpass);
mysql_select_db($dbname);

////////////////////////////////////////////////////////////////////////////////
// detect source version

if ($srcVersion === null) {
	$srcVersion = 0.4;

	print "* Detecting source version...";

	if ($srcVersion === 0.4) {
		// 0.5 projects have a primary key in sly_article
		$res = mysql_query('SHOW INDEX FROM sly_article WHERE Key_name = "PRIMARY"');

		if ($res === false) {
			die(' error: '.mysql_error());
		}

		$srcVersion = mysql_num_rows($res) === 0 ? 0.4 : 0.5;
		mysql_free_result($res);
	}

	if ($srcVersion === 0.5) {
		// 0.6 projects use 'catpos' instead 'catprior'
		$res = mysql_query('SHOW COLUMNS FROM sly_article WHERE Field = "catpos"');

		if ($res === false) {
			die(' error: '.mysql_error());
		}

		$srcVersion = mysql_num_rows($res) === 0 ? 0.5 : 0.6;
		mysql_free_result($res);
	}

	if ($srcVersion === 0.6) {
		// 0.7 projects have a new 'serialized_values' column in sly_slice
		$res = mysql_query('SHOW COLUMNS FROM sly_slice WHERE Field = "serialized_values"');

		if ($res === false) {
			die(' error: '.mysql_error());
		}

		$srcVersion = mysql_num_rows($res) === 0 ? 0.6 : 0.7;
		mysql_free_result($res);
	}

	print " looks like $srcVersion.\n";
}
else {
	print "* Assuming source version $srcVersion.\n";
}

////////////////////////////////////////////////////////////////////////////////
// regular 0.4 -> 0.5 changes

if ($srcVersion < 0.5) {
	print "* Schema updates: 0.4 to 0.5...";

	latin();
	mysql_query('UPDATE `sly_slice_value` SET `type` = REPLACE(`type`, "REX_", "SLY_") WHERE 1');
	mysql_query('ALTER TABLE `sly_article` DROP INDEX `id`, ADD PRIMARY KEY (`id`, `clang`)');
	mysql_query('ALTER TABLE `sly_article_slice` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`), ADD KEY `find_article` (`article_id`, `clang`)');
	mysql_query('ALTER TABLE `sly_file` ADD KEY `filename` (`filename`(255))');
	mysql_query('ALTER TABLE `sly_article` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `clang`)');
	mysql_query('ALTER TABLE `sly_registry` DROP INDEX `name`, ADD PRIMARY KEY (`name`)');

	print " done.\n";
}

////////////////////////////////////////////////////////////////////////////////
// pre-convert tasks

if ($dstVersion >= 0.6 && $srcVersion < 0.6) {
	print "* Schema updates: Preparing update...";

	// Beispiel. Das Modul 'redirect' nutzte gleichzeitig LINK[1] und VALUE[1].
	// Durch die Änderungen würden nun beide Werte nur noch den finder '1'
	// besitzen. Um dies zu vermeiden sollten die finder der Slice-Werte vor
	// den Änderungen umbenannt werden.
	// Im Beispiel wird LINK[1] nach 'article' und VALUE[1] nach 'url' umbenannt.

	// @mysql_query('UPDATE sly_slice_value SET finder = "article" WHERE type = "SLY_LINK" AND slice_id IN (SELECT id FROM sly_slice WHERE module = "redirect")');
	// @mysql_query('UPDATE sly_slice_value SET finder = "url" WHERE type = "SLY_VALUE" AND slice_id IN (SELECT id FROM sly_slice WHERE module = "redirect")');

	print " done.\n";
}

////////////////////////////////////////////////////////////////////////////////
// regular 0.5 -> 0.6 changes

if ($dstVersion >= 0.6 && $srcVersion < 0.6) {
	print "* Schema updates: 0.5 to 0.6...";

	latin();
	mysql_query('ALTER TABLE `sly_article` CHANGE COLUMN `catprior` `catpos` INT UNSIGNED NOT NULL');
	mysql_query('ALTER TABLE `sly_article` CHANGE COLUMN `prior` `pos` INT UNSIGNED NOT NULL');
	mysql_query('ALTER TABLE `sly_article_slice` CHANGE COLUMN `prior` `pos` INT UNSIGNED NOT NULL');
	mysql_query('ALTER TABLE `sly_file` CHANGE COLUMN `filesize` `filesize` INT UNSIGNED NOT NULL');
	mysql_query('ALTER TABLE `sly_file_category` CHANGE COLUMN `attributes` `attributes` TEXT NULL');
	mysql_query('ALTER TABLE `sly_slice_value` DROP COLUMN `type`');
	mysql_query('ALTER TABLE `sly_article_slice` DROP COLUMN `module`'); // if left over from 0.4->0.5 migration
	mysql_query('ALTER TABLE `sly_user` CHANGE COLUMN `name` `name` VARCHAR(255) NULL');
	mysql_query('ALTER TABLE `sly_user` CHANGE COLUMN `description` `description` VARCHAR(255) NULL');

	print " done.\n";
}

////////////////////////////////////////////////////////////////////////////////
// regular 0.6 -> 0.7 changes

if ($dstVersion >= 0.7 && $srcVersion < 0.7) {
	print "* Schema updates: 0.6 to 0.7...";

	latin();
	mysql_query('ALTER TABLE `sly_user` CHANGE COLUMN `psw` `password` VARCHAR(128) NULL DEFAULT NULL');
	mysql_query('ALTER TABLE `sly_slice` ADD COLUMN `serialized_values` LONGTEXT NOT NULL');

	print " done.\n";
	print "* Schema updates: converting date columns...";

	foreach ($datetimeTables as $table => $columns) {
		$add    = sprintf('ALTER TABLE `%s`', $table);
		$update = sprintf('UPDATE `%s` SET', $table);
		$drop   = sprintf('ALTER TABLE `%s`', $table);
		$rename = sprintf('ALTER TABLE `%s`', $table);

		foreach ($columns as $col => $allowNull) {
			$tmp  = '_'.$col;
			$null = $allowNull ? 'NULL' : 'NOT NULL';

			$add    .= sprintf(' ADD COLUMN `%s` DATETIME %s AFTER `%s`,', $tmp, $null, $col);
			$update .= sprintf(' `%s` = FROM_UNIXTIME(`%s`),', $tmp, $col);
			$drop   .= sprintf(' DROP COLUMN `%s`,', $col);
			$rename .= sprintf(' CHANGE COLUMN `%s` `%s` DATETIME %s,', $tmp, $col, $null);
		}

		print " $table";

		mysql_query(substr($add, 0, -1));
		mysql_query(substr($update, 0, -1));
		mysql_query(substr($drop, 0, -1));
		mysql_query(substr($rename, 0, -1));
	}

	print " done.\n";
	print "* Schema updates: Changing table engines to InnoDB...";

	$tables = array('sly_article', 'sly_article_slice', 'sly_clang', 'sly_file', 'sly_file_category', 'sly_registry', 'sly_slice', 'sly_user');

	foreach ($tables as $table) {
		print " $table";
		mysql_query(sprintf('ALTER TABLE `%s` ENGINE=InnoDB', $table));
	}

	print " done.\n";
}

////////////////////////////////////////////////////////////////////////////////
// UTF-8 encoding

if ($dstVersion >= 0.6 && $srcVersion < 0.6) {
	print "* re-encoding contents to UTF-8";

	foreach ($utf8Tables as $table => $def) {
		$trx = false;
		extract($def);

		latin();
		$res = @mysql_query('SELECT * FROM '.$table.' WHERE 1');
		if (!$res) continue; // table does not exist, probably a not-used addOn
		utf8();

		if (!empty($trx)) {
			mysql_query('BEGIN');
		}

		$rows = initCountdown($res);

		print "\n  > $table ($rows)...";

		while ($row = mysql_fetch_assoc($res)) {
			$updates = array();
			$wheres  = array();

			foreach ($cols as $col) {
				$updates[] = '`'.$col.'` = '.quote($row[$col]);
			}

			foreach ($pk as $col) {
				$wheres[] = '`'.$col.'` = '.quote($row[$col]);
			}

			// and update it
			mysql_query(sprintf('UPDATE %s SET %s WHERE %s', $table, implode(', ', $updates), implode(' AND ', $wheres)));

			countdown();
		}

		mysql_free_result($res);

		if (!empty($trx)) {
			mysql_query('COMMIT');
		}
	}

	print "\n* done.\n";
}

utf8();

////////////////////////////////////////////////////////////////////////////////
// JSON encoding for slice values

if ($dstVersion >= 0.6 && $srcVersion < 0.6) {
	print "* re-encoding slice values to JSON";

	$res  = mysql_query('SELECT * FROM sly_slice_value WHERE 1');
	$rows = initCountdown($res);

	print "\n  > sly_slice_value ($rows)...";

	while ($row = mysql_fetch_assoc($res)) {
		$value = $row['value'];

		// some fixes while we're at it
		$value = str_replace('"sally/data/mediapool', '"data/mediapool', $value);
		$value = preg_replace('#<\!--(.*?)-->#s', '', $value);
		$value = preg_replace('#<style>.*?</style>#s', '', $value);
		$value = str_replace('#"', '"', $value);

		// JSON encoding
		$value = json_encode($value);

		mysql_query('UPDATE sly_slice_value SET value = '.quote($value).' WHERE id = '.intval($row['id']));
		countdown();
	}

	mysql_free_result($res);
	print "\n* done.\n";
}

////////////////////////////////////////////////////////////////////////////////
// realURL2 stuff

print "* Rebuilding realURL2 tables...";

mysql_query('DROP TABLE IF EXISTS `sly_wv24_cache`');
mysql_query('DROP TABLE IF EXISTS `sly_wv24_route_cache`');
mysql_query('CREATE TABLE `sly_wv24_urls` (
	`namespace`  VARCHAR(32)           NOT NULL, -- im Format "addon.subnamespace"
	`ident`      VARCHAR(32)           NOT NULL,
	`article_id` INT UNSIGNED          NOT NULL,
	`clang`      SMALLINT(4) UNSIGNED  NOT NULL,
	`path`       VARCHAR(128)          NOT NULL, -- Kategorienpfad à la "1|2|3"
	`url`        VARCHAR(2024)         NOT NULL, -- erzeugte URL
	`created`    DATETIME              NOT NULL,
	`params`     VARCHAR(4096)         NOT NULL DEFAULT "",
	PRIMARY KEY (`namespace`, `ident`),
	INDEX `path` (`path`(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8');

print " done.\n";

////////////////////////////////////////////////////////////////////////////////
// guestbook

print "* Updating guestbook tables...";

@mysql_query('RENAME TABLE `sly_wv19_guestbook` TO `sly_wv19_entries`');
@mysql_query('RENAME TABLE `sly_wv19_guestbook_values` TO `sly_wv19_entries_values`');
@mysql_query('ALTER TABLE `sly_wv19_entries` CHANGE COLUMN `type` `type` VARCHAR(64) NOT NULL AFTER `id`');
@mysql_query('ALTER TABLE `sly_wv19_entries` ADD COLUMN `unread` TINYINT(0) UNSIGNED NOT NULL DEFAULT \'0\' AFTER `remote_addr`');

print " done.\n";

////////////////////////////////////////////////////////////////////////////////
// form evaluation (switch from wv registry to sly registry)

print "* fixing form eval registry elements...";
mysql_query('UPDATE sly_registry SET name = REPLACE(name, "wv.registry.", "") WHERE name LIKE "wv.registry.wv30%"');
print " done.\n";

////////////////////////////////////////////////////////////////////////////////
// remove broken feeds (table prefix changed)

print "* remove broken feeds...";
mysql_query('DELETE FROM sly_registry WHERE name LIKE "wv21.feeds.%"');
print " done.\n";

////////////////////////////////////////////////////////////////////////////////
// custom project stuff

print "* project specific stuff...";

// rename id 1 wym slices to 'html' for wymeditor value finders
$res = mysql_query('SELECT id FROM sly_slice WHERE module = '.quote('wymeditor'));
while ($row = mysql_fetch_object($res)) {
	mysql_query('UPDATE sly_slice_value SET finder = "html" WHERE slice_id = '.$row->id.' AND finder = "1"');
}
mysql_free_result($res);

print " done.\n";

////////////////////////////////////////////////////////////////////////////////
// move slices into serialized_values column

if ($dstVersion >= 0.7 && $srcVersion < 0.7) {
	print "* merging slice values into a JSON structure";

	$res  = mysql_query('SELECT id FROM sly_slice WHERE 1');
	$rows = initCountdown($res);

	print "\n  > sly_slice ($rows)...";
	mysql_query('BEGIN');

	while ($row = mysql_fetch_assoc($res)) {
		$sliceID = (int) $row['id'];
		$vres    = mysql_query('SELECT finder, value FROM sly_slice_value WHERE slice_id = '.$sliceID.' ORDER BY finder');
		$values  = array();

		while ($val = mysql_fetch_assoc($vres)) {
			$values[$val['finder']] = json_decode($val['value'], true);
		}

		$values = json_encode($values);

		mysql_free_result($vres);
		mysql_query('UPDATE sly_slice SET serialized_values = '.quote($values).' WHERE id = '.$sliceID);

		countdown();
	}

	mysql_query('COMMIT');
	mysql_free_result($res);
	print "\n* done.";

	print "\n  > dopping sly_slice_values...";
	mysql_query('DROP TABLE sly_slice_value');
	print "\n* done.\n";
}

////////////////////////////////////////////////////////////////////////////////
// done

mysql_close();