Creating Database Schemas

Before Cadenza can be installed and initialized, the required database schemas must be created in your database system. This page provides recommendations and SQL script templates for creating these schemas on Oracle and PostgreSQL databases.

As described in Overview of the Necessary Preparations, Cadenza requires two mandatory database schemas:

System database schema: Stores information required for the operation of Cadenza
Repository database schema: Stores one ore more Cadenza repositories.

Additionally, it is strongly recommended to create one or more Cadenza Data Store (CDS) schemas to store spatial and non-spatial data managed by Cadenza. While technically optional, CDS schemas are required for the full functionality of Cadenza.

After creating these schemas, they must be initialized using the Database Migration Tool, see Initializing Database Schemas.

Recommendations

PostgreSQL

When creating database schemas for Cadenza on PostgreSQL, we recommend:

Database naming: Use a database name that contains the project name with an environment suffix such as _dev, _test, or _prod (e.g., projectname_dev, projectname_prod).
Username conventions: Compose usernames from the database name and schema name (e.g., projectname_systemdb, projectname_databaserepository, projectname_cds).

Oracle

When creating database schemas for Cadenza on Oracle, we recommend:

Username conventions: Do not start usernames with a number. Use uppercase letters for consistency.
Pluggable Databases: If your Oracle instance uses Pluggable Databases, create separate pluggable databases for test and production environments (e.g., PROJECTNAME_DEV, PROJECTNAME_PROD). This allows schemas to have the same names across environments, eliminating the need for configuration adjustments in Cadenza.
Tablespace sizing: Adjust the initial datafile size according to your needs. The scripts below use 100 MB as the initial size.
Datafile paths: The paths shown in the examples are typical for Oracle instances with Pluggable Databases. Adjust them to match your database configuration.

SQL Script Templates

Select your database system:

PostgreSQL
Oracle

This script creates a PostgreSQL database with the required schemas and users for Cadenza, including a Cadenza Data Store (CDS) schema.

/*
=======================================
==  Database and Admin User          ==
=======================================
*/

CREATE USER <admin_user> WITH
  PASSWORD '<admin_password>';

CREATE DATABASE <database>
    WITH
    OWNER = <admin_user>
    ENCODING = 'UTF8';

REVOKE CONNECT ON DATABASE <database> FROM PUBLIC;
GRANT CONNECT ON DATABASE <database> TO <admin_user>;

\connect <database>

GRANT ALL PRIVILEGES ON DATABASE <database> TO <admin_user>;

CREATE EXTENSION postgis;


/*
=================================================================
== Users for systemdb, databaserepository and CDS            ==
=================================================================
*/

CREATE USER <systemdb_user> WITH
  PASSWORD '<systemdb_password>';

CREATE USER <databaserepository_user> WITH
  PASSWORD '<databaserepository_password>';

CREATE USER <cds_user> WITH
  PASSWORD '<cds_password>';


/*
=============================================================
== Schema for systemdb                                    ==
=============================================================
*/

-- User may connect to database
GRANT CONNECT ON DATABASE <database> TO <systemdb_user>;
-- Admin user inherits all privileges of the new user
GRANT <systemdb_user> TO <admin_user>;
-- Create schema with user as owner
CREATE SCHEMA systemdb AUTHORIZATION <systemdb_user>;
-- Ensure user gets all privileges on tables created by admin user
ALTER DEFAULT PRIVILEGES FOR USER <admin_user> IN SCHEMA systemdb
GRANT ALL ON TABLES TO <systemdb_user>;
-- Set search path
ALTER USER <systemdb_user> SET search_path TO systemdb, public;


/*
==============================================================================
== Schema for databaserepository                                            ==
==============================================================================
*/

\connect <database>

-- User may connect to database
GRANT CONNECT ON DATABASE <database> TO <databaserepository_user>;
-- Admin user inherits all privileges of the new user
GRANT <databaserepository_user> TO <admin_user>;
-- Create schema with user as owner
CREATE SCHEMA databaserepository AUTHORIZATION <databaserepository_user>;
-- Ensure user gets all privileges on tables created by admin user
ALTER DEFAULT PRIVILEGES FOR USER <admin_user> IN SCHEMA databaserepository
GRANT ALL ON TABLES TO <databaserepository_user>;
-- Set search path
ALTER USER <databaserepository_user> SET search_path TO databaserepository, public;


/*
==============================================================================
== Schema for Cadenza Data Store (CDS)                                      ==
==============================================================================
*/

\connect <database>

-- User may connect to database
GRANT CONNECT ON DATABASE <database> TO <cds_user>;
-- Admin user inherits all privileges of the new user
GRANT <cds_user> TO <admin_user>;
-- Create schema with user as owner
CREATE SCHEMA cds AUTHORIZATION <cds_user>;
-- Ensure user gets all privileges on tables created by admin user
ALTER DEFAULT PRIVILEGES FOR USER <admin_user> IN SCHEMA cds
GRANT ALL ON TABLES TO <cds_user>;
-- Set search path
ALTER USER <cds_user> SET search_path TO cds, public;

Replace the placeholders in angle brackets with your specific values.

Recommended naming convention: Use the pattern <projectname>_<env>_ as prefix for all database and user names to clearly identify the project and environment (e.g., myproject_prod_database, myproject_dev_admin_user). This helps manage multiple Cadenza installations across different environments.

<database>: Name of the database (e.g., myproject_prod_database)
<admin_user>: Name of the database admin user (e.g., myproject_prod_admin_user)
<admin_password>: Password for the admin user
<systemdb_user>: Name of the user for the systemdb schema (e.g., myproject_prod_systemdb_user)
<systemdb_password>: Password for the systemdb user
<databaserepository_user>: Name of the user for the databaserepository schema (e.g., myproject_prod_databaserepository_user)
<databaserepository_password>: Password for the databaserepository user
<cds_user>: Name of the user for the cds schema (e.g., myproject_prod_cds_user)
<cds_password>: Password for the cds user

You can create multiple CDS schemas by duplicating and adapting the CDS section with different schema names (e.g., cds2, cds3).

This script creates Oracle tablespaces and schemas with the required users for Cadenza, including a Cadenza Data Store (CDS) schema.

/*
============================================
==  Tablespace and User SYSTEMDB         ==
============================================
*/

/* Create Tablespace SYSTEMDB */
CREATE BIGFILE TABLESPACE SYSTEMDB
DATAFILE '/opt/oracle/oradata/<cdb_root_name>/<pluggable>/SYSTEMDB.dbf' SIZE 100 m
AUTOEXTEND ON NEXT 100 m MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;

/* Create User SYSTEMDB */
CREATE USER SYSTEMDB
IDENTIFIED BY <systemdb_password>
DEFAULT TABLESPACE SYSTEMDB
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT ACCOUNT UNLOCK;

/* Grants for User SYSTEMDB */
GRANT CREATE TABLE TO SYSTEMDB;
GRANT CREATE SEQUENCE TO SYSTEMDB;
GRANT CREATE VIEW TO SYSTEMDB;
GRANT CREATE SESSION TO SYSTEMDB;
ALTER USER SYSTEMDB QUOTA UNLIMITED ON SYSTEMDB;


/*
=====================================================
==  Tablespace and User DATABASEREPOSITORY        ==
=====================================================
*/

/* Create Tablespace DATABASEREPOSITORY */
CREATE BIGFILE TABLESPACE DATABASEREPOSITORY
DATAFILE '/opt/oracle/oradata/<cdb_root_name>/<pluggable>/DATABASEREPOSITORY.dbf' SIZE 100 m
AUTOEXTEND ON NEXT 100 m MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;

/* Create User DATABASEREPOSITORY */
CREATE USER DATABASEREPOSITORY
IDENTIFIED BY <databaserepository_password>
DEFAULT TABLESPACE DATABASEREPOSITORY
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT ACCOUNT UNLOCK;

/* Grants for User DATABASEREPOSITORY */
GRANT CREATE TABLE TO DATABASEREPOSITORY;
GRANT CREATE SEQUENCE TO DATABASEREPOSITORY;
GRANT CREATE VIEW TO DATABASEREPOSITORY;
GRANT CREATE SESSION TO DATABASEREPOSITORY;
ALTER USER DATABASEREPOSITORY QUOTA UNLIMITED ON DATABASEREPOSITORY;


/*
========================================================
==  Tablespace and User for CDS (Cadenza Data Store) ==
========================================================
*/

/* Create Tablespace CDS */
CREATE BIGFILE TABLESPACE CDS
DATAFILE '/opt/oracle/oradata/<cdb_root_name>/<pluggable>/CDS.dbf' SIZE 100 m
AUTOEXTEND ON NEXT 100 m MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;

/* Create User CDS */
CREATE USER CDS
IDENTIFIED BY <cds_password>
DEFAULT TABLESPACE CDS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT ACCOUNT UNLOCK;

/* Grants for User CDS */
GRANT CREATE TABLE TO CDS;
GRANT CREATE SEQUENCE TO CDS;
GRANT CREATE VIEW TO CDS;
GRANT CREATE SESSION TO CDS;
ALTER USER CDS QUOTA UNLIMITED ON CDS;

Replace the placeholders in angle brackets with your specific values:

<cdb_root_name>: Name of the container database
<pluggable>: Name of the pluggable database
<systemdb_password>: Password for the SYSTEMDB user
<databaserepository_password>: Password for the DATABASEREPOSITORY user
<cds_password>: Password for the CDS user

You can create multiple CDS schemas by duplicating and adapting the CDS section with different tablespace and user names (e.g., CDS2, CDS3).

Important Notes

Backup Strategy

After creating the database schemas, ensure that:

All created schemas and tables are included in your regular backup strategy
Backups are performed before any schema updates or migrations
You have tested the restore process to verify backup integrity

A comprehensive backup strategy is essential for production environments to prevent data loss. For detailed guidance on backing up Cadenza database contents, see Backing Up Database Contents.

Next Steps

After creating the database schemas using these scripts:

Verify that the schemas were created successfully and that the users can connect
Initialize the schemas using the Database Migration Tool, see Initializing Database Schemas
Configure Cadenza to use these schemas:
- Configuring the System Database
- Configuring the Repository Database