SQL DIFFs??? How.

[davood3]

Basically I want to use the latest peq, but..

I have lots of changes to the spawn and loot tables, but only in the zones that are "done" in peq, so they wouldnt be affected with the changes.

I want to first create a diff from the old "base" peq I used to make my custom world, and compare it to the "new" peq.

And I want to take those differences and run them as an update on my current "peq" database.

I made a thread in the wrong forum (i just realzed).

Here:
http://www.eqemulator.net/forums/showthread.php?t=25362

Where I experimented with log files.

If I do find a solution, I will explain in detail how I came to it for the other sql-challenged people like myself.

I think that the solution will be doing a diff using navicat on the first peq vs the new peq, and somehow automagically cleaning the log file so i can use all of those sql queries to update "my" peq db. I just can't get the log files clean/reliable enough.

[Bulle]

Unless you can find an existing tool for that it is not an easy problem. I have been working on a world "configurator" allowing to reload PEQ while re-applying your changes afterwards, but it is very much a work-in-progress, and considering how my job has ambushed my free time I guess it will stay so for a while.

I am interested if you find a solution to this.

[erik_llewellyn]

While this may not be a fancy nor a quick-to-implement solution if you didn't keep track of your updates as you went, I do find it works 100% of the time. It only requires me to run 1 SQL and 1 batch file after updating to the latest and greatest PEQ distro.

Every "customization" I have on my server is typed into notepad and now consists of over 25,000 entries. All it needs is updating as new database changes come out with the server code or if I want to add new content. The batch file copies all my custom quests from my custom content repository into the appropriate quest folders in the PEQ quest folder.

I decided on this approch after re-typing my customizations a few times and said never again! It took some effort to type it all in the first time, but saves soooo much time now that it's well worth the day I spent creating the file/query. It now only takes about 5 mins after I update the DB to PEQ's newest to have all the custom content back in place.

[davood3]

That is a good design decision.

and I do technically have most of my custom stuff in excel spreadsheets and notepad files. I could easily build my own "update" file

However... when I was editing npcs.. I used a combination of George's tool and ingame spawning., that doesn't show you any sql, so I never learned how all the spawn and merchant stuff is linked in the tables, and I don't know what exactly I have added, table-wise.

So even if I build the other stuff, I am left with the painful truth of the spawn tables that I have tinkered with. I have all of the commands I used to spawn the npcs stored in a notepad file; but I don't have locs or headings, or special npc properties.

[cavedude]

Every "customization" I have on my server is typed into notepad and now consists of over 25,000 entries. All it needs is updating as new database changes come out with the server code or if I want to add new content. The batch file copies all my custom quests from my custom content repository into the appropriate quest folders in the PEQ quest folder.

That's probably the best approach, as there is no real, accurate way to diff the data in two sql files. It's the holy grail of db development. If somebody could write up a diffing program made specifically with databases in mind, I know a lot of people would love them forever. It certainly would make both my real life job and my job here much easier.

[davood3]

here's the thing..

navicat DOES it.. however it doesn't give you the sql commands that it uses to do it.

I want to "capture" those commands as navicat does its dirty work so i can apply it to a different database than teh one navicat is working on.

I have been busy with work / exams recently. but tonight or tommorrow i will have time to spend more time smacking the log filing system around to see if i can generate some clean navicat logs with mysql server

I think the "update" logs are the ones I want right?

[quadroplex]

of course you know, saving your changes as you make them would be far simpler and take alot less time then hunting down a way to diff a sql file. I mean why would you make changes to your database without saving those changes somewhere other then inside your active db?

[quadroplex]

What I mean is I am making new zones in openzone, when I add them to the db i save sql files for each new zone, the spawns, doors zone, zonepoints, objects and so forth... Why didnt you do the same? What would happen if you had to totally reinstall everything? All that hard work would be gone forever leaving you at square one... Golden rule is Save Everything

[davood3]

to do that i'd need to do all my merchants and npc spawns via sql instead of the fancy tools or ingame convenience.

[quadroplex]

not if you do it then backup the table when your done with a simple where x = 'y' type of statement its not hard to backup things in mysql. I use fancy tools and ingame commands but I have current backups of everything I change, as long as you know the id or the name you can easily backup the information, even if you dont, you can just backup the entire table and restore it after you upgrade

[TheLieka]

Hindsight is 20/20, I suppose. We've had this problem as well in the past. We make our customizations with no intention of ever merging with another database, then suddenly - omg, something new and wonderful gets released in PEQ, so we have to go back and figure out what we've got vs what they've got - it's enough to make your head spin. For the record, we never we able to find an elegant way to do it.

If you turn on logging with the PEQ Editor, and only make your database modifications through it - it will generate a log file with all of the SQL statements for the changes that you make. If you don't have those logs though - I don't have any clue how to do it with the currently available tools.

Dax

[Theeper]

You could enable the general query log in MySQL when modifying the DB and then parse the INSERT, UPDATE and DELETE queries out.

[eq4me]

I goggled phpsqldiff (http://phpsqldiff.sourceforge.net/). Seems pretty nice at first glance.

Otherwise you could give the old fashioned programmers way a try:

To attempt to patch your modified DB to the latest and greatest PEQ release I would try the following:
Make yourself familiar with the GNU patch and diff commands. These are available under Unix/Linux and Cygwin.
Install the PEQ release your modifications are based on into a different database anf dump it. It's crucial that every line of the dump has the INSERT command. Even so you might stumble over CREATE TABLE parts(that's why I recommend setting at least -u20 for the diff command)
Install the newest PEQ release into another database anf dump it too.
Diff the two dumps to make a patch file.
Check the patch file for any rule changes that might have impact on your server.
Dump your customized DB and try to apply the generated patch file on it.

Of course you could also make an diff of your modified database to the one it is based on and try to apply it to a recent release. But you'd have to worry about your user tables.
Once again, this is just I would try and will certainly need quite some editing on the resulting patch. Maybe it would be simpler do a diff per table and not the whole database.

Btw.: I had a chat with our resident database guru and he said to turn on logging for the relevant tables. To do that under MySQL you have to use a trick since you only can enable logging on whole databases(-> major performance impact). I might not have understood it correctly cause he has an heavy russian accent, but I think me meant symlinking/linking the relevant tables into another database.