2.0.1.sql
5.07 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
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `hi-sing`.`activitys`
ADD COLUMN `song_type` tinyint UNSIGNED NOT NULL DEFAULT 1 COMMENT '1:歌曲,2:Demo' AFTER `id`;
ALTER TABLE `hi-sing`.`activitys`
ADD COLUMN `match_day` double UNSIGNED GENERATED ALWAYS AS (truncate(ifnull(((case
when (timestampdiff(HOUR, `audit_at`, `match_at`) < 0)
then 0
else timestampdiff(HOUR, `audit_at`, `match_at`) end) /
24), 0),
2)) STORED NULL AFTER `match_at`;
ALTER TABLE `hi-sing`.`projects`
ADD COLUMN `is_can_demo_apply` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '是否允许Demo提交上架申请' AFTER `is_can_manage`;
ALTER TABLE `hi-sing`.`projects`
ADD COLUMN `is_demo_audit` tinyint UNSIGNED NULL DEFAULT 0 COMMENT 'Demo上架是否需要申请' AFTER `is_can_demo_apply`;
ALTER TABLE `hi-sing`.`projects`
MODIFY COLUMN `is_can_apply` tinyint UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否允许提交上架申请' AFTER `is_public`;
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`.`song_type` AS `activity_type`,
`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 `system_config` (`creator_id`, `parent_id`, `name`, `identifier`, `content`, `expand`, `remark`, `status`,
`weight`, `created_at`, `updated_at`, `deleted_at`)
VALUES (0, 38, '创建Demo默认图', 'activity_demo_cover',
'https://hi-sing-cdn-dev.hikoon.com/file/20231127/pdzj4kq1qii1701078149373opupvd4gzg.jpg', '{
\"type\": \"image\"
}', NULL, 1, 0, '2023-11-27 17:42:41', '2023-11-27 17:42:41', NULL);
UPDATE projects
set is_demo_audit = 0;
UPDATE activitys
set song_type = 1;
SET FOREIGN_KEY_CHECKS = 1;