sprites: request to devs

[paradoxharbinger]

well i'm pretty handy with the sql scripting, and the scripts for this project would probably be relatively simple compared to some of the stuff i do at work, so if someone provided the actual database, i could write the scripts for it

[Edi]

All that needs to be done is for somebody to match the sprites to units (can be a separate table, and in fact, should be). For the rest of it (armor, weapons, unit base stats), the download link is in my sig. The spreadsheet is in both Excel and OpenOffice format and if you take the BaseA, BaseW and BaseU sheets, those are the elemental statistics and can be directly imported as DB tables.

The queries would be fairly simple and only need the formulas to calculate stuff based on that data. If you want, I can spare the time to give you the formulas, it was squeezing those formulas into spreadsheet function format that was the biggest b*tch when making the Dom3DB.

[paradoxharbinger]

the first thing that really ought to be done is decide on the structure of the database. we ought to have one table, call it tbUnitSprite, with one row for each unit, and a column for the unit number and two blob columns, one for each of the units sprites. this table would be populated from the start and would for the most part be finallized from the start. other tables would link off of it and those would be the tables that would be incrementally updated by the community to get this thing finished.

just my two cents on the direction to take it

[Edi]

Actually, what you should use as the base tables are the BaseU, BaseW and BaseA tables from the Dom3DB (name them whatever you want, though), because those are complete and accurate, barring the known issue of Firelord not having magic (F2) listed.

Alternatively, do the tbUnitSprite table in a spreadsheet and then copypaste the contents of the BaseU table so that the sprite columns are either right after the unit number column or the unit name column and use that as base.

OR, list unit sprites on a separate table with sprite number column being from 0 to wherever and add spr1 and spr2 columns to the BaseU table and then fill those with the appropriate entries.

The Dom3DB was designed with basic DB design principles in mind so that if desired, it could be ported to SQL almost as is. Besides, not all of the sprites are used and some have been changed, so having them in a separate table and having each unit have two columns for the normal and attack sprites that reference the sprite table would make for a more structurally sound database.

However, bear in mind that I do NOT do database work at my job, so if you can take those suggestions and improve on them, feel free to do so. I'm coming at it from the angle that since the unit database itself is basically complete already except for the sprite information, it would be a complete and utter waste of effort to duplicate that from the ground up when just a minor tweak would get the whole thing going.

[paradoxharbinger]

i'd forgotten about there being sprites in there that arent in the game. basing the db off of edis tables and then having them reference another table with the sprites in it sounds like a good plan. i cant remember how you handled special abilities (ie regenration). special consideration might have to be given to them since not every unit even has them let alone the same number of them.

[Gandalf Parker]

Keep in mind the headache that will be caused by units with more than one form. 2 forms, 3 forms, whats the max? 5?

[paradoxharbinger]

so we'll have an intermediary table between the actual units and the sprite table. this new table point to a unit and to any sprites associated with it. no problem.

[Edi]

Paradox, take a look at the Dom3DB. Once you take a look at the BaseU page and the glossary, the structure should be fairly clear for you. It'll be a lot easier to also plan the rest of the SQL DB that way.

The way regeneration works is a percentage of hit points, rounded up (so HP 10 is regen 1 and HP 11 is regen 2 etc). The percentages weren't too difficult to figure out from the units. Resistances, forge bonuses and other such are also percentages, which means they are between 0 and 1 after you strip the spreadsheet formatting out. All special abilities that are either on or off (like forest, mountain and swamp survival or flying) are 1 or 0.

Not quite all abilities are listed (such as Belial's corrupt commander, those would be in the Special column, which has all sorts of things that did not warrant their own category.

Awe values should default to -1 (unit does not have it) in the SQL DB and fear should default to null.

[paradoxharbinger]

from a database perspective, i dont like that baseu table WAAAAY too much wasted memory for the abilities, though i will admit that it would be fast to run a query against. that's often the trade off that has to made with this sort of stuff. i don't know how to make a decision on that trade off, as my experience is with a corporate database that is just in the other room, so speed is not something that i am too terribly concerned with when writing queries. if i were to design it myself, and didnt have the speed of the internet to factor in, i would probably do something similar to what i suggested for the sprites. one table has a row for each skill (this would allow for descriptions to be stored for each skill as well without taking up a horrendous amount of memory) and a second table is a map between units and the skills.

it could be queried like this
Code:

---

 



select UnitID

from tbUnit

	inner join tbUnitSkillMap

	on tbUnit.UnitID = tbUnitSkillMap.UnitID

	inner join tbSkill

	on tbUnitSkillMap.SkillID = tbSkill.SkillID

where SkillName = "some skill name here";

 

---

[Edi]

If you want to do it that way, is it possible to chop the BaseU table up to get the required tables? I can also ask my friend Kaljamaha to pitch in on this, he works with SQL stuff all the time, perhaps he could come up with some ideas.