Database Scripts

The VCDB v5 software package comes with shell and SQL scripts for tasks such as setting up or removing a VCDB instance, creating additional schemas, and granting or revoking access permissions.

The database scripts are included in the VCDB software package and are also bundled with the vcdb-tool software.

Shell scripts

The VCDB v5 shell scripts are located in the vcdb/postgresql/shell-scripts directory of the VCDB software package or within the installation directory of vcdb-tool. They are available in two variants, organized into the following subfolders:

unix for use on UNIX/Linux/macOS systems; and
windows for use on Windows systems.

The following table provides an overview of the available shell scripts and their purposes.

Script [.sh|.bat] Description

Script [.sh\|.bat]	Description
`connection-details`	Stores the connection details for a VCDB instance which are used by all other scripts
`create-db`	Creates a new VCDB instance (relational schema including all database functions)
`create-schema`	Creates an additional data schema (analogous to the default schema `citydb`) with a user-defined name
`drop-db`	Drops a VCDB instance (incl. all elements of the relational schema)
`drop-schema`	Drops a data schema that has been created with `create-schema`
`grant-access`	Grants read-only or read-write access to a VCDB instance
`revoke-access`	Revokes read-only or read-write access to a VCDB instance, which has been granted with `grant-access`
`create-changelog`	Create the changelog extension for a VCDB instance
`drop-changelog`	Remove the changelog extension from a VCDB instance
`upgrade-db`	Upgrade an existing VCDB instance to a newer minor or patch version

connection-details

Stores the connection details for a VCDB instance which are used by all other scripts

create-db

Creates a new VCDB instance (relational schema including all database functions)

create-schema

Creates an additional data schema (analogous to the default schema citydb) with a user-defined name

drop-db

Drops a VCDB instance (incl. all elements of the relational schema)

drop-schema

Drops a data schema that has been created with create-schema

grant-access

Grants read-only or read-write access to a VCDB instance

revoke-access

Revokes read-only or read-write access to a VCDB instance, which has been granted with grant-access

create-changelog

Create the changelog extension for a VCDB instance

drop-changelog

Remove the changelog extension from a VCDB instance

upgrade-db

Upgrade an existing VCDB instance to a newer minor or patch version

The scripts are intended to run in an interactive shell session, prompting the user for necessary information to perform their tasks. The connection-details script serves a special purpose, as it defines the connection details for your VCDB v5 instance. These details are used by all other scripts, so make sure to adjust them before executing any of them. This includes specifying the full path to the psql executable on your system, which is required by all scripts.

Open the connection-details script with a text editor of your choice and enter the necessary information, as shown below.

Linux
Windows CMD

#!/bin/bash
# Provide your database details here ----------------
export PGBIN=/var/lib/postgresql/[version]/bin
export PGHOST=localhost
export PGPORT=5432
export CITYDB=citydb_v5
export PGUSER=citydb_user
#----------------------------------------------------

# Provide your database details here ----------------
set PGBIN=C:\Program Files\PostgreSQL\[version]\bin
set PGHOST=localhost
set PGPORT=5432
set CITYDB=citydb_v5
set PGUSER=citydb_user
#----------------------------------------------------

If the psql executable is already on your PATH, you can comment out or remove the line setting the PGBIN variable in the script.

After adjusting the connection-details script, all other scripts can be executed either by double-clicking them or by running them from within a shell environment. On UNIX/Linux machines, you may first need to set the appropriate file permissions to make the script executable.

The example below demonstrates how to run the create-db script to set up a new VCDB v5 instance.

Linux
Windows CMD

chmod u+x create-db.sh
./create-db.sh

create-db.bat

It is also possible to use a different connection-details file from another folder:

Linux
Windows CMD

./create-db.sh /path/to/connection-details.sh

create-db.bat C:\path\to\connection-details.bat

SQL scripts

Technically, the shell scripts listed above are simply wrappers designed to collect user input in a convenient manner. The actual actions at the database level are carried out by SQL scripts that are invoked by these shell scripts.

The SQL scripts are provided in the vcdb/postgresql/sql-scripts directory of the VCDB software package or within the installation directory of vcdb-tool. Similar to the shell scripts, navigate to the unix or windows subfolder, depending on your operating system. The SQL scripts are designed to be executed with psql.

Most of the SQL scripts require input parameters to execute the database action. These parameters should be passed as command-line parameters to psql. Below is an example of how to invoke the create-db.sql script to set up a VCDB v5 instance. The required input parameters for this script are discussed in the setup instructions. Refer to the psql documentation for more details on its usage and command-line options.

Linux
Windows CMD

psql -d "citydb_v5" \
    -h localhost \
    -U "citydb_user" \
    -f "/path/to/the/reate-db.sql" \
    -v srid="25833" \
    -v srs_name="urn:ogc:def:crs:EPSG::25833" \
    -v changelog="no"

psql -d "citydb_v5" ^
    -h localhost ^
    -U "citydb_user" ^
    -f "C:\path\to\the\create-db.sql" ^
    -v srid="25833" ^
    -v srs_name="urn:ogc:def:crs:EPSG::25833" ^
    -v changelog="no"

By using shell or environment variables instead of hardcoding values directly into your command as shown above, you make it easier to reuse the SQL scripts across different setups or systems. This makes automating things, integrating them into other software, or running them as part of a CI/CD pipeline way more flexible. This is an easy way to streamline workflows using the SQL scripts.