Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Database/World Building

Development::Database/World Building World Building forum, dedicated to the EQEmu MySQL Database. Post partial/complete databases for spawns, items, etc.

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #12  
Old 12-17-2006, 07:07 AM
John Adams
Demi-God
 
Join Date: Jul 2006
Posts: 1,552
Default

Table Indexing 101: (this is by no means a comprehensive look at Indexing, but I hope it helps a little)

FYI, look at the Indexes on a particular table. You should see things like PRIMARY, UNIQUE or plain ol INDEX. There can only be one Primary index per table and is always UNIQUE by nature. In DOORS for instance, PK (primary key) is [id:int(11)], and it is set to auto-increment (but PKs do not have to be numeric, or auto-increment, but that's for another discussion). In this, you never really *need* to put it in your values to generate it. The database looks at it's highest number, increments by 1, and stuffs that value in for you during an insert.

UNIQUE is another type of index and can also be a single field, or combo of fields in a table. For example, if you tried to insert 2 doors like this:

insert into doors (doorid, zone) VALUES (1, 'poknowledge');
insert into doors (doorid, zone) VALUES (2, 'poknowledge');

...that would comply with the UNIQUE index of this table. However, if you tried this query:

insert into doors (doorid, zone) VALUES (1, 'poknowledge');
insert into doors (doorid, zone) VALUES (1, 'poknowledge');

...you would get a duplicate entry error and the query would fail at the offending insert.

~breathes~

Now, if you encounter an error inserting anyone's additional data, using the info above, anyone should be able to troubleshoot why and fix the dataset to suit their own needs. For my own inserts up there, my DOORIDs weren't thought out completely. I just incremented blindly passed the max poknowledge doorids, even for guild lobby and guild hall - which I shouldn't have done (no harm, just not consistent). I should have started guildlobby and guildhall doorids at 1 (or 0, if you're a purist).

A fix to my doorids is here:
Code:
INSERT INTO doors (doorid, zone, name, pos_y, pos_x, pos_z, heading, opentype, guild, lockpick, keyitem, triggerdoor, triggertype, doorisopen, door_param, dest_zone, dest_x, dest_y, dest_z, dest_heading, invert_state, incline, size) VALUES (1, 'guildlobby', 'TRANS_ENTRY', -59, 19, 10, 135, 58, 0, 0, 0, 0, 0, 0, 0, 'poknowledge', 1380, -300, -121, 0, 0, 0, 100);
INSERT INTO doors (doorid, zone, name, pos_y, pos_x, pos_z, heading, opentype, guild, lockpick, keyitem, triggerdoor, triggertype, doorisopen, door_param, dest_zone, dest_x, dest_y, dest_z, dest_heading, invert_state, incline, size) VALUES (2, 'guildlobby', 'GUILD_DOOR_', 624.45, 48.375, 4.95, 385, 58, 0, 0, 0, 0, 0, 0, 0, 'guildhall', 0, 0, 0, 0, 0, 0, 105);
INSERT INTO doors (doorid, zone, name, pos_y, pos_x, pos_z, heading, opentype, guild, lockpick, keyitem, triggerdoor, triggertype, doorisopen, door_param, dest_zone, dest_x, dest_y, dest_z, dest_heading, invert_state, incline, size) VALUES (3, 'guildlobby', 'GUILD_DOOR_', 624.45, -48.375, 4.95, 385, 58, 0, 0, 0, 0, 0, 0, 0, 'guildhall', 0, 0, 0, 0, 0, 0, 105);
INSERT INTO doors (doorid, zone, name, pos_y, pos_x, pos_z, heading, opentype, guild, lockpick, keyitem, triggerdoor, triggertype, doorisopen, door_param, dest_zone, dest_x, dest_y, dest_z, dest_heading, invert_state, incline, size) VALUES (1, 'guildhall', 'GUILD_DOOR', -162.25, -3.375, 7, 128, 58, 0, 0, 0, 0, 0, 0, 0, 'guildlobby', 0, 585, 0, 180, 0, 0, 100);
Hope this info helps anyone who has insert errors with these doors.

J
Reply With Quote
 

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 08:05 AM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3