4bVfs60J

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @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';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema finalproject
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema finalproject
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `finalproject` DEFAULT CHARACTER SET utf8 ;
18USE `finalproject` ;
19
20-- -----------------------------------------------------
21-- Table `finalproject`.`attachments`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `finalproject`.`attachments` (
24  `AttachmentID` INT(11) NOT NULL,
25  `Faceshot` LONGTEXT NOT NULL,
26  `IDShot` LONGTEXT NOT NULL,
27  `Signature` LONGTEXT NOT NULL,
28  `DriverLicense` LONGTEXT NOT NULL,
29  `PreviousTachCard` LONGTEXT NULL DEFAULT NULL,
30  `PreviousEUCard` LONGTEXT NULL DEFAULT NULL,
31  PRIMARY KEY (`AttachmentID`))
32ENGINE = InnoDB
33DEFAULT CHARACTER SET = utf8;
34
35
36-- -----------------------------------------------------
37-- Table `finalproject`.`users`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `finalproject`.`users` (
40  `UserID` INT(11) NOT NULL AUTO_INCREMENT,
41  `Username` VARCHAR(254) NOT NULL,
42  `Password` VARCHAR(256) NOT NULL,
43  PRIMARY KEY (`UserID`))
44ENGINE = InnoDB
45DEFAULT CHARACTER SET = utf8;
46
47
48-- -----------------------------------------------------
49-- Table `finalproject`.`requests`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `finalproject`.`requests` (
52  `RequestID` INT(11) NOT NULL AUTO_INCREMENT,
53  `RequestType` TINYINT(2) NOT NULL,
54  `RequestStatus` TINYINT(1) NOT NULL,
55  `RequestDate` VARCHAR(20) NOT NULL,
56  `Requests_UserID` INT(11) NOT NULL,
57  `Requests_AttachmentID` INT(11) NOT NULL,
58  PRIMARY KEY (`RequestID`),
59  INDEX `UserID_idx` (`Requests_UserID` ASC),
60  INDEX `fk_requests_attatchemts1_idx` (`Requests_AttachmentID` ASC),
61  CONSTRAINT `UserID`
62    FOREIGN KEY (`Requests_UserID`)
63    REFERENCES `finalproject`.`users` (`UserID`),
64  CONSTRAINT `fk_requests_attatchemts1`
65    FOREIGN KEY (`Requests_AttachmentID`)
66    REFERENCES `finalproject`.`attachments` (`AttachmentID`)
67    ON DELETE NO ACTION
68    ON UPDATE NO ACTION)
69ENGINE = InnoDB
70AUTO_INCREMENT = 294
71DEFAULT CHARACTER SET = utf8;
72
73
74-- -----------------------------------------------------
75-- Table `finalproject`.`roles`
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS `finalproject`.`roles` (
78  `RoleID` INT(11) NOT NULL,
79  `RoleName` VARCHAR(45) NULL DEFAULT NULL,
80  PRIMARY KEY (`RoleID`))
81ENGINE = InnoDB
82DEFAULT CHARACTER SET = utf8;
83
84
85-- -----------------------------------------------------
86-- Table `finalproject`.`user_info`
87-- -----------------------------------------------------
88CREATE TABLE IF NOT EXISTS `finalproject`.`user_info` (
89  `UserInfoID` INT(11) NOT NULL,
90  `FirstName` VARCHAR(256) NOT NULL,
91  `FirstNameCyrillic` VARCHAR(256) NOT NULL,
92  `LastName` VARCHAR(30) NOT NULL,
93  `LastNameCyrillic` VARCHAR(45) NOT NULL,
94  `PersonalNumber` VARCHAR(20) NOT NULL,
95  `Address` VARCHAR(100) NOT NULL,
96  `PhoneNumber` VARCHAR(20) NOT NULL,
97  `DateOfBirth` VARCHAR(25) NOT NULL,
98  `RequestReason` SMALLINT(3) NULL DEFAULT NULL,
99  `EuIssuerCountry` VARCHAR(25) NULL DEFAULT NULL,
100  `TcardNumber` VARCHAR(20) NULL DEFAULT NULL,
101  `DriverLicenseIssuerCountry` VARCHAR(35) NULL DEFAULT NULL,
102  `DriverLicenseNumber` VARCHAR(20) NULL DEFAULT NULL,
103  `CountryOfIssuing` VARCHAR(35) NULL DEFAULT NULL,
104  `IssuingAuthority` VARCHAR(45) NULL DEFAULT NULL,
105  `DateOfExpiryPrevious` VARCHAR(10) NULL DEFAULT NULL,
106  `UserInfo_UserID` INT(11) NOT NULL,
107  `Email` VARCHAR(256) NOT NULL,
108  PRIMARY KEY (`UserInfoID`),
109  INDEX `fk_user_info_users1_idx` (`UserInfo_UserID` ASC),
110  CONSTRAINT `fk_user_info_users1`
111    FOREIGN KEY (`UserInfo_UserID`)
112    REFERENCES `finalproject`.`users` (`UserID`)
113    ON DELETE NO ACTION
114    ON UPDATE NO ACTION)
115ENGINE = InnoDB
116DEFAULT CHARACTER SET = utf8;
117
118
119-- -----------------------------------------------------
120-- Table `finalproject`.`user_role`
121-- -----------------------------------------------------
122CREATE TABLE IF NOT EXISTS `finalproject`.`user_role` (
123  `UserID` INT(11) NOT NULL,
124  `RoleID` INT(11) NOT NULL,
125  PRIMARY KEY (`UserID`, `RoleID`),
126  CONSTRAINT `FK_RoleID`
127    FOREIGN KEY (`RoleID`)
128    REFERENCES `finalproject`.`roles` (`RoleID`),
129  CONSTRAINT `FK_UserID`
130    FOREIGN KEY (`UserID`)
131    REFERENCES `finalproject`.`users` (`UserID`))
132ENGINE = InnoDB
133DEFAULT CHARACTER SET = utf8;
134
135-- Insert part
136INSERT INTO `roles` (`RoleID`, `RoleName`)
137VALUES
138(1,'ADMIN'), (2, 'USER');
139INSERT INTO `users` (`UserID`, `Username`, `Password`)
140VALUES
141	(1,'alexanderrusev','$2a$04$v0HDZi99MrlDsm6Hx92TjOP7CDlV9UTaKTaqhohWrcfpd7YUGfQlu'),
142(2,'randomuser','$2a$04$R1PhPLjVBz2LqsfCVLi0ROAK/mIFetmDCNDZSrGz4aM6SZJq6nTo6');
143INSERT INTO `user_role` (`UserID`, `RoleID`)
144VALUES (1,1), (1,2), (2, 2);
145-- End of insert part
146
147SET SQL_MODE=@OLD_SQL_MODE;
148SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
149SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;