Item DB file structure overhaul

[Haru 290]

Item DB file structure overhaul
 
Hello~! Uguu~?!
We noticed that the Item Database file format, unchanged for years, is less than optimal (read: terrible)

• The file is really hard to read (is it the fifteenth or the sixteenth zero? No wait, that line has an extra comma!!)
• Whenever you merge an update, if you had a customized entry, you're sure to encounter large conflicts that won't be trivial to solve

How do we fix it?
We're switching to a brand new, modern, file format, making use of the libconfig library we're already using for other configuration files.

• It uses libconfig. This means the parser is more solid and, perhaps not faster, but surely easier to maintain, with simpler code. And the file format is something you're already used to, since it's the same as many other configuration files we use!
• Empty fields and the long sequences of those hard to count commas are gone! You just specify the fields you need, and the others can be completely skipped.
• The item_db2 entries can be left incomplete and set to inherit the original item_db entry. If you have a custom script for your Knife[3], you can just write the script in your item_db, and let it read the other values from the item_db, so that if we update them, you get the update automatically
• Item scripts can be split into several lines, so they can made easier to read, especially the long ones.
• We can finally add more fields (to support new features) to the file at any time, easily and without having to edit all the lines (or force you to edit all the lines of your custom item_db2)!
• Pre-Renewal and Renewal Item databases now use the same format. This also means that you can make use of the min/max level feature in both renewal and pre-renewal (of course, pre-renewal servers will ignore the Matk field, if you specify it, since it's meaningless there)

What does it look like?
 
Each entry follows this structure:

{
    // =================== Mandatory fields ===============================
    Id: ID                        (int)
    AegisName: "Aegis_Name"       (string, optional if Inherit: true)
    Name: "Item Name"             (string, optional if Inherit: true)
    // =================== Optional fields ================================
    Type: Item Type               (int, defaults to 3 = etc item)
    Buy: Buy Price                (int, defaults to Sell * 2)
    Sell: Sell Price              (int, defaults to Buy / 2)
    Weight: Item Weight           (int, defaults to 0)
    Atk: Attack                   (int, defaults to 0)
    Matk: Magical Attack          (int, defaults to 0, ignored in pre-re)
    Def: Defense                  (int, defaults to 0)
    Range: Attack Range           (int, defaults to 0)
    Slots: Slots                  (int, defaults to 0)
    Job: Job mask                 (int, defaults to all jobs = 0xFFFFFFFF)
    Upper: Upper mask             (int, defaults to any = 0x3f)
    Gender: Gender                (int, defaults to both = 2)
    Loc: Equip location           (int, required value for equipment)
    WeaponLv: Weapon Level        (int, defaults to 0)
    EquipLv: Equip required level (int, defaults to 0)
    EquipLv: [min, max]           (alternative syntax with min / max level)
    Refine: Refineable            (boolean, defaults to true)
    View: View ID                 (int, defaults to 0)
    Script: <"
        Script
        (it can be multi-line)
    ">
    OnEquipScript: <" OnEquip Script (can also be multi-line) ">
    OnUnequipScript: <" OnUnequip Script (can also be multi-line) ">
    // =================== Optional fields (item_db2 only) ================
    Inherit: true/false           (boolean, if true, inherit the values
                                  that weren't specified, from item_db.conf,
                                  else override it and use default values)
},

Here's a Red Potion in the old format:

501,Red_Potion,Red Potion,0,50,,70,,,,,0xFFFFFFFF,7,2,,,,,,{ itemheal rand(45,65),0; },{},{}

And here's the same Red Potion in the new format:

{
    Id: 501
    AegisName: "Red_Potion"
    Name: "Red Potion"
    Type: 0
    Buy: 50
    Weight: 70
    Script: <" itemheal rand(45,65),0; ">
},

Not convinced yet it's easier to read? Okay, let's try a Poison Bottle:

678,Poison_Bottle,Poison Bottle,2,5000,,100,,,,,0xFFFFFFFF,7,2,,,,,,{ if(Class==Job_Assassin_Cross) { sc_start SC_DPOISON,60000,0; sc_start SC_ATTHASTE_INFINITY,60000,0; } else percentheal -100,-100; },{},{}

changes to:

{
    Id: 678
    AegisName: "Poison_Bottle"
    Name: "Poison Bottle"
    Type: 2
    Buy: 5000
    Weight: 100
    Script: <"
        if(Class==Job_Assassin_Cross) {
            sc_start SC_DPOISON,60000,0;
            sc_start SC_ATTHASTE_INFINITY,60000,0;
        }
        else percentheal -100,-100;
    ">
},

Better, isn't it!? Let's now try a Jellopy:

909,Jellopy,Jellopy,3,6,,10,,,,,,,,,,,,,,{},{},{}

Count the commas! Did I miss any? An extra comma you say? No, I don't want to count them, thanks...

{
    Id: 909
    AegisName: "Jellopy"
    Name: "Jellopy"
    Buy: 6
    Weight: 10
},

Not even a comma!
Now, help me read what this item does?

13307,Krieger_Huuma_Shuriken1,Glorious Shuriken,4,20,,0,55,,1,0,0x02000000,7,2,34,4,80,1,22,{ bonus2 bAddRace,RC_DemiHuman,95; bonus2 bIgnoreDefRate,RC_DemiHuman,20; bonus bMatkRate,15; autobonus "{ bonus2 bSkillAtk,NJ_HUUMA,100; bonus2 bSkillAtk,NJ_ISSEN,100; }",50,10000; bonus bUnbreakableWeapon,0; if(getrefine()>5) { bonus2 bAddRace,RC_DemiHuman,(getrefine()-3)*(getrefine()-3); bonus2 bIgnoreDefRate,RC_DemiHuman,5; } if(getrefine()>8) { bonus5 bAutoSpellOnSkill,NJ_ISSEN,AL_HEAL,10,1000,1; bonus4 bAutoSpellOnSkill,NJ_HUUMA,NPC_CRITICALWOUND,2,200; } },{},{}

{
    Id: 13307
    AegisName: "Krieger_Huuma_Shuriken1"
    Name: "Glorious Shuriken"
    Type: 4
    Buy: 20
    Atk: 55
    Range: 1
    Job: 0x02000000
    Loc: 34
    WeaponLv: 4
    EquipLv: 80
    View: 22
    Script: <"
        bonus2 bAddRace,RC_DemiHuman,95;
        bonus2 bIgnoreDefRate,RC_DemiHuman,20;
        bonus bMatkRate,15;
        autobonus "{ bonus2 bSkillAtk,NJ_HUUMA,100; bonus2 bSkillAtk,NJ_ISSEN,100; }",50,10000;
        bonus bUnbreakableWeapon,0;
        if(getrefine()>5) {
            bonus2 bAddRace,RC_DemiHuman,(getrefine()-3)*(getrefine()-3);
            bonus2 bIgnoreDefRate,RC_DemiHuman,5;
        }
        if(getrefine()>8) {
            bonus5 bAutoSpellOnSkill,NJ_ISSEN,AL_HEAL,10,1000,1;
            bonus4 bAutoSpellOnSkill,NJ_HUUMA,NPC_CRITICALWOUND,2,200;
        }
    ">
},

Which one did you find easier to read? Old or new format?
 
Want to see an example that specifies both min and max levels? Here you go:

{
    Id: 2819
    AegisName: "Swordman_Manual"
    Name: "Swordman Manual"
    Type: 5
    Buy: 0
    Weight: 100
    Job: 0x00000001
    Upper: 47
    Loc: 136
    EquipLv: [1, 12]
    Refine: false
    Script: <"
        bonus bMaxSP,100;
        skill SM_BASH,1;
        skill SM_PROVOKE,1;
        skill SM_MAGNUM,1;
    ">
},

Okay, one last example. Let's try to make an item_db2 entry for a Pupa Card that gives a bonus of 1000 HP rather than just 700.

{
    Id: 4003
    Inherit: true
    Script: <" bonus bMaxHP,1000; ">
},

Done. No need to rewrite the name, location, prices... those are already in the item_db!
 
But... I have several custom items, do I have to manually convert all of them...?
 
Of course you do! No, I'm kidding, don't hate me
 
It's true that you need to convert your item database to the new format, but we can do it for you!

• Go to http://haru.ws/hercules/itemdbconverter/ and paste (or upload) your item_db2.txt (or even your item_db.txt if you have custom entries there), press the Convert button, wait a few seconds and you're done! It's already converted for you. Easy, isn't it?
• Don't trust us? No, no, we don't need your custom items, you can sleep safe... But if you still don't want to paste anything on a website... well, we have provided the source code of the converter script! It's in the 'tools' folder of the Hercules repository. All you need is a Perl interpreter (and if you're running Linux or Mac OS, on either your server or your own computer, it's almost certain that you already have that). All you have to do is run it (example: perl tools/itemdbconverter.pl < db/item_db2.txt > db/item_db2.conf), and your item database will be converted in a split second!

What if I was using SQL item databases?
 
Well... Then you won't have to worry about the txt databases, unless you were creating the items in txt, then converting them to sql (if you're not doing this, maybe you should consider it, you know? It's easier to create and manage them in txt even if you use the sql version!)
 
If you were converting your custom item databases from the txt version to sql through the db2sql plugin, there are good news for you! We just updated the plugin, and now it's able to create a .sql script rather than just inserting the entries into your database. This means that you can create the SQL entries in your own computer, without even needing a database (just a copy of Hercules), without risking to slow down your server, and then upload them whenever you want.
 
And if you're relying on the .sql scripts we provide... well, there are still good news for you! Outdated item_db.sql files are a thing of the past now: those scripts will get updated automatically after every commit by our HerculesWS API bot, exactly like the HPM Hooks!
 
As a final note, now the pre-re and re databases share the same structure in SQL as well! no more column mismatch if you're trying to import into your Renewal server an item that was meant for pre-renewal.
 
If you have data already imported to your item_db2 SQL table, it'll be updated to the new table format through the regular upgrade sql mechanism (just run the provided script in the sql-files/upgrades folder). Please note that the script requires MySQL 5.0 or newer -- if you're still on MySQL 4.0, please open it in a text editor, read the comments and run the provided queries manually.
 
I have this event item entry that came with an old script I downloaded...
 
No worries, you can get it converted. Use the same script (or the provided web page) you'd use to convert an entire item database, it'll work just fine even for an item or two!
 
Special thanks

• To Ind, for bringing up the idea and pushing it forward until it was implemented. And for his awesome work on the HerculesWS API and database converter plugin.
• To Yommy, for the original idea, and for some invaluable help finalizing the actual database format.

Links~u!

• Main commit.
• Removed redundant item_db2_re.sql (now both have the same structure).
• db2sql plugin update.
• item_db2 inheritance.
• item_db2 SQL upgrade script.

Edited February 7, 2017 by Ridley

[Yommy 265]

SQUEEE

[Nameless2you 97]

o-o

[Mystery 594]

*Mystery awaits...*

[Mumbles 193]

pls. when

[kyeme 71]

SUPEEERR WOW 

[Michi 101]

Wonderful

[kisuka 178]

Impressive and a long time coming in my opinion.

[Neo-Mind 264]

its alive ... its alive... and i can already see many people dying and cribbing.

Edited November 15, 2013 by Neo

[Judas 100]

this will be great :3

[Mhalicot 392]

Impressive

[Milamber 1]

excellente

[Lelouch 10]

[pr3p 39]

Awesome +1

[ossi0110 200]

this will be great

[Kenpachi 65]

I don't like it.
1. Now I always have to know the default values of the fields which aren't stated

2. My converter is useless now, which In just finished last weekend. -.-

[orange 4]

All announcements have credits to yommy

 

yommy mommy

[Neo-Mind 264]

I don't like it.

1. Now I always have to know the default values of the fields which aren't stated

2. My converter is useless now, which In just finished last weekend. -.-

off topic question: what converter? Only issue i see is that you would need to fix the file parsing part - im not saying its easy but it doesn't look too difficult

[Nameless2you 97]

I don't like it.

1. Now I always have to know the default values of the fields which aren't stated

2. My converter is useless now, which In just finished last weekend. -.-

Dito, but they did promise us a converter~ so let's see bout that

[Kenpachi 65]

 

im not saying its easy but it doesn't look too difficult

It isn't difficult but a load of work, which isn't necessary.

 

 

Dito, but they did promise us a converter~ so let's see bout that

I don't think there will be a converter which is compatible with my private projects.

 

What actually concerns me most is: Did you think of all the tools in use? Every single tool which reads the item DB must be rewritten because of this update.

Furthermore RegEx replacements are nearly impossible now, too.

 

I wish this would have been discussed before implementing, but now it's too late. :S

 

 

My suggestion to please everyone: Provide a converter which is capable of converting the new format into the old format.

[kisuka 178]

 

 

im not saying its easy but it doesn't look too difficult

It isn't difficult but a load of work, which isn't necessary.

 

> 

Dito, but they did promise us a converter~ so let's see bout that

I don't think there will be a converter which is compatible with my private projects.

 

What actually concerns me most is: Did you think of all the tools in use? Every single tool which reads the item DB must be rewritten because of this update.

Furthermore RegEx replacements are nearly impossible now, too.

 

I wish this would have been discussed before implementing, but now it's too late. :S

 

 

My suggestion to please everyone: Provide a converter which is capable of converting the new format into the old format.

 

 

In my honest opinion, if you are using your item db for various other things other than just your in-game items, you really should be utilizing sql instead. The point of item_db.txt originally was just to support the txt db version of athena. At this point, since Hercules doesn't support txt db it would make sense to just start using full sql. Though I get why some would wanna keep using the txt version as it's more convenient. This adds readability to the db, which has been sorely needed for years. Can't tell you the countless times I've had to 'Page Up' to the top part to remember what row / comma section I was in...

[Kenpachi 65]

 

it would make sense to just start using full sql

I suggested this many times. Noone wants it. And as long as there are just a few files available in SQL my tools will use the TXT version, simply because I don't want to implement a logic for SQL an another one for TXT.

 

 

Can't tell you the countless times I've had to 'Page Up' to the top part to remember what row / comma section I was in...

Well, now you have to search another item if the item you are editing inherits another item just to see the values. And perhaps this item inherits anotherone and you now have to search this now. Furthermore you don't see all the values with one view...  I my opinion this new format is pretty confusing.

[Gerz 7]

No it not confuse for newbie like it!

[Zezicla 2]

+1 this could become 1 of the best stuff we have ever done

[Jedzkie 58]

Well this format is good because the developers are following like aegis files, this format is looks like same as special.sc in aegis, this format is more user friendly to beginners than the old one.