Create Table
create.sql
-- ------------------------------------------------------------------------------------------------
-- Target: mariadb Ver 15.1 Distrib 10.4.10-MariaDB, for Linux (x86_64) using readline 5.1
-- Usage: mariadb -uroot -p < ${path_to_sql}/create.sql
-- ------------------------------------------------------------------------------------------------
-- Create database --------------------------------------------------------------------------------
CREATE DATABASE `ticket` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ------------------------------------------------------------------------------------------------
-- Create user ------------------------------------------------------------------------------------
GRANT INSERT, UPDATE, DELETE ON `ticket`.* TO `tkt`@`%` IDENTIFIED BY '1234qwer%^&*';
FLUSH PRIVILEGES;
-- ------------------------------------------------------------------------------------------------
-- Change database --------------------------------------------------------------------------------
USE `ticket`;
-- ------------------------------------------------------------------------------------------------
-- Create tables ----------------------------------------------------------------------------------
CREATE TABLE `user_info`
(
`uuid` CHAR(36) NOT NULL COMMENT 'To distinguish a record',
`user_id` VARCHAR(16) NOT NULL COMMENT 'User id to display and search for user',
`nickname` VARCHAR(16) NOT NULL COMMENT 'User nickname to display',
`gender` TINYINT NOT NULL DEFAULT 0 COMMENT '0 is unknown, 1 is male, 2 is female',
`deleted` BOOL NOT NULL DEFAULT FALSE COMMENT 'If record had delete is true, else if false',
`create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record create datetime',
`create_user` VARCHAR(16) NOT NULL DEFAULT 'system' COMMENT 'Record create user',
`update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record update datetime',
`update_user` VARCHAR(16) NOT NULL DEFAULT 'system' COMMENT 'Record update user',
PRIMARY KEY (`uuid`),
UNIQUE KEY `user_id` (`user_id`)
);
CREATE TABLE `login_token`
(
`uuid` CHAR(36) NOT NULL COMMENT 'To distinguish a record',
`intl_tel_code` VARCHAR(3) NOT NULL NOT NULL COMMENT 'International telephone code, use for login',
`tel_num` VARCHAR(11) NOT NULL COMMENT 'User telephone number, use for login',
`password` VARCHAR(300) NOT NULL COMMENT 'Password for login with hash',
`user_uuid` CHAR(36) NOT NULL COMMENT 'Login token related user id',
`deleted` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'If record had delete is true, else if false',
`create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record create datetime',
`create_user` VARCHAR(16) NOT NULL DEFAULT 'system' COMMENT 'Record create user',
`update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record update datetime',
`update_user` VARCHAR(16) NOT NULL DEFAULT 'system' COMMENT 'Record update user',
PRIMARY KEY (`uuid`),
FOREIGN KEY `user_uuid` (`user_uuid`) REFERENCES `user_info` (uuid),
KEY `user_tel_num` (`intl_tel_code`, `tel_num`)
) ENGINE = InnoDB COMMENT 'use for user login check id and password';
-- ------------------------------------------------------------------------------------------------
Destroy Database
destroy.sql
-- ------------------------------------------------------------------------------------------------
-- Target: mariadb Ver 15.1 Distrib 10.4.10-MariaDB, for Linux (x86_64) using readline 5.1
-- Usage: mariadb -uroot -p < ${path_to_sql}/destroy.sql
-- ------------------------------------------------------------------------------------------------
-- Drop database ----------------------------------------------------------------------------------
DROP DATABASE IF EXISTS `ticket`;
-- ------------------------------------------------------------------------------------------------
-- Drop user --------------------------------------------------------------------------------------
DROP USER IF EXISTS `tkt`@`%`;
FLUSH PRIVILEGES;
-- ------------------------------------------------------------------------------------------------
Insert Record
init.sql
-- ------------------------------------------------------------------------------------------------
-- Target: mariadb Ver 15.1 Distrib 10.4.10-MariaDB, for Linux (x86_64) using readline 5.1
-- Usage: mariadb -uroot -p < ${path_to_sql}/init.sql
-- ------------------------------------------------------------------------------------------------
-- Change database --------------------------------------------------------------------------------
USE `ticket`;
-- ------------------------------------------------------------------------------------------------
-- User variable
-- Total user record
SET @USER_RECORD_NUM = 1000;
-- Public stored function, generate general column ------------------------------------------------
-- Generate uuid function
DELIMITER $$
CREATE FUNCTION gen_uuid() RETURNS CHAR(36)
COMMENT 'Used for generate uuid column default value'
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE uuid CHAR(36);
SET uuid = (SELECT UUID());
RETURN (uuid);
END;
$$
DELIMITER ;
-- Generate deleted column function
DELIMITER $$
CREATE FUNCTION gen_deleted() RETURNS BOOLEAN
COMMENT 'Used for generate deleted column default value'
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE deleted BOOLEAN;
SET deleted = FALSE;
RETURN (deleted);
END;
$$
DELIMITER ;
-- Generate create date column function
DELIMITER $$
CREATE FUNCTION gen_create_date() RETURNS DATETIME
COMMENT 'Used for generate create_date column default value'
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE create_date DATETIME;
SET create_date = (SELECT NOW());
RETURN (create_date);
END;
$$
DELIMITER ;
-- Generate create user column function
DELIMITER $$
CREATE FUNCTION gen_create_user() RETURNS VARCHAR(16)
COMMENT 'Used for generate create_user column default value'
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE create_user VARCHAR(16);
SET create_user = 'system';
RETURN (create_user);
END;
$$
DELIMITER ;
-- Generate update date column function
DELIMITER $$
CREATE FUNCTION gen_update_date() RETURNS DATETIME
COMMENT 'Used for generate update_date column default value'
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE update_date DATETIME;
SET update_date = (SELECT NOW());
RETURN (update_date);
END;
$$
DELIMITER ;
-- Generate update user column function
DELIMITER $$
CREATE FUNCTION gen_update_user() RETURNS VARCHAR(16)
COMMENT 'Used for generate update_user column default value'
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE update_user VARCHAR(16);
SET update_user = 'system';
RETURN (update_user);
END;
$$
DELIMITER ;
-- Generate telephone, be careful this function will return same result
DELIMITER $$
CREATE FUNCTION gen_tel_num(tel_num_length INT) RETURNS VARCHAR(11)
COMMENT 'Generate random telephone number'
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE length_count INT DEFAULT 2;
DECLARE tel_num VARCHAR(11) DEFAULT '1';
-- 13xx - 19xx
SET tel_num = CONCAT(tel_num, CAST(FLOOR((RAND() * 7) + 3) AS CHAR));
WHILE length_count < tel_num_length
DO
SET length_count = length_count + 1;
SET tel_num = CONCAT(tel_num, CAST(FLOOR((RAND() * 10)) AS CHAR));
END WHILE;
RETURN tel_num;
END;
$$
DELIMITER ;
-- ------------------------------------------------------------------------------------------------
-- Use stored procedure insert value --------------------------------------------------------------
-- Insert into `user_info`
DELIMITER $$
CREATE PROCEDURE init_user_info()
COMMENT 'Insert record to init user_info'
BEGIN
DECLARE v_uuid CHAR(36);
DECLARE v_user_id VARCHAR(16);
DECLARE v_nickname VARCHAR(16);
DECLARE v_gender TINYINT;
DECLARE v_deleted BOOLEAN;
DECLARE v_create_date DATETIME;
DECLARE v_create_user VARCHAR(16);
DECLARE v_update_date DATETIME;
DECLARE v_update_user VARCHAR(16);
-- Use for check record unique
DECLARE v_is_unique BIGINT;
-- Insert count
DECLARE v_insert_count BIGINT;
DECLARE v_total_insert BIGINT;
SET v_insert_count = 0;
SELECT @USER_RECORD_NUM INTO v_total_insert;
WHILE v_insert_count < v_total_insert
DO
-- Init record data
SELECT gen_uuid() INTO v_uuid;
SELECT CONCAT('tk_', SUBSTR(MD5(RAND()), 1, 13)) INTO v_user_id;
SELECT SUBSTR(MD5(RAND()), 1, 7) INTO v_nickname;
SELECT FLOOR(RAND() * 3) INTO v_gender;
SELECT gen_deleted() INTO v_deleted;
SELECT gen_create_date() INTO v_create_date;
SELECT gen_create_user() INTO v_create_user;
SELECT gen_update_date() INTO v_update_date;
SELECT gen_update_user() INTO v_update_user;
-- Check user_id unique
SELECT COUNT(*) INTO v_is_unique FROM `user_info` WHERE `user_info`.`user_id` = v_user_id;
WHILE v_is_unique > 0
DO
SELECT CONCAT('tk_', SUBSTR(MD5(RAND()), 1, 13)) INTO v_user_id;
END WHILE;
INSERT INTO `user_info`(`uuid`, `user_id`, `nickname`, `gender`, `deleted`,
`create_date`, `create_user`, `update_date`, `update_user`)
VALUES (v_uuid, v_user_id, v_nickname, v_gender, v_deleted, v_create_date,
v_create_user, v_update_date, v_update_user);
-- Output log
SELECT CONCAT('count(s) ', (v_insert_count + 1)) AS 'Inserted into user_info: ';
SET v_insert_count = v_insert_count + 1;
END WHILE;
END;
$$
DELIMITER ;
-- Insert into `user_info`(`login_token` had foreign key constraint)
DELIMITER $$
CREATE PROCEDURE init_login_token()
COMMENT 'Insert record to init user_info'
BEGIN
DECLARE v_uuid CHAR(36);
DECLARE v_intl_tel_code VARCHAR(3);
DECLARE v_tel_num VARCHAR(11);
DECLARE v_password VARCHAR(16);
DECLARE v_user_uuid CHAR(36);
DECLARE v_deleted BOOLEAN;
DECLARE v_create_date DATETIME;
DECLARE v_create_user VARCHAR(16);
DECLARE v_update_date DATETIME;
DECLARE v_update_user VARCHAR(16);
-- Use cursor to read each user_info.uuid
DECLARE v_cursor_done BOOLEAN DEFAULT FALSE;
DECLARE v_user_info_cursor CURSOR FOR SELECT `user_info`.`uuid` FROM `user_info`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_cursor_done = TRUE;
OPEN v_user_info_cursor;
user_info_loop:
LOOP
FETCH v_user_info_cursor INTO v_user_uuid;
IF v_cursor_done THEN
LEAVE user_info_loop;
END IF;
SELECT gen_uuid() INTO v_uuid;
SELECT '86' INTO v_intl_tel_code;
SELECT gen_tel_num(11) INTO v_tel_num;
SELECT SUBSTR(SHA1(RAND()), 0) INTO v_password;
SELECT gen_deleted() INTO v_deleted;
SELECT gen_create_date() INTO v_create_date;
SELECT gen_create_user() INTO v_create_user;
SELECT gen_update_date() INTO v_update_date;
SELECT gen_update_user() INTO v_update_user;
INSERT INTO `login_token`(`uuid`, `intl_tel_code`, `tel_num`, `password`, `user_uuid`, `deleted`,
`create_date`, `create_user`, `update_date`, `update_user`)
VALUES (v_uuid, v_intl_tel_code, v_tel_num, v_password, v_user_uuid, v_deleted, v_create_date,
v_create_user, v_update_date, v_update_user);
SELECT CONCAT('user_info.user_id ', v_user_uuid) AS 'Insert into login_token: ';
END LOOP;
CLOSE v_user_info_cursor;
END;
$$
DELIMITER ;
-- ------------------------------------------------------------------------------------------------
-- Call procedure to init -------------------------------------------------------------------------
CALL init_user_info();
CALL init_login_token();
-- ------------------------------------------------------------------------------------------------