Implicit vs explicit joins (SQL)/2

Following up my recent blog on the subject, I thought that I would look at the SQL statements in one small program of mine. About 50% of the queries were based on one table only, so of course there was no need to change them. Of the other 50%, about half were simple to change; in the remaining 25%, I had cleverly put the limiting clause before the connecting clause. Correcting these queries was simple, of course, and I did become aware of the organising power of the explicit syntax.

I had to write a new report for the OP's management program, so this was a chance to use the new syntax from day one. As luck would have it, this query was somewhat more complex than the usual type of query, involving two joins to the same table (in other words, I had to use the same table twice in order to display different values). Here I discovered my first 'gotcha': if the same table is being used twice, it has to be aliased both times. Initially, I was only aliasing the table once, but when I saw that the returned results were not as they should be, I added the extra alias. This probably explains why I see the frequent use of aliasing in answers about SQL on Stack Overflow: people have been taught to alias everything to prevent problems. And I thought that they were just being lazy.

Here is the new query in all its glory:

select dockets.id, c1.name, dockets.opendate, c2.name as xname, xtracust.curdate
from dockets inner join customers c1
on dockets.customer = c1.id
inner join xtracust
on dockets.id = xtracust.docket
inner join customers c2
on xtracust.customer = c2.id
where xtracust.xtra = 1

Probably all the (Firebird) world knows this already, but here is another tidbit which I picked up this morning: in order to limit the number of rows being returned, one uses the non-standard syntax select first 1 [skip n] .... I'm not aware of having needed this (otherwise I would already have known this), but an extra piece of knowledge is always good to know. I sometimes have to check whether a query returns a dataset, but this is not the same thing. It might be useful in programs where I display all the records (but not all the fields) from a dataset on program startup; normally I try to limit this by date, but it might be easier to display the first 50 records and then require the user to click on a button if she wants more records displayed.

Spam mail

Yesterday, I received the following email:

International insurance company is hiring employees for long term cooperation.
Our main purpose is to build a strong team of distant representatives who are ready to work for 4-6 hours from home on weekdays.

Interested affiliates:
ISRAEL

Requirements:
Excellent English both reading and writing Excellent native language Strong computer skills Experience in database processing You must be hard working and responsible You must be intelligent (graduates are preferred) If you’re beginner be ready to pass trial period for 1 month.

Primary responsibilities:
Database processing and translation

We will look to hire you for a long term, at least for 6 month

Payment:
You’ll be paid according to results of interview and consideration of the application We have limited amount of vacancies so please submit your resume as you read this.
To submit your resume please use: Kirby@il-career.com

I would normally have been more than slightly interested in this offer had it not been for the minor fact that the grammar contained too many mistakes for such an email, and for the major fact that the email had apparently been sent from one of my work addresses.

Later on in the day, our system administrator sent a company wide email stating that our email server was under attack with letters similar to the above. During the course of the day, I received another two similar emails, each from a different address (but all mine) and each with a different contact address.

What I find interesting is how the mail managed to combine a few items which would appeal to me: translation, database management, a graduate. It would be interesting to see what other people received (apparently the same). Of course, the point is not the content of the mail but rather the evil intent of flooding our email server.

Replacing the dual list box dialog

I wrote about nine months ago about how I improved the dual list box dialog which came as a standard template with Delphi 1. I used this dialog once again in a program in which I was initially displaying one field from a database; after testing, I decided to add another field in order to make things clearer. When the OP saw it, she requested another field; afterwards I decided on my own to add yet another field. As a result, the list box was displaying four fields concatenated into one string and the result was visually unpleasant. I used different kinds of brackets for each field, so the string looked something like this

5298 (1110) [31-07-2011] {2543}

The operation of the dialog was oblivious to the content of the string, as the key (deliberate pun) to the string was passed as a linked and hidden object, but from the user's point of view, the modest list box was not up to the task of displaying such a complex line. It was time for a more structured component to hold the string/fields.

Normally, when displaying data taken from a database, my thoughts turn to using a dbgrid, but as the dialog required rows to be removed from one list and added to another, the use of queries would needlessly complicate matters. A compromise solution would be to use a listview. So yesterday, I wrote a small demo program with two listviews; I populated one on program startup and then figured out how to move one item from listview to listview.

I thought that the demo would be sufficient for plugging directly into the program needing this functionality, but I was wrong. First of all, I had to contend with the right to left issue; fortunately I had solved this in the past, so all I had to do was find where I had implemented this. Then I had to figure out the column widths, which was done by excruciating trial and error (there must be a better way!). Then I was left with the task of iterating through one of the listviews and checking whether the current entry had been in the populating query upon startup or had been added during the form's execution; this was the most important problem of all but eventually succumbed.

Whilst none of the above seems to be on the cutting edge of Delphi development, I have found it very difficult to find any references to the sort of problem which I face. So I have to 'wing it' most of the time, creating my own solutions to problems which I encounter. But now that the 'dual list view dialog' problem has been solved, I can reuse this solution whenever needed. 

Here is a little of code, which deals with moving one item or all the items from one list view to the other.

Procedure TConnectToReceipt.LVMoveOne (src, dst: tlistview);
var
 li1, li2: tlistitem;

begin
 if src.selected <> nil then
  begin
   li1:= src.Selected;
   li2:= dst.Items.Add;
   li2.Assign (li1);
   src.selected.delete;
  end;
 dst.alphasort;
 setbuttons;
end;

Procedure TConnectToReceipt.LVMoveAll (src, dst: tlistview);
var
 i: Integer;

begin
 src.Items.BeginUpdate;
 dst.Items.BeginUpdate;
 for i:= src.Items.Count - 1 downto 0 do
  begin
   dst.Items.Add.Assign (src.Items[i]);
   src.Items[i].Delete;
  end;
 dst.alphasort;
 dst.Items.EndUpdate;
 src.Items.EndUpdate;
 setbuttons;
end;

And here's the code to make the listviews (and their titles) display from right to left. I'm posting this here to make it easier for me to find it the next time.

procedure TConnectToReceipt.FormCreate(Sender: TObject);
const
 LVM_FIRST = $1000;      // ListView messages
 LVM_GETHEADER = LVM_FIRST + 31;

var
 header: thandle;

begin
 header:= SendMessage (srclist.Handle, LVM_GETHEADER, 0, 0);
 SetWindowLong  (header,   GWL_EXSTYLE,
GetWindowLong (header,  GWL_EXSTYLE) or  WS_EX_LAYOUTRTL or  WS_EX_NOINHERITLAYOUT);
 SetWindowLong (srclist.Handle,GWL_EXSTYLE,
                GetWindowLong (srclist.Handle, GWL_EXSTYLE)  or
                WS_EX_LAYOUTRTL or WS_EX_NOINHERITLAYOUT);
 srclist.invalidate;
end;

Implicit vs explicit joins (SQL)

As I don't use MS-SQL Server (which many people take to be the standard, even if that is de facto and not de jure), I had not been aware of the debate regarding the syntax of implicit vs explicit joins until recently. At work, the ERP program has its own specific SQL syntax which is slightly different from the standard, especially with regard to joins, so this cannot serve as an example. It transpires that the version of Firebird which I use supports both implicit and explicit joins, so again, I wasn't aware of any difference.

What is explicit join syntax?

select meetings.curdate, meetings.price, people.forename, people.surname
from meetings inner join people
on meetings.person = people.id
where meetings.kind = 2

And what is implicit join syntax?

select meetings.curdate, meetings.price, people.forename, people.surname
from meetings, people
where meetings.person = people.id
and meetings.kind = 2

What is the difference?  The best explanation which I have found is (in my phrasing) the separation of the part which defines how the tables are joined from the part which defines how the rows are filtered. As Firebird is indifferent to which syntax is used (execution times were the same regardless of syntax), it doesn't matter too much which syntax I use. I will try, however, to start using explicit syntax as this is logically clearer.

Update
As this article points out, implicit syntax is otherwise known as SQL-89 join syntax, whereas explicit syntax is otherwise known as SQL-92 join syntax. I quote: "In this [SQL-89] syntax, join criteria are mixed with search criteria. It is applicable ONLY to inner joins. [In] SQL-89 there was no standard for outer joins."

So now you know. Or, seeing as everyone already knew this, now I know.

Before the poison

If it's late August, then it's time for a new novel from Peter Robinson. Whilst I find this 'writing to timetable' slightly disturbing, I am not complaining. This year's offering is called 'Before the poison', and unusually is not about Robinson's serial character, DCI Alan Banks. Instead, it's about a composer (Chris something) who writes film soundtracks who has moved back from California to his native Yorkshire. But leaving the characterisation aside, this book might be considered a Banks story without Banks. I'll try to write about the book without giving away any spoilers.

The narrator finds himself owner of a large house, well off the beaten track. Slowly he discovers that the house has been virtually unoccupied for the last fifty years, and that the previous lady (Grace Fox) of the house was hung after being convicted of poisoning her husband, even though the husband was originally thought to have died as the result of a heart attack.  The composer becomes intrigued by this story and starts chasing facts; he meets one source in a local pub, but for other sources, he has to travel to Paris and Cape Town.

Chris's search for the truth (or at least, his search for an understanding) is woven alongside his new life in the Yorkshire village, where he is befriended by his estate agent, a 40+ old lady with marital problems. Whilst this material has clearly been added to flesh out his characterisation, I'm not sure that it contributes that much to the story (compare this with similar pieces of DCI Banks' life when he's not chasing murderers).

In common with a few of the recent Banks novels, some of the action takes place in the present and some in the past. The past is represented first by the story of Grace Fox's trial taken from a book about famous murder trials (the book is bought by the composer in a second hand bookshop after he becomes curious) and secondly by extracts from Fox's private journal (the provenance of which only becomes clear towards the end of the story). Thus the trial material views what happens only from a distance, and of course only presents what happened at the trial - Grace Fox was not called to the witness stand to defend herself.

Again, in common with a few of the recent Banks novels, the first half is not particularly focused: Robinson lays down the groundwork diligently but it doesn't seem to go anywhere. The book takes off only when the extracts from Fox's private journal begin to appear; these of course tell the tale from a different angle, although all of the entries presented deal with Fox's life during the Second World War. These entries cause Chris to better his understanding of what almost certainly happened regarding the death.

I can understand why this book had to be written without Banks: there is no connection between what happened then as to what is happening now (in the previous novels, there was always a strong connection between past and present) and so there is no room for invoking a police investigation - hence no Banks. The composer is free to come and go as he wishes, asking people for their memories but with no compulsion to answer. I suspect, though, that most people in real life would be less enthusiastic about finding a resolution - indeed, several characters ask the composer why he is so driven about the story.

Continuing the family tradition

My father spent his 30 year plus career in the British civil service, working alongside accountants. I spent 13 years as an active accountant before moving full time into the computer business. Now my daughter (aged 23 and a half) has announced that she is going to take an accountancy course at a college. She's continuing the family tradition.

This is quite a turn-around for her, as until now all her jobs have been people orientated. She's been a shift manager at a few cafes in the local vicinity (she helped start one such cafe, and simply dropping her name was enough to get a job there for her brother), she managed a stall in the arcades of New Zealand and Australia, and worked for nearly a year in customer support for one of Israel's cellphone companies (hardly anybody survives a year). So it was quite a surprise for me when she announced her intention to turn her back on people and start working with figures.

We had a long discussion on the subject a few weeks ago, in which I played the devil's advocate. When I mentioned the people orientation of all her previous jobs, she replied that these were the only jobs available to her. This may well be so: over the past thirty years, services have become more and more important, to the detriment of other kinds of jobs. Almost all of my jobs (bakery, chicken hatchery, food analyst, accountant, programmer) date from at least twenty years ago, and none of them are people orientated. The only people orientated job which I undertook in my early days was running the kitchens in my first kibbutz, and that was far from a total success.

I was fortunate to have started work when accountancy was already computerised; had I started a few years earlier, it would have meant manually writing up entries and then sending them off to a service centre where they would have been computerised. But one doesn't really understand what's happening until one does some manual examples.

Beginning the diet

I wrote last week about commencing a diet; I could start some parts of it immediately but other parts required a little time to be organised.

The first thing that enters my mouth in the morning is a glass of cold water with freshly squeezed lemon juice. This isn't as unpalatable as I had expected, but it doesn't "do" much for me. After that, I have yoghurt as before, but instead of three spoonfuls of granola (which apparently are a high calorie source), I have two spoonfuls of flax seed. I bought this in a 'healthy' supermarket when I was in the Haifa bay area on Thursday. As opposed to the picture on the left, they were selling it loose - and very cheap. I miss the crunchiness of the granola and the seeds tend to stick in my teeth - but hopefully the change will help me lose weight.

After yoghurt, I used to eat a slice of wholewheat bread with peanut butter - again, very nutritious but also full of calories. I have cut this out completely and so far it hasn't seemed to matter. During the morning, I munch on a cucumber or peach should I become hungry. Strangely, I haven't been hungry at all, except on Friday morning. Does this mean that I have been eating too much?

Lunch is as it has always been for the past year or so, a stir-fry consisting of chicken breast and seven or eight vegetables, all chopped up and cooked. I normally cook a batch on Saturday night and work my way through it during the week. Most weeks I add turmeric to the food, which naturally makes it yellow. I had thought that this spice didn't contribute anything to the taste, but there was a week when I had run out of turmeric and that week the food was distinctly tasteless. I stocked up on the spice in the Haifa supermarket on Thursday only to find it in my local corner shop on Friday.

The missing piece of the diet was the evening meal, for which I had been recommended to eat quinoa (here in Israel, it is pronounced ki-no-a, but I understand that elsewhere it is pronounced kin-wa). For some reason, I had thought that this was eaten uncooked, a form which looked distinctly unpleasant, but after reading about it (for some reason, the quinoa had slipped my mind otherwise I would have bought some of this as well in the supermarket), I bought a bag of it in the corner shop. I cooked it like rice - first fried a diced onion, then added a cup of quinoa and two cups of boiling water with some chicken flavour soup powder and let it simmer for 20 minutes - but the result was far more tasty than rice in my opinion. I've just been reading about the nutritional properties of quinoa and it seems that this should definitely become a staple of my diet. I'm going to cook it instead of rice on Friday night - it will be interesting to see what the children say, even though I can guess right now.

The other main change in my diet is no milk (apart from the yoghurt) and much drinking of water. It helps that the weather is hot as this encourages me to drink. I calculate that I am drinking about 3.5 litres of water (some of it as herbal tea, some as lemon juice) a day, causing commensurate visits to the toilet. Whereas before I would drink maybe only one cup of tea after returning home from work, I am now drinking that cup along with four or five glasses of water.

I have refrained from weighing myself so far. Next Sunday, I will have the stitches removed from my back after my minor operation, and then I will weigh myself in the clinic. It will be interesting to see what the results are.

Optimisations

The occupational psychologist (OP) is currently on holiday in USA, so I've had a bit of breathing space. I decided a week ago to try and optimise the use of the management program which I wrote for her; by optimise, I mean to make it work faster and to cut out unnecessary activities.

My first step was to include module logging; I added a table to the database to record which module was used when by whom, and of course added suitable code to each module to record its use, along with a form which displays the total use of each module per time period. The results, even after a few days, were illuminating. As the OP is absent, I was not surprised to see that very few reports were created. What was interesting was to see which data forms were used and how frequently.

It turns out that the most frequently used form - by far - is called EditMeeting. In this form, the user records a meeting for a specific person, choosing values from three different tables in the database along with adding a few dates and a price. My thinking went as follows: as rows are added to those tables infrequently, it might be possible to cache the tables in memory and load them whenever necessary, instead of hitting the database every time the form was loaded. In order to do this, it would be necessary to check first that there had been no change in the tables - meaning that I had to add a new table to the database to record the timestamp of the last change to the tables, and of course, add code to set this timestamp.

Once this had been taken care of, I considered how to cache the data and load. The approach that I took was to define three global clientdatasets in the program's data module, refresh the datasets' contents whenever necessary, and copy their contents to clientdatasets local to the form via cloning their cursors. Part of the information necessary came from this article. 

When I tested the new version of the program, it appeared to work correctly. I can't really judge whether the program runs faster as I have a complete copy of the database stored on my computer and so my access times are very fast. In the OP's office, the database is stored on a server and so access will naturally be slower. Theoretically, the program can't run any slower than it does now; it can only run faster. But will that improvement be noticeable?

After completing everything, I started thinking about this blog entry, which led me to consider another point which I had previously neglected. EditMeeting is implemented as a modal dialog box which is created every time the user wishes to add a meeting. This means that on every invocation, Delphi (or Windows) has to build the dialog box, load the data and then display the form. It occurs to me that it might be better to create the form once at program startup, using the auto-create function which I always delete, and then simply show and hide the form appropriately. As the form is modal, there is only ever going to be one instance of it visible on the screen at any given time (as opposed to many other forms in the program, which are non-modal). Making this small programming change should also shorten the time which it takes for the form to be displayed on the screen.

Of course, once the form is displayed, time becomes irrelevant as now it is the user who determines how long the form will be displayed on the screen. User time is measured in seconds whereas program time is measured in milliseconds.

The real test will be to sit with someone running the program in order to see what they are doing and why; only on the basis of those observations will I be able to formulate a better way of running the program. Such structural changes are always going to save more time than small optimisations in program code.

New beginnings

I've been quiet over the past two weeks because there have been new beginnings in my life and I have been waiting for them to develop before writing about them.

I've started my final year of the MBA; this term's course is Finance, which occupies itself with moving financial resources around in time. So far there have been two lectures, which haven't overly taxed my brain (although the same cannot be said for all the participants); we have learnt how much one should contribute every month in order to receive a large pension, and whether it is better to buy a flat as opposed to renting (no clear answer here, because it depends on the interest rate, the price and any number of psychological factors). This week we should be receiving our financial calculators, which will make calculating some figure to the power of 360 somewhat easier.

After a chance phone call, I have started to visit an alternative medicine care centre (David Lodge has his protagonist visit such a centre in his novel "Therapy"; he calls it the Wellbeing Centre, which is a good name), where I am receiving massages and acupuncture. Unfortunately, my visits will be put on hold for a couple of weeks as yesterday I had a few growths (BCC) removed from my back and I am not to return until the stitches are out. The first acupuncture session was slightly painful, sending electric shocks up my arm after the therapist presumably poked a nerve, but the second was painless. The sole massage which I have received also tended to be painful, but that's because there are muscles in my leg which hurt. Presumably, pain is good. 

It doesn't help my general condition that I have been diagnosed once again with anaemia and that I am overweight.

My goals are to improve my body tone (ie no muscle pain) and to lose at least 10kg in weight. Last night I met with what they call a 'Naturapath'; after measuring my height and weight, we talked mainly about what I eat. There are a few things which I have to cut out entirely from my diet, to be replaced by raw vegetables and plenty of water. I find it very difficult to eat raw vegetables, primarily because they were never part of my diet when I was growing up. I will try to eat cucumbers and carrots.

One interesting aspect is that I eat according to the clock as opposed to eating when I am hungry. I was told not to eat my slice of brown bread at 5:45am, but rather wait until my body tells me that I need to eat it. I got up three hours ago and all that has entered my mouth so far is one cup of cold water with squeezed lemon (something new), three cups of herbal tea and 200g yoghurt (with only one spoon of granola as opposed to three; this is to be replaced with flax seed); I don't feel hungry yet.

There was an interview with a famous Israeli basketball coach in the papers over the weekend; the most important part of the interview for me was the fact that he lost 15kg of excess weight thanks to acupuncture. When I mentioned this in the clinic, I was told that acupuncture can't make the body lose weight (good for them; they are not making any false claims) but that it can lower one's hunger. As mentioned above, I don't eat (yet) when I am hungry, but changing my eating habits and acupuncture may make a big difference to when and what I eat.

Closing off a previous post, I obtained access to the unicode complete Delphi XE. After making a few changes to my program (some of which will back propagate), I successfully compiled a unicode version of our flagship exam. More and more customers were having problems running the exam on their computers, and the new version has come just in time. The exam has no dependencies on outside libraries (such as special components or database access) which is why it was so easy to port the program. Converting other programs to unicode will be a major undertaking and won't be done unless there is no other option.