Wednesday, 09-Sep-15 21:30:00 CEST

Porting my blog for the second time, restart

This is post #5 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.

So far loading my first sample post is going very well. I had selected one simple test case post that I worked on loading into the database over and over again until it worked as supposed to. Still there are simple fields I still need to look into but now I had decided that I start working on loading comments. To find a nice example of a post that received a comment I sifted through many of my blog posts and while doing that I conclude that the quality of comments on my blog are really low. It is most of the time Spam bots posting a comment. Actually the quality is so low I think I will make a minimal implementation of comments. For sure some form of captcha although I hate it will need to be present. For example ask the user what the (random number less than 12) word from start is of the post. And what is the 15th digit of the square root of pie and that sort of questions. Or why not ask what do you get when you mix yellow and blue paint? Captcha is for later, now here is my sample file.

It got this filename 12ce5cdd-a6a3-4dfb-8765-085644919a91.xml and it looks like this:

≺?xml version="1.0" encoding="utf-8" standalone="yes"?≻
≺post≻
  ≺author≻Admin≺/author≻
  ≺title≻This painting of Emily is imperfectly nice≺/title≻
  ≺description≻This is a painting of Emily at De Stoker.≺/description≻
  ≺content≻The text...≺/content≻
  ≺ispublished≻True≺/ispublished≻
  ≺isdeleted≻False≺/isdeleted≻
  ≺iscommentsenabled≻True≺/iscommentsenabled≻
  ≺pubDate≻2011-10-29 21:50:00≺/pubDate≻
  ≺lastModified≻2011-12-20 22:10:10≺/lastModified≻
  ≺raters≻0≺/raters≻
  ≺rating≻0≺/rating≻
  ≺slug≻This-painting-of-Emily-is-imperfectly-nice≺/slug≻
  ≺tags /≻
  ≺comments≻
    ≺comment id="ddd72037-e61a-4559-8a0f-ac743b37ccb2"
parentid="00000000-0000-0000-0000-000000000000"
approved="True" spam="False" deleted="False"≻
      ≺date≻2011-11-01 04:41:56≺/date≻
      ≺author≻oil painting≺/author≻
      ≺email≻some.email@nosp.am.≺/email≻
      ≺country /≻
      ≺ip≻1.1.1.1≺/ip≻
      ≺moderatedby≻admin≺/moderatedby≻
      ≺avatar /≻
      ≺content≻Oil paints are a great medium of art which
has produced some of the greatest artists all over. If one
is willing to buy [u]oil paintings[/u], it becomes essential
to know at least the biggest artists of all time and their
works in order to differentiate between a great painting
and cheap paintings. 
≺/content≻
    ≺/comment≻
  ≺/comments≻
  ≺categories≻
    ≺category≻a4e5f89e-2782-48a3-80ac-bdd872674acb≺/category≻
    ≺category≻ac7d1fda-b384-48eb-8207-7fa92d9894fe≺/category≻
    ≺category≻0dc7f3de-5d3a-4ba7-a10b-3cd937bf318a≺/category≻
    ≺category≻de5b8b80-3220-4315-8180-bdeed2b9b560≺/category≻
  ≺/categories≻
  ≺notifications /≻
≺/post≻

The difference between category and comment is that a comment is used only for that single post. A Category on the other hand is used in many Post(s). This is why the Blogengine.NET developers are storing the comment inside the post file. However I am porting this to a database so I will have a separate comment entity and link a post to that entity. There will be a table called Comment. One Post can have several Comment(s). But the same Comment is only valid for one Post. So it is a one to many relation between a Post and Comment(s) in that order. In my implementation I do this by creating a Comment table where each comment has its own ID but then secondly it has a field with an ID to the Post it is connected to.

This is all and well but how would this work in practice? I can imagine that at the beginning I would load the data from my old XML files and then for example Post 1 would be connected to Comment 1. But what happens to Comment 1 if I would delete Post 1? It would be dangling without belonging to a post. A database as a solution for this, it has constraints.

At this point I started to realize that although this is a very simple database hand coding it might not be a brilliant idea.

So I downloaded and installed MySQL Workbench. Got it installed with great help of this document here http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html After connecting to the database I could reverse engineer what I had already done.

When designing the database in the MySQL Workbench that previous idea of deleting the tables of the database and starting over needs to be removed from my script. What I need instead is leaving the tables but I need delete the content of them so that I can load the content from scratch. It looks like I will work on that instead of start loading the comments. But that is for next time.

So there you have it, kind of restarting the project.