SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; CREATE SCHEMA IF NOT EXISTS `2dadsmsgdev` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; USE `2dadsmsgdev`; -- ----------------------------------------------------- -- Table `2dadsmsgdev`.`AddressBook` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`AddressBook` ( `OwnerContactID` BIGINT(20) NOT NULL AUTO_INCREMENT , `ActiveStatus` TINYINT(4) NOT NULL , `ContactName` VARCHAR(64) NOT NULL , `ContactContactID` BIGINT(20) NULL DEFAULT NULL , `ContactType` ENUM('Guardian','Ward','OtherAdult','Virtual') NOT NULL , `ContactSetupState` ENUM('Default','Updated') NOT NULL , `ContactCreateTimeStamp` DATETIME NULL DEFAULT NULL , `MsgInScreenType` ENUM('None','Monitor','Screen','Custom') NOT NULL , `MsgInCap` INT(11) NOT NULL , `MsgOutScreenType` ENUM('None','Monitor','Screen','Custom') NOT NULL , `MsgOutCap` INT(11) NOT NULL , `ChangedTimeStamp` DATETIME NULL DEFAULT NULL , `LastChangedFields` VARCHAR(48) NULL DEFAULT NULL , `SuspensionReason` TEXT NULL DEFAULT NULL , PRIMARY KEY (`OwnerContactID`) , INDEX `ContactName` (`ContactName` ASC) , INDEX `ContactContactID` (`ContactContactID` ASC) , CONSTRAINT `addressbook_ibfk_4` FOREIGN KEY (`ContactContactID` ) REFERENCES `2dadsmsgdev`.`AddressBook` (`OwnerContactID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB AUTO_INCREMENT = 60 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ----------------------------------------------------- -- Table `2dadsmsgdev`.`AddressBookCustomScreenDuties` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`AddressBookCustomScreenDuties` ( `ContactID` BIGINT(20) NOT NULL , `MsgInScreenType` ENUM('None','Monitor','Screen') NOT NULL , `MsgOutScreenType` ENUM('None','Monitor','Screen') NOT NULL , `ChangedTimeStamp` DATETIME NULL DEFAULT NULL , `LastChangedFields` VARCHAR(128) NULL DEFAULT NULL , `StartScreenTimeStamp` DATETIME NULL DEFAULT NULL , `StopScreenTimeStamp` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`ContactID`) , INDEX `ContactID` (`ContactID` ASC) , CONSTRAINT `addressbookcustomscreenduties_ibfk_1` FOREIGN KEY (`ContactID` ) REFERENCES `2dadsmsgdev`.`AddressBook` (`OwnerContactID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 ROW_FORMAT = DYNAMIC; -- ----------------------------------------------------- -- Table `2dadsmsgdev`.`Messages` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`Messages` ( `MessageID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `InFolder` ENUM('inbox','drafts','sent','trash','void') NOT NULL DEFAULT 'inbox' , `Type` ENUM('toscreen','toedit','monitored','normal') NOT NULL DEFAULT 'normal' COMMENT 'toscreen: sent to screen.\ntoedit: sent back to edit.\nmonitored: a monoitor copy.\nnormal: a normal message.' , `ReadStatus` ENUM('read','unread') NOT NULL DEFAULT 'unread' , `FromContactID` BIGINT(20) NULL , `InReplyToMessageID` BIGINT UNSIGNED NULL , `Subject` TEXT NOT NULL , `Content` TEXT NULL , `Comment` TEXT NULL , `NewComment` TEXT NULL , `WrittenAt` DATETIME NULL DEFAULT '0000-00-00 00:00:00' , `DeliveredAt` DATETIME NULL DEFAULT '0000-00-00 00:00:00' , `ReadAt` DATETIME NULL DEFAULT '0000-00-00 00:00:00' , PRIMARY KEY (`MessageID`) , INDEX `index3` (`InFolder` ASC) , INDEX `index4` (`Type` ASC) , INDEX `index5` (`ReadStatus` ASC) , INDEX `index6` (`WrittenAt` ASC) , INDEX `index8` (`DeliveredAt` ASC) , INDEX `index9` (`ReadAt` ASC) , INDEX `Messages_fk6` (`FromContactID` ASC) , INDEX `Messages_fk7` (`InReplyToMessageID` ASC) , CONSTRAINT `Messages_fk6` FOREIGN KEY (`FromContactID` ) REFERENCES `2dadsmsgdev`.`AddressBook` (`OwnerContactID` ) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `Messages_fk7` FOREIGN KEY (`InReplyToMessageID` ) REFERENCES `2dadsmsgdev`.`Messages` (`MessageID` ) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci; -- ----------------------------------------------------- -- Table `2dadsmsgdev`.`MessageNotifications` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`MessageNotifications` ( `NotificationID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `MessageID` BIGINT UNSIGNED NOT NULL , `AddedByWhomUID` VARCHAR(255) NULL , `NotifyWhomUID` VARCHAR(255) NULL , `Status` ENUM('unread','read') NULL DEFAULT 'unread' , `NotificationType` ENUM( 'authrequest','newmail') NOT NULL , `AddedAt` DATETIME NOT NULL DEFAULT '00000-00-00 00:00:00' , `AddedFrom` VARCHAR(255) NULL , PRIMARY KEY (`NotificationID`) , INDEX `Status` (`Status` ASC) , INDEX `NotificationType` (`NotificationType` ASC) , INDEX `MessageNofifcations_fk1` (`MessageID` ASC) , CONSTRAINT `MessageNofifcations_fk1` FOREIGN KEY (`MessageID` ) REFERENCES `2dadsmsgdev`.`Messages` (`MessageID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci; -- ----------------------------------------------------- -- Table `2dadsmsgdev`.`AddressBookNotifications` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`AddressBookNotifications` ( `NotificationID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `ContactID` BIGINT(20) NOT NULL , `AddedByWhomUID` VARCHAR(255) NULL , `NotifyWhomUID` VARCHAR(255) NULL , `Status` ENUM('unread','read') NULL DEFAULT 'unread' , `NotificationType` ENUM( 'request', 'notification1', 'notification2', 'notification3', 'notification4' ,'notification5') NOT NULL , `AddedAt` DATETIME NOT NULL DEFAULT '00000-00-00 00:00:00' , `AddedFrom` VARCHAR(255) NULL , PRIMARY KEY (`NotificationID`) , INDEX `FK1` (`ContactID` ASC) , INDEX `Status` (`Status` ASC) , INDEX `NotificationType` (`NotificationType` ASC) , CONSTRAINT `FK1` FOREIGN KEY (`ContactID` ) REFERENCES `2dadsmsgdev`.`AddressBook` (`OwnerContactID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci; -- ----------------------------------------------------- -- Placeholder table for view `2dadsmsgdev`.`AddressBookNotifcationsView` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`AddressBookNotifcationsView` (`id` INT); -- ----------------------------------------------------- -- Placeholder table for view `2dadsmsgdev`.`AddressBookRequestsView` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`AddressBookRequestsView` (`id` INT); -- ----------------------------------------------------- -- Placeholder table for view `2dadsmsgdev`.`MessagesView` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`MessagesView` (`id` INT); -- ----------------------------------------------------- -- Placeholder table for view `2dadsmsgdev`.`MessageNotifcationsView` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`MessageNotifcationsView` (`id` INT); -- ----------------------------------------------------- -- View `2dadsmsgdev`.`AddressBookNotifcationsView` -- ----------------------------------------------------- DROP TABLE IF EXISTS `2dadsmsgdev`.`AddressBookNotifcationsView`; CREATE OR REPLACE VIEW `MessageNotifcationsView` AS SELECT t1.NotificationID, t1.AddedByWhomUID, t1.NotifyWhomUID, t1.NotificationType, t1.Status, t2.*, t3.NickName AS SenderNickName, t3.FirstName AS SenderFirstName, t3.LastName AS SenderLastName, t3.Portrait AS SenderPortrait, t3.HashDirectory AS SenderHashDirectory, t4.NickName AS RecipientNickName, t4.FirstName AS RecipientFirstName, t4.LastName AS RecipientLastName, t1.AddedAt FROM MessageNotifications AS t1, Messages AS t2, UserProf AS t3, UserProf AS t4 WHERE t1.MessageID=t2.MessageID AND t3.UID=t2.SenderUID AND t4.UID=t2.RecipientUID AND t5.OwnerContactID=t1.FromContactID AND t6.OwnerContactID=t5.ContactContactID ORDER BY t1.AddedAt DESC; -- ----------------------------------------------------- -- View `2dadsmsgdev`.`AddressBookRequestsView` -- ----------------------------------------------------- DROP TABLE IF EXISTS `2dadsmsgdev`.`AddressBookRequestsView`; CREATE OR REPLACE VIEW `AddressBookRequestsView` AS SELECT t1.NotificationID, LOWER(t2.MsgInScreenType) AS MsgInScreenType, LOWER(t2.MsgOutScreenType) AS MsgOutScreenType, t2.OwnerContactID AS ContactID, t2.ContactContactID AS ContactID1, t2.OwnerUID, t2.ContactName, t3.NickName AS OwnerNickName, t3.FirstName AS OwnerFirstName, t3.LastName AS OwnerLastName, t2.ContactUID, t4.NickName AS ContactNickName, t4.FirstName AS ContactFirstName, t4.LastName AS ContactLastName, t4.Portrait AS ContactPortrait, t4.HashDirectory AS ContactHashDirectory, t1.AddedByWhomUID, t5.NickName AS AddedByNickName, t5.FirstName AS AddedByFirstName, t5.LastName AS AddedByLastName, t2.ActiveStatus, t1.AddedAt, t1.NotifyWhomUID,t2.OwnerHouseholdGID,t1.Status FROM AddressBookNotifications AS t1, AddressBook AS t2, UserProf AS t3, UserProf AS t4, UserProf AS t5 WHERE t1.NotificationType='request' AND t1.ContactID=t2.ContactContactID AND t3.UID=t2.OwnerUID AND t4.UID=t2.ContactUID AND t5.UID=t1.AddedByWhomUID ORDER BY t1.AddedAt DESC; -- ----------------------------------------------------- -- View `2dadsmsgdev`.`MessagesView` -- ----------------------------------------------------- DROP TABLE IF EXISTS `2dadsmsgdev`.`MessagesView`; CREATE OR REPLACE VIEW `MessagesView` AS SELECT t1.MessageID, t1.SenderUID,t1.RecipientUID, t1.EnvelopeSenderUID,t1.EnvelopeRecipientUID, t1.ScreenerUID, t1.Subject,t1.Content,t1.Comment, t1.InFolder, t1.Type, t1.ReadStatus, t1.FromContactID,t1.InReplyToMessageID, DATE_FORMAT(t1.DeliveredAt,'%M %e,%Y %h:%i:%s %p') as DeliveredAt, DATE_FORMAT(t1.WrittenAt,'%M %e,%Y %h:%i:%s %p') as WrittenAt, DATE_FORMAT(t1.ReadAt,'%M %e,%Y %h:%i:%s %p') as ReadAt, t2.NickName AS SenderNickName, t2.FirstName AS SenderFirstName, t2.LastName AS SenderLastName, t2.Portrait AS SenderPortrait, t2.HashDirectory AS SenderHashDirectory, t6.ContactName AS SenderContactName, t3.NickName AS RecipientNickName, t3.FirstName AS RecipientFirstName, t3.LastName AS RecipientLastName, t5.ContactName AS RecipientContactName, t4.NickName AS ScreenerNickName, t4.FirstName AS ScreenerFirstName, t4.LastName AS ScreenerLastName, t5.MsgInScreenType AS SenderMsgInScreenType, t5.MsgOutScreenType AS SenderMsgOutScreenType, t6.MsgInScreenType AS RecipientMsgInScreenType, t6.MsgOutScreenType AS RecipientMsgOutScreenType, t6.OwnerContactID AS RecipientContactID, t6.OwnerHouseholdGID AS RecipientGID FROM Messages AS t1 INNER JOIN UserProf AS t2 ON (t1.SenderUID=t2.UID) INNER JOIN UserProf AS t3 ON (t1.RecipientUID=t3.UID) LEFT JOIN UserProf AS t4 ON (t1.ScreenerUID=t4.UID) INNER JOIN AddressBook AS t5 ON (t1.FromContactID=t5.OwnerContactID) INNER JOIN AddressBook AS t6 ON (t5.ContactContactID=t6.OwnerContactID); -- ----------------------------------------------------- -- View `2dadsmsgdev`.`MessageNotifcationsView` -- ----------------------------------------------------- DROP TABLE IF EXISTS `2dadsmsgdev`.`MessageNotifcationsView`; CREATE OR REPLACE VIEW `MessageNotifcationsView` AS SELECT t1.NotificationID, t1.AddedByWhomUID, t1.NotifyWhomUID, t1.NotificationType, t1.Status, t2.*, t3.NickName AS SenderNickName, t3.FirstName AS SenderFirstName, t3.LastName AS SenderLastName, t3.Portrait AS SenderPortrait, t3.HashDirectory AS SenderHashDirectory, t4.NickName AS RecipientNickName, t4.FirstName AS RecipientFirstName, t4.LastName AS RecipientLastName, t1.AddedAt FROM MessageNotifications AS t1, Messages AS t2, UserProf AS t3, UserProf AS t4 WHERE t1.MessageID=t2.MessageID AND t3.UID=t2.SenderUID AND t4.UID=t2.RecipientUID AND t5.OwnerContactID=t1.FromContactID AND t6.OwnerContactID=t5.ContactContactID ORDER BY t1.AddedAt DESC; USE `2dadsmsgdev`; DELIMITER // CREATE TRIGGER AddressBookNotificationsTrigger AFTER UPDATE ON AddressBook FOR EACH ROW BEGIN IF New.ActiveStatus=-1 THEN UPDATE AddressBookNotifications SET Status='read' WHERE ContactID=NEW.OwnerContactID; END IF; END // DELIMITER ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;