Visual Alter Table!

Monday, 5 June 2006  |  Jaroslaw Staniek

What has implementation of database table schema (design) altering to do with Undo/Redo implementation for Table Designer?

In case of Kexi, everything!

Read on to learn why.

Someone has nicely [w:Kexi|explained] on Wikipedia that "Kexi is positioned to fill the gap between spreadsheets and database solutions requiring more sophisticated development." True, and moreover it's components can also act as a set of data services for other KDE apps. With every release Kexi hides from user (and developer) more and more boring database-related details. Recently, one of them is editing (altering) table design.

People sometimes asked me why there is no "alter table" feature in Kexi that allows to change the table design in-place, i.e. without loosing all so carefully entered data. Prior to Kexi 1.1 (to be released as alpha in early july), the app warned you gently that all data will be removed. The default, the Kexi's builtin SQL database engine, SQLite has very limited table altering features. Users asked: "I understand, but hey, why not to use 'alter table' SQL commands coming at least for server databases, MySQL and PostgreSQL?".

Fine, but features of the server database backends (even if more powerful than embedded backend like SQLite), are not enough to have the table schema altering plugged to the Kexi GUI. What we need is to carefully track all the Table Designer's actions performed by user (explicitly or implicitly) and note down them. I mean, actions like "rename table field", "remove field", "change data type from integer number to text", "reorder the fields", and so on.

Implicit actions include "change data type to integer because user has set a primary key for the table field, and the database backend requires primary keys to operate on integers". Complex? Sure, funny but more it is complex to us, developers, more friendly it is to the user.

So we have collected a list of, say, atomic actions the user performed while editing the table design. Ordered list. Is this enough? No! Imagine the following scenario:

changeDataTypeForField("A", "integer")
remove("A")

User has changed the data type of table field "A" to "integer", and then gave up and deleted the field. naive algorithm would just perform both actions when user clicked the "Save" button. He would be surprised if the app warned him about possible data loss in a column "A" due to casting to "integer". "I've just removed that field, stupid", she would most likely think.

And she would be right, so we needed to design another step of the machinery, sometime like optimizer that will simplify the sequence of actions. The optimizer is a machine knowing a few optimizing rules. Not very smart, but the trick is that the rules are applied in the opposite order. For the aforementioned case:

  1. The optimizer gets the last action, remove("A"), and can see there was no action related to "A" field yet. So the action is pushed into the separate list dedicated to the "A" field only.
  2. The optimizer gets the previous action, changeDataTypeForField("A", "integer"), and can see there is already remove("A") related action on the dedicated list. The following rule telling "forget about any actions performed before 'remove' action for the same field" fits very well to this situation.

Thus, on the output, we have only one action:

remove("A")

Now we need to send the simplified sequence of actions to the Kexi database driver, so it can do the actual, backend-specific job which has be performed at the "physical" level (so far we have analyzed altering the design at logical level). Now it's much easier for the backend to proceed with only needed (optimized) sequence. In fact, it can be also far more effective and more bug-free.

After the machinery is mostly designed and implemented, I dare to say we are about to have the first semi-complete feature that works both within the GUI and in GUI-independent environment in the FOSS world. Period.

Back to the question about "Undo/Redo implementation" in the Table Designer. As you can imagine, we have got this almost for free, since we were already collecting the actions list. The only difference is that we need to use KCommand class and have clearly separated "unexecute()" code.

One screenshot is worth of thousands words, and perhaps one movie is worth of thousands screenshots. So here's a movie presenting the machinery in action (the Internal Debugger shows you bits that are not normally visible to the mortals). You can see that "rename" and "change data type" actions have been dropped as irrelevant since the field will be removed anyway.

→ The movie (Flash, ~3MB)

Above I have mentioned about Kexi acting as a set of data services for other KDE apps. Having such a machinery, it's not too hard to imagine a user being able to quickly edit, say, database schema of KDE4's KMail/Kontact data backend, to add some additional fields or relations (the backend does not have to be SQL-compliant!)

Next time I will try show a GUI used for conversation with the user when there are decisions to be made (e.g. when changing a data type can lead to data loss).