def MakeRow(self, table_name, row_dict):
"""Creates a row in the database using the table name and row dict
Inputs:
table_name: string of valid table name from constants
row_dict: dictionary that coresponds to table_name
Raises:
InvalidInputError: Table name not valid
TransactionError: Must run StartTansaction before inserting
Outputs:
int: last insert id
"""
if (not table_name in helpers_lib.GetValidTables()):
raise errors.InvalidInputError('Table name not valid: %s' %
table_name)
if (not self.transaction_init):
raise errors.TransactionError('Must run StartTansaction before '
'inserting.')
if (self.data_validation_instance is None):
self.InitDataValidation()
self.data_validation_instance.ValidateRowDict(table_name, row_dict)
column_names = []
column_assignments = []
for k in row_dict.iterkeys():
column_names.append(k)
column_assignments.append('%s%s%s' % ('%(', k, ')s'))
query = 'INSERT INTO %s (%s) VALUES (%s)' % (
table_name, ','.join(column_names), ','.join(column_assignments))
self.cursor_execute(query, row_dict)
return self.cursor.lastrowid
def RemoveRow(self, table_name, row_dict):
"""Removes a row in the database using the table name and row dict
Inputs:
table_name: string of valid table name from constants
row_dict: dictionary that coresponds to table_name
Raises:
InvalidInputError: Table name not valid
TransactionError: Must run StartTansaction before deleting
Outputs:
int: number of rows affected
"""
if (not table_name in helpers_lib.GetValidTables()):
raise errors.InvalidInputError('Table name not valid: %s' %
table_name)
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before deleting.')
if (self.data_validation_instance is None):
self.InitDataValidation()
self.data_validation_instance.ValidateRowDict(table_name, row_dict)
where_list = []
for k in row_dict.iterkeys():
where_list.append('%s=%s%s%s' % (k, '%(', k, ')s'))
query = 'DELETE FROM %s WHERE %s' % (table_name,
' AND '.join(where_list))
self.cursor_execute(query, row_dict)
return self.cursor.rowcount
def EndTransaction(self, rollback=False):
"""Ends a transaction.
Also does some simple checking to make sure a connection was open first
and releases itself from the current queue.
Inputs:
rollback: boolean of if the transaction should be rolled back
Raises:
TransactionError: Must run StartTansaction before EndTransaction.
"""
if (not self.thread_safe):
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before '
'EndTransaction.')
try:
self.cursor.close()
if (rollback):
self.connection.rollback()
else:
self.connection.commit()
finally:
self.transaction_init = False
if (self.thread_safe):
if (not self.queue.empty()):
self.now_serving = self.queue.get()
else:
self.now_serving = None
def UpdateRow(self, table_name, search_row_dict, update_row_dict):
"""Updates a row in the database using search and update dictionaries.
Inputs:
table_name: string of valid table name from constants
search_row_dict: dictionary that coresponds to table_name containing
search args
update_row_dict: dictionary that coresponds to table_name containing
update args
Raises:
InvalidInputError: Table name not valid
TransactionError: Must run StartTansaction before inserting
Outputs:
int: number of rows affected
"""
if (not table_name in helpers_lib.GetValidTables()):
raise errors.InvalidInputError('Table name not valid: %s' %
table_name)
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before deleting.')
if (self.data_validation_instance is None):
self.InitDataValidation()
self.data_validation_instance.ValidateRowDict(table_name,
search_row_dict,
none_ok=True)
self.data_validation_instance.ValidateRowDict(table_name,
update_row_dict,
none_ok=True)
query_updates = []
query_searches = []
combined_dict = {}
for k, v in update_row_dict.iteritems():
if (v is not None):
query_updates.append('%s%s%s%s' % (k, '=%(update_', k, ')s'))
combined_dict['update_%s' % k] = v
for k, v in search_row_dict.iteritems():
if (v is not None):
query_searches.append('%s=%s%s%s' % (k, '%(search_', k, ')s'))
combined_dict['search_%s' % k] = v
query = 'UPDATE %s SET %s WHERE %s' % (
table_name, ','.join(query_updates), ' AND '.join(query_searches))
self.cursor_execute(query, combined_dict)
return self.cursor.rowcount
def perform_transcation(sender, recipient, balance):
try:
balance = float(balance)
except ValueError:
raise errors.TransactionError("The transction cannot be performed!")
# Check if the user has sufficient balance
if balance > get_balance(sender) and sender != "MINE":
raise errors.InsufficientFund("You do not have sufficient balance!")
# Check if user transfers funds to him/herself
elif sender == recipient:
raise errors.TransactionError("You cannot send balance to yourself!")
# Check if user transfers negative funds
elif balance <= 0.00:
raise errors.TransactionError("You cannot send balance less than zero!")
# Check if the username does not exists in the databse
elif isnewuser(recipient):
raise errors.TransactionError("The user with username you entered does not exists!")
blockchain = get_blockchain()
number = len(blockchain.chain) + 1
data = "%s-->%s-->%s" %(sender, recipient, balance)
blockchain.mining(Block(number, data=data))
sync_blockchain(blockchain)
def UnlockDb(self):
"""This function is to unlock the whole database.
This function expects for self.db_instance.cursor to be instantiated and
valid. It also expects all tables to be locked.
Raises:
TransactionError: Must lock tables before unlocking them.
"""
if (self.locked_db is False):
raise errors.TransactionError(
'Must lock tables before unlocking them')
self.cursor_execute('UNLOCK TABLES')
self.cursor_execute('UPDATE `locks` SET `locked`=0 WHERE '
'`lock_name`="db_lock_lock"')
self.locked_db = False
def LockDb(self):
"""This function is to lock the whole database for consistent data
retrevial.
This function expects for self.db_instance.cursor to be instantiated and
valid.
Raises:
TransactionError: Must unlock tables before re-locking them.
"""
if (self.locked_db is True):
raise errors.TransactionError(
'Must unlock tables before re-locking them')
self.cursor_execute('UPDATE `locks` SET `locked`=1 WHERE '
'`lock_name`="db_lock_lock"')
time.sleep(self.big_lock_wait)
self.cursor_execute('LOCK TABLES %s READ' %
' READ, '.join(self.ListTableNames()))
self.locked_db = True
def TableRowCount(self, table_name):
"""Counts the amount of records in a table and returns it.
Inputs:
table_name: string of valid table name from constants
Raises:
InvalidInputError: Table name not valid
TransactionError: Must run StartTansaction before getting row count.
Outputs:
int: number of rows found
"""
if (not table_name in helpers_lib.GetValidTables()):
raise errors.InvalidInputError('Table name not valid: %s' %
table_name)
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before getting '
'row count.')
self.cursor_execute('SELECT COUNT(*) FROM %s' % table_name)
row_count = self.cursor.fetchone()
return row_count['COUNT(*)']
def ListRow(self, *args, **kwargs):
"""Lists rows in the database using a dictionary of tables. Then returns
the rows found. Joins are auto generated on the fly based on foreign keys
in the database.
Inputs:
args: pairs of string of table name and dict of rows
kwargs: lock_rows: default False
column: column to search range on, if using multiple
tables, the column must be in the first table
in args.
range_values: range tuple of values to search within for on column
is_date: boolean of if range is of dates
example usage: ListRow('users', user_row_dict,
'user_group_assignments', user_assign_row_dict,
lock_rows=True)
Raises:
TransactionError: Must run StartTansaction before inserting
UnexpectedDataError: If is_date is specified you must specify column and range
UnexpectedDataError: If column or range is specified both are needed
InvalidInputError: Found unknown option(s)
UnexpectedDataError: No args given, must at least have a pair of table name and row dict
UnexpectedDataError: Number of unnamed args is not even.
Args should be entered in pairs of table name and row dict.
InvalidInputError: Table name not valid
InvalidInputError: Column not found in row
UnexpectedDataError: Column in table is not a DateTime type
UnexpectedDataError: Date from range is not a valid datetime object
InvalidInputError: Range must be int if is_date is not set
InvalidInputError: Multiple tables were passed in but no joins were found
Outputs:
tuple of row dicts consisting of all the tables that were in the input.
all column names in the db are unique so no colisions occour
example: ({'user_name': 'sharrell', 'access_level': 10,
'user_group_assignments_group_name: 'cs',
'user_group_assignments_user_name: 'sharrell'},
{'user_name': 'sharrell', 'access_level': 10,
'user_group_assignments_group_name: 'eas',
'user_group_assignments_user_name: 'sharrell'})
"""
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before getting '
'data.')
if (self.data_validation_instance is None):
self.InitDataValidation()
valid_tables = helpers_lib.GetValidTables()
tables = {}
table_names = []
lock_rows = False
column = None
range_values = ()
is_date = None
if (kwargs):
if ('lock_rows' in kwargs):
lock_rows = kwargs['lock_rows']
del kwargs['lock_rows']
if ('column' in kwargs):
column = kwargs['column']
del kwargs['column']
if ('range_values' in kwargs):
range_values = kwargs['range_values']
del kwargs['range_values']
if ('is_date' in kwargs):
is_date = kwargs['is_date']
del kwargs['is_date']
if (column is None and is_date is not None):
raise errors.UnexpectedDataError(
'If is_date is specified you must '
'specify column and range')
if (bool(column) ^ bool(range_values)):
raise errors.UnexpectedDataError(
'If column or range is specified '
'both are needed')
if (kwargs):
raise errors.InvalidInputError('Found unknown option(s): '
'%s' % kwargs.keys())
if (not args):
raise errors.UnexpectedDataError(
'No args given, must at least have a '
'pair of table name and row dict')
if (len(args) % 2):
raise errors.UnexpectedDataError(
'Number of unnamed args is not even. Args '
'should be entered in pairs of table name '
'and row dict.')
count = 0
for arg in args:
count += 1
if (count % 2):
if (not arg in valid_tables):
raise errors.InvalidInputError('Table name not valid: %s' %
arg)
current_table_name = arg
else:
# do checking in validate row dict to check if it is a dict
self.data_validation_instance.ValidateRowDict(
current_table_name, arg, none_ok=True, all_none_ok=True)
tables[current_table_name] = arg
table_names.append(current_table_name)
if (range_values):
if (column not in args[1]):
raise errors.InvalidInputError('Column %s not found in row'
'dictionary: %s' %
(column, args[1]))
if (is_date):
if (constants.TABLES[args[0]][column] != 'DateTime'):
raise errors.UnexpectedDataError(
'column: %s in table %s is not a'
'DateTime type' % (column, args[0]))
for date in range_values:
if (not self.data_validation_instance.isDateTime(date)):
raise errors.UnexpectedDataError(
'Date: %s from range is not a valid '
'datetime object' % date)
else:
for value in range_values:
if (not self.data_validation_instance.isUnsignedInt(value)
):
raise errors.InvalidInputError(
'Range must be int if is_date '
'is not set')
query_where = []
if (len(tables) > 1):
if (not self.foreign_keys):
self.cursor_execute(
'SELECT table_name, column_name, '
'referenced_table_name, referenced_column_name '
'FROM information_schema.key_column_usage WHERE '
'referenced_table_name IS NOT NULL AND '
'referenced_table_schema="%s"' % self.db_name)
self.foreign_keys = self.cursor.fetchall()
for key in self.foreign_keys:
if (key['table_name'] in table_names
and key['referenced_table_name'] in table_names):
query_where.append('(%(table_name)s.%(column_name)s='
'%(referenced_table_name)s.'
'%(referenced_column_name)s)' % key)
if (not query_where):
raise errors.InvalidInputError(
'Multiple tables were passed in but no '
'joins were found')
column_names = []
search_dict = {}
for table_name, row_dict in tables.iteritems():
for key, value in row_dict.iteritems():
column_names.append('%s.%s' % (table_name, key))
if (value is not None):
search_dict[key] = value
query_where.append('%s%s%s%s' % (key, '=%(', key, ')s'))
if (range_values):
search_dict['start'] = range_values[0]
search_dict['end'] = range_values[1]
query_where.append(
'%s%s%s%s' % (column, '>=%(start)s AND ', column, '<=%(end)s'))
query_end = ''
if (query_where):
query_end = 'WHERE %s' % ' AND '.join(query_where)
if (lock_rows):
query_end = '%s FOR UPDATE' % query_end
query = 'SELECT %s FROM %s %s' % (','.join(column_names),
','.join(table_names), query_end)
self.cursor_execute(query, search_dict)
return self.cursor.fetchall()
def StartTransaction(self):
"""Starts a transaction.
Also it starts a db connection if none exists or it times out.
Always creates a new cursor.
This function also serializes all requests on this object and if the
big lock has been activated will wait for it to be released.
Raises:
TransactionError: Cannot start new transaction last transaction not
committed or rolled-back.
"""
if (self.thread_safe):
unique_id = uuid.uuid4()
self.queue.put(unique_id)
while_sleep = 0
while (unique_id != self.now_serving):
time.sleep(while_sleep)
self.queue_update_lock.acquire()
if (self.now_serving is None):
self.now_serving = self.queue.get()
self.queue_update_lock.release()
while_sleep = 0.005
else:
if (self.transaction_init):
raise errors.TransactionError(
'Cannot start new transaction last '
'transaction not committed or '
'rolled-back.')
if (self.connection is not None):
try:
self.cursor = self.connection.cursor(
MySQLdb.cursors.DictCursor)
self.cursor_execute('DO 0') # NOOP to test connection
except MySQLdb.OperationalError:
self.connection = None
if (self.connection is None):
if (self.ssl):
self.connection = MySQLdb.connect(host=self.db_host,
user=self.db_user,
passwd=self.db_passwd,
db=self.db_name,
use_unicode=True,
charset='utf8',
ssl=self.ssl_settings)
else:
self.connection = MySQLdb.connect(host=self.db_host,
user=self.db_user,
passwd=self.db_passwd,
db=self.db_name,
use_unicode=True,
charset='utf8')
self.cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
while_sleep = 0
db_lock_locked = 1
while (db_lock_locked):
time.sleep(while_sleep)
try:
self.cursor_execute('SELECT `locked`, `lock_last_updated`, '
'NOW() as `now` from `locks` WHERE '
'`lock_name`="db_lock_lock"')
rows = self.cursor.fetchall()
except MySQLdb.ProgrammingError:
break
if (not rows):
break
lock_last_updated = rows[0]['lock_last_updated']
db_lock_locked = rows[0]['locked']
now = rows[0]['now']
if ((now - lock_last_updated).seconds > self.big_lock_timeout):
break
while_sleep = 1
self.transaction_init = True