The Sclera command line shell provides an interactive interface to manage and analyze your data, and also explore the Sclera metadata. The command-line is a lightweight alternative to the Sclera Visual Shell and does not support the Sclera Visualization language extensions.
The shell can be started by executing the script
$SCLERA_HOME is the directory where Sclera is installed.
This section lists the commands that are accepted by the shell. The commands can be given on the command line, and executed interactively. Or, they can be put in a script and executed using the
The commands can span multiple lines, and are terminated by a semicolon (
In the following, the keywords appear in upper case to distinguish them from the other terms; however, Sclera is case-insensitive and keywords in actual commands and queries can be in upper or lower case.
This set of commands enable you to manage the configuration parameters.
The following command sets the
DEFAULT location to a predefined location with name specified in
SET DEFAULT LOCATION = location_name;
Similarly, the following command sets the
CACHE location to a predefined location with name specified in
SET CACHE LOCATION = location_name;
In either case, the
location_name must be defined earlier using the
ADD LOCATION command.
The update is for the current shell session only. It does not affect the locations used in a concurrent session, or when Sclera is accessed through
JDBC. To persistently change the locations, please update the
sclera.location.default configuration parameters.
The queries and commands on the command line are translated by Sclera into a sequence of subqueries and subcommands that execute on the underlying database systems. The
EXPLAIN SCRIPT command enables you to activate or decativate the display of these commands on the console as they are executed.
The following command activates this runtime explain feature:
EXPLAIN SCRIPT [ ON ];
ON at the end is a syntactic sugar, and can be omitted.
The following command deactivates this runtime explain feature:
EXPLAIN SCRIPT OFF;
The following command shows the current default location, cache location and runtime explain settings:
The following command shows the current configuration settings:
The following command shows the current logging configuration:
Commands to manage (add and remove) data sources and underlying tables are covered in the Sclera Database System Connection Reference document.These commands can be submitted on the command line prompt.
In addition, the following command creates the metadata (aka schema) tables on the designated metadata store:
This is needed, for instance, if you want to change the location of the metadata store.
Also, the following command deletes the tables in the designated metadata store:
You can explore Sclera’s metadata (the locations, tables, views, and other objects) using the
LIST [ list_spec ]; DESCRIBE [ list_spec ];
The two commands are identical, except that
LIST outputs a list of objects obtained using
list_spec (see below) in a short format, whereas
DESCRIBE outputs the same list of objects in a more detailed, descriptive, format.
The following table presents the possible values of the optional parameter
list_spec and the associated list of objects to be output.
|(not specified)||All objects (tables across all locations, views, classifiers, clusterers and associators)|
||All tables in location
||All tables in location
||Table with name
||All tables across all locations|
||All tables with name
||View with name
||Classifier with name
||Clusterer with name
||Associator with name
ScleraSQL queries and commands accepted by Sclera are discussed in the ScleraSQL Reference document. These can be submitted on the command line prompt. Commands are executed silently, while returned query results are displayed in a table format.
In addition, the shell has an
EXPLAIN command that explains a query is executed by Sclera. The
EXPLAIN command comes in two variants.
The first variant, called runtime explain has been discussed above. This command, called
EXPLAIN SCRIPT, causes each ScleraSQL command or query to display the commands it executes on the underlying database systems.
This variant of
EXPLAIN is useful when you want to trace what is happening under the hood while a query or command is executing. We refer to the earlier discussion for the details.
The second variant, called the compile-time explain, has the following syntax:
table_expression is parsed and optimized by the query processor, and the resulting plan is then displayed on the console. Note that unlike the
EXPLAIN SCRIPT, the query is not executed; the output shows how Sclera plans to execute the query if given without the
This variant is useful when you want to explore how a query will be evaluated by Sclera, without actually evaluating the same.
This command enables you to execute a script of commands from a file. The syntax is:
script_file_path is the full path of the command script to be executed.
The file is read, and each command therein is executed in sequence; the output of the command, if any, is displayed on the console as the command is executed.
The shell maintains a command history, at a location given by the
sclera.shell.history configuration parameter. You can navigate the history using the up/down keys.
You can complete words by pressing tab. The word completion alternatives presented are not context sensitive, but that may change in future versions.
To reset all connections and recover to a clean state, issue the following command:
This is equivalent to exiting and restarting the shell.
A line with two consecutive hyphens (
"--") as the first non-whitespace characters is considered a comment, and is ignored. Unlike standard SQL and PostgreSQL, comments cannot start midway in a line, after a valid input.
Comments are only permitted in the shell, and in scripts input to the shell.
Starting with Sclera 2.2, the shell can be used to start a TCP server that implements the PostgreSQL backend protocol 3.0, which is compatible with PostgreSQL 7.4+.
Details on how to start/stop this server and configure the connections appear in a separate document.
Starting with Sclera 2.2, the shell can be used to add a password, and to create and remove users. This is discussed in a separate document.