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

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

SQL-скрипт

-- MySQL Script generated by MySQL Workbench
-- Sun Dec 25 07:19:21 2022
-- 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`.`answer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`answer` ;

CREATE TABLE IF NOT EXISTS `mydb`.`answer` (
  `user_id` INT NOT NULL,
  `text` TEXT NOT NULL,
  `data` DATE NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  `answer_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_selectedOption_id_idx` (`answer_id` ASC) VISIBLE,
  INDEX `fk_user_id_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_selectedOption_id`
    FOREIGN KEY (`answer_id`)
    REFERENCES `mydb`.`selectedOption` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


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

CREATE TABLE IF NOT EXISTS `mydb`.`category` (
  `category_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`category_id`));


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

CREATE TABLE IF NOT EXISTS `mydb`.`option` (
  `type` TEXT(255) NOT NULL,
  `text` TEXT(255) NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  `question_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_question_id_idx` (`question_id` ASC) VISIBLE,
  CONSTRAINT `fk_question_id`
    FOREIGN KEY (`question_id`)
    REFERENCES `mydb`.`question` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


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

CREATE TABLE IF NOT EXISTS `mydb`.`question` (
  `type` TEXT(255) NOT NULL,
  `text` TEXT(255) NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  `topic` TEXT(255) NOT NULL,
  `quiz_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_quiz_id_idx` (`quiz_id` ASC) VISIBLE,
  CONSTRAINT `fk_quiz_id`
    FOREIGN KEY (`quiz_id`)
    REFERENCES `mydb`.`quiz` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


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

CREATE TABLE IF NOT EXISTS `mydb`.`quiz` (
  `type` TEXT(255) NOT NULL,
  `text` TEXT(255) NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  `topic` TEXT(255) NOT NULL,
  `date` DATETIME NOT NULL,
  `creator_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_creator_id_idx` (`creator_id` ASC) VISIBLE,
  CONSTRAINT `fk_creator_id`
    FOREIGN KEY (`creator_id`)
    REFERENCES `mydb`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


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

CREATE TABLE IF NOT EXISTS `mydb`.`selectedOption` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `option_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_option_id_idx` (`option_id` ASC) VISIBLE,
  CONSTRAINT `fk_option_id`
    FOREIGN KEY (`option_id`)
    REFERENCES `mydb`.`option` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


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

CREATE TABLE IF NOT EXISTS `mydb`.`user` (
  `username` VARCHAR(16) NOT NULL,
  `mail` VARCHAR(255) NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`));


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

REST-full сервіс для управління даними

Файл підключення до бази данних

const mysql = require("mysql2");

const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root123",
  database: "mydb",
});

connection.connect((err) => {
  if (err) console.log("Not connected :(");
  else console.log("Connected successfully!");
});

module.exports = connection;

Кореневий файл серверу

const express = require("express");
const bodyParser = require("body-parser");
const connection = require("./database");

const app = express();
const port = process.env.PORT || 8080;

app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

const routes = require("./routes");
routes(app);

app.listen(port, () => {
  console.log(`App listen on port ${port}`);
});

Файл роутера

"use strict";

module.exports = (app) => {
  const users = require("./controller");

  app.route("/users").get(users.getAll);
  app.route("/user/:id").get(users.get);
  app.route("/users/add").post(users.add);
  app.route("/user/:id").patch(users.update);
  app.route("/user/:id").delete(users.delete);
};

Файл обробників запитів

"use strict";

const db = require("./database");

exports.getAll = (req, res) => {
  const query = `SELECT * FROM user`;
  db.query(query, (err, result) => {
    if (err) return res.status(500).json(err);
    if (result.length === 0) return res.sendStatus(404);
    res.status(200).json(result);
  });
};

exports.get = (req, res) => {
  const query = `SELECT * FROM user WHERE id=${req.params.id}`;
  db.query(query, (err, result) => {
    if (err) return res.status(500).json(err);
    if (result.length === 0) return res.sendStatus(404);
    res.status(200).json(result[0]);
  });
};

exports.add = (req, res) => {
  const { username, mail, id } = req.body;
  if (!(username && mail && id))
    return res
      .status(400)
      .json({ message: "Username, mail and id are required" });
  db.query(`SELECT * FROM user WHERE id=${id}`, (err, result) => {
    if (err) return res.status(500).json(err);
    if (result.length !== 0)
      return res.status(404).json(`User with id ${id} exists`);
    const query =
      "INSERT INTO `user`(`username`, `mail`, `id`) VALUES('" +
      username +
      "', '" +
      mail +
      "', '" +
      id +
      "')";
    db.query(query, (err, result) => {
      if (err) return res.status(500).json(err);
      res.status(201).json(`User with id ${id} was created`);
    });
  });
};

exports.update = (req, res) => {
  const { id } = req.params;
  const { username, mail } = req.body;
  if (!(username || mail))
    return res.status(400).json({ message: "Required username or mail" });
  db.query(`SELECT * FROM user WHERE id=${id}`, (err, result) => {
    if (err) return res.status(500).json(err);
    if (result.length === 0)
      return res.status(404).json("No user with this id");
  });
  let query = "";
  if (username) {
    query = `UPDATE user SET username='${username}' WHERE id=${id}`;
    db.query(query, (err) => {
      if (err) return res.status(500).json(err);
    });
  }
  if (mail) {
    query = `UPDATE user SET username='${mail}' WHERE id=${id}`;
    db.query(query, (err) => {
      if (err) return res.status(500).json(err);
    });
  }
  res.status(200).json({ message: `User with id ${id} was updated` });
};

exports.delete = (req, res) => {
  const { id } = req.params;
  if (!id) return res.status(400).json({ message: "Id required" });
  const query = `DELETE FROM user WHERE id=${id}`;
  db.query(`SELECT * FROM user WHERE id=${id}`, (err, result) => {
    if (err) return res.status(500).json(err);
    if (result.length === 0)
      return res.status(404).json("No user with this id");
    db.query(query, (err, result) => {
      if (err) return res.status(500).json(err);
      res.status(200).json(`User with id ${id} was deleted`);
    });
  });
};