AnnieRuru

Script Developers
  • Content count

    1161
  • Joined

  • Last visited

  • Days Won

    89

AnnieRuru last won the day on April 20

AnnieRuru had the most liked content!

About AnnieRuru

  • Rank
    ~~Cute~Cute~Scripter~~

Profile Information

  • Gender
    Female
  • Location:
    your next door ~
  • Interests
    event scripts ~ battleground scripts ~
    making battleground scripts is my hobby ~
  • Emulator:
    Hercules
  • Github
    AnnieRuru

Recent Profile Visitors

8815 profile views
  1. 6. How to show the current rank of the player Question : This is the part of the script, output as below [email protected] = query_sql("SELECT `name`, `kills` FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", [email protected]$, [email protected]); for ( [email protected] = 0; [email protected] < [email protected]; [email protected]++ ) mes "No."+([email protected]+1)+" ["+ [email protected]$[[email protected]] +"] ~ "+ [email protected][[email protected]] +" 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 [email protected] = 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", [email protected]$, [email protected], [email protected]); for ( [email protected] = 0; [email protected] < [email protected]; [email protected] ) mes "No."+ [email protected][[email protected]] +" ["+ [email protected]$[[email protected]] +"] ~ "+ [email protected][[email protected]] +" kills"; Method no.2: This method return result faster than method 1 in huge table. Not allow to use OFFSET [email protected]$ = "SELECT `name`, IF(@d=t.`kills`, @r, @r:[email protected]), @d:=t.`kills`, @i:[email protected]+1 "; [email protected]$ += "FROM `pvpladder` t, (SELECT @d:=0, @r:=0, @i:=1)q "; [email protected]$ += "ORDER BY `kills` DESC LIMIT 5"; [email protected] = query_sql([email protected]$, [email protected]$, [email protected], [email protected], [email protected]); for ( [email protected] = 0; [email protected] < [email protected]; [email protected] ) mes "No."+ [email protected][[email protected]] +" ["+ [email protected]$[[email protected]] +"] ~ "+ [email protected][[email protected]] +" kills"; . . Question : How do I show the current ranking of the player ? mes "Your kills -> "+ [email protected]; mes "Your rank -> "+ [email protected]; 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), [email protected], [email protected]; Remember to index the `kills` field Reference : https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table .
  2. 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 hercules 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
  3. 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([email protected]$) ... can be optimize .... using NULL query_sql "INSERT INTO `support_ticket` VALUES ( NULL, '"+ escape_sql([email protected]$) ... 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
  4. 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 5.7) 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 our MyISAM to InnoDB converter https://github.com/HerculesWS/Hercules/blob/stable/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,{ [email protected] = query_sql( "SELECT `name`, `kills`/(`death`+1) FROM `pvpladder` WHERE `kills` > 0 ORDER BY `kills`/(`death`+1) DESC LIMIT 10", [email protected]$, [email protected]$ ); if ( [email protected] ) { mes "no entry"; close; } mes "Current Ranking :"; for ( [email protected] = 0; [email protected] < [email protected]; [email protected] ) mes "No."+([email protected] +1)+" ["+ [email protected]$[[email protected]] +"] "+ [email protected]$[[email protected]] +" 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_db` table blame Euphy for spreading this technique 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 Reason no.2 The `value` field is not index ! This line has ORDER BY `value`, try recheck our main.sql file CREATE TABLE IF NOT EXISTS `acc_reg_num_db` ( `account_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 (`account_id`,`key`,`index`), KEY `account_id` (`account_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 !
  5. err ... maybe yes, maybe no which bg ? I write my own go make some ideas in script request section and I'll write it out
  6. I need to note down all these stuffs before I forget, will update when have time 1 when to use escape_sql script command 2.how to build case-sensitive table 3. how to index a table properly - why you shouldn't use `char_reg_num_db` table #. INSERT INTO ... ON DUPLICATE KEY UPDATE <- I think no need to add this ... with enough example script pple will follow my style 4. how to make the table AUTO-INCREMENT nicely - involve drop the column and rebuilt it ------- next level ------ 5. how to do IF-ELSE in SQL query 6. the SQL command to select current ranking not in the top 10 7. AS vs JOIN https://rathena.org/board/topic/91501-need-help-with-this-query_sql/ 8. INSERT INTO ... SELECT a. how to give players items through mail b. about its performance killingc. 9. UNION and UNION ALL - sending a long string .... brainstorming ...
  7. 2.4b http://upaste.me/0c8149627b3ddc0c6 - fix a typo in mission limitation can do daily or only once, actually allows to do twice instead of once - fix a typo in mission time limit, if your mission no.2/3/4 has expired, the script always says slot no.1 @Helena 2.4c http://upaste.me/7c9c496344829d36f - fix this f*cking deny usage once again ...this time when submit a mission, the getarraysize calculation is made AFTER declare the datetime format - if the whole board is set to EVERYDAY, remove the player side deny usage variable completely on the next day
  8. @caspa try this http://upaste.me/6b6a49616cc4ceec9 kinda know your request because you also post almost identical request on the rathena forum
  9. these steps are the same as rAthena, conf\grf-files.txt //----------------------------------------- // GRF Files // Add as many as needed. //----------------------------------------- grf: D:\Ragnarok\client\rdata.grf grf: D:\Ragnarok\client\data.grf //----------------------------------------- // Data Directory // Use the base folder, not the data\ path. //----------------------------------------- data_dir: D:\Ragnarok\client\ conf\map\maps.conf "arathi", db\map_index.txt arathi 1250 actually should do these steps before the above post I assume many people know about this because .... after do these 3 steps, in rathena just have to click mapcache.bat https://rathena.org/board/topic/108109-requesting-guide-on-how-to-create-a-mapcache/ EDIT: for below - wow, how much spoon feeding is weemapcache system ...
  10. GvG

    got a PM from rAthena member ask me to fix this yeah both rathena patch and hercules plugin ... all broken so .... update to 1.2 Script Plugin for Hercules Patch for rAthena
  11. ok this need some testing ... prontera,155,190,5 script test 1_F_MARIA,{ mes "the monster you just killed is "; mes getmonsterinfo( killedrid, MOB_NAME ); select "summon"; monster "this", -1,-1, "--ja--", 1002,1, strnpcinfo(0)+"::Onaaa"; mes "monster summoned"; close; Onaaa: end; } rathena -> yup it shows Poring hercules -> you are right, it shows null you are right, rathena fixed this https://github.com/rathena/rathena/pull/1286
  12. http://upaste.me/542d49579ec44be6d quick sort = 390~437 miliseconds (str & int) comb sort = 312~344 miliseconds (str & int) counting sort = 47~63 miliseconds (integer only) comb sort still outperform quick sort or merge sort in hercules script engine the reason is just like Haru said, *athena script engine parse function (for/while/goto) very slow, the more function it runs, the more slower it gets and there is no need to use *strcmp script command poring_w01,105,105,5 script test string 1_F_MARIA,{ dispbottom ( "test" == "test" ); // return 1 dispbottom ( "123" > "321" ); // return 0 dispbottom ( "asdf" < "qwer" ); // return 1 end; }
  13. 1st of all, sorry I only know windows environment -- windows sux !! 2nd, yeah I agree too, this took me 2 days to figure out how to do this reference topic Prelude make sure you already know how to install a plugin http://herc.ws/wiki/Building_HPM_Plugin_for_MSVC yup ~ use the new method ~ ( maybe I should've remove the old method already ... ) and add "mapcache" to your "hercules\conf\plugins.conf" Run command prompt 1. [Windows Key] + [R] -> type cmd https://drive.google.com/file/d/19Ni-NUAZusaCgEX6xnXxasZDLHtkW-N-/view?usp=sharing 2. point to your Hercules emulator folder, and type mapserver.exe --help https://drive.google.com/file/d/1EpB4jxaB3VAO0XzB9gJq7t6fmtBb4RZ5/view?usp=sharing 3. type mapserver.exe --map <YourMap> https://drive.google.com/file/d/1Q68HSNaxvGEwsm6UeHEcgL2BY-JpKNgV/view?usp=sharing now that you know the commands ... lets do it easier way Windows Execution File -- Create Shortcut 1. point to your map-server.exe, and create shortcut https://drive.google.com/file/d/15szzAa8Fuef9UQUjB3rsygNafoLtr6Tm/view?usp=sharing 2. right-click, Properties, and edit the Target Location/Target (this example is my hercules directory) D:\Ragnarok\Hercules\map-server.exe --map <YourMap> https://drive.google.com/file/d/1hwqPrJE4M53QkoAeiuA4pFCF1A8ZHPpz/view?usp=sharing whenever I want to add new maps, I just need to edit the Target field, and double click the program ~ fast and easy ~ Fun Facts !! do you know you can use GM command in map-server.bat ? https://drive.google.com/file/d/15gOf4vgl9HWc58Q8Jdn9Vbbza5lY5ek6/view?usp=sharing
  14. yup ~ :unhides: I'm was digesting all the changes in last 2 weeks but I think I'm ready to make appearance again ~
  15. omg .. I've used this quite a lot in my battleground scripts to shuffle the array of the participants, to prevent players predicting they would join in which team :stumble: never encounter a situation require this feature yet ~ reminds me of my old mission board script ... yeah, if I have known about these function, that script would have been much cleaner, rather than having for-loop over and over in that script and I have fun playing with your scripts ~ maybe we can have another algorithm script discussion again some other time ~