1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/imiptools/freebusy/database.py Fri May 26 18:25:23 2017 +0200
1.3 @@ -0,0 +1,471 @@
1.4 +#!/usr/bin/env python
1.5 +
1.6 +"""
1.7 +Managing free/busy period collections using database representations.
1.8 +
1.9 +Copyright (C) 2014, 2015, 2016, 2017 Paul Boddie <paul@boddie.org.uk>
1.10 +
1.11 +This program is free software; you can redistribute it and/or modify it under
1.12 +the terms of the GNU General Public License as published by the Free Software
1.13 +Foundation; either version 3 of the License, or (at your option) any later
1.14 +version.
1.15 +
1.16 +This program is distributed in the hope that it will be useful, but WITHOUT
1.17 +ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
1.18 +FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
1.19 +details.
1.20 +
1.21 +You should have received a copy of the GNU General Public License along with
1.22 +this program. If not, see <http://www.gnu.org/licenses/>.
1.23 +"""
1.24 +
1.25 +from imiptools.dates import format_datetime
1.26 +from imiptools.freebusy.common import FreeBusyCollectionBase, SupportAttendee, \
1.27 + SupportExpires, from_string, to_string
1.28 +from imiptools.sql import DatabaseOperations
1.29 +
1.30 +try:
1.31 + from cStringIO import StringIO
1.32 +except ImportError:
1.33 + from StringIO import StringIO
1.34 +
1.35 +# Conversion functions.
1.36 +
1.37 +def to_copy_string(s, encoding):
1.38 +
1.39 + """
1.40 + Encode 's' using 'encoding' as a string suitable for use in tabular data
1.41 + acceptable to the PostgreSQL COPY command with \N as null.
1.42 + """
1.43 +
1.44 + s = to_string(s, encoding)
1.45 + return s is None and "\\N" or s
1.46 +
1.47 +def to_copy_file(records):
1.48 +
1.49 + """
1.50 + Encode the given 'records' and store them in a file-like object for use with
1.51 + a tabular import mechanism. Return the file-like object.
1.52 + """
1.53 +
1.54 + io = StringIO()
1.55 + for values in records:
1.56 + l = []
1.57 + for v in values:
1.58 + l.append(to_copy_string(v, "utf-8"))
1.59 + io.write("\t".join(l))
1.60 + io.write("\n")
1.61 + io.seek(0)
1.62 + return io
1.63 +
1.64 +def quote_column(column):
1.65 +
1.66 + "Quote 'column' using the SQL keyword quoting notation."
1.67 +
1.68 + return '"%s"' % column
1.69 +
1.70 +
1.71 +
1.72 +# Collection abstractions.
1.73 +
1.74 +class FreeBusyDatabaseCollection(FreeBusyCollectionBase, DatabaseOperations):
1.75 +
1.76 + """
1.77 + An abstraction for a collection of free/busy periods stored in a database
1.78 + system.
1.79 + """
1.80 +
1.81 + def __init__(self, cursor, table_name, column_names=None, filter_values=None,
1.82 + mutable=True, paramstyle=None):
1.83 +
1.84 + """
1.85 + Initialise the collection with the given 'cursor' and with the
1.86 + 'table_name', 'column_names' and 'filter_values' configuring the
1.87 + selection of data. If 'mutable' is indicated, the collection may be
1.88 + changed; otherwise, an exception will be raised.
1.89 + """
1.90 +
1.91 + FreeBusyCollectionBase.__init__(self, mutable)
1.92 + DatabaseOperations.__init__(self, column_names, filter_values, paramstyle)
1.93 + self.cursor = cursor
1.94 + self.table_name = table_name
1.95 +
1.96 + # List emulation methods.
1.97 +
1.98 + def __nonzero__(self):
1.99 + return len(self) and True or False
1.100 +
1.101 + def __iter__(self):
1.102 + query, values = self.get_query(
1.103 + "select %(columns)s from %(table)s :condition" % {
1.104 + "columns" : self.columnlist(self.period_columns),
1.105 + "table" : self.table_name
1.106 + })
1.107 + self.cursor.execute(query, values)
1.108 + return iter(map(lambda t: self.make_period(t), self.cursor.fetchall()))
1.109 +
1.110 + def __len__(self):
1.111 + query, values = self.get_query(
1.112 + "select count(*) from %(table)s :condition" % {
1.113 + "table" : self.table_name
1.114 + })
1.115 + self.cursor.execute(query, values)
1.116 + result = self.cursor.fetchone()
1.117 + return result and int(result[0]) or 0
1.118 +
1.119 + def __getitem__(self, i):
1.120 + return list(iter(self))[i]
1.121 +
1.122 + # Operations.
1.123 +
1.124 + def insert_period(self, period):
1.125 +
1.126 + "Insert the given 'period' into the collection."
1.127 +
1.128 + self._check_mutable()
1.129 +
1.130 + columns, values = self.period_columns, period.as_tuple(string_datetimes=True)
1.131 +
1.132 + query, values = self.get_query(
1.133 + "insert into %(table)s (:columns) values (:values)" % {
1.134 + "table" : self.table_name
1.135 + },
1.136 + columns, [to_string(v, "utf-8") for v in values])
1.137 +
1.138 + self.cursor.execute(query, values)
1.139 +
1.140 + def insert_periods(self, periods):
1.141 +
1.142 + "Insert the given 'periods' into the collection."
1.143 +
1.144 + if not hasattr(self.cursor, "copy_from"):
1.145 + return FreeBusyCollectionBase.insert_periods(self, periods)
1.146 +
1.147 + self._check_mutable()
1.148 +
1.149 + columns = self.merge_default_columns(self.period_columns)
1.150 +
1.151 + all_values = []
1.152 + for period in periods:
1.153 + all_values.append(self.merge_default_values(period.as_tuple(string_datetimes=True)))
1.154 +
1.155 + f = to_copy_file(all_values)
1.156 +
1.157 + # Copy from the file-like object to the table.
1.158 +
1.159 + self.cursor.copy_from(f, self.table_name, columns=map(quote_column, columns))
1.160 +
1.161 + def remove_periods(self, periods):
1.162 +
1.163 + "Remove the given 'periods' from the collection."
1.164 +
1.165 + self._check_mutable()
1.166 +
1.167 + for period in periods:
1.168 + values = period.as_tuple(string_datetimes=True)
1.169 +
1.170 + query, values = self.get_query(
1.171 + "delete from %(table)s :condition" % {
1.172 + "table" : self.table_name
1.173 + },
1.174 + self.period_columns, [to_string(v, "utf-8") for v in values])
1.175 +
1.176 + self.cursor.execute(query, values)
1.177 +
1.178 + def remove_event_periods(self, uid, recurrenceid=None, participant=None):
1.179 +
1.180 + """
1.181 + Remove from the collection all periods associated with 'uid' and
1.182 + 'recurrenceid' (which if omitted causes the "parent" object's periods to
1.183 + be referenced).
1.184 +
1.185 + If 'participant' is specified, only remove periods for which the
1.186 + participant is given as attending.
1.187 +
1.188 + Return the removed periods.
1.189 + """
1.190 +
1.191 + self._check_mutable()
1.192 +
1.193 + columns, values = ["object_uid"], [uid]
1.194 +
1.195 + if recurrenceid:
1.196 + columns.append("object_recurrenceid")
1.197 + values.append(recurrenceid)
1.198 + else:
1.199 + columns.append("object_recurrenceid is null")
1.200 +
1.201 + if participant:
1.202 + columns.append("attendee")
1.203 + values.append(participant)
1.204 +
1.205 + query, _values = self.get_query(
1.206 + "select %(columns)s from %(table)s :condition" % {
1.207 + "columns" : self.columnlist(self.period_columns),
1.208 + "table" : self.table_name
1.209 + },
1.210 + columns, values)
1.211 +
1.212 + self.cursor.execute(query, _values)
1.213 + removed = self.cursor.fetchall()
1.214 +
1.215 + query, values = self.get_query(
1.216 + "delete from %(table)s :condition" % {
1.217 + "table" : self.table_name
1.218 + },
1.219 + columns, values)
1.220 +
1.221 + self.cursor.execute(query, values)
1.222 +
1.223 + return map(lambda t: self.make_period(t), removed)
1.224 +
1.225 + # Specific period removal when updating event details.
1.226 +
1.227 + remove_specific_event_periods = remove_event_periods
1.228 +
1.229 + def remove_additional_periods(self, uid, recurrenceids=None):
1.230 +
1.231 + """
1.232 + Remove from the collection all periods associated with 'uid' having a
1.233 + recurrence identifier indicating an additional or modified period.
1.234 +
1.235 + If 'recurrenceids' is specified, remove all periods associated with
1.236 + 'uid' that do not have a recurrence identifier in the given list.
1.237 +
1.238 + Return the removed periods.
1.239 + """
1.240 +
1.241 + self._check_mutable()
1.242 +
1.243 + if not recurrenceids:
1.244 + columns, values = ["object_uid", "object_recurrenceid is not null"], [uid]
1.245 + else:
1.246 + columns, values = ["object_uid", "object_recurrenceid not in ?", "object_recurrenceid is not null"], [uid, tuple(recurrenceids)]
1.247 +
1.248 + query, _values = self.get_query(
1.249 + "select %(columns)s from %(table)s :condition" % {
1.250 + "columns" : self.columnlist(self.period_columns),
1.251 + "table" : self.table_name
1.252 + },
1.253 + columns, values)
1.254 +
1.255 + self.cursor.execute(query, _values)
1.256 + removed = self.cursor.fetchall()
1.257 +
1.258 + query, values = self.get_query(
1.259 + "delete from %(table)s :condition" % {
1.260 + "table" : self.table_name
1.261 + },
1.262 + columns, values)
1.263 +
1.264 + self.cursor.execute(query, values)
1.265 +
1.266 + return map(lambda t: self.make_period(t), removed)
1.267 +
1.268 + def remove_affected_period(self, uid, start, participant=None):
1.269 +
1.270 + """
1.271 + Remove from the collection the period associated with 'uid' that
1.272 + provides an occurrence starting at the given 'start' (provided by a
1.273 + recurrence identifier, converted to a datetime). A recurrence identifier
1.274 + is used to provide an alternative time period whilst also acting as a
1.275 + reference to the originally-defined occurrence.
1.276 +
1.277 + If 'participant' is specified, only remove periods for which the
1.278 + participant is given as attending.
1.279 +
1.280 + Return any removed period in a list.
1.281 + """
1.282 +
1.283 + self._check_mutable()
1.284 +
1.285 + start = format_datetime(start)
1.286 +
1.287 + columns, values = ["object_uid", "start", "object_recurrenceid is null"], [uid, start]
1.288 +
1.289 + if participant:
1.290 + columns.append("attendee")
1.291 + values.append(participant)
1.292 +
1.293 + query, _values = self.get_query(
1.294 + "select %(columns)s from %(table)s :condition" % {
1.295 + "columns" : self.columnlist(self.period_columns),
1.296 + "table" : self.table_name
1.297 + },
1.298 + columns, values)
1.299 +
1.300 + self.cursor.execute(query, _values)
1.301 + removed = self.cursor.fetchall()
1.302 +
1.303 + query, values = self.get_query(
1.304 + "delete from %(table)s :condition" % {
1.305 + "table" : self.table_name
1.306 + },
1.307 + columns, values)
1.308 +
1.309 + self.cursor.execute(query, values)
1.310 +
1.311 + return map(lambda t: self.make_period(t), removed)
1.312 +
1.313 + def periods_from(self, period):
1.314 +
1.315 + "Return the entries in the collection at or after 'period'."
1.316 +
1.317 + start = format_datetime(period.get_start_point())
1.318 +
1.319 + columns, values = [], []
1.320 +
1.321 + if start:
1.322 + columns.append("start >= ?")
1.323 + values.append(start)
1.324 +
1.325 + query, values = self.get_query(
1.326 + "select %(columns)s from %(table)s :condition" % {
1.327 + "columns" : self.columnlist(self.period_columns),
1.328 + "table" : self.table_name
1.329 + },
1.330 + columns, values)
1.331 +
1.332 + self.cursor.execute(query, values)
1.333 +
1.334 + return map(lambda t: self.make_period(t), self.cursor.fetchall())
1.335 +
1.336 + def periods_until(self, period):
1.337 +
1.338 + "Return the entries in the collection before 'period'."
1.339 +
1.340 + end = format_datetime(period.get_end_point())
1.341 +
1.342 + columns, values = [], []
1.343 +
1.344 + if end:
1.345 + columns.append("start < ?")
1.346 + values.append(end)
1.347 +
1.348 + query, values = self.get_query(
1.349 + "select %(columns)s from %(table)s :condition" % {
1.350 + "columns" : self.columnlist(self.period_columns),
1.351 + "table" : self.table_name
1.352 + },
1.353 + columns, values)
1.354 +
1.355 + self.cursor.execute(query, values)
1.356 +
1.357 + return map(lambda t: self.make_period(t), self.cursor.fetchall())
1.358 +
1.359 + def get_overlapping(self, periods):
1.360 +
1.361 + """
1.362 + Return the entries in the collection providing periods overlapping with
1.363 + the given sorted collection of 'periods'.
1.364 + """
1.365 +
1.366 + overlapping = set()
1.367 +
1.368 + for period in periods:
1.369 + columns, values = self._get_period_values(period)
1.370 +
1.371 + query, values = self.get_query(
1.372 + "select %(columns)s from %(table)s :condition" % {
1.373 + "columns" : self.columnlist(self.period_columns),
1.374 + "table" : self.table_name
1.375 + },
1.376 + columns, values)
1.377 +
1.378 + self.cursor.execute(query, values)
1.379 +
1.380 + overlapping.update(map(lambda t: self.make_period(t), self.cursor.fetchall()))
1.381 +
1.382 + overlapping = list(overlapping)
1.383 + overlapping.sort()
1.384 + return overlapping
1.385 +
1.386 + def remove_overlapping(self, period):
1.387 +
1.388 + "Remove all periods overlapping with 'period' from the collection."
1.389 +
1.390 + self._check_mutable()
1.391 +
1.392 + columns, values = self._get_period_values(period)
1.393 +
1.394 + query, values = self.get_query(
1.395 + "delete from %(table)s :condition" % {
1.396 + "table" : self.table_name
1.397 + },
1.398 + columns, values)
1.399 +
1.400 + self.cursor.execute(query, values)
1.401 +
1.402 + def _get_period_values(self, period):
1.403 +
1.404 + start = format_datetime(period.get_start_point())
1.405 + end = format_datetime(period.get_end_point())
1.406 +
1.407 + columns, values = [], []
1.408 +
1.409 + if end:
1.410 + columns.append("start < ?")
1.411 + values.append(end)
1.412 + if start:
1.413 + columns.append("end > ?")
1.414 + values.append(start)
1.415 +
1.416 + return columns, values
1.417 +
1.418 +class FreeBusyGroupDatabaseCollection(SupportAttendee, FreeBusyDatabaseCollection):
1.419 +
1.420 + "A collection of quota group free/busy objects."
1.421 +
1.422 + def remove_specific_event_periods(self, uid, recurrenceid=None, attendee=None):
1.423 +
1.424 + """
1.425 + Remove from the collection all periods associated with 'uid' and
1.426 + 'recurrenceid' (which if omitted causes the "parent" object's periods to
1.427 + be referenced) and any 'attendee'.
1.428 +
1.429 + Return the removed periods.
1.430 + """
1.431 +
1.432 + self._check_mutable()
1.433 +
1.434 + columns, values = ["object_uid"], [uid]
1.435 +
1.436 + if recurrenceid:
1.437 + columns.append("object_recurrenceid")
1.438 + values.append(recurrenceid)
1.439 + else:
1.440 + columns.append("object_recurrenceid is null")
1.441 +
1.442 + if attendee:
1.443 + columns.append("attendee")
1.444 + values.append(attendee)
1.445 + else:
1.446 + columns.append("attendee is null")
1.447 +
1.448 + query, _values = self.get_query(
1.449 + "select %(columns)s from %(table)s :condition" % {
1.450 + "columns" : self.columnlist(self.period_columns),
1.451 + "table" : self.table_name
1.452 + },
1.453 + columns, values)
1.454 +
1.455 + self.cursor.execute(query, _values)
1.456 + removed = self.cursor.fetchall()
1.457 +
1.458 + query, values = self.get_query(
1.459 + "delete from %(table)s :condition" % {
1.460 + "table" : self.table_name
1.461 + },
1.462 + columns, values)
1.463 +
1.464 + self.cursor.execute(query, values)
1.465 +
1.466 + return map(lambda t: self.make_period(t), removed)
1.467 +
1.468 +class FreeBusyOffersDatabaseCollection(SupportExpires, FreeBusyDatabaseCollection):
1.469 +
1.470 + "A collection of offered free/busy objects."
1.471 +
1.472 + pass
1.473 +
1.474 +# vim: tabstop=4 expandtab shiftwidth=4