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 self.period_columns = self.period_class.period_columns 94 95 # List emulation methods. 96 97 def __nonzero__(self): 98 return len(self) and True or False 99 100 def __iter__(self): 101 query, values = self.get_query( 102 "select %(columns)s from %(table)s :condition" % { 103 "columns" : self.columnlist(self.period_columns), 104 "table" : self.table_name 105 }) 106 self.cursor.execute(query, values) 107 return iter(map(lambda t: self.make_period(t), self.cursor.fetchall())) 108 109 def __len__(self): 110 query, values = self.get_query( 111 "select count(*) from %(table)s :condition" % { 112 "table" : self.table_name 113 }) 114 self.cursor.execute(query, values) 115 result = self.cursor.fetchone() 116 return result and int(result[0]) or 0 117 118 def __getitem__(self, i): 119 return list(iter(self))[i] 120 121 # Dictionary emulation methods (even though this is not a mapping). 122 123 def clear(self): 124 query, values = self.get_query( 125 "delete from %(table)s :condition" % { 126 "table" : self.table_name 127 }) 128 self.cursor.execute(query, values) 129 130 # Operations. 131 132 def insert_period(self, period): 133 134 "Insert the given 'period' into the collection." 135 136 self._check_mutable() 137 138 columns, values = self.period_columns, period.as_tuple(string_datetimes=True) 139 140 query, values = self.get_query( 141 "insert into %(table)s (:columns) values (:values)" % { 142 "table" : self.table_name 143 }, 144 columns, [to_string(v, "utf-8") for v in values]) 145 146 self.cursor.execute(query, values) 147 148 def insert_periods(self, periods): 149 150 "Insert the given 'periods' into the collection." 151 152 if not hasattr(self.cursor, "copy_from"): 153 return FreeBusyCollectionBase.insert_periods(self, periods) 154 155 self._check_mutable() 156 157 columns = self.merge_default_columns(self.period_columns) 158 159 all_values = [] 160 for period in periods: 161 all_values.append(self.merge_default_values(period.as_tuple(string_datetimes=True))) 162 163 f = to_copy_file(all_values) 164 165 # Copy from the file-like object to the table. 166 167 self.cursor.copy_from(f, self.table_name, columns=map(quote_column, columns)) 168 169 def remove_periods(self, periods): 170 171 "Remove the given 'periods' from the collection." 172 173 self._check_mutable() 174 175 for period in periods: 176 values = period.as_tuple(string_datetimes=True) 177 178 query, values = self.get_query( 179 "delete from %(table)s :condition" % { 180 "table" : self.table_name 181 }, 182 self.period_columns, [to_string(v, "utf-8") for v in values]) 183 184 self.cursor.execute(query, values) 185 186 def remove_periods_before(self, period): 187 188 "Remove the entries in the collection before 'period'." 189 190 end = format_datetime(period.get_start_point()) 191 192 columns, values = [], [] 193 194 if end: 195 columns.append("end <= ?") 196 values.append(end) 197 198 query, values = self.get_query( 199 "delete from %(table)s :condition" % { 200 "columns" : self.columnlist(self.period_columns), 201 "table" : self.table_name 202 }, 203 columns, values) 204 205 self.cursor.execute(query, values) 206 207 def remove_event_periods(self, uid, recurrenceid=None, participant=None): 208 209 """ 210 Remove from the collection all periods associated with 'uid' and 211 'recurrenceid' (which if omitted causes the "parent" object's periods to 212 be referenced). 213 214 If 'participant' is specified, only remove periods for which the 215 participant is given as attending. 216 217 Return the removed periods. 218 """ 219 220 self._check_mutable() 221 222 columns, values = ["object_uid"], [uid] 223 224 if recurrenceid: 225 columns.append("object_recurrenceid") 226 values.append(recurrenceid) 227 else: 228 columns.append("object_recurrenceid is null") 229 230 if participant: 231 columns.append("attendee") 232 values.append(participant) 233 234 query, _values = self.get_query( 235 "select %(columns)s from %(table)s :condition" % { 236 "columns" : self.columnlist(self.period_columns), 237 "table" : self.table_name 238 }, 239 columns, values) 240 241 self.cursor.execute(query, _values) 242 removed = self.cursor.fetchall() 243 244 query, values = self.get_query( 245 "delete from %(table)s :condition" % { 246 "table" : self.table_name 247 }, 248 columns, values) 249 250 self.cursor.execute(query, values) 251 252 return map(lambda t: self.make_period(t), removed) 253 254 # Specific period removal when updating event details. 255 256 remove_specific_event_periods = remove_event_periods 257 258 def remove_additional_periods(self, uid, recurrenceids=None): 259 260 """ 261 Remove from the collection all periods associated with 'uid' having a 262 recurrence identifier indicating an additional or modified period. 263 264 If 'recurrenceids' is specified, remove all periods associated with 265 'uid' that do not have a recurrence identifier in the given list. 266 267 Return the removed periods. 268 """ 269 270 self._check_mutable() 271 272 if not recurrenceids: 273 columns, values = ["object_uid", "object_recurrenceid is not null"], [uid] 274 else: 275 columns, values = ["object_uid", "object_recurrenceid not in ?", "object_recurrenceid is not null"], [uid, tuple(recurrenceids)] 276 277 query, _values = self.get_query( 278 "select %(columns)s from %(table)s :condition" % { 279 "columns" : self.columnlist(self.period_columns), 280 "table" : self.table_name 281 }, 282 columns, values) 283 284 self.cursor.execute(query, _values) 285 removed = self.cursor.fetchall() 286 287 query, values = self.get_query( 288 "delete from %(table)s :condition" % { 289 "table" : self.table_name 290 }, 291 columns, values) 292 293 self.cursor.execute(query, values) 294 295 return map(lambda t: self.make_period(t), removed) 296 297 def remove_affected_period(self, uid, start, participant=None): 298 299 """ 300 Remove from the collection the period associated with 'uid' that 301 provides an occurrence starting at the given 'start' (provided by a 302 recurrence identifier, converted to a datetime). A recurrence identifier 303 is used to provide an alternative time period whilst also acting as a 304 reference to the originally-defined occurrence. 305 306 If 'participant' is specified, only remove periods for which the 307 participant is given as attending. 308 309 Return any removed period in a list. 310 """ 311 312 self._check_mutable() 313 314 start = format_datetime(start) 315 316 columns, values = ["object_uid", "start", "object_recurrenceid is null"], [uid, start] 317 318 if participant: 319 columns.append("attendee") 320 values.append(participant) 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 removed = self.cursor.fetchall() 331 332 query, values = self.get_query( 333 "delete from %(table)s :condition" % { 334 "table" : self.table_name 335 }, 336 columns, values) 337 338 self.cursor.execute(query, values) 339 340 return map(lambda t: self.make_period(t), removed) 341 342 def periods_from(self, period): 343 344 "Return the entries in the collection at or after 'period'." 345 346 start = format_datetime(period.get_start_point()) 347 348 columns, values = [], [] 349 350 if start: 351 columns.append("start >= ?") 352 values.append(start) 353 354 query, values = self.get_query( 355 "select %(columns)s from %(table)s :condition" % { 356 "columns" : self.columnlist(self.period_columns), 357 "table" : self.table_name 358 }, 359 columns, values) 360 361 self.cursor.execute(query, values) 362 363 return map(lambda t: self.make_period(t), self.cursor.fetchall()) 364 365 def periods_until(self, period): 366 367 "Return the entries in the collection before 'period'." 368 369 end = format_datetime(period.get_end_point()) 370 371 columns, values = [], [] 372 373 if end: 374 columns.append("start < ?") 375 values.append(end) 376 377 query, values = self.get_query( 378 "select %(columns)s from %(table)s :condition" % { 379 "columns" : self.columnlist(self.period_columns), 380 "table" : self.table_name 381 }, 382 columns, values) 383 384 self.cursor.execute(query, values) 385 386 return map(lambda t: self.make_period(t), self.cursor.fetchall()) 387 388 def get_overlapping(self, periods): 389 390 """ 391 Return the entries in the collection providing periods overlapping with 392 the given sorted collection of 'periods'. 393 """ 394 395 overlapping = set() 396 397 for period in periods: 398 columns, values = self._get_period_values(period) 399 400 query, values = self.get_query( 401 "select %(columns)s from %(table)s :condition" % { 402 "columns" : self.columnlist(self.period_columns), 403 "table" : self.table_name 404 }, 405 columns, values) 406 407 self.cursor.execute(query, values) 408 409 overlapping.update(map(lambda t: self.make_period(t), self.cursor.fetchall())) 410 411 overlapping = list(overlapping) 412 overlapping.sort() 413 return overlapping 414 415 def remove_overlapping(self, period): 416 417 "Remove all periods overlapping with 'period' from the collection." 418 419 self._check_mutable() 420 421 columns, values = self._get_period_values(period) 422 423 query, values = self.get_query( 424 "delete from %(table)s :condition" % { 425 "table" : self.table_name 426 }, 427 columns, values) 428 429 self.cursor.execute(query, values) 430 431 def _get_period_values(self, period): 432 433 start = format_datetime(period.get_start_point()) 434 end = format_datetime(period.get_end_point()) 435 436 columns, values = [], [] 437 438 if end: 439 columns.append("start < ?") 440 values.append(end) 441 if start: 442 columns.append("end > ?") 443 values.append(start) 444 445 return columns, values 446 447 class FreeBusyGroupDatabaseCollection(SupportAttendee, FreeBusyDatabaseCollection): 448 449 "A collection of quota group free/busy objects." 450 451 def remove_specific_event_periods(self, uid, recurrenceid=None, attendee=None): 452 453 """ 454 Remove from the collection all periods associated with 'uid' and 455 'recurrenceid' (which if omitted causes the "parent" object's periods to 456 be referenced) and any 'attendee'. 457 458 Return the removed periods. 459 """ 460 461 self._check_mutable() 462 463 columns, values = ["object_uid"], [uid] 464 465 if recurrenceid: 466 columns.append("object_recurrenceid") 467 values.append(recurrenceid) 468 else: 469 columns.append("object_recurrenceid is null") 470 471 if attendee: 472 columns.append("attendee") 473 values.append(attendee) 474 else: 475 columns.append("attendee is null") 476 477 query, _values = self.get_query( 478 "select %(columns)s from %(table)s :condition" % { 479 "columns" : self.columnlist(self.period_columns), 480 "table" : self.table_name 481 }, 482 columns, values) 483 484 self.cursor.execute(query, _values) 485 removed = self.cursor.fetchall() 486 487 query, values = self.get_query( 488 "delete from %(table)s :condition" % { 489 "table" : self.table_name 490 }, 491 columns, values) 492 493 self.cursor.execute(query, values) 494 495 return map(lambda t: self.make_period(t), removed) 496 497 class FreeBusyOffersDatabaseCollection(SupportExpires, FreeBusyDatabaseCollection): 498 499 "A collection of offered free/busy objects." 500 501 pass 502 503 # vim: tabstop=4 expandtab shiftwidth=4