lib/mailshears/plugins/roundcube_db.rb

   1 require 'pg'
   2
   3 class RoundcubeDb
   4
   5   include Plugin
   6
   7   def initialize()
   8     cfg = Configuration.new()
   9     @db_host = cfg.roundcube_dbhost
  10     @db_port = cfg.roundcube_dbport
  11     @db_opts = cfg.roundcube_dbopts
  12     @db_tty  = cfg.roundcube_dbtty
  13     @db_name = cfg.roundcube_dbname
  14     @db_user = cfg.roundcube_dbuser
  15     @db_pass = cfg.roundcube_dbpass
  16   end
  17
  18
  19   def describe_domain(domain)
  20     # Roundcube doesn't have a concept of domains.
  21     return 'N/A'
  22   end
  23
  24   def describe_account(account)
  25     user_id = self.get_user_id(account)
  26
  27     if user_id.nil?
  28       return 'User not found'
  29     else
  30       return "User ID: #{user_id}"
  31     end
  32   end
  33
  34   def delete_domain(domain)
  35     # Roundcube doesn't have a concept of domains.
  36   end
  37
  38   def delete_account(account)
  39     # Delete the given username and any records in other tables
  40     # belonging to it.
  41     user_id = self.get_user_id(account)
  42
  43     # The Roundcube developers were nice enough to include
  44     # DBMS-specific install and upgrade scripts, so Postgres can take
  45     # advantage of ON DELETE triggers. Here's an example:
  46     #
  47     #  ...
  48     #  user_id integer NOT NULL
  49     #    REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
  50     #
  51     # This query is of course necessary with any DBMS:
  52     sql_queries = ['DELETE FROM users WHERE user_id = $1::int;']
  53
  54     begin
  55       connection = PGconn.connect(@db_host,
  56                                   @db_port,
  57                                   @db_opts,
  58                                   @db_tty,
  59                                   @db_name,
  60                                   @db_user,
  61                                   @db_pass)
  62
  63       sql_queries.each do |sql_query|
  64         connection.query(sql_query, [user_id])
  65       end
  66
  67       connection.close()
  68
  69     rescue PGError => e
  70       # Pretend like we're database-agnostic in case we ever are.
  71       raise DatabaseError.new(e)
  72     end
  73
  74   end
  75
  76
  77   def get_leftover_domains(db_domains)
  78     # Roundcube doesn't have a concept of domains.
  79     return []
  80   end
  81
  82
  83   def get_leftover_accounts(db_accounts)
  84     # Get a list of all users who have logged in to Roundcube.
  85     rc_accounts = self.get_roundcube_usernames()
  86     return rc_accounts - db_accounts
  87   end
  88
  89
  90   protected;
  91
  92   def get_user_id(account)
  93     user_id = nil
  94
  95     begin
  96       connection = PGconn.connect(@db_host,
  97                                   @db_port,
  98                                   @db_opts,
  99                                   @db_tty,
 100                                   @db_name,
 101                                   @db_user,
 102                                   @db_pass)
 103
 104       sql_query = "SELECT user_id FROM users WHERE username = $1;"
 105
 106       connection.query(sql_query, [account]) do |result|
 107         if result.num_tuples > 0
 108           user_id = result[0]['user_id']
 109         end
 110       end
 111
 112       connection.close()
 113
 114     rescue PGError => e
 115       # Pretend like we're database-agnostic in case we ever are.
 116       raise DatabaseError.new(e)
 117     end
 118
 119     return user_id
 120   end
 121
 122
 123
 124   def get_roundcube_usernames()
 125     usernames = []
 126
 127     # Just assume PostgreSQL for now.
 128     begin
 129       connection = PGconn.connect(@db_host,
 130                                   @db_port,
 131                                   @db_opts,
 132                                   @db_tty,
 133                                   @db_name,
 134                                   @db_user,
 135                                   @db_pass)
 136
 137       sql_query = "SELECT username FROM users;"
 138       connection.query(sql_query) do |result|
 139         usernames = result.field_values('username')
 140       end
 141
 142       connection.close()
 143     rescue PGError => e
 144       # Pretend like we're database-agnostic in case we ever are.
 145       raise DatabaseError.new(e)
 146     end
 147
 148     return usernames
 149   end
 150
 151 end