1 files changed, 53 insertions, 36 deletions
diff --git a/pynslcd/service.py b/pynslcd/service.py
index 19f941d..c27f485 100644
--- a/pynslcd/service.py
+++ b/pynslcd/service.py
@@ -40,56 +40,73 @@ class Search(search.LDAPSearch):
     required = ('cn', 'ipServicePort', 'ipServiceProtocol')
 
 
-class ServiceQuery(cache.CnAliasedQuery):
+class Cache(cache.Cache):
 
-    sql = '''
-        SELECT `service_cache`.*,
-               `service_1_cache`.`cn` AS `alias`
+    tables = ('service_cache', 'service_alias_cache')
+
+    create_sql = '''
+        CREATE TABLE IF NOT EXISTS `service_cache`
+          ( `cn` TEXT NOT NULL,
+            `ipServicePort` INTEGER NOT NULL,
+            `ipServiceProtocol` TEXT NOT NULL,
+            `mtime` TIMESTAMP NOT NULL,
+            UNIQUE (`ipServicePort`, `ipServiceProtocol`) );
+        CREATE TABLE IF NOT EXISTS `service_alias_cache`
+          ( `ipServicePort` INTEGER NOT NULL,
+            `ipServiceProtocol` TEXT NOT NULL,
+            `cn` TEXT NOT NULL,
+            FOREIGN KEY(`ipServicePort`) REFERENCES `service_cache`(`ipServicePort`)
+            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_alias_idx1` ON `service_alias_cache`(`ipServicePort`);
+        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_1_cache`
-          ON `service_1_cache`.`ipServicePort` = `service_cache`.`ipServicePort`
-          AND `service_1_cache`.`ipServiceProtocol` = `service_cache`.`ipServiceProtocol`
-        '''
-
-    cn_join = '''
-        LEFT JOIN `service_1_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):
+        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`
             VALUES
               (?, ?, ?, ?)
-            ''', (name, port, protocol, datetime.datetime.now()))
+        ''', (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))
+        ''', (port, protocol))
         self.con.executemany('''
-            INSERT INTO `service_1_cache`
+            INSERT INTO `service_alias_cache`
             VALUES
               (?, ?, ?)
-            ''', ((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']
+        ''', ((port, protocol, alias) for alias in aliases))
 
 
 class ServiceRequest(common.Request):