I wrote a month ago about redesigning my 'blogs database' program. Since then, I've been religiously entering old blogs, a month at a time (I'm now at the end of October 2011), as well as entering the new blogs as they are written.
I've also been working on the functionality of the program. Mostly this has just been fixing bits and pieces here and there, but yesterday I devoted several hours to improving the functionality. First off was the ability to retrieve blogs connected to multiple tags (called an 'or' query in logic despite that in everyday language it's an 'and' query); I had included the possibility of retrieving blogs that are connected to either one tag or a second tag, along with the possibility of negating this (i.e. connected to one tag and not to a specific second tag). This is what lead me to the possibility of including Venn diagrams; I found an example of how to draw a circle but not how to draw two intersecting circles, so this has been quietly forgotten. I'm not a graphical programmer but rather a database programmer, so this type of programming is not easy for me.
But I wanted to allow the possibility of retrieving blogs that were connected to several blogs (i.e. connected to tag A or tag B or tag C or tag D). I had laid some groundwork for this in the past and I thought that I had implemented this multiple retrieval, but either it didn't work properly or I had simply left it for when I would have more time. So yesterday I continued the work; this required some short-circuiting of the usual 'blog retrieval' query but the resulting query was simpler than the query for two tags.
Then I worked on the 'singleton tag' query, namely tags that were connected to one blog only. I had written a complicated query for this some time ago, but realised at some stage that it would be much easier to adapt the 'number of entries per tag' query to show only those with one entry. So this was quite simple.
Another idea that came to me whilst improving the program was simplifying the code generation of the 'this day in history' table. Originally this simply output the necessary HTML code into a file; whenever I needed this, I would have to open the file with Notepad, copy the contents and paste them into the HTML interface of Blogger. It would be simpler to create a new window with the HTML text waiting to be copied and pasted, so this I did. I also discovered that the code was missing one angle bracket that explained why Blogger told me every time that there was a problem with my HTML code.
An additional idea was reusing the 'choose tags' dialog in order to show which tags were connected to a specific entry in a 'read only' manner.
Finally, I picked up an idea that I had written about previously: Another idea for a report was taking one popular tag (e.g. programming) and seeing how many times different tags have been paired with this tag. Then I could retrieve the entries that have the chosen combination of tags and show them in the the 'show entries' form. I had no idea of how complicated this would be! There were three and a half stages to this: the first half was choosing a 'base' tag - I already had a dialog box for this. Then I had to find all the tags that had joint entries with this one tag and store their identity numbers in the temporary 'linked' table. This required quite a hairy sql statement. Then the 'show tags' dialog (pre-existing) had to be extended in order to show this list of tags and calculate the number of joint entries with the base tag. I couldn't do this in the previous stage because there I only wanted the tag ids because that's all the temporary table has - an id and an instance number. It occurs to me now that I could extend this easily by adding another field to the table, 'payload', that would contain whatever needed to be passed. It would certainly make the second stage easier!
The final stage was to allow double clicking on one of the joint tabs in order to see what the joint entries were. For example, let's say that the base tag was 1970; there are seven tags that are joined to an entry that is also connected to '1970'. One of these is 'Nice enough to eat' - three common entries; doubling clicking on this line should open a new 'show entries' window with the three specific entries. The window would open but it was empty. I checked the query time and time again; I ran it in my 'db management' program where it worked perfectly. I checked the parameters time after time and they were correct. So why was I getting no results?
Normally when this happens in Priority, I check the primary keys of what I am trying to insert - are these values duplicate? No. After banging my head against a wall for at least an hour, I decided to sleep on the problem. I won't say that I awoke with the solution at my fingertips, but I did decide on a new approach to the problem. I created a new temporary table and tried inserting to this table - no difference. Then I tried adding the parameters directly into the sql query, by what is known as 'sql injection' - this is considered a bad technique. Still no luck. Then I looked at the sql query being passed to the database engine - the value of the base tag was 0 instead of 98, explaining why there was no data being transferred. It took a few minutes to realise how this could be; I had checked several times that the value of the 'tag' parameter was 98.
Then it hit me: the code is as follows:
with qInsert do begin sql.text:= ....... params[0].asinteger:= tag; execsql end
| Blog # | Date | Title | Tags |
|---|---|---|---|
| 149 | 04/12/2008 | Learning to walk | Health, Robert Silverberg |
| 654 | 04/12/2013 | The least we can do is wave to each other | Van der Graaf Generator |
| 782 | 04/12/2014 | My army service - part four | Health, Israel, Personal |
No comments:
Post a Comment