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