Request for new # command please

[gernblan]

Well sure it would be a "developer only" thing... and you're right about the reverse lookup and all, but even still, if the query was given a low priority (I'd certainly trade speed for the convenience in terms of the query return) it may not be so bad.

I don't see a situation where a dozen people are all using his command at the same time or anything.

I do see where those who run development servers especially would be able to generate content even faster.

With anniversary support coming (hopefully) we must all look for shortcuts in world building. It can't be as slow as titanium has been, you know?

IN terms of the load on the server, we'll never know until we try. I'm hoping someone out there is willing to code up a proof of concept diff so we an see if this type of thing is doable.

Please also keep in mind that most servers are running on much faster hardware now then they were when titanium came out. What I am saying is that maybe at least having it would help the hardcore content builders who most likely have invested in decent hardware that could handle it without spiking the cpu (or the mysql daemon) too much.

Mysql has come a long way since version 4 as well. It's actually pretty shocking how well it handles a load nowadays.

Know what I mean?

Thanks for responding. Definitely food for thought. I'm hoping more people respond to this as well.

I truly believe that some world building features such as this (is the spawngroup addition to npcstats a godsend or what? simple yet SO useful, how could I live without it now?) would be a major step towards making content creation actually enjoyable instead of slave labor.

And while I LOVE your tools George, you have to admit that when plugged into a remote server, they tend to crawl. But they do not slow down the server much. My players certainly do not notice any lag from them. And they do hit the db pretty hard.

Granted, these # commands would generate queries in the server code itself... but it's just another thread, I really don't think the hit would be as hard as it may seem at first glance.

[gernblan]

Another possibility could be run to it (and commands like this) on a clone copy of the live db, so that it's not tearing into the live db, but actually calling another copy of it?

Can expand the eqemu_config.xml file to point to the clone. A script could be written to backup the live db and push a refresh copy to the clone on a schedule. I doubt the item tables and loot tables change SO fast that there would ever be a sync problem.

Heck another # command can be added to trigger that script manually even if someone wanted to force it outside of schedule.

Perl to the rescue!

[cavedude]

This will do it out of game, and it's not a bad query at all (about 2 seconds on my machine):

select distinct n.id, lt.loottable_id, n.Name, i.Name, s.zone, lt.probability/100*ld.chance/100
from npc_types n
inner join loottable_entries lt on n.loottable_id = lt.loottable_id
inner join lootdrop_entries ld on ld.lootdrop_id = lt.lootdrop_id
inner join items i on ld.item_id = i.id
inner join spawnentry se on n.id = se.npcID
inner join spawn2 s on se.spawngroupID = s.spawngroupID
where i.id = '1001'
order by n.id;

That will spit out the item id, loottable id, npc's name, item name, zone the npc is in, and total chance the item will drop (in decimal 1.0 = 100%, 0.04 = 4%)
Obviously, change the item number where 1001 is and you're set. Might want to output the results to a file, or change what it spits out. Eliminating the zone (and all the spawn group queries with it) will reduce the amount of results.

Damn, Wildcard you and I spend too much time talking our queries are nearly identical. Not many ways to do this I guess.

[cavedude]

Actually, zone and the spawn queries should be removed I didn't think about it at the time, but any NPCs that are spawned via quest will not show in the results :P

So:

select distinct n.id, lt.loottable_id, n.Name, i.Name, lt.probability/100*ld.chance/100
from npc_types n
inner join loottable_entries lt on n.loottable_id = lt.loottable_id
inner join lootdrop_entries ld on ld.lootdrop_id = lt.lootdrop_id
inner join items i on ld.item_id = i.id
where i.id = '1001'
order by n.id;

[gernblan]

Fantastic! Thanks!

That's better than nothing for sure!

[gernblan]

Since you were so kind to post that, I'll share this. This finds all loot drops by zone...

SELECT
  spawnentry.npcID,
  npc_types.loottable_id,
  loottable.name,
  loottable_entries.lootdrop_id,
  lootdrop.name,
  lootdrop_entries.item_id
FROM
  spawnentry
  INNER JOIN spawn2
    ON spawn2.spawngroupID = spawnentry.spawngroupID
  INNER JOIN npc_types
    ON npc_types.id = spawnentry.npcID
  INNER JOIN loottable
    ON loottable.id = npc_types.loottable_id
  INNER JOIN  loottable_entries
    ON loottable_entries.loottable_id = loottable.id
  INNER JOIN lootdrop
    ON lootdrop.id = loottable_entries.lootdrop_id
  INNER JOIN  lootdrop_entries
    ON lootdrop_entries.lootdrop_id = lootdrop.id
WHERE
  spawn2.zone='ZONESHORTNAMEGOESHERE'
ORDER BY
  lootdrop_entries.item_id,
  spawnentry.npcID,
  npc_types.loottable_id,
  loottable_entries.lootdrop_id;

[gernblan]

Thinking about this some more, I feel even more strongly that having that command in game would be a godsend. At this point I can only hope someone more capable than I will write it.