Ich habe mit dem Agenten noch ein kleines Problem festgestellt.
Er migrierte bisher die Tickets aus der DB ins FS ohne jedoch die Mtime
oder CTime entsprechend der Ticket arival Zeit zu setzen. Das kann er
nun. Im Anhang der neue Agent. Damit ist es möglich sofort nach der
Migration "find . -mtime +XXX -print0 | xargs ..." jobs aufzusetzen.
Robert
Robert Heinzmann schrieb:
Alles gut ! :)
Ich habe den Agenten gefunden (http://lists.otrs.org/pipermail/otrs-de/2006-February/005646.html) und auch auf unserem Testsystem getestet.
Er funktioniert mit aktuellen OTRS versionne leider nicht, da die Funktionen GetTicket, GetArticle in ArticleGet und TicketGet umbenannt wurden.
Anbei eine modifizierte Version, die nun unter 2.0.X funktioniert. Wiederum ohne Garantie auf Funktion!!
Nach dem Bewegen der Tickets und Umstellen des Backend auf StorageFS funktioniert alles gut. Der Vorteil der Auslagerung im Filesystem ist neben dem Geschwindigkeitsvorteil (mySQL DB is nun 1/20el der ursprünglichen Größe) auch, dass selektiv alle Attachments und Plain Mails die älter als ein gewisses Alter sind archiviert werden können und nicht jedes Mal gesichert werden müssen - ein einfaches "find ... -print0 | xargs -0 rm" tut hier Wunder.
Im OTRS äußert sich das Ganze dann so wenn die plain Mails und Attachments to einem Ticket im FS gelöscht wurden:
1) Klickt mann auf "plain" oder "klar", kommt eine Fehlermeldung
2) Attachemnts die nicht im Filesystem vorhanden sind werden im Frontend nicht mehr angezeigt (das Datei Symbol in der Meldung ist weg)
An die OTRS Entwickler: Währe es nicht gut, wenn der "plain" / "klar" Link bur angezeigt wird wenn die Datei auch im Filesystem vorhanden sind (analog den Attachments) ?
Danke noch ein Mal an Stefan Bedorf für den super hilfreichen MoveArticleParts.pm generic agent. Ist sicherlich ein Agent, der auch in die offizielle OTRS Version einfließen sollte.
Grüße,
Robert Heinzmann
# --
# Kernel/System/GenericAgent/MoveArticleParts.pm - move article_plain and article_attachment from db to fs
# Copyright (C) 2005 Stefan Bedorf
# --
# $Id: MoveArticleParts.pm,v 1.00 2005/09/05 09:34:09 sbedorf Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (GPL). If you
# did not receive this file, see http://www.gnu.org/licenses/gpl.txt.
# --
package Kernel::System::GenericAgent::MoveArticleParts;
use strict;
use File::Path;
use File::Basename;
use MIME::Words qw(:all);
# --
# to get it writable for the otrs group (just in case)
# --
umask 002;
use vars qw($VERSION);
$VERSION = '$Revision: 1.11 $';
$VERSION =~ s/^\$.*:\W(.*)\W.+?$/$1/;
# --
sub new {
my $Type = shift;
my %Param = @_;
# allocate new hash for object
my $Self = {};
bless ($Self, $Type);
# check needed objects
foreach (qw(DBObject ConfigObject LogObject TicketObject)) {
$Self->{$_} = $Param{$_} || die "Got no $_!";
}
$Self->{UserObject} = Kernel::System::User->new(%Param);
$Self->{EmailObject} = Kernel::System::Email->new(%Param);
$Self->{QueueObject} = Kernel::System::Queue->new(%Param);
return $Self;
}
# --
sub Run {
my $Self = shift;
my %Param = @_;
# get ticket data
my %Ticket = $Self->{TicketObject}->TicketGet(%Param);
my @ArticleBox = $Self->{TicketObject}->ArticleGet(TicketID => $Ticket{TicketID});
foreach my $Article (@ArticleBox) {
if ($Self->InitArticleStorage(ArticleID => $Article->{ArticleID})) {
my $Email = $Self->GetArticlePlain(ArticleID => $Article->{ArticleID});
if ($Email) {
print "Ticket: ".$Ticket{TicketID}."/ Article: ".$Article->{ArticleID}."\n";
if ($Self->WriteArticlePlain(ArticleID => $Article->{ArticleID}, Email => $Email)) {
print "article_plain of $Article->{ArticleID} written to fs\n";
# delete from db
# $Self->{DBObject}->Do(SQL => "DELETE FROM article_plain WHERE article_id = $Article->{ArticleID}");
}
else {
print "could not write article_plain from $Article->{ArticleID} to fs\n";
}
}
if (my %AttachIndex = $Self->GetArticleAtmIndex(ArticleID => $Article->{ArticleID})) {
foreach (keys %AttachIndex) {
if (my %Attachments = $Self->GetArticleAttachment(ArticleID => $Article->{ArticleID}, FileName => $AttachIndex{$_})) {
if ($Self->WriteArticlePart(Content => $Attachments{Content}, Filename => $AttachIndex{$_}, ContentType => $Attachments{ContentType}, ArticleID => $Article->{ArticleID})) {
print "article_attachment of $Article->{ArticleID} written to fs\n";
# delete from db
# $Self->{DBObject}->Do(SQL => "DELETE FROM article_attachment WHERE article_id = $Article->{ArticleID} AND filename = '$AttachIndex{$_}'");
}
else {
print "could not write article_attachment from $Article->{ArticleID} to fs\n";
}
}
}
}
}
else {
print "storage not initialized\n";
}
}
return;
}
# --
sub InitArticleStorage {
my $Self = shift;
my %Param = @_;
# check needed stuff
foreach (qw(ArticleID)) {
if (!$Param{$_}) {
$Self->{LogObject}->Log(Priority => 'error', Message => "Need $_!");
return;
}
}
# ArticleDataDir
$Self->{ArticleDataDir} = $Self->{ConfigObject}->Get('ArticleDir')
|| die "Got no ArticleDir!";
# get ArticleContentPath
$Self->{DBObject}->Prepare(SQL => "SELECT content_path,incoming_time FROM article WHERE id = $Param{ArticleID}");
while (my @Row = $Self->{DBObject}->FetchrowArray()) {
$Self->{ArticleContentPath} = $Row[0];
$Self->{Time} = $Row[1];
}
# check fs write permissions!
my $Path = "$Self->{ArticleDataDir}/$Self->{ArticleContentPath}/check_permissons.$$";
if (-d $Path) {
File::Path::rmtree([$Path]) || die "Can't remove $Path: $!\n";
}
if (mkdir("$Self->{ArticleDataDir}/check_permissons_$$", 022)) {
if (!rmdir("$Self->{ArticleDataDir}/check_permissons_$$")) {
die "Can't remove $Self->{ArticleDataDir}/check_permissons_$$: $!\n";
}
if (File::Path::mkpath([$Path], 0, 0775)) {
File::Path::rmtree([$Path]) || die "Can't remove $Path: $!\n";
}
}
else {
my $Error = $!;
$Self->{LogObject}->Log(
Priority => 'notice',
Message => "Can't create $Self->{ArticleDataDir}/check_permissons_$$: $Error, ".
"Try: \$OTRS_HOME/bin/SetPermissions.sh !",
);
die "Error: Can't create $Self->{ArticleDataDir}/check_permissons_$$: $Error \n\n ".
"Try: \$OTRS_HOME/bin/SetPermissions.sh !!!\n";
}
return 1;
}
# --
sub WriteArticlePlain {
my $Self = shift;
my %Param = @_;
# check needed stuff
foreach (qw(ArticleID Email)) {
if (!$Param{$_}) {
$Self->{LogObject}->Log(Priority => 'error', Message => "Need $_!");
return;
}
}
# prepare/filter ArticleID
$Param{ArticleID} = quotemeta($Param{ArticleID});
$Param{ArticleID} =~ s/\0//g;
# define path
my $Path = $Self->{ArticleDataDir}.'/'.$Self->{ArticleContentPath}.'/'.$Param{ArticleID};
# write article to fs 1:1
File::Path::mkpath([$Path], 0, 0775);
# write article to fs
if (open (DATA, "> $Path/plain.txt")) {
print DATA $Param{Email};
close (DATA);
# Set the mtime and ctime for the file to article.incoming_time
utime($Self->{Time}, $Self->{Time}, "$Path/plain.txt");
return 1;
}
else {
$Self->{LogObject}->Log(
Priority => 'error',
Message => "Can't write: $Path/plain.txt: $!",
);
return;
}
}
# --
sub WriteArticlePart {
my $Self = shift;
my %Param = @_;
# check needed stuff
foreach (qw(Content Filename ContentType ArticleID)) {
if (!$Param{$_}) {
$Self->{LogObject}->Log(Priority => 'error', Message => "Need $_!");
return;
}
}
# prepare/filter ArticleID
$Param{ArticleID} = quotemeta($Param{ArticleID});
$Param{ArticleID} =~ s/\0//g;
# define path
$Param{Path} = $Self->{ArticleDataDir}.'/'.$Self->{ArticleContentPath}.'/'.$Param{ArticleID};
# check used name (we want just uniq names)
my $NewFileName = decode_mimewords($Param{Filename});
my %UsedFile = ();
my @Index = $Self->GetArticleAtmIndex(
ArticleID => $Param{ArticleID},
);
$Param{Filename} = $NewFileName;
# write attachment to backend
if (! -d $Param{Path}) {
if (! File::Path::mkpath([$Param{Path}], 0, 0775)) {
$Self->{LogObject}->Log(Priority => 'error', Message => "Can't create $Param{Path}: $!");
return;
}
}
# write attachment to fs
if (open (DATA, "> $Param{Path}/$Param{Filename}")) {
print DATA "$Param{ContentType}\n";
print DATA $Param{Content};
close (DATA);
# Set the mtime and ctime for the file to article.incoming_time
utime($Self->{Time}, $Self->{Time}, "$Param{Path}/$Param{Filename}");
return 1;
}
else {
return;
}
}
# --
sub GetArticlePlain {
my $Self = shift;
my %Param = @_;
# check needed stuff
if (!$Param{ArticleID}) {
$Self->{LogObject}->Log(Priority => 'error', Message => "Need ArticleID!");
return;
}
# prepare/filter ArticleID
$Param{ArticleID} = quotemeta($Param{ArticleID});
$Param{ArticleID} =~ s/\0//g;
# open plain article
my $Data = '';
my $SQL = "SELECT body FROM article_plain ".
" WHERE ".
" article_id = ".$Self->{DBObject}->Quote($Param{ArticleID})."";
$Self->{DBObject}->Prepare(SQL => $SQL);
while (my @Row = $Self->{DBObject}->FetchrowArray()) {
$Data = $Row[0];
}
if ($Data) {
return $Data;
}
else {
return;
}
}
# --
sub GetArticleAtmIndex {
my $Self = shift;
my %Param = @_;
# check needed stuff
if (!$Param{ArticleID}) {
$Self->{LogObject}->Log(Priority => 'error', Message => "Need ArticleID!");
return;
}
my %Index = ();
my $Counter = 0;
my $SQL = "SELECT filename FROM article_attachment ".
" WHERE ".
" article_id = ".$Self->{DBObject}->Quote($Param{ArticleID})."".
" ORDER BY id";
$Self->{DBObject}->Prepare(SQL => $SQL);
while (my @Row = $Self->{DBObject}->FetchrowArray()) {
$Counter++;
$Index{$Counter} = $Row[0];
}
return %Index;
}
# --
sub GetArticleAttachment {
my $Self = shift;
my %Param = @_;
# check needed stuff
foreach (qw(ArticleID FileName)) {
if (!$Param{$_}) {
$Self->{LogObject}->Log(Priority => 'error', Message => "Need $_!");
return;
}
}
# prepare/filter ArticleID
$Param{ArticleID} = quotemeta($Param{ArticleID});
$Param{ArticleID} =~ s/\0//g;
# get attachment index
my %Index = $Self->GetArticleAtmIndex(ArticleID => $Param{ArticleID});
my %Data;
my $Counter = 0;
$Data{Filename} = $Index{$Param{FileName}};
my $SQL = "SELECT content_type, content FROM article_attachment ".
" WHERE ".
" article_id = ".$Self->{DBObject}->Quote($Param{ArticleID})."".
" AND filename = '".$Param{FileName}."'";
$Self->{DBObject}->Prepare(SQL => $SQL);
while (my @Row = $Self->{DBObject}->FetchrowArray()) {
$Data{ContentType} = $Row[0];
# decode attachemnt if it's e. g. a postgresql backend!!!
if (!$Self->{DBObject}->GetDatabaseFunction('DirectBlob')) {
$Data{Content} = decode_base64($Row[1]);
}
else {
$Data{Content} = $Row[1];
}
}
if ($Data{Content}) {
return %Data;
}
else {
return;
}
}
# --
1;
