charinfotable.sql

Soul的源代码,类似于劲舞团之类的游戏

@stMp as smallint,
@bST as binary(10), 	-- 捞惑惑怕
@strBuff as binary(40),  	-- 滚橇 惑怕
@bLv as tinyint,		-- 饭骇
--@bSkP as tinyint,		-- Skill Point
@iSpP as int,		-- Sprite Point
@iFM as int,		-- 疙己档
@xPos as real,
@yPos as real,

@zPos as real,
@bZN as tinyint,		-- Zone Number
@Mcr as binary(120),	-- Macross
@bCl as tinyint,
@iGuild as int,
@bWarpPos as tinyint,	-- 何劝粮
@iExp as int
As
SET NOCOUNT on
begin tran
update dbo.charDetailInfo set _iMoney = @iMy, _iStoreMoney = @iSM, _Skill = @Sk,
_bStr = @bStr, _bVit = @bVit, _bInt = @bInt, _bMan = @bMan, _bDex = @bDex, /*, _shSpareST = @ssSST,*/
_stHp = @stHp, _stMp = @stMp, _bStrangeST = @bST, _strBuff = @strBuff, _bLevel = @bLv, 
/*_bSkillPoint = @bSkP,*/ _iSpritePoint = @iSpP, _iFame = @iFM, _xPos = @xPos,
_yPos = @yPos, _zPos = @zPos, _bZoneNumber = @bZN, _Macro =  @Mcr, _bClass = @bCl, _iGuild = @iGuild,
_bWarpPos = @bWarpPos , _iExp = @iExp 
where _charIndex = @iIndex
if @@Error <> 0
	begin
		rollback tran
	end
else
	begin
		commit tran
	end
go


/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
--drop table dbo.charInfo
create table charInfo
(
_charname char(16) not null,
_iShape int,
_bUnion tinyint,
_bJob tinyint,
_iClassShape int,	-- 朝俺,赣府,迫,促府狼 4何困狼 沥焊
_iAccountID int,
_CreateTime smalldatetime,
_charIndex int IDENTITY(1,1), --character Server 俊辑 积己等 key
_bSex tinyint -- 0:巢磊 1:咯磊
) -- 43BYTE


/*charInfo 俊 努矾胶飘 救等 index汲沥*/
--create index charInfo_CL2 on dbo.charInfo(_charname)
create index charInfo_CL3 on dbo.charInfo(_iAccountID)
/*charInfo 俊 努矾胶飘 等 index汲沥*/
alter table dbo.charInfo add constraint charInfo_CL primary key clustered(_charname)

/*trigger 累己*/
--array 肺 等 青捞 AFTER肺 救瞪版快 INSTEAD OF 甫 敬促
create trigger chartrigger_delete on [dbo].[charInfo] for Delete --instead of delete
as
SET NOCOUNT ON
insert  BKcharInfo 
select _charname,_iShape,_bUnion,_bJob,_iClassShape,_iAccountID,_CreateTime,_charIndex,_bSex,getdate()
from deleted
go
--drop trigger  chartrigger_delete

--insert dbo.charInfo values (@name,@iSh, @bUn, @bJob, 0/*classshape*/,@iAccountID,getdate())

--drop table dbo.charDetailInfo
create table charDetailInfo
(
_charIndex int not null, --character Server 俊辑 积己等 key
_iMoney int,
_iStoreMoney int,
_Skill binary(84),
_bStr tinyint,
_bVit tinyint,
_bInt tinyint,
_bMan tinyint,
_bDex tinyint,
--_shSpareST smallint, -- 饭骇1寸 3究 盒硅罐绰 status
_stHp smallint,
_stMp smallint,
_bStrangeST binary(10),
_strBuff binary(40),
_bLevel tinyint,
--_bSkillPoint tinyint, -- 胶懦 器牢飘
_iSpritePoint int,	-- 沥飞 器牢飘
_iFame int, -- 疙己档
_xPos real,
_yPos real,
_zPos real,
_bZoneNumber tinyint,
_Macro binary(120),
_bClass tinyint,
_iGuild int, -- 辨靛锅龋
_bWarpPos tinyint, -- 磷篮饶 登混酒 朝 谅钎 index
_iExp int --泅犁 版氰摹
) 

/*charInfo 俊 努矾胶飘 等 index汲沥*/
alter table dbo.charDetailInfo add constraint charDetailInfo_CL primary key clustered(_charIndex)
--alter table charT drop constraint charT_CL
/*trigger 累己*/
--array 肺 等 青捞 AFTER肺 救瞪版快 INSTEAD OF 甫 敬促
create trigger  DetailInfotrigger_delete on [dbo].[charDetailInfo] for Delete -- instead of delete
as
SET NOCOUNT ON
insert  BKcharDetailInfo 
select _charIndex,_iMoney,_iStoreMoney,_Skill,_bStr,_bVit,_bInt,_bMan,_bDex/*,_shSpareST*/,_stHp,_stMp,_bStrangeST,
_strBuff,_bLevel/*,_bSkillPoint*/,_iSpritePoint,_iFame,_xPos,_yPos,_zPos,_bZoneNumber,_Macro,_bClass,_iGuild,_bWarpPos,_iExp 
from deleted
go
--drop trigger  DetailInfotrigger_delete


alter table TestDB
Add
column_b varchar(20) NULL
go

/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
-- 10父俺狼 char 沥焊 火涝
declare @iIDCount int
declare @iControl int
declare @iCharCount int
declare @iCount int
declare @name char(16)
declare @put int
set @iIDCount = 30007
set @iControl = 0
set @iCharCount = 90001
set @iCount = 0
while @icount <= 9999--10000
begin
	if @iControl > 2 
	begin
		set @iIDCount = @iIDCount + 1
		set @iControl = 0
	end
	set @name = 'h' + cast( @iCharCount  as char(10) )
	exec s_insertChar @iIDCount,@name,'0','1','1','1','4','4' ,'4', '4' ,'4', '100','100','1.5', '1.6', '1.7', '6',@put output

	set @iCharCount = @iCharCount + 1
	set @iCount = @iCount + 1
	set @iControl = @iControl + 1
end

select count(_iShape) from dbo.charinfo
select * from dbo.charInfo where _charname = 'h90000'

/* 叼厚 单捞鸥 农扁 炼沥 */
--dbcc updateusage ('test1', 'dbo.charInfo')	
--sp_helpdb test1
--backup log test1 with truncate_only
--dbcc shrinkfile('test1_Log', 5)
-- 荤侩磊 单捞磐 海捞胶捞胶俊辑 颇老俊 老沥%狼 咯蜡 傍埃捞 巢档废 颇老 农扁甫 临烙
--dbcc shrinkdatabase(test1, 10)
--dbcc checkident(charInfo, reseed, 1) -- seed蔼 炼例

/* 叼厚 沥府 农扁 炼沥 */
--DBCC SHOWCONTIG ([test1.dbo.charInfo])	-- Table 狼 泅犁 荐霖 焊扁
-- charDetailInfo, charInfo, EquipItem, InvenItem, StoreItem,StoreItem2
DBCC SHOWCONTIG (StoreItem)
--dbcc indexdefrag -- 牢郸胶 其捞瘤甫 稠府利栏肺 犁沥纺篮 秦林瘤父 巢篮 傍埃阑 促矫 秦力 窍瘤绰 臼绰促 (lock阑 吧瘤 臼绰促)
--dbcc dbreindex -- 炼阿葛澜 (lock阑 吧骨肺 沥八锭父 秦具茄促)
--DBCC DBREINDEX唱 CREATE INDEX ... WITH DROP_EXISTING 
--dbcc dbreindex('test1.dbo.EquipItem', StoreIX, 90)  -- 箭磊 剐档捞促 梦梦且荐废 select绰 亮栏唱 insert绰 冻绢柳促
dbcc dbreindex('test1.dbo.charDetailInfo','',90) -- cool
dbcc dbreindex('test1.dbo.charInfo','',90) 
dbcc dbreindex('test1.dbo.EquipItem','',90) 
dbcc dbreindex('test1.dbo.InvenItem','',90) 
dbcc dbreindex('test1.dbo.StoreItem','',90) 
dbcc dbreindex('test1.dbo.StoreItem2','',90) 

--dbcc updateusage ({0|datablse_name} [, table_name [,index_id]]) 抛捞喉狼 何沥犬己阑 焊绊窍绊 荐沥窃
dbcc updateusage ('test1', 'dbo.charDetailInfo')	-- reindex 秦霖促.
dbcc updateusage ('test1', 'dbo.charInfo')
dbcc updateusage ('test1', 'dbo.EquipItem')
dbcc updateusage ('test1', 'dbo.InvenItem')
dbcc updateusage ('test1', 'dbo.StoreItem')
dbcc updateusage ('test1', 'dbo.StoreItem2')

--dbcc show_statistics (table_name,index_name) 烹拌 其捞瘤狼 郴侩 钎矫
--dbcc show_statistics ([dbo.charInfo], charInfo_CL)
--dbcc show_statistics ([dbo.InvenItem],ItemIX)
--select stats_date (tabnle_id, index_id) 付瘤阜 诀单捞飘 朝楼 钎矫
-- ex :: select stats_date (594101157, 1)

-- test
declare @put int
exec s_insertChar '1','hoon','0','1','1','1','4','4' ,'4', '4' ,'4', '100','100','1.5', '1.6', '1.7', '6',@put output
select @put

select * from charinfo

declare @put int
exec s_insertChar '1','hoon2','0','1','1','1','4','4' ,'4', '4' ,'4', '100','100','1.5', '1.6', '1.7', '6',@put output
select @put

declare @put int
exec s_deleteChar '1','5','hoon',@put output
select @put


select * from dbo.InvenItem
select * from dbo.StoreItem
select * from dbo.StoreItem2
select * from dbo.EquipItem
select * from dbo.charInfo
select * from dbo.charDetailInfo

exec s_deleteChar '19','hoon'

select * from dbo.BKStoreItem 
select * from dbo.BKStoreItem2
select * from dbo.BKEquipItem 
select * from dbo.BKInvenItem 
select * from dbo.BKcharInfo
select * from dbo.BKcharDetailInfo 


-- 昏力 --
delete from dbo.BKStoreItem 
delete from dbo.BKStoreItem2 
delete from dbo.BKEquipItem
delete from dbo.BKInvenItem 
delete from dbo.BKcharInfo  
delete from dbo.BKcharDetailInfo 

delete from dbo.InvenItem
delete from dbo.StoreItem 
delete from dbo.StoreItem2 
delete from dbo.EquipItem
delete from dbo.charInfo 
delete from dbo.charDetailInfo 


exec sp_lock

/*---------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------*/
select * from  dbo.StoreItem where _charIndex = 0
select * from dbo.StoreItem2

exec s_selectStore '12'


select * from  dbo.StoreItem where _charIndex = 10
select * from  dbo.charInfo where _charIndex = 10

declare @Test Table
(
_charIndex int, --character Server 俊辑 积己等 key
_storeCount tinyint, --character store 郴狼 Item 狼 锅龋 
_ItemDate int, -- Item 积己 锅龋
_ItemSN	smallint, -- Item 捞 积己 揪府倔
_ItemNo	smallint, -- Item Table 郴俊辑狼 绊蜡 锅龋
_ItemCount int, -- Item 狼 肮荐(荐樊屈老 版快)
_ItemLevel tinyint, -- Item狼 泅犁 饭骇
_CurDurability smallint, -- Item 狼 泅犁 郴备仿 
_SpOption1 tinyint, -- 漂荐可记1
_SpOption2 tinyint, -- 漂荐可记2
_SpOption3 tinyint, -- 漂荐可记3
_SpOption4 tinyint, -- 漂荐可记4
_NmOption1 tinyint, -- 老馆可记1
_NmOption2 tinyint, -- 老馆可记2
_NmOption3 tinyint  -- 老馆可记3
)

insert into @test select * from dbo.StoreItem where _charIndex = 10
update @test set _ItemNo = 20 where _storeCount = 1