pvsite-datamodel

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 package functions

Currently available functions accessible via from pvsite_datamodel.read import <func>:

• get_user_by_email
• get_pv_generation_by_sites
• get_site_by_uuid
• get_site_by_client_site_id
• get_site_by_client_site_name
• get_sites_by_client_name
• get_all_sites
• get_sites_by_country
• get_site_group_by_name
• get_latest_status
• get_latest_forecast_values_by_site
• get_client_by_name

Write package functions

Currently available write functions accessible via from pvsite_datamodels.write import <func>:

• insert_forecast_values
• insert_generation_values
• create_site
• create_site_group
• create_user
• add_site_to_site_group
• change_user_site_group
• update_user_site_group
• edit_site
• delete_site
• delete_user
• delete_site_group
• make_fake_site
• create_client
• edit_client
• assign_site_to_client

Install the dependencies (requires poetry)

poetry install

Coding style

Format the code in place.

make format

Lint the code

make lint

Running the tests

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 ≫
    }

    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 ≫
    }

    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
    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 💻	devsjc 💻	Peter Dudfield 💻	Chris Briggs 💻	rachel tipton 💻	Eric Liu 💻	braddf 💻
Bikram Baruah 💻	Andrew Lester 💻	Suleman Karigar 💻	Vishal J ⚠️	Nicholas Tucker 💻

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