# HG changeset patch # User Paul Boddie # Date 1457305799 -3600 # Node ID 3265b51f4a7fe66126de33ff855bbddf8597f8f8 # Parent 562e94fc8827ee374df09fcd56a49435bcc0c18c Added further parameterisation of database-resident free/busy collections so that tables may contain other columns such as user-specific details. Fixed removal of affected periods in database-resident collections. diff -r 562e94fc8827 -r 3265b51f4a7f imiptools/period.py --- a/imiptools/period.py Sun Mar 06 19:23:39 2016 +0100 +++ b/imiptools/period.py Mon Mar 07 00:09:59 2016 +0100 @@ -356,17 +356,19 @@ self.organiser = organiser or None self.expires = expires or None - def as_tuple(self, strings_only=False): + def as_tuple(self, strings_only=False, string_datetimes=False): """ - Return the initialisation parameter tuple, converting false value - parameters to strings if 'strings_only' is set to a true value. + Return the initialisation parameter tuple, converting datetimes and + false value parameters to strings if 'strings_only' is set to a true + value. Otherwise, if 'string_datetimes' is set to a true value, only the + datetime values are converted to strings. """ null = lambda x: (strings_only and [""] or [x])[0] return ( - strings_only and format_datetime(self.get_start_point()) or self.start, - strings_only and format_datetime(self.get_end_point()) or self.end, + (strings_only or string_datetimes) and format_datetime(self.get_start_point()) or self.start, + (strings_only or string_datetimes) and format_datetime(self.get_end_point()) or self.end, self.uid or null(self.uid), self.transp or strings_only and "OPAQUE" or None, self.recurrenceid or null(self.recurrenceid), @@ -830,18 +832,45 @@ system. """ - def __init__(self, cursor, table_name, mutable=True): + period_columns = ["start", "end", "uid", "transp", "recurrenceid", "summary", "organiser", "expires"] + + def __init__(self, cursor, table_name, column_names=None, filter_values=None, mutable=True): """ - Initialise the collection with the given 'cursor' and 'table_name'. + Initialise the collection with the given 'cursor' and with the + 'table_name', 'column_names' and 'filter_values' configuring the + selection of data. """ FreeBusyCollectionBase.__init__(self, mutable) self.cursor = cursor self.table_name = table_name + self.column_names = column_names + self.filter_values = filter_values # Special database-related operations. + def get_condition(self, columns=None, values=None): + + """ + Return a condition clause featuring the given 'columns' and 'values' + together with any conditions provided when initialising this class. + """ + + c = list(self.column_names or []) + list(columns or []) + v = list(self.filter_values or []) + list(values or []) + return "where %s" % " and ".join([("%s = ?" % s) for s in c]), tuple(v) + + def get_values(self, values=None): + + """ + Return the given 'values' combined with any values provided when + initialising this class. + """ + + v = list(self.filter_values or []) + list(values or []) + return self.placeholders(v), tuple(v) + def placeholders(self, values): return ", ".join(["?"] * len(values)) @@ -849,8 +878,12 @@ "Create the database table required to hold the collection." + columns = """, + """.join([("%s varchar not null" % column) for column in self.column_names or []]) + query = """\ create table %(table)s ( + %(columns)s start varchar not null, end varchar not null, uid varchar, @@ -859,7 +892,10 @@ summary varchar, organiser varchar, expires varchar - )""" % {"table" : self.table_name} + )""" % { + "table" : self.table_name, + "columns" : columns and "%s," % columns or "" + } self.cursor.execute(query) @@ -869,13 +905,22 @@ return len(self) and True or False def __iter__(self): - query = "select * from %(table)s" % {"table" : self.table_name} - self.cursor.execute(query) + condition, values = self.get_condition() + query = "select %(columns)s from %(table)s %(condition)s" % { + "columns" : ", ".join(self.period_columns), + "table" : self.table_name, + "condition" : condition + } + self.cursor.execute(query, values) return iter(map(lambda t: FreeBusyPeriod(*t), self.cursor.fetchall())) def __len__(self): - query = "select count(*) from %(table)s" % {"table" : self.table_name} - self.cursor.execute(query) + condition, values = self.get_condition() + query = "select count(*) from %(table)s %(condition)s" % { + "table" : self.table_name, + "condition" : condition + } + self.cursor.execute(query, values) result = self.cursor.fetchone() return result and result[0] or 0 @@ -890,10 +935,10 @@ self._check_mutable() - values = period.as_tuple(strings_only=True) + placeholders, values = self.get_values(period.as_tuple(string_datetimes=True)) query = "insert into %(table)s values (%(columns)s)" % { "table" : self.table_name, - "columns" : self.placeholders(values) + "columns" : placeholders } self.cursor.execute(query, values) @@ -904,12 +949,12 @@ self._check_mutable() for period in periods: - values = period.as_tuple(strings_only=True) - query = """\ -delete from %(table)s -where start = ? and end = ? and uid = ? and transp = ? and recurrenceid = ? -and summary = ? and organiser = ? and expires = ? -""" % {"table" : self.table_name} + condition, values = self.get_condition( + self.period_columns, period.as_tuple(string_datetimes=True)) + query = "delete from %(table)s %(condition)s" % { + "table" : self.table_name, + "condition" : condition + } self.cursor.execute(query, values) def remove_event_periods(self, uid, recurrenceid=None): @@ -925,13 +970,12 @@ self._check_mutable() if recurrenceid: - condition = "where uid = ? and recurrenceid = ?" - values = (uid, recurrenceid) + condition, values = self.get_condition(["uid", "recurrenceid"], [uid, recurrenceid]) else: - condition = "where uid = ?" - values = (uid,) + condition, values = self.get_condition(["uid"], [uid]) - query = "select * from %(table)s for update %(condition)s" % { + query = "select %(columns)s from %(table)s %(condition)s" % { + "columns" : ", ".join(self.period_columns), "table" : self.table_name, "condition" : condition } @@ -961,22 +1005,26 @@ self._check_mutable() if recurrenceids is None: - condition = "where uid = ? and recurrenceid is not null" - values = (uid,) + condition, values = self.get_condition(["uid"], [uid]) + extra = "recurrenceid is not null" else: - condition = "where uid = ? and recurrenceid is not null and recurrenceid not in ?" - values = (uid, recurrenceid) + condition, values = self.get_condition(["uid"], [uid]) + extra = "recurrenceid is not null and recurrenceid not in ?" + values = values + (recurrenceid,) - query = "select * from %(table)s for update %(condition)s" % { + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % { + "columns" : ", ".join(self.period_columns), "table" : self.table_name, - "condition" : condition + "condition" : condition, + "extra" : extra } self.cursor.execute(query, values) removed = self.cursor.fetchall() - query = "delete from %(table)s %(condition)s" % { + query = "delete from %(table)s %(condition)s and %(extra)s" % { "table" : self.table_name, - "condition" : condition + "condition" : condition, + "extra" : extra } self.cursor.execute(query, values) @@ -996,19 +1044,24 @@ self._check_mutable() - condition = "where uid = ? and start = ? and recurrenceid is null" - values = (uid, start) + start = format_datetime(start) + + condition, values = self.get_condition(["uid", "start"], [uid, start]) + extra = "recurrenceid is null" - query = "select * from %(table)s %(condition)s" % { + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % { + "columns" : ", ".join(self.period_columns), "table" : self.table_name, - "condition" : condition + "condition" : condition, + "extra" : extra } self.cursor.execute(query, values) removed = self.cursor.fetchall() - query = "delete from %(table)s %(condition)s" % { + query = "delete from %(table)s %(condition)s and %(extra)s" % { "table" : self.table_name, - "condition" : condition + "condition" : condition, + "extra" : extra } self.cursor.execute(query, values) @@ -1018,12 +1071,15 @@ "Return the entries in the collection at or after 'period'." - condition = "where start >= ?" - values = (format_datetime(period.get_start_point()),) + condition, values = self.get_condition() + extra = "start >= ?" + values = values + (format_datetime(period.get_start_point()),) - query = "select * from %(table)s %(condition)s" % { + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % { + "columns" : ", ".join(self.period_columns), "table" : self.table_name, - "condition" : condition + "condition" : condition, + "extra" : extra } self.cursor.execute(query, values) @@ -1033,12 +1089,15 @@ "Return the entries in the collection before 'period'." - condition = "where start < ?" - values = (format_datetime(period.get_end_point()),) + condition, values = self.get_condition() + extra = "start < ?" + values = values + (format_datetime(period.get_end_point()),) - query = "select * from %(table)s %(condition)s" % { + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % { + "columns" : ", ".join(self.period_columns), "table" : self.table_name, - "condition" : condition + "condition" : condition, + "extra" : extra } self.cursor.execute(query, values) @@ -1051,12 +1110,15 @@ 'period'. """ - condition = "where start < ? and end > ?" - values = (format_datetime(period.get_end_point()), format_datetime(period.get_start_point())) + condition, values = self.get_condition() + extra = "start < ? and end > ?" + values = values + (format_datetime(period.get_end_point()), format_datetime(period.get_start_point())) - query = "select * from %(table)s %(condition)s" % { + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % { + "columns" : ", ".join(self.period_columns), "table" : self.table_name, - "condition" : condition + "condition" : condition, + "extra" : extra } self.cursor.execute(query, values) @@ -1068,12 +1130,14 @@ self._check_mutable() - condition = "where start < ? and end > ?" - values = (format_datetime(period.get_end_point()), format_datetime(period.get_start_point())) + condition, values = self.get_condition() + extra = "start < ? and end > ?" + values = values + (format_datetime(period.get_end_point()), format_datetime(period.get_start_point())) - query = "delete from %(table)s %(condition)s" % { + query = "delete from %(table)s %(condition)s and %(extra)s" % { "table" : self.table_name, - "condition" : condition + "condition" : condition, + "extra" : extra } self.cursor.execute(query, values)