Pages

version-2/Data format 2.0

Data format 2.0

For the v 2.0 and later, the format has been completely rewritten, and now it's easier to manipulate with SQL queries

Current revision: 2.5

Table schemas

pendings

TABLE `*_pendings` ( 
`id` varchar(36) NOT NULL,
`owner` int(11) NOT NULL,
`world` varchar(100) NOT NULL,
`item` int(11) NOT NULL,
`data` tinyint(4) NOT NULL DEFAULT '0',
`damage` smallint(6) NOT NULL DEFAULT '0', 
`count` int(11) NOT NULL, 
PRIMARY KEY (`id`)
)
  • id is an Universal Unique Identifier (UUID), or any string (max 36 char) that identify the item with the (eventual) enchantments.
  • owner is the id of the owner in the table *_users
  • world is the name of the world, useful if you had an inventory per worlds
  • item is the item id
  • data is the data of the item
  • damage is the damage for the item
  • count is the number of items. Setting this to a negative value will remove items form player's inventory

Inventories

TABLE `invsql_inventories` (
  `id` varchar(36) NOT NULL,
  `owner` int(11) NOT NULL,
  `world` varchar(100) NOT NULL,
  `item` int(11) NOT NULL,
  `data` tinyint(4) NOT NULL DEFAULT '0',
  `damage` smallint(6) NOT NULL DEFAULT '0',
  `count` int(11) NOT NULL,
  `slot` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `event` varchar(100) NOT NULL,
  `suid` varchar(36) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_world` (`owner`,`world`),
  KEY `id_owner_world` (`id`,`owner`,`world`)
)

The table is the same as *_pendings, except:

  • slot is the slot id of the item
  • date is the date when the item has been added to the table (date of the latest update)
  • event is the name of the event that caused the update
  • suid is the server uid generated by InventorySQL at the first launch. Used to identify the server in mirror mode

Enchantments

TABLE `*_enchantments` (
`id` varchar(36) NOT NULL,
`ench_index` int(11) NOT NULL,
`ench` int(11) NOT NULL,
`level` int(11) NOT NULL,
`is_backup` tinyint(4) NOT NULL
)
  • id is the id of the row in the table *_pendings or *_inventories
  • ench_index is the index of the enchantments for the item (doesn't really matter however)
  • ench is the id of the enchantment
  • level is the level
  • is_backup is used in backups

Metadata

TABLE `*_meta` (
`id` varchar(36) NOT NULL,
`key` varchar(11) NOT NULL,
`value` varchar(30) NOT NULL,
`is_backup` tinyint(4) NOT NULL
)
  • id is the id of the row in the table *_pendings or *_inventories
  • key is the name of the metadata :
    • DisplayName to set the display name of the item
    • Lore_# where # is the number of the line of text added to the item
    • Owner in the case of a SKULL_ITEM to set the player face
  • is_backup is used in backups

Users

TABLE `*_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
)
  • id is the id the user
  • name is the name of the user (not the display name)
  • password is the password that can be edited with the command /invsql pw. It's hashed with MD5. You can use it if you want for auth in a web interface.


How to read inventories ?

here is an example of query that return all currentthe inventory for one player : 

SELECT
`inventory`.`id` AS `p_id`,
`inventory`.`item` AS `item`,
`inventory`.`data` AS `data`,
`inventory`.`damage` AS `damage`,
`inventory`.`count` AS `count`,
`inventory`.`date` AS `date`,
`inventory`.`slot` AS `slot`,
`enchantments`.`ench` AS `ench`,
`enchantments`.`level` AS `level`
FROM `inv_table` as `inventory`
LEFT JOIN `ench_table` AS `enchantments` ON `inventory`.`id` = `enchantments`.`id` AND `enchantments`.`is_backup` = 0
LEFT JOIN `meta_table` AS `meta` ON `inventory`.`id` = `meta`.`id` AND `meta`.`is_backup` = 0
WHERE (`inventory`.`owner` = ? AND `inventory`.`world` = ?);