Skip to content

Latest commit

 

History

History
430 lines (330 loc) · 16.6 KB

README.md

File metadata and controls

430 lines (330 loc) · 16.6 KB

pvsite-datamodel

All Contributors

Database schema specification for PV Site data.

Repository structure

pvsite_datamodel:
  read: # Sub package containing modules for reading from the database
  write: # Sub package containing modules for writing to the database
    - connection.py # Class for connecting to the database
    - sqlmodels.py # SQLAlchemy definitions of table schemas
tests: # External tests package

Top-level functions

Classes specifying table schemas:

  • APIRequestSQL
  • GenerationSQL
  • ForecastSQL
  • ForecastValueSQL
  • MLModelSQL
  • UserSQL
  • SiteSQL
  • SiteGroupSQL
  • StatusSQL
  • ClientSQL

Database connection objects:

  • DatabaseConnection

Read and write package functions

  • Read function currently accessible via from pvsite_datamodel.read import <func>.
  • Write function Currently accessible via from pvsite_datamodels.write import <func>.
Read Package Functions Write Package Functions
get_user_by_email insert_forecast_values
get_pv_generation_by_sites insert_generation_values
get_site_by_uuid create_site
get_site_by_client_site_id create_site_group
get_site_by_client_site_name create_user
get_sites_by_client_name create_client
get_all_sites make_fake_site
get_sites_by_country add_site_to_site_group
get_site_group_by_name change_user_site_group
get_latest_status update_user_site_group
get_latest_forecast_values_by_site edit_site
get_client_by_name edit_client
assign_site_to_client
delete_site
delete_user
delete_site_group

Local Repository Setup(Linux)

This guide walks you through setting up the repository locally, installing dependencies. Follow the steps carefully to get your development environment up and running.

Pre-requisite:

  • Install Poetry for dependency management

  • Install Pgadmin4 for database management

Database Setup

Follow these steps to set up the database locally:

  1. Install PostgreSQL: Download and install PostgreSQL on your system : Download PostgreSQL for Linux

  2. Start PostgreSQL Service:

sudo service postgresql start
  1. Check PostgreSQL Status: Ensure the service is running properly:
sudo service postgresql status
  1. Get Your Local IP Address: Copy the IP address to configure your connection:
hostname -I

  1. Create the Database: Use pgAdmin 4 or the command line to create your database.

  2. Run Migrations with Alembic: Export the database URL and apply migrations:

export DB_URL="postgresql://<username>:<password>@<your_ip>:5432/<your_database>"
  • Note: Replace the placeholders with your actual database credentials.
  1. Run the migrations:
poetry run alembic upgrade head

Steps to setup the repository locally

  1. Fork & Clone the Repository:
git clone https://github.com/openclimatefix/pv-site-datamodel.git
  1. Navigate to the Repository:
cd pv-site-datamodel
  1. Install Dependencies:
poetry install
  1. Activate Virtual Environment:
source .venv/bin/activate
  1. Set Database URL:

Replace the placeholder with your actual database connection string.

export DB_URL="postgresql://<username>:<password>@<your_ip>:5432/<your_database>"
  1. Make Code Changes:

Modify the necessary code files as needed.

  1. Format the Code:
make format
  1. Lint the Code:
make lint

Running the tests

Run the following command to execute the test suite:

    make test

PVSite Database Schema

---
title: SQLAlchemy relationships
---
classDiagram

    class UserSQL{
        + user_uuid : UUID ≪ PK ≫
        + email : String(255) ≪ U ≫
        + site_group_uuid : UUID ≪ FK ≫
    }
        class SiteGroupSQL{
        + site_group_uuid : UUID ≪ PK ≫
        + site_group_name : String(255) ≪ U ≫
        + service_level : Integer ≪ U ≫
    }

    class SiteGroupSiteSQL{
        + site_group_site_uuid : UUID ≪ PK ≫
        + site_group_uuid : UUID ≪ FK ≫
        + site_uuid : UUID ≪ FK ≫
    }

    class SiteSQL{
        + site_uuid : UUID ≪ PK ≫
        + client_site_id : Integer
        + client_site_name : String(255)
        + country : String(255) ≪ D ≫
        + region : String(255)
        + dno : String(255)
        + gsp : String(255)
        + asset_type : Enum ≪ D ≫
        + orientation : Float
        + tilt : Float
        + latitude : Float
        + longitude : Float
        + capacity_kw : Float
        + inverter_capacity_kw : Float
        + module_capacity_kw : Float
        + ml_id : Integer ≪ U ≫
        + client_uuid : UUID ≪ FK ≫
        + ml_model_uuid : UUID ≪ FK ≫
    }

    class ClientSQL{
        + client_uuid : UUID ≪ PK ≫
        + client_name : String(255)
    }

    class GenerationSQL{
        + generation_uuid : UUID ≪ PK ≫
        + site_uuid : UUID ≪ FK ≫
        + generation_power_kw : Float
        + start_utc : DateTime
        + end_utc : DateTime
    }

    class ForecastSQL{
        + forecast_uuid : UUID ≪ PK ≫
        + site_uuid : UUID ≪ FK ≫
        + timestamp_utc : DateTime
        + forecast_version : String(32)
    }

    class ForecastValueSQL{
        + forecast_value_uuid : UUID ≪ PK ≫
        + start_utc : DateTime
        + end_utc : DateTime
        + forecast_power_kw : Float
        + horizon_minutes : Integer
        + forecast_uuid : UUID ≪ FK ≫
    }

    class StatusSQL{
        + status_uuid : UUID ≪ PK ≫
        + status : String(255)
        + message : String(255)
    }

    class InverterSQL{
        + inverter_uuid : UUID ≪ PK ≫
        + site_uuid : UUID ≪ FK ≫
    }

    class APIRequestSQL{
        + uuid : UUID ≪ PK ≫
        + url : String
        + user_uuid : UUID ≪ FK ≫
    }

    class MLModelSQL{
        + uuid : UUID ≪ PK ≫
        + mode_name : String
        + model_version : UUID ≪ FK ≫
    }

    UserSQL "1" -- "N" SiteGroupSQL : belongs_to
    SiteGroupSQL "N" -- "N" SiteSQL : contains
    SiteGroupSQL "1" -- "N" SiteGroupSiteSQL : contains
    SiteSQL "1" -- "N" GenerationSQL : generates
    SiteSQL "1" -- "N" ForecastSQL : forecasts
    SiteSQL "N" -- "0" MLModelSQL : ml_model
    ForecastSQL "1" -- "N" ForecastValueSQL : contains
    MLModelSQL "1" -- "N" ForecastValueSQL : forecasts
    SiteSQL "1" -- "N" InverterSQL : contains
    UserSQL "1" -- "N" APIRequestSQL : performs_request
    ClientSQL "1" -- "N" SiteSQL : owns
    class Legend{
    UUID: Universally Unique Identifier
    PK: Primary Key
    FK: Foreign Key
    U: Unique Constraint
    D: Default Value
    }
Loading

Multiple Clients

We have the ability to have these different scenarios

  1. one user - can add or view one site
  2. one user, can add or view multiple sites
  3. Two users (for example from the sample company), want to look at one site
  4. Two users, wanting to look at multiple sites (could be added by another user). Any user from site group can add a site.
  5. OCF user want to see everything (admin)

Solution

  graph TD;
      User-- N:1 -->SiteGroup;
      SiteGroup-- N:N -->Site;
Loading
  • One user is in one sitegroup. Each site group can have multiple users.
  • Each sitegroup contains multiple sites. One site can be in multiple sitegroups

1. one user - one site

  graph TD;
      A(User=Alice)-->B(SiteGroup=Alice1);
      B --> C(Site);
Loading

2. one user - two sites

  graph TD;
      A(User=Alice)-->B(SiteGroup=Alice1);
      B --> C1(Site1);
B --> C2(Site2);
Loading

3. Two users - one site

  graph TD;
      A1(User=Alice)-->B(SiteGroup);
A2(User=Bob)-->B(SiteGroup);
      B --> C1(Site1);
Loading

4. Two users - two site

  graph TD;
      A1(User=Alice)-->B(SiteGroup);
A2(User=Bob)-->B(SiteGroup);
      B --> C1(Site1);
B --> C2(Site2);
Loading

5. OCF can see everything

  graph TD;
      A1(User=Alice)-->B(SiteGroup1);
A2(User=Bob)-->B(SiteGroup1);
A3(User=OCF)-->B2(SiteGroup2);
      B --> C1(Site1);
B --> C2(Site2);
      B2 --> C1(Site1);
B2 --> C2(Site2);
B2 --> C3(Site3);
Loading

Database migrations using alembic

./alembic

Contributors ✨

Thanks goes to these wonderful people (emoji key):

Abhijeet
Abhijeet
💻		 devsjc
devsjc
💻		 Peter Dudfield
Peter Dudfield
💻		 Chris Briggs
Chris Briggs
💻		 rachel tipton
rachel tipton
💻		 Eric Liu
Eric Liu
💻		 braddf
braddf
💻
Bikram Baruah
Bikram Baruah
💻		 Andrew Lester
Andrew Lester
💻		 Suleman Karigar
Suleman Karigar
💻		 Vishal J
Vishal J
⚠️		 Nicholas Tucker
Nicholas Tucker
💻		 PrabhasKalyan
PrabhasKalyan
💻		 hanaawad24
hanaawad24
📖 ⚠️
Matthew Duffin
Matthew Duffin
💻		 MAYANK SHARMA
MAYANK SHARMA
⚠️		 Ruth Velasquez
Ruth Velasquez
🤔

This project follows the all-contributors specification. Contributions of any kind welcome!