SQLShell: SQL tool for multiple databases with NoSQL potential

SQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.

WHY USE IT?

If you already only use one command-line tool such as psql for PostgreSQL or the MySQL command-line tool for MySQL and you don’t need it. If you find yourself jumping between several of these tools and want to use common functionality, or you’re a fan of many NoSQL technologies, it’s worth watching for.

JDBC DRIVER SUPPORT

SQLShell is built into Scala. Scala is a scalable programming language that compiles into Java byte code, can run on JVM, and can be used together with Java code. SQLShell can therefore interface with any database that has a JDBC (Java Database Connectivity), driver. Encase you do not know, there are many of them.

JDBC drivers are available

• DBMS Databases
• oracle
• MySQL
• PostgreSQL
• SQLite
  There are several JDBC drivers for many NoSQL technologies, but none of them yet implements JDBC fully enough for SQLShell.
• HBase
• MongoDB
• beehive
• CouchDB
• Redis

What should be noted in the recent release of Google BigQuery (part of Google Storage) is that, although they do not have a JDBC driver, they still use sqlcmd with BigQuery internally. Sqlcmd is also created by Brian M. Clapper on Clapper.org but he stopped development in favor of the new SQLShell.

However, Google likes Python and sqlcmd is written in Python.

SQLSHELL INSTALLATION

You can either download the pre-compiled JAR installation file for SQLShell or compile the binary yourself. It is recommended to download the JAR and I downloaded version 0.7.1.

curl -O http://cloud.github.com/downloads/bmc/sqlshell/sqlshell-0.7.1-install.jar

This installer can be started with the java command that launches the graphical installer. The graphical installer uses IzPack, which is a cross-platform installation environment. Therefore, although I am using Mac OS X, you should not have any problems installing it on Windows or Linux.

java -jar sqlshell-0.7.1-install.jar

The first thing you see if everything goes well is a tip from the language. After that you will see the welcome screen, then the entry and information page, and then the license page. Be sure to read the license carefully, especially the part about “you are firstborn”.

This is what you will see on the screen saver.

Initial SQLShell installer screen

When you reach the end, you will see the following message …

You have successfully installed SQLShell. For your convenience in
The “bin” directory in the “/Applications/clapper.org/sqlshell” directory has a shell script.

This tells you where sqlshell is installed. Under this directory is the bin directory which contains sqlshell. This is a command-line tool and since we will use it a lot I will add the path to this bin directory in my PATH.

echo 'export PATH=$PATH:/Applications/clapper.org/sqlshell/bin' >> ~/.profile
source ~/.profile
which sqlshell || echo "Not found in path" # This tests that it's found in your PATH

We can see what parameters he expects by running sqlshell with the -help argument.

sqlshell --help

SQLShell, version 0.7.1 (2010/11/10 17:27:55)

Usage: sqlshell [OPTIONS] db [@file]

OPTIONS

-?
-h
--help Show this usage message.

-V
--version Show version and exit.

-c config_file
--config config_file Specify configuration file. Defaults to:
/Users/phil/.sqlshell/config

-n
--no-ansi
--noansi Disable the use of ANSI terminal sequences. This option
just sets the initial value for this setting. The value
can be changed later from within SQLShell itself.

-r lib_name
--readline lib_name Specify readline libraries to use. Legal values:
editline, getline, gnu, jline, simple. (May be specified
multiple times.)

-s
-stack Show all exception stack traces.

-v
--verbose Enable various verbose messages. This option just sets
the initial verbosity value. The value can be changed
later from within SQLShell itself.

PARAMETERS

db Name of database to which to connect, or an on-the-fly database
The form:

driver,url,[user[,password]].

If the name of a database is specified, SQLShellwill look in the
configuration file for the corresponding connection. If a
database specification is used, the specification must be one argument. The
the driver can be a full driver class name, or a driver alias from the
configuration file. The user and password are optional, since some
databases (like SQLite) don't require them at all.

@file Path of file of commands to run

SQLSHELL WITH MYSQL
INSTALL THE JDBC MYSQL DRIVER

Download the driver from MySQL

curl -O <a href="http://www.mysql.com/downloads/connector/j/">http://mysql.mirror.iweb.ca/Downloads/Connector-J/mysql-connector-java-5.1.14.tar.gz</a>.
tar xvzf mysql-connector-java-5.1.14.tar.gz
cd mysql-connector-java-5.1.14
cp mysql-connector-java-5.1.14-bin.jar /Applications/clapper.org/sqlshell/lib/

Above we copied MySQL connector-Java-5.1.14-bin.jar file SQLShell to Lib. SQLShell will download all JAR files found in this directory at startup and then reference them by package name and class. We will use the MySQL driver class name to configure the alias “mysql” in SQLShell configuration.

To configure the alias “mysql” we will edit the default configuration file ~ / .sqlshell / config which does not exist at the moment.

mkdir ~/.sqlshell
vim ~/.sqlshell/config # I use vim to edit files

Add the following configuration…

[drivers]
mysql = com.mysql.jdbc.Driver

CONNECT TO MYSQL WITH SQLSHELL

The format of the connection string for the MySQL JDBC driver should be

jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

I’m just going to connect to a local mysql database and look at a pre-installed MySQL database called “test”. You should have the same database if you haven’t deleted it.

sqlshell mysql,jdbc:mysql://localhost/test?user=root
SQLShell, version 0.7.1 (2010/11/10 17:27:55)
Copyright (c) 2009-2010 Brian M. Clapper
Using JLine
Type "help" for help. Type ".about" for more information.

sqlshell>

Okay, we are connected to MySQL and can run multiple queries.

We can see the commands that we can run using help.

sqlshell> help
Help is for the following commands:
-------------------------------------------------------------------------------
.about .capture .desc .echo .run .set .show
alter begin commit create delete drop exit
help history insert r rollback select update

Let’s take a look at what databases we have …

sqlshell> show databases;
Execution time: 0.26 seconds
Retrieval time: 0.21 seconds
2 rows returned.

SCHEMA_NAME
------------------
information_schema
test

sqlshell>
We can create a new database.

sqlshell> create database sqlshell_test;
1 row affected.
Execution time: 0.5 seconds

SQLShell completely transfers SQL statements to the database, so you can use any commands that your database understands.