Firebird DB management tool - continuing the story

After a week of using my homegrown tool for managing Firebird databases, I have realised that it would help a great deal if I could edit live data - in other words,

• issue a query
• display the results of that query in a grid
• edit fields within that grid with immediate updating

I have already achieved the first two points above but editing the live data is something which I have never done before. There is a way of updating a database from a grid with TIB components but this would be extremely complicated to do with ad hoc queries. So with regret, I switched back to using the dbExpress components. As a result, I no longer have access to the query plan, but this was always a nice thing to have and not essential.

Unfortunately, dbExpress lacks the concept of a live query, because the components are not buffered. A query which references only one table and does not use parameters is a candidate for being live. I have searched high and low but have found no reference about how to check a query to see whether it references one or more tables (this management tool is not going to use parameters so I don't have to bother about this).

Just to make things clear, the following query would return a live query

select * from ranks

whereas the following query will not return a live query

select dockets.id
from dockets inner join statuses
on dockets.status = statuses.id
where statuses.future = 1

There is the canmodify property, but this is always false for SQLDataSets and always true for ClientDataSets, so basing any solution on this is doomed. It seems that the only way to solve this problem is to parse the query, at least to a certain extent.

I checked a few SQL parsers but they are far more complex than I need. Basically, all I need to do is find the 'from' keyword in the query; following that word will come the name of the first table in the query. I have to check what comes after the name of the first table - if nothing comes after the table name, or the next word is either 'where' or 'order', then there is only one table in the query and it can be regarded as being live. Anything else means that the query is not live. On the basis of this examination, I can set the editing flag of the grid.

After having written the above 'livequery' function, my program can now display and update simple recordsets or display complex recordsets.

Here is the very ad hoc parser from the program (global variable mem is a memo component which holds the user query):

function TForm1.LiveQuery: boolean;
// check what the second token after 'from' is
const
 EOI = #26;

var
 cmdlen, curpos: integer;
 ch: char;
 tmp: string;

 Procedure GetChar;
 begin
  inc (curpos);
  if curpos >= cmdlen
   then ch:= mem.text[curpos]
   else ch:= EOI;
 end;

 Function Token: string;
 begin
  result:= '';
  while ch = ' ' do getchar;
  while not (ch in [' ', ',', ';', EOI]) do
   begin
    result:= result + upcase (ch);
    getchar
   end
 end;

begin
 ch:= ' ';
 cmdlen:= length (mem.text);
 curpos:= 0;
 tmp:= token;
 while tmp <> 'FROM' do tmp:= token;
 tmp:= token; // this should be the first table name
 if ch = ',' then result:= false // select ... from table1, table2
 else if ch = EOI then result:= true // select * from table1
 else
  begin
   tmp:= token;
   result:= (tmp = 'WHERE') or (tmp = 'ORDER')
  end;
end;

Continuing the diet

I wrote a month ago about starting a diet. I have been keeping to the diet quite strictly (although I've been drinking more milk and eating less quinoa as time passes), as well as exercising in the evenings (walking swiftly for 30-40 minutes). Last week's acupuncture treatment left me with a stapled left ear, which is supposed to reduce my appetite. It's become clear to me that whilst it's important to reduce the amount of food that I eat, it's also a psychological problem which becomes apparent over the weekend (when it's all too easy to eat a biscuit instead of a cucumber).

Anyway, I weighed myself in the clinic on Friday, when I was having a 10 year tetanus booster. To my surprise, I had shed three kilograms from my weight!  I haven't felt any different, but I should point out that today I am wearing a pair of trousers which I haven't been able to wear in years - albeit without a belt. This is definitely good news, and the sort of positive reinforcement that one needs to keep on doing whatever has been done in the past month in order to continue losing weight.

Planning the SQL

Continuing from yesterday's post about the simple Firebird DB management tool that I wrote, I eventually found out how to display a query's plan. The answer was not to use the dbExpress components in order to connect to the database, but rather the IB components. These latter components are only for use in connecting to Firebird/Interbase databases and so have improved functionality at the cost of a less wide user base. This doesn't bother me as at the moment I am only supporting FB.

Firebird DB management

Normally I use a program called 'EMS SQL Manager 2005 for InterBase and Firebird Lite' for managing my Firebird databases (adding tables and fields, or correcting data) but every now and then there are problems with it. Then I use a much smaller program (both in scope and size) called 'MyWiSql', but this too has its problems. Slowly at the back of my mind, a need has been growing to write my own tool which would combine the best aspects of both programs and today I wrote that tool.

Here's a screen capture of the program

The program reads the database aliases and locations from the registry (that's the advantage of writing my own program - but of course, no one else can use the program); once a database has been chosen, its tables are selected, along with the fields of the first table. Queries can be made against the database (selects, updates, deletes, changing table structure) and the results can be shown in a different pane. The program keeps a log of the queries - I often find that when using one of the management tools, I issue a 'select' query, update a value then issue the 'select' query again to ensure that the change has been made. Now I can simply copy the original query from the log instead of typing it again.

Normally, one has to press the 'execute' button in order to execute a query but this quickly became a nuisance so I added some code which causes the query to execute whenever a semicolon is typed (this character is normally used as an SQL statement separator).

I even added a pane which displays the SQL trace, but the results of this have been disappointing. I had hoped to see the execution plan (which MyWiSQL manages to display) but I have yet to achieve this. Once can hope, but there seems to be very little information about the SQLMonitor component, which displays this information. I may have to resort to reverse engineering MyWiSQL, but I'm very doubtful about this.

Otherwise, this will be a very useful addition to my programming toolbox.

Fictional MI5

Along with the Stella Rimington book ("At risk"), I've also been watching the BBC series "Spooks". The program is more action orientated than I would have liked, which made sense when I realised that the program is about the counter-terrorism section. Similarly, Liz Carlyle (LC) belongs to the counter-terrorism section.

I ordered an received the second LC book, "Secret asset", and read it greedily over Thursday night/Friday morning. This is more cerebral/less action orientated than its predecessor and thus found more favour in these quarters. Whilst the prose is nothing to write home about, it tells an interesting story and I'm sure that a second and more sedate reading would be rewarding.

The book begins in a very similar manner to its predecessor, so much so that I'm sure a page was lifted (the description of the recruitment of the agent whose cover name escapes me). But the agent is shortly discovered and killed; here the resemblance between the novels ends. Ostensibly, the book is about the leads thrown up by this agent and a task force assembles to follow them up. But on the side, LC is given a separate task: find an unactivated mole within the ranks of MI5.

The reader, from his privileged position, is able to see how the two strands intertwine. Having two separate strands in a story is much more rewarding than one, and their combination and resolution lead to a much more satisfying finish. So author Rimington is showing welcome signs of sophistication.

But! In the terrorism strand of the book, much is made of the mistakes in tradecraft that the terrorists make (as an aside, it is inferred that MI5 can tap any phone at will, including mobile phones; I have my doubts about this). But there is no comment about the poor tradecraft of the mole, who blows an operation then has the agent killed. After a third and similar lapse, one of LC's colleagues becomes suspicious and begins suspecting a mole himself; this conclusion is made whilst being unaware of LC's activities.

The mole, in retrospect, has several damaging conversations and disseminates information which later turns out to be disinformation. Maybe he was counting on the obscurity of that information and the low possibility of his lying being caught, but it was a dangerous thing to do and led to his downfall.

The mole is not a patch on Bill Haydon ("Tinker, tailor, soldier, spy") and should be ashamed of his poor performance in this role. Real moles are much harder to catch, and the fact that he had never been activated should have made it impossible to have caught him.

There is no real reason for MI5 to have heard about this 'unactivated mole' in the first place, although this mole's actions quite likely would have led to his being discovered anyway. The person who told MI5 had no reason to do this, and this strand of the story invokes several characters who are peripheral - if not totally irrelevant - to the story. Was the author trying to confuse the reader?

So: I enjoyed this book more than its predecessor, but it's clear that author Rimington has a long way to go before she even catches up with the shadow of 'Tinker tailor'. A film version of the latter, incidentally, has recently been made and I look forward to seeing it. It will be interesting to see how well it manages to translate the story and whether it too suffers from the same problem as the famous TV adaption which often has George Smiley (as played by Sir Alec Guiness) sitting and staring into space (or polishing his glasses on his tie).

Mere anarchy is loosed upon the world

Turning and turning in the widening gyre
The falcon cannot hear the falconer;
Things fall apart; the centre cannot hold;
Mere anarchy is loosed upon the world,
The blood-dimmed tide is loosed, and everywhere
The ceremony of innocence is drowned;
The best lack all conviction, while the worst
Are full of passionate intensity.

("The second coming", W.B. Yeats)

The above refers to what is currently happening in Turkey and Egypt, re Israel.

A resizable dialog box

I can't claim that this was my idea, but it can be useful in certain circumstances. Normally a dialog box should be of fixed size; the controls are normally at fixed locations and so enlarging the box doesn't really change anything. For an extreme example, try maximising a dialog box. Thus the dialog box style doesn't allow resizing.

But every now and then, it can be useful to have a resizable dialog box: let's say that the only controls within the dialog box are a rich edit control (possibly connected to a database) and a pair of buttons. Using the anchor property, the rich edit can have all four anchors set, so its size will grow as the dialog box grows. The button on the left has its bottom and left anchors set whereas the button on the right has its bottom and right anchors set.

Here's the code

type
  TSizeDemo = class(TForm)
    Memo1: TMemo;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    PaintBox1: TPaintBox;
    procedure PaintBox1Paint(Sender: TObject);
  private
  protected    procedure CreateParams(var Params: TCreateParams); override;
    procedure CreateWnd; override;
  end;
implementation

{$R *.dfm}

procedure TSizeDemo.CreateParams(var Params: TCreateParams);
begin
 inherited CreateParams (Params);
 Params.Style:= WS_CAPTION or WS_SIZEBOX or WS_SYSMENU;
 Params.ExStyle:= WS_EX_DLGMODALFRAME or WS_EX_WINDOWEDGE;
end;

procedure TSizeDemo.CreateWnd;
begin
 inherited CreateWnd;
 SendMessage (Self.Handle, WM_SETICON, 1, 0);
end;

procedure TSizeDemo.PaintBox1Paint(Sender: TObject);
begin
 With PaintBox1 do
  DrawFrameControl (Canvas.Handle,
                   Rect (Width - 12, Height - 12, Width, Height),
                   DFC_SCROLL,
                   DFCS_SCROLLSIZEGRIP );
end;

end.

The paintbox is necessary in order to display the sizing handle in the bottom right hand corner. Here is the link to the original article.

At risk/2

Here's part of review of another of Stella Rimington's books; I think that the author makes his/her point more eloquently than I did.

What I hate most is that Liz Carlyle is portrayed as being completely omniscient. Every hunch she has, every deduction she makes, any inferences she makes from questioning people - they're always right! This feeling that MI5 could function effectively with only one member of staff - superwoman Liz - spoils it for me. She's the one who could tell you what the train driver had for lunch just knowing his shoe size. Shame - because otherwise a good read.

At risk

"At risk" is the title of the first novel written by Dame Stella Rimington, who received her damehood for running MI5. Not surprisingly, the book (and the following series) is about a female agent in MI5 - virgin territory for me (in terms of fiction, that is). Whilst Rimington is no literary stylist, her prose is far better than I had been led to believe, and reading the novel was an enjoyable experience.

As usual, the first few chapters provide background about several characters who appear later in the book; the story as such gets going at around the seventh or eighth chapter (the chapters themselves are very short). There are a few early chapters which could easily have been cut without causing damage to the story; they don't provide much background information and serve only to distract the reader.

Once the story gets going, it is an exciting read which is more similar to a police procedural than a spy novel. The story is a manhunt after two terrorists, in which protagonist Liz Carlyle provides most of the directions for the hunt. I doubt that in real life MI5 officers become so involved in such matters, and indeed, at a late stage in the story Carlyle feels that her part of the job - the cerebral analysis - is over, even though she eventually produces the final piece in the puzzle, the terrorists' target.

Carlyle does become some kind of 'wonder woman', divining intents correctly with too little input. Nowhere is this more apparent that the final chapter: in the previous scene, Carlyle suffered shell shock when the terrorists are apprehended and is hospitalised. She wakes up the next day with everything very fuzzy, but manages to make a conclusion on very slim evidence. I can't see how she had the time or the mental energy  to achieve this, and as a result the book finishes on an unbelievable note.

I enjoyed the book sufficiently to order the next installment. I hope that this is more about her life in the office as an agent-runner/analyst and less about running around in the field.

Financial report

Sometimes people ask me how my studies are going. I ask myself this question frequently. Here is the answer....

The following is an exercise which we completed at the end of yesterday's session. It takes longer to write the question down than it does to solve it.

A company is examining the possibility of investing in the following project: the project requires an initial investment in equipment and supplies at a cost of $100,000. Operating the equipment will require an improvement in the water filtering capability which will cost $20,000. The project also requires working capital of $54,000; this capital will be kept at the same level throughout the project's lifespan.

The company completed a marketing survey which examined the demand for the project and paid $10,000 for this. The company expects that the project will last for eight years, in which time the income will be $45,000 for the first five years and $30,000 for the next three years. Expenses are expected to be $4,000 per year.

Running the project will cause a loss in income of $3,000 per year from other projects of the company. The company depreciates the cost of equipment at a constant rate of the project's lifetime with an expected residual value of $0. The company's economists expect that at the end of the project it will be possible to sell the equipment for $10,000.

Tax is paid at the rate of 30%, on cash income from the same year. The project will be financed from equity (shares) and bonds which have a nominal interest rate of 5.8% per annum. The average weighted capital cost of the company is 12% per annum.

Would you recommend the company to carry out the project? Present a cash flow statement and NPV calculation in order to support your recommendation. 

Straightforward, no? Yet this is the sort of thing which financial MBAs face every day (ok, not every day, but maybe every few months). I imagine that everyone in the class understood the question and all the items contained within, although I doubt whether everyone could solve it without being shown how.

Here comes the answer to the question which I posed at the beginning of this post (how are your studies going?) - I wouldn't say that it's easy but the course isn't hard (I solved the above question while the lecturer was about half way through explaining the items). It just doesn't do anything for me; it doesn't exercise my intellect. I'm not enjoying this course as much as I have enjoyed others, although of course, my enjoyment is irrelevant to the final mark or whether I am going to use this material in my daily life. But when one studies primarily for one's enjoyment and intellectual stimulation, then the course should provide that stimulation.

By the way, the project is not recommended, with an NPV of -$9,179.