# Реалізація інформаційного та програмного забезпечення
# SQL-скрипт для створення та початкового наповнення бази даних
-- MySQL Script generated by MySQL Workbench
-- Fri May 3 11:54:48 2024
-- 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 utf8mb3 ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`action`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`action` ;
CREATE TABLE IF NOT EXISTS `mydb`.`action` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`description` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;
-- -----------------------------------------------------
-- Table `mydb`.`client`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`client` ;
CREATE TABLE IF NOT EXISTS `mydb`.`client` (
`id` CHAR(36) NOT NULL,
`login` VARCHAR(32) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`password` VARCHAR(40) NOT NULL,
`role_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_client_role1_idx` (`role_id` ASC) VISIBLE,
CONSTRAINT `fk_client_role1`
FOREIGN KEY (`role_id`)
REFERENCES `mydb`.`role` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;
-- -----------------------------------------------------
-- Table `mydb`.`mediadata`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`mediadata` ;
CREATE TABLE IF NOT EXISTS `mydb`.`mediadata` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` TEXT NOT NULL,
`fileType` VARCHAR(32) NOT NULL,
`metadata` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `mydb`.`permission`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`permission` ;
CREATE TABLE IF NOT EXISTS `mydb`.`permission` (
`id` INT NOT NULL,
`name` VARCHAR(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`permissions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`permissions` ;
CREATE TABLE IF NOT EXISTS `mydb`.`permissions` (
`id` INT NOT NULL,
`role_id` INT NOT NULL,
`permission_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_permissions_role1_idx` (`role_id` ASC) VISIBLE,
INDEX `fk_permissions_permission1_idx` (`permission_id` ASC) VISIBLE,
CONSTRAINT `fk_permissions_role1`
FOREIGN KEY (`role_id`)
REFERENCES `mydb`.`role` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_permissions_permission1`
FOREIGN KEY (`permission_id`)
REFERENCES `mydb`.`permission` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`request`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`request` ;
CREATE TABLE IF NOT EXISTS `mydb`.`request` (
`id` INT NOT NULL AUTO_INCREMENT,
`description` TEXT NULL DEFAULT NULL,
`datetime` DATETIME NOT NULL,
`action_id` INT NULL,
`mediadata_id` INT NULL,
`client_id` CHAR(36) NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_request_action1_idx` (`action_id` ASC) VISIBLE,
INDEX `fk_request_mediadata1_idx` (`mediadata_id` ASC) VISIBLE,
INDEX `fk_request_client1_idx` (`client_id` ASC) VISIBLE,
CONSTRAINT `fk_request_action1`
FOREIGN KEY (`action_id`)
REFERENCES `mydb`.`action` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_request_mediadata1`
FOREIGN KEY (`mediadata_id`)
REFERENCES `mydb`.`mediadata` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_request_client1`
FOREIGN KEY (`client_id`)
REFERENCES `mydb`.`client` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;
-- -----------------------------------------------------
-- Table `mydb`.`role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`role` ;
CREATE TABLE IF NOT EXISTS `mydb`.`role` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`description` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = armscii8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- Data for table `mydb`.`action`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'SingUp', 'Registration');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'Login', 'Authorisation');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'Help', 'User contact support');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'PasswordRecovery', 'User password recovery');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'DeleteRequest', 'Account deletion request');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'SearchRequest', 'Search for data');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'DataFilter', 'Data Filtering');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'Import', 'Loading data into the system');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'Export', 'Download data from the system');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'RequestHistory', 'User request history');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'AddSource', 'Add new source');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'DeleteSource', 'Delete data source');
INSERT INTO `mydb`.`action` (`id`, `name`, `description`) VALUES (DEFAULT, 'ProfileDelete', 'Delete account');
COMMIT;
-- -----------------------------------------------------
-- Data for table `mydb`.`role`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`role` (`id`, `name`, `description`) VALUES (DEFAULT, 'Guest', 'A temporary visitor');
INSERT INTO `mydb`.`role` (`id`, `name`, `description`) VALUES (DEFAULT, 'User', 'Person who uses a system or service');
INSERT INTO `mydb`.`role` (`id`, `name`, `description`) VALUES (DEFAULT, 'Admin', 'Person who has control over and responsibility for a system or network');
-- -----------------------------------------------------
-- Data for table `mydb`.`client`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`client` (`id`, `login`, `email`, `password`, `role_id`) VALUES ('78b810fc-d32e-4ed1-9152-1c005301ca79', 'Andrew', 'andrew@student.com', 'passandrew', 1);
INSERT INTO `mydb`.`client` (`id`, `login`, `email`, `password`, `role_id`) VALUES ('24a189c6-f27a-42cc-a253-f806b9bc479c', 'Alex', 'alex@student.com', 'passalex', 1);
INSERT INTO `mydb`.`client` (`id`, `login`, `email`, `password`, `role_id`) VALUES ('61ea5b43-f794-4299-b4f2-7e607269bed1', 'The God', 'maks@student.com', 'maxpass', 2);
INSERT INTO `mydb`.`client` (`id`, `login`, `email`, `password`, `role_id`) VALUES ('aaa060e9-4bb3-493e-852d-fa5286300889', 'Jan', 'jan2stud@gmail.com', 'pasJan', 1);
COMMIT;
COMMIT;
# RESTfull сервіс для управління даними
# Модуль запуску сервера
const express = require('express');
const dotenv = require('dotenv').config();
const app = express();
const userRoutes = require('./routes/ProfileManage');
const { handle404Errors, handleDevErrors, handleProdErrors } = require('./middlewares/errorHandlers');
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use('/', userRoutes);
app.use(handle404Errors);
app.use(handleDevErrors);
app.use(handleProdErrors);
app.listen(process.env.SERVER_PORT, () => {
console.log(`App is running on port ${process.env.SERVER_PORT}`);
});
# Модуль для з'єднання з базою даних
const mysql = require('mysql');
const dotenv = require('dotenv').config();
const db = mysql.createConnection({
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASS,
database: process.env.DB_NAME,
});
db.connect((err) => {
if (err) {
console.error('Error connecting to MySQL:', err);
return;
}
console.log('Connected to MySQL');
});
module.exports = db;
# Модуль маршрутів для забезпечення деякого менеджменту акаунтів
const express = require('express');
const router = express.Router();
const userController = require('../controllers/ProfileManage');
router.post('/users', userController.register); // Create a new user
router.post('/sessions', userController.login); // Create a new session (log in)
router.delete('/users/:id', userController.deleteRequest); // Delete a user
module.exports = router;
# Модуль контролерів для забезпечення деякого менеджменту акаунтів
const { v4: uuidv4 } = require('uuid');
const db = require('../db');
exports.register = (req, res) => {
const {login, email, password} = req.query;
const id = uuidv4(); // Generate a UUID
const role_id = 1; // Default role for new client
// Email validation
const emailRegex = /^[\w-]+(\.[\w-]+)*@([\w-]+\.)+[a-zA-Z]{2,7}$/;
if (!emailRegex.test(email)) {
return res.status(400)
.json({ error: 'SingUp.WrongEmail' });
}
// Password validation
if (password.length < 8) {
return res.status(400)
.json({ error: 'SingUp.SimplePass' });
}
var sql = "INSERT INTO client (id, login, email, password, role_id) VALUES(?, ?, ?, ?, ?)";
db.query(sql, [id, login, email, password, role_id], (err) => {
if (err) {
console.error('Error in SQL query:', err);
if (err.code === 'ER_DUP_ENTRY') {
return res.status(400)
.json({ error: 'BusyLogin' });
} else {
return res.status(500)
.json({ error: 'Internal Server Error' });
}
} else {
res.status(201)
.json({ message: 'Registered Successfully' });
}
});
};
exports.login = (req, res) => {
const {login, email, password} = req.query;
// Email validation
const emailRegex = /^[\w-]+(\.[\w-]+)*@([\w-]+\.)+[a-zA-Z]{2,7}$/;
if (!emailRegex.test(email)) {
return res.status(400)
.json({ error: 'Login.WrongEmail' });
}
var sql = "SELECT * FROM client WHERE login = ? AND email = ?";
db.query(sql, [login, email], (err, results) => {
if (err) {
console.error('Error in SQL query:', err);
return res.status(500)
.json({ error: 'Internal Server Error' });
} else {
if (results.length === 0) {
return res.status(400)
.json({ error: 'Login.AccDoesntExist' });
} else {
const user = results[0];
if (user.password !== password) {
return res.status(400)
.json({ error: 'Login.WrongPass' });
} else {
res.status(200)
.json({ message: 'Logged in Successfully' });
}
}
}
});
};
exports.deleteRequest = (req, res) => {
const {login, password} = req.query;
var sql = "SELECT * FROM client WHERE login = ?";
db.query(sql, [login], (err, results) => {
if (err) {
console.error('Error in SQL query:', err);
return res.status(500)
.json({ error: 'Internal Server Error' });
} else {
if (results.length === 0) {
return res.status(400)
.json({ error: 'DeleteRequest.AccDoesntExist' });
} else {
const user = results[0];
if (user.password !== password) {
return res.status(400)
.json({ error: 'DeleteRequest.WrongPass' });
} else {
var sql = `DELETE FROM client WHERE login = '${login}'`;
db.query(sql, (err) => {
if (err) {
console.error('Error in SQL query:', err);
return res.status(500)
.json({ error: 'Internal Server Error' });
} else {
res.status(200)
.json({ message: 'Account Deleted Successfully' });
}
});
}
}
}
});
};
# Модуль обробок помилок
// 404 Error Handler
function handle404Errors(req, res, next) {
const err = new Error('Not Found');
err.status = 404;
next(err);
}
// Development Error Handler
// Will print stacktrace
function handleDevErrors(err, req, res, next) {
if (req.app.get('env') !== 'development') return next(err);
res.status(err.status || 500);
res.json({
message: err.message,
error: err
});
}
// Production Error Handler
// No stacktraces leaked to user
function handleProdErrors(err, req, res, next) {
res.status(err.status || 500);
res.json({
message: err.message,
error: {}
});
}
module.exports = {
handle404Errors,
handleDevErrors,
handleProdErrors
};