1.9.15.sql
6.18 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
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE `hi-sing`.`im_messags`
(
`id` int NOT NULL AUTO_INCREMENT,
`from` int NOT NULL DEFAULT 0 COMMENT '发送发用户id',
`from_im` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'IM 用户标识',
`to` int NOT NULL DEFAULT 0 COMMENT '接收方用户id',
`to_im` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接收方IM用户标识',
`type` tinyint NOT NULL DEFAULT 0 COMMENT '消息类型',
`value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '消息内容',
`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;
CREATE TABLE `hi-sing`.`notification_users`
(
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`notification_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`user_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`status` tinyint NOT NULL DEFAULT 0 COMMENT '-1:失败，0:待处理，1:发送中，2:成功',
`send_at` datetime NULL DEFAULT NULL COMMENT '发送时间',
`read_at` datetime NULL DEFAULT NULL COMMENT '已读时间',
`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;
CREATE TABLE `hi-sing`.`notifications`
(
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标题',
`type` tinyint UNSIGNED NOT NULL DEFAULT 1 COMMENT '类型【1:文本，2:图文】',
`content` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '内容',
`cover` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '图文模式封面',
`user_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`link_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`link_id` bigint UNSIGNED NOT NULL DEFAULT 0,
`rich_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`publish_to` json NOT NULL COMMENT '发布对象',
`publish_type` tinyint UNSIGNED NOT NULL DEFAULT 1 COMMENT '1:立即发送 2:定时发送',
`publish_at` datetime NOT NULL COMMENT '发布时间',
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '状态[-1:发送失败 0:待发送，1:发送中 2:发送成功]',
`created_at` datetime NULL DEFAULT NULL COMMENT '创建时间',
`updated_at` datetime NULL DEFAULT NULL COMMENT '更新时间',
`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`.`users`
DROP INDEX `role`;
ALTER TABLE `hi-sing`.`users`
ADD INDEX `status` (`status` ASC) USING BTREE;
CREATE OR REPLACE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `hi-sing`.`user_manage_activities` AS
select `activity_links`.`id` AS `id`,
`activity_links`.`link_id` AS `user_id`,
`activity_links`.`activity_id` AS `activity_id`,
`activitys`.`project_id` AS `project_id`,
`activitys`.`song_name` AS `activity_name`,
`activitys`.`publish_at` AS `activity_publish_at`,
`activitys`.`status` AS `activity_status`,
json_unquote(json_extract(`activity_links`.`expand`, '$.permission')) AS `permission`
from (`activity_links` join `activitys` on ((`activity_links`.`activity_id` = `activitys`.`id`)))
where ((`activity_links`.`type` = 'manager') and (`activity_links`.`link_type` = 'App\\Models\\User'));
INSERT INTO `hi-sing`.`system_permissions` (`guard`, `parent_id`, `name`, `label`, `type`, `icon`, `weight`,
`created_at`, `updated_at`, `deleted_at`)
VALUES ('Admin', 5, 'operation-notification', '系统推送', 'Menu', '', 80, '2023-07-25 15:58:56', '2023-07-25 15:58:56',
NULL);
INSERT INTO `hi-sing`.`system_permissions` (`guard`, `parent_id`, `name`, `label`, `type`, `icon`, `weight`,
`created_at`, `updated_at`, `deleted_at`)
VALUES ('Admin', 62, 'operation-notification-update', '编辑', 'Menu', '', 80, '2023-07-28 10:16:50',
'2023-07-28 10:17:00', NULL);
INSERT INTO `hi-sing`.`system_permissions` (`guard`, `parent_id`, `name`, `label`, `type`, `icon`, `weight`,
`created_at`, `updated_at`, `deleted_at`)
VALUES ('Admin', 62, 'operation-notification-show', '详情', 'Menu', '', 80, '2023-07-28 13:17:08',
'2023-07-28 13:17:08', NULL);
SET FOREIGN_KEY_CHECKS = 1;