Create Users and Schemas

Create Users and Schemas mk Tue, 02/25/2020 - 10:47

In order to explore the wealth of functionality offered by MonetDB/SQL, the best strategy is to get started using a toy database. For this we use the VOC database which provides a peephole view into the administrative system of an early multi-national company, the Vereenigde geoctrooieerde Oostindische Compagnie (VOC for short - The (Dutch) East Indian Company) established on March 20, 1602.

First thing we need to do is download the dataset. The VOC dataset can be downloaded here voc_dump.zip (542K) gz (519K) bz2 (371K) and it is a compressed file with SQL statements.

After the file has been extracted, load its contents into MonetDB using mclient. Before you load the VOC data set, it is advised to introduce a user different from the omnipresent default monetdb.

The new user is given his own schema for the MonetDB database. Assuming you have started MonetDB with SQL module, proceed as follows:

_________________________________________________________________________________________

shell> mclient -u monetdb -d voc
password:<monetdb>
sql>CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys";
sql>CREATE SCHEMA "voc" AUTHORIZATION "voc";
sql>ALTER USER "voc" SET SCHEMA "voc";
sql>\q

_________________________________________________________________________________________

To illustrate the use of the newly created account and schema, the following example connects and creates a table, which is dropped afterwards by aborting the transaction.

_________________________________________________________________________________________

shell> mclient -u voc -d voc
password:<voc>
sql>START TRANSACTION;
auto commit mode: off

sql>CREATE TABLE test (id int, data varchar(30));
operation successful (0.722ms)

sql>\d
TABLE  voc.test

sql>\d test
CREATE TABLE "voc"."test" (
    "id"   INTEGER,
    "data" VARCHAR(30)
);
sql>CREATE TABLE "voc"."test" ("id" int, "data" varchar(30));
sql>ROLLBACK;
auto commit mode: on

sql>\d

_________________________________________________________________________________________