1.1 --- a/imiptools/stores/database/common.py Sat Oct 08 23:16:53 2016 +0200
1.2 +++ b/imiptools/stores/database/common.py Sat Oct 08 23:45:25 2016 +0200
1.3 @@ -44,21 +44,40 @@
1.4 def release_lock(self, user):
1.5 pass
1.6
1.7 + def with_tables(self, query):
1.8 +
1.9 + "Parameterise tables in the given 'query' for common operations."
1.10 +
1.11 + return query % {
1.12 + "objects" : self.objects_table,
1.13 + "recurrences" : self.recurrences_table,
1.14 + "freebusy_other" : self.freebusy_other_table,
1.15 + "freebusy_providers" : self.freebusy_providers_table,
1.16 + "freebusy_provider_datetimes" : self.freebusy_provider_datetimes_table,
1.17 + }
1.18 +
1.19 class DatabaseStore(DatabaseStoreBase, StoreBase):
1.20
1.21 "A database store of tabular free/busy data and objects."
1.22
1.23 + objects_table = "objects"
1.24 + recurrences_table = "recurrences"
1.25 + freebusy_other_table = "freebusy_other"
1.26 + freebusy_providers_table = "freebusy_providers"
1.27 + freebusy_provider_datetimes_table = "freebusy_provider_datetimes"
1.28 +
1.29 # User discovery.
1.30
1.31 def get_users(self):
1.32
1.33 "Return a list of users."
1.34
1.35 - query = "select distinct store_user from (" \
1.36 + query = self.with_tables(
1.37 + "select distinct store_user from (" \
1.38 "select store_user from freebusy " \
1.39 - "union all select store_user from objects " \
1.40 - "union all select store_user from recurrences" \
1.41 - ") as users"
1.42 + "union all select store_user from %(objects)s " \
1.43 + "union all select store_user from %(recurrences)s" \
1.44 + ") as users")
1.45 self.cursor.execute(query)
1.46 return [r[0] for r in self.cursor.fetchall()]
1.47
1.48 @@ -77,10 +96,10 @@
1.49 columns += ["store_user"]
1.50 values += [user]
1.51
1.52 - query, values = self.get_query(
1.53 - "select object_uid, null as object_recurrenceid from objects :condition "
1.54 + query, values = self.get_query(self.with_tables(
1.55 + "select object_uid, null as object_recurrenceid from %(objects)s :condition "
1.56 "union all "
1.57 - "select object_uid, object_recurrenceid from recurrences :condition",
1.58 + "select object_uid, object_recurrenceid from %(recurrences)s :condition"),
1.59 columns, values)
1.60
1.61 self.cursor.execute(query, values)
1.62 @@ -95,8 +114,8 @@
1.63 columns += ["store_user"]
1.64 values += [user]
1.65
1.66 - query, values = self.get_query(
1.67 - "select object_uid from objects :condition",
1.68 + query, values = self.get_query(self.with_tables(
1.69 + "select object_uid from %(objects)s :condition"),
1.70 columns, values)
1.71
1.72 self.cursor.execute(query, values)
1.73 @@ -143,8 +162,8 @@
1.74 columns = ["store_user", "object_uid"]
1.75 values = [user, uid]
1.76
1.77 - query, values = self.get_query(
1.78 - "select object_text from objects :condition",
1.79 + query, values = self.get_query(self.with_tables(
1.80 + "select object_text from %(objects)s :condition"),
1.81 columns, values)
1.82
1.83 self.cursor.execute(query, values)
1.84 @@ -160,8 +179,8 @@
1.85 setcolumns = ["object_text", "status"]
1.86 setvalues = [to_string(node, "utf-8"), "active"]
1.87
1.88 - query, values = self.get_query(
1.89 - "update objects :set :condition",
1.90 + query, values = self.get_query(self.with_tables(
1.91 + "update %(objects)s :set :condition"),
1.92 columns, values, setcolumns, setvalues)
1.93
1.94 self.cursor.execute(query, values)
1.95 @@ -172,8 +191,8 @@
1.96 columns = ["store_user", "object_uid", "object_text", "status"]
1.97 values = [user, uid, to_string(node, "utf-8"), "active"]
1.98
1.99 - query, values = self.get_query(
1.100 - "insert into objects (:columns) values (:values)",
1.101 + query, values = self.get_query(self.with_tables(
1.102 + "insert into %(objects)s (:columns) values (:values)"),
1.103 columns, values)
1.104
1.105 self.cursor.execute(query, values)
1.106 @@ -186,8 +205,8 @@
1.107 columns = ["store_user", "object_uid"]
1.108 values = [user, uid]
1.109
1.110 - query, values = self.get_query(
1.111 - "delete from objects :condition",
1.112 + query, values = self.get_query(self.with_tables(
1.113 + "delete from %(objects)s :condition"),
1.114 columns, values)
1.115
1.116 self.cursor.execute(query, values)
1.117 @@ -203,8 +222,8 @@
1.118 columns = ["store_user", "object_uid", "status"]
1.119 values = [user, uid, "active"]
1.120
1.121 - query, values = self.get_query(
1.122 - "select object_recurrenceid from recurrences :condition",
1.123 + query, values = self.get_query(self.with_tables(
1.124 + "select object_recurrenceid from %(recurrences)s :condition"),
1.125 columns, values)
1.126
1.127 self.cursor.execute(query, values)
1.128 @@ -220,8 +239,8 @@
1.129 columns = ["store_user", "object_uid", "status"]
1.130 values = [user, uid, "cancelled"]
1.131
1.132 - query, values = self.get_query(
1.133 - "select object_recurrenceid from recurrences :condition",
1.134 + query, values = self.get_query(self.with_tables(
1.135 + "select object_recurrenceid from %(recurrences)s :condition"),
1.136 columns, values)
1.137
1.138 self.cursor.execute(query, values)
1.139 @@ -237,8 +256,8 @@
1.140 columns = ["store_user", "object_uid", "object_recurrenceid"]
1.141 values = [user, uid, recurrenceid]
1.142
1.143 - query, values = self.get_query(
1.144 - "select object_text from recurrences :condition",
1.145 + query, values = self.get_query(self.with_tables(
1.146 + "select object_text from %(recurrences)s :condition"),
1.147 columns, values)
1.148
1.149 self.cursor.execute(query, values)
1.150 @@ -254,8 +273,8 @@
1.151 setcolumns = ["object_text", "status"]
1.152 setvalues = [to_string(node, "utf-8"), "active"]
1.153
1.154 - query, values = self.get_query(
1.155 - "update recurrences :set :condition",
1.156 + query, values = self.get_query(self.with_tables(
1.157 + "update %(recurrences)s :set :condition"),
1.158 columns, values, setcolumns, setvalues)
1.159
1.160 self.cursor.execute(query, values)
1.161 @@ -266,8 +285,8 @@
1.162 columns = ["store_user", "object_uid", "object_recurrenceid", "object_text", "status"]
1.163 values = [user, uid, recurrenceid, to_string(node, "utf-8"), "active"]
1.164
1.165 - query, values = self.get_query(
1.166 - "insert into recurrences (:columns) values (:values)",
1.167 + query, values = self.get_query(self.with_tables(
1.168 + "insert into %(recurrences)s (:columns) values (:values)"),
1.169 columns, values)
1.170
1.171 self.cursor.execute(query, values)
1.172 @@ -283,8 +302,8 @@
1.173 columns = ["store_user", "object_uid", "object_recurrenceid"]
1.174 values = [user, uid, recurrenceid]
1.175
1.176 - query, values = self.get_query(
1.177 - "delete from recurrences :condition",
1.178 + query, values = self.get_query(self.with_tables(
1.179 + "delete from %(recurrences)s :condition"),
1.180 columns, values)
1.181
1.182 self.cursor.execute(query, values)
1.183 @@ -300,8 +319,8 @@
1.184 columns = ["store_user", "object_uid"]
1.185 values = [user, uid]
1.186
1.187 - query, values = self.get_query(
1.188 - "delete from recurrences :condition",
1.189 + query, values = self.get_query(self.with_tables(
1.190 + "delete from %(recurrences)s :condition"),
1.191 columns, values)
1.192
1.193 self.cursor.execute(query, values)
1.194 @@ -332,18 +351,18 @@
1.195 "Get the table providing events for any specified 'dirname'."
1.196
1.197 if dirname == "counters":
1.198 - return "countered_objects"
1.199 + return "countered_%s" % self.objects_table
1.200 else:
1.201 - return "objects"
1.202 + return self.objects_table
1.203
1.204 def get_recurrence_table(self, dirname=None):
1.205
1.206 "Get the table providing recurrences for any specified 'dirname'."
1.207
1.208 if dirname == "counters":
1.209 - return "countered_recurrences"
1.210 + return "countered_%s" % self.recurrences_table
1.211 else:
1.212 - return "recurrences"
1.213 + return self.recurrences_table
1.214
1.215 # Free/busy period providers, upon extension of the free/busy records.
1.216
1.217 @@ -359,8 +378,8 @@
1.218 columns = ["store_user"]
1.219 values = [user]
1.220
1.221 - query, values = self.get_query(
1.222 - "select object_uid, object_recurrenceid from freebusy_providers :condition",
1.223 + query, values = self.get_query(self.with_tables(
1.224 + "select object_uid, object_recurrenceid from %(freebusy_providers)s :condition"),
1.225 columns, values)
1.226
1.227 self.cursor.execute(query, values)
1.228 @@ -369,8 +388,8 @@
1.229 columns = ["store_user"]
1.230 values = [user]
1.231
1.232 - query, values = self.get_query(
1.233 - "select start from freebusy_provider_datetimes :condition",
1.234 + query, values = self.get_query(self.with_tables(
1.235 + "select start from %(freebusy_provider_datetimes)s :condition"),
1.236 columns, values)
1.237
1.238 self.cursor.execute(query, values)
1.239 @@ -388,8 +407,8 @@
1.240 columns = ["store_user"]
1.241 values = [user]
1.242
1.243 - query, values = self.get_query(
1.244 - "delete from freebusy_providers :condition",
1.245 + query, values = self.get_query(self.with_tables(
1.246 + "delete from %(freebusy_providers)s :condition"),
1.247 columns, values)
1.248
1.249 self.cursor.execute(query, values)
1.250 @@ -399,8 +418,8 @@
1.251 for uid, recurrenceid in t:
1.252 values = [user, uid, recurrenceid]
1.253
1.254 - query, values = self.get_query(
1.255 - "insert into freebusy_providers (:columns) values (:values)",
1.256 + query, values = self.get_query(self.with_tables(
1.257 + "insert into %(freebusy_providers)s (:columns) values (:values)"),
1.258 columns, values)
1.259
1.260 self.cursor.execute(query, values)
1.261 @@ -410,8 +429,8 @@
1.262 setcolumns = ["start"]
1.263 setvalues = [dt_string]
1.264
1.265 - query, values = self.get_query(
1.266 - "update freebusy_provider_datetimes :set :condition",
1.267 + query, values = self.get_query(self.with_tables(
1.268 + "update %(freebusy_provider_datetimes)s :set :condition"),
1.269 columns, values, setcolumns, setvalues)
1.270
1.271 self.cursor.execute(query, values)
1.272 @@ -422,8 +441,8 @@
1.273 columns = ["store_user", "start"]
1.274 values = [user, dt_string]
1.275
1.276 - query, values = self.get_query(
1.277 - "insert into freebusy_provider_datetimes (:columns) values (:values)",
1.278 + query, values = self.get_query(self.with_tables(
1.279 + "insert into %(freebusy_provider_datetimes)s (:columns) values (:values)"),
1.280 columns, values)
1.281
1.282 self.cursor.execute(query, values)
1.283 @@ -443,9 +462,8 @@
1.284
1.285 "For the given 'user', get free/busy details for the 'other' user."
1.286
1.287 - table = "freebusy_other"
1.288 cls = cls or FreeBusyDatabaseCollection
1.289 - return cls(self.cursor, table, ["store_user", "other"], [user, other], mutable, self.paramstyle)
1.290 + return cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], mutable, self.paramstyle)
1.291
1.292 def set_freebusy(self, user, freebusy, name=None, cls=None):
1.293
1.294 @@ -464,11 +482,10 @@
1.295
1.296 "For the given 'user', set 'freebusy' details for the 'other' user."
1.297
1.298 - table = "freebusy_other"
1.299 cls = cls or FreeBusyDatabaseCollection
1.300
1.301 - if not isinstance(freebusy, cls) or freebusy.table_name != table:
1.302 - fbc = cls(self.cursor, table, ["store_user", "other"], [user, other], True, self.paramstyle)
1.303 + if not isinstance(freebusy, cls) or freebusy.table_name != self.freebusy_other_table:
1.304 + fbc = cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], True, self.paramstyle)
1.305 fbc += freebusy
1.306
1.307 return True
1.308 @@ -483,8 +500,8 @@
1.309 columns = ["store_user"]
1.310 values = [user]
1.311
1.312 - query, values = self.get_query(
1.313 - "select distinct other from freebusy_other :condition",
1.314 + query, values = self.get_query(self.with_tables(
1.315 + "select distinct other from %(freebusy_other)s :condition"),
1.316 columns, values)
1.317
1.318 self.cursor.execute(query, values)
1.319 @@ -818,16 +835,22 @@
1.320
1.321 "A journal system to support quotas."
1.322
1.323 + objects_table = "journal_objects"
1.324 + recurrences_table = "journal_recurrences"
1.325 + freebusy_other_table = "journal_freebusy_other"
1.326 + freebusy_providers_table = "journal_freebusy_providers"
1.327 + freebusy_provider_datetimes_table = "journal_freebusy_provider_datetimes"
1.328 +
1.329 # Quota and user identity/group discovery.
1.330
1.331 def get_quotas(self):
1.332
1.333 "Return a list of quotas."
1.334
1.335 - query = "select distinct quota from (" \
1.336 - "select distinct store_user as quota from freebusy_other " \
1.337 + query = self.with_tables("select distinct quota from (" \
1.338 + "select distinct store_user as quota from %(freebusy_other)s " \
1.339 "union all select quota from quota_limits" \
1.340 - ") as quotas"
1.341 + ") as quotas")
1.342 self.cursor.execute(query)
1.343 return [r[0] for r in self.cursor.fetchall()]
1.344
1.345 @@ -838,11 +861,11 @@
1.346 columns = ["quota"]
1.347 values = [quota]
1.348
1.349 - query, values = self.get_query(
1.350 + query, values = self.get_query(self.with_tables(
1.351 "select distinct user_group from (" \
1.352 - "select distinct other as user_group from freebusy_other :condition " \
1.353 + "select distinct other as user_group from %(freebusy_other)s :condition " \
1.354 "union all select user_group from quota_delegates :condition" \
1.355 - ") as users",
1.356 + ") as users"),
1.357 columns, values)
1.358
1.359 self.cursor.execute(query, values)