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