Просмотр архива New Top System

-- New Top System Schema
-- MySQL 5.7+ / MariaDB 10.3+
-- Charset:
utf8mb4

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone =
"+00:00";
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `config`
(
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT
NULL DEFAULT '',
  `keywords` varchar(255) NOT NULL DEFAULT '',
 
`description` varchar(255) NOT NULL DEFAULT '',
  `pf_max` int UNSIGNED NOT
NULL DEFAULT 100,
  `pf_mod` tinyint(1) NOT NULL DEFAULT 0,
  `reg`
tinyint(1) NOT NULL DEFAULT 1,
  `reg_mod` tinyint(1) NOT NULL DEFAULT 0,
 
`error` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `config`
(`id`, `title`, `keywords`, `description`, `pf_max`, `pf_mod`, `reg`,
`reg_mod`, `error`) VALUES
(1, 'Top Rating System', 'rating, top sites,
statistics', 'Modern site rating and statistics system', 100, 0, 1, 0,
1);

CREATE TABLE IF NOT EXISTS `cat` (
  `id` int UNSIGNED NOT NULL
AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `sort` int NOT NULL
DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `sort` (`sort`)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `cat`
(`id`, `name`, `sort`) VALUES
(1, 'Общение', 10),
(2,
'Бизнес', 20),
(3, 'Загрузки', 30),
(4,
'Знакомства', 40),
(5, 'Магазины и услуги',
50),
(6, 'Для взрослых', 60),
(7, 'Разработка',
70),
(8, 'Топы и каталоги', 80),
(9, 'Музыка', 90),
(10,
'Онлайн игры', 100),
(11, 'Развлечения', 110),
(12,
'Прочие', 120),
(13, 'Новые сайты', 5);

CREATE TABLE IF
NOT EXISTS `user` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `login`
varchar(32) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email`
varchar(100) NOT NULL DEFAULT '',
  `level` tinyint UNSIGNED NOT NULL
DEFAULT 1 COMMENT '1=user, 9=admin',
  `ban` tinyint(1) NOT NULL DEFAULT
0,
  `reg_time` int UNSIGNED NOT NULL DEFAULT 0,
  `last_time` int UNSIGNED
NOT NULL DEFAULT 0,
  `ip` varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY
(`id`),
  UNIQUE KEY `login` (`login`),
  KEY `level` (`level`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE
TABLE IF NOT EXISTS `platforms` (
  `id` int UNSIGNED NOT NULL
AUTO_INCREMENT,
  `user_id` int UNSIGNED NOT NULL,
  `url` varchar(64) NOT
NULL,
  `message` varchar(120) NOT NULL DEFAULT '',
  `cat_id` int UNSIGNED
NOT NULL DEFAULT 13,
  `hide` tinyint(1) NOT NULL DEFAULT 0,
  `mode`
tinyint(1) NOT NULL DEFAULT 0 COMMENT '0=active, 1=moderation',
  `ban`
tinyint(1) NOT NULL DEFAULT 0,
  `time_add` int UNSIGNED NOT NULL DEFAULT
0,
  `gold` int UNSIGNED NOT NULL DEFAULT 0 COMMENT 'unix time until
gold',
  `hs` int UNSIGNED NOT NULL DEFAULT 0 COMMENT 'unique hosts
today/period',
  `hs_today` int UNSIGNED NOT NULL DEFAULT 0,
  `hs_all` int
UNSIGNED NOT NULL DEFAULT 0,
  `ht` int UNSIGNED NOT NULL DEFAULT 0 COMMENT
'total hits',
  `ht_today` int UNSIGNED NOT NULL DEFAULT 0,
  `ht_all` int
UNSIGNED NOT NULL DEFAULT 0,
  `in_count` int UNSIGNED NOT NULL DEFAULT 0,

 `ou_count` int UNSIGNED NOT NULL DEFAULT 0,
  `mb` int UNSIGNED NOT NULL
DEFAULT 0,
  `pc` int UNSIGNED NOT NULL DEFAULT 0,
  `style_big` tinyint
UNSIGNED NOT NULL DEFAULT 1,
  `style_small` tinyint UNSIGNED NOT NULL
DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`),
  KEY
`user_id` (`user_id`),
  KEY `cat_id` (`cat_id`),
  KEY `mode_ban_hs`
(`mode`, `ban`, `hs`),
  KEY `gold` (`gold`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS
`ads` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `position`
varchar(32) NOT NULL DEFAULT 'home_top',
  `user_id` int UNSIGNED NOT NULL
DEFAULT 0,
  `status` tinyint NOT NULL DEFAULT 1 COMMENT
'0=pending,1=active,2=rejected,3=expired',
  `url` varchar(64) NOT NULL,
 
`name` varchar(64) NOT NULL,
  `text` varchar(200) NOT NULL DEFAULT '',
 
`color` varchar(32) DEFAULT NULL,
  `bold` tinyint(1) NOT NULL DEFAULT 0,
 
`italic` tinyint(1) NOT NULL DEFAULT 0,
  `days` int UNSIGNED NOT NULL
DEFAULT 0,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `time_pay` int
UNSIGNED NOT NULL DEFAULT 0,
  `time_add` int UNSIGNED NOT NULL DEFAULT 0,

 `time_end` int UNSIGNED NOT NULL DEFAULT 0,
  `admin_note` varchar(200)
NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `time_end` (`time_end`),
 
KEY `pos_status` (`position`, `status`, `time_end`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS
`ad_prices` (
  `position` varchar(32) NOT NULL,
  `title` varchar(80) NOT
NULL DEFAULT '',
  `price_day` decimal(10,2) NOT NULL DEFAULT 0.00,
 
`enabled` tinyint(1) NOT NULL DEFAULT 1,
  `sort` int NOT NULL DEFAULT 0,
 
PRIMARY KEY (`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `ad_prices` (`position`,
`title`, `price_day`, `enabled`, `sort`) VALUES
('home_top',
'Главная — верх', 50.00, 1, 10),
('home_bottom',
'Главная — низ', 30.00, 1, 20),
('cat_top', 'Категории
— верх', 25.00, 1, 30),
('stats_top', 'Статистика —
верх', 20.00, 1, 40),
('global_footer', 'Подвал на всех
страницах', 40.00, 1, 50);

-- Unique hosts log (for uniqueness
per day)
CREATE TABLE IF NOT EXISTS `hs` (
  `id` bigint UNSIGNED NOT NULL
AUTO_INCREMENT,
  `pf` int UNSIGNED NOT NULL,
  `ip` varchar(45) NOT NULL,

 `ua` varchar(255) NOT NULL DEFAULT '',
  `time` int UNSIGNED NOT NULL,
 
PRIMARY KEY (`id`),
  KEY `pf_ip_time` (`pf`, `ip`, `time`),
  KEY `time`
(`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- Total hits log
CREATE TABLE IF NOT EXISTS
`ht` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `pf` int UNSIGNED
NOT NULL,
  `ip` varchar(45) NOT NULL,
  `ua` varchar(255) NOT NULL DEFAULT
'',
  `time` int UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pf_time`
(`pf`, `time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- Online presence
CREATE TABLE IF NOT EXISTS
`on_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `pf` int
UNSIGNED NOT NULL,
  `ip` varchar(45) NOT NULL,
  `ua` varchar(255) NOT
NULL DEFAULT '',
  `time` int UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
 
UNIQUE KEY `pf_ip_ua` (`pf`, `ip`, `ua`(100)),
  KEY `pf_time` (`pf`,
`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- Inbound clicks (from top to site)
CREATE
TABLE IF NOT EXISTS `in_log` (
  `id` bigint UNSIGNED NOT NULL
AUTO_INCREMENT,
  `pf` int UNSIGNED NOT NULL,
  `ip` varchar(45) NOT NULL,

 `ua` varchar(255) NOT NULL DEFAULT '',
  `time` int UNSIGNED NOT NULL,
 
PRIMARY KEY (`id`),
  KEY `pf_ip` (`pf`, `ip`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Outbound
CREATE TABLE IF
NOT EXISTS `ou_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
 
`pf` int UNSIGNED NOT NULL,
  `ip` varchar(45) NOT NULL,
  `ua`
varchar(255) NOT NULL DEFAULT '',
  `time` int UNSIGNED NOT NULL,
  PRIMARY
KEY (`id`),
  KEY `pf_ip` (`pf`, `ip`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS
`tickets` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int
UNSIGNED NOT NULL,
  `cat_id` int UNSIGNED NOT NULL DEFAULT 0,
  `title`
varchar(120) NOT NULL,
  `text` text NOT NULL,
  `status` tinyint(1) NOT
NULL DEFAULT 0 COMMENT '0=open, 1=closed',
  `time_add` int UNSIGNED NOT
NULL DEFAULT 0,
  `time_update` int UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY
KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE
TABLE IF NOT EXISTS `ticket_comments` (
  `id` int UNSIGNED NOT NULL
AUTO_INCREMENT,
  `ticket_id` int UNSIGNED NOT NULL,
  `user_id` int
UNSIGNED NOT NULL,
  `text` text NOT NULL,
  `time_add` int UNSIGNED NOT
NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `ticket_id` (`ticket_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE
TABLE IF NOT EXISTS `news` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
 
`title` varchar(150) NOT NULL DEFAULT '',
  `text` text NOT NULL,
 
`time_add` int UNSIGNED NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL
DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `active_time` (`active`,
`time_add`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rank_snap` (
 
`pf` int UNSIGNED NOT NULL,
  `day` date NOT NULL,
  `rank` int UNSIGNED
NOT NULL DEFAULT 0,
  `hs` int UNSIGNED NOT NULL DEFAULT 0,
  `ht` int
UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`pf`, `day`),
  KEY `day_rank`
(`day`, `rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ip_ban` (
  `ip`
varchar(45) NOT NULL,
  `reason` varchar(200) NOT NULL DEFAULT '',
 
`until_time` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = permanent',
 
`time_add` int UNSIGNED NOT NULL DEFAULT 0,
  `admin_id` int UNSIGNED NOT
NULL DEFAULT 0,
  PRIMARY KEY (`ip`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS
`cheat_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `pf` int
UNSIGNED NOT NULL DEFAULT 0,
  `ip` varchar(45) NOT NULL DEFAULT '',
  `ua`
varchar(255) NOT NULL DEFAULT '',
  `kind` varchar(10) NOT NULL DEFAULT
'hit',
  `reason` varchar(32) NOT NULL DEFAULT '',
  `time` int UNSIGNED
NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `time` (`time`),
  KEY `ip`
(`ip`),
  KEY `reason` (`reason`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `points_log` (
 
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int UNSIGNED NOT
NULL,
  `delta` int NOT NULL,
  `balance` int NOT NULL DEFAULT 0,
 
`reason` varchar(64) NOT NULL DEFAULT '',
  `ref_id` int UNSIGNED NOT NULL
DEFAULT 0,
  `time` int UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),

 KEY `user_time` (`user_id`, `time`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS
`points_config` (
  `k` varchar(40) NOT NULL,
  `v` varchar(64) NOT NULL
DEFAULT '0',
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `points_config` (`k`, `v`)
VALUES
('in_reward', '1'),
('gold_day_cost',
'50'),
('ad_point_per_currency', '1'),
('enabled', '1');