def send_player_ranking(ranking_data, block_name, titles):
session = Session()
players = session.query(Player).all()
team = {}
for player in players:
name = player.name
if player.nickname is not None:
name = player.nickname
team[player.id] = {'name': name}
admin = request.args.get('admin', '') == 'True'
rank_manager = RankingManager()
table = rank_manager.ranking_table(ranking_data, True)
for id in team:
player = team[id]
player['link'] = '/players/profile/%s' % id
return render_template('players/index.html',
admin=admin,
rank_table=table,
teams=team,
block_name=block_name,
titles=titles)
def get_actor_meta(iUUID="", iCommonName=""):
ret_list = []
session = Session()
try:
if iUUID:
# query = "SELECT * FROM mp_actor_meta WHERE mpactor_uuid = ? or mp_commonname = ? "
# value = (iUUID, iCommonName)
# ret_list = cursor.fetchall()
ret_list = session.query(md.ActorMeta). \
filter(md.ActorMeta.uuid == iUUID). \
first()
return md.ActorMetaSchema(many=False).dump(ret_list)
elif iCommonName:
ret_list = session.query(md.ActorMeta). \
filter(md.ActorMeta.commonname == iCommonName). \
first()
return md.ActorMetaSchema(many=False).dump(ret_list)
else:
return {}
except Exception as error:
print("f(x) get_actor_meta: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
def get_family_meta(iUUID="", iName=""):
ret_list = []
session = Session()
try:
if iUUID:
# query = "SELECT * FROM mp_malware_meta WHERE mpmalware_uuid = ? or mp_name = ?"
# value = (iUUID, iName)
ret_list = session.query(md.MalwareMeta). \
filter(md.MalwareMeta.uuid == iUUID). \
first()
return md.MalwareMetaSchema(many=False).dump(ret_list)
elif iName:
# query = "SELECT * FROM mp_malware_meta WHERE mpmalware_uuid = ? or mp_name = ?"
# value = (iUUID, iName)
ret_list = session.query(md.MalwareMeta). \
filter(md.MalwareMeta.name == iName). \
first()
return md.MalwareMetaSchema(many=False).dump(ret_list)
else:
return {}
except Exception as error:
print("f(x) get_family_meta: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
def get_galaxy_specific_tags(iSearchTerm, iGalaxy="mitre-attack-pattern"):
ret_list = []
session = Session()
try:
# STANDARD SEARCH IN DESCRIPTIONS AND tAG
if iGalaxy == "":
iSearchTerm = "%" + iSearchTerm + "%"
# query = "select galaxy, tag from misp_galaxy_clusters where tag like ?"
ret_list = session.query(md.MispGalaxyCluster). \
filter(md.MispGalaxyCluster.tag.ilike(iSearchTerm)). \
all()
elif iGalaxy == "malware":
iSearchTerm = "%- " + iSearchTerm + "%"
# query = "select galaxy, tag from misp_galaxy_clusters where (galaxy = 'mitre-tool' or galaxy = 'mitre-malware') AND tag like ?"
ret_list = session.query(md.MispGalaxyCluster). \
filter(md.MispGalaxyCluster.tag.ilike(iSearchTerm)). \
filter(sa.or_(md.MispGalaxyCluster.galaxy == 'mitre-tool', md.MispGalaxyCluster.galaxy == 'mitre-tool')). \
all()
else:
iSearchTerm = "%- " + iSearchTerm + "%"
# query = "select galaxy, tag from misp_galaxy_clusters where galaxy=? and tag like ?"
ret_list = session.query(md.MispGalaxyCluster). \
filter(sa.and_(md.MispGalaxyCluster.galaxy.ilike(iGalaxy), md.MispGalaxyCluster.tag.ilike(iSearchTerm))). \
all()
return md.MispGalaxyClusterSchema(many=True).dump(ret_list)
except Exception as error:
print(
"f(x) get_galaxy_specific_tags: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
def insert_actor(iActorUUID, iShortName, iCommonName, iCountry, iVictimology,
iTOI, iSS, iSince, iMOO, iCaps, iLastUpdate, iDescription):
session = Session()
try:
dbInsert = md.ActorMeta()
dbInsert.uuid = iActorUUID
dbInsert.shortname = iShortName
dbInsert.commonname = iCommonName
dbInsert.country = iCountry
dbInsert.victimology = iVictimology
dbInsert.cfrtypeofincident = iTOI
dbInsert.cfrstatesponsor = iSS
dbInsert.since = iSince
dbInsert.modeofoperation = iMOO
dbInsert.capabilities = iCaps
dbInsert.last_update = iLastUpdate
dbInsert.description = iDescription
session.merge(dbInsert)
session.commit()
except Exception as error:
session.rollback()
print("f(x) insert_actor: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
#DEBUG SEQ
if gv._DEBUG:
print("f(x) insert_actor: {} DATA ADDED TO ACTORS TABLE".format(
iShortName))
def return_existing_kode(kode):
session = Session()
check_if_present = session.query(Company_List).\
filter(Company_List.company_code == kode).\
scalar()
session.close()
return check_if_present
def view_player(id):
session = Session()
player = session.query(Player).filter(Player.id == id).one()
players = session.query(Player).all()
decks = session.query(Deck).all()
pmap = {}
for d in players:
pmap[d.id] = d
dmap = {}
for d in decks:
dmap[d.id] = d
rank_manager = RankingManager()
ranking = rank_manager.get_ranking()
rank_data = ranking.get_player_ranking(id)
table = rank_manager.ranking_table([rank_data], True)
games_data, decks_data = get_player_info(id)
titles = rank_manager.get_titles(id)
return render_template('players/view_player.html',
player=player,
players=pmap,
decks=dmap,
ranking_table=table,
games_data=games_data,
decks_data=decks_data,
titles=titles)
def create_session(data):
password, session_name, username = data['password'], data[
'session_name'], data['username']
req_session: Session = Session.query.get(name=session_name)
if req_session.id is not None:
# the session already exists
# try to join?
print('Session alredy exists')
return
# create session
new_session = Session(session_name, password)
# join this user
join_session(data={
'session_id': new_session.id,
'password': password,
'username': username
},
socket_id=request.sid)
# return to the user that this session was created
response_data = {
'users': [user.serialize() for user in req_session.user_tokens]
}
emit('created_session', room=new_session.id)
def setup_data():
delete_db(engine)
session = Session()
AppModelBase.metadata.create_all(engine)
acs = [
ArtifactCoordinatesModel(lat=40.09389490340147,
lon=-85.62538134792705),
ArtifactCoordinatesModel(lat=40.09585039419487,
lon=-85.62004021718168),
ArtifactCoordinatesModel(lat=40.09451269825916, lon=-85.62251577299321)
]
aims = [
ArtifactImageModel('/static/whiteoak.jpg', 'The white oak tree'),
ArtifactImageModel('/static/entrance.jpg', 'The visitor center'),
ArtifactImageModel('/static/greatmound.jpg', 'The great mound!')
]
artifacts = [
ArtifactModel(name="White Oak", description="The white oak tree"),
ArtifactModel(name="Visitor Center",
description="The visitor center."),
ArtifactModel(name="Great Mound", description="The great mound.")
]
for loc in acs:
session.add(loc)
for a in aims:
session.add(a)
for idx, artifact in enumerate(artifacts):
artifact.addCoordinates(acs[idx])
artifact.addImage(aims[idx])
session.commit()
session.close()
def dropall():
session = Session()
print(session.execute('DROP TABLE game;'))
print(session.execute('DROP TABLE participant;'))
print(session.execute('DROP TABLE tournament;'))
print(session.execute('DROP TABLE deck;'))
print(session.execute('DROP TABLE player;'))
def dump():
session = Session()
sql_file = open('data/dump.sql', 'r')
# Create an empty command string
sql_command = ''
# Iterate over all lines in the sql file
for line in sql_file:
# Ignore comented lines
if not line.startswith('--') and line.strip('\n'):
# Append line to the command string
line = line.strip('\n')
if '--' in line:
line = line[0: line.rindex('--')]
sql_command += line
# If the command string ends with ';', it is a full statement
if sql_command.endswith(';'):
# Try to execute statemente and commit it
try:
session.execute(sql_command)
session.commit()
# Assert in case of error
except Exception as e:
print(sql_command)
print('Ops')
print(e)
# Finally, clear command string
finally:
sql_command = ''
def handleSnippetLike(snippet_id, user):
session = Session()
#increment snippet likes
snippet_to_update = session.query(Snippet).filter_by(
id=snippet_id).first()
snippet_to_update.likes = snippet_to_update.likes + 1
session.add(snippet_to_update)
#print("Updated likes for", snippet_to_update)
#associate user that liked snippet with the snippet's attributes
#print('id:', user.id)
#print("about to print temp snippet")
tempSnippetTags = snippet_to_update.tags[:]
#print(tempSnippetTags, '\n')
user_to_update = session.query(User).filter_by(id=user.id).first()
#print(user_to_update)
#print('before', user_to_update.tags, '\n')
for stag in tempSnippetTags:
for utag in user_to_update.tags:
if stag == utag:
tempSnippetTags.remove(stag)
break
user_to_update.tags.extend(tempSnippetTags)
#print('after', user_to_update.tags, '\n')
session.add(user_to_update)
#commit changes
session.commit()
def add_streams(self, user_id, s_id):
""" Add Strava data streams for a given user_id and activity_id """
# get the strava streams for that activity
stream = self.get_streams(s_id)
# convert the streams to a DF
if stream is not None:
s = Session()
df = self.stream_to_DF(stream)
# add `user_id` to the DF
df['user_id'] = user_id
# add `activity_id` to the DF
df['activity_id'] = s.query(
Activity.id).filter_by(strava_id=s_id.astype(str)).one()[0]
try:
df.to_sql(self.streams_TBL,
engine,
if_exists='append',
index=False)
s.commit()
except:
s.rollback()
print('Error: `add_streams` cannot write event to DB. \n')
raise
s.close()
else:
print('Error: Stream is empty for User {0}, Activity {1}'.format(
user_id, s_id))
return
def delete_video(self, file):
shutil.rmtree(f"hls/{file.file_id}")
session = Session()
session.delete(file)
session.commit()
Session.remove()
def post(self, user):
root_args = self.parser.parse_args()
snippet_args = self.snippetParser.parse_args(req=root_args)
#print("New snippet:", snippet_args, snippet_args.title)
#print("tags: ", snippet_args.tags.split(','))
new_tag = snippet_args.tags.split(',')
session = Session()
snippet = Snippet(title=snippet_args.title,
author=snippet_args.author,
content=snippet_args.content,
tags=[Tag(name=tagName) for tagName in new_tag])
session.add(snippet)
session.commit()
# this response is used by the react client to instantly construct the snippet
return {
'id': snippet.id,
'title': snippet.title,
'author': snippet.author,
'content': snippet.content,
#'user_id': snippet.user_id,
'likes': snippet.likes,
'created_at': snippet.created_at.isoformat() + 'Z',
'tags': snippet_args.tags
#'comments': snippet.comments
}
def post(self):
'''Login a user.'''
args = parser.parse_args()
name = args['name']
passwd = args['password']
if not name or not passwd:
return {"message": "Fill all fields!!"}, 400
user = User.query.filter_by(name=name).first()
if not user:
return {"message": "Wrong credentials!!"}, 403
if check_password_hash(user.password, passwd):
token = jwt.encode(
{
'public_id':
user.public_id,
'exp':
datetime.datetime.utcnow() + datetime.timedelta(minutes=30)
}, app.config['SECRET'])
session = Session(user_id=user.public_id,
token=token.decode('UTF-8'))
db.session.add(session)
db.session.commit()
return {
'name': user.name,
'id': user.id,
'token': token.decode('UTF-8'),
'created_date': str(user.date_created),
}, 200
def update_game(gameId):
payload = request.json
params = ['p1Wins', 'p2Wins']
for param in params:
if param not in payload:
abort(400)
p1_wins = payload['p1Wins']
p2_wins = payload['p2Wins']
if not 0 <= p1_wins <= 2:
abort(400)
if not 0 <= p2_wins <= 2:
abort(400)
session = Session()
game = session.query(Game).filter(Game.id == gameId).one()
game.p1_wins = p1_wins
game.p2_wins = p2_wins
session.add(game)
session.commit()
RankingManager().refresh()
return make_response()
def get_images():
session = Session()
image_type = request.args.get('type')
images = session.query(DisplayImage).filter_by(type=image_type).all()
res = json.dumps(images, cls=AlchemyEncoder, encoding='utf-8')
session.close()
return res
def tie_breaker(rank):
p1 = rank[0]
p2 = rank[1]
p1_wins = 0
p2_wins = 0
if p1['pts'] == p2['pts']:
session = Session()
games = session.query(Game) \
.filter(
or_(
and_(Game.p1_id == p1['id'], Game.p2_id == p2['id']),
and_(Game.p1_id == p2['id'], Game.p2_id == p1['id'])
)
) \
.order_by(Game.id.asc()) \
.all()
for game in games:
if game.p1_id == p1['id']:
if game.p1_wins > game.p2_wins:
p1_wins += 1
else:
p2_wins += 1
else:
if game.p1_wins > game.p2_wins:
p2_wins += 1
else:
p1_wins += 1
if p2_wins > p1_wins:
rank[0] = p2
rank[1] = p1
return rank
def add_user(self,
username,
email=None,
fname=None,
lname=None,
password=None):
""" Add a user to the DB"""
s = Session()
try:
u = User(username=username,
email=email,
fname=fname,
lname=lname,
password=password,
strava_id=self.athlete.id)
s.add(u)
s.commit()
return int(u.id)
except SQLAlchemyError as err:
s.rollback()
print('Error: \n', err)
raise
s.close()
return
def detail_user_profile(userid):
session = Session()
try:
prof = get_detail_user_profile(session, userid)
if not prof:
return "User with userid %s does not exist." % userid
if request.method == 'POST' and request.content_type == 'application/json':
data = {
'userid': prof.userid,
'username': prof.username,
'firstname': prof.firstname,
'lastname': prof.lastname,
'image': prof.image,
'gender': prof.gender,
'age': prof.age,
'profile_created_on': prof.created
}
return jsonify(data)
else:
return render_template('detail_user_profile.html',
profile=prof,
img_link=url_for('.profile_image',
userid=prof.userid))
except Exception as ex:
session.rollback()
raise ex
def scrape(recipe_url):
r = urllib.urlopen(recipe_url).read()
soup = BeautifulSoup(r, 'html.parser')
script = soup.find('script', type='application/ld+json')
metadata = loads(script.text)
db_session = Session()
db_recipe = Recipe()
db_recipe.name = metadata['name']
db_recipe.source = 1
db_session.add(db_recipe)
db_session.commit()
for ingredient in metadata['recipeIngredient']:
db_ingredient = Ingredient()
db_ingredient.name = ingredient
db_ingredient.recipe_id = db_recipe.id
db_session.add(db_ingredient)
step_number = 0
for instruction in metadata['recipeInstructions']:
step_number += 1
db_instruction = Instruction()
db_instruction.recipe_id = db_recipe.id
db_instruction.step_number = step_number
db_instruction.text = instruction
db_session.add(db_instruction)
db_session.commit()
def change_password(request):
session = Session()
user = session.query(User).get(request.session['user_id'])
if user == None:
return HTTPFound(location='/admin/dashboard')
return dict(status='ok', view_path=c_view_paths['admin'], user=user)
def setUp(self):
self.app = app.test_client()
self.app.testing = True
try:
session = Session()
session.add(User(email=self.test_email))
session.commit()
except Exception:
session.rollback()
finally:
session.close()
session = Session()
self.user_id = session.query(
User.id).filter(User.email == self.test_email).scalar()
session.close()
def create_insert_object_from_model(model, artifactObject):
session = Session()
session.add(artifactObject)
session.commit()
artifactID = artifactObject.id
artifact = session.query(model).get(artifactObject.id)
arttifactID1 = artifact.id
session.close()
assert arttifactID1 == artifactID
def do_delete(request):
session = Session()
post_id = request.matchdict['id']
post = session.query(Post).get(post_id)
session.delete(post)
session.commit()
return HTTPFound(location='/admin/post')
def show(request):
session = Session()
post_id = request.matchdict['id']
post = session.query(Post).get(post_id)
if post == None:
return HTTPFound(location='/admin/dashboard')
return dict(status='ok', view_path=c_view_paths['admin'], post=post)
def search_artifact_name(data):
session=Session()
searchString=data['searchString']
if(searchString==""):
emit('retrieved_search_results',[])
else:
searchResults=ArtifactModel.getPartials(session,searchString)
searchResults = [ item.getDict() for item in searchResults ]
emit('retrieved_search_results',searchResults)
session.close()
def session():
Base.metadata.bind = engine
Base.metadata.create_all()
connection = engine.connect()
transaction = connection.begin()
session = Session(bind=connection)
yield session
session.close()
Base.metadata.drop_all(bind=engine)
connection.close()
def handleSnippetDelete(snippet_id):
session = Session()
# TODO: get scoped_session.query to use BaseQuery to allow for get_or_404 etc
snippet = session.query(Snippet).get(snippet_id)
if (snippet == None):
abort(404)
session.delete(snippet)
session.commit()
#print("Deleted snippet", snippet_id)
return ("snippet %s deleted" % snippet_id), 200
