1.9.17.sql
15.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `hi-sing`.`activity_has_users` ADD COLUMN `broker_id` bigint UNSIGNED NOT NULL DEFAULT 0 COMMENT '确认合作时经纪人' AFTER `user_id`;
ALTER TABLE `hi-sing`.`activity_has_users` ADD COLUMN `broker_level` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '确认合作时经纪人等级' AFTER `broker_id`;
ALTER TABLE `hi-sing`.`activity_has_users` ADD COLUMN `wav_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '自主上传新增的高音质音频' AFTER `demo_url`;
ALTER TABLE `hi-sing`.`activity_has_users` ADD COLUMN `wav_durations` float NULL DEFAULT 0 COMMENT '作品时长,单位：秒' AFTER `durations`;
ALTER TABLE `hi-sing`.`activity_has_users` ADD COLUMN `hide_at` datetime NULL DEFAULT NULL COMMENT '设置公开试唱的时间' AFTER `is_hide`;
ALTER TABLE `hi-sing`.`activity_has_users` MODIFY COLUMN `id` bigint NOT NULL FIRST;
ALTER TABLE `hi-sing`.`activity_has_users` MODIFY COLUMN `activity_id` bigint UNSIGNED NOT NULL DEFAULT 0 COMMENT '活动id' AFTER `id`;
ALTER TABLE `hi-sing`.`activity_has_users` MODIFY COLUMN `user_id` bigint UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id' AFTER `activity_id`;
ALTER TABLE `hi-sing`.`activity_has_users` MODIFY COLUMN `id` bigint NOT NULL AUTO_INCREMENT;
CREATE TABLE `hi-sing`.`activity_relist_records` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`activity_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`project_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`user_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`broker_id` bigint UNSIGNED NOT NULL,
`broker_level` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`operator_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`created_at` datetime NULL DEFAULT NULL,
`updated_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
ALTER TABLE `hi-sing`.`activitys` ADD COLUMN `audit_at` datetime NULL DEFAULT NULL COMMENT '审核时间' AFTER `audit_status`;
CREATE TABLE `hi-sing`.`broker_push_configs` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`identifier` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`level_title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`level_intro` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`match_title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`match_intro` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`user_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`created_at` datetime NULL DEFAULT NULL,
`updated_at` datetime NULL DEFAULT NULL,
`deleted_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `key`(`identifier` ASC, `deleted_at` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE TABLE `hi-sing`.`broker_push_level_record_items` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`record_id` bigint UNSIGNED NOT NULL,
`user_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
`status` tinyint NOT NULL DEFAULT 0,
`message_id` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`send_at` datetime NULL DEFAULT NULL,
`read_at` datetime NULL DEFAULT NULL,
`created_at` datetime NULL DEFAULT NULL,
`updated_at` datetime NULL DEFAULT NULL,
`deleted_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE TABLE `hi-sing`.`broker_push_level_records` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`config_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`is_alert` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否弹窗',
`status` tinyint UNSIGNED NOT NULL DEFAULT 0,
`begin_at` datetime NOT NULL,
`end_at` datetime NOT NULL,
`publish_at` datetime NULL DEFAULT NULL,
`created_at` datetime NULL DEFAULT NULL,
`updated_at` datetime NULL DEFAULT NULL,
`deleted_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE TABLE `hi-sing`.`broker_push_match_records` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`user_tag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`broker_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`broker_level` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`activity_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`project_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`message_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`status` tinyint UNSIGNED NOT NULL DEFAULT 0,
`send_at` datetime NULL DEFAULT NULL,
`read_at` datetime NULL DEFAULT NULL,
`created_at` datetime NULL DEFAULT NULL,
`updated_at` datetime NULL DEFAULT NULL,
`deleted_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE TABLE `hi-sing`.`broker_user_config_items` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` bigint UNSIGNED NOT NULL,
`config_id` bigint UNSIGNED NOT NULL,
`identifier` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`singer_num` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`is_sync` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否同步',
`status` tinyint UNSIGNED NOT NULL DEFAULT 0,
`created_at` datetime NULL DEFAULT NULL,
`updated_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `key`(`config_id` DESC, `user_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE TRIGGER `update_singer` BEFORE UPDATE ON `broker_user_config_items` FOR EACH ROW BEGIN
IF new.singer_num != old.singer_num THEN
SET new.`is_sync` = 0;
END IF;
END;
CREATE TABLE `hi-sing`.`broker_user_configs` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '',
`begin_at` datetime NOT NULL,
`end_at` datetime NOT NULL,
`user_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`created_at` datetime NULL DEFAULT NULL,
`updated_at` datetime NULL DEFAULT NULL,
`deleted_at` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE OR REPLACE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `hi-sing`.`activity_works` AS select `activity_has_users`.`id` AS `id`,`activity_has_users`.`activity_id` AS `activity_id`,`activitys`.`song_name` AS `activity_name`,`activitys`.`cover` AS `activity_cover`,`activitys`.`sub_title` AS `activity_title`,`activitys`.`status` AS `activity_status`,`activitys`.`user_id` AS `activity_user_id`,`activity_has_users`.`user_id` AS `user_id`,`users`.`nick_name` AS `user_nick_name`,`users`.`real_name` AS `user_real_name`,`users`.`identity` AS `user_identity`,`users`.`status` AS `user_status`,`users`.`role` AS `user_role`,`activity_has_users`.`demo_url` AS `demo_url`,`activity_has_users`.`durations` AS `durations`,`activity_has_users`.`type` AS `type`,`activity_has_users`.`status` AS `status`,`activity_has_users`.`syn_data` AS `syn_data`,`activity_has_users`.`syn_status` AS `syn_status`,`activity_has_users`.`mode` AS `mode`,`activity_has_users`.`open_id` AS `open_id`,`activity_has_users`.`is_checked` AS `is_checked`,`activity_has_users`.`is_top` AS `is_top`,`activity_has_users`.`is_hide` AS `is_hide`,`activity_has_users`.`submit_at` AS `submit_at`,`activity_has_users`.`version` AS `version`,`activity_has_users`.`sing_type` AS `sing_type`,`activity_has_users`.`created_at` AS `created_at`,`activity_has_users`.`updated_at` AS `updated_at`,`activity_has_users`.`deleted_at` AS `deleted_at`,`activity_has_users`.`broker_id` AS `broker_id`,`activity_has_users`.`broker_level` AS `broker_level`,ifnull(`users`.`business_id`,0) AS `business_id`,ifnull(`activity_share_users`.`share_id`,0) AS `share_id`,ifnull(`activitys`.`project_id`,0) AS `project_id`,ifnull(`activity_user_has_prices`.`id`,0) AS `price_id` from ((((`activity_has_users` join `users` on((`users`.`id` = `activity_has_users`.`user_id`))) join `activitys` on((`activitys`.`id` = `activity_has_users`.`activity_id`))) left join `activity_share_users` on(((`activity_has_users`.`user_id` = `activity_share_users`.`user_id`) and (`activity_has_users`.`activity_id` = `activity_share_users`.`activity_id`)))) left join `activity_user_has_prices` on(((`activity_has_users`.`user_id` = `activity_user_has_prices`.`user_id`) and (`activity_has_users`.`activity_id` = `activity_user_has_prices`.`activity_id`))));
INSERT INTO `hi-sing`.`system_permissions` (`id`, `guard`, `parent_id`, `name`, `label`, `type`, `icon`, `weight`, `created_at`, `updated_at`, `deleted_at`) VALUES (65, 'Admin', 5, 'operation-broker', '运营经纪人', 'Menu', '', 70, '2023-09-04 10:23:00', '2023-09-04 10:23:03', NULL);
INSERT INTO `hi-sing`.`system_permissions` (`id`, `guard`, `parent_id`, `name`, `label`, `type`, `icon`, `weight`, `created_at`, `updated_at`, `deleted_at`) VALUES (66, 'Admin', 7, 'audition-activity-relist-log', '重新上架记录', 'Menu', '', 10, '2023-09-18 10:32:19', '2023-09-18 10:32:19', NULL);
INSERT INTO `hi-sing`.`system_config` (`id`, `creator_id`, `parent_id`, `name`, `identifier`, `content`, `expand`, `remark`, `status`, `weight`, `created_at`, `updated_at`, `deleted_at`) VALUES (140, 12, 0, '经纪人模版推送默认值', 'g65owmrgKSUhxV2afndUg', 'A', '{\"type\": \"input\"}', '运行经纪人-模版推送配置-默认模版', 1, 0, '2023-09-05 11:27:41', '2023-09-05 17:54:44', NULL);
INSERT INTO `hi-sing`.`system_config` (`id`, `creator_id`, `parent_id`, `name`, `identifier`, `content`, `expand`, `remark`, `status`, `weight`, `created_at`, `updated_at`, `deleted_at`) VALUES (146, 12, 0, '经纪人配置模版', 't8iH6OBLP_WkzP-ILhF48', 'https://hi-sing-cdn-dev.hikoon.com/file/20230907/83jfnp0y57d16940735517635hz9kpqswbq.xlsx', '{\"type\": \"file\"}', '运营经纪人-经纪人配置模版', 1, 0, '2023-09-07 15:59:25', '2023-09-07 15:59:25', NULL);
UPDATE `hi-sing`.`activity_has_users` LEFT JOIN users ON users.id = activity_has_users.user_id SET activity_has_users.broker_id = IFNULL(users.business_id,0) WHERE activity_has_users.`status` = 1;
UPDATE `hi-sing`.`activitys` SET `activitys`.audit_at = `activitys`.created_at;
SET FOREIGN_KEY_CHECKS=1;