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;
You must be logged in to post a comment.