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