1 | # -*- coding: utf-8 -*- |
---|
2 | # |
---|
3 | # Copyright (c) 2010, Robert Corsaro |
---|
4 | # Copyright (c) 2012, Steffen Hoffmann |
---|
5 | # |
---|
6 | # This software is licensed as described in the file COPYING, which |
---|
7 | # you should have received as part of this distribution. |
---|
8 | # |
---|
9 | |
---|
10 | # NOTE: users are uniquely identified by (sid, authenticated). An anonymous |
---|
11 | # user is allowed to use an sid that they desire, even one that is already |
---|
12 | # used by an authenticated user. When a user enters an sid into a field, like |
---|
13 | # ticket owner, they are refering to an authenticated user. All permission |
---|
14 | # checking for unauthenticated users should be done against the 'anonymous' |
---|
15 | # user. |
---|
16 | |
---|
17 | from trac.util.datefmt import datetime_now, to_utimestamp, utc |
---|
18 | |
---|
19 | __all__ = ['Subscription', 'SubscriptionAttribute'] |
---|
20 | |
---|
21 | |
---|
22 | class Subscription(object): |
---|
23 | |
---|
24 | fields = ('id', 'sid', 'authenticated', 'distributor', 'format', |
---|
25 | 'priority', 'adverb', 'class') |
---|
26 | |
---|
27 | def __init__(self, env): |
---|
28 | self.env = env |
---|
29 | self.values = {} |
---|
30 | |
---|
31 | def __getitem__(self, name): |
---|
32 | if name not in self.fields: |
---|
33 | raise KeyError(name) |
---|
34 | return self.values.get(name) |
---|
35 | |
---|
36 | def __setitem__(self, name, value): |
---|
37 | if name not in self.fields: |
---|
38 | raise KeyError(name) |
---|
39 | self.values[name] = value |
---|
40 | |
---|
41 | @classmethod |
---|
42 | def add(cls, env, subscription, db=None): |
---|
43 | """ID and priority get overwritten.""" |
---|
44 | |
---|
45 | with env.db_transaction as db: |
---|
46 | cursor = db.cursor() |
---|
47 | priority = len(cls.find_by_sid_and_distributor( |
---|
48 | env, subscription['sid'], subscription['authenticated'], |
---|
49 | subscription['distributor'], db)) + 1 |
---|
50 | now = to_utimestamp(datetime_now(utc)) |
---|
51 | cursor.execute(""" |
---|
52 | INSERT INTO subscription |
---|
53 | (time,changetime,sid,authenticated, |
---|
54 | distributor,format,priority,adverb,class) |
---|
55 | VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s) |
---|
56 | """, (now, now, subscription['sid'], |
---|
57 | subscription['authenticated'], subscription['distributor'], |
---|
58 | subscription['format'], int(priority), |
---|
59 | subscription['adverb'], subscription['class'])) |
---|
60 | |
---|
61 | @classmethod |
---|
62 | def delete(cls, env, rule_id, db=None): |
---|
63 | |
---|
64 | with env.db_transaction as db: |
---|
65 | cursor = db.cursor() |
---|
66 | cursor.execute(""" |
---|
67 | SELECT sid,authenticated,distributor |
---|
68 | FROM subscription |
---|
69 | WHERE id=%s |
---|
70 | """, (rule_id,)) |
---|
71 | sid, authenticated, distributor = cursor.fetchone() |
---|
72 | cursor.execute(""" |
---|
73 | DELETE FROM subscription |
---|
74 | WHERE id=%s |
---|
75 | """, (rule_id,)) |
---|
76 | i = 1 |
---|
77 | for s in cls.find_by_sid_and_distributor(env, sid, authenticated, |
---|
78 | distributor, db): |
---|
79 | s['priority'] = i |
---|
80 | s.update_priority(db) |
---|
81 | i += 1 |
---|
82 | |
---|
83 | @classmethod |
---|
84 | def move(cls, env, rule_id, priority, db=None): |
---|
85 | |
---|
86 | with env.db_transaction as db: |
---|
87 | cursor = db.cursor() |
---|
88 | cursor.execute(""" |
---|
89 | SELECT sid,authenticated,distributor |
---|
90 | FROM subscription |
---|
91 | WHERE id=%s |
---|
92 | """, (rule_id,)) |
---|
93 | sid, authenticated, distributor = cursor.fetchone() |
---|
94 | if priority > len(cls.find_by_sid_and_distributor( |
---|
95 | env, sid, authenticated, distributor, db)): |
---|
96 | return |
---|
97 | i = 1 |
---|
98 | for s in cls.find_by_sid_and_distributor(env, sid, authenticated, |
---|
99 | distributor, db): |
---|
100 | if int(s['id']) == int(rule_id): |
---|
101 | s['priority'] = priority |
---|
102 | s.update_priority(db) |
---|
103 | i -= 1 |
---|
104 | elif i == priority: |
---|
105 | i += 1 |
---|
106 | s['priority'] = i |
---|
107 | s.update_priority(db) |
---|
108 | else: |
---|
109 | s['priority'] = i |
---|
110 | s.update_priority(db) |
---|
111 | i += 1 |
---|
112 | |
---|
113 | @classmethod |
---|
114 | def update_format_by_distributor_and_sid(cls, env, distributor, sid, |
---|
115 | authenticated, format, db=None): |
---|
116 | |
---|
117 | with env.db_transaction as db: |
---|
118 | cursor = db.cursor() |
---|
119 | cursor.execute(""" |
---|
120 | UPDATE subscription |
---|
121 | SET format=%s |
---|
122 | WHERE distributor=%s |
---|
123 | AND sid=%s |
---|
124 | AND authenticated=%s |
---|
125 | """, (format, distributor, sid, int(authenticated))) |
---|
126 | |
---|
127 | @classmethod |
---|
128 | def find_by_sid_and_distributor(cls, env, sid, authenticated, distributor, |
---|
129 | db=None): |
---|
130 | subs = [] |
---|
131 | |
---|
132 | with env.db_query as db: |
---|
133 | cursor = db.cursor() |
---|
134 | cursor.execute(""" |
---|
135 | SELECT id,sid,authenticated,distributor, |
---|
136 | format,priority,adverb,class |
---|
137 | FROM subscription |
---|
138 | WHERE sid=%s |
---|
139 | AND authenticated=%s |
---|
140 | AND distributor=%s |
---|
141 | ORDER BY priority |
---|
142 | """, (sid, int(authenticated), distributor)) |
---|
143 | for i in cursor.fetchall(): |
---|
144 | sub = Subscription(env) |
---|
145 | sub['id'] = i[0] |
---|
146 | sub['sid'] = i[1] |
---|
147 | sub['authenticated'] = i[2] |
---|
148 | sub['distributor'] = i[3] |
---|
149 | sub['format'] = i[4] |
---|
150 | sub['priority'] = int(i[5]) |
---|
151 | sub['adverb'] = i[6] |
---|
152 | sub['class'] = i[7] |
---|
153 | subs.append(sub) |
---|
154 | |
---|
155 | return subs |
---|
156 | |
---|
157 | @classmethod |
---|
158 | def find_by_sids_and_class(cls, env, uids, klass, db=None): |
---|
159 | """uids should be a collection to tuples (sid, auth)""" |
---|
160 | if not uids: |
---|
161 | return [] |
---|
162 | |
---|
163 | subs = [] |
---|
164 | |
---|
165 | with env.db_query as db: |
---|
166 | cursor = db.cursor() |
---|
167 | for sid, authenticated in uids: |
---|
168 | cursor.execute(""" |
---|
169 | SELECT id,sid,authenticated,distributor, |
---|
170 | format,priority,adverb,class |
---|
171 | FROM subscription |
---|
172 | WHERE class=%s |
---|
173 | AND sid=%s |
---|
174 | AND authenticated=%s |
---|
175 | """, (klass, sid, int(authenticated))) |
---|
176 | for i in cursor.fetchall(): |
---|
177 | sub = Subscription(env) |
---|
178 | sub['id'] = i[0] |
---|
179 | sub['sid'] = i[1] |
---|
180 | sub['authenticated'] = i[2] |
---|
181 | sub['distributor'] = i[3] |
---|
182 | sub['format'] = i[4] |
---|
183 | sub['priority'] = int(i[5]) |
---|
184 | sub['adverb'] = i[6] |
---|
185 | sub['class'] = i[7] |
---|
186 | subs.append(sub) |
---|
187 | |
---|
188 | return subs |
---|
189 | |
---|
190 | @classmethod |
---|
191 | def find_by_class(cls, env, klass, db=None): |
---|
192 | subs = [] |
---|
193 | |
---|
194 | with env.db_query as db: |
---|
195 | cursor = db.cursor() |
---|
196 | cursor.execute(""" |
---|
197 | SELECT id,sid,authenticated,distributor, |
---|
198 | format,priority,adverb,class |
---|
199 | FROM subscription |
---|
200 | WHERE class=%s |
---|
201 | """, (klass,)) |
---|
202 | for i in cursor.fetchall(): |
---|
203 | sub = Subscription(env) |
---|
204 | sub['id'] = i[0] |
---|
205 | sub['sid'] = i[1] |
---|
206 | sub['authenticated'] = i[2] |
---|
207 | sub['distributor'] = i[3] |
---|
208 | sub['format'] = i[4] |
---|
209 | sub['priority'] = int(i[5]) |
---|
210 | sub['adverb'] = i[6] |
---|
211 | sub['class'] = i[7] |
---|
212 | subs.append(sub) |
---|
213 | |
---|
214 | return subs |
---|
215 | |
---|
216 | def subscription_tuple(self): |
---|
217 | return ( |
---|
218 | self.values['class'], |
---|
219 | self.values['distributor'], |
---|
220 | self.values['sid'], |
---|
221 | self.values['authenticated'], |
---|
222 | None, |
---|
223 | self.values['format'], |
---|
224 | int(self.values['priority']), |
---|
225 | self.values['adverb'] |
---|
226 | ) |
---|
227 | |
---|
228 | def update_priority(self, db=None): |
---|
229 | |
---|
230 | with self.env.db_transaction as db: |
---|
231 | cursor = db.cursor() |
---|
232 | now = to_utimestamp(datetime_now(utc)) |
---|
233 | cursor.execute(""" |
---|
234 | UPDATE subscription |
---|
235 | SET changetime=%s, |
---|
236 | priority=%s |
---|
237 | WHERE id=%s |
---|
238 | """, (now, int(self.values['priority']), self.values['id'])) |
---|
239 | |
---|
240 | |
---|
241 | class SubscriptionAttribute(object): |
---|
242 | |
---|
243 | fields = ('id', 'sid', 'authenticated', 'class', 'realm', 'target') |
---|
244 | |
---|
245 | def __init__(self, env): |
---|
246 | self.env = env |
---|
247 | self.values = {} |
---|
248 | |
---|
249 | def __getitem__(self, name): |
---|
250 | if name not in self.fields: |
---|
251 | raise KeyError(name) |
---|
252 | return self.values.get(name) |
---|
253 | |
---|
254 | def __setitem__(self, name, value): |
---|
255 | if name not in self.fields: |
---|
256 | raise KeyError(name) |
---|
257 | self.values[name] = value |
---|
258 | |
---|
259 | @classmethod |
---|
260 | def add(cls, env, sid, authenticated, klass, realm, attributes, db=None): |
---|
261 | """id and priority overwritten.""" |
---|
262 | |
---|
263 | with env.db_transaction as db: |
---|
264 | cursor = db.cursor() |
---|
265 | for a in attributes: |
---|
266 | cursor.execute(""" |
---|
267 | INSERT INTO subscription_attribute |
---|
268 | (sid,authenticated,class,realm,target) |
---|
269 | VALUES (%s,%s,%s,%s,%s) |
---|
270 | """, (sid, int(authenticated), klass, realm, a)) |
---|
271 | |
---|
272 | @classmethod |
---|
273 | def delete(cls, env, attribute_id, db=None): |
---|
274 | |
---|
275 | with env.db_transaction as db: |
---|
276 | cursor = db.cursor() |
---|
277 | cursor.execute(""" |
---|
278 | DELETE FROM subscription_attribute |
---|
279 | WHERE id=%s |
---|
280 | """, (attribute_id,)) |
---|
281 | |
---|
282 | @classmethod |
---|
283 | def delete_by_sid_and_class(cls, env, sid, authenticated, klass, db=None): |
---|
284 | |
---|
285 | with env.db_transaction as db: |
---|
286 | cursor = db.cursor() |
---|
287 | cursor.execute(""" |
---|
288 | DELETE FROM subscription_attribute |
---|
289 | WHERE sid=%s |
---|
290 | AND authenticated=%s |
---|
291 | AND class=%s |
---|
292 | """, (sid, int(authenticated), klass)) |
---|
293 | |
---|
294 | @classmethod |
---|
295 | def delete_by_sid_class_and_target(cls, env, sid, authenticated, klass, |
---|
296 | target, db=None): |
---|
297 | |
---|
298 | with env.db_transaction as db: |
---|
299 | cursor = db.cursor() |
---|
300 | cursor.execute(""" |
---|
301 | DELETE FROM subscription_attribute |
---|
302 | WHERE sid=%s |
---|
303 | AND authenticated=%s |
---|
304 | AND class=%s |
---|
305 | AND target=%s |
---|
306 | """, (sid, int(authenticated), klass, target)) |
---|
307 | |
---|
308 | @classmethod |
---|
309 | def delete_by_class_realm_and_target(cls, env, klass, realm, target, |
---|
310 | db=None): |
---|
311 | |
---|
312 | with env.db_transaction as db: |
---|
313 | cursor = db.cursor() |
---|
314 | cursor.execute(""" |
---|
315 | DELETE FROM subscription_attribute |
---|
316 | WHERE realm=%s |
---|
317 | AND class=%s |
---|
318 | AND target=%s |
---|
319 | """, (realm, klass, target)) |
---|
320 | |
---|
321 | @classmethod |
---|
322 | def find_by_sid_and_class(cls, env, sid, authenticated, klass, db=None): |
---|
323 | attrs = [] |
---|
324 | |
---|
325 | with env.db_query as db: |
---|
326 | cursor = db.cursor() |
---|
327 | cursor.execute(""" |
---|
328 | SELECT id,sid,authenticated,class,realm,target |
---|
329 | FROM subscription_attribute |
---|
330 | WHERE sid=%s |
---|
331 | AND authenticated=%s |
---|
332 | AND class=%s |
---|
333 | ORDER BY target |
---|
334 | """, (sid, int(authenticated), klass)) |
---|
335 | for i in cursor.fetchall(): |
---|
336 | attr = SubscriptionAttribute(env) |
---|
337 | attr['id'] = i[0] |
---|
338 | attr['sid'] = i[1] |
---|
339 | attr['authenticated'] = i[2] |
---|
340 | attr['class'] = i[3] |
---|
341 | attr['realm'] = i[4] |
---|
342 | attr['target'] = i[5] |
---|
343 | attrs.append(attr) |
---|
344 | |
---|
345 | return attrs |
---|
346 | |
---|
347 | @classmethod |
---|
348 | def find_by_sid_class_and_target(cls, env, sid, authenticated, klass, |
---|
349 | target, db=None): |
---|
350 | attrs = [] |
---|
351 | |
---|
352 | with env.db_query as db: |
---|
353 | cursor = db.cursor() |
---|
354 | cursor.execute(""" |
---|
355 | SELECT id,sid,authenticated,class,realm,target |
---|
356 | FROM subscription_attribute |
---|
357 | WHERE sid=%s |
---|
358 | AND authenticated=%s |
---|
359 | AND class=%s |
---|
360 | AND target=%s |
---|
361 | ORDER BY target |
---|
362 | """, (sid, int(authenticated), klass, target)) |
---|
363 | for i in cursor.fetchall(): |
---|
364 | attr = SubscriptionAttribute(env) |
---|
365 | attr['id'] = i[0] |
---|
366 | attr['sid'] = i[1] |
---|
367 | attr['authenticated'] = i[2] |
---|
368 | attr['class'] = i[3] |
---|
369 | attr['realm'] = i[4] |
---|
370 | attr['target'] = i[5] |
---|
371 | attrs.append(attr) |
---|
372 | |
---|
373 | return attrs |
---|
374 | |
---|
375 | @classmethod |
---|
376 | def find_by_sid_class_realm_and_target(cls, env, sid, authenticated, |
---|
377 | klass, realm, target, db=None): |
---|
378 | attrs = [] |
---|
379 | |
---|
380 | with env.db_query as db: |
---|
381 | cursor = db.cursor() |
---|
382 | cursor.execute(""" |
---|
383 | SELECT id,sid,authenticated,class,realm,target |
---|
384 | FROM subscription_attribute |
---|
385 | WHERE sid=%s |
---|
386 | AND authenticated=%s |
---|
387 | AND class=%s |
---|
388 | AND realm=%s |
---|
389 | AND target=%s |
---|
390 | ORDER BY target |
---|
391 | """, (sid, int(authenticated), klass, realm, target)) |
---|
392 | for i in cursor.fetchall(): |
---|
393 | attr = SubscriptionAttribute(env) |
---|
394 | attr['id'] = i[0] |
---|
395 | attr['sid'] = i[1] |
---|
396 | attr['authenticated'] = i[2] |
---|
397 | attr['class'] = i[3] |
---|
398 | attr['realm'] = i[4] |
---|
399 | attr['target'] = i[5] |
---|
400 | attrs.append(attr) |
---|
401 | |
---|
402 | return attrs |
---|
403 | |
---|
404 | @classmethod |
---|
405 | def find_by_class_realm_and_target(cls, env, klass, realm, target, |
---|
406 | db=None): |
---|
407 | attrs = [] |
---|
408 | |
---|
409 | with env.db_query as db: |
---|
410 | cursor = db.cursor() |
---|
411 | cursor.execute(""" |
---|
412 | SELECT id,sid,authenticated,class,realm,target |
---|
413 | FROM subscription_attribute |
---|
414 | WHERE class=%s |
---|
415 | AND realm=%s |
---|
416 | AND target=%s |
---|
417 | """, (klass, realm, target)) |
---|
418 | for i in cursor.fetchall(): |
---|
419 | attr = SubscriptionAttribute(env) |
---|
420 | attr['id'] = i[0] |
---|
421 | attr['sid'] = i[1] |
---|
422 | attr['authenticated'] = i[2] |
---|
423 | attr['class'] = i[3] |
---|
424 | attr['realm'] = i[4] |
---|
425 | attr['target'] = i[5] |
---|
426 | attrs.append(attr) |
---|
427 | |
---|
428 | return attrs |
---|
429 | |
---|
430 | @classmethod |
---|
431 | def find_by_class_and_realm(cls, env, klass, realm, db=None): |
---|
432 | attrs = [] |
---|
433 | |
---|
434 | with env.db_query as db: |
---|
435 | cursor = db.cursor() |
---|
436 | cursor.execute(""" |
---|
437 | SELECT id,sid,authenticated,class,realm,target |
---|
438 | FROM subscription_attribute |
---|
439 | WHERE class=%s |
---|
440 | AND realm=%s |
---|
441 | """, (klass, realm)) |
---|
442 | for i in cursor.fetchall(): |
---|
443 | attr = SubscriptionAttribute(env) |
---|
444 | attr['id'] = i[0] |
---|
445 | attr['sid'] = i[1] |
---|
446 | attr['authenticated'] = i[2] |
---|
447 | attr['class'] = i[3] |
---|
448 | attr['realm'] = i[4] |
---|
449 | attr['target'] = i[5] |
---|
450 | attrs.append(attr) |
---|
451 | |
---|
452 | return attrs |
---|
453 | |
---|
454 | @classmethod |
---|
455 | def change_target(cls, env, klass, realm, target, new_target): |
---|
456 | env.db_transaction(""" |
---|
457 | UPDATE subscription_attribute SET target=%s |
---|
458 | WHERE class=%s AND realm=%s AND target=%s |
---|
459 | """, (new_target, klass, realm, target)) |
---|