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, attendee=None): 739 740 """ 741 For the given 'user', remove all counter-proposals associated with the 742 given 'uid' and 'recurrenceid'. If 'attendee' is specified, only objects 743 provided by this attendee will be removed. 744 """ 745 746 table = self.get_event_table(recurrenceid, "counters") 747 748 if recurrenceid: 749 columns = ["store_user", "object_uid", "object_recurrenceid"] 750 values = [user, uid, recurrenceid] 751 else: 752 columns = ["store_user", "object_uid"] 753 values = [user, uid] 754 755 if attendee: 756 columns.append("other") 757 values.append(attendee) 758 759 query, values = self.get_query( 760 "delete from %(table)s :condition" % { 761 "table" : table 762 }, 763 columns, values) 764 765 self.cursor.execute(query, values) 766 return True 767 768 def remove_counter(self, user, other, uid, recurrenceid=None): 769 770 """ 771 For the given 'user', remove any counter-proposal from 'other' 772 associated with the given 'uid' and 'recurrenceid'. 773 """ 774 775 table = self.get_event_table(recurrenceid, "counters") 776 777 if recurrenceid: 778 columns = ["store_user", "other", "object_uid", "object_recurrenceid"] 779 values = [user, other, uid, recurrenceid] 780 else: 781 columns = ["store_user", "other", "object_uid"] 782 values = [user, other, uid] 783 784 query, values = self.get_query( 785 "delete from %(table)s :condition" % { 786 "table" : table 787 }, 788 columns, values) 789 790 self.cursor.execute(query, values) 791 return True 792 793 # Event cancellation. 794 795 def cancel_event(self, user, uid, recurrenceid=None): 796 797 """ 798 Cancel an event for 'user' having the given 'uid'. If the optional 799 'recurrenceid' is specified, a specific instance or occurrence of an 800 event is cancelled. 801 """ 802 803 table = self.get_event_table(recurrenceid) 804 805 if recurrenceid: 806 columns = ["store_user", "object_uid", "object_recurrenceid"] 807 values = [user, uid, recurrenceid] 808 else: 809 columns = ["store_user", "object_uid"] 810 values = [user, uid] 811 812 setcolumns = ["status"] 813 setvalues = ["cancelled"] 814 815 query, values = self.get_query( 816 "update %(table)s :set :condition" % { 817 "table" : table 818 }, 819 columns, values, setcolumns, setvalues) 820 821 self.cursor.execute(query, values) 822 return True 823 824 def uncancel_event(self, user, uid, recurrenceid=None): 825 826 """ 827 Uncancel an event for 'user' having the given 'uid'. If the optional 828 'recurrenceid' is specified, a specific instance or occurrence of an 829 event is uncancelled. 830 """ 831 832 table = self.get_event_table(recurrenceid) 833 834 if recurrenceid: 835 columns = ["store_user", "object_uid", "object_recurrenceid"] 836 values = [user, uid, recurrenceid] 837 else: 838 columns = ["store_user", "object_uid"] 839 values = [user, uid] 840 841 setcolumns = ["status"] 842 setvalues = ["active"] 843 844 query, values = self.get_query( 845 "update %(table)s :set :condition" % { 846 "table" : table 847 }, 848 columns, values, setcolumns, setvalues) 849 850 self.cursor.execute(query, values) 851 return True 852 853 def remove_cancellation(self, user, uid, recurrenceid=None): 854 855 """ 856 Remove a cancellation for 'user' for the event having the given 'uid'. 857 If the optional 'recurrenceid' is specified, a specific instance or 858 occurrence of an event is affected. 859 """ 860 861 table = self.get_event_table(recurrenceid) 862 863 if recurrenceid: 864 columns = ["store_user", "object_uid", "object_recurrenceid", "status"] 865 values = [user, uid, recurrenceid, "cancelled"] 866 else: 867 columns = ["store_user", "object_uid", "status"] 868 values = [user, uid, "cancelled"] 869 870 query, values = self.get_query( 871 "delete from %(table)s :condition" % { 872 "table" : table 873 }, 874 columns, values) 875 876 self.cursor.execute(query, values) 877 return True 878 879 class DatabaseJournal(DatabaseStore, JournalBase): 880 881 "A journal system to support quotas." 882 883 objects_table = "journal_objects" 884 recurrences_table = "journal_recurrences" 885 freebusy_other_table = "journal_freebusy_other" 886 freebusy_providers_table = "journal_freebusy_providers" 887 freebusy_provider_datetimes_table = "journal_freebusy_provider_datetimes" 888 889 # Quota and user identity/group discovery. 890 891 def get_quotas(self): 892 893 "Return a list of quotas." 894 895 query = self.with_tables("select distinct quota from (" \ 896 "select distinct store_user as quota from %(freebusy_other)s " \ 897 "union all select quota from quota_limits" \ 898 ") as quotas") 899 self.cursor.execute(query) 900 return self.get_single_values() 901 902 def get_quota_users(self, quota): 903 904 "Return a list of quota users for the 'quota'." 905 906 columns = ["quota"] 907 values = [quota] 908 909 query, values = self.get_query(self.with_tables( 910 "select distinct user_group from (" \ 911 "select distinct other as user_group from %(freebusy_other)s :condition " \ 912 "union all select user_group from quota_delegates :condition" \ 913 ") as users"), 914 columns, values) 915 916 self.cursor.execute(query, values) 917 return self.get_single_values() 918 919 # Delegate information for the quota. 920 921 def get_delegates(self, quota): 922 923 "Return a list of delegates for 'quota'." 924 925 columns = ["quota"] 926 values = [quota] 927 928 query, values = self.get_query( 929 "select distinct store_user from quota_delegates :condition", 930 columns, values) 931 932 self.cursor.execute(query, values) 933 return self.get_single_values() 934 935 def set_delegates(self, quota, delegates): 936 937 "For the given 'quota', set the list of 'delegates'." 938 939 columns = ["quota"] 940 values = [quota] 941 942 query, values = self.get_query( 943 "delete from quota_delegates :condition", 944 columns, values) 945 946 self.cursor.execute(query, values) 947 948 for store_user in delegates: 949 950 columns = ["quota", "store_user"] 951 values = [quota, store_user] 952 953 query, values = self.get_query( 954 "insert into quota_delegates (:columns) values (:values)", 955 columns, values) 956 957 self.cursor.execute(query, values) 958 959 return True 960 961 # Groups of users sharing quotas. 962 963 def get_groups(self, quota): 964 965 "Return the identity mappings for the given 'quota' as a dictionary." 966 967 columns = ["quota"] 968 values = [quota] 969 970 query, values = self.get_query( 971 "select store_user, user_group from user_groups :condition", 972 columns, values) 973 974 self.cursor.execute(query, values) 975 return dict(self.cursor.fetchall()) 976 977 def set_groups(self, quota, groups): 978 979 "For the given 'quota', set 'groups' mapping users to groups." 980 981 columns = ["quota"] 982 values = [quota] 983 984 query, values = self.get_query( 985 "delete from user_groups :condition", 986 columns, values) 987 988 self.cursor.execute(query, values) 989 990 for store_user, user_group in groups.items(): 991 992 columns = ["quota", "store_user", "user_group"] 993 values = [quota, store_user, user_group] 994 995 query, values = self.get_query( 996 "insert into user_groups (:columns) values (:values)", 997 columns, values) 998 999 self.cursor.execute(query, values) 1000 1001 return True 1002 1003 def get_limits(self, quota): 1004 1005 """ 1006 Return the limits for the 'quota' as a dictionary mapping identities or 1007 groups to durations. 1008 """ 1009 1010 columns = ["quota"] 1011 values = [quota] 1012 1013 query, values = self.get_query( 1014 "select user_group, quota_limit from quota_limits :condition", 1015 columns, values) 1016 1017 self.cursor.execute(query, values) 1018 return dict(self.cursor.fetchall()) 1019 1020 def set_limits(self, quota, limits): 1021 1022 """ 1023 For the given 'quota', set the given 'limits' on resource usage mapping 1024 groups to limits. 1025 """ 1026 1027 columns = ["quota"] 1028 values = [quota] 1029 1030 query, values = self.get_query( 1031 "delete from quota_limits :condition", 1032 columns, values) 1033 1034 self.cursor.execute(query, values) 1035 1036 for user_group, limit in limits.items(): 1037 1038 columns = ["quota", "user_group", "quota_limit"] 1039 values = [quota, user_group, limit] 1040 1041 query, values = self.get_query( 1042 "insert into quota_limits (:columns) values (:values)", 1043 columns, values) 1044 1045 self.cursor.execute(query, values) 1046 1047 return True 1048 1049 # Journal entry methods. 1050 1051 def get_entries(self, quota, group, mutable=False): 1052 1053 """ 1054 Return a list of journal entries for the given 'quota' for the indicated 1055 'group'. 1056 """ 1057 1058 return self.get_freebusy_for_other(quota, group, mutable) 1059 1060 def set_entries(self, quota, group, entries): 1061 1062 """ 1063 For the given 'quota' and indicated 'group', set the list of journal 1064 'entries'. 1065 """ 1066 1067 return self.set_freebusy_for_other(quota, entries, group) 1068 1069 # Compatibility methods. 1070 1071 def get_freebusy_for_other(self, user, other, mutable=False): 1072 return DatabaseStore.get_freebusy_for_other(self, user, other, mutable, collection=FreeBusyGroupDatabaseCollection) 1073 1074 def set_freebusy_for_other(self, user, freebusy, other): 1075 return DatabaseStore.set_freebusy_for_other(self, user, freebusy, other, collection=FreeBusyGroupDatabaseCollection) 1076 1077 # vim: tabstop=4 expandtab shiftwidth=4