· 7 years ago · Nov 07, 2018, 10:22 PM
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 AUTO_INCREMENT,
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` ENUM ('TYPE_NEW','TYPE_EXCHANGE','TYPE_REPLACE','TYPE_RENEW') NOT NULL,
54 `RequestStatus` ENUM('REQUEST_NEW','REQUEST_APPROVED','REQUEST_DISAPPROVED','REQUEST_WAITING') NOT NULL,
55 `RequestDate` VARCHAR(20) NOT NULL,
56 `RequestReason` ENUM('REASON_LOST','REASON_STOLEN','REASON_MALFUNCTIONING','REASON_DAMAGED',
57 'REASON_ADDRESS_CHANGE','REASON_NAME_CHANGE','REASON_PHOTO_CHANGE') NULL,
58 `Requests_UserID` INT(11) NOT NULL,
59 `Requests_AttachmentID` INT(11) NOT NULL,
60 PRIMARY KEY (`RequestID`),
61 INDEX `UserID_idx` (`Requests_UserID` ASC),
62 INDEX `fk_requests_attatchemts1_idx` (`Requests_AttachmentID` ASC),
63 CONSTRAINT `UserID`
64 FOREIGN KEY (`Requests_UserID`)
65 REFERENCES `finalproject`.`users` (`UserID`),
66 CONSTRAINT `fk_requests_attatchemts1`
67 FOREIGN KEY (`Requests_AttachmentID`)
68 REFERENCES `finalproject`.`attachments` (`AttachmentID`)
69 ON DELETE NO ACTION
70 ON UPDATE NO ACTION)
71ENGINE = InnoDB
72AUTO_INCREMENT = 294
73DEFAULT CHARACTER SET = utf8;
74
75
76-- -----------------------------------------------------
77-- Table `finalproject`.`roles`
78-- -----------------------------------------------------
79CREATE TABLE IF NOT EXISTS `finalproject`.`roles` (
80 `RoleID` INT(11) NOT NULL,
81 `RoleName` VARCHAR(45) NULL DEFAULT NULL,
82 PRIMARY KEY (`RoleID`))
83ENGINE = InnoDB
84DEFAULT CHARACTER SET = utf8;
85
86
87-- -----------------------------------------------------
88-- Table `finalproject`.`user_info`
89-- -----------------------------------------------------
90CREATE TABLE IF NOT EXISTS `finalproject`.`user_info` (
91 `UserInfoID` INT(11) NOT NULL AUTO_INCREMENT,
92 `FirstName` VARCHAR(256) NOT NULL,
93 `FirstNameCyrillic` VARCHAR(256) NOT NULL,
94 `LastName` VARCHAR(30) NOT NULL,
95 `LastNameCyrillic` VARCHAR(45) NOT NULL,
96 `PersonalNumber` VARCHAR(20) NOT NULL,
97 `Address` VARCHAR(100) NOT NULL,
98 `PhoneNumber` VARCHAR(20) NOT NULL,
99 `DateOfBirth` VARCHAR(25) NOT NULL,
100 `EuIssuerCountry` VARCHAR(25) NULL DEFAULT NULL,
101 `TcardNumber` VARCHAR(20) NULL DEFAULT NULL,
102 `DriverLicenseIssuerCountry` VARCHAR(35) NULL DEFAULT NULL,
103 `DriverLicenseNumber` VARCHAR(20) NULL DEFAULT NULL,
104 `CountryOfIssuing` VARCHAR(35) NULL DEFAULT NULL,
105 `IssuingAuthority` VARCHAR(45) NULL DEFAULT NULL,
106 `DateOfExpiryPrevious` VARCHAR(10) NULL DEFAULT NULL,
107 `UserInfo_UserID` INT(11) NOT NULL,
108 `Email` VARCHAR(256) NOT NULL,
109 PRIMARY KEY (`UserInfoID`),
110 INDEX `fk_user_info_users1_idx` (`UserInfo_UserID` ASC),
111 CONSTRAINT `fk_user_info_users1`
112 FOREIGN KEY (`UserInfo_UserID`)
113 REFERENCES `finalproject`.`users` (`UserID`)
114 ON DELETE NO ACTION
115 ON UPDATE NO ACTION)
116ENGINE = InnoDB
117DEFAULT CHARACTER SET = utf8;
118
119
120-- -----------------------------------------------------
121-- Table `finalproject`.`user_role`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `finalproject`.`user_role` (
124 `UserID` INT(11) NOT NULL,
125 `RoleID` INT(11) NOT NULL,
126 PRIMARY KEY (`UserID`, `RoleID`),
127 CONSTRAINT `FK_RoleID`
128 FOREIGN KEY (`RoleID`)
129 REFERENCES `finalproject`.`roles` (`RoleID`),
130 CONSTRAINT `FK_UserID`
131 FOREIGN KEY (`UserID`)
132 REFERENCES `finalproject`.`users` (`UserID`))
133ENGINE = InnoDB
134DEFAULT CHARACTER SET = utf8;
135
136-- Insert part
137INSERT INTO `roles` (`RoleID`, `RoleName`)
138VALUES
139(1,'ADMIN'), (2, 'USER');
140INSERT INTO `users` (`UserID`, `Username`, `Password`)
141VALUES
142 (1,'alexanderrusev','$2a$04$v0HDZi99MrlDsm6Hx92TjOP7CDlV9UTaKTaqhohWrcfpd7YUGfQlu'),
143(2,'randomuser','$2a$04$R1PhPLjVBz2LqsfCVLi0ROAK/mIFetmDCNDZSrGz4aM6SZJq6nTo6');
144INSERT INTO `user_role` (`UserID`, `RoleID`)
145VALUES (1,1), (1,2), (2, 2);
146-- End of insert part
147
148SET SQL_MODE=@OLD_SQL_MODE;
149SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
150SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;