Since I am planning to write a new “letter” that could be a summary of my previous letters + new information I discover on Twitter and FB, I need some kind of program for managing these statuses. Here is how things developed for me, and this time in quite some detail.
On March 18 I started working on an SQL database and its design. On March 19 I got the DB into its final form and then got tweet parsing working from the export file of tweetsbackup.com.
That was easy, because the tweets were in a txt file where one line held the date and the following lines held the tweet text.
Otherwise, March 19 was a terrible day, the dog barely stopped, so I tried to find out how I could program outdoors, since it is impossible to do it here AT HOME.. That day I also tested the tables and they were created OK, so I could move on to the C# code. I also asked Martin whether I should build an in-memory cache for the Users table.
In the end I built one for almost all the tables. While at it, I also told Martin about my plan that I will probably go outside to program somewhere in a meadow 😀 and from March 19 until today I was choosing laptops, until I picked one and ordered it for 9,500 CZK :).
Apart from that, by today I was so mentally down that I could hardly even type on the PC without a million typos. I found out there is a company supplying external laptop batteries, but on their website I found no such batteries, so I do not know whether they stopped making them or what.. I found out that with INSERT INTO you do not need to list the columns if I specify values for all columns.
Today I also moved from the downstairs bedroom (my room) to the downstairs living room, and it was really cozy there. The armchair was not great for the health of my spine, but it was comfortable. On March 20 I started parsing the Facebook export. I converted the xml to xml, but the problem was that once again the XML classes worked before the reset and not after it, so as always I had to rewrite it into LINQ. By March 21 the program was nearing completion. It could already parse both the new and the old FB format, it could detect duplicates, and why am I even copying it all here: https://twitter.com/#!/sunamoDevProg/status/182488215612178432 ..
There is a picture for you too :). However, even today I did not fully finish the program, so I continued on March 22, when I dealt with the fact that my FB wall has 2 posts with the same time! In the end I solved it by making the CreationDate column not UNIQUE but NOT NULL. Then I had to parse the tweets again, because this time the export was saved in some strange way.
It had a header saying utf-8, its size matched UtF8, and yet when reading it as utf-8 I got broken diacritics. OK, so I saved it again as UTF8 in Notepad and it worked normally! Fortunately, today I found no major bugs, so as of today the program can do the following with both tweets and FB posts:
- display all tweets/posts in the DB overall
- browse posts/tweets one by one
- display all tweets/posts in the DB from a specific day. This works via a drop-down panel, so the calendar is not always visible and does not take up space.
- display the current iteration with the option to jump to any other item
- manually refresh the displayed items when, for example, I parse new posts/tweets. Related to that is adding new tweets/posts at runtime.
- Duplicate check when adding (but only by date, so on FB I often have a post for which I wrote no description, and then it looks like this:
And that is all. On FB, when displaying all posts, they are shown without comments and likes; when displaying a single post, with comments and likes. However, I am not writing that letter yet, because FB sends me a file containing a wall.html with only 500 posts – the picture shows 3045 of them because it also includes posts from my previous export. Still, a large number of posts is missing, and most importantly – they are the most important ones. That is why today I wrote to FB support asking whether they could look into it for me ..