【1.3 自作Ecsite連載】データベースを用意する

DROP DATABASE IF EXISTS ecsite;
CREATE DATABASE ecsite DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'ecsite'@localhost IDENTIFIED BY 'password';
GRANT ALL ON ecsite.* TO 'ecsite'@localhost;
flush privileges;

USE ecsite;

/* Tableがもしあれば削除しておく */
DROP TABLE IF EXISTS contents;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS stocks;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS users;

/* Table作成 */
-- ユーザー
CREATE TABLE users
(
	user_id char(8),
	user_name varchar(32),
	password varchar(128),
	email varchar(128),
	role varchar(16),
	is_delete bit(1) DEFAULT false,
	PRIMARY KEY (user_id)
);

-- 商品
CREATE TABLE items
(
	item_id char(8),
	item_name varchar(128),
	item_url varchar(128),
	image_url varchar(128),
	price int unsigned,
	unit varchar(16),
	label varchar(16),
	description varchar(1024),
	is_delete bit(1) DEFAULT false,
	PRIMARY KEY (item_id)
);

-- 注文
CREATE TABLE orders
(
	item_id char(8),
	user_id char(8),
	order_id bigint unsigned,
	order_date timestamp DEFAULT current_timestamp,
	is_canceled bit(1) DEFAULT false,
	PRIMARY KEY (order_id)
);

/* 外部キーと紐づける */
ALTER TABLE orders
	ADD FOREIGN KEY (item_id)
	REFERENCES items (item_id)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT;

ALTER TABLE orders
	ADD FOREIGN KEY (user_id)
	REFERENCES users (user_id)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT;

INSERT INTO users VALUES ("A0000001","admin","password","admin@abc.de.f","administrator",false);
INSERT INTO users VALUES ("C0000001","customer1","password","customer1@abc.de.f","user",false);
INSERT INTO users VALUES ("C0000002","customer2","password","customer2@abc.de.f","user",false);

INSERT INTO items VALUES ("00000001","PostCoffee","https://postcoffee.co/","/images/thumnail.png",1598,"月","飲み物","こだわりコーヒーの定期便オーダーメイドコーヒーBOX",false);
INSERT INTO items VALUES ("00000002","snaq.me","https://snaq.me/","/images/thumnail.png",1980,"月","食べ物","ワクワクおやつの定期便おやつ体験BOX",false);
INSERT INTO items VALUES ("00000003","fradis","https://firadis.net/","/images/thumnail.png",5000,"月","酒","フィラディスのワインは絶対に外さない",false);
INSERT INTO items VALUES ("00000004","saketaku","https://www.saketaku.com/","/images/thumnail.png",6578,"月","酒","プロ厳選の美味しい日本酒",false);
INSERT INTO items VALUES ("00000005","よなよなの里","https://yonasato.com/","/images/thumnail.png",39720,"年","酒","クラフトビールをもっと楽しむ！",false);

update items set is_delete = true where item_id = "00000003";