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

zuka

こんにちは。zuka(@beginaid)です。

この記事は,Ecsiteを自作するシリーズになります。今回はデータベースを用意します。

その他のシリーズ記事は以下の目次をご覧ください。

目次

完成品

この記事では,以下のようなデータベースの作成を目指します。

「ユーザ表」「商品表」「注文表」の3つからなります。実際はもっと多くのサブスクリプションサービスを網羅するべきですが,今回は食に限定することにします。また,試作としてユーザ表には管理者1名とユーザ2名が登録済みだとしています。

ユーザ表

商品表

注文表

実行環境

  • Java: 11.0.10
  • Apache Tomcat: 9.0.44
  • OpenJDK: 15.0.2
  • JUnit: 5.7.1
  • Eclipse: 4.19.0
  • MySQL: 8.0.24
  • OS: Windows10

データベースとユーザの作成

最初にデータベースとユーザの作成をしてしまいましょう。データベース名は「ecsite」でユーザ名も「ecsite」にします。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;

テーブルの作成

「ユーザ表」「商品表」「注文表」を作成しましょう。それぞれ以下のような構成にします。

ユーザ表

スクロールできます
カラム名データ型説明主キー外部キー
user_idchar(8)ユーザID
user_namevarchar(32)ユーザ名
passwordvarchar(128)パスワード
emailvarchar(128)メールアドレス
rolevarchar(16)役割
is_deletebit(1)削除フラグ
usersテーブル

商品表

スクロールできます
カラム名データ型説明主キー外部キー
item_idchar(8)商品ID
item_namevarchar(128)商品名
item_urlvarchar(128)商品URL
image_urlvarchar(128)商品画像URL
priceint unsigned価格
unitvarchar(16)価格単位
tagvarchar(16)タグ
is_deletebit(1)削除フラグ
itemsテーブル

注文表

スクロールできます
カラム名データ型説明主キー外部キー
item_idchar(8)商品ID
user_idchar(8)ユーザID
order_idbigint unsigned注文ID
order_datetimestamp注文日
is_canceledbit(1)キャンセルフラグ
ordersテーブル

bit(1)というのは,語弊を恐れずに言えば,booleanの上位互換的なデータ型と思っていただければOKです。何をもって上位互換とするかは難しいところですが,今回はバグを引き起こさないという観点で見ています。

SQLのbooleanはtinyint型なので,true/falseのバイナリにならない可能性があり,バグを引き起こすタネになってしまいます。そこで,bit(1)を利用することで,あらゆる値を1/0,すなわちtrue/falseに変えてしまおうという発想です。数値型の0,bool型のfalse,2進数のb”0″以外は全てtrueになるような仕様だと思います。

実装

実際にSQL文を実行していきましょう。

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";

注意事項

本記事ではパスワードを平文で保存していますが,応用的には最低限ハッシュ関数などを利用する必要があります。しかし,ここでは簡単のため平文でパスワードを保存するものとして話を進めています。

よかったらシェアしてね!

コメント

コメントする

目次
閉じる