[suggestion] Unique item reference database structure

Znote

<?php echo $title; ?>
Staff member
Global Moderator
Premium User
Joined
Feb 14, 2008
Messages
6,572
Best answers
179
Reaction score
1,289
Location
Norway, Oslo
Check link for suggested schema changes:
SQL:
CREATE TABLE IF NOT EXISTS `items` (
  `item_uid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `itemtype` smallint(6) NOT NULL,
  `count` smallint(5) NOT NULL,
  `attributes` blob NOT NULL,
  PRIMARY KEY (`item_uid`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

## OPTIONAL TABLE/COLUMNS - IGNORE IF BAD IDEA ##
CREATE TABLE IF NOT EXISTS `items_timestamps` (
  `item_uid` bigint(20) unsigned NOT NULL,
  `created` bigint(20) unsigned NOT NULL DEFAULT '0',
  `updated` bigint(20) unsigned NOT NULL DEFAULT '0',
  `expired` bigint(20) unsigned NOT NULL DEFAULT '0'
  FOREIGN KEY (`item_uid`) REFERENCES `items`(`item_uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
## END OPTIONAL TABLE ##

CREATE TABLE IF NOT EXISTS `player_depotitems` (
  `player_id` int(11) NOT NULL,
  `sid` int(11) NOT NULL COMMENT 'any given range eg 0-100 will be reserved for depot lockers and all > 100 will be then normal items inside depots',
  `pid` int(11) NOT NULL DEFAULT '0',
  `item_uid` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `player_id_2` (`player_id`, `sid`),
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`item_uid`) REFERENCES `items`(`item_uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

CREATE TABLE IF NOT EXISTS `player_inboxitems` (
  `player_id` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  `pid` int(11) NOT NULL DEFAULT '0',
  `item_uid` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `player_id_2` (`player_id`, `sid`),
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`item_uid`) REFERENCES `items`(`item_uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

CREATE TABLE IF NOT EXISTS `player_items` (
  `player_id` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  `sid` int(11) NOT NULL DEFAULT '0',
  `item_uid` bigint(20) unsigned NOT NULL,
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`item_uid`) REFERENCES `items`(`item_uid`) ON DELETE CASCADE,
  KEY `sid` (`sid`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
Todo?:
- Transcode tile_store longblob data into SQL integer rows and add item_uid reference.

Why give each item a unique id?
  • Item tracking (like shop items, scripted items, special items, stateless item decaying)
  • Duplicate prevention
  • Special attributes
  • Analytics?
Lua:
local uid = 5431652
local item = Item(uid)
print(item:getName()) -- Fire Sword
print(item:getType()) -- 2392 (items.xml id)
print(item:created()) -- unix timestamp of when it was originally created
 
Last edited:

oen432

Intermediate OT User
Joined
Oct 3, 2014
Messages
416
Best answers
7
Reaction score
407
Location
Poland
That will allow 3rd party apps to integrate with items in a better way than now.
Admin dashboard with list of all items, possibility to edit them, checking if someone lost an item "because bugs".
Item market that can handle custom attributes, like from item upgrades.

This is promising!
 

Gesior.pl

Mega Noob&LOL 2012
Joined
Sep 18, 2007
Messages
2,001
Best answers
19
Reaction score
974
Location
PLand
I got a questions about one table:
Code:
CREATE TABLE IF NOT EXISTS `items_timestamps` (
  `item_uid` bigint(20) unsigned NOT NULL,
  `created` bigint(20) unsigned NOT NULL DEFAULT '0',
  `updated` bigint(20) unsigned NOT NULL DEFAULT '0',
  `expired` bigint(20) unsigned NOT NULL DEFAULT '0'
  FOREIGN KEY (`item_uid`) REFERENCES `items`(`item_uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
What will fill these values? TFS engine?
What is 'expired'?
and Why these columns are not in 'items' table?
 
OP
Znote

Znote

<?php echo $title; ?>
Staff member
Global Moderator
Premium User
Joined
Feb 14, 2008
Messages
6,572
Best answers
179
Reaction score
1,289
Location
Norway, Oslo
I got a questions about one table:
Code:
CREATE TABLE IF NOT EXISTS `items_timestamps` (
  `item_uid` bigint(20) unsigned NOT NULL,
  `created` bigint(20) unsigned NOT NULL DEFAULT '0',
  `updated` bigint(20) unsigned NOT NULL DEFAULT '0',
  `expired` bigint(20) unsigned NOT NULL DEFAULT '0'
  FOREIGN KEY (`item_uid`) REFERENCES `items`(`item_uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
What will fill these values? TFS engine?
What is 'expired'?
and Why these columns are not in 'items' table?
I just though some timestamps would be useful to latch onto item uids, but im not sure if its worth adding those columns. Thats why I just added them as a separate supplementary/optional table.

I imagine these to mostly be for "edge-case" scenarios, like custom lua scripts, perhaps decay states of items like enchanted staff, which would decay even when in depot/house etc. A separate table could eliminate the need to load 3 extra bigints (+24 bytes) for each item row, unless the data is needed.

If there is no good reason for this table, then just take it out of the equation.
 
Last edited:
Top