Database Organization

DataCaster is a distributed database, and contains database tables, views, and a number of other database objects., to create and use application data. These objects are quite familiar to users of relational databases, and follow well-established standards. There is most always some variation across databases in the overall organization of data, i.e. the creation and use of schemas, catalogs, and databases themselves. The organization of data in DataCaster is described below.

Most applications will need to understand how the data is organized. In a few cases you may be working purely with remote data, in which case you will not be creating your own data and not require table creation, etc. However, in most cases you will want to create schemas, tables and other database objects as required.

Databases are created in the data directory of a DataCaster server or client. Databases in DataCaster are stored in their entirety in a separate directory for each database. These database directores can be copied as files (only when the database is not open) and used on another server, or in another users data directory on the same server.

Database Structure

DataCaster closely follows the SQL standard with regard to the organization of database objects. Each database contains catalogs, which contain schemas, which contain tables, views, and other database objects.

Every database has a home catalog, and in the home catalog each user has a schema with the same name as their username. This is the default schema for each user when they connect to the database.

Users can then create schemas, catalogs, and other ways of organizing database objects. Schema owners can grant permissions to other users to allow them to use, create, alter and drop schema objects.

Remote and anonymous users cannot create databases, catalogs, or schemas. The only exception is a user schema in usually be created for them in the home catalog when they connect to a server.

The following describes each of the objects in more detail.

The Database

A database is a collection of data, organized contained in database objects like catalogs, schemas, tables, views and other objects. A database can be accessed by applications by connecting to the database after being authenticated with a username and password.

A database (or cluster - see below) contains one or more catalogs.

Any number of databases may be created on a server. Multiple databass on a given server do not share data and access in the same manner as objects within a single database. Use of data from multiple databases on the same server will need to follow the same mechanisms used for access to databases on remote servers. It is therefore desirable to put related data to be used together in the same database, even if the data may be in different catalogs.

By default, any user can create databases, and databases are created automatically when used if they do not exist. This can be turned off via a preferences parameter, as outlined in the configuration preference. If turned off, only administrators can create databases and databases are not created automatically. Only administrators and the database owner, i.e. the user creating the database, can drop a database.

Clusters

Clusters are functionally equivalent to databases in DataCaster, and a database contains a single cluster. So clusters can largely be ignored as a separate entity, and are used primarily for internal organization. In future, there may be support added for multiple clusters within a database, largely to support replication and clustering of data.

A cluster, just as described above for a database, contains one or more catalogs.

Catalogs

Catalogs are a collection of schemas. The SQL standard (SQL-99) defines a catalog as follows: "A catalog is a named collection of SQL-schemas in an SQL-environment." In DataCaster they have an owner, i.e the person that created the catalog.

DataCaster allows users to create and drop catalogs. The default home catalog is created automatically, and is always present in every database. This catalog cannot be dropped.

Each catalog has an owner. In a given catalog, users can create any number of schemas. Any authenticated user can create a schema in any catalog. Only the catalog owner, an admin, or the database owner can drop a catalog..

Schemas

Schemas contain the database objects used by applications, i.e. tables, views, indexes, and other related objects like statements, functions and procedures. The SQL standard defines an SQL schema as follows: "An SQL-schema, often referred to simply as a schema, is a persistent, named collection of descriptors that describe SQL-data. Any object whose descriptor is in some SQL-schema is known as an SQL-schema object."

Each schema has an owner. Each user has a default schema in the home catalog that is created automatically, and named after the user. In addition, authenticated users can create schema objects in any catalog.

Only the schema owner, catalog owner, database owner, or admin can drop a schema.

Schema Objects

A schema contains a number of database objects, which can be any of the following.

• Tables
• Views
• Indexes
• Statements
• Functions
• Stored Procedures
• Triggers

Please see the authorization section of the security guilde for information on how create, drop and use of these schema objects are authorized.