search

surfriderfoundationeurope / plasticorigins-ops-db

Database management (access, code, etc)
MIT License
1 stars 1 forks source link

readme

Plastic Origins OPS DB

Proudly Powered by SURFRIDER Foundation Europe, this open-source initiative is a part of the PLASTIC ORIGINS project - a citizen science project that uses AI to map plastic pollution in European rivers and share its data publicly. Browse the project repository to know more about its initiatives and how you can get involved. Please consider starring :star: the project's repositories to show your interest and support. We rely on YOU for making this project a success and thank you in advance for your contributions.

Database configuration

Several extension need to be installed on our PostGre SQL database :

Welcome to 'Plastic Origins OPS DB', a database management (access, code, etc) that uses EF.Core to manage database directly from code.

Getting Started

Prerequisites

Before you begin, ensure you have met the following requirements:

Technical stack

Schema

Logs : all log entries have default status = HARD_FAIL We define 3 different status : * SUCCESS * GRACEFULL_FAIL : we know the reason why it failed * HARD_FAIL : something unexpected happened For more details, check `Plastic Origins database documentation` file. ### Access management | Role name | PRIVILEGES | Group name | Users | | ----------------| --------------- | -------- | -------- | |r_reader | SELECT | g_reader |reader_user| | r_writer| SELECT, INSERT, UPDATE, DELETE | g_writer |writer_user| | r_manager| ALL | g_manager |manager_user| Following scripts have been used to generate user accesses : ```plsql CREATE ROLE r_reader NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT USAGE ON SCHEMA public, campaign, bi, referential to r_reader; GRANT SELECT ON ALL TABLES IN SCHEMA public, campaign, bi, referential to r_reader; ALTER DEFAULT PRIVILEGES IN SCHEMA public, campaign, bi, referential GRANT SELECT ON TABLES TO r_reader; CREATE ROLE g_reader NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT r_reader to g_reader; CREATE ROLE reader_user WITH LOGIN ; ALTER ROLE reader_user WITH PASSWORD '****' ; ALTER ROLE reader_user VALID UNTIL 'infinity' ; GRANT g_reader TO reader_user; ``` ```plsql CREATE ROLE r_writer NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT USAGE ON SCHEMA public, campaign, bi, referential to r_writer; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public, campaign, bi, referential to r_writer; ALTER DEFAULT PRIVILEGES IN SCHEMA public, campaign, bi, referential GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO r_writer; CREATE ROLE g_writer NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT r_writer to g_writer; CREATE ROLE writer_user WITH LOGIN ; ALTER ROLE writer_user WITH PASSWORD '****' ; ALTER ROLE writer_user VALID UNTIL 'infinity' ; GRANT g_writer TO writer_user; ``` ```plsql CREATE ROLE r_manager NOSUPERUSER INHERIT NOREPLICATION; GRANT USAGE ON SCHEMA public, campaign, bi, referential to r_manager; GRANT ALL ON ALL TABLES IN SCHEMA public, campaign, bi, referential to r_manager; ALTER DEFAULT PRIVILEGES IN SCHEMA public, campaign, bi, referential GRANT ALL ON TABLES TO r_manager; CREATE ROLE g_manager NOSUPERUSER INHERIT NOREPLICATION; GRANT r_manager to g_manager; CREATE ROLE manager_user WITH LOGIN ; ALTER ROLE manager_user WITH PASSWORD '****' ; ALTER ROLE manager_user VALID UNTIL 'infinity' ; GRANT g_manager TO manager_user; ``` ### Installation #### Setup the project The project can connect both to local and distant db. It first reads the connection strings contained in the appsettings.local.json file, then in your local environnement variable. You need to store your local db connection string to appsettings.local.json by replacing the existing. If you want to connect to distant db, do not store the connection string in the code. Instead, set the connection string in your environnement variable. To do that, cf https://docs.microsoft.com/en-us/azure/azure-app-configuration/quickstart-dotnet-core-app#build-and-run-the-app-locally ### Usage #### Principles Every database table is represented by a C# class. Everytime we need to make some changes over the database schema, we need to create a EFCore "Migration" > dotnet ef migrations add NameOfMyMigration --context MyDbContext -o WhereMyMigrationWillBeStored -idempotent This will create a Migration class that describes our changes over the database. Note that all tables of all schema are stored in the Models folder. Same for migrations, all stored under Migrations folder. Example : > dotnet ef migrations add initPublicSchema --context PlasticoDbContext -o Migrations -idempotent Then update the db according to this migration > dotnet ef database update ## **Build and Test** ### How to edit the database/tables/columns? 1. Create a new git branch (never work directly on master) 2. Edit the entities (= tables) the way you want. They are stored in Models folder. 3. Edit the PlasticoDbContext.cs file. It represents the whole database and links all the entities (= tables) together. 4. When all edit is done, generate the corresponding Migration with command below : > dotnet ef migrations add initPublicSchema --context PlasticoDbContext -o Migrations 5. If everything looks fine, create the pull request. 6. As soon as the PR is merged, the build pipeline (Azure DevOps) will generate the migration script (sql) and pass it to the Release pipeline. Another way of doing it is by executing the migration directly from your local computer via command below : > dotnet ef database update EF.Core will detect automatically which migrations it needs to run (only the one not runned already). ### Scaffolding Scaffolding is the fact or reverse engineer an existing database. This is the way we used to create our entities, as the database already existed. To target a particular database schema, we run the following command : > dotnet-ef dbcontext scaffold "My Connection String" Npgsql.EntityFrameworkCore.PostgreSQL -o OutputDir -c ContextName --schema SchemaName ## Contributing It's great to have you here! We welcome any help and thank you in advance for your contributions. * Feel free to **report a problem/bug** or **propose an improvement** by creating a [new issue](https://github.com/surfriderfoundationeurope/plasticorigins-ops-db/issues). Please document as much as possible the steps to reproduce your problem (even better with screenshots). If you think you discovered a security vulnerability, please contact directly our [Maintainers](##Maintainers). * Take a look at the [open issues](https://github.com/surfriderfoundationeurope/plasticorigins-ops-db/issues) labeled as `help wanted`, feel free to **comment** to share your ideas or **submit a** [**pull request**](https://github.com/surfriderfoundationeurope/plasticorigins-ops-db/pulls) if you feel that you can fix the issue yourself. Please document any relevant changes. ## Maintainers If you experience any problems, please don't hesitate to ping: * [@ChristopheHvd](https://github.com/ChristopheHvd) Special thanks to all our [Contributors](https://github.com/orgs/surfriderfoundationeurope/people). ## License We’re using the `MIT` License. For more details, check [`LICENSE`](https://github.com/surfriderfoundationeurope/plasticorigins-ops-db/blob/master/LICENSE) file.