a0hwN7uH

1CREATE DATABASE IF NOT EXISTS ICQ;
2USE ICQ;
3
4CREATE TABLE Users (
5    user_id INT,
6    user_login varchar(50) NOT null unique,
7    user_password varchar(50) not null,
8    user_email varchar(30),
9    PRIMARY KEY (user_id)
10);
11CREATE TABLE Messages (
12    message_id INT,
13    message_from INT NOT null,
14    message_to INT NOT null,
15    message_body varchar(150),
16    message_date date,
17	message_not_read bool,
18    primary key (message_id),
19    FOREIGN KEY (message_from)
20        REFERENCES Users (user_id)
21        ON UPDATE NO ACTION ON DELETE NO ACTION
22);
23CREATE TABLE Permissions (
24    permission_id INT,
25    permission_title varchar(10),
26    primary key (permission_id)
27);
28CREATE TABLE Users_To_Permissions (
29    user_id INT,
30    permission_id INT,
31    primary key (user_id , permission_id),
32    FOREIGN KEY (user_id)
33        REFERENCES Users (user_id)
34        ON UPDATE NO ACTION ON DELETE NO ACTION,
35    FOREIGN KEY (permission_id)
36        REFERENCES Permissions (permission_id)
37        ON UPDATE NO ACTION ON DELETE NO ACTION
38);
39 
40#DELIMITER $$
41#DROP TRIGGER IF EXISTS ICQ.Messages_BINS$$
42#USE `ICQ`$$
43#CREATE DEFINER=`root`@`localhost` TRIGGER `Messages_BINS` BEFORE INSERT ON `Messages` FOR EACH ROW
44#BEGIN
45# DECLARE user_id INT;
46# SET user_id=(select utp.user_id from Users_To_Permissions utp where utp.user_id=NEW.message_from);
47# IF user_id IS NULL THEN
48#      SIGNAL sqlstate '45001' SET message_text = "Невозможно выполнить операцию. У пользователя нет прав!";
49# END IF;
50#END$$
51#DELIMITER ;
52 
53 
54INSERT INTO Permissions values(1,'Admin');
55INSERT INTO Permissions values(2,'Moderator');
56INSERT INTO Permissions values(3,'User');
57 
58INSERT INTO Users values(1,'User1','1234','u1@gmail.com');
59INSERT INTO Users values(2,'User2','1234','u2@gmail.com');
60INSERT INTO Users values(3,'User3','1234','u1@gmail.com');
61INSERT INTO Users values(4,'User4','1234','u1@gmail.com');
62INSERT INTO Users values(5,'User5','1234','u1@gmail.com');
63INSERT INTO Users values(6,'Admin1','1234','a1@gmail.com');
64
65INSERT INTO Users_To_Permissions values(1,3),(2,3),(3,3),(4,3),(5,3),(6,1);
66INSERT INTO Messages values(1,1,2,"HI! 1",NOW(),true);
67INSERT INTO Messages values(2,1,2,"HI! 2",NOW(),true);
68INSERT INTO Messages values(3,1,2,"HI! 3",NOW(),true);
69INSERT INTO Messages values(4,1,2,"HI! 4",NOW(),false);
70INSERT INTO Messages values(5,1,2,"HI! 5",NOW(),true);
71INSERT INTO Messages values(6,1,2,"HI! 6",NOW(),false);
72
73INSERT INTO Messages values(7,2,4,"HI!",NOW(),true);
74INSERT INTO Messages values(8,2,4,"HI!",NOW(),true);
75INSERT INTO Messages values(9,2,4,"HI!",NOW(),false);
76INSERT INTO Messages values(10,2,4,"HI!",NOW(),true);
77
78INSERT INTO Messages values(11,3,1,"HI!",NOW(),true);
79INSERT INTO Messages values(12,3,1,"HI!",NOW(),true);
80INSERT INTO Messages values(13,3,1,"HI!",NOW(),true);
81INSERT INTO Messages values(14,3,1,"HI!",NOW(),true);
82INSERT INTO Messages values(15,3,1,"HI!",NOW(),true);
83
84INSERT INTO Messages values(16,5,4,"HI!",NOW(),false);
85INSERT INTO Messages values(17,5,4,"HI!",NOW(),false);
86INSERT INTO Messages values(18,3,1,"HI!",NOW(),true);