Monday, 12-Oct-15 20:51:00 CEST

Porting my blog for the second time, render posts part 5

This is post #27 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 /post/Porting-my-blog-for-the-second-time-Project-can-start.

In my previous post I created the basis for a query to get the data I need to present a page. That was all theory. Now I got my query in place in the PHP page. Now it looks like this:

$query = "SELECT c.Title AS Title, c.Slug AS Slug,
c.PublishedOn AS PublishedOn, c.Content AS Content,
c.PositionType AS PositionType, p.Title AS PrevTitle,
p.Slug AS PrevSlug, p.PublishedOn AS PrevPublishedOn,
n.Title AS NextTitle, n.Slug AS NextSlug,
n.PublishedOn AS NextPublishedOn FROM
Post c, Post p, Post n WHERE c.Slug = '" . $arrayArgs["post"] . "' and
c.ID = p.NextID and c.ID = n.PrevID;";

For the first time I could go around in my blog to see what I had picked up from the XML files. It became a contemplative activity when I looked around and tried to figure out what was going on.

The URL rewrite mechanism (/post/Porting-my-blog-for-the-second-time-setting-up-Apache2) gave me a surprise. When the CSS file is external it is necessary get it from the correct location. When giving a relative URL not starting with http the page wanted to get the CSS from the "same directory" as the page was loaded from. In our case this is an imaginary directory. So for example if the page was loaded from posts/xxx then the page thinks that the CSS is available at "posts/" as well while I was thinking that it was located at the root of the website. When prefixing the CSS url with a slash it worked!

Already earlier I mentioned I had seen slugs that contained special characters and that I was not happy about that . My validation routine was initially made so that it would not let through any special characters at all. To be able to browse the blog I had to allow these characters. The argument validation routine of the PHP document is very important for avoiding that the blog will get hacked so I really want the slug syntax to be as strict as possible so I decided that I will convert all Slug strings in such way I can have the original stricter validation routine. This became one to-do item for the Perl routine.

I noticed a number of blog posts originating from the old blog system itself. For example the first test message generated after installing BlogEngine.NET. A message for testing the template etc. These messages needs to be skipped by the Perl program as well, so that became to-do number two for my Perl program.

For a long time I have not been practicing the Perl program and exactly as predicted the new NextID and PrevID construction gave me problems when I wanted to remove all the posts. This was the error message I got:

DBD::mysql::db do failed:
Cannot delete or update a parent row:
a foreign key constraint fails (`jensblog`.`Post`,
CONSTRAINT `fk_Post_Post1` FOREIGN KEY (`NextID`)
REFERENCES `Post` (`ID`)
ON DELETE NO ACTION ON UPDATE NO ACTION) at /usr/local/bin/analyze.pl line 37.

It is logical when you think about it. Every every post has a PrevID and a NextID. When the Post they point to is deleted then that ID does not exist and the NextID and PrevID is invalid. Thus if we want to delete a Post then there is another post pointing to it so that is not possible. The solution is to clear the NextID and PrevID before deleting the Posts. Hence I added this line to my Perl program before deleting the content of the Post table:

$dbh-≻do('UPDATE Post SET NextID = NULL, PrevID = NULL' , undef);

Then it was possible to run the program again.

I improved the cleaning of the Slug with this line:

$dictStringFieldToValue{"slug"} =~ s/[^a-zA-Z0-9]+/-/g;

With these changes it was possible to navigate back and forth in the blog.

Noticed that the content of some blog posts breaks the layout. I need to find out why. But that is for the next time.