#!/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 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  class DatabaseStore(DatabaseStoreBase, StoreBase):      "A database store of tabular free/busy data and objects."      # User discovery.      def get_users(self):          "Return a list of users."          query = "select distinct store_user from freebusy"         self.cursor.execute(query)         return [r[0] for r in self.cursor.fetchall()]      # Event and event metadata access.      def get_events(self, user):          "Return a list of event identifiers."          columns = ["store_user", "status"]         values = [user, "active"]          query, values = self.get_query(             "select object_uid from objects :condition",             columns, values)          self.cursor.execute(query, values)         return [r[0] for r in self.cursor.fetchall()]      def get_all_events(self, user):          "Return a set of (uid, recurrenceid) tuples for all events."          query, values = self.get_query(             "select object_uid, null as object_recurrenceid from objects :condition "             "union all "             "select object_uid, object_recurrenceid from recurrences :condition",             ["store_user"], [user])          self.cursor.execute(query, values)         return self.cursor.fetchall()      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_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_table(self, dirname=None):          "Get the table providing events for any specified 'dirname'."          if dirname == "counters":             return "countered_objects"         else:             return "objects"      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(             "select object_text from objects :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(             "update objects :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(             "insert into objects (: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(             "delete from objects :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(             "select object_recurrenceid from recurrences :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(             "select object_recurrenceid from recurrences :condition",             columns, values)          self.cursor.execute(query, values)         return [t[0] for t in self.cursor.fetchall() or []]      def get_recurrence_table(self, dirname=None):          "Get the table providing recurrences for any specified 'dirname'."          if dirname == "counters":             return "countered_recurrences"         else:             return "recurrences"      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(             "select object_text from recurrences :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(             "update recurrences :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(             "insert into recurrences (: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(             "delete from recurrences :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(             "delete from recurrences :condition",             columns, values)          self.cursor.execute(query, values)         return True      # 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(             "select object_uid, object_recurrenceid from freebusy_providers :condition",             columns, values)          self.cursor.execute(query, values)         providers = self.cursor.fetchall()          columns = ["store_user"]         values = [user]          query, values = self.get_query(             "select start from freebusy_provider_datetimes :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(             "delete from freebusy_providers :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(                 "insert into freebusy_providers (: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(             "update freebusy_provider_datetimes :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(             "insert into freebusy_provider_datetimes (: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):          "Get free/busy details for the given 'user'."          table = name or "freebusy"         return FreeBusyDatabaseCollection(self.cursor, table, ["store_user"], [user], mutable, self.paramstyle)      def get_freebusy_for_other(self, user, other, mutable=False):          "For the given 'user', get free/busy details for the 'other' user."          table = "freebusy_other"         return FreeBusyDatabaseCollection(self.cursor, table, ["store_user", "other"], [user, other], mutable, self.paramstyle)      def set_freebusy(self, user, freebusy, name=None):          "For the given 'user', set 'freebusy' details."          table = name or "freebusy"          if not isinstance(freebusy, FreeBusyDatabaseCollection) or freebusy.table_name != table:             fbc = FreeBusyDatabaseCollection(self.cursor, table, ["store_user"], [user], True, self.paramstyle)             fbc += freebusy          return True      def set_freebusy_for_other(self, user, freebusy, other):          "For the given 'user', set 'freebusy' details for the 'other' user."          table = "freebusy_other"          if not isinstance(freebusy, FreeBusyDatabaseCollection) or freebusy.table_name != table:             fbc = FreeBusyDatabaseCollection(self.cursor, table, ["store_user", "other"], [user, other], True, self.paramstyle)             fbc += freebusy          return True      # 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)      def set_freebusy_offers(self, user, freebusy):          "For the given 'user', set 'freebusy' offers."          return self.set_freebusy(user, freebusy, "freebusy_offers")      # 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 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(DatabaseStoreBase, JournalBase):      "A journal system to support quotas."      # Quota and user identity/group discovery.      def get_quotas(self):          "Return a list of quotas."          query = "select distinct journal_quota from quota_freebusy"         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."          columns = ["quota"]         values = [quota]          query, values = self.get_query(             "select distinct user_group from quota_freebusy :condition",             columns, values)          self.cursor.execute(query)         return [r[0] for r in self.cursor.fetchall()]      # 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 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_limit(self, quota, group, limit):          """         For the given 'quota', set for a user 'group' the given 'limit' on         resource usage.         """          columns = ["quota", "user_group"]         values = [quota, group]         setcolumns = ["quota_limit"]         setvalues = [limit]          query, values = self.get_query(             "update quota_limits :set :condition",             columns, values, setcolumns, setvalues)          self.cursor.execute(query, values)          if self.cursor.rowcount > 0:             return True          columns = ["quota", "user_group", "quota_limit"]         values = [quota, group, limit]          query, values = self.get_query(             "insert into quota_limits (:columns) values (:values)",             columns, values)          self.cursor.execute(query, values)         return True      # Free/busy period access for users within quota groups.      def get_freebusy(self, quota, user, mutable=False):          "Get free/busy details for the given 'quota' and 'user'."          table = "user_freebusy"         return FreeBusyDatabaseCollection(self.cursor, table, ["quota", "store_user"], [quota, user], mutable, self.paramstyle)      def set_freebusy(self, quota, user, freebusy):          "For the given 'quota' and 'user', set 'freebusy' details."          table = "user_freebusy"          if not isinstance(freebusy, FreeBusyDatabaseCollection) or freebusy.table_name != table:             fbc = FreeBusyDatabaseCollection(self.cursor, table, ["quota", "store_user"], [quota, user], True, self.paramstyle)             fbc += freebusy          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'.         """          table = "quota_freebusy"         return FreeBusyDatabaseCollection(self.cursor, table, ["quota", "user_group"], [quota, group], mutable, self.paramstyle)      def set_entries(self, quota, group, entries):          """         For the given 'quota' and indicated 'group', set the list of journal         'entries'.         """          table = "quota_freebusy"          if not isinstance(entries, FreeBusyDatabaseCollection) or entries.table_name != table:             fbc = FreeBusyDatabaseCollection(self.cursor, table, ["quota", "user_group"], [quota, group], True, self.paramstyle)             fbc += entries          return True  # vim: tabstop=4 expandtab shiftwidth=4