Fork CMS 3.6 -> 3.7 upgrade script

README.md

Fork CMS DB upgrade (3.6 -> 3.7).

Description

I wondered if you could just convert your old database and make it work with Fork CMS 3.7. I gave it a try...

This php script will convert your Fork CMS 3.6 database (in the best way possible) to Fork CMS 3.7 using mysql UPDATE queries. I used Kaleidoscope, a file comparing app, to see the db changes between a 3.6 dump and 3.7 mysql dump and I wrote the appropriate queries and some php code to deserialize the arrays.

Note: I've tested this script with a fresh Fork install of 3.7 where I used the database of a Fork 3.6 installation. The script converted the db and I could use the fork backend & frontend without problems. I also tried this on an existing 3.6.6 website that I made. A fresh fork install where I copied the theme & files folders, upgraded the theme to 3.7 and finally converted my old database to a working 3.7 version.

ALWAYS HAVE A BACKUP OF YOUR DATABASE!

How to use

Open the php script and enter your database details at the top. Start the php file to convert your database (hopefully without errors).

You can merge the v3.7 into your current git repo, or you can start from scratch:

• Download and unpack a new version of Fork CMS.
• Setup or copy your app/config/parameters.yml. It's the same as the 3.6 file.
• Create an installed.txt file in your src/Install/Cache folder. By doing this, the install won't be shown when you visit the site, and it will connect to your database.
• Copy your theme & files folders to the appropriate location. Be sure to camelcase your folders &files in the theme!
• ???
• Profit

Technical details

I wrote a mysql function that converts a string to his CamelCase equivalent. Note that only '_' will be removed, for example: module_maker --> ModuleMaker. Other punctation is detected but won't be removed, for example for template files: core/layout/templates/default.tpl --> Core/Layout/Templates/Default.Tpl

Discussion

If you have bugs or remarks:

• Twitter: @jessedobbelaere

fork-upgrade.php

<?php

	// Settings
	define('DB_HOST', 'localhost');
	define('DB_USER', 'usernamehere');
	define('DB_PASS', 'passwordhere');
	define('DB_NAME', 'tablenamehere');

	// Make Connection with DB
	try {
		$db = new PDO('mysql:host=' . DB_HOST .';dbname=' . DB_NAME . ';charset=utf8', DB_USER, DB_PASS, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
		echo 'Connected to the database <br>';
	} catch (Exception $e) {
		exit('Could not connect to database server or access database');
	}

	echo 'Executing queries... <br>';

	// CamelCase mysql function
	$db->exec('DROP FUNCTION IF EXISTS camelcase');
	$db->exec("
		CREATE FUNCTION camelcase(str VARCHAR(128))
		RETURNS VARCHAR(128)
		BEGIN
			# Variables
			DECLARE c CHAR(1);
			DECLARE s VARCHAR(128);
			DECLARE i INT DEFAULT 1;
			DECLARE bool INT DEFAULT 1;
			DECLARE punct CHAR(27) DEFAULT ' ()[]{},.-_!@;:?/''#$%^&*<>';
			DECLARE punct_underscore CHAR(27) DEFAULT '_';

			# First convert to lowercase
			SET s = LCASE( str );

			# Loop the word char by char
			WHILE i <= LENGTH(s) DO
			BEGIN
				# Get new char
				SET c = SUBSTRING(s,i,1);

				# Is letter some sort of punctuation?
				IF LOCATE(c, punct) > 0 THEN
					SET bool = 1;

					IF LOCATE(c, punct_underscore) > 0 THEN
						SET s = CONCAT(LEFT(s,i-1),SUBSTRING(s,i+1));
						SET i = i-1;
					END IF;
				ELSEIF bool=1 THEN
					BEGIN
						IF c >= 'a' AND c <= 'z' THEN
							BEGIN
								SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
								SET bool = 0;
							END;
					  	ELSEIF c >= '0' AND c <= '9' THEN
					  		SET bool = 0;
					  END IF;
					END;
				END IF;
				SET i = i+1;
			END;
			END WHILE;

			RETURN s;
		END;"
	);


	// Now execute the right queries to modify each table.
	// Most tables don't needed change, but I mentioned them in the comments.
	// Other tables mostly just needed camelcasing of the module or actionname.

	// DATABASE TABLES:
	// analytics_keywords
	// analytics_landing_pages
	// analytics_pages
	// analytics_referrers
	// backend_navigation
	// blog_categories
	// blog_comments
	// blog_posts

	// content_blocks
	$db->exec('UPDATE content_blocks SET template = camelcase(template)');

	// emails
	// forms
	// forms_data
	// forms_data_fields
	// forms_fields
	// forms_fields_validation
	// groups

	// groups_rights_actions
	$db->exec('UPDATE groups_rights_actions SET module = camelcase(module), action = camelcase(action)');
	$db->exec('DELETE FROM groups_rights_actions WHERE action = \'MassAction\' AND module = \'Locale\''); // Deprecated, remove it
	$db->exec('DELETE FROM groups_rights_actions WHERE action = \'ProcessFeedback\' AND module = \'Faq\''); // Deprecated, remove it

	// groups_rights_modules
	$db->exec('UPDATE groups_rights_modules SET module = camelcase(module)');

	// groups_settings -> Serialized array needs to be updated with camelcase keys. Warning: ugly code :-)
	$groups_settings_results = $db->query('SELECT * FROM groups_settings');
	$groups_settings_rows = $groups_settings_results->fetchAll();
	foreach($groups_settings_rows as $row) {
		$values = unserialize($row['value']);
		$newValues = array();

		foreach($values as $key => $value) {
			$newValue = array();
			foreach($value as $k => $v) {
				$newValue[underscoreToCamelCase($k, true)] = $v;
			}

			$newValues[underscoreToCamelCase($key, true)] = $newValue;
		}

		$db->exec('UPDATE groups_settings SET value = "' . addslashes(serialize($newValues)) . '" WHERE group_id = ' . $row['group_id']);
	}

	// hooks_queue
	// hooks_subscriptions

	// locale
	$db->exec('UPDATE locale SET application = camelcase(application), module = camelcase(module)');
	$db->exec("
			INSERT INTO locale (id, user_id, language, application, module, type, name, value, edited_on)
			VALUES
				(NULL,1,'nl','Frontend','Core','lbl','IAgree','ik ga akkoord',NOW()),
				(NULL,1,'en','Frontend','Core','lbl','IAgree','I agree',NOW()),
				(NULL,1,'fr','Frontend','Core','lbl','IAgree','je suis d\'accord',NOW()),
				(NULL,1,'de','Frontend','Core','lbl','IAgree','ich bin einverstanden',NOW()),
				(NULL,1,'es','Frontend','Core','lbl','IAgree','estoy de acuerdo',NOW()),
				(NULL,1,'nl','Frontend','Core','lbl','IDisagree','ik ga niet akkoord',NOW()),
				(NULL,1,'en','Frontend','Core','lbl','IDisagree','I disagree',NOW()),
				(NULL,1,'fr','Frontend','Core','lbl','IDisagree','je ne suis pas d\'accord',NOW()),
				(NULL,1,'de','Frontend','Core','lbl','IDisagree','ich bin nicht einverstanden',NOW()),
				(NULL,1,'es','Frontend','Core','lbl','IDisagree','no estoy de acuerdo',NOW()),
				(NULL,1,'nl','Frontend','Core','msg','CookiesWarning','Om de gebruikerservaring van deze site te verbeteren gebruikt deze website <a href=\"/disclaimer\">cookies</a>.',NOW()),
				(NULL,1,'en','Frontend','Core','msg','CookiesWarning','To improve the user experience on this site we use <a href=\"/disclaimer\">cookies</a>.',NOW()),
				(NULL,1,'fr','Frontend','Core','msg','CookiesWarning','Pour améliorer l\'expérience utilisateur sur ce site, nous utilisons des <a href=\"/disclaimer\">cookies</a>.',NOW()),
				(NULL,1,'de','Frontend','Core','msg','CookiesWarning','Wir verwenden <a href=\"/disclaimer\">Cookies</a> um Ihr Benutzer-Erlebnis zu verbessern.',NOW()),
				(NULL,1,'es','Frontend','Core','msg','CookiesWarning','Para mejorar la experiencia del usuario en este sitio utilizamos <a href=\"/disclaimer\">cookies</a>.',NOW()),


				(NULL,1,'nl','Backend','Settings','lbl','Cookies','cookies',NOW()),
				(NULL,1,'en','Backend','Settings','lbl','Cookies','cookies',NOW()),
				(NULL,1,'fr','Backend','Settings','lbl','Cookies','cookies',NOW()),
				(NULL,1,'de','Backend','Settings','lbl','Cookies','Cookies',NOW()),
				(NULL,1,'es','Backend','Settings','lbl','Cookies','cookies',NOW()),
				(NULL,1,'nl','Backend','Settings','msg','HelpCookies','In Europa is een wetgeving rond cookies van kracht. Met deze cookie balk voldoet u aan de meest strenge wetgeving.',NOW()),
				(NULL,1,'en','Backend','Settings','msg','HelpCookies','There are several laws in Europe about the use of cookies. With this Cookie-bar you fulfill the most strict law.',NOW()),
				(NULL,1,'fr','Backend','Settings','msg','HelpCookies','En Europe, il existe plusieurs lois pour l\'utilisation des cookies. Avec ce barre de cookie vous accomplissez la loi la plus stricte.',NOW()),
				(NULL,1,'de','Backend','Settings','msg','HelpCookies','In Europa gibt es mehrere Gesetze für die Verwendung von Cookies. Mit diesem Cookie-Bar erfüllen Sie das strengsten Recht.',NOW()),
				(NULL,1,'es','Backend','Settings','msg','HelpCookies','En Europa hay varias leyes para el uso de cookies. Con este barra de cookie cumple con la legislación más estricta.',NOW()),
				(NULL,1,'nl','Backend','Settings','msg','ShowCookieBar','toon de cookie balk',NOW()),
				(NULL,1,'en','Backend','Settings','msg','ShowCookieBar','show the cookie bar',NOW()),
				(NULL,1,'fr','Backend','Settings','msg','ShowCookieBar','montrer la barre de cookie',NOW()),
				(NULL,1,'de','Backend','Settings','msg','ShowCookieBar','zeigen Sie die Cookie-Bar',NOW()),
				(NULL,1,'es','Backend','Settings','msg','ShowCookieBar','monstrar la barra de cookie',NOW())
		");

	# location
	# location_settings
	# meta

	# modules
	$db->exec('UPDATE modules SET name = camelcase(name)');

	# modules_extras
	$db->exec('UPDATE modules_extras SET module = camelcase(module), action = camelcase(action)');
	$db->exec("UPDATE modules_extras SET data = 'a:1:{s:8:\"template\";s:19:\"SubpagesDefault.tpl\";}' WHERE id = 5"); // Manual fix for subpages row
	$db->exec('UPDATE modules_extras SET action = \'TagCloud\' WHERE label = \'TagCloud\''); // Manual fix for tagcloud row
	$db->exec('UPDATE modules_extras SET action = \'MostReadQuestions\' WHERE label = \'MostReadQuestions\' AND module = \'Faq\''); // Manual fix
	$db->exec('UPDATE modules_extras SET action = \'AskOwnQuestion\' WHERE label = \'AskOwnQuestion\' AND module = \'Faq\''); // Manual fix
	$db->exec('UPDATE modules_extras SET data = NULL WHERE label = \'Faq\' AND module = \'Faq\' AND action = \'CategoryList\''); // Manual fix

	# modules_settings
	$db->exec('UPDATE modules_settings SET module = camelcase(module)');

	# modules_tags
	$db->exec('UPDATE modules_tags SET module = camelcase(module)');

	# pages
	# pages_blocks

	# search_index
	$db->exec('UPDATE search_index SET module = camelcase(module)');

	# search_modules
	$db->exec('UPDATE search_modules SET module = camelcase(module)');

	# search_statistics
	# search_synonyms
	# tags

	# themes_templates (BE SURE TO RENAME (capitalize) YOUR TEMPLATE FILES IN YOUR THEME FOLDER!)
	$db->exec('UPDATE themes_templates SET path = camelcase(path)');
	$db->exec('UPDATE themes_templates SET path = replace(path,\'.Tpl\',\'.tpl\')'); // Fix the camelcased '.Tpl' back to '.tpl'

	# timezones
	# users
	# users_groups
	# users_sessions

	# users_settings -> Serialized array needs to be updated with camelcase keys. Warning: ugly code :-)
	$users_settings = $db->query('SELECT * FROM users_settings WHERE name = \'dashboard_sequence\'');
	$users_settings = $users_settings->fetchAll();
	foreach($users_settings as $row) {
		$values = unserialize($row['value']);
		$newValues = array();

		foreach($values as $key => $value) {
			$newValue = array();
			foreach($value as $k => $v) {
				$newValue[underscoreToCamelCase($k, true)] = $v;
			}

			$newValues[underscoreToCamelCase($key, true)] = $newValue;
		}

		$db->exec('UPDATE users_settings SET value = "' . addslashes(serialize($newValues)) . '" WHERE user_id = ' . $row['user_id'] . ' AND name = \'dashboard_sequence\'');
	}

	// The end
	echo '<span style="color: green;">Execution finished!</span>';


/**
 * Convert strings with underscores into CamelCase
 *
 * @param    string    $string    The string to convert
 * @param    bool    $first_char_caps    camelCase or CamelCase
 * @return    string    The converted string
 *
 */
function underscoreToCamelCase( $string, $first_char_caps = false)
{
	if( $first_char_caps == true )
	{
		$string[0] = strtoupper($string[0]);
	}
	$func = create_function('$c', 'return strtoupper($c[1]);');
	return preg_replace_callback('/_([a-z])/', $func, $string);
}

Just converted a 3.5 db, works great!