Remove unnecessary queries from the Roundcube plugin.
[mailshears.git] / 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