You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
119 lines
4.9 KiB
SQL
119 lines
4.9 KiB
SQL
-- -------------------------------- Create undo_ Log table --------------------------------
|
|
-- Seata AT Mode Need to use undo_ Log table.
|
|
CREATE TABLE `undo_log` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`branch_id` bigint(20) NOT NULL,
|
|
`xid` varchar(100) NOT NULL,
|
|
`context` varchar(128) NOT NULL,
|
|
`rollback_info` longblob NOT NULL,
|
|
`log_status` int(11) NOT NULL,
|
|
`log_created` datetime NOT NULL,
|
|
`log_modified` datetime NOT NULL,
|
|
`ext` varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
|
|
|
|
-- -------------------------------- The script used when storeMode is 'db' --------------------------------
|
|
-- the table to store GlobalSession data
|
|
CREATE TABLE IF NOT EXISTS `global_table`
|
|
(
|
|
`xid` VARCHAR(128) NOT NULL,
|
|
`transaction_id` BIGINT,
|
|
`status` TINYINT NOT NULL,
|
|
`application_id` VARCHAR(32),
|
|
`transaction_service_group` VARCHAR(32),
|
|
`transaction_name` VARCHAR(128),
|
|
`timeout` INT,
|
|
`begin_time` BIGINT,
|
|
`application_data` VARCHAR(2000),
|
|
`gmt_create` DATETIME,
|
|
`gmt_modified` DATETIME,
|
|
PRIMARY KEY (`xid`),
|
|
KEY `idx_status_gmt_modified` (`status` , `gmt_modified`),
|
|
KEY `idx_transaction_id` (`transaction_id`)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
-- the table to store BranchSession data
|
|
CREATE TABLE IF NOT EXISTS `branch_table`
|
|
(
|
|
`branch_id` BIGINT NOT NULL,
|
|
`xid` VARCHAR(128) NOT NULL,
|
|
`transaction_id` BIGINT,
|
|
`resource_group_id` VARCHAR(32),
|
|
`resource_id` VARCHAR(256),
|
|
`branch_type` VARCHAR(8),
|
|
`status` TINYINT,
|
|
`client_id` VARCHAR(64),
|
|
`application_data` VARCHAR(2000),
|
|
`gmt_create` DATETIME(6),
|
|
`gmt_modified` DATETIME(6),
|
|
PRIMARY KEY (`branch_id`),
|
|
KEY `idx_xid` (`xid`)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
-- the table to store lock data
|
|
CREATE TABLE IF NOT EXISTS `lock_table`
|
|
(
|
|
`row_key` VARCHAR(128) NOT NULL,
|
|
`xid` VARCHAR(128),
|
|
`transaction_id` BIGINT,
|
|
`branch_id` BIGINT NOT NULL,
|
|
`resource_id` VARCHAR(256),
|
|
`table_name` VARCHAR(32),
|
|
`pk` VARCHAR(36),
|
|
`status` TINYINT NOT NULL DEFAULT '0' COMMENT '0:locked ,1:rollbacking',
|
|
`gmt_create` DATETIME,
|
|
`gmt_modified` DATETIME,
|
|
PRIMARY KEY (`row_key`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_branch_id` (`branch_id`),
|
|
KEY `idx_xid_and_branch_id` (`xid` , `branch_id`)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `distributed_lock`
|
|
(
|
|
`lock_key` CHAR(20) NOT NULL,
|
|
`lock_value` VARCHAR(20) NOT NULL,
|
|
`expire` BIGINT,
|
|
primary key (`lock_key`)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('AsyncCommitting', ' ', 0);
|
|
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('RetryCommitting', ' ', 0);
|
|
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('RetryRollbacking', ' ', 0);
|
|
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('TxTimeoutCheck', ' ', 0);
|
|
|
|
-- -------------- Create the database tables needed by the business in the example ----------------
|
|
DROP TABLE IF EXISTS `storage_tbl`;
|
|
CREATE TABLE `storage_tbl` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`commodity_code` varchar(255) DEFAULT NULL,
|
|
`count` int(11) DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY (`commodity_code`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
|
|
|
DROP TABLE IF EXISTS `order_tbl`;
|
|
CREATE TABLE `order_tbl` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` varchar(255) DEFAULT NULL,
|
|
`commodity_code` varchar(255) DEFAULT NULL,
|
|
`count` int(11) DEFAULT 0,
|
|
`money` int(11) DEFAULT 0,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
|
|
|
DROP TABLE IF EXISTS `account_tbl`;
|
|
CREATE TABLE `account_tbl` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` varchar(255) DEFAULT NULL,
|
|
`money` int(11) DEFAULT 0,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |