d563a244fcaa3fd3c25beb45f093bc7d2486352d
[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 # 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