· 6 years ago · May 27, 2019, 01:30 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 Fog
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema Fog
13-- -----------------------------------------------------
14DROP SCHEMA IF EXISTS `Fog`;
15CREATE SCHEMA IF NOT EXISTS `Fog` DEFAULT CHARACTER SET utf8 ;
16USE `Fog` ;
17
18-- -----------------------------------------------------
19-- Table `Fog`.`category`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `Fog`.`category` (
22 `id_category` INT(11) NOT NULL,
23 `category_name` VARCHAR(45) NOT NULL,
24 PRIMARY KEY (`id_category`))
25ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Table `Fog`.`materials`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `Fog`.`materials` (
32 `id_material` INT(11) AUTO_INCREMENT,
33 `description` VARCHAR(200) NOT NULL,
34 `height` INT(11) NULL,
35 `width` INT(11) NULL,
36 `length` INT(11) NULL,
37 `price` DOUBLE NOT NULL,
38 `unit` VARCHAR(45) NOT NULL,
39 `id_category` INT(11) NOT NULL,
40 PRIMARY KEY (`id_material`),
41 INDEX `id_category_idx` (`id_category` ASC) ,
42 CONSTRAINT `id_category`
43 FOREIGN KEY (`id_category`)
44 REFERENCES `Fog`.`category` (`id_category`)
45 ON DELETE NO ACTION
46 ON UPDATE NO ACTION)
47ENGINE = InnoDB;
48
49/*
50-- -----------------------------------------------------
51-- Table `Fog`.`order_details_category`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `Fog`.`order_details_category` (
54 `id_order_details_category` INT(11) NOT NULL,
55 `details_category_name` VARCHAR(45) NOT NULL,
56 PRIMARY KEY (`id_order_details_category`))
57ENGINE = InnoDB;
58*/
59
60-- -----------------------------------------------------
61-- Table `Fog`.`customers`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `Fog`.`customers` (
64 `id_customer` INT(11) NOT NULL AUTO_INCREMENT,
65 `customer_name` VARCHAR(45) NOT NULL,
66 `phone` VARCHAR(45) NOT NULL,
67 `email` VARCHAR(45) NOT NULL,
68 PRIMARY KEY (`id_customer`))
69ENGINE = InnoDB;
70
71
72-- -----------------------------------------------------
73-- Table `Fog`.`roles`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `Fog`.`roles` (
76 `id_role` INT(11) NOT NULL,
77 `role` VARCHAR(45) NOT NULL,
78 PRIMARY KEY (`id_role`, `role`))
79ENGINE = InnoDB;
80
81-- -----------------------------------------------------
82-- Table `Fog`.`employees`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `Fog`.`employees` (
85 `id_employee` INT(11) NOT NULL AUTO_INCREMENT,
86 `username` VARCHAR(45) NOT NULL,
87 `name` VARCHAR(45) NOT NULL,
88 `role` VARCHAR(45) NOT NULL,
89 `password` VARCHAR(45) NOT NULL,
90 PRIMARY KEY (`id_employee`),
91 UNIQUE INDEX `role_UNIQUE` (`role` ASC),
92 CONSTRAINT `employees_ibfk_1`
93 FOREIGN KEY (`role`)
94 REFERENCES `fog`.`roles` (`id_role`))
95ENGINE = InnoDB;
96
97
98
99-- -----------------------------------------------------
100-- Table `Fog`.`orders`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `Fog`.`orders` (
103 `id_order` INT (11) AUTO_INCREMENT,
104 `status` VARCHAR(45) NOT NULL,
105 `order_width` INT(11) NOT NULL,
106 `order_length` INT(11) NOT NULL,
107 `order_width_shed` INT(11) DEFAULT NULL,
108 `order_length_shed` INT(11) DEFAULT NULL,
109 `incline` INT(11) NOT NULL,
110 `id_customer` INT(11) NOT NULL,
111 `id_employee` INT(11) NOT NULL,
112 `price` DOUBLE NOT NULL,
113-- `Date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
114 PRIMARY KEY (`id_order`),
115 INDEX `id_customer_idx` (`id_customer` ASC) ,
116 INDEX `id_employee_idx` (`id_employee` ASC) ,
117 CONSTRAINT `id_customer`
118 FOREIGN KEY (`id_customer`)
119 REFERENCES `Fog`.`customers` (`id_customer`)
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION,
122 CONSTRAINT `id_employee`
123 FOREIGN KEY (`id_employee`)
124 REFERENCES `Fog`.`employees` (`id_employee`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION)
127ENGINE = InnoDB;
128
129
130-- -----------------------------------------------------
131-- Table `Fog`.`order_details`
132-- -----------------------------------------------------
133-- CREATE TABLE IF NOT EXISTS `Fog`.`order_details` (
134-- `id_order_detail` INT(11) NOT NULL,
135/* `id_material` INT(11) NOT NULL,
136 `id_order` INT(11) NOT NULL,
137 `id_order_detail_category` INT(11) NOT NULL,
138 PRIMARY KEY (`id_order_detail`),
139 INDEX `id_material_idx` (`id_material` ASC) ,
140 INDEX `id_order_idx` (`id_order` ASC) ,
141 INDEX `id_order_detail_category_idx` (`id_order_detail_category` ASC) ,
142 CONSTRAINT `id_material`
143 FOREIGN KEY (`id_material`)
144 REFERENCES `Fog`.`materials` (`id_material`)
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION,
147 CONSTRAINT `id_order`
148 FOREIGN KEY (`id_order`)
149 REFERENCES `Fog`.`orders` (`id_order`)
150 ON DELETE NO ACTION
151 ON UPDATE NO ACTION,
152 CONSTRAINT `id_order_detail_category`
153 FOREIGN KEY (`id_order_detail_category`)
154 REFERENCES `Fog`.`order_details_category` (`id_order_details_category`)
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION)
157ENGINE = InnoDB;
158*/
159
160CREATE
161 ALGORITHM = UNDEFINED
162 DEFINER = `testuser2`@`%`
163 SQL SECURITY DEFINER
164VIEW `Fog`.`users` AS
165 SELECT
166 `E`.`id_employee` AS `id_employee`,
167 `E`.`username` AS `username`,
168 `E`.`name` AS `name`,
169 `R`.`role` AS `role`
170 FROM
171 (`Fog`.`employees` `E`
172 JOIN `Fog`.`roles` `R` ON ((`E`.`role` = `R`.`id_role`)));
173
174INSERT INTO `Fog`.`category` VALUES(1,'Skruer');
175INSERT INTO `Fog`.`category` VALUES(2,'Brædder');
176INSERT INTO `Fog`.`category` VALUES(3,'Beslag');
177INSERT INTO `Fog`.`category` VALUES(4,'Hængsler');
178INSERT INTO `Fog`.`category` VALUES(5,'Tagbeklædning');
179INSERT INTO `Fog`.`category` VALUES(6, 'Misc');
180INSERT INTO customers VALUES(1, 'john', '22548884','simonkruse2@gmail.com');
181INSERT INTO roles VALUES(1, 'admin');
182INSERT INTO roles VALUES(2, 'salesperson');
183INSERT INTO roles VALUES(3, 'picker');
184INSERT INTO employees VALUES(1,'carl123', 'Carl', 1, MD5('hund'));
185
186
187INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('plastmo bundskruer 200 stk.', 'Pakke',250,1);
188INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('hulbånd 1x20 mm. 10 mtr.','Rulle',750,1);
189INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('universal 190 mm. højre','Stk',400,1);
190INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('universal 190 mm. venstre', 'Stk', 800,1);
191INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('4,5 x 60 mm. skruer 200 stk.', 'Pakke',1000,1);
192INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('4,0 x 60 mm. beslagskruer 250 stk.', 'Pakke',300,1);
193INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('bræddebolt 10 x 120 mm.', 'Stk',600,1);
194INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('firkantskiver 40 x 40 x 11 mm', 'Stk',150,1);
195INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('4,5 x 70 mm. skruer 400 stk.', 'Pakke',280,1);
196INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('4,5 x 50 mm. skruer 300 stk.', 'Pakke',350,1);
197
198INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('25x200 mm. trykimp. Brædt','Stk',300,2);
199INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('25x200 mm. trykimp. Brædt','Stk',500,2);
200INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('25x125 mm. trykimp. Brædt','Stk',180,2);
201INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('25x125 mm. trykimp. Brædt','Stk',980,2);
202INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('19x100 mm. trykimp. Brædt','Stk',130,2);
203INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('19x100 mm. trykimp. Brædt','Stk',380,2);
204INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('19x100 mm. trykimp. Brædt','Stk',410,2);
205INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('38x73 mm. Lægte ubh.','Stk',1000,2);
206INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x95 mm. Reglar ubh.','Stk',330,2);
207INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x95 mm. Reglar ubh.','Stk',200,2);
208INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x195 mm. spærtræ ubh.','Stk',710,2);
209INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x195 mm. spærtræ ubh.','Stk',990,2);
210INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x195 mm. spærtræ ubh.','Stk',190,2);
211INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('97x97 mm. trykimp. stolpe','Stk',710,2);
212
213INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('Vinkelbeslag 35 mm','Stk',200,3);
214
215INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('stalddørsgreb 50 x 75 mm.','Sæt',150,6);
216INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('t hængsel 390 mm.','Stk',100,4);
217INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('Plastmo Exolite blåtonet', 'Stk',905,5);
218INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('Plastmo Exolite blåtonet.', 'Stk', 390,5);
219
220
221
222SET SQL_MODE=@OLD_SQL_MODE;
223SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
224SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
225
226USE `Fog`;