Форум пользователей MySQL

CREATE TABLE IF NOT EXISTS `peers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `torrent` int(10) unsigned NOT NULL DEFAULT '0',
  `peer_id` varchar(20) NOT NULL DEFAULT '',
  `ip` varchar(64) NOT NULL DEFAULT '',
  `port` smallint(5) unsigned NOT NULL DEFAULT '0',
  `uploaded` bigint(20) unsigned NOT NULL DEFAULT '0',
  `downloaded` bigint(20) unsigned NOT NULL DEFAULT '0',
  `uploadoffset` bigint(20) unsigned NOT NULL DEFAULT '0',
  `downloadoffset` bigint(20) unsigned NOT NULL DEFAULT '0',
  `to_go` bigint(20) unsigned NOT NULL DEFAULT '0',
  `seeder` enum('yes','no') NOT NULL DEFAULT 'no',
  `started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_action` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `prev_action` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `connectable` enum('yes','no') NOT NULL DEFAULT 'yes',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `agent` varchar(60) NOT NULL DEFAULT '',
  `finishedat` int(10) unsigned NOT NULL DEFAULT '0',
  `passkey` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `torrent_peer_id` (`torrent`,`peer_id`),
  KEY `torrent` (`torrent`),
  KEY `torrent_seeder` (`torrent`,`seeder`),
  KEY `last_action` (`last_action`),
  KEY `connectable` (`connectable`),
  KEY `userid` (`userid`),
) ENGINE=MEMORY  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=26541 ;

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `torrents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `leechers` int(10) unsigned NOT NULL DEFAULT '0',
  `seeders` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251;

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `uploaded` bigint(20) unsigned NOT NULL DEFAULT '0',
  `downloaded` bigint(20) unsigned NOT NULL DEFAULT '0',
  `bonus` decimal(10,2) NOT NULL DEFAULT '0.00',
  `bonus2` decimal(10,2) NOT NULL DEFAULT '0.00',
  `alfa_upl` decimal(10,2) NOT NULL DEFAULT '0.00',
  `alfa_size` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
   FULLTEXT KEY `endis_reason` (`dis_reason`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=26296 ;

UPDATE `users` AS u SET u.`alfa_size` = u.`alfa_size` + (
SELECT SUM( (SELECT t2.`size`/1048576/32/24/4 FROM `torrents` t2 WHERE p.`torrent` = t2.`id`) )
    FROM `peers` p WHERE
        (SELECT t.`id` FROM `torrents` t WHERE p.`torrent` = t.id AND t.`seeders` < 5 AND t.`size` > 104857600) > 0
        AND p.`to_go` = 0
        AND p.`userid`= u.`id`
    GROUP BY p.`userid`)
    WHERE (SELECT count(p2.`id`) FROM `peers` p2 WHERE u.`id` = p2.`userid` AND p2.`seeder` = 'yes')

UPDATE `users` AS u SET u.`alfa_upl` = u.`alfa_upl` + (
    SELECT SUM(p.uploadoffset /1048576/30)
        FROM `peers` p WHERE
            (SELECT t.id FROM `torrents` t WHERE p.`torrent` = t.id AND t.size > 200*1048576) > 0
            AND p.`uploadoffset` > 3*1048576
            AND p.`userid`= u.`id`
        GROUP BY p.`userid`)
WHERE (
    SELECT count(p2.`id`) FROM `peers` p2 WHERE
        u.`id` = p2.`userid`
        AND p2.`uploadoffset` > 3*1048576) > 0