What you _can_ do with SQLMinus:
Buttons:
Connect: connects to database and fills table list. Also caches column
list as specified in ini file. Load the ini file again.
Run: Runs the query in the input buffer. There should be only one.
Run Selected: Run the selected command/query
Data: shows data for selected table, restricting to columns if selected
(show-data)
Clear: clears visible textarea or JTable.
Exit: closes database connection and exits
Meta: Shows database metadata for selected tables, or for database if
no table selected.
Commands that may be entered in the Input Area:
abbr:
Will add an abbreviation to the list in the session.
example: "abbr abc this is the expansion"
will expand abc to "this is the expansion"
"abbr" will list all abbreviations
cache: "cache tablename" will cache the column names of the given
table/s. After that tabbing will display these columns.
e.g. "cache Project"
"cache Project%"
"cache Project,New, Customer"
refresh: will refresh given cache. (Currently only for tables)
"refresh tables" will refresh the tables list. Any new tables added
will now show. Changing the catalog will not change the list.
reflect: allows us to inspect a class for fields and methods
"reflect java.sql.DatabaseMetaData" allows us to see fields and
methods of the meta data. It even executes all methods that
dont require any parameters and prints the results.
However, this doesnt seem to work
on Oracle, and you need to explicitly invoke a method.
"reflect ResultSet"
You can even say "reflect java.awt.Font" !
invoke: invoke a method in java.sql.DatabaseMetaData.
"invoke supportsBatchOperations()"
"invoke getTables(null,"uid","%", null)"
"invoke methodname(java.sql.ResultSet.TYPE_FORWARD_FETCH_ONLY)"
set fixedsql:
You can specify an sql to remember. Now whenever you select a
table, it will run the SQL for that table.
"set fixedsql select count(*) from $tn"
Now as you select tables, or use the down arrow key in the table
list, the "$tn" will be substituted with the selected tablename
and the result will be shown to you. You do not need to
repeatedly type an SQL. This example will show the count of rows
from each table selected.
"set fixedsql select * from $tn"
This will show you all the data from each table as you select a
table.
"set fixedsql describe $tn"
This will show you the metadata of each table as you select a
table.
"set fixedsql insertscript $tn $tn.sql"
This will create an insert script for each selected table and
write to a file named after the table with a ".sql" extension.
Control-N - this searches backward to find a word starting with the word
at the cursor and fills the word in.
Tab - replaces the word at the cursor with tablename or columnname. You
can keep tabbing till you get the required word.
space- will check abbreviation list for exact word at cursor.
Control-Z - executes command selected, or if none selected then the
command on the current line (single line only).
help- typing help followed by space will expand to the various help
options. You may save a text area to a file, or read up from a file.
You may save the JTable to a file also.
insertscript tablename filename - create an insert script and write it to
a given file. User may define variable for date and time functions to be
used (see ini file for examples).
createscript tablename filename - creates a create table script and
writes to the given file. Is database specific.
Paging of rows:
Currently i only display the first 300 rows of a table.
A quick dirty solution is as follows:
Add the following to your SQL: "/*=301-400*/"
OR : "/*=301-500*/"
Avoid extra spaces and stick to the exact format, not forgetting the
"=" after the comment start.
set header off
does not print column heading. helpful when running "fixedsql".
set columnwidth n
makes column width to n characters in text displays.
set font aaa: changes the font on output area to the specified font
family ("arial", "helvetica", "monospaced", etc). I have not yet
provided for font size and style.
set outputformat ( jtable | text )
By default, jtable. SQL results go into the JTable. If you wish to
see many results on one sheet, you can set it to text (quite ugly).
You may still use the jtable option and save the contents to a file
usig the "save table to filename" command.
save to
you may save the input/output/history/table area to a file.
e.g. save input to inp.txt
save table to tab.txt
read from
You may read in a file into the input or history area.
e.g. read input from inp.txt
e.g. read history from hist.txt
read from lines=m,n
You may read in a file into the input or history area from
lines m thru n inclusive
e.g. read input from favorite.sql lines=1,5
e.g. read history from hist.txt lines=1,1
Other keys:
Alt 1 - goto Input area (perhaps this can become a toggle later)
Alt 2 - goto output/table area
Alt 3 - goto History area
At this moment, you need to click inside the JTable first time, for the
keys to have an effect.
Within Jtable (table mode):
Control D - creates an insert script for the selected rows and places in
Input area. You may edit the script and run.
Alt U - creates an update script for the selected rows and places in
Input area. You may edit and run.
Alt I - creates an insert script for the selected rows and places in
Input area. You may edit and run.
These are precursors to proper table editing for which i should probably
create a proper data-aware widget.
Within Table List:
Alt d - In the table list if you select a table and press Alt-d will
show data (show-data).
Seeing multiple tables at once:
Setting tableview to "multiple" will put you into Frames mode. You will
see a new table for each select or show-data action. To revert to single
table mode, "set tableview single". You may select "set tableview" in
the relevant menu.
Bookmarks ("bookmark help")
SQL queries may be saved and invoked by name in the ini file or online.
e.g.
bookmark client sql select * from client where ...
bookmark project file proj.sql
bookmark proj1 system cat proj1.sql
the following may be given in input area in addition to the above:
bookmark - shows names of bookmarks
bookmark list - shows names and contents
bookmark client run - execute the bookmark client
bookmark client get - paste the bookmark client into input area
bookmark repaint - repaint the bookmark menu with current bookmarks
System
execute a system command with "system cat foo.txt"
Defining a bookmarks file:
A file may be created that contains many SQL statements. Since the size
may be large, you may want to load this file on demand. The "set
bookmarks a.xml,b.xml" command lets you do that. Upon connection, an
entry will be added to the Bookmark menu for loading each bookmarks
file. Upon clicking, a new menu will be created for that file, with each
menuitem representing an SQL. I have placed Oracles alter syntax in
ora_alter.xml, create syntax in ora_create.xml and Oracle tuning commands
in ora_tune.xml.
Making commands database specific:
The ini file contains hooks much like mutt. the current feature has a
watered-down version which will be expanded very soon as I figure out
what all needs to be provided.
If i wish certain setting to apply to any Oracle database, i could say:
"DSN-hook oracle set abbr ut user_tablespaces". This would work if the
DSN contains the string "oracle".
I could restrict the setting to when i am connected to a particular SID:
"DSN-hook oracle.*ORCL$ set abbr ut user_tablespaces".
This would work if the string "oracle" is followed by "ORCL" at the end
of the DSN.
The syntax is:
"-hook "
variable can be any previously declared variable. The value is a
perl-compatible regular expression that must match the value of the
variable for the command to execute. (A dot matches all).
At present, this is restricted to
the execution of commands while reading the ini file only. Thus, it only
provides conditional command execution (such as config settings) within
the file. However, once I decide a way to store these in memory, then
these could be queried at runtime to apply to tables and columns or
userids. The scope will be endless. "_connect_to" is a temporary vatiable
I have defined that allows me to load setting based on the database i am
connecting to. It has no usage within the software. You may create your
own variables similarly. Put a single underscore prior to your own
variables to prevent clashes with any variables that may get added to
the software later.
There are many set options in the mysql.ini file which are described
there.