def saveAll(cls, dataList):
for d in dataList:
cck = cls(**d)
db_session.add(cck)
db_session.commit()
db_session.close()
def update_release(release_codename, **kwargs):
release = (db_session.query(Release).filter(
Release.codename == release_codename).one_or_none())
if not release:
return make_response(
jsonify({"message": f"Release {release_codename} doesn't exist"}),
404,
)
release_data = request.json
release.version = release_data["version"]
release.name = release_data["name"]
release.development = release_data["development"]
release.lts = release_data["lts"]
release.release_date = release_data["release_date"]
release.esm_expires = release_data["esm_expires"]
release.support_expires = release_data["support_expires"]
db_session.add(release)
try:
db_session.commit()
except IntegrityError as error:
return make_response(
jsonify({
"message": "Failed updating release",
"error": error.orig.args[0],
}),
422,
)
return make_response(jsonify({"message": "Release updated"}), 200)
def create_notice(**kwargs):
notice_data = request.json
db_session.add(
_update_notice_object(Notice(id=notice_data["id"]), notice_data))
db_session.commit()
return make_response(jsonify({"message": "Notice created"}), 200)
def _update_statuses(cve, data, packages):
statuses = cve.packages
statuses_to_check = (db_session.query(Status).filter(
Status.cve_id == cve.id).all())
statuses_to_delete = {
f"{v.package_name}||{v.release_codename}": v
for v in statuses_to_check
}
for package_data in data.get("packages", []):
name = package_data["name"]
if packages.get(name) is None:
package = Package(name=name)
package.source = package_data["source"]
package.ubuntu = package_data["ubuntu"]
package.debian = package_data["debian"]
packages[name] = package
db_session.add(package)
for status_data in package_data["statuses"]:
update_status = False
codename = status_data["release_codename"]
status = statuses[name].get(codename)
if status is None:
update_status = True
status = Status(cve_id=cve.id,
package_name=name,
release_codename=codename)
elif f"{name}||{codename}" in statuses_to_delete:
del statuses_to_delete[f"{name}||{codename}"]
if status.status != status_data["status"]:
update_status = True
status.status = status_data["status"]
if status.description != status_data["description"]:
update_status = True
status.description = status_data["description"]
if status.component != status_data.get("component"):
update_status = True
status.component = status_data.get("component")
if status.pocket != status_data.get("pocket"):
update_status = True
status.pocket = status_data.get("pocket")
if update_status:
statuses[name][codename] = status
db_session.add(status)
for key in statuses_to_delete:
db_session.delete(statuses_to_delete[key])
def insert_article(userid, title, content, type, author, img):
category_id = Blog_category.query.filter(
Blog_category.category_name == type).first().id
blog = Blog(title, content, category_id)
blog.author = author
blog.img = img
blog.authorid = userid
db_session.add(blog)
db_session.commit()
def write_match_log(db_session, match_job_id, upload_id, match_start_at,
match_complete_at, match_status, match_runtime):
db_object = MatchLog(id=match_job_id,
upload_id=upload_id,
match_start_timestamp=match_start_at,
match_complete_timestamp=match_complete_at,
match_status=match_status,
runtime=match_runtime)
db_session.add(db_object)
db_session.commit()
def new_user():
username = request.json.get('username')
password = request.json.get('password')
if username is None or password is None:
return jsonify({'message': '1st'}), 400
result = Agents.query.filter_by(username=username).first()
if Agents.query.filter_by(username=username).first() is not None:
return jsonify({'message': 'username already being used'}), 400
agent = Agents(username=username)
agent.hash_password(password)
session.add(agent)
session.commit()
return jsonify({'username': username})
def register():
form = RegisterForm(request.form)
if form.validate_on_submit():
u = User(form.name.data, form.email.data, generate_password_hash(form.password.data))
db_session.add(u)
db_session.commit()
return redirect(url_for('auth.signin'))
return render_template("auth/register.html", form=form)
def register():
if request.method == 'POST':
username = request.json.get('username')
password = request.json.get('password')
hostname = request.json.get('hostname')
windows_user = request.json.get('windows_user')
if session.query(Agents).filter_by(username=username).first():
return jsonify({'error': 'username already taken'}), 400
new_agent = Agents(username=username,
hostname=hostname,
windows_user=windows_user)
new_agent.hash_password(password)
session.add(new_agent)
session.commit()
return jsonify({'username': username})
def update_notice(notice_id, **kwargs):
notice = (db_session.query(Notice).filter(
Notice.id == notice_id).one_or_none())
if not notice:
return make_response(
jsonify({"message": f"Notice '{notice_id}' doesn't exist"}),
404,
)
notice = _update_notice_object(notice, request.json)
db_session.add(notice)
db_session.commit()
return make_response(jsonify({"message": "Notice updated"}), 200)
def create_release(**kwargs):
release_data = request.json
release = Release(
codename=release_data["codename"],
version=release_data["version"],
name=release_data["name"],
development=release_data["development"],
lts=release_data["lts"],
release_date=release_data["release_date"],
esm_expires=release_data["esm_expires"],
support_expires=release_data["support_expires"],
)
db_session.add(release)
db_session.commit()
return make_response(jsonify({"message": "Release created"}), 200)
def upsert_raw_table_to_master(raw_table_name, jurisdiction, event_type,
upload_id, db_session):
create_merged_table(jurisdiction, event_type, db_session)
master_table_name = generate_master_table_name(jurisdiction, event_type)
goodtables_schema = load_schema_file(event_type)
base_column_list = column_list_from_goodtables_schema(goodtables_schema)
# use new postgres 'on conflict' functionality to upsert
update_statements = [
' "{column}" = EXCLUDED."{column}"'.format(column=column_def[0])
for column_def in base_column_list
]
start_ts = datetime.today()
insert_sql = '''
insert into {master}
select raw.*, '{new_ts}' inserted_ts, '{new_ts}' updated_ts, row_number() over ()::text || '{event_type}' as matched_id
from "{raw}" as raw
on conflict ({primary_key})
do update set {update_string}, updated_ts = '{new_ts}'
'''.format(raw=raw_table_name,
master=master_table_name,
event_type=event_type,
primary_key=', '.join([
"\"{}\"".format(col)
for col in goodtables_schema['primaryKey']
]),
update_string=', '.join(update_statements),
new_ts=start_ts.isoformat())
logging.info('Executing insert: %s', insert_sql)
db_session.execute(insert_sql)
end_ts = datetime.today()
merge_log = MergeLog(
upload_id=upload_id,
total_unique_rows=total_unique_rows(raw_table_name,
goodtables_schema['primaryKey'],
db_session),
new_unique_rows=new_unique_rows(master_table_name, start_ts,
db_session),
merge_start_timestamp=start_ts,
merge_complete_timestamp=end_ts,
)
db_session.add(merge_log)
db_session.execute('drop table "{}"'.format(raw_table_name))
db_session.commit()
return merge_log.id
def write_upload_log(db_session, upload_id, jurisdiction_slug, event_type_slug,
user_id, given_filename, upload_start_time,
upload_complete_time, upload_status, validate_start_time,
validate_complete_time, validate_status, num_rows,
file_size, file_hash, s3_upload_path):
db_object = Upload(id=upload_id,
jurisdiction_slug=jurisdiction_slug,
event_type_slug=event_type_slug,
user_id=user_id,
given_filename=given_filename,
upload_start_time=upload_start_time,
upload_complete_time=upload_complete_time,
upload_status=upload_status,
validate_start_time=validate_start_time,
validate_complete_time=validate_complete_time,
validate_status=validate_status,
num_rows=num_rows,
file_size=file_size,
file_hash=file_hash,
s3_upload_path=s3_upload_path)
db_session.add(db_object)
db_session.commit()
def bulk_upsert_cve(*args, **kwargs):
cves_data = request.json
if len(cves_data) > 50:
return make_response(
jsonify({
"message": ("Please only submit up to 50 CVEs at a time. "
f"({len(cves_data)} submitted)")
}),
413,
)
packages = {}
for package in db_session.query(Package).all():
packages[package.name] = package
for data in cves_data:
update_cve = False
cve = db_session.query(CVE).get(data["id"].upper())
if cve is None:
update_cve = True
cve = CVE(id=data["id"])
if cve.status != data.get("status"):
update_cve = True
cve.status = data.get("status")
published_date = (cve.published.strftime("%Y-%B-%d")
if cve.published else None)
data_published_date = (data.get("published").strftime("%Y-%B-%d")
if data.get("published") else None)
if published_date != data_published_date:
update_cve = True
cve.published = data.get("published")
if cve.priority != data.get("priority"):
update_cve = True
cve.priority = data.get("priority")
if cve.cvss3 != data.get("cvss3"):
update_cve = True
cve.cvss3 = data.get("cvss3")
if cve.description != data.get("description"):
update_cve = True
cve.description = data.get("description")
if cve.ubuntu_description != data.get("ubuntu_description"):
update_cve = True
cve.ubuntu_description = data.get("ubuntu_description")
if cve.notes != data.get("notes"):
update_cve = True
cve.notes = data.get("notes")
if cve.references != data.get("references"):
update_cve = True
cve.references = data.get("references")
if cve.bugs != data.get("bugs"):
update_cve = True
cve.bugs = data.get("bugs")
if cve.patches != data.get("patches"):
update_cve = True
cve.patches = data.get("patches")
if cve.tags != data.get("tags"):
update_cve = True
cve.tags = data.get("tags")
if cve.mitigation != data.get("mitigation"):
update_cve = True
cve.mitigation = data.get("mitigation")
if update_cve:
db_session.add(cve)
_update_statuses(cve, data, packages)
created = defaultdict(lambda: 0)
updated = defaultdict(lambda: 0)
deleted = defaultdict(lambda: 0)
for item in db_session.new:
created[type(item).__name__] += 1
for item in db_session.dirty:
updated[type(item).__name__] += 1
for item in db_session.deleted:
deleted[type(item).__name__] += 1
try:
db_session.commit()
except DataError as error:
return make_response(
jsonify({
"message": "Failed bulk upserting session",
"error": error.orig.args[0],
}),
400,
)
return make_response(
jsonify({
"created": created,
"updated": updated,
"deleted": deleted
}),
200,
)
def save(self):
db_session.add(self)
db_session.commit()
def user_register(user):
db_session.add(user)
db_session.commit()
def insert_comment(userid, username, content, article_id):
com = Comment(userid, username, content, article_id)
db_session.add(com)
db_session.commit()
def saveOne(self):
db_session.add(self)
db_session.commit()
db_session.close()