Web 開発再入門 #9 ― DDL 文、DML 文（初期データ投入）

fmockup

はじめに

データベースにユーザーや表（テーブル）を作成し、それらの表に初期データを投入します。

フォルダー・ファイル構成

D:\
└ Developments\
    └ Workspace\
         └ fmockup\
             ├ build\
             ├ sql\ ← ココ
             │ ├ fmockup_ddl.bat ← コレ
             │ ├ fmockup_ddl.sql ← コレ
             │ ├ fmockup_dml.bat ← コレ
             │ └ fmockup_dml.sql ← コレ
             ├ src\
             ├ vue-vite\
             └ WinSW.NET-nnn\

ファイルの文字コード、改行コード

多分、UTF-8、Windows 改行（CR LF）が無難と思います。
文字コードの混乱を防ぐために、mysql.exe の実行オプションに文字コード “utf8” を指定しました。

ファイルの作成

フォルダー “D:\Developments\workspace\fmockup\sql” を作成する。

テキスト・エディターで、ファイル “fmockup_ddl.bat”、“fmockup_ddl.sql”、“fmockup_dml.bat”、“fmockup_dml.sql” を作成する。

fmockup_ddl.bat

@echo off
echo fmockup - DDL
echo root password is "********"
call "C:\Program Files\MySQL\MySQL Server 8.1\bin\mysql.exe" -u root -p --default-character-set=utf8 < ./fmockup-ddl.sql
if not %ERRORLEVEL% equ 0 pause

fmockup_ddl.sql

・・・

-- Role

DROP ROLE IF EXISTS FMOCKUP_ROLE;
CREATE ROLE FMOCKUP_ROLE;

-- User

DROP USER IF EXISTS 'FMOCKUP_USER'@'localhost';
CREATE USER 'FMOCKUP_USER'@'localhost' IDENTIFIED BY 'Asdf1234' DEFAULT ROLE FMOCKUP_ROLE;

-- Schema

DROP SCHEMA IF EXISTS FMOCKUP;
CREATE SCHEMA FMOCKUP;

-- Tables
--
-- Prefix 'M_' means Maser Data Table
-- Prefix 'S_' means System Fix Value Table
-- Prefix 'T_' meas Transaction Data Table

-- M_USER_LIST

CREATE TABLE FMOCKUP.M_USER_LIST (
	USER_ID						CHAR(26)		NOT NULL UNIQUE,
	USER_NAME					VARCHAR(32)		NOT NULL UNIQUE,
	PASSWORD_AES				VARCHAR(416)	NOT NULL, 									-- 32
	REMARK						VARCHAR(768)	NOT NULL,									-- 128 * 6 = 768
	ROLE_F						CHAR(1)			NOT NULL DEFAULT 'U',
	USER_LOCKED_F				CHAR(1)			NOT NULL DEFAULT 'Y',
	FAILED_COUNT				INTEGER			NOT NULL DEFAULT 0,
	LAST_LOGIN_DATE				CHAR(19)		NOT NULL,
	LAST_CHANGE_PASSWORD_DATE	CHAR(19)		NOT NULL,
	ADD_USER_DATE				CHAR(19)		NOT NULL,
	MODIFY_USER_DATE			CHAR(19)		NOT NULL,
	CREATE_DATE					CHAR(26)		NOT NULL,
	UPDATE_DATE					CHAR(26)		NOT NULL,									-- for Transaciton Control
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( USER_ID, USER_NAME )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.M_USER_LIST TO FMOCKUP_ROLE;

-- M_ROLE_LIST

CREATE TABLE FMOCKUP.M_ROLE_LIST (
	ROLE_ID						CHAR(26)		NOT NULL UNIQUE,
	ROLE_NAME					VARCHAR(32)		NOT NULL UNIQUE,
	REMARK						VARCHAR(768)	NOT NULL,									-- 128 * 6 = 768
	ROLE_F						CHAR(1)			NOT NULL DEFAULT 'U',
	ADD_ROLE_DATE				CHAR(19)		NOT NULL,
	MODIFY_ROLE_DATE			CHAR(19)		NOT NULL,
	CREATE_DATE					CHAR(26)		NOT NULL,
	UPDATE_DATE					CHAR(26)		NOT NULL,									-- for Transaciton Control
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( ROLE_ID, ROLE_NAME )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.M_ROLE_LIST TO FMOCKUP_ROLE;

-- M_USER_ROLE_LIST

CREATE TABLE FMOCKUP.M_USER_ROLE_LIST (
	USER_ID						CHAR(26)		NOT NULL,
	ROLE_ID						CHAR(26)		NOT NULL,
	CREATE_DATE					CHAR(26)		NOT NULL,
	UPDATE_DATE					CHAR(26)		NOT NULL,									-- for Transaciton Control
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( USER_ID, ROLE_ID )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.M_USER_ROLE_LIST TO FMOCKUP_ROLE;

-- M_ROLE_TRAN_LIST

CREATE TABLE FMOCKUP.M_ROLE_TRAN_LIST (
	ROLE_ID						CHAR(26)		NOT NULL,
	TRAN_ID						CHAR(26)		NOT NULL,
	CREATE_DATE					CHAR(26)		NOT NULL,
	UPDATE_DATE					CHAR(26)		NOT NULL,									-- for Transaciton Control
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( ROLE_ID, TRAN_ID )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.M_ROLE_TRAN_LIST TO FMOCKUP_ROLE;

-- S_TRAN_LIST (System Fix Value)

CREATE TABLE FMOCKUP.S_TRAN_LIST (
	TRAN_ID						CHAR(26)		NOT NULL UNIQUE,
	TRAN_NAME					VARCHAR(32)		NOT NULL UNIQUE,
	ROLE_F						CHAR(1)			NOT NULL DEFAULT 'U',
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( TRAN_ID, TRAN_NAME )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.S_TRAN_LIST TO FMOCKUP_ROLE;

-- S_MENU_LIST (System Fix Value)

CREATE TABLE FMOCKUP.S_MENU_LIST (
	MENU_ID						CHAR(26)		NOT NULL UNIQUE,
	MENU_NAME					VARCHAR(32)		NOT NULL UNIQUE,
	TRAN_ID						CHAR(26)		NOT NULL,
	ICON_NAME					VARCHAR(32)		NOT NULL,
	LINK_NAME					VARCHAR(32)		NOT NULL UNIQUE,
	DISP_ORDER              	INTEGER			NOT NULL UNIQUE,
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( MENU_ID, MENU_NAME )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.S_MENU_LIST TO FMOCKUP_ROLE;

-- S_TRAN_TITLE_LIST

CREATE TABLE FMOCKUP.S_TRAN_TITLE_LIST (
	TRAN_ID		CHAR(32)	NOT NULL,
	LANG_ID		VARCHAR(8)	NOT NULL,
	TRAN_TITLE	VARCHAR(16)	NOT NULL,
	PRIMARY KEY(TRAN_ID, LANG_ID)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.S_TRAN_TITLE_LIST TO FMOCKUP_ROLE;

-- T_XXXX_LIST

CREATE TABLE FMOCKUP.T_XXXX_LIST (
	XXXX_ID						CHAR(26)		NOT NULL UNIQUE,
	XXXX_NAME					VARCHAR(32)		NOT NULL UNIQUE,
	XXXX_FLAG					VARCHAR(1)		NOT NULL,
	XXXX_VALUE					VARCHAR(32)		NOT NULL,
	REMARK						VARCHAR(768)	NOT NULL,
	ADD_XXXX_DATE				CHAR(19)		NOT NULL,
	MODIFY_XXXX_DATE			CHAR(19)		NOT NULL,
	CREATE_DATE					CHAR(26)		NOT NULL,
	UPDATE_DATE					CHAR(26)		NOT NULL,									-- for Transaciton Control
	USER_ID						CHAR(26)		NOT NULL,
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( XXXX_ID )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.T_XXXX_LIST TO FMOCKUP_ROLE;

-- T_YYYY_LIST

CREATE TABLE FMOCKUP.T_YYYY_LIST (
	YYYY_ID						CHAR(26)		NOT NULL UNIQUE,
	YYYY_NAME					VARCHAR(32)		NOT NULL UNIQUE,
	YYYY_FLAG					VARCHAR(1)		NOT NULL,
	YYYY_VALUE					VARCHAR(32)		NOT NULL,
	REMARK						VARCHAR(768)	NOT NULL,
	ADD_YYYY_DATE				CHAR(19)		NOT NULL,
	MODIFY_YYYY_DATE			CHAR(19)		NOT NULL,
	CREATE_DATE					CHAR(26)		NOT NULL,
	UPDATE_DATE					CHAR(26)		NOT NULL,									-- for Transaciton Control
	USER_ID						CHAR(26)		NOT NULL,
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( YYYY_ID )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.T_YYYY_LIST TO FMOCKUP_ROLE;

-- T_ZZZZ_LIST

CREATE TABLE FMOCKUP.T_ZZZZ_LIST (
	ZZZZ_ID						CHAR(26)		NOT NULL UNIQUE,
	ZZZZ_NAME					VARCHAR(32)		NOT NULL UNIQUE,
	ZZZZ_FLAG					VARCHAR(1)		NOT NULL,
	ZZZZ_VALUE					VARCHAR(32)		NOT NULL,
	REMARK						VARCHAR(768)	NOT NULL,
	ADD_ZZZZ_DATE				CHAR(19)		NOT NULL,
	MODIFY_ZZZZ_DATE			CHAR(19)		NOT NULL,
	CREATE_DATE					CHAR(26)		NOT NULL,
	UPDATE_DATE					CHAR(26)		NOT NULL,									-- for Transaciton Control
	USER_ID						CHAR(26)		NOT NULL,
	DELETED_F					CHAR(1)			NOT NULL DEFAULT 'N',
	PRIMARY KEY ( ZZZZ_ID )
);
GRANT SELECT, INSERT, UPDATE, DELETE ON FMOCKUP.T_ZZZZ_LIST TO FMOCKUP_ROLE;

上記の “FMOCKUP_USER” は、システム内から MySQL をアクセスするときのユーザーとなります。

fmockup_dml.bat

@echo off
echo fmockup - DML
echo FMOCKUP_USER password is "Asdf1234"
"C:\Program Files\MySQL\MySQL Server 8.1\bin\mysql.exe" -u FMOCKUP_USER -p --default-character-set=utf8 < ./fmockup-dml.sql
if not %ERRORLEVEL% equ 0 pause

上記の “root” は、システムの管理者ユーザーで、システム内の全ての業務をアクセスしたりアカウント管理（アカウントの作成・更新・削除）をしたりできます。
上記の “power-user” は、システム内の全ての業務をアクセスできるユーザーです。

fmockup_dml.sql

・・・

-- M_USER_LIST

DELETE FROM FMOCKUP.M_USER_LIST;
INSERT INTO FMOCKUP.M_USER_LIST
	( USER_ID, USER_NAME, PASSWORD_AES, REMARK, ROLE_F, USER_LOCKED_F, FAILED_COUNT, LAST_LOGIN_DATE, LAST_CHANGE_PASSWORD_DATE, ADD_USER_DATE, MODIFY_USER_DATE, CREATE_DATE, UPDATE_DATE, DELETED_F )
	VALUES
		( '2023-01-01T00:00:00.000000', 'root', HEX(AES_ENCRYPT('Asdf1234', 'Asdf1234Asdf1234')), 'Administrator', 'A', 'Y', 0, '2023-01-01 00:00:01', '1970-01-01 00:00:00', '2023-01-01 00:00:00', '2023-01-01 00:00:00', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:01.000000', 'N' ),
		( '2023-01-01T00:00:00.000001', 'power-user', HEX(AES_ENCRYPT('Asdf1234', 'Asdf1234Asdf1234')), 'Power User (for Full Transactions Access)', 'U', 'Y', 0, '2023-01-01 00:00:01', '1970-01-01 00:00:00', '2023-01-01 00:00:00', '2023-01-01 00:00:00', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:01.000000', 'N' );

-- M_ROLE_LIST

DELETE FROM FMOCKUP.M_ROLE_LIST;
INSERT INTO FMOCKUP.M_ROLE_LIST
	( ROLE_ID, ROLE_NAME, REMARK, ROLE_F, ADD_ROLE_DATE, MODIFY_ROLE_DATE, CREATE_DATE, UPDATE_DATE, DELETED_F )
	VALUES
		( '2023-01-01T00:00:00.000000', 'administrator-role', 'Administrator Role', 'A', '2023-01-01 00:00:00',  '2023-01-01 00:00:00',  '2023-01-01T00:00:00.000000',  '2023-01-01T00:00:00.000000', 'N'),
		( '2023-01-01T00:00:00.000001', 'power-role', 'Power Role (for Full Transactions Access)', 'U', '2023-01-01 00:00:00',  '2023-01-01 00:00:00',  '2023-01-01T00:00:00.000000',  '2023-01-01T00:00:00.000000', 'N');

-- M_USER_ROLE_LIST

DELETE FROM FMOCKUP.M_USER_ROLE_LIST;
INSERT INTO FMOCKUP.M_USER_ROLE_LIST
	( USER_ID, ROLE_ID, CREATE_DATE, UPDATE_DATE, DELETED_F )
	VALUES
		( '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N' ),
		( '2023-01-01T00:00:00.000001', '2023-01-01T00:00:00.000001', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N' );

-- M_ROLE_TRAN_LIST

DELETE FROM FMOCKUP.M_ROLE_TRAN_LIST;
INSERT INTO FMOCKUP.M_ROLE_TRAN_LIST
	( ROLE_ID, TRAN_ID, CREATE_DATE, UPDATE_DATE, DELETED_F )
	VALUES
		( '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000701', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N'),
		( '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000702', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N'),
		( '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000703', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N'),
		( '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000801', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N'),
		( '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000802', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N'),
		( '2023-01-01T00:00:00.000001', '2023-01-01T00:00:00.000701', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N'),
		( '2023-01-01T00:00:00.000001', '2023-01-01T00:00:00.000702', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N'),
		( '2023-01-01T00:00:00.000001', '2023-01-01T00:00:00.000703', '2023-01-01T00:00:00.000000', '2023-01-01T00:00:00.000000', 'N');

-- S_TRAN_LIST

DELETE FROM FMOCKUP.S_TRAN_LIST;
INSERT INTO FMOCKUP.S_TRAN_LIST
	( TRAN_ID, TRAN_NAME, ROLE_F, DELETED_F )
	VALUES
		( '2023-01-01T00:00:00.000701', 'Xxxx', 'U', 'N' ),
		( '2023-01-01T00:00:00.000702', 'Yyyy', 'U', 'N' ),
		( '2023-01-01T00:00:00.000703', 'Zzzz', 'U', 'N' ),
		( '2023-01-01T00:00:00.000801', 'User', 'S', 'N' ),
		( '2023-01-01T00:00:00.000802', 'Role', 'S', 'N' );

-- S_MENU_LIST

DELETE FROM FMOCKUP.S_MENU_LIST;
INSERT INTO FMOCKUP.S_MENU_LIST
	( MENU_ID, MENU_NAME, TRAN_ID, DISP_ORDER, ICON_NAME, LINK_NAME, DELETED_F )
	VALUES
		( '2023-01-01T00:00:00.000100', 'Home', '2023-01-01T00:00:00.000100', 100, 'mdi-home', '/pages/home.html', 'N' ),
		( '2023-01-01T00:00:00.000701', 'Xxxx', '2023-01-01T00:00:00.000701', 701, 'mdi-table', '/pages/xxxx_list.html', 'N' ),
		( '2023-01-01T00:00:00.000702', 'Yyyy', '2023-01-01T00:00:00.000702', 702, 'mdi-table', '/pages/yyyy_list.html', 'N' ),
		( '2023-01-01T00:00:00.000703', 'Zzzz', '2023-01-01T00:00:00.000703', 703, 'mdi-table', '/pages/zzzz_list.html', 'N' ),
		( '2023-01-01T00:00:00.000801', 'User / Role', '2023-01-01T00:00:00.000801', 800, 'mdi-cog-outline', '/pages/user_list.html', 'N' );

-- S_TRAN_TITLE_LIST

DELETE FROM FMOCKUP.S_TRAN_TITLE_LIST;
INSERT INTO FMOCKUP.S_TRAN_TITLE_LIST
	( TRAN_ID, LANG_ID, TRAN_TITLE )
	VALUES
		( '2023-01-01T00:00:00.000100', 'en', 'Home' ),
		( '2023-01-01T00:00:00.000100', 'ja', 'ホーム' ),
		( '2023-01-01T00:00:00.000100', 'zh-CN', '主页' ),
		( '2023-01-01T00:00:00.000701', 'en', 'Xxxx' ),
		( '2023-01-01T00:00:00.000701', 'ja', 'ＸＸＸＸ' ),
		( '2023-01-01T00:00:00.000701', 'zh-CN', 'ＸＸＸＸ' ),
		( '2023-01-01T00:00:00.000702', 'en', 'Yyyy' ),
		( '2023-01-01T00:00:00.000702', 'ja', 'ＹＹＹＹ' ),
		( '2023-01-01T00:00:00.000702', 'zh-CN', 'ＹＹＹＹ' ),
		( '2023-01-01T00:00:00.000703', 'en', 'Zzzz' ),
		( '2023-01-01T00:00:00.000703', 'ja', 'ＺＺＺＺ' ),
		( '2023-01-01T00:00:00.000703', 'zh-CN', 'ＺＺＺＺ' ),
		( '2023-01-01T00:00:00.000801', 'en', 'User / Role' ),
		( '2023-01-01T00:00:00.000801', 'ja', 'ユーザー・ロール' ),
		( '2023-01-01T00:00:00.000801', 'zh-CN', '用户・角色' );

-- T_XXXX_LIST

DELETE FROM FMOCKUP.T_XXXX_LIST;

-- T_YYYY_LIST

DELETE FROM FMOCKUP.T_YYYY_LIST;

-- T_ZZZZ_LIST

DELETE FROM FMOCKUP.T_ZZZZ_LIST;

-- Generate current datetime string rule
--   DATE_FORMAT(CURRENT_TIMESTAMP(6), '%Y-%m-%d_%T.%f')	micro seconds id
--   DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d %T')			seconds

ファイル “fmockup_ddl.bat”、“fmockup_dml.bat” を実行する。
“fmockup_ddl.bat” を実行したときのパスワードは、MySQL をインストールしたときの管理者パスワードを指定する。
“fmockup_dml.bat” を実行したときのパスワードは、“Asdf1234” を指定する。

参考

mysql コマンド

AES_ENCRYPT()