
CREATE TABLE IF NOT EXISTS `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hierarchy_id` int(11) NOT NULL,
  `label_id` int(11) NOT NULL,
  `sf_guard_user_id` int(11) NOT NULL,
  `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `available` int(11) NOT NULL DEFAULT '1',
  `expired_at` date DEFAULT NULL,
  `filename` varchar(2048) COLLATE utf8_unicode_ci NOT NULL,
  `format` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `checksum` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_code` (`code`),
  KEY `news_FI_1` (`hierarchy_id`),
  KEY `news_FI_2` (`label_id`),
  KEY `news_FI_3` (`sf_guard_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure de la table `news_i18n`
--

CREATE TABLE IF NOT EXISTS `news_i18n` (
  `id` int(11) NOT NULL,
  `culture` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(2048) COLLATE utf8_unicode_ci DEFAULT NULL,
  `text` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`,`culture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Structure de la table `news_sf_guard_user`
--

CREATE TABLE IF NOT EXISTS `news_sf_guard_user` (
  `news_id` int(11) NOT NULL,
  `sf_guard_user_id` int(11) NOT NULL,
  `read_at` datetime DEFAULT NULL,
  PRIMARY KEY (`news_id`,`sf_guard_user_id`),
  KEY `news_sf_guard_user_FI_2` (`sf_guard_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contraintes pour les tables exportées
--

--
-- Contraintes pour la table `news`
--
ALTER TABLE `news`
  ADD CONSTRAINT `news_FK_1` FOREIGN KEY (`hierarchy_id`) REFERENCES `hierarchy` (`id`),
  ADD CONSTRAINT `news_FK_2` FOREIGN KEY (`label_id`) REFERENCES `label` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `news_FK_3` FOREIGN KEY (`sf_guard_user_id`) REFERENCES `sf_guard_user` (`id`) ON DELETE CASCADE;

--
-- Contraintes pour la table `news_i18n`
--
ALTER TABLE `news_i18n`
  ADD CONSTRAINT `news_i18n_FK_1` FOREIGN KEY (`id`) REFERENCES `news` (`id`) ON DELETE CASCADE;

--
-- Contraintes pour la table `news_sf_guard_user`
--
ALTER TABLE `news_sf_guard_user`
  ADD CONSTRAINT `news_sf_guard_user_FK_1` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `news_sf_guard_user_FK_2` FOREIGN KEY (`sf_guard_user_id`) REFERENCES `sf_guard_user` (`id`) ON DELETE CASCADE;