mysql schema for limetracker 1.0

MYSQL-lib.model.schema.sql

# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- client
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `client`;


CREATE TABLE `client`
(
	`torrent_id` INTEGER,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	`port` INTEGER,
	`bytes_uploaded` INTEGER,
	`bytes_downloaded` INTEGER,
	`bytes_left` INTEGER,
	`ip` VARCHAR(40),
	`client_key` VARCHAR(60),
	`tracker_id` VARCHAR(60),
	`peer_id` VARCHAR(60),
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	INDEX `client_FI_1` (`torrent_id`),
	CONSTRAINT `client_FK_1`
		FOREIGN KEY (`torrent_id`)
		REFERENCES `torrent` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- torrent
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `torrent`;


CREATE TABLE `torrent`
(
	`episode_id` INTEGER default null,
	`feed_id` INTEGER default null,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	`file` VARCHAR(255),
	`size` INTEGER,
	`mime_type` VARCHAR(40),
	`web_url` TEXT,
	`file_sha1` VARCHAR(40),
	`guid_hash` VARCHAR(40),
	`info_hash` VARCHAR(40),
	`downloads` INTEGER,
	`seeders` INTEGER,
	`peers` INTEGER,
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	UNIQUE KEY `torrent_U_1` (`guid_hash`),
	UNIQUE KEY `unique_file_name` (`file`, `episode_id`),
	UNIQUE KEY `unique_episode_feed` (`episode_id`, `feed_id`),
	CONSTRAINT `torrent_FK_1`
		FOREIGN KEY (`episode_id`)
		REFERENCES `episode` (`id`)
		ON DELETE CASCADE,
	INDEX `torrent_FI_2` (`feed_id`),
	CONSTRAINT `torrent_FK_2`
		FOREIGN KEY (`feed_id`)
		REFERENCES `feed` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- episode
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `episode`;


CREATE TABLE `episode`
(
	`podcast_id` INTEGER default null,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	`title` TEXT,
	`slug` VARCHAR(255),
	`description` TEXT,
	`length` INTEGER,
	`downloads` INTEGER,
	`guid_hash` VARCHAR(40),
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	UNIQUE KEY `episode_U_1` (`guid_hash`),
	UNIQUE KEY `unique_slug` (`slug`, `podcast_id`),
	INDEX `episode_FI_1` (`podcast_id`),
	CONSTRAINT `episode_FK_1`
		FOREIGN KEY (`podcast_id`)
		REFERENCES `podcast` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- feed
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `feed`;


CREATE TABLE `feed`
(
	`podcast_id` INTEGER default null,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	`last_fetched` DATETIME,
	`title` TEXT,
	`slug` VARCHAR(255),
	`rss_url` TEXT,
	`rss_url_hash` VARCHAR(40),
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	UNIQUE KEY `feed_U_1` (`rss_url_hash`),
	INDEX `feed_FI_1` (`podcast_id`),
	CONSTRAINT `feed_FK_1`
		FOREIGN KEY (`podcast_id`)
		REFERENCES `podcast` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- podcast
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `podcast`;


CREATE TABLE `podcast`
(
	`default_feed_id` INTEGER default null,
	`slug` VARCHAR(255),
	`created_at` DATETIME,
	`updated_at` DATETIME,
	`title` TEXT,
	`description` TEXT,
	`author` TEXT,
	`email` TEXT,
	`link` TEXT,
	`itunes_id` TEXT,
	`image_url` TEXT,
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	UNIQUE KEY `podcast_U_1` (`slug`),
	INDEX `podcast_FI_1` (`default_feed_id`),
	CONSTRAINT `podcast_FK_1`
		FOREIGN KEY (`default_feed_id`)
		REFERENCES `feed` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- setting
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `setting`;


CREATE TABLE `setting`
(
	`key` VARCHAR(255),
	`value` VARCHAR(255),
	`vendor` VARCHAR(255),
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	UNIQUE KEY `setting_U_1` (`key`)
)Type=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;