#!/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 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 FreeBusyPeriod, FreeBusyDatabaseCollection from imiptools.sql import DatabaseOperations  class DatabaseStoreBase:      "A database store supporting user-specific locking."      def acquire_lock(self, user, timeout=None):         FileBase.acquire_lock(self, timeout, user)      def release_lock(self, user):         FileBase.release_lock(self, user)  class DatabaseStore(DatabaseStoreBase, StoreBase, DatabaseOperations):      "A database store of tabular free/busy data and objects."      def __init__(self, connection, paramstyle=None):         DatabaseOperations.__init__(self, paramstyle=paramstyle)         self.connection = connection         self.cursor = connection.cursor()      # 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 [], []      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"         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"          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)      # 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 from requests :condition",             columns, values)          self.cursor.execute(query, values)         return self.cursor.fetchall()      def set_requests(self, user, requests):          "For the given 'user', set the list of queued 'requests'."          # NOTE: Locking?          columns = ["store_user"]         values = [user]          query, values = self.get_query(             "delete from requests :condition",             columns, values)          self.cursor.execute(query, values)          for uid, recurrenceid, type in requests:             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 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 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.fetchall()         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")          columns = ["store_user", "other", "object_uid", "object_recurrenceid", "object_text"]         values = [user, other, uid, recurrenceid, 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)         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)         return dict(self.cursor.fetchall())      # 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