lib/rm/plugins/roundcube_db.rb

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