def connCM(conn=None, commit=True):
if conn == None:
with mysqlutil.open_url_cm(DB_URL) as conn:
if commit:
with dbutil.doTransaction(conn) as conn:
yield conn
else:
yield conn
else:
if commit:
with dbutil.doTransaction(conn) as conn:
yield conn
else:
yield conn
def set(self, key, value):
encodedValue = json.dumps(value)
sql = "INSERT INTO " + self.table + " (id, value, create_time, mod_time, access_time) VALUES (%s, %s, NOW(), NOW(), NOW()) "
sql += " ON DUPLICATE KEY UPDATE value=%s, mod_time=NOW(), access_time=NOW() "
with self.manager as conn:
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, sql, args=[self._cache_hash(key), encodedValue, encodedValue])
def put(self, key, value):
encodedKey = json.dumps(key)
encodedValue = json.dumps(value)
with self.connect() as conn:
with dbutil.doTransaction(conn):
sql = 'INSERT INTO ' + self.table + ' (name, value) VALUES (%s, %s) ON DUPLICATE KEY UPDATE value=%s'
return dbutil.insertSQL(conn, sql, args=[encodedKey, encodedValue, encodedValue])
def put(self, key, value):
encodedKey = json.dumps(key)
encodedValue = json.dumps(value)
with self.manager as conn:
with dbutil.doTransaction(conn):
sql = 'INSERT INTO ' + self.table + ' (name, value) VALUES (%s, %s) ON DUPLICATE KEY UPDATE value=%s'
return dbutil.insertSQL(
conn, sql, args=[encodedKey, encodedValue, encodedValue])
def send(self, message, timeout=None):
'''
timeout: if None, the default read lock timeout is used. if not None, this is the number of seconds before the read lock on this message expires.
'''
if timeout is None:
timeout = self.timeout
sql = 'INSERT INTO message_queue (queue, message, timeout) VALUES (%s, %s, %s)'
with self.manager as conn:
with dbutil.doTransaction(conn):
return dbutil.insertSQL(conn, sql, args=[self.queue, message, timeout])
def create(self):
sql = '''CREATE TABLE IF NOT EXISTS ''' + self.table + ''' (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE KEY,
value blob,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX key_index (name)
) ENGINE = InnoDB '''
with self.manager as conn:
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, sql)
return self
def create(self):
sql = '''CREATE TABLE IF NOT EXISTS ''' + self.table + ''' (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE KEY,
value blob,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX key_index (name)
) ENGINE = InnoDB '''
with self.connect() as conn:
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, sql)
return self
def get(self, key, default=None):
sql = " SELECT value FROM " + self.table + " WHERE id=%s"
with self.manager as conn:
results = dbutil.selectSQL(conn, sql, args=[self._cache_hash(key)])
if results:
value = json.loads(results[0][0])
else:
value = default
# update access time
sql = "UPDATE " + self.table + " SET access_time=NOW() WHERE id=%s"
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, sql, args=[self._cache_hash(key)])
return value
def _create(self):
sql = '''CREATE TABLE IF NOT EXISTS message_queue (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
queue varchar(200) NOT NULL,
message blob,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
read_time TIMESTAMP,
lock_time TIMESTAMP,
timeout INT NOT NULL,
locked BOOLEAN NOT NULL DEFAULT FALSE,
INDEX queue_index (queue)
) ENGINE = InnoDB '''
with self.manager as conn:
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, sql)
def _readUnhandled(self):
'''
Reads the next message from the queue.
Returns: message_id, message.
Use message_id to delete() the message when done or to changeTimeout() of the message if necessary.
'''
with self.manager as conn:
with dbutil.doTransaction(conn):
# read first available message (pending or lock timeout)
sql = 'SELECT id, message FROM message_queue WHERE queue = %s AND (NOT locked OR lock_time < CURRENT_TIMESTAMP)'
sql += ' ORDER BY id ASC LIMIT 1 FOR UPDATE '
results = dbutil.selectSQL(conn, sql, args=[self.queue])
if results:
id, message = results[0]
# mark message unavailable for reading for timeout seconds.
sql = 'UPDATE message_queue SET locked = TRUE, read_time = CURRENT_TIMESTAMP, lock_time = ADDTIME(CURRENT_TIMESTAMP, SEC_TO_TIME(timeout)) WHERE id = %s'
dbutil.executeSQL(conn, sql, args=[id])
return id, message
else:
raise EmptyQueueError(str(self.queue))
def remove(self, key):
encodedKey = json.dumps(key)
sql = 'DELETE FROM ' + self.table + ' WHERE name = %s'
with self.connect() as conn:
with dbutil.doTransaction(conn):
return dbutil.executeSQL(conn, sql, args=[encodedKey])
def remove(self, key):
sql = "DELETE FROM " + self.table + " WHERE id=%s"
with self.manager as conn:
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, sql, args=[self._cache_hash(key)])
def drop(self):
with self.connect() as conn:
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, 'DROP TABLE IF EXISTS ' + self.table)
return self
def _drop(self):
with self.manager as conn:
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, 'drop table if exists message_queue')
def changeTimeout(self, id, timeout):
''' changes read lock to <timeout> seconds from now. '''
sql = 'UPDATE message_queue SET lock_time = ADDTIME(CURRENT_TIMESTAMP, SEC_TO_TIME(%s)) WHERE id = %s'
with self.manager as conn:
with dbutil.doTransaction(conn):
return dbutil.executeSQL(conn, sql, args=[timeout, id])
def delete(self, id):
sql = 'DELETE FROM message_queue WHERE id = %s '
with self.manager as conn:
with dbutil.doTransaction(conn):
return dbutil.executeSQL(conn, sql, args=[id])
def remove(self, key):
encodedKey = json.dumps(key)
sql = 'DELETE FROM ' + self.table + ' WHERE name = %s'
with self.manager as conn:
with dbutil.doTransaction(conn):
return dbutil.executeSQL(conn, sql, args=[encodedKey])
def drop(self):
with self.manager as conn:
with dbutil.doTransaction(conn):
dbutil.executeSQL(conn, 'DROP TABLE IF EXISTS ' + self.table)
return self
