• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

[suggestion] Unique item reference database structure

Znote

<?php echo $title; ?>
Staff member
Global Moderator
Premium User
Joined
Feb 14, 2008
Messages
7,030
Solutions
256
Reaction score
2,115
Location
Norway
GitHub
Znote
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:
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!
 
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 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:
Back
Top