Request for new # command please

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

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