From b0b57234790da62c9dd6ba11b3093e9b80678f94 Mon Sep 17 00:00:00 2001 From: Arthur de Jong Date: Tue, 16 Apr 2013 17:48:10 +0200 Subject: Move cache table creation to modules This also moves the creation of a SQLite database connection to a _get_connection() function to ensure the cache is only created when the caches are instantiated. --- pynslcd/protocol.py | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'pynslcd/protocol.py') diff --git a/pynslcd/protocol.py b/pynslcd/protocol.py index cafda9d..76c7d18 100644 --- a/pynslcd/protocol.py +++ b/pynslcd/protocol.py @@ -37,6 +37,19 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): + create_sql = ''' + CREATE TABLE IF NOT EXISTS `protocol_cache` + ( `cn` TEXT PRIMARY KEY, + `ipProtocolNumber` INTEGER NOT NULL, + `mtime` TIMESTAMP NOT NULL ); + CREATE TABLE IF NOT EXISTS `protocol_1_cache` + ( `protocol` TEXT NOT NULL, + `cn` TEXT NOT NULL, + FOREIGN KEY(`protocol`) REFERENCES `protocol_cache`(`cn`) + ON DELETE CASCADE ON UPDATE CASCADE ); + CREATE INDEX IF NOT EXISTS `protocol_1_idx` ON `protocol_1_cache`(`protocol`); + ''' + def retrieve(self, parameters): query = cache.CnAliasedQuery('protocol', parameters) for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('alias', )): -- cgit v1.2.3 From 76712760885774f883f7bf79ce97931a6e251428 Mon Sep 17 00:00:00 2001 From: Arthur de Jong Date: Sun, 11 Aug 2013 22:32:11 +0200 Subject: Explicitly define tables used for cache This introduces the tables property in the Cache object that is used to define the used tables. This also fixes the storing of mulit-valued attributes in the cache. --- pynslcd/alias.py | 2 ++ pynslcd/cache.py | 35 +++++++++++++++++++++-------------- pynslcd/group.py | 2 ++ pynslcd/host.py | 2 ++ pynslcd/network.py | 2 ++ pynslcd/protocol.py | 2 ++ pynslcd/rpc.py | 2 ++ pynslcd/service.py | 2 ++ 8 files changed, 35 insertions(+), 14 deletions(-) (limited to 'pynslcd/protocol.py') diff --git a/pynslcd/alias.py b/pynslcd/alias.py index bf313c4..48eccda 100644 --- a/pynslcd/alias.py +++ b/pynslcd/alias.py @@ -37,6 +37,8 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): + tables = ('alias_cache', 'alias_1_cache') + create_sql = ''' CREATE TABLE IF NOT EXISTS `alias_cache` ( `cn` TEXT PRIMARY KEY COLLATE NOCASE, diff --git a/pynslcd/cache.py b/pynslcd/cache.py index c730c5a..56a83e5 100644 --- a/pynslcd/cache.py +++ b/pynslcd/cache.py @@ -112,7 +112,9 @@ class Cache(object): def __init__(self): self.con = _get_connection() - self.table = sys.modules[self.__module__].__name__ + self.db = sys.modules[self.__module__].__name__ + if not hasattr(self, 'tables'): + self.tables = ['%s_cache' % self.db] self.create() def create(self): @@ -120,38 +122,43 @@ class Cache(object): self.con.executescript(self.create_sql) def store(self, *values): - """Store the values in the cache for the specified table.""" + """Store the values in the cache for the specified table. + The order of the values is the order returned by the Reques.convert() + function.""" + # split the values into simple (flat) values and one-to-many values simple_values = [] - multi_values = {} - for n, v in enumerate(values): + multi_values = [] + for v in values: if isinstance(v, (list, tuple, set)): - multi_values[n] = v + multi_values.append(v) else: simple_values.append(v) + # insert the simple values simple_values.append(datetime.datetime.now()) args = ', '.join(len(simple_values) * ('?', )) self.con.execute(''' - INSERT OR REPLACE INTO %s_cache + INSERT OR REPLACE INTO %s VALUES (%s) - ''' % (self.table, args), simple_values) - for n, vlist in multi_values.items(): + ''' % (self.tables[0], args), simple_values) + # insert the one-to-many values + for n, vlist in enumerate(multi_values): self.con.execute(''' - DELETE FROM %s_%d_cache + DELETE FROM %s WHERE `%s` = ? - ''' % (self.table, n, self.table), (values[0], )) + ''' % (self.tables[n + 1], self.db), (values[0], )) self.con.executemany(''' - INSERT INTO %s_%d_cache + INSERT INTO %s VALUES (?, ?) - ''' % (self.table, n), ((values[0], x) for x in vlist)) + ''' % (self.tables[n + 1]), ((values[0], x) for x in vlist)) def retrieve(self, parameters): """Retrieve all items from the cache based on the parameters supplied.""" query = Query(''' SELECT * - FROM %s_cache - ''' % self.table, parameters) + FROM %s + ''' % self.tables[0], parameters) return (list(x)[:-1] for x in query.execute(self.con)) diff --git a/pynslcd/group.py b/pynslcd/group.py index f893ac1..aed6cc0 100644 --- a/pynslcd/group.py +++ b/pynslcd/group.py @@ -75,6 +75,8 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): + tables = ('group_cache', 'group_3_cache') + create_sql = ''' CREATE TABLE IF NOT EXISTS `group_cache` ( `cn` TEXT PRIMARY KEY, diff --git a/pynslcd/host.py b/pynslcd/host.py index 990cec3..77789ec 100644 --- a/pynslcd/host.py +++ b/pynslcd/host.py @@ -53,6 +53,8 @@ class HostQuery(cache.CnAliasedQuery): class Cache(cache.Cache): + tables = ('host_cache', 'host_1_cache', 'host_2_cache') + create_sql = ''' CREATE TABLE IF NOT EXISTS `host_cache` ( `cn` TEXT PRIMARY KEY COLLATE NOCASE, diff --git a/pynslcd/network.py b/pynslcd/network.py index 4b3e8bb..5a43b93 100644 --- a/pynslcd/network.py +++ b/pynslcd/network.py @@ -54,6 +54,8 @@ class NetworkQuery(cache.CnAliasedQuery): class Cache(cache.Cache): + tables = ('network_cache', 'network_1_cache', 'network_2_cache') + create_sql = ''' CREATE TABLE IF NOT EXISTS `network_cache` ( `cn` TEXT PRIMARY KEY COLLATE NOCASE, diff --git a/pynslcd/protocol.py b/pynslcd/protocol.py index 76c7d18..7684161 100644 --- a/pynslcd/protocol.py +++ b/pynslcd/protocol.py @@ -37,6 +37,8 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): + tables = ('protocol_cache', 'protocol_1_cache') + create_sql = ''' CREATE TABLE IF NOT EXISTS `protocol_cache` ( `cn` TEXT PRIMARY KEY, diff --git a/pynslcd/rpc.py b/pynslcd/rpc.py index 24cf673..efb6cb9 100644 --- a/pynslcd/rpc.py +++ b/pynslcd/rpc.py @@ -37,6 +37,8 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): + tables = ('rpc_cache', 'rpc_1_cache') + create_sql = ''' CREATE TABLE IF NOT EXISTS `rpc_cache` ( `cn` TEXT PRIMARY KEY, diff --git a/pynslcd/service.py b/pynslcd/service.py index 260941a..6b3424e 100644 --- a/pynslcd/service.py +++ b/pynslcd/service.py @@ -69,6 +69,8 @@ class ServiceQuery(cache.CnAliasedQuery): class Cache(cache.Cache): + tables = ('service_cache', 'service_1_cache') + create_sql = ''' CREATE TABLE IF NOT EXISTS `service_cache` ( `cn` TEXT NOT NULL, -- cgit v1.2.3 From 1b89df59af349feb27205b90727263f17f29a9d8 Mon Sep 17 00:00:00 2001 From: Arthur de Jong Date: Mon, 12 Aug 2013 13:26:03 +0200 Subject: Give cache tables friendlier names This also defined the tables for netgroup storage. --- pynslcd/alias.py | 13 +++++++------ pynslcd/cache.py | 8 ++++---- pynslcd/group.py | 13 +++++++------ pynslcd/host.py | 22 +++++++++++----------- pynslcd/netgroup.py | 21 ++++++++++++++++----- pynslcd/network.py | 24 ++++++++++++------------ pynslcd/protocol.py | 6 +++--- pynslcd/rpc.py | 6 +++--- pynslcd/service.py | 22 +++++++++++----------- 9 files changed, 74 insertions(+), 61 deletions(-) (limited to 'pynslcd/protocol.py') diff --git a/pynslcd/alias.py b/pynslcd/alias.py index 48eccda..d5ae390 100644 --- a/pynslcd/alias.py +++ b/pynslcd/alias.py @@ -37,26 +37,27 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): - tables = ('alias_cache', 'alias_1_cache') + tables = ('alias_cache', 'alias_member_cache') create_sql = ''' CREATE TABLE IF NOT EXISTS `alias_cache` ( `cn` TEXT PRIMARY KEY COLLATE NOCASE, `mtime` TIMESTAMP NOT NULL ); - CREATE TABLE IF NOT EXISTS `alias_1_cache` + CREATE TABLE IF NOT EXISTS `alias_member_cache` ( `alias` TEXT NOT NULL COLLATE NOCASE, `rfc822MailMember` TEXT NOT NULL, FOREIGN KEY(`alias`) REFERENCES `alias_cache`(`cn`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `alias_1_idx` ON `alias_1_cache`(`alias`); + CREATE INDEX IF NOT EXISTS `alias_member_idx` ON `alias_member_cache`(`alias`); ''' retrieve_sql = ''' SELECT `alias_cache`.`cn` AS `cn`, - `alias_1_cache`.`rfc822MailMember` AS `rfc822MailMember` + `alias_member_cache`.`rfc822MailMember` AS `rfc822MailMember`, + `alias_cache`.`mtime` AS `mtime` FROM `alias_cache` - LEFT JOIN `alias_1_cache` - ON `alias_1_cache`.`alias` = `alias_cache`.`cn` + LEFT JOIN `alias_member_cache` + ON `alias_member_cache`.`alias` = `alias_cache`.`cn` ''' def retrieve(self, parameters): diff --git a/pynslcd/cache.py b/pynslcd/cache.py index 56a83e5..f6d894e 100644 --- a/pynslcd/cache.py +++ b/pynslcd/cache.py @@ -59,14 +59,14 @@ class CnAliasedQuery(Query): sql = ''' SELECT `%(table)s_cache`.*, - `%(table)s_1_cache`.`cn` AS `alias` + `%(table)s_alias_cache`.`cn` AS `alias` FROM `%(table)s_cache` - LEFT JOIN `%(table)s_1_cache` - ON `%(table)s_1_cache`.`%(table)s` = `%(table)s_cache`.`cn` + LEFT JOIN `%(table)s_alias_cache` + ON `%(table)s_alias_cache`.`%(table)s` = `%(table)s_cache`.`cn` ''' cn_join = ''' - LEFT JOIN `%(table)s_1_cache` `cn_alias` + LEFT JOIN `%(table)s_alias_cache` `cn_alias` ON `cn_alias`.`%(table)s` = `%(table)s_cache`.`cn` ''' diff --git a/pynslcd/group.py b/pynslcd/group.py index aed6cc0..2028f1e 100644 --- a/pynslcd/group.py +++ b/pynslcd/group.py @@ -75,7 +75,7 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): - tables = ('group_cache', 'group_3_cache') + tables = ('group_cache', 'group_member_cache') create_sql = ''' CREATE TABLE IF NOT EXISTS `group_cache` @@ -83,19 +83,20 @@ class Cache(cache.Cache): `userPassword` TEXT, `gidNumber` INTEGER NOT NULL UNIQUE, `mtime` TIMESTAMP NOT NULL ); - CREATE TABLE IF NOT EXISTS `group_3_cache` + CREATE TABLE IF NOT EXISTS `group_member_cache` ( `group` TEXT NOT NULL, `memberUid` TEXT NOT NULL, FOREIGN KEY(`group`) REFERENCES `group_cache`(`cn`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `group_3_idx` ON `group_3_cache`(`group`); + CREATE INDEX IF NOT EXISTS `group_member_idx` ON `group_member_cache`(`group`); ''' retrieve_sql = ''' - SELECT `cn`, `userPassword`, `gidNumber`, `memberUid` + SELECT `group_cache`.`cn` AS `cn`, `userPassword`, `gidNumber`, + `memberUid`, `mtime` FROM `group_cache` - LEFT JOIN `group_3_cache` - ON `group_3_cache`.`group` = `group_cache`.`cn` + LEFT JOIN `group_member_cache` + ON `group_member_cache`.`group` = `group_cache`.`cn` ''' def retrieve(self, parameters): diff --git a/pynslcd/host.py b/pynslcd/host.py index 77789ec..91c3fa0 100644 --- a/pynslcd/host.py +++ b/pynslcd/host.py @@ -38,13 +38,13 @@ class HostQuery(cache.CnAliasedQuery): sql = ''' SELECT `host_cache`.`cn` AS `cn`, - `host_1_cache`.`cn` AS `alias`, - `host_2_cache`.`ipHostNumber` AS `ipHostNumber` + `host_alias_cache`.`cn` AS `alias`, + `host_address_cache`.`ipHostNumber` AS `ipHostNumber` FROM `host_cache` - LEFT JOIN `host_1_cache` - ON `host_1_cache`.`host` = `host_cache`.`cn` - LEFT JOIN `host_2_cache` - ON `host_2_cache`.`host` = `host_cache`.`cn` + LEFT JOIN `host_alias_cache` + ON `host_alias_cache`.`host` = `host_cache`.`cn` + LEFT JOIN `host_address_cache` + ON `host_address_cache`.`host` = `host_cache`.`cn` ''' def __init__(self, parameters): @@ -53,24 +53,24 @@ class HostQuery(cache.CnAliasedQuery): class Cache(cache.Cache): - tables = ('host_cache', 'host_1_cache', 'host_2_cache') + tables = ('host_cache', 'host_alias_cache', 'host_address_cache') create_sql = ''' CREATE TABLE IF NOT EXISTS `host_cache` ( `cn` TEXT PRIMARY KEY COLLATE NOCASE, `mtime` TIMESTAMP NOT NULL ); - CREATE TABLE IF NOT EXISTS `host_1_cache` + CREATE TABLE IF NOT EXISTS `host_alias_cache` ( `host` TEXT NOT NULL COLLATE NOCASE, `cn` TEXT NOT NULL COLLATE NOCASE, FOREIGN KEY(`host`) REFERENCES `host_cache`(`cn`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `host_1_idx` ON `host_1_cache`(`host`); - CREATE TABLE IF NOT EXISTS `host_2_cache` + CREATE INDEX IF NOT EXISTS `host_alias_idx` ON `host_alias_cache`(`host`); + CREATE TABLE IF NOT EXISTS `host_address_cache` ( `host` TEXT NOT NULL COLLATE NOCASE, `ipHostNumber` TEXT NOT NULL, FOREIGN KEY(`host`) REFERENCES `host_cache`(`cn`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `host_2_idx` ON `host_2_cache`(`host`); + CREATE INDEX IF NOT EXISTS `host_address_idx` ON `host_address_cache`(`host`); ''' def retrieve(self, parameters): diff --git a/pynslcd/netgroup.py b/pynslcd/netgroup.py index 7be2936..1de60bf 100644 --- a/pynslcd/netgroup.py +++ b/pynslcd/netgroup.py @@ -43,13 +43,24 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): + tables = ('netgroup_cache', 'netgroup_triple_cache', 'netgroup_member_cache') + create_sql = ''' - -- FIXME: this does not work as entries are never removed from the cache CREATE TABLE IF NOT EXISTS `netgroup_cache` - ( `cn` TEXT NOT NULL, - `member` TEXT NOT NULL, - `mtime` TIMESTAMP NOT NULL, - UNIQUE (`cn`, `member`) ); + ( `cn` TEXT PRIMARY KEY COLLATE NOCASE, + `mtime` TIMESTAMP NOT NULL ); + CREATE TABLE IF NOT EXISTS `netgroup_triple_cache` + ( `netgroup` TEXT NOT NULL COLLATE NOCASE, + `nisNetgroupTriple` TEXT NOT NULL COLLATE NOCASE, + FOREIGN KEY(`netgroup`) REFERENCES `netgroup_cache`(`cn`) + ON DELETE CASCADE ON UPDATE CASCADE ); + CREATE INDEX IF NOT EXISTS `netgroup_triple_idx` ON `netgroup_triple_cache`(`netgroup`); + CREATE TABLE IF NOT EXISTS `netgroup_member_cache` + ( `netgroup` TEXT NOT NULL COLLATE NOCASE, + `memberNisNetgroup` TEXT NOT NULL, + FOREIGN KEY(`netgroup`) REFERENCES `netgroup_cache`(`cn`) + ON DELETE CASCADE ON UPDATE CASCADE ); + CREATE INDEX IF NOT EXISTS `netgroup_membe_idx` ON `netgroup_member_cache`(`netgroup`); ''' diff --git a/pynslcd/network.py b/pynslcd/network.py index 5a43b93..bf49b4d 100644 --- a/pynslcd/network.py +++ b/pynslcd/network.py @@ -39,13 +39,13 @@ class NetworkQuery(cache.CnAliasedQuery): sql = ''' SELECT `network_cache`.`cn` AS `cn`, - `network_1_cache`.`cn` AS `alias`, - `network_2_cache`.`ipNetworkNumber` AS `ipNetworkNumber` + `network_alias_cache`.`cn` AS `alias`, + `network_address_cache`.`ipNetworkNumber` AS `ipNetworkNumber` FROM `network_cache` - LEFT JOIN `network_1_cache` - ON `network_1_cache`.`network` = `network_cache`.`cn` - LEFT JOIN `network_2_cache` - ON `network_2_cache`.`network` = `network_cache`.`cn` + LEFT JOIN `network_alias_cache` + ON `network_alias_cache`.`network` = `network_cache`.`cn` + LEFT JOIN `network_address_cache` + ON `network_address_cache`.`network` = `network_cache`.`cn` ''' def __init__(self, parameters): @@ -54,24 +54,24 @@ class NetworkQuery(cache.CnAliasedQuery): class Cache(cache.Cache): - tables = ('network_cache', 'network_1_cache', 'network_2_cache') + tables = ('network_cache', 'network_alias_cache', 'network_address_cache') create_sql = ''' CREATE TABLE IF NOT EXISTS `network_cache` ( `cn` TEXT PRIMARY KEY COLLATE NOCASE, `mtime` TIMESTAMP NOT NULL ); - CREATE TABLE IF NOT EXISTS `network_1_cache` + CREATE TABLE IF NOT EXISTS `network_alias_cache` ( `network` TEXT NOT NULL COLLATE NOCASE, `cn` TEXT NOT NULL COLLATE NOCASE, FOREIGN KEY(`network`) REFERENCES `network_cache`(`cn`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `network_1_idx` ON `network_1_cache`(`network`); - CREATE TABLE IF NOT EXISTS `network_2_cache` - ( `network` TEXT NOT NULL, + CREATE INDEX IF NOT EXISTS `network_alias_idx` ON `network_alias_cache`(`network`); + CREATE TABLE IF NOT EXISTS `network_address_cache` + ( `network` TEXT NOT NULL COLLATE NOCASE, `ipNetworkNumber` TEXT NOT NULL, FOREIGN KEY(`network`) REFERENCES `network_cache`(`cn`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `network_2_idx` ON `network_2_cache`(`network`); + CREATE INDEX IF NOT EXISTS `network_address_idx` ON `network_address_cache`(`network`); ''' def retrieve(self, parameters): diff --git a/pynslcd/protocol.py b/pynslcd/protocol.py index 7684161..122673d 100644 --- a/pynslcd/protocol.py +++ b/pynslcd/protocol.py @@ -37,19 +37,19 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): - tables = ('protocol_cache', 'protocol_1_cache') + tables = ('protocol_cache', 'protocol_alias_cache') create_sql = ''' CREATE TABLE IF NOT EXISTS `protocol_cache` ( `cn` TEXT PRIMARY KEY, `ipProtocolNumber` INTEGER NOT NULL, `mtime` TIMESTAMP NOT NULL ); - CREATE TABLE IF NOT EXISTS `protocol_1_cache` + CREATE TABLE IF NOT EXISTS `protocol_alias_cache` ( `protocol` TEXT NOT NULL, `cn` TEXT NOT NULL, FOREIGN KEY(`protocol`) REFERENCES `protocol_cache`(`cn`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `protocol_1_idx` ON `protocol_1_cache`(`protocol`); + CREATE INDEX IF NOT EXISTS `protocol_alias_idx` ON `protocol_alias_cache`(`protocol`); ''' def retrieve(self, parameters): diff --git a/pynslcd/rpc.py b/pynslcd/rpc.py index efb6cb9..98a0ecc 100644 --- a/pynslcd/rpc.py +++ b/pynslcd/rpc.py @@ -37,19 +37,19 @@ class Search(search.LDAPSearch): class Cache(cache.Cache): - tables = ('rpc_cache', 'rpc_1_cache') + tables = ('rpc_cache', 'rpc_alias_cache') create_sql = ''' CREATE TABLE IF NOT EXISTS `rpc_cache` ( `cn` TEXT PRIMARY KEY, `oncRpcNumber` INTEGER NOT NULL, `mtime` TIMESTAMP NOT NULL ); - CREATE TABLE IF NOT EXISTS `rpc_1_cache` + CREATE TABLE IF NOT EXISTS `rpc_alias_cache` ( `rpc` TEXT NOT NULL, `cn` TEXT NOT NULL, FOREIGN KEY(`rpc`) REFERENCES `rpc_cache`(`cn`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `rpc_1_idx` ON `rpc_1_cache`(`rpc`); + CREATE INDEX IF NOT EXISTS `rpc_alias_idx` ON `rpc_alias_cache`(`rpc`); ''' def retrieve(self, parameters): diff --git a/pynslcd/service.py b/pynslcd/service.py index 6b3424e..6f55cc1 100644 --- a/pynslcd/service.py +++ b/pynslcd/service.py @@ -44,15 +44,15 @@ class ServiceQuery(cache.CnAliasedQuery): sql = ''' SELECT `service_cache`.*, - `service_1_cache`.`cn` AS `alias` + `service_alias_cache`.`cn` AS `alias` FROM `service_cache` - LEFT JOIN `service_1_cache` - ON `service_1_cache`.`ipServicePort` = `service_cache`.`ipServicePort` - AND `service_1_cache`.`ipServiceProtocol` = `service_cache`.`ipServiceProtocol` + LEFT JOIN `service_alias_cache` + ON `service_alias_cache`.`ipServicePort` = `service_cache`.`ipServicePort` + AND `service_alias_cache`.`ipServiceProtocol` = `service_cache`.`ipServiceProtocol` ''' cn_join = ''' - LEFT JOIN `service_1_cache` `cn_alias` + LEFT JOIN `service_alias_cache` `cn_alias` ON `cn_alias`.`ipServicePort` = `service_cache`.`ipServicePort` AND `cn_alias`.`ipServiceProtocol` = `service_cache`.`ipServiceProtocol` ''' @@ -69,7 +69,7 @@ class ServiceQuery(cache.CnAliasedQuery): class Cache(cache.Cache): - tables = ('service_cache', 'service_1_cache') + tables = ('service_cache', 'service_alias_cache') create_sql = ''' CREATE TABLE IF NOT EXISTS `service_cache` @@ -78,7 +78,7 @@ class Cache(cache.Cache): `ipServiceProtocol` TEXT NOT NULL, `mtime` TIMESTAMP NOT NULL, UNIQUE (`ipServicePort`, `ipServiceProtocol`) ); - CREATE TABLE IF NOT EXISTS `service_1_cache` + CREATE TABLE IF NOT EXISTS `service_alias_cache` ( `ipServicePort` INTEGER NOT NULL, `ipServiceProtocol` TEXT NOT NULL, `cn` TEXT NOT NULL, @@ -86,8 +86,8 @@ class Cache(cache.Cache): ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(`ipServiceProtocol`) REFERENCES `service_cache`(`ipServiceProtocol`) ON DELETE CASCADE ON UPDATE CASCADE ); - CREATE INDEX IF NOT EXISTS `service_1_idx1` ON `service_1_cache`(`ipServicePort`); - CREATE INDEX IF NOT EXISTS `service_1_idx2` ON `service_1_cache`(`ipServiceProtocol`); + CREATE INDEX IF NOT EXISTS `service_alias_idx1` ON `service_alias_cache`(`ipServicePort`); + CREATE INDEX IF NOT EXISTS `service_alias_idx2` ON `service_alias_cache`(`ipServiceProtocol`); ''' def store(self, name, aliases, port, protocol): @@ -97,12 +97,12 @@ class Cache(cache.Cache): (?, ?, ?, ?) ''', (name, port, protocol, datetime.datetime.now())) self.con.execute(''' - DELETE FROM `service_1_cache` + DELETE FROM `service_alias_cache` WHERE `ipServicePort` = ? AND `ipServiceProtocol` = ? ''', (port, protocol)) self.con.executemany(''' - INSERT INTO `service_1_cache` + INSERT INTO `service_alias_cache` VALUES (?, ?, ?) ''', ((port, protocol, alias) for alias in aliases)) -- cgit v1.2.3 From d66162ad308878d2f3fb505a05742798283a8854 Mon Sep 17 00:00:00 2001 From: Arthur de Jong Date: Mon, 12 Aug 2013 22:42:56 +0200 Subject: Use retrieve_by, group_by and group_columns in the cache This removes custom retrieve() functions and Query classes from the database modules and uses retrieve_sql retrieve_by, group_by and group_columns to make a custom retrieval query. In the cache module this completely replaces how the query grouping is done. The Query class is now only used inside the cache and the CnAliasedQuery, RowGrouper and related classed have been removed. --- pynslcd/alias.py | 16 +++++-- pynslcd/cache.py | 130 ++++++++++++++++++++++++++-------------------------- pynslcd/group.py | 18 +++++--- pynslcd/host.py | 51 ++++++++++++--------- pynslcd/netgroup.py | 15 ++++++ pynslcd/network.py | 51 ++++++++++++--------- pynslcd/protocol.py | 24 ++++++++-- pynslcd/rpc.py | 24 ++++++++-- pynslcd/service.py | 60 +++++++++++------------- 9 files changed, 229 insertions(+), 160 deletions(-) (limited to 'pynslcd/protocol.py') diff --git a/pynslcd/alias.py b/pynslcd/alias.py index d5ae390..371ac2e 100644 --- a/pynslcd/alias.py +++ b/pynslcd/alias.py @@ -60,11 +60,17 @@ class Cache(cache.Cache): ON `alias_member_cache`.`alias` = `alias_cache`.`cn` ''' - def retrieve(self, parameters): - query = cache.Query(self.retrieve_sql, parameters) - # return results, returning the members as a list - for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('rfc822MailMember', )): - yield row['cn'], row['rfc822MailMember'] + retrieve_by = dict( + rfc822MailMember=''' + `cn` IN ( + SELECT `a`.`alias` + FROM `alias_member_cache` `a` + WHERE `a`.`rfc822MailMember` = ?) + ''', + ) + + group_by = (0, ) # cn + group_columns = (1, ) # rfc822MailMember class AliasRequest(common.Request): diff --git a/pynslcd/cache.py b/pynslcd/cache.py index 796bef4..3974fef 100644 --- a/pynslcd/cache.py +++ b/pynslcd/cache.py @@ -30,18 +30,50 @@ import sqlite3 # FIXME: have some way to remove stale entries from the cache if all items from LDAP are queried (perhas use TTL from all request) +class regroup(object): + + def __init__(self, results, group_by=None, group_column=None): + """Regroup the results in the group column by the key columns.""" + self.group_by = tuple(group_by) + self.group_column = group_column + self.it = iter(results) + self.tgtkey = self.currkey = self.currvalue = object() + + def keyfunc(self, row): + return tuple(row[x] for x in self.group_by) + + def __iter__(self): + return self + + def next(self): + # find a start row + while self.currkey == self.tgtkey: + self.currvalue = next(self.it) # Exit on StopIteration + self.currkey = self.keyfunc(self.currvalue) + self.tgtkey = self.currkey + # turn the result row into a list of columns + row = list(self.currvalue) + # replace the group column + row[self.group_column] = list(self._grouper(self.tgtkey)) + return row + + def _grouper(self, tgtkey): + """Generate the group columns.""" + while self.currkey == tgtkey: + value = self.currvalue[self.group_column] + if value is not None: + yield value + self.currvalue = next(self.it) # Exit on StopIteration + self.currkey = self.keyfunc(self.currvalue) + + class Query(object): + """Helper class to build an SQL query for the cache.""" - def __init__(self, query, parameters=None): + def __init__(self, query): self.query = query self.wheres = [] self.parameters = [] - if parameters: - for k, v in parameters.items(): - self.add_where('`%s` = ?' % k, [v]) - - def add_query(self, query): - self.query += ' ' + query def add_where(self, where, parameters): self.wheres.append(where) @@ -51,64 +83,17 @@ class Query(object): query = self.query if self.wheres: query += ' WHERE ' + ' AND '.join(self.wheres) - c = con.cursor() - return c.execute(query, self.parameters) - - -class CnAliasedQuery(Query): - - sql = ''' - SELECT `%(table)s_cache`.*, - `%(table)s_alias_cache`.`cn` AS `alias` - FROM `%(table)s_cache` - LEFT JOIN `%(table)s_alias_cache` - ON `%(table)s_alias_cache`.`%(table)s` = `%(table)s_cache`.`cn` - ''' - - cn_join = ''' - LEFT JOIN `%(table)s_alias_cache` `cn_alias` - ON `cn_alias`.`%(table)s` = `%(table)s_cache`.`cn` - ''' - - def __init__(self, table, parameters): - args = dict(table=table) - super(CnAliasedQuery, self).__init__(self.sql % args) - for k, v in parameters.items(): - if k == 'cn': - self.add_query(self.cn_join % args) - self.add_where('(`%(table)s_cache`.`cn` = ? OR `cn_alias`.`cn` = ?)' % args, [v, v]) - else: - self.add_where('`%s` = ?' % k, [v]) - - -class RowGrouper(object): - """Pass in query results and group the results by a certain specified - list of columns.""" - - def __init__(self, results, groupby, columns): - self.groupby = groupby - self.columns = columns - self.results = itertools.groupby(results, key=self.keyfunc) - - def __iter__(self): - return self - - def keyfunc(self, row): - return tuple(row[x] for x in self.groupby) - - def next(self): - groupcols, rows = self.results.next() - tmp = dict((x, list()) for x in self.columns) - for row in rows: - for col in self.columns: - if row[col] is not None: - tmp[col].append(row[col]) - result = dict(row) - result.update(tmp) - return result + cursor = con.cursor() + return cursor.execute(query, self.parameters) class Cache(object): + """The description of the cache.""" + + retrieve_sql = None + retrieve_by = dict() + group_by = () + group_columns = () def __init__(self): self.con = _get_connection() @@ -154,12 +139,25 @@ class Cache(object): ''' % (self.tables[n + 1]), ((values[0], x) for x in vlist)) def retrieve(self, parameters): - """Retrieve all items from the cache based on the parameters supplied.""" - query = Query(''' + """Retrieve all items from the cache based on the parameters + supplied.""" + query = Query(self.retrieve_sql or ''' SELECT * FROM %s - ''' % self.tables[0], parameters) - return (list(x)[:-1] for x in query.execute(self.con)) + ''' % self.tables[0]) + if parameters: + for k, v in parameters.items(): + where = self.retrieve_by.get(k, '`%s`.`%s` = ?' % (self.tables[0], k)) + query.add_where(where, where.count('?') * [v]) + # group by + # FIXME: find a nice way to turn group_by and group_columns into names + results = query.execute(self.con) + group_by = list(self.group_by + self.group_columns) + for column in self.group_columns[::-1]: + group_by.pop() + results = regroup(results, group_by, column) + # strip the mtime from the results + return (list(x)[:-1] for x in results) def __enter__(self): return self.con.__enter__(); diff --git a/pynslcd/group.py b/pynslcd/group.py index 2028f1e..10e3423 100644 --- a/pynslcd/group.py +++ b/pynslcd/group.py @@ -99,13 +99,17 @@ class Cache(cache.Cache): ON `group_member_cache`.`group` = `group_cache`.`cn` ''' - def retrieve(self, parameters): - query = cache.Query(self.retrieve_sql, parameters) - # return results returning the members as a set - q = itertools.groupby(query.execute(self.con), - key=lambda x: (x['cn'], x['userPassword'], x['gidNumber'])) - for k, v in q: - yield k + (set(x['memberUid'] for x in v if x['memberUid'] is not None), ) + retrieve_by = dict( + memberUid=''' + `cn` IN ( + SELECT `a`.`group` + FROM `group_member_cache` `a` + WHERE `a`.`memberUid` = ?) + ''', + ) + + group_by = (0, ) # cn + group_columns = (3, ) # memberUid class GroupRequest(common.Request): diff --git a/pynslcd/host.py b/pynslcd/host.py index 91c3fa0..04f5337 100644 --- a/pynslcd/host.py +++ b/pynslcd/host.py @@ -34,23 +34,6 @@ class Search(search.LDAPSearch): required = ('cn', ) -class HostQuery(cache.CnAliasedQuery): - - sql = ''' - SELECT `host_cache`.`cn` AS `cn`, - `host_alias_cache`.`cn` AS `alias`, - `host_address_cache`.`ipHostNumber` AS `ipHostNumber` - FROM `host_cache` - LEFT JOIN `host_alias_cache` - ON `host_alias_cache`.`host` = `host_cache`.`cn` - LEFT JOIN `host_address_cache` - ON `host_address_cache`.`host` = `host_cache`.`cn` - ''' - - def __init__(self, parameters): - super(HostQuery, self).__init__('host', parameters) - - class Cache(cache.Cache): tables = ('host_cache', 'host_alias_cache', 'host_address_cache') @@ -73,10 +56,36 @@ class Cache(cache.Cache): CREATE INDEX IF NOT EXISTS `host_address_idx` ON `host_address_cache`(`host`); ''' - def retrieve(self, parameters): - query = HostQuery(parameters) - for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('alias', 'ipHostNumber', )): - yield row['cn'], row['alias'], row['ipHostNumber'] + retrieve_sql = ''' + SELECT `host_cache`.`cn` AS `cn`, + `host_alias_cache`.`cn` AS `alias`, + `host_address_cache`.`ipHostNumber` AS `ipHostNumber`, + `host_cache`.`mtime` AS `mtime` + FROM `host_cache` + LEFT JOIN `host_alias_cache` + ON `host_alias_cache`.`host` = `host_cache`.`cn` + LEFT JOIN `host_address_cache` + ON `host_address_cache`.`host` = `host_cache`.`cn` + ''' + + retrieve_by = dict( + cn=''' + ( `host_cache`.`cn` = ? OR + `host_cache`.`cn` IN ( + SELECT `by_alias`.`host` + FROM `host_alias_cache` `by_alias` + WHERE `by_alias`.`cn` = ?)) + ''', + ipHostNumber=''' + `host_cache`.`cn` IN ( + SELECT `by_ipHostNumber`.`host` + FROM `host_address_cache` `by_ipHostNumber` + WHERE `by_ipHostNumber`.`ipHostNumber` = ?) + ''', + ) + + group_by = (0, ) # cn + group_columns = (1, 2) # alias, ipHostNumber class HostRequest(common.Request): diff --git a/pynslcd/netgroup.py b/pynslcd/netgroup.py index 1de60bf..d86e38c 100644 --- a/pynslcd/netgroup.py +++ b/pynslcd/netgroup.py @@ -63,6 +63,21 @@ class Cache(cache.Cache): CREATE INDEX IF NOT EXISTS `netgroup_membe_idx` ON `netgroup_member_cache`(`netgroup`); ''' + retrieve_sql = ''' + SELECT `netgroup_cache`.`cn` AS `cn`, + `netgroup_triple_cache`.`nisNetgroupTriple` AS `nisNetgroupTriple`, + `netgroup_member_cache`.`memberNisNetgroup` AS `memberNisNetgroup`, + `netgroup_cache`.`mtime` AS `mtime` + FROM `netgroup_cache` + LEFT JOIN `netgroup_triple_cache` + ON `netgroup_triple_cache`.`netgroup` = `netgroup_cache`.`cn` + LEFT JOIN `netgroup_member_cache` + ON `netgroup_member_cache`.`netgroup` = `netgroup_cache`.`cn` + ''' + + group_by = (0, ) # cn + group_columns = (1, 2) # nisNetgroupTriple, memberNisNetgroup + class NetgroupRequest(common.Request): diff --git a/pynslcd/network.py b/pynslcd/network.py index bf49b4d..01bf6c2 100644 --- a/pynslcd/network.py +++ b/pynslcd/network.py @@ -35,23 +35,6 @@ class Search(search.LDAPSearch): required = ('cn', ) -class NetworkQuery(cache.CnAliasedQuery): - - sql = ''' - SELECT `network_cache`.`cn` AS `cn`, - `network_alias_cache`.`cn` AS `alias`, - `network_address_cache`.`ipNetworkNumber` AS `ipNetworkNumber` - FROM `network_cache` - LEFT JOIN `network_alias_cache` - ON `network_alias_cache`.`network` = `network_cache`.`cn` - LEFT JOIN `network_address_cache` - ON `network_address_cache`.`network` = `network_cache`.`cn` - ''' - - def __init__(self, parameters): - super(NetworkQuery, self).__init__('network', parameters) - - class Cache(cache.Cache): tables = ('network_cache', 'network_alias_cache', 'network_address_cache') @@ -74,10 +57,36 @@ class Cache(cache.Cache): CREATE INDEX IF NOT EXISTS `network_address_idx` ON `network_address_cache`(`network`); ''' - def retrieve(self, parameters): - query = NetworkQuery(parameters) - for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('alias', 'ipNetworkNumber', )): - yield row['cn'], row['alias'], row['ipNetworkNumber'] + retrieve_sql = ''' + SELECT `network_cache`.`cn` AS `cn`, + `network_alias_cache`.`cn` AS `alias`, + `network_address_cache`.`ipNetworkNumber` AS `ipNetworkNumber`, + `network_cache`.`mtime` AS `mtime` + FROM `network_cache` + LEFT JOIN `network_alias_cache` + ON `network_alias_cache`.`network` = `network_cache`.`cn` + LEFT JOIN `network_address_cache` + ON `network_address_cache`.`network` = `network_cache`.`cn` + ''' + + retrieve_by = dict( + cn=''' + ( `network_cache`.`cn` = ? OR + `network_cache`.`cn` IN ( + SELECT `by_alias`.`network` + FROM `network_alias_cache` `by_alias` + WHERE `by_alias`.`cn` = ?)) + ''', + ipNetworkNumber=''' + `network_cache`.`cn` IN ( + SELECT `by_ipNetworkNumber`.`network` + FROM `network_address_cache` `by_ipNetworkNumber` + WHERE `by_ipNetworkNumber`.`ipNetworkNumber` = ?) + ''', + ) + + group_by = (0, ) # cn + group_columns = (1, 2) # alias, ipNetworkNumber class NetworkRequest(common.Request): diff --git a/pynslcd/protocol.py b/pynslcd/protocol.py index 122673d..1472c04 100644 --- a/pynslcd/protocol.py +++ b/pynslcd/protocol.py @@ -52,10 +52,26 @@ class Cache(cache.Cache): CREATE INDEX IF NOT EXISTS `protocol_alias_idx` ON `protocol_alias_cache`(`protocol`); ''' - def retrieve(self, parameters): - query = cache.CnAliasedQuery('protocol', parameters) - for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('alias', )): - yield row['cn'], row['alias'], row['ipProtocolNumber'] + retrieve_sql = ''' + SELECT `protocol_cache`.`cn` AS `cn`, `protocol_alias_cache`.`cn` AS `alias`, + `ipProtocolNumber`, `mtime` + FROM `protocol_cache` + LEFT JOIN `protocol_alias_cache` + ON `protocol_alias_cache`.`protocol` = `protocol_cache`.`cn` + ''' + + retrieve_by = dict( + cn=''' + ( `protocol_cache`.`cn` = ? OR + `protocol_cache`.`cn` IN ( + SELECT `by_alias`.`protocol` + FROM `protocol_alias_cache` `by_alias` + WHERE `by_alias`.`cn` = ?)) + ''', + ) + + group_by = (0, ) # cn + group_columns = (1, ) # alias class ProtocolRequest(common.Request): diff --git a/pynslcd/rpc.py b/pynslcd/rpc.py index 98a0ecc..2a241fd 100644 --- a/pynslcd/rpc.py +++ b/pynslcd/rpc.py @@ -52,10 +52,26 @@ class Cache(cache.Cache): CREATE INDEX IF NOT EXISTS `rpc_alias_idx` ON `rpc_alias_cache`(`rpc`); ''' - def retrieve(self, parameters): - query = cache.CnAliasedQuery('rpc', parameters) - for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('alias', )): - yield row['cn'], row['alias'], row['oncRpcNumber'] + retrieve_sql = ''' + SELECT `rpc_cache`.`cn` AS `cn`, `rpc_alias_cache`.`cn` AS `alias`, + `oncRpcNumber`, `mtime` + FROM `rpc_cache` + LEFT JOIN `rpc_alias_cache` + ON `rpc_alias_cache`.`rpc` = `rpc_cache`.`cn` + ''' + + retrieve_by = dict( + cn=''' + ( `rpc_cache`.`cn` = ? OR + `rpc_cache`.`cn` IN ( + SELECT `by_alias`.`rpc` + FROM `rpc_alias_cache` `by_alias` + WHERE `by_alias`.`cn` = ?)) + ''', + ) + + group_by = (0, ) # cn + group_columns = (1, ) # alias class RpcRequest(common.Request): diff --git a/pynslcd/service.py b/pynslcd/service.py index 6f55cc1..c27f485 100644 --- a/pynslcd/service.py +++ b/pynslcd/service.py @@ -40,33 +40,6 @@ class Search(search.LDAPSearch): required = ('cn', 'ipServicePort', 'ipServiceProtocol') -class ServiceQuery(cache.CnAliasedQuery): - - sql = ''' - SELECT `service_cache`.*, - `service_alias_cache`.`cn` AS `alias` - FROM `service_cache` - LEFT JOIN `service_alias_cache` - ON `service_alias_cache`.`ipServicePort` = `service_cache`.`ipServicePort` - AND `service_alias_cache`.`ipServiceProtocol` = `service_cache`.`ipServiceProtocol` - ''' - - cn_join = ''' - LEFT JOIN `service_alias_cache` `cn_alias` - ON `cn_alias`.`ipServicePort` = `service_cache`.`ipServicePort` - AND `cn_alias`.`ipServiceProtocol` = `service_cache`.`ipServiceProtocol` - ''' - - def __init__(self, parameters): - super(ServiceQuery, self).__init__('service', {}) - for k, v in parameters.items(): - if k == 'cn': - self.add_query(self.cn_join) - self.add_where('(`service_cache`.`cn` = ? OR `cn_alias`.`cn` = ?)', [v, v]) - else: - self.add_where('`service_cache`.`%s` = ?' % k, [v]) - - class Cache(cache.Cache): tables = ('service_cache', 'service_alias_cache') @@ -90,6 +63,34 @@ class Cache(cache.Cache): CREATE INDEX IF NOT EXISTS `service_alias_idx2` ON `service_alias_cache`(`ipServiceProtocol`); ''' + retrieve_sql = ''' + SELECT `service_cache`.`cn` AS `cn`, + `service_alias_cache`.`cn` AS `alias`, + `service_cache`.`ipServicePort`, + `service_cache`.`ipServiceProtocol`, + `mtime` + FROM `service_cache` + LEFT JOIN `service_alias_cache` + ON `service_alias_cache`.`ipServicePort` = `service_cache`.`ipServicePort` + AND `service_alias_cache`.`ipServiceProtocol` = `service_cache`.`ipServiceProtocol` + ''' + + retrieve_by = dict( + cn=''' + ( `service_cache`.`cn` = ? OR + 0 < ( + SELECT COUNT(*) + FROM `service_alias_cache` `by_alias` + WHERE `by_alias`.`cn` = ? + AND `by_alias`.`ipServicePort` = `service_cache`.`ipServicePort` + AND `by_alias`.`ipServiceProtocol` = `service_cache`.`ipServiceProtocol` + )) + ''', + ) + + group_by = (0, 2, 3) # cn, ipServicePort, ipServiceProtocol + group_columns = (1, ) # alias + def store(self, name, aliases, port, protocol): self.con.execute(''' INSERT OR REPLACE INTO `service_cache` @@ -107,11 +108,6 @@ class Cache(cache.Cache): (?, ?, ?) ''', ((port, protocol, alias) for alias in aliases)) - def retrieve(self, parameters): - query = ServiceQuery(parameters) - for row in cache.RowGrouper(query.execute(self.con), ('cn', 'ipServicePort', 'ipServiceProtocol'), ('alias', )): - yield row['cn'], row['alias'], row['ipServicePort'], row['ipServiceProtocol'] - class ServiceRequest(common.Request): -- cgit v1.2.3