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 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()
class TestCard(unittest.TestCase):
def setUp(self):
self.session = Session()
def tearDown(self):
self.session.close()
def testSaveCard(self):
delete_db(engine)
AppModelBase.metadata.create_all(engine)
forbidden = "{'word1', 'word2', 'word3', 'word4'}"
card = CardModel(buzzword="unit test",
forbidden_words=forbidden,
source="Book",
source_page="345",
skipped_count=0,
won_count=0,
lost_count=0,
is_phrase=IS_PHRASE)
self.session.add(card)
self.session.commit()
card = CardModel.findCardById(self.session, 1)
self.assertEqual(card.buzzword, "unit test")
def testCountCard(self):
self.assertEqual(CardModel.numberOfRows(self.session), 1)
def testRemoveForbiddenWords(self):
card = CardModel.findCardById(self.session, 1)
card.removeForbiddenWords()
self.assertEqual(card.forbiddenWords, "")
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 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 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 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 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 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 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()
class TestShop(unittest.TestCase):
def setUp(self):
self.session = Session().session
def tearDown(self):
self.session.close()
def test_select(self):
shop = self.session.query(Shop.name).filter(Shop.id == 1).first()
self.assertEqual(shop.name, "ラビットハウス")
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 post_images():
session = Session()
if 'type' in request.form and 'url' in request.form:
image_type = request.form['type']
image_url = request.form['url']
session.add(DisplayImage(url=image_url, type=image_type))
session.commit()
session.close()
return handle_response({'success': True})
else:
return handle_response({'success': False, 'msg': 'invalid post data'})
def tearDown(self):
session = Session()
user = session.query(User).filter(
User.email == self.new_user_email).first()
# TODO make it cascade to the users's transfers
if user:
session.delete(user)
session.commit()
session.close()
else:
session.rollback()
def get_parent_child_data(iValue="", iUUID=""):
ret_list = ""
session = Session()
try:
if gv._DEBUG:
print("f(x) get_parent_child_data: PULLING UUID:VALUE: [{}]:[{}]".
format(iUUID, iValue))
if iValue == "all":
# query = "select uuid from mp_parent_child_by_uuid"
ret_list = session.query(md.ParentChildByUuid). \
with_entities(md.ParentChildByUuid.uuid). \
all()
return md.ParentChildByUuidSchema(many=True).dump(ret_list)
elif iValue == "actor" or iValue == "family" or iValue == "path" or iValue == "malware":
# query = "select uuid from mp_parent_child_by_uuid where mytype = ? order by path asc"
# value = (iValue.upper(),)
ret_list = session.query(md.ParentChildByUuid). \
with_entities(md.ParentChildByUuid.uuid). \
filter(md.ParentChildByUuid.mytype == iValue.upper()). \
order_by(md.ParentChildByUuid.path). \
all()
return md.ParentChildByUuidSchema(many=True).dump(ret_list)
elif iValue == "" and iUUID != "":
# query = "select * from mp_parent_child_by_uuid where uuid = ?"
# value = (iUUID,)
# ret_val = cursor.fetchone()
ret_list = ret_list = session.query(md.ParentChildByUuid). \
filter(md.ParentChildByUuid.uuid == iUUID). \
first()
return md.ParentChildByUuidSchema(many=False).dump(ret_list)
elif iValue != "" and iUUID == "":
# query = "select * from mp_parent_child_by_uuid where name = ?"
# ret_val = cursor.fetchone()
ret_list = session.query(md.ParentChildByUuid). \
filter(md.ParentChildByUuid.name == iValue). \
first()
return md.ParentChildByUuidSchema(many=False).dump(ret_list)
elif iValue != "" and iUUID != "":
# query = "select * from mp_parent_child_by_uuid where name = ? or uuid = ?"
# value = (iValue, iUUID)
# ret_val = cursor.fetchone()
ret_list = session.query(md.ParentChildByUuid). \
filter(sa.or_(md.ParentChildByUuid.name == iValue, md.ParentChildByUuid.uuid == iUUID)). \
first()
return md.ParentChildByUuidSchema(many=False).dump(ret_list)
else:
return {}
except Exception as error:
print("f(x) get_parent_child_data: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
def get_children(iUUID):
ret_list = []
session = Session()
try:
# query = "SELECT uuid FROM mp_parent_child_by_uuid WHERE parentuuid = ?"
ret_list = session.query(md.ParentChildByUuid). \
with_entities(md.ParentChildByUuid.uuid). \
filter(md.ParentChildByUuid.parentuuid == iUUID). \
all()
return md.ParentChildByUuidSchema(many=True).dump(ret_list)
except Exception as error:
print("f(x) get_children: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
def add_user(self, username, email, fname=None, lname=None, password=None):
s = Session()
try:
u = User(username=username, email=email,
fname=fname, lname=lname, password=password)
s.add(u)
s.commit()
return int(u.id)
except SQLAlchemyError as err:
s.rollback()
print('Error: \n', err)
raise
s.close()
def timed_job():
session = Session()
users = session.query(User)
for u in users:
if datetime.datetime.utcnow() >= u.time_reminder:
try:
viber.send_messages(u.viber_id, [
TextMessage(text="Время повторить слова",
keyboard=WAIT_KEYBOARD,
tracking_data='tracking_data')
])
except:
print("Пользователь отписался")
print(u.full_name)
session.close()
def test6GameRollWheel(self):
wheel = {"gameID": 1, "duration": 4}
self.game_namespace.on("my_roll_result", self.passWheelInfo)
self.game_namespace.emit("roll_wheel", wheel)
self.socketIO.wait(seconds=5)
session = Session()
turnID = 1
turn = TurnModel.getTurnById(turnID, session)
print(
"\n\n**********Test wheel result game changer write to DB*********Pass"
)
self.assertEqual(self.currentWheel["rollID"], turn.gameChangerNumber)
session.commit()
session.close()
def book_list():
session = Session()
query = session.query(Book)
show_author = bool(int(request.args.get("show_author", 0)))
show_id = bool(int(request.args.get("show_id", 0)))
if "title" in request.args:
query = query.filter(Book.title == request.args.get("title"))
if "isbn" in request.args:
query = query.filter(Book.isbn == request.args.get("isbn"))
if "book_read" in request.args:
query = query.filter(Book.book_read == request.args.get("book_read"))
rsp = [book.as_dict(show_id, show_author) for book in query]
session.close()
return json.dumps(rsp), 200
def get_synonyms(iUUID):
ret_list = []
session = Session()
try:
# query = "select * from synonyms where uuid = ?"
ret_list = session.query(md.Synonym). \
filter(md.Synonym.uuid == iUUID). \
all()
return md.SynonymSchema(many=True).dump(ret_list)
except Exception as error:
print("f(x) get_synonyms: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
def add_user():
""" Add a user from the GUI interface"""
s = Session()
try:
u = User(username='******', email='*****@*****.**',
fname='me', lname='me', password='******')
s.add(u)
s.commit()
s.close()
flash ("User %s added" %u.username)
return int(u.id)
except:
s.rollback()
s.close()
raise
def get_manual_tags(iSearchTag, iSearchGalaxy):
ret_list = []
session = Session()
try:
# query = "select * from manual_tag_maps where tagvalue = ? and galaxy like ?"
ret_list = session.query(md.ManualTagMap). \
filter(sa.and_(md.ManualTagMap.tagvalue == iSearchTag, md.ManualTagMap.galaxy.ilike(iSearchGalaxy))). \
all()
return md.ManualTagMapSchema(many=True).dump(ret_list)
except Exception as error:
session.rollback()
print("f(x) insert_galaxy: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
def add_activity(self,
user_id,
before=None,
after=None,
limit=None,
add_streams=True):
""" Get & add a list of activities from strava """
# get the list of activities from strava
activities = self.get_activities(before=before,
after=after,
limit=limit)
activities = activities
# transform activities to a DF ready for Postgres
df = self.munge_activity(activities)
df['user_id'] = user_id
s = Session()
try:
df.to_sql(self.activity_TBL,
engine,
if_exists='append',
index=False)
s.commit()
print('Added {0} activities from Strava.\n'.format(
len(df.strava_id)))
except:
s.rollback()
print('Error: `add_activity` cannot write event to DB. \n')
raise
s.close()
# if needed, add the streams as well
if add_streams is True:
size = len(df.strava_id)
for (i, strava_id) in enumerate(df.strava_id):
print('Fetching data streams for {0}: {1} of {2}'.format(
strava_id, i, size),
end='\r')
time.sleep(
self.API_CALL_PAUSE_SECONDS) # limit API call to 40 / min
self.add_streams(user_id, strava_id)
print('Added `Streams` for {0} activities from Strava.'.format(
len(df.strava_id)))
return
def get_mitre_software_code(iValue):
ret_list = []
session = Session()
try:
namevariations = iValue.replace("-", "").replace(" ", "").replace(
"(", "").replace(")", "").replace("/", "").replace("\\", "")
# query = "select * from mitre_software_xref where mitreName like ? or mitreName like ?"
ret_list = session.query(md.MitreSoftwareXref). \
filter(sa.or_(md.MitreSoftwareXref.mitrename.ilike(iValue), md.MitreSoftwareXref.mitrename.ilike(namevariations))). \
all()
return md.MitreSoftwareXrefSchema(many=True).dump(ret_list)
except Exception as error:
print("f(x) get_mitre_software_code: DATABASE ERROR: {}".format(error))
sys.exit(error)
finally:
session.close()
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 book_list_by_id(book_id):
session = Session()
query = session.query(Book).filter(Book.id == book_id)
show_author = False
show_id = False
if not exists_in_db("id", book_id, "Book"):
return json.dumps(return_code[4]), 404
if request.args.get("show_author") == "1":
show_author = True
if request.args.get("show_id") == "1":
show_id = True
rsp = [book.as_dict(show_id, show_author) for book in query]
session.close()
return json.dumps(rsp), 200
def book_add():
session = Session()
authorid = None
title = None
isbn = None
read = 0
if len(request.json) == 0:
return json.dumps(return_code[1]), 400
if "title" in request.json:
title = request.json["title"]
else:
return json.dumps(return_code[2]), 400
if "author_id" in request.json:
authorid = request.json["author_id"]
if not exists_in_db("id", authorid, "Author"):
return json.dumps(return_code[5]), 400
else:
return json.dumps(return_code[2]), 400
if "isbn" in request.json:
isbn = request.json["isbn"]
if "read" in request.json:
read = request.json["read"]
new_book = Book(authorid, title, isbn=isbn, book_read=read)
if exists_in_db("title", title, "Book"):
return json.dumps(return_code[3]), 409
if exists_in_db("isbn", isbn, "Book"):
return json.dumps(return_code[3]), 409
session.add(new_book)
session.commit()
rsp_dict = dict(return_code[0])
rsp_dict["added"] = new_book.as_dict(show_id=True)
rsp_dict = json.dumps(rsp_dict)
rsp = Response(rsp_dict)
rsp.headers.add("Location", "/api/books/{}".format(new_book.id))
session.close()
return rsp, 201
