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