Getting Started
Features
Source code:
- RSQL is written in Go, which is a compiled language.
- The source code of RSQL has 140,000 lines of code.
RSQL Server is a SQL database server.
- It implements a subset of the TSQL dialect (TSQL is the SQL script language of Microsoft SQL Server).
Transactions are supported.
Each table and index is stored in its own file.
A Write-Ahead journal logs all the modifications, which are then applied to the table files.
- Even if the server crashes in the middle of a transaction, no database corruption occurs and the transaction is rolled back.
When a batch starts:
- Locks are acquired on all tables referenced by the batch.
- Locks are shared read locks or exlusive write locks.
- Locks are put on tables and databases.
When a batch terminates:
- If no transaction is open, all locks are released.
- If an explicit transaction is still open, all locks are kept until a subsequent batch executes a
COMMIT TRANSACTION
orROLLBACK TRANSACTION
statement.
When a session (=connection) terminates:
- If an explicit transaction is still open, it is rolled back and all locks are released.
Installation
Install RSQL Server and Client as decribed here.
General Information
Logins:
- A login and its password form the credential that is used to obtain a connection to RSQL Server.
- After installation, you can connect only with the sa login.
- sa is the administrator account. It bypasses all permissions and has all privileges on the server and databases.
- Only sa can change the parameters of the server, and create or drop logins or databases.
- The statement
CREATE LOGIN
creates new logins.
Databases:
- After installation, the trashdb database has been created, in read-only mode.
- The trashdb database exists only to have a default database for sa to connect to, but you should not use it.
- The statement
CREATE DATABASE
creates new databases. - On the top of the script or batch, put
USE
to choose the default database. - The default database can also be specified with
ALTER LOGIN
. - By default, a new database is owned by the user dbo, which underlying login is sa.
- The owner of a database has all permissions on all objects in the database.
- To change the owner of a database, use
ALTER AUTHORIZATION
. - When a database is created, the special user dbo and the special role public are also created.
Schemas:
- A database has only one schema: dbo.
- It is not possible to create other schemas, as there is no
CREATE SCHEMA
statement. - The name dbo of the schema has no relation with the dbo user. Schema and user are different concepts.
- It is not possible to create other schemas, as there is no
Users and Roles:
- Inside a database, all the work is done as a user, provided it has the necessary permissions.
- By default, a new user has no permission on any database object (see
GRANT permissions
). - The dbo user has always all permissions on all objects in the database it owns.
- A user has always an underlying login.
- A normal user cannot have sa as underlying login.
- Only the dbo user can have sa as login. To change the login of dbo, use
ALTER AUTHORIZATION
. - Roles can be granted permissions, and can contain users and roles as members.
- The statement
CREATE USER
creates new users. - The statement
CREATE ROLE
creates new roles, andALTER ROLE
manages members. - All users are implicitly members of the role public.
Tables:
- The statement
CREATE TABLE
creates new tables.
Information:
- The statement
SHOW
displays all information about all objects.
Scripts
When using the RSQL Client rcli
, you can pass a script file as argument.
- A script file can contain many batches, separated by
GO
,EXIT
orQUIT
command. - The batches in a script are sent one after another to the server.
- Usually, if an error is encountered, the current batch is terminated but the subsequent batches will be sent.
- However, if a syntax error is encountered, the script terminates immediately and subsequent batches are not sent.
A batch is sent as a whole to the server, where it will be parsed, compiled and executed.
- Identifiers are case insensitive. They can be delimited between brackets
[...]
or double-quotes"..."
. - Statements should be terminated by a semi-colon
;
, but it is not mandatory.
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 |