Request for new # command please

[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.

[Lalolyen]

My only recommendation is to have that command locked or only granted to the server-op by default, because if you have a few leads/mgmt's and admins working or just playing with that command, I can see it taking down the MySQL database really quick, even worse the server.

As Cavedude said, it took him 2 seconds to querey outside the server (assuming machine to machine) so some of that is overhead from the internet but keep in mind, a querey that takes longer than a second on a decent machine causes a decent amount of load multiply that by two or three at the same time, it could be enough to take a server on the verge of a 'strain' completely down.

Again I'd suggest locking that command down and even securing it, because I'm sure that could be an exploit waiting to happen.

[gernblan]

Well, just make it a status 300 command.. only the serverOP would get it then.


That's fine by me. Truly.

[gernblan]

...but please... someone, anyone... code up a proof of concept?

Even something as simple as "yes, item drops" or "no, item does not drop in game" would be huge, even if it doesn't tell you where.

Even the ability in game to be able to see if an item is being used in the loot tables at all would be better than nothing at all.