CSV madness

08/23/2013 19:49 Krypticpain#1

Well, Ive started the daunting task of creating a pserver. Im doing it for fun and to learn a lot on the way. I am a Dekaron and pserver noob, but I think I am learning fast and have been reading ePvP and DKUnderground for weeks.

Right now I am trying to get a grip on the CSV madness I find myself in. There HAS to be a better way to handle all these CSVs.

1) Import the CSVs and file DB structure into MS Access. I can create forms that will generate PCs, items and NPCs. It will also give the ability to generate reports that show the exp/item curves vs mob lvl to help with zone balancing.

2) Focus on SQL. Learn SQL in and out to create resuable scripts to generate items/chars etc. This could be done with notepad++. I figure learned SQL will have huge benefits anyway.

3) Do it all by hand in the SQL enterprise manager. I dont know why but this option nags me. I feel like there is an easier way.

I plan on sharing a great deal of my work with the community, only holding back what may risk the security of the server.

What are peoples thoughts, recommendations or requests? I know Access 2013 in and out and have a good grasp of SQL. I just dont have the experience with Dekaron to know what is worth the time.

My weekend project is going to get a SQL script written to generate PCs with appropriate GM stats.

08/23/2013 21:28 janvier123#2

wow, good luck then

08/23/2013 23:44 Krypticpain#3

Quote:

Originally Posted by janvier123

wow, good luck then

Why thank you. I hope to have something for you to see by Monday. Just a little something to get started.

08/27/2013 03:36 Krypticpain#4

Okay, so I wrote a simple SQL query that can be used to create a GM character and update his stats at the same time. If you do some research you will find it similar to KingTut's GM code with a twist.

1. Create a character on the server
2. Open MS Server SQL Query Analyzer
3. Enter this code:

Spoiler

4. Enter the character's name at the bottom, the GM name and feel free to play around with the values.

Feel free to ask questions if I was unclear.

Edit: Added Spoilers

08/27/2013 03:54 Decima#5

Code:

declare @charname char(19)
set @charname = 'paste name here'

Use character
Go 
Update [user_character]
Set dwMoney=900000000,
dwStorageMoney=900000000,
wStr=65500,
wDex=65500,
wCon=65500,
wSpr=65500,
wStatPoint=0,
wSkillPoint=1000,
wLevel=250,
character_name='[GM]'@charname
Where character_name = @charname;

nShield=65000,
nHP=65000
nMP=65000, // not necessary, /gm heal in game will fill ur hp/mp, and it is set from ur stats when u login and all ur stats set to almost max, unless u want ppl to be able to kill ur gm, and im not really sure id 5 billion is going to be ok in ur inv, i think max is only 1, u can mail them money in game as well so i would remove that as well.

08/27/2013 09:28 janvier123#6

and why should you give GMs direct access to the database, thats just wrong !

08/27/2013 10:00 Decima#7

hes not?

just a query to run to make someone a GM right?

08/27/2013 19:44 janvier123#8

lol, ok here is my version (Fixed ofc)

PHP Code:


			
declare @charname varchar(40) // YES 40, not 19
set @charname = 'paste name here'

Use character
Go 
Update [user_character]
Set 
character_name = '[GM]'@charname
Where character_name = @charname;

08/27/2013 20:38 Krypticpain#9

Thank you both for the replies. I really like the declared variables as that makes it easier to interact with.

I started this code because I am at the point where I am creating many characters to test skills, armor sets and animations. This script lets me copy paste instead of manually going into the tables.

I am definitely NOT giving non-dev personal access to the database.

08/28/2013 00:03 Decima#10

i thought max character name length was 25?

minus the 4 needed for the [GM] should had been 21 (not 19, thats my bad) but yeah making it 40, kinda overkill when u can only have 25 max :P plus what if the orginal name is 25 already, and u add 4 more characters to it? db wont like that, and i doubt the game would either

and would be a good idea to set the stats max so players dont kill them every time they log on

Code:

declare @charname varchar(21) // 21, becus max character name length is 25, minus the 4 characters needed for '[GM]'
set @charname = 'paste name here' // if original name length is 25 it will cut 4 off for the GM name and wont bug out the db

Use character
Go 
Update [user_character]
Set 
wStr=65500, // Max their stats so asshole players dont just kill them @ login
wDex=65500,
wCon=65500,
wSpr=65500,
wStatPoint=0, // clear any extra points they may have so they dont bug their GM
wSkillPoint=1000, // give them plenty of skill points to max their skills
wLevel=250, // max their level so they dont gain any extra stat points to bug their gm with
character_name = '[GM]'@charname
Where character_name = @charname;

and if ur smart and dont wanna learn the hard way, dont ever give anyone access to the db, not even ur mom ;)

08/29/2013 02:40 Krypticpain#11

Does the @ sign have a special function in SQL or is it just for your visual reference? I learned SQL through MS Access so Im still picking things up.

08/29/2013 02:47 Decima#12

yes, the @ symbol is what tells the SQL that it is a variable

like the $ in php, and the % in asp

08/29/2013 04:19 Krypticpain#13

Quote:

Originally Posted by Decima

yes, the @ symbol is what tells the SQL that it is a variable

like the $ in php, and the % in asp

Thank you, makes everything clear now.

08/29/2013 06:37 janvier123#14

Quote:

Originally Posted by Decima

i thought max character name length was 25?

Nope, here is some stuff that can back me up :)

PHP Code:


			
--Table: dbo.user_character



CREATE TABLE dbo.user_character (

  character_no       varchar(18),

  character_name     varchar(40) NOT NULL,

PHP Code:


			
CREATE PROCEDURE dbo.SP_CHAR_CREATE

    @user_no            varchar(14)                ,

    @character_name        varchar(40)

PHP Code:


			
CREATE   PROCEDURE dbo.SP_GUILD_GUILDMASTER_UPDATE

    @i_guild_code            VARCHAR(10)        ,    --&#44600;드코드

    @i_from_character_name    VARCHAR(40)        ,    --&#51060;전길드장캐릭터명

    @i_to_character_name    VARCHAR(40)        ,    --&#50948;임길드장캐릭터명

seems its 40 to me :)
anyway, yea it is kinda big but it must have some reason why they made it that big

08/29/2013 16:06 Decima#15

have u tried making a 40 character name and then logging in game tho?

it only reads 25 characters. . . . .

just cus the db is expecting 40 characters or less doesnt mean the game will :P