1 #!/usr/bin/env python 2 3 """ 4 A database store of calendar data. 5 6 Copyright (C) 2014, 2015, 2016, 2017 Paul Boddie <paul@boddie.org.uk> 7 8 This program is free software; you can redistribute it and/or modify it under 9 the terms of the GNU General Public License as published by the Free Software 10 Foundation; either version 3 of the License, or (at your option) any later 11 version. 12 13 This program is distributed in the hope that it will be useful, but WITHOUT 14 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS 15 FOR A PARTICULAR PURPOSE. See the GNU General Public License for more 16 details. 17 18 You should have received a copy of the GNU General Public License along with 19 this program. If not, see <http://www.gnu.org/licenses/>. 20 """ 21 22 from imiptools.stores.common import StoreBase, JournalBase 23 24 from datetime import datetime 25 from imiptools.data import Object, parse_string, to_string 26 from imiptools.dates import format_datetime, get_datetime, to_timezone 27 from imiptools.freebusy import FreeBusyDatabaseCollection, \ 28 FreeBusyGroupDatabaseCollection, \ 29 FreeBusyOffersDatabaseCollection 30 from imiptools.sql import DatabaseOperations 31 32 def first(l): return l[0] 33 34 # Store classes. 35 36 class DatabaseStoreBase(DatabaseOperations): 37 38 "A database store supporting user-specific locking." 39 40 def __init__(self, connection, paramstyle=None): 41 DatabaseOperations.__init__(self, paramstyle=paramstyle) 42 self.connection = connection 43 self.cursor = connection.cursor() 44 45 def acquire_lock(self, user, timeout=None): 46 pass 47 48 def release_lock(self, user): 49 pass 50 51 def with_tables(self, query): 52 53 "Parameterise tables in the given 'query' for common operations." 54 55 return query % { 56 "objects" : self.objects_table, 57 "recurrences" : self.recurrences_table, 58 "freebusy_other" : self.freebusy_other_table, 59 "freebusy_providers" : self.freebusy_providers_table, 60 "freebusy_provider_datetimes" : self.freebusy_provider_datetimes_table, 61 } 62 63 def get_single_values(self): 64 65 """ 66 Return the cursor results as a list of single values from each of the 67 result tuples. 68 """ 69 70 return map(first, self.cursor.fetchall()) 71 72 class DatabaseStore(DatabaseStoreBase, StoreBase): 73 74 "A database store of tabular free/busy data and objects." 75 76 objects_table = "objects" 77 recurrences_table = "recurrences" 78 freebusy_other_table = "freebusy_other" 79 freebusy_providers_table = "freebusy_providers" 80 freebusy_provider_datetimes_table = "freebusy_provider_datetimes" 81 82 # User discovery. 83 84 def get_users(self): 85 86 "Return a list of users." 87 88 query = self.with_tables( 89 "select distinct store_user from (" \ 90 "select store_user from freebusy " \ 91 "union all select store_user from %(objects)s " \ 92 "union all select store_user from %(recurrences)s" \ 93 ") as users") 94 self.cursor.execute(query) 95 return self.get_single_values() 96 97 # Event and event metadata access. 98 99 def get_all_events(self, user, dirname=None): 100 101 """ 102 Return a set of (uid, recurrenceid) tuples for all events. Unless 103 'dirname' is specified, only active events are returned; otherwise, 104 events from the given 'dirname' are returned. 105 """ 106 107 columns, values = self.get_event_table_filters(dirname) 108 109 columns += ["store_user"] 110 values += [user] 111 112 query, values = self.get_query(self.with_tables( 113 "select object_uid, null as object_recurrenceid from %(objects)s :condition " 114 "union all " 115 "select object_uid, object_recurrenceid from %(recurrences)s :condition"), 116 columns, values) 117 118 self.cursor.execute(query, values) 119 return self.cursor.fetchall() 120 121 def get_events(self, user, dirname=None): 122 123 "Return a list of event identifiers." 124 125 columns, values = self.get_event_table_filters(dirname) 126 127 columns += ["store_user"] 128 values += [user] 129 130 query, values = self.get_query(self.with_tables( 131 "select object_uid from %(objects)s :condition"), 132 columns, values) 133 134 self.cursor.execute(query, values) 135 return self.get_single_values() 136 137 def get_cancelled_events(self, user): 138 139 "Return a list of event identifiers for cancelled events." 140 141 return self.get_events(user, "cancellations") 142 143 def get_event(self, user, uid, recurrenceid=None, dirname=None): 144 145 """ 146 Get the event for the given 'user' with the given 'uid'. If 147 the optional 'recurrenceid' is specified, a specific instance or 148 occurrence of an event is returned. 149 """ 150 151 table = self.get_event_table(recurrenceid, dirname) 152 columns, values = self.get_event_table_filters(dirname) 153 154 if recurrenceid: 155 columns += ["store_user", "object_uid", "object_recurrenceid"] 156 values += [user, uid, recurrenceid] 157 else: 158 columns += ["store_user", "object_uid"] 159 values += [user, uid] 160 161 query, values = self.get_query( 162 "select object_text from %(table)s :condition" % { 163 "table" : table 164 }, 165 columns, values) 166 167 self.cursor.execute(query, values) 168 result = self.cursor.fetchone() 169 return result and Object(parse_string(result[0], "utf-8")) 170 171 def get_complete_event(self, user, uid): 172 173 "Get the event for the given 'user' with the given 'uid'." 174 175 columns = ["store_user", "object_uid"] 176 values = [user, uid] 177 178 query, values = self.get_query(self.with_tables( 179 "select object_text from %(objects)s :condition"), 180 columns, values) 181 182 self.cursor.execute(query, values) 183 result = self.cursor.fetchone() 184 return result and Object(parse_string(result[0], "utf-8")) 185 186 def set_complete_event(self, user, uid, node): 187 188 "Set an event for 'user' having the given 'uid' and 'node'." 189 190 columns = ["store_user", "object_uid"] 191 values = [user, uid] 192 setcolumns = ["object_text", "status"] 193 setvalues = [to_string(node, "utf-8"), "active"] 194 195 query, values = self.get_query(self.with_tables( 196 "update %(objects)s :set :condition"), 197 columns, values, setcolumns, setvalues) 198 199 self.cursor.execute(query, values) 200 201 if self.cursor.rowcount > 0 or self.get_complete_event(user, uid): 202 return True 203 204 columns = ["store_user", "object_uid", "object_text", "status"] 205 values = [user, uid, to_string(node, "utf-8"), "active"] 206 207 query, values = self.get_query(self.with_tables( 208 "insert into %(objects)s (:columns) values (:values)"), 209 columns, values) 210 211 self.cursor.execute(query, values) 212 return True 213 214 def remove_parent_event(self, user, uid): 215 216 "Remove the parent event for 'user' having the given 'uid'." 217 218 columns = ["store_user", "object_uid"] 219 values = [user, uid] 220 221 query, values = self.get_query(self.with_tables( 222 "delete from %(objects)s :condition"), 223 columns, values) 224 225 self.cursor.execute(query, values) 226 return self.cursor.rowcount > 0 227 228 def get_active_recurrences(self, user, uid): 229 230 """ 231 Get additional event instances for an event of the given 'user' with the 232 indicated 'uid'. Cancelled recurrences are not returned. 233 """ 234 235 columns = ["store_user", "object_uid", "status"] 236 values = [user, uid, "active"] 237 238 query, values = self.get_query(self.with_tables( 239 "select object_recurrenceid from %(recurrences)s :condition"), 240 columns, values) 241 242 self.cursor.execute(query, values) 243 return [t[0] for t in self.cursor.fetchall() or []] 244 245 def get_cancelled_recurrences(self, user, uid): 246 247 """ 248 Get additional event instances for an event of the given 'user' with the 249 indicated 'uid'. Only cancelled recurrences are returned. 250 """ 251 252 columns = ["store_user", "object_uid", "status"] 253 values = [user, uid, "cancelled"] 254 255 query, values = self.get_query(self.with_tables( 256 "select object_recurrenceid from %(recurrences)s :condition"), 257 columns, values) 258 259 self.cursor.execute(query, values) 260 return [t[0] for t in self.cursor.fetchall() or []] 261 262 def get_recurrence(self, user, uid, recurrenceid): 263 264 """ 265 For the event of the given 'user' with the given 'uid', return the 266 specific recurrence indicated by the 'recurrenceid'. 267 """ 268 269 columns = ["store_user", "object_uid", "object_recurrenceid"] 270 values = [user, uid, recurrenceid] 271 272 query, values = self.get_query(self.with_tables( 273 "select object_text from %(recurrences)s :condition"), 274 columns, values) 275 276 self.cursor.execute(query, values) 277 result = self.cursor.fetchone() 278 return result and Object(parse_string(result[0], "utf-8")) 279 280 def set_recurrence(self, user, uid, recurrenceid, node): 281 282 "Set an event for 'user' having the given 'uid' and 'node'." 283 284 columns = ["store_user", "object_uid", "object_recurrenceid"] 285 values = [user, uid, recurrenceid] 286 setcolumns = ["object_text", "status"] 287 setvalues = [to_string(node, "utf-8"), "active"] 288 289 query, values = self.get_query(self.with_tables( 290 "update %(recurrences)s :set :condition"), 291 columns, values, setcolumns, setvalues) 292 293 self.cursor.execute(query, values) 294 295 if self.cursor.rowcount > 0 or self.get_recurrence(user, uid, recurrenceid): 296 return True 297 298 columns = ["store_user", "object_uid", "object_recurrenceid", "object_text", "status"] 299 values = [user, uid, recurrenceid, to_string(node, "utf-8"), "active"] 300 301 query, values = self.get_query(self.with_tables( 302 "insert into %(recurrences)s (:columns) values (:values)"), 303 columns, values) 304 305 self.cursor.execute(query, values) 306 return True 307 308 def remove_recurrence(self, user, uid, recurrenceid): 309 310 """ 311 Remove a special recurrence from an event stored by 'user' having the 312 given 'uid' and 'recurrenceid'. 313 """ 314 315 columns = ["store_user", "object_uid", "object_recurrenceid"] 316 values = [user, uid, recurrenceid] 317 318 query, values = self.get_query(self.with_tables( 319 "delete from %(recurrences)s :condition"), 320 columns, values) 321 322 self.cursor.execute(query, values) 323 return True 324 325 def remove_recurrences(self, user, uid): 326 327 """ 328 Remove all recurrences for an event stored by 'user' having the given 329 'uid'. 330 """ 331 332 columns = ["store_user", "object_uid"] 333 values = [user, uid] 334 335 query, values = self.get_query(self.with_tables( 336 "delete from %(recurrences)s :condition"), 337 columns, values) 338 339 self.cursor.execute(query, values) 340 return True 341 342 # Event table computation. 343 344 def get_event_table(self, recurrenceid=None, dirname=None): 345 346 "Get the table providing events for any specified 'dirname'." 347 348 if recurrenceid: 349 return self.get_recurrence_table(dirname) 350 else: 351 return self.get_complete_event_table(dirname) 352 353 def get_event_table_filters(self, dirname=None): 354 355 "Get filter details for any specified 'dirname'." 356 357 if dirname == "cancellations": 358 return ["status"], ["cancelled"] 359 else: 360 return ["status"], ["active"] 361 362 def get_complete_event_table(self, dirname=None): 363 364 "Get the table providing events for any specified 'dirname'." 365 366 if dirname == "counters": 367 return "countered_%s" % self.objects_table 368 else: 369 return self.objects_table 370 371 def get_recurrence_table(self, dirname=None): 372 373 "Get the table providing recurrences for any specified 'dirname'." 374 375 if dirname == "counters": 376 return "countered_%s" % self.recurrences_table 377 else: 378 return self.recurrences_table 379 380 # Free/busy period providers, upon extension of the free/busy records. 381 382 def _get_freebusy_providers(self, user): 383 384 """ 385 Return the free/busy providers for the given 'user'. 386 387 This function returns any stored datetime and a list of providers as a 388 2-tuple. Each provider is itself a (uid, recurrenceid) tuple. 389 """ 390 391 columns = ["store_user"] 392 values = [user] 393 394 query, values = self.get_query(self.with_tables( 395 "select object_uid, object_recurrenceid from %(freebusy_providers)s :condition"), 396 columns, values) 397 398 self.cursor.execute(query, values) 399 providers = self.cursor.fetchall() 400 401 columns = ["store_user"] 402 values = [user] 403 404 query, values = self.get_query(self.with_tables( 405 "select start from %(freebusy_provider_datetimes)s :condition"), 406 columns, values) 407 408 self.cursor.execute(query, values) 409 result = self.cursor.fetchone() 410 dt_string = result and result[0] 411 412 return dt_string, providers 413 414 def _set_freebusy_providers(self, user, dt_string, t): 415 416 "Set the given provider timestamp 'dt_string' and table 't'." 417 418 # NOTE: Locking? 419 420 columns = ["store_user"] 421 values = [user] 422 423 query, values = self.get_query(self.with_tables( 424 "delete from %(freebusy_providers)s :condition"), 425 columns, values) 426 427 self.cursor.execute(query, values) 428 429 columns = ["store_user", "object_uid", "object_recurrenceid"] 430 431 for uid, recurrenceid in t: 432 values = [user, uid, recurrenceid] 433 434 query, values = self.get_query(self.with_tables( 435 "insert into %(freebusy_providers)s (:columns) values (:values)"), 436 columns, values) 437 438 self.cursor.execute(query, values) 439 440 columns = ["store_user"] 441 values = [user] 442 setcolumns = ["start"] 443 setvalues = [dt_string] 444 445 query, values = self.get_query(self.with_tables( 446 "update %(freebusy_provider_datetimes)s :set :condition"), 447 columns, values, setcolumns, setvalues) 448 449 self.cursor.execute(query, values) 450 451 if self.cursor.rowcount > 0: 452 return True 453 454 columns = ["store_user", "start"] 455 values = [user, dt_string] 456 457 query, values = self.get_query(self.with_tables( 458 "insert into %(freebusy_provider_datetimes)s (:columns) values (:values)"), 459 columns, values) 460 461 self.cursor.execute(query, values) 462 return True 463 464 # Free/busy period access. 465 466 def get_freebusy(self, user, name=None, mutable=False, cls=None): 467 468 "Get free/busy details for the given 'user'." 469 470 table = name or "freebusy" 471 cls = cls or FreeBusyDatabaseCollection 472 return cls(self.cursor, table, ["store_user"], [user], mutable, self.paramstyle) 473 474 def get_freebusy_for_other(self, user, other, mutable=False, cls=None): 475 476 "For the given 'user', get free/busy details for the 'other' user." 477 478 cls = cls or FreeBusyDatabaseCollection 479 return cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], mutable, self.paramstyle) 480 481 def set_freebusy(self, user, freebusy, name=None, cls=None): 482 483 "For the given 'user', set 'freebusy' details." 484 485 table = name or "freebusy" 486 cls = cls or FreeBusyDatabaseCollection 487 488 if not isinstance(freebusy, cls) or freebusy.table_name != table: 489 fbc = cls(self.cursor, table, ["store_user"], [user], True, self.paramstyle) 490 fbc += freebusy 491 492 return True 493 494 def set_freebusy_for_other(self, user, freebusy, other, cls=None): 495 496 "For the given 'user', set 'freebusy' details for the 'other' user." 497 498 cls = cls or FreeBusyDatabaseCollection 499 500 if not isinstance(freebusy, cls) or freebusy.table_name != self.freebusy_other_table: 501 fbc = cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], True, self.paramstyle) 502 fbc += freebusy 503 504 return True 505 506 def get_freebusy_others(self, user): 507 508 """ 509 For the given 'user', return a list of other users for whom free/busy 510 information is retained. 511 """ 512 513 columns = ["store_user"] 514 values = [user] 515 516 query, values = self.get_query(self.with_tables( 517 "select distinct other from %(freebusy_other)s :condition"), 518 columns, values) 519 520 self.cursor.execute(query, values) 521 return self.get_single_values() 522 523 # Tentative free/busy periods related to countering. 524 525 def get_freebusy_offers(self, user, mutable=False): 526 527 "Get free/busy offers for the given 'user'." 528 529 # Expire old offers and save the collection if modified. 530 531 now = format_datetime(to_timezone(datetime.utcnow(), "UTC")) 532 columns = ["store_user", "expires"] 533 values = [user, now] 534 535 query, values = self.get_query( 536 "delete from freebusy_offers :condition", 537 columns, values) 538 539 self.cursor.execute(query, values) 540 541 return self.get_freebusy(user, "freebusy_offers", mutable, FreeBusyOffersDatabaseCollection) 542 543 def set_freebusy_offers(self, user, freebusy): 544 545 "For the given 'user', set 'freebusy' offers." 546 547 return self.set_freebusy(user, freebusy, "freebusy_offers", cls=FreeBusyOffersDatabaseCollection) 548 549 # Requests and counter-proposals. 550 551 def get_requests(self, user): 552 553 "Get requests for the given 'user'." 554 555 columns = ["store_user"] 556 values = [user] 557 558 query, values = self.get_query( 559 "select object_uid, object_recurrenceid, request_type from requests :condition", 560 columns, values) 561 562 self.cursor.execute(query, values) 563 return self.cursor.fetchall() 564 565 def set_request(self, user, uid, recurrenceid=None, type=None): 566 567 """ 568 For the given 'user', set the queued 'uid' and 'recurrenceid', 569 indicating a request, along with any given 'type'. 570 """ 571 572 columns = ["store_user", "object_uid", "object_recurrenceid", "request_type"] 573 values = [user, uid, recurrenceid, type] 574 575 query, values = self.get_query( 576 "insert into requests (:columns) values (:values)", 577 columns, values) 578 579 self.cursor.execute(query, values) 580 return True 581 582 def queue_request(self, user, uid, recurrenceid=None, type=None): 583 584 """ 585 Queue a request for 'user' having the given 'uid'. If the optional 586 'recurrenceid' is specified, the entry refers to a specific instance 587 or occurrence of an event. The 'type' parameter can be used to indicate 588 a specific type of request. 589 """ 590 591 if recurrenceid: 592 columns = ["store_user", "object_uid", "object_recurrenceid"] 593 values = [user, uid, recurrenceid] 594 else: 595 columns = ["store_user", "object_uid"] 596 values = [user, uid] 597 598 setcolumns = ["request_type"] 599 setvalues = [type] 600 601 query, values = self.get_query( 602 "update requests :set :condition", 603 columns, values, setcolumns, setvalues) 604 605 self.cursor.execute(query, values) 606 607 if self.cursor.rowcount > 0: 608 return 609 610 self.set_request(user, uid, recurrenceid, type) 611 612 def dequeue_request(self, user, uid, recurrenceid=None): 613 614 """ 615 Dequeue all requests for 'user' having the given 'uid'. If the optional 616 'recurrenceid' is specified, all requests for that specific instance or 617 occurrence of an event are dequeued. 618 """ 619 620 if recurrenceid: 621 columns = ["store_user", "object_uid", "object_recurrenceid"] 622 values = [user, uid, recurrenceid] 623 else: 624 columns = ["store_user", "object_uid"] 625 values = [user, uid] 626 627 query, values = self.get_query( 628 "delete from requests :condition", 629 columns, values) 630 631 self.cursor.execute(query, values) 632 return True 633 634 def get_counters(self, user, uid, recurrenceid=None): 635 636 """ 637 For the given 'user', return a list of users from whom counter-proposals 638 have been received for the given 'uid' and optional 'recurrenceid'. 639 """ 640 641 table = self.get_event_table(recurrenceid, "counters") 642 643 if recurrenceid: 644 columns = ["store_user", "object_uid", "object_recurrenceid"] 645 values = [user, uid, recurrenceid] 646 else: 647 columns = ["store_user", "object_uid"] 648 values = [user, uid] 649 650 query, values = self.get_query( 651 "select other from %(table)s :condition" % { 652 "table" : table 653 }, 654 columns, values) 655 656 self.cursor.execute(query, values) 657 return self.get_single_values() 658 659 def get_counter(self, user, other, uid, recurrenceid=None): 660 661 """ 662 For the given 'user', return the counter-proposal from 'other' for the 663 given 'uid' and optional 'recurrenceid'. 664 """ 665 666 table = self.get_event_table(recurrenceid, "counters") 667 668 if recurrenceid: 669 columns = ["store_user", "other", "object_uid", "object_recurrenceid"] 670 values = [user, other, uid, recurrenceid] 671 else: 672 columns = ["store_user", "other", "object_uid"] 673 values = [user, other, uid] 674 675 query, values = self.get_query( 676 "select object_text from %(table)s :condition" % { 677 "table" : table 678 }, 679 columns, values) 680 681 self.cursor.execute(query, values) 682 result = self.cursor.fetchone() 683 return result and Object(parse_string(result[0], "utf-8")) 684 685 def set_counter(self, user, other, node, uid, recurrenceid=None): 686 687 """ 688 For the given 'user', store a counter-proposal received from 'other' the 689 given 'node' representing that proposal for the given 'uid' and 690 'recurrenceid'. 691 """ 692 693 table = self.get_event_table(recurrenceid, "counters") 694 695 if recurrenceid: 696 columns = ["store_user", "other", "object_uid", "object_recurrenceid", "object_text"] 697 values = [user, other, uid, recurrenceid, to_string(node, "utf-8")] 698 else: 699 columns = ["store_user", "other", "object_uid", "object_text"] 700 values = [user, other, uid, to_string(node, "utf-8")] 701 702 query, values = self.get_query( 703 "insert into %(table)s (:columns) values (:values)" % { 704 "table" : table 705 }, 706 columns, values) 707 708 self.cursor.execute(query, values) 709 return True 710 711 def remove_counters(self, user, uid, recurrenceid=None): 712 713 """ 714 For the given 'user', remove all counter-proposals associated with the 715 given 'uid' and 'recurrenceid'. 716 """ 717 718 table = self.get_event_table(recurrenceid, "counters") 719 720 if recurrenceid: 721 columns = ["store_user", "object_uid", "object_recurrenceid"] 722 values = [user, uid, recurrenceid] 723 else: 724 columns = ["store_user", "object_uid"] 725 values = [user, uid] 726 727 query, values = self.get_query( 728 "delete from %(table)s :condition" % { 729 "table" : table 730 }, 731 columns, values) 732 733 self.cursor.execute(query, values) 734 return True 735 736 def remove_counter(self, user, other, uid, recurrenceid=None): 737 738 """ 739 For the given 'user', remove any counter-proposal from 'other' 740 associated with the given 'uid' and 'recurrenceid'. 741 """ 742 743 table = self.get_event_table(recurrenceid, "counters") 744 745 if recurrenceid: 746 columns = ["store_user", "other", "object_uid", "object_recurrenceid"] 747 values = [user, other, uid, recurrenceid] 748 else: 749 columns = ["store_user", "other", "object_uid"] 750 values = [user, other, uid] 751 752 query, values = self.get_query( 753 "delete from %(table)s :condition" % { 754 "table" : table 755 }, 756 columns, values) 757 758 self.cursor.execute(query, values) 759 return True 760 761 # Event cancellation. 762 763 def cancel_event(self, user, uid, recurrenceid=None): 764 765 """ 766 Cancel an event for 'user' having the given 'uid'. If the optional 767 'recurrenceid' is specified, a specific instance or occurrence of an 768 event is cancelled. 769 """ 770 771 table = self.get_event_table(recurrenceid) 772 773 if recurrenceid: 774 columns = ["store_user", "object_uid", "object_recurrenceid"] 775 values = [user, uid, recurrenceid] 776 else: 777 columns = ["store_user", "object_uid"] 778 values = [user, uid] 779 780 setcolumns = ["status"] 781 setvalues = ["cancelled"] 782 783 query, values = self.get_query( 784 "update %(table)s :set :condition" % { 785 "table" : table 786 }, 787 columns, values, setcolumns, setvalues) 788 789 self.cursor.execute(query, values) 790 return True 791 792 def uncancel_event(self, user, uid, recurrenceid=None): 793 794 """ 795 Uncancel an event for 'user' having the given 'uid'. If the optional 796 'recurrenceid' is specified, a specific instance or occurrence of an 797 event is uncancelled. 798 """ 799 800 table = self.get_event_table(recurrenceid) 801 802 if recurrenceid: 803 columns = ["store_user", "object_uid", "object_recurrenceid"] 804 values = [user, uid, recurrenceid] 805 else: 806 columns = ["store_user", "object_uid"] 807 values = [user, uid] 808 809 setcolumns = ["status"] 810 setvalues = ["active"] 811 812 query, values = self.get_query( 813 "update %(table)s :set :condition" % { 814 "table" : table 815 }, 816 columns, values, setcolumns, setvalues) 817 818 self.cursor.execute(query, values) 819 return True 820 821 def remove_cancellation(self, user, uid, recurrenceid=None): 822 823 """ 824 Remove a cancellation for 'user' for the event having the given 'uid'. 825 If the optional 'recurrenceid' is specified, a specific instance or 826 occurrence of an event is affected. 827 """ 828 829 table = self.get_event_table(recurrenceid) 830 831 if recurrenceid: 832 columns = ["store_user", "object_uid", "object_recurrenceid", "status"] 833 values = [user, uid, recurrenceid, "cancelled"] 834 else: 835 columns = ["store_user", "object_uid", "status"] 836 values = [user, uid, "cancelled"] 837 838 query, values = self.get_query( 839 "delete from %(table)s :condition" % { 840 "table" : table 841 }, 842 columns, values) 843 844 self.cursor.execute(query, values) 845 return True 846 847 class DatabaseJournal(DatabaseStore, JournalBase): 848 849 "A journal system to support quotas." 850 851 objects_table = "journal_objects" 852 recurrences_table = "journal_recurrences" 853 freebusy_other_table = "journal_freebusy_other" 854 freebusy_providers_table = "journal_freebusy_providers" 855 freebusy_provider_datetimes_table = "journal_freebusy_provider_datetimes" 856 857 # Quota and user identity/group discovery. 858 859 def get_quotas(self): 860 861 "Return a list of quotas." 862 863 query = self.with_tables("select distinct quota from (" \ 864 "select distinct store_user as quota from %(freebusy_other)s " \ 865 "union all select quota from quota_limits" \ 866 ") as quotas") 867 self.cursor.execute(query) 868 return self.get_single_values() 869 870 def get_quota_users(self, quota): 871 872 "Return a list of quota users for the 'quota'." 873 874 columns = ["quota"] 875 values = [quota] 876 877 query, values = self.get_query(self.with_tables( 878 "select distinct user_group from (" \ 879 "select distinct other as user_group from %(freebusy_other)s :condition " \ 880 "union all select user_group from quota_delegates :condition" \ 881 ") as users"), 882 columns, values) 883 884 self.cursor.execute(query, values) 885 return self.get_single_values() 886 887 # Delegate information for the quota. 888 889 def get_delegates(self, quota): 890 891 "Return a list of delegates for 'quota'." 892 893 columns = ["quota"] 894 values = [quota] 895 896 query, values = self.get_query( 897 "select distinct store_user from quota_delegates :condition", 898 columns, values) 899 900 self.cursor.execute(query, values) 901 return self.get_single_values() 902 903 def set_delegates(self, quota, delegates): 904 905 "For the given 'quota', set the list of 'delegates'." 906 907 columns = ["quota"] 908 values = [quota] 909 910 query, values = self.get_query( 911 "delete from quota_delegates :condition", 912 columns, values) 913 914 self.cursor.execute(query, values) 915 916 for store_user in delegates: 917 918 columns = ["quota", "store_user"] 919 values = [quota, store_user] 920 921 query, values = self.get_query( 922 "insert into quota_delegates (:columns) values (:values)", 923 columns, values) 924 925 self.cursor.execute(query, values) 926 927 return True 928 929 # Groups of users sharing quotas. 930 931 def get_groups(self, quota): 932 933 "Return the identity mappings for the given 'quota' as a dictionary." 934 935 columns = ["quota"] 936 values = [quota] 937 938 query, values = self.get_query( 939 "select store_user, user_group from user_groups :condition", 940 columns, values) 941 942 self.cursor.execute(query, values) 943 return dict(self.cursor.fetchall()) 944 945 def set_groups(self, quota, groups): 946 947 "For the given 'quota', set 'groups' mapping users to groups." 948 949 columns = ["quota"] 950 values = [quota] 951 952 query, values = self.get_query( 953 "delete from user_groups :condition", 954 columns, values) 955 956 self.cursor.execute(query, values) 957 958 for store_user, user_group in groups.items(): 959 960 columns = ["quota", "store_user", "user_group"] 961 values = [quota, store_user, user_group] 962 963 query, values = self.get_query( 964 "insert into user_groups (:columns) values (:values)", 965 columns, values) 966 967 self.cursor.execute(query, values) 968 969 return True 970 971 def get_limits(self, quota): 972 973 """ 974 Return the limits for the 'quota' as a dictionary mapping identities or 975 groups to durations. 976 """ 977 978 columns = ["quota"] 979 values = [quota] 980 981 query, values = self.get_query( 982 "select user_group, quota_limit from quota_limits :condition", 983 columns, values) 984 985 self.cursor.execute(query, values) 986 return dict(self.cursor.fetchall()) 987 988 def set_limits(self, quota, limits): 989 990 """ 991 For the given 'quota', set the given 'limits' on resource usage mapping 992 groups to limits. 993 """ 994 995 columns = ["quota"] 996 values = [quota] 997 998 query, values = self.get_query( 999 "delete from quota_limits :condition", 1000 columns, values) 1001 1002 self.cursor.execute(query, values) 1003 1004 for user_group, limit in limits.items(): 1005 1006 columns = ["quota", "user_group", "quota_limit"] 1007 values = [quota, user_group, limit] 1008 1009 query, values = self.get_query( 1010 "insert into quota_limits (:columns) values (:values)", 1011 columns, values) 1012 1013 self.cursor.execute(query, values) 1014 1015 return True 1016 1017 # Journal entry methods. 1018 1019 def get_entries(self, quota, group, mutable=False): 1020 1021 """ 1022 Return a list of journal entries for the given 'quota' for the indicated 1023 'group'. 1024 """ 1025 1026 return self.get_freebusy_for_other(quota, group, mutable) 1027 1028 def set_entries(self, quota, group, entries): 1029 1030 """ 1031 For the given 'quota' and indicated 'group', set the list of journal 1032 'entries'. 1033 """ 1034 1035 return self.set_freebusy_for_other(quota, entries, group) 1036 1037 # Compatibility methods. 1038 1039 def get_freebusy_for_other(self, user, other, mutable=False): 1040 return DatabaseStore.get_freebusy_for_other(self, user, other, mutable, cls=FreeBusyGroupDatabaseCollection) 1041 1042 def set_freebusy_for_other(self, user, freebusy, other): 1043 return DatabaseStore.set_freebusy_for_other(self, user, freebusy, other, cls=FreeBusyGroupDatabaseCollection) 1044 1045 # vim: tabstop=4 expandtab shiftwidth=4