PEQDB inquiry.

vales · #1 06-03-2009, 03:58 PM

Hey, CaveDude.

I was meaning to ask if you could update the PEQDB with additional SQL query updates instead of uploading the whole database. This way, it's much more efficient to keep modifications and is lighter on your resources. We're always updating the whole thing instead of sourcing in only the database updates. Not the updates like Rev610.sql in the updates folder, but rather the actual sql query just for the database like items, mobs, etc.

If you could just post the difference files as far as updates are concerned, then it would be much easier.

The way I've learned to keep database updates in order was to have SQL entries from, for example, Rev10 to Rev20, was to post just the updates for those that already have Rev10 running, but post the sql data relevant up to Rev20.

This way, people can update it manually, source in additional tweaks and stuff without having to source in a completely full database before adding in updates again. Then, you could also have a full Rev20 for download for those that haven't used Rev10 yet. This way, it's much easier to also keep track of errenous files and issue a quickfix without breaking the whole database in the process or sourcing in the whole thing again when updating.

Breaking up the SQL files into categories would be ideal as well. For example, there's already sql entries for characters.sql, load_system.sql, etc. But in a Rev20 folder for example, there would be an item.sql that contained all of the item updates from Rev10 to Rev20, a mobs.sql that contained all of the mob updates, etc. This way, it's easier to pick and choose and also keep track of fixes much, much easier.

If people want to customize their servers without adding in let's say, adventure merchants, then they can immediately pick out what they don't want by commenting it out and then just sourcing in the rest.

**AndMetal** · #2 06-03-2009, 04:12 PM

Just a thought, but I wonder if it would be possible to have BOTH the way it's done now PLUS a repository for Revision to Revision updates? The latter is a pure nightmare for new servers (reminds me of setting up Mangos for the first time), but is almost a must for older servers with custom content. Then again, it might encourage server ops to be lazy & not use SQL files for their customizations.

Thoughts?

vales · #3 06-03-2009, 05:27 PM

Yeah - that's where the idea comes from. At first, the database team had a heck of a time trying to organize everything and how it would work out for the benefit of everyone. Fast forward a few years later, and now everything is working flawlessly. The problem was catering to the MaNGOS team's fickle way of handling the structure of the game. If they change something that required a datbase change for functionality, a tiny SVN update was all that it took.

The system we have in place for updating databases is super easy. And trust me, we've had our share of database nightmares. Submitting the small database updates in increments weekly or bi-weekly is a much easier task as well, for those that want to stay current. For those that want to start fresh, all they had to do was source in the main sql file with all of the tables and play.

**GeorgeS** · #4 06-03-2009, 10:01 PM

I think one problem is due to all the tables inter-connectivity, keeping track of changes without breaking stuff is really pretty hard. I would know.

But I do support this idea though...

GeorgeS

**cavedude** · #5 06-04-2009, 02:30 AM

Hundreds sometimes even thousands of database changes go in daily by several of us, at a mysql prompt, through the PHP editor, and even in-game. While everything is logged, it would not be feasible for me to round them all up and put them into update files. I'm lazy about the current system as is which I've made largely automatic.

Separating the tables isn't a great idea, because as GeorgeS said many tables inter-connect making it real easy to make a mess of the DB if an update gets fudged.

I like the single file mainly because it is as fool proof as one can get with DB sourcing. You don't have to worry about missed or outdated updates, conflicting data, etc. People manage to mess up the current system as is, I couldn't imagine the support requests I'd get if I separate the files or offer individual content updates.

Irreverent · #6 06-04-2009, 11:32 AM

Considering my databases is one of the most custom out there, I have learned to live with the single-file that CD uses.

What I have done is keep two different files, "Checklists", that I use. One is for code, one is for perl.

I just whipe the tables / file folders, load the quests or tables, then perform the checklist.

so in my database checklist, I have several hundreds of rows of SQL that will modify WHATEVER is in the new database back to where I need it.

For my custom npcs/items I have a different schema, to which in my checklist it has SQL to load those rows into the new database and then alter as nessecary.

That way, its VERY easy for me to keep in sync with ANY reload. I just load the script, hit run...and minutes later I'm running with the cool new db or quests version.

I would reccomend you do the same, because he's busy enough keeping his server running for his rules. If you want to alter it, its really up to oneself to do it. I'd rather not slow down CD so his database gets updated faster...and I can quickly keep in sync.

Plus, if I do something that is EQLive...(like the 85 new fableds I hacked), share it with CD and he'll load it into his database.

vales · #7 06-04-2009, 01:07 PM

Quote:

Originally Posted by cavedude

Hundreds sometimes even thousands of database changes go in daily by several of us, at a mysql prompt, through the PHP editor, and even in-game. While everything is logged, it would not be feasible for me to round them all up and put them into update files. I'm lazy about the current system as is which I've made largely automatic.

Separating the tables isn't a great idea, because as GeorgeS said many tables inter-connect making it real easy to make a mess of the DB if an update gets fudged.

I like the single file mainly because it is as fool proof as one can get with DB sourcing. You don't have to worry about missed or outdated updates, conflicting data, etc. People manage to mess up the current system as is, I couldn't imagine the support requests I'd get if I separate the files or offer individual content updates.

Ah, okay then.

Sounds fair enough.

**GeorgeS** · #8 06-04-2009, 10:01 PM

Ok I thought this out some more, and it's doable with the current schema IF you do the following. You create a flag field in NPC_TYPES called "CUSTOM" and whenever you make a custom change in the table, the flag becomes = TRUE, you can export that NPC and all dependant tables with either a perl script or .net code. It's pretty clear to me how it should work, and I'm pretty sure it will. IF you want something like this, I can code a backup and restore script.

When you load a new PEQ db from scratch, you can restore by running the script you made earlier, restoring your custom npc's. This can even be applied to items as well.

GeorgeS

vales · #9 06-05-2009, 02:50 AM

That would be most awesome if you could do that, George.