def validate_user_rights(db, session_token, caller_name):
print "validate_user_rights"
try:
user_id = db.validate_session_token(session_token)
print "----------"
print user_id
print user_id, caller_name
if user_id is True and caller_name not in ("/knowledge/home",
"/knowledge/profile"):
print user_id
rows = db.call_proc_with_multiresult_set("sp_verify_user_rights",
[user_id, caller_name], 2)
print rows
if rows:
if rows[1][0].get("form_url") == caller_name:
return user_id
else:
return False
elif user_id:
return user_id
else:
return False
except Exception, e:
logger.logKnowledge("error", "validate_rights",
str(traceback.format_exc()))
logger.logKnowledge("error", "validate_rights", str(e))
raise fetch_error()
def _grant_privileges(self, cursor):
try:
q = "CREATE USER %s@%s IDENTIFIED BY %s;"
cursor.execute(q, [self._db_username, str('%'), self._db_password])
logger.logKnowledge(
"info", "create user", "query:%s, param:%s" %
(q, [self._db_username,
str('%'), self._db_password]))
query = "GRANT SELECT, INSERT, UPDATE, " + \
" DELETE ON %s.* to '%s'@'%s' ;"
param = (self._db_name, self._db_username, str('%'))
cursor.execute(query % param)
cursor.execute("FLUSH PRIVILEGES;")
logger.logKnowledge("info", "grant privileges",
"query:%s, param:%s" % (query, param))
except Exception, ex:
logger.logKnowledge(
"error", "create user", "query:%s, param:%s" %
(q, [self._db_username,
str('%'), self._db_password]))
logger.logKnowledge("error", "grant privileges",
"query:%s, param:%s" % (query, param))
logger.logKnowledge("error", "create", str(ex))
e = "client database user grant_privileges failed"
raise RuntimeError(str(e))
def delete_action_after_approval(db, csv_id):
try:
args = [csv_id]
db.call_proc("sp_statutory_mapping_delete", args)
return True
except Exception, e:
logger.logKnowledge("error", "update action from list",
str(traceback.format_exc()))
logger.logKnowledge("error", "update action from list", str(e))
raise fetch_error()
def _mysql_server_connect(self, host, username, password, port):
try:
return mysql.connect(host=host,
user=username,
passwd=password,
port=port)
except Exception, e:
logger.logKnowledge("error", "mysql server connect",
"%s, %s, %s" % (host, port, username))
logger.logKnowledge("error", "mysql server connect", str(e))
raise RuntimeError("server connection failed")
def save_action_from_view(db, csv_id, sm_id, action, remarks, session_user):
try:
args = [csv_id, sm_id, action, remarks]
data = db.call_proc("sp_approve_mapping_action_save", args)
print data
return True
except Exception, e:
logger.logKnowledge("error", "update action from view",
str(traceback.format_exc()))
logger.logKnowledge("error", "update action from view", str(e))
raise fetch_error()
def verify_password(db, user_id, encrypt_password):
try:
row = db.call_proc("sp_verify_password", [user_id, encrypt_password])
# if int(row[0]["count"]) == 0:
# raise process_error("E065")
# else
print row
return int(row[0]["count"])
except Exception, e:
logger.logKnowledge("error", "verify_password",
str(traceback.format_exc()))
logger.logKnowledge("error", "verify_password", str(e))
raise fetch_error()
def _create_le_trigger(self, cursor):
t6 = "CREATE TRIGGER `tbl_statutory_notifications_insert` AFTER INSERT ON `tbl_statutory_notifications` " + \
" FOR EACH ROW BEGIN " + \
" SET @notificationid = NEW.notification_id; " + \
" INSERT INTO tbl_statutory_notifications_users ( " + \
" notification_id, user_id, is_read) " + \
" select distinct @notificationid, t1.user_id, 0 from tbl_users as t1 " + \
" left join tbl_user_domains as t3 on t1.user_id = t3.user_id " + \
" left join tbl_legal_entities as t4 on t3.legal_entity_id = t4.legal_entity_id " + \
" left join tbl_compliances as t2 " + \
" on t3.domain_id = t2.domain_id and t4.country_id = t2.country_id and" + \
" t2.compliance_id = new.compliance_id;" + \
" END ;"
cursor.execute(t6)
logger.logKnowledge("query", "create le trigger", t6)
def __init__(self, db, client_id, short_name, email_id, database_ip,
database_port, database_username, database_password):
logger.logKnowledge("info", "Database creation process", "begin")
self._db = db
self._client_id = client_id
self._short_name = short_name
self._email_id = email_id
self._host = database_ip
self._port = database_port
self._username = database_username
self._password = database_password
self._db_name = None
self._db_username = None
self._db_password = None
self._is_db_failed = False
self._db_file_path = None
self._db_prefix = None
def update_approve_action_from_list(db, csv_id, action, remarks, session_user,
type):
try:
print "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&"
print "type-> ", type
if type == "all":
args = [csv_id, action, remarks, session_user.user_id()]
data = db.call_proc("sp_statutory_mapping_update_all_action", args)
print data
return True
else:
args = [csv_id, session_user.user_id()]
db.call_proc("sp_statutory_update_action", args)
return True
except Exception, e:
logger.logKnowledge("error", "update action from list",
str(traceback.format_exc()))
logger.logKnowledge("error", "update action from list", str(e))
raise fetch_error()
def update_bulk_client_unit_approve_reject_list(db, csv_unit_id, action,
remarks, declined_count,
session_user):
try:
args = [
csv_unit_id, action, remarks,
session_user.user_id(), declined_count
]
print args
data = db.call_proc("sp_bulk_client_unit_update_action", args)
print "here"
print data
return True
except Exception, e:
logger.logKnowledge("error", "update action from list",
str(traceback.format_exc()))
logger.logKnowledge("error", "update action from list", str(e))
raise fetch_error()
def _create_tables(self, cursor):
# "scripts/mirror-client-group.sql"
try:
cmd = None
sql_script_path = os.path.join(
os.path.join(os.path.split(__file__)[0], ".."),
self._db_file_path)
with io.FileIO(sql_script_path, "r") as file_obj:
sql_file = file_obj.read()
sql_commands = sql_file.split(';')
size = len(sql_commands)
for index, command in enumerate(sql_commands):
if (index < size - 1):
cmd = command
cursor.execute(command)
else:
break
except Exception, e:
logger.logKnowledge("error", "create table", "query:%s" % (cmd))
logger.logKnowledge("error", "create table", str(e))
raise RuntimeError("table creation failed")
def _create_db(self, cursor):
query = "CREATE DATABASE %s" % self._db_name
try:
cursor.execute("DROP DATABASE IF EXISTS %s" % (self._db_name))
cursor.execute(query)
logger.logKnowledge("info", "creating database",
"query:%s" % (query))
except Exception, ex:
self._is_db_failed = True
logger.logKnowledge("error", "create", query)
logger.logKnowledge("error", "create", str(ex))
e = "client db creation failed"
raise RuntimeError(str(e))
def delete_database(self):
con = self._mysql_server_connect(self._host, self._username,
self._password, self._port)
try:
cursor = con.cursor()
query = "DROP DATABASE IF EXISTS %s" % self._db_name
cursor.execute(query)
con.commit()
logger.logKnowledge("info", "dropping database",
"query:%s" % (query))
except Exception, e:
logger.logKnowledge("error", "drop", query)
logger.logKnowledge("error", "drop", str(e))
con.rollback()
raise RuntimeError(str(e))
def _create_admin_user(self, cursor):
try:
query = "insert into tbl_users (user_category_id, client_id, employee_name, " + \
" email_id " + \
" )" + \
" values (1, %s, 'Administrator', %s)"
cursor.execute(query, [self._client_id, self._email_id])
logger.logKnowledge(
"query", "create admin user", "query:%s, param:%s" %
(query, [self._client_id, self._email_id]))
except Exception, e:
logger.logKnowledge(
"error", "create admin user", "query:%s, param:%s" %
(query, [self._client_id, self._email_id]))
logger.logKnowledge("error", "create admin user", str(e))
raise RuntimeError("admin user creation failed")
def _create_database(self):
db_con = None
main_con = None
try:
temp_var = "%s, %s, %s" % (self._host, self._username,
self._db_name)
logger.logKnowledge("info", "create database - begin", temp_var)
main_con = self._mysql_server_connect(self._host, self._username,
self._password, self._port)
main_cursor = main_con.cursor()
self._create_db(main_cursor)
self._grant_privileges(main_cursor)
main_con.commit()
db_con = self._db_connect(self._host, self._username,
self._password, self._db_name,
self._port)
self.db_con = db_con
db_cursor = db_con.cursor()
self._create_tables(db_cursor)
self._create_trigger(db_cursor)
self._create_admin_user(db_cursor)
db_con.commit()
logger.logKnowledge("info", "create database - end", temp_var)
return (True, self._db_name, self._db_username, self._db_password)
except Exception, e:
logger.logKnowledge("error", "create_database", str(e))
if db_con is not None:
db_con.rollback()
if main_con is not None:
main_con.rollback()
if self._is_db_failed:
self.delete_database()
raise Exception(e)
def bulk_insert(self, cursor, 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:
assert cursor is not None
cursor.executemany(query, valueList)
logger.logKnowledge("query", "insert data to client db",
"query:%s, param:%s" % (query, valueList))
cursor.nextset()
return True
except Exception, e:
logger.logKnowledge("error", "insert data",
"query:%s, param:%s" % (query, valueList))
logger.logKnowledge("error", "insert data", str(e))
return False
def _create_procedure(self, cursor):
try:
p1 = "CREATE PROCEDURE `procedure_to_update_version` " + \
" (IN update_type VARCHAR(100)) " + \
" BEGIN " + \
" SET SQL_SAFE_UPDATES=0; " + \
" case " + \
" when update_type = 'unit' then " + \
" SET @count = (SELECT unit_details_version+1 " + \
" FROM tbl_mobile_sync_versions ); " + \
" update tbl_mobile_sync_versions set " + \
" unit_details_version = @count; " + \
" when update_type = 'user' then " + \
" SET @count = (SELECT user_details_version+1 " + \
" FROM tbl_mobile_sync_versions ); " + \
" update tbl_mobile_sync_versions set " + \
" user_details_version = @count; " + \
" when update_type = 'compliance' then " + \
" SET @count = (SELECT compliance_applicability_version+1 " + \
" FROM tbl_mobile_sync_versions ); " + \
" update tbl_mobile_sync_versions set " + \
" compliance_applicability_version = @count; " + \
" when update_type = 'history' then " + \
" SET @count = (SELECT compliance_history_version+1 " + \
" FROM tbl_mobile_sync_versions ); " + \
" update tbl_mobile_sync_versions set " + \
" compliance_history_version = @count; " + \
" end case; " + \
" SET SQL_SAFE_UPDATES=1; " + \
" END "
cursor.execute(p1)
logger.logKnowledge("info", "_create_procedure", p1)
except Exception, e:
logger.logKnowledge("error", "_create_procedure", p1)
logger.logKnowledge("error", "_create_procedure", str(e))
raise self.process_error(
"procedure creation failed in client database")
def parse_dictionary_values(x, field_names=[], is_validation_and_parse=False):
for field_name in field_names:
print field_name
try:
val = x.get(field_name)
param = api_params.get(field_name)
if param is None:
raise ValueError('%s is not configured in settings' %
(field_name))
_type = param.get('type')
_module_name = param.get('module_name')
_class_name = param.get('class_name')
_validation_method = param.get('validation_method')
_is_optional = param.get("is_optional")
if _type == 'VECTOR_TYPE':
assert _module_name is not None
assert _class_name is not None
if _is_optional is False or val is not None:
val = parse_VectorType(_module_name, _class_name, val)
if is_validation_and_parse is True:
x[field_name] = val
elif _type == 'MAP_TYPE':
assert _module_name is not None
assert _class_name is not None
# assert _validation_method is not None
val = parse_MapType(_module_name, _class_name,
_validation_method, val)
if is_validation_and_parse is True:
x[field_name] = val
elif _type == 'MAP_TYPE_VECTOR_TYPE':
map = {}
for key, value in val.items():
key = _validation_method(key)
vals = parse_VectorType(_module_name, _class_name, val)
map[key] = vals
val = map
elif param.get('type') == 'RECORD_TYPE':
assert param.get('module_name') is not None
assert param.get('class_name') is not None
if param.get('is_optional') is True and val is None:
val = None
else:
val = parse_RecordType(param.get('module_name'),
param.get('class_name'), val)
if is_validation_and_parse is True:
x[field_name] = val
elif _type == 'ENUM_TYPE':
assert _module_name is not None
assert _class_name is not None
val = parse_EnumType(_module_name, _class_name, val)
else:
val = parse_values(field_name, param, val, "parse")
if (val is not None and _validation_method is not None
and _type != "MAP_TYPE" and type(val) != list):
val = _validation_method(val)
except Exception, e:
logger.logKnowledge("error", "field_name", str(field_name))
logger.logKnowledge("error", "parse_values",
str(traceback.format_exc()))
raise ValueError(str(e))
def to_structure_dictionary_values(x):
keys = x.keys()
if len(keys) == 0:
return {}
for field_name in keys:
try:
val = x.get(field_name)
param = api_params.get(field_name)
if param is None:
raise ValueError('%s is not configured in settings' %
(field_name))
_type = param.get('type')
_module_name = param.get('module_name')
_class_name = param.get('class_name')
_validation_method = param.get('validation_method')
if param is None:
raise ValueError('%s is not configured in settings' %
(field_name))
if _type == 'VECTOR_TYPE':
assert _module_name is not None
assert _class_name is not None
val = to_VectorType(_module_name, _class_name, val)
elif _type == 'MAP_TYPE':
assert _module_name is not None
assert _class_name is not None
assert _validation_method is not None
val = to_MapType(_module_name, _class_name, _validation_method,
val)
elif _type == 'MAP_TYPE_VECTOR_TYPE':
map = {}
for key, value in val.items():
key = _validation_method(key)
if type(value) is list:
vals = to_VectorType(_module_name, _class_name, value)
map[key] = vals
else:
map[key] = value
val = map
elif param.get('type') == 'RECORD_TYPE':
assert param.get('module_name') is not None
assert param.get('class_name') is not None
if param.get('is_optional') is True and val is None:
val = None
else:
val = to_RecordType(param.get('module_name'),
param.get('class_name'), val)
elif _type == 'ENUM_TYPE':
assert _module_name is not None
assert _class_name is not None
val = to_EnumType(_module_name, _class_name, val)
else:
val = parse_values(field_name, param, val)
if (val is not None and _validation_method is not None
and _type != 'MAP_TYPE' and _type != 'MAP_TYPE_VECTOR_TYPE'
and type(val) != list):
val = _validation_method(val)
x[field_name] = val
except Exception, e:
logger.logKnowledge("error", "field_name", str(field_name))
logger.logKnowledge("error", "parse_to_values",
str(traceback.format_exc()))
raise ValueError(str(e))
def process_error(self, message):
logger.logKnowledge("error", "ClientDDBase", message)
raise ValueError(str(message))
def _create_trigger(self, cursor):
try:
t1 = "CREATE TRIGGER `after_tbl_legal_entity_insert` AFTER INSERT ON `tbl_legal_entities` " + \
" FOR EACH ROW BEGIN " + \
" INSERT INTO tbl_le_replication_status(legal_entity_id) values(new.legal_entity_id); " + \
" INSERT INTO tbl_reminder_settings(client_id, legal_entity_id) select client_id, new.legal_entity_id from " + \
" tbl_client_groups ;" + \
" END; "
cursor.execute(t1)
logger.logKnowledge("info", "create trigger", t1)
t2 = " CREATE TRIGGER `after_tbl_user_legal_entities_insert` AFTER INSERT ON `tbl_user_legal_entities` " + \
" FOR EACH ROW BEGIN " + \
" INSERT INTO tbl_le_user_replication_status(legal_entity_id, user_id, s_action) " + \
" values(new.legal_entity_id, new.user_id, 1) on duplicate key update s_action = 1; " + \
" UPDATE tbl_le_replication_status set user_data = 1 where legal_entity_id = new.legal_entity_id; " + \
" END; "
cursor.execute(t2)
logger.logKnowledge("info", "create trigger", t2)
t3 = "CREATE TRIGGER `after_tbl_user_legal_entities_delete` AFTER DELETE ON `tbl_user_legal_entities` " + \
" FOR EACH ROW BEGIN " + \
" if old.user_id is not null then " + \
" INSERT INTO tbl_le_user_replication_status(legal_entity_id, user_id, s_action) " + \
" values(old.legal_entity_id, old.user_id, 3) on duplicate key update s_action = 3; " + \
" UPDATE tbl_le_replication_status set user_data = 1 where legal_entity_id = old.legal_entity_id; " + \
" end if; " + \
" END; "
cursor.execute(t3)
logger.logKnowledge("info", "create trigger", t3)
t4 = "CREATE TRIGGER `after_tbl_service_providers_insert` AFTER INSERT ON `tbl_service_providers` " + \
" FOR EACH ROW BEGIN " + \
" insert into tbl_le_provider_replication_status(legal_entity_id, provider_id, s_action) " + \
" select legal_entity_id, new.service_provider_id, 1 from tbl_legal_entities on duplicate key update s_action = 1; " + \
" UPDATE tbl_le_replication_status set provider_data = 1 ; " + \
" END ;"
cursor.execute(t4)
logger.logKnowledge("info", "create trigger", t4)
t5 = "CREATE TRIGGER `after_tbl_service_providers_update` AFTER UPDATE ON `tbl_service_providers` " + \
" FOR EACH ROW BEGIN " + \
" insert into tbl_le_provider_replication_status(legal_entity_id, provider_id, s_action) " + \
" select legal_entity_id, new.service_provider_id, 1 from tbl_legal_entities on duplicate key update s_action = 1; " + \
" UPDATE tbl_le_replication_status set provider_data = 1 ; " + \
" END ;"
cursor.execute(t5)
logger.logKnowledge("info", "create trigger", t5)
t6 = "CREATE TRIGGER `after_tbl_reminder_settings_update` AFTER UPDATE ON `tbl_reminder_settings` " + \
" FOR EACH ROW BEGIN " + \
" insert into tbl_le_settings_replication_status(legal_entity_id, s_action) " + \
" values(new.legal_entity_id, 1) on duplicate key update s_action = 1; " + \
" UPDATE tbl_le_replication_status set settings_data = 1 where legal_entity_id = new.legal_entity_id ; " + \
" END ;"
cursor.execute(t6)
logger.logKnowledge("info", "create trigger", t6)
t7 = "CREATE TRIGGER `after_tbl_users_status_update` AFTER UPDATE ON `tbl_users` " + \
" FOR EACH ROW BEGIN " + \
" if ((old.is_active <> new.is_active) or (old.is_disable <> new.is_disable)) then " + \
" INSERT INTO tbl_le_user_replication_status(legal_entity_id, user_id, s_action) " + \
" select legal_entity_id, new.user_id, 1 from tbl_user_legal_entities where user_id = new.user_id " + \
" on duplicate key update s_action = 1; " + \
" UPDATE tbl_le_replication_status set user_data = 1 " + \
" where legal_entity_id in (select legal_entity_id from tbl_user_legal_entities where user_id = new.user_id); " + \
" end if; " + \
" END ; "
cursor.execute(t7)
logger.logKnowledge("info", "create trigger", t7)
except Exception, e:
logger.logGroup("error", "_create_trigger", str(e))
raise self.process_error(
"trigger creation failed in client database ")