Database Tables Schema

GD Rating System adds several database tables to store rating logs and rating objects/items data. There are 2 main tables and 2 metadata tables for them both using a structure similar to normal WordPress metatables.

All tables added use standard WordPress database tables prefix (from WordPress own database object), followed by GD Rating System table prefix (gdrts) and the name of the table.

If you use the plugin in the multisite / network WordPress installation, the plugin will add tables into every blog it was activated for. There are no global, network-level tables, everything is on the blog level, and each blog uses the plugin on its own.

Objects / Items Tables

There are four tables added: wp_gdrts_items, wp_gdrts_items_basic, wp_gdrts_items_multi, wp_gdrts_itemmeta. For each rating object/item, you need to specify the entity, type name, and ID. The table is auto-increment based, and the unique item_id is used to connect items to the log table. All rating information for every rating method about each item is stored in form of metadata in the meta table.

Log Tables

There are three tables added: wp_gdrts_logs, wp_gdrts_logs_multi and wp_gdrts_logmeta. Each log entry contains item ID (pointing to items tables, and the object definition), the user who rated, reference log ID (used for revotes right now), rating action and status, rating method, date and time logged and IP. Other information, specific to the rating method used is saved as metadata.

Free Plugin

The free plugin version has 5 of the 7 total tables. The tables not used by free plugin are wp_gdrts_items_multi and wp_gdrts_logs_multi.

Schema

This schema is for the default WordPress database prefix ‘wp_’. If your website uses a different prefix, replace in the CREATE TABLE statements. In the multisite environment, each website has a different prefix, so make sure you use the correct one.

CREATE TABLE `wp_gdrts_items` (
  `item_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity` VARCHAR(32) NOT NULL DEFAULT 'posts' COMMENT 'posts,comments,users,terms',
  `name` VARCHAR(64) NOT NULL DEFAULT 'post',
  `id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `latest` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'gmt',
  PRIMARY KEY (`item_id`),
  UNIQUE INDEX `entity_name_id` (`entity`, `name`, `id`),
  INDEX `entity` (`entity`),
  INDEX `name` (`name`),
  INDEX `id` (`id`)
);

CREATE TABLE `wp_gdrts_items_basic` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `method` VARCHAR(64) NOT NULL DEFAULT '',
  `series` VARCHAR(128) NOT NULL DEFAULT '',
  `latest` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'gmt',
  `rating` INT(11) NOT NULL DEFAULT '0',
  `votes` INT(11) NOT NULL DEFAULT '0',
  `sum` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `max` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `item_method_series` (`item_id`, `method`, `series`),
  INDEX `item_id` (`item_id`),
  INDEX `method` (`method`),
  INDEX `series` (`series`),
  INDEX `latest` (`latest`),
  INDEX `rating` (`rating`)
);

CREATE TABLE `wp_gdrts_items_multi` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `basic_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'from wp_gdrts_items_basic table',
  `item` VARCHAR(128) NOT NULL DEFAULT '',
  `rating` INT(11) NOT NULL DEFAULT '0',
  `sum` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `basic_id` (`basic_id`),
  INDEX `item` (`item`),
  INDEX `basic_id_item` (`basic_id`, `item`),
  INDEX `rating` (`rating`)
) ;
CREATE TABLE `wp_gdrts_itemmeta` (
  `meta_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `meta_key` VARCHAR(255) NULL DEFAULT NULL,
  `meta_value` LONGTEXT NULL,
  PRIMARY KEY (`meta_id`),
  INDEX `item_id` (`item_id`),
  INDEX `meta_key` (`meta_key`(191))
);
CREATE TABLE `wp_gdrts_logs` (
  `log_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT(20) NOT NULL DEFAULT '0' COMMENT 'from gdrs_items table',
  `user_id` BIGINT(20) NOT NULL DEFAULT '0',
  `ref_id` BIGINT(20) NOT NULL DEFAULT '0' COMMENT 'reference id for revotes from this same table',
  `action` VARCHAR(128) NOT NULL DEFAULT 'vote' COMMENT 'vote,revote,queue',
  `status` VARCHAR(128) NOT NULL DEFAULT 'active' COMMENT 'active,replaced',
  `method` VARCHAR(64) NOT NULL DEFAULT 'stars-rating',
  `logged` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'gmt',
  `ip` VARCHAR(128) NOT NULL DEFAULT '',
  `series` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'set belonging to method',
  PRIMARY KEY (`log_id`),
  INDEX `item_id` (`item_id`),
  INDEX `user_id` (`user_id`),
  INDEX `action` (`action`),
  INDEX `ref_id` (`ref_id`),
  INDEX `status` (`status`),
  INDEX `method` (`method`),
  INDEX `ip` (`ip`),
  INDEX `series` (`series`)
);

CREATE TABLE `wp_gdrts_logmeta` (
  `meta_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `log_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `meta_key` VARCHAR(255) NULL DEFAULT NULL,
  `meta_value` LONGTEXT NULL,
  PRIMARY KEY (`meta_id`),
  INDEX `log_id` (`log_id`),
  INDEX `meta_key` (`meta_key`)
);

CREATE TABLE `wp_gdrts_logs_multi` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `log_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `item` VARCHAR(128) NOT NULL DEFAULT '',
  `vote` VARCHAR(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  INDEX `log_id` (`log_id`),
  INDEX `item` (`item`),
  INDEX `vote` (`vote`)
);
CREATE TABLE `wp_gdrts_cache` (
  `cache_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `module` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'aggregate,period',
  `method` VARCHAR(128) NOT NULL DEFAULT '',
  `access` VARCHAR(32) NOT NULL DEFAULT '',
  `store` LONGTEXT NOT NULL,
  `expire` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`cache_id`),
  INDEX `module` (`module`),
  INDEX `method` (`method`)
);