Migrate Gallery2 To Yii

One of my sites was built on Menalto Gallery2. This software has disappointed me in many ways but the worst came with the new version 3 when they dropped support for Multilanguage sites. This made it impossible to upgrade my old Gallery2. Thus my site had to remain insecure and poorly functioning.

So the time has come to migrate it to my favorite framework Yii. I want to preserve everything from the old installation - text, photos and URLS.

After inspecting closely Gallery2 MySQL database I have found the information I need in the following tables:
  • g2_ChildEntity - shows the relation between albums and pages. Each child entity belongs to a parent (album) from the table g2_Item.
  • g2_FileSystemEntity - contains all the URL aliases or the so called slugs
  • g2_Item - may be the most important table of all. It contains all the information about the albums and their pages. Albums can be distinguished from regular pages by the column g_canContainChildren. If it is 1 then it is an album and 0 is for a page. This is important since our Yii based site will have categories which will represent the albums.
  • g2_MultiLangItemMap - the table where the translations are kept. It is similar to g2_Item in structure.
The design of our new yii gallery will be as simple as possible - categories and articles only for the beginning. For authorization, editing and so on we will use components and Gii Crud generator.

The first thing we will do for our new gallery is the albums table which we will call categories:

CREATE TABLE `category` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `cat_slug` varchar(30) NOT NULL,
 `cat_name_en` varchar(50) NOT NULL,
 `description_en` text,
 `cat_name_bg` varchar(50) CHARACTER SET utf8 NOT NULL,
 `description_bg` text CHARACTER SET utf8 NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7630 DEFAULT CHARSET=latin1

You will notice that we have a field cat_slug. That's important because we want our new gallery to be SEO optimized and its albums to show like http://example.org/sample_album. Similarly photo pages urls will look like http://example.org/sample_album/sample_photo.

Also, we have category name and description for the two languages we have in our gallery.

To make things easier we will work only with one database. Thus we can insert all values automatically when all tables are in the same database with the following query:

INSERT INTO category
SELECT gi.g_id, gf.g_pathComponent, gi.g_title, gi.g_description, gm.g_title, gm.g_description
FROM g2_Item gi
LEFT JOIN g2_MultiLangItemMap gm ON gi.g_id = gm.g_itemId
LEFT JOIN g2_FileSystemEntity gf ON gi.g_id = gf.g_id
WHERE gi.g_canContainChildren =1;

Next, let's take care of the pages with photos. They will become articles in our new Yii site. First, we create their table:

CREATE TABLE `pages` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `slug` varchar(50) NOT NULL,
 `description` varchar(200) DEFAULT NULL,
 `keywords` varchar(200) DEFAULT NULL,
 `title` varchar(200) NOT NULL,
 `html` text,
 `description_bg` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `keywords_bg` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `titile_bg` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `html_bg` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
 `active` tinyint(1) NOT NULL DEFAULT '1',
 `cat_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `cat_id` (`cat_id`),
 CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`cat_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then to populate the records for our pages from the existing Gallery2 pages we use:

INSERT INTO pages(id,slug,title,html,title_bg,html_bg,cat_id)
SELECT gi.g_id, gf.g_pathComponent, gi.g_title, gi.g_description, gm.g_title, gm.g_description, gc.g_parentId
FROM g2_Item gi
LEFT JOIN g2_MultiLangItemMap gm ON gi.g_id = gm.g_itemId
LEFT JOIN g2_FileSystemEntity gf ON gi.g_id = gf.g_id
LEFT JOIN g2_ChildEntity gc ON gi.g_id = gc.g_id
WHERE gi.g_canContainChildren =0;

In progress...


blog comments powered by Disqus