imip-agent

Annotated imiptools/stores/database/common.py

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