projekt kivitendo

  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' },

      my $folder_uidvalidity = $self->{imap_client}->uidvalidity($folder_string)

        or die "Could not get UIDVALIDITY for folder '$folder_string': $@\n";

          AND ej.folder_uidvalidity = ?

        $self->{hostname}, $self->{username}, $folder_string, $folder_uidvalidity);

          $email, $email_import, $new_uid, $folder_string, $folder_uidvalidity

  my ($self, $email, $email_import, $uid, $folder_string, $folder_uidvalidity) = @_;

    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,

-- @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;

-- @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;