Makers of ocelotgui

Version 1.8.0

The ocelotgui GUI, a database client, allows users to connect to a MySQL or MariaDB DBMS server, enter SQL statements, and receive results. Some of its features are: syntax highlighting, user-settable colors and fonts for each part of the screen, result-set displays with multi-line rows and resizable columns, and a debugger.

Copyright (c) 2022 by Peter Gulutzan. All rights reserved.

For the GPL license terms see https://github.com/ocelot-inc/ocelotgui/blob/master/LICENSE.GPL.

For instructions for end users see the User Manual.

For ocelotgui screenshots see http://ocelot.ca/screenshots.htm.

For ocelotgui/debugger screenshots see http://ocelot.ca/blog/the-ocelotgui-debugger.

This README file has installation instructions, screenshots, and the user manual.

Contents

Installing

Illustrating

Using

Appendixes

Prerequisites

The installation instructions in this section are for Linux. If you prefer to run on Windows, read the installation instructions in Appendix 4 windows and come back to read the User Manual section. The basic prerequisites for installation are Linux, and the Qt library. The libmysqlclient library will also be necessary, at runtime.

Getting the Qt library

You probably will find that the Qt package is already installed, since other common packages depend on it. If not, your Linux distro's repositories will provide a Qt package. For example, on some platforms you can say "sudo apt-get install libqt5core5a libqt5widgets5" , on others you can say "dnf install qt qt-x11", on others you can say "dnf install qt5-qtbase qt5-qtbase-gui".

The Qt version number can be found with find /usr/lib -name "libQt*Gui.so*", or find /usr/lib64 -name "libQt*Gui.so*". If the response starts with libQtGui.so.4 then you have Qt4, if the response starts with libQt5Gui.so.5 then you have Qt5. Alternatively it sometimes can be found with qmake -v. Peter Gulutzan supplies executables only for Qt version 5, but often appropriate files are downloaded automatically during ocelotgui installation. To force use of a different Qt version you can build from source.

The Qt library is necessary for ocelotgui installation.

Getting the libmysqlclient.so library

You may find that the libmysqlclient.so library is already installed, if you have used a MySQL or MariaDB client program before. If not, your Linux distro's repositories will contain it, usually with a package name like "libmysqlclient-dev" or "libmysqlclient-devel".

A tip for Mageia 5: You can use "urpmf " to find what packages contain libmysqlclient.so. If the answer is lib64mariadb18, you can install it with: sudo urpmi lib64mariadb18.

A tip for openSUSE 13.1: if neither Qt nor libmysqlclient libraries exist, say:

 sudo zypper install libqt5-devel
 sudo zypper install mariadb-client
 sudo zypper install libmysqlclient-devel

The important file is named "libmysqlclient.so" or something similar. If it is not already on the default path, then an error or warning will appear when you try to run ocelotgui. Find it, and say something like

export LD_RUN_PATH=[path to directory that contains libmysqlclient.so]

The libmysqlclient library is not necessary for ocelotgui installation; however, it is necessary at runtime in order to connect to a MySQL or MariaDB server.

Getting the ocelotgui executable package

wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.8.0/ocelotgui_1.8.0-1_i386.deb
sudo apt install ./ocelotgui_1.8.0-1_i386.deb

wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.8.0/ocelotgui_1.8.0-1_amd64.deb
sudo apt install ./ocelotgui_1.8.0-1_amd64.deb

wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.8.0/ocelotgui-1.8.0-1.x86_64.rpm
sudo rpm -i ocelotgui-1.8.0-1.x86_64.rpm

wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.8.0/ocelotgui-1.8.0.tar.gz
tar zxvf ocelotgui-1.8.0.tar.gz
ocelotgui/ocelotgui-qt5

wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.8.0/ocelotgui-1.8.0.tar.gz
tar zxvf ocelotgui-1.8.0.tar.gz
ocelotgui/ocelotgui-qt4

Starting the program

After installing and making sure that ocelotgui is on the path, start it with

ocelotgui

ocelotgui --host=127.0.0.1 --user=joe --password=secret

Warning: Some menu shortcut keys may not work properly with Ubuntu 14.04.

Some screenshots

User Manual

Version 1.8.0, November 16 2022

Copyright (c) 2022 by Peter Gulutzan. All rights reserved.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

Executive Summary

The ocelotgui application, previously called 'The Ocelot Graphical User Interface', allows users to connect to a MySQL or MariaDB DBMS server, enter SQL statements, and receive results. Some of its features are: syntax highlighting, user-settable colors and fonts for each part of the screen, and result-set displays with multi-line rows and resizable columns, and a debugger.

The developer, the product, and the status

Peter Gulutzan is a Canadian who has specialized in database products for thirty years, as an employee of Ocelot Computer Services Inc. and MySQL AB and Sun Microsystems and Oracle and HP, or as a contractor for a large company in eastern Europe.

The ocelotgui program is a front end which connects to MySQL (tm) or MariaDB (tm). In some ways it is like the basic mysql client program, with added GUI features: full-screen editing, syntax highlighting, tabular display, customized fonts and colors. It differs from some other front-end GUI products because it is open source (GPL), it is written in C++, and it makes use of the Qt multi-platform widget library.

The product status is: stable. It has been known to work as described in this manual on several Linux distros. It is stable, in the sense that there are no known severe errors and the features are frozen until the next version. Peter Gulutzan will address any bug reports and will answer any questions.

Downloading, installing, and building

To download the product go to https://github.com/ocelot-inc/ocelotgui. Instructions for installation will be in the README.md file. This location may change, or alternate locations may appear. If so there will either be an announcement on github or on ocelot.ca.

The package contains source code and an executable file named ocelotgui-qt5.

Starting

There must be an instance of MySQL or MariaDB running somewhere.

If connection is possible with the mysql client and does not require unusual options, then connection is possible with ocelotgui. If there is a my.cnf file, ocelotgui will read it, just as the mysql client would. If there are connection-related options on the command line, ocelotgui will accept them just as the mysql client would. Therefore the typical way to start the program is to say ocelotgui [--option [--option...]]
For a description of options see Appendix 1 Details about ocelotgui options.

If a password is required but not supplied, a dialog box will appear. Or, if the initial attempt to connect fails, an error message will appear saying it is necessary to choose File|Connect, which will cause the dialog box to appear. The dialog box has many possible settings (see the list in Appendix 1; however, for getting started, the ones that matter most are the ones at the top: host, port, user, socket, password, protocol. If the connection still fails, then ocelotgui will still come up, but only non-DBMS tasks such as screen customizing will be possible.

In any case, an initial screen will appear. After some activity has taken place, the screen will have four parts, from top to bottom:
menu
history widget, where retired statements and diagnostics end up
results widget, where SELECT result sets appear
statement widget, where users can type in instructions.
(And optionally, on the side, an explorer widget.) Initially, though, only the menu and statement widget will appear.

Again, this should be reminiscent of the way the mysql client works: statements are typed at the bottom of the screen, and appear to scroll off the top after they are executed, with results in the middle.

Statement widget

The statement widget is an editable multi-line text box. The usual control keys that work on other text editors will work here too; see the later description of Menu Item: Edit.

The program includes a syntax checker and can recognize the parts of speech in MySQL grammar. It will do syntax highlighting by changing the color, for example comments will appear in light green, identifiers in green, operators in dark gray, and so on. The colors can be customized, see the later description of Menu Item: Settings.

The left side of the statement widget is reserved for the prompt, and cannot be typed over. Initially the prompt will be 'mysql>' but this can be changed, see the later description of Client Statements: Prompt.

For example, this screenshot shows the statement widget after the user has changed the default prompt and entered an SQL statement. The statement has keywords in magenta, literals in dark green, operators in light green, and comments in red. The prompt on the left has a gray background.

Major Feature Alert: this is not merely a GUI that only will highlight words that are in a list of keywords. This GUI will parse the complete MySQL or MariaDB grammar, without needing to ask the server. So the highlighting will be correct, syntax errors will be underlined in red, and -- since the parsing method is predictive -- there will be continuous hints about what word is expected next, and optionally an error message explaining suspected syntax problems before they go to the server.

Once a statement has been entered and is ready to be executed, the user can hit control-E, choose menu item Run|Execute, or place the cursor at the end of the text (after the ';' or other delimiter) and type Enter. It is legal to enter multiple statements, separated by semicolons, and then execute them in a single sequence.

Client statements

A client statement is a statement which changes some behavior of the client (that is, of the ocelotgui front end) but does not necessarily go to the MySQL/MariaDB server. Of the statements that the MySQL Reference manual describes in section 'mysql client commands' https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html the ocelotgui program has working equivalents for: clear, delimiter, exit, prompt, source, tee, and warnings. For example, entering 'quit;' followed by Enter will cause the program to stop. It is sometimes not mandatory to end a client statement with ';', but is strongly recommended.

There are some enhancements affecting the PROMPT statement. The special sequence '\2' means 'repeat the prompt on all lines', and the special sequence '\L' means 'show line numbers'. For example, 'PROMPT \2\Lmariadb;' will change the prompt so that each line begins with '[line number] mariadb>'.

History widget

Once a statement has been executed, a copy of the statement text and the diagnostic result (for example: 0.04 seconds, OK) will be placed in the history widget. Everything in the history widget is editable including the prompt, and it simply fills up so that after a while the older statements are scrolled off the screen. Thus its main function is to show what recent statements and results were. Statements in the history can be retrieved while the focus is on the statement widget, by selecting 'Previous statement' or 'Next statement' menu items.

Initially the history widget will show some statements from past sessions which are stored in a history file.

Result widget

If a statement is SELECT or SHOW or some other statement that returns a result set, it will appear in the result widget in the middle area of the screen. The result widget is split up into columns. Each column has a header and details taken from what the DBMS returns.

The width of the column depends on the result set's definition, but extremely wide columns will be split onto multiple lines. That is, one result-set row may take up to five lines. If the data still is too wide or too tall to fit in the cell, then the row will get a vertical scroll bar. The user can change the width of a column by dragging the column's right border to the right to make the column wider, or to the left to make it narrower.

The result widget as a whole may have a horizontal and a vertical scroll bar. The vertical scroll bar moves a row at a time rather than a pixel at a time -- this makes large result sets more manageable, but makes the vertical scroll bar unresponsive if each row has multiple lines and the number of rows is small.

For example, this screenshot shows the whole screen after the user has typed the statement "select * from information_schema.tables;" on the statement widget and then executed it. The statement text has been copied to the history widget, the statement widget has been cleared, the result widget has the rows. The user has dragged the border of the fourth column to the left, causing a scroll bar to appear.

Menu

The menu at the top of the screen has File, Edit, Run, Settings, Options, Debug and Help.

File|Connect, or Ctrl+O, starts the Connect dialog box. File|Exit, or Ctrl+Q, stops the program. File|Export brings up a dialog box for exporting selections.

Edit|Undo or Ctrl+Z, Edit|Redo or Ctrl+Shift+Z, Edit|Cut or Ctrl+X, Edit|Cut or Ctrl+X, Edit|Copy or Ctrl+C, Edit|Paste or Ctrl+V, and Edit|Select or Ctrl+A, all work in the conventional manner. Edit|Redo can only redo the last change. Previous Statement or Ctrl+P and Next Statement or Ctrl+N will copy earlier statements from the history widget into the statement widget, so that they can be edited or re-executed with Run|Execute or Ctrl+E. Format or Alt+Shift+F changes what is in the statement widget according to a simple style guide. Autocomplete or Tab will be discussed later.

Run|Execute or Ctrl+E or Ctrl+Enter causes execution of whatever is in the statement widget. Run|Kill or Ctrl+C tries to stop execution -- this menu item is enabled only when a long-running statement needs to be aborted by user intervention.

Settings|Menu, Settings|History Widget, Settings|Grid Widget, Settings|Statement, and Settings|Extra Rule 1 are items which affect the behavior of each individual widget. The color settings affect foregrounds, backgrounds, borders, and (for the statement widget only) the syntax highlights. The font settings affect font family, boldness, italics, and size. There may be additional choices affecting appearance, for example the width of the border used to drag columns in the result widget. Settings|Extra Rule 1 is conditional -- for example, to specify that BLOBs should be displayed as images on a pink background, set Grid Background Color Pink, set Condition = data_type LIKE '%BLOB', set Display As = image, then click OK.

Options|detach history widget, Options|detach result grid widget, Options|detach debug widget are for turning the respective widgets into independent windows, so that they can be moved away from the statement widget, or resized. A detached widget is always kept on top of the other widgets in the application screen. When a widget is already detached, the menu item text will change to "attached" and clicking it will put the widget back in its original position.

The items on the Debug menu are enabled only when a debug session is in progress. The way to debug SQL stored procedures or functions will be explained in a later section.

Help|About will show the license and copyright and version. Help|The Manual will show the contents of README.md (the manual that you are reading) if README.md is on the same path as the ocelotgui program; otherwise it will show a copyright, a GPL license, and a pointer to README.md. Help|libmysqlclient will advise about finding and loading the libmysqlclient.so library. Help|settings will advise about how to use the Settings menu items.

Debugger

It is possible to debug stored procedures and functions. This version of ocelotgui incorporates MDBug (read about MDBug at http://bazaar.launchpad.net/~hp-mdbug-team/mdbug/trunk/view/head:/debugger.txt). All debugger instructions can be entered on the ocelotgui command line; some operations can also be done via the Debug menu or by clicking on the stored-procedure display. Currently-supported instructions are:
$install -- this is always the first thing to do.
$setup routine_name [, routine_name ...] -- prepares so '$debug routine_name' is possible.
$debug routine_name -- starts a debug session, shows routines in a tabbed widget.
$breakpoint routine_name line_number or Debug|breakpoint -- sets a breakpoint.
$clear routine_name line_number -- clears a breakpoint.
$next or Debug|Next -- goes to next executable line, without dropping into subroutines.
$step or Debug|Step -- goes to next executable line, will drop into subroutines.
$continue or Debug|Continue -- executes until breakpoint or until end of procedure.
$refresh breakpoints -- refreshes xxxmdbug.breakpoints table.
$refresh server_variables -- refreshes xxxmdbug.server_variables table.
$refresh variables -- refreshes xxxmdbug.variables table.
$refresh user_variables -- refreshes xxxmdbug.user_variables table.
$exit or Debug|Exit -- stops a debug session.

For a walk through a debugger example, with screenshots, see this blog post: http://ocelot.ca/blog/the-ocelotgui-debugger. For reference, read: Appendix 2 Reference for the ocelotgui debugger (ocelotgui)">.

Special Effects

Vertical: If a user starts the program with ocelotgui --vertical=1 or ends a statement with backslash G, results come up with one column per row.

Images: If a user chooses Settings | Extra Rule 1 from the menu, and sets the Condition and Display As boxes as described earlier, and selects rows which contain LONGBLOB columns, and the column values are images (such as PNG or JPEG or BMP or GIF format data), ocelotgui will display the result as images.

Result-set editing: If a user clicks on a column in the result set and makes a change, an update statement will appear in the statement widget. For example, if a result set is the result from SELECT column1, column2 FROM t;, and the column1 value is 5, and the column2 value is 'ABC', and the user changes the column2 value to 'AB', then the statement widget will show UPDATE t SET column2 = 'AB' WHERE column1 = 5 AND column2 = 'AB';. The user then has the choice of ignoring the update statement or executing it.

Detaching: If a user chooses Options | detach history widget or Options | detach result grid widget, then the widget will become a separate window which can be moved or resized.

Colors: The Colors and fonts dialog boxes have a simple way to choose colors, by selecting from a choice of 148 color names / color icons. Users can also change colors by saying SET object_name_color = color-name | hex-rgb-value. In fact ocelotgui mixes the modes: for example if a user chooses Settings | Grid Text Color, then clicks on the 'Red' icon, then clicks OK, ocelotgui generates a statement "SET ocelot_grid_text_color = 'Red';". This makes the instruction easy to repeat or put in a script.

RE: SQL_MODE. To distinguish between literals and identifiers enclosed in double quotes, ocelotgui needs to know the value of sql_mode (ansi_quotes). It calculates this automatically; however, in rare circumstances it can fail to detect changes on the server. If that appears to be the case, say SET SESSION SQL_MODE = @@SESSION.SQL_MODE; to update it.

RE: AUTOCOMPLETION. While a user is entering an SQL statement, ocelotgui will display a list of possible words that may follow. Hitting the Tab key will cause the first word in the list to be displayed and accepted. Users can use arrow keys to select other words, and can use "set ocelot_shortcut_autocomplete='...'; to choose a different key instead of the Tab key, and can use "set ocelot_completer_timeout=...'; to choose how many seconds the list will be visible, and can use "rehash;" to update the list.

RE: HINTING FOR COLUMN NAMES. Although hints for syntax appear by default, hints for table / column identifiers might not. In order to make identifiers appear on the hint list: (1) ensure the setting for auto_rehash has not been turned off, and/or (2) enter the statement "REHASH;" to make the client ask the server for a list of identifiers in the current database; (3) when entering an SQL statement, type ` (backtick) at the point where an identifier is expected.

RE: FONT. By default, ocelotgui uses a fixed-pitch (mono) font that has similar attributes to whatever font was in use at the time it started. This may be a bad choice. We recommend trying out other fonts with the Settings menu for each widget.

RE: PERMANENT CUSTOMIZING. Changes to settings can be done with the Settings menu items, but such changes are not permanent. So note the commands that ocelotgui performs when settings are changed, and paste them into a file. Later this file can be executed (for example with SOURCE file-name), whenever ocelotgui is started again. Alternatively, settings can be placed in an options file such as my.cnf.

RE: CONDITIONAL SETTINGS. To override the ordinary settings for result set displays there is a special SET statement with a WHERE clause:
SET ocelot_grid_setting = string|integer [, ocelot_grid_value = string-or-integer...]
WHERE condition [AND|OR condition ...];
where ocelot_grid_setting is OCELOT_GRID_BACKGROUND_COLOR | OCELOT_GRID_FONT_STYLE | etc.,
and condition has the form item comparison-operator literal, where item is COLUMN_NAME | COLUMN_NUMBER | COLUMN_TYPE | ROW_NUMBER | VALUE,
and comparison-operator is = | > | >= | < | <= | <> | IS | REGEXP.
For example to say "I want the background color to be pink if it's in the fourth column of the result set and it's NULL", say
SET ocelot_grid_background_color='pink' WHERE column_number = 4 AND value IS NULL;

RE: DEBUGGING WITH MYSQL 5.7. Oracle has made a significant incompatible behavior change in version 5.7, which affects the debugger. The originally recommended workaround was to say "set global show_compatibility_56=on;". We believe we made a more permanent fix for this problem in ocelotgui version 1.0.8.

RE: CONNECTION DIALOG. As stated earlier, if a password is necessary to connect, it is sufficient to start ocelotgui with "--password=password" or by choosing File|Connect and typing a password in the Password field (the sixth field in the Connection Dialog Box). Also on the Connection Dialog Box, if the server is running on the same computer as the ocelotgui client, it is sometimes a good idea to enter '127.0.0.1' in the host field, instead of 'localhost'.

RE: ROW NUMBERS. ocelotgui will replace the value 'row_number() over ()' with the row number within the result set. For example, try
SELECT 'row_number() over ()' as r, table_name.* FROM table_name;
To disable this feature, start ocelotgui with --ocelot_client_side_functions=0.

RE: HOVERING. Use the mouse to hover over a word in the statement widget, and ocelotgui will display what kind of word it is, for example "table identifier".

RE: FORMAT. Click Edit|Format, and ocelotgui will change the contents of the statement widget so that keywords are upper case and sub-clauses or sub-statements are indented.

RE: HISTORY. By default the history does not contain any rows from result sets of previous statements. To change this, click Settings|History and enter a number for Max Row Count. Also users can change the history file name with HISTFILE=name, change what statements should not go to the ihstory file with HISTIGNORE=regexp, change whether the history file will include system-generated comments with OCELOT_HISTFILEFLAGS='L'|'LP', change how large the history file can become with OCELOT_HISTFILESIZE=number, change how large the initial history can become with OCELOT_HISTSIZE=number.

RE: EXPORT. A result set can be dumped to a file as text, as table, or as html.

RE: TARANTOOL. By default ocelotgui is a client for MySQL or MariaDB. To use it as a client for Tarantool, read Appendix 3 Tarantool.

Explorer widget

EXPLORER NAME. Explorer. A similar feature in other GUI clients may be named "object explorer" or "navigator". Compare the name "Windows explorer".

EXPLORER SETTINGS. To change some significant settings, click on the menu bar: Settings|Explorer.
Visible: yes|no. Default no. So if you don't change this to 'yes', it is not visible. There must be a connection to the DBMS server before it can be made visible.
Sort alphabetically: yes|no. Default no. If 'yes', objects of the same type are sorted alphabetically.
Query: a long select with unions. The default query selects all databases. Users can change this query to add WHERE clauses (affects MySQL/MariaDB only).
Detached|Top|Left|Width|Height: As with other widgets, the explorer is detachable and the explorer's size + position are settable when it is detached. Unlike with other widgets, changing width may have an effect even if the explorer is not detached. The default width depends on the widths of the object names.

Ordinarily the explorer widget appears on the left while the other main widgets (history, grid, statement) appear vertically on the right.

EXPLORER STYLE. It is a result grid with text columns. Therefore users get some features without needing to learn anything new:
* ^Find works. (if the item is visible)
* Vertical scroll bar works.
* Settings | Explorer can be used to change colors and font.
* Example: SET ocelot_explorer_text_color='blue' WHERE value = '▶'; REFRESH;

EXPLORER COLUMNS. There are three text columns.
#1: Min. = Unicode Black Down-Pointing Triangle ▼ or Black Right-Pointing Triangle ▶ if database or table. If it's a database or table, click on the triangle to hide the parts e.g. columns, or to show them again. (This is "toggling".) It's treated as a header item, that is, Result Grid Settings for header affect it. So ordinarily it has a different background color.
#2: object_type. = S or T or V or C or P or I or E or t
#3: object_name. for example, if object_type='T', this is a table name.
If object_type='C', this is a column name and is taken from part_name.

EXPLORER ROWS. Rows are in a hierarchy: S, T or V, C or I, etc. Within the hierarchy, rows are in order by creation time.

EXPLORER POSSIBLE USER ACTIONS
Hover. This will show what type of object is under the cursor and contain some short advice.
DoubleClick: This will copy the cell contents to the end of the statement widget
Right Click: puts up a context menu.
Click on the leftmost (Min) column. This will "toggle".

EXPLORER CONTEXT MENU. This is the menu that will appear when the user uses the right mouse button to click over a row in the explorer display. It is called a context menu because the menu is different for each object type. For example if it's over a table, then table-related menu items will appear.
Items for all types: Copy to clipboard, Send to SQL editor, Reset, Refresh.
Items for 'S' (Schema): Set as default schema, Filter to this schema, Schema inspector, Create schema, Alter schema, Drop schema.
Items for 'T' (Table): Select rows, Export dialog - Text, Export dialog - Table, Export dialog - Html, Import - Text, Create table, Create table like, Drop table, Truncate table.
For 'C' (Column): Show column, Drop column.
For 'I' (Index): Show index, Select index, drop index.
For 'P' (Procedure): Create procedure, Drop procedure.
Details for 'T':

  Copy to Clipboard      Equivalent of "Copy"
  Send to SQL editor     Copy, then put in statement widget, then execute
  Select rows            Execute a statement to select 100 rows
  Export Dialog - Text   Dialog box for export, then do the export if OK
  Export Dialog - Table  Dialog box for export, then do the export if OK
  Export Dialog - Html   Dialog box for export, then do the export if OK
  Import - Text          Generate INSERTs for a file's contents, no options
  Create table           Generate SHOW CREATE TABLE ...
  Create table like      Generate CREATE TABLE ... LIKE ...
  Drop table             Generate DROP TABLE
  Truncate table         Generate TRUNCATE TABLE
  Reset                  Back to default state with nothing expanded
  Refresh                Big SELECT to recreate the widget (this can be slow)

EXPLORER EXAMPLE.

The user makes the explorer visible with Settings|Explorer Widget, it looks like this:

The user clicks the ▶ beside information_schema, it changes to ▼ and the schema items appear:

The user right-clicks on COLLATIONS, and the context menu appears:

The user navigates (with the mouse or the down-arrow key) and selects (with Enter or Tab or click) the Export dialog - Text choice, and the export dialog appears:

The user selects the OK button and the contents are dumped to STDOUT:

EXPLORER SET STATEMENTS. The user can enter SET statements on the select widget. Or, some SET statements will be generated when the user clicks OK on the Settings menu.
Keywords are OCELOT_EXPLORER_ACTION, OCELOT_EXPLORER_APPLICABLE_DBMSS, OCELOT_EXPLORER_APPLICABLE_TYPES, OCELOT_EXPLORER_BACKGROUND_COLOR, OCELOT_EXPLORER_DETACHED, OCELOT_EXPLORER_ENABLED, OCELOT_EXPLORER_FONT_FAMILY, OCELOT_EXPLORER_FONT_SIZE, OCELOT_EXPLORER_FONT_STYLE, OCELOT_EXPLORER_FONT_WEIGHT, OCELOT_EXPLORER_HEIGHT, OCELOT_EXPLORER_LEFT, OCELOT_EXPLORER_SHORTCUT, OCELOT_EXPLORER_TEXT, OCELOT_EXPLORER_TEXT_COLOR, OCELOT_EXPLORER_TOP, OCELOT_EXPLORER_VISIBLE, OCELOT_EXPLORER_WIDTH.
The important one is SET ocelot_explorer_visible='yes';
Settings can also be done in the .cnf file or the command line, for example start ocelotgui with --ocelot_explorer_visible='yes'.
Such statements affect both the explorer widget and its context menu. Note: some settings changes do not take effect immediately, they are delayed until "Refresh" happens.

EXPLORER CONDITIONAL SET STATEMENTS. There is an optional clause ... WHERE VALUE operator 'literal' [{AND|OR} value operator 'literal' ...]. Statements with WHERE clauses affect only rows which match the expression. Example:
SET ocelot_explorer_background_color='blue' WHERE value > 'p' OR value regexp '1';

EXPLORER REFRESH STATEMENT. REFRESH is a bit similar to REHASH, but it gets all schemas (databases) -- so has an effect on the server. REFRESH is necessary because we don't know when other users might change data definitions. And it's slow. Whenever users say REFRESH, the explorer tables are redone.

EXPLORER IN OPTIONS MENU. Choosing Options|Detach explorer widget, and rearranging widgets, could result in something like this:

EXPLORER ADVANCED-LEVEL CUSTOMIZING. As explained earlier, customizing the appearance of the explorer widget and the context menu, or specific rows on them, is simple and somewhat like the customizing of the other widgets. However, one can do more with the context menu -- change the text and action and shortcut.
Here, for example, is a statement that says there will be a shortcut associated with the Refresh option:
SET ocelot_explorer_shortcut='Alt+G' WHERE value = 'Refresh';
Here, for example, is a statement that says "Instead of 'Create table', the 'T' menu choice will be 'Select Count(*)' and the effect, if you click it, or type the shortcut key, will be that the statement "SELECT COUNT(*) FROM (table-name-in-context);" will appear on the statement widget, where it is executed, so the result count appears in the grid widget.
SET ocelot_explorer_action='SELECT COUNT(*) FROM ${object_name};', ocelot_explorer_text='Customized Selection' WHERE value = 'Create table';
In other words, users can write their own explorer widgets.
Many of the possible actions can have "macros" -- strings enclosed inside ${...} that will be replaced when the statement is executed. Some macros are: ... ${dialog-table} ${dialog_file} ${occurs_text} ${schema_name} ${object_name} '${object_name}' ${part_name} ${unqualified_part_name} '${part_name}' ${part_type} ${cell} ${clipboard} ${action}. Since ocelotgui source code is supplied, users can download it and see the exact effects by reading the C++ statements in program ocelotgui.cpp, function Context_menu::replacer().

Contact

We need feedback!

Registered github users can simply go to https://github.com/ocelot-inc/ocelotgui and click the "Star" button.

Send bug reports and feature requests to https://github.com/ocelot-inc/ocelotgui/issues. Or send a private note to pgulutzan at ocelot.ca.

There may be announcements from time to time on Ocelot's web page (ocelot.ca) or on the employee blog (http://ocelot.ca/blog).

Any contributions will be appreciated.

Appendix 1 Details about ocelotgui options

Appendix 2 Reference for the ocelotgui debugger (ocelotgui)

Before trying to read this reference, it is a good idea to become familiar with the ocelotgui client and try out the simple demonstration on the ocelot.ca blog.

Temporary warning: for debugging MySQL version 8.0 routines, you need a recent ocelotgui version as pushed to source on or after June 2018. Use ocelotgui version 1.0.7 or later.

All debug statements can be entered on the Statement widget, optionally ending with ";".
Some debug statements can be invoked via the menu or with shortcut keys.
Like other statements, debug statements are editable, are logged, and can cause error or warning messages to appear in the history.
Most statements can be entered even while the routine is running.
Warning: the focus can change, be sure that the cursor is blinking on the Statement Widget and not on the Debug Widget before entering a statement.
The following table shows statements in alphabetical order.

Debug Widget

The Debug Widget appears at the bottom of the window after execution of $debug, and disappears after execution of $exit. It is a tabbed widget, that is, if $setup was done for multiple routines, then the debug widget has multiple tabs and the user can look at a particular routine by clicking its tab.

Above the widget is a line with information about current state. The state information includes the current position (what routine and line number is currently being executed), the result of the last debug statement, and whether the execution is currently halted (it will show "stopped at breakpoint" initially because there is automatically a temporary breakpoint at the start) (it will show "executing" if it is running) (it will show "routine end" at end). For example the line may contain
"Debugger status = (Current position: `test`.`p24` line 3) (Last debug command: tbreakpoint test.p24 3, result: OK, breakpoint_identifier=3) (STOPPED AT BREAKPOINT)"

Below the tab is a display of the routine source, with line numbers on the left. This will appear to be editable; however, the only reason for that is so that $breakpoint and $clear will work. The line which is currently being executed will be highlighted. A line which has a breakpoint will have a mark on the left.

The Debug Widget shares the settings of the Statement Widget. For example, if the user clicks Settings | Statement Widget and changes the background color to yellow, then the Debug Widget will also have a yellow background.

Maintenance

The $install statement places many routines and tables in the xxxmdbug database; to uninstall, simply drop the database.

The $setup statement places copies of routines in the same databases that the original routines are in. These are not dropped automatically; users or administrators should watch for obsolete setup results.

The $refresh statements make temporary tables, which may become large but which should disappear at session end.

Privileges

To do $install, one needs authorization to create a new database, xxxmdbug, and populate it. The user who does $install may then choose to grant or revoke privileges on the procedures of xxxmdbug so that debugging is limited to certain users.

To do $setup routine_name(s), one needs authorization to execute the named routines and to create routines in the same database.

To do $debug, one needs authorization to execute both the original routine and the copy that was made by $setup. Also, one needs READ or EXECUTE authorization for objects in xxxmdbug. Also, one needs the SUPER privilege.

Security

Before allowing ocelotgui in production systems, administrators should know:

The SUPER privilege allows ordinary users to do a few things which ordinarily they can't.

For anyone who knows the workings of the system, it is possible to monitor or disrupt debugger activity. This means, for example, that if a debugger user asks what is the current value of variable X, some other user (with SUPER privilege) could see that the request was made and could see the value.

The debugger runs in a separate thread, this separate thread will attempt to do a CONNECT every time the user calls $debug, with the same options and credentials as the one used for the main thread.

For passing messages between the debugger thread and the main thread, the debugger makes heavy use of a system variable named @@init_connect. It is our belief that the debugger will not interfere with the ordinary usage of @@init_connect (for specifying what to do when connecting); however, the reverse is definitely not true. That is, if someone changes @@init_connect while the debugger is running, the debugger could return unexpected results or even hang.

Simultaneity

It is designed, but not tested in the field, that the debugger should operate while others are simultaneously accessing the same data with the same DBMS instance. Naturally the debugger will slow down other operations if (say) a user chooses to stop at a breakpoint while a table is locked -- but that is a user fault not a product flaw.

Although the debugger is not terribly fast, the majority of the time in a stored procedure is probably taken up by the SQL statements rather than the debugger's processing. Therefore it should be possible to run a long routine for hours or days, occasionally monitoring it to see what the state of the variables is.

Further information

The low-level part of the debugger is based on a product from Hewlett-Packard called MDBug. An interesting early document is http://bazaar.launchpad.net/~hp-mdbug-team/mdbug/trunk/view/head:/debugger.txt.

Appendix 3 Tarantool

NB: There is a better description of ocelotgui for Tarantool in https://github.com/ocelot-inc/ocelotgui-tarantool.

Ordinarily ocelotgui is a client for MySQL/MariaDB, and some documentation assumes that. However, it is possible to connect to Tarantool Tarantool 2.x (with SQL) instead. You get all the same features except the debugger.

The ocelotgui connection to Tarantool is not produced or approved by Tarantool.

You need the latest Tarantool "SQL" server. The official "SQL" release is available now, look for it on https://github.com/tarantool/tarantool/releases. Download it according to the instructions in the Tarantool manual, the version number must be 2.1.2 or later. For a more current version, download from github.com/tarantool/tarantool
and build from source as instructed in the Tarantool manual.

Usually you do not need to install the Tarantool client (libtarantool.so) library, but it is possible to use it if you build ocelotgui with "cmake ... -DOCELOT_THIRD_PARTY=0". If you did that, then this is how to get tarantool.so. The tarantool-dev package does not have it any more. Clone and follow the instructions at github.com/tarantool/tarantool-c ...

cd ~
git clone https://github.com/tarantool/msgpuck.git tarantool-msgpuck
cd tarantool-msgpuck
cmake .
make
sudo make install
cd ~
git clone https://github.com/tarantool/tarantool-c tarantool-c
cd tarantool-c
cp ~/tarantool-msgpuck/msgpuck.h third_party/msgpuck/msgpuck.h
cmake .
make
sudo make install

You need the latest ocelotgui client. The Release 1.8.0 version is okay at the time of release, but some things might not be up to date. It may be better to build it from source. Download from github.com/ocelot-inc/ocelotgui.

Start the Tarantool server, and say:
box.cfg{listen=3301}
-- Second connect if you want LUA '...' to work
box.schema.user.grant('guest','read,write,execute','universe')
net_box = require('net.box')
ocelot_conn2=net_box.new('localhost:3301')
ocelot_conn2:eval('return 5')
NB: user 'guest' can read and write but not create. Therefore for demonstration purposes it is far better to be user 'admin'. To assign a password to user 'admin', say:
box.schema.user.passwd('admin')

Start ocelotgui thus:
ocelotgui --ocelot_dbms='tarantool' --port=3301 --host='localhost' --user='admin' --password='admin'
The initial screen should come up saying "OK", you're connected.

Type some SQL statements in the statement widget at the bottom of the screen.

CREATE TABLE test1 (s1 INT PRIMARY KEY, s2 VARCHAR(5));
INSERT INTO test1 VALUES (1,'a'),(2,'b'),(3,'c');
UPDATE test1 set s2 = s2 || '!';
SELECT * FROM test1;

Now type any other SQL statements, as described in the Tarantool manual. The tutorial SQL statements work.)

Now type
LUA 'return box.space._vindex:select()';
or simply
return box.space._vindex:select();
This will evaluate the expression, without SQL. The expression must return a result set. The result will be tabular (rows and columns), even though box.space._vindex was created with NoSQL.

Bonus feature: A client statement,
CREATE SERVER id ... OPTIONS (PORT ..., HOST ..., USER ..., PASSWORD ...);
allows a second connection. Usually this is a connection to a different server.
Later you can use the second connection to create an SQL table that's populated from a NoSQL space, preserving all the data and converting it to fit in table rows, preserving names if they were made with a Tarantool format clause.
* The advantage is that the second server does no work except (lua box.space.x:select); the main server makes a temporary copy of the result set and the main server does all work required for select-list computations, group by, order by, etc. That should enhance throughput. And, since it's now an SQL table, you can create indexes on it and do SQL manipulations without needing NoSQL.
* The limitation is that you are now working on a copy instead of the original; it might quickly go out of date. * The big limitation is that the first column of the new table is automatically and always the PRIMARY KEY. Therefore if there is any duplication in the space, the CREATE TABLE statement will fail.
Example:

  On #1 (server/lua):
  box.cfg{listen=3301}
  box.schema.user.grant('guest','read,write,create,execute','universe')
  box.execute([[create table a (s1 int primary key, s2 varchar(15));]])
  box.execute([[insert into a values (1,'wombat');]])
  On #2 (server/lua)
  box.cfg{listen=3302}
  box.schema.user.grant('guest','read,write,create,execute','universe')
  box.execute([[CREATE TABLE t2 (x1 INT PRIMARY key, x2 VARCHAR(15));]])
  box.execute([[INSERT INTO t2 VALUES (0, 'Hi!');]])
  On ocelotgui connection:
  CREATE SERVER id FOREIGN DATA WRAPPER ocelot_tarantool OPTIONS (PORT 3302, HOST 'localhost', USER 'guest');
  CREATE TABLE y4 SERVER id LUA 'return box.space._space:select()';
  SELECT * FROM y4;
-- It does not have to be a second server, so simplify the example.
-- It does not have to be LUA '...', it can be RETURN lua-expression.
-- We can have MariaDB=main and Tarantool=remote, or Tarantool=main and Tarantool=remote

Images:
Doubtless you have image (.png or .gif or .jpg) files on your system. For this example, change the three "box.space.timages:insert" lines, changing the file names to file names that are on your system, or changing the directory to wherever you installed ocelotgui documentation. Then "copy" the example code here and "paste" it into the ocelotgui statement widget. (Sometimes it is better to copy and paste statements one at a time rather than all at once.) (Important: "timages" is a quoted identifier, the quote marks are necessary.)

-- Lua function to set a variable to a file contents: based on fio_read.lua:
function load_file(file_name)
  local fio = require('fio')
  local errno = require('errno')
  local f = fio.open(file_name, {'O_RDONLY' })
  if not f then
    error("Failed to open file: "..errno.strerror())
  end
  local data = f:read(1000000)
  f:close()
  return data
end;
DROP TABLE "timages";
create table "timages" (s1 int PRIMARY KEY, s2 scalar, s3 varchar(5));
box.space.timages:insert{1, load_file('/usr/share/doc/ocelotgui/shot1.jpg'), 'shot1'};
box.space.timages:insert{2, load_file('/usr/share/doc/ocelotgui/shot2.jpg'), 'shot2'};
box.space.timages:insert{3, load_file('/usr/share/doc/ocelotgui/shot3.png'), 'shot3'};
SET ocelot_extra_rule_1_display_as = 'image';
SET ocelot_extra_rule_1_condition = 'data_type LIKE ''%BLOB''';
SELECT * FROM "timages";

Alternative: (details are left to the reader's imagination) We could instead use:
a Lua function.

Rules concerning ocelotgui when connecting to tarantool:
* All statements must end with ; (or something established by DELIMITER statement). This applies to Lua as well as SQL.
* If you want a result set for a Lua request, you must say "return". For example "return box.space.T:select();" rather than "box.space.T:select();"
* SQL-style comments /* ... */ will not be considered errors inside Lua statements, but will not be passed to the server.
* Statements may contain [[...]] strings, but not =[[...]]= strings.
* Defaults are MySQL/MariaDB defaults: --delimiter is off but ansi_quotes is on.
* (Possible flaw) When ocelotgui is displaying an image, cpu time rises.
* Decisions to right-justify, or display as images, are automatic rather than dependent on data type.
* SQL "verbs", for example COMMIT, should not be used as Lua identifiers.
* If you use SQL, you need Tarantool 2.1 or (preferably) a later version. If you only use Lua, you can use Tarantool 1.10 or an earlier version.
* We don't accept identifiers longer than 64 characters.

If the Tarantool server version release date is after the date of the ocelotgui release, then there will probably be problems because the parsers are different. Sometimes this can be solved by downloading ocelotgui source and building again.

Appendix 4 Windows

These are extra instructions for ocelotgui for Microsoft Windows (TM).

The Windows ocelotgui program has the same functionality as the Linux ocelotgui program, but is newer and has only been tested with basic Windows 10 64-bit machinery. We believe that on some other Windows platforms it won't start.

Connection should work to any modern MySQL or MariaDB server.

How to get it:
* Download the ocelotgui zip file from github. Check https://github.com/ocelot-inc/ocelotgui/blob/master/README.md to see where the latest release is. For example it might be https://github.com/ocelot-inc/ocelotgui/releases/download/1.8.0/ocelotgui-1.8.0-1.ocelotgui.zip
* Unzip. It was zipped with 7-zip from http://www.7-zip.org, but other utilities should work. For example, on Windows command prompt, if you have the PowerShell utility on your path: PowerShell Expand-Archive ocelotgui-1.8.0-1.ocelotgui.zip c:\ocelotgui
* Read the COPYING and LICENSE arrangements. On Windows ocelotgui is statically linked to Qt and MariaDB libraries, so the copyright and licensing is not the same as for Linux.
* The unzipped package includes a file named ocelotgui.exe. This is the file that you need for day-to-day ocelotgui use. There is no installation file. There is no need to download a MySQL/MariaDB client library. There is no need to download a Qt library. Since ocelotgui.exe may read other files on the same directory, it is best to leave it in the directory that you unzipped to.

How to test it:
Start up a MySQL or MariaDB server that is easily accessible (ocelotgui can use SSL etc. but for an initial test make it easy). Let's assume the download directory is c:\ocelotgui. Let's assume the host is 192.168.1.65, the user name is 'root', the password is 'root', the port is 3306. Say:
c:\ocelotgui.exe --port=3306 --host=192.168.1.65 --port=3306 --user=root --password=root --protocol=tcp

Full instructions are in the main documentation. The following notes are specifically for ocelotgui for Windows.
* When connecting, use protocol=tcp.
* this is a 32-bit .exe file
* these DLLs are used ... (they're all supplied with Windows 10) ntdll.dll, wow64.dll, wow64win.dll, wow64cpu.dll -- should be in system32 for 32-bit process running on a 64-bit computer
* this DLL might be used: libeay.dll. It only matters if there is a .mylogin.cnf file, which is rare. If libeay.dll is not found on the system, .mylogin.cnf is ignored.
* notice that the licence is slightly different from the Linux distribution, because the MariaDB client library and the Qt library are statically linked (in the Linux distribution they are dynamically linked and supplied separately).
* the program does not always take over the screen at start time. You have to look for an icon on the bottom and click it, or check the task manager.
* initial application load is very slow, although after that there are no known performance problems.

The rest of this appendix is for advanced users only.

Building ocelotgui.exe from source

Appendix 5 Getting and using the ocelotgui source

The ocelotgui source package has everything necessary to compile and link and install for any Linux distribution, or FreeBSD. If the typical developer packages such as cmake and the gcc c/c++ compiler have been installed already, building an executable usually takes less than fifteen minutes.

The official location of the project is on github: https://github.com/ocelot-inc/ocelotgui. This is where the latest source files are. This is what can be "cloned". Typically, to get it, one would install git, cd to a download directory, then

git clone https://github.com/ocelot-inc/ocelotgui

A clone has the latest source, but not executables. A clone may contain patches which are not yet part of a release. Ordinarily users are advised to use a release rather than a clone, until they have used ocelotgui for a while.

The releases for ocelot-inc/ocelotgui are also on github: https://github.com/ocelot-inc/ocelotgui/releases. A release includes the source files as of the release time. Although the release does not have the "latest" source which is in ocelot-inc/ocelotgui, it usually is more stable. A release file is highlighted in green by github and is named ocelotgui-[version].tar.gz. Since version 1.0.9, there is also a release file named ocelotgui_[version].orig.tar.gz which is preferable because it does not contain unnecessary executables. Thus release 1.8.0 is at https://github.com/ocelot-inc/ocelotgui/releases/download/1.8.0/ocelotgui_1.8.0.orig.tar.gz. Typically, to get it, one would cd to a download directory, then

wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.8.0/ocelotgui_1.8.0.orig.tar.gz

On Debian-like systems some packages must be installed first. For example on Ubuntu:

 sudo apt-get install build-essential gcc cmake make
 #It is possible to say libmariadbclient-dev instead of libmysqlclient-dev
 sudo apt-get install libmysqlclient-dev
 #Do the following if and only if build is for use with Qt4
 sudo apt-get install qt4-dev-tools
 #Do the following if and only if build is for use with Qt5
 sudo apt-get install qt5-default qtbase5-dev qt5-qmake qtbase5-dev-tools
 #Do the following if and only if build is for use with Qt6
 sudo apt-get install qt-6-base-dev qt-6-base-dev-tools

On RPM-like systems some packages must be installed first with "yum install" or "dnf install" or "urpmi". For example on Mageia:

 urpmi gcc gcc-c++ make cmake git
 #The name of the following package (containing mysql.h) varies,
 #it might be lib64mariadb-devel or libmysqlclient-devel or mariadb-devel
 urpmi mysql-devel
 urpmi rpm-build       
 #Do the following if and only if build is for use with Qt4
 urpmi libqt4-devel
 #Do the following if and only if build is for use with Qt5
 #The name of the following package varies,
 #it might be qt5-qtbase-devel
 urpmi libqt5-devel

On Fedora 35 which is an RPM-like system but might have Qt6 not Qt4 or Qt5:

sudo dnf install qt6-qtbase qt-devel qt6-qtbase-devel

On Manjaro which is an ArchLinux-like system:

# Some people would recommend sudo pacman -S base-devel
sudo pacman -S binutils make gcc cmake 
sudo pacman -S mariadb-clients

(Package builds on ArchLinux-like systems can also be done with the PKGBUILD file in the ocelotgui github repository.)

Unpack all the source files by saying:

 tar -zxvf ocelotgui_1.8.0.orig.tar.gz
 cd ocelotgui-1.8.0

 make clean        # unnecessary the first time
 rm CMakeCache.txt # unnecessary the first time
 cmake .
 make
 sudo make install

 ./deb_build.sh
 sudo apt install /tmp/debian3/ocelotgui_1.8.0-1_amd64.deb
 #or sudo apt install /tmp/debian3/ocelotgui_1.8.0-1_i386.deb

 ./rpm_build.sh
 sudo rpm -i ~/ocelotgui_rpm//rp/rpmbuild/RPMS/x86_64/ocelotgui-1.8.0-1.x86_64.rpm
 #or sudo rpm -i ~/ocelotgui_rpm//rp/rpmbuild/RPMS/x86_64/ocelotgui-1.8.0-1.i686.rpm

Some other facts about the source package, for users who like to explore code ... files with the extension *.png or *.htm or *.md or *.txt or *.jpg are for documentation, the file LICENSE.GPL is for legal requirements, and files with the extension *.cpp or *.pro or *.h are source code. The main() code is in ocelotgui.cpp. All the source code has comments. Since ocelotgui is a Qt-using program, it is also possible to use Qt Creator as an IDE editor/compiler and qmake to build -- the file ocelotgui.pro exists for this purpose, and the comments at the end of ocelotgui.pro have more explanation. For example, on Ubuntu 14.04, if the intent is to rebuild for Qt 4 from source, these instructions have been known to work:

 sudo apt-get install qt4-qmake
 sudo apt-get install libqt4-dev
 cd [path to ocelotgui source files]
 make clean
 /usr/bin/qmake-qt4 -config release
 make