def delete(self, table, condition, condition_val):
query = "DELETE from " + table + " WHERE " + condition
try:
return self.execute(query, condition_val)
except Exception, e:
logger.logclient("error", "delete",
"query: %s, param:%s" % (query, condition_val))
logger.logclient("error", "delete", str(e))
return
def _parse_data(self, changes):
self._get_received_count()
# print self._temp_count
if self._temp_count > self._received_count:
return
self._db.begin()
self._temp_count = self._received_count
try:
changes_list = []
tbl_name = ""
auto_id = 0
is_insert = False
for change in changes:
# print change.to_structure()
# Update
if change.action == "1":
if is_insert:
# print "inerst 1 ------------- "
self._execute_insert_statement(changes_list)
is_insert = False
changes_list = []
# print "update 1 ---------------"
self._execute_update_statement(change)
else:
if is_insert is False:
is_insert = True
auto_id = change.tbl_auto_id
tbl_name = change.tbl_name
if auto_id != change.tbl_auto_id or tbl_name != change.tbl_name:
# print "insert 2 ---------------"
self._execute_insert_statement(changes_list)
changes_list = []
auto_id = change.tbl_auto_id
tbl_name = change.tbl_name
changes_list.append(change)
if is_insert:
# print "insert 3 -------------------------"
self._execute_insert_statement(changes_list, error_ok=True)
changes_list = []
# print "audit_trail_id updated ", self._temp_count, self._type
update_traild_id(self._db, self._temp_count, self._type)
self._received_count = self._temp_count
self._db.commit()
# self._temp_count = 0
except Exception, e:
# print(traceback.format_exc())
print e
logger.logclient("error", "client replication base", e)
logger.logclient("error", "client replication base",
str(traceback.format_exc()))
self._temp_count = self._received_count
self._db.rollback()
def connect(self):
assert self._connection is None
try:
connection = self._mysql.get_db
connection.autocommit(True)
self._connection = connection
logger.logclient("info", "database connection initiate", "")
return self._connection
except Exception, e:
logger.logclient("error", "database.py-connect", e)
def _execute_update_statement(self, change):
auto_id = self._auto_id_columns.get(change.tbl_name)
assert auto_id is not None
val = change.value
if val is not None:
# val = "'" + change.value.replace("'", "\\'") + "'"
query = "UPDATE %s SET %s = '%s' WHERE %s = %s;" % (
change.tbl_name, change.column_name, val, auto_id,
change.tbl_auto_id)
try:
self._db.execute(query)
except Exception, e:
print e,
logger.logclient("error", "client replication base", e)
def validate_user_rights(self, session_token, rcaller_name, is_mobile):
if is_mobile is True:
user_id, user_category_id = self.validate_session_token(
session_token)
return user_id, user_category_id
caller_name = [str(x) for x in rcaller_name.split("/") if x != ""]
caller_name = "/%s" % (caller_name[0])
try:
user_id, user_category_id = self.validate_session_token(
session_token)
if user_id is not None:
if user_category_id == 1:
q = "select t2.form_url from tbl_form_category as t1 " + \
" inner join tbl_forms as t2 on t1.form_id = t2.form_id where " + \
" t1.user_category_id = 1 " + \
" and t2.form_url = %s "
param = [caller_name]
else:
q = "select t3.form_url " + \
" from tbl_users as t1 " + \
" inner join tbl_user_group_forms as t2 on t1.user_group_id = t2.user_group_id " + \
" inner join tbl_forms as t3 on t2.form_id = t3.form_id " + \
" where t1.user_id = %s and t3.form_url = %s"
param = [user_id, caller_name]
if caller_name not in ("/welcome", "/home", "/profile",
"/themes", "/reminders", "/escalations",
"/message", "/notifications",
"/view-profile", "/settings"):
rows = self.select_one(q, param)
if rows:
if rows.get("form_url") == caller_name:
return user_id, user_category_id
else:
return None, None
else:
return user_id, user_category_id
else:
return user_id, user_category_id
except Exception, e:
logger.logclient("error", "validate_rights", str(e))
raise fetch_error()
def append(self, table, column, value, condition, condition_val):
try:
rows = self.get_data(table, column, condition, condition_val)
currentValue = rows[0][column]
if currentValue is not None:
newValue = currentValue + "," + str(value)
else:
newValue = str(value)
columns = [column]
values = [newValue]
values += condition_val
res = self.update(table, columns, values, condition)
return res
except Exception, e:
logger.logclient(
"error", "append",
"table: %s, column:%s, value:%s" % (table, column, value))
logger.logclient("error", "append", str(e))
return False
def select_one(self, query, param=None):
cursor = self.cursor()
assert cursor is not None
try:
if param is None:
cursor.execute(query)
else:
if type(param) is tuple:
cursor.execute(query, param)
elif type(param) is list:
cursor.execute(query, param)
else:
cursor.execute(query)
cursor.nextset()
res = cursor.fetchone()
cursor.nextset()
logger.logclient("query", "select_one",
"query: %s, param:%s" % (query, param))
return res
except Exception, e:
logger.logclient("error", "select_one",
"query: %s, param:%s" % (query, param))
logger.logclient("error", "select_one", str(e))
raise fetch_error()
def bulk_update(self, table, columns, values, conditions):
try:
for outer_index, cond in enumerate(conditions):
query = "UPDATE " + table + " set "
for index, column in enumerate(columns):
if values[outer_index][index] is not None:
if (index < len(columns) - 1):
query += column + " = '%s', " % (
values[outer_index][index])
else:
query += column + " = '%s' " % (
values[outer_index][index])
query += " WHERE " + cond + "; "
cursor = self.cursor()
assert cursor is not None
cursor.execute(query)
cursor.nextset()
logger.logclient("query", "bulk_update",
"query: %s, param:%s" % (query, values))
return True
except Exception, e:
logger.logclient("error", "bulk_update",
"query: %s, param:%s" % (query, values))
logger.logclient("error", "bulk_update", str(e))
return False
def call_proc_with_multiresult_set(self, procedure_name, args,
expected_result_count):
cursor = self.cursor()
rows = []
assert cursor is not None
try:
if args is None:
cursor.callproc(procedure_name)
else:
cursor.callproc(procedure_name, args)
logger.logclient(
"query", "call_proc_with_multiresult_set",
"procedure: %s, param:%s" % (procedure_name, args))
rows = []
for c in cursor.stored_results():
cols = c.description
if cols:
cols = [x[0] for x in cols]
else:
cols = []
r = convert_to_dict(c.fetchall(), cols)
rows.append(r)
except Exception, e:
logger.logclient(
"error", "call_proc_with_multiresult_set",
"procedure: %s, param:%s" % (procedure_name, args))
logger.logclient("error", "call_proc_with_multiresult_set", str(e))
def call_update_proc(self, procedure_name, args):
cursor = self.cursor()
assert cursor is not None
try:
if args is None:
cursor.callproc(procedure_name)
else:
cursor.callproc(procedure_name, args)
logger.logclient(
"query", "call_update_proc",
"procedure: %s, param:%s" % (procedure_name, args))
cursor.nextset()
except Exception, e:
logger.logclient(
"error", "call_update_proc",
"procedure: %s, param:%s" % (procedure_name, args))
logger.logclient("error", "call_update_proc", str(e))
raise process_procedure_error(procedure_name, args, e)
def update(self, table, columns, values, condition):
query = "UPDATE " + table + " set "
for index, column in enumerate(columns):
if index < len(columns) - 1:
query += column + " = %s, "
else:
query += column + " = %s "
query += " WHERE " + condition
try:
status = self.execute(query, values)
logger.logclient("query", "update",
"query: %s, param:%s" % (query, values))
return status
except Exception, e:
logger.logclient("error", "update",
"query: %s, param:%s" % (query, values))
logger.logclient("error", "update", str(e))
return False
def execute(self, query, param=None):
cursor = self.cursor()
assert cursor is not None
try:
if type(param) is tuple:
cursor.execute(query, param)
elif type(param) is list:
cursor.execute(query, param)
else:
cursor.execute(query)
logger.logclient("query", "execute",
"query: %s, param:%s" % (query, param))
cursor.nextset()
return True
except Exception, e:
logger.logclient("error", "execute",
"query: %s, param:%s" % (query, param))
logger.logclient("error", "execute", str(e))
return False
def call_proc(self, procedure_name, args=None, columns=None):
# columns no longer need here, so remove argument once removed from the reference place
# args can be tuple/list e.g, (parm1, parm2)/[param1, param2]
cursor = self.cursor()
rows = []
assert cursor is not None
try:
if args is None:
cursor.callproc(procedure_name)
else:
cursor.callproc(procedure_name, args)
logger.logclient(
"query", "call_proc",
"procedure: %s, param:%s" % (procedure_name, args))
cols = cursor.description
if cols:
cols = [x[0] for x in cols]
else:
cols = []
for c in cursor.stored_results():
cols = c.description
if cols:
cols = [x[0] for x in cols]
else:
cols = []
r = convert_to_dict(c.fetchall(), cols)
rows.append(r)
# rows = list(cursor.fetchall())
# rows = convert_to_dict(cursor.fetchall(), cols)
if len(rows) > 0:
rows = rows[0]
cursor.nextset()
except Exception, e:
logger.logclient(
"error", "call_proc",
"procedure: %s, param:%s" % (procedure_name, args))
logger.logclient("error", "call_proc", str(e))
raise process_procedure_error(procedure_name, args, e)
def bulk_insert(self, table, columns, valueList):
stringValue = []
for i in range(len(columns)):
stringValue.append('%s')
if type(columns) is list:
columns = ", ".join(columns)
query = "INSERT INTO %s (%s) " % (table, columns)
query += " VALUES (%s) " % (",".join(stringValue))
try:
cursor = self.cursor()
assert cursor is not None
cursor.executemany(query, valueList)
cursor.nextset()
logger.logclient("query", "bulk_insert",
"query: %s, param:%s" % (query, valueList))
return True
except Exception, e:
logger.logclient("error", "bulk_insert",
"query: %s, param:%s" % (query, valueList))
logger.logclient("error", "bulk_insert", str(e))
return False
def call_insert_proc(self, procedure_name, args):
cursor = self.cursor()
assert cursor is not None
new_id = None
try:
if args is None:
cursor.callproc(procedure_name)
else:
cursor.callproc(procedure_name, args)
logger.logclient(
"query", "call_insert_proc",
"procedure: %s, param:%s" % (procedure_name, args))
cursor.nextset()
cursor.execute("SELECT LAST_INSERT_ID() as newid")
r = cursor.fetchone()
cursor.nextset()
new_id = r["newid"]
except Exception, e:
logger.logclient(
"error", "call_insert_proc",
"procedure: %s, param:%s" % (procedure_name, args))
logger.logclient("error", "call_insert_proc", str(e))
raise process_procedure_error(procedure_name, args, e)
def execute_insert(self, query, param):
cursor = self.cursor()
assert cursor is not None
try:
if type(param) is tuple:
cursor.execute(query, param)
elif type(param) is list:
cursor.execute(query, param)
else:
cursor.execute(query)
cursor.nextset()
no = int(cursor.lastrowid)
logger.logclient("query", "execute_insert",
"query: %s, param:%s" % (query, param))
if no == 0:
return False
else:
return no
except Exception, e:
logger.logclient("error", "execute_insert",
"query: %s, param:%s" % (query, param))
logger.logclient("error", "execute_insert", str(e))
return False
def insert(self, table, columns, values):
# columns = ", ".join(columns)
stringValue = []
for i in range(len(values)):
stringValue.append('%s')
if type(columns) is list:
columns = ", ".join(columns)
columns = "(%s)" % columns
query = """INSERT INTO %s %s """ % (table, columns)
query += " VALUES (%s) " % (",".join(stringValue))
try:
n_id = int(self.execute_insert(query, values))
logger.logclient("query", "insert",
"query: %s, param:%s" % (query, values))
return n_id
if n_id == 0:
return False
except Exception, e:
logger.logclient("error", "insert",
"query: %s, param:%s" % (query, values))
logger.logclient("error", "insert", str(e))
return False
def _execute_insert_statement(self, changes, error_ok=False):
assert (len(changes)) > 0
tbl_name = changes[0].tbl_name
auto_id = self._auto_id_columns.get(tbl_name)
# print tbl_name
column_count = self._columns_count.get(tbl_name)
column_count -= 1
# print column_count
if tbl_name == "tbl_mapped_industries":
pass
# self._execute_insert_mapped_industry(changes)
else:
print error_ok
assert auto_id is not None
if error_ok:
if column_count != len(changes):
if tbl_name == "tbl_countries":
for r in changes:
self._execute_insert_statement([r])
return
else:
return
else:
if column_count != len(changes):
return
# columns = [x.column_name for x in changes]
i_column = []
values = []
domain_id = None
compliance_id = None
r_country_id = None
r_le_id = None
r_client_d = None
for x in changes:
if x.value is None:
# values.append('')
pass
else:
i_column.append(x.column_name)
values.append(str(x.value))
if tbl_name == "tbl_compliances" and x.column_name == "domain_id":
domain_id = int(x.value)
if tbl_name == "tbl_compliances" and x.column_name == "country_id":
r_country_id = int(x.value)
if tbl_name == "tbl_statutory_notifications" and x.column_name == "compliance_id":
compliance_id = int(x.value)
if tbl_name == "tbl_units" and x.column_name == "legal_entity_id":
r_le_id = int(x.value)
if tbl_name == "tbl_client_configuration" and x.column_name == "country_id":
r_country_id = int(x.value)
if tbl_name == "tbl_client_configuration" and x.column_name == "client_id":
r_client_d = int(x.value)
if tbl_name == "tbl_validity_date_settings" and x.column_name == "country_id":
r_country_id = int(x.value)
val = str(values)[1:-1]
query = "INSERT INTO %s (%s, %s) VALUES(%s, %s)" % (
tbl_name, auto_id, ",".join(i_column), changes[0].tbl_auto_id,
val)
if tbl_name == "tbl_client_groups":
query += " ON DUPLICATE KEY UPDATE email_id = values(email_id), total_view_licence = values(total_view_licence) ;"
elif tbl_name == "tbl_legal_entities":
query += " ON DUPLICATE KEY UPDATE legal_entity_name = values(legal_entity_name), " + \
" contract_from = values(contract_from), contract_to = values(contract_to), " + \
" logo = values(logo), logo_size = values(logo_size), file_space_limit = values(file_space_limit), " + \
" total_licence = values(total_licence), " + \
" is_closed = values(is_closed), closed_on = values(closed_on), " + \
" closed_by = values(closed_by), closed_remarks = values(closed_remarks)"
elif tbl_name == "tbl_units":
query += " ON DUPLICATE KEY UPDATE unit_name = values(unit_name), " + \
" unit_code = values(unit_code), geography_name = values(geography_name), " + \
" address = values(address), postal_code = values(postal_code) "
elif tbl_name == "tbl_compliances":
query += " ON DUPLICATE KEY UPDATE statutory_provision = values(statutory_provision), " + \
" compliance_task = values(compliance_task), document_name = values(document_name), " + \
" compliance_description = values(compliance_description), penal_consequences = values(penal_consequences), " + \
" reference_link = values(reference_link), frequency_id = values(frequency_id), " + \
" statutory_dates = values(statutory_dates), repeats_type_id = values(repeats_type_id), " + \
" duration_type_id = values(duration_type_id), repeats_every = values(repeats_every), " + \
" duration = values(duration), is_active = values(is_active), " + \
" format_file = values(format_file), format_file_size = values(format_file_size), " + \
" statutory_nature = values(statutory_nature), statutory_mapping = values(statutory_mapping)"
elif tbl_name == "tbl_legal_entity_domains":
query += "ON DUPLICATE KEY UPDATE count = values(count)"
else:
query += ""
try:
# print domain_id, self._domains
if self._is_group:
print "Replication for client ", self._client_id, self
else:
print "Replication for legal entity ", self._client_id, self
# print tbl_name
# print query
if tbl_name == "tbl_client_groups":
if self._is_group is False and self._group_id == changes[
0].tbl_auto_id:
self._db.execute(query)
elif self._is_group:
self._db.execute(query)
elif tbl_name == "tbl_compliances":
# print r_country_id, domain_id
# print self._country_id, self._domains
if r_country_id == self._country_id and domain_id in self._domains:
self._db.execute(query)
elif tbl_name == "tbl_statutory_notifications":
if self.check_compliance_available_for_statutory_notification(
compliance_id) is True:
self._db.execute(query)
elif tbl_name == "tbl_legal_entities":
self._db.execute(
"delete from tbl_legal_entity_domains where legal_entity_id = %s",
[changes[0].tbl_auto_id])
self._db.execute("delete from tbl_client_configuration")
if self._is_group is False and self._client_id == changes[
0].tbl_auto_id:
self._db.execute(query)
elif self._is_group is True:
self._db.execute(query)
elif tbl_name == "tbl_units":
self._db.execute(
"delete from tbl_units_organizations where unit_id = %s",
[changes[0].tbl_auto_id])
if self._is_group:
self._db.execute(query)
elif self._is_group is False and self._client_id == r_le_id:
self._db.execute(query)
elif tbl_name == "tbl_client_configuration":
# print self._group_id, self._country_id
# print r_client_d, r_country_id
# print self._is_group
if self._is_group and self._client_id == r_client_d:
self._db.execute(query)
elif self._is_group is False and self._group_id == r_client_d and self._country_id == r_country_id:
self._db.execute(query)
elif tbl_name == "tbl_countries":
if self._is_group is False and self._country_id == changes[
0].tbl_auto_id:
self._db.execute(query)
else:
self._db.execute(query)
elif tbl_name == "tbl_validity_date_settings":
# print self._country_id, r_country_id
if self._country_id == r_country_id:
self._db.execute(query)
else:
self._db.execute(query)
except Exception, e:
pass
print e
logger.logclient("error", "client replication base", e)