CREATE TABLE `author` (
	`id` tinyint(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`name` varchar(63) NOT NULL,
	`email` varchar(255) NOT NULL,
	`password` varchar(255) NOT NULL
) 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,
	`git_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 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`);