lyfz_repos/经典版源码/document/数据库维护脚本/多多福数据库升级.sql

/*多多福增量备份及多个备份目录*/

-- 1.分店表; 

--判断表是否存在,不存在则创建;
if not exists(select * from dbo.sysobjects where id = object_id(N'CatalogInfo') and objectproperty(id,'IsTable') = 1)
begin
create table [dbo].[CatalogInfo](
	[enable]		[bit] default(1) not null,					/* 是否启用该分店实例 */
	[branch]		[nvarchar](24) not null,					/* 分店名称 */
	[branchid]		[nvarchar](32) not null,					/* 分店id */
	[clgname]		[nvarchar](24) not null,					/* 数据库名 */
	[clgport]		[int] default(0) null,						/* 数据库映射端口 */
	[clgsource]		[nvarchar](24) not null,					/* 数据库源(地址) */
	[clgaccount]	[nvarchar](24) not null,					/* 数据库登陆账号 */
	[clgpassword]	[nvarchar](16) not null,					/* 数据库登陆密码 */
	
	/* 主键分店ID */
	constraint [PK_CatalogInfo] primary key clustered
	(
		[branchid] asc
	) with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary],

) on [primary]
end
go

-- 2.增量备份表; 
--判断表是否存在,不存在则创建;
if not exists(select * from dbo.sysobjects where id = object_id(N'IncrementalBackup') and objectproperty(id,'IsTable') = 1)
begin
create table [dbo].[IncrementalBackup](
	[isbackup]		[bit] default(0) not null,					/* 备份状态:false未备份,true已备份 */
	[branchid]		[nvarchar](32) not null,					/* 分店ID */
	[opttime]		datetime default(getdate()),				/* 操作的时间 */
	[order]			[nvarchar](32) not null,					/* 操作的订单号 */
	[photoType]		[tinyint] default(0) not null,				/* 操作的相片类型 原片==1,初修片==2,精修片==3,设计片==4,原片备份==5,初修备份==6,精修备份==7,设计备份==8*/
	[content]		[nvarchar](max) null,						/* 增量内容,订单添加或修改的相片名称数组,以"|"作为分隔符 */
	[userid]		[nvarchar](64) not null,					/* 用户id (ftp服务端,直接用0表示) */
	[username]		[nvarchar](16) not null,					/* 用户名 (ftp使用"ftp")*/
	[baktime]		datetime null,								/* 备份完成的时间 */

	/* 联合主键 */
	constraint [PK_IncrementBackup] primary key clustered
	(
		[branchid] asc,
		[order] asc,
		[opttime] asc
	)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary],

) on [primary]
end
go

-- 3.共享表; 

--判断表是否存在,不存在则创建;
if not exists(select * from dbo.sysobjects where id = object_id(N'NetShareInfo') and objectproperty(id,'IsTable') = 1)
begin
create table [dbo].[NetShareInfo](
	[enable]		[bit] default(1) not null,					/* 启动状态:false禁用,true启用 */
	[branchid]		[nvarchar](32) not null,					/* 分店id(域名) */
	[sharePath]		[nvarchar](64) not null,					/* 共享目录名 */
	[mincapacity]	[tinyint] default(5) not null,				/* 共享目录所在硬盘的最小可用容量(单位G byte),当实际可用容量值小于该值时,停止使用硬盘 */
	[maxcapacity]	[tinyint] default(35) not null,				/* 共享目录所在硬盘的最大可用容量(单位G byte),当实际可用容量值小于该值时,使用下一共享目录 */
	[photoType]		[tinyint] not null,							/* 共享相片类型:原片==1,初修片==2,精修片==3,设计片==4,原片备份==5,初修备份==6,精修备份==7,设计备份==8 */
	[priority]		[tinyint] default(1) not null,				/* 某分店下,相同共享相片类型的多个共享目录的优先使用级别 */

	/* 主键 */
	constraint [PK_NetShareInfo] primary key clustered
	(
		--[branchid] asc,--只要共享目录作为主键;
		[sharePath] asc
	) with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary],
) on [primary]
end

go
--可用容量最小设置值为5G;
alter table [dbo].[NetShareInfo] with check add check([mincapacity] >= 5)
alter table [dbo].[NetShareInfo] with check add check([maxcapacity] >= 35)
go
--最低权限值为1,无上限;
alter table [dbo].[NetShareInfo] with check add check([priority] >= 1)

go 

-- 4.触发器
if (object_id('tgr_photomanager_insert','tr') is not null)
	drop trigger tgr_photomanager_insert 

go
create trigger tgr_photomanager_insert on dindan for insert--订单表插入数据时;
as
--提交事务;
begin transaction
	declare @branchid nvarchar(32)		--本店域名;
	declare @order nvarchar(24)			--订单号;
	declare @status nvarchar(8)			--订单的取件状态;
	--获取path中的域名值;
	select top 1 @branchid = path1 from [path]
	--获取inserted表更新的dindan数据;
	select @order = id, @status = status3 from inserted
	if ( @status = 'OK')
		insert into [dbo].[photoManager]([branchid],[order],[takestatus])values(@branchid,@order,1)
	else
		insert into [dbo].[photoManager]([branchid],[order],[takestatus])values(@branchid,@order,0)
commit transaction
go

--update类型触发器;
if (object_id('tgr_photomanager_update','tr') is not null)
	drop trigger tgr_photomanager_update

go
create trigger tgr_photomanager_update on dindan for update--订单表更新数据时;
as
begin transaction
	declare @branchid nvarchar(32)		--本店域名;
	declare @order nvarchar(24)			--订单号;
	declare @status nvarchar(8)			--订单取件状态;
	declare @stime nvarchar(24)			--订单取件时间;
	declare @count int					--查询某订单在PhotoManager表中的数量;

	--获取path中的域名值;
	select top 1 @branchid = path1 from [path]
	--获取inserted表更新的dindan数据;
	select @order = id, @status = status3, @stime = time5 from inserted
	select @count=count(*) from photomanager where [order] = @order

	if ( @status = 'OK' and @count > 0 )
		update [photoManager] set [takestatus] = 1,[taketime] = @stime where [order] = @order	-- 更新取件状态和取件时间;
	else if ( @status <> 'OK' and @count > 0 )
		update [photoManager] set [takestatus] = 0,[taketime] = @stime where [order] = @order	-- 已取件后改回未取件;
	else if ( @status = 'OK' and @count = 0 )
		insert into [photoManager]([branchid],[order],[takestatus],[taketime])values(@branchid,@order,1,@stime)	-- 无记录则补insert;
	else if ( @status <> 'OK' and @count = 0 )
		insert into [photoManager]([branchid],[order],[takestatus])values(@branchid,@order,0)	-- 无记录则补insert;
commit transaction
go

-- 取件表; 
--判断表是否存在,不存在则创建;
if not exists(select * from dbo.sysobjects where id = object_id(N'PhotoManager') and objectproperty(id,'IsTable') = 1)
begin
create table [dbo].[PhotoManager](
	[branchid]		[nvarchar](32) not null,					/* 分店id */
	[order]			[nvarchar](24) not null,					/* 主键订单号(唯一) */
	[takestatus]	[bit] default(0) not null,					/* 取件状态:false未取,true已取 */
	[taketime]		[nvarchar](24) null,						/* 整个订单的取件时间 */

	[opdel]			[bit] default(0) not null,					/* 原片删除状态 */
	[epdel]			[bit] default(0) not null,					/* 初修删除状态 */
	[fpdel]			[bit] default(0) not null,					/* 精修删除状态 */
	[dpdel]			[bit] default(0) not null,					/* 设计删除状态 */

	[opbdel]		[bit] default(0) not null,					/* 原片备份删除状态 */
	[epbdel]		[bit] default(0) not null,					/* 初修备份删除状态 */
	[fpbdel]		[bit] default(0) not null,					/* 精修备份删除状态 */
	[dpbdel]		[bit] default(0) not null,					/* 设计备份删除状态 */

	[opdeltm]		[nvarchar](24)  null,						/* 原片删除时间 */
	[epdeltm]		[nvarchar](24)  null,						/* 初修删除时间 */
	[fpdeltm]		[nvarchar](24)  null,						/* 精修删除时间 */
	[dpdeltm]		[nvarchar](24)  null,						/* 设计删除时间 */

	[opbdeltm]		[nvarchar](24)  null,						/* 原片备份删除时间 */
	[epbdeltm]		[nvarchar](24)  null,						/* 初修备份删除时间 */
	[fpbdeltm]		[nvarchar](24)  null,						/* 精修备份删除时间 */
	[dpbdeltm]		[nvarchar](24)  null,						/* 设计备份删除时间 */

	/* 主键订单号*/
	constraint [PK_PhotoManager] primary key clustered
	(
		[branchid] asc,
		[order] asc
	) with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary],

) on [primary]
end
go


--更新dindan表,使之更新PhotoManager表;
select identity(int,1,1) as autoid, id, VisitTime8 into #temp1 from dindan
declare @maxid	int
declare @id		nvarchar(50)
declare @v8		nvarchar(20)

select @maxid = max(autoid) from #temp1

while @maxid > 0
begin
	select @id = id, @v8 = VisitTime8 from #temp1 where autoid = @maxid
	update dindan set VisitTime8 = VisitTime8 where id = @id
	set @maxid = @maxid -1
end
drop table #temp1