1 #!/usr/bin/env python 2 3 """ 4 Managing free/busy period collections using database representations. 5 6 Copyright (C) 2014, 2015, 2016, 2017 Paul Boddie <paul@boddie.org.uk> 7 8 This program is free software; you can redistribute it and/or modify it under 9 the terms of the GNU General Public License as published by the Free Software 10 Foundation; either version 3 of the License, or (at your option) any later 11 version. 12 13 This program is distributed in the hope that it will be useful, but WITHOUT 14 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS 15 FOR A PARTICULAR PURPOSE. See the GNU General Public License for more 16 details. 17 18 You should have received a copy of the GNU General Public License along with 19 this program. If not, see <http://www.gnu.org/licenses/>. 20 """ 21 22 from imiptools.dates import format_datetime 23 from imiptools.freebusy.common import FreeBusyCollectionBase, SupportAttendee, \ 24 SupportExpires, from_string, to_string 25 from imiptools.sql import DatabaseOperations 26 27 try: 28 from cStringIO import StringIO 29 except ImportError: 30 from StringIO import StringIO 31 32 # Conversion functions. 33 34 def to_copy_string(s, encoding): 35 36 """ 37 Encode 's' using 'encoding' as a string suitable for use in tabular data 38 acceptable to the PostgreSQL COPY command with \N as null. 39 """ 40 41 s = to_string(s, encoding) 42 return s is None and "\\N" or s 43 44 def to_copy_file(records): 45 46 """ 47 Encode the given 'records' and store them in a file-like object for use with 48 a tabular import mechanism. Return the file-like object. 49 """ 50 51 io = StringIO() 52 for values in records: 53 l = [] 54 for v in values: 55 l.append(to_copy_string(v, "utf-8")) 56 io.write("\t".join(l)) 57 io.write("\n") 58 io.seek(0) 59 return io 60 61 def quote_column(column): 62 63 "Quote 'column' using the SQL keyword quoting notation." 64 65 return '"%s"' % column 66 67 68 69 # Collection abstractions. 70 71 class FreeBusyDatabaseCollection(FreeBusyCollectionBase, DatabaseOperations): 72 73 """ 74 An abstraction for a collection of free/busy periods stored in a database 75 system. 76 """ 77 78 def __init__(self, cursor, table_name, column_names=None, filter_values=None, 79 mutable=True, paramstyle=None): 80 81 """ 82 Initialise the collection with the given 'cursor' and with the 83 'table_name', 'column_names' and 'filter_values' configuring the 84 selection of data. If 'mutable' is indicated, the collection may be 85 changed; otherwise, an exception will be raised. 86 """ 87 88 FreeBusyCollectionBase.__init__(self, mutable) 89 DatabaseOperations.__init__(self, column_names, filter_values, paramstyle) 90 self.cursor = cursor 91 self.table_name = table_name 92 93 # List emulation methods. 94 95 def __nonzero__(self): 96 return len(self) and True or False 97 98 def __iter__(self): 99 query, values = self.get_query( 100 "select %(columns)s from %(table)s :condition" % { 101 "columns" : self.columnlist(self.period_columns), 102 "table" : self.table_name 103 }) 104 self.cursor.execute(query, values) 105 return iter(map(lambda t: self.make_period(t), self.cursor.fetchall())) 106 107 def __len__(self): 108 query, values = self.get_query( 109 "select count(*) from %(table)s :condition" % { 110 "table" : self.table_name 111 }) 112 self.cursor.execute(query, values) 113 result = self.cursor.fetchone() 114 return result and int(result[0]) or 0 115 116 def __getitem__(self, i): 117 return list(iter(self))[i] 118 119 # Operations. 120 121 def insert_period(self, period): 122 123 "Insert the given 'period' into the collection." 124 125 self._check_mutable() 126 127 columns, values = self.period_columns, period.as_tuple(string_datetimes=True) 128 129 query, values = self.get_query( 130 "insert into %(table)s (:columns) values (:values)" % { 131 "table" : self.table_name 132 }, 133 columns, [to_string(v, "utf-8") for v in values]) 134 135 self.cursor.execute(query, values) 136 137 def insert_periods(self, periods): 138 139 "Insert the given 'periods' into the collection." 140 141 if not hasattr(self.cursor, "copy_from"): 142 return FreeBusyCollectionBase.insert_periods(self, periods) 143 144 self._check_mutable() 145 146 columns = self.merge_default_columns(self.period_columns) 147 148 all_values = [] 149 for period in periods: 150 all_values.append(self.merge_default_values(period.as_tuple(string_datetimes=True))) 151 152 f = to_copy_file(all_values) 153 154 # Copy from the file-like object to the table. 155 156 self.cursor.copy_from(f, self.table_name, columns=map(quote_column, columns)) 157 158 def remove_periods(self, periods): 159 160 "Remove the given 'periods' from the collection." 161 162 self._check_mutable() 163 164 for period in periods: 165 values = period.as_tuple(string_datetimes=True) 166 167 query, values = self.get_query( 168 "delete from %(table)s :condition" % { 169 "table" : self.table_name 170 }, 171 self.period_columns, [to_string(v, "utf-8") for v in values]) 172 173 self.cursor.execute(query, values) 174 175 def remove_event_periods(self, uid, recurrenceid=None, participant=None): 176 177 """ 178 Remove from the collection all periods associated with 'uid' and 179 'recurrenceid' (which if omitted causes the "parent" object's periods to 180 be referenced). 181 182 If 'participant' is specified, only remove periods for which the 183 participant is given as attending. 184 185 Return the removed periods. 186 """ 187 188 self._check_mutable() 189 190 columns, values = ["object_uid"], [uid] 191 192 if recurrenceid: 193 columns.append("object_recurrenceid") 194 values.append(recurrenceid) 195 else: 196 columns.append("object_recurrenceid is null") 197 198 if participant: 199 columns.append("attendee") 200 values.append(participant) 201 202 query, _values = self.get_query( 203 "select %(columns)s from %(table)s :condition" % { 204 "columns" : self.columnlist(self.period_columns), 205 "table" : self.table_name 206 }, 207 columns, values) 208 209 self.cursor.execute(query, _values) 210 removed = self.cursor.fetchall() 211 212 query, values = self.get_query( 213 "delete from %(table)s :condition" % { 214 "table" : self.table_name 215 }, 216 columns, values) 217 218 self.cursor.execute(query, values) 219 220 return map(lambda t: self.make_period(t), removed) 221 222 # Specific period removal when updating event details. 223 224 remove_specific_event_periods = remove_event_periods 225 226 def remove_additional_periods(self, uid, recurrenceids=None): 227 228 """ 229 Remove from the collection all periods associated with 'uid' having a 230 recurrence identifier indicating an additional or modified period. 231 232 If 'recurrenceids' is specified, remove all periods associated with 233 'uid' that do not have a recurrence identifier in the given list. 234 235 Return the removed periods. 236 """ 237 238 self._check_mutable() 239 240 if not recurrenceids: 241 columns, values = ["object_uid", "object_recurrenceid is not null"], [uid] 242 else: 243 columns, values = ["object_uid", "object_recurrenceid not in ?", "object_recurrenceid is not null"], [uid, tuple(recurrenceids)] 244 245 query, _values = self.get_query( 246 "select %(columns)s from %(table)s :condition" % { 247 "columns" : self.columnlist(self.period_columns), 248 "table" : self.table_name 249 }, 250 columns, values) 251 252 self.cursor.execute(query, _values) 253 removed = self.cursor.fetchall() 254 255 query, values = self.get_query( 256 "delete from %(table)s :condition" % { 257 "table" : self.table_name 258 }, 259 columns, values) 260 261 self.cursor.execute(query, values) 262 263 return map(lambda t: self.make_period(t), removed) 264 265 def remove_affected_period(self, uid, start, participant=None): 266 267 """ 268 Remove from the collection the period associated with 'uid' that 269 provides an occurrence starting at the given 'start' (provided by a 270 recurrence identifier, converted to a datetime). A recurrence identifier 271 is used to provide an alternative time period whilst also acting as a 272 reference to the originally-defined occurrence. 273 274 If 'participant' is specified, only remove periods for which the 275 participant is given as attending. 276 277 Return any removed period in a list. 278 """ 279 280 self._check_mutable() 281 282 start = format_datetime(start) 283 284 columns, values = ["object_uid", "start", "object_recurrenceid is null"], [uid, start] 285 286 if participant: 287 columns.append("attendee") 288 values.append(participant) 289 290 query, _values = self.get_query( 291 "select %(columns)s from %(table)s :condition" % { 292 "columns" : self.columnlist(self.period_columns), 293 "table" : self.table_name 294 }, 295 columns, values) 296 297 self.cursor.execute(query, _values) 298 removed = self.cursor.fetchall() 299 300 query, values = self.get_query( 301 "delete from %(table)s :condition" % { 302 "table" : self.table_name 303 }, 304 columns, values) 305 306 self.cursor.execute(query, values) 307 308 return map(lambda t: self.make_period(t), removed) 309 310 def periods_from(self, period): 311 312 "Return the entries in the collection at or after 'period'." 313 314 start = format_datetime(period.get_start_point()) 315 316 columns, values = [], [] 317 318 if start: 319 columns.append("start >= ?") 320 values.append(start) 321 322 query, values = self.get_query( 323 "select %(columns)s from %(table)s :condition" % { 324 "columns" : self.columnlist(self.period_columns), 325 "table" : self.table_name 326 }, 327 columns, values) 328 329 self.cursor.execute(query, values) 330 331 return map(lambda t: self.make_period(t), self.cursor.fetchall()) 332 333 def periods_until(self, period): 334 335 "Return the entries in the collection before 'period'." 336 337 end = format_datetime(period.get_end_point()) 338 339 columns, values = [], [] 340 341 if end: 342 columns.append("start < ?") 343 values.append(end) 344 345 query, values = self.get_query( 346 "select %(columns)s from %(table)s :condition" % { 347 "columns" : self.columnlist(self.period_columns), 348 "table" : self.table_name 349 }, 350 columns, values) 351 352 self.cursor.execute(query, values) 353 354 return map(lambda t: self.make_period(t), self.cursor.fetchall()) 355 356 def get_overlapping(self, periods): 357 358 """ 359 Return the entries in the collection providing periods overlapping with 360 the given sorted collection of 'periods'. 361 """ 362 363 overlapping = set() 364 365 for period in periods: 366 columns, values = self._get_period_values(period) 367 368 query, values = self.get_query( 369 "select %(columns)s from %(table)s :condition" % { 370 "columns" : self.columnlist(self.period_columns), 371 "table" : self.table_name 372 }, 373 columns, values) 374 375 self.cursor.execute(query, values) 376 377 overlapping.update(map(lambda t: self.make_period(t), self.cursor.fetchall())) 378 379 overlapping = list(overlapping) 380 overlapping.sort() 381 return overlapping 382 383 def remove_overlapping(self, period): 384 385 "Remove all periods overlapping with 'period' from the collection." 386 387 self._check_mutable() 388 389 columns, values = self._get_period_values(period) 390 391 query, values = self.get_query( 392 "delete from %(table)s :condition" % { 393 "table" : self.table_name 394 }, 395 columns, values) 396 397 self.cursor.execute(query, values) 398 399 def _get_period_values(self, period): 400 401 start = format_datetime(period.get_start_point()) 402 end = format_datetime(period.get_end_point()) 403 404 columns, values = [], [] 405 406 if end: 407 columns.append("start < ?") 408 values.append(end) 409 if start: 410 columns.append("end > ?") 411 values.append(start) 412 413 return columns, values 414 415 class FreeBusyGroupDatabaseCollection(SupportAttendee, FreeBusyDatabaseCollection): 416 417 "A collection of quota group free/busy objects." 418 419 def remove_specific_event_periods(self, uid, recurrenceid=None, attendee=None): 420 421 """ 422 Remove from the collection all periods associated with 'uid' and 423 'recurrenceid' (which if omitted causes the "parent" object's periods to 424 be referenced) and any 'attendee'. 425 426 Return the removed periods. 427 """ 428 429 self._check_mutable() 430 431 columns, values = ["object_uid"], [uid] 432 433 if recurrenceid: 434 columns.append("object_recurrenceid") 435 values.append(recurrenceid) 436 else: 437 columns.append("object_recurrenceid is null") 438 439 if attendee: 440 columns.append("attendee") 441 values.append(attendee) 442 else: 443 columns.append("attendee is null") 444 445 query, _values = self.get_query( 446 "select %(columns)s from %(table)s :condition" % { 447 "columns" : self.columnlist(self.period_columns), 448 "table" : self.table_name 449 }, 450 columns, values) 451 452 self.cursor.execute(query, _values) 453 removed = self.cursor.fetchall() 454 455 query, values = self.get_query( 456 "delete from %(table)s :condition" % { 457 "table" : self.table_name 458 }, 459 columns, values) 460 461 self.cursor.execute(query, values) 462 463 return map(lambda t: self.make_period(t), removed) 464 465 class FreeBusyOffersDatabaseCollection(SupportExpires, FreeBusyDatabaseCollection): 466 467 "A collection of offered free/busy objects." 468 469 pass 470 471 # vim: tabstop=4 expandtab shiftwidth=4