Check link for suggested schema changes:
Todo?:
- Transcode
Why give each item a unique id?
[TFS 2.0] items table to support unique item reference
[TFS 2.0] items table to support unique item reference - changes.sql
gist.github.com
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: