Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@tengbu-haoshou(tengbu haoshou)

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

Last updated at Posted at 2024-04-25

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” を指定しました。

ファイルの作成

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

  2. テキスト・エディターで、ファイル “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

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

参考

mysql コマンド

AES_ENCRYPT()

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?