PDA

View Full Version : SQL routine to create level stats



leifus
04-20-2016, 07:51 PM
Hello, today i want to realease routin i made a while ago to create level stats.

This easily editable routin you can use to create level stats for your server and here is a example for "pet_levelstats". So this adds 175 levels from the current max level, so if the max level is 83 it will add level stats up to 258(83+175 =258), but you can change that by editing the 175 number.

The percentage you can change where it says @percent = 1.10 and you can change how you want(1.05 = 5%, 1.75 = 75%) and so on.

Table can be changed by editing @tableh = pet_levelstats


BEGIN

SET
@percent = 1.10, /* increase 10% from last level */
@tableh = pet_levelstats; /* Table you are going to use */

DECLARE v1 INT DEFAULT 175; /* How many times you want it to run*/

WHILE v1 > 0
DO

INSERT INTO @tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`creature_entry`
,`level` + 1 `level`
,CAST(`hp` * @percent AS UNSIGNED) `hp`
,CAST(`mana` * @percent AS UNSIGNED) `mana`
,CAST(`armor` * @percent AS UNSIGNED) `armor`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM @tableh
WHERE `level` = (SELECT MAX(`level`) FROM @tableh);

SET v1 = v1 - 1;
END WHILE;

END

When it comes to changing the table, you have to change the table colums so they are in tact with the table you change i to. As an example if you want to change to "player_levelstats" you have to change the Insert command to this
- INSERT INTO @tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
+ INSERT INTO @tableh (`race`, `class`, `level`, `str`, `agi`, `sta`, `inte`, `spi`)
and the select command to this

- SELECT
- `creature_entry`
- ,`level` + 1 `level`
- ,CAST(`hp` * @percent AS UNSIGNED) `hp`
- ,CAST(`mana` * @percent AS UNSIGNED) `mana`
- ,CAST(`armor` * @percent AS UNSIGNED) `armor`
- ,CAST(`str` * @percent AS UNSIGNED) `str`
- ,CAST(`agi` * @percent AS UNSIGNED) `agi`
- ,CAST(`sta` * @percent AS UNSIGNED) `sta`
- ,CAST(`inte` * @percent AS UNSIGNED) `inte`
- ,CAST(`spi` * @percent AS UNSIGNED) `spi`
- FROM @tableh
- WHERE `level` = (SELECT MAX(`level`) FROM @tableh);

+ SELECT
+ `race`
+ ,`class`
+ ,`level` + 1 `level`
+ ,CAST(`str` * @percent AS UNSIGNED) `str`
+ ,CAST(`agi` * @percent AS UNSIGNED) `agi`
+ ,CAST(`sta` * @percent AS UNSIGNED) `sta`
+ ,CAST(`inte` * @percent AS UNSIGNED) `inte`
+ ,CAST(`spi` * @percent AS UNSIGNED) `spi`
+ FROM @tableh
+ WHERE `level` = (SELECT MAX(`level`) FROM @tableh);

and make sure they are in the right order. You can also use this to create xp_for_level and other things you need to scale if you are creating a hige level server.

This could probebly done easyer and better, but i did it this way and if you see something i could have possible done better, please let me know :)

Hope you like it :)

Portals
04-20-2016, 08:07 PM
This will come in handy for some people, thank you!

jonmii
04-21-2016, 03:14 PM
Thanks! Added to my SQL utilities list ^^ It was very usefull

Regards

leifus
04-22-2016, 06:06 AM
This will come in handy for some people, thank you!


Thanks! Added to my SQL utilities list ^^ It was very usefull

Regards

Thanks guys, glad you like it :)

revowow
08-15-2016, 08:26 PM
Not sure why this wont work, gives me error online 3

Tommy
08-15-2016, 11:40 PM
Not sure why this wont work, gives me error online 3

Can you screenshot the error?

Marko
11-20-2016, 05:43 PM
SET
@percent = 1.10,
@tableh = pet_levelstats;
/* SQL Error (1054): Unknown column 'pet_levelstats' in 'field list' */
DECLARE v1 INT DEFAULT 171;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE v1 INT DEFAULT 171' at line 1 */
WHILE v1 > 0
DO

INSERT INTO @tableh (`pet_levelstats`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`creature_entry`
,`level` + 1 `level`
,CAST(`hp` * @percent AS UNSIGNED) `hp`
,CAST(`mana` * @percent AS UNSIGNED) `mana`
,CAST(`armor` * @percent AS UNSIGNED) `armor`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM @tableh
WHERE `level` = (SELECT MAX(`level`) FROM @tableh);
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@tableh (`pet_levelstats`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, ' at line 4 */
SET v1 = v1 - 1;
/* SQL Error (1193): Unknown system variable 'v1' */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 5 queries: 0.000 sec. */

tables and rows in my db have same names as in this query but it does not work :(

MrPixelMC
11-20-2016, 06:18 PM
tables and rows in my db have same names as in this query but it does not work :(

What error does it give?

slp13at420
11-20-2016, 09:24 PM
/* SQL Error (1193): Unknown system variable 'v1' */


is the error he is getting.
By simply adding a `@` infront of the variable `v1` (i.e.`@v1`) I got it to stop throwing that error.

Marko
11-21-2016, 03:39 PM
/* SQL Error (1193): Unknown system variable 'v1' */


is the error he is getting.
By simply adding a `@` infront of the variable `v1` (i.e.`@v1`) I got it to stop throwing that error.

Have put @ in front of every v1 now, like this:


SET
@percent = 1.20,
@tableh = pet_levelstats;
DECLARE @v1 INT DEFAULT 10;

WHILE @v1 > 0
DO

INSERT INTO @tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`creature_entry`
,`level` + 1 `level`
,CAST(`hp` * @percent AS UNSIGNED) `hp`
,CAST(`mana` * @percent AS UNSIGNED) `mana`
,CAST(`armor` * @percent AS UNSIGNED) `armor`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM @tableh
WHERE `level` = (SELECT MAX(`level`) FROM @tableh);

SET @v1 = @v1 - 1;
END WHILE;



but again errors:


SET
@percent = 1.20,
@tableh = pet_levelstats;
/* SQL Error (1054): Unknown column 'pet_levelstats' in 'field list' */
DECLARE @v1 INT DEFAULT 10;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE @v1 INT DEFAULT 10' at line 1 */
WHILE @v1 > 0
DO

INSERT INTO @tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`creature_entry`
,`level` + 1 `level`
,CAST(`hp` * @percent AS UNSIGNED) `hp`
,CAST(`mana` * @percent AS UNSIGNED) `mana`
,CAST(`armor` * @percent AS UNSIGNED) `armor`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM @tableh
WHERE `level` = (SELECT MAX(`level`) FROM @tableh);
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, ' at line 4 */
SET @v1 = @v1 - 1;
END WHILE;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END WHILE' at line 1 */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 4 of 5 queries: 0.000 sec. */

:foreveralone:

Rochet2
11-21-2016, 03:49 PM
You cannot use @varname for table names. You must explicitly set the table name.
So replace all @tableh with pet_levelstats

That probably fixes all the errors.. hmm
read more here: http://stackoverflow.com/questions/2838490/table-name-as-variable

Marko
11-21-2016, 04:18 PM
You cannot use @varname for table names. You must explicitly set the table name.
So replace all @tableh with pet_levelstats

That probably fixes all the errors.. hmm
read more here: http://stackoverflow.com/questions/2838490/table-name-as-variable

Like this?


SET
@percent = 1.20,
@tableh = pet_levelstats;
DECLARE @v1 INT DEFAULT 10;

WHILE @v1 > 0
DO

INSERT INTO @pet_levelstats (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`creature_entry`
,`level` + 1 `level`
,CAST(`hp` * @percent AS UNSIGNED) `hp`
,CAST(`mana` * @percent AS UNSIGNED) `mana`
,CAST(`armor` * @percent AS UNSIGNED) `armor`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM @pet_levelstats
WHERE `level` = (SELECT MAX(`level`) FROM @pet_levelstats);

SET @v1 = @v1 - 1;
END WHILE;

but still does not work.. :okay:


SET
@percent = 1.20,
@tableh = pet_levelstats;
/* SQL Error (1054): Unknown column 'pet_levelstats' in 'field list' */
DECLARE @v1 INT DEFAULT 10;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE @v1 INT DEFAULT 10' at line 1 */
WHILE @v1 > 0
DO

INSERT INTO @pet_levelstats (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`creature_entry`
,`level` + 1 `level`
,CAST(`hp` * @percent AS UNSIGNED) `hp`
,CAST(`mana` * @percent AS UNSIGNED) `mana`
,CAST(`armor` * @percent AS UNSIGNED) `armor`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM @pet_levelstats
WHERE `level` = (SELECT MAX(`level`) FROM @pet_levelstats);
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@pet_levelstats (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`,' at line 4 */
SET @v1 = @v1 - 1;
END WHILE;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END WHILE' at line 1 */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 4 of 5 queries: 0.000 sec. */

slp13at420
11-21-2016, 05:21 PM
yea I am getting the same error.



SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END WHILE' at line 1


that one tho I dunno how to fix lol ...

Marko
11-21-2016, 05:34 PM
its too damn tiresome manually making all level, pet, player, xp stats for a fun server.. for a jadecore 5.4.8 that means 165 more levels to 255. for each pet am manually adding one by one lvl stats.. takes too long. not to mention some end at lvl 85 and continue after next few pets pet so i have to search manually to duplicate that row and edit properly each stat..

:smash:

Rochet2
11-21-2016, 05:42 PM
I said to replace @tableh with pet_levelstats, not with @pet_levelstats.
Also while you are at it, remove this:

@tableh = pet_levelstats;

from the SET statement at the top

slp13at420
11-21-2016, 05:45 PM
30 seconds with the search window for `pet_levelstats` produced this thread:
http://emudevs.com/showthread.php/2890-toolz-2-do-a-255-realm

but that was in the donor section that no longer exists.
here are the links to those tools:

https://github.com/BlackWolfsDen/Toolz/tree/master/CreatureStatGen
https://github.com/BlackWolfsDen/Toolz/tree/master/Pet_LevelStats
https://github.com/BlackWolfsDen/Toolz/tree/master/PlayerStatsGenerator

I went ahead and moved the thread to here since it seems it can be helpful still ;) :
http://emudevs.com/showthread.php/2890-toolz-2-do-a-255-realm

slp13at420
11-21-2016, 06:02 PM
here is what I did to just troubleshoot:



-- Template released by Leifus of EmuDevs.com
-- \o/ Tnx Leifus
-- http://emudevs.com/showthread.php/5604-SQL-routine-to-create-level-stats

BEGIN

SET

-- @percent = 1.10, /* increase 10% from last level */
-- @tableh = pet_levelstats; /* Table you are going to use */

DECLARE @v1 INT DEFAULT 105; /* How many times you want it to run*/

WHILE (@v1 > 0) DO

/* INSERT INTO @tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`creature_entry`
,`level` + 1 `level`
,CAST(`hp` * @percent AS UNSIGNED) `hp`
,CAST(`mana` * @percent AS UNSIGNED) `mana`
,CAST(`armor` * @percent AS UNSIGNED) `armor`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM @tableh
WHERE `level` = (SELECT MAX(`level`) FROM @tableh);
*/
SET @v1 = @v1 - 1;

END WHILE;

END;



yet I get an error with `END WHILE;` even tho I only have 7 active lines of code there lol it looks right when I researched sql `WHILE` loops.

Rochet2
11-21-2016, 06:04 PM
Here is the full code you need, it should work on mysql 5.6


DROP PROCEDURE IF EXISTS create_level_stats;

delimiter //

CREATE PROCEDURE create_level_stats()
BEGIN

DECLARE v1 INT DEFAULT 10; /* How many times you want it to run*/

SET
@percent = 1.10; /* increase 10% from last level */

WHILE v1 > 0
DO

INSERT INTO pet_levelstats (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`creature_entry`
,`level` + 1 `level`
,CAST(`hp` * @percent AS UNSIGNED) `hp`
,CAST(`mana` * @percent AS UNSIGNED) `mana`
,CAST(`armor` * @percent AS UNSIGNED) `armor`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM pet_levelstats
WHERE `level` = (SELECT MAX(`level`) FROM pet_levelstats);

SET v1 = v1 - 1;
END WHILE;

END

//
delimiter ;

call create_level_stats();

DROP PROCEDURE IF EXISTS create_level_stats;

slp13at420
11-21-2016, 06:10 PM
aaah that's what I was missing lol create/destry procedure. the examples I found didn't all have that in them so I didn't concern my self with it ...

and yea that works proper now .

Marko
11-21-2016, 08:43 PM
Thank you so much guys :)
I could post sql of a teleporter for jadecore 5.4.8 i managed to adjust from wowsource.. but i doubt anyone here would need that.
OH and yeah important warning for anyone wanting to use this epic sql, remember to change all your datatype of table pet_levelstats (those SMALLINT) to MEDIUMINT, otherwise it will not be able to support those high custom stats :)

edit:
here is a working query for player_levelstats.. :)


DROP PROCEDURE IF EXISTS create_level_stats;

delimiter //

CREATE PROCEDURE create_level_stats()
BEGIN

DECLARE v1 INT DEFAULT 10;

SET
@percent = 1.20;

WHILE v1 > 0
DO

INSERT INTO player_levelstats (`race`, `class`, `level`, `str`, `agi`, `sta`, `inte`, `spi`)
SELECT
`race`
,`class` + 1 `level`
,`level` + 1 `level`
,CAST(`str` * @percent AS UNSIGNED) `str`
,CAST(`agi` * @percent AS UNSIGNED) `agi`
,CAST(`sta` * @percent AS UNSIGNED) `sta`
,CAST(`inte` * @percent AS UNSIGNED) `inte`
,CAST(`spi` * @percent AS UNSIGNED) `spi`
FROM player_levelstats
WHERE `level` = (SELECT MAX(`level`) FROM player_levelstats);

SET v1 = v1 - 1;
END WHILE;

END

//
delimiter ;

call create_level_stats();

DROP PROCEDURE IF EXISTS create_level_stats;

Ofc i was not satisfied since at lvl 100 some class/races had just 413stamina so i manually edited all level 100 staminas to have at least 714, since some other class/races had like 1600 @ lvl 100

:OhIMember: dont forget to to change all your stats datatype to MEDIUMINT