risotto/docker/postgres-init/10-postgres.init.sh

#!/bin/bash
set -e

psql --username "$POSTGRES_USER" <<-EOSQL
    CREATE ROLE risotto WITH LOGIN PASSWORD 'risotto';
    CREATE DATABASE risotto;
    GRANT ALL ON DATABASE risotto TO risotto;
    \c risotto
    CREATE EXTENSION hstore;
    CREATE EXTENSION pgcrypto;
EOSQL

psql --username "risotto" --password "risotto" <<-EOSQL
    -- Création de la table Source
    CREATE TABLE Source (
    SourceId SERIAL PRIMARY KEY,
    SourceName VARCHAR(255) NOT NULL UNIQUE,
    SourceURL TEXT
    );

    -- Création de la table Release
    CREATE TABLE Release (
    ReleaseId SERIAL PRIMARY KEY,
    ReleaseName VARCHAR(255) NOT NULL,
    ReleaseSourceId INTEGER NOT NULL,
    ReleaseDistribution VARCHAR(20) CONSTRAINT releasedistribution_choice CHECK (ReleaseDistribution IN ('last', 'n-1', 'n-2')),
    UNIQUE (ReleaseName, ReleaseSourceId),
    UNIQUE (ReleaseDistribution, ReleaseSourceId),
    FOREIGN KEY (ReleaseSourceId) REFERENCES Source(SourceId)
    );


    -- Création de la table Servermodel
    CREATE TABLE Servermodel (
    ServermodelId SERIAL PRIMARY KEY,
    ServermodelName VARCHAR(255) NOT NULL,
    ServermodelDescription VARCHAR(255) NOT NULL,
    ServermodelParentsId INTEGER [] DEFAULT '{}',
    ServermodelReleaseId INTEGER NOT NULL,
    ServermodelApplicationServiceId INTEGER NOT NULL,
    ServermodelUsers hstore,
    UNIQUE (ServermodelName, ServermodelReleaseId)
    );

    -- Création de la table ApplicationService
    CREATE TABLE ApplicationService (
    ApplicationServiceId SERIAL PRIMARY KEY,
    ApplicationServiceName VARCHAR(255) NOT NULL,
    ApplicationServiceDescription VARCHAR(255) NOT NULL,
    ApplicationServiceReleaseId INTEGER NOT NULL,
    ApplicationServiceDependencies JSON,
    UNIQUE (ApplicationServiceName, ApplicationServiceReleaseId)
    );

    -- Server table creation
    CREATE TABLE Server (
    ServerId SERIAL PRIMARY KEY,
    ServerName VARCHAR(255) NOT NULL UNIQUE,
    ServerDescription VARCHAR(255) NOT NULL,
    ServerServermodelId INTEGER NOT NULL
    );

    -- User, Role and ACL table creation

    CREATE TABLE RisottoUser (
    UserId SERIAL PRIMARY KEY,
    UserLogin VARCHAR(100) NOT NULL UNIQUE,
    UserPassword TEXT NOT NULL,
    UserName VARCHAR(100) NOT NULL,
    UserSurname VARCHAR(100) NOT NULL
    );

    CREATE TABLE UserRole (
    RoleId SERIAL PRIMARY KEY,
    RoleUserId INTEGER NOT NULL,
    RoleName VARCHAR(255) NOT NULL,
    RoleAttribute VARCHAR(255),
    RoleAttributeValue VARCHAR(255),
    FOREIGN KEY (RoleUserId) REFERENCES RisottoUser(UserId)
    );

    CREATE TABLE URI (
    URIId SERIAL PRIMARY KEY,
    URIName VARCHAR(255) NOT NULL UNIQUE
    );

    CREATE TABLE RoleURI (
    RoleName VARCHAR(255) NOT NULL,
    URIId INTEGER NOT NULL,
    FOREIGN KEY (URIId) REFERENCES URI(URIId),
    PRIMARY KEY (RoleName, URIId)
    );

    -- Log table creation

    CREATE TABLE log(
    Msg VARCHAR(255) NOT NULL,
    Level VARCHAR(10) NOT NULL,
    Path VARCHAR(255),
    Username VARCHAR(100) NOT NULL,
    Data JSON,
    Date timestamp DEFAULT current_timestamp
    );

EOSQL