Need clarification on DB items

[Arcalyn]

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]

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]

I still need to understand them.

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

[Venelar]

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]

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]

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]

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]

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]

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]

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]

How was the npc DB created?



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]

I have datafiles I collected from spawnlogging using showEQ. Drawde and Lurker took those and created databases based on the logs.

[Lurker_005]

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]

My mistake.. I thought the addon db was generated from the spawnlogs, not eqcollecter...

[Arcalyn]

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