# HG changeset patch # User Paul Boddie # Date 1475963125 -7200 # Node ID 7d1cbe361b3bbe08a9a870aab3d7124eb277d63b # Parent 16c397f18526d7986e51119809e6f2178a28ce36 Established separate journal tables so that store and journal data are not mixed together in the same tables when both are configured to reside in the same database. diff -r 16c397f18526 -r 7d1cbe361b3b conf/postgresql/schema.sql --- a/conf/postgresql/schema.sql Sat Oct 08 23:16:53 2016 +0200 +++ b/conf/postgresql/schema.sql Sat Oct 08 23:45:25 2016 +0200 @@ -132,3 +132,55 @@ user_group varchar not null, primary key(quota, store_user, user_group) ); + +-- Separate object store tables. + +create table journal_objects ( + store_user varchar not null, + object_uid varchar not null, + object_text varchar not null, + status varchar not null, -- 'active', 'cancelled' + primary key(store_user, object_uid) +); + +create table journal_recurrences ( + store_user varchar not null, + object_uid varchar not null, + object_recurrenceid varchar not null, + object_text varchar not null, + status varchar not null, -- 'active', 'cancelled' + primary key(store_user, object_uid, object_recurrenceid) +); + +-- Separate object store free/busy details. + +create table journal_freebusy_other ( + store_user varchar not null, + other varchar not null, + "start" varchar not null, + "end" varchar not null, + object_uid varchar, + transp varchar, + object_recurrenceid varchar, + summary varchar, + organiser varchar, + attendee varchar -- used by quotas +); + +create index journal_freebusy_other_start on journal_freebusy_other(store_user, other, "start"); +create index journal_freebusy_other_end on journal_freebusy_other(store_user, other, "end"); + +create table journal_freebusy_providers ( + store_user varchar not null, + object_uid varchar not null, + object_recurrenceid varchar +); + +create index journal_freebusy_providers_store_user on journal_freebusy_providers(store_user); + +create table journal_freebusy_provider_datetimes ( + store_user varchar not null, + "start" varchar +); + +create index journal_freebusy_provider_datetimes_store_user on journal_freebusy_provider_datetimes(store_user); diff -r 16c397f18526 -r 7d1cbe361b3b docs/wiki/DatabaseStore --- a/docs/wiki/DatabaseStore Sat Oct 08 23:16:53 2016 +0200 +++ b/docs/wiki/DatabaseStore Sat Oct 08 23:45:25 2016 +0200 @@ -50,18 +50,18 @@ {{{#!table '''Table''' || '''Purpose''' == -`freebusy_other` +`journal_freebusy_other` || Period descriptions describing reservations for resources sharing a quota .. (`store_user`) made by users or groups (`other`), structured similarly to the .. `freebusy` table in the data store; this may be the same table as the one employed .. by the data store to store received or deduced free/busy details == -`freebusy_providers` +`journal_freebusy_providers` || Details of [[../EventRecurrences|recurring events]] for which new free/busy records .. must be [[../CronIntegration|periodically generated]] because these events recur .. indefinitely, selectable for each user (`store_user`) == -`freebusy_provider_datetimes` +`journal_freebusy_provider_datetimes` || Date/time details associated with the `freebusy_providers` information == `quota_delegates` @@ -72,11 +72,21 @@ `quota_limits` || A mapping from user identities or group identifiers to quota limits == +`journal_objects` +|| Records for each quota (`store_user`) containing received event data (`object_text`) +== +`journal_recurrences` +|| Records for each quota (`store_user`) containing received recurrence event data +.. (`object_text`) +== `user_groups` || A mapping from user identities to group identifiers indicating the sharing of a quota .. across a number of users }}} +The `journal_` prefix is employed for certain tables in order to allow the combination of +the journal and store databases without each kind of data getting mixed up. + == Store Structure == The store information is retained in a collection of tables. Unlike the structure of @@ -117,7 +127,7 @@ || Date/time details associated with the `freebusy_providers` information == `objects` -|| Records for each user (`store_data`) containing received event data (`object_text`) +|| Records for each user (`store_user`) containing received event data (`object_text`) == `requests` || A collections of records, each belonging to a specific user (`store_user`) @@ -126,6 +136,6 @@ .. indicating the availability of an incoming scheduling request for handling by a user == `recurrences` -|| Records for each user (`store_data`) containing received recurrence event data +|| Records for each user (`store_user`) containing received recurrence event data .. (`object_text`) }}} diff -r 16c397f18526 -r 7d1cbe361b3b imiptools/stores/database/common.py --- a/imiptools/stores/database/common.py Sat Oct 08 23:16:53 2016 +0200 +++ b/imiptools/stores/database/common.py Sat Oct 08 23:45:25 2016 +0200 @@ -44,21 +44,40 @@ def release_lock(self, user): pass + def with_tables(self, query): + + "Parameterise tables in the given 'query' for common operations." + + return query % { + "objects" : self.objects_table, + "recurrences" : self.recurrences_table, + "freebusy_other" : self.freebusy_other_table, + "freebusy_providers" : self.freebusy_providers_table, + "freebusy_provider_datetimes" : self.freebusy_provider_datetimes_table, + } + class DatabaseStore(DatabaseStoreBase, StoreBase): "A database store of tabular free/busy data and objects." + objects_table = "objects" + recurrences_table = "recurrences" + freebusy_other_table = "freebusy_other" + freebusy_providers_table = "freebusy_providers" + freebusy_provider_datetimes_table = "freebusy_provider_datetimes" + # User discovery. def get_users(self): "Return a list of users." - query = "select distinct store_user from (" \ + query = self.with_tables( + "select distinct store_user from (" \ "select store_user from freebusy " \ - "union all select store_user from objects " \ - "union all select store_user from recurrences" \ - ") as users" + "union all select store_user from %(objects)s " \ + "union all select store_user from %(recurrences)s" \ + ") as users") self.cursor.execute(query) return [r[0] for r in self.cursor.fetchall()] @@ -77,10 +96,10 @@ columns += ["store_user"] values += [user] - query, values = self.get_query( - "select object_uid, null as object_recurrenceid from objects :condition " + query, values = self.get_query(self.with_tables( + "select object_uid, null as object_recurrenceid from %(objects)s :condition " "union all " - "select object_uid, object_recurrenceid from recurrences :condition", + "select object_uid, object_recurrenceid from %(recurrences)s :condition"), columns, values) self.cursor.execute(query, values) @@ -95,8 +114,8 @@ columns += ["store_user"] values += [user] - query, values = self.get_query( - "select object_uid from objects :condition", + query, values = self.get_query(self.with_tables( + "select object_uid from %(objects)s :condition"), columns, values) self.cursor.execute(query, values) @@ -143,8 +162,8 @@ columns = ["store_user", "object_uid"] values = [user, uid] - query, values = self.get_query( - "select object_text from objects :condition", + query, values = self.get_query(self.with_tables( + "select object_text from %(objects)s :condition"), columns, values) self.cursor.execute(query, values) @@ -160,8 +179,8 @@ setcolumns = ["object_text", "status"] setvalues = [to_string(node, "utf-8"), "active"] - query, values = self.get_query( - "update objects :set :condition", + query, values = self.get_query(self.with_tables( + "update %(objects)s :set :condition"), columns, values, setcolumns, setvalues) self.cursor.execute(query, values) @@ -172,8 +191,8 @@ columns = ["store_user", "object_uid", "object_text", "status"] values = [user, uid, to_string(node, "utf-8"), "active"] - query, values = self.get_query( - "insert into objects (:columns) values (:values)", + query, values = self.get_query(self.with_tables( + "insert into %(objects)s (:columns) values (:values)"), columns, values) self.cursor.execute(query, values) @@ -186,8 +205,8 @@ columns = ["store_user", "object_uid"] values = [user, uid] - query, values = self.get_query( - "delete from objects :condition", + query, values = self.get_query(self.with_tables( + "delete from %(objects)s :condition"), columns, values) self.cursor.execute(query, values) @@ -203,8 +222,8 @@ columns = ["store_user", "object_uid", "status"] values = [user, uid, "active"] - query, values = self.get_query( - "select object_recurrenceid from recurrences :condition", + query, values = self.get_query(self.with_tables( + "select object_recurrenceid from %(recurrences)s :condition"), columns, values) self.cursor.execute(query, values) @@ -220,8 +239,8 @@ columns = ["store_user", "object_uid", "status"] values = [user, uid, "cancelled"] - query, values = self.get_query( - "select object_recurrenceid from recurrences :condition", + query, values = self.get_query(self.with_tables( + "select object_recurrenceid from %(recurrences)s :condition"), columns, values) self.cursor.execute(query, values) @@ -237,8 +256,8 @@ columns = ["store_user", "object_uid", "object_recurrenceid"] values = [user, uid, recurrenceid] - query, values = self.get_query( - "select object_text from recurrences :condition", + query, values = self.get_query(self.with_tables( + "select object_text from %(recurrences)s :condition"), columns, values) self.cursor.execute(query, values) @@ -254,8 +273,8 @@ setcolumns = ["object_text", "status"] setvalues = [to_string(node, "utf-8"), "active"] - query, values = self.get_query( - "update recurrences :set :condition", + query, values = self.get_query(self.with_tables( + "update %(recurrences)s :set :condition"), columns, values, setcolumns, setvalues) self.cursor.execute(query, values) @@ -266,8 +285,8 @@ columns = ["store_user", "object_uid", "object_recurrenceid", "object_text", "status"] values = [user, uid, recurrenceid, to_string(node, "utf-8"), "active"] - query, values = self.get_query( - "insert into recurrences (:columns) values (:values)", + query, values = self.get_query(self.with_tables( + "insert into %(recurrences)s (:columns) values (:values)"), columns, values) self.cursor.execute(query, values) @@ -283,8 +302,8 @@ columns = ["store_user", "object_uid", "object_recurrenceid"] values = [user, uid, recurrenceid] - query, values = self.get_query( - "delete from recurrences :condition", + query, values = self.get_query(self.with_tables( + "delete from %(recurrences)s :condition"), columns, values) self.cursor.execute(query, values) @@ -300,8 +319,8 @@ columns = ["store_user", "object_uid"] values = [user, uid] - query, values = self.get_query( - "delete from recurrences :condition", + query, values = self.get_query(self.with_tables( + "delete from %(recurrences)s :condition"), columns, values) self.cursor.execute(query, values) @@ -332,18 +351,18 @@ "Get the table providing events for any specified 'dirname'." if dirname == "counters": - return "countered_objects" + return "countered_%s" % self.objects_table else: - return "objects" + return self.objects_table def get_recurrence_table(self, dirname=None): "Get the table providing recurrences for any specified 'dirname'." if dirname == "counters": - return "countered_recurrences" + return "countered_%s" % self.recurrences_table else: - return "recurrences" + return self.recurrences_table # Free/busy period providers, upon extension of the free/busy records. @@ -359,8 +378,8 @@ columns = ["store_user"] values = [user] - query, values = self.get_query( - "select object_uid, object_recurrenceid from freebusy_providers :condition", + query, values = self.get_query(self.with_tables( + "select object_uid, object_recurrenceid from %(freebusy_providers)s :condition"), columns, values) self.cursor.execute(query, values) @@ -369,8 +388,8 @@ columns = ["store_user"] values = [user] - query, values = self.get_query( - "select start from freebusy_provider_datetimes :condition", + query, values = self.get_query(self.with_tables( + "select start from %(freebusy_provider_datetimes)s :condition"), columns, values) self.cursor.execute(query, values) @@ -388,8 +407,8 @@ columns = ["store_user"] values = [user] - query, values = self.get_query( - "delete from freebusy_providers :condition", + query, values = self.get_query(self.with_tables( + "delete from %(freebusy_providers)s :condition"), columns, values) self.cursor.execute(query, values) @@ -399,8 +418,8 @@ for uid, recurrenceid in t: values = [user, uid, recurrenceid] - query, values = self.get_query( - "insert into freebusy_providers (:columns) values (:values)", + query, values = self.get_query(self.with_tables( + "insert into %(freebusy_providers)s (:columns) values (:values)"), columns, values) self.cursor.execute(query, values) @@ -410,8 +429,8 @@ setcolumns = ["start"] setvalues = [dt_string] - query, values = self.get_query( - "update freebusy_provider_datetimes :set :condition", + query, values = self.get_query(self.with_tables( + "update %(freebusy_provider_datetimes)s :set :condition"), columns, values, setcolumns, setvalues) self.cursor.execute(query, values) @@ -422,8 +441,8 @@ columns = ["store_user", "start"] values = [user, dt_string] - query, values = self.get_query( - "insert into freebusy_provider_datetimes (:columns) values (:values)", + query, values = self.get_query(self.with_tables( + "insert into %(freebusy_provider_datetimes)s (:columns) values (:values)"), columns, values) self.cursor.execute(query, values) @@ -443,9 +462,8 @@ "For the given 'user', get free/busy details for the 'other' user." - table = "freebusy_other" cls = cls or FreeBusyDatabaseCollection - return cls(self.cursor, table, ["store_user", "other"], [user, other], mutable, self.paramstyle) + return cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], mutable, self.paramstyle) def set_freebusy(self, user, freebusy, name=None, cls=None): @@ -464,11 +482,10 @@ "For the given 'user', set 'freebusy' details for the 'other' user." - table = "freebusy_other" cls = cls or FreeBusyDatabaseCollection - if not isinstance(freebusy, cls) or freebusy.table_name != table: - fbc = cls(self.cursor, table, ["store_user", "other"], [user, other], True, self.paramstyle) + if not isinstance(freebusy, cls) or freebusy.table_name != self.freebusy_other_table: + fbc = cls(self.cursor, self.freebusy_other_table, ["store_user", "other"], [user, other], True, self.paramstyle) fbc += freebusy return True @@ -483,8 +500,8 @@ columns = ["store_user"] values = [user] - query, values = self.get_query( - "select distinct other from freebusy_other :condition", + query, values = self.get_query(self.with_tables( + "select distinct other from %(freebusy_other)s :condition"), columns, values) self.cursor.execute(query, values) @@ -818,16 +835,22 @@ "A journal system to support quotas." + objects_table = "journal_objects" + recurrences_table = "journal_recurrences" + freebusy_other_table = "journal_freebusy_other" + freebusy_providers_table = "journal_freebusy_providers" + freebusy_provider_datetimes_table = "journal_freebusy_provider_datetimes" + # Quota and user identity/group discovery. def get_quotas(self): "Return a list of quotas." - query = "select distinct quota from (" \ - "select distinct store_user as quota from freebusy_other " \ + query = self.with_tables("select distinct quota from (" \ + "select distinct store_user as quota from %(freebusy_other)s " \ "union all select quota from quota_limits" \ - ") as quotas" + ") as quotas") self.cursor.execute(query) return [r[0] for r in self.cursor.fetchall()] @@ -838,11 +861,11 @@ columns = ["quota"] values = [quota] - query, values = self.get_query( + query, values = self.get_query(self.with_tables( "select distinct user_group from (" \ - "select distinct other as user_group from freebusy_other :condition " \ + "select distinct other as user_group from %(freebusy_other)s :condition " \ "union all select user_group from quota_delegates :condition" \ - ") as users", + ") as users"), columns, values) self.cursor.execute(query, values)