Installation (Linux)

The server program is just one single executable binary file. There are no external dependencies. The client program is also just one single executable binary file.

The installation procedure is quite straightforward, and you can customize it at your convenience.

The $ shell prompt assumes that you are working under your personal account, or the instance account.
The # shell prompt assumes that you are working as root. Alternatively, you can prefix each command with the sudo command.

If You Want to Quickly Try the Program

For demonstration purpose, you can quickly install the program in your home directory. Just do the following:

Then, open a terminal and in your home directory, type the following commands:

$ tar -zxvf rsql-VERSION-OS.tgz
$ mv rsql/bin/rsql_server-VERSION-OS rsql_server
$ mv rsql/bin/rcli-VERSION-OS rcli
$ chmod +x rsql_server
$ chmod +x rcli

$ tar -zxvf rsql_mytestdb_scripts.tgz

Create an instance directory and initialize it (replace /your/home_directory by the absolute path to your home directory).
You must initialize (-install flag) the instance directory only once.
Note that instead of rsql1, you can choose any other name for the instance directory.

$ mkdir rsql1
$ ./rsql_server -install -dir=/your/home_directory/rsql1 -server_default_collation=en_ci_ai -server_default_language=en_us

You can now run the server:

$ ./rsql_server -dir=/your/home_directory/rsql1

The server is now waiting for connections.

To stop the server, just type ctrl-c. The server stops abruptly (no corruption will occur as the server has journal files).

To restart it, just type again:

$ ./rsql_server -dir=/your/home_directory/rsql1

Open a new terminal, and send a query with the client program (at installation, the default password for sa is changeme):

$ ./rcli -U=sa -P=changeme -Q="print 'Hello';"

To avoid typing the login and password, create a client configuration file in your home directory:

$ ./rcli -config_model > ~/rcli.conf

You can now just type:

$ ./rcli -Q="print 'Hello again';"

Create a new database:

$ ./rcli -Q="create database mytest"

You can send SQL scripts by just passing the file name as argument:

$ ./rcli my_batch_file.sql

For example, you can run the sample SQL scripts in the rsql_mytestdb_scripts directory:

$ ./rcli rsql_mytestdb_scripts/mytest_country_create.sql
$ ./rcli rsql_mytestdb_scripts/mytest_country_fill.sql
$ ./rcli -Q="select * from mytest..country"

More info on rcli flags:

$ ./rcli -h

In particular, the -width= flag is useful to display VARCHAR columns of SELECT statement in larger columns if strings are truncated (default width is 30).

Here is the info page on RSQL client rcli.

To continue, please read these documents:

If you want to make a serious installation, with the server running as a daemon, you should follow the whole procedure below.

Installation of Server and Client Executable Binaries

Go to the Download page.

Download the latest available version of RSQL Server rsql-VERSION-OS.tgz into your home directory or anywhere else.

This tar file contains the server and the client executable:

As root, create the group rsql and decompress the archive into the directory /opt ( or /usr/local).

Only members of the group rsql have the permission to run rsql server.

$ su -

# groupadd rsql
# cd /opt
# tar -zxvf /your/home_directory/rsql-VERSION-OS.tgz
# cd rsql
# chown -R root:rsql .
# chmod -R 755 .
# chmod 754 bin/rsql_server-VERSION-OS
# ln -s /opt/rsql/bin/rcli-VERSION-OS /usr/local/bin/rcli

Check the server version:

# /opt/rsql/bin/rsql_server-VERSION-OS -version
rsql server: version 0.6

Create an Instance Account

Create a new OS user, which will own the database files and run the server executable for the instance.

We create the account rsql1, but you can choose a more descriptive name. In particular, if you run many instances of rsql server on the same machine, you should create a specific account (e.g. rsql_accounting, rsql_inventory or any other name) for each instance.

# groupadd rsql1
# useradd -r -m -g rsql1 rsql1
# usermod -a -G rsql rsql1
# passwd rsql1

and enter a password for the user rsql1.

The useradd command created a system account (-r) with a home directory (-m). This user is also member of the group rsql, so that it has permission to execute the server.

Create the Instance Base Directory

This directory will contain all the database files.

Only the instance has the permission to write in this base directory.

This directory can be created in any location, e.g. in /var/opt.

It is sensible to have the directory name be the same as the instance account.

# cd /var/opt
# mkdir rsql1
# cd rsql1
# chown rsql1:rsql1 .
# chmod 755 .
# exit

Initialize the Instance Base Directory

Now, we must initialize this directory.

The command rsql_server-VERSION-OS -install will create several directories under the base directory, and initialize the master data dictionary (master.db file) containing the description of all instance objects (server parameters, logins, users, databases, tables, etc).

The following options are mandatory for installation:

The following command will initialize the instance base directory with english collation and language.

Switch to instance user rsql1 and run the installation command:

$ su - rsql1
$ /opt/rsql/bin/rsql_server-VERSION-OS -install -dir=/var/opt/rsql1 -server_default_collation=en_ci_ai -server_default_language=en_us

To see the directory tree, type:

$ cd /var/opt/rsql1
$ tree -pug

Run RSQL Server

Now, we run the server in a terminal. Later, we will run it as a daemon.

As instance user rsql1, type the following command:

$ /opt/rsql/bin/rsql_server-VERSION-OS -dir=/var/opt/rsql1

The server displays some startup information and waits, listening for requests.

By default, the server is listening on the address localhost:7777, which means that only clients running on the same machine can access the server.

If you want to stop it abruptly, just type Ctrl-C.

All database modifications are written in a write-ahead journal, so database corruption won’t happen even if the server stops in the middle of an active transaction.

YOU SHOULD RUN ONLY ONE rsql_server PROCESS ON A SPECIFIC INSTANCE DIRECTORY. ELSE, DATABASE CORRUPTION WILL OCCUR !

Client

The RSQL Client rcli is a tool that sends batches to the server, and displays the result on the terminal.

Open a new terminal and log in as your personal account.

$ rcli -U=sa -P=changeme -Q="print 'Hello';"

This command should print Hello on your terminal.

The default password for login sa after installation is changeme.

We will change it now. Replace the new_password_here argument by the password of your choice:

$ rcli -U=sa -P=changeme -Q="ALTER LOGIN sa WITH PASSWORD='new_password_here';"

To avoid writing the login and password each time, we can put them in the configuration file of the rcli client.

To create a model config file in your home directory, type the following commands:

rcli -config_model > ~/rcli.conf
chmod 600 ~/rcli.conf

Only the owner has the permission to read and modify this file, because it contains a login and password.

Open this file and change the password parameter with the new password. Now, you should be able to run the following command without the user and password options:

$ rcli -Q="print 'Hello again';"

If you have troubles, use the -v option to check if the correct config file has been loaded.

$ rcli -U=sa -P=new_password_here -v -Q="print 'Hello again';"

It is often more convenient to write SQL statements in a file.
Just pass the file name (e.g. myfile.sql) as argument to RSQL Client rcli:

$ rcli myfile.sql

Server Listening Address

The server listens for incoming connections at the address stored in the file listener_address.cfg located in the base directory.
This file is read at startup.

To change the listening address, just change the content of this file and restart the server.

Setting the Global Cache Size

Internally, RSQL works only with pages that are loaded in one of the caches:

The size of the global cache should be a small portion of the available RAM

10% or less is good. If your RAM is huge, you can even try with a much smaller size.

At installation, the default global cache size is 100 MB, which is good enough for most medium-size databases.

When a page is ejected from this global cache, it remains for a while in the Linux system cache.

So, if the size of the global cache is defined too large, a page will be kept in memory twice (in the global page cache and in the Linux system cache), which is bad for performance.

To change the global cache size, use the statement ALTER SERVER PARAMETER.

The parameter SERVER_GLOBAL_PAGE_CACHE_MEMORY sets the size of the global cache, in MB.

E.g. to set the global cache to a size of 200 MB:

ALTER SERVER PARAMETER SET SERVER_GLOBAL_PAGE_CACHE_MEMORY = 200;

This parameter takes effect after server restart.

Logging Files

The server writes information messages during startup, shutdown and on other occasions.

By default, the server writes these messages to the standard output.

If the flag -logpath=logging_file is passed, the messages are written to the specified logging_file.

For example:

$ /opt/rsql/bin/rsql_server-VERSION-OS -dir=/var/opt/rsql1 -logpath=rsql1.log

To avoid the logging file to grow indefinitely, it is possible to rotate it:

The server always write messages in the current logging file, and the old logging files just accumulate in the directory.

The parameters SERVER_LOGGING_MAX_SIZE, SERVER_LOGGING_MAX_COUNT, SERVER_LOGGING_LOCALTIME control the rotation of the logging file. They can be modified by the statement ALTER SERVER PARAMETER.

SERVER_LOGGER_MAX_SIZE = logger_max_size
when the size of the logging file reaches this value in MB, the rotation occurs.
  • If 0, the logging file grows indefinitely.
  • By default, it is 5 (MB).
SERVER_LOGGER_MAX_COUNT = logger_max_count
specifies the number of old logging files to keep.
  • The logging files in excess are automatically deleted.
  • If 0, old logging files are never deleted.
  • By default, it is 10.
SERVER_LOGGER_LOCALTIME = logger_localtime
specifies if the timestamp appended to the logging file name when rotated is in local time or UTC.
  • By default, it is in local time.

These parameters take effect after server restart.

Accessing Files in bulkdir and dumpdir

These directories have been created during the initialization of the instance base directory.

If you make your personal OS account member of the instance group rsql1, you will have access to all the files of the instance. This is not recommended.

It is safer to have read-write permission only on the files in the bulkdir and dumpdir directories when working with your personal account.

To accomplish this, you can do the following:

As root, type the following commands (instead of rsql1_file_access, you can choose another name):

# groupadd rsql1_file_access
# chgrp rsql1_file_access /var/opt/rsql1/bulkdir
# chmod g+s /var/opt/rsql1/bulkdir
# chgrp rsql1_file_access /var/opt/rsql1/dumpdir
# chmod g+s /var/opt/rsql1/dumpdir

# usermod -a -G rsql1_file_access john    <-- replace john by your own account

Now, you have read-write access on the files in bulkdir and dumpdir.

This change will take effect only when you log in again, because the group membership of your account is not refreshed. You can do this by restarting your computer, or by typing su -l john, which opens a new login shell.

Run RSQL Server as a Daemon with Systemd

If your distribution is running systemd, you can create a configuration file (called a unit file) for the service.

This unit file defines how systemd will handle this service.

The file name should be the name of the instance with the .service suffix. In our case, it is rsql1.service.

Copy this file in /etc/systemd/system (or /lib/systemd/system, or another directory used by systemd).

[Unit]
Description=RSQL instance rsql1
After=network.target

[Service]
User=rsql1
Group=rsql1
ExecStart=/opt/rsql/bin/rsql_server-VERSION-OS -logging_no_date -dir=/var/opt/rsql1
TimeoutStopSec=30

[Install]
WantedBy=multi-user.target

Don’t forget to change the VERSION-OS part in the ExecStart option with the actual version and OS.

The -logging_no_date option doesn’t put the date and time in the message, because systemd logging already puts a timestamp on each log record.

To display the last 20 lines of the systemd journal:

journalctl -n20

If you prefer to log to a file instead of systemd journal, replace the ExecStart line by e.g.:

ExecStart=/opt/rsql/bin/rsql_server-VERSION-OS -logpath=/var/log/rsql1/rsql1.log -dir=/var/opt/rsql1

And create the specified directory as root:

cd /var/log
mkdir rsql1
chown rsql1:rsql1 rsql1

After a unit file has been created or modified, you must always run the command systemctl daemon-reload, to make systemd aware of the changes. Then, enable the service to make it run at boot:

systemctl daemon-reload
systemctl enable rsql1.service

The rsql1 instance will start when the machine reboots.

To start it now, type:

systemctl start rsql1.service

To display its status:

systemctl status rsql1.service

To stop it:

systemctl stop rsql1.service

If the server has not terminated after TimeoutStopSec seconds specified in the unit file, systemd kills it by sending a SIGKILL signal.

Each instance running on your machine should have its own unit file, and listen on a different port. E.g. for the instance rsql2:

[Unit]
Description=RSQL instance rsql2
After=network.target

[Service]
User=rsql2
Group=rsql2
ExecStart=/opt/rsql/bin/rsql_server-VERSION-OS -logging_no_date -dir=/var/opt/rsql2
TimeoutStopSec=30

[Install]
WantedBy=multi-user.target

with listener_address.cfg file:

localhost:7778

or, if you want the server to be accessible from another machine:

0.0.0.0:7778
RSQL, a simple alternative to Microsoft SQL Server