pvsite-datamodel 1.2.15

SDK for interacting with the PVSite database

pvsite-datamodel

:warning: Note this repo will soon be deprecated in favour of a new Data Platform

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
• LocationSQL
• LocationGroupSQL
• 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
TODO update table

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: Use uv for virtual environment 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

3. Check PostgreSQL Status: Ensure the service is running properly:

sudo service postgresql status

4. Get Your Local IP Address: Copy the IP address to configure your connection:

hostname -I

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

6. 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.

7. Run the migrations:

uv 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

2. Navigate to the Repository:

cd pv-site-datamodel

3. Install Dependencies:

uv sync

4. Activate Virtual Environment:

source .venv/bin/activate

5. Set Database URL:

Replace the placeholder with your actual database connection string.

export DB_URL="postgresql://<username>:<password>@<your_ip>:5432/<your_database>"

6. Make Code Changes:

Modify the necessary code files as needed.

7. Format the Code:

make format

8. 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 ≫
        + location_group_uuid : UUID ≪ FK ≫
    }
        class LocationGroupSQL{
        + location_group_uuid : UUID ≪ PK ≫
        + location_group_name : String ≪ U ≫
        + service_level : Integer ≪ U ≫
    }

    class LocationGroupLocationSQL{
        + location_group_location_uuid : UUID ≪ PK ≫
        + location_group_uuid : UUID ≪ FK ≫
        + location_uuid : UUID ≪ FK ≫
    }
    
    class LocationLocationSQL{
        + location_location_uuid : UUID ≪ PK ≫
        + location_parent_uuid : UUID ≪ FK ≫
        + location_child_uuid : UUID ≪ FK ≫
    }

    class LocationSQL{
        + location_uuid : UUID ≪ PK ≫
        + client_location_id : Integer
        + client_location_name : String
        + country : String ≪ D ≫
        + region : String
        + location_type: Enum ≪ D ≫
        + location_metadata : String
        + dno : String
        + gsp : String
        + 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
    }

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

    class ForecastSQL{
        + forecast_uuid : UUID ≪ PK ≫
        + location_uuid : UUID ≪ FK ≫
        + timestamp_utc : DateTime
        + forecast_version : String
    }

    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
        + message : String
    }

    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" LocationGroupSQL : belongs_to
    LocationGroupLocationSQL "N" -- "1" LocationSQL : contains
    LocationGroupSQL "1" -- "N" LocationGroupLocationSQL : contains
    LocationSQL "1" -- "N" GenerationSQL : generates
    LocationSQL "1" -- "N" ForecastSQL : forecasts
    LocationSQL "N" -- "0" MLModelSQL : ml_model
    ForecastSQL "1" -- "N" ForecastValueSQL : contains
    MLModelSQL "1" -- "N" ForecastValueSQL : forecasts
    LocationSQL "1" -- "N" InverterSQL : contains
    UserSQL "1" -- "N" APIRequestSQL : performs_request
    ClientSQL "1" -- "N" LocationSQL : owns
    LocationSQL "1" -- "N" LocationLocationSQL : contains
    LocationLocationSQL "N" -- "1" LocationSQL : contains
    
    class Legend{
    UUID: Universally Unique Identifier
    PK: Primary Key
    FK: Foreign Key
    U: Unique Constraint
    D: Default Value
    }

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

Location can mean site or region.

  graph TD;
      User-- N:1 -->LocationGroup;
      LocationGroup-- N:N -->Location;

• One user is in one loationgroup. Each location group can have multiple users.
• Each locationroup contains multiple locations. One location can be in multiple locationgroups

1. one user - one site

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

2. one user - two sites

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

3. Two users - one site

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

4. Two users - two site

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

5. OCF can see everything

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

Database migrations using alembic

./alembic

Contributors ✨

Thanks goes to these wonderful people (emoji key):

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

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