SQL from item xml's? (Dropcalc)

[blarke]

Hello All,

I'm not a big tool maker myself, so I'd like to ask for some assistance from those who have more experience with this.
I'm willing to write a dropcalculator for our server. The main problem I have with it is that all items are in xml's, thus, normal PHP SQL queries cannot reach it.
The resolution a friend of mine had for this (but had quit from assisting already ) is to create an SQL file from the item XML's which can be imported to the database and with that, basic item attributes (patk, pdef, etc.) can be queried with a simple Left Join.
However, the sql that was created with this method is now wastly outdated and I do not have the creator tool myself.

Can anybody assist me with this?

Thanks!

[blacksea]

http://www.msknight.com/dropcalc/index.php

try this dropcalc

[blarke]

Thanks, looks quite nice, but not what I'm looking for (too many options and some things, ie. custom drop rates set in configs cannot be loaded). But at least it contains the sql's I need, so fields covered.
However, this is like giving fish to a starving man instead of teaching him to fish. So if somebody can help with the original question (converting xml back to SQL database), I'd really appriciate it.

[blacksea]

you can import xml to sql also with this... or you can use navicat

[blarke]

you can import xml to sql also with this... or you can use navicat

Found it in navicat... took a while to understand it, but this is exactly what I needed.
Big thanks!

[Hyrelius]

Since I am analyzing the item XMLs for about 2 weeks now, I can safely tell you that there are quite some "mistakes" in there (which are probably caused by mistakes within the armor-, weapon-, etcitemgrp.dat and itemname-e.dat files from NCZ0ft).

For example: there's a range of Vesper and Dynasty weapons / armors that are listed as EtcItems, but in fact are not Etc-Items, but Armors or Weapons. I thought there's a good way to determine this by evaluating the "weapon_type", "armor_type" or "etcitem_type" attribute within the item-XML-Element. All armors and weapons seem to map nicely once you evaluate this as well as the type specified directly within the "type"-attribute of the <item>-element.

Also: the item-XMLs do not contain the descriptions and icon information except for the first icon (that you'll want in most, but not all cases).

I am currently writing a dropcalc replacement using Google GWT (AJAX framework) and Java Servlets along with mysql-database access on the backend. My application is already able to login (you can login with several accounts - it will add the characters to the character box), retrieve the characters and items. Though I'm rewriting the retrieval of the items, because I'm adjusting to the XMLs.

Additionally my backend already has static services for skills and recipes (and soon: items). This means I'll soon be able to retrieve detailed character information along with subclasses and skills. In my application you can just place the skill-XMLs, item-XMLs and recipe-XML(s) into the resource-folder and it will automatically find them. Also there's an exception-XML for skills, because some skills are not correctly defined within the skills XMLs (e.g. "Expertise S80" and S84 is supposed to have a skill-icon, which it does not; therefore I map it to the S-grade icon).

Sorry for this much text, just wanted to let you know.

Cheers!

Edit: I just saw that you only wanted to create that "item_list" SQL-table. That's what I originally wanted to do. If you do that, don't forget to keep your "armor", "etcitem" and "weapon" SQL-tables updated as well. If you have trouble creating those tables, post here and I'll see if I can make my XMLGen-tool output a SQL-script (for High5) that would contain the same columns etc. as the outdated "item_list" does. See ya.

[blarke]

Hi Hyrelius,
Thanks, I do not need a full-all-round dropcalc with skills, recipes, merchants and so forth (like, for example, l2jdb.l2jdp.com), only functionality needed is npc list for specific item (looking drop by item name) or droplist for a specific npc (looking drop by npc name).
For this, I basically need(ed) an item id-item name table, which Navicat can handle very well (but it cannot load sub-element attributes, like Weight, Crystal Grade etc.; would be "nice to have", but not absolutely necessary).
This being combined by the item descriptions table (from the dropcalc blacksea suggested), this would be sufficient enough to make our own.
Only one question, however - for displaying NPC locations. How are the x and y coordinates scaled in High Five, what are the borders?

Thanks, cheers
blarke