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 ⭐ 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.
Several extension need to be installed on our PostGre SQL database :
- postgis
- postgis_topology
- pg_routing
Create a new git branch (never work directly on master)
Welcome to 'Plastic Origins OPS DB', a database management (access, code, etc) that uses EF.Core to manage database directly from code.
Before you begin, ensure you have met the following requirements:
- You have installed
.Net Core 3.1 or lastest - You have installed the latest version of
Azure Emulatorif you want to use on your local machine - You have a
PostgreSQL 11.6 minimumdatabase for local use on your machine ORMicrosoft Azure PostgreSQL(you can create a free account here) - You have
Visul Studio 2019(or latest version) or you have latest version ofVisual code.
- Language:
C# - Framework:
.Net Core - Functionality :
Azure function - Unit test framework:
XUnit
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.
| 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 :
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;
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;
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;
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
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
-
Create a new git branch (never work directly on master)
-
Edit the entities (= tables) the way you want. They are stored in Models folder.
-
Edit the PlasticoDbContext.cs file. It represents the whole database and links all the entities (= tables) together.
-
When all edit is done, generate the corresponding Migration with command below :
dotnet ef migrations add initPublicSchema --context PlasticoDbContext -o Migrations
-
If everything looks fine, create the pull request.
-
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 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
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. 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.
-
Take a look at the open issues labeled as
help wanted, feel free to comment to share your ideas or submit a pull request if you feel that you can fix the issue yourself. Please document any relevant changes.
If you experience any problems, please don't hesitate to ping:
Special thanks to all our Contributors.
We’re using the MIT License. For more details, check LICENSE file.