SQL schema/structure

[dud3]

Description

Create a database called e_fwd.

Schema

Really simple schema(at least for now).
Basically Every email in email_address_list contains keyword(s), if the keywords match from the incoming email(such as from the gmail account), then store into the mails table, and from there to the email_address that they belong to.

Tables

CREATE TABLE IF NOT EXISTS `email_address_list`;
CREATE TABLE IF NOT EXISTS `mails`;
CREATE TABLE IF NOT EXISTS `groups`;
CREATE TABLE IF NOT EXISTS `keywords_list;`
CREATE TABLE IF NOT EXISTS `migrations`;
CREATE TABLE IF NOT EXISTS `throttle`;
CREATE TABLE IF NOT EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users_groups`; 

References

63217ed

[dud3]

Add messages.

[dud3]

New Table introduced:
Keeps all the emails that come from the mail server.

CREATE TABLE IF NOT EXISTS `mails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_address_id` int(11) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `body` varchar(255) DEFAULT NULL,
  `sender_email` varchar(255) DEFAULT NULL,
  `reciver_email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMEN

[dud3]

Table mails

Add fwd_accept column.

ALTER TABLE `mails` ADD `fwd_accept` TINYINT( 0 ) NOT NULL AFTER `reciver_email` ;

[dud3]

Table email_address_list

Drop fwd_accept.

ALTER TABLE `email_address_list` DROP `fwd_accept` ;

[dud3]

Table mails

ALTER TABLE `mails` ADD `optional_text` VARCHAR( 255 ) NULL AFTER `body` ;

[dud3]

Table mails

ALTER TABLE `mails` ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `fwd_accept` ;

[dud3]

Table mails

ALTER TABLE  `mails` CHANGE  `subject`  `subject` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
CHANGE  `body`  `body` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `optional_text`  `optional_text` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ;

[dud3]

Table mails

ALTER TABLE  `mails` ADD  `html` TINYINT( 1 ) NOT NULL DEFAULT  '0' AFTER  `sent` ;

[dud3]

Table mails

ALTER TABLE  `mails` ADD  `body_html` VARCHAR( 255 ) NULL AFTER  `body` ;

[dud3]

Table mails

ALTER TABLE  `mails` ADD  `__message_id` VARCHAR( 255 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `html` ,
ADD  `__date` VARCHAR( 32 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__message_id` ,
ADD  `__size` INT( 255 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__date` ,
ADD  `__uid` INT( 32 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__size` ,
ADD  `__msgno` INT( 32 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__uid` ;

[dud3]

Table mails

ALTER TABLE  `mails` ADD  `__recent` TINYINT( 1 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__msgno` ,
ADD  `__flagged` TINYINT( 1 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__recent` ,
ADD  `__answered` TINYINT( 1 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__flagged` ,
ADD  `__deleted` TINYINT( 1 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__answered` ,
ADD  `__seen` TINYINT( 1 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__deleted` ,
ADD  `__draft` TINYINT( 1 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__seen` ,
ADD  `__udate` INT( 255 ) NULL COMMENT  'This Column belongs to actual mail information.' AFTER  `__draft` ;

[dud3]

Table mails

ALTER TABLE  `mails` ADD  `deleted` TINYINT( 1 ) NOT NULL DEFAULT  '0' AFTER  `html` ;

[dud3]

Table mails

It seems that SQL or maybe Eloquent of Laravel doesn't like prefix of _(underline) on the table rows.
This way it should store the data into the DB.

ALTER TABLE `mails` 

CHANGE `x_message_id` `x_message_id` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_date` `x_date` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL 
DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_size` `x_size` INT(255) NULL DEFAULT NULL COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_uid` `x_uid` INT(32) NULL DEFAULT NULL COMMENT 'This Column belongs to actual mail information.',

CHANGE `x_msgno` `x_msgno` INT(32) NULL DEFAULT NULL
COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_recent` `x_recent` TINYINT(1) NULL DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_flagged` `x_flagged` TINYINT(1) NULL DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_answered` `x_answered` TINYINT(1) NULL DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_deleted` `x_deleted` TINYINT(1) NULL DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_seen` `x_seen` TINYINT(1) NULL DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.', 

CHANGE `x_draft` `x_draft` TINYINT(1) NULL DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.',

CHANGE `x_udate` `x_udate` INT(255) NULL DEFAULT NULL 
COMMENT 'This Column belongs to actual mail information.';

[dud3]

Table keywords_list

ALTER TABLE  `email_address_list` ADD  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER  `keyword_id` ,
ADD  `updated_at` TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' AFTER  `created_at` ;

[dud3]

Table keywords_list

ALTER TABLE  `keywords_list` ADD  `original_content` TINYINT( 1 ) NOT NULL DEFAULT  '0' AFTER  `keywords` ;

ALTER TABLE  `keywords_list` CHANGE  `original_content`  `original_content` TINYINT( 1 ) NOT NULL DEFAULT  '0' COMMENT 'This column tell if the emails associated with this keyword(s) should or shouldn''t include HTML.';

[dud3]

Table keywords_list

ALTER TABLE `keywords_list` ADD `send_automatically` TINYINT( 1 ) NOT NULL AFTER `keywords` ;

ALTER TABLE `keywords_list` CHANGE `send_automatically` `send_automatically` TINYINT( 1 ) NOT NULL DEFAULT '0';

ALTER TABLE `keywords_list` CHANGE `send_automatically` `send_automatically` TINYINT( 1 ) NOT NULL DEFAULT '0' COMMENT 'Send the emails that belong to this keywords.';

[dud3]

Table keywords_list

ALTER TABLE `keywords_list` ADD `user_id` INT( 11 ) UNSIGNED NOT NULL COMMENT 'Lists for the current logged in users.' AFTER `id` ;

[dud3]

Table keywords_list

ALTER TABLE  `keywords_list` ADD  `multiple` INT( 11 ) NULL COMMENT  'If the copy of the same keyword exits multiple times.' AFTER  `original_content` ;

[dud32]

ALTER TABLE  `email_address_list` ADD  `include_receivers` TINYINT( 1 ) NOT NULL DEFAULT  '0' AFTER  `keyword_id` ;

[dud32]

ALTER TABLE  `keywords_list_links` ADD  `position` VARCHAR( 8 ) NOT NULL AFTER  `text_align` ;

[dud3]

CREATE TABLE IF NOT EXISTS `keywords_list_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keywords_list_id` int(11) NOT NULL,
  `link` text NOT NULL,
  `position` varchar(255) NOT NULL DEFAULT 'top' COMMENT 'top, bottom',
  `text_align` varchar(25) NOT NULL DEFAULT 'left' COMMENT 'left, right',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;