Monday, 14-Sep-15 20:10:00 CEST

Porting my blog for the second time, tags

This is post #7 of my series about how I port this blog from Blogengine.NET 2.5 ASPX on a Windows Server 2003 to a Linux Ubuntu server, Apache2, MySQL and PHP. A so called LAMP. The introduction to this project can be found in this blog post https://www.malmgren.nl/post/Porting-my-blog-for-the-second-time-Project-can-start.aspx.

It is time to start working on loading the Tags but I have a little challenge, I cannot test my program anymore because the constraints between the tables is locking the tables against each others. Obviously I cannot delete the content of the tables alphabetically anymore so I had to do something. First I tried the brute force and that was to remove the constraints before I could delete the tables. But if I worked that way then I would have to break down the entire database every time. Then I figured out that it is not possible with the constraint settings I am using to delete the parent table if there exists a child table belonging to the parent. Understanding what this really is saying was the key to the solution. A Post table is connected to a Category but in-between these two there exists a connection table PostCategory. It is child of both Post and Category. So when I first remove the data in the connection tables then I am allowed to delete the rest of the data in the tables. That worked!

# Delete data from the tables
# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-tags.aspx
if ($deleteDataBase)
{
	$dbh-≻do('delete from PostTag');
	$dbh-≻do('delete from PostCategory');
	$dbh-≻do('delete from Tag');
	$dbh-≻do('delete from Category');
	$dbh-≻do('delete from Comment');
	$dbh-≻do('delete from Post');
}

With this solved I could run my program over and over again. The database tables stays in the database but the data in them is gone. Then I can load the tables and try out the program loading the tables again and again. So then started my work on the Tags. While working on that I can across some nice information:

• Good practice for using Perl DBI: http://www.easysoft.com/developer/languages/perl/dbi-debugging.html#dbi-return-calls
• Really inserting UTF-8: http://www.perlmonks.org/index.pl/?node_id=745506

In my sample file the one and only tag in the blog looked like this:

≺tags≻
    ≺tag≻Sunnerås≺/tag≻
≺/tags≻

A funny coincidence here was that I used the letter å and as it turned out my database did not have UTF8 collation specified. Also it turned out to be so that I did not tell Perl DBI to enter data as UTF8 so the tag showed up corrupted. Another thing special thing with the tag implementation in Blogengine.NET is that there is no central repository of tags as with the Categories. But I had decided to make one. So to solve this while loading files I decided that I would check if the Tag was available in the database and if so connect the post with the available tag. If the tag was missing in the database I would first create it and then connect it to the post. This looks like this in Perl:

# Load tags of a post. Connect the Tags entity to this Post through the PostTag entity.
for my $node ($xmldoc-≻findnodes("/post/tags/tag"))
{
	print "Loaded tag: " . $node-≻textContent() . "\n";
	my $sth = $dbh -≻ prepare('SELECT ID FROM Tag WHERE Name = ?');
	$sth-≻execute( $node-≻textContent() );
	my $tagID = "-";
	if(my $row = $sth-≻fetchrow_hashref())
	{
		$tagID = $$row{"ID"};
		print "Tag found in database: " . $node-≻textContent() . " with ID: " . $tagID ."\n";
	}
	if ($tagID eq "-")
	{
		print "Tag not found in database: " . $node-≻textContent() . "\n";
		$dbh-≻do('INSERT INTO Tag (Name) VALUES (?)' , undef, $node-≻textContent() );
		$tagID = $dbh-≻{mysql_insertid};
	}
	$dbh-≻do('INSERT INTO PostTag (PostID,TagID) VALUES (?, ?)' , undef, $postID, $tagID );
}

However it is important to notice that it is not possible to connect the same tag two times to one post. The database reports an error if that occurs. I am fine with that.

So this is it for now. Next time I will start download images.