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_event_periods(self, uid, recurrenceid=None, participant=None): 187 188 """ 189 Remove from the collection all periods associated with 'uid' and 190 'recurrenceid' (which if omitted causes the "parent" object's periods to 191 be referenced). 192 193 If 'participant' is specified, only remove periods for which the 194 participant is given as attending. 195 196 Return the removed periods. 197 """ 198 199 self._check_mutable() 200 201 columns, values = ["object_uid"], [uid] 202 203 if recurrenceid: 204 columns.append("object_recurrenceid") 205 values.append(recurrenceid) 206 else: 207 columns.append("object_recurrenceid is null") 208 209 if participant: 210 columns.append("attendee") 211 values.append(participant) 212 213 query, _values = self.get_query( 214 "select %(columns)s from %(table)s :condition" % { 215 "columns" : self.columnlist(self.period_columns), 216 "table" : self.table_name 217 }, 218 columns, values) 219 220 self.cursor.execute(query, _values) 221 removed = self.cursor.fetchall() 222 223 query, values = self.get_query( 224 "delete from %(table)s :condition" % { 225 "table" : self.table_name 226 }, 227 columns, values) 228 229 self.cursor.execute(query, values) 230 231 return map(lambda t: self.make_period(t), removed) 232 233 # Specific period removal when updating event details. 234 235 remove_specific_event_periods = remove_event_periods 236 237 def remove_additional_periods(self, uid, recurrenceids=None): 238 239 """ 240 Remove from the collection all periods associated with 'uid' having a 241 recurrence identifier indicating an additional or modified period. 242 243 If 'recurrenceids' is specified, remove all periods associated with 244 'uid' that do not have a recurrence identifier in the given list. 245 246 Return the removed periods. 247 """ 248 249 self._check_mutable() 250 251 if not recurrenceids: 252 columns, values = ["object_uid", "object_recurrenceid is not null"], [uid] 253 else: 254 columns, values = ["object_uid", "object_recurrenceid not in ?", "object_recurrenceid is not null"], [uid, tuple(recurrenceids)] 255 256 query, _values = self.get_query( 257 "select %(columns)s from %(table)s :condition" % { 258 "columns" : self.columnlist(self.period_columns), 259 "table" : self.table_name 260 }, 261 columns, values) 262 263 self.cursor.execute(query, _values) 264 removed = self.cursor.fetchall() 265 266 query, values = self.get_query( 267 "delete from %(table)s :condition" % { 268 "table" : self.table_name 269 }, 270 columns, values) 271 272 self.cursor.execute(query, values) 273 274 return map(lambda t: self.make_period(t), removed) 275 276 def remove_affected_period(self, uid, start, participant=None): 277 278 """ 279 Remove from the collection the period associated with 'uid' that 280 provides an occurrence starting at the given 'start' (provided by a 281 recurrence identifier, converted to a datetime). A recurrence identifier 282 is used to provide an alternative time period whilst also acting as a 283 reference to the originally-defined occurrence. 284 285 If 'participant' is specified, only remove periods for which the 286 participant is given as attending. 287 288 Return any removed period in a list. 289 """ 290 291 self._check_mutable() 292 293 start = format_datetime(start) 294 295 columns, values = ["object_uid", "start", "object_recurrenceid is null"], [uid, start] 296 297 if participant: 298 columns.append("attendee") 299 values.append(participant) 300 301 query, _values = self.get_query( 302 "select %(columns)s from %(table)s :condition" % { 303 "columns" : self.columnlist(self.period_columns), 304 "table" : self.table_name 305 }, 306 columns, values) 307 308 self.cursor.execute(query, _values) 309 removed = self.cursor.fetchall() 310 311 query, values = self.get_query( 312 "delete from %(table)s :condition" % { 313 "table" : self.table_name 314 }, 315 columns, values) 316 317 self.cursor.execute(query, values) 318 319 return map(lambda t: self.make_period(t), removed) 320 321 def periods_from(self, period): 322 323 "Return the entries in the collection at or after 'period'." 324 325 start = format_datetime(period.get_start_point()) 326 327 columns, values = [], [] 328 329 if start: 330 columns.append("start >= ?") 331 values.append(start) 332 333 query, values = self.get_query( 334 "select %(columns)s from %(table)s :condition" % { 335 "columns" : self.columnlist(self.period_columns), 336 "table" : self.table_name 337 }, 338 columns, values) 339 340 self.cursor.execute(query, values) 341 342 return map(lambda t: self.make_period(t), self.cursor.fetchall()) 343 344 def periods_until(self, period): 345 346 "Return the entries in the collection before 'period'." 347 348 end = format_datetime(period.get_end_point()) 349 350 columns, values = [], [] 351 352 if end: 353 columns.append("start < ?") 354 values.append(end) 355 356 query, values = self.get_query( 357 "select %(columns)s from %(table)s :condition" % { 358 "columns" : self.columnlist(self.period_columns), 359 "table" : self.table_name 360 }, 361 columns, values) 362 363 self.cursor.execute(query, values) 364 365 return map(lambda t: self.make_period(t), self.cursor.fetchall()) 366 367 def get_overlapping(self, periods): 368 369 """ 370 Return the entries in the collection providing periods overlapping with 371 the given sorted collection of 'periods'. 372 """ 373 374 overlapping = set() 375 376 for period in periods: 377 columns, values = self._get_period_values(period) 378 379 query, values = self.get_query( 380 "select %(columns)s from %(table)s :condition" % { 381 "columns" : self.columnlist(self.period_columns), 382 "table" : self.table_name 383 }, 384 columns, values) 385 386 self.cursor.execute(query, values) 387 388 overlapping.update(map(lambda t: self.make_period(t), self.cursor.fetchall())) 389 390 overlapping = list(overlapping) 391 overlapping.sort() 392 return overlapping 393 394 def remove_overlapping(self, period): 395 396 "Remove all periods overlapping with 'period' from the collection." 397 398 self._check_mutable() 399 400 columns, values = self._get_period_values(period) 401 402 query, values = self.get_query( 403 "delete from %(table)s :condition" % { 404 "table" : self.table_name 405 }, 406 columns, values) 407 408 self.cursor.execute(query, values) 409 410 def _get_period_values(self, period): 411 412 start = format_datetime(period.get_start_point()) 413 end = format_datetime(period.get_end_point()) 414 415 columns, values = [], [] 416 417 if end: 418 columns.append("start < ?") 419 values.append(end) 420 if start: 421 columns.append("end > ?") 422 values.append(start) 423 424 return columns, values 425 426 class FreeBusyGroupDatabaseCollection(SupportAttendee, FreeBusyDatabaseCollection): 427 428 "A collection of quota group free/busy objects." 429 430 def remove_specific_event_periods(self, uid, recurrenceid=None, attendee=None): 431 432 """ 433 Remove from the collection all periods associated with 'uid' and 434 'recurrenceid' (which if omitted causes the "parent" object's periods to 435 be referenced) and any 'attendee'. 436 437 Return the removed periods. 438 """ 439 440 self._check_mutable() 441 442 columns, values = ["object_uid"], [uid] 443 444 if recurrenceid: 445 columns.append("object_recurrenceid") 446 values.append(recurrenceid) 447 else: 448 columns.append("object_recurrenceid is null") 449 450 if attendee: 451 columns.append("attendee") 452 values.append(attendee) 453 else: 454 columns.append("attendee is null") 455 456 query, _values = self.get_query( 457 "select %(columns)s from %(table)s :condition" % { 458 "columns" : self.columnlist(self.period_columns), 459 "table" : self.table_name 460 }, 461 columns, values) 462 463 self.cursor.execute(query, _values) 464 removed = self.cursor.fetchall() 465 466 query, values = self.get_query( 467 "delete from %(table)s :condition" % { 468 "table" : self.table_name 469 }, 470 columns, values) 471 472 self.cursor.execute(query, values) 473 474 return map(lambda t: self.make_period(t), removed) 475 476 class FreeBusyOffersDatabaseCollection(SupportExpires, FreeBusyDatabaseCollection): 477 478 "A collection of offered free/busy objects." 479 480 pass 481 482 # vim: tabstop=4 expandtab shiftwidth=4