Who plays with whom?

In 1996, I started writing a cd database program; this would have been one of my first serious efforts at writing database programs in Delphi. The program would store physical information about musical cds (the cd's internal id number, how many tracks, how long each track lasts), and I would add data such as cd name, song title, who made the cd, who played, who composed, etc. Once this was done, I could get out of the database arcane facts such as how many tracks were composed by Richard Thompson, on how many tracks Simon Nicol played and so on. Maybe the highlight of the data mining was calculating on which tracks both RT and SN played.

Under the hood, the program was not written very well. My original source of information about Delphi database programming used the 'ttable' component all the time, so tables were lavishly scattered around the code. Somewhere around 2003, I started replacing tables with queries, at least in the places where I understood what was happening. Such queries made the code much simpler to understand, with a few terse but clear lines replacing such monstrosities as a record by record search of a table.

The 'who plays with whom' form was an exceptional case of using tables, most of them temporary, and presumably because of this form's complexity, I had stayed away from trying to improve it. Yesterday, I was having a routine look through the program, changing bits and pieces to current standards, when I came across this form. I knew what the code was supposed to do, but didn't like the way it was being done - and in fact, I cringe at what I had written twelve or thirteen years ago. Well, I didn't know better.

As it was dog walking time, I had the perfect opportunity to think about improving this function. I was fairly sure that I could replace almost all of the code (some of which dealt with finding which songs were common to two or more people, and some of which dealt with finding data about those songs from various tables - as if I had never heard of the 'join' statement in SQL) with one moderately advanced query. When I came back from our half hour walk, I set to work, and came up with this:

select tracks."track name", artists."artist name", cds."cd title", tracks."track id", count(*)

from musician, tracks, artists, cds

where musician."track id" = tracks."track id"

and tracks."artist id" = artists.id

and tracks."cd id" = cds."cd id"

and musician."person id" in (0)

group by tracks."track name", artists."artist name", cds."cd title", tracks."track id"

having count(*) = :an

The '(0)' gets replaced by a list of people (their id numbers), so basically the query returns a list of tracks (their name, the artist and the cd on which they appear) and how many times those tracks were returned from the musicians' table, given a list of musicians. The musicians table, being a link table between people and tracks, has three fields: a meaningless id, a person id and a track id, where each record means that person 'a' played on track 'b'. In retrospect, the record's id field is unnecessary but harmless; but I have refrained from changing the database structure as this would entail multiple changes in the program code.

The parameter 'an' is the number of people contained in the list passed to the query. The query returns a list of all the songs on which one or more people in the musicians' list played, but the count value can vary: it could be one (in which case only one of the people in the list played on the song), two or more. This value can only be checked after the query has completed, which is why the final line is a 'having' clause, something which I rarely use. The query must return only the songs on which all the people played, so the 'count' field must equal the number of people in the list.

Holy Grail, part 2

It turns out that the holy grail is not so easily found.

The code which I posted here worked perfectly at work. So when I came home, I plugged it into a real program ... and of course, there was no bold text. Hmmm, I thought to myself, maybe I've written this code before and discarded it because it doesn't work on my computer. Maybe it will work on my client's computer, which is the main point.

Then I took the dog for a walk. Whilst on the walk, I thought about this 'bold' problem, and decided that I should attack it with a more scientific approach. First see whether the demo program does print bold text on my computer - after all, I'm using the same version of Word (2003), and the code which I saw on the internet was several years old, meaning that the functionality has existed from much earlier versions of Word.

So when I came home, I ran the original demo ... and got bold text. I then took the specific code for the bold text and plugged it into my program. Still bold text. I replaced the literal string being printed bold with what I actually want as bold - no bold. Aha! The moment of revelation! Could it be that this code prints English in bold text, but not Hebrew? This wouldn't be the first time that I've seen a gotcha with Hebrew text. As I say, maybe I have been here before and discarded the (almost) correct solution because it didn't print Hebrew text in bold. This time, I knew that the code was almost correct, and this gave me the impetus to carry on.

The next step was to see what Word does itself, by recording a macro and performing the required operation, then by looking at the resulting macro. I often do this, but sometimes there's a problem recognising the variable which Word uses for the operation. Anyway, this time I could see what was going on: first, there was the call to set the value of the 'bold' property, and then there was an extra call, setting the value of a property called boldbi (presumably a contraction of 'bold bidi', ie bidirectional, where 'bidi' is often used in Windows when printing right to left languages such as Hebrew and Arabic). Based on this knowledge, I then added a call in my program to set the value of this new property - and then my Hebrew text appeared in bold! So here is the complete 'bold' procedure:

Procedure Bold (const s: string);
begin
 wrdSel.Font.Bold:= 1;
 wrdSel.Font.BoldBi:= 1;
 wrdSel.TypeText (s);
 wrdSel.Font.Bold:= 0;
 wrdSel.Font.BoldBi:= 0;
end;

wrdSel is a variable global to this procedure, declared as per yesterday:

wrdSel:= wrdApp.selection.

Holy grail found

Every now and then, a 'programming holy grail' emerges: this is normally something that I very much want to learn how to do in a program, but can't find out how to do it. Such a holy grail has to be something incidental - otherwise the program's development will be stymied and never completed. The latest holy grail is something which has been bothering me for some time - how to print from a Delphi program bold text within a Word document.

Creating a Word document and adding contents to it is called automation; whilst there is a fair amount of information about this on the internet, it tends to be basic, such as how to create the document, add some text and then close it. The finer points of formatting tend not to be discussed in internet articles. As it happens, I bought several months ago an excellent book on Office Automation, "Microsoft Office Automation with Visual FoxPro"; the only problem with this book being that it's written for Visual FoxPro (duh!) whereas I'm using Delphi, and the syntax is different. It's not too different, but sometimes sufficiently so and not clear enough which object has which properties and methods.

To create a Word document, and some text and then close it is done like this in Delphi:

var

 wrdApp, wrdDoc: variant;

begin

 wrdApp:= CreateOleObject ('Word.Application');

 wrdApp.visible:= false;

 wrdDoc:= wrdApp.documents.add;

 wrdDoc.select;

 wrdApp.selection.typetext ('Wow - I''m adding text to a Word document from  Delphi');

 wrdApp.selection.typeparagraph;

 wrdApp.visible:= true;

 wrdDoc:= unassigned;

 wrdApp:= unassigned;

end;

All calls to the wrdApp object are slow, and so I've found all sorts of ways to improve the duo of typetext and typeparagraph; the latter call is unnecessary, as appending a carriage return (character 13) to the string being printed is enough to force the cursor to a new line. When printing blocks of text, I now add the text to a wide string variable, and only pass that variable to the typetext method when all the text has been added.

So how does one add bold text? As it turns out, it's very simple, although this method negates the above optimisation. First of all, I define a new variant, wrdSel:

wrdSel:= wrdApp.selection;

This by itself simplifies the typing of the original code, for now I can replace wrdApp.selection with wrdSel, thus saving 10 characters each time. This may not seem important, but it does save wear and tear of the fingers. The 'selection' object has a 'font' record which holds a 'bold' property, and in order to print bold, all one has to do is turn this property on (and afterwards turn it off). So the simple code now becomes

var

 wrdApp, wrdDoc, wrdSel: variant;

begin

 wrdApp:= CreateOleObject ('Word.Application');

 wrdApp.visible:= false;

 wrdDoc:= wrdApp.documents.add;

 wrdDoc.select;

 wrdSel:= wrdApp.selection;

 wrdSel.typetext ('Wow - I''m adding ');

 wrdSel.font.bold:= integer (true);

 wrdSel.typetext ('bold');

 wrdSel.font.bold:= integer (false);

 wrdSel.typetext (' text to a Word document from Delphi' + #13);

 wrdApp.visible:= true;

 wrdSel:= unassigned;

 wrdDoc:= unassigned;

 wrdApp:= unassigned;

end;

Fortunately, I doubt whether I'll be using bold text very much in the body of a print-out; it's more likely that a paragraph heading will be in bold and the paragraph body in regular text. In this case, all the body can be loaded into one string and then printed.

I don't know how long I've been searching for this holy grail, but I'm pleased now that I've found it. I imagine that soon there'll be a new grail to search for.

Heron

My previous entry, a ten year old review of Heron's "River of fortune", seemed to come out of nowhere. What inspired it was receiving a double cd of all the tracks that Heron recorded for Pye's "progressive" label Dawn in the early 70s.

I made reference to the "penny tour" - this took place at the beginning of November 1970. Reasoning that for the admission price of one old penny there was no way that I could lose, I went to the concert. I know that four groups appeared, but two obviously made no impression as I could never remember who they were. One of the two groups that I do remember was Comus, who might have had Lindsay Cooper (later to play with National Health and Henry Cow) in their ranks at that time. The other group was Heron.

So impressed was I with their performance that I immediately went and bought their eponymous album. If I remember correctly, this was via mail from some pre-Virgin outfit which offered discounts, so I have no way of knowing whether the album actually hit the shops.

The simple arrangements - the record was recorded "live" in a field, supposedly with no overdubs - caught and tickled my ear, and some of the songs accompanied me throughout the years. Unfortunately, an accident with a record player managed to leave the opening song on side two, "Lord and Master", with a huge scratch, making it unplayable.

At the beginning of the 90s, in a fit of nostalgia, I discovered that there had been a cd release for Heron called "The best of ... plus", which featured songs from both of their albums as well as a few oddities. Whilst the cd was better than nothing, it wasn't really what I wanted, which was the first album in its entirety, sequenced as per the vinyl. "River of fortune" was interesting in its exhumation of the old songs, but sounded too modern for the material.

So I was delighted when I found the new compilation, which begins with the first album in toto. Here's the sole review at Amazon, which sums things up nicely: I can't believe how good this album is - imagine if in the summer 1970 Simon and Garfunkel, the Beatles and Crowded House (no kidding) got together and recorded a hazy summer filled acoustic album in a field complete with heavenly vocal harmonics and real background bird song and field sounds - this album is that and more. You will NOT be disappointed, a true gem of a find; how this band didn't end up massive is a true mystery to me. I'll put money on it being your new fave album within 10 minutes of listening.

Apart from the delight of being reunited with old friends who haven't lost their charm, it's also very instructive to compare the songs from the first album with their other material. Most fascinating from my point of view is "Harlequin 2 (long version)", an alternate take of this exquisite song. I remember from the penny concert when Gerald Moore introduced it by saying that "harlequin is such a lovely word" - so much so that he wrote several numbered Harlequin songs. After the song part of H2 ends, there is a brief pause and then a longish (by Heron standards) instrumental section, followed by variations on the song's chorus. The instrumental section is adorned by several instruments being played out of time, explaining why the song was cut at the end of the vocal section and so appeared enigmatic. I wrote that this is an alternate take; it may be the same basic take as the final version, but mixed slightly differently and without the Hammond organ overdubs, which give the lie to the "recorded live in a field" statement. Actually, H2 is the only song which sounds as if overdubs were added.

I am reminded of the quote which I put in the 'River of fortune' review: we shortened a couple of the original recordings because of cock-ups. The out of time instrumental contributions certainly fall into the cock-up category.

There is another extra song, 'Rosalind', which sounds like the other songs on the first album. In my review I compared Heron to Crosby, Stills and Nash, but this song sounds much more like Simon and Garfunkel. In fact, listening to the entire first album reveals a division in sound between two camps: on the one hand there are Roy Apps and Tony Pook, whereas on the other hand there is Gerald Moore. The Apps/Pook songs have two part harmonies and are gentle (for example, "Car crash", "For you"), whereas the Moore songs tend to be more adventurous musically (the aforementioned "Harlequin 2") and sound closer to pop. The best songs, naturally, are when everybody contributes fully.

This division becomes clearer on the second album, when bass and drums were added to the Heron mix (and I can also hear electric guitar here and there). All the charm of the first album has been lost, replaced by some homogenous acoustic pop sound which is not particularly good. I have only listened to a few of the songs from the second album; those which included on the 'Best of' compilation are similar but not as good as those from the first album, and those which weren't included are dissimilar and nowhere as good.

It seems very much like I am going to confine my listening to the first album and recommend it to everyone.

Heron - River of fortune

Below is a review which I wrote of the above disc ten years ago; it appeared on the online review magazine, The Greenman Review, but is no longer available there.

------------------------------------------------------------------------------------

If your taste runs to obscure British groups, then you're in luck as you can't get more obscure than Heron. Originating in a Maidenhead folk club in 1968, this group (Tony Pook - vocals; Roy Apps - guitar, vocals; Steve Jones - accordion, piano; Gerald Moore - guitar, mandolin, vocals) achieved a certain amount of notoriety by eschewing the recording studios and making their first eponymous record in a field. In an attempt to publicise their record, they took part in what was known as the 'Penny tour' - the price of admission to the concerts being one (old) British penny. Unfortunately, Heron the record didn't sell vast amounts in the record shops, and although a second album ("Twice as nice at half the price" - also recorded in a field) was released, this too was a commercial failure, and so the group split up - 25 years ago [1972].

Further on down the road in 1997, the "boys" decided to get together again (without Gerald Moore, who had found some success with GT Moore and his reggae guitars, but with Gerry Power), and "River of Fortune" is the result. Of the sixteen songs on this disk (70 minutes long), eight are rerecordings of songs which originally appeared on their first album, three are rerecordings of songs from their second album, and five are new recordings of songs which date from that era but didn't appear on any of their records.

And now to the music: Heron, in my humble opinion, could have been the British answer to Crosby, Stills and Nash: acoustic guitars, a bit of organ or accordion, and sometimes three piece harmonies.

The songs are simpler, less "arty" and more "down to home" than CSN, being generally less pretentious (although Gerald Moore's lyrics tend to the abstract). In keeping with the Heron tradition, ROF was recorded in a field, but the production is much more modern, with reverb added to the vocals and instrumental overdubs added at will. While this intially detracts from the special feel of their earlier albums, it allows one to listen to ROF without an anachronistic feeling.

The songs are pleasant and melodic, well-played without being over-arranged, and there is plenty of variation. Whereas first time around, the songs were short (only one song on their first album was over three minutes long), on this one they are much longer. As Steve Jones wrote to me, "With regard [to] lengthening songs, has it occurred to you that the earlier versions may have been shortened? One of the reasons for including some of the songs was so that we could do them the way they were originally intended (more to the point, we shortened a couple of the original recordings because of cock-ups: it's the problem with spontaneous recording)."

An example of this is Moore's "Harlequin 2", one of my favourites from the original album; here the verses are arranged much as they were, but there are long instrumental interludes, which weren't present on the original. The title song starts off with a burst of Spanish guitar and some moody synthesizer, but then moves into a jolly singalong for the chorus; this track is also extended and features a live-sounding vocal coda.

The general sound of the disk is much fuller, this being due to the influence of Steve Jones the keyboardist, who also served as producer. Obviously he was quite limited 30 years ago as to which instruments he could take into a field, but also the range, variety, quality and usage of keyboard instruments has changed greatly during that time.

The complete track list is as follows: "Car crash", "Lord and Master", "River of fortune", "Wanderer", "Yellow roses", "Stars", "Friend", "I wouldn't mind", "Harlequin 5", "Adagio", "Carnival and penitence", "Summer in the city", "Harlequin 2", "Upon reflection", "Smiling ladies", "Gypsy trails".