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.

12 files changed, 193 insertions, 177 deletions

diff --git a/pynslcd/alias.py b/pynslcd/alias.py
index 46c4d6b..bf313c4 100644
--- a/pynslcd/alias.py
+++ b/pynslcd/alias.py
@@ -37,13 +37,25 @@ class Search(search.LDAPSearch):
 
 class Cache(cache.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`
+          ( `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`);
+    '''
+
     retrieve_sql = '''
         SELECT `alias_cache`.`cn` AS `cn`,
                `alias_1_cache`.`rfc822MailMember` AS `rfc822MailMember`
         FROM `alias_cache`
         LEFT JOIN `alias_1_cache`
           ON `alias_1_cache`.`alias` = `alias_cache`.`cn`
-        '''
+    '''
 
     def retrieve(self, parameters):
         query = cache.Query(self.retrieve_sql, parameters)
diff --git a/pynslcd/cache.py b/pynslcd/cache.py
index 7089d41..c730c5a 100644
--- a/pynslcd/cache.py
+++ b/pynslcd/cache.py
@@ -27,171 +27,8 @@ import sqlite3
 
 
 # TODO: probably create a config table
-
-
-# FIXME: store the cache in the right place and make it configurable
-filename = '/tmp/cache.sqlite'
-dirname = os.path.dirname(filename)
-if not os.path.isdir(dirname):
-    os.mkdir(dirname)
-con = sqlite3.connect(filename,
-                detect_types=sqlite3.PARSE_DECLTYPES, check_same_thread=False)
-con.row_factory = sqlite3.Row
-
 # FIXME: have some way to remove stale entries from the cache if all items from LDAP are queried (perhas use TTL from all request)
 
-# set up the database
-con.executescript('''
-
-    -- store temporary tables in memory
-    PRAGMA temp_store = MEMORY;
-
-    -- disable sync() on database (corruption on disk failure)
-    PRAGMA synchronous = OFF;
-
-    -- put journal in memory (corruption if crash during transaction)
-    PRAGMA journal_mode = MEMORY;
-
-    -- tables for alias cache
-    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`
-      ( `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`);
-
-    -- table for ethernet cache
-    CREATE TABLE IF NOT EXISTS `ether_cache`
-      ( `cn` TEXT NOT NULL COLLATE NOCASE,
-        `macAddress` TEXT NOT NULL COLLATE NOCASE,
-        `mtime` TIMESTAMP NOT NULL,
-        UNIQUE (`cn`, `macAddress`) );
-
-    -- table for group cache
-    CREATE TABLE IF NOT EXISTS `group_cache`
-      ( `cn` TEXT PRIMARY KEY,
-        `userPassword` TEXT,
-        `gidNumber` INTEGER NOT NULL UNIQUE,
-        `mtime` TIMESTAMP NOT NULL );
-    CREATE TABLE IF NOT EXISTS `group_3_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`);
-
-    -- tables for host cache
-    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`
-      ( `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`
-      ( `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`);
-
-    -- 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`) );
-
-    -- tables for network cache
-    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`
-      ( `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,
-        `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`);
-
-    -- table for passwd cache
-    CREATE TABLE IF NOT EXISTS `passwd_cache`
-      ( `uid` TEXT PRIMARY KEY,
-        `userPassword` TEXT,
-        `uidNumber` INTEGER NOT NULL UNIQUE,
-        `gidNumber` INTEGER NOT NULL,
-        `gecos` TEXT,
-        `homeDirectory` TEXT,
-        `loginShell` TEXT,
-        `mtime` TIMESTAMP NOT NULL );
-
-    -- table for protocol cache
-    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`);
-
-    -- table for rpc cache
-    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`
-      ( `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`);
-
-    -- tables for service cache
-    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_1_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_1_idx1` ON `service_1_cache`(`ipServicePort`);
-    CREATE INDEX IF NOT EXISTS `service_1_idx2` ON `service_1_cache`(`ipServiceProtocol`);
-
-    -- table for shadow cache
-    CREATE TABLE IF NOT EXISTS `shadow_cache`
-      ( `uid` TEXT PRIMARY KEY,
-        `userPassword` TEXT,
-        `shadowLastChange` INTEGER,
-        `shadowMin` INTEGER,
-        `shadowMax` INTEGER,
-        `shadowWarning` INTEGER,
-        `shadowInactive` INTEGER,
-        `shadowExpire` INTEGER,
-        `shadowFlag` INTEGER,
-        `mtime` TIMESTAMP NOT NULL );
-
-    ''')
-
 
 class Query(object):
 
@@ -274,8 +111,13 @@ class RowGrouper(object):
 class Cache(object):
 
     def __init__(self):
-        self.con = con
+        self.con = _get_connection()
         self.table = sys.modules[self.__module__].__name__
+        self.create()
+
+    def create(self):
+        """Create the needed tables if neccesary."""
+        self.con.executescript(self.create_sql)
 
     def store(self, *values):
         """Store the values in the cache for the specified table."""
@@ -288,17 +130,17 @@ class Cache(object):
                 simple_values.append(v)
         simple_values.append(datetime.datetime.now())
         args = ', '.join(len(simple_values) * ('?', ))
-        con.execute('''
+        self.con.execute('''
             INSERT OR REPLACE INTO %s_cache
             VALUES
               (%s)
             ''' % (self.table, args), simple_values)
         for n, vlist in multi_values.items():
-            con.execute('''
+            self.con.execute('''
                 DELETE FROM %s_%d_cache
                 WHERE `%s` = ?
                 ''' % (self.table, n, self.table), (values[0], ))
-            con.executemany('''
+            self.con.executemany('''
                 INSERT INTO %s_%d_cache
                 VALUES
                   (?, ?)
@@ -311,3 +153,32 @@ class Cache(object):
             FROM %s_cache
             ''' % self.table, parameters)
         return (list(x)[:-1] for x in query.execute(self.con))
+
+
+# the connection to the sqlite database
+_connection = None
+
+
+# FIXME: make tread safe (is this needed the way the caches are initialised?)
+def _get_connection():
+    global _connection
+    if _connection is None:
+        filename = '/tmp/pynslcd_cache.sqlite'
+        dirname = os.path.dirname(filename)
+        if not os.path.isdir(dirname):
+            os.mkdir(dirname)
+        connection = sqlite3.connect(
+            filename, detect_types=sqlite3.PARSE_DECLTYPES,
+            check_same_thread=False)
+        connection.row_factory = sqlite3.Row
+        #  initialise connection properties
+        connection.executescript('''
+            -- store temporary tables in memory
+            PRAGMA temp_store = MEMORY;
+            -- disable sync() on database (corruption on disk failure)
+            PRAGMA synchronous = OFF;
+            -- put journal in memory (corruption if crash during transaction)
+            PRAGMA journal_mode = MEMORY;
+            ''')
+        _connection = connection
+    return _connection
diff --git a/pynslcd/ether.py b/pynslcd/ether.py
index d5d8c06..e5060ca 100644
--- a/pynslcd/ether.py
+++ b/pynslcd/ether.py
@@ -59,7 +59,14 @@ class Search(search.LDAPSearch):
 
 
 class Cache(cache.Cache):
-    pass
+
+    create_sql = '''
+        CREATE TABLE IF NOT EXISTS `ether_cache`
+          ( `cn` TEXT NOT NULL COLLATE NOCASE,
+            `macAddress` TEXT NOT NULL COLLATE NOCASE,
+            `mtime` TIMESTAMP NOT NULL,
+            UNIQUE (`cn`, `macAddress`) );
+    '''
 
 
 class EtherRequest(common.Request):
diff --git a/pynslcd/group.py b/pynslcd/group.py
index 2868d96..f893ac1 100644
--- a/pynslcd/group.py
+++ b/pynslcd/group.py
@@ -75,12 +75,26 @@ class Search(search.LDAPSearch):
 
 class Cache(cache.Cache):
 
+    create_sql = '''
+        CREATE TABLE IF NOT EXISTS `group_cache`
+          ( `cn` TEXT PRIMARY KEY,
+            `userPassword` TEXT,
+            `gidNumber` INTEGER NOT NULL UNIQUE,
+            `mtime` TIMESTAMP NOT NULL );
+        CREATE TABLE IF NOT EXISTS `group_3_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`);
+    '''
+
     retrieve_sql = '''
         SELECT `cn`, `userPassword`, `gidNumber`, `memberUid`
         FROM `group_cache`
         LEFT JOIN `group_3_cache`
           ON `group_3_cache`.`group` = `group_cache`.`cn`
-        '''
+    '''
 
     def retrieve(self, parameters):
         query = cache.Query(self.retrieve_sql, parameters)
diff --git a/pynslcd/host.py b/pynslcd/host.py
index ffd9588..990cec3 100644
--- a/pynslcd/host.py
+++ b/pynslcd/host.py
@@ -53,6 +53,24 @@ class HostQuery(cache.CnAliasedQuery):
 
 class Cache(cache.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`
+          ( `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`
+          ( `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`);
+    '''
+
     def retrieve(self, parameters):
         query = HostQuery(parameters)
         for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('alias', 'ipHostNumber', )):
diff --git a/pynslcd/netgroup.py b/pynslcd/netgroup.py
index 20f8779..7be2936 100644
--- a/pynslcd/netgroup.py
+++ b/pynslcd/netgroup.py
@@ -42,7 +42,15 @@ class Search(search.LDAPSearch):
 
 
 class Cache(cache.Cache):
-    pass
+
+    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`) );
+    '''
 
 
 class NetgroupRequest(common.Request):
diff --git a/pynslcd/network.py b/pynslcd/network.py
index dc91d68..4b3e8bb 100644
--- a/pynslcd/network.py
+++ b/pynslcd/network.py
@@ -54,6 +54,24 @@ class NetworkQuery(cache.CnAliasedQuery):
 
 class Cache(cache.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`
+          ( `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,
+            `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`);
+    '''
+
     def retrieve(self, parameters):
         query = NetworkQuery(parameters)
         for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('alias', 'ipNetworkNumber', )):
diff --git a/pynslcd/passwd.py b/pynslcd/passwd.py
index 7504961..a8e407f 100644
--- a/pynslcd/passwd.py
+++ b/pynslcd/passwd.py
@@ -47,7 +47,18 @@ class Search(search.LDAPSearch):
 
 
 class Cache(cache.Cache):
-    pass
+
+    create_sql = '''
+        CREATE TABLE IF NOT EXISTS `passwd_cache`
+          ( `uid` TEXT PRIMARY KEY,
+            `userPassword` TEXT,
+            `uidNumber` INTEGER NOT NULL UNIQUE,
+            `gidNumber` INTEGER NOT NULL,
+            `gecos` TEXT,
+            `homeDirectory` TEXT,
+            `loginShell` TEXT,
+            `mtime` TIMESTAMP NOT NULL );
+    '''
 
 
 class PasswdRequest(common.Request):
@@ -106,7 +117,6 @@ class PasswdByUidRequest(PasswdRequest):
         self.fp.write_int32(constants.NSLCD_RESULT_END)
 
 
-
 class PasswdAllRequest(PasswdRequest):
 
     action = constants.NSLCD_ACTION_PASSWD_ALL
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', )):
diff --git a/pynslcd/rpc.py b/pynslcd/rpc.py
index f20960e..24cf673 100644
--- a/pynslcd/rpc.py
+++ b/pynslcd/rpc.py
@@ -37,6 +37,19 @@ class Search(search.LDAPSearch):
 
 class Cache(cache.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`
+          ( `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`);
+    '''
+
     def retrieve(self, parameters):
         query = cache.CnAliasedQuery('rpc', parameters)
         for row in cache.RowGrouper(query.execute(self.con), ('cn', ), ('alias', )):
diff --git a/pynslcd/service.py b/pynslcd/service.py
index 19f941d..260941a 100644
--- a/pynslcd/service.py
+++ b/pynslcd/service.py
@@ -69,22 +69,41 @@ class ServiceQuery(cache.CnAliasedQuery):
 
 class Cache(cache.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_1_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_1_idx1` ON `service_1_cache`(`ipServicePort`);
+        CREATE INDEX IF NOT EXISTS `service_1_idx2` ON `service_1_cache`(`ipServiceProtocol`);
+    '''
+
     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`
             WHERE `ipServicePort` = ?
               AND `ipServiceProtocol` = ?
-            ''', (port, protocol))
+        ''', (port, protocol))
         self.con.executemany('''
             INSERT INTO `service_1_cache`
             VALUES
               (?, ?, ?)
-            ''', ((port, protocol, alias) for alias in aliases))
+        ''', ((port, protocol, alias) for alias in aliases))
 
     def retrieve(self, parameters):
         query = ServiceQuery(parameters)
diff --git a/pynslcd/shadow.py b/pynslcd/shadow.py
index bedac50..5fd0aa9 100644
--- a/pynslcd/shadow.py
+++ b/pynslcd/shadow.py
@@ -44,7 +44,20 @@ class Search(search.LDAPSearch):
 
 
 class Cache(cache.Cache):
-    pass
+
+    create_sql = '''
+        CREATE TABLE IF NOT EXISTS `shadow_cache`
+          ( `uid` TEXT PRIMARY KEY,
+            `userPassword` TEXT,
+            `shadowLastChange` INTEGER,
+            `shadowMin` INTEGER,
+            `shadowMax` INTEGER,
+            `shadowWarning` INTEGER,
+            `shadowInactive` INTEGER,
+            `shadowExpire` INTEGER,
+            `shadowFlag` INTEGER,
+            `mtime` TIMESTAMP NOT NULL );
+    '''
 
 
 class ShadowRequest(common.Request):