mirror of
https://github.com/Cockatrice/Cockatrice.git
synced 2026-09-21 09:05:10 -07:00
d5d99e4dfb
* [Server/Client/Protocol] Add developer staff role Introduce a Developer staff level (proto flag 32, DB admin bit 8) that sits between admin and moderator: no kick/ban/warn/report/admin powers, but gets server log access via a new developer command container family (GET_SERVER_STATS, VIEWLOG_HISTORY) and an idle-timeout exemption. - Protocol: IsDeveloper flag, developer_commands.proto envelope, Command_GetServerStats/Command_GetLogHistory, Response_GetServerStats, Command_AdjustMod.should_be_developer - Servatrice: fail-closed developer dispatcher, uptime snapshot handler, shared log history handler reuse, bit-8 DB mapping - Client: burgundy pawn/badge/labels/sort order, prepareDeveloperCommand, minimal Developer stats tab, log tab access, promote/demote actions Took 24 minutes Took 18 seconds * [Server/Client/Protocol] Address developer role review feedback Address ZeizaZach's review of the developer staff role: - Nudge the developer log query to exclude private chat and sender IPs (the ModeratorCommand path still sees everything). - Deduplicate Command_GetLogHistory into Command_ViewLogHistory, which now extends both ModeratorCommand (ext) and DeveloperCommand (dev_ext); the client picks the DeveloperCommand-scoped extension by extendee, and the server reads it via the extension number. - Pull the uptime snapshot SQL into Servatrice_DatabaseInterface as getLatestUptimeSnapshot() and widen the reported counters to 64-bit. - Document the admin bitfield (1 admin, 2 moderator, 4 judge, 8 developer) and add a server-side test for the developer command path. * Add missing trailing newline to user_context_menu.cpp * Remove stale includes of deleted command_get_log_history proto The Command_GetLogHistory message was folded into Command_ViewLogHistory, which deleted command_get_log_history.proto, but serversocketinterface still #included its generated header. Fresh CI builds fail on the missing file; local builds masked it by reusing a previously generated header. * [Server] Exclude chat rows when private-chat filter is bypassable A developer who omits log_location entirely — or sends only "chat" — leaves chatType, gameType, roomType all false, so getMessageLogHistory skips the target_type clause and returns every row, private messages included. When !allowPrivateChat the server now forces game+room when no surviving location was requested, guaranteeing the query always carries a target_type restriction. [Client] Demote mod+dev to moderator path in log-tab dispatch The developer command family is strictly weaker than the moderator one (no private chat, no sender_ip, ip filter ignored), so granting the developer bit to an existing moderator must not silently strip their capabilities. useDeveloperCommands is now true only when the user holds the developer bit and not the moderator bit. [Client] Hide the IP-address filter for developer log tab users The developer path ignores the ip_address query field server-side. Showing the field lets a developer type an IP and get results that are silently unfiltered by it rather than an empty result set — reads as a broken filter. Hide labelFindIPAddress/findIPAddress alongside the privateChat checkbox. * Developer pawn is silver. * [Client] Fix indentation of merged Card Art Rules / Developer tabs --------- Co-authored-by: Lukas Brübach <Bruebach.Lukas@bdosecurity.de>
369 lines
16 KiB
SQL
369 lines
16 KiB
SQL
-- Schema file for servatrice database.
|
|
|
|
-- This schema file is using the default table prefix "cockatrice",
|
|
-- to match the "prefix=cockatrice" default setting in servatrice.ini
|
|
|
|
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
|
|
|
|
|
|
/*!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 */;
|
|
|
|
-- Every time the database schema changes, the schema version number
|
|
-- must be incremented. Also remember to update the corresponding
|
|
-- number in servatrice/src/servatrice_database_interface.h
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_schema_version` (
|
|
`version` int(7) unsigned NOT NULL,
|
|
PRIMARY KEY (`version`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
INSERT INTO cockatrice_schema_version VALUES(36);
|
|
|
|
-- users and user data tables
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_users` (
|
|
`id` int(7) unsigned zerofill NOT NULL auto_increment,
|
|
-- Bitfield of staff levels: 1 = admin (implies moderator), 2 = moderator,
|
|
-- 4 = judge, 8 = developer. Operators set these by hand with
|
|
-- "UPDATE cockatrice_users SET admin = ...".
|
|
`admin` tinyint(1) NOT NULL,
|
|
`name` varchar(35) NOT NULL,
|
|
`realname` varchar(255) NOT NULL,
|
|
`password_sha512` char(120) NOT NULL,
|
|
`email` varchar(255) NOT NULL,
|
|
`country` char(2) NOT NULL,
|
|
`avatar_bmp` mediumblob NOT NULL,
|
|
`registrationDate` datetime NOT NULL,
|
|
`active` tinyint(1) NOT NULL,
|
|
`token` binary(16),
|
|
`clientid` varchar(15) NOT NULL,
|
|
`adminnotes` mediumtext NOT NULL,
|
|
`privlevel` enum("NONE","VIP","DONATOR") NOT NULL,
|
|
`privlevelStartDate` datetime NOT NULL,
|
|
`privlevelEndDate` datetime NOT NULL,
|
|
`passwordLastChangedDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
`force_password_change` tinyint(1) NOT NULL DEFAULT 0,
|
|
`leftPawnColorOverride` varchar(255),
|
|
`rightPawnColorOverride` varchar(255),
|
|
`card_art_params` TEXT DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `name` (`name`),
|
|
KEY `token` (`token`),
|
|
KEY `email` (`email`),
|
|
INDEX `idx_admin` (`admin`),
|
|
INDEX `idx_active` (`active`),
|
|
INDEX `idx_privlevel` (`privlevel`),
|
|
INDEX `idx_clientid` (`clientid`),
|
|
INDEX `idx_pawnColorOverrides` (`leftPawnColorOverride`, `rightPawnColorOverride`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_decklist_files` (
|
|
`id` int(7) unsigned zerofill NOT NULL auto_increment,
|
|
`id_folder` int(7) unsigned zerofill NOT NULL,
|
|
`id_user` int(7) unsigned NULL,
|
|
`name` varchar(50) NOT NULL,
|
|
`upload_time` datetime NOT NULL,
|
|
`content` text NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FolderPlusUser` (`id_folder`,`id_user`),
|
|
FOREIGN KEY(`id_user`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_decklist_folders` (
|
|
`id` int(7) unsigned zerofill NOT NULL auto_increment,
|
|
`id_parent` int(7) unsigned zerofill NOT NULL,
|
|
`id_user` int(7) unsigned NULL,
|
|
`name` varchar(30) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `ParentPlusUser` (`id_parent`,`id_user`),
|
|
FOREIGN KEY(`id_user`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_ignorelist` (
|
|
`id_user1` int(7) unsigned NOT NULL,
|
|
`id_user2` int(7) unsigned NOT NULL,
|
|
UNIQUE KEY `key` (`id_user1`, `id_user2`),
|
|
FOREIGN KEY(`id_user1`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY(`id_user2`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_buddylist` (
|
|
`id_user1` int(7) unsigned NOT NULL,
|
|
`id_user2` int(7) unsigned NOT NULL,
|
|
UNIQUE KEY `key` (`id_user1`, `id_user2`),
|
|
FOREIGN KEY(`id_user1`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY(`id_user2`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- rooms
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_rooms` (
|
|
`id` int(7) unsigned NOT NULL auto_increment,
|
|
`name` varchar(50) NOT NULL,
|
|
`descr` varchar(255) NOT NULL,
|
|
`permissionlevel` enum('NONE','REGISTERED','MODERATOR','ADMINISTRATOR') NOT NULL,
|
|
`privlevel` enum('NONE','PRIVILEGED','VIP','DONATOR') NOT NULL,
|
|
`auto_join` tinyint(1) default 0,
|
|
`join_message` varchar(255) NOT NULL,
|
|
`chat_history_size` int(4) NOT NULL,
|
|
`id_server` tinyint(3) NOT NULL DEFAULT 1,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_rooms_gametypes` (
|
|
`id_room` int(7) unsigned NOT NULL,
|
|
`name` varchar(50) NOT NULL,
|
|
`id_server` tinyint(3) NOT NULL DEFAULT 1,
|
|
FOREIGN KEY(`id_room`) REFERENCES `cockatrice_rooms`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- games
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_games` (
|
|
`room_name` varchar(255) NOT NULL,
|
|
`id` int(7) unsigned NOT NULL auto_increment,
|
|
`descr` varchar(50) default NULL,
|
|
`creator_name` varchar(35) NOT NULL,
|
|
`password` tinyint(1) NOT NULL,
|
|
`game_types` varchar(255) NOT NULL,
|
|
`player_count` tinyint(3) NOT NULL,
|
|
`time_started` datetime default NULL,
|
|
`time_finished` datetime default NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_games_players` (
|
|
`id_game` int(7) unsigned zerofill NOT NULL,
|
|
`player_name` varchar(35) NOT NULL,
|
|
FOREIGN KEY(`id_game`) REFERENCES `cockatrice_games`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- Note: an empty row with id_game = NULL is created when the game is created,
|
|
-- and then updated when the game ends with the full replay data.
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_replays` (
|
|
`id` int(7) NOT NULL AUTO_INCREMENT,
|
|
`id_game` int(7) unsigned NULL,
|
|
`duration` int(7) NOT NULL,
|
|
`replay` mediumblob NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY(`id_game`) REFERENCES `cockatrice_games`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_replays_access` (
|
|
`id_game` int(7) unsigned NOT NULL,
|
|
`id_player` int(7) unsigned NOT NULL,
|
|
`replay_name` varchar(255) NOT NULL,
|
|
`do_not_hide` tinyint(1) NOT NULL,
|
|
KEY `id_player` (`id_player`),
|
|
FOREIGN KEY(`id_game`) REFERENCES `cockatrice_games`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY(`id_player`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- server administration
|
|
|
|
-- Note: unused table
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_servers` (
|
|
`id` mediumint(8) unsigned NOT NULL,
|
|
`ssl_cert` text NOT NULL,
|
|
`hostname` varchar(255) NOT NULL,
|
|
`address` varchar(255) NOT NULL,
|
|
`game_port` mediumint(8) unsigned NOT NULL,
|
|
`control_port` mediumint(9) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_uptime` (
|
|
`id_server` tinyint(3) NOT NULL,
|
|
`timest` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
`uptime` int(11) NOT NULL,
|
|
`users_count` int(11) NOT NULL,
|
|
`mods_count` int(11) NOT NULL DEFAULT 0,
|
|
`mods_list` TEXT,
|
|
`games_count` int(11) NOT NULL,
|
|
`rx_bytes` int(11) NOT NULL,
|
|
`tx_bytes` int(11) NOT NULL,
|
|
PRIMARY KEY (`timest`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_servermessages` (
|
|
`id_server` tinyint(3) not null default 1,
|
|
`timest` datetime NOT NULL default '0000-00-00 00:00:00',
|
|
`message` text,
|
|
PRIMARY KEY (`timest`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_sessions` (
|
|
`id` int(9) NOT NULL AUTO_INCREMENT,
|
|
`user_name` varchar(35) NOT NULL,
|
|
`id_server` tinyint(3) NOT NULL,
|
|
`ip_address` varchar(45) NOT NULL,
|
|
`start_time` datetime NOT NULL,
|
|
`end_time` datetime DEFAULT NULL,
|
|
`clientid` varchar(15) NOT NULL,
|
|
`connection_type` ENUM('tcp', 'websocket'),
|
|
PRIMARY KEY (`id`),
|
|
KEY `username` (`user_name`),
|
|
INDEX `idx_start_time` (`start_time`),
|
|
INDEX `idx_clientid` (`clientid`),
|
|
INDEX `idx_ip_address` (`ip_address`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- server moderation
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_bans` (
|
|
`user_name` varchar(35) NOT NULL,
|
|
`ip_address` varchar(45) NOT NULL,
|
|
`id_admin` int(7) unsigned zerofill NOT NULL,
|
|
`time_from` datetime NOT NULL,
|
|
`minutes` int(6) NOT NULL,
|
|
`reason` text NOT NULL,
|
|
`visible_reason` text NOT NULL,
|
|
`clientid` varchar(15) NOT NULL,
|
|
PRIMARY KEY (`user_name`,`time_from`),
|
|
KEY `time_from` (`time_from`,`ip_address`),
|
|
KEY `ip_address` (`ip_address`),
|
|
INDEX `idx_user_name` (`user_name`),
|
|
FOREIGN KEY(`id_admin`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_warnings` (
|
|
`user_id` int(7) unsigned NOT NULL,
|
|
`user_name` varchar(35) NOT NULL,
|
|
`mod_name` varchar(35) NOT NULL,
|
|
`reason` text NOT NULL,
|
|
`time_of` datetime NOT NULL,
|
|
`clientid` varchar(15) NOT NULL,
|
|
PRIMARY KEY (`user_id`,`time_of`),
|
|
INDEX `idx_time_of` (`time_of`),
|
|
INDEX `idx_user_name` (`user_name`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_reports` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`reporter_id` int(7) unsigned NULL,
|
|
`reporter_name` varchar(35) NOT NULL,
|
|
`reported_user_id` int(7) unsigned NULL,
|
|
`reported_user_name` varchar(35) NOT NULL,
|
|
`game_id` int(7) unsigned NULL,
|
|
`room_id` int(7) unsigned NULL,
|
|
`category` varchar(255) NOT NULL,
|
|
`description` text NOT NULL,
|
|
`chat_log` mediumtext NULL,
|
|
`created_at` datetime NOT NULL,
|
|
`resolution_time` datetime NULL,
|
|
`status` enum('open','assigned','resolved','dismissed') NOT NULL DEFAULT 'open',
|
|
`assigned_to` int(7) unsigned NULL,
|
|
`resolved_by` int(7) unsigned NULL,
|
|
`resolution_note` text,
|
|
`notified` tinyint(1) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_status` (`status`),
|
|
INDEX `idx_created` (`created_at`),
|
|
INDEX `idx_reporter_id_created` (`reporter_id`, `created_at`),
|
|
INDEX `idx_reported_user_name` (`reported_user_name`),
|
|
INDEX `idx_status_created` (`status`, `created_at`),
|
|
FOREIGN KEY (`reporter_id`) REFERENCES `cockatrice_users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
FOREIGN KEY (`reported_user_id`) REFERENCES `cockatrice_users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
FOREIGN KEY (`assigned_to`) REFERENCES `cockatrice_users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
FOREIGN KEY (`resolved_by`) REFERENCES `cockatrice_users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_report_comments` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`report_id` int(11) unsigned NOT NULL,
|
|
`author_name` varchar(35) NOT NULL,
|
|
`author_id` int(7) unsigned NULL,
|
|
`comment_text` text NOT NULL,
|
|
`created_at` datetime NOT NULL,
|
|
`is_moderator` tinyint(1) NOT NULL DEFAULT 0,
|
|
`notified` tinyint(1) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_report_id` (`report_id`),
|
|
INDEX `idx_notified` (`notified`),
|
|
FOREIGN KEY (`report_id`) REFERENCES `cockatrice_reports`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY (`author_id`) REFERENCES `cockatrice_users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_log` (
|
|
`log_time` datetime NOT NULL,
|
|
`sender_id` int(7) unsigned NULL,
|
|
`sender_name` varchar(35) NOT NULL,
|
|
`sender_ip` varchar(45) NOT NULL,
|
|
`log_message` text NOT NULL,
|
|
`target_type` ENUM('room', 'game', 'chat'),
|
|
`target_id` int(7) NULL,
|
|
`target_name` varchar(50) NOT NULL,
|
|
KEY `sender_name` (`sender_name`),
|
|
KEY `sender_ip` (`sender_ip`),
|
|
KEY `target_id` (`target_id`),
|
|
KEY `target_name` (`target_name`),
|
|
INDEX `idx_log_time` (`log_time`),
|
|
FOREIGN KEY(`sender_id`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
-- No FK on target_id, it can be zero
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_activation_emails` (
|
|
`name` varchar(35) NOT NULL,
|
|
FOREIGN KEY(`name`) REFERENCES `cockatrice_users`(`name`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_user_analytics` (
|
|
`id` int(7) unsigned zerofill NOT NULL,
|
|
`client_ver` varchar(35) NOT NULL,
|
|
`last_login` datetime NOT NULL,
|
|
`notes` varchar(255) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_last_login` (`last_login`),
|
|
FOREIGN KEY(`id`) REFERENCES `cockatrice_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_donations` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`username` varchar(35) DEFAULT NULL,
|
|
`email` varchar(255) DEFAULT NULL,
|
|
`payment_pre_fee` double DEFAULT NULL,
|
|
`payment_post_fee` double DEFAULT NULL,
|
|
`term_length` int(11) DEFAULT NULL,
|
|
`date` varchar(255) DEFAULT NULL,
|
|
`pp_type` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_forgot_password` (
|
|
`id` int(7) unsigned zerofill NOT NULL auto_increment,
|
|
`name` varchar(35) NOT NULL,
|
|
`requestDate` datetime NOT NULL default '0000-00-00 00:00:00',
|
|
`emailed` tinyint(1) NOT NULL default 0,
|
|
PRIMARY KEY (`id`),
|
|
KEY `user_name` (`name`),
|
|
INDEX `idx_emailed` (`emailed`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_audit` (
|
|
`id` int(7) unsigned zerofill NOT NULL auto_increment,
|
|
`id_server` tinyint(3) NOT NULL,
|
|
`name` varchar(35) NOT NULL,
|
|
`ip_address` varchar(45) NOT NULL,
|
|
`clientid` varchar(15) NOT NULL,
|
|
`incidentDate` datetime NOT NULL default '0000-00-00 00:00:00',
|
|
`action` varchar(35) NOT NULL,
|
|
`results` ENUM('fail', 'success') NOT NULL DEFAULT 'fail',
|
|
`details` varchar(255) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `user_name` (`name`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `cockatrice_card_art_name_rules` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`card_name` varchar(255) NOT NULL,
|
|
`card_provider_id` varchar(255) NOT NULL,
|
|
`mode` enum('ALLOW','DENY') NOT NULL,
|
|
`reason` varchar(255) DEFAULT NULL,
|
|
`created_by` int(7) unsigned DEFAULT NULL,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uniq_provider_card_name` (`card_provider_id`, `card_name`),
|
|
KEY `idx_mode` (`mode`),
|
|
FOREIGN KEY (`created_by`) REFERENCES `cockatrice_users`(`id`)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
|