Peter Gulutzan's blog
Books by employees
Ocelot Computer Services Inc.
Makers of ocelotgui
The Ocelot GUI (ocelotgui), 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) 2014-2017, Ocelot Computer Services Inc. 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.
Installing... Prerequisites ... Getting the Qt library ... Getting the libmysqlclient.so library ... Getting the ocelotgui executable package ... Getting and using the ocelotgui source ... Starting the program
Illustrating... Some screenshots
Using... User Manual ... Executive Summary ... The company, the product, and the status ... Downloading, installing, and building ... Starting ... Statement widget ... Client statements ... History widget ... Result widget ... Menu ... Debugger ... Special effects ... Contact
The installation instructions in this file are for Linux. If you prefer to run on Windows, read the installation instructions in the file windows.txt and come back to this file 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" to install Qt5.
You will need to know the version of the Qt library. It 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.
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 name like "libmysqlclient-dev" or "libmysqlclient-devel".
A tip for Mageia 5: You can use "urpmf
A tip for openSUSE 13.1: if neither Qt nor libmysqlclient libraries exist, say:
sudo zypper install libqt4-devel sudo zypper install mariadb-client sudo zypper install libmysqlclient-devel
The important file is named "libmysqlclient.so". 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]Several other directories are searched; for details start ocelotgui after installation and choose Help | libmysqlclient.
The libmysqlclient library is not necessary for ocelotgui installation; however, it is necessary at runtime in order to connect to a database server.
Getting the ocelotgui executable package
There are ocelotgui binary packages for platforms such as Ubuntu where "Debian-like" packages are preferred, or platforms such as Mageia/SUSE/Fedora where "RPM-like" packages are preferred. If one of the following ocelotgui binary packages is compatible with your platform, cut and paste the corresponding pair of instructions onto your computer and you can be up and running in about 15 seconds.
For 32-bit, Debian-like, Qt4
wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.0.6/ocelotgui_1.0.6qt4-1_i386.deb sudo dpkg -i ocelotgui_1.0.6qt4-1_i386.debFor 32-bit, Debian-like, Qt5
wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.0.6/ocelotgui_1.0.6-1_i386.deb sudo dpkg -i ocelotgui_1.0.6-1_i386.debFor 64-bit, Debian-like, Qt4
wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.0.6/ocelotgui_1.0.6qt4-1_amd64.deb sudo dpkg -i ocelotgui_1.0.6qt4-1_amd64.debFor 64-bit, Debian-like, Qt5
wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.0.6/ocelotgui_1.0.6-1_amd64.deb sudo dpkg -i ocelotgui_1.0.6-1_amd64.debFor 64-bit, RPM-like, Qt4
wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.0.6/ocelotgui-1.0.6qt4-1.x86_64.rpm sudo rpm -i ocelotgui-1.0.6qt4-1.x86_64.rpmFor 64-bit, RPM-like, Qt5
wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.0.6/ocelotgui-1.0.6-1.x86_64.rpm sudo rpm -i ocelotgui-1.0.6-1.x86_64.rpm
Getting and using the ocelotgui source
The ocelotgui source package has everything necessary to compile and link and install for any Linux distribution. If the typical developer packages such as cmake and the gcc c/c++ compiler has 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. Thus release 1.0.6 is at https://github.com/ocelot-inc/ocelotgui/releases/download/1.0.6/ocelotgui-1.0.6.tar.gz. Typically, to get it, one would cd to a download directory, then
wget https://github.com/ocelot-inc/ocelotgui/releases/download/1.0.6/ocelotgui-1.0.6.tar.gzor use a browser to go to https://github.com/ocelot-inc/ocelotgui/releases and click ocelotgui-1.0.6.tar.gz.
On Debian-like systems some packages must be installed first. For example on Ubuntu:
sudo apt-get install gcc cmake make 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
On RPM-like systems some packages must be installed first. 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 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 urpmi libqt5-devel
Unpack all the source files by saying:
tar -zxvf ocelotgui-1.0.6.tar.gz cd ocelotguiAt this point it is a good idea to examine the file CMakeLists.txt. This file has comments about options which are available to customize the build process: CMAKE_PREFIX_PATH, CMAKE_INSTALL_PREFIX, MYSQL_INCLUDE_DIR, WITH_QT4. For explanation of these flags, read the comments in the CMakeLists.txt file. If no customizing is necessary, the typical build process is:
make clean # unnecessary the first time rm CMakeCache.txt # unnecessary the first time cmake . make sudo make installThe above instructions will usually put the ocelotgui program and directories in subdirectories of /usr/local, so if /usr/local/bin is on your PATH then after this saying ocelotgui will start the program. However, it is sometimes better to make and install a package, which will cause a few additional steps to be performed, such as registering so that ocelotgui can be started from the launcher. For Debian-like platforms say:
cmake . -DCPACK_GENERATOR="DEB" make cpack sudo dpkg -i ocelotgui_1.0.6-1_i386.deb #or sudo dpkg -i ocelotgui_1.0.6-1_amd64.debFor RPM-like platforms say:
cmake . -DCPACK_GENERATOR="RPM" make cpack sudo rpm -i ocelotgui-1.0.6-1.x86_64.rpm #or sudo rpm -i ocelotgui-1.0.6-1.i686.rpmUsually the result will go to subdirectories of /usr, in which case, if /usr/bin is on your PATH, then saying ocelotgui will start the program. For additional cmake options see the comments in CMakeLists.txt.
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 makeFor more commentary about compiling and building, read an ocelotgui.cpp comment that begins with the words "General comments". For instructions to build from source on Microsoft Windows, see the file windows.txt.
Starting the program
After installing and making sure that ocelotgui is on the path, start it with
ocelotguior use options, for example
ocelotgui --host=127.0.0.1 --user=joe --password=secret-- if the program starts, and menu items such as Help|Manual work, then installation is successful. Stop again with File|Exit or control-Q.
Warning: Some menu shortcut keys may not work properly with Ubuntu 14.04.
Version 1.0.6, December 12 2017
Copyright (c) 2014-2017 by Ocelot Computer Services Inc. 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
The ocelotgui application, more fully '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 company, the product, and the status
Ocelot Computer Services is a Canadian company which has specialized in database products for thirty years. Its main employees worked for MySQL AB and Sun Microsystems and Oracle between 2003 and 2011.
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. Ocelot 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.
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 https://github.com/ocelot-inc/ocelotgui/blob/master/options.txt.
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 https://github.com/ocelot-inc/ocelotgui/blob/master/options.txt);
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:
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.
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.
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 commands' http://dev.mysql.com/doc/refman/5.6/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>'.
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.
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 cell 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.
The menu at the top of the screen has File, Edit, Run, Settings, Options, Debug and Help.
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
Run|Execute or Ctrl+E or Ctrl+Enter causes execution of whatever is in the
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. Font settings
involve further dialog boxes which are standard with Qt.
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.
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.
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:
For a walk through a debugger example, with screenshots, see this blog post: http://ocelot.ca/blog/the-ocelotgui-debugger. For reference, read: https://github.com/ocelot-inc/ocelotgui/blob/master/debugger_reference.txt.
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.
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: TAB COMPLETION. 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.
RE: HINTING FOR COLUMN NAMES. Although hints for syntax appear by default, hints for table / column identifiers do not. In order to make identifiers appear on the hint list: (1) ensure the setting for auto_rehash has not been turned off; (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: 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 have made a more permanent fix for this problem in ocelotgui version 1.0.6.
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. Ocelot will replace the value 'row_number() over ()'
with the row number within the result set. For example, try
RE: HOVERING. Use the mouse to hover over a word in the statement widget, and Ocelot will display what kind of word it is, for example "table identifier".
RE: FORMAT. Click Edit|Format, and Ocelot 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.
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.
Copyright (c) 2002-2017 by Ocelot Computer Services Inc. All rights reserved.
Send enquiries or suggestions to firstname.lastname@example.org.