-
Notifications
You must be signed in to change notification settings - Fork 554
/
basedata.sql
65 lines (55 loc) · 2.43 KB
/
basedata.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# SQL configs
SET SQL_MODE ='IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
# create database and use it
CREATE DATABASE IF NOT EXISTS ecommjava;
USE ecommjava;
# create the category table
CREATE TABLE IF NOT EXISTS CATEGORY(
category_id int unique key not null auto_increment primary key,
name varchar(255) null
);
# insert default categories
INSERT INTO CATEGORY(name) VALUES ('Fruits'),
('Vegetables'),
('Meat'),
('Fish'),
('Dairy'),
('Bakery'),
('Drinks'),
('Sweets'),
('Other');
# create the customer table
CREATE TABLE IF NOT EXISTS CUSTOMER(
id int unique key not null auto_increment primary key,
address varchar(255) null,
email varchar(255) null,
password varchar(255) null,
role varchar(255) null,
username varchar(255) null,
UNIQUE (username)
);
# insert default customers
INSERT INTO CUSTOMER(address, email, password, role, username) VALUES
('123, Albany Street', 'admin@nyan.cat', '123', 'ROLE_ADMIN', 'admin'),
('765, 5th Avenue', 'lisa@gmail.com', '765', 'ROLE_NORMAL', 'lisa');
# create the product table
CREATE TABLE IF NOT EXISTS PRODUCT(
product_id int unique key not null auto_increment primary key,
description varchar(255) null,
image varchar(255) null,
name varchar(255) null,
price int null,
quantity int null,
weight int null,
category_id int null,
customer_id int null
);
# insert default products
INSERT INTO PRODUCT(description, image, name, price, quantity, weight, category_id) VALUES
('Fresh and juicy', 'https://freepngimg.com/save/9557-apple-fruit-transparent/744x744', 'Apple', 3, 40, 76, 1),
('Woops! There goes the eggs...', 'https://www.nicepng.com/png/full/813-8132637_poiata-bunicii-cracked-egg.png', 'Cracked Eggs', 1, 90, 43, 9);
# create indexes
CREATE INDEX FK7u438kvwr308xcwr4wbx36uiw
ON PRODUCT (category_id);
CREATE INDEX FKt23apo8r9s2hse1dkt95ig0w5
ON PRODUCT (customer_id);