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." 835 836 columns = ["quota"] 837 values = [quota] 838 839 query, values = self.get_query( 840 "select distinct user_group from quota_freebusy :condition", 841 columns, values) 842 843 self.cursor.execute(query, values) 844 return [r[0] for r in self.cursor.fetchall()] 845 846 # Groups of users sharing quotas. 847 848 def get_groups(self, quota): 849 850 "Return the identity mappings for the given 'quota' as a dictionary." 851 852 columns = ["quota"] 853 values = [quota] 854 855 query, values = self.get_query( 856 "select store_user, user_group from user_groups :condition", 857 columns, values) 858 859 self.cursor.execute(query, values) 860 return dict(self.cursor.fetchall()) 861 862 def set_group(self, quota, store_user, user_group): 863 864 """ 865 For the given 'quota', set a mapping from 'store_user' to 'user_group'. 866 """ 867 868 columns = ["quota", "store_user"] 869 values = [quota, store_user] 870 setcolumns = ["user_group"] 871 setvalues = [user_group] 872 873 query, values = self.get_query( 874 "update user_groups :set :condition", 875 columns, values, setcolumns, setvalues) 876 877 self.cursor.execute(query, values) 878 879 if self.cursor.rowcount > 0: 880 return True 881 882 columns = ["quota", "store_user", "user_group"] 883 values = [quota, store_user, user_group] 884 885 query, values = self.get_query( 886 "insert into user_groups (:columns) values (:values)", 887 columns, values) 888 889 self.cursor.execute(query, values) 890 return True 891 892 def get_limits(self, quota): 893 894 """ 895 Return the limits for the 'quota' as a dictionary mapping identities or 896 groups to durations. 897 """ 898 899 columns = ["quota"] 900 values = [quota] 901 902 query, values = self.get_query( 903 "select user_group, quota_limit from quota_limits :condition", 904 columns, values) 905 906 self.cursor.execute(query, values) 907 return dict(self.cursor.fetchall()) 908 909 def set_limit(self, quota, group, limit): 910 911 """ 912 For the given 'quota', set for a user 'group' the given 'limit' on 913 resource usage. 914 """ 915 916 columns = ["quota", "user_group"] 917 values = [quota, group] 918 setcolumns = ["quota_limit"] 919 setvalues = [limit] 920 921 query, values = self.get_query( 922 "update quota_limits :set :condition", 923 columns, values, setcolumns, setvalues) 924 925 self.cursor.execute(query, values) 926 927 if self.cursor.rowcount > 0: 928 return True 929 930 columns = ["quota", "user_group", "quota_limit"] 931 values = [quota, group, limit] 932 933 query, values = self.get_query( 934 "insert into quota_limits (:columns) values (:values)", 935 columns, values) 936 937 self.cursor.execute(query, values) 938 return True 939 940 # Free/busy period access for users within quota groups. 941 942 def get_freebusy_users(self, quota): 943 944 """ 945 Return a list of users whose free/busy details are retained for the 946 given 'quota'. 947 """ 948 949 columns = ["quota"] 950 values = [quota] 951 952 query, values = self.get_query( 953 "select distinct store_user from user_freebusy :condition", 954 columns, values) 955 956 self.cursor.execute(query, values) 957 return [r[0] for r in self.cursor.fetchall()] 958 959 def get_freebusy(self, quota, user, mutable=False, cls=None): 960 961 "Get free/busy details for the given 'quota' and 'user'." 962 963 table = "user_freebusy" 964 cls = cls or FreeBusyDatabaseCollection 965 return cls(self.cursor, table, ["quota", "store_user"], [quota, user], mutable, self.paramstyle) 966 967 def set_freebusy(self, quota, user, freebusy, cls=None): 968 969 "For the given 'quota' and 'user', set 'freebusy' details." 970 971 table = "user_freebusy" 972 cls = cls or FreeBusyDatabaseCollection 973 974 if not isinstance(freebusy, cls) or freebusy.table_name != table: 975 fbc = cls(self.cursor, table, ["quota", "store_user"], [quota, user], True, self.paramstyle) 976 fbc += freebusy 977 978 return True 979 980 # Journal entry methods. 981 982 def get_entries(self, quota, group, mutable=False): 983 984 """ 985 Return a list of journal entries for the given 'quota' for the indicated 986 'group'. 987 """ 988 989 table = "quota_freebusy" 990 return FreeBusyGroupDatabaseCollection(self.cursor, table, ["quota", "user_group"], [quota, group], mutable, self.paramstyle) 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 table = "quota_freebusy" 1000 1001 if not isinstance(entries, FreeBusyGroupDatabaseCollection) or entries.table_name != table: 1002 fbc = FreeBusyGroupDatabaseCollection(self.cursor, table, ["quota", "user_group"], [quota, group], True, self.paramstyle) 1003 fbc += entries 1004 1005 return True 1006 1007 # vim: tabstop=4 expandtab shiftwidth=4