#!/usr/bin/env python

"""
A database store of calendar data.

Copyright (C) 2014, 2015, 2016 Paul Boddie <paul@boddie.org.uk>

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation; either version 3 of the License, or (at your option) any later
version.

This program is distributed in the hope that it will be useful, but WITHOUT
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
details.

You should have received a copy of the GNU General Public License along with
this program.  If not, see <http://www.gnu.org/licenses/>.
"""

from imiptools.stores.common import StoreBase, JournalBase

from datetime import datetime
from imiptools.data import parse_string, to_string
from imiptools.dates import format_datetime, get_datetime, to_timezone
from imiptools.period import FreeBusyDatabaseCollection, \
                             FreeBusyGroupDatabaseCollection, \
                             FreeBusyOffersDatabaseCollection
from imiptools.sql import DatabaseOperations

class DatabaseStoreBase(DatabaseOperations):

    "A database store supporting user-specific locking."

    def __init__(self, connection, paramstyle=None):
        DatabaseOperations.__init__(self, paramstyle=paramstyle)
        self.connection = connection
        self.cursor = connection.cursor()

    def acquire_lock(self, user, timeout=None):
        pass

    def release_lock(self, user):
        pass

    def with_tables(self, query):

        "Parameterise tables in the given 'query' for common operations."

        return query % {
            "objects" : self.objects_table,
            "recurrences" : self.recurrences_table,
            "freebusy_other" : self.freebusy_other_table,
            "freebusy_providers" : self.freebusy_providers_table,
            "freebusy_provider_datetimes" : self.freebusy_provider_datetimes_table,
            }

class DatabaseStore(DatabaseStoreBase, StoreBase):

    "A database store of tabular free/busy data and objects."

    objects_table = "objects"
    recurrences_table = "recurrences"
    freebusy_other_table = "freebusy_other"
    freebusy_providers_table = "freebusy_providers"
    freebusy_provider_datetimes_table = "freebusy_provider_datetimes"

    # User discovery.

    def get_users(self):

        "Return a list of users."

        query = self.with_tables(
                "select distinct store_user from (" \
                "select store_user from freebusy " \
                "union all select store_user from %(objects)s " \
                "union all select store_user from %(recurrences)s" \
                ") as users")
        self.cursor.execute(query)
        return [r[0] for r in self.cursor.fetchall()]

    # Event and event metadata access.

    def get_all_events(self, user, dirname=None):

        """
        Return a set of (uid, recurrenceid) tuples for all events. Unless
        'dirname' is specified, only active events are returned; otherwise,
        events from the given 'dirname' are returned.
        """

        columns, values = self.get_event_table_filters(dirname)

        columns += ["store_user"]
        values += [user]

        query, values = self.get_query(self.with_tables(
            "select object_uid, null as object_recurrenceid from %(objects)s :condition "
            "union all "
            "select object_uid, object_recurrenceid from %(recurrences)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        return self.cursor.fetchall()

    def get_events(self, user, dirname=None):

        "Return a list of event identifiers."

        columns, values = self.get_event_table_filters(dirname)

        columns += ["store_user"]
        values += [user]

        query, values = self.get_query(self.with_tables(
            "select object_uid from %(objects)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        return [r[0] for r in self.cursor.fetchall()]

    def get_cancelled_events(self, user):

        "Return a list of event identifiers for cancelled events."

        return self.get_events(user, "cancellations")

    def get_event(self, user, uid, recurrenceid=None, dirname=None):

        """
        Get the event for the given 'user' with the given 'uid'. If
        the optional 'recurrenceid' is specified, a specific instance or
        occurrence of an event is returned.
        """

        table = self.get_event_table(recurrenceid, dirname)
        columns, values = self.get_event_table_filters(dirname)

        if recurrenceid:
            columns += ["store_user", "object_uid", "object_recurrenceid"]
            values += [user, uid, recurrenceid]
        else:
            columns += ["store_user", "object_uid"]
            values += [user, uid]

        query, values = self.get_query(
            "select object_text from %(table)s :condition" % {
                "table" : table
                },
            columns, values)

        self.cursor.execute(query, values)
        result = self.cursor.fetchone()
        return result and parse_string(result[0], "utf-8")

    def get_complete_event(self, user, uid):

        "Get the event for the given 'user' with the given 'uid'."

        columns = ["store_user", "object_uid"]
        values = [user, uid]

        query, values = self.get_query(self.with_tables(
            "select object_text from %(objects)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        result = self.cursor.fetchone()
        return result and parse_string(result[0], "utf-8")

    def set_complete_event(self, user, uid, node):

        "Set an event for 'user' having the given 'uid' and 'node'."

        columns = ["store_user", "object_uid"]
        values = [user, uid]
        setcolumns = ["object_text", "status"]
        setvalues = [to_string(node, "utf-8"), "active"]

        query, values = self.get_query(self.with_tables(
            "update %(objects)s :set :condition"),
            columns, values, setcolumns, setvalues)

        self.cursor.execute(query, values)

        if self.cursor.rowcount > 0 or self.get_complete_event(user, uid):
            return True

        columns = ["store_user", "object_uid", "object_text", "status"]
        values = [user, uid, to_string(node, "utf-8"), "active"]

        query, values = self.get_query(self.with_tables(
            "insert into %(objects)s (:columns) values (:values)"),
            columns, values)

        self.cursor.execute(query, values)
        return True

    def remove_parent_event(self, user, uid):

        "Remove the parent event for 'user' having the given 'uid'."

        columns = ["store_user", "object_uid"]
        values = [user, uid]

        query, values = self.get_query(self.with_tables(
            "delete from %(objects)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        return self.cursor.rowcount > 0

    def get_active_recurrences(self, user, uid):

        """
        Get additional event instances for an event of the given 'user' with the
        indicated 'uid'. Cancelled recurrences are not returned.
        """

        columns = ["store_user", "object_uid", "status"]
        values = [user, uid, "active"]

        query, values = self.get_query(self.with_tables(
            "select object_recurrenceid from %(recurrences)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        return [t[0] for t in self.cursor.fetchall() or []]

    def get_cancelled_recurrences(self, user, uid):

        """
        Get additional event instances for an event of the given 'user' with the
        indicated 'uid'. Only cancelled recurrences are returned.
        """

        columns = ["store_user", "object_uid", "status"]
        values = [user, uid, "cancelled"]

        query, values = self.get_query(self.with_tables(
            "select object_recurrenceid from %(recurrences)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        return [t[0] for t in self.cursor.fetchall() or []]

    def get_recurrence(self, user, uid, recurrenceid):

        """
        For the event of the given 'user' with the given 'uid', return the
        specific recurrence indicated by the 'recurrenceid'.
        """

        columns = ["store_user", "object_uid", "object_recurrenceid"]
        values = [user, uid, recurrenceid]

        query, values = self.get_query(self.with_tables(
            "select object_text from %(recurrences)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        result = self.cursor.fetchone()
        return result and parse_string(result[0], "utf-8")

    def set_recurrence(self, user, uid, recurrenceid, node):

        "Set an event for 'user' having the given 'uid' and 'node'."

        columns = ["store_user", "object_uid", "object_recurrenceid"]
        values = [user, uid, recurrenceid]
        setcolumns = ["object_text", "status"]
        setvalues = [to_string(node, "utf-8"), "active"]

        query, values = self.get_query(self.with_tables(
            "update %(recurrences)s :set :condition"),
            columns, values, setcolumns, setvalues)

        self.cursor.execute(query, values)

        if self.cursor.rowcount > 0 or self.get_recurrence(user, uid, recurrenceid):
            return True

        columns = ["store_user", "object_uid", "object_recurrenceid", "object_text", "status"]
        values = [user, uid, recurrenceid, to_string(node, "utf-8"), "active"]

        query, values = self.get_query(self.with_tables(
            "insert into %(recurrences)s (:columns) values (:values)"),
            columns, values)

        self.cursor.execute(query, values)
        return True

    def remove_recurrence(self, user, uid, recurrenceid):

        """
        Remove a special recurrence from an event stored by 'user' having the
        given 'uid' and 'recurrenceid'.
        """

        columns = ["store_user", "object_uid", "object_recurrenceid"]
        values = [user, uid, recurrenceid]

        query, values = self.get_query(self.with_tables(
            "delete from %(recurrences)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        return True

    def remove_recurrences(self, user, uid):

        """
        Remove all recurrences for an event stored by 'user' having the given
        'uid'.
        """

        columns = ["store_user", "object_uid"]
        values = [user, uid]

        query, values = self.get_query(self.with_tables(
            "delete from %(recurrences)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        return True

    # Event table computation.

    def get_event_table(self, recurrenceid=None, dirname=None):

        "Get the table providing events for any specified 'dirname'."

        if recurrenceid:
            return self.get_recurrence_table(dirname)
        else:
            return self.get_complete_event_table(dirname)

    def get_event_table_filters(self, dirname=None):

        "Get filter details for any specified 'dirname'."

        if dirname == "cancellations":
            return ["status"], ["cancelled"]
        else:
            return ["status"], ["active"]

    def get_complete_event_table(self, dirname=None):

        "Get the table providing events for any specified 'dirname'."

        if dirname == "counters":
            return "countered_%s" % self.objects_table
        else:
            return self.objects_table

    def get_recurrence_table(self, dirname=None):

        "Get the table providing recurrences for any specified 'dirname'."

        if dirname == "counters":
            return "countered_%s" % self.recurrences_table
        else:
            return self.recurrences_table

    # Free/busy period providers, upon extension of the free/busy records.

    def _get_freebusy_providers(self, user):

        """
        Return the free/busy providers for the given 'user'.

        This function returns any stored datetime and a list of providers as a
        2-tuple. Each provider is itself a (uid, recurrenceid) tuple.
        """

        columns = ["store_user"]
        values = [user]

        query, values = self.get_query(self.with_tables(
            "select object_uid, object_recurrenceid from %(freebusy_providers)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        providers = self.cursor.fetchall()

        columns = ["store_user"]
        values = [user]

        query, values = self.get_query(self.with_tables(
            "select start from %(freebusy_provider_datetimes)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        result = self.cursor.fetchone()
        dt_string = result and result[0]

        return dt_string, providers

    def _set_freebusy_providers(self, user, dt_string, t):

        "Set the given provider timestamp 'dt_string' and table 't'."

        # NOTE: Locking?

        columns = ["store_user"]
        values = [user]

        query, values = self.get_query(self.with_tables(
            "delete from %(freebusy_providers)s :condition"),
            columns, values)

        self.cursor.execute(query, values)

        columns = ["store_user", "object_uid", "object_recurrenceid"]

        for uid, recurrenceid in t:
            values = [user, uid, recurrenceid]

            query, values = self.get_query(self.with_tables(
                "insert into %(freebusy_providers)s (:columns) values (:values)"),
                columns, values)

            self.cursor.execute(query, values)

        columns = ["store_user"]
        values = [user]
        setcolumns = ["start"]
        setvalues = [dt_string]

        query, values = self.get_query(self.with_tables(
            "update %(freebusy_provider_datetimes)s :set :condition"),
            columns, values, setcolumns, setvalues)

        self.cursor.execute(query, values)

        if self.cursor.rowcount > 0:
            return True

        columns = ["store_user", "start"]
        values = [user, dt_string]

        query, values = self.get_query(self.with_tables(
            "insert into %(freebusy_provider_datetimes)s (:columns) values (:values)"),
            columns, values)

        self.cursor.execute(query, values)
        return True

    # Free/busy period access.

    def get_freebusy(self, user, name=None, mutable=False, cls=None):

        "Get free/busy details for the given 'user'."

        table = name or "freebusy"
        cls = cls or FreeBusyDatabaseCollection
        return cls(self.cursor, table, ["store_user"], [user], mutable, self.paramstyle)

    def get_freebusy_for_other(self, user, other, mutable=False, cls=None):

        "For the given 'user', get free/busy details for the 'other' user."

        cls = cls or FreeBusyDatabaseCollection
        return cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], mutable, self.paramstyle)

    def set_freebusy(self, user, freebusy, name=None, cls=None):

        "For the given 'user', set 'freebusy' details."

        table = name or "freebusy"
        cls = cls or FreeBusyDatabaseCollection

        if not isinstance(freebusy, cls) or freebusy.table_name != table:
            fbc = cls(self.cursor, table, ["store_user"], [user], True, self.paramstyle)
            fbc += freebusy

        return True

    def set_freebusy_for_other(self, user, freebusy, other, cls=None):

        "For the given 'user', set 'freebusy' details for the 'other' user."

        cls = cls or FreeBusyDatabaseCollection

        if not isinstance(freebusy, cls) or freebusy.table_name != self.freebusy_other_table:
            fbc = cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], True, self.paramstyle)
            fbc += freebusy

        return True

    def get_freebusy_others(self, user):

        """
        For the given 'user', return a list of other users for whom free/busy
        information is retained.
        """

        columns = ["store_user"]
        values = [user]

        query, values = self.get_query(self.with_tables(
            "select distinct other from %(freebusy_other)s :condition"),
            columns, values)

        self.cursor.execute(query, values)
        return [r[0] for r in self.cursor.fetchall()]

    # Tentative free/busy periods related to countering.

    def get_freebusy_offers(self, user, mutable=False):

        "Get free/busy offers for the given 'user'."

        # Expire old offers and save the collection if modified.

        now = format_datetime(to_timezone(datetime.utcnow(), "UTC"))
        columns = ["store_user", "expires"]
        values = [user, now]

        query, values = self.get_query(
            "delete from freebusy_offers :condition",
            columns, values)

        self.cursor.execute(query, values)

        return self.get_freebusy(user, "freebusy_offers", mutable, FreeBusyOffersDatabaseCollection)

    def set_freebusy_offers(self, user, freebusy):

        "For the given 'user', set 'freebusy' offers."

        return self.set_freebusy(user, freebusy, "freebusy_offers", cls=FreeBusyOffersDatabaseCollection)

    # Requests and counter-proposals.

    def get_requests(self, user):

        "Get requests for the given 'user'."

        columns = ["store_user"]
        values = [user]

        query, values = self.get_query(
            "select object_uid, object_recurrenceid, request_type from requests :condition",
            columns, values)

        self.cursor.execute(query, values)
        return self.cursor.fetchall()

    def set_request(self, user, uid, recurrenceid=None, type=None):

        """
        For the given 'user', set the queued 'uid' and 'recurrenceid',
        indicating a request, along with any given 'type'.
        """

        columns = ["store_user", "object_uid", "object_recurrenceid", "request_type"]
        values = [user, uid, recurrenceid, type]

        query, values = self.get_query(
            "insert into requests (:columns) values (:values)",
            columns, values)

        self.cursor.execute(query, values)
        return True

    def queue_request(self, user, uid, recurrenceid=None, type=None):

        """
        Queue a request for 'user' having the given 'uid'. If the optional
        'recurrenceid' is specified, the entry refers to a specific instance
        or occurrence of an event. The 'type' parameter can be used to indicate
        a specific type of request.
        """

        if recurrenceid:
            columns = ["store_user", "object_uid", "object_recurrenceid"]
            values = [user, uid, recurrenceid]
        else:
            columns = ["store_user", "object_uid"]
            values = [user, uid]

        setcolumns = ["request_type"]
        setvalues = [type]

        query, values = self.get_query(
            "update requests :set :condition",
            columns, values, setcolumns, setvalues)

        self.cursor.execute(query, values)

        if self.cursor.rowcount > 0:
            return

        self.set_request(user, uid, recurrenceid, type)

    def dequeue_request(self, user, uid, recurrenceid=None):

        """
        Dequeue all requests for 'user' having the given 'uid'. If the optional
        'recurrenceid' is specified, all requests for that specific instance or
        occurrence of an event are dequeued.
        """

        if recurrenceid:
            columns = ["store_user", "object_uid", "object_recurrenceid"]
            values = [user, uid, recurrenceid]
        else:
            columns = ["store_user", "object_uid"]
            values = [user, uid]

        query, values = self.get_query(
            "delete from requests :condition",
            columns, values)

        self.cursor.execute(query, values)
        return True

    def get_counters(self, user, uid, recurrenceid=None):

        """
        For the given 'user', return a list of users from whom counter-proposals
        have been received for the given 'uid' and optional 'recurrenceid'.
        """

        table = self.get_event_table(recurrenceid, "counters")

        if recurrenceid:
            columns = ["store_user", "object_uid", "object_recurrenceid"]
            values = [user, uid, recurrenceid]
        else:
            columns = ["store_user", "object_uid"]
            values = [user, uid]

        query, values = self.get_query(
            "select other from %(table)s :condition" % {
                "table" : table
                },
            columns, values)

        self.cursor.execute(query, values)
        return [r[0] for r in self.cursor.fetchall()]

    def get_counter(self, user, other, uid, recurrenceid=None):

        """
        For the given 'user', return the counter-proposal from 'other' for the
        given 'uid' and optional 'recurrenceid'.
        """

        table = self.get_event_table(recurrenceid, "counters")

        if recurrenceid:
            columns = ["store_user", "other", "object_uid", "object_recurrenceid"]
            values = [user, other, uid, recurrenceid]
        else:
            columns = ["store_user", "other", "object_uid"]
            values = [user, other, uid]

        query, values = self.get_query(
            "select object_text from %(table)s :condition" % {
                "table" : table
                },
            columns, values)

        self.cursor.execute(query, values)
        result = self.cursor.fetchone()
        return result and parse_string(result[0], "utf-8")

    def set_counter(self, user, other, node, uid, recurrenceid=None):

        """
        For the given 'user', store a counter-proposal received from 'other' the
        given 'node' representing that proposal for the given 'uid' and
        'recurrenceid'.
        """

        table = self.get_event_table(recurrenceid, "counters")

        if recurrenceid:
            columns = ["store_user", "other", "object_uid", "object_recurrenceid", "object_text"]
            values = [user, other, uid, recurrenceid, to_string(node, "utf-8")]
        else:
            columns = ["store_user", "other", "object_uid", "object_text"]
            values = [user, other, uid, to_string(node, "utf-8")]

        query, values = self.get_query(
            "insert into %(table)s (:columns) values (:values)" % {
                "table" : table
                },
            columns, values)

        self.cursor.execute(query, values)
        return True

    def remove_counters(self, user, uid, recurrenceid=None):

        """
        For the given 'user', remove all counter-proposals associated with the
        given 'uid' and 'recurrenceid'.
        """

        table = self.get_event_table(recurrenceid, "counters")

        if recurrenceid:
            columns = ["store_user", "object_uid", "object_recurrenceid"]
            values = [user, uid, recurrenceid]
        else:
            columns = ["store_user", "object_uid"]
            values = [user, uid]

        query, values = self.get_query(
            "delete from %(table)s :condition" % {
                "table" : table
                },
            columns, values)

        self.cursor.execute(query, values)
        return True

    def remove_counter(self, user, other, uid, recurrenceid=None):

        """
        For the given 'user', remove any counter-proposal from 'other'
        associated with the given 'uid' and 'recurrenceid'.
        """

        table = self.get_event_table(recurrenceid, "counters")

        if recurrenceid:
            columns = ["store_user", "other", "object_uid", "object_recurrenceid"]
            values = [user, other, uid, recurrenceid]
        else:
            columns = ["store_user", "other", "object_uid"]
            values = [user, other, uid]

        query, values = self.get_query(
            "delete from %(table)s :condition" % {
                "table" : table
                },
            columns, values)

        self.cursor.execute(query, values)
        return True

    # Event cancellation.

    def cancel_event(self, user, uid, recurrenceid=None):

        """
        Cancel an event for 'user' having the given 'uid'. If the optional
        'recurrenceid' is specified, a specific instance or occurrence of an
        event is cancelled.
        """

        table = self.get_event_table(recurrenceid)

        if recurrenceid:
            columns = ["store_user", "object_uid", "object_recurrenceid"]
            values = [user, uid, recurrenceid]
        else:
            columns = ["store_user", "object_uid"]
            values = [user, uid]

        setcolumns = ["status"]
        setvalues = ["cancelled"]

        query, values = self.get_query(
            "update %(table)s :set :condition" % {
                "table" : table
                },
            columns, values, setcolumns, setvalues)

        self.cursor.execute(query, values)
        return True

    def uncancel_event(self, user, uid, recurrenceid=None):

        """
        Uncancel an event for 'user' having the given 'uid'. If the optional
        'recurrenceid' is specified, a specific instance or occurrence of an
        event is uncancelled.
        """

        table = self.get_event_table(recurrenceid)

        if recurrenceid:
            columns = ["store_user", "object_uid", "object_recurrenceid"]
            values = [user, uid, recurrenceid]
        else:
            columns = ["store_user", "object_uid"]
            values = [user, uid]

        setcolumns = ["status"]
        setvalues = ["active"]

        query, values = self.get_query(
            "update %(table)s :set :condition" % {
                "table" : table
                },
            columns, values, setcolumns, setvalues)

        self.cursor.execute(query, values)
        return True

    def remove_cancellation(self, user, uid, recurrenceid=None):

        """
        Remove a cancellation for 'user' for the event having the given 'uid'.
        If the optional 'recurrenceid' is specified, a specific instance or
        occurrence of an event is affected.
        """

        table = self.get_event_table(recurrenceid)

        if recurrenceid:
            columns = ["store_user", "object_uid", "object_recurrenceid", "status"]
            values = [user, uid, recurrenceid, "cancelled"]
        else:
            columns = ["store_user", "object_uid", "status"]
            values = [user, uid, "cancelled"]

        query, values = self.get_query(
            "delete from %(table)s :condition" % {
                "table" : table
                },
            columns, values)

        self.cursor.execute(query, values)
        return True

class DatabaseJournal(DatabaseStore, JournalBase):

    "A journal system to support quotas."

    objects_table = "journal_objects"
    recurrences_table = "journal_recurrences"
    freebusy_other_table = "journal_freebusy_other"
    freebusy_providers_table = "journal_freebusy_providers"
    freebusy_provider_datetimes_table = "journal_freebusy_provider_datetimes"

    # Quota and user identity/group discovery.

    def get_quotas(self):

        "Return a list of quotas."

        query = self.with_tables("select distinct quota from (" \
                "select distinct store_user as quota from %(freebusy_other)s " \
                "union all select quota from quota_limits" \
                ") as quotas")
        self.cursor.execute(query)
        return [r[0] for r in self.cursor.fetchall()]

    def get_quota_users(self, quota):

        "Return a list of quota users for the 'quota'."

        columns = ["quota"]
        values = [quota]

        query, values = self.get_query(self.with_tables(
            "select distinct user_group from (" \
            "select distinct other as user_group from %(freebusy_other)s :condition " \
            "union all select user_group from quota_delegates :condition" \
            ") as users"),
            columns, values)

        self.cursor.execute(query, values)
        return [r[0] for r in self.cursor.fetchall()]

    # Delegate information for the quota.

    def get_delegates(self, quota):

        "Return a list of delegates for 'quota'."

        columns = ["quota"]
        values = [quota]

        query, values = self.get_query(
            "select distinct store_user from quota_delegates :condition",
            columns, values)

        self.cursor.execute(query, values)
        return [r[0] for r in self.cursor.fetchall()]

    def set_delegates(self, quota, delegates):

        "For the given 'quota', set the list of 'delegates'."

        columns = ["quota"]
        values = [quota]

        query, values = self.get_query(
            "delete from quota_delegates :condition",
            columns, values)

        self.cursor.execute(query, values)

        for store_user in delegates:

            columns = ["quota", "store_user"]
            values = [quota, store_user]

            query, values = self.get_query(
                "insert into quota_delegates (:columns) values (:values)",
                columns, values)

            self.cursor.execute(query, values)

        return True

    # Groups of users sharing quotas.

    def get_groups(self, quota):

        "Return the identity mappings for the given 'quota' as a dictionary."

        columns = ["quota"]
        values = [quota]

        query, values = self.get_query(
            "select store_user, user_group from user_groups :condition",
            columns, values)

        self.cursor.execute(query, values)
        return dict(self.cursor.fetchall())

    def set_groups(self, quota, groups):

        "For the given 'quota', set 'groups' mapping users to groups."

        columns = ["quota"]
        values = [quota]

        query, values = self.get_query(
            "delete from user_groups :condition",
            columns, values)

        self.cursor.execute(query, values)

        for store_user, user_group in groups.items():

            columns = ["quota", "store_user", "user_group"]
            values = [quota, store_user, user_group]

            query, values = self.get_query(
                "insert into user_groups (:columns) values (:values)",
                columns, values)

            self.cursor.execute(query, values)

        return True

    def get_limits(self, quota):

        """
        Return the limits for the 'quota' as a dictionary mapping identities or
        groups to durations.
        """

        columns = ["quota"]
        values = [quota]

        query, values = self.get_query(
            "select user_group, quota_limit from quota_limits :condition",
            columns, values)

        self.cursor.execute(query, values)
        return dict(self.cursor.fetchall())

    def set_limits(self, quota, limits):

        """
        For the given 'quota', set the given 'limits' on resource usage mapping
        groups to limits.
        """

        columns = ["quota"]
        values = [quota]

        query, values = self.get_query(
            "delete from quota_limits :condition",
            columns, values)

        self.cursor.execute(query, values)

        for user_group, limit in limits.items():

            columns = ["quota", "user_group", "quota_limit"]
            values = [quota, user_group, limit]

            query, values = self.get_query(
                "insert into quota_limits (:columns) values (:values)",
                columns, values)

            self.cursor.execute(query, values)

        return True

    # Journal entry methods.

    def get_entries(self, quota, group, mutable=False):

        """
        Return a list of journal entries for the given 'quota' for the indicated
        'group'.
        """

        return self.get_freebusy_for_other(quota, group, mutable)

    def set_entries(self, quota, group, entries):

        """
        For the given 'quota' and indicated 'group', set the list of journal
        'entries'.
        """

        return self.set_freebusy_for_other(quota, entries, group)

    # Compatibility methods.

    def get_freebusy_for_other(self, user, other, mutable=False):
        return DatabaseStore.get_freebusy_for_other(self, user, other, mutable, cls=FreeBusyGroupDatabaseCollection)

    def set_freebusy_for_other(self, user, freebusy, other):
        return DatabaseStore.set_freebusy_for_other(self, user, freebusy, other, cls=FreeBusyGroupDatabaseCollection)

# vim: tabstop=4 expandtab shiftwidth=4