CREATE TABLE `#__openaire` (`key` VARCHAR(32) NOT NULL PRIMARY KEY, `value` VARCHAR(256));


CREATE TABLE `#__openaire_alert_modes` (`id` VARCHAR (16) NOT NULL PRIMARY KEY, `name` VARCHAR (32) NOT NULL, `description` TEXT NOT NULL, `batch` BOOLEAN NOT NULL, `adminOnly` BOOLEAN NOT NULL);
INSERT INTO `#__openaire_alert_modes` (`id`, `name`, `description`, `batch`, `adminOnly`) VALUES
	('Atom', 'Atom Feed (RSS)', 'Post alert in OpenAIRE Atom feed.', FALSE, TRUE),
	('JDBC', 'OpenAIRE Portal - My Alerts', 'Post alert in My Alerts.', FALSE, FALSE),
	('Joomla!', 'OpenAIRE News', 'Post alert in OpenAIRE News.', FALSE, TRUE),
	('SMTP', 'Email', 'Send alert by email.', TRUE, FALSE),
	('twitter', 'twitter', 'Post alert to OpenAIRE twitter account.', FALSE, TRUE);

CREATE TABLE `#__openaire_alerts` (`username` VARCHAR (150) NOT NULL, `time` TIMESTAMP NOT NULL, `title` VARCHAR (255) NOT NULL, `message` TEXT NOT NULL, `link` TEXT, PRIMARY KEY (`username`, `time`, `title`));

CREATE TABLE `#__openaire_topic_hierarchy` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `parent` INT, `name` TEXT NOT NULL, `description` TEXT NOT NULL, `templateId` VARCHAR (36), `notificationService` VARCHAR (255), `queryId` VARCHAR (36), `resultId` VARCHAR (36), FOREIGN KEY (`parent`) REFERENCES `#__openaire_topic_hierarchy` (`id`) ON UPDATE CASCADE ON DELETE CASCADE);

