Saturday, 02-Jul-16 17:38:26 CEST

Porting my blog for the second time, links

This is post #55 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 had finished the deployment. It was feeling like I was done with this project so why not start blogging a little? And as you might expect I encountered a bug right away. It was not possible to make internal links to other posts in the blog!

In that post I am talking about rendering and that works pretty well but here my problem is that after editing the link needs to be parsed and placed in the database. Nothing of that worked. I cannot remember that I worked on it and it is such a strange feeling that source code that is supposed to be there is simply not there. But I made it? No? Or did I not? Nope. So I have to figure out how this is supposed to work. Again. This time I am going to explain it a little bit better because I realize that I will need that explanation some time in the future.

I explained the link system before. Here you can see a link "Whit Monday photo shoot":

This is how that same link is stored in the content of the post:

Here you can see that the URL has ID 1222. The post has ID 348. We can figure out what URL elements are stored in a post with this query:

mysql≻ select * from PostURL where PostID = 348;
+-------+--------+--------+
| URLID | PostID | IsLink |
+-------+--------+--------+
|  1222 |    348 |      0 |
+-------+--------+--------+
1 row in set (0.00 sec)

Suppose you would want an overview of all posts having a link then you would remove the where part. Then it would be possible to check that all of these links are valid. It is awful with dead links so that will become a really handy feature in the future. I will make a kind of URL maintenance screen. So this is the purpose of the URL record when IsLink is 0. Also for external links this is all there is to it. For internal links within the site there is an additional feature. Lets have a look at all PostURL records pointing to URL with ID 1222:

mysql≻ select * from PostURL where URLID = 1222;
+-------+--------+--------+
| URLID | PostID | IsLink |
+-------+--------+--------+
|  1222 |    348 |      0 |
|  1222 |    398 |      1 |
+-------+--------+--------+
2 rows in set (0.00 sec)

There are two PostURL records pointing to the URL of ID 1222. The record of line 5 is the information that URL 1222 is stored in post 348. The row on line 6 is a record telling us that the target location post of URL 1222 is pointing to post 398. The rendering of the link is done with the following query:

mysql≻ SELECT p.Slug FROM URL u, PostURL pu, Post p WHERE u.ID = 1222 AND u.ID = pu.URLID AND pu.PostID = p.ID AND pu.IsLink = 1;
+----------------------------------------------+
| Slug                                         |
+----------------------------------------------+
| Wild-Garden-Photos-taken-on-Whit-Monday-2012 |
+----------------------------------------------+
1 row in set (0.00 sec)

Now this is all fine and well for how to handle rendering of posts. How is this supposed to work in the editor? Especially what happens when finished editing and a post needs to be stored in the database. In the editor the URL will be entered in the readable form. It is after clicking the save button before the content is written to the database that the URL will need to be parsed and changed into the form {URL : xxx} and the records for doing the linking will also need to be created in the database. How are we going to do that?

For a given link in we need to find out if there already exists an URLs pointing to the target location. Here is an example:

mysql≻ select pu1.PostID, URL.ID, pu2.PostID, pu2.IsLink, URL.FileName from PostURL as pu1 inner join PostURL as pu2 on pu2.URLID = pu1.URLID inner join URL on pu1.URLID = URL.ID where pu1.PostID = 472 and pu2.IsLink = 1;
+--------+------+--------+--------+-------------------------------------------------------+
| PostID | ID   | PostID | IsLink | FileName                                              |
+--------+------+--------+--------+-------------------------------------------------------+
|    472 | 1666 |    348 |      1 | Techno-or-House                                       |
|    472 | 1667 |     16 |      1 | Porting-my-blog-for-the-second-time-Project-can-start |
+--------+------+--------+--------+-------------------------------------------------------+
2 rows in set (0.00 sec)

In post ID 472 there is two URL records ID 1666 and ID 1667 pointing to another post ID 348 and 16. So If we already got URL 1666 pointing to the target then we can link to that URL by creating a PostURL record with the PostID of the post to link and URLID of 1666.

Ok, enough of talking. Time for workshop! Before parsing any links I need to remove all old links data:

    $query = GetQueryWithData(1,"delete pu from PostURL pu, URL u where pu.PostID = ? and u.ID = pu.URLID and u.IsFileContent = 0", $ip_strPostID);
    $result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));

Here is the routine setting up the link data:

# https://www.malmgren.nl/post/Porting-my-blog-for-the-second-time-links
$_bIsLocalURL = 0;
if (preg_match("/^(https?://www.malmgren.nl|/|https?://www.jens.malmgren.nl)/", $_strURL))
{
    $_bIsLocalURL = 1;
}
if ($_strID == "" && preg_match("//post/(.+?)(.aspx)?$/i", $_strURL, $match))
{
    $_strTargetSlug = $match[1];
    $_strTargetSlugAspx = $match[1]. ".aspx";
    
    $_bCreatePostURLRecord = 0;
    
    $query = GetQueryWithData(1,
        "SELECT " .
            "PostURL.URLID " .
        "FROM " .
            "PostURL " .
            "INNER JOIN URL ON URL.ID = PostURL.URLID " .
        "WHERE " .
            "IsLink = 1 AND " .
            "(URL.FileName = ? OR URL.FileName = ?)", $_strTargetSlug, $_strTargetSlugAspx);
    $result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
    if ($row = mysqli_fetch_array($result))
    {
        # We found the URL of this link so we should reuse it.
        $_strID = $row['URLID'];
        $_bCreatePostURLRecord = 1;
    }
    else
    {
        # We found no existing URL pointing to the target of this link.
        # What is the ID of the post that this is linking to?
        $query = GetQueryWithData(1,
            "SELECT ID FROM Post WHERE Slug = ? OR Slug = ?",
            $_strTargetSlug, $_strTargetSlugAspx);
        $result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));

        if ($row = mysqli_fetch_array($result))
        {
            $_strTargetID = $row['ID'];
            $query = GetQueryWithData(0,
                "INSERT INTO URL (    TagName,    IsLocalURL,        IsFileContent,     FileName        )" .
                "VALUES          (    ?,            ?,                ?,                 ?                )",
                                    $_strTag,    $_bIsLocalURL,    0,                $_strTargetSlug        );
            $result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
            $_strID = $mysqli-≻insert_id;

            # Link from URL to target post
            $query = GetQueryWithData(0,
                "INSERT INTO PostURL    (    URLID,        PostID,            IsLink    )" .
                "VALUES                  (    ?,            ?,                ?        )",
                                            $_strID,    $_strTargetID,    1        );
            $result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
            $_bCreatePostURLRecord = 1;
        }
        else
        {
            # A failure we have no solution for. Jens, where are you?
            echo "Jens, where are you?";
        }
    }

    if ($_bCreatePostURLRecord == 1)
    {
        $query = GetQueryWithData(0,
            "INSERT INTO PostURL    (    URLID,        PostID,            IsLink    )" .
            "VALUES                  (    ?,            ?,                ?        )",
                                        $_strID,    $ip_strPostID,    0        );
        $result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
    }
}

It was quite some effort to get this working. Pfff...

Obviously if I want to have this code to run on any domain I need to formalize the comparison with the domain-name. That is for later.

To try the functionality of the links resolve I started to blog about model painting season of 2015 - 2016. It looks like if the source panel is open strange things happens. So the workaround right now is  to avoid saving the page with the source panel open.

This went all well until I blogged about Francesca 10 of January 2016. She had a website and the link to her website could not be handled by my blog-system. So there is work to do.