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     # This is mostly unnecessary when using Postgres. The Roundcube
  44     # developers were nice enough to include DBMS-specific install and
  45     # upgrade scripts, so Postgres can take advantage of ON DELETE
  46     # triggers. Here's an example:
  47     #
  48     #  ...
  49     #  user_id integer NOT NULL
  50     #    REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
  51     #
  52     sql_queries = ['DELETE FROM cache WHERE user_id = $1::int;']
  53     sql_queries << 'DELETE FROM cache_index WHERE user_id = $1::int;'
  54     sql_queries << 'DELETE FROM cache_messages WHERE user_id = $1::int;'
  55     sql_queries << 'DELETE FROM cache_thread WHERE user_id = $1::int;'
  56     sql_queries << 'DELETE FROM contactgroupmembers WHERE contactgroup_id IN (SELECT contactgroup_id FROM contactgroups WHERE user_id = $1::int);'
  57     sql_queries << 'DELETE FROM contactgroups WHERE user_id = $1::int;'
  58     sql_queries << 'DELETE FROM contacts WHERE user_id = $1::int;'
  59     sql_queries << 'DELETE FROM identities WHERE user_id = $1::int;'
  60     sql_queries << 'DELETE FROM dictionary WHERE user_id = $1::int;'
  61     sql_queries << 'DELETE FROM searches WHERE user_id = $1::int;'
  62
  63     # This one is of course necessary with any DBMS.
  64     sql_queries << 'DELETE FROM users WHERE user_id = $1::int;'
  65
  66     begin
  67       connection = PGconn.connect(@db_host,
  68                                   @db_port,
  69                                   @db_opts,
  70                                   @db_tty,
  71                                   @db_name,
  72                                   @db_user,
  73                                   @db_pass)
  74
  75       sql_queries.each do |sql_query|
  76         connection.query(sql_query, [user_id])
  77       end
  78
  79       connection.close()
  80
  81     rescue PGError => e
  82       # Pretend like we're database-agnostic in case we ever are.
  83       raise DatabaseError.new(e)
  84     end
  85
  86   end
  87
  88
  89   def get_leftover_domains(db_domains)
  90     # Roundcube doesn't have a concept of domains.
  91     return []
  92   end
  93
  94
  95   def get_leftover_accounts(db_accounts)
  96     # Get a list of all users who have logged in to Roundcube.
  97     rc_accounts = self.get_roundcube_usernames()
  98     return rc_accounts - db_accounts
  99   end
 100
 101
 102   protected;
 103
 104   def get_user_id(account)
 105     user_id = nil
 106
 107     begin
 108       connection = PGconn.connect(@db_host,
 109                                   @db_port,
 110                                   @db_opts,
 111                                   @db_tty,
 112                                   @db_name,
 113                                   @db_user,
 114                                   @db_pass)
 115
 116       sql_query = "SELECT user_id FROM users WHERE username = $1;"
 117
 118       connection.query(sql_query, [account]) do |result|
 119         if result.num_tuples > 0
 120           user_id = result[0]['user_id']
 121         end
 122       end
 123
 124       connection.close()
 125
 126     rescue PGError => e
 127       # Pretend like we're database-agnostic in case we ever are.
 128       raise DatabaseError.new(e)
 129     end
 130
 131     return user_id
 132   end
 133
 134
 135
 136   def get_roundcube_usernames()
 137     usernames = []
 138
 139     # Just assume PostgreSQL for now.
 140     begin
 141       connection = PGconn.connect(@db_host,
 142                                   @db_port,
 143                                   @db_opts,
 144                                   @db_tty,
 145                                   @db_name,
 146                                   @db_user,
 147                                   @db_pass)
 148
 149       sql_query = "SELECT username FROM users;"
 150       connection.query(sql_query) do |result|
 151         usernames = result.field_values('username')
 152       end
 153
 154       connection.close()
 155     rescue PGError => e
 156       # Pretend like we're database-agnostic in case we ever are.
 157       raise DatabaseError.new(e)
 158     end
 159
 160     return usernames
 161   end
 162
 163 end