chie/chie.sql

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.18-MariaDB-1:10.4.18+maria~bionic-log - mariadb.org binary distribution
-- Server OS:                    debian-linux-gnu
-- HeidiSQL Version:             11.2.0.6213
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!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 */;


-- Dumping database structure for flash_forum
CREATE DATABASE IF NOT EXISTS `flash_forum` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;
USE `flash_forum`;

-- Dumping structure for table flash_forum.fmf_categories
CREATE TABLE IF NOT EXISTS `fmf_categories` (
  `cat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cat_order` smallint(6) NOT NULL DEFAULT 0,
  `cat_type` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `cat_parent` int(10) unsigned NOT NULL DEFAULT 0,
  `cat_variation` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `cat_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `cat_name` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `cat_description` text COLLATE utf8mb4_bin DEFAULT NULL,
  `cat_link` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `cat_count_topics` int(10) unsigned NOT NULL DEFAULT 0,
  `cat_count_posts` int(10) unsigned NOT NULL DEFAULT 0,
  `cat_last_post_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`cat_id`),
  KEY `categories_type_key` (`cat_type`),
  KEY `categories_parent_key` (`cat_parent`),
  KEY `categories_order_key` (`cat_order`),
  KEY `categories_last_post_foreign` (`cat_last_post_id`),
  CONSTRAINT `categories_last_post_foreign` FOREIGN KEY (`cat_last_post_id`) REFERENCES `fmf_posts` (`post_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Data exporting was unselected.

-- Dumping structure for table flash_forum.fmf_posts
CREATE TABLE IF NOT EXISTS `fmf_posts` (
  `post_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) unsigned NOT NULL,
  `cat_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `post_type` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `post_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `post_edited` timestamp NULL DEFAULT NULL,
  `post_deleted` timestamp NULL DEFAULT NULL,
  `post_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`post_id`),
  KEY `posts_topic_foreign` (`topic_id`),
  KEY `posts_user_foreign` (`user_id`),
  KEY `posts_category_foreign` (`cat_id`),
  KEY `posts_created_key` (`post_created`),
  KEY `posts_deleted_key` (`post_deleted`),
  FULLTEXT KEY `posts_text_fulltext` (`post_text`),
  CONSTRAINT `posts_category_foreign` FOREIGN KEY (`cat_id`) REFERENCES `fmf_categories` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `posts_topic_foreign` FOREIGN KEY (`topic_id`) REFERENCES `fmf_topics` (`topic_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `posts_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `fmf_users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Data exporting was unselected.

-- Dumping structure for table flash_forum.fmf_sessions
CREATE TABLE IF NOT EXISTS `fmf_sessions` (
  `sess_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `sess_key` char(64) COLLATE utf8mb4_bin NOT NULL,
  `sess_created` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`sess_id`),
  UNIQUE KEY `sessions_key_unique` (`sess_key`),
  KEY `sessions_user_foreign` (`user_id`),
  KEY `sessions_created_key` (`sess_created`),
  CONSTRAINT `sessions_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `fmf_users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Data exporting was unselected.

-- Dumping structure for table flash_forum.fmf_topics
CREATE TABLE IF NOT EXISTS `fmf_topics` (
  `topic_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cat_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `topic_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `topic_bumped` timestamp NULL DEFAULT NULL,
  `topic_locked` timestamp NULL DEFAULT NULL,
  `topic_resolved` timestamp NULL DEFAULT NULL,
  `topic_confirmed` timestamp NULL DEFAULT NULL,
  `topic_title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `topic_count_replies` int(10) unsigned NOT NULL DEFAULT 0,
  `topic_last_post_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`topic_id`),
  KEY `topics_category_foreign` (`cat_id`),
  KEY `topics_bumped_key` (`topic_bumped`),
  KEY `topics_title_key` (`topic_title`),
  KEY `topics_user_foreign` (`user_id`),
  KEY `topic_last_post_foreign` (`topic_last_post_id`),
  CONSTRAINT `topic_last_post_foreign` FOREIGN KEY (`topic_last_post_id`) REFERENCES `fmf_posts` (`post_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `topics_category_foreign` FOREIGN KEY (`cat_id`) REFERENCES `fmf_categories` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `fmf_users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Data exporting was unselected.

-- Dumping structure for table flash_forum.fmf_track
CREATE TABLE IF NOT EXISTS `fmf_track` (
  `cat_id` int(10) unsigned NOT NULL,
  `topic_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `track_timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  UNIQUE KEY `track_unique` (`topic_id`,`user_id`),
  KEY `track_category_foreign` (`cat_id`),
  KEY `track_topic_foreign` (`topic_id`),
  KEY `track_user_foreign` (`user_id`),
  KEY `track_timestamp_key` (`track_timestamp`),
  CONSTRAINT `track_category_foreign` FOREIGN KEY (`cat_id`) REFERENCES `fmf_categories` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `track_topic_foreign` FOREIGN KEY (`topic_id`) REFERENCES `fmf_topics` (`topic_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `track_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `fmf_users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Data exporting was unselected.

-- Dumping structure for table flash_forum.fmf_users
CREATE TABLE IF NOT EXISTS `fmf_users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_email_verification` char(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `user_password` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `user_moderator` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `user_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `user_ip_created` varbinary(16) NOT NULL,
  `user_date_format` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT 'D Y-m-d H:i:s T',
  `user_time_zone` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT 'UTC',
  `user_flags` int(10) unsigned NOT NULL DEFAULT 0,
  `user_banned` timestamp NULL DEFAULT NULL,
  `user_banned_reason` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `users_login_unique` (`user_login`),
  UNIQUE KEY `users_email_unique` (`user_email`),
  UNIQUE KEY `users_email_verification_unique` (`user_email_verification`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Data exporting was unselected.

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;