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

В рамках проекту розробляється:

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

-- MySQL Script generated by MySQL Workbench
-- Fri May 12 18:14:59 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 JustChill
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `JustChill` ;

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

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

CREATE TABLE IF NOT EXISTS `JustChill`.`Role` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(180) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `JustChill`.`Grant` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action` VARCHAR(45) NOT NULL,
  `Role` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Grant_Role_idx` (`Role` ASC) VISIBLE,
  CONSTRAINT `fk_Grant_Role`
    FOREIGN KEY (`Role`)
    REFERENCES `JustChill`.`Role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `JustChill`.`Project` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(180) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `JustChill`.`Team` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `motto` VARCHAR(100) NOT NULL,
  `Project` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Team_Project1_idx` (`Project` ASC) VISIBLE,
  CONSTRAINT `fk_Team_Project1`
    FOREIGN KEY (`Project`)
    REFERENCES `JustChill`.`Project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `JustChill`.`Ban`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `JustChill`.`Ban` ;

CREATE TABLE IF NOT EXISTS `JustChill`.`Ban` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `datetime` DATETIME NOT NULL,
  `reason` VARCHAR(180) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `JustChill`.`User` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nickname` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `photo` JSON NULL,
  `Ban` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_User_Ban1_idx` (`Ban` ASC) VISIBLE,
  CONSTRAINT `fk_User_Ban1`
    FOREIGN KEY (`Ban`)
    REFERENCES `JustChill`.`Ban` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `JustChill`.`Member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `JustChill`.`Member` ;

CREATE TABLE IF NOT EXISTS `JustChill`.`Member` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Role` INT NOT NULL,
  `Team` INT NULL,
  `Project` INT NOT NULL,
  `User` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Member_Role1_idx` (`Role` ASC) VISIBLE,
  INDEX `fk_Member_Team1_idx` (`Team` ASC) VISIBLE,
  INDEX `fk_Member_Project1_idx` (`Project` ASC) VISIBLE,
  INDEX `fk_Member_User1_idx` (`User` ASC) VISIBLE,
  CONSTRAINT `fk_Member_Role1`
    FOREIGN KEY (`Role`)
    REFERENCES `JustChill`.`Role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Member_Team1`
    FOREIGN KEY (`Team`)
    REFERENCES `JustChill`.`Team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Member_Project1`
    FOREIGN KEY (`Project`)
    REFERENCES `JustChill`.`Project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Member_User1`
    FOREIGN KEY (`User`)
    REFERENCES `JustChill`.`User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `JustChill`.`Sprint`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `JustChill`.`Sprint` ;

CREATE TABLE IF NOT EXISTS `JustChill`.`Sprint` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `goal` VARCHAR(100) NOT NULL,
  `startdate` DATETIME NULL,
  `enddate` DATETIME NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `JustChill`.`Tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `JustChill`.`Tag` ;

CREATE TABLE IF NOT EXISTS `JustChill`.`Tag` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(180) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `JustChill`.`Task` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(180) NULL,
  `deadline` DATETIME NULL,
  `Sprint` INT NULL,
  `Tag` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Task_Sprint1_idx` (`Sprint` ASC) VISIBLE,
  INDEX `fk_Task_Tag1_idx` (`Tag` ASC) VISIBLE,
  CONSTRAINT `fk_Task_Sprint1`
    FOREIGN KEY (`Sprint`)
    REFERENCES `JustChill`.`Sprint` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Task_Tag1`
    FOREIGN KEY (`Tag`)
    REFERENCES `JustChill`.`Tag` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `JustChill`.`Assignment` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `datatime` DATETIME NULL,
  `Member` INT NOT NULL,
  `Task` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Assignment_Member1_idx` (`Member` ASC) VISIBLE,
  INDEX `fk_Assignment_Task1_idx` (`Task` ASC) VISIBLE,
  CONSTRAINT `fk_Assignment_Member1`
    FOREIGN KEY (`Member`)
    REFERENCES `JustChill`.`Member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Assignment_Task1`
    FOREIGN KEY (`Task`)
    REFERENCES `JustChill`.`Task` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `JustChill`.`Tast_comment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `JustChill`.`Tast_comment` ;

CREATE TABLE IF NOT EXISTS `JustChill`.`Tast_comment` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `subject` VARCHAR(45) NOT NULL,
  `text` VARCHAR(45) NOT NULL,
  `datetime` DATETIME NULL,
  `Author` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Tast_comment_Member1_idx` (`Author` ASC) VISIBLE,
  CONSTRAINT `fk_Tast_comment_Member1`
    FOREIGN KEY (`Author`)
    REFERENCES `JustChill`.`Member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `JustChill`.`View_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `JustChill`.`View_type` ;

CREATE TABLE IF NOT EXISTS `JustChill`.`View_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(180) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `JustChill`.`View`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `JustChill`.`View` ;

CREATE TABLE IF NOT EXISTS `JustChill`.`View` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `datatime` DATETIME NULL,
  `Project` INT NOT NULL,
  `View_type` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_View_Project1_idx` (`Project` ASC) VISIBLE,
  INDEX `fk_View_View_type1_idx` (`View_type` ASC) VISIBLE,
  CONSTRAINT `fk_View_Project1`
    FOREIGN KEY (`Project`)
    REFERENCES `JustChill`.`Project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_View_View_type1`
    FOREIGN KEY (`View_type`)
    REFERENCES `JustChill`.`View_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

-- -----------------------------------------------------
-- Data for table `JustChill`.`Role`
-- -----------------------------------------------------
START TRANSACTION;
USE `JustChill`;
INSERT INTO `JustChill`.`Role` (`id`, `name`, `description`) VALUES (1, 'Collaborator', NULL);
INSERT INTO `JustChill`.`Role` (`id`, `name`, `description`) VALUES (2, 'Teamlead', NULL);
INSERT INTO `JustChill`.`Role` (`id`, `name`, `description`) VALUES (3, 'Administrator', NULL);
INSERT INTO `JustChill`.`Role` (`id`, `name`, `description`) VALUES (4, 'ProjectManager', NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `JustChill`.`User`
-- -----------------------------------------------------
START TRANSACTION;
USE `JustChill`;
INSERT INTO `JustChill`.`User` (`id`, `nickname`, `email`, `password`, `photo`, `Ban`) VALUES (DEFAULT, 'ChVictoria', 'butterflylight01@gmail.com', 'dd555G_1212', NULL, NULL);
INSERT INTO `JustChill`.`User` (`id`, `nickname`, `email`, `password`, `photo`, `Ban`) VALUES (DEFAULT, 'VladHrabuk', 'hrabuk.vlad2004@gmail.com', 'bbvvddVV121@', NULL, NULL);
INSERT INTO `JustChill`.`User` (`id`, `nickname`, `email`, `password`, `photo`, `Ban`) VALUES (DEFAULT, 'Djekichoid', 'ponomarchuck.evgenyi@gmail.com', 'aaaddd423_VD', NULL, NULL);
INSERT INTO `JustChill`.`User` (`id`, `nickname`, `email`, `password`, `photo`, `Ban`) VALUES (DEFAULT, 'kkulesh', 'kateryna.kulesh@gmail.com ', 'adfvDD321@22', NULL, NULL);
INSERT INTO `JustChill`.`User` (`id`, `nickname`, `email`, `password`, `photo`, `Ban`) VALUES (DEFAULT, 'LizaMartynuyk', 'lizamartynuyk10@gmail.com', 'mvdhAaA!1102', NULL, NULL);
INSERT INTO `JustChill`.`User` (`id`, `nickname`, `email`, `password`, `photo`, `Ban`) VALUES (DEFAULT, 'baidykova', 'annbaidikova2053@gmail.com', 'yytTl598_YAL77', NULL, NULL);
INSERT INTO `JustChill`.`User` (`id`, `nickname`, `email`, `password`, `photo`, `Ban`) VALUES (DEFAULT, 'dovefoke', 'DoveFoke@gmail.com', 'qrtyrA!sd4412A', NULL, NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `JustChill`.`Tag`
-- -----------------------------------------------------
START TRANSACTION;
USE `JustChill`;
INSERT INTO `JustChill`.`Tag` (`id`, `name`, `description`) VALUES (1, 'InReview', NULL);
INSERT INTO `JustChill`.`Tag` (`id`, `name`, `description`) VALUES (2, 'Done', NULL);
INSERT INTO `JustChill`.`Tag` (`id`, `name`, `description`) VALUES (3, 'InProgres', NULL);
INSERT INTO `JustChill`.`Tag` (`id`, `name`, `description`) VALUES (4, 'ToDo', NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `JustChill`.`View_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `JustChill`;
INSERT INTO `JustChill`.`View_type` (`id`, `name`, `description`) VALUES (1, 'Dashboard', NULL);
INSERT INTO `JustChill`.`View_type` (`id`, `name`, `description`) VALUES (2, 'Backlog', NULL);
INSERT INTO `JustChill`.`View_type` (`id`, `name`, `description`) VALUES (3, 'Kanban', NULL);
INSERT INTO `JustChill`.`View_type` (`id`, `name`, `description`) VALUES (4, 'Scrum', NULL);
INSERT INTO `JustChill`.`View_type` (`id`, `name`, `description`) VALUES (5, 'Roadmap', NULL);

COMMIT;

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

import fastapi
from fastapi import FastAPI, Request
from fastapi.responses import JSONResponse
import pymysql

app = FastAPI()


class DataBase(object):

    def __new__(cls):
        if not hasattr(cls, 'instance'):
            cls.instance = super(DataBase, cls).__new__(cls)
        return cls.instance

    def __init__(self):
        self.connection = None
        self.cursor = None
        self.__connect()


    def __connect(self):
        self.connection = pymysql.connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            password='',
            database='justchill',
        )
        self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)

    def execute(self, command):
        self.cursor.execute(command)
        result = self.cursor.fetchall()
        self.connection.commit()
        return result


@app.get("/api/allusers")
async def get_users():
    db = DataBase()
    return JSONResponse(db.execute('SELECT * FROM User'))


@app.get('/api/user/{id}')
def get_user_by_id(id):
    db = DataBase()
    result = db.execute(f'SELECT * FROM User WHERE id={id}')
    if not result:
        raise fastapi.HTTPException(status_code=404)
    return JSONResponse(result)


@app.post('/api/adduser', status_code=201)
async def add_new_user(req: Request):
    req_dict = await req.json()
    try:
        email = req_dict['email']
        password = req_dict['password']
        nickname = req_dict['nickname']
        id_ban = req_dict['Ban']
        print(req_dict['Ban'])
    except:
        raise fastapi.HTTPException(status_code=400)
    db = DataBase()
    db.execute(f"INSERT INTO `User`(`email`, `password`, `nickname`, `Ban`) VALUES ('{email}','{password}','{nickname}',{id_ban});")
    return {'message': 'New user added!'}


@app.put('/api/updateuser/{id}')
async def update_user(id, req: Request):
    req_dict = await req.json()
    db = DataBase()
    for key in req_dict:
         if not db.execute(f'SELECT * FROM User WHERE id={id}'):
            raise fastapi.HTTPException(status_code=404)
         db.execute(f'UPDATE User SET {key}="{req_dict[key]}" WHERE id={id}')
    return {"message": 'Updated!'}


@app.delete('/api/deleteuser/{id}')
def delete(id):
    db = DataBase()
    if not db.execute(f'SELECT * FROM User WHERE id={id}'):
        raise fastapi.HTTPException(status_code=404)
    db.execute(f'DELETE FROM `User` WHERE id={id}')
    return {'message': f'User with id={id} deleted'}
    ```