def update_data(self, Model, filter_data, data, commit=False):
try:
valid_query = Model.query.filter_by(**filter_data).all()
if valid_query:
db.session.query(Model).filter_by(**filter_data).update(data)
db.session.flush()
if commit:
db.session.commit()
db.session.close()
db.engine.dispose()
return True
else:
return False
except exc.IntegrityError as integrity_err:
logger.log("Encountered Integrity error while inserting the data : %s" % (integrity_err), log_type='ERROR')
raise ValueError(ErrorObject(type="DuplicateEntryError", message="Encountered duplicate data").to_json())
except exc.DataError as data_err:
logger.log("Encountered Data error while inserting the data : %s" % (data_err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="MaxLengthError", message="Encountered max length").to_json())
except ValueError as value_err:
logger.log("Encountered value error while inserting the data : %s" % (value_err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="ValueError", message=str(value_err)).to_json())
def check_column_if_exist(self, table_name, column_name):
try:
sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS \
where TABLE_NAME = '%s' and COLUMN_NAME = '%s'" % (table_name, column_name)
sql_result = self.execute_raw_query(sql)
column = [row[0] for row in sql_result]
if len(column) is not 0:
return True
raise ValueError(ErrorObject(type="FieldsError", message="Fields doesn't exist on Target table").to_json())
except Exception as err:
logger.log("Encountered error : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="ExceptionError", message="Error on finding the columns on table").to_json())
def create_trigger(self, data):
sql = """
CREATE TRIGGER [dbo].trigger_{0} ON
[dbo].{1} AFTER INSERT AS BEGIN
INSERT INTO {2} ({3}, [{2}_source], [source_id], [source_table])
SELECT {4}, '{5}', [{1}_id], '{1}'
FROM dbo.{1} as t1 WITH (NOLOCK)
WHERE not exists (select * from {2} as t2
where t1.{1}_id = t2.source_id and t2.source_table = '{1}')
WAITFOR DELAY '00:00:01'
UPDATE {2} SET [date_dimension_id] = (SELECT date_id FROM date_dimension
WHERE [datetime] = cast({2}.datetime as date)) WHERE [date_dimension_id] IS NULL;
WAITFOR DELAY '00:00:01'
END;
""".format(
data['trigger_name'],
data['table'],
data['destination_table'],
','.join('[{}]'.format(dest) for dest in data['destination']),
','.join('[{}]'.format(targ) for targ in data['target']),
data['source']
)
try:
self.execute_raw_query(sql)
except Exception as error:
logger.log(sql)
logger.log("Encountered error : %s" % (str(error)), log_type='ERROR')
self.drop_trigger(data['trigger_name'])
raise ValueError(ErrorObject(type="StoredProcedureError", message="Please check the target tables and fields").to_json())
def drop_table(self, *table_name):
try:
for table in reversed(table_name):
sql = "DROP TABLE [{0}]".format(table)
self.execute_raw_query(sql)
except Exception as err:
logger.log("Encountered error : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="ExceptionError", message="Table name doesn't exist").to_json())
def select_query_with_filter(self, Model, filter):
try:
_model = Model
_model_list = db.session.query(_model).filter_by(**filter).all()
db.session.close()
db.engine.dispose()
return _model_list
except Exception as err:
raise ValueError(ErrorObject(type="QueryError", message="Invalid {table} id : {filter}".format(table=_model().__class__.__name__, filter=filter)).to_json())
def model_join_many(self, Model, model, *entities):
try:
_model_column = Model.__table__.columns
_model = Model.query.with_entities(*_model_column, *entities).outerjoin(*model)
return _model
except Exception as err:
raise ValueError(ErrorObject(type="JoinError", message="Error on join tables").to_json())
finally:
db.session.close()
db.engine.dispose()
def execute_raw_query(self, query):
try:
return db.engine.execute(query)
except Exception as err:
logger.log("Encountered error : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="ExceptionError", message="Error on executing the query").to_json())
finally:
db.session.commit()
db.session.close()
db.engine.dispose()
def post(self):
args = self.api_utils.parameters_without_model(config='append')
try:
config = args['config'][0].replace('\'', '\"')
if len(config) == 0:
raise ValueError(
ErrorObject(type="Config cannot be blank",
message="ConfigError").to_json())
config = json.loads(config)
config_preview = {
'ip_list': config['ip_list'],
'community_string': config['community_string'],
'oid_list': config['oid_list']
}
status = config['ip_list'][0] if config['ip_list'] else ""
if self.api_utils.check_list_has_empty_string(
config_preview['ip_list']) or isinstance(status, str):
raise ValueError("Invalid IP list value")
for ip in config_preview['ip_list']:
IpListSchema().load(ip)
if not isinstance(config_preview['oid_list'], list):
logger.log(
"Validation encountered on polling data preview: %s" %
('Invalid oid list values'),
log_type='ERROR')
return {
'lists': 'Invalid oid list',
'type': 'ValidationError',
'message': 'Validation errors in your request'
}, 422
poll_preview = PollPreview(config_preview)
result = poll_preview.run()
return result
except ValidationError as value_error:
logger.log("Validation encountered on polling data preview: %s" %
(str(value_error)),
log_type='ERROR')
return {
'lists': value_error.messages,
'type': 'ValidationError',
'message': 'Validation errors in your request'
}, 422
except Exception as err:
logger.log("Error encountered on polling data preview: %s" %
str(err),
log_type='ERROR')
return {'type': 'ExceptionType', 'message': str(err)}, 422
def check_table_if_not_exist(self, table_name):
try:
sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '%s'" % (table_name)
sql_result = self.execute_raw_query(sql)
table = [row[0] for row in sql_result]
if len(table) is 0:
return True
else:
return False
except Exception as err:
logger.log("Encountered error while checking the table if not exists : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="TableError", message="Error on checking the table if not exists").to_json())
def create_table(self,default_date=False,show_datetime=True, agg_type=None, status_id=False, normalize_status_id=False, source_ref=False, date_dim=False, date_dim_name='date_dimension_id'):
datetime = ""
status_field = ""
normalize_status_field = ""
source_fields = ""
date_dim_field = ""
fk = ""
if default_date:
datetime = '[datetime] datetime DEFAULT GETDATE(),'
else:
datetime = '[datetime] datetime,'
if status_id:
status_field = "[%s_status_id] VARCHAR(50) DEFAULT '0'," % (str(self.table).lower())
if normalize_status_id:
normalize_status_field = "[%s_normalize_status_id] VARCHAR(50) DEFAULT '0'," % (str(self.table).lower())
if show_datetime is False:
datetime = ""
if agg_type is not None:
if agg_type != 'current':
fk = '{0}_id INT FOREIGN KEY REFERENCES {0}_current({0}_current_id),'.format(self.orig_table_name)
if source_ref:
source_fields = "[source_id] INT NULL, \
[source_table] varchar(255), \
[{0}] INT FOREIGN KEY REFERENCES date_dimension(date_id),".format(date_dim_name)
if date_dim:
date_dim_field = "[{0}] INT FOREIGN KEY REFERENCES date_dimension(date_id),".format(date_dim_name)
try:
sql = """
CREATE TABLE [%s]
( [%s_id] INT IDENTITY(1,1) PRIMARY KEY,
%s %s %s %s %s %s %s )
""" % (self.table,
str(self.table).lower(),
''.join('[{}] VARCHAR(255),'.format(field) for field in self.fields),
''.join('[{0}_{1}] VARCHAR(255),'.format(str(self.table).lower(),default) for default in self.default),
datetime,
fk,
status_field,
source_fields,
date_dim_field)
self.execute_raw_query(sql)
return True
except Exception as err:
raise ValueError(ErrorObject(type="TableError", message="Encountered error while creating table.").to_json())
def insert_data(self ,module_name, Model, data, commit=False):
try:
model = Model(**data)
db.session.add(model)
db.session.flush()
if commit:
db.session.commit()
return model.id
except exc.IntegrityError as integrity_err:
logger.log("Encountered Integrity error while inserting the data : %s" % (integrity_err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="DuplicateEntryError", message="%s already exists." % (module_name)).to_json())
except exc.DataError as data_err:
logger.log("Encountered Data error while inserting the data : %s" % (data_err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="MaxLengthError", message="Encountered max length").to_json())
except Exception as err:
logger.log("Encountered error while inserting the data : %s" % (err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="InsertError", message="Encountered while inserting the data").to_json())
finally:
db.session.close()
db.engine.dispose()
def create_data_retention(self, table_name, retention=30):
sql = """
CREATE TRIGGER [dbo].trigger_{0} ON
[dbo].{0} AFTER INSERT AS BEGIN
DELETE FROM dbo.{0} WHERE {0}_id in (SELECT {0}_id
FROM dbo.{0}
where cast([datetime] as date) < CAST(DATEADD(DAY,-{1},GETDATE()) AS date))
END
""".format(table_name, retention)
try:
self.execute_raw_query(sql)
except Exception as error:
logger.log("Encountered error : %s" % (error), log_type='ERROR')
self.drop_trigger('data_retention_{0}'.format(table_name))
raise ValueError(ErrorObject(type="StoredProcedureError", message="Please check the target tables and fields").to_json())
def alter_table(self, table_name, fields, status):
try:
mode = ""
status = status.lower()
if status == 'add':
mode = 'ADD {} VARCHAR(255)'.format(fields)
elif status == 'drop':
mode = 'DROP COLUMN {}'.format(fields)
sql = """
ALTER TABLE {0} {1}
""".format(table_name, mode)
self.execute_raw_query(sql)
return status
except Exception as err:
raise ValueError(ErrorObject(type="AlterTableError", message="Encountered while altering the table").to_json())
def delete_data_using_id(self, Model, id):
try:
valid_query = db.session.query(Model).filter_by(**id).all()
if valid_query:
db.session.query(Model).filter_by(**id).delete(synchronize_session=False)
db.session.commit()
return True
else:
return False
except Exception as err:
db.session.rollback()
logger.log("Encountered value error while deleting the data : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="ExceptionError",
message="Encountered error while deleting the data").to_json())
finally:
db.session.close()
db.engine.dispose()
def filter_with_paginate(self, model_session, Model,id=None, args=None, params=None):
try:
data_query = model_session
if id is not None:
data_query = data_query.filter(Model.id==id)
elif args['start'] != 1 or args['limit'] != 0:
data_query = data_query.order_by(Model.id.desc()).paginate(args['start'],args['limit'], False).items
elif params:
data_query = data_query.filter_by(**params).all()
else:
data_query = data_query.order_by(Model.id.desc()).all()
except Exception as error:
raise ValueError(ErrorObject(type="PaginateError", message="Error on filter and paginate").to_json())
finally:
db.session.close()
db.engine.dispose()
return data_query
def delete(self, id=None):
try:
get_table_name = None
poller = self.db_utils.select_with_filter(self.main_model,
self.main_schema,
{'id': id})
if poller:
# poller_tasklist = self.service.check_service(poller[0]['pid'], 'poller.exe')
poller_tasklist = self.service.check_service(
poller[0]['pid'], 'poller.py')
if 'image name' in str(
poller_tasklist.decode("utf-8")).lower():
raise ValueError(
ErrorObject(type="ServiceError",
message="Poller is running.").to_json())
delete_status = self.db_utils.delete_data_using_id(
SnmpPoller, {'id': id})
get_table_name = poller[0]['table_name']
self.db_utils.drop_table(get_table_name)
logger.log("Deleting the data on SNMP Poller, id : %s" % (id))
return {'message': "Successfully deleted."}, 200
else:
logger.log("Encountered error on poller: ID doesn't exist",
log_type='ERROR')
return {
'message': "ID doesn't exist",
"type": "ReferenceError"
}, 422
except Exception as err:
logger.log("Error encountered on SNMP Poller : %s" % str(err),
log_type='ERROR')
return eval(str(err)), 422
def data(self, no_limit=None):
try:
auth = self.authentication()
parameter = self.set_parameters()
if self.args['type'] == "API":
if self.auth['authentication_type'] == 'token':
api_url = "{0}{1}".format(self.auth['host'],
self.args['url'])
target_api = requests.request(
self.args['method'],
api_url,
headers={self.auth['token_header']: auth['Token']},
params=parameter,
verify=False)
status_code = target_api.status_code
if status_code == 401 or status_code == 403 \
or status_code == 400 or status_code == 404:
raise ValueError("Invalid url or method")
return target_api.json()
else:
api_url = "{0}{1}".format(self.auth['host'],
self.args['url'])
for param in self.args['parameter']:
param = eval(param)
if param['param_type'] == 'url':
api_url = '{0}/{1}'.format(api_url, param['value'])
target_api = requests.request(
self.args['method'],
api_url,
headers={
self.auth['token_header']: self.auth['password']
},
params=parameter,
verify=False)
status_code = target_api.status_code
if status_code == 401 or status_code == 403 \
or status_code == 400 or status_code == 404:
raise ValueError("Invalid url or method")
return {'0': target_api.json()}
elif self.args['type'] == "Database":
for param in self.args['parameter']:
param = eval(param)
# add top 1 in query
query = self.args['url']
if self.args['get_values']:
select_word = 'select'
select_index = query.lower().find(select_word)
if select_index != -1:
validate_line = query[select_index + 1:]
top_word = 'top 1'
top_index = validate_line.lower().find(top_word)
if top_index <= 0:
index = select_index + len(select_word)
query = query[:index] + ' TOP 5' + query[index:]
if no_limit:
query_string = "{0} {1}".format(self.args['url'],
self.set_parameters())
else:
query_string = "{0} {1}".format(query,
self.set_parameters())
data = auth.select_query(query_string)
#temp
data_cleanse = []
data_obj = {}
if data:
if no_limit:
return data
if self.args['get_values']:
for data_values in data:
for key, value in data_values.items():
data_obj[str(key)] = str(value)
data_cleanse.append(data_obj)
return data_cleanse
else:
for key, value in data[0].items():
data_obj[str(key)] = ""
data_cleanse.append(data_obj)
return data_cleanse
else:
data_fields = {}
if "*" in self.args['url']:
query_set = str(self.args['url']).split(" ")
sql_info_schema = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN "
for i in range(len(query_set)):
if str(query_set[i]).lower() == 'from':
tables = query_set[i + 1].split(",")
sql_info_schema += "({}".format(",".join(
"'{0}'".format(table) for table in tables))
if str(query_set[i]).lower() == 'join':
sql_info_schema += ",'{}'".format(query_set[i +
1])
sql_info_schema += ")"
data = auth.select_query(sql_info_schema)
new_dict = {}
for key in data:
data_fields[key['column_name']] = ""
data_cleanse.append(data_fields)
else:
query_set = re.sub("select|SELECT|distinct|DISTINCT",
"", str(self.args['url']))
parse_query = query_set[:str(query_set).lower().
find('from')].split(",")
for field in parse_query:
remove_period = field.find(".")
removed_period = field[remove_period + 1:]
final_removed = removed_period.split(" ")
if len(final_removed) >= 2:
data_fields[final_removed[2]] = ""
else:
data_fields[removed_period] = ""
# remove_period = field.find(".")
# data_fields[field[remove_period+1:]] = ""
data_cleanse.append(data_fields)
return data_cleanse
except ValueError as value_err:
raise ValueError(
ErrorObject(
message="Encountered an error : {0}".format(value_err),
type="ValueError").to_json())
except UnicodeError as unicode_error:
raise ValueError(
ErrorObject(
message="Encountered an error : {0}".format(unicode_error),
type="UnicodeError").to_json())
except requests.exceptions.ConnectionError as conn_err:
raise ValueError(
ErrorObject(message="Invalid request url or query format",
type="ConnectionError").to_json())
except Exception as err:
raise ValueError(
ErrorObject(message=str(err), type="ExceptionError").to_json())
