DbTalk - talk to your database servers

There is a new development version of DbTalk which works with newest Ruby, Fox and FXRuby versions. Download dbtalk075_050314.zip (29kB). Ruby 1.8.2+ is recommended, FXRuby version later or equal to 1.2.5 is required (and consequently Fox 1.2.x).

By calling it "development version" I mean it still does not have a couple of features I want to add before another release. However it is pretty stable.

Please note a few changes in key shortcuts I made to be more compatible with other database managers. The most important is executing a command with F5 which seems to be a de-facto standard. For others see menus.

Among new features are:
* multiple result set support
* db tree and result table now support clipboard and DND operations

Content

• Introduction
• What's New
• Screenshot Tour
• Menu Description
• More Information On Some Features
  • Connection Opening
  • Transaction Support
  • SQL Parsing
  • Syntax Highlighting
  • Text Completion
  • Result Table Color
• ToDo
• Requirements
  • UNIX
  • Windows
• Download and Installation
• Known Issues
• Contact

Introduction

DbTalk is an interactive GUI based tool for database querying, programming, administration etc. It consists of three basic parts:

• SQL editor
• query result viewer
• database structure viewer

DbTalk is written in a modern interpreted language called Ruby. It uses the portable graphical toolkit FOX for the GUI. DbTalk runs on various UNIX flavors (notably Linux and FreeBSD) as well as on MS Windows.

Important features of DbTalk are:

• ODBC support
• native support for MySQL
• native support for PostgreSQL (UNIX only)
• multiple database/server connections with easily configurable profiles
• editing and testing of multiline SQL commands (e.g. procedure definitions) with syntax highlighting and text completion (SQL + table and column names)
• saving SQL queries into files for later use
• nice tabular view of returned data
• tree view of the database structure
• GPL

What's New

It is now possible to download a development version of DbTalk. It is not yet ready for release but contains quite a few new features which you may want to try (e.g. multiple result sets, cleaner GUI, support for clipboard and DND operations from the database tree and the result table). See the download section.

The new home of DbTalk is www.insula.cz/dbtalk/. Please update your bookmarks.

Current version is 0.73. This version brings very minor changes made to ensure that DbTalk can run with more different versions of Ruby and FXRuby. Tested configurations are listed in the Requirements section.

DbTalk 0.72 introduced following changes:

• The ODBC module was improved and the bug in cursor reading was removed (thanks to Erich Roos for his research).
• DbTalk supports database schemas in PostgreSQL 7.3+.
• DbTalk now captures stderr on UNIX to catch notifications from the PostgreSQL library.
• There is now a basic transaction support. It is possible to switch autocommit mode and send commit and rollback commands with toolbar buttons.
• The built-in editor recognizes new keywords: 'schema', 'abort', 'start', 'transaction', 'isolation level', 'read committed', 'serializable ', 'case' and 'full'.
• The profile administration is slightly improved.
• DbTalk is now compatible with the latest stable Ruby (1.6.8) and FXRuby (1.0.20) versions.

Screenshot Tour

At the moment there are no command line options. Just run ruby dbtalk.rb and if you have all required packages installed (see bellow) the program starts up.

There is a message about loaded database libraries in the bottom frame. You can open a database connection either with the menu command or using the keyboard shortcut. The connection window pops-up.

Connection data can be stored in profiles.
If you open a new connection to a database DbTalk creates the tree view of the database structure. The editor frame waits for your SQL commands. The bottom text frame contains status messages obtained from the backend.

Pressing Ctrl-E you send the SQL command under the cursor to the database backend. If the command returns a result set it is displayed in the table.

You can switch freely between tabular and text views.
Pressing F5 while editing brings you the text completion dialog.

The tree view has its own context sensitive small menu accessible with right mouse button click.

You can open several database connections and use the combo box to switch them.

In the options menu you can customize various features of DbTalk. The following dialog allows you to set syntax highlighting colors.

Menu Description

File

• New - clear the editor buffer (save or discard current content).
• Open - open new file (save or discard current).
• Save - save current file.
• Save as - save current file with different name.
• Quit - quit DbTalk.

Edit

• Copy - copy selected text to clipboard.
• Cut - copy selected text to clipboard and delete it.
• Paste - insert text from clipboard.
• Delete - delete selected text.
• Shift left - shift selected text one position left.
• Shift right - shift selected text one position right.
• Shift tab left - shift selected text one tab position left.
• Shift tab right - shift selected text one tab position right.
• Find - open string search dialogue.
• Replace - open string search and replace dialogue.
• Go to matching (..) - go to the matching brace or parenthesis.

SQL

• Select - insert the skeleton of SQL SELECT.
• Insert - insert the skeleton of SQL INSERT.
• Update - insert the skeleton of SQL UPDATE.
• Delete - insert the skeleton of SQL DELETE.
• Select command - select current SQL command in the editor. Useful for checking exactly what is sent to the backend.
• Complete text - shows a dialog with SQL keyword and database element names. The list can be limited with a regular expression mask. The mask is initialized according to already written letters if possible. In case there is only one possibility to complete the text it is immediately done and no dialog is shown.
• Paste from DB tree - insert selected items from DB tree delimited with commas.
• Paste from result table - selected items from the result table are pasted into the editor window in format suitable for use in SQL commands.

Session

• Open connection - open new connection.
• Close connection - close current connection.
• Execute command - execute command under the cursor.
• Run script - execute all commands starting from the cursor.
• Autocommit - toggle autocommit status.
• Commit - execute commit command.
• Rollback - execute rollback command.
• Show DB tree - toggle DB tree view.
• Reload DB tree - reload and recreate the DB tree.
• Toggle result - toggle between text and tabular result view.

Options

• Fonts - configure custom fonts.
• Colors - configure various application colors.
• Highlight syntax - toggle syntax highlighting.

Help

• About - (surprise) show the copyright message plus some info on Ruby and library versions.

DB tree popup menu

The menu popups after clicking on the DB tree with the right mouse button. It is partially context sensitive - not all listed options are always available.

• Show structure - show structure of the selected item in the result frame.
• Browse - show rows from the selected table in the result frame. You can move forward/backward in the entire table with arrows. You can also setup the number of lines displayed in one step. This function is more suitable for looking at unknown tables than plain "select *" as DbTalk currently offers no way to interrupt running query. In case of a huge table the uncontrolled query can lead to reading tens of thousands rows which is at least rather memory consuming.
• Show databases - show database list for the current connection in the result window. If your current connection is ODBC the list of ODBC data sources is displayed.
• Paste items to editor - insert selected items delimited with commas into the editor.
• Reload tree - reload and recreate the DB tree.

More Information On Some Features

Connection Opening

The fields in the connection opening dialog were originally designed for MySQL and PostgreSQL. If you want to make an ODBC connection you must first setup an ODBC data source (in your ODBC installation properties). Use the name of the data source as the database name in the dialog. The other useful fields for ODBC are user name and password. The host and port field is not used for ODBC.

Transaction Support

Transaction support should be considered incomplete in DbTalk 0.72. Please use it with caution.

It is possible to toggle autocommit status for the session using a menu command or a toolbar button. The initial autocommit status is checked during connection opening. (At the moment this is not true for MySQL because I do not know how to find the autocommit status out with an SQL command. For MySQL it is simply supposed that the autocommit is on when a session begins.) The toolbar button indicates current status with labels. 'Auto' means the autocommit is on. 'Man' means the autocommit is off.

There are also menu commands and toolbar buttons for commit and rollback. These just send the appropriate SQL command to the backend.

SQL Parsing

DbTalk does not contain a full featured SQL parser. Instead it uses a few regular expressions to find boundaries of the current SQL query. If you want to know what exactly is sent to the server use Ctl-= for selecting the current query. The selected string is stripped of blanks and also the trailing semicolon is removed before sending to the server. SQL comments are left untouched which may cause problems with some databases (for example MS Access).

Syntax Highlighting

Syntax highlighting is also based on the regular expression matching. It recognizes three categories of highlighted elements: literals (text or number constants), comments (from leading '--' to the end of line) and keywords. Keywords are defined in the source code at the moment. If you know Ruby you can easily find the appropriate part in dbtalk.rb. Keywords can have one of three possible colors associated with them. The next version of DbTalk may have syntax highlighting definitions in a separate file for easier customizing.

Text Completion

The list of possible completions is created by combining SQL keywords and the database element names shown at the database tree. One of these groups can be switched off in the completion dialog. The setting is remembered. If there are many suggestions in the completion list you can enter a regular expression which limits the display. The list widget itself supports limited search. If you press a key the current item in the list is set to the first word beginning with the key.

Result Table Color

The Fox toolkit table widget use to have (IMO) a nice colorized background. However the default is white in newer versions. You can use the item in the options menu to set the original colors for the table.

ToDo

DbTalk is still in its development stage and has many limitations. I plan to add at least these features in the future (listed in random order):

• better ODBC support (large DBMS, schema support etc.)
• interruptable query execution
• support for more result sets
• editable result table with automatic database updates
• better database structure browser and perhaps editor
• wizards for some common operations (create table etc.)
• useful toolbar
• built-in help
• user friendly installator

Requirements

DbTalk is written in Ruby and uses FOX graphical toolkit wrapped in FXRuby. DbTalk loads libraries for MySQL and/or PostgreSQL and/or ODBC access if they are present. ODBC and MySQL libraries exist for both UNIX and Windows. As far as I know the Ruby PostgreSQL library exists only for UNIX.

When running DbTalk press F1 to see your versions of Ruby and Fox. In the log window you can find out what databases are supported (e.g. DbTalk could read the appropriate libraries).

DbTalk was tested with the following versions of Ruby/FXRuby:

• FreeBSD, Ruby 1.6.8, FXRuby 1.0.20
• Windows 2000, Ruby 1.6.8, FXRuby 1.0.24
• Windows 2000, Ruby 1.8.0, FXRuby 1.0.25

UNIX

The primary development platform for DbTalk is FreeBSD and it is also well tested on Linux. It should be possible to run DbTalk on any other modern UNIX flavor. However there is no universal description how to set things up. The table bellow overviews DbTalk dependencies.

The installation can be divided into several independent steps:

• Ruby interpreter
• FOX and FXRuby (at this point DbTalk should run but will not be able to connect to any database)
• database libraries (grey cells in the table)
• Ruby database bindings (pink cells in the table)

Depending on your preferences and skills you can compile from sources or you can use precompiled packages available for some systems.

Windows

If you install the all-in-one package from Pragmatic Programmers you should have all you need to run DbTalk and connect to ODBC and MySQL databases. It is possible to install a newer version of FXRuby over this installation (which brings you also a recent version of FOX).

The following table shows detailed application and library structure for running DbTalk on Windows. Note that components included in the Pragmatic Programmers installer have the pink background. The ODBC drivers are either a part of MS Windows or you have to install them individually.

Download and Installation

DbTalk package contains following files and folders:

• html - directory containing this page.
• LICENSE - General Public License
• dbtalk.rb - executable script.
• dbtalk2mysql.rb - module for MySQL access.
• dbtalk2postgres.rb - module for PostgreSQL access.
• dbtalk2odbc.rb - module for ODBC.
• toybase.sql - sample SQL script for setting up and querying the little database.

At the moment there is no installator. Just unzip the package wherever you want to launch DbTalk from. You can run DbTalk from command line using ruby dbtalk.rb (assuming the Ruby interpreter is in your path). If you have .rb files associated with Ruby on Windows you can start DbTalk by double clicking on dbtalk.rb. I would recommend renaming dbtalk.rb to dbtalk.rbw (the 'w' at the end) which will suppress the console window. On UNIX you can strip the .rb extension and run chmod +x dbtalk. This allows you to start the program typing just dbtalk.

The last release version dbtalk073.zip (136kB) is obsolete.

Currently usable version is dbtalk075_050314.zip (29kB)

If you need the older version you can download dbtalk072.zip (135kB) or dbtalk071.zip (132kB) or dbtalk07.zip (131kB) or dbtalk06.zip (95kB) or dbtalk05.zip (89kB) or dbtalk04.zip (51kB).

Known Issues

• DbTalk is insufficiently tested. I have access to only limited number of database servers and operating systems.
• From time to time a new version of FXRuby or Fox is slightly backward incompatible. This can cause strange errors while running DbTalk.
• The ODBC part lacks some features for working with large scale DBMS (Oracle...). Improving this becomes the priority for the next version.
• Syntax highlighting is not perfect in recognizing patterns. Especially procedure definitions make problems with double '.
• There is no built-in help.
• The stderr is captured on UNIX because of catching notifications from the PostgreSQL client library. In a (hopefully unlikely) case of DbTalk crash this also prevents Ruby exception messages from appearing.

Contact

Your feedback is welcome. Mail me to dali@insula.cz