Of GUI conventions and cross platform portability

Well, this post I guess belongsin the "and stuff" category of my blogg. I am reading Joel Spolsky's book "Joel on Software" now, which is a collectioon of Joels writings on different software things. I will post a review in due time, but for now, I wanted to touch on another subject that this book brought up, and which is related to my new version of MyQuery that is soon coming up, which is this: Who controls the conventions of the GUI of an application? There are several possible answers to that question, such as:

• The application - I.e. the application code determines what keys are used for cut, paste, open file, the order of standard buttons (Open,Close) the look of the windows and other things that the application does.
• The GUI - I.e. Windows / KDE / Gnome / OSX or whatever GUI framework you use determines the standard functions as listed above. As for any applicatioon specific functions, that is still left to someone else.
• The user - I.e. the user can determine this. Note that this can be done in several ways, i.e. the user can control the Application or the GUI Framework itself.

Usually, there is some mix of this in most applications. Some keys and conventions are left to the GUI Framework (Cut / Paste / Open File shortcut keys, Dialog button ordering etc), some are hardcoded in the application, and some are configurable by the user. The question is how we determine the mix of these things.

To put it differently. If I have a cross platform application, do I follow the conventions of the different platforms I run on, so it looks like a Windows app on Windows, a Mac app on OSX etc? Or do I create an application that works more or less the same, independent of platform? The latter path is choosen by, for example, Adobe, to a large extent. This means that Photoshop works like Photoshop, not like any other Windows app, assuming we run on Windows. For someone using Photoshop day in and day out, this is great, the application works and looks the same, independent of platform. For someone like myself, who use it now and then, this is a major pain. Things don't work as they do in any other Windows app I know, PS Windows don't have a "close" button on the top right corner (at least not last time I looked) and key combinations that I am used to using doesn't work.

Which path to choose? I don't really know, I am just raising the issue. And while I was thinking about it, I decided to have the new version of the MyQuery GUI much more flexible, with many more functions controllable by the user. This is how I would like PS to work: Have it configurable and provide useful defaults: Work like PS, Work like Windows, Work like KDE etc. To an extent, this is what I am doing in MyQuery now. Now, I don't have all the time in the world, but i have relaized one thing: Having configurable function keys for menu options is real easy in Windows. These are called "Accellerators" in Windows, and I have built a simple framework to support configuration them with easy. I can't beleive more applicatoons don't allow this.

Also, I have included proper exception dumps in MyQuery. This is still on a basic level, but I got the idea on this from Spolskys book. And not to get ahead of myself here, but, I do like some aspects of the book, I do get ideas from it, but it is terribly opinionated (hey, who isn't!) and there are quite a lot of things I do not agree with.

And not even Microsoft got this right. Ctrl-C / Ctrl-V to cut and paste is the Windows convention, right? Try that in aDOS Window in Windows...

/Karlsson
Back to finishing up MyQuery, and spending some vacation time in the Sun

Editing SQL for MySQL with SciTe / Scintilla

This is just a brief announcement that the popular Open Source text editor control Scintilla, and the SciTe editor, which is a text editor based on Scintilla and part of the same project, these days properly supports MySQL Syntax.

Scintilla has supported generic SQL for quite a while, but this was heavily influenced by Oracle, and for example MySQL procedure code and folding didn't work properly. This has been fixed to an extent now, and there is a MySQL specific lexer that I contributed. This has been part of my own MyQuery tool since version 2.0, but that is a special version with the MySQL lexer patched in, but this patch is now included in the standard Scintilla and SciTe.

And just so you know, although my MyQuery tool is Windows only (yes, this is still the case), Scintilla and SciTe is not, they support Linux, GTK, Windows etc. So you can always use SciTe if you are on Linux, and get proper MySQL code folding etc.

/Karlsson
Hacking away at MyQuery

MyQuery 2.4 - Of multithreading, messages and Windows

Warning!. This is not much database related, except that I write this as I am developing my Database Query Tool MyQuery 2.4 right now.

While working with MyQuery 2.4, which should be ready for beta real soon now, I have gotten myself into an issue with Windows and Multithreading. Actually, this issue exists to some extent most message based systems I guess, assuming they also support multithreading.

If you have not developed with Win16/Win32 or some other message based GUI system, then let me fill you in on how Windows does this. This to a large extent dates back to when Windows wasn't truly multi-threading at all (such as Win 3.0, Win 3.11 etc.). Even in those old versions of Windows, you could switch from application to application, window to window anyway. And the application didn't seem to be blocked (I am ignoring any general performance issues with those old versions of Windows here).

How did that work? It worked as the applications weren't just running away and handling all keyboard and mouse input all on it's own, and wasn't blocking when no action was needed. Instead, an application has a "message loop" that handles messages. If a message is to be handled, then it is handled, and then control is handed back to Windows itself, until I have another message meant for yours truly. OK Fine, this is a cheap way of "multithreading" which is also reasonably lightweight and works quite well.

What is not so good is that we, at times, need to deal with blocking operations. As long as I do not return from handling a message, the application is blocked now. And this goes for ALL parts of the application, GUI included! No repaints, nothing, dead. And there are some blocking operations we need to deal with here.

And not only that. Let's say we have a database connection that is shared by several objects, active at the same time, in your application. In my case, I have a bunch on modeless dialogs that show database data that share one connection. Usually, you would think that just putting a mutex on that connection would be enough: One Window does it's thing:

• Wait for the Mutex until I have it.
• While I have the Mutex, do my database things.
• Release the Mutex when the database thing is done.

Now, I can end up in the same situation as when I have multiple interlocking mutexes, I get a deadlock. But this time the situation is worse, as the waiters aren't from the same group (i.e. if I have several mutexes deadlocking, the Mutex system can potentially figure this out). The Windows messages are stuck, as my modeless dialog is stuck waiting for a mutex, and the other modeless dialog is stuck, as it is waiting for a message whilst holding the Mutex.

In Windows, a modeless Dialog by default use the applications main message loop. If I remember correctly MFC (which I do not like, and I am not alone in this it seems) does this by using a smarter than usual message loop. I could have done that in my case I guess, but I decided on a different approach:

• Send an application defined message to do the database processing.

In the processing of this message:

• Get the Mutex, but do not wait for it.
• If I got, then do the database processing and release the Mutex and reset the flag that I am waiting for a Mutex.
  
• If I didn't get it, set a flag that I am waiting to get it.
  

For any message that i am not processing (I could probably pick some useful idle message here also):

• If the flag that I am waiting for the mutex, then send the User defined message to myself.

As we can see, I am avoiding deadlocks here by converting blocked operations to messages, making everything that is blocking into a polling process for the resource in question, using a message for each poll. One thing I cannot do here though, is to send any messages to myself whilst I am processing the database stuff, at least not any that can potentially block (other messages will nicely queue up of course).

Is this a good an accepted way of doing things? Maybe, at least it seems to work. I can have a script running in one window, and another window using the same database connection, all protected by a mutex, but none of the windows blocking. Next time around, I will probably figure out a way to massage the message loop so I can do this smarter, but for now, this works.

And this is example of how locking always comes back at you. In particular when multiple locking systems interact (like a GUI, a threading system and a database. Or a database that at one place locks the table, and at another place locks a row).

/Karlsson
Back to MyQuery. And I will not blog more about it until 2.4 is done. I promise...

Working on MyQuery 2.4, issues with the C API (Opinionated)

I am currently working on a 2.4 version of MyQuery. I have included a few cool features in it, and I have also done some significant changes to the code.
Above all, all database access function has been put in one single file with a set up functions to allow me to add other types of connections later. In the process, I have figured out a few things about the MySQL C API that I am not so hot on.
To take one example, I'm not too keen on the naming convention of fields and columns. It seems that, in the naming of functions in the API, someone seems to think that a column and a field is the same thing. They are not.
This is how many people look at things, including myself, when we speak of relational data:

• A row is one of 0 or more rows in a database.
• A column is one of 1 or more columns in a dataset.
• A field is the "crossing" of a row and a column.

Now, you are welcome to disagree with this (it is midsummer after all, so I am quite relaxed), but whichever the semantics, using column and field interchangeable not not really appropriate, and I think you agree. And even if you don't, and we would say that in some terminology, they are the same, then just one of the terms should be used. Not so in the MySQL C API docs:
mysql_field_count - Returns the number of result columns for the most recent statement

Also, why is there both a mysql_field_count and a mysql_num_fields function? Yes, I know the use of both of them, but the former seems rather useless. Or rather, on a high-level-view, design vise, this is useless. Although it does have a use, this is mainly because of other weird aspects of the C API.

If my SQL returns just 1 result, then I get just on result. If my SQL returns more than 1 result, I might get results back, the last one being empty? I don't see why this is necessary? Or rather, again, I read the documentation and I realize why the protocol needs to send an extra resultset, but why does that have to be exposed to the user of the API. Come on, if I can get rid of it in my own abstraction of the API, it can be gotten rid of in the API itself. Right?

And all this said, the work in progress for a better implementation of teh C API, in the shape of the C Connector (Thanx Jim), there is better stuff coming here. Hey, we need a better protocol, and a better C API!

/Karlsson

Of Oracle API's, array interface and bind variables

As we know, the MySQL prepared statements API leaves a bit to be desired, although there is a fair amount of progress. With MySQL, using the "normal" API or the prepared statement API usually doesn't have that much different in terms of performance.

When you use Oracle though, things are different. There is no separate "prepared statement" API, there is just on interface, which has a lot of functions and structures, and isn't the easiest to use, because of the complexity, but it IS very functional and performant.

If we look at the low-level Oracle interface, there used to be three of them:

• Oracle Call Interface (OCI) - This is the the interface that is most complex, but also most functional. It is a C level interface along the lines of the MySQL C API, i.e. you still pass SQL statements, it's not a file level interface of some kind. This is also the interface that is used to build Oracle Data Cartridges (i.e. the "real" Cartridges that plug into the database and are called like stored routines).
  
• Oracle PRO* - These are the Oracle Embedded SQL interfaces. And if you think that the Oracle Embedded SQL Precompilers produce OCI code, then think again, the precompilers use a different library. But they may be used together, with some work.
• Oracle SQL*Plus API. As far as I know, SQL*Plus (which is the Oracle interactive command line query tool) used a different interface from the other two guys. This was never confirmed though, so I might be wrong here and it might have changed.

Now, using any of the two public API's above, OCO or PRO* (C, Fortran etc), you nearly always use what are prepared statements in MySQL. Why? Because Oracle is pretty slow at parsing and optimizing statements, and also because that when you work with Oracle, you tend to write rather complex SQL (as the SQL Syntax is a bit more rish and because this is what a macho Oracle developer is supposed to do. Write complex SQL), which just adds to the time it takes to parse and optimize. On the other hand, once a statement is parsed, reusing it is blindingly fast in comparison. So in the case or Oracle, prepared statements are used ALL THE TIME, frankly, often you see prepared statements with bind variables being used even if you are to execute a statement just once, and this is sometimes faster than just running a single statement with the data being part of the statement, the way we mostly do it with MySQL.

So, what is the Array Interface then? This is an extension to PRO* and is also available with OCI (everything is available in OCI) that allows you to so what Ronald Bradford described in his blog, i.e. inserting multiple rows with one statement. But the Array interface does this differently, the INSERT statement looks the same, the difference is on the API side of things. What you do is to parse you statement as:
INSERT INTO t1(c1) VALUES(?)
But when you bind the ? parameter, you bind it to an array of integers, and when you execute the statement, you tell Oracle how many array records to process. This is very fast, the speedup provided, compared to executing N separate INSERT statement, is at least as big as it is with MySQL using the array interface, actually higher (As Oracle is typically not that good at executing many small statements).

Which is the better way of dealing with arrays then? If you ask me, I have a slight preference for Oracle, and let me tell you why: With MySQL, arrays are only available in this shape and form for INSERT statements, for obvious reasons. With Oracle, the same array interface is applicable also for UPDATE, DELETE and (don't hold your breath), SELECT! Yes, you can allocate an array of N rows, and then tell Oracle to fetch N rows at the time, again this is way faster than fetching row-by-row (on the other hand, the way data is fetched is very different between MySQL and Oracle. MySQL will always materialize a dataset somewhere (now I am talking API datasets, I know are many materializations going on inside MySQL also, before we reach the protocol), where as Oracle will typically not do this, at least not on the Client side of things (if this is good or bad is a different issue).

Another advantage, if you ask me, of the Oracle way of doing things, is the the SQL statement itself is intact. The disadvantages are at least two:

• You have to use bind variables.
• The API is a bit complex.
  

So, all in all, I like the idea that MySQL has some kind of array interface. The optimal would be to have an API array interface AND a SQL based (like MySQL) array interface. But maybe I'm just dreaming.

/Karlsson

Embedded MySQL = libmysqld - Where is it?? I want to download it??

I have seen this question more than once, and I have answered it more than once, but let me get you the facts here, so you know what to look for:

First, Embedded MySQL isn't necessarily the same thing as libmysqld. Libmysqld is the technically embedded MySQL server in a library, in your application. As far as the MySQL website goes though, this is not necessarily how it works. On the website, Embedded MySQL refers to any kind of embedding of MySQL in an application, be it the "normal" MYSQL Server that has been embedded, or be it libmysqld.

This is confusing, and unnecessary, but I have failed to fix this, although I have tried, and this convention has been in place for so long now, that it is difficult to change (also, it means I can keep my job, as I have to explain this to people frequently. Not the most fun of jobs though).

For anyone wanting to embed MySQL and hasn't used MySQL before, maybe you have Oracle or SQL Server or such experiences, embedding these products with an application is actually rather difficult. There are loads of files, registers to update and such-and-such. As for the MySQL server though, this is not the case. MySQL is dead easy to embed, even the full Server, although there is even less to worry about when using libmysqld.

Libmysqld, as you might know, is a "server in a library" implementation of MySQL, which has a few limitations, but which also has surprisingly low overhead. Have a look in my blog for more posts on libmysqld, or more specifically the post on my libmysqld demo application, available on sourceforge.

So hopefully I have cleared that one up. And then, if we assume that it is libmysqld is what you want, they I guess you want to know where to download libmysqld. And the answer is that you can't. There is no separate download for libmysqld, rather it is included with the usually MySQL Server download. And note that there is no libmysqld with 5.0, neither as a separate download, nor as part of the server download. You have to go with 4.1 or 5.1 to get hold of libmysqld.

So to find libmysqld, you download the appropriate version of the MySQL Server, and then you find libmysqld somewhere there. On Linux, it's in the lib subdirectory, on Windows, it's in the Embedded subdirectory.

So, one last comment on this subject then. Why is there not a separate libmsyqld download? Well, one reason is that, even though libmysqld is a self contained MySQL Server in a library, you still need a few more things, like the MySQL C/C++ includefiles to compile you application and at least one of the MySQL language files, named errmsg.sys, to be able to run the application. Usually, at least during development, you want more things, such as some the MySQL utilities. So having to download the whole MySQL Server, including utilities etc, might not be such a bad idea after all.

But what IS a distinctly bad idea is how we confuse Embedded MySQL with libmysqld, but as I said, it's difficult to change now, and it keeps me on the job (if that last argument is a good thing or not, well I leave that to judge, but at least it means I can have a few beers at the end of the day).

/Karlsson

MySQL 5.4 testing - Failure and a W/O

I had this idea around 5.4. It is meant to scale better on multi-core machines compared to, say, standard 5.1. This has been proven also it seems. 5.4 is still in Beta, but testing has shown that scalability is definitively better.

What I wanted to know was how well 5.4 would work on a lower spec box compared to 5.1. One reason for me wanting to test this is that I am currently in my summer house, and I have no multi-core 64-bit machines around. All I have in terms of Linux boxes is an old Dell Laptop with Gentoo on it, all this will as similar setup as possible between 5.1 and 5.4.

So, I download 5.4 sources, as there are no 32-bit Linux binaries yet, and I do a simple configure and make. And.. I fail. Out of the box, some of the inline optimizations in innodb (the config I built with was max-no-ndb) will screw up gcc when using -O3 optimization (OK, I admit, I'm not 100% sure that this is the cause, but it sure looks like it, and some googling seems to confirm it, but I'm not a compiler engineer, so who knows).

So today, when I was planning to do some benchmarking and some other fun things, I tried to track this one down, and I did find a W/O for this issue, which is to configure with CFLAGS=-O2, i.e.:
./configure --with-plugins=max-no-ndb CFLAGS="-O2"
This will actually do the trick. And once I figured that out, I had to report it as a bug (#45131) and try with a few different compilers.

I did an emerge on gcc for my gentoo, and got gcc 4.1.2 instead of 4.1.1, and had the same problem. I also tested on another box running SuSE and gcc 3.3.1, and with the same problem. But as I said, at least there is a fix.

Tomorrow, I might do the actual low-end 32-bit machine performance test, I'll keep you posted.

/Karlsson