AnnieRuru

May 31, 2018

are you using hercules ?
1. hercules dropped specialeffect2 ... the way you use specialeffect is hercules format
2. hercules use SC_SOULLINK, rathena use SC_SPIRIT ... and your script follow hercules format again ...

rather than fix that script, I would rather re-write this whole thing ... using hercules

prontera,155,185,3	script	Guild Buffer	4_F_ARUNA_POP,{
	mes "[Guild Buffs]";
	if ( !getcharid(2) ) {
		mes "I'm sorry, I can only help you, if you're in a guild.";
		close;
	}
	mes "Hello, I offer buffs for guilds.";
	mes "Please select the buff you want to buy for your guild.";
	mes "The buffs you bought will be permanently available for your guild.";
	next;
	mes "[Guild Buffs]";
	mes "Guild Leaders can remove buffs by selecting to buy them and then confirming.";
	.@gid = getcharid(2);
	next;
	.@s = select( ( (getguildmasterid( getcharid(2) ) == getcharid(0))? "^FF0000Guild Master setting^000000:" : ":" )+ .buff_menu$ ) -2;
	if ( .@s == -1 ) {
		.@s = select( .buff_menu$ ) -1;
		mes "[Guild Buffs]";
		if ( $guild_buff[.@gid] & (1 << .@s) ) {
			mes "You already bought "+ .buff_desc$[.@s] +".";
			mes "Do you want to remove it?";
			next;
			if ( select( "No", "Yes" ) == 1 ) close;
			$guild_buff[.@gid] &= ~(1 << .@s);
			mes "[Guild Buffs]";
			mes "Transaction completed. "+ .buff_desc$[.@s] +" has been deleted from your Guildbuffs.";
			close;
		}
		if ( .buff_price[.@s] > Zeny ) { // can change into #CASHPOINTS
			mes "I'm sorry, you don't have enough cash.";
			close;
		}
		Zeny -= .buff_price[.@s]; // can change into #CASHPOINTS
		mes "Transaction complete, the buff is now available for your guild.";
		$guild_buff[.@gid] |= (1 << .@s);
		close;
	}
//	if ( $guild_buff[.@gid] & (1 << .@s) )
		callsub L_Buff, .@s;
	// no dialog for buff that is not yet bought ...
	close;
	
L_Buff:
	.@gid = getcharid(2);
	switch ( getarg(0) ) {
	case 0:
		if ( $guild_buff[.@gid] & (1<<0) ) {
			sc_start SC_ASSUMPTIO, -1, 5;
//			specialeffect EF_ASSUMPTIO, AREA, getcharid(3);
			skilleffect HP_ASSUMPTIO, 5;
		}
		break;
	case 1:
		if ( $guild_buff[.@gid] & (1<<1) ) {
			sc_start SC_MAGNIFICAT, -1, 5;
//			specialeffect EF_MAGNIFICAT, AREA, getcharid(3);
			skilleffect PR_MAGNIFICAT, 5;
		}
		break;
	case 2:
		if ( $guild_buff[.@gid] & (1<<2) ) {
			sc_start SC_ANGELUS, -1, 10;
//			specialeffect EF_ANGELUS, AREA, getcharid(3);
			skilleffect AL_ANGELUS, 10;
		}
	break;
	case 3:
		if ( $guild_buff[.@gid] & (1<<3) ) {
			sc_start SC_GLORIA, -1, 5;
//			specialeffect EF_GLORIA, AREA, getcharid(3);
			skilleffect PR_GLORIA, 5;
		}
	break;
	case 4:
		if ( $guild_buff[.@gid] & (1<<4) ) {
			sc_start SC_IMPOSITIO, -1, 5;
//			specialeffect EF_IMPOSITIO, AREA, getcharid(3);
			skilleffect PR_IMPOSITIO, 5;
		}
		break;
	case 5:
		if ( $guild_buff[.@gid] & (1<<5) ) {
			switch( BaseJob ) {
				case Job_Alchemist: .@spirit = SL_ALCHEMIST; break;
				case Job_Monk: .@spirit = SL_MONK; break;
				case Job_Star_Gladiator: .@spirit = SL_STAR; break;
				case Job_Sage: .@spirit = SL_SAGE; break;
				case Job_Crusader: .@spirit = SL_CRUSADER; break;
				case Job_SuperNovice: .@spirit = SL_SUPERNOVICE; break;
				case Job_Knight: .@spirit = SL_KNIGHT; break;
				case Job_Wizard: .@spirit = SL_WIZARD; break;
				case Job_Priest: .@spirit = SL_PRIEST; break;
				case Job_Bard: case Job_Dancer: .@spirit = SL_BARDDANCER; break;
				case Job_Rogue: .@spirit = SL_ROGUE; break;
				case Job_Assassin: .@spirit = SL_ASSASIN; break;
				case Job_Blacksmith: .@spirit = SL_BLACKSMITH; break;
				case Job_Hunter: .@spirit = SL_HUNTER; break;
//				case Job_Soul_Linker: .@spirit = SL_SOULLINKER; break; // excluding soul linker spirite
				default:
//					if ( ( Upper & 1 ) && BaseLevel < 70 && !( eaclass() & EAJL_2 ) ) // excluding rebirth spirit
//							.@spirit = SL_HIGH;
			}
			if ( .@spirit ) {
				sc_start4 SC_SOULLINK, -1, 4, .@spirit, 0, 0;
//				specialeffect EF_SOULLINK, AREA, getcharid(3);
				skilleffect .@spirit, 5;
			}
		}
	}
	return;
OnInit:
	.buff_name$[0] = "Assumptio";
	.buff_desc$[0] = "Assumptio Level 5";
	.buff_price[0] = 1000;
	.buff_effect[0] = EF_ASSUMPTIO;

	.buff_name$[1] = "Magnificat";
	.buff_desc$[1] = "Magnificat Level 5";
	.buff_price[1] = 5000;
	
	.buff_name$[2] = "Angelus";
	.buff_desc$[2] = "Angelus Level 10";
	.buff_price[2] = 5000;
	
	.buff_name$[3] = "Gloria";
	.buff_desc$[3] = "Gloria Level 5";
	.buff_price[3] = 5000;
	
	.buff_name$[4] = "Impositio Manus";
	.buff_desc$[4] = "Impositio Manus Level 5";
	.buff_price[4] = 5000;
	
	.buff_name$[5] = "Spirits";;
	.buff_desc$[5] = "Spirits Level 4 (excluding Rebirth Spirit and Soul Linker Spirit)";
	.buff_price[5] = 10000;

	.buff_menu$ = implode(.buff_name$, ":");
	end;
}

May 30, 2018

I don't think our script commands are that ... advance yet

create a new monster with mob_avail might be a faster way

May 23, 2018

6 hours ago, pajodex said:

My point is.. can this also be implemented in rAthena or something a-like? This would greatly help improve all scripts that will utilize queue system.. especially BGs.. This is just a suggestion.. Though it is possible to make a queue using arrays and stuff but it is more convenient when it has its own script command.

uhh ... honestly ... rathena can survive without the queue iterator script commands ...
I've used it before, so its not a MUST HAVE stuffs

for example the last man standing script I've made for hercules -> http://herc.ws/board/topic/11185-last-man-standing-using-queue-iterator-script-commands/
is actually just a rewrite from this topic -> https://rathena.org/board/topic/90441-last-man-standing/

and for the battleground ... that's a misunderstanding
rathena *bg_create supports OnQuit and OnDeath event label
but hercules *bg_create_team doesn't ...
so its practically forcing the Hercules members to use Queue Iterator script commands to make battleground queue system
for example I made exact same system but meant for different emulator (herc/rA)
http://herc.ws/board/topic/15913-annieruru-emp-bg/
yeah, practically 2 different script ...
if you read the Hercules version carefully, you'll noticed that Hercules one,
after doing *bg_create_team and*bg_team_join, still has to add *queue and *queueadd, which is actually wasting memory in a sense

well, but its truly convenient in some cases
for example the *queueopt script command although only support 3 flags, but can extend more by plugin
can add more like QUEUEOPT_NOCHANGEEQ for example to make the players in the queue cannot change equipment ... etc

May 22, 2018

conf\battle\monster.conf

// Zeny from mobs
zeny_from_mobs: yes

its a yes/no condition though, because the formula is hard-coded ... mob.cpp

			if(battle_config.zeny_from_mobs && md->level) {
				 // zeny calculation moblv + random moblv [Valaris]
				zeny=(int) ((md->level+rnd()%md->level)*per*bonus/100.);
				if(md->db->mexp > 0)
					zeny*=rnd()%250;
			}

May 22, 2018

nono ... the permanent server variable $var has to be use here to prevent other players from claiming the prize

your script, you should try with multi-client
Player A reach the max level in the server, then Player B can again claim the prize again, Player C again can claim the prize

if done it with permanent server variable then only Player A can claim the prize

the variable maxlvlreward is on the wrong scope
should use $maxlvlreward[BaseJob] something like this

May 22, 2018

19 hours ago, Tyrfing said:

Here you go, please upvote my post.

mine -> https://rathena.org/board/topic/75015-asking-for-a-prize-npc/
pajodex -> https://rathena.org/board/topic/115293-race-to-99-rewards/#comment-344269

the similarity is there is always a permanent global variable, and your script is missing this thing

heh ... you got potential ... so I refrain from doing this one

May 22, 2018

http://upaste.me/28324983795b4e841

May 22, 2018

that' how the original Devil Square works from MU online
http://muonline.webzen.com/guides/25/116/game-contents/devil-square

if you want players can only hit 1 chest and cannot attack the rest needs source modification

a better solution that give out rewards per player,
then do like King of Emperium Hill style, an npc appear for Exit that's give reward upon clicking it

May 22, 2018

[Warning]: script: showscript: self can't be used for non-players objects.
[Debug]: Source (NPC): #rockno04 at prontera (155,185)

should be

prontera,155,185,3	script	#rockno04	111,5,5,{
	end;
OnTouch:
	showscript "West: The Inn, East: The Sheriff's Office", getcharid(3), SELF;
	end;
}

May 21, 2018

that's the fun part why I did that
did you noticed 705,032,704 + Your current Zeny = MAX_ZENY ?

hmm ... maybe I should split it out and tell the players why the value shrink instead ? hmm ....

http://upaste.me/2c6e4982211843700
yeah not that hard

May 21, 2018

okay, my turn too xD

http://upaste.me/5e274982184279fcd

Emistry script is total failure ...
yes the script engine has a limit of 2.1b, but SQL can calculate the value more than that
so its possible to make this script entirely with strings , and let the SQL do the calculation

pajodex ... not bad
a very obvious bug on a few lines with the SELECT syntax

line 84
if( query_sql("SELECT `daily_deposit` FROM `s_bank` WHERE `daily_deposit`<="+.@amount+" AND `account_id`="+getcharid(3))) {

line 110
if( query_sql("SELECT `daily_withdraw` FROM `s_bank` WHERE `daily_withdraw`<="+.@amount+" AND `account_id`="+getcharid(3))) {

line 114
if( query_sql("SELECT `zeny` FROM `s_bank` WHERE `zeny`<="+.@amount+" AND `account_id`="+getcharid(3))) {

which caused my test server to spam these error

[Warning]: script:query_sql: Too many columns, discarding last 1 columns.
[Debug]: Source (NPC): Super Banker at prontera (150,185)

ok explanation ...

.@nb = query_sql( "SELECT 5", .@value );

the .@nb is 1, .@value is 5
.@nb is equal to 1 is because it return 1 row of data
you have did the pvp ladder script which return number of rows of the highest pvp points ... you should know what this means

so the if ( query_sql("... means if the SQL does fetch the data successfully ...
usually meant to check if the player has the data inside the table or not

you can either add a .@dummy variable for it ... or change the line into

line 84
query_sql "SELECT `daily_deposit` < "+ .@amount +" FROM `s_bank` WHERE `account_id` = "+ getcharid(3), .@condition;
if ( .@condition == true ) {

and my script ... I let the script engine do all the calculations XD
there is no need to have a calculator in front of my computer to know 7,463,562 + X = 2,000,000,000
my script just let the *input script command calculate whats the best value to input in

		query_sql "select "+ .bank_with$[.@t] +" - "+ .@withdraw$, .@limit$;
		input .@num, 0, min( atoi( .@limit$ ), atoi( .@zeny$ ), MAX_ZENY - Zeny );

either the withdraw limit, the Zeny in the bank, or the maximum zeny you can hold ... the *input command do that automatically

May 20, 2018

basically is a modification from this topic
instead of killing the leader minus 2 points, you just want the leader killed immediately and win the round

http://upaste.me/bb1949814ef80ce94

On 5/19/2018 at 10:02 AM, Santafe said:

(and if possible make it part of github as well? )

I am sure only the scripts that's useful for learning purpose can go inside the github
example like devil square ... shows how to kill monster round by round
the battleground script template in the github are so basic and are lacking a lot of modern features ...

May 20, 2018

hmm ... do you know all woe/event controller only support weekday and hour setting, but omit the minute setting ?
because let's say I want the woe go from Sunday 11:30pm until Monday 12:30 am, this is actually very complicated to do

same thing happen here, the minute stuff is assuming the hour runs at 00 minute

so yup, your minute condition is wrong

if ( (gettime(DT_HOUR) != 12 && gettime(DT_HOUR) != 13) && gettime(DT_MINUTE) > 5 && gettime(DT_MINUTE) <= 6 ) {

honestly, just stick to ... assume it runs on xx:00 , then don't need to consider all the complicated minute stuff

May 20, 2018

unit controller does the trick

prontera,155,185,5	script	skfshdfs	1_F_MARIA,{
	mes "blah blah"; // main script here ... all lines below needs to be added
	close;
OnInit:
	if ( gettime(DT_HOUR) != 8 && gettime(DT_HOUR) != 9 || gettime(DT_MINUTE) > 15 ) {
		hideonnpc strnpcinfo(0);
		end;
	}
OnClock0800:
OnClock0900:
	hideoffnpc strnpcinfo(0);
	while ( checkcell( .@map$ = F_Rand("prontera", "payon", "izlude","geffen"), .@x = rand(500), .@y = rand(500), cell_chknopass ) );
	unitwarp getnpcid(0), .@map$, .@x, .@y;
//	announce "warp to "+ .@map$ +" "+ .@x +" "+ .@y, bc_all;
	sleep 15 * 60 * 1000; // 15 minutes
	hideonnpc strnpcinfo(0);
	end;
}

May 19, 2018

guild_vs5	mapflag	pvp
prontera,155,185,5	script	skfdhsjf	1_F_MARIA,{
	select "GO PVP";
	warp "guild_vs5", 0,0;
	attachnpctimer;
	initnpctimer;
	end;
OnTimer5000:
	callsub L_checkmap;
	sc_start SC_AGIFOOD,180000,3;
	end;
OnTimer10000:
	callsub L_checkmap;
	sc_start SC_VITFOOD,180000,3;
	end;
OnTimer15000:
	callsub L_checkmap;
	sc_start SC_VITFOOD,180000,3;
	end;
L_checkmap:
	if ( strcharinfo(3) != "guild_vs5" ) {
		stopnpctimer;
		detachnpctimer;
		end;
	}
	return;
}

urgh .... do the rest yourself

unfortunately rathena don't have something like hercules' QUEUEOPT_MAPCHANGE so nah ... the buff will stay on changing map

EDIT: or unless making another OnPCQuitMapEvent: ....

May 19, 2018

do the same thing over there ....
instead of warping the player, just do your refiner stuffs there

even a beginner level scripter can do this kind of stuffs

May 18, 2018

ok let me rephrase that sentence ...
in rathena .......

34,Instance ONE,3600,15,guild_vs1,49,49
35,Instance TWO,3600,15,guild_vs2,49,49

prontera,150,180,0	script	Check Instance	1_F_MARIA,{
OnClick:
	dispbottom strcharinfo(3); 
	dispbottom instance_id() +""; // this line sux ... if you only have 1 instance attached then its ok, but if more than one ...
	end;
OnInit:
	bindatcmd "test", strnpcinfo(0)+"::OnClick";
	end;
}

prontera,155,180,0	script	Instance ONE	1_F_MARIA,{
	if ( getstrlen( instance_mapname("guild_vs1") ) ) {
		instance_enter "Instance ONE";
		end;
	}
	if ( instance_create( "Instance ONE", IM_PARTY ) < 0 ) {
		mes "failed to create instance";
		close;
	}
	instance_enter "Instance ONE";
	end;
}

prontera,158,180,0	script	Instance TWO	1_F_MARIA,{
	if ( getstrlen( instance_mapname("guild_vs2") ) ) {
		instance_enter "Instance TWO";
		end;
	}
	if ( instance_create( "Instance TWO", IM_GUILD ) < 0 ) {
		mes "failed to create instance";
		close;
	}
	instance_enter "Instance TWO";
	end;
}

yes, one for party, another one for guild

now here's the scenario ...

let's say Player A same party with Player C, and Player A register the Instance ONE for party
Player B same guild with Player C, and Player B register the Instance TWO for guild

Player C has the choice to go in both instance, since Player C has meet both requirement

my test result is ... it seems rathena has bug in this case, Player C allow to go in Party type only

test again in hercules ... no problem, Player C can go inside both instance

prontera,150,180,0	script	Check Instance	1_F_MARIA,{
OnClick:
	dispbottom strcharinfo(3);
	dispbottom instance_id() +""; // this script command is useless in hercules, always return -1 on non-instanced npc. An instanced npc don't need to use this script command anyway
	dispbottom has_instance2("guild_vs1") +"";
	dispbottom has_instance2("guild_vs2") +"";
	end;
OnInit:
	bindatcmd "test", strnpcinfo(0)+"::OnClick";
	end;
}

prontera,155,180,0	script	Instance ONE	1_F_MARIA,{
	if ( has_instance2("guild_vs1") >= 0 ) {
		warp has_instance("guild_vs1"), 49,49;
		end;
	}
	if ( ( .@ins = instance_create( "Instance ONE", getcharid(CHAR_ID_PARTY), IOT_PARTY ) ) < 0 ) {
		mes "error : "+ .@ins;
		close;
	}
	if ( !getstrlen( instance_attachmap( "guild_vs1", .@ins, true, .@ins +"INS1" ) ) ) {
		mes "error : 5";
		instance_destroy .@ins;
		close;
	}
	instance_set_timeout 3600, 15, .@ins;
	instance_init .@ins;
	warp has_instance("guild_vs1"), 49,49;
	end;
}

prontera,158,180,0	script	Instance TWO	1_F_MARIA,{
	if ( has_instance2("guild_vs2") >= 0 ) {
		warp has_instance("guild_vs2"), 49,49;
		end;
	}
	if ( ( .@ins = instance_create( "Instance ONE", getcharid(CHAR_ID_GUILD), IOT_GUILD ) ) < 0 ) {
		mes "error : "+ .@ins;
		close;
	}
	if ( !getstrlen( instance_attachmap( "guild_vs2", .@ins, true, .@ins +"INS2" ) ) ) {
		mes "error : 5";
		instance_destroy .@ins;
		close;
	}
	instance_set_timeout 3600, 15, .@ins;
	instance_init .@ins;
	warp has_instance("guild_vs2"), 49,49;
	end;
}

May 18, 2018

Spoiler

1st thing came to my mind was bit-shifting ....


prontera,155,185,5	script	bit set	1_F_MARIA,{
	while (true) {
		mes "Number is -> "+ .bit;
		for ( .@i = 0; .@i < 4; ++.@i )
			mes "Set "+( .@i +1 )+" -> "+( (.bit >> (.@i*5)) & ((1<<5)-1) );
		next;
		.@s = select( "Set 1:Set 2:Set 3:Set 4" ) -1;
		input .@num, 0, (1<<5) -1;
		.bit &= ~( ((1<<5)-1) << (.@s*5));
		.bit |= .@num << (.@s*5);
	}
}

prontera,158,185,5	script	bit increment	1_F_MARIA,{
	while (true) {
		mes "Number is -> "+ .bit;
		for ( .@i = 0; .@i < 4; ++.@i )
			mes "Set "+( .@i +1 )+" -> "+( (.bit >> (.@i*5)) & ((1<<5)-1) );
		next;
		.@s = select( "Set 1:Set 2:Set 3:Set 4" ) -1;
		.@num = ( (.bit >> (.@s*5)) & ((1<<5)-1) ) +1;
		.bit &= ~( ((1<<5)-1) << (.@s*5));
		.bit |= .@num << (.@s*5);
	}
}

then I realize bit-shifting not allow to do a simple addition or even value increment ...
hmm ... maybe use back the old school base 10 method then

-	script	Sample	-1,{
OnPCBaseLvUpEvent:
	if ( Upper & 1 ) end; // yeah PRE-TRANS ...
	else if ( BaseLevel == 90 && (#lv_milestone /1 %10) < 3 ) {
		#lv_milestone += 1;
		getitem 607, 15;
		getitem 610, 15;
		getitem 616, 1;
		dispbottom "You gained reward for level 90.";
	}
	else if ( BaseLevel == 70 && (#lv_milestone /10 %10) < 3 ) {
		#lv_milestone += 10;
		getitem 607, 10;
		getitem 610, 10;
		dispbottom "You gained reward for level 70.";
	}
	else if ( BaseLevel == 50 && (#lv_milestone /100 %10) < 3 ) {
		#lv_milestone += 100;
		getitem 607, 5;
		getitem 610, 5;
		dispbottom "You gained reward for level 50.";
	}
	end;
}

May 18, 2018

@Emistry should be donpcevent

donpcevent "::OnEventEmote";

another thing that's different from rathena and hercules is the gettime constants ...
hercules use GETTIME_WEEKDAY
rathena use DT_DAYOFWEEK

May 17, 2018

same topic appear on hercules forum

34,Test Instance,3600,15,guild_vs2,49,49

prontera,155,180,0	script	Test Instance	1_F_MARIA,{
	if ( getstrlen( instance_mapname("guild_vs2") ) ) {
		instance_destroy;
	}
	if ( instance_create( "Test Instance", IM_CHAR ) < 0 ) {
		mes "failed to create instance";
		close;
	}
	instance_enter "Test Instance";
	end;
}
guild_vs2,49,49,5	script	test dialog	1_F_MARIA,{
	dispbottom strnpcinfo(4);
	mes sprintf( "%d monsters on this map", 'amount );
	next;
	select "Destroy";
	instance_destroy;
	end;
OnInstanceInit:
	monster instance_mapname("guild_vs2"), 49,49, "--ja--", 1002, 10, instance_npcname( strnpcinfo(0) )+"::OnMobDead";
	'amount = 10;
	end;
OnMobDead:
	--'amount;
	if ( !'amount )
		instance_announce 0, "all monster killed", bc_map;
	end;
}

A few things noted for rathena ones ...

1. rathena has instance_db.txt file ... make sure you add this line ... and @reloadinstancedb

2. rathena has much less instance related script command, making rathena instance script easier to learn, but there are 2 drawback
a. rathena seems cannot retrieve the instance ID you are in ... but doesn't matter since the map name is the one matter most
b. not as flexible as hercules has instance_timeout script command ... this value is hardcoded into database so all party has to use the same timeout value

3. rathena instance ID start from 1, hercules instance ID start from 0
a. make sure the instance_announce is set to 0

May 16, 2018

4 hours ago, Akkarin said:

As for being a kids project, the average age of the dev team is ~27. The average age of our user base is much lower.

Laugh My Ass Off !!!!
where do you get this statistic XD

anyway rathena was from eathena, and eathena was from athena ...
athena has renamed as jathena ... yup, early eathena has bunch of japanese comments on them

official ragnarok emulator is Aegis btw
you can google search for it ... some people leak it on github

May 1, 2018

Original guide is in Hercules forum, I just import the information into rAthena since both emulator share most of the similar trait when using query_sql
and I guess the main reason is Olrox reminded me to do so ... because someone else might claim it as their own ... etc

Anyway, this guide is a compilation of SQL commands that I have used, or Questions answered on the forum
every single subject here are related to rAthena/Ragnarok Online in some ways, so you won't feel bored reading them XD

Table of Content
1. When to use *escape_sql script command
2. How to build a case-sensitive table
3. Choose a table type, MyISAM or InnoDB ?
3a. How to index a table properly
3b. Why you shouldn't use `char_reg_num` table
4. AUTO_INCREMENT
5. How to do IF-ELSE in SQL query ?
5a. How to update multiple rows on different conditions in a single query
6. How to show the current rank of the player
7. INSERT INTO ... SELECT ...
8. Table JOIN vs AS
9. What is the maximum string limit for *query_sql
9a. UNION

This topic will open to Suggestions, Ideas, Improvements, and Questions ~
You may also post up your tricks if you want to share with us

1. When to use escape_sql script command

ultramage said:

if the message contains " or ' or some such special character, then after it gets combined into a single string, it will not form a correct query

.

	input .@haha$;
	dispbottom .@haha$;
	dispbottom escape_sql(.@haha$);

it doesn't has much differences, because it only affect 3 special characters
' <- single quotation mark
" <- double quotation mark
\ <- left slash

if I input -> haha"lala'hehe <-
it will return -> haha\"lala\'hehe <-
this is what we call, Escape a character

in rAthena script, we also know we can use " symbol in any string input

	mes "Susan says :\" Today I ate 3 eggs \".";

where in the game client, you can see the " symbol in the npc msg box

let's say I have a sql script like this

prontera,153,171,5	script	Show Characters	1_F_MARIA,{
	mes "input name, I'll show you all characters name it has on that player's account";
	input .@name$;
	.@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `name` LIKE '"+ .@name$ +"'", .@cid, .@name$);
	if ( !.@nb ) {
		mes "no result";
		close;
	}
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes .@cid[.@i] +" "+ .@name$[.@i];
	close;
}

this script has a possibility to be hacked
because to perform sql injection, I can enclose the string with quotation mark, then use another sql command to hack

BUT with an escape_sql command, if the user want to enclose the string with quotation mark to hack the script
the escape_sql command escaped the string, the quotation mark the user input will be escaped
thus the script will become impossible to hack

just now that script was for string input

prontera,153,171,5	script	Show Characters	1_F_MARIA,{
	mes "input account ID, I'll show you all characters name it has on that player's account";
	input .@aid$;
	.@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `account_id` = "+ escape_sql(.@aid$), .@cid, .@name$);
	if ( !.@nb ) {
		mes "no result";
		close;
	}
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes .@cid[.@i] +" "+ .@name$[.@i];
	close;
}

this is another stupid case.
1. the scripter use string input while the script just needed a number
2. even with escape_sql command over there, there is no quotation mark at all
yes this script also has a risk to be hack

because escape_sql only escape quotation mark.
that hacker don't even have to input quotation mark because it is a number
and an injection query can be sent without any quotation mark input

there are 2 ways to solve this
either use numeric variable for the input command
or enclose that ....

..... WHERE `account_id` = '"+ escape_sql(.@aid$) +"'", ....

with single quotation mark, when the hacker input a quotation mark will be escaped by escape_sql command

Reference : https://www.w3schools.com/sql/sql_injection.asp

escape_sql command for another thing is
if the player register their names containing ' or ", these characters are escaped
only happens when the server have no restriction on the creation of players name

rAthena\conf\char_athena.conf

// Manage possible letters/symbol in the name of charater. Control character (0x00-0x1f) are never accepted. Possible values are:
// NOTE: Applies to character, party and guild names.
// 0: no restriction (default)
// 1: only letters/symbols in 'char_name_letters' option.
// 2: Letters/symbols in 'char_name_letters' option are forbidden. All others are possibles.
char_name_option: 1

and this was what happened to my SQL dota pvpladder script

Quote

I'm having this DB error with the script:


[SQL]: DB error - You have an error in your SQL syntax; check the manual that co
rresponds to your MySQL server version for the right syntax to use near 's Babie
s', 1 , 1 )' at line 1
[Debug]: at ..\src\map\script.c:11553 - insert into ownladder ( guild_id, name,
currentown, highestown ) values ( 98, 'Silo's Babies', 1 , 1 )
[Debug]: Source (NPC): DOTAPVP (invisible/not on a map)

I'm using SVN 12077. But this error only happens when that certain guild would have a kill.

Silo's Babies <-- this is a guild name
you can see the 5th string has a single quotation mark

with escape_sql command, that string will turn into
Silo\'s Babies <-- the quotation mark is escaped when send to sql query

2. How to build a case-sensitive table

this is the answer I found
Reference : http://dba.stackexchange.com/questions/15250/how-to-do-a-case-sensitive-search-in-where-clause

by default, the table creation use charset = latin1;
means it couldn't do a case-sensitive search

if you want to do a case-sensitive in a query, use BINARY

SELECT * FROM `char` WHERE `name` = BINARY('AnnieRuru');

however using BINARY might have performance hit if it is a big table
so its more recommend to convert your SQL table to collate with latin1_general_cs

let's say this is a sample table

CREATE TABLE `test` (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(23)
)ENGINE = InnoDB;

do an ALTER table syntax

ALTER TABLE `test` MODIFY COLUMN `name` VARCHAR(23) COLLATE latin1_general_cs;

or just put it into the table creation

CREATE TABLE `test` (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(23)
)ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE latin1_general_cs;

3. Choose a table type, MyISAM or InnoDB ?

https://stackoverflow.com/questions/20148/myisam-versus-innodb

Before MySQL 5.5,
MyISAM is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )
InnoDB is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )

After MySQL 5.6, (currently is 8.0)
just stick to InnoDB

there is only 1 reason MyISAM is better than InnoDB
- MyISAM use smaller disk usage than InnoDB

let's take a look at rAthena MyISAM to InnoDB converter
https://github.com/rathena/rathena/blob/master/sql-files/tools/convert_engine_innodb.sql
This converter is useful if you are using MySQL 5.6 or above

There are 4 tables that are commented out
the reason is simple, these 4 tables only read once and forgotten when server is live
since MyISAM is good at reading (SELECT) + smaller disk usage, its no use to convert these 4 tables into InnoDB

3a. How to index a table properly

http://mysql.rjweb.org/doc.php/index_cookbook_mysql
http://www.dbta.com/Columns/DBA-Corner/Top-10-Steps-to-Building-Useful-Database-Indexes-100498.aspx

a simple thumb of rule, anything that is SELECT .... WHERE `field` = .....
that `field` has to be index

let's take a look at this PVP Ladder script that use Kill/Death ratio

CREATE TABLE `pvpladder` (
`char_id` INT(11),
`name` VARCHAR(23),
`kills` INT(11),
`death` INT(11),
PRIMARY KEY (`char_id`),
KEY (`kills`, `death`)
) ENGINE = InnoDB;

prontera,155,186,6	script	PVP Ladder	1_F_MARIA,{
	.@nb = query_sql( "SELECT `name`, `kills`/(`death`+1) FROM `pvpladder` WHERE `kills` > 0 ORDER BY `kills`/(`death`+1) DESC LIMIT 10", .@name$, .@ratio$ );
	if ( !.@nb ) {
		mes "no entry";
		close;
	}
	mes "Current Ranking :";
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes "No."+(.@i +1)+" ["+ .@name$[.@i] +"] "+ .@ratio$[.@i] +" kill";
	close;
OnPCKillEvent:
	if ( killedrid == getcharid(3) ) { // killing self should only increase death count. EG: Grand-cross
		query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1";
		end;
	}
	query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 1,0 ) ON DUPLICATE KEY UPDATE `kills` = `kills` +1";
	attachrid killedrid;
	query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1";
	end;
}

This kind of query -> ORDER BY kills/death, needs to index them together like this

KEY (`kills`, `death`)

3b. Why you shouldn't use `char_reg_num` table

There are 2 reasons why you shouldn't even touch all these variable tables

Reason no.1 This table is sorely meant for server usage
Once these data is loaded, it is process internally, and only save character data according to this configuration

Spoiler

rAthena/conf/map_athena.conf said:

// Database autosave time
// All characters are saved on this time in seconds (example:
// autosave of 60 secs with 60 characters online -> one char is saved every
// second)
autosave_time: 300

// Min database save intervals (in ms)
// Prevent saving characters faster than at this rate (prevents char-server
// save-load getting too high as character-count increases)
minsave_time: 100

// Apart from the autosave_time, players will also get saved when involved
// in the following (add as needed):
// 1: after every successful trade
// 2: after opening vending/every vending transaction
// 4: after closing storage/guild storage.
// 8: After hatching/returning to egg a pet.
// 16: After successfully sending a mail with attachment
// 32: After successfully submitting an item for auction
// 64: After successfully get/delete/complete a quest
// 128: After every bank transaction (deposit/withdraw)
// NOTE: These settings decrease the chance of dupes/lost items when there's a
// server crash at the expense of increasing the map/char server lag. If your
// server rarely crashes, but experiences interserver lag, you may want to set
// these off.
save_settings: 255

Reason no.2 The `value` field is not index !

Hunting Mission Line 146 said:

query_sql("SELECT char_id AS id, (SELECT `name` FROM `char` WHERE char_id = id),`value` FROM `char_reg_num` WHERE `key` = 'Mission_Total' ORDER BY `value` DESC LIMIT 5",.@id,.@name$,.@val);

This line has ORDER BY `value`, try recheck our main.sql file

CREATE TABLE IF NOT EXISTS `char_reg_num` (
  `char_id` int(11) unsigned NOT NULL default '0',
  `key` varchar(32) binary NOT NULL default '',
  `index` int(11) unsigned NOT NULL default '0',
  `value` int(11) NOT NULL default '0',
  PRIMARY KEY (`char_id`,`key`,`index`),
  KEY `char_id` (`char_id`)
) ENGINE=MyISAM;

SQL will search through every single line in the `value` field if that column isn't index

Of course you can ... do ALTER table to add KEY to the `value` field
but this table has already optimized in that way for server usage
the more field you index into the table, the more disk usage space it use

Conclusion : If you want to make a custom script, then make a custom table. Leave these table alone !

4. AUTO_INCREMENT

CREATE TABLE `support_ticket` (
`id` INT(11) AUTO_INCREMENT,
`title` VARCHAR(70),
`message` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE = InnoDB;

In this kind of query that has AUTO_INCREMENT, many people do ....

	$support_ticket_id++;
	query_sql "INSERT INTO `support_ticket` VALUES ( "+ $support_ticket_id +", '"+ escape_sql(.@title$) ...

can be optimize .... using NULL

	query_sql "INSERT INTO `support_ticket` VALUES ( NULL, '"+ escape_sql(.@title$) ...

can retrieve the last row with

	query_sql "SELECT MAX(`id`) FROM `support_ticket`", .@id;
//	----- OR -----
	query_sql "SELECT LAST_INSERT_ID()", .@id;

Question : This question was asked on eathena forum board

One of my friend touched my custom table and the AUTO_INCREMENT has jump off the value

|   1   | <data set 1>
|   2   | <data set 2>
|   3   | <data set 3>
| 25854 | <data set 4>
| 25855 | <data set 5>
| 25856 | <data set 6>

I want to make the value return back to normal as shown

|   1   | <data set 1>
|   2   | <data set 2>
|   3   | <data set 3>
|   4   | <data set 4>
|   5   | <data set 5>
|   6   | <data set 6>

How to do this WITHOUT losing any of the current data ?

Answer: The trick is ... just drop that column and rebuild it

ALTER TABLE `inventory` DROP COLUMN `id`;
ALTER TABLE `inventory` ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

convert the table into MyISAM will process the query much faster

5. How to do IF-ELSE in SQL query ?

Question : I have a PVP ladder script that runs on Points system. Each kill plus 1 point and each death minus 1 point.
The problem is, this query will make the points go into negative value if the player is being kill repeatedly

query_sql "UPDATE `pvp_points` SET `points` = `points` - 1 WHERE `char_id` = "+ getcharid(0);

How do I make the points stop at 0 if the player is already at 0 points ?

Answer :

query_sql "UPDATE `pvp_points` SET `points` = IF(`points` = 0, 0, `points` - 1) WHERE `char_id` = "+ getcharid(0);

query_sql "UPDATE `pvp_points` SET `points` = (CASE WHEN `points` = 0 THEN 0 ELSE `points` - 1 END) WHERE `char_id` = "+ getcharid(0);

Explanations:

similar to rAthena script language,

	if (<condition>)
		<execute true condition>;
	else
		<execute false condition>;

in SQL language

	IF(<condition>, <execute true condition>, <execute false condition>)

CASE WHEN <condition>
	THEN <execute true condition>
	ELSE <execute false condition>
END

Reference : https://www.w3schools.com/sql/func_mysql_if.asp

https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select

5a. How to update multiple rows on different conditions in a single query

This query will update multiple rows on different condition

UPDATE `pvpladder` SET `points` = 
CASE
	WHEN `char_id` = 150000 THEN `points` +1
	WHEN `char_id` = 150001 THEN `points` -1
END
WHERE `char_id` IN (150000,150001);

Reference : https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query

6. How to show the current rank of the player

Spoiler


CREATE TABLE `pvpladder` (
`char_id` INT(11) PRIMARY KEY,
`name` VARCHAR(23),
`kills` INT(11),
KEY (`kills` DESC)
) ENGINE = InnoDB;


| char_id | name      | kills |
|  150000 | Alice     |   19  |
|  150001 | Brittany  |    8  |
|  150002 | Chaterine |    5  |
|  150003 | Dorothy   |    4  |
|  150004 | Emily     |   11  |
|  150005 | Fiona     |    7  |
|  150006 | Gamila    |    3  |
|  150007 | Helena    |    2  |
|  150008 | Irene     |   11  |
|  150009 | Joyce     |    1  |

Question : This is the part of the script, output as below

.@nb = query_sql("SELECT `name`, `kills` FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills);
for ( .@i = 0; .@i < .@nb; .@i++ )
	mes "No."+(.@i+1)+" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills";

No.1 [Alice] ~ 19 kills
No.2 [Emily] ~ 11 kills
No.3 [Irene] ~ 11 kills
No.4 [Brittany] ~ 8 kills
No.5 [Fiona] ~ 7 kills

2nd place and 3rd place has the same amount of kills, how do I make both of them display as 2nd place like this ?

No.1 [Alice] ~ 19 kills
No.2 [Emily] ~ 11 kills
No.2 [Irene] ~ 11 kills
No.4 [Brittany] ~ 8 kills
No.5 [Fiona] ~ 7 kills

Answer :

Method no.1: Convert the table into InnoDB will return the result faster. Allow to use OFFSET

.@nb = query_sql("SELECT `name`, `kills`, FIND_IN_SET(`kills`, (SELECT GROUP_CONCAT(`kills` ORDER BY `kills` DESC) FROM `pvpladder`)) FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills, .@rank);
for ( .@i = 0; .@i < .@nb; ++.@i )
	mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills";

Method no.2: This method return result faster than method 1 in huge table. Not allow to use OFFSET

.@query$  = "SELECT `name`, IF(@d=t.`kills`, @r, @r:=@i), @d:=t.`kills`, @i:=@i+1 ";
.@query$ += "FROM `pvpladder` t, (SELECT @d:=0, @r:=0, @i:=1)q ";
.@query$ += "ORDER BY `kills` DESC LIMIT 5";
.@nb = query_sql(.@query$, .@name$, .@rank, .@kills, .@dummy);
for ( .@i = 0; .@i < .@nb; ++.@i )
	mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills";

.

Question : How do I show the current ranking of the player ?

	mes "Your kills -> "+ .@kills;
	mes "Your rank -> "+ .@rank;

Answer :

query_sql "SELECT `kills`, 1+(SELECT COUNT(1) FROM `pvpladder` t1 WHERE t1.`kills` > t2.`kills`) FROM `pvpladder` t2 WHERE `char_id` = "+ getcharid(0), .@kills, .@rank;

Remember to index the `kills` field

Reference : https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table

.

Spoiler

MySQL doesn't support RANK() unlike other SQL program
so let's test with all the methods around the internet


prontera,155,186,6	script	PVP Ladder	1_F_MARIA,{
	.@start = gettimetick(0);
	freeloop true;
	for ( .@j = 0; .@j < 100; ++.@j ) {
		for ( .@i = 0; .@i < 1000; ++.@i )
			.@values$[.@i] = "( "+( .@i + 2000000 + .@j * 1000 ) +", '"+ .@i +"', "+ rand(20000) +" )";
		query_sql "replace into testest values "+ implode( .@values$, ", " );
	}
	announce "done -> "+( gettimetick(0) - .@start )+" ms", 0;
	end;
}

build a large table, with 100,000 rows, and experiment with all of these

Spoiler



# --------------------------------------------------------------
# Create a table, test with both InnoDB and MyISAM engine

create table testest (
account_id int(11) primary key,
name varchar(23),
kills int(11),
key (`kills` desc)
) engine = innodb;

create table testest (
account_id int(11) primary key,
name varchar(23),
kills int(11)
) engine = myisam;

explain testest;
select * from testest order by kills desc limit 10;

truncate table testest;
drop table testest;
alter table testest engine = innodb;
alter table testest engine = myisam;
alter table testest add index (kills desc);
drop index kills on testest;

# -----------------------------------------------------------------
# Example script from www.1keydata.com
# this method create additional rows by power of 2
# if I have 100,000 rows, will create 10,000,000,000 rows then only start counting <-- bad method

select t1.*, count(1)
from testest t1, testest t2
where (t1.kills < t2.kills or t1.account_id = t2.account_id)
group by t1.account_id
order by t1.kills desc limit 20;

select t1.*, count(1)
from testest t1, testest t2
where (t1.kills < t2.kills or t1.account_id = t2.account_id) and t1.account_id = 2000000
group by t1.account_id;

# ------------------------------------------------------
# Best answer from stackexchange
# it has potential problem, the GROUP_CONCAT has string limit at 1024
# when the resultset return over this string limit, FIND_IN_SET always return 0
# although not useful for return current rank, this query is quite fast, probably good for return top 10 results

select *,
find_in_set( kills, ( select group_concat( kills order by kills desc ) from testest ) ) as rank
from testest order by kills desc limit 10;

select *,
find_in_set( kills, ( select group_concat( kills order by kills desc ) from testest ) ) as rank
from testest where account_id = 2000844;

SELECT length( GROUP_CONCAT(kills) ) FROM testest;

# --------------------------------------------------------
# another answer from stackexchange
# use count on a temporary table, very slow when list out multiple rows
# but with InnoDB engine and `kills` field index, return result instantly when only 1 row selected

select *, 1+(SELECT count(1) from testest a WHERE a.kills > b.kills) from testest b
  order by kills desc limit 10;

select *, 1+(SELECT count(1) from testest a WHERE a.kills > b.kills) from testest b
  where account_id = 2000844;

# -------------------------------------------------------
# a modify version from another answer in stackexchange
# ORDER by `kills` always return instantly, make this query EXTREMELY useful for display TOP10

SELECT account_id, `name`,
IF (@score=s.kills, @rank:=@rank, @rank:=@rank+1) rank, @score:=s.kills score
FROM testest s,
(SELECT @score:=0, @rank:=0) r
ORDER BY kills DESC limit 10;

set @i:=221, @score:=3230, @rank:=23230;
SELECT account_id, `name`,
IF (@score=t.kills, @rank:=@rank, @rank:=@i) rank, @score:=t.kills, @i:=@i+1
FROM testest t
ORDER BY kills DESC limit 100;

SELECT account_id, `name`,
IF (@score=t.kills, @rank:=@rank, @rank:=@i) rank, @score:=t.kills, @i:=@i+1
FROM testest t,
(SELECT @score:=0, @rank:=0, @i:=1)q
ORDER BY kills DESC limit 100;

SELECT `name`, IF(@d=t.kills, @r, @r:=@i), @d:=t.kills, @i:=@i+1
FROM testest t, (SELECT @d:=0, @r:=0, @i:=1)q
ORDER BY kills DESC limit 100;
# Note the @i will start at 1, but it left the @i at 101 after this query is run
select @i; # return 101

with my MYSQL 5.7,
1. InnoDB return result faster when using ORDER BY
2. INDEX the `kills` field with DESC doesn't seems to make the result return faster ...
3. and if I drop the `kills` field index, with InnoDB table type return result 4x slower. MyISAM perform even worst
4. the example script provided by www.1keydata.com totally useless, I have to cancel execution after waiting for 60 seconds

Both Methods I've shown in the answer actually do the same things, but there are difference

Answer no.1 Method 1 use FIND_IN_SET + GROUP_CONCAT
this method return the result quite fast with InnoDB, but the GROUP_CONCAT can only show the string length at 1024
in other words, when exceeding this length, all other rank will be shown as 0, because the FIND_IN_SET cannot find the data available

Answer no.1 Method 2 use SQL variables order with number of rows
this method return result fastest even with MyISAM
however due to the variable insert with number of rows, doesn't allow OFFSET

Answer no.2 calculate the rank by comparing with a temporary table
this method runs slower, but with InnoDB engine AND `kills` field index, the result return instantly when only display 1 row of data

7. INSERT INTO ... SELECT ...

Question : This question was asked on eathena forum . Note: at the time the old mail system was unstable and not many server use it
I want to reward my players for supporting my server.
I want to give every player who are still actively login in this year (2010) an item ID 22574 in their storage
How do I achieve this ?

Answer : Run this SQL command when your server is offline

INSERT INTO `storage` (`account_id`, `nameid`, `amount`, `identify`)
SELECT `account_id`, '22574', '1', '1'
FROM `login`
WHERE DATE(`lastlogin`) >= '2010-01-01' && `account_id` !=1;

The reply from the topic starter gives a feed back, including this

		Inserted rows: 7738
		Inserted row id: 8244859 (Query took 13.6253 sec)

8. Table JOIN vs AS

CREATE TABLE `pvpladder` (
`char_id` int(11) PRIMARY KEY,
`points` int(11)
) ENGINE = InnoDB;

This table is missing the `name` field. So have to retrieve the `name` from the `char` table.

A simple way is using table JOIN

SELECT `char`.`char_id`, `char`.`name` , `pvpladder`.`points`
FROM `pvpladder` LEFT JOIN `char` ON `pvpladder`.`char_id` = `char`.`char_id`
ORDER BY `pvpladder`.`points` DESC LIMIT 10;

However, there is an uncommon method, the same thing can be done using Aliases

SELECT `char_id` AS `CID`, (SELECT `name` FROM `char` WHERE `char_id` = `CID`), `points`
FROM `pvpladder` ORDER BY `points` DESC LIMIT 10;

In this example, both tables `char` and `pvpladder` have the `char_id` field index as PRIMARY KEY
and thus both examples return the result in same amount of time

However, there is a key difference on the optimization speed if the one of the table is not index properly
The below example, `item_id`.`id` field is index as PRIMARY KEY, but `mob_db`.`DropCardid` is not index

SELECT `mob_db`.`ID`, `mob_db`.`kname`, `item_db`.`id`, `item_db`.`name_japanese`
FROM `mob_db` LEFT JOIN `item_db` ON `mob_db`.`DropCardid` = `item_db`.`id`
WHERE `mob_db`.`DropCardid` > 0;

SELECT `ID`, `kname`, `DropCardid` AS `ITEM_ID`, (SELECT `name_japanese` FROM `item_db` WHERE `id` = `ITEM_ID`)
FROM `mob_db` WHERE `mob_db`.`DropCardid` > 0;

The 2nd query that uses Aliases will return result faster than table JOIN in this case

Conclusion : If use table JOIN, you have to keep in mind that the joined column has to be index
but if use AS, there is no need to consider this issue

9. What is the maximum string limit for *query_sql

Since query_sql sending the query as a string, we can actually use string manipulation script commands such as *sprintf and *implode

Example : *sprintf

Spoiler


prontera,155,186,5	script	sprintf_query_sql	1_F_MARIA,{
	.@table$ = "mob_db";
	.@column$ = "kname";
	.@field$ = "ID";
	.@value = "1002";
	query_sql sprintf("SELECT `%s` FROM `%s` WHERE `%s` = %d", .@column$, .@table$, .@field$, .@value), .@name$;
	dispbottom .@name$; // return Poring
	end;
}

Example : *implode

Spoiler


prontera,158,186,5	script	implode_query_sql	1_F_MARIA,{
	.@nb = query_sql( "SELECT * FROM `mvp_table`", .@mvpid, .@points );
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes "ID "+ .@mvpid[.@i] +" -> "+ .@points[.@i];
	close;
OnInit:
	setarray .@mvpid[0], // taken from my MVP Ladder script
		1086,//	Golden Thief Bug    64
		1115,//	Eddga               65
		1150,//	Moonlight Flower    67
		1159,//	Phreeoni            69
		1112,//	Drake               70
		1583,//	Tao Gunka           70
		1492,//	Incantation Samurai 71
		1046,//	Doppelgangger       72
		1252,//	Garm                73
		1418,//	Evil Snake Lord     73
		1059,//	Mistress            74
		1190,//	Orc Lord            74
		1087,//	Orc Hero            77
		1251,//	Knight of Windstorm 77
		1038,//	Osiris              78
		1658,//	Ygnizem             79
		1272,//	Dark Lord           80
		1871,//	Fallen Bishop       80
		1039,//	Baphomet            81
		1147,//	Maya                81
		1785,//	Atroce              82
		1389,//	Dracula             85
		1630,//	Bacsojin            85
		1885,//	Gorynych            85
		1623,//	RSX 0806            86
		1511,//	Amon Ra             88
		1688,//	Lady Tanee          89
		1768,//	Gloom Under Night   89
		1719,//	Datale              90
		1734,//	Kiel D-01           90
		1157,//	Pharaoh             93
		1373,//	Lord of Death       94
		1312,//	Turtle General      97
		1779,//	Ktullanux           98
		1874,//	Beelzebub           98
		1646,// Bio3 placeholder    99
		1708,//	Thanatos            99
		1751,//	Valkyrie Randgris   99
		1832;//	Ifrit               99
	.@size = getarraysize( .@mvpid );
	query_sql "CREATE TABLE IF NOT EXISTS `mvp_table` (`mvp_id` SMALLINT(6) PRIMARY KEY, `points` INT(11)) ENGINE = MyISAM";
	for ( .@i = 0; .@i < .@size; ++.@i )
		.@values$[.@i] = "( "+ .@mvpid[.@i] +", 50 )";
	query_sql "INSERT IGNORE INTO `mvp_table` VALUES "+ implode( .@values$, ", " );
	end;
}

So, someone might ask, what is the string limit for query_sql until the map-server show error ?

Spoiler


create table testest (
account_id int(11) primary key,
name varchar(23),
kills int(11)
) engine = myisam;


prontera,155,186,6	script	test_until_error	1_F_MARIA,{
	freeloop true;
	.@start = gettimetick(0);
	for ( .@i = 0; .@i < 140000; ++.@i )
		.@values$[.@i] = "( "+( .@i + 2000000 ) +", '"+ .@i +"', "+ rand(20000) +" )";
	announce "loop done -> "+( gettimetick(0) - .@start )+" ms", 0;
	sleep 1;
	.@start = gettimetick(0);
	query_sql "replace into testest values "+ implode( .@values$, ", " );
	announce "query done -> "+( gettimetick(0) - .@start )+" ms", 0;
	end;
}

The answer is very surprising ... I just tested with above script, and it still works perfectly fine !
loop -> 64.210 seconds
query -> 3.229 seconds

it means, ahh .... or maybe ...

The answer might be ...

[SQL]: DB error - MySQL server has gone away
[Debug]: showmsg: dynamic buffer used, increase the static buffer size to 4306185 or more.

until MySQL stop responding XD

Spoiler


[Debug]: showmsg: dynamic buffer used, increase the static buffer size to 2061 or more.


#define SBUF_SIZE 2054 // never put less that what's required for the debug message

This src\common\showmsg.c is just a display error message ...
this means, if your query is bug, it only display the string length until this limit

9a. UNION

Just now the *implode example, it just shows the `mvp_id`, but if we want to display the information like this,

+--------+------------------+------------+-----------------------+
| MVP_ID |     MVP_NAME     | DropCardid |     MVP_CARD_NAME     |
+--------+------------------+------------+-----------------------+
|  1086  | Golden Thief Bug |    4128    | Golden Thiefbug Card  |
|  1115  |      Eddga       |    4123    |      Eddga Card       |
|  1150  | Moonlight Flower |    4131    | Moonlight Flower Card |

Spoiler


prontera,158,186,5	script	test_mvp_card_name	1_F_MARIA,{
	.@nb = query_sql( "SELECT `mvp_name`, `mvp_card_name` FROM `mvp_table`", .@name$, .@card_name$ );
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes .@name$[.@i] +" -> "+ .@card_name$[.@i];
	close;
OnInit:
	setarray .@mvpid[0], // taken from my MVP Ladder script
		1086,//	Golden Thief Bug    64
		1115,//	Eddga               65
		1150,//	Moonlight Flower    67
		1159,//	Phreeoni            69
		1112,//	Drake               70
		1583,//	Tao Gunka           70
		1492,//	Incantation Samurai 71
		1046,//	Doppelgangger       72
		1252,//	Garm                73
		1418,//	Evil Snake Lord     73
		1059,//	Mistress            74
		1190,//	Orc Lord            74
		1087,//	Orc Hero            77
		1251,//	Knight of Windstorm 77
		1038,//	Osiris              78
		1658,//	Ygnizem             79
		1272,//	Dark Lord           80
		1871,//	Fallen Bishop       80
		1039,//	Baphomet            81
		1147,//	Maya                81
		1785,//	Atroce              82
		1389,//	Dracula             85
		1630,//	Bacsojin            85
		1885,//	Gorynych            85
		1623,//	RSX 0806            86
		1511,//	Amon Ra             88
		1688,//	Lady Tanee          89
		1768,//	Gloom Under Night   89
		1719,//	Datale              90
		1734,//	Kiel D-01           90
		1157,//	Pharaoh             93
		1373,//	Lord of Death       94
		1312,//	Turtle General      97
		1779,//	Ktullanux           98
		1874,//	Beelzebub           98
		1646,// Bio3 placeholder    99
		1708,//	Thanatos            99
		1751,//	Valkyrie Randgris   99
		1832;//	Ifrit               99
	.@size = getarraysize( .@mvpid );
//	query_sql "DROP TABLE `mvp_table`";
	query_sql "CREATE TABLE IF NOT EXISTS `mvp_table` (`mvp_id` SMALLINT(6) PRIMARY KEY, `mvp_name` VARCHAR(30), `mvp_card` SMALLINT(6), `mvp_card_name` VARCHAR(30) ) ENGINE = MyISAM";
	for ( .@i = 0; .@i < .@size; ++.@i )
		.@values$[.@i] = "( "+ .@mvpid[.@i] +", (SELECT `kname` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `DropCardid` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `item_db`.`name_japanese` FROM `item_db` LEFT JOIN `mob_db` ON `item_db`.`id` = `mob_db`.`DropCardid` WHERE `mob_db`.`ID` = "+ .@mvpid[.@i] +") )";
	query_sql "INSERT IGNORE INTO `mvp_table` VALUES "+ implode( .@values$, ", " );
	end;
}

	for ( .@i = 0; .@i < .@size; ++.@i )
		.@values$[.@i] = "( "+ .@mvpid[.@i] +", (SELECT `kname` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `DropCardid` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `item_db`.`name_japanese` FROM `item_db` LEFT JOIN `mob_db` ON `item_db`.`id` = `mob_db`.`DropCardid` WHERE `mob_db`.`ID` = "+ .@mvpid[.@i] +") )";
	query_sql "INSERT IGNORE INTO `mvp_table` VALUES "+ implode( .@values$, ", " );

This part ... can be optimize with UNION

	for ( .@i = 0; .@i < .@size; ++.@i )
		.@values$[.@i] = "SELECT "+ .@mvpid[.@i] +", `kname`, `DropCardid` AS `MVP_CARD`, (SELECT `name_japanese` FROM `item_db` WHERE `id` = `MVP_CARD`) FROM `mob_db` WHERE ID = "+ .@mvpid[.@i];
	query_sql "INSERT IGNORE INTO `mvp_table` "+ implode( .@values$, " UNION " );

DONE ---- FINALLY !!

Yes, I knew the chapter 5 and beyond is very tough to understand ... it also took me longer time to write and test all these advance SQL techniques too

Anyway, this topic is now open to Suggestions, Ideas, Improvements, and Questions ~
You may also post up your tricks if you want to share with us

May 1, 2018

    input .@PlayerSpecInputName$;
    .@ownName$ = strcharinfo(0);
    if (compare(.@PlayerSpecInputName$, .@ownName$) && compare(.@ownName$, .@PlayerSpecInputName$)) {
        mes "You cant use your own name";
        close;
    }

April 30, 2018

bindatcmd "itemshower",strnpcinfo(3)+"::OnShower", 99, 100;

doc

Quote

*bindatcmd "<command>","<NPC object name>::<event label>"{,<atcommand level>,<charcommand level>};

This command will bind a NPC event label to an atcommand. Upon execution of the
atcommand, the user will invoke the NPC event label. Each atcommand is only allowed
one binding. If you rebind, it will override the original binding.
Note: The default level for atcommand is 0 while the default level for charcommand is 100.

April 30, 2018

@Secrets I'm just comparing hercules ones with rathena ones

45 minutes ago, Secrets said:

The instance isn’t supposed to work if a required map isn’t added in the database anyways.
Map name retrieval from outside of the instance isn’t something we need...

hercules instancing can duplicate any maps ... so the checks not just on the database, but also on duplicated ones
see instance_attachmap / instance_detachmap

in other words, I could make this party go into map A, and other party instance into map B (as long as the map cell are similar)
remember hercules don't have instance_db.txt file to begin with

and for daniel ... I'm currently solving his problem in discord, I think for his problem, its better to teach 1 on 1

Sign In

Profiles

Forums

Downloads

Jobs Available

Server Database

Third-Party Services

Top Guides

Store

Crowdfunding

Posts posted by AnnieRuru