mirror of
https://github.com/fulpstation/fulpstation.git
synced 2025-12-09 07:54:14 +00:00
7199947c08
Several months worth of updates. --------- Co-authored-by: A miscellaneous Fern <80640114+FernandoJ8@users.noreply.github.com> Co-authored-by: Pepsilawn <reisenrui@gmail.com> Co-authored-by: Ray <64306407+OneAsianTortoise@users.noreply.github.com> Co-authored-by: Cure221 <106662180+Cure221@users.noreply.github.com>
744 lines
27 KiB
SQL
744 lines
27 KiB
SQL
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8 */;
|
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_admin`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_admin`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_admin` (
|
|
`ckey` varchar(32) NOT NULL,
|
|
`rank` varchar(32) NOT NULL,
|
|
`feedback` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`ckey`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_admin_log`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_admin_log`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_admin_log` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`datetime` datetime NOT NULL,
|
|
`round_id` int(11) unsigned NULL,
|
|
`adminckey` varchar(32) NOT NULL,
|
|
`adminip` int(10) unsigned NOT NULL,
|
|
`operation` enum('add admin','remove admin','change admin rank','add rank','remove rank','change rank flags') NOT NULL,
|
|
`target` varchar(32) NOT NULL,
|
|
`log` varchar(1000) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_admin_ranks`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_admin_ranks`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_admin_ranks` (
|
|
`rank` varchar(32) NOT NULL,
|
|
`flags` mediumint(5) unsigned NOT NULL,
|
|
`exclude_flags` mediumint(5) unsigned NOT NULL,
|
|
`can_edit_flags` mediumint(5) unsigned NOT NULL,
|
|
PRIMARY KEY (`rank`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_ban`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_ban`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_ban` (
|
|
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`bantime` DATETIME NOT NULL,
|
|
`server_ip` INT(10) UNSIGNED NOT NULL,
|
|
`server_port` SMALLINT(5) UNSIGNED NOT NULL,
|
|
`round_id` INT(11) UNSIGNED NULL,
|
|
`role` VARCHAR(32) NULL DEFAULT NULL,
|
|
`expiration_time` DATETIME NULL DEFAULT NULL,
|
|
`applies_to_admins` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
|
|
`reason` VARCHAR(2048) NOT NULL,
|
|
`ckey` VARCHAR(32) NULL DEFAULT NULL,
|
|
`ip` INT(10) UNSIGNED NULL DEFAULT NULL,
|
|
`computerid` VARCHAR(32) NULL DEFAULT NULL,
|
|
`a_ckey` VARCHAR(32) NOT NULL,
|
|
`a_ip` INT(10) UNSIGNED NOT NULL,
|
|
`a_computerid` VARCHAR(32) NOT NULL,
|
|
`who` VARCHAR(2048) NOT NULL,
|
|
`adminwho` VARCHAR(2048) NOT NULL,
|
|
`edits` TEXT NULL DEFAULT NULL,
|
|
`unbanned_datetime` DATETIME NULL DEFAULT NULL,
|
|
`unbanned_ckey` VARCHAR(32) NULL DEFAULT NULL,
|
|
`unbanned_ip` INT(10) UNSIGNED NULL DEFAULT NULL,
|
|
`unbanned_computerid` VARCHAR(32) NULL DEFAULT NULL,
|
|
`unbanned_round_id` INT(11) UNSIGNED NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_ban_isbanned` (`ckey`,`role`,`unbanned_datetime`,`expiration_time`),
|
|
KEY `idx_ban_isbanned_details` (`ckey`,`ip`,`computerid`,`role`,`unbanned_datetime`,`expiration_time`),
|
|
KEY `idx_ban_count` (`bantime`,`a_ckey`,`applies_to_admins`,`unbanned_datetime`,`expiration_time`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `citation`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_citation`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE IF NOT EXISTS `SS13_citation` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`round_id` int(11) unsigned NULL,
|
|
`server_ip` int(11) unsigned NOT NULL,
|
|
`server_port` int(11) unsigned NOT NULL,
|
|
`citation` text NOT NULL,
|
|
`action` varchar(20) NOT NULL DEFAULT '',
|
|
`sender` varchar(32) NOT NULL DEFAULT '',
|
|
`sender_ic` varchar(64) NOT NULL DEFAULT '' COMMENT 'Longer because this is the character name, not the ckey',
|
|
`recipient` varchar(64) NOT NULL DEFAULT '' COMMENT 'Longer because this is the character name, not the ckey',
|
|
`crime` text NOT NULL,
|
|
`crime_desc` text NULL DEFAULT NULL,
|
|
`fine` int(4) DEFAULT NULL,
|
|
`paid` int(4) DEFAULT 0,
|
|
`timestamp` datetime NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_constraints` (`round_id`,`server_ip`,`server_port`,`citation`(100)) USING BTREE
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_connection_log`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_connection_log`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_connection_log` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`datetime` datetime DEFAULT NULL,
|
|
`server_ip` int(10) unsigned NOT NULL,
|
|
`server_port` smallint(5) unsigned NOT NULL,
|
|
`round_id` int(11) unsigned NOT NULL,
|
|
`ckey` varchar(32) DEFAULT NULL,
|
|
`ip` int(10) unsigned NOT NULL,
|
|
`computerid` varchar(45) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_death`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_death`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_death` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`pod` varchar(50) NOT NULL,
|
|
`x_coord` smallint(5) unsigned NOT NULL,
|
|
`y_coord` smallint(5) unsigned NOT NULL,
|
|
`z_coord` smallint(5) unsigned NOT NULL,
|
|
`mapname` varchar(32) NOT NULL,
|
|
`server_ip` int(10) unsigned NOT NULL,
|
|
`server_port` smallint(5) unsigned NOT NULL,
|
|
`round_id` int(11) NULL,
|
|
`tod` datetime NOT NULL COMMENT 'Time of death',
|
|
`job` varchar(32) NOT NULL,
|
|
`special` varchar(32) DEFAULT NULL,
|
|
`name` varchar(96) NOT NULL,
|
|
`byondkey` varchar(32) NOT NULL,
|
|
`laname` varchar(96) DEFAULT NULL,
|
|
`lakey` varchar(32) DEFAULT NULL,
|
|
`bruteloss` smallint(5) unsigned NOT NULL,
|
|
`brainloss` smallint(5) unsigned NOT NULL,
|
|
`fireloss` smallint(5) unsigned NOT NULL,
|
|
`oxyloss` smallint(5) unsigned NOT NULL,
|
|
`toxloss` smallint(5) unsigned NOT NULL,
|
|
`cloneloss` smallint(5) unsigned DEFAULT '0',
|
|
`staminaloss` smallint(5) unsigned NOT NULL,
|
|
`last_words` varchar(255) DEFAULT NULL,
|
|
`suicide` tinyint(1) NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_feedback`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_feedback`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_feedback` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`datetime` datetime NOT NULL,
|
|
`round_id` int(11) unsigned NULL,
|
|
`key_name` varchar(32) NOT NULL,
|
|
`version` tinyint(3) unsigned NOT NULL,
|
|
`key_type` enum('text', 'amount', 'tally', 'nested tally', 'associative') NOT NULL,
|
|
`json` json NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_ipintel`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_ipintel`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_ipintel` (
|
|
`ip` int(10) unsigned NOT NULL,
|
|
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`intel` double NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`ip`),
|
|
KEY `idx_ipintel` (`ip`,`intel`,`date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
--
|
|
-- Table structure for table `ipintel_whitelist`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_ipintel_whitelist`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_ipintel_whitelist` (
|
|
`ckey` varchar(32) NOT NULL,
|
|
`admin_ckey` varchar(32) NOT NULL,
|
|
PRIMARY KEY (`ckey`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_legacy_population`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_legacy_population`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_legacy_population` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`playercount` int(11) DEFAULT NULL,
|
|
`admincount` int(11) DEFAULT NULL,
|
|
`time` datetime NOT NULL,
|
|
`server_ip` int(10) unsigned NOT NULL,
|
|
`server_port` smallint(5) unsigned NOT NULL,
|
|
`round_id` int(11) unsigned NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_library`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_library`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_library` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`author` varchar(45) NOT NULL,
|
|
`title` varchar(45) NOT NULL,
|
|
`content` text NOT NULL,
|
|
`category` enum('Any','Fiction','Non-Fiction','Adult','Reference','Religion') NOT NULL,
|
|
`ckey` varchar(32) NOT NULL DEFAULT 'LEGACY',
|
|
`datetime` datetime NOT NULL,
|
|
`deleted` tinyint(1) unsigned DEFAULT NULL,
|
|
`round_id_created` int(11) unsigned NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `deleted_idx` (`deleted`),
|
|
KEY `idx_lib_id_del` (`id`,`deleted`),
|
|
KEY `idx_lib_del_title` (`deleted`,`title`),
|
|
KEY `idx_lib_search` (`deleted`,`author`,`title`,`category`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_library_action`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_library_action`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_library_action` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`book` int(10) unsigned NOT NULL,
|
|
`reason` longtext DEFAULT NULL,
|
|
`ckey` varchar(32) NOT NULL DEFAULT '',
|
|
`datetime` datetime NOT NULL DEFAULT current_timestamp(),
|
|
`action` varchar(11) NOT NULL DEFAULT '',
|
|
`ip_addr` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_manifest`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_manifest`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_manifest` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`server_ip` int(10) unsigned NOT NULL,
|
|
`server_port` smallint(5) NOT NULL,
|
|
`round_id` int(11) NOT NULL,
|
|
`ckey` varchar(32) NOT NULL,
|
|
`character_name` text NOT NULL,
|
|
`job` text NOT NULL,
|
|
`special` text DEFAULT NULL,
|
|
`latejoin` tinyint(1) NOT NULL DEFAULT 0,
|
|
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_messages`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_messages`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_messages` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`type` enum('memo','message','message sent','note','watchlist entry') NOT NULL,
|
|
`targetckey` varchar(32) NOT NULL,
|
|
`adminckey` varchar(32) NOT NULL,
|
|
`text` varchar(2048) NOT NULL,
|
|
`timestamp` datetime NOT NULL,
|
|
`server` varchar(32) DEFAULT NULL,
|
|
`server_ip` int(10) unsigned NOT NULL,
|
|
`server_port` smallint(5) unsigned NOT NULL,
|
|
`round_id` int(11) unsigned NULL,
|
|
`secret` tinyint(1) unsigned NOT NULL,
|
|
`expire_timestamp` datetime DEFAULT NULL,
|
|
`severity` enum('high','medium','minor','none') DEFAULT NULL,
|
|
`playtime` int(11) unsigned NULL DEFAULT NULL,
|
|
`lasteditor` varchar(32) DEFAULT NULL,
|
|
`edits` text,
|
|
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
`deleted_ckey` VARCHAR(32) NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_msg_ckey_time` (`targetckey`,`timestamp`, `deleted`),
|
|
KEY `idx_msg_type_ckeys_time` (`type`,`targetckey`,`adminckey`,`timestamp`, `deleted`),
|
|
KEY `idx_msg_type_ckey_time_odr` (`type`,`targetckey`,`timestamp`, `deleted`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_role_time`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_role_time`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
|
|
CREATE TABLE `SS13_role_time`
|
|
( `ckey` VARCHAR(32) NOT NULL ,
|
|
`job` VARCHAR(32) NOT NULL ,
|
|
`minutes` INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (`ckey`, `job`)
|
|
) ENGINE = InnoDB;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_role_time`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_role_time_log`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
|
|
CREATE TABLE IF NOT EXISTS `SS13_role_time_log` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`ckey` varchar(32) NOT NULL,
|
|
`job` varchar(128) NOT NULL,
|
|
`delta` int(11) NOT NULL,
|
|
`datetime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
KEY `ckey` (`ckey`),
|
|
KEY `job` (`job`),
|
|
KEY `datetime` (`datetime`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_player`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_player`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_player` (
|
|
`ckey` varchar(32) NOT NULL,
|
|
`byond_key` varchar(32) DEFAULT NULL,
|
|
`firstseen` datetime NOT NULL,
|
|
`firstseen_round_id` int(11) unsigned NULL,
|
|
`lastseen` datetime NOT NULL,
|
|
`lastseen_round_id` int(11) unsigned NULL,
|
|
`ip` int(10) unsigned NOT NULL,
|
|
`computerid` varchar(32) NOT NULL,
|
|
`lastadminrank` varchar(32) NOT NULL DEFAULT 'Player',
|
|
`accountjoindate` DATE DEFAULT NULL,
|
|
`flags` smallint(5) unsigned DEFAULT '0' NOT NULL,
|
|
PRIMARY KEY (`ckey`),
|
|
KEY `idx_player_cid_ckey` (`computerid`,`ckey`),
|
|
KEY `idx_player_ip_ckey` (`ip`,`ckey`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_poll_option`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_poll_option`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_poll_option` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`pollid` int(11) NOT NULL,
|
|
`text` varchar(255) NOT NULL,
|
|
`minval` int(3) DEFAULT NULL,
|
|
`maxval` int(3) DEFAULT NULL,
|
|
`descmin` varchar(32) DEFAULT NULL,
|
|
`descmid` varchar(32) DEFAULT NULL,
|
|
`descmax` varchar(32) DEFAULT NULL,
|
|
`default_percentage_calc` tinyint(1) unsigned NOT NULL DEFAULT '1',
|
|
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_pop_pollid` (`pollid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_poll_question`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_poll_question`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_poll_question` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`polltype` enum('OPTION','TEXT','NUMVAL','MULTICHOICE','IRV') NOT NULL,
|
|
`created_datetime` datetime NOT NULL,
|
|
`starttime` datetime NOT NULL,
|
|
`endtime` datetime NOT NULL,
|
|
`question` varchar(255) NOT NULL,
|
|
`subtitle` varchar(255) DEFAULT NULL,
|
|
`adminonly` tinyint(1) unsigned NOT NULL,
|
|
`multiplechoiceoptions` int(2) DEFAULT NULL,
|
|
`createdby_ckey` varchar(32) NOT NULL,
|
|
`createdby_ip` int(10) unsigned NOT NULL,
|
|
`dontshow` tinyint(1) unsigned NOT NULL,
|
|
`allow_revoting` tinyint(1) unsigned NOT NULL,
|
|
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_pquest_question_time_ckey` (`question`,`starttime`,`endtime`,`createdby_ckey`,`createdby_ip`),
|
|
KEY `idx_pquest_time_deleted_id` (`starttime`,`endtime`, `deleted`, `id`),
|
|
KEY `idx_pquest_id_time_type_admin` (`id`,`starttime`,`endtime`,`polltype`,`adminonly`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_poll_textreply`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_poll_textreply`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_poll_textreply` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`datetime` datetime NOT NULL,
|
|
`pollid` int(11) NOT NULL,
|
|
`ckey` varchar(32) NOT NULL,
|
|
`ip` int(10) unsigned NOT NULL,
|
|
`replytext` varchar(2048) NOT NULL,
|
|
`adminrank` varchar(32) NOT NULL,
|
|
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_ptext_pollid_ckey` (`pollid`,`ckey`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_poll_vote`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `SS13_poll_vote`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_poll_vote` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`datetime` datetime NOT NULL,
|
|
`pollid` int(11) NOT NULL,
|
|
`optionid` int(11) NOT NULL,
|
|
`ckey` varchar(32) NOT NULL,
|
|
`ip` int(10) unsigned NOT NULL,
|
|
`adminrank` varchar(32) NOT NULL,
|
|
`rating` int(2) DEFAULT NULL,
|
|
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_pvote_pollid_ckey` (`pollid`,`ckey`),
|
|
KEY `idx_pvote_optionid_ckey` (`optionid`,`ckey`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_round`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_round`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `SS13_round` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`initialize_datetime` DATETIME NOT NULL,
|
|
`start_datetime` DATETIME NULL,
|
|
`shutdown_datetime` DATETIME NULL,
|
|
`end_datetime` DATETIME NULL,
|
|
`server_ip` INT(10) UNSIGNED NOT NULL,
|
|
`server_port` SMALLINT(5) UNSIGNED NOT NULL,
|
|
`commit_hash` CHAR(40) NULL,
|
|
`game_mode` VARCHAR(32) NULL,
|
|
`game_mode_result` VARCHAR(64) NULL,
|
|
`end_state` VARCHAR(64) NULL,
|
|
`shuttle_name` VARCHAR(64) NULL,
|
|
`map_name` VARCHAR(32) NULL,
|
|
`station_name` VARCHAR(80) NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
|
|
|
--
|
|
-- Table structure for table `SS13_schema_revision`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_schema_revision`;
|
|
CREATE TABLE `SS13_schema_revision` (
|
|
`major` TINYINT(3) unsigned NOT NULL,
|
|
`minor` TINYINT(3) unsigned NOT NULL,
|
|
`date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`major`,`minor`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Table structure for table `SS13_stickyban`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_stickyban`;
|
|
CREATE TABLE `SS13_stickyban` (
|
|
`ckey` VARCHAR(32) NOT NULL,
|
|
`reason` VARCHAR(2048) NOT NULL,
|
|
`banning_admin` VARCHAR(32) NOT NULL,
|
|
`datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`ckey`)
|
|
) ENGINE=InnoDB;
|
|
|
|
--
|
|
-- Table structure for table `SS13_stickyban_matched_ckey`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_stickyban_matched_ckey`;
|
|
CREATE TABLE `SS13_stickyban_matched_ckey` (
|
|
`stickyban` VARCHAR(32) NOT NULL,
|
|
`matched_ckey` VARCHAR(32) NOT NULL,
|
|
`first_matched` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`last_matched` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`exempt` TINYINT(1) NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`stickyban`, `matched_ckey`)
|
|
) ENGINE=InnoDB;
|
|
|
|
--
|
|
-- Table structure for table `SS13_stickyban_matched_ip`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_stickyban_matched_ip`;
|
|
CREATE TABLE `SS13_stickyban_matched_ip` (
|
|
`stickyban` VARCHAR(32) NOT NULL,
|
|
`matched_ip` INT UNSIGNED NOT NULL,
|
|
`first_matched` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`last_matched` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`stickyban`, `matched_ip`)
|
|
) ENGINE=InnoDB;
|
|
|
|
--
|
|
-- Table structure for table `SS13_stickyban_matched_cid`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_stickyban_matched_cid`;
|
|
CREATE TABLE `SS13_stickyban_matched_cid` (
|
|
`stickyban` VARCHAR(32) NOT NULL,
|
|
`matched_cid` VARCHAR(32) NOT NULL,
|
|
`first_matched` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`last_matched` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`stickyban`, `matched_cid`)
|
|
) ENGINE=InnoDB;
|
|
|
|
--
|
|
-- Table structure for table `SS13_achievements`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_achievements`;
|
|
CREATE TABLE `SS13_achievements` (
|
|
`ckey` VARCHAR(32) NOT NULL,
|
|
`achievement_key` VARCHAR(32) NOT NULL,
|
|
`value` INT NULL,
|
|
`last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`ckey`,`achievement_key`)
|
|
) ENGINE=InnoDB;
|
|
|
|
DROP TABLE IF EXISTS `SS13_achievement_metadata`;
|
|
CREATE TABLE `SS13_achievement_metadata` (
|
|
`achievement_key` VARCHAR(32) NOT NULL,
|
|
`achievement_version` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`achievement_type` enum('achievement','score','award') NULL DEFAULT NULL,
|
|
`achievement_name` VARCHAR(64) NULL DEFAULT NULL,
|
|
`achievement_description` VARCHAR(512) NULL DEFAULT NULL,
|
|
PRIMARY KEY (`achievement_key`)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Table structure for table 'SS13_x_progress'
|
|
|
|
DROP TABLE IF EXISTS `SS13_fish_progress`;
|
|
CREATE TABLE `fish_progress` (
|
|
`ckey` VARCHAR(32) NOT NULL,
|
|
`progress_entry` VARCHAR(32) NOT NULL,
|
|
`datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`ckey`,`progress_entry`)
|
|
) ENGINE=InnoDB;
|
|
|
|
--
|
|
-- Table structure for table `SS13_ticket`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_ticket`;
|
|
CREATE TABLE `SS13_ticket` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`server_ip` int(10) unsigned NOT NULL,
|
|
`server_port` smallint(5) unsigned NOT NULL,
|
|
`round_id` int(11) unsigned NULL,
|
|
`ticket` smallint(11) unsigned NOT NULL,
|
|
`action` varchar(20) NOT NULL DEFAULT 'Message',
|
|
`urgent` TINYINT(1) unsigned NOT NULL DEFAULT '0',
|
|
`message` text NOT NULL,
|
|
`timestamp` datetime NOT NULL,
|
|
`recipient` varchar(32) DEFAULT NULL,
|
|
`sender` varchar(32) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_ticket_act_recip` (`action`, `recipient`),
|
|
KEY `idx_ticket_act_send` (`action`, `sender`),
|
|
KEY `idx_ticket_tic_rid` (`ticket`, `round_id`),
|
|
KEY `idx_ticket_act_time_rid` (`action`, `timestamp`, `round_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
DELIMITER $$
|
|
CREATE PROCEDURE `set_poll_deleted`(
|
|
IN `poll_id` INT
|
|
)
|
|
SQL SECURITY INVOKER
|
|
BEGIN
|
|
UPDATE `SS13_poll_question` SET deleted = 1 WHERE id = poll_id;
|
|
UPDATE `SS13_poll_option` SET deleted = 1 WHERE pollid = poll_id;
|
|
UPDATE `SS13_poll_vote` SET deleted = 1 WHERE pollid = poll_id;
|
|
UPDATE `SS13_poll_textreply` SET deleted = 1 WHERE pollid = poll_id;
|
|
END
|
|
$$
|
|
CREATE TRIGGER `SS13_role_timeTlogupdate` AFTER UPDATE ON `SS13_role_time` FOR EACH ROW BEGIN INSERT into SS13_role_time_log (ckey, job, delta) VALUES (NEW.CKEY, NEW.job, NEW.minutes-OLD.minutes);
|
|
END
|
|
$$
|
|
CREATE TRIGGER `SS13_role_timeTloginsert` AFTER INSERT ON `SS13_role_time` FOR EACH ROW BEGIN INSERT into SS13_role_time_log (ckey, job, delta) VALUES (NEW.ckey, NEW.job, NEW.minutes);
|
|
END
|
|
$$
|
|
CREATE TRIGGER `SS13_role_timeTlogdelete` AFTER DELETE ON `SS13_role_time` FOR EACH ROW BEGIN INSERT into SS13_role_time_log (ckey, job, delta) VALUES (OLD.ckey, OLD.job, 0-OLD.minutes);
|
|
END
|
|
$$
|
|
DELIMITER ;
|
|
|
|
--
|
|
-- Table structure for table `discord_links`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_discord_links`;
|
|
CREATE TABLE `SS13_discord_links` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`ckey` VARCHAR(32) NOT NULL,
|
|
`discord_id` BIGINT(20) DEFAULT NULL,
|
|
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`one_time_token` VARCHAR(100) NOT NULL,
|
|
`valid` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB;
|
|
|
|
--
|
|
-- Table structure for table `admin_connections`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_admin_connections`;
|
|
CREATE TABLE `SS13_admin_connections` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`ckey` VARCHAR(32) NOT NULL,
|
|
`ip` INT(11) UNSIGNED NOT NULL,
|
|
`cid` VARCHAR(32) NOT NULL,
|
|
`verification_time` DATETIME NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `unique_constraints` (`ckey`, `ip`, `cid`)
|
|
) ENGINE=InnoDB;
|
|
|
|
--
|
|
-- Table structure for table `known_alts`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_known_alts`;
|
|
CREATE TABLE `SS13_known_alts` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`ckey1` VARCHAR(32) NOT NULL,
|
|
`ckey2` VARCHAR(32) NOT NULL,
|
|
`admin_ckey` VARCHAR(32) NOT NULL DEFAULT '*no key*',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `unique_contraints` (`ckey1` , `ckey2`)
|
|
);
|
|
|
|
--
|
|
-- Table structure for table `telemetry_connections`
|
|
--
|
|
DROP TABLE IF EXISTS `SS13_telemetry_connections`;
|
|
CREATE TABLE `SS13_telemetry_connections` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`ckey` VARCHAR(32) NOT NULL,
|
|
`telemetry_ckey` VARCHAR(32) NOT NULL,
|
|
`address` INT(10) UNSIGNED NOT NULL,
|
|
`computer_id` VARCHAR(32) NOT NULL,
|
|
`first_round_id` INT(11) UNSIGNED NULL,
|
|
`latest_round_id` INT(11) UNSIGNED NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `unique_constraints` (`ckey` , `telemetry_ckey` , `address` , `computer_id`)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS `SS13_tutorial_completions`;
|
|
CREATE TABLE `SS13_tutorial_completions` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`ckey` VARCHAR(32) NOT NULL,
|
|
`tutorial_key` VARCHAR(64) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `ckey_tutorial_unique` (`ckey`, `tutorial_key`));
|
|
|
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
|
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
|
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
|