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 saveAll(cls, dataList):
for d in dataList:
cck = cls(**d)
db_session.add(cck)
db_session.commit()
db_session.close()
def detail_article(article_id):
blog = Blog.query.get(article_id)
db_session.query(Blog).filter(Blog.id==article_id).\
update({Blog.count_scan: Blog.count_scan+1})
db_session.commit()
category = get_blog_category()
return render_template('articledetail.html',
article=blog,
categorys=category)
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 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 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 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 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 delete_cve(cve_id):
cve = db_session.query(CVE).filter(CVE.id == cve_id.upper()).one_or_none()
if not cve:
return make_response(
jsonify({"message": f"CVE {cve_id} doesn't exist"}),
404,
)
db_session.delete(cve)
db_session.commit()
return make_response(
jsonify({"message": f"CVE with id '{cve_id}' was deleted"}), 200)
def delete_notice(notice_id):
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,
)
db_session.delete(notice)
db_session.commit()
return make_response(jsonify({"message": f"Notice {notice_id} deleted"}),
200)
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 merge_file():
upload_id = request.args.get('uploadId', None)
if not upload_id:
return jsonify(status='invalid', reason='uploadId not present')
has_access = False
try:
has_access = can_access_file(upload_id)
if has_access:
upload_log = db_session.query(Upload).get(upload_id)
logger.info('Retrieved upload log, merging raw table to master')
raw_table_name = 'raw_{}'.format(upload_id)
logger.info('Merging raw table to master')
merge_id = upsert_raw_table_to_master(raw_table_name,
upload_log.jurisdiction_slug,
upload_log.event_type_slug,
upload_id, db_session)
logger.info('Syncing merged file to s3')
bootstrap_master_tables(upload_log.jurisdiction_slug, db_session)
sync_merged_file_to_storage(upload_log.jurisdiction_slug,
upload_log.event_type_slug,
db_session.get_bind())
merge_log = db_session.query(MergeLog).get(merge_id)
try:
logger.info('Merge succeeded. Now querying matcher')
notify_matcher(upload_log.jurisdiction_slug, upload_id)
except Exception as e:
logger.error('Error matching: ', e)
db_session.rollback()
return make_response(jsonify(status='error'), 500)
db_session.commit()
return jsonify(status='success',
new_unique_rows=merge_log.new_unique_rows,
total_unique_rows=merge_log.total_unique_rows)
else:
return jsonify(status='not authorized')
except ValueError as e:
logger.error('Error merging: ', e)
db_session.rollback()
return make_response(jsonify(status='error'), 500)
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 delete_release(release_codename):
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,
)
if len(release.statuses) > 0:
return (
jsonify({
"message": (f"Cannot delete '{release_codename}' release. "
f"Release already in use")
}),
400,
)
db_session.delete(release)
db_session.commit()
return make_response(
jsonify({"message": f"Release {release_codename} deleted"}), 200)
def test_download_file(self):
with full_rig_with_s3() as (app, engine):
# Create matched jail_bookings
table_name = 'jail_bookings'
create_and_populate_master_table(table_name, engine,
MATCHED_BOOKING_FILE)
# Create matched hmis_service_stays
table_name = 'hmis_service_stays'
create_and_populate_master_table(table_name, engine,
MATCHED_HMIS_FILE)
db_session.commit()
response = app.get(
'/api/chart/download_source?jurisdiction=boone&eventType=jail_bookings'
)
assert response.status_code == 200
assert response.headers[
"Content-Disposition"] == "attachment; filename=jail_bookings.csv"
assert response.headers["Content-type"] == "text/csv"
data = response.get_data()
reader = csv.reader(BytesIO(data))
with open(MATCHED_BOOKING_FILE, 'rb') as source_file:
source_reader = csv.reader(source_file)
for returned_row, expected_row in zip(reader, source_reader):
assert returned_row == expected_row
def get_validated_result(job_key):
job = Job.fetch(job_key, connection=get_redis_connection())
if job.is_failed:
logger.error(job.exc_info)
return jsonify(
format_error_report(
'System error. The error has been logged, please try again later'
))
if not job.is_finished:
return jsonify({
'validation': {
'jobKey': job_key,
'status': 'validating',
'message': 'Still validating data!'
},
'upload_result': {
'status': 'not yet',
}
})
result = job.result
if 'validation' in result and 'upload_result' in result:
return jsonify(result)
validation_report = result['validation_report']
event_type = result['event_type']
filename_with_all_fields = result['filename_with_all_fields']
if validation_report['valid']:
upload_id = job.meta['upload_id']
row_count = validation_report['tables'][0]['row-count'] - 1
sample_rows, field_names = get_sample(filename_with_all_fields)
db_session.commit()
return jsonify({
'validation': {
'status': 'valid',
'jobKey': job_key,
},
'upload_result': {
'status': 'done',
'rowCount': row_count,
'exampleRows': sample_rows,
'fieldOrder': field_names,
'uploadId': upload_id
}
})
else:
db_session.commit()
return jsonify({
'validation': {
'jobKey': job_key,
'status': 'invalid'
},
'upload_result': {
'status':
'done',
'rowCount':
'',
'fieldOrder': [],
'errorReport':
format_validation_report(validation_report, event_type),
'upload_id':
''
}
})
def validate_async(uploaded_file_name, jurisdiction, full_filename, event_type,
flask_user_id, upload_id):
validate_start_time = datetime.today()
sync_upload_metadata_partial = partial(sync_upload_metadata,
upload_id=upload_id,
event_type=event_type,
jurisdiction=jurisdiction,
flask_user_id=flask_user_id,
given_filename=uploaded_file_name,
local_filename=full_filename,
db_session=db_session)
try:
# 1. validate header
validate_header(event_type, full_filename)
# 2. preprocess file
filename_with_all_fields = add_missing_fields(event_type,
full_filename)
# 3. validate body
body_validation_report = two_pass_validation(event_type,
filename_with_all_fields)
validate_complete_time = datetime.today()
if not body_validation_report['valid']:
sync_upload_metadata_partial(
validate_start_time=validate_start_time,
validate_complete_time=validate_complete_time,
validate_status=False,
)
return {
'validation_report': body_validation_report,
'event_type': event_type,
'jurisdiction': jurisdiction,
'filename_with_all_fields': filename_with_all_fields,
'uploaded_file_name': uploaded_file_name,
'full_filename': full_filename
}
try:
# 4. upload to s3
upload_start_time = datetime.today()
final_upload_path = upload_path(jurisdiction, event_type,
upload_id)
upload_to_storage(final_upload_path, filename_with_all_fields)
# 5. load into raw table
copy_raw_table_to_db(final_upload_path, event_type, upload_id,
db_session.get_bind())
upload_complete_time = datetime.today()
# 6. sync upload metadata to db
sync_upload_metadata_partial(
s3_upload_path=final_upload_path,
validate_start_time=validate_start_time,
validate_complete_time=validate_complete_time,
validate_status=True,
upload_start_time=upload_start_time,
upload_complete_time=upload_complete_time,
upload_status=True)
except ValueError as e:
sync_upload_metadata_partial(
validate_start_time=validate_start_time,
validate_complete_time=validate_complete_time,
validate_status=False,
upload_start_time=upload_start_time,
upload_status=False,
)
body_validation_report = {
'valid':
False,
'tables': [{
'headers': [],
'errors': [{
'column-number': None,
'row-number': None,
'message': str(e)
}]
}]
}
db_session.commit()
return {
'validation_report': body_validation_report,
'event_type': event_type,
'jurisdiction': jurisdiction,
'filename_with_all_fields': filename_with_all_fields,
'uploaded_file_name': uploaded_file_name,
'full_filename': full_filename
}
except ValueError as e:
sync_upload_metadata_partial(validate_start_time=validate_start_time,
validate_status=False)
db_session.commit()
return format_error_report(str(e))
def saveOne(self):
db_session.add(self)
db_session.commit()
db_session.close()
def delete(self):
db_session.delete(self)
db_session.commit()
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 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,
)
