imip-agent

imiptools/stores/database/common.py

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