src/plugins/roundcube_db.rb

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