686 lines
35 KiB
PHP
686 lines
35 KiB
PHP
<?php
|
|
use Index\Data\IDbConnection;
|
|
use Index\Data\Migration\IDbMigration;
|
|
|
|
// Switching to the Index migration system!!!!!!
|
|
|
|
final class InitialStructureNdx_20230107_023235 implements IDbMigration {
|
|
public function migrate(IDbConnection $conn): void {
|
|
$hasMszTrack = false;
|
|
|
|
// check if the old migrations table exists
|
|
$tables = $conn->query('SHOW TABLES');
|
|
while($tables->next())
|
|
if($tables->getString(0) === 'msz_migrations') {
|
|
$hasMszTrack = true;
|
|
break;
|
|
}
|
|
|
|
// nuke it and bail
|
|
if($hasMszTrack) {
|
|
$conn->execute('DROP TABLE msz_migrations');
|
|
return;
|
|
}
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_roles (
|
|
role_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
role_hierarchy INT(11) NOT NULL DEFAULT 1,
|
|
role_name VARCHAR(255) NOT NULL,
|
|
role_title VARCHAR(64) DEFAULT NULL,
|
|
role_description TEXT DEFAULT NULL,
|
|
role_hidden TINYINT(1) NOT NULL DEFAULT 0,
|
|
role_can_leave TINYINT(1) NOT NULL DEFAULT 0,
|
|
role_colour INT(11) DEFAULT NULL,
|
|
role_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (role_id),
|
|
KEY roles_hierarchy_index (role_hierarchy),
|
|
KEY roles_hidden_index (role_hidden)
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_users (
|
|
user_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(255) NOT NULL,
|
|
password VARCHAR(255) DEFAULT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
register_ip VARBINARY(16) NOT NULL,
|
|
last_ip VARBINARY(16) NOT NULL,
|
|
user_super TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
|
|
user_country CHAR(2) NOT NULL DEFAULT \'XX\',
|
|
user_colour INT(11) DEFAULT NULL,
|
|
user_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
user_active TIMESTAMP NULL DEFAULT NULL,
|
|
user_deleted TIMESTAMP NULL DEFAULT NULL,
|
|
display_role INT(10) UNSIGNED DEFAULT NULL,
|
|
user_totp_key CHAR(26) DEFAULT NULL,
|
|
user_about_content TEXT DEFAULT NULL,
|
|
user_about_parser TINYINT(4) NOT NULL DEFAULT 0,
|
|
user_signature_content TEXT DEFAULT NULL,
|
|
user_signature_parser TINYINT(4) NOT NULL DEFAULT 0,
|
|
user_birthdate DATE DEFAULT NULL,
|
|
user_background_settings TINYINT(4) DEFAULT 0,
|
|
user_title VARCHAR(64) DEFAULT NULL,
|
|
PRIMARY KEY (user_id),
|
|
UNIQUE KEY users_username_unique (username),
|
|
UNIQUE KEY users_email_unique (email),
|
|
KEY users_display_role_foreign (display_role),
|
|
KEY users_indices (user_country, user_created, user_active, user_deleted, user_birthdate),
|
|
CONSTRAINT users_display_role_foreign
|
|
FOREIGN KEY (display_role)
|
|
REFERENCES msz_roles (role_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_audit_log (
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
log_action VARCHAR(50) NOT NULL,
|
|
log_params TEXT NOT NULL,
|
|
log_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
log_ip VARBINARY(16) DEFAULT NULL,
|
|
log_country CHAR(2) NOT NULL DEFAULT \'XX\',
|
|
KEY audit_log_user_id_foreign (user_id),
|
|
KEY audit_log_created_index (log_created),
|
|
CONSTRAINT audit_log_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_auth_tfa (
|
|
user_id INT(10) UNSIGNED NOT NULL,
|
|
tfa_token CHAR(32) NOT NULL,
|
|
tfa_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
UNIQUE KEY auth_tfa_token_unique (tfa_token),
|
|
KEY auth_tfa_user_foreign (user_id),
|
|
KEY auth_tfa_created_index (tfa_created),
|
|
CONSTRAINT auth_tfa_user_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_changelog_changes (
|
|
change_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
change_action INT(10) UNSIGNED DEFAULT NULL,
|
|
change_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
change_log VARCHAR(255) NOT NULL,
|
|
change_text TEXT DEFAULT NULL,
|
|
PRIMARY KEY (change_id),
|
|
KEY changes_user_foreign (user_id),
|
|
KEY changes_action_index (change_action),
|
|
KEY changes_created_index (change_created),
|
|
CONSTRAINT changes_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_changelog_tags (
|
|
tag_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
tag_name VARCHAR(255) NOT NULL,
|
|
tag_description TEXT DEFAULT NULL,
|
|
tag_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
tag_archived TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (tag_id),
|
|
UNIQUE KEY tag_name (tag_name),
|
|
KEY tag_archived (tag_archived)
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_changelog_change_tags (
|
|
change_id INT(10) UNSIGNED NOT NULL,
|
|
tag_id INT(10) UNSIGNED NOT NULL,
|
|
UNIQUE KEY change_tag_unique (change_id, tag_id),
|
|
KEY tag_id_foreign_key (tag_id),
|
|
CONSTRAINT change_id_foreign_key
|
|
FOREIGN KEY (change_id)
|
|
REFERENCES msz_changelog_changes (change_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT tag_id_foreign_key
|
|
FOREIGN KEY (tag_id)
|
|
REFERENCES msz_changelog_tags (tag_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_comments_categories (
|
|
category_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
category_name VARCHAR(255) NOT NULL,
|
|
owner_id INT(10) UNSIGNED DEFAULT NULL,
|
|
category_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
category_locked TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (category_id),
|
|
UNIQUE KEY comments_categories_name_unique (category_name),
|
|
KEY comments_categories_locked_index (category_locked),
|
|
KEY comments_categories_owner_foreign (owner_id),
|
|
CONSTRAINT comments_categories_owner_foreign
|
|
FOREIGN KEY (owner_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_comments_posts (
|
|
comment_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
category_id INT(10) UNSIGNED NOT NULL,
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
comment_reply_to INT(10) UNSIGNED DEFAULT NULL,
|
|
comment_text TEXT NOT NULL,
|
|
comment_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
comment_pinned TIMESTAMP NULL DEFAULT NULL,
|
|
comment_edited TIMESTAMP NULL DEFAULT NULL ON UPDATE current_timestamp(),
|
|
comment_deleted TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (comment_id),
|
|
KEY comments_posts_category_foreign (category_id),
|
|
KEY comments_posts_user_foreign (user_id),
|
|
KEY comments_posts_reply_id (comment_reply_to),
|
|
KEY comments_posts_dates (comment_created, comment_pinned, comment_edited, comment_deleted),
|
|
CONSTRAINT comments_posts_category_foreign
|
|
FOREIGN KEY (category_id)
|
|
REFERENCES msz_comments_categories (category_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT comments_posts_user_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_comments_votes (
|
|
comment_id INT(10) UNSIGNED NOT NULL,
|
|
user_id INT(10) UNSIGNED NOT NULL,
|
|
comment_vote TINYINT(4) NOT NULL DEFAULT 0,
|
|
UNIQUE KEY comments_vote_unique (comment_id, user_id),
|
|
KEY comments_vote_user_foreign (user_id),
|
|
KEY comments_vote_index (comment_vote),
|
|
CONSTRAINT comment_vote_id
|
|
FOREIGN KEY (comment_id)
|
|
REFERENCES msz_comments_posts (comment_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT comment_vote_user
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_config (
|
|
config_name varchar(100) NOT NULL,
|
|
config_value blob NOT NULL DEFAULT \'\',
|
|
PRIMARY KEY (config_name)
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_emoticons (
|
|
emote_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
emote_order MEDIUMINT(9) NOT NULL DEFAULT 0,
|
|
emote_hierarchy INT(11) NOT NULL DEFAULT 0,
|
|
emote_url VARCHAR(255) NOT NULL,
|
|
PRIMARY KEY (emote_id),
|
|
UNIQUE KEY emotes_url (emote_url),
|
|
KEY emotes_order (emote_order),
|
|
KEY emotes_hierarchy (emote_hierarchy)
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_emoticons_strings (
|
|
emote_id INT(10) UNSIGNED NOT NULL,
|
|
emote_string_order MEDIUMINT(9) NOT NULL DEFAULT 0,
|
|
emote_string VARCHAR(50) CHARACTER SET ascii COLLATE ascii_general_nopad_ci NOT NULL,
|
|
UNIQUE KEY string_unique (emote_string),
|
|
KEY string_emote_foreign (emote_id),
|
|
KEY string_order_key (emote_string_order),
|
|
CONSTRAINT string_emote_foreign
|
|
FOREIGN KEY (emote_id)
|
|
REFERENCES msz_emoticons (emote_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_forum_categories (
|
|
forum_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
forum_order INT(10) UNSIGNED NOT NULL DEFAULT 1,
|
|
forum_parent INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
forum_name VARCHAR(255) NOT NULL,
|
|
forum_type TINYINT(4) NOT NULL DEFAULT 0,
|
|
forum_description TEXT DEFAULT NULL,
|
|
forum_icon VARCHAR(50) DEFAULT NULL,
|
|
forum_colour INT(10) UNSIGNED DEFAULT NULL,
|
|
forum_link VARCHAR(255) DEFAULT NULL,
|
|
forum_link_clicks INT(10) UNSIGNED DEFAULT NULL,
|
|
forum_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
forum_archived TINYINT(1) NOT NULL DEFAULT 0,
|
|
forum_hidden TINYINT(1) NOT NULL DEFAULT 0,
|
|
forum_count_topics INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
forum_count_posts INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (forum_id),
|
|
KEY forum_order_index (forum_order),
|
|
KEY forum_parent_index (forum_parent),
|
|
KEY forum_type_index (forum_type),
|
|
KEY forum_link_clicks_index (forum_link_clicks),
|
|
KEY forum_hidden_index (forum_hidden)
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_forum_permissions (
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
role_id INT(10) UNSIGNED DEFAULT NULL,
|
|
forum_id INT(10) UNSIGNED NOT NULL,
|
|
forum_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
forum_perms_deny INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
UNIQUE KEY forum_permissions_unique (user_id, role_id, forum_id),
|
|
KEY forum_permissions_forum_id (forum_id),
|
|
KEY forum_permissions_role_id (role_id),
|
|
CONSTRAINT forum_permissions_forum_id_foreign
|
|
FOREIGN KEY (forum_id)
|
|
REFERENCES msz_forum_categories (forum_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT forum_permissions_role_id_foreign
|
|
FOREIGN KEY (role_id)
|
|
REFERENCES msz_roles (role_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT forum_permissions_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_forum_topics (
|
|
topic_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
forum_id INT(10) UNSIGNED NOT NULL,
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
topic_type TINYINT(4) NOT NULL DEFAULT 0,
|
|
topic_title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
|
|
topic_count_views INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
topic_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
topic_bumped TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
topic_deleted TIMESTAMP NULL DEFAULT NULL,
|
|
topic_locked TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (topic_id),
|
|
KEY topics_forum_id_foreign (forum_id),
|
|
KEY topics_user_id_foreign (user_id),
|
|
KEY topics_type_index (topic_type),
|
|
KEY topics_created_index (topic_created),
|
|
KEY topics_bumped_index (topic_bumped),
|
|
KEY topics_deleted_index (topic_deleted),
|
|
KEY topics_locked_index (topic_locked),
|
|
FULLTEXT KEY topics_fulltext (topic_title),
|
|
CONSTRAINT topics_forum_id_foreign
|
|
FOREIGN KEY (forum_id)
|
|
REFERENCES msz_forum_categories (forum_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT topics_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_forum_topics_track (
|
|
user_id INT(10) UNSIGNED NOT NULL,
|
|
topic_id INT(10) UNSIGNED NOT NULL,
|
|
forum_id INT(10) UNSIGNED NOT NULL,
|
|
track_last_read TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
UNIQUE KEY topics_track_unique (user_id, topic_id),
|
|
KEY topics_track_topic_id_foreign (topic_id),
|
|
KEY topics_track_user_id_foreign (user_id),
|
|
KEY topics_track_forum_id_foreign (forum_id),
|
|
KEY forum_track_last_read (track_last_read),
|
|
CONSTRAINT topics_track_forum_id_foreign
|
|
FOREIGN KEY (forum_id)
|
|
REFERENCES msz_forum_categories (forum_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT topics_track_topic_id_foreign
|
|
FOREIGN KEY (topic_id)
|
|
REFERENCES msz_forum_topics (topic_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT topics_track_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_forum_posts (
|
|
post_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
topic_id INT(10) UNSIGNED NOT NULL,
|
|
forum_id INT(10) UNSIGNED NOT NULL,
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
post_ip VARBINARY(16) NOT NULL,
|
|
post_text MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
|
|
post_parse TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
|
|
post_display_signature TINYINT(4) UNSIGNED NOT NULL DEFAULT 1,
|
|
post_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
post_edited TIMESTAMP NULL DEFAULT NULL,
|
|
post_deleted TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (post_id),
|
|
KEY posts_topic_id_foreign (topic_id),
|
|
KEY posts_forum_id_foreign (forum_id),
|
|
KEY posts_user_id_foreign (user_id),
|
|
KEY posts_created_index (post_created),
|
|
KEY posts_deleted_index (post_deleted),
|
|
KEY posts_parse_index (post_parse),
|
|
KEY posts_edited_index (post_edited),
|
|
KEY posts_display_signature_index (post_display_signature),
|
|
KEY posts_ip_index (post_ip),
|
|
FULLTEXT KEY posts_fulltext (post_text),
|
|
CONSTRAINT posts_forum_id_foreign
|
|
FOREIGN KEY (forum_id)
|
|
REFERENCES msz_forum_categories (forum_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT posts_topic_id_foreign
|
|
FOREIGN KEY (topic_id)
|
|
REFERENCES msz_forum_topics (topic_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT posts_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_login_attempts (
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
attempt_success TINYINT(1) NOT NULL,
|
|
attempt_ip VARBINARY(16) NOT NULL,
|
|
attempt_country CHAR(2) NOT NULL DEFAULT \'XX\',
|
|
attempt_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
attempt_user_agent VARCHAR(255) NOT NULL DEFAULT \'\',
|
|
KEY login_attempts_user_id_foreign (user_id),
|
|
KEY login_attempts_created_index (attempt_created),
|
|
KEY login_attempts_success_index (attempt_success),
|
|
KEY login_attempts_ip_index (attempt_ip),
|
|
CONSTRAINT login_attempts_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_news_categories (
|
|
category_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
category_name VARCHAR(255) NOT NULL,
|
|
category_description TEXT NOT NULL,
|
|
category_is_hidden TINYINT(1) NOT NULL DEFAULT 0,
|
|
category_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (category_id),
|
|
KEY news_categories_is_hidden_index (category_is_hidden)
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_news_posts (
|
|
post_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
category_id INT(10) UNSIGNED NOT NULL,
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
comment_section_id INT(10) UNSIGNED DEFAULT NULL,
|
|
post_is_featured TINYINT(1) NOT NULL DEFAULT 0,
|
|
post_title VARCHAR(255) NOT NULL,
|
|
post_text TEXT NOT NULL,
|
|
post_scheduled TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
post_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
post_updated TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
post_deleted TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (post_id),
|
|
KEY news_posts_category_id_foreign (category_id),
|
|
KEY news_posts_user_id_foreign (user_id),
|
|
KEY news_posts_comment_section (comment_section_id),
|
|
KEY news_posts_featured_index (post_is_featured),
|
|
KEY news_posts_scheduled_index (post_scheduled),
|
|
KEY news_posts_created_index (post_created),
|
|
KEY news_posts_updated_index (post_updated),
|
|
KEY news_posts_deleted_index (post_deleted),
|
|
FULLTEXT KEY news_posts_fulltext (post_title, post_text),
|
|
CONSTRAINT news_posts_category_id_foreign
|
|
FOREIGN KEY (category_id)
|
|
REFERENCES msz_news_categories (category_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT news_posts_comment_section
|
|
FOREIGN KEY (comment_section_id)
|
|
REFERENCES msz_comments_categories (category_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT news_posts_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_permissions (
|
|
user_id INT(10) UNSIGNED DEFAULT NULL,
|
|
role_id INT(10) UNSIGNED DEFAULT NULL,
|
|
general_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
general_perms_deny INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
user_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
user_perms_deny INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
changelog_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
changelog_perms_deny INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
news_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
news_perms_deny INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
forum_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
forum_perms_deny INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
comments_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
comments_perms_deny INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
UNIQUE KEY permissions_user_id_unique (user_id),
|
|
UNIQUE KEY permissions_role_id_unique (role_id),
|
|
CONSTRAINT permissions_role_id_foreign
|
|
FOREIGN KEY (role_id)
|
|
REFERENCES msz_roles (role_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT permissions_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_profile_fields (
|
|
field_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
field_order INT(11) NOT NULL DEFAULT 0,
|
|
field_key VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
|
|
field_title VARCHAR(50) NOT NULL,
|
|
field_regex VARCHAR(255) NOT NULL,
|
|
PRIMARY KEY (field_id),
|
|
UNIQUE KEY profile_fields_key_unique (field_key),
|
|
KEY profile_fields_order_key (field_order)
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_profile_fields_formats (
|
|
format_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
field_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
|
|
format_regex VARCHAR(255) DEFAULT NULL,
|
|
format_link VARCHAR(255) DEFAULT NULL,
|
|
format_display VARCHAR(255) NOT NULL DEFAULT \'%s\',
|
|
PRIMARY KEY (format_id),
|
|
KEY profile_field_format_field_foreign (field_id),
|
|
CONSTRAINT profile_field_format_field_foreign
|
|
FOREIGN KEY (field_id)
|
|
REFERENCES msz_profile_fields (field_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_profile_fields_values (
|
|
field_id INT(10) UNSIGNED NOT NULL,
|
|
user_id INT(10) UNSIGNED NOT NULL,
|
|
format_id INT(10) UNSIGNED NOT NULL,
|
|
field_value VARCHAR(255) NOT NULL,
|
|
PRIMARY KEY (field_id, user_id),
|
|
KEY profile_fields_values_format_foreign (format_id),
|
|
KEY profile_fields_values_user_foreign (user_id),
|
|
KEY profile_fields_values_value_key (field_value),
|
|
CONSTRAINT profile_fields_values_field_foreign
|
|
FOREIGN KEY (field_id)
|
|
REFERENCES msz_profile_fields (field_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT profile_fields_values_format_foreign
|
|
FOREIGN KEY (format_id)
|
|
REFERENCES msz_profile_fields_formats (format_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT profile_fields_values_user_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_sessions (
|
|
session_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id INT(10) UNSIGNED NOT NULL,
|
|
session_key BINARY(64) NOT NULL,
|
|
session_ip VARBINARY(16) NOT NULL,
|
|
session_ip_last VARBINARY(16) DEFAULT NULL,
|
|
session_user_agent VARCHAR(255) NOT NULL,
|
|
session_country CHAR(2) NOT NULL DEFAULT \'XX\',
|
|
session_expires TIMESTAMP NOT NULL DEFAULT (current_timestamp() + INTERVAL 1 MONTH),
|
|
session_expires_bump TINYINT(3) UNSIGNED NOT NULL DEFAULT 1,
|
|
session_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
session_active TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (session_id),
|
|
UNIQUE KEY sessions_key_unique (session_key),
|
|
KEY sessions_user_id_foreign (user_id),
|
|
KEY sessions_expires_index (session_expires),
|
|
KEY sessions_created_index (session_created),
|
|
CONSTRAINT sessions_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_users_password_resets (
|
|
user_id INT(10) UNSIGNED NOT NULL,
|
|
reset_ip VARBINARY(16) NOT NULL,
|
|
reset_requested TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
verification_code CHAR(12) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
|
|
UNIQUE KEY users_password_resets_user_unique (user_id,reset_ip),
|
|
UNIQUE KEY users_password_resets_token_unique (verification_code),
|
|
KEY users_password_resets_created_index (reset_requested),
|
|
CONSTRAINT msz_users_password_resets_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_user_roles (
|
|
user_id INT(10) UNSIGNED NOT NULL,
|
|
role_id INT(10) UNSIGNED NOT NULL,
|
|
UNIQUE KEY user_roles_unique (user_id, role_id),
|
|
KEY user_roles_role_id_foreign (role_id),
|
|
CONSTRAINT user_roles_role_id_foreign
|
|
FOREIGN KEY (role_id)
|
|
REFERENCES msz_roles (role_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT user_roles_user_id_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
|
|
$conn->execute('
|
|
CREATE TABLE msz_user_warnings (
|
|
warning_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id INT(10) UNSIGNED NOT NULL,
|
|
user_ip VARBINARY(16) NOT NULL,
|
|
issuer_id INT(10) UNSIGNED DEFAULT NULL,
|
|
issuer_ip VARBINARY(16) NOT NULL,
|
|
warning_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
warning_duration TIMESTAMP NULL DEFAULT NULL,
|
|
warning_type TINYINT(3) UNSIGNED NOT NULL,
|
|
warning_note VARCHAR(255) NOT NULL,
|
|
warning_note_private TEXT DEFAULT NULL,
|
|
PRIMARY KEY (warning_id),
|
|
KEY user_warnings_user_foreign (user_id),
|
|
KEY user_warnings_issuer_foreign (issuer_id),
|
|
KEY user_warnings_created_index (warning_created),
|
|
KEY user_warnings_duration_index (warning_duration),
|
|
KEY user_warnings_type_index (warning_type),
|
|
KEY user_warnings_user_ip_index (user_ip),
|
|
CONSTRAINT user_warnings_issuer_foreign
|
|
FOREIGN KEY (issuer_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT user_warnings_user_foreign
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES msz_users (user_id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB COLLATE=utf8mb4_bin;
|
|
');
|
|
}
|
|
}
|