def execute_bid(token_id, price, address):
query = f"UPDATE {SCHEMA}.{NEW_SELL_TABLE_NAME} SET current_price={price}, address='{address}', " \
f"end_date=(SELECT CASE WHEN end_date > DATEADD(minute, 10, GETUTCDATE()) " \
f"THEN end_date ELSE DATEADD(minute, 10, GETUTCDATE()) " \
f"END AS end_date FROM NewSell WHERE token_id={token_id}) " \
f"WHERE token_id={token_id}"
SqlManager().execute_query(query, True)
def follow(follower: str, star: str) -> None:
if username_in_db(follower) and username_in_db(star) and not is_follow(
follower, star):
query = f"INSERT INTO {SCHEMA}.{FOLLOWERS_TABLE_NAME} " \
f"VALUES ((SELECT id FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE username='******'), " \
f"(SELECT id FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE username='******'))"
SqlManager().execute_query(query, True)
def get_followers_from_username(username: str) -> Optional[int]:
if username_in_db(username):
query = f"SELECT count(*) as total_followers " \
f"FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} RIGHT JOIN {SCHEMA}.{FOLLOWERS_TABLE_NAME} " \
f"ON {SCHEMA}.{ACCOUNT_TABLE_NAME}.[id]={SCHEMA}.{FOLLOWERS_TABLE_NAME}.star_id" \
f" WHERE username='******'"
return SqlManager().query_df(query).loc[0, 'total_followers']
return None
def cancel_resale(token_id: int) -> bool:
address = get_address_of_resale(token_id)
tx_id = transfer_nft_to_user(token_id, address)
tx_info = wait_for_confirmation(tx_id)
if bool(tx_info.get('confirmed-round')):
query = f"DELETE FROM {SCHEMA}.{RESELL_TABLE_NAME} WHERE token_id={token_id}"
SqlManager().execute_query(query, True)
return True
return False
def get_data_from_token_id(token_ids: list):
query = f"SELECT {SCHEMA}.{TOKEN_TABLE_NAME}.username, extension, swarm_hash, title, token_id, Token.is_public, " \
f"profile_picture_extension " \
f"FROM {SCHEMA}.{TOKEN_TABLE_NAME} " \
f"LEFT JOIN {SCHEMA}.{ACCOUNT_TABLE_NAME} " \
f"ON {SCHEMA}.{TOKEN_TABLE_NAME}.username = {SCHEMA}.{ACCOUNT_TABLE_NAME}.username " \
f"WHERE token_id in ({','.join(str(token_id) for token_id in token_ids)})"
df = SqlManager().query_df(query)
return df
def update_profile_picture(username: str, data: FileStorage,
extension: str) -> None:
blob_client = BlobClient.from_connection_string(
BLOB_CONNECTION_STRING, PROFILE_PICTURES_CONTAINER,
f"{username.lower()}.{extension}")
blob_client.upload_blob(data)
query = f"UPDATE {SCHEMA}.{ACCOUNT_TABLE_NAME} SET profile_picture_extension='{extension}' " \
f"WHERE username='******'"
SqlManager().execute_query(query, True)
def create_resale(username: str, token_id: int, price: int,
tx_id: str) -> None:
tx_info = wait_for_confirmation(tx_id)
address = tx_info.get('txn').get('txn').get('snd')
xaid = tx_info.get('txn').get('txn').get('xaid')
confirmed = bool(tx_info.get('confirmed-round'))
if xaid == token_id and confirmed and token_id in list_account_assets(
ADDRESS_ALGO_OURSELF):
query = f"INSERT INTO {SCHEMA}.{RESELL_TABLE_NAME} (username, address, token_id, price) " \
f"VALUES ('{username}', '{address}', {token_id}, {price})"
SqlManager().execute_query(query, True)
def save_to_db_temp(reason: str,
email: str,
username: str = None,
password: str = None,
fullname: str = None) -> None:
code = random.randint(100000, 999999)
query = f"DELETE FROM {SCHEMA}.{TEMP_ACCOUNT_TABLE_NAME} WHERE email='{email}'"
SqlManager().execute_query(query, True)
query = f"INSERT INTO {SCHEMA}.{TEMP_ACCOUNT_TABLE_NAME} (email, username, password, fullname, code) " \
f"VALUES ('{email}', '{username}', '{password}', '{fullname}', {code})"
SqlManager().execute_query(query, True)
# TODO : remove print
print(code)
data = {
"body": f"Your validation code : {code}",
"email": f"{email}",
"object": f"MyPic Security - {reason}"
}
headers = {"Content-Type": "application/json"}
requests.post(LOGIC_APP_MAIL_URL, headers=headers, json=data)
def create_new_image(username: str, file: FileStorage, title: str, price: int,
end_date: datetime, is_public: int, is_nsfw: int) -> None:
extension = DICTIONARY_FORMAT[secure_filename(
file.filename).split('.')[-1].lower()]
swarm_hash_all = upload_image_swarm(file, username, is_public)
swarm_hash = swarm_hash_all[:64]
swarm_key = swarm_hash_all[64:]
is_public_bool = bool(is_public)
token_id = mint_official_nft(swarm_hash=swarm_hash,
is_public=is_public_bool,
username=username,
title=title,
number=0)
query = f"INSERT INTO {SCHEMA}.{TOKEN_TABLE_NAME} " \
f"(token_id, username, swarm_hash, title, extension, number, is_public, is_nsfw) " \
f"VALUES ({token_id}, '{username}', '{swarm_hash}', '{title}', '{extension}', 1, {is_public}, {is_nsfw})"
SqlManager().execute_query(query, True)
query = f"INSERT INTO {SCHEMA}.{SWARM_ENCRYPT_TABLE_NAME} (token_id, swarm_hash, swarm_key) " \
f"VALUES ({token_id}, '{swarm_hash}', '{swarm_key}')"
SqlManager().execute_query(query, True)
query = f"INSERT INTO {SCHEMA}.{NEW_SELL_TABLE_NAME} " \
f"(token_id, start_price, current_price, end_date) VALUES ({token_id}, {price}, {price}, '{end_date}')"
SqlManager().execute_query(query, True)
def get_resale(page: int,
username: str = None,
email: str = None,
follower: str = None,
my: bool = False) -> list:
query = f"SELECT * FROM {SCHEMA}.{RESELL_VIEW_NAME}"
if follower is not None:
query += f" WHERE username in (SELECT star FROM [dbo].[FollowersView] WHERE follower='{follower}')"
df = SqlManager().query_df(query)
df.sort_values('price', inplace=True)
if username is not None:
df = df[df['seller'].str.lower() == username.lower()]
if email is not None:
df = df[
df['seller'].str.lower() != get_username_from_email(email).lower()]
df.reset_index(inplace=True)
df = df.loc[page * NUMBER_PRINT_IMAGE:(page + 1) * NUMBER_PRINT_IMAGE - 1]
num_cores = multiprocessing.cpu_count()
resale_images = Parallel(n_jobs=num_cores)(
delayed(build_resale_images)(row, my) for _, row in df.iterrows())
return resale_images
def token_id_in_resale(token_id: int) -> bool:
query = f"SELECT COUNT(*) AS total FROM {SCHEMA}.{RESELL_TABLE_NAME} WHERE token_id={token_id}"
return SqlManager().query_df(query).loc[0, 'total'] == 1
def update_address(email: str, address: str) -> None:
query = f"UPDATE {SCHEMA}.{ACCOUNT_TABLE_NAME} SET address='{address}' WHERE email='{email}'"
SqlManager().execute_query(query, True)
def unfollow(follower: str, star: str) -> None:
if username_in_db(follower) and username_in_db(star):
query = f"DELETE FROM {SCHEMA}.{FOLLOWERS_TABLE_NAME} " \
f"WHERE follower_id=(SELECT id FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE username='******') " \
f"AND star_id=(SELECT id FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE username='******')"
SqlManager().execute_query(query, True)
def update_fullname(email: str, fullname: str) -> None:
query = f"UPDATE {SCHEMA}.{ACCOUNT_TABLE_NAME} SET fullname='{fullname}' WHERE email='{email}'"
SqlManager().execute_query(query, True)
def get_fullname_from_email(email: str) -> Optional[str]:
if email_in_db(email):
query = f"SELECT fullname FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE email='{email}'"
return SqlManager().query_df(query).loc[0, 'fullname']
return None
def jti_in_blacklist(jti: str) -> bool:
query = f"SELECT COUNT(*) AS total FROM {SCHEMA}.{ACCESS_TOKEN_BLACKLIST_TABLE} WHERE jti='{jti}'"
return SqlManager().query_df(query).loc[0, 'total'] == 1
def update_nsfw(email: str, nsfw: int) -> None:
query = f"UPDATE {SCHEMA}.{ACCOUNT_TABLE_NAME} SET nsfw={nsfw} WHERE email='{email}'"
SqlManager().execute_query(query, True)
def get_address_from_username(username: str) -> Optional[str]:
if username_in_db(username):
query = f"SELECT address FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE username='******'"
return SqlManager().query_df(query).loc[0, 'address']
return None
def get_current_price_from_token_id(token_id: int) -> Optional[int]:
if token_id_in_new_sell(token_id):
query = f"SELECT current_price FROM {SCHEMA}.{NEW_SELL_TABLE_NAME} WHERE token_id={token_id}"
return SqlManager().query_df(query).loc[0, 'current_price']
return None
def insert_jti_in_blacklist(jti: str) -> None:
query = f"INSERT INTO {SCHEMA}.{ACCESS_TOKEN_BLACKLIST_TABLE} (jti) VALUES ('{jti}')"
SqlManager().execute_query(query, True)
def is_follow(follower: str, star: str) -> bool:
query = f"SELECT COUNT(*) AS total FROM {SCHEMA}.{FOLLOWERS_VIEW} WHERE follower='{follower}' AND star='{star}'"
return SqlManager().query_df(query).loc[0, 'total'] == 1
def username_in_db(username: str) -> bool:
query = f"SELECT COUNT(*) AS total FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE username='******'"
return SqlManager().query_df(query).loc[0, 'total'] == 1
def update_password(email: str, password: str) -> None:
query = f"UPDATE {SCHEMA}.{ACCOUNT_TABLE_NAME} SET password='******' WHERE email='{email}'"
SqlManager().execute_query(query, True)
def update_is_public(email: str, is_public: int) -> None:
query = f"UPDATE {SCHEMA}.{ACCOUNT_TABLE_NAME} SET is_public={is_public} WHERE email='{email}'"
SqlManager().execute_query(query, True)
def save_to_db(email: str) -> None:
query = f"INSERT INTO {SCHEMA}.{ACCOUNT_TABLE_NAME} (email, username, password, fullname) " \
f"SELECT email, username, password, fullname FROM {SCHEMA}.{TEMP_ACCOUNT_TABLE_NAME} " \
f"WHERE email='{email}'"
SqlManager().execute_query(query, True)
def get_nsfw_from_email(email: str) -> Optional[bool]:
if email_in_db(email):
query = f"SELECT nsfw FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE email='{email}'"
return bool(SqlManager().query_df(query).loc[0, 'nsfw'])
return None
def execute_buy(token_id):
query = f"DELETE FROM {SCHEMA}.{RESELL_TABLE_NAME} WHERE token_id={token_id}"
SqlManager().execute_query(query, True)
def get_username_of_resale(token_id: int) -> Optional[str]:
if token_id_in_resale(token_id):
query = f"SELECT username FROM {SCHEMA}.{RESELL_TABLE_NAME} WHERE token_id='{token_id}'"
return SqlManager().query_df(query).loc[0, 'username']
return None
def get_stars_from_follower(follower: str):
query = f"SELECT star FROM [dbo].[FollowersView] WHERE follower='{follower}' ORDER BY star"
return list(SqlManager().query_df(query)['star'])
def email_in_db(email: str) -> bool:
query = f"SELECT COUNT(*) AS total FROM {SCHEMA}.{ACCOUNT_TABLE_NAME} WHERE email='{email}'"
return SqlManager().query_df(query).loc[0, 'total'] == 1