Hollowshade Moore watch

[Angelox]

PART ONE

With my last ax_classic update (2j), i have a working Hollowshade Moore zone, not just "working", but actually has part of the on-going tribal wars active, and not all the three tribes are spawned at the same time. I might finish it up one day, but for now, this is what I got.
First thing I had to do, was form the three spawn groups: grimlins, wolves, owlbears. Hollowshade zone id is 166, so NPCs there start with 166 (an_owlbear_bonegrinder is 166039). I wanted the spawngroup, spawnentry spawn2, id's to also fall under 166xxxx -since 1660000-1669999 in all these tables were not used, i set myself to move them there, and further group the numbers into owlbears, grimlins, and wolves. The in-game #spawn commands and the PEQ editor, or any other editor for that matter, always pick up id numbers from the highest number in the table, the 166xxx numbers are probably still available in all the databases, PEQ included - so you can do this with whatever database you have too. I did this, so I could have an organized picture of what i was going to do, and it will also be much easier for anyone else who wants to work on this zone (just have to look for the 166xxxx spawns and npcs).

UPDATE spawn2 SET id=id+1291864 WHERE (id>=368136 AND id<=368404);
UPDATE spawn2 SET id=id+1282531 WHERE (id>=377738 AND id<=377729);
UPDATE spawn2 SET id=id+1281768 WHERE (id>=378503 AND id<=378549);

UPDATE spawn2 SET spawngroupID=spawngroupID+331999 WHERE (spawngroupID>=1328001 AND spawngroupID<=1328268);
UPDATE spawn2 SET spawngroupID=spawngroupID+226970 WHERE (spawngroupID>=1433299 AND spawngroupID<=1433300);
UPDATE spawn2 SET spawngroupID=spawngroupID-31929767 WHERE (spawngroupID>=33590038 AND spawngroupID<=33590071);

UPDATE spawngroup SET ID=ID+331999 WHERE (ID>=1328001 AND ID<=1328268);
UPDATE spawngroup SET ID=ID+226970 WHERE (ID>=1433299 AND ID<=1433300);
UPDATE spawngroup SET ID=ID-31929767 WHERE (ID>=33590038 AND ID<=33590071);

UPDATE spawnentry SET spawngroupID=spawngroupID+331999 WHERE (spawngroupID>=1328001 AND spawngroupID<=1328268);
UPDATE spawnentry SET spawngroupID=spawngroupID+226970 WHERE (spawngroupID>=1433299 AND spawngroupID<=1433300);
UPDATE spawnentry SET spawngroupID=spawngroupID-31929767 WHERE (spawngroupID>=33590038 AND spawngroupID<=33590071);

This will re-number them to the 166xxxx area
Only thing is, these npc's 33590038-33590071 (the last line), do not exist in the PEQ database, these are named and extras that I added.

You could also just dump/make an SQL of what I did, then source it into the PEQ database, but you'd still have to delete the old spawns if you don't re-number.
But you won't need to, because I'll post an SQL that will update any database, when I'm done with this explanation.

EDIT: there was one spawngroup with all of the NPCs combined - I basically made three copys (two additional) of these spawn groups, then removed the other NPC tribes leaving one outstanding tribe. After that, I renumbered the extra copies with the new spawngroups and and set them to spawn with assigned _condition values, so they would spawn when needed.
MORE TO COME

[Angelox]

UPDATE spawnentry SET spawngroupID=spawngroupID+331999 WHERE (spawngroupID>=1328001 AND spawngroupID<=1328268);

How does this work?
the bottom number in the original spawngroupID is 1328001 if you add 331999 to it, you get 1660000. next number (as per code mentioned) will be 1328001; add 331999 , you get 1660001, all get changed by one up to 1328268 ( last should be 1660268 ).

[gernblan]

Renumbering and "packing" spawngroups back down to reasonable numbers db-wide would be an INCREDIBLE THING.

[John Adams]

I've been screaming and crying about the horrific indexes in the millions for over a year. I too am very happy to see this making sense to someone other than me.

[gernblan]

We're all in pain, John. Every one of us.

It hurts hehe.

[Angelox]

If anyone needs any help understanding this, go ahead and ask - at one time my database was "burning memory" since I had a plan to classify everything (loots included) by zoneid the loot numbers were too high, and caused everything to eat up memory. After a lot of thinking, not to mention I have a good friend that "tutors" me through situations like these (he's not a "MONKEY" programmer like me ), I learned this method, and it's been very helpful for many situations.

[gernblan]

I'm just TERRIBLY worried about links across tables breaking. I really appreciate your queries and for sharing them with us.

My fear with using them though is that I may get a collision, and then have a half-processed query or something.

I just think that if we all work togethe,r maybe we can use some more advanced SQl to not only combine some of those statements, but to do checking and stuff to make sure the numbers are being changed to unique values and stuff beforehand...

There used to be a fantastic graphic layout of the DB structure I remember seeing somewhere. Does anyone know if there is a current one?

I'm MORE THAN willing to help (perhaps work with you?) to come up with some queries for public consumption that can help people renumber their databases (in other words, "pack" and make sense out of" the IDs).

I just feel like I am flying blind.. because no matter how hard I try to understand the db structure (and I do to an extent), I'm ALWAYS afraid I'm going to miss something.

Another thing that we may wish to think about, consider, or even address... I wonder if there is a way to approach the way these things are created in the first place.

I'd certainly be willing to trade a BIT of performance for a more sane way of creating these IDs in the first place (maybe some code that allows you to override these values upon creation IF you want to... or even some code that searches and tries to create the IDs intelligently in the zone you're in.. or whatever)...

[Angelox]

This is really not that urgent or needed atm. The database works pretty good as is. In my case, I just had a bad idea (classify the loot tables into too large numbers), and was able to fix it. With a lot of thought, it probably could be done properly.
After a lot of work with the databases, you start to "get the big picture" on how it all works, and what needs what. I use a lot of tools when I do db work, but the one i most use and recommend is the "mysql-query-browser", for its simplicity and usefulness. mysql-query-browser is one for you to start with, and will always find use, even when you become advanced.
The database needs to be cleaned up and organized mainly so you don't have to be a "MySql Guru" in order to find/change things. It will be hard to do this, as first thing that would need fixing are things like the in-game #spawn commands (they don't look at the zone you're in, they only look at what the last number was) and editors (PEQ Editor). Even if we did organize the database, next person that #spawns something or uses an editor for adding loot, will have it all out of place again.
When I work with spawns and drops, I'm now aware of what happens, so I at least try not to make things worse. In Hollowshade, I cleaned it up, organized it before I started (as best I could). If you do look at that zone, it will be easier to make some sense out of it all.
Then, there's something else: What to do with your fixes and how to make them useful to everyone? Because if you do it just for yourself, it will soon become forgotten. There are three "public" database; EQ, AX_CLASSIC, and PEQ. EQ is the original database that Cavedude made public for all of us to use, Cavedude has since turned to PEQ and is supporting their database, so making/posting a SQL for EQ DB is a bad idea, since he is not building on it anymore. AX_CLASSIC is my DB , which I originally started (AX_PEQ) and stubbornly maintained mostly by myself, because at the time (lately, I just don't know how to give it up), no one was doing anything, and I wanted to do something. I'm "running out of steam", and am looking at ways to port my stuff over to the PEQ database. Already have ported all my LoY work, Hollowshade, and more. PEQ is the way to go, everyone that wants to do "public" work, goes there - and there is no need for any "forks" of any kind. At PEQ. there actually is a working team which is more than anyone has ever done in the database area. So, if you do manage to make some changes, do it under PEQ and run it by Cavedude, he will work it in. The way to do this, is to have a SQL available for him to look at, test, and finally source in. You can post the SQL here, we can verify it for you.

[GeorgeS]

Part of the problem was asignment of very high id's in tables when inserting records. I'm pretty sure MySQL does a Select MAX(field)+1 when running an insert query, thus a table like spawngroup has very high spawngroup.id's.
The highest one I have is '33,590,086'!. So a while ago I had considered a tool to 'pack the data', but realised some people had id's ranged in specific groups so they could identify npc's to zones etc.. This in effect killed the idea. Packing is not hard, but tricky because there are many possibilities of breaking tables irrevocably. I can write a tool to 'pack' specific tables, but then it may never get used.

GeorgeS

[John Adams]

I wish I could find the old debates between Angelox and myself about index IDs. Just once I'd like him to acknowledge "John Adams was right a year ago". But that'll never happen.

I'm still happy.

[Angelox]

Originally Posted by John Adams

I wish I could find the old debates between Angelox and myself about index IDs. Just once I'd like him to acknowledge "John Adams was right a year ago". But that'll never happen.

I'm still happy.

You mean this?
http://www.eqemulator.net/forums/sho...455#post125455
or this?
http://www.eqemulator.net/forums/sho...462#post125462