export.sql

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pageId` int(10) unsigned NOT NULL COMMENT 'komentovaná stránka',
  `authorId` int(10) unsigned NOT NULL,
  `text` text NOT NULL COMMENT 'text komentáře v BBCode',
  `textHtml` text NOT NULL COMMENT 'text komentáře v HTML',
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pageId` (`pageId`),
  KEY `authorId` (`authorId`),
  CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`authorId`) REFERENCES `users` (`id`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`pageId`) REFERENCES `pages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='komentáře ke článkům';


CREATE TABLE `pages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL COMMENT 'obsah <title> / název článku',
  `slug` varchar(100) NOT NULL COMMENT 'slug / "seo url" článku',
  `publicRevId` int(10) unsigned DEFAULT NULL COMMENT 'publikovaná revize',
  `latestRevId` int(10) unsigned DEFAULT NULL COMMENT 'nejnovější revize',
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `publicRevId` (`publicRevId`),
  KEY `latestRevId` (`latestRevId`),
  CONSTRAINT `pages_ibfk_3` FOREIGN KEY (`slug`) REFERENCES `urls` (`url`),
  CONSTRAINT `pages_ibfk_4` FOREIGN KEY (`publicRevId`) REFERENCES `revisions` (`id`),
  CONSTRAINT `pages_ibfk_5` FOREIGN KEY (`latestRevId`) REFERENCES `revisions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `revisions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pageId` int(10) unsigned NOT NULL,
  `num` int(10) unsigned NOT NULL COMMENT 'číslo revize',
  `summary` varchar(250) NOT NULL COMMENT 'jednořádkové shrnutí úpravy',
  `content` text NOT NULL COMMENT 'obsah v BBCode',
  `contentHtml` text NOT NULL COMMENT 'obsah v HTML',
  `authorId` int(10) unsigned NOT NULL,
  `date` datetime NOT NULL COMMENT 'datum a čas vzniku revize',
  PRIMARY KEY (`id`),
  UNIQUE KEY `pageId_rev` (`pageId`,`num`),
  KEY `authorId` (`authorId`),
  CONSTRAINT `revisions_ibfk_4` FOREIGN KEY (`authorId`) REFERENCES `users` (`id`),
  CONSTRAINT `revisions_ibfk_3` FOREIGN KEY (`pageId`) REFERENCES `pages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `urls` (
  `url` varchar(100) NOT NULL,
  `pageId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`url`),
  KEY `pageId` (`pageId`),
  CONSTRAINT `urls_ibfk_1` FOREIGN KEY (`pageId`) REFERENCES `pages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='na jednu stránku může vést více URL';


CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'asi stejné jako na djpw',
  `nick` varchar(40) NOT NULL COMMENT 'nick z fóra',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;