Important! SQL vs XML

[toastgodsupreme]

So before I even poke around and start work on this, I need to know, primarily, what the devs think. DP and core alike, as well as the community.

The Goal:
Set one location for armor and weapon stats.

Our Current Situation:
We store data for weapons and armor in XML files AND in SQL tables. Some info is read from each of these locations. This is inefficient and makes it a pain in the ass for DP updates since TWO locations need to be maintained.

Possible Outcome #1 - SQL:
We no longer store OR call weapon and armor stats from xmls. For weapons, this means that we do NOT define base values for patk, matk, rcrit, acccombat, patkspd, etc in the xmls. These values will be deleted from the XMLs and will now be called directly from the database. Some XML values will remain. These include the enchant information (see below for further discussion on this), and outdated weapon SA information (see below for further discussion on this).

Possible Outcome #2 - XML:
Whatever information isn't being called from the SQL tables, we delete. pdef, patk, etc, etc. All the stuff we define in xmls that gets called instead of from SQL. We verify that the core doesn't attempt to pull that information EVER from the database and once it's verified, remove it.


Pros/Cons/Questions/Points of interest...
Some of these questions are important that we discuss and get answers to. Otherwise we'll just continue to use inefficient methods of doing things. And honestly, that sucks balls.
I want to make a little note here that I pretty much abandoned all hope some time ago for the majority of the DP team due to the lack of answers/responses to some of the same questions I'm about to pose below. Either they didn't know, didn't care enough to find out the answer, or just plain didn't care at all. But since we're all together now in one forum, I think it's time some of these were asked again.


Question: Which method is more efficient? DB or XML? This is static information.
In regards to updates, SQL is the clear winner. We can easily run scripts and fix a number of items in a quick second. XML requires a lot of manual updating.
In regards to flexibility, XML wins. But how flexible does this static information need to be? Weapons and armor don't exactly evolve nearly as fast as skills do (which is why skills need to be done in XML because it's far more flexible when we're fixing them).

Question: Currently, why do we define <enchant val="0" order="0x0C" stat="blah"/>?
I've looked through a random sampling of files from the armor and weapons folders and this value never deviates. So why do we keep defining it? Why can't we set this in the core?

This is further solved if we can get support for isEnchantable added:
viewtopic.php?f=69&t=10751

That way all armor and weapons have the <enchant val="0" order="0x0C" stat="blah"/> defined in the core, but are restricted for enchanting via their isEnchantable value.

Question: Why do we currently define add val="0" order="0x10". That seems dumb to add 0. Or do we do it just because that's what the C4 data had in it's files so we never bothered to think "do we really need to say, add 0"?

Point of interest: If SQL wins, some of the only information that will be left in the weapon XMLs will be weapon SA info. We can't have this information located in the weapon XMLs any longer. I've spent many hours going through official C4 data along with information from a number of sources to make weapon SAs more retail-like. But I need cooperation on this from a certain member of the DP team (you know who you are). Our current method is not only very non-retail-like, but lazy, and confusing for future editors (which causes problems that I have previously pointed out and which have been ignored, lol). So if we go with SQL, after all is done, our next goal should be to clean out the weapon xmls further by getting rid of weapon SA information and doing them the right way.

Point of interest: If SQL wins, XMLs SHOULD only be used for item restriction information. But we might be able to move that information into SQL as well. Though I don't see that method for restriction info being as flexible as keeping it in the xmls. Thoughts?


I'm sure I've missed a few things as it's nearly 6:00 am here. But I wanted to get the ball rolling on this discussion because I really want to see this done. And I'm sure the DP team would love to not have to manage two locations for weapon and armor info anymore.

[Maitrechat]

My point of vue is Xml is more flexible for many things than sql tables. If you want to add a parameter for some items only you just have to add a line into xml of this item plus the core managment of this param. If you want to do the same with only Sql tables, you have to add a default parameter for every item in database. Depending the number of parameter you want to add it can be horrible.
I said that because I have tested it with a time-limited system I put several month ago with adding a parameter for each item/weapon/armor. Now I regret and I think it would be better to do this in Xml beacause maintain is more easy.
The flexibility of Xml is very interresting when you have structure modifications to apply.

[InShadow]

I vote for:
Skills in XML, skill trees in SQL as it is now.
Item data in SQL, item skills - SA etc in XML...
Simply because I think its faster and more efficient to use SQL. For things that have more parameters or need greater flexibility its surely better to use XML.

[Maitrechat]

You don't acces database each time you need information on a item, once it's loaded you don't access it anymore except in case of reload (for the base stats of items/weapon/armors).
Items are like Skills, each of them can have different parameters. Some skills are staticReuse other consume items, for items it's the same, some can be enchantable, other possess skills. Items can have many different attributes that are not necessarily used by all items. So why try to put them into a fixed-structure database when you can use a storage mode like Xml wich already works for other things (skill for example :p).

[nBd]

I prefer XML over SQL. Anyways, there was a rework and big discussion in Dev Team month ago, about moving all Items to SQL. Some devs had good points why moving them to SQL instead of XML. If i would rework Items i would choose XML as its more flexible. Only bad point would be mass editing, but for that, a small gui could handle this fine too. Maybe one of those Dev's working on SQL can post pro's for SQL and negs for XML

[_DS_]

Please remember about backward compartibility
Even now additional fields in the tables shock noob-admins. Moving all into sql you will make huge number of contribs useless.

[janiii]

yeah, mass editing..e.g. i had problems with every common item and every hero item and every whatever item that cannot be enchanted to add <cond><item enchantable="false"/></cond> . there are soooo many common items. in sql it would be sooo much more easier..
but i don't say that everything should be in sql. no. but xml is harder to compare with values in client. in xml you can't do any queries, no filtering of data.. or i just don't know such methods..

[toastgodsupreme]

I prefer XML over SQL. Anyways, there was a rework and big discussion in Dev Team month ago, about moving all Items to SQL. Some devs had good points why moving them to SQL instead of XML. If i would rework Items i would choose XML as its more flexible. Only bad point would be mass editing, but for that, a small gui could handle this fine too. Maybe one of those Dev's working on SQL can post pro's for SQL and negs for XML

Well for a lot of stuff, the data is already present in SQL, it's just a matter of calling it from SQL instead of XML.

For armor and weapons, I feel that most information (not all, but most) should be handled in SQL. Like janiii said, updating can be a nightmare otherwise. For skills, obviously that should stay in XML because we need to maintain flexibility with those. But armor and weapons don't change a whole lot as far as attributes and whatnot, so we'd rarely ever need to add a new column.

Please remember about backward compartibility

As long as the custom contrib authors made their items correctly (ie. including the data in xml and SQL), then there shouldn't be any compatibility issues when the switch happens.

The only time this would be an issue is if the custom contrib itself calls certain data from one location and that location no longer stores that info. But I can't imagine a mod that would do that.

[Reimu]

XML for "static" data, SQL for "dynamic", isn't it? Why you try to dynamic data in XML and static data in SQL by reason what it is useful NOW? It is possible to convert XML document in the table hierarchy (or one table), so you can create interface to data source and easy change data source XML->SQL \ SQL->XML.

[toastgodsupreme]

XML for "static" data, SQL for "dynamic", isn't it? Why you try to dynamic data in XML and static data in SQL by reason what it is useful NOW? It is possible to convert XML document in the table hierarchy (or one table), so you can create interface to data source and easy change data source XML->SQL \ SQL->XML.

Guys, remember, this is for ARMOR and WEAPON data only. We're not discussing any other data.

And this is JUST about where we call their stats from. Once that location is decided, the "loser" can have that stat data removed so we only need to manage one location for item stats.

[InShadow]

XML for "static" data, SQL for "dynamic", isn't it? Why you try to dynamic data in XML and static data in SQL by reason what it is useful NOW? It is possible to convert XML document in the table hierarchy (or one table), so you can create interface to data source and easy change data source XML->SQL \ SQL->XML.

Guys, remember, this is for ARMOR and WEAPON data only. We're not discussing any other data.

And this is JUST about where we call their stats from. Once that location is decided, the "loser" can have that stat data removed so we only need to manage one location for item stats.

Armor and weapon data should be imo in SQL - its attributes are not changing alot or at least they didn't change since the beginning of L2. Only the skills, SAs, augmentations have been added, and this should be in XML like all the skills.

[Aikimaniac]

imho data like fix stats (matk..patk..mdef..pdef...etc) should be in SQL... other values in xml like..enchants..SAs..called skills...etc...from xml can be cleaned then stuff like matk and patk to have those files cleaner... its more comfortable to browse xmls files than sql files (in eclipse..i dont say navicat or some similar tool)...

[Xanewok]

IMO all data should be stored in one location, not two as Aiki said... It will be practically no difference if only stats are in SQL or XML, 'cause these values are static anyway, and the loading time will be the same (true?) and in addition, we'll have all data in one place + possibility do data filter and easy browsing thanks to any XML editor (GUI, changing to table structure etc.) That's my opinion. (Btw, is any skill values like power etc. stored in SQL? no, it's all in XML and noone has complained about it : )

@_DS_
About shocking noob-admins, IMO they should first read about it and practice L2J admining, before doing anything serious, thus being shocked because of their own lack of experience.

[toastgodsupreme]

IMO all data should be stored in one location, not two as Aiki said... It will be practically no difference if only stats are in SQL or XML, 'cause these values are static anyway, and the loading time will be the same (true?) and in addition, we'll have all data in one place + possibility do data filter and easy browsing thanks to any XML editor (GUI, changing to table structure etc.) That's my opinion. (Btw, is any skill values like power etc. stored in SQL? no, it's all in XML and noone has complained about it : )

What it comes down to is that the biggest benefits from this won't be seen in performance or speed, but rather maintainability and increased control over information.

There's just no reason at all to duplicate these stats and have identical information residing in memory. It benefits no one and hurts the project by requiring additional time to sync two sources with each other every time there's a change.

As far as I know from rumors, Ahmed started the transition from xml to SQL but never completed it.

And with Gracia Final practically here, I think most people agree that we need to choose a winner and run with it before we start implementing all those shiny new GF armors, weapons, etc.

For armor and weapon stats, I'm 100% in favor of SQL. It's easier to maintain and update. Armor and weapon STATS don't fluxuate a whole lot so we don't need the flexibility that XML offers. The XML should be there to pick up whatever we can't handle properly through SQL (such as temporary SA info and item restrictions).

[InShadow]

IMO all data should be stored in one location, not two as Aiki said... It will be practically no difference if only stats are in SQL or XML, 'cause these values are static anyway, and the loading time will be the same (true?) and in addition, we'll have all data in one place + possibility do data filter and easy browsing thanks to any XML editor (GUI, changing to table structure etc.) That's my opinion. (Btw, is any skill values like power etc. stored in SQL? no, it's all in XML and noone has complained about it : )

What it comes down to is that the biggest benefits from this won't be seen in performance or speed, but rather maintainability and increased control over information.

There's just no reason at all to duplicate these stats and have identical information residing in memory. It benefits no one and hurts the project by requiring additional time to sync two sources with each other every time there's a change.

As far as I know from rumors, Ahmed started the transition from xml to SQL but never completed it.

And with Gracia Final practically here, I think most people agree that we need to choose a winner and run with it before we start implementing all those shiny new GF armors, weapons, etc.

For armor and weapon stats, I'm 100% in favor of SQL. It's easier to maintain and update. Armor and weapon STATS don't fluxuate a whole lot so we don't need the flexibility that XML offers. The XML should be there to pick up whatever we can't handle properly through SQL (such as temporary SA info and item restrictions).

Totally agree with you.