Request for new # command please

[gernblan]

Wouldn't it be great if you could type something like...

#showdrops <itemnumber>

IN game and it will tell you which mobs have it in their loot table?

The output could be something like:

Item 12345 drops:
NPC- Yaka Razorhoof -- Loottable- 16454
NPC- a rat -- Loottable- 1055
2 NPCs found.

or if it doesn't currently drop in game...

Item 12345 drops:
0 NPCs found. 0 Loottables found.

Imagine how incredible this would be when world building in game. You can browse items and see if they are already dropping, or not, or where and off of who.

I mean it would make things so much more pleasant...

Thoughts?

Anyone a hell of a lot btter than me with C++ want to take a stab at this?

Thanks for reading.

[Lalolyen]

Wouldn't it be great if you could type something like...

#showdrops <itemnumber>

IN game and it will tell you which mobs have it in their loot table?

The output could be something like:

Item 12345 drops:
NPC- Yaka Razorhoof -- Loottable- 16454
NPC- a rat -- Loottable- 1055
2 NPCs found.

or if it doesn't currently drop in game...

Item 12345 drops:
0 NPCs found. 0 Loottables found.

Imagine how incredible this would be when world building in game. You can browse items and see if they are already dropping, or not, or where and off of who.

I mean it would make things so much more pleasant...

Thoughts?

Anyone a hell of a lot btter than me with C++ want to take a stab at this?

Thanks for reading.

Yeah its an excellent idea, but that command would put one hell of a load on the server =S.

You see that would be a reverse lookup forcing the server to check every record it has for that item across every zone (as most searches and queries are just within a zone).

And if you are talking about just pulling that from the database, thats another potentially huge querey.

Great concept, and yes it would REALLY help in world building, but I wouldn't recommend using that kind of command on a live server or allowing too many people to use it.

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

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

[krusher]

I have begun work on this request. Will keep you posted as i progress...

Krusher

[gernblan]

Thank You!

[Magoth78]

I've coded the function the way you requested.


Shot:
http://eqoffline.free.fr/showdrops.jpg


Code:
1/ Add the prototype in the command.h

void command_showdrops(Client *c, const Seperator *sep);


2/ In command.cpp
2.1/ Find the line:
command_add("refundaa", "Refunds your target's AA points, will disconnect them in the process as well.", 100, command_refundaa)
2.2/Replace by

command_add("refundaa", "Refunds your target's AA points, will disconnect them in the process as well.", 100, command_refundaa) ||
command_add("showdrops"," Usage #showdrops [itemID]", 100, command_showdrops)

2.3/At the end of the file, add the function's code

void command_showdrops(Client *c, const Seperator *sep)
{
if(sep->arg[1][0] == '\0'){
c->Message(10, "Bad argument, usage #showdrops [itemID].");
return;
}

int itemID = atoi(sep->arg[1]);

char errbuf[MYSQL_ERRMSG_SIZE];
char *query = 0;

MYSQL_RES *result_lootdrop_id;
MYSQL_RES *result_lootdtable_id;
MYSQL_RES *result_npc_name;

MYSQL_ROW row_lootdrop;
MYSQL_ROW row_loottable;
MYSQL_ROW row_npc_name;

int npc_numbers=0;

c->Message(0,"Item %i drops:", itemID);

if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT lootdrop_id from lootdrop_entries where item_id=%i",itemID), errbuf, &result_lootdrop_id))
{
while ((row_lootdrop = mysql_fetch_row(result_lootdrop_id)))
{
npc_numbers++;
if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT loottable_id from loottable_entries where lootdrop_id=%i",atoi(row_lootdrop[0])), errbuf, &result_lootdtable_id))
{

while ((row_loottable = mysql_fetch_row(result_lootdtable_id)))
{
if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT name from loottable where id=%i",atoi(row_loottable[0])), errbuf, &result_npc_name))
{
while ((row_npc_name = mysql_fetch_row(result_npc_name)))
{
c->Message(0, "NPC- %s -- Loottable: %i",row_npc_name[0],atoi(row_loottable[0]));
}
}
}
}
}
}
if(npc_numbers == 0)
c->Message(0,"0 NPC found. 0 Loottable found.");
else
c->Message(0,"%i NPC(s) found.",npc_numbers);

}


Have fun,
Mag

[John Adams]

Thank you, Magoth! If for nothing else, a little tutorial on how to play with commands. :)

[gernblan]

THANK YOU! SO MUCH!

Will play with this and if it works great, maybe it can get added to the tree :)

[gernblan]

Ok this is FLAT OUT incredible!

THANK YOU.

And yes, I too learned something about how new commands are made. THANKS on all counts!

Now, I can only pray this gets into the tree so that I do not have to diff it every build... :)

You posting that like that really helps out those of us who can "read the language but not speak the language that well (yet)" understand this stuff at a whole new level.

So, thank you for your time.

Can you tell I am grateful? lol

[gernblan]

One more thing:

THANK YOU AGAIN

(lol)

[gernblan]

Won't let me post code for some reason BUT it's bombimg out on the compile... vs. build 1052.

Will double check and see if it's me or not.

[gernblan]

Ok this:

command_add("showdrops"," Usage #showdrops [itemID]", 100, command_showdrops)

...needs to be this...

command_add("showdrops"," Usage #showdrops [itemID]", 100, command_showdrops) ||

[gernblan]

It's also showing the loot table name instead of the NPC name for some reason.

Please see screenshot:

http://media.buttwrenching.com/loot.jpg

[Magoth78]

If you want to see the name of the NPC showed instead of the name of the Lootable, then change the 2.3 of my previous answer to:

2.3/At the end of the file (command.cpp), add the function's code

void command_showdrops(Client *c, const Seperator *sep)
{
if(sep->arg[1][0] == '\0'){
c->Message(10, "Bad argument, usage #showdrops [itemID].");
return;
}

int itemID = atoi(sep->arg[1]);

char errbuf[MYSQL_ERRMSG_SIZE];
char *query = 0;

MYSQL_RES *result_lootdrop_id;
MYSQL_RES *result_lootdtable_id;
MYSQL_RES *result_npc_name;

MYSQL_ROW row_lootdrop;
MYSQL_ROW row_loottable;
MYSQL_ROW row_npc_name;

int npc_numbers=0;

c->Message(0,"Item %i drops:", itemID);

if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT lootdrop_id from lootdrop_entries where item_id=%i",itemID), errbuf, &result_lootdrop_id))
{
while ((row_lootdrop = mysql_fetch_row(result_lootdrop_id)))
{
if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT loottable_id from loottable_entries where lootdrop_id=%i",atoi(row_lootdrop[0])), errbuf, &result_lootdtable_id))
{

while ((row_loottable = mysql_fetch_row(result_lootdtable_id)))
{
if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT name from npc_types where loottable_id=%i",atoi(row_loottable[0])), errbuf, &result_npc_name))
{
while ((row_npc_name = mysql_fetch_row(result_npc_name)))
{
c->Message(0, "NPC- %s -- Loottable: %i",row_npc_name[0],atoi(row_loottable[0]));
npc_numbers++;
}
}
}
}
}
}
if(npc_numbers == 0)
c->Message(0,"0 NPC found. 0 Loottable found.");
else
c->Message(0,"%i NPC(s) found.",npc_numbers);

}


Mag

[gernblan]

Thank you very much!!

This is perfect :)

No clue how I could ever live without this now. I hope it makes it into the tree.

[gernblan]

Been using this for two weeks now, it's incredible, still :) SO useful.

Please, someone, stick it in the tree hehe.

Just set the command for 200+ status or whatever.

[bruise]

I apologize for reviving an old thread but I was hoping someone could get this to work. I added the code exactly as shown and compiled it successfully but it's not working. I very well could have compiled it incorrectly though.

Edited the command.h and the command.cpp files located in c:\EQEmuServerFiles\trunk\EQEmuServer\Zone

Saved those changes, then I opened Visual C++ 2008 Express Edition, opened the server.sln project. Highlighted "zone" then Build>Project Only>Rebuild only Zone and it completes successfully. I start up the server and nada, doesn't work. Am I missing something?

[trevius]

Did you make sure to copy the newly created files to your server folder after you compiled?

[bruise]

I just editted the files in the "c:\EQEmuServerFiles\trunk\EQEmuServer\Zone" directory and saved them there. Should I copy them to c:\eqemu folder or something?

[bruise]

Oh what a glorious day.. I figured it out thanks to your sentence structure lol. newly created files .. THAT'S what I wasn't doing! I had no clue how compiling worked and really was just taking guesses. I thought once you click "rebuild solution" that you could then start up your server and bam, you're all set! The KEY was then copying over the newly modified file (zones.exe in my case) to my server folder and wham, bam, thank you Trevius! Again, 1,000 thanks for your help!