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): 443 444 "For the given 'user', get free/busy details for the 'other' user." 445 446 table = "freebusy_other" 447 return FreeBusyDatabaseCollection(self.cursor, table, ["store_user", "other"], [user, other], mutable, self.paramstyle) 448 449 def set_freebusy(self, user, freebusy, name=None, cls=None): 450 451 "For the given 'user', set 'freebusy' details." 452 453 table = name or "freebusy" 454 cls = cls or FreeBusyDatabaseCollection 455 456 if not isinstance(freebusy, cls) or freebusy.table_name != table: 457 fbc = cls(self.cursor, table, ["store_user"], [user], True, self.paramstyle) 458 fbc += freebusy 459 460 return True 461 462 def set_freebusy_for_other(self, user, freebusy, other): 463 464 "For the given 'user', set 'freebusy' details for the 'other' user." 465 466 table = "freebusy_other" 467 468 if not isinstance(freebusy, FreeBusyDatabaseCollection) or freebusy.table_name != table: 469 fbc = FreeBusyDatabaseCollection(self.cursor, table, ["store_user", "other"], [user, other], True, self.paramstyle) 470 fbc += freebusy 471 472 return True 473 474 def get_freebusy_others(self, user): 475 476 """ 477 For the given 'user', return a list of other users for whom free/busy 478 information is retained. 479 """ 480 481 columns = ["store_user"] 482 values = [user] 483 484 query, values = self.get_query( 485 "select distinct other from freebusy_other :condition", 486 columns, values) 487 488 self.cursor.execute(query, values) 489 return [r[0] for r in self.cursor.fetchall()] 490 491 # Tentative free/busy periods related to countering. 492 493 def get_freebusy_offers(self, user, mutable=False): 494 495 "Get free/busy offers for the given 'user'." 496 497 # Expire old offers and save the collection if modified. 498 499 now = format_datetime(to_timezone(datetime.utcnow(), "UTC")) 500 columns = ["store_user", "expires"] 501 values = [user, now] 502 503 query, values = self.get_query( 504 "delete from freebusy_offers :condition", 505 columns, values) 506 507 self.cursor.execute(query, values) 508 509 return self.get_freebusy(user, "freebusy_offers", mutable, FreeBusyOffersDatabaseCollection) 510 511 def set_freebusy_offers(self, user, freebusy): 512 513 "For the given 'user', set 'freebusy' offers." 514 515 return self.set_freebusy(user, freebusy, "freebusy_offers", cls=FreeBusyOffersDatabaseCollection) 516 517 # Requests and counter-proposals. 518 519 def get_requests(self, user): 520 521 "Get requests for the given 'user'." 522 523 columns = ["store_user"] 524 values = [user] 525 526 query, values = self.get_query( 527 "select object_uid, object_recurrenceid, request_type from requests :condition", 528 columns, values) 529 530 self.cursor.execute(query, values) 531 return self.cursor.fetchall() 532 533 def set_request(self, user, uid, recurrenceid=None, type=None): 534 535 """ 536 For the given 'user', set the queued 'uid' and 'recurrenceid', 537 indicating a request, along with any given 'type'. 538 """ 539 540 columns = ["store_user", "object_uid", "object_recurrenceid", "request_type"] 541 values = [user, uid, recurrenceid, type] 542 543 query, values = self.get_query( 544 "insert into requests (:columns) values (:values)", 545 columns, values) 546 547 self.cursor.execute(query, values) 548 return True 549 550 def queue_request(self, user, uid, recurrenceid=None, type=None): 551 552 """ 553 Queue a request for 'user' having the given 'uid'. If the optional 554 'recurrenceid' is specified, the entry refers to a specific instance 555 or occurrence of an event. The 'type' parameter can be used to indicate 556 a specific type of request. 557 """ 558 559 if recurrenceid: 560 columns = ["store_user", "object_uid", "object_recurrenceid"] 561 values = [user, uid, recurrenceid] 562 else: 563 columns = ["store_user", "object_uid"] 564 values = [user, uid] 565 566 setcolumns = ["request_type"] 567 setvalues = [type] 568 569 query, values = self.get_query( 570 "update requests :set :condition", 571 columns, values, setcolumns, setvalues) 572 573 self.cursor.execute(query, values) 574 575 if self.cursor.rowcount > 0: 576 return 577 578 self.set_request(user, uid, recurrenceid, type) 579 580 def dequeue_request(self, user, uid, recurrenceid=None): 581 582 """ 583 Dequeue all requests for 'user' having the given 'uid'. If the optional 584 'recurrenceid' is specified, all requests for that specific instance or 585 occurrence of an event are dequeued. 586 """ 587 588 if recurrenceid: 589 columns = ["store_user", "object_uid", "object_recurrenceid"] 590 values = [user, uid, recurrenceid] 591 else: 592 columns = ["store_user", "object_uid"] 593 values = [user, uid] 594 595 query, values = self.get_query( 596 "delete from requests :condition", 597 columns, values) 598 599 self.cursor.execute(query, values) 600 return True 601 602 def get_counters(self, user, uid, recurrenceid=None): 603 604 """ 605 For the given 'user', return a list of users from whom counter-proposals 606 have been received for the given 'uid' and optional 'recurrenceid'. 607 """ 608 609 table = self.get_event_table(recurrenceid, "counters") 610 611 if recurrenceid: 612 columns = ["store_user", "object_uid", "object_recurrenceid"] 613 values = [user, uid, recurrenceid] 614 else: 615 columns = ["store_user", "object_uid"] 616 values = [user, uid] 617 618 query, values = self.get_query( 619 "select other from %(table)s :condition" % { 620 "table" : table 621 }, 622 columns, values) 623 624 self.cursor.execute(query, values) 625 return [r[0] for r in self.cursor.fetchall()] 626 627 def get_counter(self, user, other, uid, recurrenceid=None): 628 629 """ 630 For the given 'user', return the counter-proposal from 'other' for the 631 given 'uid' and optional 'recurrenceid'. 632 """ 633 634 table = self.get_event_table(recurrenceid, "counters") 635 636 if recurrenceid: 637 columns = ["store_user", "other", "object_uid", "object_recurrenceid"] 638 values = [user, other, uid, recurrenceid] 639 else: 640 columns = ["store_user", "other", "object_uid"] 641 values = [user, other, uid] 642 643 query, values = self.get_query( 644 "select object_text from %(table)s :condition" % { 645 "table" : table 646 }, 647 columns, values) 648 649 self.cursor.execute(query, values) 650 result = self.cursor.fetchone() 651 return result and parse_string(result[0], "utf-8") 652 653 def set_counter(self, user, other, node, uid, recurrenceid=None): 654 655 """ 656 For the given 'user', store a counter-proposal received from 'other' the 657 given 'node' representing that proposal for the given 'uid' and 658 'recurrenceid'. 659 """ 660 661 table = self.get_event_table(recurrenceid, "counters") 662 663 if recurrenceid: 664 columns = ["store_user", "other", "object_uid", "object_recurrenceid", "object_text"] 665 values = [user, other, uid, recurrenceid, to_string(node, "utf-8")] 666 else: 667 columns = ["store_user", "other", "object_uid", "object_text"] 668 values = [user, other, uid, to_string(node, "utf-8")] 669 670 query, values = self.get_query( 671 "insert into %(table)s (:columns) values (:values)" % { 672 "table" : table 673 }, 674 columns, values) 675 676 self.cursor.execute(query, values) 677 return True 678 679 def remove_counters(self, user, uid, recurrenceid=None): 680 681 """ 682 For the given 'user', remove all counter-proposals associated with the 683 given 'uid' and 'recurrenceid'. 684 """ 685 686 table = self.get_event_table(recurrenceid, "counters") 687 688 if recurrenceid: 689 columns = ["store_user", "object_uid", "object_recurrenceid"] 690 values = [user, uid, recurrenceid] 691 else: 692 columns = ["store_user", "object_uid"] 693 values = [user, uid] 694 695 query, values = self.get_query( 696 "delete from %(table)s :condition" % { 697 "table" : table 698 }, 699 columns, values) 700 701 self.cursor.execute(query, values) 702 return True 703 704 def remove_counter(self, user, other, uid, recurrenceid=None): 705 706 """ 707 For the given 'user', remove any counter-proposal from 'other' 708 associated with the given 'uid' and 'recurrenceid'. 709 """ 710 711 table = self.get_event_table(recurrenceid, "counters") 712 713 if recurrenceid: 714 columns = ["store_user", "other", "object_uid", "object_recurrenceid"] 715 values = [user, other, uid, recurrenceid] 716 else: 717 columns = ["store_user", "other", "object_uid"] 718 values = [user, other, uid] 719 720 query, values = self.get_query( 721 "delete from %(table)s :condition" % { 722 "table" : table 723 }, 724 columns, values) 725 726 self.cursor.execute(query, values) 727 return True 728 729 # Event cancellation. 730 731 def cancel_event(self, user, uid, recurrenceid=None): 732 733 """ 734 Cancel an event for 'user' having the given 'uid'. If the optional 735 'recurrenceid' is specified, a specific instance or occurrence of an 736 event is cancelled. 737 """ 738 739 table = self.get_event_table(recurrenceid) 740 741 if recurrenceid: 742 columns = ["store_user", "object_uid", "object_recurrenceid"] 743 values = [user, uid, recurrenceid] 744 else: 745 columns = ["store_user", "object_uid"] 746 values = [user, uid] 747 748 setcolumns = ["status"] 749 setvalues = ["cancelled"] 750 751 query, values = self.get_query( 752 "update %(table)s :set :condition" % { 753 "table" : table 754 }, 755 columns, values, setcolumns, setvalues) 756 757 self.cursor.execute(query, values) 758 return True 759 760 def uncancel_event(self, user, uid, recurrenceid=None): 761 762 """ 763 Uncancel an event for 'user' having the given 'uid'. If the optional 764 'recurrenceid' is specified, a specific instance or occurrence of an 765 event is uncancelled. 766 """ 767 768 table = self.get_event_table(recurrenceid) 769 770 if recurrenceid: 771 columns = ["store_user", "object_uid", "object_recurrenceid"] 772 values = [user, uid, recurrenceid] 773 else: 774 columns = ["store_user", "object_uid"] 775 values = [user, uid] 776 777 setcolumns = ["status"] 778 setvalues = ["active"] 779 780 query, values = self.get_query( 781 "update %(table)s :set :condition" % { 782 "table" : table 783 }, 784 columns, values, setcolumns, setvalues) 785 786 self.cursor.execute(query, values) 787 return True 788 789 def remove_cancellation(self, user, uid, recurrenceid=None): 790 791 """ 792 Remove a cancellation for 'user' for the event having the given 'uid'. 793 If the optional 'recurrenceid' is specified, a specific instance or 794 occurrence of an event is affected. 795 """ 796 797 table = self.get_event_table(recurrenceid) 798 799 if recurrenceid: 800 columns = ["store_user", "object_uid", "object_recurrenceid", "status"] 801 values = [user, uid, recurrenceid, "cancelled"] 802 else: 803 columns = ["store_user", "object_uid", "status"] 804 values = [user, uid, "cancelled"] 805 806 query, values = self.get_query( 807 "delete from %(table)s :condition" % { 808 "table" : table 809 }, 810 columns, values) 811 812 self.cursor.execute(query, values) 813 return True 814 815 class DatabaseJournal(DatabaseStoreBase, JournalBase): 816 817 "A journal system to support quotas." 818 819 # Quota and user identity/group discovery. 820 821 def get_quotas(self): 822 823 "Return a list of quotas." 824 825 query = "select distinct quota from (" \ 826 "select quota from quota_freebusy " \ 827 "union all select quota from quota_limits" \ 828 ") as quotas" 829 self.cursor.execute(query) 830 return [r[0] for r in self.cursor.fetchall()] 831 832 def get_quota_users(self, quota): 833 834 "Return a list of quota users for the 'quota'." 835 836 columns = ["quota"] 837 values = [quota] 838 839 query, values = self.get_query( 840 "select distinct user_group from (" \ 841 "select user_group from quota_freebusy :condition " \ 842 "union all select user_group from quota_delegates :condition" \ 843 ") as users", 844 columns, values) 845 846 self.cursor.execute(query, values) 847 return [r[0] for r in self.cursor.fetchall()] 848 849 # Delegate information for the quota. 850 851 def get_delegates(self, quota): 852 853 "Return a list of delegates for 'quota'." 854 855 columns = ["quota"] 856 values = [quota] 857 858 query, values = self.get_query( 859 "select distinct store_user from quota_delegates :condition", 860 columns, values) 861 862 self.cursor.execute(query, values) 863 return [r[0] for r in self.cursor.fetchall()] 864 865 def set_delegates(self, quota, delegates): 866 867 "For the given 'quota', set the list of 'delegates'." 868 869 columns = ["quota"] 870 values = [quota] 871 872 query, values = self.get_query( 873 "delete from quota_delegates :condition", 874 columns, values) 875 876 self.cursor.execute(query, values) 877 878 for store_user in delegates: 879 880 columns = ["quota", "store_user"] 881 values = [quota, store_user] 882 883 query, values = self.get_query( 884 "insert into quota_delegates (:columns) values (:values)", 885 columns, values) 886 887 self.cursor.execute(query, values) 888 return True 889 890 # Groups of users sharing quotas. 891 892 def get_groups(self, quota): 893 894 "Return the identity mappings for the given 'quota' as a dictionary." 895 896 columns = ["quota"] 897 values = [quota] 898 899 query, values = self.get_query( 900 "select store_user, user_group from user_groups :condition", 901 columns, values) 902 903 self.cursor.execute(query, values) 904 return dict(self.cursor.fetchall()) 905 906 def set_groups(self, quota, groups): 907 908 "For the given 'quota', set 'groups' mapping users to groups." 909 910 columns = ["quota"] 911 values = [quota] 912 913 query, values = self.get_query( 914 "delete from user_groups :condition", 915 columns, values) 916 917 self.cursor.execute(query, values) 918 919 for store_user, user_group in groups.items(): 920 921 columns = ["quota", "store_user", "user_group"] 922 values = [quota, store_user, user_group] 923 924 query, values = self.get_query( 925 "insert into user_groups (:columns) values (:values)", 926 columns, values) 927 928 self.cursor.execute(query, values) 929 return True 930 931 def get_limits(self, quota): 932 933 """ 934 Return the limits for the 'quota' as a dictionary mapping identities or 935 groups to durations. 936 """ 937 938 columns = ["quota"] 939 values = [quota] 940 941 query, values = self.get_query( 942 "select user_group, quota_limit from quota_limits :condition", 943 columns, values) 944 945 self.cursor.execute(query, values) 946 return dict(self.cursor.fetchall()) 947 948 def set_limits(self, quota, limits): 949 950 """ 951 For the given 'quota', set the given 'limits' on resource usage mapping 952 groups to limits. 953 """ 954 955 columns = ["quota"] 956 values = [quota] 957 958 query, values = self.get_query( 959 "delete from quota_limits :condition", 960 columns, values) 961 962 self.cursor.execute(query, values) 963 964 for user_group, limit in limits.items(): 965 966 columns = ["quota", "user_group", "quota_limit"] 967 values = [quota, user_group, limit] 968 969 query, values = self.get_query( 970 "insert into quota_limits (:columns) values (:values)", 971 columns, values) 972 973 self.cursor.execute(query, values) 974 return True 975 976 # Free/busy period access for users within quota groups. 977 978 def get_freebusy_users(self, quota): 979 980 """ 981 Return a list of users whose free/busy details are retained for the 982 given 'quota'. 983 """ 984 985 columns = ["quota"] 986 values = [quota] 987 988 query, values = self.get_query( 989 "select distinct store_user from user_freebusy :condition", 990 columns, values) 991 992 self.cursor.execute(query, values) 993 return [r[0] for r in self.cursor.fetchall()] 994 995 def get_freebusy(self, quota, user, mutable=False, cls=None): 996 997 "Get free/busy details for the given 'quota' and 'user'." 998 999 table = "user_freebusy" 1000 cls = cls or FreeBusyDatabaseCollection 1001 return cls(self.cursor, table, ["quota", "store_user"], [quota, user], mutable, self.paramstyle) 1002 1003 def set_freebusy(self, quota, user, freebusy, cls=None): 1004 1005 "For the given 'quota' and 'user', set 'freebusy' details." 1006 1007 table = "user_freebusy" 1008 cls = cls or FreeBusyDatabaseCollection 1009 1010 if not isinstance(freebusy, cls) or freebusy.table_name != table: 1011 fbc = cls(self.cursor, table, ["quota", "store_user"], [quota, user], True, self.paramstyle) 1012 fbc += freebusy 1013 1014 return True 1015 1016 # Journal entry methods. 1017 1018 def get_entries(self, quota, group, mutable=False): 1019 1020 """ 1021 Return a list of journal entries for the given 'quota' for the indicated 1022 'group'. 1023 """ 1024 1025 table = "quota_freebusy" 1026 return FreeBusyGroupDatabaseCollection(self.cursor, table, ["quota", "user_group"], [quota, group], mutable, self.paramstyle) 1027 1028 def set_entries(self, quota, group, entries): 1029 1030 """ 1031 For the given 'quota' and indicated 'group', set the list of journal 1032 'entries'. 1033 """ 1034 1035 table = "quota_freebusy" 1036 1037 if not isinstance(entries, FreeBusyGroupDatabaseCollection) or entries.table_name != table: 1038 fbc = FreeBusyGroupDatabaseCollection(self.cursor, table, ["quota", "user_group"], [quota, group], True, self.paramstyle) 1039 fbc += entries 1040 1041 return True 1042 1043 # vim: tabstop=4 expandtab shiftwidth=4