Properly converting a Hebrew database to Unicode

Since the great computer outage¹, my capabilities of developing Delphi programs has dropped to minimal. I've been able to work on the Prolog interpreter primarily because this uses no database and no Hebrew, so I can develop it with Delphi 7 using a virtual computer on my laptop. I used to say that I would convert the Occupational Therapist's programs to Unicode only if someone put a gun to my head - well, I've reached that stage and have to start converting her programs. I find it rather ironic to see that the 'This day in blog history' has two blogs from five years ago about porting an application to Windows 10/Delphi 10.2! The applications may have been ported but the databases certainly weren't.

As working in Delphi 10.2 on my laptop computer (with 8 GB memory) is painful, I've taken the step of ordering a new desktop computer - this will have 16 GB memory, a much faster processor, a 1 TB disc and a larger screen. To think that my first ever computer had 512 KB memory and that adding a further 128 KB was considered a huge win! This new computer is due to be delivered later today.

In the mean time, I decided to convert one of the OP's programs that she's been having problems with recently. This program has "only" 15 tables, two of which are not being used any more (for licensing purposes) and a few have only integer fields. It's the tables with the character data - like the People table - that cause all the problems in converting data. Last night, I created the new database using 'SQL Manager Lite'; I defined the default character set for the database as UTF8, as necessary for Unicode. I defined all the tables using the DDL data, modifying this only when there were character fields.

Today I started work on the conversion tool. Some time ago, I worked on a similar tool for converting the Manager database - this has slightly over 100 tables, many of which contain character data; I wrote code that converted about half of the tables. The first table that I worked on this morning had only integer fields so that wasn't a problem, but the second had a character field, and this is where the problems began.

Simply reading from one database and saving to another is not good enough when the data has to be changed. I tried various methods, such as reading the old data by means of a conversion to AnsiString then trying to convert it to utf8 via the AnsiToUtf8 function but this didn't work either. I wasn't even sure that the field in the new database was defined as utf8 because the 'column character set' was empty.

After lengthy consultation with CoPilot, I ended up using the following obscure to read the old data then save it as unicode. 

    for i:= 8 to 15 do
     begin
      raw:= qGetPeople.fields[i].AsBytes;
      s:= TEncoding.GetEncoding(1255).GetString(raw);
      utf8:= TEncoding.Utf8.getbytes (s);
      p:= qInsPeople.Params[i];
      if Length(utf8) > 0
       then p.SetBlobData(@utf8[0], Length(utf8))
       else p.Clear;
     end;

CoPilot tells me that there's a bug in the database driver and there might be one in the SQL manager as well, so using a very low level method of writing the data as a blob should overcome these bugs. CP also gave me the code for checking the character set of the appropriate fields; I discovered that fortunately they are defined as UTF8. When I look at the character fields in the Unicode database, they are in Hebrew and not some form of gibberish.

Today I'll finish converting the entire database (fortunately not too large) then tomorrow I'll start on converting the program itself. Who knows what problems await me.

Internal links
[1] 2041

---

This day in blog history:

Blog #	Date	Title	Tags
328	23/01/2011	Sumptuous Sunday 4	Cooking
541	23/01/2013	The simplest ideas are normally the best	ERP, Problem solving
920	23/01/2016	Vinyl log 27 - 23 January	Vinyl log, 10cc
1371	23/01/2021	Porting an application to Windows 10/Delphi 10.2 (part 3)	Programming, Delphi, Unicode
1372	23/01/2021	Porting an application to Windows 10/Delphi 10.2 (part 4)	Programming, Delphi, Unicode
1576	23/01/2023	More David Crosby: Guinnevere	CSN
1892	23/01/2025	Garth Hudson RIP	Obituary, The Band

Have I completed the Prolog interpreter? Take 2.

Just over a month ago, I wrote¹ So now I've exhausted all the goals that I originally wanted from a Prolog interpreter. It can solve the following queries (or parse the statements), each of which exercises a different part of the syntax as well as including goals that include multiple clauses. Of course, about a day later, I was adding more functionality to the interpreter.

Last week, I wrote² that I was working with a Prolog program that plays the game 'tic-tac-toe'. The game at first was irrelevant; the program contained a few constructs that my interpreter could not handle. So once again CoPilot and I started adding what was missing: most of this was simple (for example, the i/o functions write/1, nl/0 and read/1) but one was extremely difficult.

It turns out that the simple comma has two different functions in Prolog. Until now, the comma separated arguments to a predicate; for example, the comma in the clause 'parent (noam, netta)' separates the two arguments to the 'parent' fact. But in another context, the comma behaves differently: this is when there is a list of goals to be solved. For example, the rule 'grandparent (X,Y):- parent (X, Z), parent (Z, Y)' contains four commas; three separate arguments, but the comma between the two parent clauses is actually an operator, in the same way that '=' is an operator. If this comma were not an infix operator, the body of the rule would be written as , (parent (X, Z), parent (Z, Y). It took a great deal of time to get the parser to parse an expression of this kind successfully. A casual user would be completely unaware of this problem.

Of course, after the parser succeeded in parsing the expression, the interpreter could no longer solve the rules; when solving, that compound statement has to be turned back into two separate clauses. This is similar to the manipulation performed on the goal list in order to implement the 'arg' predicate. This, at least, was relatively simple compared to the challenge of parsing the two conjoined clauses.

Now the tic-tac-toe program could load and I could get the computer to play itself. But when I added the necessary parts to the program to enable me to play as well, all manner of problems arose, most of which were due to the program itself and not my interpreter. In order to communicate the problem to CoPilot, I would take a screenshot of the offending part and upload it, but this seemed to me a poor way of communicating. Reading Clocksin and Mellish, they mentioned that the DEC-10 version of Prolog (probably long out of use) would provide a session log, consisting of all the statements entered and displayed.

I thought this a good idea, and during a dog walk, I considered ways of implementing this. When I put it to CoPilot, it had a better idea: create modified versions of write/writeln and readln, and use these instead of the normal procedures. My private procedures write both to the screen and to the session log; very simple to implement, and very useful. Now I could upload complete sessions to CoPilot for analysis.

Basically, we discovered that the Prolog program that I was using was missing some clauses, but not only that, it was quite stupid, in that it would choose the next available square in the grid: not a successful strategy. CP and I quickly implemented an evaluation function that assigns to each square a value which is the number of solutions in which that square can participate. On a 3x3 grid, each corner can participate in three solutions whereas each middle square can participate in only two; the middle square is the most valuable as it can participate in four solutions. I also wanted that the computer would announce its moves, instead of having to look at the grid and figure out what had changed.

Work finished on late Friday afternoon: it seemed that the program was tantalisingly close to completion but I had run out of time. On Saturday morning, I ran the program and saw that it was 99% complete, so with a few additions, we now had a Prolog version of tic-tac-toe that works quite well (the result is always a draw) as well as a much improved Prolog interpreter. Here's the session log for the final run:

> ?- consult (ttt).
ttt consulted
Yes.
> ?- playo.
You play X by entering integer positions followed by a period.
[1, 2, 3]
[4, 5, 6]
[7, 8, 9]

|: 1.
[x, b, b]
[b, b, b]
[b, b, b]

I play 2
[x, o, b]
[b, b, b]
[b, b, b]

|: 5.
[x, o, b]
[b, x, b]
[b, b, b]

I play 9
[x, o, b]
[b, x, b]
[b, b, o]

|: 3.
[x, o, x]
[b, x, b]
[b, b, o]

I play 7
[x, o, x]
[b, x, b]
[o, b, o]

|: 8.
[x, o, x]
[b, x, b]
[o, x, o]

I play 4
[x, o, x]
[o, x, b]
[o, x, o]

|: 6.
[x, o, x]
[o, x, x]
[o, x, o]

Cats game!
Yes.
> quit.

Incidentally, I didn't know what the expression "Cat's game" was supposed to mean. Could it be that someone whose nickname was Cat wrote the program and that this was her way of immortalising herself? No, says CoPilot. “Cat’s game” does mean something. It’s an old American expression for a tic‑tac‑toe draw. The idea is that when neither X nor O can win, the game is “for the cats” — meaning worthless, unwinnable, a stalemate. No cats were involved in the writing of your Prolog code.  I would have known this had I read the Wikipedia article more thoroughly.

I ran a new game: normally I would start with square 5 but just to keep things interesting, I decided to start with square 1 instead. The computer choosing square 2 was not clever; this probably means that the simple rule that chooses the next available square appears before the rule that used the evaluation function. As rules are evaluated in a top-down order, the position of a rule can be critical. After thinking about this, I rearranged the 'moves' rule so that they would appear according to their value; thus the first rule would cause the computer to choose square 5, then square 1, etc. When I ran the program with the rearranged rules, the computer played much better. Again, I chose to start with square 1 deliberately to see what the computer would do; it chose square 5. I then chose square 3, so the computer had to choose square 2, otherwise I would win with my next move. In doing so, the computer had set up the 2-5-8 combination, so of course I had to chose square 8. The computer then chose square 7 - this doesn't contribute anything, but it shows that the computer is playing according to the order of the moves: 5,1,3,7,9,2,4,6,8. As squares 5, 1 and 3 were occupied, the next unoccupied square according to the precedence was 7.

So once again I wonder, have I completed the Prolog interpreter?

Internal links
[1] 2047
[2] 2059

---

This day in blog history:

Blog #	Date	Title	Tags
230	18/01/2010	Keith Tippett Group – "Dedicated to you, but you weren't listening"	King Crimson
538	18/01/2013	Nic Potter RIP	Obituary, Van der Graaf Generator, RIP
918	18/01/2016	MP3 headphones (2) / The scientific method	Programming, MP3
1370	18/01/2021	Covid-19 vaccination (3)	Covid-19