Item DB file structure overhaul

[Haru 290]

 

 

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.

 

 

The provided converter is to transition from the old to the new format. It's already there.

If you need to use the file for any other purpose I'd recommend you to look at the db2sql plugin code, and modify that to suit your needs. All the item db info is available there (including the default values for the missing fields), and you can change the fprintf format strings to suit your needs.

 

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 am aware of that. But that's not an excuse for being stuck in the past with unreadable file formats. Tools can be easily edited and/or rewritten. Regular expressions are much harder to use (if not impossible), but that doesn't prevent you from writing a tool to convert the file to whatever format you find more suitable, and back to the libconfig format. The file in the repository is fine as it is, at least when it's updated, diffs are easier to read, and nobody can sneak in nasty, subtle changes that would pass unnoticed.

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

Actually, it's been discussed since May 2013.

 

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

Feel free to release one. I personally won't, as there's no need for it to be in the repository (other than making the Hercules database usable with other server emulators, which is outside our scope.)

 

On a side note, the format has now new (optional) fields (see BindOnEquip). Those can't be converted to the "old" format, unless you add another field to it (but then it won't be the same old format anymore - that was one of its biggest weaknesses, it was impossible to add fields in a sane way without rewriting the entire file)

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...

Yep. Hated the comma separated version (for anything other than machine parsing) since I saw it the first time. And as fields got added to it, it became harder and harder.

 

 

 

 

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.

 

 

There's a reason not to do that. We'd be back to a hard to read file format (the import scripts). I personally find it pointless to have the item db in SQL (other than for control panels and such), because, well, it's an info that gets read once on server startup, and cached in any case. SQL is useful if you only need to look up parts of it (like a control panel does), but quite pointless (and an annoyance to keep up to date) if all you need is to SELECT * on server startup.

If one of the two formats were to go away, to me it'd be SQL. But it's gonna stay, mostly for Control Panel purposes.

 

 

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.

 

 

You misunderstood the 'Inherit' point. Please re-read how it works.

Inherit can only be used in the item_db2 to inherit the values defined in item_db for *the same item*. You can't have inheritance chains, as it's only a boolean. If your custom Knife[3] has 'Inherit: true' set, and you only define a script (i.e. because you want it to have 100 Atk instead of its standard value), it means it'll be exactly a Knife[3], with all other standard parameters from the item_db, and only the Atk you have overridden will be changed. This means that, if for some reason the official item is updated to have a different weight, you wouldn't need to edit yours, as the change will be propagated (unless you override it.)

[Petey Pablo 0]

nice work!

[Angelmelody 221]

Can the online converter support Chinese charset 950 and 936 ?  they become mojibake

 

 

http://herc.ws/board/topic/2980-about-use-itemdbconverterpl/?p=19746

Edited November 16, 2013 by Angelmelody

[Kenpachi 65]

 

Well, backward campability should be a big point in a community project like this, but maybe that's just my opinion. And forcing people to completely rewrite their tools, isn't nice, too. Adding a field to an existing converter logic is much easier than rethinking the complete logic. I really don't know if the advatages of this update are bigger than the disadvantages. For me personally there are no advatages, so you maybe understand why I'm grumbling.

 

 

Actually, it's been discussed since May 2013.

Can't find a topic. Please send me a link via. PM.

 

 

Feel free to release one. I personally won't, as there's no need for it to be in the repository (other than making the Hercules database usable with other server emulators, which is outside our scope.)

Currently I don't even have time to be actively developing Hercules, so I can't do this.

 

 

There's a reason not to do that. We'd be back to a hard to read file format (the import scripts). I personally find it pointless to have the item db in SQL (other than for control panels and such), because, well, it's an info that gets read once on server startup, and cached in any case. SQL is useful if you only need to look up parts of it (like a control panel does), but quite pointless (and an annoyance to keep up to date) if all you need is to SELECT * on server startup.

If one of the two formats were to go away, to me it'd be SQL. But it's gonna stay, mostly for Control Panel purposes.

That's the point. Think of the possibilities of SQL. You would be able to change data while server is running and this not just temporary. You could even create completely new data structures if required for a script. at runtime. (You can do this know, but not when dropping SQL.) Wouldn't whis be an advantage? "Hard to read".. Well, there are many many tools which make it easy to create SQL statements if you don't like typing them, that's not a valid reason in my opinion.

 

 

You misunderstood the 'Inherit' point. Please re-read how it works.

Inherit can only be used in the item_db2 to inherit the values defined in item_db for *the same item*. You can't have inheritance chains, as it's only a boolean. If your custom Knife[3] has 'Inherit: true' set, and you only define a script (i.e. because you want it to have 100 Atk instead of its standard value), it means it'll be exactly a Knife[3], with all other standard parameters from the item_db, and only the Atk you have overridden will be changed. This means that, if for some reason the official item is updated to have a different weight, you wouldn't need to edit yours, as the change will be propagated (unless you override it.) 

Yeah, thanks for the clarification. I indeed misunderstood that one.

[kisuka 178]

Well, backward campability should be a big point in a community project like this, but maybe that's just my opinion. And forcing people to completely rewrite their tools, isn't nice, too. Adding a field to an existing converter logic is much easier than rethinking the complete logic. I really don't know if the advatages of this update are bigger than the disadvantages. For me personally there are no advatages, so you maybe understand why I'm grumbling.

 

That's not actually true. There are plenty of open source, community driven projects on the internet that have had huge changes happen to them that cause massive rewrites to third-party applications. The project itself doesn't support or dev these third party projects, so it's up to the people running the third party projects to update their code to match with the latest version of the original software, or at least support it.

[Mhalicot 392]

I wish there is also overhaul in Mob db file structure

[Yommy 265]

I wish there is also overhaul in Mob db file structure

this is next on the hit list

[simplynice 1]

I like the idea how you guys changed the item database file structure just for the sake that us server owner and you guys as a developers wants a readable format. In my point of view I can only see:

 

 

{	// =================== 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)}, 

35 lines of code if it's a complete code, 3 lines of mandatory code excluding braces. In my item_db.txt i have 8.3k lines of code and I'm still using the old format, converting it will increase the line and yes parsing is not that fast.

I agree it's readable. Format is good but line consuming. Parsing is not that fast. Size of file increase.

Edited November 16, 2013 by simplynice

[mrlongshen 22]

Wow ! such a miracle. nice development ! yeahhhhh ~ waiting for mob_db structure. hehe...

I wish someone make the convertor instead the online convertor.

[Yommy 265]

I agree it's readable. Format is good but line consuming. Parsing is not that fast. Size of file increase.

lines are not some valuable resource that will run out, and file size does not increase as much as you think it would.

685kb -> 1023kb (pre-re)

887kb -> 1353kb (re)

 

 

I wish someone make the convertor instead the online convertor.

its already in the repo

https://raw.github.com/HerculesWS/Hercules/master/tools/itemdbconverter.pl

 

And about it not being that fast, its actually faster

[13:48] <Haruna> [11:07:48] <Yommy>   Haruna, does new item_db format take longer to load ? <--- likely, but not in a human-perceivable form, I believe *benchmarks*

[13:57] <Haruna> takes about 57ms to do the entire parsing and validation

[14:13] <Haruna> Lol, guess what? The old version takes longer

[14:13] <Haruna> 95ms

[14:14] <Haruna> old:  (repeated 1000 times in 95603 ms total).

[14:14] <Haruna> new:  (repeated 1000 times in 57390 ms total).

[14:14] <Haruna>

[mrlongshen 22]

 

I agree it's readable. Format is good but line consuming. Parsing is not that fast. Size of file increase.

lines are not some valuable resource that will run out, and file size does not increase as much as you think it would.

685kb -> 1023kb (pre-re)

887kb -> 1353kb (re)

 

 

>I wish someone make the convertor instead the online convertor.

its already in the repo

https://raw.github.com/HerculesWS/Hercules/master/tools/itemdbconverter.pl

 

 

how to use that sir ?

[Yommy 265]

how to use that sir ?

you didnt even put a single bit of effort into trying to figure it out by yourself o_O

[Haru 290]

Well, backward campability should be a big point in a community project like this, but maybe that's just my opinion. And forcing people to completely rewrite their tools, isn't nice, too. Adding a field to an existing converter logic is much easier than rethinking the complete logic. I really don't know if the advatages of this update are bigger than the disadvantages. For me personally there are no advatages, so you maybe understand why I'm grumbling.

I'm perfectly fine with backwards compatibility, whenever it doesn't slow down development or force developers to use hacks and workarounds to introduce new features.

 

 

 

Actually, it's been discussed since May 2013.

Can't find a topic. Please send me a link via. PM.

 

 

Done~

 

 

There's a reason not to do that. We'd be back to a hard to read file format (the import scripts). I personally find it pointless to have the item db in SQL (other than for control panels and such), because, well, it's an info that gets read once on server startup, and cached in any case. SQL is useful if you only need to look up parts of it (like a control panel does), but quite pointless (and an annoyance to keep up to date) if all you need is to SELECT * on server startup.

If one of the two formats were to go away, to me it'd be SQL. But it's gonna stay, mostly for Control Panel purposes.

That's the point. Think of the possibilities of SQL. You would be able to change data while server is running and this not just temporary. You could even create completely new data structures if required for a script. at runtime. (You can do this know, but not when dropping SQL.) Wouldn't whis be an advantage? "Hard to read".. Well, there are many many tools which make it easy to create SQL statements if you don't like typing them, that's not a valid reason in my opinion.

 

 

You can't really change the data while the server is running (without reloading the database), but then you can reload the txt version in the same exact way (and possibly even faster than the SQL version).

 

I don't really understand the 'create new data structures' part. If you mean creating other SQL tables, sure, but it has nothing to do with the item database. If you mean adding fields to the database -- no, you can't do that, since the code that reads from the database is compiled to the server (both for the txt and the SQL version), so you have at the very least to recompile and restart the server.

 

Also, no matter how you format a SQL statement, it can never become as clean and easy to read as the new item database format. It'll always be a bunch of fields next to each others.

 

 

 

 

I wish there is also overhaul in Mob db file structure

this is next on the hit list

 

 

It indeed is

 

I like the idea how you guys changed the item database file structure just for the sake that us server owner and you guys as a developers wants a readable format. In my point of view I can only see:

{	// =================== 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)}, 

35 lines of code if it's a complete code, 3 lines of mandatory code excluding braces. In my item_db.txt i have 8.3k lines of code and I'm still using the old format, converting it will increase the line and yes parsing is not that fast.

 

I agree it's readable. Format is good but line consuming. Parsing is not that fast. Size of file increase.

 

 

Even if file size increases, I have actually benchmarked it and the parse time decreases. Loading and parsing the old format 1000 times (test done on db/pre_re/item_db.txt) takes 95603ms (average of 93 milliseconds each time), while loading and parsing the new format 1000 times (test done on db/pre_re/item_db.conf) takes 57390ms (so an average of 57 milliseconds).

 

The tests were done respectively on the latest revision before the item db update appeared (7d0a63b72f93c05b302c08cfb19d5651cef1cdb8) and the latest revision (d1a635a06f5152b51d0879176f011b5c6e45431c), so they are even biased towards the old version, since it had less features back then (BindOnEquip didn't exist, so it was reading one less field.)

 

Line count isn't relevant to the parse time. The new parser probably uses a more optimized file reading approach (through libconfig), and doesn't need to unnecessarily read fields that are set to their default values (file is bigger, but it actually contains less data.)

 

If a higher line count disturbs you this much, you can flatten the entire database to ONE line, and it will still work. Make sure you add a semicolon after each value.

This is a perfectly valid item database, if one is that crazy:

item_db: ( { Id: 501; AegisName: "Red_Potion"; Name: "Red Potion"; Type: 0; Buy: 50; Weight: 70; Script: <" itemheal rand(45,65),0; ">; },{ Id: 502; AegisName: "Orange_Potion"; Name: "Orange Potion"; Type: 0; Buy: 200; Weight: 100; Script: <" itemheal rand(105,145),0; ">; },)

(but I refuse to understand why anyone would want that)

 

 

 

how to use that sir ?

you didnt even put a single bit of effort into trying to figure it out by yourself o_O

 

 

Read the first post, it is briefly explained there. You need a perl interpreter and some (very little) ability to run a command from the command-line.

[mrlongshen 22]

 

how to use that sir ?

you didnt even put a single bit of effort into trying to figure it out by yourself o_O

 

perl tools/itemdbconverter.pl < db/item_db2.txt > db/item_db2.conf

sry bro. i didn't  notice. hehe

so i just can use this command on linux only ?

[Haru 290]

Not necessarily. OS X will work out of the box, and so probably will most UNIX variants. It is likely that it works even on Windows (as long as you install the perl interpreter, since it doesn't come pre-installed)

[mrlongshen 22]

@haru. oohh. so for windows I need install the perl interpreter ?

[Zezicla 2]

i love this are all items from old db item and item2 alrdy in this new rev ?

+1 :D:D:D

[mrlongshen 22]

i love this are all items from old db item and item2 alrdy in this new rev ?

+1 :D:D:D

 

yup. you may try it now

[Haru 290]

@haru. oohh. so for windows I need install the perl interpreter ?

Yes. There's a version packaged for easy installation (ActivePerl) at http://www.activestate.com/activeperl/downloads (You can download the free edition)

I have just tested it in a windows virtual machine with the version 5.16.3.1603 downloaded from that site, and it works without any issues.

[Zezicla 2]

item combo db and mob/npc's might fallow right? 

+1 that alrdy

[evilpuncker 503]

anyone can confirm this?

http://herc.ws/board/topic/3029-ritem-data-structure-overhaul-error-msg/

[Zezicla 2]

if read somewhere sql files for items are auto made ? how to do that ?

[Haru 290]

item_db and item_db_re are automatically generated by the HerculesWS API bot. For item_db2 (or if you have edited your item_db for any reason), you can build your custom version with the db2sql plugin.

 

 

(instructions for linux/unix; if you're on windows, YMMV)

 

- Compile the db2sql plugin (make -C src/plugins db2sql)

- Enable it in conf/plugins.conf

- Run the map server with ./map-server --db2sql

- You can find the updated files in the sql-files folder.

[Zezicla 2]

aah thanks sir/maam would in the future item_db2 also included ?

[evilpuncker 503]

item_db and item_db_re are automatically generated by the HerculesWS API bot. For item_db2 (or if you have edited your item_db for any reason), you can build your custom version with the db2sql plugin.

 

 

(instructions for linux/unix; if you're on windows, YMMV)

 

- Compile the db2sql plugin (make -C src/plugins db2sql)

- Enable it in conf/plugins.conf

- Run the map server with ./map-server --db2sql

- You can find the updated files in the sql-files folder.

 

I've used ur online converter with my item_db2.txt that was working flawlessly but after converting it to item_db.conf I got this:

 

[Error]: db/item_db2.conf:1 - syntax error

[Error]: can't read db/item_db2.conf

 

I can't see to find anything wrong, if you want I may send it by PM for you