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