WindS@U.PORTO

windscanner.eu & NEWA

User Tools

Site Tools


windsp:db

WindsP SQL

windsp_nc.sql
-- TYPES ----------------------------------------------------------
 
CREATE TYPE collaboration_fc AS ENUM ('yes', 'no', 'maybe');
 
CREATE TYPE "enum_VersionedFile_permission" AS ENUM ('public', 'participant', 'manager'
);
 
// NOT USED: TABLE event_category instead
CREATE TYPE enum_events_event_type AS ENUM ('schedule', 'install', 'issue');
 
-- TABLES ---------------------------------------------------------
 
CREATE TABLE "Capabilities" (
    id SERIAL NOT NULL,
    name CHARACTER VARYING(255),
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "Capabilities_pkey" PRIMARY KEY (id)
);
 
CREATE TABLE "CollabDevices" (
    id SERIAL NOT NULL,
    collab_equipment_model_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "CollabDevices_pkey" PRIMARY KEY (id),
    CONSTRAINT "CollabDevices_collab_equipment_model_id_fkey" FOREIGN KEY (collab_equipment_model_id) REFERENCES "CollabEquipmentModels"(id)
);
 
CREATE TABLE "CollabEquipmentModels" (
    id SERIAL NOT NULL,
    collab_category_id INTEGER NOT NULL,
    equipment_model_id INTEGER NOT NULL,
    uses_datalogger BOOLEAN,
    datalogger_id INTEGER,
    quantity INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "CollabEquipmentModels_pkey" PRIMARY KEY (id),
    CONSTRAINT "CollabEquipmentModels_collab_category_id_fkey" FOREIGN KEY (collab_category_id) REFERENCES "CollabEquipmentSubcategories"(id) ON DELETE CASCADE,
    CONSTRAINT "CollabEquipmentModels_datalogger_id_fkey" FOREIGN KEY (datalogger_id) REFERENCES "EquipmentModels"(id),
    CONSTRAINT "CollabEquipmentModels_equipment_model_id_fkey" FOREIGN KEY (equipment_model_id) REFERENCES "EquipmentModels"(id)
);
 
CREATE TABLE "CollabEquipmentSubcategories" (
    id SERIAL NOT NULL,
    collaboration_id INTEGER NOT NULL,
    equipment_subcategory_id INTEGER NOT NULL,
    quantity INTEGER,
    transportation_cost BOOLEAN,
    usage_mode CHARACTER VARYING(255),
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "CollabEquipmentSubcategories_pkey" PRIMARY KEY (id),
    CONSTRAINT "CollabEquipmentSubcategories_collaboration_id_fkey" FOREIGN KEY (collaboration_id) REFERENCES "Collaborations"(id),
    CONSTRAINT "CollabEquipmentSubcategories_equipment_subcategory_id_fkey" FOREIGN KEY (equipment_subcategory_id) REFERENCES "EquipmentSubcategories"(id)
);
 
CREATE TABLE "CollabPersonnelCategories" (
    id SERIAL NOT NULL,
    collaboration_id INTEGER NOT NULL,
    personnel_category_id INTEGER NOT NULL,
    quantity INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "CollabPersonnelCategories_pkey" PRIMARY KEY (id),
    CONSTRAINT "CollabPersonnelCategories_collaboration_id_fkey" FOREIGN KEY (collaboration_id) REFERENCES "Collaborations"(id),
    CONSTRAINT "CollabPersonnelCategories_personnel_category_id_fkey" FOREIGN KEY (personnel_category_id) REFERENCES "PersonnelCategories"(id)
);
 
CREATE TABLE "CollabWindmodels" (
    id SERIAL NOT NULL,
    collaboration_id INTEGER NOT NULL,
    windmodel_id INTEGER,
    windmodel_category_id INTEGER NOT NULL,
    uses BOOLEAN,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "CollabWindmodels_pkey" PRIMARY KEY (id, windmodel_category_id),
    CONSTRAINT "CollabWindmodels_collaboration_id_fkey" FOREIGN KEY (collaboration_id) REFERENCES "Collaborations"(id),
    CONSTRAINT "CollabWindmodels_windmodel_category_id_fkey" FOREIGN KEY (windmodel_category_id) REFERENCES "WindmodelCategories"(id),
    CONSTRAINT "CollabWindmodels_windmodel_id_fkey" FOREIGN KEY (windmodel_id) REFERENCES "Windmodels"(id)
);
 
CREATE TABLE "Collaborations" (
    id SERIAL NOT NULL,
    institution_id INTEGER NOT NULL,
    experiment_id INTEGER NOT NULL,
    will_collaborate CHARACTER VARYING(160),
    DATA text,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT collaborations_pkey PRIMARY KEY (id),
    CONSTRAINT collaboration_partner_experiment UNIQUE (institution_id, experiment_id),
    CONSTRAINT collaborations_experiment_id_fkey FOREIGN KEY (experiment_id) REFERENCES "Experiments"(id),
    CONSTRAINT collaborations_partner_id_fkey FOREIGN KEY (institution_id) REFERENCES "Institutions"(id)
);
 
CREATE TABLE "Countries" (
    id SERIAL NOT NULL,
    name CHARACTER VARYING,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT country_pkey PRIMARY KEY (id)
);
 
CREATE TABLE "Devices" (
    id SERIAL NOT NULL,
    SERIAL_number CHARACTER VARYING(255),
    equipment_model_id INTEGER,
    institution_id INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "Devices_pkey" PRIMARY KEY (id),
    CONSTRAINT "Devices_equipment_model_id_fkey" FOREIGN KEY (equipment_model_id) REFERENCES "EquipmentModels"(id),
    CONSTRAINT "Devices_institution_id_fkey" FOREIGN KEY (institution_id) REFERENCES "Institutions"(id)
);
 
CREATE TABLE "EquipmentCategories" (
    id SERIAL NOT NULL,
    short_title CHARACTER VARYING(100),
    long_title CHARACTER VARYING(150),
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT equipment_categories_pkey PRIMARY KEY (id)
);
 
CREATE TABLE "EquipmentManufacturers" (
    id SERIAL NOT NULL,
    name CHARACTER VARYING(150),
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT equipment_manufacturers_pkey PRIMARY KEY (id),
    CONSTRAINT equipment_manufacturers_name_key UNIQUE (name)
);
 
CREATE TABLE "EquipmentModels" (
    id SERIAL NOT NULL,
    model CHARACTER VARYING(150),
    webpage CHARACTER VARYING(200),
    equipment_manufacturer_id INTEGER NOT NULL,
    equipment_subcategory_id INTEGER,
    updated_at TIMESTAMP WITH TIME zone,
    created_at TIMESTAMP WITH TIME zone,
    "photosFolder" INTEGER,
    "datasheetsFolder" INTEGER,
    "manualsFolder" INTEGER,
    "othersFolder" INTEGER,
    CONSTRAINT equipment_models_pkey PRIMARY KEY (id),
    CONSTRAINT "EquipmentModels_datasheetsFolder_fkey" FOREIGN KEY ("datasheetsFolder") REFERENCES "Folder"(id),
    CONSTRAINT "EquipmentModels_manualsFolder_fkey" FOREIGN KEY ("manualsFolder") REFERENCES "Folder"(id),
    CONSTRAINT "EquipmentModels_othersFolder_fkey" FOREIGN KEY ("othersFolder") REFERENCES "Folder"(id),
    CONSTRAINT "EquipmentModels_personalFolder_fkey" FOREIGN KEY ("photosFolder") REFERENCES "Folder"(id),
    CONSTRAINT equipment_models_manufacturer_id_fkey FOREIGN KEY (equipment_manufacturer_id) REFERENCES "EquipmentManufacturers"(id),
    CONSTRAINT equipment_models_type_id_fkey FOREIGN KEY (equipment_subcategory_id) REFERENCES "EquipmentSubcategories"(id)
);
 
CREATE TABLE "EquipmentPositions" (
    id SERIAL NOT NULL,
    station_id INTEGER NOT NULL,
    NUMBER INTEGER NOT NULL,
    equipment_subcategory_id INTEGER NOT NULL,
    angle DOUBLE PRECISION,
    height DOUBLE PRECISION NOT NULL,
    created_at TIMESTAMP WITH TIME zone NOT NULL,
    updated_at TIMESTAMP WITH TIME zone NOT NULL,
    CONSTRAINT "EquipmentPositions_pkey" PRIMARY KEY (id),
    CONSTRAINT "EquipmentPositions_station_id_number_equipment_subcategory__key" UNIQUE (station_id, NUMBER, equipment_subcategory_id),
    CONSTRAINT "EquipmentPositions_equipment_subcategory_id_fkey" FOREIGN KEY (equipment_subcategory_id) REFERENCES "EquipmentSubcategories"(id) ON UPDATE CASCADE,
    CONSTRAINT "EquipmentPositions_station_id_fkey" FOREIGN KEY (station_id) REFERENCES "Stations"(id) ON DELETE CASCADE,
);
 
CREATE TABLE "EquipmentSubcategories" (
    id SERIAL NOT NULL,
    title CHARACTER VARYING(150),
    not_operable BOOLEAN DEFAULT FALSE,
    not_transportable BOOLEAN DEFAULT FALSE,
    equipment_category_id INTEGER,
    inserted_by INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    icon INTEGER,
    CONSTRAINT equipment_pkey PRIMARY KEY (id),
    CONSTRAINT "EquipmentSubcategories_icon_fkey" FOREIGN KEY (icon) REFERENCES "NoVersionFile"(id) ON DELETE SET NULL,
    CONSTRAINT equipment_equipment_category_id_fkey FOREIGN KEY (equipment_category_id) REFERENCES "EquipmentCategories"(id),
    CONSTRAINT equipment_inserted_by_fkey FOREIGN KEY (inserted_by) REFERENCES "Institutions"(id)
);
 
CREATE TABLE "ExperimentResponsibles" (
    experiment_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT experiment_responsible_pkey PRIMARY KEY (experiment_id, user_id),
    CONSTRAINT experiment_responsible_experiment_id_fkey FOREIGN KEY (experiment_id) REFERENCES "Experiments"(id),
    CONSTRAINT experiment_responsible_person_id_fkey FOREIGN KEY (user_id) REFERENCES "Users"(id),
    CONSTRAINT institution_responsibles_institution_id_fkey FOREIGN KEY (institution_id) REFERENCES "Institutions"(id)
);
 
CREATE TABLE "Experiments" (
    id SERIAL NOT NULL,
    begin_date DATE,
    end_date DATE,
    title CHARACTER VARYING(100),
    disclaimer CHARACTER VARYING(200),
    objective text,
    objective_abstract text,
    short_title CHARACTER VARYING(100),
    contact_id INTEGER,
    location INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    y DOUBLE PRECISION,
    x DOUBLE PRECISION,
    "personalFolder" INTEGER,
    project_id INTEGER DEFAULT 1 NOT NULL,
    publish_date TIMESTAMP WITH TIME zone,
    photo INTEGER,
    maps text,
    useful text,
    "mapPhotoFolder" INTEGER,
    "mapFilesFolder" INTEGER,
    CONSTRAINT experiments_pkey1 PRIMARY KEY (id),
    CONSTRAINT "Experiments_fm_folder_id_fkey" FOREIGN KEY ("personalFolder") REFERENCES "Folder"(id),
    CONSTRAINT "Experiments_mapFilesFolder_fkey" FOREIGN KEY ("mapFilesFolder") REFERENCES "Folder"(id),
    CONSTRAINT "Experiments_mapPhotoFolder_fkey" FOREIGN KEY ("mapPhotoFolder") REFERENCES "Folder"(id),
    CONSTRAINT "Experiments_photo_fkey" FOREIGN KEY (photo) REFERENCES "NoVersionFile"(id) ON DELETE SET NULL,
    CONSTRAINT "Experiments_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id),
    CONSTRAINT location_foreign_idx FOREIGN KEY (location) REFERENCES public."Regions"(id),
    CONSTRAINT experiment_contact_fkey FOREIGN KEY (contact_id) REFERENCES "Users"(id) ON DELETE SET NULL
);
 
CREATE TABLE "VersionedFile" (
    id SERIAL NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    name CHARACTER VARYING(255),
    description text,
    folder_id INTEGER,
    permission "enum_VersionedFile_permission" DEFAULT 'public'::"enum_VersionedFile_permission" NOT NULL,
    user_id INTEGER,
    CONSTRAINT "FmFiles_pkey" PRIMARY KEY (id),
    CONSTRAINT "VersionedFile_idCreator_fkey" FOREIGN KEY (user_id) REFERENCES "Users"(id),
    CONSTRAINT folder_id_foreign_idx FOREIGN KEY (folder_id) REFERENCES "Folder"(id) ON DELETE CASCADE,
 
);
 
CREATE TABLE "Folder" (
    id SERIAL NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    name CHARACTER VARYING(255),
    folder_id INTEGER,
    user_id INTEGER,
    CONSTRAINT "FmFolders_pkey" PRIMARY KEY (id),
    CONSTRAINT folder_id_foreign_idx FOREIGN KEY (folder_id) REFERENCES "Folder"(id) ON DELETE CASCADE,
    CONSTRAINT "Folder_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "Users"(id)
);
 
CREATE TABLE "Institutions" (
    id SERIAL NOT NULL,
    NUMBER INTEGER NOT NULL,
    initials CHARACTER VARYING(20) NOT NULL,
    name CHARACTER VARYING(150) NOT NULL,
    parent_institution_id INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT partners_pkey PRIMARY KEY (id),
    CONSTRAINT p_partner_foreign_key FOREIGN KEY (parent_institution_id) REFERENCES "Institutions"(id)
);
 
CREATE TABLE "NoVersionFile" (
    id SERIAL NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    "mongoID" CHARACTER VARYING(255) NOT NULL,
    user_id INTEGER,
    name CHARACTER VARYING(255) NOT NULL,
    "desc" text,
    folder_id INTEGER,
    CONSTRAINT "NoVersionFile_mongoID_key" UNIQUE ("mongoID"),
    CONSTRAINT "NoVersionFile_pkey" PRIMARY KEY (id),
    CONSTRAINT "NoVersionFile_folder_id_fkey" FOREIGN KEY (folder_id) REFERENCES "Folder"(id),
    CONSTRAINT "NoVersionFile_idCreator_fkey" FOREIGN KEY (user_id) REFERENCES "Users"(id)
);
 
CREATE TABLE "ParticipationCapabilities" (
    participation_id INTEGER NOT NULL,
    capability_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "ParticipationCapabilities_capability_id_fkey" FOREIGN KEY (capability_id) REFERENCES "Capabilities"(id),
    CONSTRAINT "ParticipationCapabilities_participation_id_fkey" FOREIGN KEY (participation_id) REFERENCES "Participations"(id) ON DELETE CASCADE
);
 
CREATE TABLE "Participations" (
    id SERIAL NOT NULL,
    experiment_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "Participations_pkey" PRIMARY KEY (id),
    CONSTRAINT unique_participation UNIQUE (user_id, experiment_id),
    CONSTRAINT "Participations_experiment_id_fkey" FOREIGN KEY (experiment_id) REFERENCES "Experiments"(id),
    CONSTRAINT "Participations_role_id_fkey" FOREIGN KEY (role_id) REFERENCES roles(id),
    CONSTRAINT "Participations_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "Users"(id)
);
 
CREATE TABLE "Periods" (
    id SERIAL NOT NULL,
    participation_id INTEGER NOT NULL,
    "from" TIMESTAMP WITH TIME zone,
    "to" TIMESTAMP WITH TIME zone,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "Periods_pkey" PRIMARY KEY (id),
    CONSTRAINT "Periods_participation_id_fkey" FOREIGN KEY (participation_id) REFERENCES "Participations"(id) ON DELETE CASCADE
);
 
CREATE TABLE "PersonnelCategories" (
    id SERIAL NOT NULL,
    name CHARACTER VARYING(255),
    personnel_category_id INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT "PersonnelCategories_pkey" PRIMARY KEY (id),
    CONSTRAINT "PersonnelCategories_personnel_category_id_fkey" FOREIGN KEY (personnel_category_id) REFERENCES "PersonnelCategories"(id)
);
 
CREATE TABLE "Regions" (
    id SERIAL NOT NULL,
    country_id INTEGER,
    region CHARACTER VARYING(100),
    utm CHARACTER VARYING(150),
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT location_pkey PRIMARY KEY (id)
);
 
CREATE TABLE "SequelizeMeta" (
    name CHARACTER VARYING(255) NOT NULL,
    CONSTRAINT "SequelizeMeta_pkey" PRIMARY KEY (name)
);
 
CREATE TABLE "Sessions" (
    id SERIAL NOT NULL,
    sid CHARACTER VARYING(255) NOT NULL,
    DATA text,
    created_at TIMESTAMP WITH TIME zone NOT NULL,
    updated_at TIMESTAMP WITH TIME zone NOT NULL,
    CONSTRAINT "Sessions_pkey" PRIMARY KEY (id),
    CONSTRAINT "Sessions_sid_key" UNIQUE (sid)
);
 
CREATE TABLE "Stations" (
    id SERIAL NOT NULL,
    experiment_id INTEGER NOT NULL,
    coordinate_system CHARACTER VARYING(255) NOT NULL,
    acronym CHARACTER VARYING(255),
    NUMBER INTEGER NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    equipment_subcategory_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME zone NOT NULL,
    updated_at TIMESTAMP WITH TIME zone NOT NULL,
    "photosFolder" INTEGER,
    install_at DATE,
    provision_at DATE,
    deprovision_at DATE,
    uninstall_at DATE,
    "documentsFolder" INTEGER,
    details text,
    CONSTRAINT "Stations_pkey" PRIMARY KEY (id),
    CONSTRAINT "Stations_number_equipment_subcategory_id_key" UNIQUE (NUMBER, equipment_subcategory_id),
    CONSTRAINT "Stations_documentsFolder_fkey" FOREIGN KEY ("documentsFolder") REFERENCES "Folder"(id),
    CONSTRAINT "Stations_equipment_subcategory_id_fkey" FOREIGN KEY (equipment_subcategory_id) REFERENCES "EquipmentSubcategories"(id) ON UPDATE CASCADE,
    CONSTRAINT "Stations_experiment_id_fkey" FOREIGN KEY (experiment_id) REFERENCES "Experiments"(id) ON UPDATE CASCADE,
    CONSTRAINT "Stations_personalFolder_fkey" FOREIGN KEY ("photosFolder") REFERENCES "Folder"(id)
);
 
CREATE TABLE "Users" (
    id SERIAL NOT NULL,
    first_name CHARACTER VARYING(100) NOT NULL,
    last_name CHARACTER VARYING(100) NOT NULL,
    password CHARACTER VARYING(150),
    email CHARACTER VARYING(150) NOT NULL,
    url CHARACTER VARYING(255) NOT NULL,
    institution_id INTEGER,
    may_edit BOOLEAN DEFAULT FALSE,
    last_login TIMESTAMP WITHOUT TIME zone,
    may_edit_2 BOOLEAN DEFAULT FALSE,
    is_admin BOOLEAN DEFAULT FALSE,
    telephone CHARACTER VARYING(100),
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    is_curator BOOLEAN DEFAULT FALSE,
    photo_id INTEGER,
    cv_id INTEGER,
    CONSTRAINT personnel_pkey PRIMARY KEY (id),
    CONSTRAINT personnel_email_key UNIQUE (email),
    CONSTRAINT "Users_cv_id_fkey" FOREIGN KEY (cv_id) REFERENCES "NoVersionFile"(id) ON DELETE SET NULL,
    CONSTRAINT "Users_photo_id_fkey" FOREIGN KEY (photo_id) REFERENCES "NoVersionFile"(id),
    CONSTRAINT personnel_works_at_fkey FOREIGN KEY (institution_id) REFERENCES "Institutions"(id)
);
 
CREATE TABLE "Version" (
    id SERIAL NOT NULL,
    versioned_file_id INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    "mongoID" CHARACTER VARYING(255) NOT NULL,
    user_id INTEGER,
    CONSTRAINT "Version_pkey" PRIMARY KEY (id),
    CONSTRAINT "Version_mongoID_key" UNIQUE ("mongoID"),
    CONSTRAINT "Version_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "Users"(id),
    CONSTRAINT versioned_file_id_foreign_idx FOREIGN KEY (versioned_file_id) REFERENCES "VersionedFile"(id) ON DELETE CASCADE
);
 
CREATE TABLE "WindmodelCategories" (
    id SERIAL NOT NULL,
    title CHARACTER VARYING(150),
    windmodel_category_parent_id INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT models_categories_pkey PRIMARY KEY (id),
    CONSTRAINT valid_parent CHECK ((id <> windmodel_category_parent_id)),
    CONSTRAINT models_categories_model_category_parent_id_fkey FOREIGN KEY (windmodel_category_parent_id) REFERENCES "WindmodelCategories"(id)
);
 
CREATE TABLE "Windmodels" (
    id SERIAL NOT NULL,
    title CHARACTER VARYING(150) NOT NULL,
    model_category_id INTEGER NOT NULL,
    inserted_by INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT models_pkey PRIMARY KEY (id),
    CONSTRAINT models_inserted_by_fkey FOREIGN KEY (inserted_by) REFERENCES "Institutions"(id),
    CONSTRAINT models_model_category_id_fkey FOREIGN KEY (model_category_id) REFERENCES "WindmodelCategories"(id)
);
 
CREATE TABLE "WmsLayers" (
    id SERIAL NOT NULL,
    name CHARACTER VARYING(255) NOT NULL,
    url CHARACTER VARYING(255) NOT NULL,
    layer CHARACTER VARYING(255) DEFAULT '0'::CHARACTER VARYING,
    STYLE CHARACTER VARYING(255) DEFAULT 'default'::CHARACTER VARYING,
    experiment_id INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    show_by_default BOOLEAN DEFAULT TRUE,
    CONSTRAINT "WmsLayers_pkey" PRIMARY KEY (id),
    CONSTRAINT "WmsLayers_experiment_id_fkey" FOREIGN KEY (experiment_id) REFERENCES "Experiments"(id)
);
 
CREATE TABLE emails (
    id SERIAL NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    "to" CHARACTER VARYING(255) NOT NULL,
    cc CHARACTER VARYING(255),
    subject CHARACTER VARYING(255),
    body text NOT NULL,
    attached CHARACTER VARYING(255),
    sender INTEGER,
    CONSTRAINT emails_pkey PRIMARY KEY (id),
    CONSTRAINT emails_sender_fkey FOREIGN KEY (sender) REFERENCES "Users"(id) ON DELETE SET NULL
);
 
CREATE TABLE equipment_characteristics (
    equipment_characteristic_id SERIAL NOT NULL,
    equipment_id INTEGER NOT NULL,
    placeholder CHARACTER VARYING(150) NOT NULL,
    is_expandable BOOLEAN DEFAULT FALSE,
    CONSTRAINT equipment_characteristics_pkey PRIMARY KEY (equipment_characteristic_id),
    CONSTRAINT equipment_characteristics_equipment_id_fkey FOREIGN KEY (equipment_id) REFERENCES "EquipmentSubcategories"(id)
);
 
CREATE TABLE equipment_characteristics_enum_options (
    equipment_characteristics_enum_option_id SERIAL NOT NULL,
    equipment_characteristic_id INTEGER NOT NULL,
    title CHARACTER VARYING(150) NOT NULL,
    inserted_by INTEGER,
    CONSTRAINT equipment_characteristics_enum_options_pkey PRIMARY KEY (equipment_characteristics_enum_option_id),
    CONSTRAINT equipment_characteristics_enum_equipment_characteristic_id_fkey FOREIGN KEY (equipment_characteristic_id) REFERENCES equipment_characteristics(equipment_characteristic_id),
    CONSTRAINT equipment_characteristics_enum_options_inserted_by_fkey FOREIGN KEY (inserted_by) REFERENCES "Institutions"(id)
);
 
CREATE TABLE equipment_characteristics_number (
    equipment_characteristic_number_id SERIAL NOT NULL,
    equipment_characteristic_id INTEGER NOT NULL,
    units CHARACTER VARYING(100),
    CONSTRAINT equipment_characteristics_number_pkey PRIMARY KEY (equipment_characteristic_number_id),
    CONSTRAINT equipment_characteristics_numb_equipment_characteristic_id_fkey FOREIGN KEY (equipment_characteristic_id) REFERENCES equipment_characteristics(equipment_characteristic_id)
);
 
CREATE TABLE event_category (
    id SERIAL NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    name CHARACTER VARYING(255) NOT NULL,
    TYPE CHARACTER VARYING(255),
    icon CHARACTER VARYING(255),
    deleted_at TIMESTAMP WITH TIME zone,
    CONSTRAINT event_category_pkey PRIMARY KEY (id)
);
 
CREATE TABLE events (
    id SERIAL NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    "photosFolder" INTEGER,
    "documentsFolder" INTEGER,
    content text NOT NULL,
    title CHARACTER VARYING(255) NOT NULL,
    experiment_id INTEGER,
    user_id INTEGER,
    event_category_id INTEGER,
    hidden BOOLEAN DEFAULT FALSE,
    CONSTRAINT events_pkey PRIMARY KEY (id),
    CONSTRAINT "events_documentsFolder_fkey" FOREIGN KEY ("documentsFolder") REFERENCES "Folder"(id),
    CONSTRAINT events_event_category_id_fkey FOREIGN KEY (event_category_id) REFERENCES event_category(id),
    CONSTRAINT events_experimet_id_fkey FOREIGN KEY (experiment_id) REFERENCES "Experiments"(id),
    CONSTRAINT "events_photosFolder_fkey" FOREIGN KEY ("photosFolder") REFERENCES "Folder"(id),
    CONSTRAINT events_user_id_fkey FOREIGN KEY (user_id) REFERENCES "Users"(id)
);
 
CREATE TABLE institution_responsibles (
    user_id INTEGER NOT NULL,
    institution_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT institution_responsibles_pkey PRIMARY KEY (user_id, institution_id),
    CONSTRAINT institution_responsibles_user_id_fkey FOREIGN KEY (user_id) REFERENCES "Users"(id)
);
 
CREATE TABLE project_institutions (
    project_id INTEGER NOT NULL,
    institution_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    NUMBER INTEGER NOT NULL,
    CONSTRAINT project_institutions_pkey PRIMARY KEY (project_id, institution_id),
    CONSTRAINT project_institutions_institution_id_fkey FOREIGN KEY (institution_id) REFERENCES "Institutions"(id),
    CONSTRAINT project_institutions_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id)
);
 
CREATE TABLE project_responsibles (
    project_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT project_responsibles_pkey PRIMARY KEY (project_id, user_id),
    CONSTRAINT project_responsibles_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id,
    CONSTRAINT project_responsibles_user_id_fkey FOREIGN KEY (user_id) REFERENCES "Users"(id)
);
 
CREATE TABLE projects (
    id SERIAL NOT NULL,
    name CHARACTER VARYING(255) NOT NULL,
    "personalFolder" INTEGER,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    CONSTRAINT projects_pkey PRIMARY KEY (id),
    CONSTRAINT "projects_personalFolder_fkey" FOREIGN KEY ("personalFolder") REFERENCES "Folder"(id)
);
 
CREATE TABLE roles (
    id SERIAL NOT NULL,
    created_at TIMESTAMP WITH TIME zone,
    updated_at TIMESTAMP WITH TIME zone,
    name CHARACTER VARYING(255),
    CONSTRAINT roles_pkey PRIMARY KEY (id)
);
 
CREATE TABLE SESSION (
    sid CHARACTER VARYING NOT NULL,
    sess text NOT NULL,
    expire TIMESTAMP(6) WITHOUT TIME zone NOT NULL,
    CONSTRAINT session_pkey PRIMARY KEY (sid)
);
 
-- INDEXES ---------------------------------------------------------
 
CREATE UNIQUE INDEX equipment_model_reference ON "Devices" USING btree (serial_number, equipment_model_id);
 
CREATE UNIQUE INDEX project_number ON project_institutions USING btree (project_id, NUMBER);

With comments: windsp.sql_20170324.zip

windsp/db.txt · Last modified: 2017/06/16 10:10 by Correia Lopes