def __init__(self,
database_file,
table_name,
table_schema,
key,
logger=None):
if logger is None:
self.logger = logging.getLogger()
self.logger.addHandler(logging.NullHandler())
else:
self.logger = logger
self._table_name = table_name
self._autocommit = True
self._key = None
self._columns = None
self._schema = None
self._db = SQLiteDB(database_file)
self._filename = database_file
self.recreated = False
# Note: self.schema is a property object
self.schema = table_schema
self._check_schema(
) # possibly creating the table if it does not exist
# Note: self.key is a property object
self.key = key
self._create_index_if_needed()
# Remember not to vacuum from any other method than the constructor,
# since it makes any open transaction commit!
self._execute("VACUUM")
def test_file_gets_created_on_connect():
filename = absolute_filename('test.db')
db = SQLiteDB(filename)
db._get_connection()
assert_true(os.path.exists(filename))
db.close()
delete_file(filename)
def test_connection_is_singleton():
filename = absolute_filename('test.db')
db = SQLiteDB(filename)
c1 = db._get_connection()
c2 = db._get_connection()
assert_true(c1 is c2)
assert_equal(c1, c2)
db.close()
delete_file(filename)
def test_connection_is_singleton():
filename = absolute_filename('test.db')
db = SQLiteDB(filename)
c1 = db._get_connection()
c2 = db._get_connection()
assert_true(c1 is c2)
assert_equal(c1, c2)
db.close()
delete_file(filename)
def __init__(self,
database_file, table_name, table_schema, key, logger=None):
if logger is None:
self.logger = logging.getLogger()
self.logger.addHandler(logging.NullHandler())
else:
self.logger = logger
self._table_name = table_name
self._autocommit = True
self._key = None
self._columns = None
self._schema = None
self._db = SQLiteDB(database_file)
self._filename = database_file
self.recreated = False
# Note: self.schema is a property object
self.schema = table_schema
self._check_schema() # possibly creating the table if it does not exist
# Note: self.key is a property object
self.key = key
self._create_index_if_needed()
# Remember not to vacuum from any other method than the constructor,
# since it makes any open transaction commit!
self._execute("VACUUM")
def test_each_thread_has_its_own_connection():
filename = absolute_filename('test.db')
db = SQLiteDB(filename)
connection1 = db._get_connection()
def other_thread():
connection2 = db._get_connection()
assert_true(connection1 is connection1)
assert_false(connection2 is connection1)
assert_equal(connection1, connection1)
assert_not_equal(connection2, connection1)
db.close()
try:
t1 = threading.Thread(target=other_thread)
t1.start()
t1.join()
finally:
db.close()
delete_file(filename)
def test_each_thread_has_its_own_connection():
filename = absolute_filename('test.db')
db = SQLiteDB(filename)
connection1 = db._get_connection()
def other_thread():
connection2 = db._get_connection()
assert_true(connection1 is connection1)
assert_false(connection2 is connection1)
assert_equal(connection1, connection1)
assert_not_equal(connection2, connection1)
db.close()
try:
t1 = threading.Thread(target=other_thread)
t1.start()
t1.join()
finally:
db.close()
delete_file(filename)
def test_file_gets_created_on_connect():
filename = absolute_filename('test.db')
db = SQLiteDB(filename)
db._get_connection()
assert_true(os.path.exists(filename))
db.close()
delete_file(filename)
def create_onefield_database():
filename = absolute_filename('test.db')
delete_file(filename)
db = SQLiteDB(filename)
db.execute("CREATE TABLE test (field1 text)")
return db
def test_object_creation():
SQLiteDB('foobar')
assert_true(True)
def create_onefield_database():
filename = absolute_filename('test.db')
delete_file(filename)
db = SQLiteDB(filename)
db.execute("CREATE TABLE test (field1 text)")
return db
class AbstractCache(object):
"""A generic SQL-based store.
"Caches" are databases used by FileRock to persistently store
several kinds of data. Each database (which is implemented with
SQLite) contains a set of "records", identified by a column called
"key". A cache can be configured with the following parameters:
TABLE: name of the cache (and of the underlying SQL table)
SCHEMA: list of string, each defining a field of the records
KEY: the field that identifies the records.
Usually AbstractCache isn't used as is, but instead it's subclassed
into "concrete" caches, which may expose higher-level functionalities.
A common pattern in defining caches is defining the module-level
constants TABLE, SCHEMA, KEY, making the constructor use them.
@param database_file:
absolute filesystem pathname to a file which
will contain the database.
@param table_name:
name of the cache.
@param table_schema:
List of strings ['col_name col_type', 'col2_name col2_type']
defining the fields of the records.
@param key:
Name of the field that identifies the records.
"""
def __init__(self,
database_file,
table_name,
table_schema,
key,
logger=None):
if logger is None:
self.logger = logging.getLogger()
self.logger.addHandler(logging.NullHandler())
else:
self.logger = logger
self._table_name = table_name
self._autocommit = True
self._key = None
self._columns = None
self._schema = None
self._db = SQLiteDB(database_file)
self._filename = database_file
self.recreated = False
# Note: self.schema is a property object
self.schema = table_schema
self._check_schema(
) # possibly creating the table if it does not exist
# Note: self.key is a property object
self.key = key
self._create_index_if_needed()
# Remember not to vacuum from any other method than the constructor,
# since it makes any open transaction commit!
self._execute("VACUUM")
def _check_schema(self):
"""
Check the given table schema with the one present on db.
Raises exception if declared table is not there or the schema is
wrong.
"""
data = self._query(
u"SELECT sql FROM sqlite_master WHERE "
"type='table' and name=?", [self._table_name])
if len(data) < 1:
raise NoSuchTable()
sql = data[0][0]
sql = sql.split('(')[1]
sql = sql.split(')')[0]
sql = sql.split(',')
found_schema = [' '.join(column_def.split()) for column_def in sql]
if self._schema != found_schema:
raise WrongSchema("expected: %s, found: %s" %
(self._schema, found_schema))
@property
def table_name(self):
return self._table_name
@property
def schema(self):
return self._schema
@schema.setter
def schema(self, schema):
schema = [' '.join(column_def.split()) for column_def in schema]
self._schema = schema
self._schema_tostr = ', '.join(self._schema)
self._columns = [string.split()[0] for string in self._schema]
self._recreate_db_if_not_exists()
@property
def key(self):
return self._key
@key.setter
def key(self, key):
if self._columns is None:
raise MissingSchema(u'Please define a schema')
msg = u'key %s is not part of %s table' % (key, self.table_name)
if key not in self._columns:
raise NonexistentKey(msg)
self._key = key
self._key_index = self._columns.index(key)
def _recreate_db_if_not_exists(self):
must_recreate = False
if not os.path.exists(self._filename):
must_recreate = True
else:
try:
self._db.query("SELECT * FROM %s LIMIT 1" % self.table_name)
must_recreate = False
except Exception:
must_recreate = True
if must_recreate:
self.logger.debug(u"Initializing a new database "
u"because no valid database could be found.")
self._initialize_new()
self.recreated = True
def _create_index_if_needed(self):
"""Assuming the database is there and the schema is ok,
it add a key index if it is not present."""
data = self._query(
u"SELECT sql FROM sqlite_master "
u"WHERE type='index' and name=?", [self._key + "_index"])
if len(data) == 0:
self.logger.debug("adding index to %s" % self._table_name)
self._execute(u'CREATE INDEX "%s_index" on %s (%s ASC)' %
(self._key, self._table_name, self._key))
def _initialize_new(self):
"""Initialize a new database table.
"""
self.logger.debug(u'Creating database table...')
args = (self.table_name, self._schema_tostr)
self._execute(u'CREATE TABLE %s (%s)' % args)
self.logger.debug(u'Database table successfully created.')
def update_record(self, *record):
"""
Write a record into the cache.
If a record with the same key values already exists, it is
overwritten.
Raises WrongNumberOfParameters exception if a wrong number of
fields is passed.
@param record: a tuple of values (column1_value, column2_value, ...)
"""
number_of_field = len(record)
if number_of_field != len(self.schema):
raise WrongNumberOfParameters(
u'Passed %s parameters, %s were required in the'
' following schema: %s' %
(len(record), len(self.schema), self.schema))
if not self.exist_record(record[self._key_index]):
self._insert_record(record)
else:
self._update_record(record)
def _insert_record(self, record):
fields = ', '.join(['?'] * len(record))
statement = ''.join([u'INSERT INTO %s VALUES (', fields, ')'])
self._execute(statement % self.table_name, record)
def _update_record(self, record):
columns = u', '.join(["%s = ?" % column for column in self._columns])
statement = u"UPDATE %s SET %s WHERE %s = ?"
statement = statement % (self.table_name, columns, self.key)
values = record + (record[self._key_index], )
self._execute(statement, values)
def update_record_fields(self, key_value, **fields):
"""
Updates the fields specified by the "keyword args" for the row
with the given key_value.
@param key_value: the value of the key column
@param **fields: parameters with format: field_name=value
"""
if len(fields) == 0:
raise WrongNumberOfParameters(
u'You should pass at least 1 column to update')
if len(fields) >= len(self._schema):
raise WrongNumberOfParameters(
u'You pass %s parameters, %s was required in the'
' following schema %s' %
(len(fields), len(self.schema), self.schema))
for key in fields.keys():
if key not in self._columns:
raise UnknownColumn(u'Column %s not in %s table' %
(key, self._table_name))
columns = u', '.join(["%s = ?" % column for column in fields])
statement = u'UPDATE %s SET %s WHERE %s = ?' \
% (self.table_name, columns, self.key)
values = fields.values()
values.append(key_value)
self._execute(statement, tuple(values))
def delete_record(self, key_value):
"""
Delete a record from db with the given key value.
@param key_value: the value of the key of the row to delete.
"""
statement = u'DELETE FROM %s WHERE %s=?' % (self.table_name, self.key)
self._execute(statement, (key_value, ))
def delete_records(self, key_values):
"""
Delete all records with the given key values.
@param key_values: an array of key values of the rows to delete
"""
if len(key_values) == 0:
return
statement = u"DELETE FROM %s where %s=?" % (self.table_name, self.key)
eargs = [(unicode(x), ) for x in key_values]
self._execute(statement, eargs)
def exist_record(self, key_value):
"""
Returns true if a there is a row with the given key value.
@param key_value: the value of the key you are looking for
@return: boolean
"""
statement = "SELECT COUNT(*) FROM %s ""WHERE %s = ?" \
% (self.table_name, self.key)
result = self._query(statement, (key_value, ))
count = result[0][0]
return count > 0
def get_record(self, key_value):
"""
Return the record with the given key value.
@param key_value: the value of the key column
@return: a tuple representing the first row found with the given key
or None if no row was found
"""
stm = u'SELECT * FROM %s WHERE %s=?' % (self.table_name, self.key)
result = self._query(stm, [key_value])
if len(result) == 0:
return None
if len(result) > 1:
self.logger.warning(
u'More than one record found for %s="%s", returning the first.'
% (self.key, key_value))
return result[0]
def get_all_records(self):
return self._query(u"SELECT * FROM %s" % self.table_name)
def get_all_keys(self):
res = self._query(u"SELECT %s FROM %s" % (self.key, self.table_name))
res = [record[0] for record in res]
return res
def clear(self):
""" Delete all records from the database """
self._execute(u"DELETE FROM %s" % self.table_name)
def destroy(self):
""" Delete DB File """
if os.path.exists(self._filename):
os.remove(self._filename)
def _execute(self, statement, parameters=[]):
if not self._autocommit:
self._db.execute(statement, parameters)
else:
with self.transaction() as transactional_self:
transactional_self._db.execute(statement, parameters)
def _query(self, statement, parameters=[]):
try:
return self._db.query(statement, parameters)
finally:
if self._autocommit:
self._db.close()
@contextmanager
def transaction(self, *caches_to_attach):
"""Open a transaction on this cache.
Modifications to a cache usually are immediate, that is, they
get persisted just after being made. However sometimes there is
need for making several modifications in a transactional fashion,
so to rollback if any error happens during the process.
Any modification to the cache made inside this context manager
is automatically committed when the context is finished and is
automatically rollbacked if an exception is raised.
Calling the context manager returns a clone of this cache, which
must be used in place of the original one in order for the
transaction to be effective.
E.g.: with mycache.transaction() as transactional_mycache: ...
If other caches are passed to the context manager call, they
are "attached" to this and become part of the same transaction
(that is, either all caches are modified or none of them).
E.g: with cache1.transaction(cache2) as (trans_c1, trans_c2): ...
"""
transactional_self = copy.copy(self)
transactional_self._autocommit = False
transactional_self._db.begin_transaction()
attached_caches = [transactional_self]
for cache in caches_to_attach:
statement = "ATTACH DATABASE '%s' as %s" \
% (cache._filename, cache.__class__.__name__)
transactional_self._execute(statement)
transactional_cache = copy.copy(cache)
transactional_cache._autocommit = False
transactional_cache._db = self._db
transactional_cache._table_name = "%s.%s" \
% (cache.__class__.__name__, cache._table_name)
attached_caches.append(transactional_cache)
try:
if not caches_to_attach:
yield transactional_self
else:
yield tuple(attached_caches)
except:
transactional_self._db.rollback_transaction()
raise
else:
transactional_self._db.commit_transaction()
finally:
transactional_self._db.close()
class AbstractCache(object):
"""A generic SQL-based store.
"Caches" are databases used by FileRock to persistently store
several kinds of data. Each database (which is implemented with
SQLite) contains a set of "records", identified by a column called
"key". A cache can be configured with the following parameters:
TABLE: name of the cache (and of the underlying SQL table)
SCHEMA: list of string, each defining a field of the records
KEY: the field that identifies the records.
Usually AbstractCache isn't used as is, but instead it's subclassed
into "concrete" caches, which may expose higher-level functionalities.
A common pattern in defining caches is defining the module-level
constants TABLE, SCHEMA, KEY, making the constructor use them.
@param database_file:
absolute filesystem pathname to a file which
will contain the database.
@param table_name:
name of the cache.
@param table_schema:
List of strings ['col_name col_type', 'col2_name col2_type']
defining the fields of the records.
@param key:
Name of the field that identifies the records.
"""
def __init__(self,
database_file, table_name, table_schema, key, logger=None):
if logger is None:
self.logger = logging.getLogger()
self.logger.addHandler(logging.NullHandler())
else:
self.logger = logger
self._table_name = table_name
self._autocommit = True
self._key = None
self._columns = None
self._schema = None
self._db = SQLiteDB(database_file)
self._filename = database_file
self.recreated = False
# Note: self.schema is a property object
self.schema = table_schema
self._check_schema()
# Note: self.key is a property object
self.key = key
# Remember not to vacuum from any other method than the constructor,
# since it makes any open transaction commit!
self._execute("VACUUM")
def _check_schema(self):
"""
Check the given table schema with the one present on db.
Raises exception if declared table is not there or the schema is
wrong.
"""
data = self._query(u"SELECT sql FROM sqlite_master WHERE "
"type='table' and name=?", [self._table_name])
if len(data) < 1:
raise NoSuchTable()
sql = data[0][0]
sql = sql.split('(')[1]
sql = sql.split(')')[0]
sql = sql.split(',')
found_schema = [' '.join(column_def.split()) for column_def in sql]
if self._schema != found_schema:
raise WrongSchema("expected: %s, found: %s"
% (self._schema, found_schema))
@property
def table_name(self):
return self._table_name
@property
def schema(self):
return self._schema
@schema.setter
def schema(self, schema):
schema = [' '.join(column_def.split()) for column_def in schema]
self._schema = schema
self._schema_tostr = ', '.join(self._schema)
self._columns = [string.split()[0] for string in self._schema]
self._recreate_db_if_not_exists()
@property
def key(self):
return self._key
@key.setter
def key(self, key):
if self._columns is None:
raise MissingSchema(u'Please define a schema')
msg = u'key %s is not part of %s table' % (key, self.table_name)
if key not in self._columns:
raise NonexistentKey(msg)
self._key = key
self._key_index = self._columns.index(key)
def _recreate_db_if_not_exists(self):
must_recreate = False
if not os.path.exists(self._filename):
must_recreate = True
else:
try:
self._db.query("SELECT * FROM %s LIMIT 1" % self.table_name)
must_recreate = False
except Exception:
must_recreate = True
if must_recreate:
self.logger.debug(
u"Initializing a new database "
u"because no valid database could be found.")
self._initialize_new()
self.recreated = True
def _initialize_new(self):
"""Initialize a new database table.
"""
self.logger.debug(u'Creating database table...')
args = (self.table_name, self._schema_tostr)
self._execute(u'CREATE TABLE %s (%s)' % args)
self.logger.debug(u'Database table successfully created.')
def update_record(self, *record):
"""
Write a record into the cache.
If a record with the same key values already exists, it is
overwritten.
Raises WrongNumberOfParameters exception if a wrong number of
fields is passed.
@param record: a tuple of values (column1_value, column2_value, ...)
"""
number_of_field = len(record)
if number_of_field != len(self.schema):
raise WrongNumberOfParameters(
u'Passed %s parameters, %s were required in the'
' following schema: %s'
% (len(record), len(self.schema), self.schema))
if not self.exist_record(record[self._key_index]):
self._insert_record(record)
else:
self._update_record(record)
def _insert_record(self, record):
fields = ', '.join(['?'] * len(record))
statement = ''.join([u'INSERT INTO %s VALUES (', fields, ')'])
self._execute(statement % self.table_name, record)
def _update_record(self, record):
columns = u', '.join(["%s = ?" % column for column in self._columns])
statement = u"UPDATE %s SET %s WHERE %s = ?"
statement = statement % (self.table_name, columns, self.key)
values = record + (record[self._key_index],)
self._execute(statement, values)
def update_record_fields(self, key_value, **fields):
"""
Updates the fields specified by the "keyword args" for the row
with the given key_value.
@param key_value: the value of the key column
@param **fields: parameters with format: field_name=value
"""
if len(fields) == 0:
raise WrongNumberOfParameters(
u'You should pass at least 1 column to update')
if len(fields) >= len(self._schema):
raise WrongNumberOfParameters(
u'You pass %s parameters, %s was required in the'
' following schema %s'
% (len(fields), len(self.schema), self.schema))
for key in fields.keys():
if key not in self._columns:
raise UnknownColumn(u'Column %s not in %s table'
% (key, self._table_name))
columns = u', '.join(["%s = ?" % column for column in fields])
statement = u'UPDATE %s SET %s WHERE %s = ?' \
% (self.table_name, columns, self.key)
values = fields.values()
values.append(key_value)
self._execute(statement, tuple(values))
def delete_record(self, key_value):
"""
Delete a record from db with the given key value.
@param key_value: the value of the key of the row to delete.
"""
statement = u'DELETE FROM %s WHERE %s=?' % (self.table_name, self.key)
self._execute(statement, (key_value,))
def delete_records(self, key_values):
"""
Delete all records with the given key values.
@param key_values: an array of key values of the rows to delete
"""
if len(key_values) == 0:
return
statement = u"DELETE FROM %s where %s=?" % (self.table_name, self.key)
eargs = [(unicode(x),) for x in key_values]
self._execute(statement, eargs)
def exist_record(self, key_value):
"""
Returns true if a there is a row with the given key value.
@param key_value: the value of the key you are looking for
@return: boolean
"""
statement = "SELECT COUNT(*) FROM %s ""WHERE %s = ?" \
% (self.table_name, self.key)
result = self._query(statement, (key_value,))
count = result[0][0]
return count > 0
def get_record(self, key_value):
"""
Return the record with the given key value.
@param key_value: the value of the key column
@return: a tuple representing the first row found with the given key
or None if no row was found
"""
stm = u'SELECT * FROM %s WHERE %s=?' % (self.table_name, self.key)
result = self._query(stm, [key_value])
if len(result) == 0:
return None
if len(result) > 1:
self.logger.warning(
u'More than one record found for %s="%s", returning the first.'
% (self.key, key_value))
return result[0]
def get_all_records(self):
return self._query(u"SELECT * FROM %s" % self.table_name)
def get_all_keys(self):
res = self._query(u"SELECT %s FROM %s" % (self.key, self.table_name))
res = [record[0] for record in res]
return res
def clear(self):
""" Delete all records from the database """
self._execute(u"DELETE FROM %s" % self.table_name)
def destroy(self):
""" Delete DB File """
if os.path.exists(self._filename):
os.remove(self._filename)
def _execute(self, statement, parameters=[]):
if not self._autocommit:
self._db.execute(statement, parameters)
else:
with self.transaction() as transactional_self:
transactional_self._db.execute(statement, parameters)
def _query(self, statement, parameters=[]):
try:
return self._db.query(statement, parameters)
finally:
if self._autocommit:
self._db.close()
@contextmanager
def transaction(self, *caches_to_attach):
"""Open a transaction on this cache.
Modifications to a cache usually are immediate, that is, they
get persisted just after being made. However sometimes there is
need for making several modifications in a transactional fashion,
so to rollback if any error happens during the process.
Any modification to the cache made inside this context manager
is automatically committed when the context is finished and is
automatically rollbacked if an exception is raised.
Calling the context manager returns a clone of this cache, which
must be used in place of the original one in order for the
transaction to be effective.
E.g.: with mycache.transaction() as transactional_mycache: ...
If other caches are passed to the context manager call, they
are "attached" to this and become part of the same transaction
(that is, either all caches are modified or none of them).
E.g: with cache1.transaction(cache2) as (trans_c1, trans_c2): ...
"""
transactional_self = copy.copy(self)
transactional_self._autocommit = False
transactional_self._db.begin_transaction()
attached_caches = [transactional_self]
for cache in caches_to_attach:
statement = "ATTACH DATABASE '%s' as %s" \
% (cache._filename, cache.__class__.__name__)
transactional_self._execute(statement)
transactional_cache = copy.copy(cache)
transactional_cache._autocommit = False
transactional_cache._db = self._db
transactional_cache._table_name = "%s.%s" \
% (cache.__class__.__name__, cache._table_name)
attached_caches.append(transactional_cache)
try:
if not caches_to_attach:
yield transactional_self
else:
yield tuple(attached_caches)
except:
transactional_self._db.rollback_transaction()
raise
else:
transactional_self._db.commit_transaction()
finally:
transactional_self._db.close()
