############################################################################## # # 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