1.1 --- a/imiptools/period.py Sun Mar 06 19:23:39 2016 +0100
1.2 +++ b/imiptools/period.py Mon Mar 07 00:09:59 2016 +0100
1.3 @@ -356,17 +356,19 @@
1.4 self.organiser = organiser or None
1.5 self.expires = expires or None
1.6
1.7 - def as_tuple(self, strings_only=False):
1.8 + def as_tuple(self, strings_only=False, string_datetimes=False):
1.9
1.10 """
1.11 - Return the initialisation parameter tuple, converting false value
1.12 - parameters to strings if 'strings_only' is set to a true value.
1.13 + Return the initialisation parameter tuple, converting datetimes and
1.14 + false value parameters to strings if 'strings_only' is set to a true
1.15 + value. Otherwise, if 'string_datetimes' is set to a true value, only the
1.16 + datetime values are converted to strings.
1.17 """
1.18
1.19 null = lambda x: (strings_only and [""] or [x])[0]
1.20 return (
1.21 - strings_only and format_datetime(self.get_start_point()) or self.start,
1.22 - strings_only and format_datetime(self.get_end_point()) or self.end,
1.23 + (strings_only or string_datetimes) and format_datetime(self.get_start_point()) or self.start,
1.24 + (strings_only or string_datetimes) and format_datetime(self.get_end_point()) or self.end,
1.25 self.uid or null(self.uid),
1.26 self.transp or strings_only and "OPAQUE" or None,
1.27 self.recurrenceid or null(self.recurrenceid),
1.28 @@ -830,18 +832,45 @@
1.29 system.
1.30 """
1.31
1.32 - def __init__(self, cursor, table_name, mutable=True):
1.33 + period_columns = ["start", "end", "uid", "transp", "recurrenceid", "summary", "organiser", "expires"]
1.34 +
1.35 + def __init__(self, cursor, table_name, column_names=None, filter_values=None, mutable=True):
1.36
1.37 """
1.38 - Initialise the collection with the given 'cursor' and 'table_name'.
1.39 + Initialise the collection with the given 'cursor' and with the
1.40 + 'table_name', 'column_names' and 'filter_values' configuring the
1.41 + selection of data.
1.42 """
1.43
1.44 FreeBusyCollectionBase.__init__(self, mutable)
1.45 self.cursor = cursor
1.46 self.table_name = table_name
1.47 + self.column_names = column_names
1.48 + self.filter_values = filter_values
1.49
1.50 # Special database-related operations.
1.51
1.52 + def get_condition(self, columns=None, values=None):
1.53 +
1.54 + """
1.55 + Return a condition clause featuring the given 'columns' and 'values'
1.56 + together with any conditions provided when initialising this class.
1.57 + """
1.58 +
1.59 + c = list(self.column_names or []) + list(columns or [])
1.60 + v = list(self.filter_values or []) + list(values or [])
1.61 + return "where %s" % " and ".join([("%s = ?" % s) for s in c]), tuple(v)
1.62 +
1.63 + def get_values(self, values=None):
1.64 +
1.65 + """
1.66 + Return the given 'values' combined with any values provided when
1.67 + initialising this class.
1.68 + """
1.69 +
1.70 + v = list(self.filter_values or []) + list(values or [])
1.71 + return self.placeholders(v), tuple(v)
1.72 +
1.73 def placeholders(self, values):
1.74 return ", ".join(["?"] * len(values))
1.75
1.76 @@ -849,8 +878,12 @@
1.77
1.78 "Create the database table required to hold the collection."
1.79
1.80 + columns = """,
1.81 + """.join([("%s varchar not null" % column) for column in self.column_names or []])
1.82 +
1.83 query = """\
1.84 create table %(table)s (
1.85 + %(columns)s
1.86 start varchar not null,
1.87 end varchar not null,
1.88 uid varchar,
1.89 @@ -859,7 +892,10 @@
1.90 summary varchar,
1.91 organiser varchar,
1.92 expires varchar
1.93 - )""" % {"table" : self.table_name}
1.94 + )""" % {
1.95 + "table" : self.table_name,
1.96 + "columns" : columns and "%s," % columns or ""
1.97 + }
1.98
1.99 self.cursor.execute(query)
1.100
1.101 @@ -869,13 +905,22 @@
1.102 return len(self) and True or False
1.103
1.104 def __iter__(self):
1.105 - query = "select * from %(table)s" % {"table" : self.table_name}
1.106 - self.cursor.execute(query)
1.107 + condition, values = self.get_condition()
1.108 + query = "select %(columns)s from %(table)s %(condition)s" % {
1.109 + "columns" : ", ".join(self.period_columns),
1.110 + "table" : self.table_name,
1.111 + "condition" : condition
1.112 + }
1.113 + self.cursor.execute(query, values)
1.114 return iter(map(lambda t: FreeBusyPeriod(*t), self.cursor.fetchall()))
1.115
1.116 def __len__(self):
1.117 - query = "select count(*) from %(table)s" % {"table" : self.table_name}
1.118 - self.cursor.execute(query)
1.119 + condition, values = self.get_condition()
1.120 + query = "select count(*) from %(table)s %(condition)s" % {
1.121 + "table" : self.table_name,
1.122 + "condition" : condition
1.123 + }
1.124 + self.cursor.execute(query, values)
1.125 result = self.cursor.fetchone()
1.126 return result and result[0] or 0
1.127
1.128 @@ -890,10 +935,10 @@
1.129
1.130 self._check_mutable()
1.131
1.132 - values = period.as_tuple(strings_only=True)
1.133 + placeholders, values = self.get_values(period.as_tuple(string_datetimes=True))
1.134 query = "insert into %(table)s values (%(columns)s)" % {
1.135 "table" : self.table_name,
1.136 - "columns" : self.placeholders(values)
1.137 + "columns" : placeholders
1.138 }
1.139 self.cursor.execute(query, values)
1.140
1.141 @@ -904,12 +949,12 @@
1.142 self._check_mutable()
1.143
1.144 for period in periods:
1.145 - values = period.as_tuple(strings_only=True)
1.146 - query = """\
1.147 -delete from %(table)s
1.148 -where start = ? and end = ? and uid = ? and transp = ? and recurrenceid = ?
1.149 -and summary = ? and organiser = ? and expires = ?
1.150 -""" % {"table" : self.table_name}
1.151 + condition, values = self.get_condition(
1.152 + self.period_columns, period.as_tuple(string_datetimes=True))
1.153 + query = "delete from %(table)s %(condition)s" % {
1.154 + "table" : self.table_name,
1.155 + "condition" : condition
1.156 + }
1.157 self.cursor.execute(query, values)
1.158
1.159 def remove_event_periods(self, uid, recurrenceid=None):
1.160 @@ -925,13 +970,12 @@
1.161 self._check_mutable()
1.162
1.163 if recurrenceid:
1.164 - condition = "where uid = ? and recurrenceid = ?"
1.165 - values = (uid, recurrenceid)
1.166 + condition, values = self.get_condition(["uid", "recurrenceid"], [uid, recurrenceid])
1.167 else:
1.168 - condition = "where uid = ?"
1.169 - values = (uid,)
1.170 + condition, values = self.get_condition(["uid"], [uid])
1.171
1.172 - query = "select * from %(table)s for update %(condition)s" % {
1.173 + query = "select %(columns)s from %(table)s %(condition)s" % {
1.174 + "columns" : ", ".join(self.period_columns),
1.175 "table" : self.table_name,
1.176 "condition" : condition
1.177 }
1.178 @@ -961,22 +1005,26 @@
1.179 self._check_mutable()
1.180
1.181 if recurrenceids is None:
1.182 - condition = "where uid = ? and recurrenceid is not null"
1.183 - values = (uid,)
1.184 + condition, values = self.get_condition(["uid"], [uid])
1.185 + extra = "recurrenceid is not null"
1.186 else:
1.187 - condition = "where uid = ? and recurrenceid is not null and recurrenceid not in ?"
1.188 - values = (uid, recurrenceid)
1.189 + condition, values = self.get_condition(["uid"], [uid])
1.190 + extra = "recurrenceid is not null and recurrenceid not in ?"
1.191 + values = values + (recurrenceid,)
1.192
1.193 - query = "select * from %(table)s for update %(condition)s" % {
1.194 + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.195 + "columns" : ", ".join(self.period_columns),
1.196 "table" : self.table_name,
1.197 - "condition" : condition
1.198 + "condition" : condition,
1.199 + "extra" : extra
1.200 }
1.201 self.cursor.execute(query, values)
1.202 removed = self.cursor.fetchall()
1.203
1.204 - query = "delete from %(table)s %(condition)s" % {
1.205 + query = "delete from %(table)s %(condition)s and %(extra)s" % {
1.206 "table" : self.table_name,
1.207 - "condition" : condition
1.208 + "condition" : condition,
1.209 + "extra" : extra
1.210 }
1.211 self.cursor.execute(query, values)
1.212
1.213 @@ -996,19 +1044,24 @@
1.214
1.215 self._check_mutable()
1.216
1.217 - condition = "where uid = ? and start = ? and recurrenceid is null"
1.218 - values = (uid, start)
1.219 + start = format_datetime(start)
1.220 +
1.221 + condition, values = self.get_condition(["uid", "start"], [uid, start])
1.222 + extra = "recurrenceid is null"
1.223
1.224 - query = "select * from %(table)s %(condition)s" % {
1.225 + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.226 + "columns" : ", ".join(self.period_columns),
1.227 "table" : self.table_name,
1.228 - "condition" : condition
1.229 + "condition" : condition,
1.230 + "extra" : extra
1.231 }
1.232 self.cursor.execute(query, values)
1.233 removed = self.cursor.fetchall()
1.234
1.235 - query = "delete from %(table)s %(condition)s" % {
1.236 + query = "delete from %(table)s %(condition)s and %(extra)s" % {
1.237 "table" : self.table_name,
1.238 - "condition" : condition
1.239 + "condition" : condition,
1.240 + "extra" : extra
1.241 }
1.242 self.cursor.execute(query, values)
1.243
1.244 @@ -1018,12 +1071,15 @@
1.245
1.246 "Return the entries in the collection at or after 'period'."
1.247
1.248 - condition = "where start >= ?"
1.249 - values = (format_datetime(period.get_start_point()),)
1.250 + condition, values = self.get_condition()
1.251 + extra = "start >= ?"
1.252 + values = values + (format_datetime(period.get_start_point()),)
1.253
1.254 - query = "select * from %(table)s %(condition)s" % {
1.255 + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.256 + "columns" : ", ".join(self.period_columns),
1.257 "table" : self.table_name,
1.258 - "condition" : condition
1.259 + "condition" : condition,
1.260 + "extra" : extra
1.261 }
1.262 self.cursor.execute(query, values)
1.263
1.264 @@ -1033,12 +1089,15 @@
1.265
1.266 "Return the entries in the collection before 'period'."
1.267
1.268 - condition = "where start < ?"
1.269 - values = (format_datetime(period.get_end_point()),)
1.270 + condition, values = self.get_condition()
1.271 + extra = "start < ?"
1.272 + values = values + (format_datetime(period.get_end_point()),)
1.273
1.274 - query = "select * from %(table)s %(condition)s" % {
1.275 + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.276 + "columns" : ", ".join(self.period_columns),
1.277 "table" : self.table_name,
1.278 - "condition" : condition
1.279 + "condition" : condition,
1.280 + "extra" : extra
1.281 }
1.282 self.cursor.execute(query, values)
1.283
1.284 @@ -1051,12 +1110,15 @@
1.285 'period'.
1.286 """
1.287
1.288 - condition = "where start < ? and end > ?"
1.289 - values = (format_datetime(period.get_end_point()), format_datetime(period.get_start_point()))
1.290 + condition, values = self.get_condition()
1.291 + extra = "start < ? and end > ?"
1.292 + values = values + (format_datetime(period.get_end_point()), format_datetime(period.get_start_point()))
1.293
1.294 - query = "select * from %(table)s %(condition)s" % {
1.295 + query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.296 + "columns" : ", ".join(self.period_columns),
1.297 "table" : self.table_name,
1.298 - "condition" : condition
1.299 + "condition" : condition,
1.300 + "extra" : extra
1.301 }
1.302 self.cursor.execute(query, values)
1.303
1.304 @@ -1068,12 +1130,14 @@
1.305
1.306 self._check_mutable()
1.307
1.308 - condition = "where start < ? and end > ?"
1.309 - values = (format_datetime(period.get_end_point()), format_datetime(period.get_start_point()))
1.310 + condition, values = self.get_condition()
1.311 + extra = "start < ? and end > ?"
1.312 + values = values + (format_datetime(period.get_end_point()), format_datetime(period.get_start_point()))
1.313
1.314 - query = "delete from %(table)s %(condition)s" % {
1.315 + query = "delete from %(table)s %(condition)s and %(extra)s" % {
1.316 "table" : self.table_name,
1.317 - "condition" : condition
1.318 + "condition" : condition,
1.319 + "extra" : extra
1.320 }
1.321 self.cursor.execute(query, values)
1.322
