DeeEmm

Pragmatism in code

The final part of our Geeklog to Joomla / Jomsocial migration deals with migrating the main site content.

With Geeklog, content is organised into 'Stories'. Similarly, with Joomla, content is organised into 'Articles'.

Migration is simple, and requires running one SQL command.

First, ensure that you copy the following tables to your new database

gl_stories

then simply run the following command

INSERT INTO `jos_content` (`title`,`introtext`,`fulltext`,`catid`,`created`,`hits`)
SELECT title,introtext,bodytext,tid,date,hits
FROM gl_stories

You will now be able to see all of your old Geeklog stories in the Article manager.

If you are using Media Gallery to provide image handling functionality to your Geeklog site, the following will help you migrate your images. Media gallery is now an integral part of GLfusion, and so the same principles should apply to GLFusion too.

If you are using 4Images, it is possible to migrate to Media Gallery, using the media gallery import functions, or alternatively you may modify the code below to migrate direct.

The version of Media Gallery that I have migrated from is 1.5.1. and is no longer available as a stand alone plugin. However, a diligent web search will likely be able to turn up a version.

So.... On with the migration.

The first thing to do is to copy the following tables from your old geeklog database to your new Joomla database.

gl_mg_media
gl_mg_media_albums
gl_mg_albums

Once these have been copied over, you can start to migrate the data.

 

First up we copy the image info over

INSERT INTO `jos_community_photos` (albumid, caption, published, creator, permissions, image, thumbnail, original, created, hits)
SELECT
b.album_id,
a.`media_title`,
'1',
c.owner_id,
'0',
concat('images/photos/',concat(a.media_user_id,concat('/',concat(b.album_id,concat('/',concat((SELECT media_filename from gl_mg_media WHERE media_id = a.media_id), concat('.', a.media_mime_ext))))))),
concat('images/photos/',concat(a.media_user_id,concat('/',concat(b.album_id,concat('/thumb_',concat((SELECT media_filename from gl_mg_media WHERE media_id = a.media_id), concat('.', a.media_mime_ext))))))),
concat('images/originalphotos/',concat(a.media_user_id,concat('/',concat(b.album_id,concat('/',(SELECT media_filename from gl_mg_media WHERE media_id = a.media_id), concat('.', a.media_mime_ext)))))),
a.media_upload_time,
a.media_views
FROM gl_mg_media a
JOIN gl_mg_media_albums b ON b.media_id = a.media_id
JOIN gl_mg_albums c ON c.album_id = b.album_id
WHERE c.album_parent='45'



Next the album info...

insert into jos_community_photos_albums (id, photoid, creator, name, description, permissions, path, type,hits)
SELECT album_id, '', owner_id, album_title, album_desc, '0', concat('images/photos/',concat( owner_id, '/')), 'user', album_views from gl_mg_albums WHERE album_parent = '45'

 

And finally, we set the album cover image

//set gallery default image (arbitrary image)
UPDATE jos_community_photos_albums a, jos_community_photos b
SET a.`photoid` = b.id
WHERE a.`creator` = b.`creator`
AND a.`photoid` ='0'

 

Once done all that is left is to copy the images into the correct location. This is done with the aid of a bit of PHP code. The images need to be organised into the proper folder hierarchy, which involves interrogating the database, creating the relevant folder structure, and then copying the images.

Points to note...

  • Be sure to change the database login details + database name
  • If not on the same domain, you will need to copy the existing /images/mediaobjects/orig/ contents across.
  • Correct folder permission will need to be granted to allow the script to create the folders.

 



//copy function
function copyemz($file1,$file2){
$contentx =@file_get_contents($file1);
$openedfile = fopen($file2, "w");
fwrite($openedfile, $contentx);
fclose($openedfile);
if ($contentx === FALSE) {
$status=false;
}else $status=true;

return $status;
}

//estabish database connection
$con = mysql_connect('localhost','USER','PASSWORD');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('DATABASENAME', $con);


//get filename from database
$result = mysql_query("SELECT Image FROM jos_community_photos");

while($row = mysql_fetch_array($result))
{
//generate filenames + paths

$strpart = explode('/', $row['Image']);
$filename = $strpart[4];
$filepath = explode($filename, $row['Image']);

$sourcepart = explode( '_', $filename);

//get current directory
$thisdir = getcwd();



//get filename and directory
$SourceFile = 'images/mediaobjects/orig/' . $sourcepart[0] . '/' . $filename;
$DestFile = $row['Image'];
$DestPath = $thisdir . '/images/photos/' . $strpart[2] . '/' . $strpart[3];

//create user directory
if(mkdir($DestPath , 0777, true)) {
echo "Directory $DestPath has been created successfully...
";
} else {
echo "Failed to create $DestPath directory...
";
}

//copy file
if (!copyemz($SourceFile, $DestFile)) {    
echo "failed to copy $DestFile ...
";
} else {
echo $SourceFile . ' -> ' . $DestFile . ' copied successfully
';
}

//get filename and directory for thumbnails
$SourceFile = 'images/mediaobjects/tn/' . $sourcepart[0] . '/' . $filename;
$DestFile = 'images/photos/' . $strpart[2] . '/' . $strpart[3] . '/thumb_' . $filename;

if (!copyemz($SourceFile, $DestFile)) {    
echo "failed to copy $DestFile ...
";
} else {
echo $SourceFile . ' -> ' . $DestFile . ' copied successfully
';
}



//get filename and directory
$SourceFile = 'images/mediaobjects/orig/' . $sourcepart[0] . '/' . $filename;
$DestFile = 'images/originalphotos/' . $strpart[2] . '/' . $strpart[3] . '/' . $filename;
$DestPath = $thisdir . '/images/originalphotos/' . $strpart[2] . '/' . $strpart[3];

//create user directory
if(mkdir($DestPath , 0777, true)) {
echo "Directory $DestPath has been created successfully...
";
} else {
echo "Failed to create $DestPath directory...
";
}

if (!copyemz($SourceFile, $DestFile)) {    
echo "failed to copy $DestFile ...
";
} else {
echo $SourceFile . ' -> ' . $DestFile . ' copied successfully
';
}


}

mysql_close($con);

?>

Once you have run the script, you should now be able to see all of your users photo albums

/DM

This is the second article in the migrate from Geeklog to Joomla series and outlines the method by which you can inport your existing geeklog forum into Kunena. Our previous article outlined how to migrate your users across to Joomla and also integrate them into the JomSocial component. You can find the first article here - Joomla / JomSocial - Import users from Geeklog / GLFusion

Kunena is a great forum component, and easily rivals dedicated forums such as phpBB or SMF. It also integrates nicely with JomSocial, allowing proper integration of avatars and profiles. Although there are other forum components available, only Kunena integration will be covered here.

The migration process requires that you have access to both the original site's SQL database, and the target sites database via an SQL editor. I personally favor phpMyAdmin, mostly as it comes pre bundled in C-Panel, but any other flavour will also do the job. You need to be able to export and import tables, and also execute SQL commands.

The basic premise is this.

The relevant tables will be exported from the old site and then imported into the target database. We will then execute some SQL statements to copy this data into the target database at the correct locations, and then tidy up some secondary information to make it all work. Sounds easy huh.

First off you will need to export the following tables from your geeklog database.

gl_forum_topic
gl_forum_forums

Next. Import these tables into your Joomla database. (You can delete them later.)

Once the tables have been imported you will need to run the following SQL statements, in sequence.

insert into jos_kunena_messages (id, parent, thread, catid, name, userid, email, subject, time, ip, hits) SELECT id, pid, pid, 'forum', name, uid, email, subject, date, ip, views from gl_forum_topic

This copies the topic titles across. it sets much of the topic information, such as which categories they belong to, but some of this needs to be 'fixed' later.

 

insert into jos_kunena_messages_text (mesid, message) SELECT id, comment from gl_forum_topic

This statement imports the actual posts into the new database.

 

insert into jos_kunena_categories (id, name, parent) SELECT forum_id, forum_name, '100' from gl_forum_forums
insert into jos_kunena_categories (id, name, parent) values('100', 'forum category','0')
update jos_kunena_categories set published = '1' where published = '0'

These statements set the category information, and also set each category to 'published' so that it is visible in the browser.

 

update jos_kunena_messages set thread = id where thread = '0'
update jos_kunena_messages, gl_forum_topic set jos_kunena_messages.catid = gl_forum_topic.forum where jos_kunena_messages.id = gl_forum_topic.id;
update jos_kunena_messages set parent = thread
update jos_kunena_messages set parent = '0' where id = thread
update jos_kunena_categories set pub_access = '0' , pub_recurse = '1', admin_access = '30'

Finally, these statements tidy up the messages table and correctly allocate the categories to the messages.

Once you have carried out the above steps you should then be able to see all of your old geeklog forum posts in Kunena.

If you wish to convert your Geeklog site to the Joomla platform, then you will have a number of things that you will need to do. First off, you will need to get a copy of Joomla up and running. In my case, I am using JomSocial, but the process is the same for both JomSocial and Joomla installations.

To perform this migration you will need to use a database management tool to run SQL statements. I use phpMyAdmin, which is usually bundled with most hosting packages, but you could easily use any other similar tool for this.

Once you have your Joomla install set up, you will need to make a backup of the following table on your Geeklog site

gl_users

To do this using phpMyAdmin, simply navigate to the table and then go to the export tab. Check the 'save to file' checkbox and then save the backup to your local computer.

Next you will need to add this table to your Joomla database. To do this using phpMyAdmin, navigate to your Joomla database and then hit the import tab. Browse to the file you just saved and then hit the 'go' button. You should now be able to see the gl_users table in your joomla database.

The reason for copying this is simply to make copying the fields easier. If you are only using one database with both the Geeklog and Joomla info in it, there is no need to do this

Now for the exciting part...

The main issue with user migration between platforms, is the multitude of different authentication methods used, Joomla uses a salted password, where the password is stored in the following format

password:salt

Fortunately for us, it still works if we leave the salt off. This is great news as it means we can simply copy our MD5 hashed Geeklog passwords straight across without having to do anything to them.

All that it left to do now, is to map the Geeklog database fields to their corresponding Joomla database fields, and construct an SQL statement to perform the migration.

The Joomla database table that contains the user data is

jos_users

But there are two other tables that we need to consider. These contain the user permissions data, and without corresponding entries, users will not be able to log in. These table are

jos_core_acl_aro

jos_core_acl_groups_aro_map

Important things to remember are that we need to maintain the same ID field as the Geeklog table, the user ID is used to reference all content, in the geeklog gl_users table this is the `uid` field. Same with the username and password fields. It is really only these three fields that are needed to get everything up and running.

So, putting all of this together, we end up with the following SQL statements.

To migrate the user data, first you will need to empty the existing data from the tables, to do this in phpMyAdmin, simply navigate to the table and then selet the 'Emtpy' tab. Just in case something goes wrong, make sure you take a backup of everything BEFORE you try any of this, then you can proceed with the migration.

To populate jos_users table

insert into jos_users (id, username, name, password, usertype, gid, email, registerDate) SELECT uid, username, fullname, passwd, 'Registered', '18', email, regdate from gl_users

To populate jos_core_acl_aro table

insert into  jos_core_acl_aro (section_value, value, name) select 'users', id, name from jos_users;

To populate jos_core_acl_groups_aro_map

insert into jos_core_acl_groups_aro_map (group_id, aro_id) select '18', id from jos_core_acl_aro;

This will set all users as normal registered users. Assuming that you're site is like mine, and that you only have one or two admins, it's easy enough to manually change users into administrators. To do this you will need to change both the jos_users table and the jos_core_acl_groups_aro_map table.

In `jos_users`, change the `user_type` field to 'Super Administrator', and the `gid` field to '25'

In `jos_core_acl_groups_aro_map`, change the `group_id` field to '25'

You should now be able to log into your site using your Geeklog log in details, and also log in to the administration panel using your administrators log in details.

If you are a JomSocial user and you would also like to copy across your user avatars, you can use the following SQL statements

To copy the user avatar image info...

INSERT into jos_community_users (userid, avatar, thumb) SELECT uid, photo, photo FROM gl_users

To convert the image data to include the correct path (for only those users who have an avatar)...

UPDATE jos_community_users set avatar = CONCAT('images/avatar/', thumb) WHERE thumb not like null

Now, simply copy the avatars into your images/avatar folder.

/DM

This is a quick and dirty hack to add some text to the footer of the PDF's generated within Joomla. The library used to generate the PDF's is the open source TCPDF library - http://www.tcpdf.org To add code into the footer of the generated PDF's, you will need to edit the following file:

/libraries/tcpdf/tcpdf.php

Find the following code in the footer() function

//Print page number
if ($this->rtl) {
$this->SetX($this->original_rMargin);
$this->Cell(0, $footer_height, $pagenumtxt, 'T', 0, 'L');
} else {
$this->SetX($this->original_lMargin);
$this->Cell(0, $footer_height, $pagenumtxt, 'T', 0, 'R');
}

 

Underneath it add the following code - replacing the text with your own

                //DeeEmm footer text hack
                $this->SetY($footer_y);
                $this->Cell(0, 10, 'www.DeeEmm.com', 0, 0, 'C');


Hey presto - you now have additional text in your footer