CREATE TABLE `author` (
	`id` tinyint(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`name` varchar(64) NOT NULL,
	`email` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `job` (
	`id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`package_id` smallint(5) UNSIGNED NOT NULL,
	`major` tinyint(3) UNSIGNED NOT NULL,
	`minor` tinyint(3) UNSIGNED NOT NULL,
	`revision` tinyint(3) UNSIGNED NOT NULL,
	`time_scheduled` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`time_started` datetime DEFAULT NULL,
	`time_finished` datetime DEFAULT NULL,
	`result_code` tinyint(3) UNSIGNED DEFAULT NULL,
	`log` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `package` (
	`id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`author_id` tinyint(3) UNSIGNED NOT NULL,
	`name` varchar(63) NOT NULL,
	`url` varchar(2047) NOT NULL,
	`desc` text NOT NULL,
	`time_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `version` (
	`id` mediumint(9) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`package_id` smallint(5) UNSIGNED NOT NULL,
	`major` tinyint(3) UNSIGNED NOT NULL,
	`minor` tinyint(3) UNSIGNED NOT NULL,
	`revision` tinyint(3) UNSIGNED NOT NULL,
	`time_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`depends` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `author`
	ADD PRIMARY KEY (`id`),
	ADD UNIQUE KEY `name` (`name`);

ALTER TABLE `package`
	ADD UNIQUE KEY `name` (`name`),
	ADD FULLTEXT KEY `description` (`desc`);

ALTER TABLE `version`
	ADD KEY `package` (`package_id`);