Filtern von Spam E-Mails

Aufgabenstellung

Individuelle Datenbanken (Custom Databases) sind ein herausragendes Merkmal von todo4teams, um aus Skripten heraus auf eine Datenbank zuzugreifen.
Der große Vorteil hierbei ist, dass die Datenbanken problemspezifische Lösungen sehr stark vereinfachen. In unserem Beispiel wollen wir z.B. eine Datenbank anlegen, um Spam-E-Mails anhand ihres Absenders zu unterdrücken. So soll das durch eine eintreffende E-Mail erstellte Todo automatisch geschlossen werden, wenn die E-Mail von einer Adresse kommt, die als Spam-Quelle markiert ist.

Schritt 1: Definieren der Servereigenschaft

Im ersten Schritt legen wir dazu eine Servereigenschaft an, die zwei wichtige Eigenschaften der neu zu erstellenden Datenbank in ihrem Wert enthält.
Das ist zum einen der Name der Datenbank, definiert über die Variable dbname, zum anderen das Verzeichnis, in dem die Dateien der Datenbank abgelegt werden sollen. Dies wird über die Variable basedir festgelegt:

dbname=SpamlistDB
basedir=/tmp/spamlistdb

Schritt 2: Anlegen der benötigten Tabellen

Dann meldet man sich im Web-Frontend von todo4teams an und wechselt in den Reiter "Custom-DB".
Dort sollte die Datenbank SpamlistDB bereits angeboten werden.
Jetzt legt man z.B. die Tabelle spamlist an. Dazu wird, wie bei relationalen Datenbanken üblich, die SQL Syntax verwendet:

CREATE TABLE spamlist (taskid BIGINT, eventdate TIMESTAMP, email VARCHAR(256))

Mit dem Befehl SHOW TABLES kann man sich alle Tabellen der Datenbank anzeigen lassen.

Schritt 3: Zugriff auf die Datenbank aus einem Skript heraus

Mit Hilfe der Funktion isSpam wird im Eingangs-Skript der zu filternden Mailbox gesucht, ob die eintreffende E-Mail in der Tabelle spamlist zu finden ist. Ist dies der Fall, so wird das bereits entstandene Todo automatisch mit dem Endkommentar "Email ist blacklisted." geschlossen.

function isSpam()
{
   var conny  = helper.getCustomDB("SpamlistDB");
   var selectStmt = conny.createStatement();
   var numberOfHits = selectStmt.executeQuery("SELECT COUNT(*) FROM spamlist WHERE '"+fromEmail+"' LIKE '%' || email ||'%'");
  if (numberOfHits.next())
  {
        var hits=numberOfHits.getInt(1);
        selectStmt.close();
       return hits>0;
  }

  return false;
}

println("Checking for spam in Custom DB:"+fromEmail);

if (isSpam())
{
    println("Closing task with id "+task.id+" for sender "+fromEmail);
    task.startWorking();
    task.setDoneComment("Email is blacklisted.");
    task.finishWorking(true);

}
else
{
       println("No Spam detected for task with id "+task.id+" for sender "+fromEmail);
}

Schritt 4: End-Aktion der Gruppe zur Aufnahme neuer E-Mail-Adressen

Zusätzlich zu den oben beschriebenen Anforderungen soll es für bestimmte Gruppen möglich sein, ein Todo welches aus einer E-Mail generiert wurde, als Spam zu markieren. Dazu wird in der End-Aktion der Gruppe geprüft, ob das Wort Spam vorkommt. Wenn ja, so wird die E-Mail-Adresse, falls noch nicht vorhanden, in die spamlist-Tabelle aufgenommen.

function emailExists()
{
     var conny  = helper.getCustomDB("SpamlistDB");
     var selectStmt = conny.createStatement();
     var numberOfHits = selectStmt.executeQuery("SELECT COUNT(*) FROM spamlist WHERE email='"+fromEmail+"'");

    if (numberOfHits.next())
    {
         var hits=numberOfHits.getInt(1);
         selectStmt.close();
        return hits>0;
    }
    return false;
}

function insertEmail()
{
    var conny  = helper.getCustomDB("SpamlistDB");
    var insertStmt = conny.createStatement();
    var query = "INSERT INTO spamlist (taskid,creationdate,email) VALUES("+task.id+",CURRENT_TIMESTAMP,'"+fromEmail+"')";
    println("Insert email with query:"+query);
    insertStmt.execute(query);
    insertStmt.close();
}

if (task.getEmail() != null && task.getEmail().trim().length()>0)
{
    var fromEmail=task.getEmail().toLowerCase();

   if (task.getDoneComment().toLowerCase().contains("spam"))
   {
       if (emailExists())
       {
            print("Ignoring existing spam email from "+fromEmail+" for task id "+task.id)
       }
       else
       {
            insertEmail();
       }
   }
}