[Release]Instant Leveling

[castor4878]

Context

the creation of new toons involves some tables - including PS_GameDefs.dbo.StatusDefs to get the initial HP, SP, MP - some hard coded values (the initial Str, Dex, ..., Wis) and some user defined parameters (including face, hair, size).
all these parameters are transmitted to the PS_GameData.dbo.usp_Create_Char_R procedure who actually creates the toon, ie insert a record into PS_GameData.dbo.Chars.

In order to provide "immediate leveling" or to change the characteristics of the toons upon their creation, we have to change on the fly some of the values used by that SP.

Solution

One was presented by Snuggle; it has several constraints including risks of bug and difficulty of maintenance.


Another coding is to use a new table to store the fields we want to change on the fly.

Knowing that usp_Create_Char_R is defined as:

Proc dbo.usp_Create_Char_R(
	@serverID tinyint, @userID varchar(12), @userUID int, @charName varchar(50),
	@slot Tinyint, @family Tinyint, @grow Tinyint,
	@hair Tinyint, @face Tinyint, @size Tinyint, @job Tinyint, @sex Tinyint,
	@level Smallint, @statpoint Smallint, @skillpoint Smallint,
	@str Smallint, @dex Smallint, @rec Smallint,
	@int Smallint, @luc Smallint, @wis Smallint,
	@HP Smallint, @MP Smallint, @SP Smallint,
	@map Smallint, @dir Smallint, @exp Int, @money Int,
	@posX Real, @posY Real, @posZ Real,
	@Hg Smallint, @Vg Smallint, @Cg Tinyint, @Og Tinyint, @Ig Tinyint)

it's appears that the information we want to change is function of family, job & grow and consists in level, statPoint, skillPoint, str, dex, rec, int, luc, wis, HP, MP, SP, exp, money, map, dir, posX, posY, posZ.
(other parameters (fields of Chars table) are constant or unused or unknown).

1- definition of CharDefs

we create the PS_GameDefs.dbo.CharDefs table as follows:

USE PS_GameDefs
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

if exists (select * from sysobjects where id=object_id(N'dbo.CharDefs')) drop table CharDefs;
go

CREATE TABLE dbo.CharDefs (
	grow Tinyint not null,	--	0 (lvling), 1 (15), 2 (30), 3 (70)
	family Tinyint not null,--	0: human, 1: elf, 2: vail, 3: deatheater
	job Tinyint not null,	--	0: fighter(fam: 0)  or warrior(fam: 3)
				--	1: defender(fam: 0) or guardian(fam: 3)
				--	2: ranger(fam: 1)   or assassin(fam: 2)
				--	3: archer(fam: 1)   or hunter(fam: 3)
				--	4: mage(fam: 1)     or pagan(fam: 2)
				--	5: priest(fam: 0)   or oracle(fam: 2)
	level Smallint not null,
	statpoint Smallint not null,
	skillpoint Smallint not null,

	str Smallint not null,
	dex Smallint not null,
	rec Smallint not null,
	[int] Smallint not null,
	luc Smallint not null,
	wis Smallint not null,
	HP Smallint not null,
	MP Smallint not null,
	SP Smallint not null,

	exp Int not null,
	money Int not null,

	map Smallint not null,
	dir Smallint not null,
	posX Real not null,
	posY Real not null,
	posZ Real not null,
) ON [PRIMARY]
GO

We then insert records with toon characteristics.
The following rows use the original AGE data to create a "grinding toon" (toon created at lvl 1) when the grow parameter is 0 (ie "Easy" mode according AGE default button labels).

insert into CharDefs values (0,0,0,	1,0,5,	14,12, 9, 8,15, 7,	352,110,257,	0,0,	1,0, 578.0, 78.6, 1762.1);
insert into CharDefs values (0,0,1,	1,0,5,	10, 9,12,10,14,10,	435,147,152,	0,0,	1,0, 578.0, 78.6, 1762.1);
insert into CharDefs values (0,1,2,	1,0,5,	10,19, 9, 7,12, 8,	300,140,320,	0,0,	1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,1,3,	1,0,5,	11,14,10, 7,13,10,	312,165,272,	0,0,	1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,1,4,	1,0,5,	 7,13, 9,15, 9,12,	270,325,155,	0,0,	1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,0,5,	1,0,5,	 8, 9,10,12,12,14,	290,295,160,	0,0,	1,0, 578.0, 78.6, 1762.1);

insert into CharDefs values (0,3,0,	1,0,5,	16,12,11, 8,11, 7,	372,110,247,	0,0,	2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,3,1,	1,0,5,	12, 9,14,10,10,10,	455,147,142,	0,0,	2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,2,2,	1,0,5,	10,15, 9, 9,12,10,	300,145,305,	0,0,	2,0, 165.2, 118.5, 397.9);
insert into CharDefs values (0,3,3,	1,0,5,	13,10,12, 7,13,10,	332,155,252,	0,0,	2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,2,4,	1,0,5,	 7, 9, 9,17, 9,14,	270,330,140,	0,0,	2,0, 165.2, 118.5, 397.9);
insert into CharDefs values (0,2,5,	1,0,5,	 8, 9,10,14, 8,16,	290,310,155,	0,0,	2,0, 165.2, 118.5, 397.9);

You will create similar blocks for the grow modes 1, 2 and 3.
mode 1 can be used to create lvl 15 toons, mode 2 for 30ers and mode 3 for 60/70+
or you can use this table to redefine any data applied on toon upon creation (such as giving more stats to UM vs NM, or remove the diffs between lights and darks classes or whatever relevant for your server.

The big advantage of the storage of these data in a table (instead of hard coded values in a lot of if statements) is that you will be able to easily update them (with a macroscopic / general view) if required.

The second advantage is that the changes made on the existing create chars SP are very limited and easily readable.

Of course the creation of that table is done once, for any latter change on the values you will use the "editable grid" of SQL Management Studio.

2- modification of usp_Create_Char_R

The procedure is changed to update on the fly some of the received parameters with values stored in our table:

USE [PS_GameData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER  Proc [dbo].[usp_Create_Char_R]

	@serverID tinyint, @userID varchar(12), @userUID int, @charName varchar(50),
	@slot Tinyint, @family Tinyint, @grow Tinyint,
	@hair Tinyint, @face Tinyint, @size Tinyint, @job Tinyint, @sex Tinyint,
	@level Smallint, @statpoint Smallint, @skillpoint Smallint,
	@str Smallint, @dex Smallint, @rec Smallint, @int Smallint,
	@luc Smallint, @wis Smallint, @HP Smallint, @MP Smallint, @SP Smallint,
	@map Smallint, @dir Smallint, @exp Int, @money Int,
	@posX Real, @posY Real, @posZ Real,
	@Hg Smallint, @Vg Smallint, @Cg Tinyint, @Og Tinyint, @Ig Tinyint

AS

SET NOCOUNT ON

declare @result int

SET @charName = LTRIM(RTRIM(@charName))
SET @SkillPoint = 5
set @result = 0


if exists (select CharID from Chars where CharName=@charName and
	(Del=0 or (Del=1 and Level>10 and DeleteDate>DATEADD(dd, -7, GETDATE()))))
begin
	set @result = -2
end
else
begin

-- Changes starts Here

	if exists (select level from PS_GameDefs.dbo.CharDefs where
		grow=@grow and family=@family and job=@job)
	begin
	--	replace some received values with data read from our table
		select @level=[level], @statPoint=[statPoint], @skillPoint=[skillPoint],
			@str=[str], @dex=[dex], @rec=[rec], @int=[int], @luc=[luc], @wis=[wis],
			@HP=[HP], @MP=[MP], @SP=[SP], @exp=[exp], @money=[money],
			@map=[map], @dir=[dir], @posX=[posX], @posY=[posY], @posz=[posZ]
		from PS_GameDefs.dbo.CharDefs
		where grow=@grow and family=@family and job=@job
		
	--	set mode to ultimate (valid for "immediate leveling" use only)
		set @grow = 3
	end

-- Changes Ends Here

	begin transaction

--	create toon with updated information
	insert into Chars(ServerID, UserID, UserUID, CharName, Slot, Family, Grow, 
		Hair, Face, [Size], Job, Sex, [Level], StatPoint, SkillPoint, 
		[Str], Dex, Rec, [Int], Luc, Wis, HP, MP, SP, Map, Dir, [Exp], [Money], 
		PosX, PosY, Posz, Hg, Vg, Cg, Og, Ig, RenameCnt, RemainTime)
	values (
		@serverID, @userID, @userUID, @charName, @slot, @family, @grow, 
		@hair, @face, @size, @job, @sex, @level, @statPoint, @skillPoint, 
		@str, @dex, @rec, @int, @luc, @wis, @HP, @MP, @SP, @map, @dir,
		@exp, @money, @posX, @posY, @posz, @Hg, @Vg, @Cg, @Og, @Ig, 0, 0)

	if (@@ERROR <> 0)
	begin
		rollback transaction
		set @result = -1
	end
	else
	begin
		commit transaction
		set @result = IDENT_CURRENT('Chars')
	end
end

SET NOCOUNT OFF
return @result

the impact on the original SP is minimal (a single SELECT statement) making maintenance easy if you have to restore / replace the SP with a new AGE/other one and re-insert the modification.

<QUOTE>

:

Originally Posted by viper4513

I just have to say, you did make a very simple thing, pretty complicated there Castor (maybe just cuz I am a SQL nub and most of what you wrote just went right over my head, even if I would like to understand it all fully)

And a little note for Castor, or anyone that does know.. Is it regarded as "poor" to input a table like how Castor suggested.. if that is what it was.. I may be completely wrong, I'm not too sure, and if I am, then just ignore me
But I was thinking adding another table for the database to "sort" things with would slow down the system and possibly cause more harm than good?

But like I said, I'm no expert, I use a method similar to the release, and it works fine for me.. just curious for my knowledge. I like to learn, even if I don't understand it fully.

</QUOTE>

Originally Posted by viper4513

I just have to say, you did make a very simple thing, pretty complicated there Castor[...]

complicated ?!?

the table creation is part of the solution but it does not impact the SP that has to be changed to achieve the goal.

alll the modif made on the original SP is *1 select statement* (the "select some new values from the CharDefs" table). it is obviously cleaner than a long brunch of tests.

adding a table is never bad as per, bad design - or bad coding - is bad.
it also doesn't slow down the server, and a single select statement is likely faster than several if.

another point is the completion of the code, the select, together with the optional but safe test I've used, guaranty that all cases are managed with a single statement.
OOH it is very easy to make a lot of coding / design bug when the solution use a lot of codes.

for instance, one can see code like:

IF @Family IN(0,1)
BEGIN
-- set light coords and map
END
ELSE IF @Family IN(2,3)
BEGIN
-- set fury coords and map
END

this contains potential bug.
correct analysis of all cases shall enforce:

IF @Family IN(0,1)
BEGIN
-- set light coords and map
END
ELSE IF @Family IN(2,3)
BEGIN
-- set fury coords and map
END
ELSE
BEGIN
RETURN -3
END

or

IF @Family IN(0,1)
BEGIN
-- set light coords and map
END
ELSE
BEGIN
-- set fury coords and map
END


the way I suggest to process avoid such issues, as well as the bug of Cryptic btw.

[Bаne]

Changed the thread title, as a user here might find it offensive.

[Alladrios]

Thanks a lot Castor, I have started using this. It's flawless, thank you very much.

I wouldnt have used the other way of doing it. cheers.

[[Dev]Ansem]

Originally Posted by castor4878

insert into CharDefs values (0,0,0,    1,0,5,    14,12, 9, 8,15, 7,    352,110,257,    0,0,    1,0, 578.0, 78.6, 1762.1);
insert into CharDefs values (0,0,1,    1,0,5,    10, 9,12,10,14,10,    435,147,152,    0,0,    1,0, 578.0, 78.6, 1762.1);
insert into CharDefs values (0,1,2,    1,0,5,    10,19, 9, 7,12, 8,    300,140,320,    0,0,    1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,1,3,    1,0,5,    11,14,10, 7,13,10,    312,165,272,    0,0,    1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,1,4,    1,0,5,     7,13, 9,15, 9,12,    270,325,155,    0,0,    1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,0,5,    1,0,5,     8, 9,10,12,12,14,    290,295,160,    0,0,    1,0, 578.0, 78.6, 1762.1);

insert into CharDefs values (0,3,0,    1,0,5,    16,12,11, 8,11, 7,    372,110,247,    0,0,    2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,3,1,    1,0,5,    12, 9,14,10,10,10,    455,147,142,    0,0,    2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,2,2,    1,0,5,    10,15, 9, 9,12,10,    300,145,305,    0,0,    2,0, 165.2, 118.5, 397.9);
insert into CharDefs values (0,3,3,    1,0,5,    13,10,12, 7,13,10,    332,155,252,    0,0,    2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,2,4,    1,0,5,     7, 9, 9,17, 9,14,    270,330,140,    0,0,    2,0, 165.2, 118.5, 397.9);
insert into CharDefs values (0,2,5,    1,0,5,     8, 9,10,14, 8,16,    290,310,155,    0,0,    2,0, 165.2, 118.5, 397.9);

You will create similar blocks for the grow modes 1, 2 and 3.
mode 1 can be used to create lvl 15 toons, mode 2 for 30ers and mode 3 for 60/70+

Does someone have a full lvl 15 - lvl 30 - lvl 70 setup?
€: nvm figured it out by myself.

Thank you castor

[ShaiyaEternity]

Can you please make the script lvl 15 30 70 pvpers like Shaiya Rebirth

[rici19]

Code:

insert into CharDefs values (0,0,0, 1,0,5, 14,12, 9, 8,15, 7, 352,110,257, 0,0, 1,0, 578.0, 78.6, 1762.1);
insert into CharDefs values (0,0,1, 1,0,5, 10, 9,12,10,14,10, 435,147,152, 0,0, 1,0, 578.0, 78.6, 1762.1);
insert into CharDefs values (0,1,2, 1,0,5, 10,19, 9, 7,12, 8, 300,140,320, 0,0, 1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,1,3, 1,0,5, 11,14,10, 7,13,10, 312,165,272, 0,0, 1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,1,4, 1,0,5, 7,13, 9,15, 9,12, 270,325,155, 0,0, 1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,0,5, 1,0,5, 8, 9,10,12,12,14, 290,295,160, 0,0, 1,0, 578.0, 78.6, 1762.1);

insert into CharDefs values (0,3,0, 1,0,5, 16,12,11, 8,11, 7, 372,110,247, 0,0, 2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,3,1, 1,0,5, 12, 9,14,10,10,10, 455,147,142, 0,0, 2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,2,2, 1,0,5, 10,15, 9, 9,12,10, 300,145,305, 0,0, 2,0, 165.2, 118.5, 397.9);
insert into CharDefs values (0,3,3, 1,0,5, 13,10,12, 7,13,10, 332,155,252, 0,0, 2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,2,4, 1,0,5, 7, 9, 9,17, 9,14, 270,330,140, 0,0, 2,0, 165.2, 118.5, 397.9);
insert into CharDefs values (0,2,5, 1,0,5, 8, 9,10,14, 8,16, 290,310,155, 0,0, 2,




where you have pasted the code as I do not understand. Sorry for the question

[[Dev]Ansem]

Originally Posted by rici19

Code:

insert into CharDefs values (0,0,0, 1,0,5, 14,12, 9, 8,15, 7, 352,110,257, 0,0, 1,0, 578.0, 78.6, 1762.1);
insert into CharDefs values (0,0,1, 1,0,5, 10, 9,12,10,14,10, 435,147,152, 0,0, 1,0, 578.0, 78.6, 1762.1);
insert into CharDefs values (0,1,2, 1,0,5, 10,19, 9, 7,12, 8, 300,140,320, 0,0, 1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,1,3, 1,0,5, 11,14,10, 7,13,10, 312,165,272, 0,0, 1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,1,4, 1,0,5, 7,13, 9,15, 9,12, 270,325,155, 0,0, 1,0,1679.0, 38.9, 1776.9);
insert into CharDefs values (0,0,5, 1,0,5, 8, 9,10,12,12,14, 290,295,160, 0,0, 1,0, 578.0, 78.6, 1762.1);

insert into CharDefs values (0,3,0, 1,0,5, 16,12,11, 8,11, 7, 372,110,247, 0,0, 2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,3,1, 1,0,5, 12, 9,14,10,10,10, 455,147,142, 0,0, 2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,2,2, 1,0,5, 10,15, 9, 9,12,10, 300,145,305, 0,0, 2,0, 165.2, 118.5, 397.9);
insert into CharDefs values (0,3,3, 1,0,5, 13,10,12, 7,13,10, 332,155,252, 0,0, 2,0,1838.9, 129.3, 443.4);
insert into CharDefs values (0,2,4, 1,0,5, 7, 9, 9,17, 9,14, 270,330,140, 0,0, 2,0, 165.2, 118.5, 397.9);
insert into CharDefs values (0,2,5, 1,0,5, 8, 9,10,14, 8,16, 290,310,155, 0,0, 2,




where you have pasted the code as I do not understand. Sorry for the question

that is the grow = 0. You need to create the same for grow 1-3.
and it got inserted to the -> CharDefs table.

[[GM]Tinkerbell]

hi guys
Just wondering ifi use a script to unlock um at lvl one willit mess with this.
Thank you castor works well i jus have one issue unlocking that last button at the start

If this wont work does anyone have another suggestion?

[[Dev]Ansem]

Originally Posted by [GM]Tinkerbell

hi guys
Just wondering ifi use a script to unlock um at lvl one willit mess with this.
Thank you castor works well i jus have one issue unlocking that last button at the start

If this wont work does anyone have another suggestion?

I don't think it will mess, but you need to make a "lvl 30" toon (=hard mode one) so it unlocks lvl 70 (UM).
I'm also searching for a fix to have UM unlocked generally and without the need of a hard mode character.

[Danco1990]

Greester, use a stored procedure to do this upon logging in.

[edit]
You can also make 4 UM's, just use a stored procedure to change the grow.

[[GM]Tinkerbell]

thx Danco can you plz give an example of what that stored procedure might look like. Its not my strong point....

[R274188938]

Hi there,

I dont hav the table dbo.usp_Create_Char_R on my PS_GameData tables... could u help me how I should place the codes and how i´d create it if need?

thx vm

idk why but like i said i dont have the usp_Create_Char... wht should I do? also would be nice if sum1 put the link to download the server already with all these changes...



thx

[KrustzJokes]

Originally Posted by R274188938

Hi there,

I dont hav the table dbo.usp_Create_Char_R on my PS_GameData tables... could u help me how I should place the codes and how i´d create it if need?

thx vm

idk why but like i said i dont have the usp_Create_Char... wht should I do? also would be nice if sum1 put the link to download the server already with all these changes...



thx

The table is in The Progammilaty Folder, Not in The table Folder
And there you find usp....

[.Curious.]

While creating a new char these procedures work perfect but after reseting skill or stat via NPC or Reset Stones ,all values turn back to the usual (original) values.

Such as Skill point ,stats etc..

I had a look at some procedures in GameData but could not figure out.
I know stat points per level is about ps_game.exe but have no idea about other stuffs such as HP ,Mp,SP ,Str ,Dex,Skill point.

Two factions also have hp differences. Darkies hp's are 4-5k more than Lighties'.

It can be balanced by item features but not a complete solution and it is becoming a problem in naked pvp events cos darks have more HP than Lights.

It is nothing to do with StatusDefs some are offering that as a solution but not even close cos there is no family descrimination in StatusDefs. There are only jobs which are 6,it means values for job 0 are executed for Fighter/Defender/Warrior/Guardian. If it had been about StatusDefs ,All classes must have had the same HP,MP,DP at both faction but they do not.

Best regards,

[[ADM]Minxii]

you need to edit what the stat/skill reset values I had the same issue with this

I hope this TUT helps