def insert_one(self, record):
try:
db_session.add(record)
db_session.commit()
except Exception:
db_session.rollback()
raise
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 execute(self,args,diagnostics):
print("Setting kernel test data to 0")
diagnostic = Diagnostic()
diagnostic.data = 0
diagnostics["counter"] = diagnostic
range_arg = RangeArgument(range(0,15),0)
args["range"] = range_arg
db_session.add(diagnostic)
db_session.add(range_arg)
db_session.commit()
def insert_debug(msg):
try:
print(msg.topic + " " + str(msg.qos) + " " + str(msg.payload))
m_in = msg.payload.decode("UTF-8").split(";")
if isinstance(m_in, list) and len(m_in) == 3:
db_session.add(
SensorDebug(device_id=m_in[0], code=m_in[1], message=m_in[2]))
db_session.commit()
else:
print("Debug Message is not with the right format")
except Exception as e:
print(f"error trying to insert {m_in}")
def generate(email, password):
user = UserRepository().get_by(email, as_dict=False)
if not isinstance(user, User):
return user
if not user.valid_password(password):
return {"message": "invalid password"}, 403
valid_until = str(datetime.now() + timedelta(days=180))
user.auth_token = {"email": email, "valid_until": valid_until}
db_session.add(user)
db_session.commit()
return {"token": user.auth_token.decode()}
def user_register():
if request.method.__eq__('POST'):
data = request.get_json()
if data is None or data["email"] is None or data["password"] is None:
response = {
"status": Fail,
"timestamp": stamp(),
"reason": "data is missing!"
}
resp = make_response(response)
resp.status_code = 403
return resp
user_name = data['name']
user_email = data['email']
hashed_password = generate_password_hash(data['password'], method='sha256')
optional_user = Users.query.filter_by(email=user_email).first()
if optional_user is None:
new_user = Users(user_name, user_email, hashed_password)
new_user.token = new_user.encode_auth_token(new_user.id)
db_session.add(new_user)
db_session.commit()
session['logged_in'] = True
response = {
"status": Success,
"timestamp": stamp()
}
resp = make_response(response)
resp.headers['Authorization'] = new_user.token
resp.status_code = 201
return resp
else:
response = {
"status": Fail,
"timestamp": stamp(),
"reason": "user already exists!"
}
resp = make_response(response)
resp.status_code = 406
return resp
response = {
"status": Fail,
"timestamp": stamp(),
"reason": "unable to process request!"
}
resp = make_response(response)
resp.status_code = 405
return resp
def user_login():
if request.method.__eq__('POST'):
data = request.get_json()
if data is None or len(data) == 0:
response = {
"status": Fail,
"timestamp": stamp(),
"reason": "data is missing!"
}
resp = make_response(response)
resp.status_code = 403
return resp
user_email = data['email']
user_password = data['password']
optional_user = Users.query.filter_by(email=user_email).first()
if optional_user and check_password_hash(optional_user.password, user_password):
session['logged_in'] = True
auth_token = optional_user.encode_auth_token(optional_user.id)
optional_user.token = auth_token
assert optional_user.token == auth_token
db_session.add(optional_user)
db_session.commit()
response = {
"status": Success,
"timestamp": stamp()
}
resp = make_response(response)
resp.headers['Authorization'] = optional_user.token
resp.status_code = 200
return resp
else:
response = {
"status": Fail,
"timestamp": stamp(),
"reason": "unauthorized! email / password is not correct"
}
resp = make_response(response)
resp.status_code = 401
return resp
response = {
"status": Fail,
"timestamp": stamp(),
"reason": "Unable to verify WWW-Authenticate: Basic realm 'login realm'"
}
resp = make_response(response)
resp.status_code = 405
return resp
def insert(self, email, password):
user = User.query.filter(User.email == email)
if user.count():
return {"message": "email or passowrd incorrect"}, 400
try:
user = User()
user.password = password.encode()
user.email = email
db_session.add(user)
db_session.commit()
except ValueError as e:
db_session.rollback()
return {"message": f"{str(e)}"}, 400
return {
"message": f"user with email {email} successfully created"
}, 200
def insert_sensor_data(msg):
try:
print(msg.topic + " " + str(msg.qos) + " " + str(msg.payload))
m_in = json.loads(msg.payload)
for key, value in m_in["channels"].items():
db_session.add(
SensorData(
app_key=m_in["app_key"],
net_key=m_in["net_key"],
device_id=m_in["device_id"],
field_name=key,
value=value,
unit_string=m_in["unit"],
))
db_session.commit()
except Exception:
print(f"error trying to insert {m_in}")
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_duplicates(dryrun):
# batches = (db_session.query(Batch)\
# .filter(CiCommit.id == commit_id, Batch.label == label)\
# .all())
sql = "SELECT ci_commit_id, label, count(*) as qty FROM batches GROUP BY ci_commit_id, label HAVING count(*)> 1;"
result = db_session.execute(sql)
duplicates = list(result)
# for commit_id, label, count in duplicates:
# print(commit_id, label, count)
print(len(duplicates), "duplicates")
for commit_id, label, count in duplicates:
print(f"commit_id: {commit_id}, label={label}, count={count}")
# .filter(and_(CiCommit.id == commit_id, Batch.label == label))\
batches = (db_session.query(Batch)\
.join(CiCommit)\
.filter(Batch.label == label)\
.filter(CiCommit.id == commit_id)\
.all())
print(f" found {len(batches)}, expected: {count}")
for b in batches:
print(" ", b, b.label, b.ci_commit.id)
print(" ", b.ci_commit, b.ci_commit.project)
print('--')
assert len(batches) == count
final_batch, *other_batches = list(batches)
if not final_batch.data:
final_batch.data = {}
if not final_batch.data.get('commands'):
final_batch.data['commands'] = {}
print('BEFORE', final_batch.data['commands'])
for b in other_batches:
if b.data and b.data.get('commands'):
final_batch.data['commands'].update(b.data['commands'])
for o in b.outputs:
o.batch = final_batch
db_session.delete(b)
print('AFTER', final_batch.data['commands'])
if not dryrun:
db_session.add(final_batch)
db_session.commit()
def add_new_todo_task():
'''
:description:
- create a new task associated with user by token
:param:
- accepts JSON with
'taskDescription'
:return:
- JSON response
'''
if request.method == 'POST':
session_data = get_jwt_decode_data()
optional_user = Users.query.filter_by(id=session_data['sub']).first()
if optional_user is None:
response = {
"status": __STATUS_FAIL,
"timestamp": stamp()
}
resp = make_response(response)
resp.status_code = 404
return resp
payload = request.get_json()
task_description = payload['taskDescription']
new_todo = Todos(user_id=optional_user.id, todo_description=task_description)
db_session.add(new_todo)
db_session.commit()
response = {
'timestamp': stamp(),
'status': __STATUS_SUCCESS,
'data': {
'taskId': new_todo.id,
'taskCreationTime': new_todo.time_created
}
}
resp = make_response(response)
resp.status_code = 201
return resp
response = {
'timestamp': stamp(),
'status': __STATUS_FAIL
}
resp = make_response(response)
resp.status_code = 403
return resp
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 insert_data(n_users=10, n_sticky=5, n_events=10):
fake = Faker()
users = set([fake.name() for _ in range(n_users)])
print(len(users))
for user in users:
u = User(username=user, password='******')
for i in range(n_sticky):
sticky = ReminderSticky(reminder=fake.text())
u.reminders_sticky.append(sticky)
for i in range(n_events):
event = ReminderEvents(reminder=fake.text(),
time_of_reminder=generate_random_datetime(),
how_often=generate_how_often())
u.reminders_events.append(event)
db_session.add(u)
try:
db_session.commit()
except Exception as e:
print(str(e))
def heartbeat():
data = get_jwt_decode_data()
exp = data["exp"]
now = datetime.datetime.utcnow() - datetime.timedelta(days=0, seconds=600)
now = int(now.timestamp().__str__()[:10])
state = exp - now
if state <= 0:
session['log_in'] = False
resp = make_response({'message': 'Invalid token'})
resp.status_code = 403
return resp
user_obj = Users.query.filter_by(id=data["sub"]).first()
new_token = user_obj.encode_auth_token(user_obj.id)
user_obj.token = new_token
db_session.add(user_obj)
db_session.commit()
response = {
"status": Success,
"timestamp": stamp()
}
resp = make_response(response)
resp.status_code = 200
return resp
def add_new_todo_task():
if request.method.__eq__('POST'):
session_data = get_jwt_decode_data()
optional_user = Users.query.filter_by(id=session_data['sub']).first()
if optional_user is None:
response = {
"status": Fail,
"timestamp": stamp(),
"reason": "no such user"
}
resp = make_response(response)
resp.status_code = 404
return resp
payload = request.get_json()
task_description = payload['taskDescription']
new_todo = Todos(user_id=optional_user.id)
new_todo.task_description = task_description
db_session.add(new_todo)
db_session.commit()
response = {
'timestamp': stamp(),
'status': Success,
'data': {
'taskId': new_todo.id,
'taskCreationTime': new_todo.time_created
}
}
resp = make_response(response)
resp.status_code = 201
return resp
response = {
'timestamp': stamp(),
'status': Fail
}
resp = make_response(response)
resp.status_code = 403
return resp
def add_reminder_to_log(id):
rl = ReminderLog(reminder=id, executed_at=datetime.now())
db_session.add(rl)
db_session.commit()
# .yield_per(batch_size)
)
print("Starting updates")
most_recent = datetime.datetime.now() - datetime.timedelta(days=7)
for idx, output in enumerate(query(most_recent)):
if output.is_pending:
continue
if output.data.get('storage'):
continue
manifest_path = output.output_dir / 'manifest.outputs.json'
if not manifest_path.exists():
if not output.output_dir.exists():
output.data['storage'] = 0
db_session.add(output)
flag_modified(output, "data")
continue
else:
manifest = save_outputs_manifest(output.output_dir)
else:
with manifest_path.open() as f:
manifest = json.load(f)
output.data['storage'] = total_storage(manifest)
flag_modified(output, "data")
db_session.add(output)
if idx % batch_size == 0:
print(f"{idx/output_total:.1%}", output, f"{output.data['storage']/1024/1024:.01f} MB")
def create_new_user():
'''
:description:
- user sign in , creation of new user
:generate:
- new user object in todo-tasks db
:param:
- accepts JSON with
'name', 'email', 'password'
:returns:
- JSON response
'''
recall = request
if recall.method.__eq__('POST'):
data = recall.get_json()
if data is None or data["email"] is None or data["password"] is None:
e = Exception('data is missing!')
response = {
"status": STATUS_FAILED,
"timestamp": stamp(),
"reason": e.__str__()
}
resp = make_response(response)
resp.status_code = 403
return resp
user_name = data['name']
user_email = data['email']
hashed_password = generate_password_hash(data['password'], method='sha256')
optional_user = Users.query.filter_by(email=user_email).first()
if optional_user is None:
new_user = Users(user_name, user_email, hashed_password)
new_user.token = new_user.encode_auth_token(new_user.id)
db_session.add(new_user)
db_session.commit()
session['logged_in'] = True
response = {
"status": __STATUS_SUCCESS,
"timestamp": stamp()
}
resp = make_response(response)
resp.headers['Authorization'] = new_user.token.decode()
resp.status_code = 201
return resp
else:
e = Exception("User already exists!")
response = {
"status": __STATUS_FAIL,
"timestamp": stamp(),
"reason": e.__str__()
}
resp = make_response(response)
resp.status_code = 406
return resp
e = Exception("Unable to process request!")
response = {
"status": __STATUS_FAIL,
"timestamp": stamp(),
"reason": e.__str__()
}
resp = make_response(response)
resp.status_code = 405
return resp
def user_login():
'''
:description:
- make a login with existing user
:param:
- accepts JSON with
'email', 'password'
:return:
- JSON response
'''
recall = request
if recall.method.__eq__('POST'):
data = recall.get_json()
if data is None or len(data) == 0:
e = Exception('data is missing!')
response = {
"status": __STATUS_FAIL,
"timestamp": stamp(),
"reason": e.__str__()
}
resp = make_response(response)
resp.status_code = 403
return resp
user_email = data['email']
user_password = data['password']
optional_user = Users.query.filter_by(email=user_email).first()
if optional_user is not None and check_password_hash(optional_user.password, user_password):
session['logged_in'] = True
auth_token = optional_user.encode_auth_token(optional_user.id)
optional_user.token = auth_token
assert optional_user.token == auth_token
db_session.add(optional_user)
db_session.commit()
response = {
"status": __STATUS_SUCCESS,
"timestamp": stamp(),
"message": "Login Successfully!",
}
resp = make_response(response)
resp.headers['Authorization'] = optional_user.token.decode()
resp.status_code = 200
return resp
else:
e = Exception("Unauthorized! email / password is not correct.")
response = {
"status": __STATUS_FAIL,
"timestamp": stamp(),
"reason": e.__str__()
}
resp = make_response(response)
resp.status_code = 401
return resp
e = Exception("Unable to verify WWW-Authenticate: Basic realm 'login realm'")
response = {
"status": __STATUS_FAIL,
"timestamp": stamp(),
"reason": e.__str__()
}
resp = make_response(response)
resp.status_code = 405
return resp
