DB Databases

Database: Databases

Prerequisites

• A running PostgresSQL server with PostGIS extensions
• An application database user
• A copy of the nics-db repository

The examples below assume you are using nics-docker and the PostgreSQL Client Container to accomplish these tasks. If you are running the scripts directly, you will need to configure psql such that you connect as a superuser to the NICS database host when psql is invoked without a username

Create Main Database

Execute the create_db.sh script from the nics-db repo.

create_db.sh database_name database_user

% docker run -it --rm --network nics_default -e PGDATABASE=postgres nicsdb bash -c "cd /opt/nics/nics-db && ./create_db.sh nics nics"

Create Data Databases

Execute the create_data_dbs.sh script from the nics-db repo.

create_data_dbs.sh database_prefix

% docker run -it --rm --network nics_default -e PGDATABASE=postgres nicsdb bash -c "cd /opt/nics/nics-db && ./create_data_dbs.sh nics"

Create Workspace

Execute the scripts/create_workspace.sh script from the nics-db repo.

scripts/create_workspace.sh database_name workspace_id workspace_name

% docker run -it --rm --network nics_default nicsdb bash -c "cd /opt/nics/nics-db && ./scripts/create_workspace.sh nics 1 Default"

Create System

Execute the scripts/create_system.sh script from the nics-db repo.

scripts/create_system.sh database_name system_name system_description system_id workspace_id

% docker run -it --rm --network nics_default nicsdb bash -c "cd /opt/nics/nics-db && ./scripts/create_system.sh nics nicsweb Testing 1 1"

Create Organization

Execute the scripts/create_org.sh script from the nics-db repo.

scripts/create_org.sh database_name org_name org_state org_county org_prefix org_type_id

You can look up the orgtypeid choices in the orgtype table in baseline_data.sql.

% docker run -it --rm --network nics_default nicsdb bash -c "cd /opt/nics/nics-db && ./scripts/create_org.sh nics Testers Sacramento CA o1 8"

Create Default User

Execute the scripts/create_default_user.sh script from the nics-db repo.

scripts/create_default_user.sh database_name username org_id workspace_id

% docker run -it --rm --network nics_default nicsdb bash -c "cd /opt/nics/nics-db && ./scripts/create_default_user.sh nics tester@tabordasolutions.net 1 1"

Create Default Folders

Execute the scripts/create_default_folders.sh script from the nics-db repo.

scripts/create_default_folders.sh database_name workspace_id

% docker run -it --rm --network nics_default nicsdb bash -c "cd /opt/nics/nics-db && ./scripts/create_default_folders.sh nics 1"

Apply Schema Changes

Run all the sql scripts in the changes directory, if they exist.

% docker run -it --rm --network nics_default nicsdb bash -c "psql -f /opt/nics/nics-db/changes/6.2.sql nics"

Create Weather Maps

Execute the datalayers/weather/weather_layers.sh script from the nics-db repo.

datalayers/weather/weather_layers.sh database_name workspace_id

% docker run -it --rm --network nics_default nicsdb bash -c "cd /opt/nics/nics-db/datalayers/weather && ./weather_layers.sh nics 1"

Create Base Maps

Execute the datalayers/maps/maps_layers.sh script from the nics-db repo.

datalayers/weather/weather_layers.sh database_name

% docker run -it --rm --network nics_default nicsdb bash -c "cd /opt/nics/nics-db/datalayers/maps && ./maps_layers.sh nics"

Create RAWS DB Objects

Execute the datalayers/weather/weather_datafeed.sh script from the nics-db repo.

datalayers/weather/weather_datafeed.sh database_name

% docker run -it --rm --network nics_default -e PGDATABASE=postgres nicsdb bash -c "cd /opt/nics/nics-db/datalayers/weather && ./weather_datafeed.sh nics_datafeeds nics"