1.1 --- a/imiptools/period.py Mon Mar 07 17:35:01 2016 +0100
1.2 +++ b/imiptools/period.py Mon Mar 07 23:31:35 2016 +0100
1.3 @@ -28,6 +28,7 @@
1.4 get_start_of_day, \
1.5 get_tzid, \
1.6 to_timezone, to_utc_datetime
1.7 +from imiptools.sql import DatabaseOperations
1.8
1.9 def ifnone(x, y):
1.10 if x is None: return y
1.11 @@ -825,14 +826,14 @@
1.12 for fb in overlapping:
1.13 self.periods.remove(fb)
1.14
1.15 -class FreeBusyDatabaseCollection(FreeBusyCollectionBase):
1.16 +class FreeBusyDatabaseCollection(FreeBusyCollectionBase, DatabaseOperations):
1.17
1.18 """
1.19 An abstraction for a collection of free/busy periods stored in a database
1.20 system.
1.21 """
1.22
1.23 - period_columns = ["start", "end", "uid", "transp", "recurrenceid", "summary", "organiser", "expires"]
1.24 + period_columns = ["start", "end", "object_uid", "transp", "object_recurrenceid", "summary", "organiser", "expires"]
1.25
1.26 def __init__(self, cursor, table_name, column_names=None, filter_values=None, mutable=True):
1.27
1.28 @@ -843,61 +844,9 @@
1.29 """
1.30
1.31 FreeBusyCollectionBase.__init__(self, mutable)
1.32 + DatabaseOperations.__init__(self, column_names, filter_values)
1.33 self.cursor = cursor
1.34 self.table_name = table_name
1.35 - self.column_names = column_names
1.36 - self.filter_values = filter_values
1.37 -
1.38 - # Special database-related operations.
1.39 -
1.40 - def get_condition(self, columns=None, values=None):
1.41 -
1.42 - """
1.43 - Return a condition clause featuring the given 'columns' and 'values'
1.44 - together with any conditions provided when initialising this class.
1.45 - """
1.46 -
1.47 - c = list(self.column_names or []) + list(columns or [])
1.48 - v = list(self.filter_values or []) + list(values or [])
1.49 - return "where %s" % " and ".join([("%s = ?" % s) for s in c]), tuple(v)
1.50 -
1.51 - def get_values(self, values=None):
1.52 -
1.53 - """
1.54 - Return the given 'values' combined with any values provided when
1.55 - initialising this class.
1.56 - """
1.57 -
1.58 - v = list(self.filter_values or []) + list(values or [])
1.59 - return self.placeholders(v), tuple(v)
1.60 -
1.61 - def placeholders(self, values):
1.62 - return ", ".join(["?"] * len(values))
1.63 -
1.64 - def initialise(self):
1.65 -
1.66 - "Create the database table required to hold the collection."
1.67 -
1.68 - columns = """,
1.69 - """.join([("%s varchar not null" % column) for column in self.column_names or []])
1.70 -
1.71 - query = """\
1.72 -create table %(table)s (
1.73 - %(columns)s
1.74 - start varchar not null,
1.75 - end varchar not null,
1.76 - uid varchar,
1.77 - transp varchar,
1.78 - recurrenceid varchar,
1.79 - summary varchar,
1.80 - organiser varchar,
1.81 - expires varchar
1.82 - )""" % {
1.83 - "table" : self.table_name,
1.84 - "columns" : columns and "%s," % columns or ""
1.85 - }
1.86 -
1.87 - self.cursor.execute(query)
1.88
1.89 # List emulation methods.
1.90
1.91 @@ -905,21 +854,19 @@
1.92 return len(self) and True or False
1.93
1.94 def __iter__(self):
1.95 - condition, values = self.get_condition()
1.96 - query = "select %(columns)s from %(table)s %(condition)s" % {
1.97 - "columns" : ", ".join(self.period_columns),
1.98 - "table" : self.table_name,
1.99 - "condition" : condition
1.100 - }
1.101 + query, values = self.get_query(
1.102 + "select %(columns)s from %(table)s :condition" % {
1.103 + "columns" : ", ".join(self.period_columns),
1.104 + "table" : self.table_name
1.105 + })
1.106 self.cursor.execute(query, values)
1.107 return iter(map(lambda t: FreeBusyPeriod(*t), self.cursor.fetchall()))
1.108
1.109 def __len__(self):
1.110 - condition, values = self.get_condition()
1.111 - query = "select count(*) from %(table)s %(condition)s" % {
1.112 - "table" : self.table_name,
1.113 - "condition" : condition
1.114 - }
1.115 + query, values = self.get_query(
1.116 + "select count(*) from %(table)s :condition" % {
1.117 + "table" : self.table_name
1.118 + })
1.119 self.cursor.execute(query, values)
1.120 result = self.cursor.fetchone()
1.121 return result and result[0] or 0
1.122 @@ -935,11 +882,14 @@
1.123
1.124 self._check_mutable()
1.125
1.126 - placeholders, values = self.get_values(period.as_tuple(string_datetimes=True))
1.127 - query = "insert into %(table)s values (%(columns)s)" % {
1.128 - "table" : self.table_name,
1.129 - "columns" : placeholders
1.130 - }
1.131 + columns, values = self.period_columns, period.as_tuple(string_datetimes=True)
1.132 +
1.133 + query, values = self.get_query(
1.134 + "insert into %(table)s (:columns) values (:values)" % {
1.135 + "table" : self.table_name
1.136 + },
1.137 + columns, values)
1.138 +
1.139 self.cursor.execute(query, values)
1.140
1.141 def remove_periods(self, periods):
1.142 @@ -949,12 +899,11 @@
1.143 self._check_mutable()
1.144
1.145 for period in periods:
1.146 - condition, values = self.get_condition(
1.147 + query, values = self.get_query(
1.148 + "delete from %(table)s :condition" % {
1.149 + "table" : self.table_name
1.150 + },
1.151 self.period_columns, period.as_tuple(string_datetimes=True))
1.152 - query = "delete from %(table)s %(condition)s" % {
1.153 - "table" : self.table_name,
1.154 - "condition" : condition
1.155 - }
1.156 self.cursor.execute(query, values)
1.157
1.158 def remove_event_periods(self, uid, recurrenceid=None):
1.159 @@ -970,15 +919,17 @@
1.160 self._check_mutable()
1.161
1.162 if recurrenceid:
1.163 - condition, values = self.get_condition(["uid", "recurrenceid"], [uid, recurrenceid])
1.164 + columns, values = ["object_uid", "object_recurrenceid"], [uid, recurrenceid]
1.165 else:
1.166 - condition, values = self.get_condition(["uid"], [uid])
1.167 + columns, values = ["object_uid"], [uid]
1.168
1.169 - query = "select %(columns)s from %(table)s %(condition)s" % {
1.170 - "columns" : ", ".join(self.period_columns),
1.171 - "table" : self.table_name,
1.172 - "condition" : condition
1.173 - }
1.174 + query, values = self.get_query(
1.175 + "select %(columns)s from %(table)s :condition" % {
1.176 + "columns" : ", ".join(self.period_columns),
1.177 + "table" : self.table_name
1.178 + },
1.179 + columns, values)
1.180 +
1.181 self.cursor.execute(query, values)
1.182 removed = self.cursor.fetchall()
1.183
1.184 @@ -1005,27 +956,26 @@
1.185 self._check_mutable()
1.186
1.187 if recurrenceids is None:
1.188 - condition, values = self.get_condition(["uid"], [uid])
1.189 - extra = "recurrenceid is not null"
1.190 + columns, values = ["object_uid", "object_recurrenceid is not null"], [uid]
1.191 else:
1.192 - condition, values = self.get_condition(["uid"], [uid])
1.193 - extra = "recurrenceid is not null and recurrenceid not in ?"
1.194 - values = values + (recurrenceid,)
1.195 + columns, values = ["object_uid", "object_recurrenceid not in", "object_recurrenceid is not null"], [uid, recurrenceid]
1.196
1.197 - query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.198 - "columns" : ", ".join(self.period_columns),
1.199 - "table" : self.table_name,
1.200 - "condition" : condition,
1.201 - "extra" : extra
1.202 - }
1.203 + query, values = self.get_query(
1.204 + "select %(columns)s from %(table)s :condition" % {
1.205 + "columns" : ", ".join(self.period_columns),
1.206 + "table" : self.table_name
1.207 + },
1.208 + columns, values)
1.209 +
1.210 self.cursor.execute(query, values)
1.211 removed = self.cursor.fetchall()
1.212
1.213 - query = "delete from %(table)s %(condition)s and %(extra)s" % {
1.214 - "table" : self.table_name,
1.215 - "condition" : condition,
1.216 - "extra" : extra
1.217 - }
1.218 + query = self.get_query(
1.219 + "delete from %(table)s %(condition)s" % {
1.220 + "table" : self.table_name
1.221 + },
1.222 + columns, values)
1.223 +
1.224 self.cursor.execute(query, values)
1.225
1.226 return map(lambda t: FreeBusyPeriod(*t), removed)
1.227 @@ -1046,23 +996,24 @@
1.228
1.229 start = format_datetime(start)
1.230
1.231 - condition, values = self.get_condition(["uid", "start"], [uid, start])
1.232 - extra = "recurrenceid is null"
1.233 + columns, values = ["object_uid", "start", "object_recurrenceid is null"], [uid, start]
1.234
1.235 - query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.236 - "columns" : ", ".join(self.period_columns),
1.237 - "table" : self.table_name,
1.238 - "condition" : condition,
1.239 - "extra" : extra
1.240 - }
1.241 + query, values = self.get_query(
1.242 + "select %(columns)s from %(table)s :condition" % {
1.243 + "columns" : ", ".join(self.period_columns),
1.244 + "table" : self.table_name
1.245 + },
1.246 + columns, values)
1.247 +
1.248 self.cursor.execute(query, values)
1.249 removed = self.cursor.fetchall()
1.250
1.251 - query = "delete from %(table)s %(condition)s and %(extra)s" % {
1.252 - "table" : self.table_name,
1.253 - "condition" : condition,
1.254 - "extra" : extra
1.255 - }
1.256 + query, values = self.get_query(
1.257 + "delete from %(table)s :condition" % {
1.258 + "table" : self.table_name
1.259 + },
1.260 + columns, values)
1.261 +
1.262 self.cursor.execute(query, values)
1.263
1.264 return map(lambda t: FreeBusyPeriod(*t), removed)
1.265 @@ -1071,16 +1022,15 @@
1.266
1.267 "Return the entries in the collection at or after 'period'."
1.268
1.269 - condition, values = self.get_condition()
1.270 - extra = "start >= ?"
1.271 - values = values + (format_datetime(period.get_start_point()),)
1.272 + columns, values = ["start >= ?"], [format_datetime(period.get_start_point())]
1.273
1.274 - query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.275 - "columns" : ", ".join(self.period_columns),
1.276 - "table" : self.table_name,
1.277 - "condition" : condition,
1.278 - "extra" : extra
1.279 - }
1.280 + query, values = self.get_query(
1.281 + "select %(columns)s from %(table)s :condition" % {
1.282 + "columns" : ", ".join(self.period_columns),
1.283 + "table" : self.table_name
1.284 + },
1.285 + columns, values)
1.286 +
1.287 self.cursor.execute(query, values)
1.288
1.289 return map(lambda t: FreeBusyPeriod(*t), self.cursor.fetchall())
1.290 @@ -1089,16 +1039,15 @@
1.291
1.292 "Return the entries in the collection before 'period'."
1.293
1.294 - condition, values = self.get_condition()
1.295 - extra = "start < ?"
1.296 - values = values + (format_datetime(period.get_end_point()),)
1.297 + columns, values = ["start < ?"], [format_datetime(period.get_end_point())]
1.298
1.299 - query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.300 - "columns" : ", ".join(self.period_columns),
1.301 - "table" : self.table_name,
1.302 - "condition" : condition,
1.303 - "extra" : extra
1.304 - }
1.305 + query, values = self.get_query(
1.306 + "select %(columns)s from %(table)s :condition" % {
1.307 + "columns" : ", ".join(self.period_columns),
1.308 + "table" : self.table_name
1.309 + },
1.310 + columns, values)
1.311 +
1.312 self.cursor.execute(query, values)
1.313
1.314 return map(lambda t: FreeBusyPeriod(*t), self.cursor.fetchall())
1.315 @@ -1110,16 +1059,15 @@
1.316 'period'.
1.317 """
1.318
1.319 - condition, values = self.get_condition()
1.320 - extra = "start < ? and end > ?"
1.321 - values = values + (format_datetime(period.get_end_point()), format_datetime(period.get_start_point()))
1.322 + columns, values = ["start < ?", "end > ?"], [format_datetime(period.get_end_point()), format_datetime(period.get_start_point())]
1.323
1.324 - query = "select %(columns)s from %(table)s %(condition)s and %(extra)s" % {
1.325 - "columns" : ", ".join(self.period_columns),
1.326 - "table" : self.table_name,
1.327 - "condition" : condition,
1.328 - "extra" : extra
1.329 - }
1.330 + query, values = self.get_query(
1.331 + "select %(columns)s from %(table)s :condition" % {
1.332 + "columns" : ", ".join(self.period_columns),
1.333 + "table" : self.table_name
1.334 + },
1.335 + columns, values)
1.336 +
1.337 self.cursor.execute(query, values)
1.338
1.339 return map(lambda t: FreeBusyPeriod(*t), self.cursor.fetchall())
1.340 @@ -1130,15 +1078,14 @@
1.341
1.342 self._check_mutable()
1.343
1.344 - condition, values = self.get_condition()
1.345 - extra = "start < ? and end > ?"
1.346 - values = values + (format_datetime(period.get_end_point()), format_datetime(period.get_start_point()))
1.347 + columns, values = ["start < ?", "end > ?"], [format_datetime(period.get_end_point()), format_datetime(period.get_start_point())]
1.348
1.349 - query = "delete from %(table)s %(condition)s and %(extra)s" % {
1.350 - "table" : self.table_name,
1.351 - "condition" : condition,
1.352 - "extra" : extra
1.353 - }
1.354 + query, values = self.get_query(
1.355 + "delete from %(table)s :condition" % {
1.356 + "table" : self.table_name
1.357 + },
1.358 + columns, values)
1.359 +
1.360 self.cursor.execute(query, values)
1.361
1.362 # Period layout.