Skip to content

Latest commit

 

History

History
81 lines (53 loc) · 3.56 KB

Database.md

File metadata and controls

81 lines (53 loc) · 3.56 KB
Raw

The REAM database

This document gives details about the REAM database. It covers:

  • An overview

  • How to connect

  • The process to make changes

Overview

As described in docs/Overview.md, the database is where all our data is stored. When we wish to run a scenario in Switch, we run switch get_inputs to retrieve the necessary data from the database.

There are a few useful tools and techniques to understand what each table in the databse does. First, PGAdmin and DBVisualizer are two great tools that allow viewing a database. I recommending installing both as they each have their strong points. DBVisualizer can also create a graph of all the relationships between tables.

Further, it is often useful to read the comments on tables (PGAdmin: right-click table -> Properties) as they sometimes give details on the tables role. Finally, if the table is used in get_inputs.py one can discover what it does by looking at how get_inputs.py uses the table to generate the SWITCH inputs.

Connecting to the database

First you'll need access to the REAM server. Then you'll need an account in the database. For both of these, ask Paty for access. Once you have access you can connect by creating an SSH tunnel. An SSH tunnel binds the database port to your computer port. The following command creates an SSH tunnel to your 5432 port.

ssh -L 5432:localhost:5432 -N -f <user>@<server_url>

After running this command, tools like PGAdmin and DBVisualizer can access the database at localhost port 5432.

Of course, you can always ssh directly into the server and access the database from the SSH terminal. The command to enter PostgreSQL while SSH'd into the server is psql wecc.

Making changes to the database

Common operations

Some database changes are common operations that are used repeatedly. For these operations, it's helpful to make a command line tool to make the process easy.

For example, adding a scenario to the database is a common operation. Therefore, we have built the switch db save_scenario command to easily do this. (Run switch db save_scenario --help for details).

Custom operations

When running custom operations on the database it's very important to proceed carefully. Always make sure to test and keep track of your changes.

Here are the steps to make a change.

  1. In the \database folder, make a copy of TEMPLATE.sql and name it according to the convention YYYY-MM-DD_<script_name>.

  2. Fill out the title, date and description fields in the .sql file and then add your SQL commands.

  3. Run your script on the wecc-test database to make sure it works properly. If the wecc-test database doesn't exist or is out of sync you might need to create it (see Create a Test Database.md).

  4. Once you are sure your changes work as expected run them on the wecc database.

  5. Open a pull request to add your script to the repository (see docs/Contribute.md) so we can keep track of the changes that have been made.

Note that sometimes, it isn't feasible to have your entire change as a single SQL script. One way to make bigger changes is to use a Python script. Use the same process as for the SQL scripts. That is save your Python scripts in the /database folder.

When adding large datasets to the database, you won't be able to store the initial data with your script in this Git repo. Do however indicate where that initial data can be found.