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

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

fmockup

## はじめに

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

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

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

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

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

## ファイルの作成

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

2. テキスト・エディターで、ファイル “fmockup_ddl.bat”、“fmockup_ddl.sql”、“fmockup_dml.bat”、“fmockup_dml.sql” を作成する。
    ```bat: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
    ```
    ```sql: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 をアクセスするときのユーザーとなります。
    ```bat: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” は、システム内の全ての業務をアクセスできるユーザーです。    
    ```sql: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
    ```

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

## 参考

**mysql コマンド**

https://qiita.com/CyberMergina/items/f889519e6be19c46f5f4

**AES_ENCRYPT()**

https://qiita.com/arinc0/items/a97b14657fc02948bec2

https://qiita.com/cszunwr291/items/f5fba3fc6ac428f7fabb