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