Fixing unresolved links after editing

I am back in updating my blog-engine!

Since I finished my blog-engine 13 of July 2016 I have been doing other things. All the time I have been blogging about my art, but I have not changed the blog-engine and I have not been persistent in uploading new blog-posts. That has not felt good on one hand but on the other hand I have enjoyed my life and I have not promised anyone to blog a specific day of the week so if I blog or not, that is not something I need to go around and worry about. For the art it feels like blogging is a motor in improving my art. For that reason, I have blogged every time I went to Amsterdam to paint but I just not uploaded the blogs. For an entire year I had my story about my visit to Shokland on 25th of September 2016 as my most recent blog. When I found I was just about to celebrate being one entire year behind uploading blogs that felt like an absolute maximum milestone. So that was the reason I started uploading old art blogs this fall 2017. That went well until I came to the post about Eva on 22 of January 2017. At that moment I got overly excited about my achievements and wandered around in the blog and edited a little here and there when I realized that I was not doing any good. Instead I was destroying the earlier posts one by one. Links stopped working and overall misery.

Now comes the moment I need to figure out what I am doing wrong here. Here is a post that I destroyed:

Where is that /post/NotResolved coming from? I know that when it works I should point to this post:

The content with the unresolved URL it looks like this in the database:

Content refers to URL with ID 13 and that URL what is that?

There is an entry with ID 13. That entry is however not up to date with the new location of the new website, but it IS available. We might come back to that it right now ends witch aspx later. What happens to that place holder when it is resolved?

The routine for resolving the URL placeholder is found in a function ResolveURL and I can see that it is this query that fails:

SELECT p.Slug FROM URL u, PostURL pu, Post p WHERE u.ID = '$strDataBaseLookupId' AND u.ID = pu.URLID AND pu.PostID = p.ID AND pu.IsLink = 1;

At first, I am clueless because this was the style that I wrote queries back then and I have improved considerably since then. The underlying idea is not visible from the query (yet) and I cannot remember what it was supposed to be either.

The query reads from the tables URL, PostURL and Post. It looks like there needs to be a URL u of ID = 13 and then there also needs to be a PostURL pu record where the URLID is 13 where the PostID is of some ID and the PostURL is having IsLink set to 1.

When I look through the entire PostURL table it looks like there is no record with URLID = 13 and PostID = 16 and IsLink = 1.

Suppose I create that record, will it work then?

I make this insert: insert into PostURL (URLID, PostID, IsLink) values (13, 16, 1);

And sure enough, this solved the issue:

With that comes the question: What caused the PostURL to be deleted in the first place?

For that I got an idea, how about that I somewhere removed links when “checking out” the text for editing and then after editing I restore the URL and in that process, I failed to restore the deleted links. Does that sound plausible?

So that brings us to a blogpost from 2 July 2016 where I am talking about deleting URL and PostURL records before editing a post: https://www.malmgren.nl/post/Porting-my-blog-for-the-second-time-links In that post I am saying that I just delete the URL. How can I be so sure that it is so simple?

The offending query looks like this:

delete pu from PostURL pu, URL u where pu.PostID = ? and u.ID = pu.URLID and u.IsFileContent = 0

I slash the links here. Delete it from both PostURL as well as the URL table with no extra checks. For a moment it could be nice to see what is going on here.

The URL table holds the actual URL here. It represents the link in a metaphysic way as to what actual website the blog is hosted. The URL is an entity in the true meaning of the name. Originally the URL was picked up from the text as it was entered but the system stores it as an entity and by doing so it is metaphysic. This URL that was broken here it was originally pointing to the previous version of the blog. The target page was an aspx page. Now this has been turned into an entry in the URL table. The ID of this URL entry is 13 and no longer the URL is pointing directly to a page, but it is resolved and calculated into an actual URL.

So, what makes an internal URL point from one page into another? That is the PostURL many-to-many table. There are entries in the PostURL that connects the URL to the target post of the URL. Then there are other entries making use of the URL. This is two different usages, meanings, of the PostURL table.

The entry 13, 16, 1 is the entry that points the URL with ID 13 to post 16. All other entries are “from”-entries.

Normally there is only one entry with IsLink = 1 but there must be at least one. It does not make sense to have no such entry or more than one. What am I doing here? I just delete it. That is wrong.

When is it okay to delete the IsLink = 1 entry? It is okay to delete the IsLink = 1 entry when there are no more IsLink = 0 entries. But not before.

Do I test that. Not yet.

The original post addressed the case of one IsLink = 1 entry and only one IsLink = 0 entry. In that case all is fine. But it is just a special case. The incorrect deletion routine is called when pressing save on a post.

This is the updated deletion routine:

$query = GetQueryWithData(1,"delete from PostURL where PostID = ? and IsLink = 0", $ip_strPostID);
$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
$query = GetQueryWithData(1,"select count(*) as PostURLCount from PostURL where PostID = ? and IsLink = 0;", $ip_strPostID);
$result = $mysqli-≻query($query) or die("Error query. " . mysqli_error($mysqli) . " in " . $query);
if ($row = mysqli_fetch_array($result))
{
       $_iPostURLCount = $row['PostURLCount'];
       if ($_iPostURLCount == 0)
       {
              $query = GetQueryWithData(1,"delete from PostURL where PostID = ? and IsLink = 1", $ip_strPostID);
              $result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
       }
}

This looks like it is working.

I was born 1967 in Stockholm, Sweden. I grew up in the small village Vågdalen in north Sweden. 1989 I moved to Umeå to study Computer Science at University of Umeå. 1995 I moved to the Netherlands where I live in Almere not far from Amsterdam.

Here on this site I let you see my creations.

I create, that is my hobby.