how to make these changes for a RP server

[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

[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