class EntryTag(BaseTable):
__tablename__ = 'entry_tag'
__export__ = {
const.ACL_READ: ['entry_id', 'tag_id', 'tag_class', 'data']
}
entry_id = db.Column(db.BigInteger, db.ForeignKey('entry.id'), primary_key=True)
tag_id = db.Column(db.BigInteger, db.ForeignKey('tag.id'), primary_key=True)
tag_class = db.Column(db.String(255))
data = db.Column(sa_utils.JSONType())
tag = relationship("Tag", backref="entry_assocs")
__table_args__ = (
db.UniqueConstraint("entry_id", "tag_id"),
)
@classmethod
def update_tags(cls_, entry, utags):
cls_.delete({'entry_id': entry.id})
for tag_class, tags in utags.items():
for tag in tags:
if isinstance(tag, int):
el = cls_(entry_id=entry.id, tag_id=tag, tag_class=tag_class)
el.save()
else:
el = cls_(entry_id=entry.id, tag_id=tag['id'], tag_class=tag_class, data=tag)
el.save()
class Action(ObjectTable):
__tablename__ = 'action'
__export__ = {
const.ACL_READ: [
'id', 'action_type', 'user_id', 'object_id', 'domain_id', 'data',
'created_at'
]
}
action_type = db.Column(db.Integer)
user_id = db.Column(db.BigInteger, db.ForeignKey('user.id'))
object_id = db.Column(db.BigInteger)
domain_id = db.Column(db.BigInteger, db.ForeignKey('domain.id'))
data = db.Column(sa_utils.JSONType())
@classmethod
def mark(cls_, current_user, action_type, data, domain_id=None):
r = {
'user_id': current_user.id,
'action_type': action_types[action_type],
'data': data
}
if domain_id is not None:
r['domain_id'] = domain_id
action = cls_(**r)
action.save()
return action
@classmethod
def af_find(cls_, current_user, data):
q = ActionQuery(current_user)
q.assign_request(data)
return q.execute()
class DomainUser(ObjectTable):
__tablename__ = 'domain_user'
user_id = db.Column(db.BigInteger,
db.ForeignKey('user.id'),
index=True,
nullable=False)
domain_id = db.Column(db.BigInteger,
db.ForeignKey('domain.id'),
index=True,
nullable=True)
@classmethod
def add_connection(cls_, user_id, domain_id):
domain_user = cls_(user_id=user_id, domain_id=domain_id)
domain_user.save()
return domain_user
class DomainTagclass(ObjectTable):
__tablename__ = 'domain_tagclass'
domain_id = db.Column(db.BigInteger,
db.ForeignKey('domain.id'),
index=True,
nullable=True)
tag_class = db.Column(db.String(255), index=True, nullable=False)
state = db.Column(sa_utils.JSONType())
@classmethod
def af_domain_state(cls_, current_user, domain_id):
rsp = {'exclude_tag_classes': []}
rows = cls_.find(domain_id=domain_id).all()
for row in rows:
if 'exclude_display' in row.state and row.state[
'exclude_display'] is True:
rsp['exclude_tag_classes'].append(row.tag_class)
return rsp
@classmethod
def af_update_tag_class(cls_, current_user, tag_class, data):
cls_.delete({'tag_class': tag_class})
rows = {}
if 'exclude_domain_ids' in data:
for domain_id in data['exclude_domain_ids']:
rows[domain_id] = {'exclude_display': True}
for domain_id, state in rows.items():
udate = {
'domain_id': domain_id,
'tag_class': tag_class,
'state': state
}
el = cls_(**udate)
el.save()
return cls_.af_get_tag_class_states(current_user, tag_class)
@classmethod
def af_get_tag_class_states(cls_, current_user, tag_class):
rsp = {'exclude_domain_ids': []}
rows = cls_.find(tag_class=tag_class).all()
for row in rows:
if 'exclude_display' in row.state and row.state[
'exclude_display'] is True:
rsp['exclude_domain_ids'].append(row.domain_id)
return rsp
class Entry(ObjectTable):
__tablename__ = 'entry'
__export__ = {
const.ACL_READ: ['id', 'user_id', 'domain_id', 'lead_id', 'status', 'name', 'country_code', 'created_at', 'excerpt', 'severity', 'reliability', 'status_ord', 'timeline', 'information_at']
}
status = db.Column(db.SmallInteger, default=const.STATUS_ACTIVE)
user_id = db.Column(db.BigInteger, db.ForeignKey('user.id'), index=True)
lead_id = db.Column(db.BigInteger, db.ForeignKey('lead.id'), index=True)
domain_id = db.Column(db.BigInteger, db.ForeignKey('domain.id'), index=True)
name = db.Column(db.Text)
country_code = db.Column(db.String(3))
excerpt = db.Column(db.Text)
tags = relationship("EntryTag", backref="entry")
locations = relationship("EntryLocation", backref="entry")
severity = db.Column(db.Integer)
reliability = db.Column(db.Integer)
status_ord = db.Column(db.Integer)
timeline = db.Column(db.Integer)
information_at = db.Column(db.DateTime)
validate_schema = {
"#lead_id": "integer",
"#severity": "integer",
"#reliability": "integer",
"?status": validator.Enum([const.STATUS_ACTIVE, const.STATUS_INACTIVE, const.STATUS_DELETED]),
"?status_ord": validator.Tag(tag_class='status'),
"?timeline": validator.Tag(tag_class='timeline'),
"?country_code": validator.Enum([None] + [country.alpha2 for country in pycountry.countries]),
"#tags": {
'?sector': [validator.Tag(tag_class='sector')],
'?vulnerable': [validator.TagBlock(tag_class='vulnerable')],
'?affected': [validator.TagBlock(tag_class='affected')],
'?underlying': [validator.Tag(tag_class='underlying')],
'?crisis_driver': [validator.Tag(tag_class='crisis_driver')]
},
"#locations": [{
"+source": validator.Enum([const.LOCATION_SOURCE_GEONAME, const.LOCATION_SOURCE_GOOGLE_MAP_SHAPE, const.LOCATION_SOURCE_SELF]),
"+location_id": validator.AnyOf("string", "integer"),
"+asciiname": "string",
"?data": validator.AnyDict()
}],
"#excerpt": "string",
"?information_at": validator.Timestamp()
}
validate_save = validator.parser(copy.copy(validate_schema), flip_hash='+')
validate_update = validator.parser(copy.copy(validate_schema), flip_hash='?')
@classmethod
def af_save(cls_, current_user, data, obj_id=None):
from sidr import models
# cuz js is lame
if 'lead_id' in data:
data['lead_id'] = int(data['lead_id'])
if obj_id is not None:
action_type = 'EDIT_ENTRY'
data = cls_.validate_update.validate(data)
entry_data = {key: value for (key, value) in data.items() if key not in ['tags', 'locations']}
entry = cls_.get(obj_id, required=True)
entry.user_id = current_user.id
entry.update(**entry_data)
else:
action_type = 'ADD_ENTRY'
data = cls_.validate_save.validate(data)
lead = models.Lead.get(data['lead_id'], required=True)
data['user_id'] = current_user.id
data['lead_id'] = lead.id
data['domain_id'] = lead.domain_id
entry_data = {key: value for (key, value) in data.items() if key not in ['tags', 'locations']}
entry = cls_(**entry_data)
entry.save()
if 'locations' in data:
models.EntryLocation.update_locations(entry, data['locations'])
if 'tags' in data:
models.EntryTag.update_tags(entry, data['tags'])
models.Action.mark(current_user, action_type, entry.jsonify(acl=const.ACL_READ), domain_id=entry.domain_id)
return entry.jsonify_complete(acl=const.ACL_READ)
@classmethod
def af_find(cls_, current_user, data, rtype='json'):
q = EntryQuery(current_user, rtype=rtype)
q.assign_request(data)
return q.execute()
@classmethod
def get_overview(cls_, current_user, domain_id):
sql = 'SELECT SUM(IF(TO_DAYS(NOW()) - TO_DAYS( created_at ) <= 1, 1, 0)) AS entries_today, SUM(IF(status=1, 1,0)) AS entries_active, SUM(IF(status=2, 1,0)) AS entries_inactive '
for i in range(1, 7):
sql += ' ,SUM(IF(status=1 AND severity={0}, 1,0)) AS severity_{0}_active, SUM(IF(status=2 AND severity={0}, 1,0)) AS severity_{0}_inactive'.format(i)
sql += ' FROM entry WHERE domain_id={0}'.format(int(domain_id))
row = db.session.execute(sql).first()
if row is None or row['entries_today'] is None:
return {}
rsp = {
'today': int(row['entries_today']),
'active': int(row['entries_active']),
'inactive': int(row['entries_inactive']),
'severity': {}
}
for i in range(1, 7):
rsp['severity'][i] = {
'active': int(row['severity_%s_active' % i]),
'inactive': int(row['severity_%s_inactive' % i])
}
return rsp
def jsonify_complete(self, acl):
rsp = self.jsonify(acl=acl)
if self.tags is not None:
utags = {}
for tag in self.tags:
if tag.tag_class not in utags:
utags[tag.tag_class] = []
utag = {
'id': tag.tag_id
}
if isinstance(tag.data, dict):
utag.update(tag.data)
utags[tag.tag_class].append(utag)
rsp['tags'] = utags
if self.locations is not None:
rsp['locations'] = []
for location in self.locations:
rsp['locations'].append(location.jsonify(acl=const.ACL_READ))
if self.user is not None:
rsp['user'] = self.user.jsonify(acl=const.ACL_READ)
if self.lead is not None:
rsp['lead'] = self.lead.jsonify(acl=const.ACL_READ)
return rsp
class EntryLocation(BaseTable):
__tablename__ = 'entry_location'
__export__ = {
const.ACL_READ:
['location_id', 'source', 'asciiname', 'country_code', 'data']
}
entry_id = db.Column(db.BigInteger,
db.ForeignKey('entry.id'),
primary_key=True)
location_id = db.Column(db.String(255),
primary_key=True,
autoincrement=False)
source = db.Column(db.BigInteger, primary_key=True, autoincrement=False)
asciiname = db.Column(db.String(255))
data = db.Column(sa_utils.JSONType())
country_code = db.Column(db.String(3))
__table_args__ = (db.UniqueConstraint("entry_id", "location_id",
"source"), )
@classmethod
def update_locations(cls_, entry, locations):
cls_.delete({'entry_id': entry.id})
for data in locations:
udata = {
'location_id': data['location_id'],
'source': data['source'],
'asciiname': data['asciiname'],
'data': data['data'] if 'data' in data else None,
'country_code': entry.country_code,
'entry_id': entry.id
}
el = cls_(**udata)
el.save()
@classmethod
def get_overview(cls_, current_user, domain_id):
sql = 'SELECT location_id, data, source, entry_id, entry.severity, entry_location.asciiname, geoname.latitude, geoname.longitude FROM entry_location'
sql += ' INNER JOIN entry ON (entry.id=entry_location.entry_id)'
sql += ' LEFT JOIN geoname ON (geoname.id=location_id AND source=%s)' % const.LOCATION_SOURCE_GEONAME
sql += ' WHERE entry.status !=%s AND domain_id=%s' % (
const.STATUS_DELETED, int(domain_id))
rows = db.session.execute(sql)
rsp = []
for row in rows:
rsp.append({
'location_id':
row['location_id'],
'source':
row['source'],
'entry_id':
row['entry_id'],
'severity':
row['severity'],
'asciiname':
row['asciiname'],
'latitude':
row['latitude'],
'longitude':
row['longitude'],
'data':
json.loads(row['data']) if row['data'] is not None else None
})
return rsp
class Lead(ObjectTable):
__tablename__ = 'lead'
__export__ = {
const.ACL_READ: [
'id', 'user_id', 'assignee_id', 'domain_id', 'lead_type', 'status',
'name', 'data', 'confidentiality', 'created_at', 'published_at',
'binbags', 'source_id', 'content_format_id', 'website', 'url'
]
}
user_id = db.Column(db.BigInteger, db.ForeignKey('user.id'), index=True)
assignee_id = db.Column(db.BigInteger)
domain_id = db.Column(db.BigInteger,
db.ForeignKey('domain.id'),
index=True)
source_id = db.Column(db.BigInteger, db.ForeignKey('tag.id'), index=True)
content_format_id = db.Column(db.BigInteger,
db.ForeignKey('tag.id'),
index=True)
lead_type = db.Column(db.String(255))
binbags = db.Column(sa_utils.JSONType())
status = db.Column(db.SmallInteger, default=const.STATUS_PENDING)
name = db.Column(db.String(255))
description = db.Column(db.Text)
website = db.Column(db.String(255))
confidentiality = db.Column(db.SmallInteger,
default=const.CONFIDENTIALITY_UNPROTECTED)
url = db.Column(db.Text)
published_at = db.Column(db.DateTime)
entries = db.relationship("Entry", backref="lead")
validate_schema = {
"?name":
"string",
"?status":
validator.Enum([
const.STATUS_ACTIVE, const.STATUS_INACTIVE, const.STATUS_PENDING,
const.STATUS_DELETED
]),
"#domain_id":
"integer",
"?confidentiality":
"integer",
"#lead_type":
validator.Enum(lead_type_dict.keys()),
"#source_id":
validator.Tag(tag_class='source'),
"?content_format_id":
validator.Tag(tag_class='content_format'),
"?description":
"string",
"?binbags": [{
"mime": "string",
"name": "string",
"reference": "string",
"id": "integer"
}],
"?published_at":
validator.Timestamp()
}
validate_save = validator.parser(copy.copy(validate_schema),
flip_hash='+',
additional_properties=True)
validate_update = validator.parser(copy.copy(validate_schema),
flip_hash='?',
additional_properties=True)
@classmethod
def af_save(cls_, current_user, data, obj_id=None):
from sidr import models
if obj_id is not None:
action_type = 'EDIT_LEAD'
data = cls_.validate_update.validate(data)
lead = cls_.get(obj_id, required=True)
if lead.lead_type is not None:
data = lead_type_dict[lead.lead_type].validate_update.validate(
data)
lead.user_id = current_user.id
lead.update(**data)
else:
action_type = 'ADD_LEAD'
data = cls_.validate_save.validate(data)
data = lead_type_dict[data['lead_type']].validate_save.validate(
data)
data['user_id'] = current_user.id
lead = cls_(**data)
lead.save()
models.Action.mark(current_user,
action_type,
lead.jsonify(acl=const.ACL_READ),
domain_id=lead.domain_id)
return lead.jsonify(acl=const.ACL_READ)
@classmethod
def af_find(cls_, current_user, data, rtype='json'):
q = LeadQuery(current_user, rtype=rtype)
q.assign_request(data)
return q.execute()
@classmethod
def get_overview(cls_, current_user, domain_id):
sql = 'SELECT SUM(IF(TO_DAYS(NOW()) - TO_DAYS( created_at ) <= 1, 1, 0)) AS leads_today, SUM(IF(status=1, 1,0)) AS leads_active, SUM(IF(status=3, 1,0)) AS leads_pending from lead'
sql += ' WHERE domain_id={0}'.format(int(domain_id))
row = db.session.execute(sql).first()
if row is None or row['leads_today'] is None:
return {}
return {
'today': int(row['leads_today']),
'active': int(row['leads_active']),
'pending': int(row['leads_pending']),
}
class Tag(ObjectTable):
__tablename__ = 'tag'
__export__ = {
const.ACL_READ:
['id', 'tag_class', 'name', 'data', 'restricted_domains', 'tree']
}
tag_class = db.Column(db.String(255))
status = db.Column(db.SmallInteger, default=const.STATUS_ACTIVE)
name = db.Column(db.String(255))
data = db.Column(sa_utils.JSONType())
restrict_domains = db.Column(sa_utils.ScalarListType())
parent_id = db.Column(db.BigInteger, db.ForeignKey('tag.id'))
tree = db.Column(sa_utils.JSONType())
validate_save = validator.parser(
{
"+tag_class": validator.Enum(tag_class_dict.keys()),
"?parent_id": validator.Integer()
},
additional_properties=True)
@classmethod
def af_tag_classes(cls_):
rsp = []
for name, obj in tag_class_dict.items():
rsp.append({'name': name, 'metadata': obj.jsonify_metadata()})
return {'result': rsp, 'total': len(rsp)}
@classmethod
def af_delete(cls_, obj_id):
tag = cls_.get(obj_id, required=True)
tag.update(**{'status': const.STATUS_DELETED})
return tag.jsonify(acl=const.ACL_READ)
@classmethod
def af_save(cls_, current_user, data, obj_id=None):
data = cls_.validate_save.validate(data)
data = tag_class_dict[data['tag_class']].validate(data, obj_id)
if 'parent_id' in data:
parents = cls_.get_parenthood(data['parent_id'])
tree = []
for parent in parents:
tree.append({
'id': parent.id,
'name': parent.name,
'title': parent.data['title']
})
data['tree'] = tree
if obj_id is not None:
tag = cls_.get(obj_id, required=True)
tag.update(**data)
return tag.jsonify(acl=const.ACL_READ)
else:
tag = cls_(**data)
tag.save()
return tag.jsonify(acl=const.ACL_READ)
@classmethod
def af_find(cls_, current_user, data):
q = TagQuery(current_user)
q.assign_request(data)
return q.execute()
@classmethod
def get_parenthood(cls_, obj_id, parents=None):
if parents is None:
parents = []
if obj_id == 0 or obj_id is None:
return parents
obj = cls_.get(obj_id)
parents.append(obj)
return cls_.get_parenthood(obj.parent_id, parents=parents)
@classmethod
def get_id_map(cls_):
umap = {}
rows = cls_.find().all()
for row in rows:
umap[row.id] = row.name
return umap
def jsonify(self, acl=None):
return tag_class_dict[self.tag_class].jsonify_tag(self)