Guide

Reference

Misc

Getting Started

Features

Installation

Install RSQL Server and Client as decribed here.

General Information

Logins:

Databases:

Schemas:

Users and Roles:

Tables:

Information:

Scripts

When using the RSQL Client rcli, you can pass a script file as argument.

A batch is sent as a whole to the server, where it will be parsed, compiled and executed.

See also

Usage

In this tutorial, you will connect to RSQL Server as sa, as explained in the installation prodedure.

Copy-paste each batch of this tutorial in an example.sql file in your home directory, and send it to the server by typing:

rcli example.sql

To increase the column width for SELECT results, use the -width= option:

rcli -width=50 example.sql

Show Information

The SHOW statement displays all the information you need on the parameters, databases, tables, users, etc of the current instance and current database.

SHOW I;
SHOW P;

Information about the session and the server parameters are printed on the terminal:

INFO for worker id 0
--------------------------------------------------
server name:               jupiter/rsql1
host:                      jupiter
base directory:            /var/opt/rsql1
version:                   0.6
server default collation:  en_ci_ai
login:                     sa
current user:              dbo
current database:          trashdb   (Read only)
current language:          en-US, first day of week:0, MDY

PARAMETER                                 VALUE
----------------------------------------  ------------------------
server_default_collation                  [en_ci_ai]
server_servername                         'jupiter/rsql1'
server_workers_max                        30
server_global_page_cache_memory           30000000
server_default_database                   [trashdb]
server_default_language                   [en-us]
server_quoted_identifier                  ON
server_listener_address                   'localhost:7777'
server_bulk_dir                           'bulkdir'
server_dump_dir                           'dumpdir'
server_keepalive_ticker_interval          30000
server_read_timeout                       30000
server_lock_ticker_interval               1000
server_lock_timeout_ticks_count           30
server_logfiles_max_size                  5
server_logfiles_max_count                 10
server_logfiles_localtime                 TRUE

Create a New Database

CREATE DATABASE mydb;
SHOW D;

The list of databases in the instance are printed.

DATABASE                   OWNER LOGIN NAME           INFO
-------------------------  -------------------------  ----------
trashdb (*)                sa                         (Read only)
mydb                       sa

Create a Table in mydb

USE mydb;

CREATE TABLE t(id INT NOT NULL IDENTITY, name VARCHAR(20));
SHOW T;

The statement USE mydb changes the current database to mydb. Else, the table would be created in trashdb, which is the default database for sa.

The list of the tables in the current database are printed.

TABLE                                             
--------------------------------------------------
mydb.dbo.t

Display Table Structure

To display the structure of a table, pass the first characters of a table name as argument to SHOW:

USE mydb;

SHOW T t;            -- displays SQL for all tables which name starts with 't' in current database.

SHOW T mydb.dbo.t;   -- it is possible to specify the database and schema in the SHOW T command.

SHOW T mydb..t;      -- same. The default schema is always dbo.

In fact, SHOW T t is a shortcut for SHOW SQL TABLE t.

The result is:

CREATE TABLE [dbo].[t] (
    [id]                  INT                  IDENTITY(1, 1) NOT NULL,
    [name]                VARCHAR(20)          NULL,
    -- CONSTRAINT [$sysidx$0] UNIQUE CLUSTERED ([rowid])
);

NOTE: if you search for an exact match, append a hyphen - after the name, as in the example below:

SHOW T customers;   -- displays SQL for all tables which name starts with 'customers' in current database.

SHOW T cust;        -- displays SQL for all tables which name starts with 'cust' in current database.

SHOW T c;           -- displays SQL for all tables which name starts with 'c' in current database.

SHOW T mydb..cust;  -- displays SQL for all tables which name starts with 'cust' in mydb database.

SHOW T customers-;  -- exact match: displays SQL for the 'customers' table in current database.

Insert some Records

USE mydb;

INSERT INTO t VALUES ('Fred'), ('Maria'), ('Omer');

SELECT * FROM t;

The result is:

id         |name           |
-----------+---------------+
          1|Fred           |
          2|Maria          |
          3|Omer           |

Insert more Records

SET NOCOUNT ON;

DECLARE @i INT = 0;

BEGIN TRAN;

WHILE @i<10
    BEGIN
    INSERT t(name) VALUES ('Row_' + CAST(@i AS VARCHAR));
    SET @i += 1;
    END

COMMIT;
SET NOCOUNT OFF;

SELECT * FROM t;

The result is:

id         |name           |
-----------+---------------+
          1|Fred           |
          2|Maria          |
          3|Omer           |
          4|Row_0          |
          5|Row_1          |
          6|Row_2          |
          7|Row_3          |
          8|Row_4          |
          9|Row_5          |
         10|Row_6          |
         11|Row_7          |
         12|Row_8          |
         13|Row_9          |

Drop the Table

USE mydb;

DROP TABLE t;

Drop the Database

DROP DATABASE mydb;

Fill tables with large number of random data for test

When you develop an application, you should always test your queries against tables containing large number of records, mimicking your real data.

That way, you can check the performances of your queries on real size data.

RSQL allows you to easily fill your tables with random data.

Use the convenience functions RANDOM_VARCHAR,RANDOM_NUMERIC, RANDOM_INT, RANDOM_DATE, etc.

SET NOCOUNT ON;

TRUNCATE TABLE t;

DECLARE @i INT = 0;

BEGIN TRAN;

WHILE @i<10
    BEGIN
    INSERT t(name) VALUES (RANDOM_VARCHAR(5,10));
    SET @i += 1;
    END

COMMIT;
SET NOCOUNT OFF;

SELECT * FROM t;

The result is:

id         |name           |
-----------+---------------+
          1|Ilaruaqac      |
          2|Aznese         |
          3|Kheojisi       |
          4|Aotungona      |
          5|Finoocape      |
          6|Oxuhume        |
          7|Ixejeemo       |
          8|Ogiedu         |
          9|Epomaaw        |
         10|Sezanex        |
RSQL, a simple alternative to Microsoft SQL Server