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 ")