Data definition

Data definition mk Thu, 06/30/2011 - 09:49

The MonetDB/SQL supports the full range of object definitions.

Table definitions

Table definitions mk Sat, 03/27/2010 - 22:34

The CREATE TABLE statement conforms to the full SQL standard. Tables are assigned to the current schema unless the schema name is explicitly given as part of the table name. Table names should be unique amongst those mentioned within the same schema and distinct from view names.

table_def:
     CREATE TABLE [ IF NOT EXISTS ] table_name  table_content_source [ STORAGE ident string ]
   | CREATE [ LOCAL | GLOBAL ] TEMPORARY TABLE [ IF NOT EXISTS ] table_name  table_content_source
                     [ ON COMMIT { DELETE ROWS  |  PRESERVE ROWS  |  DROP } ]
   | CREATE TABLE [ IF NOT EXISTS ] table_name FROM LOADER function_ref
   | CREATE MERGE TABLE [ IF NOT EXISTS ] table_name  table_content_source
   | CREATE REPLICA TABLE [ IF NOT EXISTS ] table_name  table_content_source
   | CREATE REMOTE TABLE [ IF NOT EXISTS ] table_name  table_content_source  ON
                     'mapi:monetdb://host:port/dbname' [ WITH [ USER 'username' ] [ [ ENCRYPTED ] PASSWORD 'password'] ]
   | CREATE STREAM TABLE [ IF NOT EXISTS ] table_name  table_content_source

table_content_source:
   '(' table_element_list ')'
  |  [ '(' column_name_list ')' ] AS select_query   [ WITH DATA | WITH NO DATA ]

table_element_list:
   table_element
  |  table_element_list , table_element

The derived (temporary) tables are either filled upon creation or automatically upon use within queries.

Temporary tables are stored automatically under the schema 'tmp'. Temporary local tables are limited to the client session. The qualifiers denote the actions taken during transaction commit over a temporary table. If the ON COMMIT clause is omitted then all tuples are dropped while retaining the structure. In most cases you would use: ON COMMIT PRESERVE ROWS

For using Loader functions we support the MonetDB specific CREATE table FROM LOADER syntax.

For merging partitioned table data we support the MonetDB specific CREATE MERGE table syntax.

For replicating table data we support the MonetDB specific CREATE REPLICA table syntax.

For distributed query processing we support the MonetDB specific CREATE REMOTE table syntax.

For data stream processing we support the MonetDB specific CREATE STREAM table syntax.

Table elements

Table elements mk Sun, 03/28/2010 - 13:46
table_element:
     column_name   data_type   [ column_option ... ]
   | column_name { SERIAL | BIGSERIAL }
   | column_name   WITH OPTIONS '(' column_option [ ',' ... ] ')'
   | LIKE table_name
   | [ CONSTRAINT constraint_name ] table_constraint_type
column_option:
     DEFAULT default_value_expr
   | [ CONSTRAINT constraint_name ] column_constraint_type
   | GENERATED ALWAYS AS IDENTITY [ '(' serial_parameters ')' ]
   | AUTO_INCREMENT

Identity columns take their values from a sequence generator. The MySQL (auto_increment) and PostgreSQL (serial data type) syntax are also supported. The SERIAL type is an identity column which is mapped to an INTEGER NOT NULL PRIMARY KEY. Similarly the BIGSERIAL type is an identity column which is mapped to a BIGINT NOT NULL PRIMARY KEY.

Column and Table constraints are both supported. Besides the not null constraint also unique, primary key and foreign key constraints are supported. A table can have at most one primary key definition. We currently check constraints directly on insert, update and delete, so immediate. This limitation stems from the missing triggers. The null matching on foreign keys is limited to the SIMPLE match type (null values satisfy the constraint). The full and partial match types are not supported. The referential action is currently limited to RESTRICT, i.e. an update fails if other columns have references to it.

column_constraint_type:
     NOT NULL
   | NULL
   | UNIQUE
   | PRIMARY KEY
   | REFERENCES table_name [ '(' column_name [ ',' ... ')' ] ]   [ match_options ]   [ ref_actions ]

match_options:
     MATCH { FULL | PARTIAL | SIMPLE }

ref_actions:
     { ON UPDATE | ON DELETE } { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }

table_constraint_type:
     UNIQUE '(' column_name [ ',' ... ] ')'
   | PRIMARY KEY '(' column_name [ ',' ... ] ')'
   | FOREIGN KEY '(' column_name [ ',' ... ] ')' REFERENCES table_name [ '(' column_name [ ',' ... ] ')' ]   [ match_options ]   [ ref_actions ]

Note: The column CHECK constraint definitions are no longer accepted by the parser as of Nov2019 (11.35.3) release. They used to be accepted (for ease of migration) but were not enforced nor recorded in a data dictionary table.

A table constraint definition is not tied to a particular column, but rather to a column group. A column constraint is a notational convenience when the constraint only affects one column.

A constraint over a referenced value can be either a FULL, PARTIAL or SIMPLE (default) match. A full match requires all column values to be not null unless all are null. The simple match is more relaxed; any of the keys may be null. The partial match is considered noise for the time being.

The serial columns are a slight extension over the serial types defined earlier. In particular, we can designate RESTART option when the sequence is exhausted. This may involve a subquery over the database.

serial_parameters:
     [ START WITH nonzero-bigint ]
   | [ RESTART | RESTART WITH subquery
   | RESTART WITH nonzero-bigint ]
   | [INCREMENT BY nonzero-bigint ]
   | [MINVALUE nonzero-bigint | NOMINVALUE]
   | [MAXVALUE nonzero-bigint | NOMAXVALUE ]
   | [CACHE nonzero-bigint ]
   | [CYCLE | NOCYCLE]

Index definitions

Index definitions mk Sat, 03/27/2010 - 22:46

The index statements in the SQL standard are recognized, but their implementation is different from competitive products. MonetDB/SQL interprets these statements as an advice and often freely neglects it, relying on its own decision to create and maintain indexes for fast access. Also the UNIQUE qualifier is not honored or enforced. To add a uniqueness constraint for a table or column use UNIQUE in the CREATE TABLE or ALTER TABLE statement.

index_def:
    CREATE [ UNIQUE ] INDEX ident ON [ schema name . ] table name '(' ident ','... ')'

 

MonetDB supports two special kinds of secondary indices: IMPRINTS and ORDERED INDEX, introduced in release Dec2016 (v11.25.3). These index types are experimental and have some limitations: Only 1 column can be indexed per index. Only columns of numeric data type (tinyint, smallint, integer, bigint, hugeint, float, real, double, decimal) can be indexed. The index is not maintained automatically and will become inactive when inserts, deletes or updates are done on the column data. Since creating these indices can be expensive, they are only used when explicitly created. They are useful for large static (or read only) tables with numeric columns.

index_def:
    CREATE IMPRINTS INDEX ident ON [ schema name . ] table name '(' numeric column name ')'

Creates a new imprints index on one numerical column of a specific table. This index stores meta data (min, max, nulls) on segments of column data. It can speed-up queries which have a column selection condition (such as: AGE IS NULL OR AGE >= 18). It is a very compact index.

See also: Documentation/Manuals/MonetDB/Kernel/Modules/Imprints

index_def:
    CREATE ORDERED INDEX ident ON [ schema name . ] table name '(' numeric column name ')'

Creates a new ordered index on one numerical column of a specific table. This index is a special single column index where the numeric values are stored in ascending order. It can speed-up queries which have a column selection condition (such as: AGE BETWEEN 18 AND 30) or need sorting such as when used in a GROUP BY clause.

Alter statement

Alter statement mk Sat, 03/27/2010 - 22:39

The ALTER statement can be used to change the TABLE properties, it requires authorization to do so. Addition of a column follows the same syntax and functionality as the CREATE TABLE statement. If the table is used in a foreign key relationship the actions to be take can be further specified as RESTRICT to forbid it if there are external references to the column values. The CASCADE nullifies all references to the values being removed.

alter_statement:

         ALTER TABLE [IF EXISTS] qname ADD [COLUMN] column_name { data_type [ column_option ... ] | SERIAL | BIGSERIAL }
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET [NOT] NULL
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET DEFAULT value
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name DROP DEFAULT
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET STORAGE {string | NULL}
      |  ALTER TABLE [IF EXISTS] qname DROP [COLUMN] column_name [RESTRICT | CASCADE]
      |  ALTER TABLE [IF EXISTS] qname ADD [ CONSTRAINT ident ]  table_constraint_type
      |  ALTER TABLE [IF EXISTS] qname DROP CONSTRAINT ident [RESTRICT | CASCADE]

As of Apr2019 release (11.33.3) you can also change the name of a column, the name of the table itself or the schema the table is belonging to, provided there are no objects which depend on the table or column name.

alter_rename_statement:

         ALTER TABLE [IF EXISTS] qname RENAME [COLUMN] column_name TO new_column_name
      |  ALTER TABLE [IF EXISTS] qname RENAME TO new_table_name
      |  ALTER TABLE [IF EXISTS] qname SET SCHEMA schema_name

An individual table can be protected using the READ ONLY and INSERT ONLY mode. All attempts to update are flagged as a SQL error. The reverse operation is ALTER TABLE qname READ WRITE, which makes the table accessible for all update operations.

alter_access_statement:

         ALTER TABLE [IF EXISTS] qname SET READ ONLY
      |  ALTER TABLE [IF EXISTS] qname SET INSERT ONLY
      |  ALTER TABLE [IF EXISTS] qname SET READ WRITE

The ALTER statement has been extended for merge tables with ADD TABLE and DROP TABLE options to attach and detach a partition table to/from a MERGE TABLE and with SET TABLE p AS PARTITION to change the partitioning specification of the partition table.

alter_statement_for_merge_tables:

        ALTER TABLE [IF EXISTS] qname ADD TABLE qname [ AS PARTITION partition_spec ]
      | ALTER TABLE [IF EXISTS] qname SET TABLE qname AS PARTITION partition_spec
      | ALTER TABLE [IF EXISTS] qname DROP TABLE qname [ RESTRICT | CASCADE ]

View definitions

View definitions mk Fri, 04/02/2010 - 10:39

Regular SQL view definitions are supported.

view_def:
    CREATE [ OR REPLACE ] VIEW [ schema_name '.' ] view_name
           [ '(' column_name [ ',' column_name ] [ ',' ...] ')' ]
           AS select_query_without_limit
           [ WITH CHECK OPTION ]

A view can be seen as a stored SELECT query with a unique name. It can be used in queries at all the places where you can normally use a table name. Views are useful to reduce user query complexity as they can predefine joins, computations, derivations, aggregations, selections, conditions, ordering, etc in the view, so the user doesn't have to define them in the queries again and again. They are also very useful to standardise and simplify reporting.

Note: The "WITH CHECK OPTION" is accepted for SQL compliance but has no effect.

Note: Views do not contain or store data, so do not require disk space.

Limitations: Recursive views and reference-able views are not (yet) supported.
Updatable views are not supported, so it's not possible to insert, update, merge, delete or truncate data from a view.

Tip: to find out which user views are defined in the database run query:
  SELECT * FROM sys.tables WHERE type IN (SELECT table_type_id FROM sys.table_types WHERE table_type_name = 'VIEW')

Schema definitions

Schema definitions mk Sat, 03/27/2010 - 22:50

A SCHEMA is a container for tables, views, indices, triggers, functions and procedures.  Schema access and modification is strictly controlled using the user role and authorisation scheme.

Every SCHEMA belongs to a database. One reason for organizing the logically separated data in schemas rather than databases is that in this way there will still be just one MonetDB process running.

schema:
   CREATE SCHEMA schema_name_clause
      [ DEFAULT CHARACTER SET ident ]
      [ PATH schema_name ','... ]
      [ schema_element ... ]

The DEFAULT CHARACTER SET and PATH options are here for compatibility reasons with the the SQL standard, however they are not yet implemented. The default character set is UTF-8.

schema_name_clause:
   ident | [ ident ] AUTHORIZATION ident

The AUTHORIZATION option allows specifying the name of the user or the role that will own the schema. If omitted, the user who has executed the query will be the owner. The owner of the schema is allowed to create, alter and drop tables. With the AUTHORIZATION option, an explicit name for the schema is optional. If omitted, the schema automatically gets the name of the authorised user/role as its name.

Notes on schema creation:

  1. The ownership of a schema can be assigned to only one user/role, and it can not be modified after its creation. Therefore, to share the ownership of a schema, one must assign the ownership of a schema to a role at the creation of the schema. Subsequently, the role can be granted to multiple users to own the schema. 
  2. Only the 'monetdb' user and the 'sysadmin' role can create a new schema. Therefore, to allow other users other to create schema, the 'monetdb' user should assign the 'sysadmin' role to the intended users.

schema_element:
   grant | revoke | create_statement | drop_statement | alter_statement

schema:
  | DROP SCHEMA qname drop_action

drop_action:
   RESTRICT | CASCADE

The drop_action option is supported for compatibility with the SQL standard, however it is not implemented yet. Currently it runs with the CASCADE option, meaning that once a schema is dropped every object inside the schema is dropped as well, such as tables and functions. However objects that are dependent on the schema, such as users will not automatically be dropped and will stop the schema from being dropped as well. One can either ALTER the user and give it a different default schema, or to simply drop the user if it is no longer needed.

schema:
  | SET SCHEMA ident

When opening the database, by default the “sys” schema is set. Another automatically created schema is “tmp”, used for temporally local tables. It is not possible for a user to access the “tmp” schema of another user.

One can create a new schema using the CREATE command and change to it, by using the SET command. When creating a table without specifying the schema, the table will be created in the schema that is currently in usage. In order to create a table in a different schema, use the schema name as a prefix.

Drop statement

Drop statement mk Mon, 05/24/2010 - 07:58

The objects created can be removed provided the authorization permissions are set. Note: If you do not specify the full signature of the function the DROP query will successfully execute if there is only one function with this name, if not the query is aborted. The DROP ALL is used to drop all the functions with the name specified in the query.

drop_statement:
     DROP TABLE qname [ RESTRICT | CASCADE ]
   | DROP VIEW qname [ RESTRICT | CASCADE ]
   | DROP [ FILTER ] FUNCTION qname [ '(' [ data_type ','... ] ')' ] [ RESTRICT | CASCADE ]
   | DROP PROCEDURE qname [ '(' [ data_type ','... ] ')' ] [ RESTRICT | CASCADE ]
   | DROP AGGREGATE qname [ '(' [ data_type ','... ] ')' ] [ RESTRICT | CASCADE ]
   | DROP LOADER qname [ '(' [ data_type ','... ] ')' ]
   | DROP ALL [ FILTER ] FUNCTION qname [ RESTRICT | CASCADE ]
   | DROP ALL PROCEDURE qname [ RESTRICT | CASCADE ]
   | DROP ALL AGGREGATE qname [ RESTRICT | CASCADE ]
   | DROP INDEX qname
   | DROP TRIGGER qname
   | DROP SEQUENCE qname
   | DROP ROLE qname
   | DROP USER ident
   | DROP TYPE qname
   | DROP SCHEMA name [ RESTRICT | CASCADE ]

 

Privileges

Privileges mk Wed, 02/26/2014 - 15:41

Users

Users mk Sat, 03/27/2010 - 22:54

All interactions with the database server are attributed to a particular user known to the system. Therefore, a user login name should be created upfront, much like you would have to on any computer system, Your user name determines the schemas you have permission to access and your role in managing them. Much like your computers file system, it is essential to restrict access to your valuable database content via grant and revoke privileges and/or roles.

To create, modify/alter or drop a user in the database you can use the following syntax:

user_stmt:
    CREATE USER user_name WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'user_password' NAME 'full name' SCHEMA schema_name
   | ALTER USER user_name RENAME TO new_user_name
   | ALTER USER SET [ENCRYPTED | UNENCRYPTED] PASSWORD 'new_password' USING OLD PASSWORD 'user_password'
   | ALTER USER user_name WITH [ENCRYPTED | UNENCRYPTED] PASSWORD 'user_password'
   | ALTER USER user_name [ WITH [ENCRYPTED | UNENCRYPTED] PASSWORD 'user_password' ] SET SCHEMA schema_name
   | DROP USER user_name

The user name, password and default schema can be changed using the different alter user statements.

Note that for a user (including the administrator user: monetdb) to change its own password, the ALTER USER SET form has to be used where the current password is given. When the ENCRYPTED clause is given for PASSWORD, a hashed form of the password has to be supplied. The type of this hash depends on how the server was configured, but defaults to SHA512.

You may view the list of users in the database via query:

    SELECT * FROM sys.users;

The user name, password and database name are needed to authenticate yourself against tools such as mclient and all APIs, to gain access to the particular database. Once connected the current schema is the one as specified for the user.

Every user plays a certain role. So, when a user is created, a role with the same name is automatically created as well. The difference is that, the user has all corresponding permissions, e.g., the user has all permissions on the table it creates. However, the role with the same name remains empty until permissions have been granted to it explicitly.

Roles

Roles mk Mon, 05/24/2010 - 07:51

Every user takes a role with respect to the privileges on a database scheme. A user can be granted multiple roles and a role can be granted to multiple users.

By the creation of a new user, a role with the same name is automatically created, which is also the default role the user takes. However, only the role can be granted to other users/roles.

Privileges cannot be inherited via another role. For example Alice can only SELECT, Bob can only INSERT, and Alice is granted to Bob. If Bob in turn is granted to Charlie, then Charlie can only INSERT, but not SELECT.

 

role_statement:
    CREATE ROLE role_name [ WITH ADMIN grantor ]
  | DROP ROLE role_name [ WITH ADMIN grantor ]
  | SET ROLE role_name
  | GRANT role_name TO user_name
 

grantor:
    CURRENT_USER | CURRENT_ROLE

A user can only assume a role after he/she has logged in. The user assumes a role by using the SET ROLE command. Then the user can use all the right given to that role. Two roles cannot be assumed at once.

Grant and revoke

Grant and revoke mk Fri, 04/02/2010 - 12:47

Qualified users can grant roles and other users various privileges on tables or execute privilege on functions and procedures or global privileges. The table privileges are any combination of SELECT, INSERT, DELETE, TRUNCATE, UPDATE and REFERENCES. ALTER and INDEX privileges cannot be granted. Only the owner has the right to alter a table definition. Any privilege can be later revoked, which takes any combination of the previously granted privileges.

grant:
    GRANT privileges TO grantees [ WITH GRANT OPTION ]
  | GRANT role_name ',' ... TO grantees [ WITH ADMIN OPTION ] [ WITH ADMIN grantor ]

revoke:
    REVOKE [ GRANT OPTION FOR ] privileges FROM grantees [ FROM grantor ]
  | REVOKE [ ADMIN OPTION FOR ] role_name ','... FROM grantees [ FROM grantor ]

privileges:
    object_privileges ON  [ TABLE ]  qname
    | EXECUTE ON [ FUNCTION | AGGREGATE ] qname
    | global_privilege [ ',' global_privilege ]

object_privileges:
    ALL [ PRIVILEGES ] | table-operation ','...

table-operation:
    INSERT | DELETE | TRUNCATE
  | UPDATE [ opt_column_list ]
  | SELECT [ opt_column_list ]
  | REFERENCES [ opt_column_list ]

global_privilege:
    COPY INTO | COPY FROM

grantees: [ PUBLIC | authid ] ','...

grantor:
    CURRENT_USER | CURRENT_ROLE

Bulk I/O permissions

For reasons of security, by default, MonetDB only allows the special user monetdb to execute the bulk I/O statements COPY INTO (copy data from the database into a file) and COPY FROM (copy data from a file into the database). As of the June2016 release MonetDB supports granting a user/role the privilege of using the bulk I/O statements. These privileges are global privileges, i.e., they apply to all tables on which the user/role has the INSERT permission.

Examples

1. Grant permissions per table

It is possible to grant one privilege at a time to a user, per table:

GRANT SELECT ON bookSchema.toOrderTable TO libraryUser;
GRANT INSERT ON bookSchema.tOrderTable TO libraryUser;

However, if you want the user to be able to SELECT, INSERT, UPDATE DELETE and TRUNCATE then it can be done in one go:

GRANT ALL ON bookSchema.orderTable TO libraryWorker;

2. Parallel bulk load in shared schema

The following queries grant both the ownership of a schema and the bulk data loading permission (i.e., COPY FROM) to multiple users. Then, those users can log in and bulk load the data into different tables (of the same schema) in parallel.

-- 'monetdb' user
CREATE ROLE copy_role;
GRANT COPY FROM TO copy_role;
CREATE SCHEMA copy_schema AUTHORIZATION copy_role;

CREATE USER user1 WITH PASSWORD 'user1' NAME 'copy user' SCHEMA copy_schema;
CREATE USER user2 WITH PASSWORD 'user2' NAME 'copy user' SCHEMA copy_schema;

GRANT copy_role TO user1;
GRANT copy_role TO user2;

CREATE TABLE copy_schema.t1 (id INT, val VARCHAR(128));
CREATE TABLE copy_schema.t2 (id INT, num INT);

-- normal user user1
SET ROLE copy_role;
COPY INTO t1 FROM '/[path-to]/input.csv' USING DELIMITERS ',';

-- normal user user2
SET ROLE copy_role;
COPY INTO t2 FROM '/[path-to]/input.csv' USING DELIMITERS ',';