PDA

View Full Version : Need clarification on DB items


Arcalyn
09-26-2002, 01:42 AM
I'm tryin to figure out a way to clean the (spawn)database, getting sick of seeing 3 Guntriks,4 priests of discord and bout 10 of those butterflies in kelethin! heeheh

Anyones suggestions on a progy to use would be welcomed.

BUT.......I NEED......Clarification......PLEASE......

What is the order for the NPCs into the database.....what I mean is.............

first ya need an:
NPC (a_ugly_arcalyn, 42, 2334,12 3242 blah blah) <NPC>
then ya need: ?????
Spawngroup (2353,'fearplane') <WHICH ZONE>
then maybe: ?????? what does this do? other than %chance to spawn
Spawnentry (point-to-spawngroup, point-to-NPC_type, %) <????>
then: ????
Spawn2 (values) <SET WHO AND WHERE to spawn>

^ not worried about the loot/faction part of this yet just the above ^

Basically I'm asking what are these for?
Obviously don't answer the ones I have figured out correctly =)

NPC_type - to define the npc and its traits/abilities/skills.

Spawngroup - selects the NPC_type and NOCLUE?

Spawnentry - Looks like it just combines but what?

Spawn2 - to specify the location of the NPC to spawn.

No guesses from the peanut gallery, please.....just info from a DEV when they get time.

I need to understand those in order to clean up the database;
I have got boat loads of time at work to organize the HQ_spawn.sql database.

Lurker_005
09-26-2002, 02:13 AM
I plan to work on this via VBScripting, if you were intending to do it by hand, you may want to reconsider. Remember these files get updated every few weeks. Quests are next though, so it will be a week or 2 before I have any time to attempt to clean up spawns.

Arcalyn
09-26-2002, 02:18 AM
I still need to understand them.

And your post did nothing to add me in that respect.

Venelar
09-26-2002, 04:57 AM
One option is to go through the spawns in EQAdmin and delete the copies by hand. It would probably take a very long time though.

Arcalyn
09-26-2002, 05:06 AM
once again that doesn't solve the matter at hand.

If someone who knows manages to read this post please let me know what is which and who? =)

Thanks.

ps. Already have most of the duplicate entries cleared, BUT will need more time to make sure its the proper duplicate. Don't want Guntrik standing in the wrong spot now do we? *smile*

Lurker_005
09-26-2002, 07:02 AM
My point was that if your doing it by hand, every 2 weeks or so you would need to re do it to remain up to date.

Now if your going to make a way to automate it, then I'll sit down and write out the relevant db field relations. BTW what you want is more or less contained in the DB field names in the DB.

Arcalyn
09-26-2002, 09:11 AM
How will you write a script to elliminate the duplicates?

spawnentry & spawn2 contain the duplicates but I can't see any field which could be an identifier.

spawnentry the second number(which has the duplicates) could be used but what would your variable be? Can't just delete them all, and everything else within spawnentry doesn't have a duplicate.

spawn2 the only thing I've found which shows that they are duplicates is if there is only a SMALL varience in the X,Y fields with Z staying constant BUT....

EXAMPLE from actually DB:
-<npc_types>-
(7045,'Guntrik',61,10,17,37529,0,3,0,-1,0,0,7,0,0,0,'','',0,0,0,1,1,1,1,1,0,0,0,0.67,1.2 5),

-<spawngroup>-
(7045,'oggok7045'),

Everything makes sense so far.......BUT......

-<spawnentry>-
if ya look for 7045 in the first field only one entry BUT
(7045,7078,15), ------> is >NOT< Guntrik its Uoola
so search through second field
(7050,7045,12), ------> Guntrik in emulator
(7076,7045,1), --------> Guntrik in emulator
(7086,7045,4), --------> Guntrik in emulator

-<spawn2>-
(7045,7045,'oggok',684,659,5.6,50,180,0), -----> Unfound
(7076,7076,'oggok',64,26,-24.8,173,180,0), --> Front Guntrik
(7087,7087,'oggok',69,26,-24.8,173,180,0), --> Middle Guntrik
(7050,7050,'oggok',74,26,-24.8,173,180,0), --> By-wall Guntrik

(7078,7078,'oggok',-44,356,17.3,74,180,0), --> Uoola

Since no one will answer my original post up top, I'd have to officially say the DB is wacked. *chuckles*

So if a DEV or Lurker_005 if you can answer my first post up top it'd help a whole bunch!

Lurker_005
09-26-2002, 11:23 AM
ok a simple attempt to comment the mysql table definitions for spawns.

I trimed out the table fields not relarted to tieing the tables together.


#
# Table structure for table 'npc_types'
#

CREATE TABLE npc_types ( # key is the ID, Table defines mob to be spawned
id int(11) NOT NULL auto_increment,
name text NOT NULL,
#sniped
PRIMARY KEY (id)
) TYPE=MyISAM;



#
# Table structure for table 'spawn2'
#

CREATE TABLE spawn2 ( # defines where and how the spawngroup spawns
id int(11) NOT NULL auto_increment,
spawngroupID int(11) NOT NULL default '0',
#sniped
PRIMARY KEY (id)
) TYPE=MyISAM;



#
# Table structure for table 'spawnentry'
#

CREATE TABLE spawnentry ( # lists the npc_types that can spawn in a given spawn group
spawngroupID int(11) NOT NULL default '0',
npcID int(11) NOT NULL default '0', # npc_tpe ID
#sniped
PRIMARY KEY (spawngroupID,npcID)
) TYPE=MyISAM;



#
# Table structure for table 'spawngroup'
#

CREATE TABLE spawngroup ( # named entry used to tie spawn2 and spawnenrty together
id int(11) NOT NULL auto_increment, # ID (spawngroupID)
name varchar(30) NOT NULL default '', # user given name neams nothing
PRIMARY KEY (id),
UNIQUE KEY name (name)
) TYPE=MyISAM;

Kolo
09-26-2002, 03:29 PM
So, basically, if we tell you that we could develop such a program that would go through, check to make sure all the "x,y,z"'s were say at least 10 coordinates apart and delete anything/store duplicates elswhere for review, that you would give us the table layout....

Data crunching is what I'm good at....

I believe the formula:
Cube Root of ((SQR(x1^2+x2^2))^3 + (SQR(y1^2+y2^2))^3 + (SQR(z1^2+z2^2))^3)
Would return the distance between spawns...I'm pulling that out my ass right now, havent tested it, and it's been a while since my last math class. The part I'm questioning is if the cubing and cube root would work. If anyone knows off the top of their heads :P please post info.

Trumpcard
09-27-2002, 12:32 AM
Distance between 2 points in 3 dimensional space is


D = SQR[(x1 - x2) ^ 2 + (y1 - y) ^2 + (z1 - z2) ^2]

No cubes in there. Seems like there should be as there's 3 dimensions, but it doesnt work that way.

I think the idea would be to get rid of the duplicate mobs throughout the zone, not just ones close together though, so cleaning them out based on distance might not do a whole lot for you.

Arcalyn
09-27-2002, 02:19 AM
How was the npc DB created?

http://home.columbus.rr.com/arcalyn/screwed_DB.jpg

Is there anyway for me to use some data collecting program to log into EQ and create my own NPC database?

And how would it be done, if so?

Thanks for any help.

Trumpcard
09-27-2002, 02:34 AM
I have datafiles I collected from spawnlogging using showEQ. Drawde and Lurker took those and created databases based on the logs.

Lurker_005
09-27-2002, 03:14 AM
The current DB Addon is generated from the eqcollector.com data What you see is what is in their data.

Current spawns are less than perfect, but until I have all the other data converted that is avaliable, I didn't plan to go back and try to do any clean up. There is also Drawde's spawn DB, the MSH data, and the huge seq spawn log to try and process. Incorperating those will create more duplicates that will need cleaning also.

Oh and based on what I have seen, it looks like some servers have spawns in slightly different locations. That is my guess why there are so many dupe spawns.

Trumpcard
09-27-2002, 03:16 AM
My mistake.. I thought the addon db was generated from the spawnlogs, not eqcollecter...

Arcalyn
09-27-2002, 09:43 AM
WAY over my head on this one!

But will figure out enough to be able to create personalised zones for me.

but right now that is one screwy DB

No its NOT your responsibility to fix it lurker_005!

but I'll appreciate it when ya do =)

no matter how long

hehe

Lurker_005
09-27-2002, 10:58 AM
I just don't want someone to spend 40 hours cleaning up the DB, then the next day I release a new DB. Then 2 weeks later I have a way to clean the DB automatically (at least in part).

Arcalyn
09-27-2002, 01:01 PM
Can you post how you got rid of the duplicates?

Would love to know

Thanks

Meekrat
09-27-2002, 01:36 PM
What I believe would good is to just have one database....

Team up on it...one person work item, one on spawns, one on quests....so forth. Link up in email or IM and compile it into one database. Should prevent dups and you have more than one head to solve a problem when they arise. The team should get together and figure out a layout, that way they are all on the same sheet of music and not trying to use the same ID numbers for diff things.

Just a thought (2 cents).

Kolo
09-29-2002, 06:38 AM
Well...I now know Arcalyn lives within 30 miles of me :P Anyone have any suggestions on how I an to remain alive with a madman in the area? j/k