The CREATE CATALOG creates a new catalog in the solidDB® database. The use of catalogs in solidDB® is an extension to the SQL standard.
Catalogs allow you to logically partition databases so you can organize your data to meet the needs of your business or application.
A solidDB® database file may contain more than one logical database. Each logical database is a complete, independent group of database objects, such as tables, indexes, triggers, stored procedures, and so on. Each logical database is implemented as a database catalog. Thus, solidDB® can have one or more catalogs.
When creating a new database or converting an old database to a new format, you are prompted for a default catalog name. This default catalog name allows for backward compatibility of solidDB® databases prior to version 3.x.
A catalog can have zero or more schema names. The default schema name is the user ID of the user who creates the catalog.
A schema can have zero or more database object names. A database object can be qualified by a schema or user ID.
The catalog name is used to qualify a database object name.
Important: The catalog name must not contain spaces.
Database object names can be qualified in all DML statements as:
catalog_name.schema_name.database_object
or
catalog_name.user_id.database_object
If you use the catalog name, you must also use the schema name. The converse is not true; you may use the schema name without using the catalog name (if you have already done an appropriate SET CATALOG statement to specify the default catalog).
Only a user with DBA authority (SYS_ADMIN_ROLE) can create a catalog for a database.
Creating a catalog does not automatically make that catalog the current default catalog. If you have created a new catalog and want your subsequent commands to execute within that catalog, you must also execute the SET CATALOG statement. For example:
CREATE CATALOG MyCatalog; CREATE SCHEMA smith; -- not in MyCatalog SET CATALOG MyCatalog; CREATE SCHEMA jones; -- in MyCatalog
To use schemas, a schema name must be created with CREATE SCHEMA statement before creating the database object name. However, a database object name can be created without a schema name. In such cases, database objects are qualified using user_id only.
A catalog context can be set in a program using the SET CATALOG statement.
A catalog can be dropped from a database using DROP CATALOG. When dropping a catalog name, all objects associated with the catalog name must be dropped prior to dropping the catalog.
Rules for resolving catalog names
▪A fully qualified name (catalog_name.schema_name.database_object_name) does not need any name resolution, but will be validated.
▪If a catalog context is not set using SET CATALOG, all database object names are resolved always using the default catalog name as the catalog name. The database object name is resolved using schema name resolution rules. For for more information about these rules, see CREATE SCHEMA.
▪If a catalog context is set and the catalog name cannot be resolved using the catalog_name in the context, database_object_name resolution fails.
▪To access a database system catalog, users do not need to know the system catalog name. Users can specify ""._SYSTEM.table". solidDB® translates the empty string " that is used as a catalog name to the default catalog name. solidDB® also provides automatic resolution of _SYSTEM schema to the system catalog, even when the catalog name is not provided.
Examples
CREATE CATALOG C; SET CATALOG C; CREATE SCHEMA S; SET SCHEMA S; CREATE TABLE T (i INTEGER); SELECT * FROM T; -- the name T is resolved to C.S.T
-- Assume the userid is SMITH CREATE CATALOG C; SET CATALOG C; CREATE TABLE T (i INTEGER); SELECT * FROM T; -- The name T is resolved to C.SMITH.T
-- Assume there is no Catalog context set. -- Meaning the default catalog name is BASE or the setting -- of the base catalog. CREATE SCHEMA S; SET SCHEMA S; CREATE TABLE T (i INTEGER); SELECT * FROM T; --The name T is resolved to <BASE>.S.T
CREATE CATALOG C1; SET CATALOG C1; CREATE SCHEMA S1; SET SCHEMA S1; CREATE TABLE T1 (c1 INTEGER); CREATE CATALOG C2; SET CATALOG C2; CREATE SCHEMA S2; SET SCHEMA S2; CREATE TABLE T1 (c2 INTEGER) SET CATALOG BASE; SET SCHEMA USER; SELECT * FROM T1; -- This select will give an error as it -- cannot resolve the T1.