2
# cache.py - caching layer for pynslcd
4
# Copyright (C) 2012 Arthur de Jong
6
# This library is free software; you can redistribute it and/or
7
# modify it under the terms of the GNU Lesser General Public
8
# License as published by the Free Software Foundation; either
9
# version 2.1 of the License, or (at your option) any later version.
11
# This library is distributed in the hope that it will be useful,
12
# but WITHOUT ANY WARRANTY; without even the implied warranty of
13
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14
# Lesser General Public License for more details.
16
# You should have received a copy of the GNU Lesser General Public
17
# License along with this library; if not, write to the Free Software
18
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
29
# TODO: probably create a config table
31
# FIXME: store the cache in the right place and make it configurable
32
filename = '/var/run/nslcd/cache.sqlite'
33
dirname = os.path.dirname(filename)
34
if not os.path.isdir(dirname):
36
con = sqlite3.connect(filename,
37
detect_types=sqlite3.PARSE_DECLTYPES, check_same_thread=False)
38
con.row_factory = sqlite3.Row
40
# FIXME: have some way to remove stale entries from the cache if all items from LDAP are queried (perhas use TTL from all request)
45
-- store temporary tables in memory
46
PRAGMA temp_store = MEMORY;
48
-- disable sync() on database (corruption on disk failure)
49
PRAGMA synchronous = OFF;
51
-- put journal in memory (corruption if crash during transaction)
52
PRAGMA journal_mode = MEMORY;
54
-- tables for alias cache
55
CREATE TABLE IF NOT EXISTS `alias_cache`
56
( `cn` TEXT PRIMARY KEY COLLATE NOCASE,
57
`mtime` TIMESTAMP NOT NULL );
58
CREATE TABLE IF NOT EXISTS `alias_1_cache`
59
( `alias` TEXT NOT NULL COLLATE NOCASE,
60
`rfc822MailMember` TEXT NOT NULL,
61
FOREIGN KEY(`alias`) REFERENCES `alias_cache`(`cn`)
62
ON DELETE CASCADE ON UPDATE CASCADE );
63
CREATE INDEX IF NOT EXISTS `alias_1_idx` ON `alias_1_cache`(`alias`);
65
-- table for ethernet cache
66
CREATE TABLE IF NOT EXISTS `ether_cache`
67
( `cn` TEXT NOT NULL COLLATE NOCASE,
68
`macAddress` TEXT NOT NULL COLLATE NOCASE,
69
`mtime` TIMESTAMP NOT NULL,
70
UNIQUE (`cn`, `macAddress`) );
72
-- table for group cache
73
CREATE TABLE IF NOT EXISTS `group_cache`
74
( `cn` TEXT PRIMARY KEY,
76
`gidNumber` INTEGER NOT NULL UNIQUE,
77
`mtime` TIMESTAMP NOT NULL );
78
CREATE TABLE IF NOT EXISTS `group_3_cache`
79
( `group` TEXT NOT NULL,
80
`memberUid` TEXT NOT NULL,
81
FOREIGN KEY(`group`) REFERENCES `group_cache`(`cn`)
82
ON DELETE CASCADE ON UPDATE CASCADE );
83
CREATE INDEX IF NOT EXISTS `group_3_idx` ON `group_3_cache`(`group`);
85
-- tables for host cache
86
CREATE TABLE IF NOT EXISTS `host_cache`
87
( `cn` TEXT PRIMARY KEY COLLATE NOCASE,
88
`mtime` TIMESTAMP NOT NULL );
89
CREATE TABLE IF NOT EXISTS `host_1_cache`
90
( `host` TEXT NOT NULL COLLATE NOCASE,
91
`cn` TEXT NOT NULL COLLATE NOCASE,
92
FOREIGN KEY(`host`) REFERENCES `host_cache`(`cn`)
93
ON DELETE CASCADE ON UPDATE CASCADE );
94
CREATE INDEX IF NOT EXISTS `host_1_idx` ON `host_1_cache`(`host`);
95
CREATE TABLE IF NOT EXISTS `host_2_cache`
96
( `host` TEXT NOT NULL COLLATE NOCASE,
97
`ipHostNumber` TEXT NOT NULL,
98
FOREIGN KEY(`host`) REFERENCES `host_cache`(`cn`)
99
ON DELETE CASCADE ON UPDATE CASCADE );
100
CREATE INDEX IF NOT EXISTS `host_2_idx` ON `host_2_cache`(`host`);
102
-- FIXME: this does not work as entries are never removed from the cache
103
CREATE TABLE IF NOT EXISTS `netgroup_cache`
104
( `cn` TEXT NOT NULL,
105
`member` TEXT NOT NULL,
106
`mtime` TIMESTAMP NOT NULL,
107
UNIQUE (`cn`, `member`) );
109
-- tables for network cache
110
CREATE TABLE IF NOT EXISTS `network_cache`
111
( `cn` TEXT PRIMARY KEY COLLATE NOCASE,
112
`mtime` TIMESTAMP NOT NULL );
113
CREATE TABLE IF NOT EXISTS `network_1_cache`
114
( `network` TEXT NOT NULL COLLATE NOCASE,
115
`cn` TEXT NOT NULL COLLATE NOCASE,
116
FOREIGN KEY(`network`) REFERENCES `network_cache`(`cn`)
117
ON DELETE CASCADE ON UPDATE CASCADE );
118
CREATE INDEX IF NOT EXISTS `network_1_idx` ON `network_1_cache`(`network`);
119
CREATE TABLE IF NOT EXISTS `network_2_cache`
120
( `network` TEXT NOT NULL,
121
`ipNetworkNumber` TEXT NOT NULL,
122
FOREIGN KEY(`network`) REFERENCES `network_cache`(`cn`)
123
ON DELETE CASCADE ON UPDATE CASCADE );
124
CREATE INDEX IF NOT EXISTS `network_2_idx` ON `network_2_cache`(`network`);
126
-- table for passwd cache
127
CREATE TABLE IF NOT EXISTS `passwd_cache`
128
( `uid` TEXT PRIMARY KEY,
130
`uidNumber` INTEGER NOT NULL UNIQUE,
131
`gidNumber` INTEGER NOT NULL,
133
`homeDirectory` TEXT,
135
`mtime` TIMESTAMP NOT NULL );
137
-- table for protocol cache
138
CREATE TABLE IF NOT EXISTS `protocol_cache`
139
( `cn` TEXT PRIMARY KEY,
140
`ipProtocolNumber` INTEGER NOT NULL,
141
`mtime` TIMESTAMP NOT NULL );
142
CREATE TABLE IF NOT EXISTS `protocol_1_cache`
143
( `protocol` TEXT NOT NULL,
145
FOREIGN KEY(`protocol`) REFERENCES `protocol_cache`(`cn`)
146
ON DELETE CASCADE ON UPDATE CASCADE );
147
CREATE INDEX IF NOT EXISTS `protocol_1_idx` ON `protocol_1_cache`(`protocol`);
149
-- table for rpc cache
150
CREATE TABLE IF NOT EXISTS `rpc_cache`
151
( `cn` TEXT PRIMARY KEY,
152
`oncRpcNumber` INTEGER NOT NULL,
153
`mtime` TIMESTAMP NOT NULL );
154
CREATE TABLE IF NOT EXISTS `rpc_1_cache`
155
( `rpc` TEXT NOT NULL,
157
FOREIGN KEY(`rpc`) REFERENCES `rpc_cache`(`cn`)
158
ON DELETE CASCADE ON UPDATE CASCADE );
159
CREATE INDEX IF NOT EXISTS `rpc_1_idx` ON `rpc_1_cache`(`rpc`);
161
-- tables for service cache
162
CREATE TABLE IF NOT EXISTS `service_cache`
163
( `cn` TEXT NOT NULL,
164
`ipServicePort` INTEGER NOT NULL,
165
`ipServiceProtocol` TEXT NOT NULL,
166
`mtime` TIMESTAMP NOT NULL,
167
UNIQUE (`ipServicePort`, `ipServiceProtocol`) );
168
CREATE TABLE IF NOT EXISTS `service_1_cache`
169
( `ipServicePort` INTEGER NOT NULL,
170
`ipServiceProtocol` TEXT NOT NULL,
172
FOREIGN KEY(`ipServicePort`) REFERENCES `service_cache`(`ipServicePort`)
173
ON DELETE CASCADE ON UPDATE CASCADE,
174
FOREIGN KEY(`ipServiceProtocol`) REFERENCES `service_cache`(`ipServiceProtocol`)
175
ON DELETE CASCADE ON UPDATE CASCADE );
176
CREATE INDEX IF NOT EXISTS `service_1_idx1` ON `service_1_cache`(`ipServicePort`);
177
CREATE INDEX IF NOT EXISTS `service_1_idx2` ON `service_1_cache`(`ipServiceProtocol`);
179
-- table for shadow cache
180
CREATE TABLE IF NOT EXISTS `shadow_cache`
181
( `uid` TEXT PRIMARY KEY,
183
`shadowLastChange` INTEGER,
186
`shadowWarning` INTEGER,
187
`shadowInactive` INTEGER,
188
`shadowExpire` INTEGER,
189
`shadowFlag` INTEGER,
190
`mtime` TIMESTAMP NOT NULL );
197
def __init__(self, query, parameters=None):
202
for k, v in parameters.items():
203
self.add_where('`%s` = ?' % k, [v])
205
def add_query(self, query):
206
self.query += ' ' + query
208
def add_where(self, where, parameters):
209
self.wheres.append(where)
210
self.parameters += parameters
212
def execute(self, con):
215
query += ' WHERE ' + ' AND '.join(self.wheres)
217
return c.execute(query, self.parameters)
220
class CnAliasedQuery(Query):
223
SELECT `%(table)s_cache`.*,
224
`%(table)s_1_cache`.`cn` AS `alias`
225
FROM `%(table)s_cache`
226
LEFT JOIN `%(table)s_1_cache`
227
ON `%(table)s_1_cache`.`%(table)s` = `%(table)s_cache`.`cn`
231
LEFT JOIN `%(table)s_1_cache` `cn_alias`
232
ON `cn_alias`.`%(table)s` = `%(table)s_cache`.`cn`
235
def __init__(self, table, parameters):
236
args = dict(table=table)
237
super(CnAliasedQuery, self).__init__(self.sql % args)
238
for k, v in parameters.items():
240
self.add_query(self.cn_join % args)
241
self.add_where('(`%(table)s_cache`.`cn` = ? OR `cn_alias`.`cn` = ?)' % args, [v, v])
243
self.add_where('`%s` = ?' % k, [v])
246
class RowGrouper(object):
247
"""Pass in query results and group the results by a certain specified
250
def __init__(self, results, groupby, columns):
251
self.groupby = groupby
252
self.columns = columns
253
self.results = itertools.groupby(results, key=self.keyfunc)
258
def keyfunc(self, row):
259
return tuple(row[x] for x in self.groupby)
262
groupcols, rows = self.results.next()
263
tmp = dict((x, list()) for x in self.columns)
265
for col in self.columns:
266
if row[col] is not None:
267
tmp[col].append(row[col])
277
self.table = sys.modules[self.__module__].__name__
279
def store(self, *values):
280
"""Store the values in the cache for the specified table."""
283
for n, v in enumerate(values):
284
if isinstance(v, (list, tuple, set)):
287
simple_values.append(v)
288
simple_values.append(datetime.datetime.now())
289
args = ', '.join(len(simple_values) * ('?', ))
291
INSERT OR REPLACE INTO %s_cache
294
''' % (self.table, args), simple_values)
295
for n, vlist in multi_values.items():
297
DELETE FROM %s_%d_cache
299
''' % (self.table, n, self.table), (values[0], ))
301
INSERT INTO %s_%d_cache
304
''' % (self.table, n), ((values[0], x) for x in vlist))
306
def retrieve(self, parameters):
307
"""Retrieve all items from the cache based on the parameters supplied."""
311
''' % self.table, parameters)
312
return (list(x)[:-1] for x in query.execute(self.con))