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

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

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

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

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

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

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

Реалізація доступу до бази даних

Файл ApplicationDbContext.cs

using Microsoft.EntityFrameworkCore;
using WebAPI.Models;

namespace WebAPI;

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> dbContext) : base(dbContext)
    {
    }
}

Файл UserRepository.cs

using Microsoft.EntityFrameworkCore;
using WebAPI.Interfaces;
using WebAPI.Models;

namespace WebAPI.Repositories;

public class UserRepository : IUserRepository
{
    private readonly ApplicationDbContext _dbContext;

    public UserRepository(ApplicationDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public IQueryable<User> GetAllUsers()
    {
        return _dbContext.Users;
    }

    public User GetUserById(int id)
    {
        return _dbContext.Users.FirstOrDefault(user => user.Id == id);
    }

    public void AddUser(User user)
    {
        _dbContext.Users.Add(user);
    }

    public void UpdateUser(User user)
    {
        _dbContext.Users.Update(user);
    }

    public void DeleteUser(User user)
    {
        _dbContext.Users.Remove(user);
    }

    public int SaveChanges()
    {
        return _dbContext.SaveChanges();
    }
}

Файл UserService.cs

using WebAPI.Interfaces;
using WebAPI.Models;

namespace WebAPI.Services;

public class UserService : IUserService
{
    private readonly IUserRepository _userRepository;

    public UserService(IUserRepository userRepository)
    {
        _userRepository = userRepository;
    }

    public IQueryable<User> GetAllUsers()
    {
        return _userRepository.GetAllUsers();
    }

    public User GetUserById(int id)
    {
        return _userRepository.GetUserById(id);
    }

    public void AddUser(User user)
    {
        _userRepository.AddUser(user);
    }

    public void UpdateUser(User user)
    {
        _userRepository.UpdateUser(user);
    }

    public void DeleteUser(User user)
    {
        _userRepository.DeleteUser(user);
    }

    public int SaveChanges()
    {
        return _userRepository.SaveChanges();
    }
}

Файл UserController.cs

using Microsoft.AspNetCore.Mvc;
using WebAPI.Interfaces;
using WebAPI.Models;

namespace WebAPI.Controllers;

[ApiController]
[Route("api/[controller]")]
public class UserController : ControllerBase
{
    private readonly IUserService _userService;

    public UserController(IUserService userService)
    {
        _userService = userService;
    }

    [HttpGet]
    [Route("[action]")]
    public ActionResult<IEnumerable<User>> GetAll()
    {
        IEnumerable<User> users = _userService.GetAllUsers();
        if (users is null) return NotFound("Users not found");
        return Ok(users);
    }

    [HttpGet("{id}")]
    public ActionResult<User> Get(int id)
    {
        User user = _userService.GetUserById(id);
        if (user is null) return NotFound("User not found.");
        return Ok(user);
    }

    [HttpDelete]
    public ActionResult Delete(User user)
    {
        try
        {
            _userService.DeleteUser(user);
            _userService.SaveChanges();
        }
        catch (Exception)
        {
            return BadRequest("Bad request.");
        }
        
        return Ok("User was deleted.");
    }

    [HttpPost]
    public ActionResult Post(User user)
    {
        try
        {
            _userService.AddUser(user);
            _userService.SaveChanges();
        }
        catch (Exception)
        {
            return BadRequest("Bad request.");
        }

        return Ok("User was added.");
    }
    
    [HttpPut]
    public ActionResult Put(User user)
    {
        try
        {
            _userService.UpdateUser(user);
            _userService.SaveChanges();
        }
        catch (Exception)
        {
            return BadRequest("Bad request.");
        }

        return Ok("User was updated.");
    }
}

Файл Program.cs

using Microsoft.EntityFrameworkCore;
using WebAPI;
using WebAPI.Interfaces;
using WebAPI.Repositories;
using WebAPI.Services;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    string connectionString = builder.Configuration.GetConnectionString("DatabaseCS");
    MySqlServerVersion serverVersion = new MySqlServerVersion(new Version(5, 7, 32));
    options.UseMySql(connectionString, serverVersion);
});

builder.Services.AddScoped<IUserRepository, UserRepository>();
builder.Services.AddScoped<IUserService, UserService>();

builder.Services.AddSwaggerGen();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseDeveloperExceptionPage();

    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseStatusCodePages();
app.MapDefaultControllerRoute();

app.Run();