Add the GPL3 'LICENSE' file.
[mailshears.git] / lib / 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