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

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());

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

Підключення до бази данних

const db = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: '',
	database: 'mydb',
});

Роутер

app.get('/', (req, res) => {
	res.json('Connected to backend');
});

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

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

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

app.post('/users', (req, res) => {
	const q = 'INSERT INTO user (`id`, `nickname`, `email`, `password`, `isBlocked`) VALUES (?)';
	const values = [null, req.body.nickname, req.body.email, req.body.password, req.body.isBlocked];
	db.query(q, [values], (err, data) => {
		if (err) return res.json(err);
		return res.json('User registred successfuly');
	});
});

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

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

app.put('/users/:id', (req, res) => {
	const id = req.params.id;
	const q = 'UPDATE user SET `nickname`= ?, `email`= ?, `password`= ?, `isBlocked`= ? WHERE id = ?';

	const values = [req.body.nickname, req.body.email, req.body.password, req.body.isBlocked];

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