97 lines
5.1 KiB
Transact-SQL
97 lines
5.1 KiB
Transact-SQL
-- 授权码模块表结构、菜单和项目编号配置
|
||
-- 数据库：SQL Server
|
||
|
||
IF NOT EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_license]') AND type IN ('U'))
|
||
BEGIN
|
||
CREATE TABLE [dbo].[sys_license] (
|
||
[license_id] bigint IDENTITY(1,1) NOT NULL,
|
||
[license_code] nvarchar(500) NULL,
|
||
[start_time] datetime2(7) NULL,
|
||
[expire_time] datetime2(7) NULL,
|
||
[status] char(1) NULL,
|
||
[active_time] datetime2(7) NULL,
|
||
[active_by] nvarchar(64) NULL,
|
||
[create_by] nvarchar(64) NULL,
|
||
[create_time] datetime2(7) NULL,
|
||
[update_by] nvarchar(64) NULL,
|
||
[update_time] datetime2(7) NULL,
|
||
[remark] nvarchar(500) NULL,
|
||
CONSTRAINT [PK_sys_license] PRIMARY KEY CLUSTERED ([license_id] ASC)
|
||
);
|
||
|
||
EXEC sp_addextendedproperty 'MS_Description', N'授权码表', 'SCHEMA', N'dbo', 'TABLE', N'sys_license';
|
||
EXEC sp_addextendedproperty 'MS_Description', N'授权ID', 'SCHEMA', N'dbo', 'TABLE', N'sys_license', 'COLUMN', N'license_id';
|
||
EXEC sp_addextendedproperty 'MS_Description', N'授权码', 'SCHEMA', N'dbo', 'TABLE', N'sys_license', 'COLUMN', N'license_code';
|
||
EXEC sp_addextendedproperty 'MS_Description', N'授权开始时间', 'SCHEMA', N'dbo', 'TABLE', N'sys_license', 'COLUMN', N'start_time';
|
||
EXEC sp_addextendedproperty 'MS_Description', N'授权结束时间', 'SCHEMA', N'dbo', 'TABLE', N'sys_license', 'COLUMN', N'expire_time';
|
||
EXEC sp_addextendedproperty 'MS_Description', N'授权状态（0未激活 1已激活 2已过期）', 'SCHEMA', N'dbo', 'TABLE', N'sys_license', 'COLUMN', N'status';
|
||
END
|
||
GO
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM [dbo].[sys_config] WHERE [config_key] = N'fortucky.project.no')
|
||
BEGIN
|
||
INSERT INTO [dbo].[sys_config]
|
||
([config_name], [config_key], [config_value], [config_type], [create_by], [create_time], [remark])
|
||
VALUES
|
||
(N'项目编号', N'fortucky.project.no', N'FT546', N'Y', N'admin', SYSDATETIME(), N'用于授权码项目匹配');
|
||
END
|
||
GO
|
||
|
||
SET IDENTITY_INSERT [dbo].[sys_menu] ON;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM [dbo].[sys_menu] WHERE [menu_id] = 2100)
|
||
BEGIN
|
||
INSERT INTO [dbo].[sys_menu]
|
||
([menu_id], [menu_name], [parent_id], [order_num], [path], [component], [query], [route_name], [is_frame], [is_cache], [menu_type], [visible], [status], [perms], [icon], [create_by], [create_time], [update_by], [update_time], [remark])
|
||
VALUES
|
||
(2100, N'授权管理', 1, 99, N'license', N'system/license/index', N'', N'', 1, 0, N'C', N'0', N'0', N'system:license:list', N'validCode', N'admin', SYSDATETIME(), N'', NULL, N'授权管理菜单');
|
||
END
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM [dbo].[sys_menu] WHERE [menu_id] = 2101)
|
||
BEGIN
|
||
INSERT INTO [dbo].[sys_menu]
|
||
([menu_id], [menu_name], [parent_id], [order_num], [path], [component], [query], [route_name], [is_frame], [is_cache], [menu_type], [visible], [status], [perms], [icon], [create_by], [create_time], [update_by], [update_time], [remark])
|
||
VALUES
|
||
(2101, N'授权查询', 2100, 1, N'', N'', N'', N'', 1, 0, N'F', N'0', N'0', N'system:license:query', N'#', N'admin', SYSDATETIME(), N'', NULL, N'');
|
||
END
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM [dbo].[sys_menu] WHERE [menu_id] = 2102)
|
||
BEGIN
|
||
INSERT INTO [dbo].[sys_menu]
|
||
([menu_id], [menu_name], [parent_id], [order_num], [path], [component], [query], [route_name], [is_frame], [is_cache], [menu_type], [visible], [status], [perms], [icon], [create_by], [create_time], [update_by], [update_time], [remark])
|
||
VALUES
|
||
(2102, N'授权新增', 2100, 2, N'', N'', N'', N'', 1, 0, N'F', N'0', N'0', N'system:license:add', N'#', N'admin', SYSDATETIME(), N'', NULL, N'');
|
||
END
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM [dbo].[sys_menu] WHERE [menu_id] = 2103)
|
||
BEGIN
|
||
INSERT INTO [dbo].[sys_menu]
|
||
([menu_id], [menu_name], [parent_id], [order_num], [path], [component], [query], [route_name], [is_frame], [is_cache], [menu_type], [visible], [status], [perms], [icon], [create_by], [create_time], [update_by], [update_time], [remark])
|
||
VALUES
|
||
(2103, N'授权修改', 2100, 3, N'', N'', N'', N'', 1, 0, N'F', N'0', N'0', N'system:license:edit', N'#', N'admin', SYSDATETIME(), N'', NULL, N'');
|
||
END
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM [dbo].[sys_menu] WHERE [menu_id] = 2104)
|
||
BEGIN
|
||
INSERT INTO [dbo].[sys_menu]
|
||
([menu_id], [menu_name], [parent_id], [order_num], [path], [component], [query], [route_name], [is_frame], [is_cache], [menu_type], [visible], [status], [perms], [icon], [create_by], [create_time], [update_by], [update_time], [remark])
|
||
VALUES
|
||
(2104, N'授权删除', 2100, 4, N'', N'', N'', N'', 1, 0, N'F', N'0', N'0', N'system:license:remove', N'#', N'admin', SYSDATETIME(), N'', NULL, N'');
|
||
END
|
||
|
||
SET IDENTITY_INSERT [dbo].[sys_menu] OFF;
|
||
GO
|
||
|
||
IF EXISTS (SELECT 1 FROM [dbo].[sys_role] WHERE [role_id] = 1)
|
||
BEGIN
|
||
INSERT INTO [dbo].[sys_role_menu] ([role_id], [menu_id])
|
||
SELECT 1, menu_id
|
||
FROM (VALUES (2100), (2101), (2102), (2103), (2104)) AS menus(menu_id)
|
||
WHERE NOT EXISTS (
|
||
SELECT 1
|
||
FROM [dbo].[sys_role_menu] rm
|
||
WHERE rm.[role_id] = 1 AND rm.[menu_id] = menus.menu_id
|
||
);
|
||
END
|
||
GO
|