Final Database Design for Torrent Tracker!!


The following Database is what I have decided will be my final for my first release in a week or two of the torrent tracker; the following SQL is the design of the torrent trackers database, which is:-

CREATE TABLE `agents` (
 `id` varchar(32) NOT NULL,
 `agent` tinytext NOT NULL,
 `created` int(12) NOT NULL DEFAULT '0',
 `instances` int(8) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `apis` (
 `id` varchar(32) NOT NULL,
 `api-url` varchar(200) NOT NULL,
 `polinating` enum('Yes','No') NOT NULL DEFAULT 'Yes',
 `major` tinyint(4) NOT NULL DEFAULT '1',
 `minor` tinyint(4) NOT NULL DEFAULT '0',
 `revision` tinyint(4) NOT NULL DEFAULT '1',
 `callback` varchar(200) NOT NULL,
 `secret` tinytext NOT NULL,
 `agentid` varchar(32) NOT NULL DEFAULT '',
 `calls-recieved` mediumint(24) NOT NULL DEFAULT '0',
 `calls-sent` mediumint(24) NOT NULL DEFAULT '0',
 `kbytes-recieved` mediumint(24) NOT NULL DEFAULT '0',
 `kbytes-sent` mediumint(24) NOT NULL DEFAULT '0',
 `torrents-recieved` mediumint(24) NOT NULL DEFAULT '0',
 `torrents-sent` mediumint(24) NOT NULL DEFAULT '0',
 `peers-recieved` mediumint(24) NOT NULL DEFAULT '0',
 `peers-sent` mediumint(24) NOT NULL DEFAULT '0',
 `questions` mediumint(24) NOT NULL DEFAULT '0',
 `failures` mediumint(24) NOT NULL DEFAULT '0',
 `heard` int(12) NOT NULL DEFAULT '0',
 `down` int(12) NOT NULL DEFAULT '0',
 `failed` int(12) NOT NULL DEFAULT '0',
 `created` int(12) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`,`api-url`,`polinating`,`callback`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `apis_to_pings` (
 `source-apiid` varchar(32) NOT NULL,
 `target-apiid` varchar(32) NOT NULL,
 `time-zone` varchar(100) NOT NULL,
 `recieved` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `sent` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `delay` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `average` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `test` tinyint(4) NOT NULL,
 `tests` tinyint(4) NOT NULL,
 `year` tinyint(4) NOT NULL,
 `month` tinyint(2) NOT NULL,
 `day` tinyint(2) NOT NULL,
 `hour` tinyint(2) NOT NULL,
 `minute` tinyint(2) NOT NULL,
 `second` tinyint(2) NOT NULL,
 `stamp` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `delete` int(12) NOT NULL DEFAULT '0',
 PRIMARY KEY (`source-apiid`,`target-apiid`,`test`,`tests`,`month`,`year`,`day`,`hour`,`minute`,`second`,`time-zone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `callbacks` (
 `when` int(12) NOT NULL,
 `uri` varchar(250) NOT NULL DEFAULT '',
 `timeout` int(4) NOT NULL DEFAULT '0',
 `connection` int(4) NOT NULL DEFAULT '0',
 `data` mediumtext NOT NULL,
 `queries` mediumtext NOT NULL,
 `fails` int(3) NOT NULL DEFAULT '0',
 PRIMARY KEY (`when`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `files` (
 `id` bigint(40) unsigned NOT NULL,
 `path` varchar(255) NOT NULL DEFAULT '',
 `filename` varchar(255) NOT NULL,
 `size` bigint(20) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `torrentid` (`path`),
 KEY `filename` (`filename`(100)),
 KEY `EXIST` (`path`,`filename`,`size`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `files_to_torrents` (
 `id` bigint(60) unsigned zerofill NOT NULL,
 `fileid` bigint(40) unsigned NOT NULL,
 `torrentid` bigint(30) unsigned zerofill NOT NULL,
 `seeders` int(10) unsigned NOT NULL,
 `leechers` int(10) unsigned NOT NULL,
 `completed` int(10) unsigned NOT NULL,
 `lastchecked` int(12) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `common` (`fileid`,`torrentid`,`lastchecked`),
 KEY `EXISTS` (`fileid`,`torrentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `networking` (
 `id` varchar(32) NOT NULL DEFAULT '',
 `type` enum('ipv4','ipv6') NOT NULL DEFAULT 'ipv4',
 `ipaddy` varchar(64) NOT NULL DEFAULT '',
 `netbios` varchar(198) NOT NULL DEFAULT '',
 `domain` varchar(128) NOT NULL DEFAULT '',
 `country` varchar(3) NOT NULL DEFAULT '',
 `region` varchar(128) NOT NULL DEFAULT '',
 `city` varchar(128) NOT NULL DEFAULT '',
 `postcode` varchar(15) NOT NULL DEFAULT '',
 `timezone` varchar(10) NOT NULL DEFAULT '',
 `longitude` float(12,8) NOT NULL DEFAULT '0.00000000',
 `latitude` float(12,8) NOT NULL DEFAULT '0.00000000',
 `left` bigint(20) NOT NULL DEFAULT '0',
 `downloads` bigint(20) NOT NULL DEFAULT '0',
 `uploads` bigint(20) NOT NULL DEFAULT '0',
 `owned` int(10) NOT NULL DEFAULT '0',
 `torrents` int(10) NOT NULL DEFAULT '0',
 `created` int(13) NOT NULL DEFAULT '0',
 `last` int(13) NOT NULL DEFAULT '0',
 `whoisids` tinytext,
 PRIMARY KEY (`id`,`type`,`ipaddy`(15)),
 KEY `SEARCH` (`type`,`ipaddy`(15),`netbios`(12),`domain`(12),`country`(2),`city`(12),`region`(12),`postcode`(6),`longitude`,`latitude`,`created`,`last`,`timezone`(6)),
 KEY `EXISTS` (`type`,`ipaddy`,`netbios`,`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `networking_to_agents` (
 `netagentid` varchar(32) NOT NULL,
 `ipid` varchar(32) NOT NULL DEFAULT '',
 `apiid` varchar(32) NOT NULL DEFAULT '',
 `trackerid` int(10) NOT NULL DEFAULT '0',
 `torrentid` bigint(30) unsigned zerofill NOT NULL DEFAULT '000000000000000000000000000000',
 `agentid` int(10) NOT NULL DEFAULT '0',
 `start` int(12) NOT NULL DEFAULT '0',
 `last` int(12) NOT NULL DEFAULT '0',
 `left` bigint(20) NOT NULL DEFAULT '0',
 `uploaded` bigint(20) NOT NULL DEFAULT '0',
 `downloaded` bigint(20) NOT NULL DEFAULT '0',
 `hits` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`netagentid`),
 KEY `EXISTS` (`ipid`,`apiid`,`trackerid`,`torrentid`,`agentid`) USING BTREE KEY_BLOCK_SIZE=128
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `networking_to_peers` (
 `netpeerid` varchar(32) NOT NULL,
 `ipid` varchar(32) NOT NULL DEFAULT '',
 `apiid` varchar(32) NOT NULL DEFAULT '',
 `trackerid` int(10) NOT NULL DEFAULT '0',
 `torrentid` bigint(30) unsigned zerofill NOT NULL DEFAULT '000000000000000000000000000000',
 `peerhash` varchar(44) NOT NULL DEFAULT '',
 `start` int(12) NOT NULL DEFAULT '0',
 `last` int(12) NOT NULL DEFAULT '0',
 `left` bigint(20) NOT NULL DEFAULT '0',
 `uploaded` bigint(20) NOT NULL DEFAULT '0',
 `downloaded` bigint(20) NOT NULL DEFAULT '0',
 `hits` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`netpeerid`),
 KEY `EXISTS` (`ipid`,`apiid`,`trackerid`,`torrentid`,`peerid`) USING BTREE KEY_BLOCK_SIZE=128
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `networking_to_torrents` (
 `ipid` varchar(32) NOT NULL,
 `torrentid` bigint(30) unsigned zerofill NOT NULL DEFAULT '000000000000000000000000000000',
 `started` int(13) NOT NULL DEFAULT '0',
 `last` int(13) NOT NULL DEFAULT '0',
 `finished` int(13) NOT NULL DEFAULT '0',
 `left` bigint(20) NOT NULL DEFAULT '0',
 `uploaded` bigint(20) NOT NULL DEFAULT '0',
 `downloaded` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ipid`,`torrentid`),
 KEY `EXISTS` (`torrentid`,`ipid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `networking_to_whois` (
 `netwhoisid` varchar(32) NOT NULL,
 `ipid` varchar(32) NOT NULL DEFAULT '',
 `apiid` varchar(32) NOT NULL DEFAULT '',
 `trackerid` int(10) NOT NULL DEFAULT '0',
 `torrentid` bigint(30) unsigned zerofill NOT NULL DEFAULT '000000000000000000000000000000',
 `whoisid` varchar(32) NOT NULL DEFAULT '',
 `start` int(12) NOT NULL DEFAULT '0',
 `last` int(12) NOT NULL DEFAULT '0',
 `left` bigint(20) NOT NULL DEFAULT '0',
 `uploaded` bigint(20) NOT NULL DEFAULT '0',
 `downloaded` bigint(20) NOT NULL DEFAULT '0',
 `hits` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`netwhoisid`),
 KEY `EXISTS` (`ipid`,`apiid`,`trackerid`,`torrentid`,`whoisid`) USING BTREE KEY_BLOCK_SIZE=128
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `peers` (
 `id` bigint(60) unsigned zerofill NOT NULL,
 `torrentid` bigint(30) unsigned zerofill NOT NULL DEFAULT '000000000000000000000000000000',
 `trackerid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
 `apiid` varchar(32) NOT NULL DEFAULT '',
 `ipid` varchar(32) NOT NULL DEFAULT '',
 `peerhash` varchar(44) NOT NULL DEFAULT '',
 `peerid` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `port` smallint(5) unsigned NOT NULL DEFAULT '0',
 `uploaded` bigint(20) unsigned NOT NULL DEFAULT '0',
 `downloaded` bigint(20) unsigned NOT NULL DEFAULT '0',
 `left` bigint(20) unsigned NOT NULL DEFAULT '0',
 `numwant` bigint(20) unsigned NOT NULL DEFAULT '0',
 `key` varchar(20) NOT NULL DEFAULT '',
 `compact` enum('yes','no') NOT NULL DEFAULT 'yes',
 `supportcrypto` enum('yes','no') NOT NULL DEFAULT 'yes',
 `event` varchar(30) NOT NULL DEFAULT '',
 `seeder` enum('yes','no') NOT NULL DEFAULT 'no',
 `started` int(11) NOT NULL,
 `lastaction` int(11) NOT NULL,
 `connectable` enum('yes','no') NOT NULL DEFAULT 'yes',
 `agent` varchar(60) NOT NULL,
 `finished` int(10) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `torrent_peer_id` (`torrentid`,`peerid`),
 KEY `torrent` (`torrentid`),
 KEY `torrent_seeder` (`torrentid`,`seeder`),
 KEY `last_action` (`lastaction`),
 KEY `connectable` (`connectable`),
 KEY `torrent_connect` (`torrentid`,`connectable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `peers_history` (
 `id` bigint(60) unsigned zerofill NOT NULL,
 `torrentid` bigint(30) unsigned zerofill NOT NULL DEFAULT '000000000000000000000000000000',
 `trackerid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
 `apiid` varchar(32) NOT NULL DEFAULT '',
 `ipid` varchar(32) NOT NULL DEFAULT '',
 `agentid` varchar(32) NOT NULL DEFAULT '',
 `peerhash` varchar(44) NOT NULL DEFAULT '',
 `peerid` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `started` int(11) NOT NULL,
 `lastaction` int(11) NOT NULL,
 `finished` int(10) unsigned NOT NULL DEFAULT '0',
 `deleted` int(12) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `torrent_peer_id` (`torrentid`,`peerid`),
 KEY `torrent` (`torrentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `peers_history_events` (
 `id` bigint(60) unsigned zerofill NOT NULL,
 `historyid` bigint(60) unsigned zerofill NOT NULL DEFAULT '000000000000000000000000000000',
 `event` varchar(30) NOT NULL DEFAULT '',
 `uploaded` bigint(20) unsigned NOT NULL DEFAULT '0',
 `downloaded` bigint(20) unsigned NOT NULL DEFAULT '0',
 `left` bigint(20) unsigned NOT NULL DEFAULT '0',
 `numwant` bigint(20) unsigned NOT NULL DEFAULT '0',
 `compact` enum('yes','no') NOT NULL DEFAULT 'yes',
 `supportcrypto` enum('yes','no') NOT NULL DEFAULT 'yes',
 `seeder` enum('yes','no') NOT NULL DEFAULT 'no',
 `connectable` enum('yes','no') NOT NULL DEFAULT 'yes',
 `created` int(12) unsigned NOT NULL DEFAULT '0',
 `deleted` int(12) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `torrent_peer_id` (`id`,`historyid`),
 KEY `event` (`event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `torrents` (
 `id` bigint(30) unsigned zerofill NOT NULL,
 `info_hash` varchar(40) NOT NULL,
 `info_bin` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `name` varchar(300) NOT NULL,
 `size` bigint(20) unsigned NOT NULL DEFAULT '0',
 `added` int(11) NOT NULL DEFAULT '0',
 `type` enum('single','multi') NOT NULL DEFAULT 'single',
 `numfiles` int(10) unsigned NOT NULL DEFAULT '0',
 `hits` int(10) unsigned NOT NULL DEFAULT '0',
 `times_completed` int(10) unsigned NOT NULL DEFAULT '0',
 `leechers` int(10) unsigned NOT NULL DEFAULT '0',
 `seeders` int(10) unsigned NOT NULL DEFAULT '0',
 `trackers` int(10) unsigned NOT NULL DEFAULT '0',
 `last_action` int(11) NOT NULL DEFAULT '0',
 `visible` enum('yes','no') NOT NULL DEFAULT 'yes',
 `banned` enum('yes','no') NOT NULL DEFAULT 'no',
 `owner_ipid` varchar(32) NOT NULL DEFAULT '',
 `activity` int(12) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `info_hash` (`info_hash`),
 KEY `owner` (`owner_ipid`(10)),
 KEY `visible` (`visible`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `trackers` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `added` int(11) NOT NULL DEFAULT '0',
 `tracker` varchar(500) NOT NULL DEFAULT '',
 `method` enum('curl','sockets','unknown') NOT NULL DEFAULT 'curl',
 `offline` enum('Yes','No') NOT NULL DEFAULT 'No',
 `protcol` enum('http://','https://','udp://') NOT NULL DEFAULT 'http://',
 `port` int(8) NOT NULL DEFAULT '80',
 `base` varchar(150) NOT NULL DEFAULT '',
 `domain` varchar(250) NOT NULL DEFAULT '',
 `path` varchar(150) NOT NULL DEFAULT '/announce',
 `whoisid` varchar(32) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `added` (`added`,`tracker`(25))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `trackers_to_polling` (
 `id` bigint(60) unsigned zerofill NOT NULL,
 `trackerid` int(10) unsigned NOT NULL,
 `torrentid` bigint(30) unsigned zerofill NOT NULL,
 `when` int(10) unsigned NOT NULL,
 `seeders` int(10) unsigned NOT NULL,
 `leechers` int(10) unsigned NOT NULL,
 `completed` int(10) unsigned NOT NULL,
 `lastchecked` int(12) unsigned NOT NULL,
 `activity` int(12) unsigned NOT NULL,
 `verify` int(12) unsigned NOT NULL,
 `failed` int(12) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `common` (`trackerid`,`torrentid`,`lastchecked`),
 KEY `EXISTS` (`trackerid`,`torrentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `trackers_to_torrents` (
 `id` bigint(60) unsigned zerofill NOT NULL,
 `trackerid` int(10) unsigned NOT NULL,
 `torrentid` bigint(30) unsigned zerofill NOT NULL,
 `seeders` int(10) unsigned NOT NULL,
 `leechers` int(10) unsigned NOT NULL,
 `completed` int(10) unsigned NOT NULL,
 `lastchecked` int(12) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `common` (`trackerid`,`torrentid`,`lastchecked`),
 KEY `EXISTS` (`trackerid`,`torrentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tracker_to_pings` (
 `apiid` varchar(32) NOT NULL,
 `trackerid` int(10) NOT NULL,
 `time-zone` varchar(100) NOT NULL,
 `started` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `finish` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `delay` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `average` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `test` tinyint(4) NOT NULL,
 `bytes` int(10) NOT NULL,
 `year` tinyint(4) NOT NULL,
 `month` tinyint(2) NOT NULL,
 `day` tinyint(2) NOT NULL,
 `hour` tinyint(2) NOT NULL,
 `minute` tinyint(2) NOT NULL,
 `second` tinyint(2) NOT NULL,
 `stamp` float(24,14) NOT NULL DEFAULT '0.00000000000000',
 `delete` int(12) NOT NULL DEFAULT '0',
 PRIMARY KEY (`apiid`,`trackerid`,`test`,`month`,`year`,`day`,`hour`,`minute`,`second`,`time-zone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `whois` (
 `id` varchar(32) NOT NULL,
 `whois` mediumtext NOT NULL,
 `emails` tinytext NOT NULL,
 `created` int(12) NOT NULL DEFAULT '0',
 `instances` int(8) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Advertisements