CREATE DATABASE

CREATE DATABASE database_name
TYPE KDB|VIRTUAL|SQL
[USER user_name IDENTIFIED BY 'password'] [DEFAULT TABLESPACE tablespace_name]
[DESCRIPTION string]
[WITH PERMISSIONS]

Use with KDB

Creates a new database entry in the machine configuration file (kconf.xml). If the database is of type KDB then a catalog will also be created with the name DATABASE_NAME_CAT.kdb in the 'system' tablespace, which must already exist and point to an existing directory. No catalog is created for a VIRTUAL database, and connections to such a database can only be used to issue further CREATE DATABASE or CREATE TABLESPACE commands.

The optional WITH PERMISSIONS syntax allows a new style permissions file to be created for the database. The file will be named DATABASE_NAME_PERMS.kdb and will also be created in the 'system' tablespace.

It is recommended that the DEFAULT TABLESPACE, which must exist before CREATE DATABASE is executed, is a FLAT tablespace.

The USER clause is not implemented for KDB databases and should not used.

This function can be executed whilst connected to a 'virtual' database to allow boot-strapping of new systems. The KCML installer will create a default virtual database called 'system' with a default tablespace also called 'system' which will be a subdirectory of the KCML install directory. Note also that the user used to make the connection upon which the CREATE DATABASE statement is issued becomes the dbauser for the database, which has implications for any permissions checking that occurs.

Use with SQL databases

This creates a new database schema owned by a nominated user in the current database. It can only be executed while connected as a DBA (e.g. SYS AS SYSDBA in Oracle or postgres in PostgreSQL). The database TYPE must be SQL and the USER clause is required. The DESCRIPTION and PERMISSIONS clauses are not used and will be ignored.

For Oracle the database name is not used directly though it is tracked in a table SYS.K$DATABASES for the benefit of KI_DATABASES. Instead schemas are owned by users so there should be a one to one correspondence between users and databases.

With PostgreSQL the database name is used in the database clause of the KI_CONNECT connect string to identify the database. A role for the name in the USER clause will be created if necessary. There can be more than one database owned by a user but this is not recommended. All databases will be created using UTF-8 encoding in the C locale.

On some SQL databases, for example PostgreSQL, the database_name and the user_name are case sensitive and therefore the identifiers should be quoted to protect them from being uppercased.

All databases

A program can get a list of the databases available with KI_DATABASES.

Examples

CREATE DATABASE test TYPE KDB DEFAULT TABLESPACE temp DESCRIPTION 'Test database'
CREATE DATABASE boot TYPE VIRTUAL DEFAULT TABLESPACE SYSTEM DESCRIPTION 'Bootstrap connection'
CREATE DATABASE "K8test" TYPE SQL USER "k809" IDENTIFIED BY 'secret' DEFAULT TABLESPACE users