1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
##############################################################################
#
# Copyright (c) 2005 Nexedi SARL and Contributors. All Rights Reserved.
# Ivan Tyagov <ivan@nexedi.com>
#
# WARNING: This program as such is intended to be used by professional
# programmers who take the whole responsability of assessing all potential
# consequences resulting from its eventual inadequacies and bugs
# End users who are looking for a ready-to-use solution with commercial
# garantees and support are strongly adviced to contract a Free Software
# Service Company
#
# This program is Free Software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
#
##############################################################################
"""
SQL (MySQL) based cache plugin.
"""
from BaseCache import *
import time, base64
try:
import cPickle as pickle
except ImportError:
import pickle
try:
import MySQLdb
except ImportError:
raise CachedMethodError, "MySQLdb module is not available"
class SQLCache(BaseCache):
""" SQL based cache plugin. """
cache_expire_check_interval = 3600
create_table_sql = '''CREATE TABLE %s(cache_id VARCHAR(970) NOT NULL,
value LONGTEXT,
scope VARCHAR(20),
stored_at INT,
cache_duration INT DEFAULT 0,
calculation_time FLOAT,
UNIQUE(cache_id, scope))
'''
insert_key_sql = '''INSERT INTO %s (cache_id, value, scope, stored_at, cache_duration, calculation_time)
VALUES("%s", "%s", "%s", %s, %s, %s)
'''
has_key_sql = '''SELECT count(*)
FROM %s
WHERE cache_id = "%s" and scope="%s"
'''
get_key_sql = '''SELECT value, cache_duration, calculation_time
FROM %s
WHERE cache_id = "%s" and scope="%s"
'''
delete_key_sql = '''DELETE
FROM %s
WHERE cache_id = "%s" and scope="%s"
'''
delete_all_keys_sql = '''DELETE
FROM %s
'''
delete_all_keys_for_scope_sql = '''DELETE
FROM %s
WHERE scope="%s"
'''
delete_expired_keys_sql = '''DELETE
FROM %s
WHERE cache_duration + stored_at < %s and cache_duration!=0
'''
get_scope_list_sql = '''SELECT scope
FROM %s
GROUP BY scope
'''
get_scope_key_list_sql = '''SELECT cache_id
FROM %s
WHERE scope="%s"
'''
def __init__(self, params):
BaseCache.__init__(self)
self._dbConn = None
self._db_server = params.get('server', '')
self._db_user = params.get('user', '')
self._db_passwd = params.get('passwd', '')
self._db_name = params.get('db', '')
self._db_cache_table_name = params.get('cache_table_name')
## since SQL cache is persistent check for expired objects
#self.expireOldCacheEntries(forceCheck=True)
def getCacheStorage(self):
"""
Return current DB connection or create a new one for his thread.
See http://sourceforge.net/docman/display_doc.php?docid=32071&group_id=22307
especially threadsafety part why we create every time a new MySQL db connection object.
"""
try:
from Products.ERP5Type.Utils import get_request
request = get_request()
except ImportError:
request = None
if request:
## Zope/ERP5 environment
dbConn = request.get('_erp5_dbcache_connection', None)
if not dbConn:
## we have not dbConn for this request
dbConn = MySQLdb.connect(host=self._db_server, \
user=self._db_user,\
passwd=self._db_passwd, \
db=self._db_name)
request.set('_erp5_dbcache_connection', dbConn)
return dbConn
else:
## we have already dbConn for this request
return dbConn
else:
## run from unit tests
dbConn = MySQLdb.connect(host=self._db_server, \
user=self._db_user,\
passwd=self._db_passwd, \
db=self._db_name)
return dbConn
def get(self, cache_id, scope, default=None):
sql_query = self.get_key_sql %(self._db_cache_table_name, cache_id, scope)
cursor = self.execSQLQuery(sql_query)
if cursor:
## count return one row only
result = cursor.fetchall()
if 0 < len(result):
## we found results
result = result[0]
decoded_result = pickle.loads(base64.decodestring(result[0]))
self.markCacheHit()
cache_entry = CacheEntry(decoded_result, result[1], result[2])
return cache_entry
else:
## no such cache_id in DB
return None
else:
## DB not available
return None
def set(self, cache_id, scope, value, cache_duration=None, calculation_time=0):
value = base64.encodestring(pickle.dumps(value,2))
if not cache_duration:
## should live forever ==> setting cache_duration = 0 will make it live forever
cache_duration = 0
else:
## we have strict cache_duration defined. we calculate seconds since start of epoch
cache_duration = int(cache_duration)
## Set key in DB
stored_at = int(time.time())
sql_query = self.insert_key_sql %(self._db_cache_table_name, cache_id, value, scope, stored_at, cache_duration, calculation_time)
self.execSQLQuery(sql_query)
self.markCacheMiss()
def expireOldCacheEntries(self, forceCheck = False):
now = time.time()
if forceCheck or (now > (self._last_cache_expire_check_at + self.cache_expire_check_interval)):
## time to check for expired cache items
self._last_cache_expire_check_at = now
my_query = self.delete_expired_keys_sql %(self._db_cache_table_name, now)
self.execSQLQuery(my_query)
def delete(self, cache_id, scope):
my_query = self.delete_key_sql %(self._db_cache_table_name, cache_id, scope)
self.execSQLQuery(my_query)
def has_key(self, cache_id, scope):
my_query = self.has_key_sql %(self._db_cache_table_name, cache_id, scope)
cursor = self.execSQLQuery(my_query)
if cursor:
## count() SQL function will return one row only
result = cursor.fetchall()
result = result[0][0]
if result == 0:
## no such key in DB
return False
elif result==1:
## we have this key in DB
return True
else:
## something wrong in DB model
raise CachedMethodError, "Invalid cache table reltion format. cache_id MUST be unique!"
else:
## DB not available
return False
def getScopeList(self):
rl = []
my_query = self.get_scope_list_sql %(self._db_cache_table_name)
cursor = self.execSQLQuery(my_query)
results = cursor.fetchall()
for result in results:
rl.append(result[0])
return rl
def getScopeKeyList(self, scope):
rl = []
my_query = self.get_scope_key_list_sql %(self._db_cache_table_name, scope)
cursor = self.execSQLQuery(my_query)
results = cursor.fetchall()
for result in results:
rl.append(result[0])
return rl
def clearCache(self):
BaseCache.clearCache(self)
## SQL Cache is a persistent storage rather than delete all entries
## just expire them
## self.expireOldCacheEntries(forceCheck = True):
my_query = self.delete_all_keys_sql %(self._db_cache_table_name)
self.execSQLQuery(my_query)
def clearCacheForScope(self, scope):
my_query = self.delete_all_keys_for_scope_sql %(self._db_cache_table_name, scope)
self.execSQLQuery(my_query)
def execSQLQuery(self, sql_query):
"""
Try to execute sql query.
Return cursor object because some queris can return result
"""
dbConn = self.getCacheStorage()
cursor = dbConn.cursor()
cursor.execute(sql_query)
return cursor