ezyarticle-scripts.sql

/*
 * Copyright 2022 youngmonkeys.org
 *
 * Licensed under the ezyplatform, Version 1.0.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     https://youngmonkeys.org/licenses/ezyplatform-1.0.0.txt
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
*/

CREATE TABLE IF NOT EXISTS `ezyarticle_post_meta` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `post_id` bigint unsigned NOT NULL,
    `meta_key` varchar(256) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
    `meta_value` varchar(12000) COLLATE utf8mb4_unicode_520_ci,
    PRIMARY KEY (`id`),
    INDEX `index_post_id` (`post_id`),
    INDEX `index_meta_key` (`meta_key`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezyarticle_post_terms` (
    `post_id` bigint unsigned NOT NULL,
    `term_id` bigint unsigned NOT NULL,
    `term_order` int NOT NULL DEFAULT 0,
    PRIMARY KEY (`post_id`,`term_id`),
    INDEX `index_post_id` (`post_id`),
    INDEX `index_term_id` (`term_id`),
    INDEX `index_term_order` (`term_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezyarticle_terms` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(256) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `slug` varchar(256) COLLATE utf8mb4_unicode_520_ci,
    `term_type` varchar(25) COLLATE utf8mb4_unicode_520_ci,
    `parent_id` bigint NOT NULL DEFAULT 0,
    `description` varchar(500) COLLATE utf8mb4_unicode_520_ci,
    PRIMARY KEY (`id`),
    UNIQUE KEY `key_name` (`name`),
    UNIQUE KEY `key_slug` (`slug`),
    INDEX `index_term_type` (`term_type`),
    INDEX `index_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezyarticle_comments` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `post_id` bigint unsigned NOT NULL,
    `author_user_id` bigint unsigned NOT NULL default 0,
    `author_name` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `author_email` varchar(256) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `author_url` varchar(256) COLLATE utf8mb4_unicode_520_ci,
    `content` varchar(12000) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `status` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `parent_id` bigint unsigned NOT NULL DEFAULT 0,
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `index_post_id` (`post_id`),
    INDEX `index_author_user_id` (`author_user_id`),
    INDEX `index_author_name` (`author_name`),
    INDEX `index_author_email` (`author_email`),
    INDEX `index_status` (`status`),
    INDEX `index_parent_id` (`parent_id`),
    INDEX `index_created_at` (`created_at`),
    INDEX `index_updated_at` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezyarticle_posts` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `author_admin_id` bigint unsigned NOT NULL DEFAULT 0,
    `author_user_id` bigint unsigned NOT NULL DEFAULT 0,
    `post_type` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'POST',
    `title` varchar(1200) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `content` mediumtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `featured_image_id` int NOT NULL default 0,
    `slug` varchar(256) COLLATE utf8mb4_unicode_520_ci,
    `status` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'PUBLISH',
    `comment_status` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'OPEN',
    `priority` int NOT NULL DEFAULT 0,
    `password` varchar(256) COLLATE utf8mb4_unicode_520_ci,
    `comment_count` bigint NOT NULL DEFAULT 0,
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `index_author_admin_id` (`author_admin_id`),
    INDEX `index_author_user_id` (`author_user_id`),
    INDEX `index_post_type` (`post_type`),
    UNIQUE KEY `key_slug` (`slug`),
    INDEX `index_status` (`status`),
    INDEX `index_comment_status` (`comment_status`),
    INDEX `index_priority` (`priority`),
    INDEX `index_comment_count` (`comment_count`),
    INDEX `index_created_at` (`created_at`),
    INDEX `index_updated_at` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezyarticle_post_histories` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `parent_id` bigint unsigned NOT NULL,
    `title` varchar(1200) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `content` mediumtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `featured_image_id` int NOT NULL default 0,
    `slug` varchar(256) COLLATE utf8mb4_unicode_520_ci,
    `status` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'PUBLISH',
    `comment_status` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'OPEN',
    `password` varchar(256) COLLATE utf8mb4_unicode_520_ci,
    `created_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `index_parent_id` (`parent_id`),
    INDEX `index_slug` (`slug`),
    INDEX `index_status` (`status`),
    INDEX `index_comment_status` (`comment_status`),
    INDEX `index_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezyarticle_menus` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(256) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
    `display_name` varchar(256) COLLATE utf8mb4_unicode_520_ci,
    PRIMARY KEY (`id`),
    INDEX `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezyarticle_menu_items` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `menu_id` bigint unsigned NOT NULL,
    `parent_id` bigint unsigned NOT NULL DEFAULT 0,
    `menu_item_level` int NOT NULL DEFAULT 0,
    `link_type` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `link_id` bigint NOT NULL DEFAULT 0,
    `custom_link` varchar(256) COLLATE utf8mb4_unicode_520_ci DEFAULT '',
    `order_value` bigint NOT NULL DEFAULT 0,
    `link_text` varchar(256) COLLATE utf8mb4_unicode_520_ci DEFAULT '',
    PRIMARY KEY (`id`),
    INDEX `index_menu_id` (`menu_id`),
    INDEX `index_parent_id` (`parent_id`),
    INDEX `index_menu_item_level` (`menu_item_level`),
    INDEX `index_link_type` (`link_type`),
    INDEX `index_link_id` (`link_id`),
    INDEX `index_order_value` (`order_value`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;