Реалізація інформаційного та програмного забезпечення

SQL-скрипт для створення на початкового наповнення бази даних

-- MySQL Script generated by MySQL Workbench
-- Fri Nov 10 19:30:08 2023
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`User` ;

CREATE TABLE IF NOT EXISTS `mydb`.`User` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nickname` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `avatar` VARCHAR(255) NULL,
  `blockStatus` TINYINT NULL,
  PRIMARY KEY (`id`));


-- -----------------------------------------------------
-- Table `mydb`.`Role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Role` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Role` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`));


-- -----------------------------------------------------
-- Table `mydb`.`Project`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Project` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Project` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL,
  `manager` VARCHAR(255) NOT NULL,
  `isArchived` TINYINT NULL,
  PRIMARY KEY (`id`));


-- -----------------------------------------------------
-- Table `mydb`.`Team`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Team` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Team` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `Project_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_Team_Project1`
    FOREIGN KEY (`Project_id`)
    REFERENCES `mydb`.`Project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_Team_Project1_idx` ON `mydb`.`Team` (`Project_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `mydb`.`Collaborator`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Collaborator` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Collaborator` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `User_id` INT NOT NULL,
  `Role_id` INT NOT NULL,
  `Team_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_Collaborator_User`
    FOREIGN KEY (`User_id`)
    REFERENCES `mydb`.`User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Collaborator_Role1`
    FOREIGN KEY (`Role_id`)
    REFERENCES `mydb`.`Role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Collaborator_Team1`
    FOREIGN KEY (`Team_id`)
    REFERENCES `mydb`.`Team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_Collaborator_User_idx` ON `mydb`.`Collaborator` (`User_id` ASC) VISIBLE;

CREATE INDEX `fk_Collaborator_Role1_idx` ON `mydb`.`Collaborator` (`Role_id` ASC) VISIBLE;

CREATE INDEX `fk_Collaborator_Team1_idx` ON `mydb`.`Collaborator` (`Team_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `mydb`.`Permission`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Permission` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Permission` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`));


-- -----------------------------------------------------
-- Table `mydb`.`Grant`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Grant` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Grant` (
  `Permission_id` INT NOT NULL,
  `Role_id` INT NOT NULL,
  CONSTRAINT `fk_Grant_Permission1`
    FOREIGN KEY (`Permission_id`)
    REFERENCES `mydb`.`Permission` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Grant_Role1`
    FOREIGN KEY (`Role_id`)
    REFERENCES `mydb`.`Role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_Grant_Permission1_idx` ON `mydb`.`Grant` (`Permission_id` ASC) VISIBLE;

CREATE INDEX `fk_Grant_Role1_idx` ON `mydb`.`Grant` (`Role_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `mydb`.`Task`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Task` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Task` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL,
  `deadline` DATETIME NULL,
  `priority` VARCHAR(45) NULL,
  `difficulty` VARCHAR(45) NULL,
  `Project_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_Task_Project1`
    FOREIGN KEY (`Project_id`)
    REFERENCES `mydb`.`Project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_Task_Project1_idx` ON `mydb`.`Task` (`Project_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `mydb`.`Assignment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Assignment` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Assignment` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `datetime` DATETIME NOT NULL,
  `Task_id` INT NOT NULL,
  `Collaborator_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_Assignment_Task1`
    FOREIGN KEY (`Task_id`)
    REFERENCES `mydb`.`Task` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Assignment_Collaborator1`
    FOREIGN KEY (`Collaborator_id`)
    REFERENCES `mydb`.`Collaborator` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_Assignment_Task1_idx` ON `mydb`.`Assignment` (`Task_id` ASC) VISIBLE;

CREATE INDEX `fk_Assignment_Collaborator1_idx` ON `mydb`.`Assignment` (`Collaborator_id` ASC) VISIBLE;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


-- Test Data

-- Adding into `mydb`.`Role`
INSERT INTO `mydb`.`Role` (`name`)
VALUES
    ('Administrator'),
    ('Team-lead'),
    ('Collaborator');

-- Adding into `mydb`.`Permission`
INSERT INTO `mydb`.`Permission` (`role`, `name`)
VALUES        
    ('Team-lead', 'EditProfile'),
    ('Team-lead', 'CreateTask'),
    ('Team-lead', 'EditTask'),
    ('Team-lead', 'DeleteTask'),            
    ('Team-lead', 'CreateProject'),
    ('Team-lead', 'EditProject'),
    ('Team-lead', 'DeleteProject'),    
    ('Team-lead', 'ArchiveProject'),    
    ('Team-laed', 'AddCollaborator'),
    ('Team-lead', 'DeleteCollaborator'),
        
    ('Collaborator', 'EditProfile'),
    ('Collaborator', 'CreateTask'),
    ('Collaborator', 'EditTask'),
    ('Collaborator', 'DeleteTask'),
    
    ('Administrator', 'AssignManager'),
    ('Administrator', 'BanUser'),
    ('Administrator', 'UnBanUser');
    ('Administrator', 'Support');

-- Adding into `mydb`.`Grant`
INSERT INTO `db_coursework`.`Grant` (`Permission_id`, `Role_id`)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),

    (2, 1),
    (2, 2),

    (3, 1),
    (3, 2),

    (4, 1),
    (4, 2),
    
    (5, 1),    

    (6, 1),    

    (7, 1),

    (8, 1),

    (9, 1),

    (10, 1),

    (11, 3),

    (12, 3),

    (13, 3),    

    (14, 3),    


-- Adding into `mydb`.`Project`
INSERT INTO `mydb`.`Project` (`name`, `description`, `manager`, `isArchived`)
VALUES
    ('Project_0', 'Description_0', 'Team-lead_0', 'false'),
    ('Project_1', 'Description_1', 'Team-lead_1', 'true'),

-- Додавання даних в таблицю `mydb`.`Team`
INSERT INTO `db_coursework`.`Team` (`name`, `Project_id`)
VALUES
    ('Team_0', 'Project_0'),
    ('Team_1', 'Project_1'),

-- Adding into `mydb`.`User`
INSERT INTO `db_coursework`.`User` (`nickname`, `email`, `password`, `avatar`, `blockStatus`)
VALUES
    ('User_0', 'user_0@mail.com', 'password_0', './photo', false),
    ('User_1', 'user_1@mail.com', 'password_1', './photo', true);

-- Adding into `mydb`.`Collaborator`
INSERT INTO `mydb`.`Collaborator` ( `User_id`, `Role_id`, `Team_id`)
VALUES
    ('0', '0', '0'),
    ('1', '1', '1'),


-- Adding into ` `mydb`.`Task`
INSERT INTO `mydb`.`Task` (`name`, `description`, `deadline`, `priority`, `difficulty`, `Project_id`)
VALUES
    ('Task_0', 'Description_0', '01-01-2023 12:00:00', 'High', 'Low', 'Project_0'),    
    ('Task_1``, 'Description_1', '01-01-2023 12:00:00', 'Low', 'High', 'Project_1'),    

-- Adding into `mydb`.`Assignment`
INSERT INTO `mydb`.`Assignment` (`datetime`, `Task_id`, `Collaborator_id`)
VALUES
    ('2023-01-01 12:00:00', 0, 0),
    ('2023-01-01 12:00:00', 1, 1),    


COMMIT;

RESTfull сервіс для управління даними

Головний файл index.js

import express from 'express';
import mysql from 'mysql';
import cors from 'cors';

const app = express();
app.use(cors());
app.use(express.json());

const db = mysql.createConnection({
    host: 'localhost', 
    user: 'root',      
    password: '',      
    database: 'mybd',  
  });
app.get('/', (req, res) => {
	res.json('Connected to backend');
});

app.get('/project', (req, res) => {
	const q = 'SELECT * FROM Project';
	db.query(q, (err, data) => {
		if (err) return res.json(err.message);
		return res.json(data);
	});
});

app.get('/project/:id', (req, res) => {
	const id = req.params.id;
	const q = 'SELECT * FROM Project WHERE id = ?';

	db.query(q, [id], (err, data) => {
		if (err) return res.json(err);
		return res.json(data);
	});
});

app.post('/project', (req, res) => {
	const q = 'INSERT INTO Project (`id`, `name`, `description`, `manager`, `isArchived`) VALUES (?)';
	const values = [null, req.body.name, req.body.description, req.body.manager, req.body.isArchived];
	db.query(q, [values], (err, data) => {
		if (err) return res.json(err);
		return res.json('Project registred successfuly');
	});
});

app.delete('/project/:id', (req, res) => {
	const id = req.params.id;
	const q = 'DELETE FROM project WHERE id = ?';

	db.query(q, [id], (err, data) => {
		if (err) return res.json(err);
		return res.json('Project deleted successfuly');
	});
});

app.put('/project/:id', (req, res) => {
	const id = req.params.id;
	const q = 'UPDATE Project SET `name`= ?, `description`= ?, `manager`= ?, `isArchived`= ? WHERE id = ?';

	const values = [req.body.name, req.body.description, req.body.manager, req.body.isArchived];

	db.query(q, [...values, id], (err, data) => {
		if (err) return res.send(err);
		return res.json('Updated successfuly');
	});
});

app.listen(8080, () => {
	console.log(`Connected on the port 8080 ~ http://localhost:8080/`);
});