sub initialise_url_shortener_cache()

in lib/Mail/SpamAssassin/Plugin/DecodeShortURLs.pm [505:656]

• 131 lines of code
• 16 McCabe index (conditional complexity)

sub initialise_url_shortener_cache {
  my ($self, $conf) = @_;

  return if $self->{dbh};
  return if !$conf->{url_shortener_cache_type};

  if (!$conf->{url_shortener_cache_dsn}) {
    warn "DecodeShortURLs: invalid cache configuration\n";
    return;
  }

  ##
  ## SQLite
  ## 
  if ($conf->{url_shortener_cache_type} =~ /^(?:dbi|sqlite)$/i
      && $conf->{url_shortener_cache_dsn} =~ /^dbi:SQLite/)
  {
    eval {
      local $SIG{'__DIE__'};
      require DBI;
      require DBD::SQLite;
      DBD::SQLite->VERSION(1.59_01); # Required for ON CONFLICT
      $self->{dbh} = DBI->connect_cached(
        $conf->{url_shortener_cache_dsn}, '', '',
        {RaiseError => 1, PrintError => 0, InactiveDestroy => 1, AutoCommit => 1}
      );
      $self->{dbh}->do("
        CREATE TABLE IF NOT EXISTS short_url_cache (
          short_url   TEXT PRIMARY KEY NOT NULL,
          decoded_url TEXT NOT NULL,
          hits        INTEGER NOT NULL DEFAULT 1,
          created     INTEGER NOT NULL,
          modified    INTEGER NOT NULL
        )
      ");
      # Maintaining index for cleaning is likely more expensive than occasional full table scan
      #$self->{dbh}->do("
      #  CREATE INDEX IF NOT EXISTS short_url_modified
      #    ON short_url_cache(created)
      #");
      $self->{sth_insert} = $self->{dbh}->prepare("
        INSERT INTO short_url_cache (short_url, decoded_url, created, modified)
        VALUES (?,?,strftime('%s','now'),strftime('%s','now'))
        ON CONFLICT(short_url) DO UPDATE
          SET decoded_url = excluded.decoded_url,
              modified = excluded.modified,
              hits = hits + 1
      ");
      $self->{sth_select} = $self->{dbh}->prepare("
        SELECT decoded_url FROM short_url_cache
        WHERE short_url = ?
      ");
      $self->{sth_delete} = $self->{dbh}->prepare("
        DELETE FROM short_url_cache
        WHERE short_url = ? AND created < strftime('%s','now') - $conf->{url_shortener_cache_ttl}
      ");
      $self->{sth_clean} = $self->{dbh}->prepare("
        DELETE FROM short_url_cache
        WHERE created < strftime('%s','now') - $conf->{url_shortener_cache_ttl}
      ");
    };
  }
  ##
  ## MySQL/MariaDB
  ## 
  elsif (lc $conf->{url_shortener_cache_type} eq 'dbi'
      && $conf->{url_shortener_cache_dsn} =~ /^dbi:(?:mysql|MariaDB)/i)
  {
    eval {
      local $SIG{'__DIE__'};
      require DBI;
      $self->{dbh} = DBI->connect_cached(
        $conf->{url_shortener_cache_dsn},
        $conf->{url_shortener_cache_username},
        $conf->{url_shortener_cache_password},
        {RaiseError => 1, PrintError => 0, InactiveDestroy => 1, AutoCommit => 1}
      );
      $self->{sth_insert} = $self->{dbh}->prepare("
        INSERT INTO short_url_cache (short_url, decoded_url, created, modified)
        VALUES (?,?,UNIX_TIMESTAMP(),UNIX_TIMESTAMP())
        ON DUPLICATE KEY UPDATE
          decoded_url = VALUES(decoded_url),
          modified = VALUES(modified),
          hits = hits + 1
      ");
      $self->{sth_select} = $self->{dbh}->prepare("
        SELECT decoded_url FROM short_url_cache
        WHERE short_url = ?
      ");
      $self->{sth_delete} = $self->{dbh}->prepare("
        DELETE FROM short_url_cache
        WHERE short_url = ? AND created < UNIX_TIMESTAMP() - $conf->{url_shortener_cache_ttl}
      ");
      $self->{sth_clean} = $self->{dbh}->prepare("
        DELETE FROM short_url_cache
        WHERE created < UNIX_TIMESTAMP() - $conf->{url_shortener_cache_ttl}
      ");
    };
  }
  ##
  ## PostgreSQL
  ## 
  elsif (lc $conf->{url_shortener_cache_type} eq 'dbi'
      && $conf->{url_shortener_cache_dsn} =~ /^dbi:Pg/i)
  {
    eval {
      local $SIG{'__DIE__'};
      require DBI;
      $self->{dbh} = DBI->connect_cached(
        $conf->{url_shortener_cache_dsn},
        $conf->{url_shortener_cache_username},
        $conf->{url_shortener_cache_password},
        {RaiseError => 1, PrintError => 0, InactiveDestroy => 1, AutoCommit => 1}
      );
      $self->{sth_insert} = $self->{dbh}->prepare("
        INSERT INTO short_url_cache (short_url, decoded_url, created, modified)
        VALUES (?,?,CAST(EXTRACT(epoch FROM NOW()) AS INT),CAST(EXTRACT(epoch FROM NOW()) AS INT))
        ON CONFLICT (short_url) DO UPDATE SET
          decoded_url = EXCLUDED.decoded_url,
          modified = EXCLUDED.modified,
          hits = short_url_cache.hits + 1
      ");
      $self->{sth_select} = $self->{dbh}->prepare("
        SELECT decoded_url FROM short_url_cache
        WHERE short_url = ?
      ");
      $self->{sth_delete} = $self->{dbh}->prepare("
        DELETE FROM short_url_cache
        WHERE short_url = ? AND created < CAST(EXTRACT(epoch FROM NOW()) AS INT) - $conf->{url_shortener_cache_ttl}
      ");
      $self->{sth_clean} = $self->{dbh}->prepare("
        DELETE FROM short_url_cache
        WHERE created < CAST(EXTRACT(epoch FROM NOW()) AS INT) - $conf->{url_shortener_cache_ttl}
      ");
    };
  ##
  ## ...
  ##
  } else {
    warn "DecodeShortURLs: invalid cache configuration\n";
    return;
  }

  if ($@ || !$self->{sth_clean}) {
    warn "DecodeShortURLs: cache connect failed: $@\n";
    undef $self->{dbh};
    undef $self->{sth_insert};
    undef $self->{sth_select};
    undef $self->{sth_delete};
    undef $self->{sth_clean};
  }
}