def delete(self, ci_id):
ci = db.session.query(CI).filter(CI.ci_id == ci_id).first()
if ci is not None:
attrs = db.session.query(CITypeAttribute.attr_id).filter(
CITypeAttribute.type_id == ci.type_id).all()
attr_names = []
for attr in attrs:
attr_names.append(CIAttributeCache.get(attr.attr_id).attr_name)
attr_names = set(attr_names)
for attr_name in attr_names:
Table = TableMap(attr_name=attr_name).table
db.session.query(Table).filter(Table.ci_id == ci_id).delete()
db.session.query(CIRelation).filter(
CIRelation.first_ci_id == ci_id).delete()
db.session.query(CIRelation).filter(
CIRelation.second_ci_id == ci_id).delete()
db.session.query(CIAttributeHistory).filter(
CIAttributeHistory.ci_id == ci_id).delete()
db.session.flush()
db.session.delete(ci)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error("delete CI error, {0}".format(str(e)))
return abort(500, "delete CI error, {0}".format(str(e)))
# TODO: write history
ci_delete.apply_async([ci.ci_id], queue="cmdb_async")
return ci_id
return abort(404, "CI {0} not found".format(ci_id))
def _query_wrap_for_device(self, query_sql, **kwargs):
_type = kwargs.pop("_type", False) or kwargs.pop("type", False) \
or kwargs.pop("ci_type", False)
if _type:
ci_type = CITypeCache.get(_type)
if ci_type is None:
return
query_sql = query_sql.filter(CI.type_id == ci_type.type_id)
for k, v in kwargs.iteritems():
attr = CIAttributeCache.get(k)
if attr is None:
continue
Table = TableMap(attr_name=k).table
CI_table = query_sql.subquery()
query_sql = db.session.query(CI_table.c.ci_id).join(
Table, Table.ci_id == CI_table.c.ci_id).filter(
Table.attr_id == attr.attr_id).filter(
Table.value.ilike(v.replace("*", "%")))
current_app.logger.debug(query_sql)
sort_by = kwargs.pop("sort", False)
if sort_by:
query_sql = self._sort_handler(sort_by, query_sql)
return query_sql
def in_query_handler(self, attr, v):
new_v = v[1:-1].split(";")
table_name = TableMap(attr_name=attr.attr_name).table_name
_query_sql = QUERY_CI_BY_ATTR_NAME.format(
table_name, attr.attr_id, " OR {0}.value ".format(table_name).join(
['LIKE "{0}"'.format(_v.replace("*", "%")) for _v in new_v]))
return _query_sql
def range_query_handler(self, attr, v):
start, end = [x.strip() for x in v[1:-1].split("_TO_")]
table_name = TableMap(attr_name=attr.attr_name).table_name
_query_sql = QUERY_CI_BY_ATTR_NAME.format(
table_name, attr.attr_id, "BETWEEN '{0}' AND '{1}'".format(
start.replace("*", "%"), end.replace("*", "%")))
return _query_sql
def ci_is_exist(self, ci_type, unique_key, unique):
table = TableMap(attr_name=unique_key.attr_name).table
unique = db.session.query(table).filter(
table.attr_id == unique_key.attr_id).filter(
table.value == unique).first()
if unique:
return db.session.query(CI).filter(
CI.ci_id == unique.ci_id).first()
def comparison_query_handler(self, attr, v):
table_name = TableMap(attr_name=attr.attr_name).table_name
if (v.startswith("<") and not v.startswith("<=")) or \
(v.startswith(">") and not v.startswith(">=")):
_query_sql = QUERY_CI_BY_ATTR_NAME.format(
table_name, attr.attr_id,
"{0} '{1}'".format(v[0], v[1:].replace("*", "%")))
elif v.startswith(">=") or v.startswith("<="):
_query_sql = QUERY_CI_BY_ATTR_NAME.format(
table_name, attr.attr_id,
"{0} '{1}'".format(v[:2], v[2:].replace("*", "%")))
return _query_sql
def _get_attr_values(self,
fields,
ci_id,
ret_key="name",
uniq_key=None,
use_master=False):
res = dict()
for field in fields:
attr = CIAttributeCache.get(field)
if not attr:
current_app.logger.warn('attribute %s not found' % field)
return res
table = TableMap(attr_name=attr.attr_name).table
if use_master:
rs = db.session().using_bind("master").query(
table.value).filter_by(ci_id=ci_id).filter_by(
attr_id=attr.attr_id)
else:
rs = db.session.query(table.value).filter_by(
ci_id=ci_id).filter_by(attr_id=attr.attr_id)
field_name = getattr(attr, "attr_{0}".format(ret_key))
try:
if attr.is_multivalue:
if attr.value_type == 'datetime':
res[field_name] = [
datetime.datetime.strftime(x.value,
'%Y-%m-%d %H:%M:%S')
for x in rs.all()
]
else:
res[field_name] = [x.value for x in rs.all()]
else:
x = rs.first()
if x:
if attr.value_type == 'datetime':
res[field_name] = datetime.datetime.strftime(
rs.first().value, '%Y-%m-%d %H:%M:%S')
else:
res[field_name] = rs.first().value
else:
res[field_name] = None
except AttributeError as e:
current_app.logger.warn("get ci by id error, {0}".format(e))
if attr.is_multivalue:
res[field_name] = list()
else:
res[field_name] = ""
if uniq_key is not None and attr.attr_id == uniq_key.attr_id \
and rs.first() is not None:
res['unique'] = uniq_key.attr_name
return res
def facet_build(self):
facet = {}
for f in self.facet_field:
k, field_type, _, attr = self.attr_name_proc(f)
if k:
table_name = TableMap(attr_name=k).table_name
query_sql = FACET_QUERY.format(
table_name, self.query_sql, attr.attr_id)
result = db.session.execute(query_sql).fetchall()
facet[k] = result
facet_result = dict()
for k, v in facet.items():
if not k.startswith('_'):
a = getattr(CIAttributeCache.get(k), "attr_%s" % self.ret_key)
facet_result[a] = list()
for f in v:
if f[1] != 0:
facet_result[a].append((f[0], f[1], a))
return facet_result
def add_heartbeat(self, ci_type, unique):
ci_type = CITypeCache.get(ci_type)
if not ci_type:
return 'error'
uniq_key = CIAttributeCache.get(ci_type.uniq_id)
Table = TableMap(attr_name=uniq_key.attr_name).table
ci_id = db.session.query(
Table.ci_id).filter(Table.attr_id == uniq_key.attr_id).filter(
Table.value == unique).first()
if ci_id is None:
return 'error'
ci = db.session.query(CI).filter(CI.ci_id == ci_id.ci_id).first()
if ci is None:
return 'error'
ci.heartbeat = datetime.datetime.now()
db.session.add(ci)
db.session.commit()
return "ok"
def _sort_handler(self, sort_by, query_sql):
if sort_by.startswith("+"):
sort_type = "asc"
sort_by = sort_by[1:]
elif sort_by.startswith("-"):
sort_type = "desc"
sort_by = sort_by[1:]
else:
sort_type = "asc"
attr = CIAttributeCache.get(sort_by)
if attr is None:
return query_sql
attr_id = attr.attr_id
Table = TableMap(attr_name=sort_by).table
CI_table = query_sql.subquery()
query_sql = db.session.query(CI_table.c.ci_id, Table.value).join(
Table, Table.ci_id == CI_table.c.ci_id).filter(
Table.attr_id == attr_id).order_by(
getattr(Table.value, sort_type)())
return query_sql
def query_build_raw(self):
query_sql, alias, tor = "", "A", ["&"]
is_first = True
only_type_query = False
queries = self.orig_query.split(",")
queries = filter(lambda x: x != "", queries)
for q in queries:
if q.startswith("_type"):
queries.remove(q)
queries.insert(0, q)
if len(queries) == 1 or queries[1].startswith("-") or \
queries[1].startswith("~"):
only_type_query = True
break
current_app.logger.debug(queries)
special = True
for q in queries:
_query_sql = ""
if ":" in q:
k = q.split(":")[0].strip()
v = ":".join(q.split(":")[1:]).strip()
current_app.logger.info(v)
field, field_type, tor, attr = self.attr_name_proc(k)
if field == "_type":
_query_sql = self.type_query_handler(v, only_type_query)
current_app.logger.debug(_query_sql)
elif field == "_id": # exclude all others
_ci_ids = [str(v)]
ci = db.session.query(
CI.ci_id).filter(CI.ci_id == int(v)).first()
if ci is not None:
return 1, _ci_ids
elif field:
if attr is None:
raise SearchError("{0} is not found".format(field))
# in query
if v.startswith("(") and v.endswith(")"):
_query_sql = self.in_query_handler(attr, v)
# range query
elif v.startswith("[") and v.endswith("]") and "_TO_" in v:
_query_sql = self.range_query_handler(attr, v)
# comparison query
elif v.startswith(">=") or v.startswith("<=") or \
v.startswith(">") or v.startswith("<"):
_query_sql = self.comparison_query_handler(attr, v)
else:
table_name = \
TableMap(attr_name=attr.attr_name).table_name
_query_sql = QUERY_CI_BY_ATTR_NAME.format(
table_name, attr.attr_id,
'LIKE "{0}"'.format(v.replace("*", "%")))
else:
return 0, []
elif q:
return 0, []
if is_first and _query_sql and not only_type_query:
query_sql = "SELECT * FROM ({0}) AS {1}".format(
_query_sql, alias)
is_first = False
alias += "A"
elif only_type_query and special:
is_first = False
special = False
query_sql = _query_sql
elif _query_sql:
query_sql = self._wrap_sql(tor, alias, _query_sql, query_sql)
alias += "AA"
_start = time.time()
if query_sql:
self.query_sql = query_sql
current_app.logger.debug(query_sql)
numfound, res = self._execute_sql(query_sql, only_type_query)
current_app.logger.info("query ci ids is: {0}".format(time.time() -
_start))
return numfound, [_res[0] for _res in res]
return 0, []
def sort_query_handler(self, field, query_sql, only_type_query):
if field is None:
field = ""
if field.startswith("+"):
field = field[1:]
sort_type = "ASC"
elif field.startswith("-"):
field = field[1:]
sort_type = "DESC"
else:
sort_type = "ASC"
if field in ("_id", "ci_id") or not field:
if only_type_query:
return """SELECT SQL_CALC_FOUND_ROWS DISTINCT B.ci_id
FROM ({0}) AS B {1}""".format(
query_sql, "ORDER BY B.ci_id {1} LIMIT {0:d}, {2};".format(
(self.page - 1) * self.count, sort_type, self.count))
elif self.type_id_list:
self.query_sql = """SELECT B.ci_id
FROM ({0}) AS B {1}""".format(
query_sql, "INNER JOIN cis on cis.ci_id=B.ci_id "
"WHERE cis.type_id in ({0}) ".format(",".join(
self.type_id_list)))
return """SELECT SQL_CALC_FOUND_ROWS DISTINCT B.ci_id
FROM ({0}) AS B {1}""".format(
query_sql, "INNER JOIN cis on cis.ci_id=B.ci_id "
"WHERE cis.type_id in ({3}) "
"ORDER BY B.ci_id {1} LIMIT {0:d}, {2};".format(
(self.page - 1) * self.count, sort_type, self.count,
",".join(self.type_id_list)))
else:
self.query_sql = """SELECT B.ci_id
FROM ({0}) AS B {1}""".format(
query_sql, "INNER JOIN cis on cis.ci_id=B.ci_id ")
return """SELECT SQL_CALC_FOUND_ROWS DISTINCT B.ci_id
FROM ({0}) AS B {1}""".format(
query_sql, "INNER JOIN cis on cis.ci_id=B.ci_id "
"ORDER BY B.ci_id {1} LIMIT {0:d}, {2};".format(
(self.page - 1) * self.count, sort_type, self.count))
else:
attr = CIAttributeCache.get(field)
attr_id = attr.attr_id
table_name = TableMap(attr_name=attr.attr_name).table_name
_v_query_sql = """SELECT {0}.ci_id, {1}.value FROM
({2}) AS {0} INNER JOIN {1} ON {1}.ci_id = {0}.ci_id
WHERE {1}.attr_id = {3}""".format("ALIAS", table_name,
query_sql, attr_id)
new_table = _v_query_sql
if only_type_query:
return "SELECT SQL_CALC_FOUND_ROWS DISTINCT C.ci_id " \
"FROM ({0}) AS C " \
"ORDER BY C.value {2} " \
"LIMIT {1:d}, {3};".format(new_table,
(self.page - 1) * self.count,
sort_type, self.count)
elif self.type_id_list:
self.query_sql = """SELECT C.ci_id
FROM ({0}) AS C
INNER JOIN cis on cis.ci_id=C.ci_id
WHERE cis.type_id in ({1})""".format(
new_table, ",".join(self.type_id_list))
return """SELECT SQL_CALC_FOUND_ROWS DISTINCT C.ci_id
FROM ({0}) AS C
INNER JOIN cis on cis.ci_id=C.ci_id
WHERE cis.type_id in ({4})
ORDER BY C.value {2}
LIMIT {1:d}, {3};""".format(new_table,
(self.page - 1) * self.count,
sort_type, self.count,
",".join(self.type_id_list))
else:
return """SELECT SQL_CALC_FOUND_ROWS DISTINCT C.ci_id
FROM ({0}) AS C
ORDER BY C.value {2}
LIMIT {1:d}, {3};""".format(new_table,
(self.page - 1) * self.count,
sort_type, self.count)
def add_attr_value(self,
key,
value,
ci_id,
ci_type,
_no_attribute_policy="ignore",
ci_existed=False):
"""key is one of attr_id, attr_name and attr_alias
"""
attr = self._get_attr(key)
if attr is None:
if _no_attribute_policy == 'ignore':
return True, None
if _no_attribute_policy == 'reject':
return False, 'attribute {0} not exist'.format(key)
table, old_value, old_value_table = TableMap(
attr_name=attr.attr_name).table, None, None
if ci_existed:
old_value_table = db.session.query(table).filter(
table.attr_id == attr.attr_id).filter(
table.ci_id == ci_id).first()
if old_value_table is not None:
old_value = old_value_table.value
if not value and ci_existed:
db.session.query(table).filter(
table.attr_id == attr.attr_id).filter(
table.ci_id == ci_id).delete()
if old_value:
return True, (attr.attr_id, "delete", old_value, None)
else:
return True, None
elif not value:
return True, None
if not attr.is_multivalue:
ret, res = self._validate(attr, value, table, ci_id)
if not ret:
return False, res
value_table = table()
if ci_existed: # for history
old = db.session.query(table).filter(
table.attr_id == attr.attr_id).filter(
table.value == value).filter(
table.ci_id == ci_id).first()
if old is not None:
return True, None
elif old_value_table:
value_table = old_value_table
value_table.ci_id = ci_id
value_table.attr_id = attr.attr_id
value_table.value = res
db.session.add(value_table)
elif attr.is_multivalue:
if ci_existed:
db.session.query(table).filter(
table.attr_id == attr.attr_id).filter(
table.ci_id == ci_id).delete()
for v in value.strip().split(","):
ret, res = self._validate(attr, v, table, ci_id)
if not ret:
return False, res
value_table = table()
value_table.ci_id = ci_id
value_table.attr_id = attr.attr_id
value_table.value = res
db.session.add(value_table)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error(
"add attribute value is error, {0}".format(str(e)))
return False, "add attribute value is error, {0}".format(str(e))
if ci_existed:
if old_value != value:
return True, (attr.attr_id, "update", old_value, value)
else:
return True, None
return True, (attr.attr_id, "add", None, value)