|
You last visited: Today at 16:37
Advertisement
Development Project Of Silkroad Server Files !
Discussion on Development Project Of Silkroad Server Files ! within the SRO PServer Guides & Releases forum part of the SRO Private Server category.
02/07/2012, 23:57
|
#1
|
elite*gold: 7 
Join Date: May 2010
Posts: 2,115
Received Thanks: 2,374
|
Development Project Of Silkroad Server Files !
Hello ,
Those are my last days in SRO Community Here , after 1 week i will leave to start work in new project out of SRO which will take all My time ,
So i decided before i Leave To make full explain project and guide how to edit all parts in databases/applications~Client\Modules/pk2 (take a look at the project script to got more understand)
We will start to learn out that with each other, tell we can made new game So the life will be more better ^_ ^
And after we will finish that project I will release from time to time some of leaked server files !
Oky let's discussion about what script we are going to work on
1 - Explain of all stored procedures .
2 - Explain of all databases/tables and it's data content and how it works .
3 - Client side Pk2/applications/protection .
4 - dev. of agent .
5 - Explain of modules and how they work in real .
6 - After we finish all of that we will start to make full edit to make new game (System) , I mean for example ..
This project will depends on VSRO/BR/SWSRO/CSRO< Server Files
How this thread will going on?
At first we need only people who will join us, so we could help each other , as I can't finish all those parts in only 1 week so i will do as fast as i can but maybe i forgot or lose a thing so just mind me , also if you got experiences or skills with something we are going to explain it in our Development project So we will be thanks if you share with us,
Ideas is sure accepted .
I will start my First part of this project tomorrow as it's too late here ,
This Thread Will be updated every new part is done also we will move step by step .
Hope at last we done this Project as it will take a big effect and also change in all MMO games not with SRO community only and also it will help u at last .
-------------------------------------
We are going today to Start Our First Steps about Stored Procedures , When we were making emulators we were develop it to read all the system from it's packets , I mean for example like add remove item and so
But with server files they extract those packets to add them into the stored procedures, as 85% of GameServer System is add in Stored Procedures So we could edit on them easy and they are the main discussion as i see for this Project .
Let's Start :
So let's Start out first step with stored procedures With BR Files :
From Time to time i will add group of stored procedures, Also we will begain with Shard db then Shard log then Account db
Let's go .
Named : __GoldTracePerMin
PHP Code:
/****** Object: StoredProcedure [dbo].[__GoldTracePerMin] Script Date: 02/08/2012 11:16:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[__GoldTracePerMin]
AS
DECLARE [MENTION=392237]sum[/MENTION]CharGold bigint
SELECT [MENTION=392237]sum[/MENTION]CharGold = SUM(RemainGold) FROM _Char
DECLARE [MENTION=392237]sum[/MENTION]ChestGold bigint
SELECT [MENTION=392237]sum[/MENTION]ChestGold = SUM(Gold) FROM _AccountJID
DECLARE [MENTION=392237]sum[/MENTION]GuildChestGold bigint
SELECT [MENTION=392237]sum[/MENTION]GuildChestGold = SUM(Gold) FROM _Guild
DECLARE [MENTION=392237]sum[/MENTION]GuildWarGold bigint
SELECT [MENTION=392237]sum[/MENTION]GuildWarGold = SUM(LodgedGold) FROM _GuildWar
DECLARE [MENTION=392237]sum[/MENTION]GuildBootyGold bigint
SELECT [MENTION=392237]sum[/MENTION]GuildBootyGold = SUM(Booty) FROM _Guild
DECLARE @OffsetCharGold bigint
DECLARE @OffsetChestGold bigint
DECLARE @OffsetGuildChestGold bigint
DECLARE @OffsetGuildWarGold bigint
DECLARE @OffsetGuildBootyGold bigint
SELECT
@OffsetCharGold = [MENTION=392237]sum[/MENTION]CharGold - SumCharGold,
@OffsetChestGold = [MENTION=392237]sum[/MENTION]ChestGold - SumChestGold,
@OffsetGuildChestGold = [MENTION=392237]sum[/MENTION]GuildChestGold - SumGuildChestGold,
@OffsetGuildWarGold = [MENTION=392237]sum[/MENTION]GuildWarGold - SumGuildWarGold,
@OffsetGuildBootyGold = [MENTION=392237]sum[/MENTION]GuildBootyGold - SumGuildBootyGold
FROM __GoldTrace
WHERE idx = (SELECT MAX(IDX) FROM __GoldTrace)
INSERT INTO __GoldTrace (SumCharGold, SumChestGold, SumGuildChestGold, SumGuildWarGold, SumGuildBootyGold, OffsetCharGold, OffsetChestGold, OffsetGuildChestGold, OffsetGuildWarGold, OffsetGuildBootyGold, dEventTime)
VALUES [MENTION=392237]sum[/MENTION]CharGold, [MENTION=392237]sum[/MENTION]ChestGold, [MENTION=392237]sum[/MENTION]GuildChestGold, [MENTION=392237]sum[/MENTION]GuildWarGold, [MENTION=392237]sum[/MENTION]GuildBootyGold, @OffsetCharGold, @OffsetChestGold, @OffsetGuildChestGold, @OffsetGuildWarGold, @OffsetGuildBootyGold, getdate() )
As This seems to you it's the packet which summon the gold in game and also reload when the item used or we can say increase or decrease by amount of gold , Without this Gold in game won't load or reload .
Let's make small Discussion about this
PHP Code:
FROM _Char
FROM _AccountJID
FROM _Guild
FROM _GuildWar
Those are the tables which recall the gold amount and add from them
We Could say this packet is the gold system in game , But maybe not the full system as there's another parts in stored pro. related to gold load and reload .
PHP Code:
SUM(RemainGold)
SUM(Gold)
SUM(LodgedGold)
SUM(Booty)
and so , those are the columns in tables which contain the gold data .
PHP Code:
DECLARE @OffsetCharGold bigint
DECLARE @OffsetChestGold bigint
DECLARE @OffsetGuildChestGold bigint
DECLARE @OffsetGuildWarGold bigint
DECLARE @OffsetGuildBootyGold bigint
Those explain them selves here
PHP Code:
DECLARE [MENTION=392237]sum[/MENTION]CharGold bigint
SELECT [MENTION=392237]sum[/MENTION]CharGold = SUM(RemainGold) FROM _Char
DECLARE [MENTION=392237]sum[/MENTION]ChestGold bigint
SELECT [MENTION=392237]sum[/MENTION]ChestGold = SUM(Gold) FROM _AccountJID
DECLARE [MENTION=392237]sum[/MENTION]GuildChestGold bigint
SELECT [MENTION=392237]sum[/MENTION]GuildChestGold = SUM(Gold) FROM _Guild
DECLARE [MENTION=392237]sum[/MENTION]GuildWarGold bigint
SELECT [MENTION=392237]sum[/MENTION]GuildWarGold = SUM(LodgedGold) FROM _GuildWar
With this part
PHP Code:
SELECT
@OffsetCharGold = [MENTION=392237]sum[/MENTION]CharGold - SumCharGold,
@OffsetChestGold = [MENTION=392237]sum[/MENTION]ChestGold - SumChestGold,
@OffsetGuildChestGold = [MENTION=392237]sum[/MENTION]GuildChestGold - SumGuildChestGold,
@OffsetGuildWarGold = [MENTION=392237]sum[/MENTION]GuildWarGold - SumGuildWarGold,
@OffsetGuildBootyGold = [MENTION=392237]sum[/MENTION]GuildBootyGold - SumGuildBootyGold
FROM __GoldTrace
WHERE idx = (SELECT MAX(IDX) FROM __GoldTrace)
This Part to collect the data which is needed to load in game to insert in a table
PHP Code:
INSERT INTO __GoldTrace (SumCharGold, SumChestGold, SumGuildChestGold, SumGuildWarGold, SumGuildBootyGold, OffsetCharGold, OffsetChestGold, OffsetGuildChestGold, OffsetGuildWarGold, OffsetGuildBootyGold, dEventTime)
VALUES [MENTION=392237]sum[/MENTION]CharGold, [MENTION=392237]sum[/MENTION]ChestGold, [MENTION=392237]sum[/MENTION]GuildChestGold, [MENTION=392237]sum[/MENTION]GuildWarGold, [MENTION=392237]sum[/MENTION]GuildBootyGold, @OffsetCharGold, @OffsetChestGold, @OffsetGuildChestGold, @OffsetGuildWarGold, @OffsetGuildBootyGold, getdate() )
Here is the part which Insert those values in __GoldTrace Table .
Any Part i forgot Hope you mind me plz
Every 1 Stored Pro. will explained same as this one .
Hope It's appear as easy to be understand
-----------------------------------------------
Named : _ActiveteCharCOS
PHP Code:
/****** Object: StoredProcedure [dbo].[_ActivateCharCOS] Script Date: 02/08/2012 11:42:37 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[_ActivateCharCOS]
@COSDBID int,
@itemID int
AS
update _CharCos set State = 1 where ID = @COSDBID
if( @@error <> 0 or @@rowcount = 0 )
begin
return -1
end
return 1
As the prevue Packet explain it self, It's the packet which accept the summon of any COS in game by a player char , without this packet u can't summon any COS/Pet In game,
When u summon ur COS it Insert aka update at _CharCos table the State of this COS to 1 as it's summoned and also when u load for first time in game it's read that the Statue and if it's 1 so u will find ur COS is already summoned ,
PHP Code:
if( @@error <> 0 or @@rowcount = 0 )
begin
return -1
end
return 1
This part to remove any error could happen in reading values(State) as he made the State Start with -1 and end with 1 , so any another value in Statue column will be changed to 0 to prevent any error or You GameServer may take overflaw when this player who's have This COS which got wrong Statue Load in game .
Also it remove any other errors so if error occur , it set the value to 0 in any column in COS table (_CharCos) . -----------------------------------------
Named : _ADD_ITEM_EXTERN
PHP Code:
/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN] Script Date: 02/08/2012 12:01:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN]
@charname varchar(64),
@codename varchar(128),
@data int,
@opt_level int
as
declare @charid int
declare @ref_item_id int
declare @link_to_item int
declare @inv_capacity int
set @inv_capacity = 45
if (not exists (select * from _RefObjCommon where Codename128 = @codename))
begin
raiserror('unknown item: %s', 1, 16, @codename)
return -1
end
if (not exists (select * from _char with (nolock) where charname16 = @charname))
begin
raiserror('not existing character: %s', 1, 16, @charname)
return -2
end
select @charid = charid, @inv_capacity = InventorySize from _char with (nolock) where charname16 = @charname
if (not exists (select * from _inventory with (nolock) where charid = @charid and slot >= 13 and slot < @inV_capacity and itemid = 0))
begin
raiserror('Inventory Full: %s', 1, 16, @charname)
return -3 -- inventory full
end
declare @empty_slot int
select top 1 @empty_slot = slot from _inventory with (nolock) where charid = @charid and slot >= 13 and slot < @inV_capacity and itemid = 0 order by slot
set @ref_item_id = 0
set @link_to_item = 0
select @ref_item_id = id, @link_to_item = link from _RefObjCommon where codename128 = @codename
if (@ref_item_id = 0 or @ref_item_id is null)
begin
raiserror('@RefItemID == NULL: %s', 1, 16, @charname)
return -4
end
if (@link_to_item = 0 or @link_to_item is null)
begin
raiserror('RefItem Link == NULL: %s', 1, 16, @charname)
return -5
end
declare [MENTION=2683706]Tid[/MENTION]1 int
declare [MENTION=2683706]Tid[/MENTION]2 int
declare [MENTION=2683706]Tid[/MENTION]3 int
declare [MENTION=2683706]Tid[/MENTION]4 int
select [MENTION=2683706]Tid[/MENTION]1 = TypeID1, [MENTION=2683706]Tid[/MENTION]2 = TypeID2, [MENTION=2683706]Tid[/MENTION]3 = TypeID3, [MENTION=2683706]Tid[/MENTION]4 = TypeID4 from _RefObjCommon where ID = @ref_item_id
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
if [MENTION=2683706]Tid[/MENTION]1 <> 3)
begin
raiserror('not item: %s, %s', 1, 16, @charname, @codename)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 1)
set @IS_EQUIP = 1
else if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 2 and [MENTION=2683706]Tid[/MENTION]3 = 1 and [MENTION=2683706]Tid[/MENTION]4 = 1 or [MENTION=2683706]Tid[/MENTION]4 = 2 ) )
set @IS_PET = 1
declare @item_id bigint
set @item_id = 0
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
if (@IS_EQUIP = 1)
begin
declare @dur int
select @dur = Dur_L from _RefObjItem where ID = @link_to_item
set @data = @dur
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
declare [MENTION=508836]remain[/MENTION]_gold bigint
select [MENTION=508836]remain[/MENTION]_gold = remaingold from _char where charid = @charid
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ป๓มกฐณฦํ (รึผฑศฃ)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
exec _STRG_ADD_EQUIP_NORMAL @item_id output, @dummy_serial_number output, 6, @charid, @empty_slot, @ref_item_id, @opt_level, 0, @data
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
end
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
begin
set @data = @max_count
end
end
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ป๓มกฐณฦํ (รึผฑศฃ)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
exec _STRG_ADD_EXPENDABLE @item_id output, @dummy_serial_number output, 6, @charid, @empty_slot, @ref_item_id, @data
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
end
if (@item_id > 0)
return 1
else
begin
raiserror('failed for unknown reason: %s, %s', 1, 16, @charname, @codename)
return -7
end
This Packet is something Hard ,
Let's Start a few Explain about it ,
This Packet is for summon or load the data of items in game of a char,
It's check all item data like blues and states and opt level (+) and so
also it's check after that, that the item is active and already found in _RefObjCommon table ,
It's also check where's the item in inventory (Slot number) to load the item in the right place ,
Also it contain the error module so any unknown error occurs The GameServer will DC this Char .
PHP Code:
@charname varchar(64),
@codename varchar(128),
@data int,
@opt_level int
as
declare @charid int
declare @ref_item_id int
declare @link_to_item int
declare @inv_capacity int
set @inv_capacity = 45
That part for data reader and also gives the max inventory size in game as in BR files the max ever inventory size is 45 ,
PHP Code:
if (not exists (select * from _RefObjCommon where Codename128 = @codename))
begin
raiserror('unknown item: %s', 1, 16, @codename)
return -1
end
Here is the module packet of error (If not exists)
So if GameServer don't find the current item at RefObjCommon table at Codename128 (Gameserver is searching by item name for example ITEM_CH_...)
PHP Code:
raiserror('unknown item: %s', 1, 16, @codename)
return -1
This unkown error at %=item id @Codename , will appear at GameServer
and also will set that item to -1 , so GameServer won't show it in game and might the Char DC in First load (Login) time .
PHP Code:
if (not exists (select * from _char with (nolock) where charname16 = @charname))
begin
raiserror('not existing character: %s', 1, 16, @charname)
return -2
end
This is a part same related to the error packet
But that's if item found for non existing char , return -2 so the gameserver won't see that item again .
PHP Code:
select @charid = charid, @inv_capacity = InventorySize from _char with (nolock) where charname16 = @charname
This is part of packet which load the data in game , It's make connection between every data like at this example
PHP Code:
@charid = charid, @inv_capacity = InventorySize from _char
and
PHP Code:
where charname16 = @charname
To load out the char name in game ,
PHP Code:
if (not exists (select * from _inventory with (nolock) where charid = @charid and slot >= 13 and slot < @inV_capacity and itemid = 0))
begin
raiserror('Inventory Full: %s', 1, 16, @charname)
return -3 -- inventory full
end
This is an part also from error packet , so if it found item is out of slots or 2 items in same slots or in wrong slot
So it return to -3 item deleted , and if u were in game and need to add item while ur inventory is full it , GameServer gives packet of inventory is full
PHP Code:
declare @empty_slot int
select top 1 @empty_slot = slot from _inventory with (nolock) where charid = @charid and slot >= 13 and slot < @inV_capacity and itemid = 0 order by slot
set @ref_item_id = 0
set @link_to_item = 0
select @ref_item_id = id, @link_to_item = link from _RefObjCommon where codename128 = @codename
This if there's an empty slot in ur inventory in game and need to take item by exchange or buy it from small or collect it from mobs drop or any drop and so it search for the top empty slot to add this item in and it's already check if the item already in _RefObjCommon and it's imformation cuz some items u can't pick up it and so ..
PHP Code:
if (@ref_item_id = 0 or @ref_item_id is null)
begin
raiserror('@RefItemID == NULL: %s', 1, 16, @charname)
return -4
end
if (@link_to_item = 0 or @link_to_item is null)
begin
raiserror('RefItem Link == NULL: %s', 1, 16, @charname)
return -5
end
This error module is something for the un-avilable errors in game !
what i mean ! , if the item current insert in game but after that by some way the service of this item change to 0 or delete from RefObjCommon so GameServer fives error with Item id and return -4 = no action done ,
if there isn't link for this item or the link column is null (u will understand more when we explain the tables side) gameserver will gives u error and return -5 = no action also
PHP Code:
declare [MENTION=2683706]Tid[/MENTION]1 int
declare [MENTION=2683706]Tid[/MENTION]2 int
declare [MENTION=2683706]Tid[/MENTION]3 int
declare [MENTION=2683706]Tid[/MENTION]4 int
select [MENTION=2683706]Tid[/MENTION]1 = TypeID1, [MENTION=2683706]Tid[/MENTION]2 = TypeID2, [MENTION=2683706]Tid[/MENTION]3 = TypeID3, [MENTION=2683706]Tid[/MENTION]4 = TypeID4 from _RefObjCommon where ID = @ref_item_id
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
This is the part where this packet read from _RefObjCommon
PHP Code:
if [MENTION=2683706]Tid[/MENTION]1 <> 3)
begin
raiserror('not item: %s, %s', 1, 16, @charname, @codename)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 1)
set @IS_EQUIP = 1
else if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 2 and [MENTION=2683706]Tid[/MENTION]3 = 1 and [MENTION=2683706]Tid[/MENTION]4 = 1 or [MENTION=2683706]Tid[/MENTION]4 = 2 ) )
set @IS_PET = 1
declare @item_id bigint
set @item_id = 0
This is common errors and known and also this packet fix it
PHP Code:
raiserror('not item: %s, %s', 1, 16, @charname, @codename)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
This error if there's no item in current data -6 = DC
PHP Code:
if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 1)
set @IS_EQUIP = 1
else if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 2 and [MENTION=2683706]Tid[/MENTION]3 = 1 and [MENTION=2683706]Tid[/MENTION]4 = 1 or [MENTION=2683706]Tid[/MENTION]4 = 2 ) )
set @IS_PET = 1
declare @item_id bigint
set @item_id = 0
This is a calculation or not calculation but it's IF the packet find out the values of tid and it's parents as the current the item id will change to 0 disabled .
PHP Code:
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
if (@IS_EQUIP = 1)
begin
declare @dur int
select @dur = Dur_L from _RefObjItem where ID = @link_to_item
set @data = @dur
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
It's a part of the prevue part , This common error or not really error , but it's read just the data of opt_level >12
as it seems so the last damage of the weapon for example if more than +12 the damage will reset as the same like +12 , You Could test ur self that part in game in BR files make +200 and u will find damage same like +12 .
PHP Code:
declare [MENTION=508836]remain[/MENTION]_gold bigint
select [MENTION=508836]remain[/MENTION]_gold = remaingold from _char where charid = @charid
Part of Packet which load the gold in game .
PHP Code:
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ป๓มกฐณฦํ (รึผฑศฃ)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
exec _STRG_ADD_EQUIP_NORMAL @item_id output, @dummy_serial_number output, 6, @charid, @empty_slot, @ref_item_id, @opt_level, 0, @data
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
This part of packet is call data from another stored pro. , when we explain _STRG_ADD_EQUIP_NORMAL Packet , we will understand this part more as i won't explain it with this packet .
PHP Code:
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
begin
set @data = @max_count
end
end
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ป๓มกฐณฦํ (รึผฑศฃ)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
exec _STRG_ADD_EXPENDABLE @item_id output, @dummy_serial_number output, 6, @charid, @empty_slot, @ref_item_id, @data
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
end
Same as _STRG_ADD_EQUIP_NORMAL
PHP Code:
if (@item_id > 0)
return 1
else
begin
raiserror('failed for unknown reason: %s, %s', 1, 16, @charname, @codename)
return -7
end
Also Error module so if item id = 0 so return 1 = gameserver won't read it
Else
So gameserver will write the unknown error with char name and item code and return -7 = Char DC ----------------------------------------
Named : _ADD_ITEM_EXTERN_CHEST
PHP Code:
/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN_CHEST] Script Date: 02/08/2012 12:55:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN_CHEST]
@account_name varchar(128),
@codename varchar(128),
@data int,
@opt_level int
as
declare @JID int
declare @ref_item_id int
declare [MENTION=3807069]ReTuRn_[/MENTION]value tinyint
select @JID = JID from _AccountJID with ( nolock ) where AccountID = @account_name
select @ref_item_id = [ID] from _RefObjCommon with ( nolock ) where Codename128 = @codename
exec [MENTION=3807069]ReTuRn_[/MENTION]value = _ADD_ITEM_EXTERN_CHEST_FAST @JID, @ref_item_id, @data, @opt_level
return [MENTION=3807069]ReTuRn_[/MENTION]value
/*******
declare @link_to_item int
if (not exists (select * from _RefObjCommon where Codename128 = @codename))
begin
raiserror('unknown item: %s', 1, 16, @codename)
return -1
end
-- JID
if (not exists (select * from _accountjid with (nolock) where AccountID = @account_name))
begin
raiserror('not existing account name: %s', 1, 16, @account_name)
return -2
end
-- CHEST
select @JID = JID from _AccountJID with (nolock) where AccountID = @account_name
if (not exists (select * from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null)))
begin
raiserror('chest Full: %s', 1, 16, @account_name)
return -3
end
declare @empty_slot int
select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
set @ref_item_id = 0
set @link_to_item = 0
select @ref_item_id = id, @link_to_item = link from _RefObjCommon where codename128 = @codename
if (@ref_item_id = 0 or @ref_item_id is null)
begin
raiserror('@RefItemID == NULL: %s', 1, 16, @account_name)
return -4
end
if (@link_to_item = 0 or @link_to_item is null)
begin
raiserror('RefItem Link == NULL: %s', 1, 16, @account_name)
return -5
end
declare [MENTION=2683706]Tid[/MENTION]1 int
declare [MENTION=2683706]Tid[/MENTION]2 int
select [MENTION=2683706]Tid[/MENTION]1 = TypeID1, [MENTION=2683706]Tid[/MENTION]2 = TypeID2 from _RefObjCommon where ID = @ref_item_id
declare @IS_EQUIP int
set @IS_EQUIP = 0
if [MENTION=2683706]Tid[/MENTION]1 <> 3)
begin
raiserror('not item: %s, %s', 1, 16, @account_name, @codename)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 1)
set @IS_EQUIP = 1
if (@IS_EQUIP = 1)
begin
select @data = Dur_L from _RefObjItem where ID = @link_to_item
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
end
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
set @data = @max_count
set @opt_level = 0
end
set xact_abort on
begin transaction
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
declare @NewItemID bigint
set @NewItemID = 0
EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
IF (@NewItemID = 0)
BEGIN
rollback transaction
raiserror('failed to allocate new item id: %s, %s', 1, 16, @account_name, @codename)
return -7
END
UPDATE _Items SET RefItemID = @ref_item_id, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
raiserror('failed to create new item: %s, %s', 1, 16, @account_name, @codename)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
raiserror('failed to insert item to chest: %s, %s', 1, 16, @account_name, @codename)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
raiserror('item generation failed for unknown reason: %s, %s', 1, 16, @account_name, @codename)
return -10
end
commit transaction
return 1
*******/
This don't need more explain as it's same as _ADD_ITEM_EXTERN
It's not a same it's a part of it , Just this Part for add items , and also got the common error module . ---------------------------------------
Named : _ADD_ITEM_EXTERN_CHEST_FAST
PHP Code:
/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN_CHEST_FAST] Script Date: 02/08/2012 14:00:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN_CHEST_FAST]
@JID int,
@ItemToAdd int,
@data int,
@opt_level int
as
if (not exists(select * from sysobjects where name = '_TEMP_ADDITEMEXTERN_CHEST_LOG'))
begin
create table _TEMP_ADDITEMEXTERN_CHEST_LOG
(
ID int identity(0, 1) not null,
LogString varchar(256),
LogDate datetime default getdate() not null
)
end
declare @LogString varchar(256)
declare @link_to_item int
if (not exists (select UserJID from _User with (nolock) where UserJID = @JID))
begin
set @LogString = 'not existing account ID: ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -2
end
if (not exists (select * from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null)))
begin
set @LogString = 'chest Full! JID is ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -3
end
declare @empty_slot int
select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
set @link_to_item = 0
select @link_to_item = link from _RefObjCommon where ID = @ItemToAdd
if (@link_to_item = 0 or @link_to_item is null)
begin
set @LogString = 'RefItem Link == NULL JID is ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -5
end
declare [MENTION=2683706]Tid[/MENTION]1 int
declare [MENTION=2683706]Tid[/MENTION]2 int
declare [MENTION=2683706]Tid[/MENTION]3 int
declare [MENTION=2683706]Tid[/MENTION]4 int
select [MENTION=2683706]Tid[/MENTION]1 = TypeID1, [MENTION=2683706]Tid[/MENTION]2 = TypeID2, [MENTION=2683706]Tid[/MENTION]3 = TypeID3, [MENTION=2683706]Tid[/MENTION]4 = TypeID4 from _RefObjCommon where ID = @ItemToAdd
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
if [MENTION=2683706]Tid[/MENTION]1 <> 3)
begin
set @LogString = 'not item! JID is ' + cast(@JID as char(15)) + 'item is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 1)
set @IS_EQUIP = 1
else if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 2 and [MENTION=2683706]Tid[/MENTION]3 = 1 and [MENTION=2683706]Tid[/MENTION]4 = 1 or [MENTION=2683706]Tid[/MENTION]4 = 2))
set @IS_PET = 1
if (@IS_EQUIP = 1)
begin
select @data = Dur_L from _RefObjItem where ID = @link_to_item
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
end
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
set @data = @max_count
end
set @opt_level = 0
end
-------------------------------------------------------------------------------- let's generate item !!!
set xact_abort on
begin transaction
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
declare @NewItemID bigint
set @NewItemID = 0
EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
IF (@NewItemID = 0)
BEGIN
rollback transaction
set @LogString = 'failed to allocate new item id! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -7
END
if( @IS_PET = 1 )
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data, Variance = 0 WHERE ID64 = @NewItemID
else
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to create new item! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to insert item to chest! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
set @LogString = 'item generation failed for unknown reason! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -10
end
commit transaction
set @LogString = 'success ' + 'jid: ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15)) + cast(@data as varchar(10)) + ', slot: ' + cast(@empty_slot as varchar(10))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
return 1
Few Fast explain about this one
it's just same like _ADD_ITEM_EXTERN
But it's work is different
PHP Code:
@JID int,
@ItemToAdd int,
@data int,
@opt_level int
Columns will be read data from
PHP Code:
as
if (not exists(select * from sysobjects where name = '_TEMP_ADDITEMEXTERN_CHEST_LOG'))
begin
create table _TEMP_ADDITEMEXTERN_CHEST_LOG
(
ID int identity(0, 1) not null,
LogString varchar(256),
LogDate datetime default getdate() not null
)
end
Part of if not exist
_TEMP_ADDITEMEXTERN_CHEST_LOG
Is a Stored Pro. which gives out the item logs like exchange or move and so to Log DB
PHP Code:
if (not exists (select UserJID from _User with (nolock) where UserJID = @JID))
begin
set @LogString = 'not existing account ID: ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -2
end
Same but this if the account JID/Name not exist
PHP Code:
declare @empty_slot int
select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
set @link_to_item = 0
select @link_to_item = link from _RefObjCommon where ID = @ItemToAdd
if (@link_to_item = 0 or @link_to_item is null)
begin
set @LogString = 'RefItem Link == NULL JID is ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -5
end
Check the item in RefObjCommon and it's link and if link = 0 or NULL or not found so error will show ('%s', 1, 16, @LogString)
and
return -5 = won't read or gives log .
PHP Code:
declare [MENTION=2683706]Tid[/MENTION]1 int
declare [MENTION=2683706]Tid[/MENTION]2 int
declare [MENTION=2683706]Tid[/MENTION]3 int
declare [MENTION=2683706]Tid[/MENTION]4 int
select [MENTION=2683706]Tid[/MENTION]1 = TypeID1, [MENTION=2683706]Tid[/MENTION]2 = TypeID2, [MENTION=2683706]Tid[/MENTION]3 = TypeID3, [MENTION=2683706]Tid[/MENTION]4 = TypeID4 from _RefObjCommon where ID = @ItemToAdd
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
if [MENTION=2683706]Tid[/MENTION]1 <> 3)
begin
set @LogString = 'not item! JID is ' + cast(@JID as char(15)) + 'item is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 1)
set @IS_EQUIP = 1
else if [MENTION=2683706]Tid[/MENTION]1 = 3 and [MENTION=2683706]Tid[/MENTION]2 = 2 and [MENTION=2683706]Tid[/MENTION]3 = 1 and [MENTION=2683706]Tid[/MENTION]4 = 1 or [MENTION=2683706]Tid[/MENTION]4 = 2))
set @IS_PET = 1
Same as all of those parts for writing Logs
and same error system if item id wrong or or or will gives error .. and return .
PHP Code:
if (@IS_EQUIP = 1)
begin
select @data = Dur_L from _RefObjItem where ID = @link_to_item
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
end
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
set @data = @max_count
end
set @opt_level = 0
end
This part of checking out the item Opt Level (+)
PHP Code:
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
PHP Code:
if (@data <= 0 or @data > @max_count)
set @data = @max_count
This part if it found item over the max opt level will be reset to @max_count where the value of it in another stored Procedure
Also
PHP Code:
set @opt_level = 0
the opt level reset to 0 .
PHP Code:
-------------------------------------------------------------------------------- let's generate item !!!
set xact_abort on
Explain !
PHP Code:
begin transaction
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
declare @NewItemID bigint
set @NewItemID = 0
EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
IF (@NewItemID = 0)
BEGIN
rollback transaction
set @LogString = 'failed to allocate new item id! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -7
END
if( @IS_PET = 1 )
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data, Variance = 0 WHERE ID64 = @NewItemID
else
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to create new item! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to insert item to chest! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
set @LogString = 'item generation failed for unknown reason! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -10
end
All of that part for adding item , add log for this item , error module (I think u could read the error module now)
any Question about that just ask
PHP Code:
commit transaction
set @LogString = 'success ' + 'jid: ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15)) + cast(@data as varchar(10)) + ', slot: ' + cast(@empty_slot as varchar(10))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
return 1
If Success of checking item / adding and insert data to Item Log (@Log DB)
return 1 = success
----------------------------------------
Named : _ADD_ITEM_EXTERN_CHEST_FAST_FULL_DATA
[spoiler]
[php]/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN_CHEST_FAST_FULL_DATA] Script Date: 02/08/2012 15:28:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN_CHEST_FAST_FULL_DATA]
@JID int,
@ItemToAdd int,
@data int,
@opt_level int,
@MagParamNum tinyint,
@MagParam1 bigint,
@MagParam2 bigint,
@MagParam3 bigint,
@MagParam4 bigint,
@MagParam5 bigint,
@MagParam6 bigint,
@MagParam7 bigint,
@MagParam8 bigint,
@MagParam9 bigint,
@MagParam10 bigint,
@MagParam11 bigint,
@MagParam12 bigint, @  64 bigint
as
if (not exists(select * from sysobjects where name = '_TEMP_ADDITEMEXTERN_CHEST_LOG'))
begin
create table _TEMP_ADDITEMEXTERN_CHEST_LOG
(
ID int identity(0, 1) not null,
LogString varchar(256),
LogDate datetime default getdate() not null
)
end
declare @LogString varchar(256)
declare @link_to_item int
if (not exists (select UserJID from _User with (nolock) where UserJID = @JID))
begin
set @LogString = 'not existing account ID: ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -2
end
if (not exists (select * from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null)))
begin
set @LogString = 'chest Full! JID is ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -3
end
declare @empty_slot int
select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
set @link_to_item = 0
select @link_to_item = link from _RefObjCommon where ID = @ItemToAdd
if (@link_to_item = 0 or @link_to_item is null)
begin
set @LogString = 'RefItem Link == NULL JID is ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -5
end
declare @ 1 int declare @ 2 int
declare @ 3 int declare @ 4 int
select @ 1 = TypeID1, @ 2 = TypeID2, @ 3 = TypeID3, @ 4 = TypeID4 from _RefObjCommon where ID = @ItemToAdd
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
if @ 1 <> 3)
begin
set @LogString = 'not item! JID is ' + cast(@JID as char(15)) + 'item is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if @ 1 = 3 and @ 2 = 1)
set @IS_EQUIP = 1
else if @ 1 = 3 and @ 2 = 2 and @ 3 = 1 and @ 4 = 1 or @ 4 = 2))
set @IS_PET = 1
if (@IS_EQUIP = 1)
begin
select @data = Dur_L from _RefObjItem where ID = @link_to_item
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
end
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
set @data = @max_count
end
set @opt_level = 0
end
-------------------------------------------------------------------------------- let's generate item !!!
set xact_abort on
begin transaction
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
declare @NewItemID bigint
set @NewItemID = 0
EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
IF (@NewItemID = 0)
BEGIN
rollback transaction
set @LogString = 'failed to allocate new item id! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -7
END
if( @IS_PET = 1 )
UPDATE _Items SET
RefItemID = @ItemToAdd,
OptLevel = @Opt_Level,
Data = @Data,
Variance = 0 ,
MagParamNum = @MagParamNum,
MagParam1 = @MagParam1 ,
MagParam2 = @MagParam2 ,
MagParam3 = @MagParam3 ,
MagParam4 = @MagParam4 ,
MagParam5 = @MagParam5 ,
MagParam6 = @MagParam6 ,
MagParam7 = @MagParam7 ,
MagParam8 = @MagParam8 ,
MagParam9 = @MagParam9 ,
MagParam10 = @MagParam10 ,
MagParam11 = @MagParam11 ,
MagParam12 = @MagParam12 ,
Serial64 = @ 64
WHERE ID64 = @NewItemID
else
UPDATE _Items SET
RefItemID = @ItemToAdd,
OptLevel = @Opt_Level,
Data = @Data ,
MagParamNum = @MagParamNum,
MagParam1 = @MagParam1 ,
MagParam2 = @MagParam2 ,
MagParam3 = @MagParam3 ,
MagParam4 = @MagParam4 ,
MagParam5 = @MagParam5 ,
MagParam6 = @MagParam6 ,
MagParam7 = @MagParam7 ,
MagParam8 = @MagParam8 ,
MagParam9 = @MagParam9 ,
MagParam10 = @MagParam10 ,
MagParam11 = @MagParam11 ,
MagParam12 = @MagParam12 ,
Serial64 = @ 64
WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to create new item! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to insert item to chest! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
set @LogString = 'item generation failed for unknown reason! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -10
end
commit transaction
|
|
|
|
02/08/2012, 00:06
|
#2
|
elite*gold: 0
Join Date: Feb 2012
Posts: 7
Received Thanks: 1
|
Great
|
|
|
|
|
02/08/2012, 00:43
|
#3
|
elite*gold: 0
Join Date: Apr 2009
Posts: 1,715
Received Thanks: 892
|
Great bro!  , Explanations are more important than a setup guide imo. Because you'll get a wider view of everything and when you know how things works out. You can fix the problems on your own 
|
|
|
|
|
02/08/2012, 00:47
|
#4
|
elite*gold: 0
Join Date: Jan 2012
Posts: 130
Received Thanks: 90
|
Great
|
|
|
|
|
02/08/2012, 01:17
|
#5
|
elite*gold: 0
Join Date: Sep 2011
Posts: 50
Received Thanks: 4
|
wow This's Good ^^
|
|
|
|
|
02/08/2012, 01:23
|
#6
|
elite*gold: 0
Join Date: Sep 2011
Posts: 419
Received Thanks: 59
|
G.L bro
thats will be Awesome
|
|
|
|
|
02/08/2012, 02:07
|
#7
|
elite*gold: 0
Join Date: Feb 2010
Posts: 2,278
Received Thanks: 445
|
i think i can help with my exp in this
send me private mesg with your email
going sleep as well
|
|
|
|
|
02/08/2012, 02:50
|
#8
|
elite*gold: 0
Join Date: Jan 2012
Posts: 1,867
Received Thanks: 1,091
|
Cool , since no1 want to help this section ! and G.L with ur own project <3
|
|
|
|
|
02/08/2012, 03:13
|
#9
|
elite*gold: 0
Join Date: Apr 2011
Posts: 590
Received Thanks: 775
|
Great
|
|
|
|
|
02/08/2012, 05:36
|
#10
|
elite*gold: 844
Join Date: Oct 2010
Posts: 839
Received Thanks: 192
|
so nice bro and im sorry i didn't finish your order yet cuz i have something wrong with my PC but while i make your order i gonna explain it and how you can make one like it
|
|
|
|
|
02/08/2012, 11:36
|
#11
|
elite*gold: 7
Join Date: May 2010
Posts: 2,115
Received Thanks: 2,374
|
Stored Proceddure
Hello again ,
Nice to see great guys like u will join us
So let's Start out first step with stored procedures With BR Files :
From Time to time i will add group of stored procedures, Also we will begain with Shard db then Shard log then Account db
Let's go .
Named : __GoldTracePerMin
PHP Code:
/****** Object: StoredProcedure [dbo].[__GoldTracePerMin] Script Date: 02/08/2012 11:16:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[__GoldTracePerMin]
AS
DECLARE @SumCharGold bigint
SELECT @SumCharGold = SUM(RemainGold) FROM _Char
DECLARE @SumChestGold bigint
SELECT @SumChestGold = SUM(Gold) FROM _AccountJID
DECLARE @SumGuildChestGold bigint
SELECT @SumGuildChestGold = SUM(Gold) FROM _Guild
DECLARE @SumGuildWarGold bigint
SELECT @SumGuildWarGold = SUM(LodgedGold) FROM _GuildWar
DECLARE @SumGuildBootyGold bigint
SELECT @SumGuildBootyGold = SUM(Booty) FROM _Guild
DECLARE @OffsetCharGold bigint
DECLARE @OffsetChestGold bigint
DECLARE @OffsetGuildChestGold bigint
DECLARE @OffsetGuildWarGold bigint
DECLARE @OffsetGuildBootyGold bigint
SELECT
@OffsetCharGold = @SumCharGold - SumCharGold,
@OffsetChestGold = @SumChestGold - SumChestGold,
@OffsetGuildChestGold = @SumGuildChestGold - SumGuildChestGold,
@OffsetGuildWarGold = @SumGuildWarGold - SumGuildWarGold,
@OffsetGuildBootyGold = @SumGuildBootyGold - SumGuildBootyGold
FROM __GoldTrace
WHERE idx = (SELECT MAX(IDX) FROM __GoldTrace)
INSERT INTO __GoldTrace (SumCharGold, SumChestGold, SumGuildChestGold, SumGuildWarGold, SumGuildBootyGold, OffsetCharGold, OffsetChestGold, OffsetGuildChestGold, OffsetGuildWarGold, OffsetGuildBootyGold, dEventTime)
VALUES (@SumCharGold, @SumChestGold, @SumGuildChestGold, @SumGuildWarGold, @SumGuildBootyGold, @OffsetCharGold, @OffsetChestGold, @OffsetGuildChestGold, @OffsetGuildWarGold, @OffsetGuildBootyGold, getdate() )
As This seems to you it's the packet which summon the gold in game and also reload when the item used or we can say increase or decrease by amount of gold , Without this Gold in game won't load or reload .
Let's make small Discussion about this
PHP Code:
FROM _Char
FROM _AccountJID
FROM _Guild
FROM _GuildWar
Those are the tables which recall the gold amount and add from them
We Could say this packet is the gold system in game , But maybe not the full system as there's another parts in stored pro. related to gold load and reload .
PHP Code:
SUM(RemainGold)
SUM(Gold)
SUM(LodgedGold)
SUM(Booty)
and so , those are the columns in tables which contain the gold data .
PHP Code:
DECLARE @OffsetCharGold bigint
DECLARE @OffsetChestGold bigint
DECLARE @OffsetGuildChestGold bigint
DECLARE @OffsetGuildWarGold bigint
DECLARE @OffsetGuildBootyGold bigint
Those explain them selves here
PHP Code:
DECLARE @SumCharGold bigint
SELECT @SumCharGold = SUM(RemainGold) FROM _Char
DECLARE @SumChestGold bigint
SELECT @SumChestGold = SUM(Gold) FROM _AccountJID
DECLARE @SumGuildChestGold bigint
SELECT @SumGuildChestGold = SUM(Gold) FROM _Guild
DECLARE @SumGuildWarGold bigint
SELECT @SumGuildWarGold = SUM(LodgedGold) FROM _GuildWar
With this part
PHP Code:
SELECT
@OffsetCharGold = @SumCharGold - SumCharGold,
@OffsetChestGold = @SumChestGold - SumChestGold,
@OffsetGuildChestGold = @SumGuildChestGold - SumGuildChestGold,
@OffsetGuildWarGold = @SumGuildWarGold - SumGuildWarGold,
@OffsetGuildBootyGold = @SumGuildBootyGold - SumGuildBootyGold
FROM __GoldTrace
WHERE idx = (SELECT MAX(IDX) FROM __GoldTrace)
This Part to collect the data which is needed to load in game to insert in a table
PHP Code:
INSERT INTO __GoldTrace (SumCharGold, SumChestGold, SumGuildChestGold, SumGuildWarGold, SumGuildBootyGold, OffsetCharGold, OffsetChestGold, OffsetGuildChestGold, OffsetGuildWarGold, OffsetGuildBootyGold, dEventTime)
VALUES (@SumCharGold, @SumChestGold, @SumGuildChestGold, @SumGuildWarGold, @SumGuildBootyGold, @OffsetCharGold, @OffsetChestGold, @OffsetGuildChestGold, @OffsetGuildWarGold, @OffsetGuildBootyGold, getdate() )
Here is the part which Insert those values in __GoldTrace Table .
Any Part i forgot Hope you mind me plz
Every 1 Stored Pro. will explained same as this one .
Hope It's appear as easy to be understand
-----------------------------------------------
Named : _ActiveteCharCOS
PHP Code:
/****** Object: StoredProcedure [dbo].[_ActivateCharCOS] Script Date: 02/08/2012 11:42:37 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[_ActivateCharCOS]
@COSDBID int,
@itemID int
AS
update _CharCos set State = 1 where ID = @COSDBID
if( @@error <> 0 or @@rowcount = 0 )
begin
return -1
end
return 1
As the prevue Packet explain it self, It's the packet which accept the summon of any COS in game by a player char , without this packet u can't summon any COS/Pet In game,
When u summon ur COS it Insert aka update at _CharCos table the State of this COS to 1 as it's summoned and also when u load for first time in game it's read that the Statue and if it's 1 so u will find ur COS is already summoned ,
PHP Code:
if( @@error <> 0 or @@rowcount = 0 )
begin
return -1
end
return 1
This part to remove any error could happen in reading values(State) as he made the State Start with -1 and end with 1 , so any another value in Statue column will be changed to 0 to prevent any error or You GameServer may take overflaw when this player who's have This COS which got wrong Statue Load in game .
Also it remove any other errors so if error occur , it set the value to 0 in any column in COS table (_CharCos) .
|
|
|
|
|
02/08/2012, 12:49
|
#12
|
elite*gold: 7
Join Date: May 2010
Posts: 2,115
Received Thanks: 2,374
|
Named : _ADD_ITEM_EXTERN
PHP Code:
/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN] Script Date: 02/08/2012 12:01:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN]
@charname varchar(64),
@codename varchar(128),
@data int,
@opt_level int
as
declare @charid int
declare @ref_item_id int
declare @link_to_item int
declare @inv_capacity int
set @inv_capacity = 45
if (not exists (select * from _RefObjCommon where Codename128 = @codename))
begin
raiserror('unknown item: %s', 1, 16, @codename)
return -1
end
if (not exists (select * from _char with (nolock) where charname16 = @charname))
begin
raiserror('not existing character: %s', 1, 16, @charname)
return -2
end
select @charid = charid, @inv_capacity = InventorySize from _char with (nolock) where charname16 = @charname
if (not exists (select * from _inventory with (nolock) where charid = @charid and slot >= 13 and slot < @inV_capacity and itemid = 0))
begin
raiserror('Inventory Full: %s', 1, 16, @charname)
return -3 -- inventory full
end
declare @empty_slot int
select top 1 @empty_slot = slot from _inventory with (nolock) where charid = @charid and slot >= 13 and slot < @inV_capacity and itemid = 0 order by slot
set @ref_item_id = 0
set @link_to_item = 0
select @ref_item_id = id, @link_to_item = link from _RefObjCommon where codename128 = @codename
if (@ref_item_id = 0 or @ref_item_id is null)
begin
raiserror('@RefItemID == NULL: %s', 1, 16, @charname)
return -4
end
if (@link_to_item = 0 or @link_to_item is null)
begin
raiserror('RefItem Link == NULL: %s', 1, 16, @charname)
return -5
end
declare @tid1 int
declare @tid2 int
declare @tid3 int
declare @tid4 int
select @tid1 = TypeID1, @tid2 = TypeID2, @tid3 = TypeID3, @tid4 = TypeID4 from _RefObjCommon where ID = @ref_item_id
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
if (@tid1 <> 3)
begin
raiserror('not item: %s, %s', 1, 16, @charname, @codename)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if (@tid1 = 3 and @tid2 = 1)
set @IS_EQUIP = 1
else if (@tid1 = 3 and @tid2 = 2 and @tid3 = 1 and (@tid4 = 1 or @tid4 = 2 ) )
set @IS_PET = 1
declare @item_id bigint
set @item_id = 0
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
if (@IS_EQUIP = 1)
begin
declare @dur int
select @dur = Dur_L from _RefObjItem where ID = @link_to_item
set @data = @dur
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
declare @remain_gold bigint
select @remain_gold = remaingold from _char where charid = @charid
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ป๓มกฐณฦํ (รึผฑศฃ)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
exec _STRG_ADD_EQUIP_NORMAL @item_id output, @dummy_serial_number output, 6, @charid, @empty_slot, @ref_item_id, @opt_level, 0, @data
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
end
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
begin
set @data = @max_count
end
end
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ป๓มกฐณฦํ (รึผฑศฃ)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
exec _STRG_ADD_EXPENDABLE @item_id output, @dummy_serial_number output, 6, @charid, @empty_slot, @ref_item_id, @data
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
end
if (@item_id > 0)
return 1
else
begin
raiserror('failed for unknown reason: %s, %s', 1, 16, @charname, @codename)
return -7
end
This Packet is something Hard ,
Let's Start a few Explain about it ,
This Packet is for summon or load the data of items in game of a char,
It's check all item data like blues and states and opt level (+) and so
also it's check after that, that the item is active and already found in _RefObjCommon table ,
It's also check where's the item in inventory (Slot number) to load the item in the right place ,
Also it contain the error module so any unknown error occurs The GameServer will DC this Char .
PHP Code:
@charname varchar(64),
@codename varchar(128),
@data int,
@opt_level int
as
declare @charid int
declare @ref_item_id int
declare @link_to_item int
declare @inv_capacity int
set @inv_capacity = 45
That part for data reader and also gives the max inventory size in game as in BR files the max ever inventory size is 45 ,
PHP Code:
if (not exists (select * from _RefObjCommon where Codename128 = @codename))
begin
raiserror('unknown item: %s', 1, 16, @codename)
return -1
end
Here is the module packet of error (If not exists)
So if GameServer don't find the current item at RefObjCommon table at Codename128 (Gameserver is searching by item name for example ITEM_CH_...)
PHP Code:
raiserror('unknown item: %s', 1, 16, @codename)
return -1
This unkown error at %=item id @Codename , will appear at GameServer
and also will set that item to -1 , so GameServer won't show it in game and might the Char DC in First load (Login) time .
PHP Code:
if (not exists (select * from _char with (nolock) where charname16 = @charname))
begin
raiserror('not existing character: %s', 1, 16, @charname)
return -2
end
This is a part same related to the error packet
But that's if item found for non existing char , return -2 so the gameserver won't see that item again .
PHP Code:
select @charid = charid, @inv_capacity = InventorySize from _char with (nolock) where charname16 = @charname
This is part of packet which load the data in game , It's make connection between every data like at this example
PHP Code:
@charid = charid, @inv_capacity = InventorySize from _char
and
PHP Code:
where charname16 = @charname
To load out the char name in game ,
PHP Code:
if (not exists (select * from _inventory with (nolock) where charid = @charid and slot >= 13 and slot < @inV_capacity and itemid = 0))
begin
raiserror('Inventory Full: %s', 1, 16, @charname)
return -3 -- inventory full
end
This is an part also from error packet , so if it found item is out of slots or 2 items in same slots or in wrong slot
So it return to -3 item deleted , and if u were in game and need to add item while ur inventory is full it , GameServer gives packet of inventory is full
PHP Code:
declare @empty_slot int
select top 1 @empty_slot = slot from _inventory with (nolock) where charid = @charid and slot >= 13 and slot < @inV_capacity and itemid = 0 order by slot
set @ref_item_id = 0
set @link_to_item = 0
select @ref_item_id = id, @link_to_item = link from _RefObjCommon where codename128 = @codename
This if there's an empty slot in ur inventory in game and need to take item by exchange or buy it from small or collect it from mobs drop or any drop and so it search for the top empty slot to add this item in and it's already check if the item already in _RefObjCommon and it's imformation cuz some items u can't pick up it and so ..
PHP Code:
if (@ref_item_id = 0 or @ref_item_id is null)
begin
raiserror('@RefItemID == NULL: %s', 1, 16, @charname)
return -4
end
if (@link_to_item = 0 or @link_to_item is null)
begin
raiserror('RefItem Link == NULL: %s', 1, 16, @charname)
return -5
end
This error module is something for the un-avilable errors in game !
what i mean ! , if the item current insert in game but after that by some way the service of this item change to 0 or delete from RefObjCommon so GameServer fives error with Item id and return -4 = no action done ,
if there isn't link for this item or the link column is null (u will understand more when we explain the tables side) gameserver will gives u error and return -5 = no action also
PHP Code:
declare @tid1 int
declare @tid2 int
declare @tid3 int
declare @tid4 int
select @tid1 = TypeID1, @tid2 = TypeID2, @tid3 = TypeID3, @tid4 = TypeID4 from _RefObjCommon where ID = @ref_item_id
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
This is the part where this packet read from _RefObjCommon
PHP Code:
if (@tid1 <> 3)
begin
raiserror('not item: %s, %s', 1, 16, @charname, @codename)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if (@tid1 = 3 and @tid2 = 1)
set @IS_EQUIP = 1
else if (@tid1 = 3 and @tid2 = 2 and @tid3 = 1 and (@tid4 = 1 or @tid4 = 2 ) )
set @IS_PET = 1
declare @item_id bigint
set @item_id = 0
This is common errors and known and also this packet fix it
PHP Code:
raiserror('not item: %s, %s', 1, 16, @charname, @codename)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
This error if there's no item in current data -6 = DC
PHP Code:
if (@tid1 = 3 and @tid2 = 1)
set @IS_EQUIP = 1
else if (@tid1 = 3 and @tid2 = 2 and @tid3 = 1 and (@tid4 = 1 or @tid4 = 2 ) )
set @IS_PET = 1
declare @item_id bigint
set @item_id = 0
This is a calculation or not calculation but it's IF the packet find out the values of tid and it's parents as the current the item id will change to 0 disabled .
PHP Code:
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
if (@IS_EQUIP = 1)
begin
declare @dur int
select @dur = Dur_L from _RefObjItem where ID = @link_to_item
set @data = @dur
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
It's a part of the prevue part , This common error or not really error , but it's read just the data of opt_level >12
as it seems so the last damage of the weapon for example if more than +12 the damage will reset as the same like +12 , You Could test ur self that part in game in BR files make +200 and u will find damage same like +12 .
PHP Code:
declare @remain_gold bigint
select @remain_gold = remaingold from _char where charid = @charid
Part of Packet which load the gold in game .
PHP Code:
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ป๓มกฐณฦํ (รึผฑศฃ)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
exec _STRG_ADD_EQUIP_NORMAL @item_id output, @dummy_serial_number output, 6, @charid, @empty_slot, @ref_item_id, @opt_level, 0, @data
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
This part of packet is call data from another stored pro. , when we explain _STRG_ADD_EQUIP_NORMAL Packet , we will understand this part more as i won't explain it with this packet .
PHP Code:
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
begin
set @data = @max_count
end
end
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ป๓มกฐณฦํ (รึผฑศฃ)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
exec _STRG_ADD_EXPENDABLE @item_id output, @dummy_serial_number output, 6, @charid, @empty_slot, @ref_item_id, @data
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
end
Same as _STRG_ADD_EQUIP_NORMAL
PHP Code:
if (@item_id > 0)
return 1
else
begin
raiserror('failed for unknown reason: %s, %s', 1, 16, @charname, @codename)
return -7
end
Also Error module so if item id = 0 so return 1 = gameserver won't read it
Else
So gameserver will write the unknown error with char name and item code and return -7 = Char DC
Finished
|
|
|
|
|
02/08/2012, 13:02
|
#13
|
elite*gold: 7
Join Date: May 2010
Posts: 2,115
Received Thanks: 2,374
|
Stored Procedure
Named : _ADD_ITEM_EXTERN_CHEST
PHP Code:
/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN_CHEST] Script Date: 02/08/2012 12:55:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN_CHEST]
@account_name varchar(128),
@codename varchar(128),
@data int,
@opt_level int
as
declare @JID int
declare @ref_item_id int
declare @return_value tinyint
select @JID = JID from _AccountJID with ( nolock ) where AccountID = @account_name
select @ref_item_id = [ID] from _RefObjCommon with ( nolock ) where Codename128 = @codename
exec @return_value = _ADD_ITEM_EXTERN_CHEST_FAST @JID, @ref_item_id, @data, @opt_level
return @return_value
/*******
declare @link_to_item int
if (not exists (select * from _RefObjCommon where Codename128 = @codename))
begin
raiserror('unknown item: %s', 1, 16, @codename)
return -1
end
-- JID
if (not exists (select * from _accountjid with (nolock) where AccountID = @account_name))
begin
raiserror('not existing account name: %s', 1, 16, @account_name)
return -2
end
-- CHEST
select @JID = JID from _AccountJID with (nolock) where AccountID = @account_name
if (not exists (select * from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null)))
begin
raiserror('chest Full: %s', 1, 16, @account_name)
return -3
end
declare @empty_slot int
select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
set @ref_item_id = 0
set @link_to_item = 0
select @ref_item_id = id, @link_to_item = link from _RefObjCommon where codename128 = @codename
if (@ref_item_id = 0 or @ref_item_id is null)
begin
raiserror('@RefItemID == NULL: %s', 1, 16, @account_name)
return -4
end
if (@link_to_item = 0 or @link_to_item is null)
begin
raiserror('RefItem Link == NULL: %s', 1, 16, @account_name)
return -5
end
declare @tid1 int
declare @tid2 int
select @tid1 = TypeID1, @tid2 = TypeID2 from _RefObjCommon where ID = @ref_item_id
declare @IS_EQUIP int
set @IS_EQUIP = 0
if (@tid1 <> 3)
begin
raiserror('not item: %s, %s', 1, 16, @account_name, @codename)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if (@tid1 = 3 and @tid2 = 1)
set @IS_EQUIP = 1
if (@IS_EQUIP = 1)
begin
select @data = Dur_L from _RefObjItem where ID = @link_to_item
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
end
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
set @data = @max_count
set @opt_level = 0
end
set xact_abort on
begin transaction
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
declare @NewItemID bigint
set @NewItemID = 0
EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
IF (@NewItemID = 0)
BEGIN
rollback transaction
raiserror('failed to allocate new item id: %s, %s', 1, 16, @account_name, @codename)
return -7
END
UPDATE _Items SET RefItemID = @ref_item_id, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
raiserror('failed to create new item: %s, %s', 1, 16, @account_name, @codename)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
raiserror('failed to insert item to chest: %s, %s', 1, 16, @account_name, @codename)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
raiserror('item generation failed for unknown reason: %s, %s', 1, 16, @account_name, @codename)
return -10
end
commit transaction
return 1
*******/
This don't need more explain as it's same as _ADD_ITEM_EXTERN
It's not a same it's a part of it , Just this Part for add items , and also got the common error module .
|
|
|
|
|
02/08/2012, 13:33
|
#14
|
elite*gold: 0
Join Date: Jul 2009
Posts: 1,121
Received Thanks: 420
|
For me it's a wrong section...
|
|
|
|
|
02/08/2012, 14:31
|
#15
|
elite*gold: 7
Join Date: May 2010
Posts: 2,115
Received Thanks: 2,374
|
Stored Procedure
Named : _ADD_ITEM_EXTERN_CHEST_FAST
PHP Code:
/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN_CHEST_FAST] Script Date: 02/08/2012 14:00:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN_CHEST_FAST]
@JID int,
@ItemToAdd int,
@data int,
@opt_level int
as
if (not exists(select * from sysobjects where name = '_TEMP_ADDITEMEXTERN_CHEST_LOG'))
begin
create table _TEMP_ADDITEMEXTERN_CHEST_LOG
(
ID int identity(0, 1) not null,
LogString varchar(256),
LogDate datetime default getdate() not null
)
end
declare @LogString varchar(256)
declare @link_to_item int
if (not exists (select UserJID from _User with (nolock) where UserJID = @JID))
begin
set @LogString = 'not existing account ID: ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -2
end
if (not exists (select * from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null)))
begin
set @LogString = 'chest Full! JID is ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -3
end
declare @empty_slot int
select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
set @link_to_item = 0
select @link_to_item = link from _RefObjCommon where ID = @ItemToAdd
if (@link_to_item = 0 or @link_to_item is null)
begin
set @LogString = 'RefItem Link == NULL JID is ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -5
end
declare @tid1 int
declare @tid2 int
declare @tid3 int
declare @tid4 int
select @tid1 = TypeID1, @tid2 = TypeID2, @tid3 = TypeID3, @tid4 = TypeID4 from _RefObjCommon where ID = @ItemToAdd
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
if (@tid1 <> 3)
begin
set @LogString = 'not item! JID is ' + cast(@JID as char(15)) + 'item is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if (@tid1 = 3 and @tid2 = 1)
set @IS_EQUIP = 1
else if (@tid1 = 3 and @tid2 = 2 and @tid3 = 1 and (@tid4 = 1 or @tid4 = 2))
set @IS_PET = 1
if (@IS_EQUIP = 1)
begin
select @data = Dur_L from _RefObjItem where ID = @link_to_item
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
end
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
set @data = @max_count
end
set @opt_level = 0
end
-------------------------------------------------------------------------------- let's generate item !!!
set xact_abort on
begin transaction
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
declare @NewItemID bigint
set @NewItemID = 0
EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
IF (@NewItemID = 0)
BEGIN
rollback transaction
set @LogString = 'failed to allocate new item id! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -7
END
if( @IS_PET = 1 )
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data, Variance = 0 WHERE ID64 = @NewItemID
else
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to create new item! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to insert item to chest! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
set @LogString = 'item generation failed for unknown reason! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -10
end
commit transaction
set @LogString = 'success ' + 'jid: ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15)) + cast(@data as varchar(10)) + ', slot: ' + cast(@empty_slot as varchar(10))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
return 1
Few Fast explain about this one
it's just same like _ADD_ITEM_EXTERN
But it's work is different
PHP Code:
@JID int,
@ItemToAdd int,
@data int,
@opt_level int
Columns will be read data from
PHP Code:
as
if (not exists(select * from sysobjects where name = '_TEMP_ADDITEMEXTERN_CHEST_LOG'))
begin
create table _TEMP_ADDITEMEXTERN_CHEST_LOG
(
ID int identity(0, 1) not null,
LogString varchar(256),
LogDate datetime default getdate() not null
)
end
Part of if not exist
_TEMP_ADDITEMEXTERN_CHEST_LOG
Is a Stored Pro. which gives out the item logs like exchange or move and so to Log DB
PHP Code:
if (not exists (select UserJID from _User with (nolock) where UserJID = @JID))
begin
set @LogString = 'not existing account ID: ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -2
end
Same but this if the account JID/Name not exist
PHP Code:
declare @empty_slot int
select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
set @link_to_item = 0
select @link_to_item = link from _RefObjCommon where ID = @ItemToAdd
if (@link_to_item = 0 or @link_to_item is null)
begin
set @LogString = 'RefItem Link == NULL JID is ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -5
end
Check the item in RefObjCommon and it's link and if link = 0 or NULL or not found so error will show ('%s', 1, 16, @LogString)
and
return -5 = won't read or gives log .
PHP Code:
declare @tid1 int
declare @tid2 int
declare @tid3 int
declare @tid4 int
select @tid1 = TypeID1, @tid2 = TypeID2, @tid3 = TypeID3, @tid4 = TypeID4 from _RefObjCommon where ID = @ItemToAdd
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
if (@tid1 <> 3)
begin
set @LogString = 'not item! JID is ' + cast(@JID as char(15)) + 'item is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -6 -- ฟภภื! พฦภฬลภฬ พฦดฯพ฿?
end
if (@tid1 = 3 and @tid2 = 1)
set @IS_EQUIP = 1
else if (@tid1 = 3 and @tid2 = 2 and @tid3 = 1 and (@tid4 = 1 or @tid4 = 2))
set @IS_PET = 1
Same as all of those parts for writing Logs
and same error system if item id wrong or or or will gives error .. and return .
PHP Code:
if (@IS_EQUIP = 1)
begin
select @data = Dur_L from _RefObjItem where ID = @link_to_item
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
end
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
set @data = @max_count
end
set @opt_level = 0
end
This part of checking out the item Opt Level (+)
PHP Code:
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
PHP Code:
if (@data <= 0 or @data > @max_count)
set @data = @max_count
This part if it found item over the max opt level will be reset to @max_count where the value of it in another stored Procedure
Also
PHP Code:
set @opt_level = 0
the opt level reset to 0 .
PHP Code:
-------------------------------------------------------------------------------- let's generate item !!!
set xact_abort on
Explain !
PHP Code:
begin transaction
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
declare @NewItemID bigint
set @NewItemID = 0
EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
IF (@NewItemID = 0)
BEGIN
rollback transaction
set @LogString = 'failed to allocate new item id! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -7
END
if( @IS_PET = 1 )
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data, Variance = 0 WHERE ID64 = @NewItemID
else
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to create new item! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to insert item to chest! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
set @LogString = 'item generation failed for unknown reason! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -10
end
All of that part for adding item , add log for this item , error module (I think u could read the error module now)
any Question about that just ask
PHP Code:
commit transaction
set @LogString = 'success ' + 'jid: ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15)) + cast(@data as varchar(10)) + ', slot: ' + cast(@empty_slot as varchar(10))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
return 1
If Success of checking item / adding and insert data to Item Log (@Log DB)
return 1 = success
|
|
|
|
 |
|
Similar Threads
|
[Tour] Project Redemption Development Server
08/20/2011 - Rappelz - 85 Replies
Introduction:
Hello, I am Chris. Or as many of you know me 'ismokedrow' I am the Project Lead and Lead Developer of Project Redemption. This whole project is aimed at sharing with the community and providing new and easy ways to adapt and tame an other-wise unfriendly server.
While the project does owe it's roots to the NCarbon Developement Team, it does not hold their values or practices. This project is founded on sharing and will remain that way.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~...
|
Project LVL Development [Binary Server]
03/26/2011 - CO2 Private Server - 1 Replies
I start working on Project LVL which is a binary server 5065 based, what were working on is a 0 action server this meaning is scratch off no npcs mobs or anything clean. So I been wanting the community to post ideas/events you would like it to have, cps or not. Note the idea is to bring old co back so the server is balance and attracts veterans co players. Note were not a high rate server so don't post non sense for high rate base servers. Thanks for your time for reading this paragraph....
|
All times are GMT +1. The time now is 16:37.
|
|
