X-Git-Url: https://git.mxchange.org/?p=mailer.git;a=blobdiff_plain;f=install%2Ftables.sql;h=aa034d0308412fdd5d01411920bbdb5fd17b0981;hp=3e62332519bfa26962becac845b8cb4812565570;hb=c4be539d249046d6d64d2181c3a1e898fe9a64f5;hpb=c6471ec23e169970a8090e3b560df5a78d95bed4 diff --git a/install/tables.sql b/install/tables.sql index 3e62332519..aa034d0308 100644 --- a/install/tables.sql +++ b/install/tables.sql @@ -1,135 +1,121 @@ DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_admin_menu`; CREATE TABLE `{?_MYSQL_PREFIX?}_admin_menu` ( - `id` BIGINT(22) NOT NULL AUTO_INCREMENT, + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `action` VARCHAR(255) NOT NULL DEFAULT '', `what` VARCHAR(255) NULL DEFAULT NULL, - `title` VARCHAR(50) NOT NULL DEFAULT '', + `title` VARCHAR(255) NOT NULL DEFAULT '', `sort` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, `descr` TEXT NOT NULL, PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Admin menu'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_admins`; CREATE TABLE `{?_MYSQL_PREFIX?}_admins` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `login` VARCHAR(100) NOT NULL DEFAULT '', - `password` VARCHAR(40) NOT NULL DEFAULT '', + `password` VARCHAR(255) NOT NULL DEFAULT '', `email` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`), - UNIQUE KEY (`login`) -) TYPE={?_TABLE_TYPE?}; + UNIQUE INDEX (`login`) +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Administrator accounts'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_cats`; CREATE TABLE `{?_MYSQL_PREFIX?}_cats` ( - `id` BIGINT(22) NOT NULL AUTO_INCREMENT, + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `cat` VARCHAR(255) NOT NULL DEFAULT '', `visible` ENUM('Y','N') NOT NULL DEFAULT 'Y', `sort` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'General categories'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_config`; CREATE TABLE `{?_MYSQL_PREFIX?}_config` ( - `config` tinyint(1) NOT NULL DEFAULT 0, - `pass_len` TINYINT(3) UNSIGNED NOT NULL DEFAULT 5, + `config` TINYINT(1) NOT NULL DEFAULT 0, + `min_password_length` TINYINT(3) UNSIGNED NOT NULL DEFAULT 5, `points_register` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, `points_ref` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, - `least_cats` TINYINT(3) UNSIGNED NOT NULL DEFAULT 5, - `check_double_email` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `check_double_pass` ENUM('Y','N') NOT NULL DEFAULT 'N', - `admin_notify` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `url_tlock` BIGINT(20) UNSIGNED NOT NULL DEFAULT '86400', - `test_text` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `max_tlength` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1000', - `test_subj` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `autosend_active` ENUM('Y','N') NOT NULL DEFAULT 'N', - `max_send` TINYINT(3) UNSIGNED NOT NULL DEFAULT '100', - `url_blacklist` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `auto_purge` TINYINT(3) UNSIGNED NOT NULL DEFAULT '14', - `auto_purge_active` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `last_update` VARCHAR(10) NOT NULL DEFAULT 0, - `unconfirmed` BIGINT(20) UNSIGNED NOT NULL DEFAULT '100', - `profile_lock` BIGINT(20) UNSIGNED NOT NULL DEFAULT '86400', - `online_timeout` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1800', + `url_tlock` BIGINT(20) UNSIGNED NOT NULL DEFAULT 86400, + `online_timeout` BIGINT(20) UNSIGNED NOT NULL DEFAULT 1800, `mad_timestamp` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, `mad_count` BIGINT(20) UNSIGNED NOT NULL, - `profile_update` BIGINT(20) UNSIGNED NOT NULL DEFAULT '15768000', - `send_prof_update` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `resend_profile_update` BIGINT(20) UNSIGNED NOT NULL DEFAULT '172800', - `patch_level` VARCHAR(100) NOT NULL DEFAULT '78', - `patch_ctime` VARCHAR(10) NOT NULL DEFAULT 0, + `patch_level` VARCHAR(100) NOT NULL DEFAULT '0', + `patch_ctime` VARCHAR(10) NOT NULL DEFAULT '0', `guest_stats` ENUM('MEMBERS','MODULES','INACTIVE') NOT NULL DEFAULT 'MEMBERS', `ref_payout` TINYINT(3) UNSIGNED NOT NULL DEFAULT 5, - `activate_xchange` BIGINT(20) UNSIGNED NOT NULL DEFAULT 100, - `order_multi_page` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `display_refid` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `ip_timeout` BIGINT(20) UNSIGNED NOT NULL DEFAULT 86400, `allow_direct_pay` ENUM('Y','N') NOT NULL DEFAULT 'N', PRIMARY KEY (`config`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Configuration'; + +DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_extensions`; +CREATE TABLE `{?_MYSQL_PREFIX?}_extensions` ( + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + `ext_name` VARCHAR(255) NOT NULL DEFAULT '', + `ext_active` ENUM('Y','N') NOT NULL DEFAULT 'N', + `ext_version` VARCHAR(255) NOT NULL DEFAULT 'INVALID', + PRIMARY KEY (`id`) +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Extension registry'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_guest_menu`; CREATE TABLE `{?_MYSQL_PREFIX?}_guest_menu` ( - `id` BIGINT(22) NOT NULL AUTO_INCREMENT, + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `action` VARCHAR(255) NOT NULL DEFAULT '', `what` VARCHAR(255) NULL DEFAULT NULL, - `title` VARCHAR(50) NOT NULL DEFAULT '', + `title` VARCHAR(255) NOT NULL DEFAULT '', `sort` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, `visible` ENUM('Y','N') NOT NULL DEFAULT 'Y', `locked` ENUM('Y','N') NOT NULL DEFAULT 'Y', `counter` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Guest menu'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_max_receive`; CREATE TABLE `{?_MYSQL_PREFIX?}_max_receive` ( - `id` BIGINT(22) NOT NULL AUTO_INCREMENT, - `value` MEDIUMINT(9) NOT NULL DEFAULT 0, + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + `value` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT 0, `comment` VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Mails per day setup'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_member_menu`; CREATE TABLE `{?_MYSQL_PREFIX?}_member_menu` ( - `id` BIGINT(22) NOT NULL AUTO_INCREMENT, + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `action` VARCHAR(255) NOT NULL DEFAULT '', `what` VARCHAR(255) NULL DEFAULT NULL, - `title` VARCHAR(50) NOT NULL DEFAULT '', + `title` VARCHAR(255) NOT NULL DEFAULT '', `sort` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, `visible` ENUM('Y','N') NOT NULL DEFAULT 'Y', `locked` ENUM('Y','N') NOT NULL DEFAULT 'Y', `counter` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Member menu'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_mod_reg`; CREATE TABLE `{?_MYSQL_PREFIX?}_mod_reg` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `module` VARCHAR(50) NOT NULL DEFAULT '', `locked` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `hidden` ENUM('Y','N') NOT NULL DEFAULT 'N', `admin_only` ENUM('Y','N') NOT NULL DEFAULT 'N', - `title` VARCHAR(200) NOT NULL DEFAULT '', + `title` VARCHAR(255) NOT NULL DEFAULT '', `mem_only` ENUM('Y','N') NOT NULL DEFAULT 'N', `clicks` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Module registry'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_payments`; CREATE TABLE `{?_MYSQL_PREFIX?}_payments` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, - `time` INT(7) NOT NULL DEFAULT 0, - `payment` FLOAT(5,3) UNSIGNED NOT NULL DEFAULT '0.000', + `time` INT(7) UNSIGNED NOT NULL DEFAULT 0, + `payment` FLOAT(5,3) UNSIGNED NOT NULL DEFAULT 0.000, `mail_title` VARCHAR(255) NOT NULL DEFAULT '', - `price` FLOAT(5,5) UNSIGNED NOT NULL DEFAULT '0.00000', + `price` FLOAT(5,5) UNSIGNED NOT NULL DEFAULT 0.00000, PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Payment types'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_pool`; CREATE TABLE `{?_MYSQL_PREFIX?}_pool` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sender` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, - `subject` VARCHAR(200) NOT NULL DEFAULT '', + `subject` VARCHAR(255) NOT NULL DEFAULT '', `text` LONGTEXT NOT NULL, `receivers` LONGTEXT NOT NULL, `payment_id` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, @@ -143,18 +129,18 @@ CREATE TABLE `{?_MYSQL_PREFIX?}_pool` ( INDEX (`sender`), INDEX (`payment_id`), INDEX (`cat_id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Sending pool'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_refbanner`; CREATE TABLE `{?_MYSQL_PREFIX?}_refbanner` ( - `id` BIGINT(22) NOT NULL AUTO_INCREMENT, + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `url` TEXT NOT NULL, `alternate` TEXT NOT NULL, `visible` ENUM('Y','N') NOT NULL DEFAULT 'Y', - `counter` BIGINT(22) NOT NULL DEFAULT 0, + `counter` BIGINT(20) NOT NULL DEFAULT 0, `clicks` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Referral banner'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_refdepths`; CREATE TABLE `{?_MYSQL_PREFIX?}_refdepths` ( @@ -162,25 +148,25 @@ CREATE TABLE `{?_MYSQL_PREFIX?}_refdepths` ( `level` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, `percents` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Referral levels'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_refsystem`; CREATE TABLE `{?_MYSQL_PREFIX?}_refsystem` ( - `id` BIGINT(22) NOT NULL AUTO_INCREMENT, + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `userid` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, `level` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, `counter` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, INDEX (`userid`), PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Referral system (not points)'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_task_system`; CREATE TABLE `{?_MYSQL_PREFIX?}_task_system` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, - `assigned_admin` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, - `userid` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, + `assigned_admin` BIGINT(20) UNSIGNED NULL DEFAULT NULL, + `userid` BIGINT(20) UNSIGNED NULL DEFAULT NULL, `status` ENUM('NEW','OPEN','SOLVED','CLOSED','DELETED') NOT NULL DEFAULT 'NEW', - `task_type` VARCHAR(255) NOT NULL DEFAULT 'FIRST_USER', + `task_type` VARCHAR(255) NOT NULL DEFAULT 'UNKNOWN', `subject` VARCHAR(255) NOT NULL DEFAULT '', `text` LONGTEXT NOT NULL, `task_created` VARCHAR(10) NOT NULL DEFAULT 0, @@ -188,40 +174,31 @@ CREATE TABLE `{?_MYSQL_PREFIX?}_task_system` ( INDEX (`userid`), INDEX (`assigned_admin`), PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; - -DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_extensions`; -CREATE TABLE `{?_MYSQL_PREFIX?}_extensions` ( - `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, - `ext_name` VARCHAR(255) NOT NULL DEFAULT '', - `ext_active` ENUM('Y','N') NOT NULL DEFAULT 'N', - `ext_version` VARCHAR(255) NOT NULL DEFAULT 'INVALID', - PRIMARY KEY (`id`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Task system'; DROP TABLE IF EXISTS `{?_MYSQL_PREFIX?}_jackpot`; CREATE TABLE `{?_MYSQL_PREFIX?}_jackpot` ( `ok` CHAR(2) NOT NULL DEFAULT 'ok', - `points` FLOAT(20,3) NOT NULL DEFAULT '0.000', + `points` FLOAT(20,3) NOT NULL DEFAULT 0.000, PRIMARY KEY (`ok`) -) TYPE={?_TABLE_TYPE?}; +) ENGINE = {?_TABLE_TYPE?} CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'Jackpot'; INSERT INTO `{?_MYSQL_PREFIX?}_config` (`config`) VALUES (0); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('admin','N','N','Y','','N'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('chk_login','N','Y','N','','N'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('confirm','N','Y','N','','N'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('css','N','Y','N','','N'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('frametester','N','Y','N','','N'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('index','N','N','N','','N'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('js','N','Y','N','','N'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('loader','N','Y','N','','N'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('login','N','N','N','','Y'); -INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`,`locked`,`hidden`,`admin_only`,`title`,`mem_only`) VALUES ('ref','N','Y','N','','N'); - -INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`,`percents`) VALUES (0,100); -INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`,`percents`) VALUES (1,30); -INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`,`percents`) VALUES (2,15); -INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`,`percents`) VALUES (3,10); -INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`,`percents`) VALUES (4,8); -INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`,`percents`) VALUES (5,5); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('admin','N','Y','','N'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('chk_login','N','N','','N'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('confirm','N','N','','N'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('css','N','N','','N'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('frametester','N','N','','N'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('index','N','N','','N'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('js','N','N','','N'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('loader','N','N','','N'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('login','N','N','','Y'); +INSERT INTO `{?_MYSQL_PREFIX?}_mod_reg` (`module`, `locked`, `admin_only`, `title`, `mem_only`) VALUES ('ref','N','N','','N'); + +INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`, `percents`) VALUES (0,100); +INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`, `percents`) VALUES (1,30); +INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`, `percents`) VALUES (2,15); +INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`, `percents`) VALUES (3,10); +INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`, `percents`) VALUES (4,8); +INSERT INTO `{?_MYSQL_PREFIX?}_refdepths` (`level`, `percents`) VALUES (5,5);