how to make these changes for a RP server

[RhinoDude]

I don't mean to cross-post, but I realize now that this forum is a far better location than what I posted yesterday in General : Server, and whereas that post was more for discussion, I want this to concentrate on the technical aspects.

In particular, I'm wondering if all of the following can be done at the database level with single SQL UPDATE and/or DELETE statements, and if so, can somebody point me in the right direction on each? Perhaps a rating of 1 to 5 on each, there 1 is trivialand 5 is herculean effort?

What I'm trying to do is roughly determine the amount of work ahead of me, and if somebody already has script to do one or more of these and if they could share and save me time.

Here is the list:

- remove NO DROP flag from all items
- remove QUEST flag from all items
- remove LORE flag from all items
- change mob/npc corpose rot rate to 24 hours real time (or never if possible)
- always force named mobs to spawn on zone pop where unnamed placeholders are used (this one is probably a lot of work?)

For outdoor and city zones only:
- change named mobs to never respawn if killed
For indoor dungeon zones only:
- change all mobs to never respawn

I'm not adverse to implementing one or more of these with stored proc logic, but what I need to know is if these can all be done at the db level, and if so are we talking a trivial to herculean effort to pull off?

For example, to permanently remove dungeon mobs so that they never respawn once killed, is there a way to automate this, or will I have to manually track the dead mob's somehow and then remove them from the db later when a gaming session is finished? Clearly a server code change could kill respawning, but if I can do it in the db through a trick like bumping the respawn rate to a point greater than our maximum game session length, then that would be fine. As long as for the next game session they are GONE.

Oh, I forgot to mention, I'd like to implement this in a way that is agnostic to a particular db or it's version (PEQ vs. others), and also I would like to do it all in SQL so that the scripts can be simply re-run against a new db drop to get it up to date. I am hoping that for most of the items above, this will be doable. The only I'm concerned about is the placeholder -> named spawn conversion. My fear is that will require specific NPC knowledge, and thus be a pain to implement, and a pain to maintain. But if I can write some smart sproc logic that analyzes the spawn condition / trigger metadata and figures out what triggers what, and can write some simple rules that handle things as expected 98% of the time, and the rest of the time I'll have to manually go in and check things out and fix it up by hand before a given adventure session starts.

Thanks in advance for any pointers...

- Rhino

[So_1337]

I'll do the easy ones now.

Remove NO DROP flag from all items:

UPDATE items SET nodrop = 1 WHERE nodrop = 0;

Remove QUEST flag from all items:

UPDATE items SET questitemflag = 0 WHERE questitemflag = 1;

Remove LORE flag from all items:

UPDATE items SET lore = 0 WHERE lore = 1;

Change NPC corpse rot rate to 24 hours real time:

UPDATE rule_values SET rule_value = 86400000 WHERE rule_name = 'NPC:MinorNPCCorpseDecayTimeMS';
UPDATE rule_values SET rule_value = 86400000 WHERE rule_name = 'NPC:MajorNPCCorpseDecayTimeMS';

More to come =P

[So_1337]

The rest are going to require a lot of time. A lot. The only easy one out of that above list is to change all mobs to never respawn for indoor dungeon zones. Take the following query and modify the zone name to whichever dungeon you choose:

UPDATE spawn2 SET respawntime = 31276800 WHERE zone = 'gukbottom';

That would give everything in the zone a respawn time of one year, which might as well be never.

And yes, these are all queries that you can save and source into any fresh database, so long as the column names match. Should be fine between either PEQ or Angelox's database. In fact, I run the query to remove the NO DROP flags every time I do an update to PEQ's latest database.

Hope this helps. Good luck with the rest, too much work for me to do. Or else I'm not thinking of an easier way to do them, which someone else could maybe help with.

[RhinoDude]

I figured those first 4 would be easy like that.

Take your time on the hard ones, I'm in no rush. As I said I don't mind figuring it out for myself, but if you have the time that's fantastic. I was more interested if a) can it be done at the db level at all, and b) what is the scope of the work.

For example, I assume some of these may be easier to do at the code level than at the db level. For example, when a mob is killed, I was thinking I could add a new "deadmob" table or similar, and populate it when the mob is toast, and then a back-end sproc can run at the end of each session to roll through the killed mobs and remove them from the db to prevent future spawns. Alternatively, the server code could immediately delete the named mob. But the problem with this is it changes the server code which I'd like to avoid if possible.

Or better yet, if enough info is aleady in the log files to do this, a simple program to parse the log files and then remove the items from the database could be another (perhps better) solution. This of course assumes that the log entires are rich enough to implement my necessary rules as defined. If the NPC ID is in the logs as well as it's name and zone name or ID I could quite easily write a program to do this in a few hours. I'd just have to hardcode zone names or ID's to a list of dungeon vs. non-dungeon zone, which is no big deal maintenance wise as the zone list isn't changing often.

I actually like this approach better as there may be rare occasions where I don't want to get rid of ALL of a dungeons mobs even if it was completely cleared out. I may want the named to go, but I may want to keep a few unnamed stragglers behind. Just enough to be annoying and act almost as "random" encounters, especially in those dungeon zones which act as a connection for multiple zones (BlackBurrow, RunnyEye, etc.). By using the log parsing approach, what I could do is write a program which simply generates SQL statements from the logs (if the logs are XML this could actually be triivial as a XSLT could perhaps do it). Then I can pare down the SQL by hand before executing it. In fact, in theory I could even be in the dungeon zone as GM, repop, delete a few, repop again, and see the changes on the fly. If the tool writes to stdout, it could still be used in an automated fashion for cases where I want everything wiped that was killed with no exceptions.

- Rhino

[AndMetal]

I think these might work a little better...

- remove NO DROP flag from all items

UPDATE items SET nodrop = 1

- remove QUEST flag from all items

This is only used in the Anniversary client, which isn't supported, so you won't have to worry about it.

- remove LORE flag from all items

UPDATE items SET loregroup = 0

As for the rest...

- always force named mobs to spawn on zone pop where unnamed placeholders are used (this one is probably a lot of work?)

If memory serves me (I don't do a lot of world building), this should really just involve tweaking of the spawnentry table. Last I knew, it contains the mobs, from the npc_types table, so just remove all of the other rows in the same spawn group. The page in the wiki about creating NPCs might help to understand the relationship between the tables a little better.

For outdoor and city zones only:
- change named mobs to never respawn if killed
For indoor dungeon zones only:
- change all mobs to never respawn

One way to do this would be to set the respawn timer to some ungodly high number. You might also be able to use -1 for the respawn time, although I'm not sure if that would actually work (not sure what the server looks for in the source from the DB). You could also use spawn_conditions, which honestly would probably be the best way to go, since if you change it to something different than what you use normally (1 vs 0 for example), nothing should spawn by default. In addition, you could create some custom NPCs that thank you for your previous help or something like that.

Hopefully this helps point you in the right direction, it sounds like a fun alternative to face-to-face D&D

[RhinoDude]

Thanks again So. These actually get me quite far. Thanks a ton!

I assume "spawn2" table means respawn? Is there some db documentation somewhere that defines the tables and cols? Or better yet a ER diagram?

Assuming I am correct, what drives initial spawning? The "spawn" table? If so, would deleting from that table once a named mob is killed prevent it from ever coming back even after server resets?

What about the idea of joining with the spawn2 and npc tables (directly or indirectly as needed...I am not familiar with this schema yet) and using a like clause where mob name NOT LIKE "a *" in order to delete all respawns of named mobs in non-dugeon zones? I realize this may involve joining quite a few tables to get from the spawn2 all the way back to where the npc name is defined, but I don't care if this is efficient. It can crawl for all I care. I will only do it once per new db deployment after all. If I had an ER diagram and schema definitions I could likely whip this up in a few minutes.

If that will work, then all I'm really left with is determining how to permanently delete mobs so they don't respawn after server reboot. I know now how to distinguish between dungeon and non-dungeon zones. I *think* I also know how to distinguish between named and unnamed mobs (please confirm the ie ("a Gnoll" vs. "Fippy Darkpaw"). So all I need to know is where initial zone repop spawn points are stored, and then figure out a way to pseudo-automagically generate DELETE statements to remove those spawn points for killed mob's, and I'll have it licked.

Of course it's probably not that easy, but I can dream.

Thanks again,
- Rhino

[So_1337]

Leave it to AndMetal to show me the proper way to do things =) I guess I just figured that only changing the ones that needed changed saved some time on running the query. But I bow to superior knowledge, hehe.

Sounds like you have some really interesting ideas, hope you're able to pull them off!

[RhinoDude]

Thanks you AndMetal, I didn't see the schema docs before. Now I'm all set.

I concur with your analysis for deleting unnamed placeholders. I think this is the algorithm. If I am understanding the schema correctly, all I need to do is roll through the spawngroup table and for each group, iterate through all spawnentries. If the group contains an entry for a named spawn (name != "a"??? pls confirm), then change the chance of that to 100%, and delete all other spawnentry for that spawngroup. The net result is all spawn groups with named mobs end up containing only that mob with a 100% chance of spawning.`

Is that the correct algorithm? If so that's about 15 mins of SQL coding tops. This should handle most cases, but clearly it won't work where multiple named mob's are in the same spawn group. I guess in that case I could simply set all named npc's to 100% chance, and then they will simply stack on top of each other. Kind of ugly. Perhaps a better solution would be to create a new spawn group for each alternate named spawn, and adjust the loc by an offset. Of course, doing this in a generic fashion that worked for every mob would be almost impossible. Some are larger than others, some are right next to a wall, etc. It would almost certainly require some manual effort in order to get it right in all cases. So maybe having them spawn on top of one another wouldn't be so bad. I guess before game session I could log in as GM and move them so they aren't stacked...

But I need somebody to confirm that for unnamed spawns that name col is "a" and lastname col is the name of the mob, ie "a Gnoll", "a Bixie", etc.

Thanks
- Rhino

[RhinoDude]

So to permanently kill all mobs in a dungeon once my adventurers have cleared it, could I simply do something like this?

DELETE FROM spawn2 WHERE zone = 'blackburrow'

Alternatively this would work, but only for spawns that had conditions, right?

UPDATE spawn_conditions
SET onchange = 0
WHERE zone = 'blackburrow'

If all spawns have conditions, that would be preferable, as it's easily reversed. But according to the schema, it seems as if a spawn point does not have to have a condition. So the only sure way to do it is to just delete them spawn points and the conditions.

Of course, I assume if yu are going to delete from spawn2, you should probably cascade delete all referenced rows in spawn_conditions.

I however am confused as to what spawn_entry is for. THere is no doc on that table...

- Rhino

[GeorgeS]

Deleting all npc's in blackburrow in spawn2 would work, but break spawnentry and spawngroup tables due to the links being broken in spawn2.

Delete the spawngroup in its entirety then it should be ok.


GeorgeS

[RhinoDude]

Originally Posted by GeorgeS

Delete the spawngroup in its entirety then it should be ok.
GeorgeS

Thanks again George.

Do you know if the npcID and spawngroupID is written out anywhere (lot files, eventlog table, etc.) when a mob is killed? This is really all the info I need. I assume at runtime the server must know the npcID and spawngroupID of the mob, and it seems like this would be useful information to have logged somwhere.

With this information I could easily write a back-end process or schedueld procedure which rolls through the list of deleted spawns, and delete the spawn entry from the group, and then delete the group and spawn2 itself if the group contains no more entries.

Also does anybody know of a fool proof way to consult the npctype table and determine if a NPC is named or not? From consultuing the db, it seems that simply relying on names that start with "a " the "the " is going to be insufficient.

- Rhino