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