3.1 --- a/imiptools/stores/database/common.py Sat Oct 08 23:16:53 2016 +0200
3.2 +++ b/imiptools/stores/database/common.py Sat Oct 08 23:45:25 2016 +0200
3.3 @@ -44,21 +44,40 @@
3.4 def release_lock(self, user):
3.5 pass
3.6
3.7 + def with_tables(self, query):
3.8 +
3.9 + "Parameterise tables in the given 'query' for common operations."
3.10 +
3.11 + return query % {
3.12 + "objects" : self.objects_table,
3.13 + "recurrences" : self.recurrences_table,
3.14 + "freebusy_other" : self.freebusy_other_table,
3.15 + "freebusy_providers" : self.freebusy_providers_table,
3.16 + "freebusy_provider_datetimes" : self.freebusy_provider_datetimes_table,
3.17 + }
3.18 +
3.19 class DatabaseStore(DatabaseStoreBase, StoreBase):
3.20
3.21 "A database store of tabular free/busy data and objects."
3.22
3.23 + objects_table = "objects"
3.24 + recurrences_table = "recurrences"
3.25 + freebusy_other_table = "freebusy_other"
3.26 + freebusy_providers_table = "freebusy_providers"
3.27 + freebusy_provider_datetimes_table = "freebusy_provider_datetimes"
3.28 +
3.29 # User discovery.
3.30
3.31 def get_users(self):
3.32
3.33 "Return a list of users."
3.34
3.35 - query = "select distinct store_user from (" \
3.36 + query = self.with_tables(
3.37 + "select distinct store_user from (" \
3.38 "select store_user from freebusy " \
3.39 - "union all select store_user from objects " \
3.40 - "union all select store_user from recurrences" \
3.41 - ") as users"
3.42 + "union all select store_user from %(objects)s " \
3.43 + "union all select store_user from %(recurrences)s" \
3.44 + ") as users")
3.45 self.cursor.execute(query)
3.46 return [r[0] for r in self.cursor.fetchall()]
3.47
3.48 @@ -77,10 +96,10 @@
3.49 columns += ["store_user"]
3.50 values += [user]
3.51
3.52 - query, values = self.get_query(
3.53 - "select object_uid, null as object_recurrenceid from objects :condition "
3.54 + query, values = self.get_query(self.with_tables(
3.55 + "select object_uid, null as object_recurrenceid from %(objects)s :condition "
3.56 "union all "
3.57 - "select object_uid, object_recurrenceid from recurrences :condition",
3.58 + "select object_uid, object_recurrenceid from %(recurrences)s :condition"),
3.59 columns, values)
3.60
3.61 self.cursor.execute(query, values)
3.62 @@ -95,8 +114,8 @@
3.63 columns += ["store_user"]
3.64 values += [user]
3.65
3.66 - query, values = self.get_query(
3.67 - "select object_uid from objects :condition",
3.68 + query, values = self.get_query(self.with_tables(
3.69 + "select object_uid from %(objects)s :condition"),
3.70 columns, values)
3.71
3.72 self.cursor.execute(query, values)
3.73 @@ -143,8 +162,8 @@
3.74 columns = ["store_user", "object_uid"]
3.75 values = [user, uid]
3.76
3.77 - query, values = self.get_query(
3.78 - "select object_text from objects :condition",
3.79 + query, values = self.get_query(self.with_tables(
3.80 + "select object_text from %(objects)s :condition"),
3.81 columns, values)
3.82
3.83 self.cursor.execute(query, values)
3.84 @@ -160,8 +179,8 @@
3.85 setcolumns = ["object_text", "status"]
3.86 setvalues = [to_string(node, "utf-8"), "active"]
3.87
3.88 - query, values = self.get_query(
3.89 - "update objects :set :condition",
3.90 + query, values = self.get_query(self.with_tables(
3.91 + "update %(objects)s :set :condition"),
3.92 columns, values, setcolumns, setvalues)
3.93
3.94 self.cursor.execute(query, values)
3.95 @@ -172,8 +191,8 @@
3.96 columns = ["store_user", "object_uid", "object_text", "status"]
3.97 values = [user, uid, to_string(node, "utf-8"), "active"]
3.98
3.99 - query, values = self.get_query(
3.100 - "insert into objects (:columns) values (:values)",
3.101 + query, values = self.get_query(self.with_tables(
3.102 + "insert into %(objects)s (:columns) values (:values)"),
3.103 columns, values)
3.104
3.105 self.cursor.execute(query, values)
3.106 @@ -186,8 +205,8 @@
3.107 columns = ["store_user", "object_uid"]
3.108 values = [user, uid]
3.109
3.110 - query, values = self.get_query(
3.111 - "delete from objects :condition",
3.112 + query, values = self.get_query(self.with_tables(
3.113 + "delete from %(objects)s :condition"),
3.114 columns, values)
3.115
3.116 self.cursor.execute(query, values)
3.117 @@ -203,8 +222,8 @@
3.118 columns = ["store_user", "object_uid", "status"]
3.119 values = [user, uid, "active"]
3.120
3.121 - query, values = self.get_query(
3.122 - "select object_recurrenceid from recurrences :condition",
3.123 + query, values = self.get_query(self.with_tables(
3.124 + "select object_recurrenceid from %(recurrences)s :condition"),
3.125 columns, values)
3.126
3.127 self.cursor.execute(query, values)
3.128 @@ -220,8 +239,8 @@
3.129 columns = ["store_user", "object_uid", "status"]
3.130 values = [user, uid, "cancelled"]
3.131
3.132 - query, values = self.get_query(
3.133 - "select object_recurrenceid from recurrences :condition",
3.134 + query, values = self.get_query(self.with_tables(
3.135 + "select object_recurrenceid from %(recurrences)s :condition"),
3.136 columns, values)
3.137
3.138 self.cursor.execute(query, values)
3.139 @@ -237,8 +256,8 @@
3.140 columns = ["store_user", "object_uid", "object_recurrenceid"]
3.141 values = [user, uid, recurrenceid]
3.142
3.143 - query, values = self.get_query(
3.144 - "select object_text from recurrences :condition",
3.145 + query, values = self.get_query(self.with_tables(
3.146 + "select object_text from %(recurrences)s :condition"),
3.147 columns, values)
3.148
3.149 self.cursor.execute(query, values)
3.150 @@ -254,8 +273,8 @@
3.151 setcolumns = ["object_text", "status"]
3.152 setvalues = [to_string(node, "utf-8"), "active"]
3.153
3.154 - query, values = self.get_query(
3.155 - "update recurrences :set :condition",
3.156 + query, values = self.get_query(self.with_tables(
3.157 + "update %(recurrences)s :set :condition"),
3.158 columns, values, setcolumns, setvalues)
3.159
3.160 self.cursor.execute(query, values)
3.161 @@ -266,8 +285,8 @@
3.162 columns = ["store_user", "object_uid", "object_recurrenceid", "object_text", "status"]
3.163 values = [user, uid, recurrenceid, to_string(node, "utf-8"), "active"]
3.164
3.165 - query, values = self.get_query(
3.166 - "insert into recurrences (:columns) values (:values)",
3.167 + query, values = self.get_query(self.with_tables(
3.168 + "insert into %(recurrences)s (:columns) values (:values)"),
3.169 columns, values)
3.170
3.171 self.cursor.execute(query, values)
3.172 @@ -283,8 +302,8 @@
3.173 columns = ["store_user", "object_uid", "object_recurrenceid"]
3.174 values = [user, uid, recurrenceid]
3.175
3.176 - query, values = self.get_query(
3.177 - "delete from recurrences :condition",
3.178 + query, values = self.get_query(self.with_tables(
3.179 + "delete from %(recurrences)s :condition"),
3.180 columns, values)
3.181
3.182 self.cursor.execute(query, values)
3.183 @@ -300,8 +319,8 @@
3.184 columns = ["store_user", "object_uid"]
3.185 values = [user, uid]
3.186
3.187 - query, values = self.get_query(
3.188 - "delete from recurrences :condition",
3.189 + query, values = self.get_query(self.with_tables(
3.190 + "delete from %(recurrences)s :condition"),
3.191 columns, values)
3.192
3.193 self.cursor.execute(query, values)
3.194 @@ -332,18 +351,18 @@
3.195 "Get the table providing events for any specified 'dirname'."
3.196
3.197 if dirname == "counters":
3.198 - return "countered_objects"
3.199 + return "countered_%s" % self.objects_table
3.200 else:
3.201 - return "objects"
3.202 + return self.objects_table
3.203
3.204 def get_recurrence_table(self, dirname=None):
3.205
3.206 "Get the table providing recurrences for any specified 'dirname'."
3.207
3.208 if dirname == "counters":
3.209 - return "countered_recurrences"
3.210 + return "countered_%s" % self.recurrences_table
3.211 else:
3.212 - return "recurrences"
3.213 + return self.recurrences_table
3.214
3.215 # Free/busy period providers, upon extension of the free/busy records.
3.216
3.217 @@ -359,8 +378,8 @@
3.218 columns = ["store_user"]
3.219 values = [user]
3.220
3.221 - query, values = self.get_query(
3.222 - "select object_uid, object_recurrenceid from freebusy_providers :condition",
3.223 + query, values = self.get_query(self.with_tables(
3.224 + "select object_uid, object_recurrenceid from %(freebusy_providers)s :condition"),
3.225 columns, values)
3.226
3.227 self.cursor.execute(query, values)
3.228 @@ -369,8 +388,8 @@
3.229 columns = ["store_user"]
3.230 values = [user]
3.231
3.232 - query, values = self.get_query(
3.233 - "select start from freebusy_provider_datetimes :condition",
3.234 + query, values = self.get_query(self.with_tables(
3.235 + "select start from %(freebusy_provider_datetimes)s :condition"),
3.236 columns, values)
3.237
3.238 self.cursor.execute(query, values)
3.239 @@ -388,8 +407,8 @@
3.240 columns = ["store_user"]
3.241 values = [user]
3.242
3.243 - query, values = self.get_query(
3.244 - "delete from freebusy_providers :condition",
3.245 + query, values = self.get_query(self.with_tables(
3.246 + "delete from %(freebusy_providers)s :condition"),
3.247 columns, values)
3.248
3.249 self.cursor.execute(query, values)
3.250 @@ -399,8 +418,8 @@
3.251 for uid, recurrenceid in t:
3.252 values = [user, uid, recurrenceid]
3.253
3.254 - query, values = self.get_query(
3.255 - "insert into freebusy_providers (:columns) values (:values)",
3.256 + query, values = self.get_query(self.with_tables(
3.257 + "insert into %(freebusy_providers)s (:columns) values (:values)"),
3.258 columns, values)
3.259
3.260 self.cursor.execute(query, values)
3.261 @@ -410,8 +429,8 @@
3.262 setcolumns = ["start"]
3.263 setvalues = [dt_string]
3.264
3.265 - query, values = self.get_query(
3.266 - "update freebusy_provider_datetimes :set :condition",
3.267 + query, values = self.get_query(self.with_tables(
3.268 + "update %(freebusy_provider_datetimes)s :set :condition"),
3.269 columns, values, setcolumns, setvalues)
3.270
3.271 self.cursor.execute(query, values)
3.272 @@ -422,8 +441,8 @@
3.273 columns = ["store_user", "start"]
3.274 values = [user, dt_string]
3.275
3.276 - query, values = self.get_query(
3.277 - "insert into freebusy_provider_datetimes (:columns) values (:values)",
3.278 + query, values = self.get_query(self.with_tables(
3.279 + "insert into %(freebusy_provider_datetimes)s (:columns) values (:values)"),
3.280 columns, values)
3.281
3.282 self.cursor.execute(query, values)
3.283 @@ -443,9 +462,8 @@
3.284
3.285 "For the given 'user', get free/busy details for the 'other' user."
3.286
3.287 - table = "freebusy_other"
3.288 cls = cls or FreeBusyDatabaseCollection
3.289 - return cls(self.cursor, table, ["store_user", "other"], [user, other], mutable, self.paramstyle)
3.290 + return cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], mutable, self.paramstyle)
3.291
3.292 def set_freebusy(self, user, freebusy, name=None, cls=None):
3.293
3.294 @@ -464,11 +482,10 @@
3.295
3.296 "For the given 'user', set 'freebusy' details for the 'other' user."
3.297
3.298 - table = "freebusy_other"
3.299 cls = cls or FreeBusyDatabaseCollection
3.300
3.301 - if not isinstance(freebusy, cls) or freebusy.table_name != table:
3.302 - fbc = cls(self.cursor, table, ["store_user", "other"], [user, other], True, self.paramstyle)
3.303 + if not isinstance(freebusy, cls) or freebusy.table_name != self.freebusy_other_table:
3.304 + fbc = cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], True, self.paramstyle)
3.305 fbc += freebusy
3.306
3.307 return True
3.308 @@ -483,8 +500,8 @@
3.309 columns = ["store_user"]
3.310 values = [user]
3.311
3.312 - query, values = self.get_query(
3.313 - "select distinct other from freebusy_other :condition",
3.314 + query, values = self.get_query(self.with_tables(
3.315 + "select distinct other from %(freebusy_other)s :condition"),
3.316 columns, values)
3.317
3.318 self.cursor.execute(query, values)
3.319 @@ -818,16 +835,22 @@
3.320
3.321 "A journal system to support quotas."
3.322
3.323 + objects_table = "journal_objects"
3.324 + recurrences_table = "journal_recurrences"
3.325 + freebusy_other_table = "journal_freebusy_other"
3.326 + freebusy_providers_table = "journal_freebusy_providers"
3.327 + freebusy_provider_datetimes_table = "journal_freebusy_provider_datetimes"
3.328 +
3.329 # Quota and user identity/group discovery.
3.330
3.331 def get_quotas(self):
3.332
3.333 "Return a list of quotas."
3.334
3.335 - query = "select distinct quota from (" \
3.336 - "select distinct store_user as quota from freebusy_other " \
3.337 + query = self.with_tables("select distinct quota from (" \
3.338 + "select distinct store_user as quota from %(freebusy_other)s " \
3.339 "union all select quota from quota_limits" \
3.340 - ") as quotas"
3.341 + ") as quotas")
3.342 self.cursor.execute(query)
3.343 return [r[0] for r in self.cursor.fetchall()]
3.344
3.345 @@ -838,11 +861,11 @@
3.346 columns = ["quota"]
3.347 values = [quota]
3.348
3.349 - query, values = self.get_query(
3.350 + query, values = self.get_query(self.with_tables(
3.351 "select distinct user_group from (" \
3.352 - "select distinct other as user_group from freebusy_other :condition " \
3.353 + "select distinct other as user_group from %(freebusy_other)s :condition " \
3.354 "union all select user_group from quota_delegates :condition" \
3.355 - ") as users",
3.356 + ") as users"),
3.357 columns, values)
3.358
3.359 self.cursor.execute(query, values)