Updating Database

[AsmoTiC]

So that I don't derail another thread any further, had a couple questions about updating an existing database which has a couple custom modifications.

I've been running off the old August 2015 PEQ database, and would like to come up with a process to keep it up-to-date with the weekly dump PEQ does. I have a small handful of custom entries that i'd like to preserve, but need a little guidance on creating the SQL.

If I understand the process correctly, from reading the readme.txt file that comes with the weekly dump. After creating a full backup of the database you'd source "drop_system.sql", after which you could then source "peqbeta.sql". I assume login server accounts, characters, and bots/healing rotations are all left intact.

So, I have a single custom item, which is sold from an existing merchant in pok. I have 2 quest npc's in the guild lobby for handing out starting gear (the quest files won't change). Finally, I moved the succor point in Sirens Grotto so you don't end up in the middle of 3 mobs.

My thought is, i'd just create a custom SQL with those changes and source it in after I source "peqbeta.sql". Question is, where in the DB would I look for these changes. I used the peqeditor to make them, so not sure exactly what tables each would be in.

Appreciate any help. In addition, any example of SQL Syntax for the merchant list would be great. Pretty sure once I know what table the new npcs are under and the new item, they would be straight forward, but I believe the syntax for modifying an existing tables value (succor point in SG for example) is different than just creating a new entry.

Thanks again guys!

edit - Quick addition. The two NPC's in the Guild Lobby have spawn group information as well. In addition, if the server rules are set, having a way to quickly change them back to the existing ones would be nice too. Probably obvious, but thought i'd mention it quickly.

[AsmoTiC]

Just a follow up, incase anybody searches and finds this post.

There may be an easier way, if there is, perhaps someone can chime in.

Using Heidi I found my custom entries and using the right-click context menu exported the grid to an SQL file. For anybody that's interested, you are given the option to export as a new entry, or a replacement entry. So in the case of my Sirens Grotto safe point change, I used a replace; where the new item and merchantlist entries were inserts (new).

As far as the server rules go, I ended up just exporting the whole "rule_values" table to a SQL file all it's own.

Anyway, from there the process was simply; source "drop_system.sql", source "peqbeta.sql", source "custom.sql" (the file created above), and finally source "rules.sql". No errors/warnings/etc. Pretty clean process, something easy enough to do each time you'd like to update to the newest PEQ dump. Even easier if you stay ontop of the DB changes as you make them and just keep the custom.sql current.

Now with all that said, I did have one problem. Hopefully someone could offer advice. My two new NPC's didn't update, so I ended up reverting back to my backup. What all tables do I need to export so they are in the world? I exported each npc from the "npc_types" table, I exported both "spawngroup" values, and both "spawnentry" values. When I restarted the server and checked on them, they wern't up. Did I miss a table somewhere?

Hopefully this will help someone who has custom entries in the DB, but still wants to stay current with PEQ.

[AsmoTiC]

Now with all that said, I did have one problem. Hopefully someone could offer advice. My two new NPC's didn't update, so I ended up reverting back to my backup. What all tables do I need to export so they are in the world? I exported each npc from the "npc_types" table, I exported both "spawngroup" values, and both "spawnentry" values. When I restarted the server and checked on them, they wern't up. Did I miss a table somewhere?


Incase anybody ever uses this as a rough guide for doing the same type of thing, I figured i'd post back what I did to fix my two missing NPCS.

Sure enough I missed the entries in the "spawn2" table. Once I exported them and merged them into the custom.sql, the whole process ended up working out. So if you have custom NPC's you want to save you'll need to grab the NPC data, and all Spawn data.

Here are the tables that had everything associated with them:
npc_types, spawngroup, spawnentry, and spawn2.

Again, ended up answering all my own questions over the course of a couple days. Hopefully anybody that does a search will find the thread and get some direction from it.

[DanCanDo]

I suppose that was feasible in your situation, but 2 custom npc's would only take me
about 4 minutes to re-create. I do all that in-game with #gm cmds.
Using #spawn > #npcspawn create > #repop and then #npcedit (for everything else)
When I need to find a custom npc in the database, I use Navicat and open the npc_types
table, go to last page and the custom npcs are always at the end of that.
I use George's Tools for spawn group editing, loot, etc. Only takes a minute or so for one
npc. Changes happen with the click of a mouse (chuckle).
Two days ago, I kept all tools open, gm client logged in and created 40 npc's
within about 3 hours. In game, #wpadd comes in really handy for pathing.
I like to do as much editing as I can in-game with gm, as I get to see the
results instantly.
Of course, each day, I do a mysqldump to back up the database, so I don't
waste all the work I did.