Import mails on gmail into PostgreSQL server

I have received many emails from systems. Some are urgent alerts and some are just for my information. I wonder how does the trend of these alerts go so far. It is difficult to analyze the trend just on my mailbox so I  thought that it would be useful to import those mails into a local PostgreSQL server.

First, I enabled POP3 service on my gmail account, then I wrote some ruby code.

With some gems and ruby, I could do these task in handy.

-------------------------------------------------

require 'mail' #gem mail

require 'pg'   #gem pg

Mail.defaults do

  retriever_method :pop3, { 

    :address => "pop.gmail.com",

    :port => 995,

    :user_name => 'username',

    :password => 'password',

    :enable_ssl => true

  }

end

# connect PostgreSQL server

db = PG.connect( :dbname => 'db_name', :user => 'db_user')

Mail.all.each do |email|

  $stderr.puts email.date.to_s # Output date of email

  begin

    # Japanese character sets are often undetected.

    # So I use Content-Type of email.

    if email.content_type =~ /ISO-2022-JP/i then

      body_str = email.body.to_s.encode("UTF-8", "ISO-2022-JP")

    else

      body_str = email.body.encoded

    end

    # insert into PostgreSQL server

    db.exec "INSERT INTO mailbox ( dt, subject, body ) VALUES ($1,$2,$3);", [email.date.to_s, email.subject, body_str]

  rescue => ignore

    $stderr.puts "[error]:" + ignore.to_s

  end

end

-------------------------------------------------

And this code needs two gems, pg and mail.

Those are installed in following commands.

-------------------------------------------------

(For Windows)

gem install --no-ri --no-rdoc pg -- --with-pg-config="c:\Program Files\PostgreSQL\9.2\bin\pg_config.exe"

gem install --no-ri --no-rdoc mail

-------------------------------------------------

(For Linux)

sudo gem install pg -- --with-pg-config=/usr/pgsql-9.2/bin/pg_config

sudo gem install mail

-------------------------------------------------

Now I have been importing mails. When it is finished, it is time to analyze. That is another story.