Revision 814ba821
Von Tamino Steinert vor mehr als 1 Jahr hinzugefügt
- ID 814ba821969b16ab2268759fe436967dbbe5a91e
- Vorgänger d6db8528
| SL/DB/MetaSetup/EmailJournal.pm | ||
|---|---|---|
|
__PACKAGE__->meta->table('email_journal');
|
||
|
|
||
|
__PACKAGE__->meta->columns(
|
||
|
body => { type => 'text', not_null => 1 },
|
||
|
email_import_id => { type => 'integer' },
|
||
|
extended_status => { type => 'text', not_null => 1 },
|
||
|
folder => { type => 'text' },
|
||
|
from => { type => 'text', not_null => 1 },
|
||
|
headers => { type => 'text', not_null => 1 },
|
||
|
id => { type => 'serial', not_null => 1 },
|
||
|
itime => { type => 'timestamp', default => 'now()', not_null => 1 },
|
||
|
mtime => { type => 'timestamp', default => 'now()', not_null => 1 },
|
||
|
recipients => { type => 'text', not_null => 1 },
|
||
|
sender_id => { type => 'integer' },
|
||
|
sent_on => { type => 'timestamp', default => 'now()', not_null => 1 },
|
||
|
status => { type => 'enum', check_in => [ 'sent', 'send_failed', 'imported' ], db_type => 'email_journal_status', not_null => 1 },
|
||
|
subject => { type => 'text', not_null => 1 },
|
||
|
uid => { type => 'integer' },
|
||
|
body => { type => 'text', not_null => 1 },
|
||
|
email_import_id => { type => 'integer' },
|
||
|
extended_status => { type => 'text', not_null => 1 },
|
||
|
folder => { type => 'text' },
|
||
|
folder_uidvalidity => { type => 'text' },
|
||
|
from => { type => 'text', not_null => 1 },
|
||
|
headers => { type => 'text', not_null => 1 },
|
||
|
id => { type => 'serial', not_null => 1 },
|
||
|
itime => { type => 'timestamp', default => 'now()', not_null => 1 },
|
||
|
mtime => { type => 'timestamp', default => 'now()', not_null => 1 },
|
||
|
recipients => { type => 'text', not_null => 1 },
|
||
|
sender_id => { type => 'integer' },
|
||
|
sent_on => { type => 'timestamp', default => 'now()', not_null => 1 },
|
||
|
status => { type => 'enum', check_in => [ 'sent', 'send_failed', 'imported' ], db_type => 'email_journal_status', not_null => 1 },
|
||
|
subject => { type => 'text', not_null => 1 },
|
||
|
uid => { type => 'integer' },
|
||
|
);
|
||
|
|
||
|
__PACKAGE__->meta->primary_key_columns([ 'id' ]);
|
||
| SL/IMAPClient.pm | ||
|---|---|---|
|
$self->{imap_client}->select($folder_string)
|
||
|
or die "Could not select IMAP folder '$folder_string': $@\n";
|
||
|
|
||
|
my $folder_uidvalidity = $self->{imap_client}->uidvalidity($folder_string)
|
||
|
or die "Could not get UIDVALIDITY for folder '$folder_string': $@\n";
|
||
|
|
||
|
my $msg_uids = $self->{imap_client}->messages
|
||
|
or die "Could not get messages via IMAP: $@\n";
|
||
|
|
||
| ... | ... | |
|
WHERE ei.host_name = ?
|
||
|
AND ei.user_name = ?
|
||
|
AND ej.folder = ?
|
||
|
AND ej.folder_uidvalidity = ?
|
||
|
SQL
|
||
|
|
||
|
my $existing_uids = $dbh->selectall_hashref($query, 'uid', undef,
|
||
|
$self->{hostname}, $self->{username}, $folder_string);
|
||
|
$self->{hostname}, $self->{username}, $folder_string, $folder_uidvalidity);
|
||
|
|
||
|
my @new_msg_uids = grep { !$existing_uids->{$_} } @$msg_uids;
|
||
|
|
||
| ... | ... | |
|
my $new_email_string = $self->{imap_client}->message_string($new_uid);
|
||
|
my $email = Email::MIME->new($new_email_string);
|
||
|
my $email_journal = $self->_create_email_journal(
|
||
|
$email, $email_import, $new_uid, $folder_string
|
||
|
$email, $email_import, $new_uid, $folder_string, $folder_uidvalidity
|
||
|
);
|
||
|
$email_journal->save();
|
||
|
}
|
||
| ... | ... | |
|
}
|
||
|
|
||
|
sub _create_email_journal {
|
||
|
my ($self, $email, $email_import, $uid, $folder_path) = @_;
|
||
|
my ($self, $email, $email_import, $uid, $folder_string, $folder_uidvalidity) = @_;
|
||
|
|
||
|
my @email_parts = $email->parts; # get parts or self
|
||
|
my $text_part = $email_parts[0];
|
||
| ... | ... | |
|
});
|
||
|
|
||
|
my $email_journal = SL::DB::EmailJournal->new(
|
||
|
email_import_id => $email_import->id,
|
||
|
folder => $folder_path,
|
||
|
uid => $uid,
|
||
|
status => 'imported',
|
||
|
extended_status => '',
|
||
|
from => $email->header('From') || '',
|
||
|
recipients => $recipients,
|
||
|
sent_on => $date,
|
||
|
subject => $email->header('Subject') || '',
|
||
|
body => $body,
|
||
|
headers => $header_string,
|
||
|
attachments => \@attachments,
|
||
|
email_import_id => $email_import->id,
|
||
|
folder => $folder_string,
|
||
|
folder_uidvalidity => $folder_uidvalidity,
|
||
|
uid => $uid,
|
||
|
status => 'imported',
|
||
|
extended_status => '',
|
||
|
from => $email->header('From') || '',
|
||
|
recipients => $recipients,
|
||
|
sent_on => $date,
|
||
|
subject => $email->header('Subject') || '',
|
||
|
body => $body,
|
||
|
headers => $header_string,
|
||
|
attachments => \@attachments,
|
||
|
);
|
||
|
|
||
|
return $email_journal;
|
||
| sql/Pg-upgrade2/email_import.sql | ||
|---|---|---|
|
-- @tag: email_import
|
||
|
-- @description: Email Journal für importierte E-Mails erweitern
|
||
|
-- @depends: release_3_8_0
|
||
|
|
||
|
CREATE TABLE email_imports (
|
||
|
id SERIAL NOT NULL PRIMARY KEY,
|
||
|
host_name TEXT NOT NULL,
|
||
|
user_name TEXT NOT NULL,
|
||
|
folder TEXT NOT NULL,
|
||
|
itime TIMESTAMP NOT NULL DEFAULT now()
|
||
|
);
|
||
|
|
||
|
ALTER TABLE email_journal ADD COLUMN email_import_id INTEGER REFERENCES email_imports(id);
|
||
|
ALTER TABLE email_journal ADD COLUMN folder TEXT;
|
||
|
ALTER TABLE email_journal ADD COLUMN uid INTEGER;
|
||
|
CREATE INDEX email_journal_folder_uid_idx ON email_journal (folder, uid);
|
||
|
-- NOTE: change status from text to enum and add 'imported'
|
||
|
CREATE TYPE email_journal_status AS ENUM ('sent', 'send_failed', 'imported');
|
||
|
ALTER TABLE email_journal DROP CONSTRAINT valid_status;
|
||
|
ALTER TABLE email_journal RENAME COLUMN status TO old_status;
|
||
|
ALTER TABLE email_journal ADD COLUMN status email_journal_status;
|
||
|
UPDATE email_journal SET status = 'sent' WHERE old_status = 'ok';
|
||
|
UPDATE email_journal SET status = 'send_failed' WHERE old_status = 'failed';
|
||
|
ALTER TABLE email_journal ALTER COLUMN status SET NOT NULL;
|
||
|
ALTER TABLE email_journal DROP COLUMN old_status;
|
||
|
|
||
|
|
||
| sql/Pg-upgrade2/email_journal_add_uidvalidity.sql | ||
|---|---|---|
|
-- @tag: email_journal_add_uidvalidity
|
||
|
-- @description: Ordner uidvalidity für importierte E-Mails
|
||
|
-- @depends: release_3_8_0
|
||
|
|
||
|
ALTER TABLE email_journal ADD COLUMN folder_uidvalidity TEXT;
|
||
| sql/Pg-upgrade2/email_journal_update.sql | ||
|---|---|---|
|
-- @tag: email_import
|
||
|
-- @description: Email Journal für importierte E-Mails erweitern
|
||
|
-- @depends: release_3_8_0
|
||
|
|
||
|
CREATE TABLE email_imports (
|
||
|
id SERIAL NOT NULL PRIMARY KEY,
|
||
|
host_name TEXT NOT NULL,
|
||
|
user_name TEXT NOT NULL,
|
||
|
folder TEXT NOT NULL,
|
||
|
itime TIMESTAMP NOT NULL DEFAULT now()
|
||
|
);
|
||
|
|
||
|
ALTER TABLE email_journal ADD COLUMN email_import_id INTEGER REFERENCES email_imports(id);
|
||
|
ALTER TABLE email_journal ADD COLUMN folder TEXT;
|
||
|
ALTER TABLE email_journal ADD COLUMN uid INTEGER;
|
||
|
CREATE INDEX email_journal_folder_uid_idx ON email_journal (folder, uid);
|
||
|
-- NOTE: change status from text to enum and add 'imported'
|
||
|
CREATE TYPE email_journal_status AS ENUM ('sent', 'send_failed', 'imported');
|
||
|
ALTER TABLE email_journal DROP CONSTRAINT valid_status;
|
||
|
ALTER TABLE email_journal RENAME COLUMN status TO old_status;
|
||
|
ALTER TABLE email_journal ADD COLUMN status email_journal_status;
|
||
|
UPDATE email_journal SET status = 'sent' WHERE old_status = 'ok';
|
||
|
UPDATE email_journal SET status = 'send_failed' WHERE old_status = 'failed';
|
||
|
ALTER TABLE email_journal ALTER COLUMN status SET NOT NULL;
|
||
|
ALTER TABLE email_journal DROP COLUMN old_status;
|
||
|
|
||
|
|
||
Auch abrufbar als: Unified diff
FIX: uidvalidity für Emailordner mit speichern