User Manual
Requirements
MitzaSQL runs on Linux and requires Python 3.6.
Supported MySQL versions:
- 5.6
- 5.7
- 8
Dependencies
- urwid v2
- mysql-connector-python v8
- appdirs v1.4
Install
pip3 install --user mitzasql
On systems which have a default Python 3 installation replace pip3 with pip.
If you require clipboard support you need to install the extra dependency:
pip3 install --user mitzasql[clipboard]
This will install the pyperclip module. Keep in mind that pyperclip requires xclip/xsel to be installed on Linux, or the gtk/qt python modules.
MitzaSQL creates by default the following files and folders in your home directory:
- $HOME/.config/mitzasql/sessions.ini
- $HOME/.cache/mitzasql/log/mitzasql.log
- $HOME/.cache/mitzasql/schema/
You can override the default location of the sessions file using the --sessions_file switch. See the Command line options section
Usage
mitzasql
Run mitzasql --help to see all the available options.
Command line options
| Option | Description |
| --session=[session_name] | Skip the 'select session' screen and connect to the previously saved 'session_name' |
| --list | Show all the saved sessions |
|
--host=[host] --port=[port] --user=[user] --password=[password] --database=[database] |
Connect to new database server |
| --macro=[/path/to/macro/file] | Run macro |
| --sessions_file=[/path/to/sessions/file] | Store the saved sessions in this file |
| --no-logging | Disable logging |
Keyboard shortcuts
The user interface supports the following methods for performing actions:
- keyboard shortcuts
- VIM-like keyboard shortcuts
- VIM-like commands
- action keys
Keyboard shortcuts list
| Key(s) | Action | VIM emulation | Context |
| Arrow keys | Scroll / move | all | |
| home / end | Scroll to top / bottom | all | |
| page up / down | Scroll one page up / down | all | |
| ctrl left / right | Go to first / last column | tables | |
| j / k / h / l | Scroll / move down / up / left / right | yes | all except text inputs |
| ctrl u / d | Scroll one page up / down | yes | all except text inputs |
| gg | Scroll to top | yes | tables, lists |
| G | Scroll to bottom | yes | tables, lists |
| 0 | Go to first column | yes | tables |
| $ | Go to last column | yes | tables |
| enter | Select / change context | all | |
| esc | Exit context / go back | all | |
| : | Enter command mode | yes | tables |
| q: | Enter command mode and show last command | yes | tables |
| ctrl p | Go back in the command history | yes | command mode |
| ctrl n | Go forward in the command history | yes | command mode |
| n / p | Go to next or previous search result | yes | search mode |
| / | Enter search mode | yes | server view, database view |
| tab | Start autocomplete / select next suggested keyword | yes | all contexts which support commands, query editor |
| shift tab | Select previous suggested keyword during autocomplete | yes | all contexts which support commands, query editor |
| ctrl o | Open the "change table" popup | database table | |
| ctrl shift up / down | Resize the query editor | query editor | |
| ctrl c / v | Copy and paste to/from the system clipboard (only if the pyperclip module is installed ) | query editor | |
Actions keys are equivalent to regular buttons found in a conventional user interface. To perform an action press a specific key highlighted with a different color than the rest of the text. In the example below:
- to quit press
k - to show the help press
F1 - to refresh the current table press
F5
Some of the VIM-style keys support VIM motions:
10lwill scroll 10 columns to the right25jwill scroll down 25 rows
VIM-like commands
Commands support autocompletion for the command name and the first argument. For example, typing :res and pressing tab will autocomplete the :resize command. Pressing tab again will autocomplete the column name.
To exit command mode press esc.
| Command | Action | Context |
| :q / :quit | Exit program | all except server view |
| /[keyword] | Search database or table. Press `n` or `p` to go to the next or previous search result | server view, database view |
| :resize [column name] [increment] |
Resize [column name] by [increment]. [increment] can be a positive or a negative value:
:resize date 30
:resize date -10
|
tables |
| :sort [column name] [asc|desc] | Sort [column name] asceding or descending. | all except server view |
| :clearcache | Clear the schema caches. When resizing a column the new width is cached in order to persist it across restarts. Calling this command will remove all cache files. | all except server view |
The following commands are available only when browsing a MySQL table or view and they act as shortcuts to writing full SQL queries for filtering data. For example, using the command :eq id 100 is equivalent to writing SELECT * FROM [current table] WHERE id = 100. As such it is important to appropriately quote the value for the filter in order to avoid SQL syntax errors, using the same . Starting with version 1.4.3 string and temporal values are automatically quoted for all commands except between and nbetween.eq command on a string column will result in an SQL error if the value is not quoted ('value')
| Command | Action | SQL WHERE clause |
| :eq [column] [value] |
Find row where [column] equals [value]. Ex:
:eq id 100
Will find the row with id 100. As of v1.4.3 values involving string/temporal columns will be quoted automatically |
column = value |
| :neq [column] [value] | Find row where [column] doesn't equal [value] | column != value |
| :lt [column] [value] | Find the rows where [column] is lower than [value] | column < value |
| :lte [column] [value] | Find the rows where [column] is lower or equal than [value] | column <= value |
| :gt [column] [value] | Find the rows where [column] is greater than [value] | column > value |
| :gte [column] [value] | Find the rows where [column] is greater or equal than [value] | column >= value |
| :in [column] [value1, value2, value3] | Find the rows where [column] is in set | column in (value1, value2, value3) |
| :nin [column] [value1, value2, value3] | Find the rows where [column] is not in set | column not in (value1, value2, value3) |
| :null [column] | Find the rows where [column] is null | column is null |
| :nnull [column] | Find the rows where [column] is not null | column is not null |
| :empty [column] | Find the rows where [column] is empty | column = '' |
| :nempty [column] | Find the rows where [column] is not empty | column != '' |
| :like [column] [value] | Find the rows where [column] contains [value]. This command quotes the value automatically. Manually quoting the value will most likely yield unwanted results. You can use %value% to search for substrings. | column LIKE 'value' |
| :nlike [column] [value] | Find the rows where [column] doesn't contains [value] | column NOT LIKE 'value' |
| :between [column] [value1] [value2] | Find the rows where [column] value is between [value1] and [value2]. If you are filtering a date column make sure you are quoting the values. Ex: :between last_update '2017-09-09 12:20' '2018-09-02 24:32' | column BETWEEN 'value1' AND 'value2' |
| :nbetween [column] [value1] [value2] | Find the rows where [column] value is not between [value1] and [value2]. If you are filtering a date column make sure you are quoting the values. Ex: :between last_update '2017-09-09 12:20' '2018-09-02 24:32' | column NOT BETWEEN 'value1' AND 'value2' |
| :clearfilters | Clear the previously applied filter command. |
Query editor
Pressing F2 will open the SQL query editor.
| Key(s) | Action |
| f9 | Execute query |
| ctrl f9 | Clear editor |
| ctrl p / n | Go back / forward in the query history |
| ctrl shift up / down | Resize the editor |
| ctrl c / v | Copy and paste to/from the system clipboard (only if the pyperclip module is installed ) |
| esc | Close the editor |
| tab | Start autocomplete / select next suggested keyword |
| shift tab | Select previous suggested keyword during autocomplete |
The autocomplete feature will suggest keywords and schema object names depending on the SQL statement. Schema object name suggestions work only for the main data manipulation statements:
- SELECT
- UPDATE
- INSERT
- REPLACE
- DELETE
- CALL
For other types of statements the autocomplete system falls back to dumb suggestions (keywords which match the beginning of a word).
Clipboard support
Clipboard support is an optional feature implemented in the Query Editor with the help of the pyperclip module. This feature speeds up considerably pasting large SQL statements in the query window. Without it you can use your terminal’s copy/paste feature but you will notice a slow down in case you are pasting a large SQL statement - this issue is caused by the syntax highlighting implementation.
Text inputs
All the text inputs support basic Emacs keyboard shortcuts.
Macros
The main use case for the macro functionality is testing the user interface but it can be used to script the interface. The functionality is basic and might be buggy. To create a macro use the macrofile.txt as template and run the program with the --macro option:
mitzasql --macro /path/to/your-macrofile.txt
To see more macro examples go over the UI tests.