1.9.11.sql
7.86 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
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
SET FOREIGN_KEY_CHECKS = 0;
/**
增加 标签权重,扩展属性列
*/
ALTER TABLE `hi-sing`.`system_tags`
ADD COLUMN `expand` json NULL AFTER `weight`;
ALTER TABLE `hi-sing`.`system_tags`
MODIFY COLUMN `type` tinyint UNSIGNED NOT NULL DEFAULT 1 COMMENT '类型：1:歌曲标签 2:技能标签;3:声音标签 4:认证能力标签' AFTER `id`;
ALTER TABLE `hi-sing`.`system_tags`
MODIFY COLUMN `weight` int NOT NULL DEFAULT 0 COMMENT '排序权重，越大越靠前' AFTER `user_id`;
/**
增加用户认证表
*/
CREATE TABLE `hi-sing`.`user_auth_infos`
(
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL DEFAULT 0 COMMENT '用户id',
`nick_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`platform` json NOT NULL,
`works` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`img` json NULL,
`tags` json NOT NULL,
`audio_info` json NULL,
`status` tinyint NULL DEFAULT 0 COMMENT '0:待认证 1:认证完成 2:拒绝',
`reason` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
`operator_id` bigint NOT NULL DEFAULT 0 COMMENT '操作人',
`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;
/**
增加用户标签关联表类型说明
*/
ALTER TABLE `hi-sing`.`user_tag_relations`
MODIFY COLUMN `type` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型[1:风格,2:技能,3:声音 4：认证音乐人标签]' AFTER `tag_id`;
/**
增加用户身份
*/
ALTER TABLE `hi-sing`.`users`
ADD COLUMN `identity` tinyint NULL DEFAULT 0 COMMENT '0:游客 1：音乐人 2：经纪人 3：音乐人+经纪人' AFTER `last_login`;
/**
增加用户提交作品视图封面列
*/
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`.`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`,
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`))));
/**
创建用户最后一个提交认证信息视图
*/
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `hi-sing`.`user_last_certifies` AS
select `user_auth_infos`.`id` AS `id`,
`user_auth_infos`.`user_id` AS `user_id`,
`user_auth_infos`.`nick_name` AS `nick_name`,
`user_auth_infos`.`platform` AS `platform`,
`user_auth_infos`.`works` AS `works`,
`user_auth_infos`.`img` AS `img`,
`user_auth_infos`.`tags` AS `tags`,
`user_auth_infos`.`audio_info` AS `audio_info`,
`user_auth_infos`.`status` AS `status`,
`user_auth_infos`.`reason` AS `reason`,
`user_auth_infos`.`created_at` AS `created_at`,
`user_auth_infos`.`updated_at` AS `updated_at`,
`user_auth_infos`.`deleted_at` AS `deleted_at`
from `user_auth_infos`
where `user_auth_infos`.`id` in
(select max(`user_auth_infos`.`id`) from `user_auth_infos` group by `user_auth_infos`.`user_id`);
/**
增加后台菜单及其权限
*/
INSERT INTO `hi-sing`.`system_permissions` (`id`, `guard`, `parent_id`, `name`, `label`, `type`, `icon`, `weight`,
`created_at`, `updated_at`, `deleted_at`)
VALUES (60, 'Admin', 36, 'user-register-show', '详情', 'Menu', '', 90, '2023-05-29 10:05:53', '2023-05-29 10:05:57',
NULL);
INSERT INTO `hi-sing`.`system_permissions` (`id`, `guard`, `parent_id`, `name`, `label`, `type`, `icon`, `weight`,
`created_at`, `updated_at`, `deleted_at`)
VALUES (61, 'Admin', 1, 'user-certify', '认证待审核', 'Menu', '', 88, '2023-05-29 10:09:30', '2023-05-29 10:09:34',
NULL);
INSERT INTO `hi-sing`.`system_role_has_permissions` (`role_id`, `permission_id`)
VALUES (1, 60);
INSERT INTO `hi-sing`.`system_role_has_permissions` (`role_id`, `permission_id`)
VALUES (1, 61);
UPDATE `hi-sing`.`system_permissions`
SET `label` = '注册未认证'
WHERE `name` = 'user-register';
UPDATE `hi-sing`.`system_permissions`
SET `label` = '音乐人管理'
WHERE `name` = 'user-singer';
UPDATE `hi-sing`.`system_permissions`
SET `label` = '经纪人管理'
WHERE `name` = 'user-business';
UPDATE `hi-sing`.`system_permissions`
SET `label` = '详情'
WHERE `name` IN ('user-singer-show', 'user-business-show');
/**
刷新用户数据
*/
UPDATE `hi-sing`.`users`
SET `status`=2,
`deleted_at` = NOW()
WHERE `audit_status` = 2
AND `deleted_at` IS NULL;
UPDATE `hi-sing`.`users`
SET `audit_status`=1
WHERE `audit_status` = 0;
UPDATE `hi-sing`.`users`
SET `audit_at`= NULL
WHERE `audit_status` = 1
AND `deleted_at` IS NULL;
SET FOREIGN_KEY_CHECKS = 1;