Website/database_structure.sql

--
-- Table structure for table `articles`
--

CREATE TABLE `articles` (
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `page` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ord` int(11) NOT NULL DEFAULT 0,
  `src` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `facebook_links`
--

CREATE TABLE `facebook_links` (
  `postID` int(10) UNSIGNED NOT NULL,
  `unshimmed_url` varchar(250) NOT NULL,
  `caption` varchar(250) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `facebook_media`
--

CREATE TABLE `facebook_media` (
  `fbid` bigint(20) UNSIGNED NOT NULL,
  `postID` int(10) UNSIGNED DEFAULT NULL,
  `mimetype` varchar(50) DEFAULT NULL,
  `ext` varchar(250) DEFAULT NULL,
  `width` smallint(5) UNSIGNED DEFAULT NULL,
  `height` smallint(5) UNSIGNED DEFAULT NULL,
  `thumbext` varchar(10) DEFAULT NULL,
  `twidth` smallint(5) UNSIGNED DEFAULT NULL,
  `theight` smallint(5) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `facebook_posts`
--

CREATE TABLE `facebook_posts` (
  `ID` int(10) UNSIGNED NOT NULL,
  `fbid` bigint(20) UNSIGNED NOT NULL,
  `catid` int(11) NOT NULL,
  `created_time` datetime NOT NULL,
  `permalink_url` varchar(250) NOT NULL,
  `title` varchar(250) NOT NULL,
  `message` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='id,created_time,type,status_type,permalink_url,message,link,name,picture,full_picture,object_id,parent_id,source,attachments{url,type,subattachments{type,media}}';

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

--
-- Table structure for table `galleries`
--

CREATE TABLE `galleries` (
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `article` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ord` int(11) NOT NULL,
  `path` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `thumbheight` smallint(5) UNSIGNED NOT NULL DEFAULT 100,
  `previewheight` smallint(5) UNSIGNED NOT NULL DEFAULT 100,
  `previewpic` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `htmlcache` text COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `pages`
--

CREATE TABLE `pages` (
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ord` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `sections`
--

CREATE TABLE `sections` (
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `article` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ord` int(11) NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `articles`
--
ALTER TABLE `articles`
  ADD PRIMARY KEY (`name`),
  ADD KEY `src` (`src`),
  ADD KEY `fk_sections_page` (`page`);

--
-- Indexes for table `facebook_links`
--
ALTER TABLE `facebook_links`
  ADD PRIMARY KEY (`postID`,`unshimmed_url`);

--
-- Indexes for table `facebook_media`
--
ALTER TABLE `facebook_media`
  ADD PRIMARY KEY (`fbid`),
  ADD KEY `fk_fbmedia_post` (`postID`);

--
-- Indexes for table `facebook_posts`
--
ALTER TABLE `facebook_posts`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `galleries`
--
ALTER TABLE `galleries`
  ADD PRIMARY KEY (`name`) USING BTREE,
  ADD KEY `fk_galleries_article` (`article`) USING BTREE;

--
-- Indexes for table `pages`
--
ALTER TABLE `pages`
  ADD PRIMARY KEY (`name`);

--
-- Indexes for table `sections`
--
ALTER TABLE `sections`
  ADD PRIMARY KEY (`name`,`article`) USING BTREE,
  ADD KEY `fk_articles_section` (`article`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `facebook_posts`
--
ALTER TABLE `facebook_posts`
  MODIFY `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `articles`
--
ALTER TABLE `articles`
  ADD CONSTRAINT `fk_sections_page` FOREIGN KEY (`page`) REFERENCES `pages` (`name`) ON UPDATE CASCADE;

--
-- Constraints for table `facebook_links`
--
ALTER TABLE `facebook_links`
  ADD CONSTRAINT `fk_fblinks_post` FOREIGN KEY (`postID`) REFERENCES `facebook_posts` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `facebook_media`
--
ALTER TABLE `facebook_media`
  ADD CONSTRAINT `fk_fbmedia_post` FOREIGN KEY (`postID`) REFERENCES `facebook_posts` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `galleries`
--
ALTER TABLE `galleries`
  ADD CONSTRAINT `fk_galleries_article` FOREIGN KEY (`article`) REFERENCES `articles` (`name`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `sections`
--
ALTER TABLE `sections`
  ADD CONSTRAINT `fk_articles_section` FOREIGN KEY (`article`) REFERENCES `articles` (`name`) ON UPDATE CASCADE;
COMMIT;