Chapter 14 – SQL Cluster¶

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

The book and the standard does not reflect the features of CrateDB, we are just publishing it as a service for the community and for reference purposes.

On the one hand, CrateDB does not implement the SQL-99 standard thoroughly, on the other hand, it extends the standard for implementing cluster features and others.

For more information specific to CrateDB, check out the CrateDB Reference documentation.

The SQL Standard describes the concepts on which SQL is based in terms of Objects, such as Tables. Each SQL Object is defined in terms of the characteristics (e.g.: its name) that describe it; the Standard calls this the Object’s descriptor. Some Objects are dependent on other Objects, e.g.: a Column is dependent on the Table it belongs to. If an Object is dropped (i.e.: destroyed), then every Object dependent on it is also dropped. The following diagram shows the main SQL Object hierarchy, illustrating, for example, that a Cluster can contain one or more Catalogs; Catalogs can contain one or more Schemas; Schemas can contain one or more Domains, Tables, Character sets, etc.

=========
=Cluster=
=========
     |
============
=Catalog(s)=
============
     |
===========
=Schema(s)=
===========
     |
===========
=Domain(s)=
==================
     =or Table(s)=
===========================
          =or Assertion(s)=
====================================
               =or Character set(s)=
=====================================
                    =or Collation(s)=
============================================
                         =or Translation(s)=
============================================

(There are several other SQL Objects; this diagram shows only the major ones.)

In this chapter, we’ll describe SQL Clusters in detail, and show you the syntax to use to create, alter and destroy them.

Table of Contents

Cluster
- Cluster Names

Cluster ¶

An SQL Cluster is the group of Catalogs available to an SQL-session at any point in time; that is, it contains all the SQL-data you may access through a given SQL-server. The Objects that belong to a Cluster are known as Cluster Objects; that is, they depend on some Cluster. Every Cluster Object has a name that must be unique (among Objects of its name class) within the Cluster it belongs to. The Cluster Object name class is:

A Cluster may consist of zero or more of these Cluster Objects. The Cluster’s name implicitly qualifies the names of the Objects that belong to it, and cannot be explicitly stated. Three SQL statements relate to Clusters: CONNECT, SET CONNECTION and DISCONNECT.

[NON-PORTABLE] SQL does not include any CREATE CLUSTER, OPEN CLUSTER, ADD TO CLUSTER or DROP CLUSTER statements. The method you’ll use to access a Cluster with your DBMS is thus non-standard because the SQL Standard requires implementors to define what the physical aspects of a Cluster are, whether any Catalog can be part of more than one Cluster at a time, how a Cluster comes into being, how it may be accessed and how it may be destroyed.

[OCELOT Implementation] applies for the rest of this chapter.

The OCELOT DBMS that comes with this book considers a Cluster to be a directory on your storage device, e.g.: this would represent a Cluster on an MS-DOS hard drive:

C:\CLUSTER

Each Cluster directory contains two files and zero or more subdirectories. The first file, called CLUSTER, contains the current definition of all the lower-level SQL Objects contained within the Catalogs that make up the Cluster. The second file, called CLUSTER.BAK, contains the definitions as they were prior to the last COMMIT statement issued during a SQL-session. Any subdirectories in the Cluster directory represent SQL Catalogs. OCELOT does not allow a Catalog to be part of multiple Clusters.

OCELOT’s method of creating and connecting to Clusters depends on the way you choose to begin a SQL session.

If the first SQL statement in your SQL-session is a CONNECT statement, the DBMS will search for a CLUSTER file on a directory whose name matches the CONNECT statement’s <SQL-server name>. If a CLUSTER file is found on the correct directory, the file will be opened. If the correct directory is found but there is no CLUSTER file on it, a CLUSTER file will be created on that directory, and then opened. If no directory with the correct name is found, the directory will be created, then a CLUSTER file will be created on that directory, and opened.
If the first SQL statement in your SQL-session is not a CONNECT statement, the DBMS will open a CLUSTER file on a directory named OCELOT.

To drop a Cluster, simply delete the CLUSTER file from your storage device.

Cluster Names¶

A <SQL-server name> identifies a Cluster. The required syntax for a <SQL-server name> is as follows.

<SQL-server name> ::=
string

An <SQL-server name> has a maximum length of 128 octets and is either a <character string literal>, the name of a host character string parameter or a reference to an SQL character string parameter that conforms to the rules for an <identifier>.

NON-PORTABLE] A <SQL-server name> must be unique (for all Clusters) within an SQL-environment, but is non-standard because the SQL Standard requires implementors to define what a <SQL-server name> may be and which Character set it belongs to.

[OCELOT Implementation] The OCELOT DBMS that comes with this book defines a <SQL-server name> as any <identifier> belonging to the SQL_TEXT Character set that also follows the rules for a directory name on the operating system in use; generally it may include [drive:] and upper-level name.

Here are some examples of possible <SQL-server name>s:

'CLUSTER_1'
-- a <SQL-server name> that is a <character string literal>

:CLUSTER_1
-- a <SQL-server name> that is a host parameter name

CLUSTER_1
-- a <SQL-server name> that is an SQL parameter name