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]

Originally Posted by erik_llewellyn

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?