Media Overhaul 917118

1. Writing a script to copy caption of photos to comment table. 2. setting category id of each image to general

3. Making a new table for videos (id, churchID, memberID, video_title, video_description, approved, video_thumb, time) //description just contains the embed tag 4. Adding a enum type 'church_video' in comment table 5. Importing videos from jouranl table in to new videos table

6. Adding a table for saving documents sub_info (id, docID, slideshareID, slideshareSrc) church_media table

//docId is the forign key from

7. Making common media_approval field for each media type

CREATE TABLE `videos` ( `id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `churchID` BIGINT( 20 ) NOT NULL , `memberID` BIGINT( 20 ) NOT NULL , `video_title` VARCHAR( 255 ) NOT NULL , `video_description` TEXT NOT NULL , `video_thumb` VARCHAR( 255 ) NOT NULL , `time` INT( 11 ) NOT NULL DEFAULT '0', `approved` TINYINT NOT NULL DEFAULT '0' ) ENGINE = INNODB; ALTER TABLE `comment` CHANGE `main` `main` ENUM( 'church_media', 'gb', 'journal', 'userpic', 'church_pic', 'church_video' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

CREATE TABLE `slideshare_documents` ( `id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`docID` BIGINT( 20 ) NOT NULL , `slideshareID` BIGINT( 20 ) NOT NULL , `slideshareSrc` TINYTEXT NOT NULL , INDEX ( `docID` ) ) ENGINE = INNODB; ALTER TABLE `slideshare_documents` ADD `user` BIGINT( 20 ) NOT NULL AFTER `slideshareSrc` ;

(

SELECT v.id as itemID, v.memberID as user, v.video_description as description, v.video_title as title, un.displayname , un.personalURL, 'video' as itemType, '' as category, '' as serverPath, '' as serverAlias, '' as extension, '' as fileName, v.time as time FROM videos v LEFT JOIN user_name un ON un.id = v.memberID WHERE v.churchID = 1 AND v.approved = 1 ) UNION ALL ( SELECT i.imageID as itemID, i.memberID as user, '' as description, '' as title, un.displayname, un.personalURL, 'photo' as ItemType, '' as category, IF(f.`alias` <=> NULL, '', f.`alias`) as serverAlias, IF(f.`filepath` <=> NULL, '', f.`filepath`) as serverPath, '' as extension, '' as fileName, i.time as time FROM image i LEFT OUTER JOIN file_repository f ON i.fileRepositoryID = f.`id` LEFT JOIN user_name un ON un.id = i.memberID LEFT JOIN image_category ic ON ic.categoryID = i.categoryID WHERE i.type = 'church' AND i.parentID = '1' AND i.approved = 1 AND i.`default` = 0 ) UNION ALL ( SELECT m.churchMediaID as itemID, m.memberID as user, m.title as title, m.description as description, un.displayname, un.personalURL, 'media' as itemType, '' as category, IF(f.`filepath` <=> NULL, '', f.`filepath`) as serverPath, IF(f.`alias` <=> NULL, '', f.`alias`) as serverAlias, m.extension as extension, m.fileName as fileName, m.time as time FROM church_media m INNER JOIN user_name un ON m.memberID = un.id LEFT OUTER JOIN file_repository f ON m.fileRepositoryID = f.`id` WHERE m.churchID = 1 AND m.category IN (1, 3) ) UNION ALL (

SELECT m.churchMediaID as itemID, m.memberID as user, m.title as title, m.description as description, un.displayname, un.personalURL, 'document' as itemType, '' as category, IF(f.`filepath` <=> NULL, '', f.`filepath`) as serverPath, IF(f.`alias` <=> NULL, '', f.`alias`) as serverAlias, m.extension as extension, m.fileName as fileName, m.time as time FROM church_media m INNER JOIN user_name un ON m.memberID = un.id LEFT OUTER JOIN file_repository f ON m.fileRepositoryID = f.`id` WHERE m.churchID = 1 AND m.category = 2 ) ORDER BY time DESC LIMIT 10