ddl.portal-message.sql Maven / Gradle / Ivy

Go to download
--
-- Copyright 2013-2018 butor.com
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

DROP TABLE IF EXISTS message;

CREATE TABLE message (
  id bigint not null comment 'message Id' auto_increment,
  relatedTo bigint comment 'Related To',
  toUserId varchar(128) not null comment 'To User Id', 
  fromUserId varchar(128) not null comment 'From User Id',
  msgType varchar(50) not null comment 'Message Type',
  subject varchar(256) not null comment 'message Subject',
  message mediumtext not null comment 'Message Body',
  status int not null default 0 comment 'Message status : 0:new, 1:read, 2:archived, 3:deleted',
  creationDate datetime not null comment 'Creation Date',
  endDate datetime comment 'end date of alert',
  readDate datetime comment 'Read Date',
  revNo integer not null comment 'Revision Number',
  stamp timestamp not null default CURRENT_TIMESTAMP comment 'Last Modification Timestamp',
  userId varchar(250) not null comment 'Last Modification User Id',

PRIMARY KEY (id),
INDEX Xto (toUserId),
INDEX Xcd (creationDate, endDate),
INDEX Xfrom (fromUserId)
)
ENGINE = InnoDB;


DROP TABLE IF EXISTS deletedMessage;

CREATE TABLE deletedMessage (
  id bigint not null comment 'message Id',
  relatedTo bigint comment 'Related To',
  toUserId varchar(128) not null comment 'To User Id', 
  fromUserId varchar(128) not null comment 'From User Id',
  msgType varchar(50) not null comment 'Message Type',
  subject varchar(256) not null comment 'message Subject',
  message mediumtext not null comment 'Message Body',
  status int not null default 0 comment 'Message status : 0:new, 1:read, 2:archived, 3:deleted, 4:sent, 5:draft',
  creationDate datetime not null comment 'Creation Date',
  endDate datetime comment 'end date of alert',
  readDate datetime comment 'Read Date',
  revNo integer not null comment 'Revision Number',
  stamp timestamp not null default CURRENT_TIMESTAMP comment 'Last Modification Timestamp',
  userId varchar(250) not null comment 'Last Modification User Id',

PRIMARY KEY (id),
INDEX Xto (toUserId),
INDEX Xcd (creationDate, endDate),
INDEX Xfrom (fromUserId)
)
ENGINE = InnoDB;