def update(self, type_id, type_name, type_alias, _id=None, unique=None,
icon_url="", enabled=None):
citype = CITypeCache.get(type_id)
if citype is None:
return False, "CIType {0} is not existed".format(type_name)
uniq_key = CIAttributeCache.get(_id) or CIAttributeCache.get(unique)
if uniq_key is not None:
citype.uniq_id = uniq_key.attr_id
citype_attr = db.session.query(CITypeAttribute).filter(
CITypeAttribute.type_id == type_id).filter(
CITypeAttribute.attr_id == uniq_key.attr_id).first()
if citype_attr is None:
citype_attr = CITypeAttribute()
citype_attr.attr_id = uniq_key.attr_id
citype_attr.type_id = type_id
citype_attr.is_required = True
db.session.add(citype_attr)
if type_name:
citype.type_name = type_name
if type_alias:
citype.type_alias = type_alias
if icon_url:
citype.icon_url = icon_url
if enabled is not None:
citype.enabled = enabled
db.session.add(citype)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error("add CIType is error, {0}".format(str(e)))
return False, str(e)
CITypeCache.clean(type_id)
return True, type_id
def get_ci_by_id(self, ci_id, ret_key="name",
fields=None, need_children=True, use_master=False):
"""@params: `ret_key` is one of 'name', 'id', 'alias'
`fields` is list of attribute name/alias/id
"""
ci = CI.query.get(ci_id) or \
abort(404, "CI {0} is not existed".format(ci_id))
res = dict()
if need_children:
children = self.get_children(ci_id, ret_key=ret_key) # one floor
res.update(children)
ci_type = CITypeCache.get(ci.type_id)
res["ci_type"] = ci_type.type_name
uniq_key = CIAttributeCache.get(ci_type.uniq_id)
if not fields: # fields are all attributes
attr_ids = db.session.query(CITypeAttribute.attr_id).filter_by(
type_id=ci.type_id)
fields = [CIAttributeCache.get(_.attr_id).attr_name
for _ in attr_ids]
if uniq_key.attr_name not in fields:
fields.append(uniq_key.attr_name)
if fields:
value_manager = AttributeValueManager()
_res = value_manager._get_attr_values(
fields, ci_id,
ret_key=ret_key, uniq_key=uniq_key, use_master=use_master)
res.update(_res)
res['_type'] = ci_type.type_id
res['_id'] = ci_id
return res
def add(self, type_name, type_alias, _id=None, unique=None,
icon_url="", enabled=True):
uniq_key = CIAttributeCache.get(_id) or CIAttributeCache.get(unique)
if uniq_key is None:
return False, "uniq_key is not existed"
citype = CITypeCache.get(type_name)
if citype:
return False, "this CIType {0} is existed".format(type_name)
_citype = CIType()
_citype.type_name = type_name
_citype.type_alias = type_alias
_citype.uniq_id = uniq_key.attr_id
_citype.enabled = enabled
_citype.icon_url = icon_url
db.session.add(_citype)
db.session.flush()
_citype_attr = CITypeAttribute()
_citype_attr.attr_id = uniq_key.attr_id
_citype_attr.type_id = _citype.type_id
_citype_attr.is_required = True
db.session.add(_citype_attr)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error("add CIType is error, {0}".format(str(e)))
return False, str(e)
CITypeCache.clean(type_name)
return True, _citype.type_id
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 delete(self, type_id, attr_ids=None):
"""
delete attributes at CIType, attr_ids are list
"""
if not attr_ids or not isinstance(attr_ids, list):
return abort(
500, "delete attribute of CIType, attr_ids must be required")
ci_type = CITypeCache.get(type_id)
if ci_type is None:
return abort(
404, "CIType ID({0}) is not existed".format(type_id))
for attr_id in attr_ids:
attr = CIAttributeCache.get(attr_id)
if attr is None:
return abort(
404, "attribute id {0} is not existed".format(attr_id))
db.session.query(CITypeAttribute).filter_by(
type_id=type_id).filter_by(attr_id=attr_id).delete()
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error(
"delete attributes of CIType is error, {0}".format(str(e)))
return abort(500, "delete attributes of CIType is error")
CITypeAttributeCache.clean(type_id)
return True
def add(self, type_id, attr_ids=None, is_required=False):
"""
add attributes to CIType, attr_ids are list
"""
if not attr_ids or not isinstance(attr_ids, list):
return abort(500, "attr_ids must be required")
ci_type = CITypeCache.get(type_id)
if ci_type is None:
return abort(404, "CIType ID({0}) is not existed".format(type_id))
for attr_id in attr_ids:
attr = CIAttributeCache.get(attr_id)
if attr is None:
return abort(404,
"attribute id {0} is not existed".format(attr_id))
existed = db.session.query(CITypeAttribute.attr_id).filter_by(
type_id=type_id).filter_by(attr_id=attr_id).first()
if existed is not None:
continue
current_app.logger.debug(attr_id)
db.session.add(CITypeAttribute(
type_id=type_id, attr_id=attr_id, is_required=is_required))
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error(
"add attribute to CIType is error, {0}".format(str(e)))
return abort(
500, "add attribute to CIType is error, maybe duplicate entry")
CITypeAttributeCache.clean(type_id)
return True
def get_detail_by_record(record_id=None):
record = db.session.query(OperationRecord).filter(
OperationRecord.record_id == record_id).first()
if record is None:
abort(404, "record is not found")
username = UserCache.get(record.uid).nickname \
if UserCache.get(record.uid).nickname \
else UserCache.get(record.uid).username
timestamp = record.timestamp.strftime("%Y-%m-%d %H:%M:%S")
attr_history = db.session.query(CIAttributeHistory).filter(
CIAttributeHistory.record_id == record_id).all()
rel_history = db.session.query(CIRelationHistory).filter(
CIRelationHistory.record_id == record_id).all()
attr_dict, rel_dict = dict(), {"add": [], "delete": []}
for attr_h in attr_history:
attr_dict[CIAttributeCache.get(attr_h.attr_id).attr_alias] = {
"old": attr_h.old,
"new": attr_h.new,
"operate_type": attr_h.operate_type
}
manager = CIManager()
for rel_h in rel_history:
_, first = manager.get_ci_by_id(rel_h.first_ci_id)
_, second = manager.get_ci_by_id(rel_h.second_ci_id)
rel_dict[rel_h.operate_type].append(
(first, rel_h.relation_type, second))
return jsonify(username=username,
timestamp=timestamp,
attr_history=attr_dict,
rel_history=rel_dict)
def add(self, attr_name, attr_alias, **kwargs):
choice_value = kwargs.get("choice_value", False)
attr = CIAttributeCache.get(attr_name)
if attr is not None:
return False, "attribute {0} is already existed".format(attr_name)
is_choice = False
if choice_value:
is_choice = True
if not attr_alias:
attr_alias = attr_name
attr = CIAttribute()
attr.attr_name = attr_name
attr.attr_alias = attr_alias
attr.is_choice = is_choice
attr.is_multivalue = kwargs.get("is_multivalue", False)
attr.is_uniq = kwargs.get("is_uniq", False)
attr.is_index = kwargs.get("is_index", False)
attr.value_type = kwargs.get("value_type", "text")
db.session.add(attr)
db.session.flush()
if choice_value:
self._add_choice_value(choice_value, attr.attr_id, attr.value_type)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error("add attribute error, {0}".format(str(e)))
return False, str(e)
CIAttributeCache.clean(attr)
return True, attr.attr_id
def update_unique_value(self, ci_id, args):
ci = self.get_ci_by_id(ci_id, need_children=False)
unique_key = ci.get("unique")
attr = CIAttributeCache.get(unique_key)
table_key = "index_{0}".format(attr.value_type) \
if attr.is_index else attr.value_type
value_table = type_map.get("table").get(table_key)
v = args.get(unique_key)
if value_table and v:
item = db.session.query(value_table).filter(
value_table.ci_id == ci_id).filter(
value_table.attr_id == attr.attr_id).first()
if item:
converter = type_map.get("converter").get(attr.value_type)
try:
item.value = converter(v)
except:
return abort(400, "value is illegal")
db.session.add(item)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error(str(e))
return abort(400, "update unique failed")
ci_cache.apply_async([ci_id], queue="cmdb_async")
def table_name(self):
if self.attr_name is not None:
attr = CIAttributeCache.get(self.attr_name)
if attr.is_index:
i = "index_{0}".format(attr.value_type)
else:
i = attr.value_type
return type_map["table_name"].get(i)
def get_citypes(self, type_name=None):
ci_types = db.session.query(CIType).all() if type_name is None else \
db.session.query(CIType).filter(
CIType.type_name.ilike("%{0}%".format(type_name))).all()
res = list()
for ci_type in ci_types:
type_dict = row2dict(ci_type)
type_dict["uniq_key"] = CIAttributeCache.get(
type_dict["uniq_id"]).attr_name
res.append(type_dict)
return res
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 attr_name_proc(self, key):
tor, key = self.tor_proc(key)
if key in ('ci_type', 'type', '_type'):
return '_type', 'text', tor, None
if key in ('id', 'ci_id', '_id'):
return '_id', 'text', tor, None
attr = CIAttributeCache.get(key)
if attr is not None:
# if not attr.is_index:
# raise SearchError("{0} is not indexed".format(attr.attr_name))
field_name = attr.attr_name
return field_name, attr.value_type, tor, attr
else:
raise SearchError("{0} is not existed".format(key))
def get_attributes_by_type(type_id=None, type_name=None):
manager = CITypeAttributeManager()
from models.attribute import CIAttributeCache
from models.ci_type import CITypeCache
from models.ci_type import CITypeAttributeCache
t = CITypeCache.get(type_id)
if not t:
t = CITypeCache.get(type_name)
if not t:
return abort(400, "CIType {0} is not existed".format(type_id))
type_id = t.type_id
uniq_id = t.uniq_id
CITypeAttributeCache.clean(type_id)
unique = CIAttributeCache.get(uniq_id).attr_name
return jsonify(attributes=manager.get_attributes_by_type_id(type_id),
type_id=type_id, uniq_id=uniq_id, unique=unique)
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 get_record():
page = get_page(request.values.get("page", 1))
_start = request.values.get("start")
_end = request.values.get("end")
username = request.values.get("username", "")
per_page_cnt = current_app.config.get("DEFAULT_PAGE_COUNT")
start, end = None, None
if _start:
try:
start = datetime.datetime.strptime(_start, '%Y-%m-%d %H:%M:%S')
except ValueError:
abort(400, 'incorrect start date time')
if _end:
try:
end = datetime.datetime.strptime(_end, '%Y-%m-%d %H:%M:%S')
except ValueError:
abort(400, 'incorrect end date time')
records = db.session.query(OperationRecord)
numfound = db.session.query(db.func.count(OperationRecord.record_id))
if start:
records = records.filter(OperationRecord.timestamp >= start)
numfound = numfound.filter(OperationRecord.timestamp >= start)
if end:
records = records.filter(OperationRecord.timestamp <= end)
numfound = records.filter(OperationRecord.timestamp <= end)
if username:
user = UserCache.get(username)
if user:
records = records.filter(OperationRecord.uid == user.uid)
else:
return jsonify(numfound=0,
records=[],
page=1,
total=0,
start=_start,
end=_end,
username=username)
records = records.order_by(-OperationRecord.record_id).offset(
per_page_cnt * (page - 1)).limit(per_page_cnt).all()
total = len(records)
numfound = numfound.first()[0]
res = []
for record in records:
_res = row2dict(record)
_res["user"] = UserCache.get(_res.get("uid")).nickname \
if UserCache.get(_res.get("uid")).nickname \
else UserCache.get(_res.get("uid")).username
attr_history = db.session.query(CIAttributeHistory.attr_id).filter(
CIAttributeHistory.record_id == _res.get("record_id")).all()
_res["attr_history"] = [
CIAttributeCache.get(h.attr_id).attr_alias for h in attr_history
]
rel_history = db.session.query(CIRelationHistory.operate_type).filter(
CIRelationHistory.record_id == _res.get("record_id")).all()
rel_statis = {}
for rel in rel_history:
if rel.operate_type not in rel_statis:
rel_statis[rel.operate_type] = 1
else:
rel_statis[rel.res.operate_type] += 1
_res["rel_history"] = rel_statis
res.append(_res)
return jsonify(numfound=numfound,
records=res,
page=page,
total=total,
start=_start,
end=_end,
username=username)
def get_cis_by_ids(ci_ids, ret_key="name", fields="", value_tables=None):
""" argument ci_ids are string list of CI instance ID, eg. ['1', '2']
"""
if not ci_ids:
return []
start = time.time()
ci_id_tuple = tuple(map(int, ci_ids))
res = rd.get(ci_id_tuple)
if res is not None and None not in res and ret_key == "name":
res = map(json.loads, res)
if not fields:
return res
else:
_res = []
for d in res:
_d = dict()
_d["_id"], _d["_type"] = d.get("_id"), d.get("_type")
_d["ci_type"] = d.get("ci_type")
for field in fields:
_d[field] = d.get(field)
_res.append(_d)
current_app.logger.debug("filter time: %s" % (time.time() - start))
return _res
current_app.logger.warning("cache not hit...............")
if not fields:
_fields = ""
else:
_fields = list()
for field in fields:
attr = CIAttributeCache.get(field)
if attr is not None:
_fields.append(str(attr.attr_id))
_fields = "WHERE A.attr_id in ({0})".format(",".join(_fields))
ci_ids = ",".join(ci_ids)
if value_tables is None:
value_tables = type_map["table_name"].values()
current_app.logger.debug(value_tables)
value_sql = " UNION ".join([
QUERY_CIS_BY_VALUE_TABLE.format(value_table, ci_ids)
for value_table in value_tables
])
query_sql = QUERY_CIS_BY_IDS.format(ci_ids, _fields, value_sql)
current_app.logger.debug(query_sql)
start = time.time()
hosts = db.session.execute(query_sql).fetchall()
current_app.logger.info("get cis time is: {0}".format(time.time() - start))
ci_list = set()
res = list()
ci_dict = dict()
start = time.time()
for ci_id, type_id, attr_id, attr_name, \
attr_alias, value, value_type, is_multivalue in hosts:
if ci_id not in ci_list:
ci_dict = dict()
ci_type = CITypeSpecCache.get(type_id)
ci_dict["_id"] = ci_id
ci_dict["_type"] = type_id
ci_dict["ci_type"] = ci_type.type_name
ci_dict["ci_type_alias"] = ci_type.type_alias
ci_list.add(ci_id)
res.append(ci_dict)
if ret_key == "name":
if is_multivalue:
if isinstance(ci_dict.get(attr_name), list):
ci_dict[attr_name].append(value)
else:
ci_dict[attr_name] = [value]
else:
ci_dict[attr_name] = value
elif ret_key == "alias":
if is_multivalue:
if isinstance(ci_dict.get(attr_alias), list):
ci_dict[attr_alias].append(value)
else:
ci_dict[attr_alias] = [value]
else:
ci_dict[attr_alias] = value
elif ret_key == "id":
if is_multivalue:
if isinstance(ci_dict.get(attr_id), list):
ci_dict[attr_id].append(value)
else:
ci_dict[attr_id] = [value]
else:
ci_dict[attr_id] = value
current_app.logger.debug("result parser time is: {0}".format(time.time() -
start))
return res
def _get_attr(self, key):
"""key is one of attr_id, attr_name and attr_alias
"""
attr = CIAttributeCache.get(key)
return attr
def add(self,
ci_type_name,
exist_policy="replace",
_no_attribute_policy="ignore",
**ci_dict):
ci_existed = False
ci_type = CITypeCache.get(ci_type_name) or \
abort(404, "CIType {0} is not existed".format(ci_type_name))
unique_key = CIAttributeCache.get(ci_type.uniq_id) \
or abort(500, 'illegality unique attribute')
unique = ci_dict.get(unique_key.attr_name) \
or abort(500, '{0} missing'.format(unique_key.attr_name))
old_ci = self.ci_is_exist(ci_type, unique_key, unique)
if old_ci is not None:
ci_existed = True
if exist_policy == 'reject':
return abort(500, 'CI is existed')
if old_ci.type_id != ci_type.type_id: # update ci_type
old_ci.type_id = ci_type.type_id
db.session.add(old_ci)
db.session.flush()
ci = old_ci
else:
if exist_policy == 'need':
return abort(404, 'CI {0} not exist'.format(unique))
ci = CI()
ci.type_id = ci_type.type_id
_uuid = uuid.uuid4().hex
ci.uuid = _uuid
ci.created_time = datetime.datetime.now()
db.session.add(ci)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
current_app.logger.error('add CI error: {0}'.format(str(e)))
return abort(500, 'add CI error')
value_manager = AttributeValueManager()
histories = list()
for p, v in ci_dict.items():
ret, res = value_manager.add_attr_value(
p,
v,
ci.ci_id,
ci_type,
_no_attribute_policy=_no_attribute_policy,
ci_existed=ci_existed)
if not ret:
db.session.rollback()
if not ci_existed:
self.delete(ci.ci_id)
current_app.logger.info(res)
return abort(500, res)
if res is not None:
histories.append(res)
try:
db.session.commit()
except Exception as e:
current_app.logger.error(str(e))
db.session.rollback()
if not ci_existed: # only add
self.delete(ci.ci_id)
return abort(500, "add CI error")
his_manager = CIAttributeHistoryManger()
his_manager.add(ci.ci_id, histories)
ci_cache.apply_async([ci.ci_id], queue="cmdb_async")
return ci.ci_id
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)