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(self, user, other, uid, recurrenceid=None): 666 667 """ 668 For the given 'user', return the counter-proposal from 'other' for the 669 given 'uid' and optional 'recurrenceid'. 670 """ 671 672 table = self.get_event_table(recurrenceid, "counters") 673 674 if recurrenceid: 675 columns = ["store_user", "other", "object_uid", "object_recurrenceid"] 676 values = [user, other, uid, recurrenceid] 677 else: 678 columns = ["store_user", "other", "object_uid"] 679 values = [user, other, uid] 680 681 query, values = self.get_query( 682 "select object_text from %(table)s :condition" % { 683 "table" : table 684 }, 685 columns, values) 686 687 self.cursor.execute(query, values) 688 result = self.cursor.fetchone() 689 return result and Object(parse_string(result[0], "utf-8")) 690 691 def set_counter(self, user, other, node, uid, recurrenceid=None): 692 693 """ 694 For the given 'user', store a counter-proposal received from 'other' the 695 given 'node' representing that proposal for the given 'uid' and 696 'recurrenceid'. 697 """ 698 699 table = self.get_event_table(recurrenceid, "counters") 700 701 if recurrenceid: 702 columns = ["store_user", "other", "object_uid", "object_recurrenceid", "object_text"] 703 values = [user, other, uid, recurrenceid, to_string(node, "utf-8")] 704 else: 705 columns = ["store_user", "other", "object_uid", "object_text"] 706 values = [user, other, uid, to_string(node, "utf-8")] 707 708 query, values = self.get_query( 709 "insert into %(table)s (:columns) values (:values)" % { 710 "table" : table 711 }, 712 columns, values) 713 714 self.cursor.execute(query, values) 715 return True 716 717 def remove_counters(self, user, uid, recurrenceid=None): 718 719 """ 720 For the given 'user', remove all counter-proposals associated with the 721 given 'uid' and 'recurrenceid'. 722 """ 723 724 table = self.get_event_table(recurrenceid, "counters") 725 726 if recurrenceid: 727 columns = ["store_user", "object_uid", "object_recurrenceid"] 728 values = [user, uid, recurrenceid] 729 else: 730 columns = ["store_user", "object_uid"] 731 values = [user, uid] 732 733 query, values = self.get_query( 734 "delete from %(table)s :condition" % { 735 "table" : table 736 }, 737 columns, values) 738 739 self.cursor.execute(query, values) 740 return True 741 742 def remove_counter(self, user, other, uid, recurrenceid=None): 743 744 """ 745 For the given 'user', remove any counter-proposal from 'other' 746 associated with the given 'uid' and 'recurrenceid'. 747 """ 748 749 table = self.get_event_table(recurrenceid, "counters") 750 751 if recurrenceid: 752 columns = ["store_user", "other", "object_uid", "object_recurrenceid"] 753 values = [user, other, uid, recurrenceid] 754 else: 755 columns = ["store_user", "other", "object_uid"] 756 values = [user, other, uid] 757 758 query, values = self.get_query( 759 "delete from %(table)s :condition" % { 760 "table" : table 761 }, 762 columns, values) 763 764 self.cursor.execute(query, values) 765 return True 766 767 # Event cancellation. 768 769 def cancel_event(self, user, uid, recurrenceid=None): 770 771 """ 772 Cancel an event for 'user' having the given 'uid'. If the optional 773 'recurrenceid' is specified, a specific instance or occurrence of an 774 event is cancelled. 775 """ 776 777 table = self.get_event_table(recurrenceid) 778 779 if recurrenceid: 780 columns = ["store_user", "object_uid", "object_recurrenceid"] 781 values = [user, uid, recurrenceid] 782 else: 783 columns = ["store_user", "object_uid"] 784 values = [user, uid] 785 786 setcolumns = ["status"] 787 setvalues = ["cancelled"] 788 789 query, values = self.get_query( 790 "update %(table)s :set :condition" % { 791 "table" : table 792 }, 793 columns, values, setcolumns, setvalues) 794 795 self.cursor.execute(query, values) 796 return True 797 798 def uncancel_event(self, user, uid, recurrenceid=None): 799 800 """ 801 Uncancel an event for 'user' having the given 'uid'. If the optional 802 'recurrenceid' is specified, a specific instance or occurrence of an 803 event is uncancelled. 804 """ 805 806 table = self.get_event_table(recurrenceid) 807 808 if recurrenceid: 809 columns = ["store_user", "object_uid", "object_recurrenceid"] 810 values = [user, uid, recurrenceid] 811 else: 812 columns = ["store_user", "object_uid"] 813 values = [user, uid] 814 815 setcolumns = ["status"] 816 setvalues = ["active"] 817 818 query, values = self.get_query( 819 "update %(table)s :set :condition" % { 820 "table" : table 821 }, 822 columns, values, setcolumns, setvalues) 823 824 self.cursor.execute(query, values) 825 return True 826 827 def remove_cancellation(self, user, uid, recurrenceid=None): 828 829 """ 830 Remove a cancellation for 'user' for the event having the given 'uid'. 831 If the optional 'recurrenceid' is specified, a specific instance or 832 occurrence of an event is affected. 833 """ 834 835 table = self.get_event_table(recurrenceid) 836 837 if recurrenceid: 838 columns = ["store_user", "object_uid", "object_recurrenceid", "status"] 839 values = [user, uid, recurrenceid, "cancelled"] 840 else: 841 columns = ["store_user", "object_uid", "status"] 842 values = [user, uid, "cancelled"] 843 844 query, values = self.get_query( 845 "delete from %(table)s :condition" % { 846 "table" : table 847 }, 848 columns, values) 849 850 self.cursor.execute(query, values) 851 return True 852 853 class DatabaseJournal(DatabaseStore, JournalBase): 854 855 "A journal system to support quotas." 856 857 objects_table = "journal_objects" 858 recurrences_table = "journal_recurrences" 859 freebusy_other_table = "journal_freebusy_other" 860 freebusy_providers_table = "journal_freebusy_providers" 861 freebusy_provider_datetimes_table = "journal_freebusy_provider_datetimes" 862 863 # Quota and user identity/group discovery. 864 865 def get_quotas(self): 866 867 "Return a list of quotas." 868 869 query = self.with_tables("select distinct quota from (" \ 870 "select distinct store_user as quota from %(freebusy_other)s " \ 871 "union all select quota from quota_limits" \ 872 ") as quotas") 873 self.cursor.execute(query) 874 return self.get_single_values() 875 876 def get_quota_users(self, quota): 877 878 "Return a list of quota users for the 'quota'." 879 880 columns = ["quota"] 881 values = [quota] 882 883 query, values = self.get_query(self.with_tables( 884 "select distinct user_group from (" \ 885 "select distinct other as user_group from %(freebusy_other)s :condition " \ 886 "union all select user_group from quota_delegates :condition" \ 887 ") as users"), 888 columns, values) 889 890 self.cursor.execute(query, values) 891 return self.get_single_values() 892 893 # Delegate information for the quota. 894 895 def get_delegates(self, quota): 896 897 "Return a list of delegates for 'quota'." 898 899 columns = ["quota"] 900 values = [quota] 901 902 query, values = self.get_query( 903 "select distinct store_user from quota_delegates :condition", 904 columns, values) 905 906 self.cursor.execute(query, values) 907 return self.get_single_values() 908 909 def set_delegates(self, quota, delegates): 910 911 "For the given 'quota', set the list of 'delegates'." 912 913 columns = ["quota"] 914 values = [quota] 915 916 query, values = self.get_query( 917 "delete from quota_delegates :condition", 918 columns, values) 919 920 self.cursor.execute(query, values) 921 922 for store_user in delegates: 923 924 columns = ["quota", "store_user"] 925 values = [quota, store_user] 926 927 query, values = self.get_query( 928 "insert into quota_delegates (:columns) values (:values)", 929 columns, values) 930 931 self.cursor.execute(query, values) 932 933 return True 934 935 # Groups of users sharing quotas. 936 937 def get_groups(self, quota): 938 939 "Return the identity mappings for the given 'quota' as a dictionary." 940 941 columns = ["quota"] 942 values = [quota] 943 944 query, values = self.get_query( 945 "select store_user, user_group from user_groups :condition", 946 columns, values) 947 948 self.cursor.execute(query, values) 949 return dict(self.cursor.fetchall()) 950 951 def set_groups(self, quota, groups): 952 953 "For the given 'quota', set 'groups' mapping users to groups." 954 955 columns = ["quota"] 956 values = [quota] 957 958 query, values = self.get_query( 959 "delete from user_groups :condition", 960 columns, values) 961 962 self.cursor.execute(query, values) 963 964 for store_user, user_group in groups.items(): 965 966 columns = ["quota", "store_user", "user_group"] 967 values = [quota, store_user, user_group] 968 969 query, values = self.get_query( 970 "insert into user_groups (:columns) values (:values)", 971 columns, values) 972 973 self.cursor.execute(query, values) 974 975 return True 976 977 def get_limits(self, quota): 978 979 """ 980 Return the limits for the 'quota' as a dictionary mapping identities or 981 groups to durations. 982 """ 983 984 columns = ["quota"] 985 values = [quota] 986 987 query, values = self.get_query( 988 "select user_group, quota_limit from quota_limits :condition", 989 columns, values) 990 991 self.cursor.execute(query, values) 992 return dict(self.cursor.fetchall()) 993 994 def set_limits(self, quota, limits): 995 996 """ 997 For the given 'quota', set the given 'limits' on resource usage mapping 998 groups to limits. 999 """ 1000 1001 columns = ["quota"] 1002 values = [quota] 1003 1004 query, values = self.get_query( 1005 "delete from quota_limits :condition", 1006 columns, values) 1007 1008 self.cursor.execute(query, values) 1009 1010 for user_group, limit in limits.items(): 1011 1012 columns = ["quota", "user_group", "quota_limit"] 1013 values = [quota, user_group, limit] 1014 1015 query, values = self.get_query( 1016 "insert into quota_limits (:columns) values (:values)", 1017 columns, values) 1018 1019 self.cursor.execute(query, values) 1020 1021 return True 1022 1023 # Journal entry methods. 1024 1025 def get_entries(self, quota, group, mutable=False): 1026 1027 """ 1028 Return a list of journal entries for the given 'quota' for the indicated 1029 'group'. 1030 """ 1031 1032 return self.get_freebusy_for_other(quota, group, mutable) 1033 1034 def set_entries(self, quota, group, entries): 1035 1036 """ 1037 For the given 'quota' and indicated 'group', set the list of journal 1038 'entries'. 1039 """ 1040 1041 return self.set_freebusy_for_other(quota, entries, group) 1042 1043 # Compatibility methods. 1044 1045 def get_freebusy_for_other(self, user, other, mutable=False): 1046 return DatabaseStore.get_freebusy_for_other(self, user, other, mutable, collection=FreeBusyGroupDatabaseCollection) 1047 1048 def set_freebusy_for_other(self, user, freebusy, other): 1049 return DatabaseStore.set_freebusy_for_other(self, user, freebusy, other, collection=FreeBusyGroupDatabaseCollection) 1050 1051 # vim: tabstop=4 expandtab shiftwidth=4