Month: July 2021
Fancy SQL Result Set Display
A while ago I noticed a
forum with tips for changing the colour of some results of the mysql utility. They’re nifty but tricky and don’t look appropriate for ordinary users. So I’ll concentrate on what a GUI can do instead. I illustrate with ocelotgui but any good product should be similar.
My example table is
CREATE TABLE elements (atomic_number INTEGER PRIMARY KEY, name VARCHAR(20)); INSERT INTO elements VALUES (48,'Cadmium'),(20,'Calcium'),(98,'Californium'),(6,'Carbon'),(58,'Cerium'), (55,'Cesium'),(17,'Chlorine'),(24,'Chromium'), (27,'Cobalt'),(112,'Copernicium'),(29,'Copper'),(96,'Curium');
Obvious and simple things with a Settings menu
Of course there will be a menu item for Settings that puts up a dialog box.
The important items on it will be for changing background colour and font. For example these three displays show what happens after the settings menu has changed background colours and fonts.
I prefer to use the dialog box initially and then make the changes
permanent with client statements or startup options.
HTML in the select list
HTML is designed for people who want to customize colours and fonts, and many SQL users are familiar with it, so it’s natural to want it for changing colours and fonts in an SQL result set.
For example, with a WHEN trick, here we display a column as bold, or as italic, depending on the value:
SELECT CASE WHEN name LIKE '%ium' THEN '<b>' || name || '</i>' ELSE '<i>' || name || '</i>' END AS name, atomic_number FROM elements;
For example, with a UNION trick, here we display in red or in blue, depending on the value:
SELECT '<p style="color:red">' || CAST(atomic_number AS VARCHAR(20)) AS atomic_number, '<p style="color:red">' || name AS name FROM elements WHERE atomic_number < 90 UNION ALL SELECT atomic_number, '<p style="color:blue">' || name AS name FROM elements WHERE atomic_number >= 90;
You could accomplish the same effects with REPLACE() or with stored procedures. An obvious usage is for emphasizing NULLs which otherwise are hard to distinguish from blanks. The advantage of this technique is that, even if your GUI isn’t this sophisticated, you can still get the effect by piping the result set to a file where a browser can read it. The distadvantages of this technique are: (a) it’s forcing the server to do a client job, (b) the column might contain ‘<‘ or ‘>’ or other entities, (c) some data types won’t work with the || concatenate operator (or the CONCAT function). So it’s best to have it as a non-default option (and that’s what the “HTML effects” option is for, on the first screenshot that I showed for Settings menu).
The more general technique is to use a separate instruction for the setting, that will apply for all result sets, for cells that have certain characteristics. This is a bit like using cascading style sheets rather than inline HTML, but the best syntax is SQL-like, since that’s the language that users are bound to know. For example here is an instruction that changes a cell’s background colour and height if certain conditions are met (REGEXP means “regular expression”) (“SET ocelot_…” is a client-side statement that won’t get passed to the server):
SET ocelot_grid_background_color='yellow', ocelot_grid_cell_height=40 WHERE column_name REGEXP 'number' AND row_number <> 4 ; SELECT * FROM elements LIMIT 6;
Here is a cheap bar graph:
SELECT atomic_number, SUBSTR('------------',1, atomic_number / 10) AS bar, name FROM elements;
MySQL/MariaDB users might prefer REPEAT(‘a’,atomic_number / 10) AS bar instead of SUBSTR. Unicode-character-set users might prefer Unicode 2588 █ (full block) instead of ‘-‘, to get a solid bar,
or might prefer a series of emojis.
Every good GUI can display .png of .jpg images too, so it’s not particularly hard to store them in the database and add them to the result via joins or subqueries. For example, with thanks to Wikipedia for the original picture,
SELECT atomic_number, name, (SELECT png FROM "timages" WHERE caption = 'copper') AS png FROM elements WHERE name = 'copper';
but I hesitate to say that .png and .jpg images make good decoration, because they are never small.
To be specific: the examples were all done with ocelotgui 1.5.0 and I used the options|detach menu choices so I could show only the select and grid widgets in a left-to-right order. Some of the functionality, particularly the new layout of the settings menu, is new. As always, the release is available on github.