Admin/DB.sql

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

--系统日志
CREATE TABLE [dbo].[uap_role_logs] (
    [id]      BIGINT        IDENTITY (1, 1) NOT NULL,
    [userid]  INT           NOT NULL,
    [logtime] DATETIME      DEFAULT (getdate()) NOT NULL,
    [ip]      VARCHAR (17)  NOT NULL,
    [operate] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
);
insert into uap_role_logs(userid,ip,operate)values(1,'::1','INSTALL')
go
--系统对象
CREATE TABLE [dbo].[uap_role] (
    [id]        INT            IDENTITY (0, 1) NOT NULL,
    [ireg]      BIGINT         NOT NULL,
    [iactive]   BIGINT         DEFAULT ((0)) NOT NULL,
    [_active_]  INT            DEFAULT ((0)) NOT NULL,
    [_sysid_]   VARCHAR (200)  NULL,
    [_level_]   INT            NULL,
    [isactive]  AS             (case when [iactive]>(0) AND [_active_]>(0) then (1) else (0) end),
    [iparent]   INT            NULL,
    [itype]     INT            NOT NULL,
    [name]      VARCHAR (50)   NOT NULL,
    [tag]       VARCHAR (100)  NULL,
    [bak]       VARCHAR (1000) NULL,
    [subtype]   TINYINT        DEFAULT ((0)) NOT NULL,
    [latitude]  REAL           DEFAULT ((0)) NOT NULL,
    [longitude] REAL           DEFAULT ((0)) NOT NULL,
    [address]   VARCHAR (500)  NULL,
    [phone]     VARCHAR (15)   NULL,
    [username]  NVARCHAR (10)  NULL,
    [userpass]  VARCHAR (32)   NULL,
    PRIMARY KEY CLUSTERED ([id] ASC),
    UNIQUE NONCLUSTERED ([_sysid_] ASC),
    FOREIGN KEY ([iparent]) REFERENCES [dbo].[uap_role] ([id]),
    CHECK ([id]<>[iparent])
);
GO
CREATE UNIQUE NONCLUSTERED INDEX [uap_role$idx$username] ON [dbo].[uap_role]([username] ASC) WHERE ([username] IS NOT NULL);
go
insert into uap_role(name,ireg,iactive,_active_,_sysid_,_level_,itype)values('InsAG',0,1,1,'0',0,0)
go
--刷新启用项
create proc uap_role$refresh_active_(
	@sysid varchar(200)
)as
	declare @ipar int,@lv int
	set nocount on
begin transaction
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	declare uap_rolecur cursor for
		select distinct iparent,_level_ from uap_role r WITH(HOLDLOCK) where _sysid_ like @sysid+'|%' and (select COUNT(0) from uap_role where iparent=r.id)>0 order by _level_ asc
	open uap_rolecur
	fetch next from uap_rolecur into @ipar,@lv
		while @@fetch_status=0 begin
			update uap_role set _active_=(select isactive from uap_role where id=@ipar) where iparent=@ipar
			fetch next from uap_rolecur into @ipar,@lv
		end
	close uap_rolecur
	deallocate uap_rolecur