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 asroot
. Alternatively, you can prefix each command with thesudo
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:
- Go to the Download page
- Download the latest available version of RSQL Server
rsql-VERSION-OS.tgz
into your home directory. - Optionally, download the SQL scripts
rsql_mytestdb_scripts.tgz
into your home directory, for creating tables in themytest database
.
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:
- read the
Getting Started
page. - read the
mytest Database
page to install themytest
database for practicing.
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:
rsql/bin/rsql_server-VERSION-OS
rsql/bin/rcli-VERSION-OS
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
-install
option. - The
-dir
option specifies the absolute path of the instance base directory. The
-server_default_collation
specifies the default collation.- This option cannot be changed later. So take some time to choose it wisely.
- The collation defines a set of rules that determine how
varchar
andchar
data are sorted and compared. - If you work in english, use
en_ci_ai
. If you work in another language, e.g. in french, usefr_ci_ai
. - Read the documentation about collations, which also lists all available collations.
The
-server_default_language
specifies the default language for theCREATE LOGIN
statement.- The language contains information like localized name of days of the week, name of months, default date formats, decimal point symbol, etc.
- If you work in english, use
en_us
. If you work in another language, e.g. in french in Switzerland, usefr_ch
. In France, usefr_fr
. - All available languages are listed here.
- This option can be changed later by the statement
ALTER SERVER PARAMETER SET SERVER_DEFAULT_LANGUAGE
.
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.
by default, the content of this file is
localhost:7777
. Trailing blanks are ignored.
ONLY CLIENTS ON THE SAME MACHINE AS THE SERVER CAN CONNECT TO IT !IF YOU WANT TO CONNECT TO THE SERVER FROM ANOTHER MACHINE, THE LISTENING ADDRESS SHOULD BE
0.0.0.0:7777
.
The server will then listen to all IP addresses on local machine.
Setting the Global Cache Size
Internally, RSQL works only with pages that are loaded in one of the caches:
- The global cache contains only committed pages. It is shared and accessed by all sessions.
- Each session has also a private write cache. It contains pages being modified by the session and not committed yet.
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 server is run as a systemd daemon, the standard output will be redirected to the systemd journal.
- If you prefer to write to a logging file, you specify it with the
-logpath
flag.
If the flag -logpath=
logging_file is passed, the messages are written to the specified logging_file.
- If only a filename is given, the logging file will be written in the
logfiles
subdirectory of the instance base directory. - Else, it must be an absolute path. The logging file will be written at the specified location, and all directories in the path will be created if missing.
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:
- when the logging file reaches a specified size, it is closed and the current timestamp is appended to its name.
- a new logging file is created to receive the next server messages.
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.
buldir
is the default directory used byBULK INSERT
andBULK EXPORT
.dumpdir
is the default directory used byRESTORE
andDUMP
.
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:
- Create a group which will have the read and write permissions on the files.
- Give ownership of these directories to this group.
- and set the setgid bit of these directories. All files created in these directories will belong to the group owning these directories.
- Add your personal account as member of this group.
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