def translate_contract(connection, contract_id=None):
"""
Reads the meta.json file in the contract file and translate it.
"""
ipfs_object_stmt = """
SELECT `if`.* FROM `ipfs_files` `if`
INNER JOIN `music_contracts` `mc`
ON (`if`.`file_id` = `mc`.`ipfs_file_id`)
WHERE `mc`.`contract_id` = :contract_id
LIMIT 1
"""
ipfs_object = connection.execute(text(ipfs_object_stmt),
contract_id=contract_id).fetchone()
if not ipfs_object:
raise ValueError('ipfs object does not exist.')
import requests
root_hash = ipfs_object['ipfs_hash']
meta_query_url = 'https://ipfs.io/ipfs/{root_hash}/meta.json'.format(
root_hash=root_hash)
req = requests.get(meta_query_url)
if req.status_code != 200:
raise ValueError('cannot find meta file')
db.statement(db.table.MUSIC_CONTRACTS).set(meta=req.text).where(
contract_id=contract_id).update(connection)
def _delete_draftbox(board_type):
user_id = request.user['user_id']
with db.engine_rdwr.connect() as connection:
db.statement(db.table.DRAFT_BOX).where(user_id=user_id, board_type=board_type).limit(1).delete(connection)
return helper.response_ok({'status': 'success'})
def _put_user_info():
"""
Modify user's info. Never change user_id, address.
"""
json_form = request.get_json(force=True, silent=True)
user_id = request.user['user_id']
profile_file_id = json_form.get('profile_file_id')
# only these columns can be changed
changable_columns = ['name', 'youtube_url', 'facebook_url', 'soundcloud_url', 'spotify_url']
change_value = {}
if isinstance(profile_file_id, int):
change_value.update({'profile_file_id': profile_file_id})
for column in changable_columns:
if column in json_form:
change_value.update({column: json_form.get(column)})
if column == 'name' and len(change_value['name']) < 1:
return helper.response_err(ERR.COMMON.TOO_SHORT_PARAMETER)
with db.engine_rdwr.connect() as connection:
try:
db.statement(db.table.USERS).set(**change_value).where(user_id=user_id).update(connection)
except IntegrityError:
return helper.response_err(ERR.COMMON.ALREADY_EXIST)
return helper.response_ok({'status': 'success'})
def _change_user_profile():
json_form = request.get_json(force=True, silent=True)
user_id = request.user['user_id']
profile_file_id = request.user.get('profile_file_id')
if not isinstance(profile_file_id, int):
return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY)
with db.engine_rdwr.connect() as connection:
db.statement(db.table.USERS).set(profile_file_id=profile_file_id).where(user_id=user_id).update(connection)
return helper.response_ok({'status': 'success'})
def _change_user_info(column_name, max_len, min_len=0):
json_form = request.get_json(force=True, silent=True)
user_id = request.user['user_id']
value = json_form.get(column_name)
if len(value) > max_len or len(value) < min_len:
return helper.response_err(ERR.COMMON.TOO_LONG_PARAMETER)
with db.engine_rdwr.connect() as connection:
try:
db.statement(db.table.USERS).set(**{column_name:value}).where(user_id=user_id).update(connection)
except IntegrityError:
return helper.response_err(ERR.COMMON.ALREADY_EXIST)
return helper.response_ok({'status': 'success'})
def _put_draft(draft_id):
user_id = request.user['user_id']
data = request.get_json(force=True, silent=True)
with db.engine_rdwr.connect() as connect:
draft_row = db.statement(db.table.POST_DRAFTS) \
.where(user_id=user_id, draft_id=draft_id).select(connect).fetchone()
if draft_row is None:
return helper.response_err(ERR.DRAFT.NOT_EXISTS)
db.statement(db.table.POST_DRAFTS).set(data=json.dumps(data)).where(
draft_id=draft_id).update(connect)
return helper.response_ok("OK")
def posts_query_stmt(board_type, **kwargs):
"""
Returns a statement for querying board posts.
:param board_type: the type of the posts for querying.
:return: a statement instance for querying board posts.
"""
table_name = db.table.board(board_type)
user_id = kwargs.get('user_id')
stmt = db.statement(table_name).columns('*')
if board_type == 'music':
# If the board type is music, only returns the root IPFS file hash, not recursively since the contract and
# IPFS files are 1:N relationship, but if only returning the root IPFS file, it can be 1:1 relationship.
stmt.columns('!music_contract', (db.table.MUSIC_CONTRACTS, '*'))
stmt.columns('!ipfs_file', (db.table.IPFS_FILES, '*'))
stmt.inner_join(db.table.MUSIC_CONTRACTS, 'post_id')
stmt.inner_join((db.table.IPFS_FILES, db.table.MUSIC_CONTRACTS),
('file_id', 'ipfs_file_id'))
stmt.inner_join(db.table.USERS, 'user_id').columns('!author',
(db.table.USERS, '*'))
stmt.where(status='posted')
if user_id and isinstance(user_id, int):
stmt.where(user_id=user_id)
return stmt
def _delete_draft(draft_id):
user_id = request.user['user_id']
with db.engine_rdwr.connect() as connect:
draft_row = db.statement(db.table.POST_DRAFTS) \
.where(user_id=user_id, draft_id=draft_id) \
.select(connect).fetchone()
if draft_row is None:
return helper.response_err(ERR.DRAFT.NOT_EXISTS)
elif draft_row['is_uploaded'] == 1:
return helper.response_err(ERR.DRAFT.ALREADY_UPLOADED)
else:
db.statement(
db.table.POST_DRAFTS).where(draft_id=draft_id).delete(connect)
return helper.response_ok("OK")
def register_sign_message_by_id(connection,
user_id,
platform_type,
message=None):
message = message or generate_random_sign_message()
message_id = db.statement(db.table.SIGN_MESSAGES).set(
user_id=user_id, platform_type=platform_type,
message=message).insert(connection).lastrowid
return message_id, message
def test_select(self):
"""
Test that db.statement test in select query
"""
query = pretty_sql(
db.statement(db.table.USERS).where(user_id='3').select(
None, False))
self.assertEqual(
query.strip(),
'SELECT * FROM `users` WHERE user_id = :where_user_id')
def _get_draft():
user_id = request.user['user_id']
board_type = request.args.get('boardType')
with db.engine_rdonly.connect() as connect:
draft_list = db.statement(db.table.POST_DRAFTS) \
.where(user_id=user_id, board_type=board_type) \
.select(connect)
return helper.response_ok(
[dict(draft_row) for draft_row in draft_list])
def _post_draft():
user_id = request.user['user_id']
board_type = request.args.get('boardType')
data = request.get_json(force=True, silent=True)
with db.engine_rdwr.connect() as connect:
draft_id = db.statement(
db.table.POST_DRAFTS).set(type=board_type,
user_id=user_id,
data=json.dumps(data),
version=1).insert(connect).lastrowid
return helper.response_ok(draft_id)
def _get_user(address):
"""
Returns an user information by wallet address.
If user(address) does not exist, give a random message for signing
"""
# if invalid address format, don't generate message
if not check_address_format(address):
return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG)
with db.engine_rdonly.connect() as connection:
user = db.statement(db.table.USERS).where(address=address).select(connection).fetchone()
return helper.response_ok(db.to_relation_model(user))
def test_update_with_where(self):
stmt = db.statement(db.table.USERS) \
.where(address='address') \
.set(name='name')
self.assertDictEqual(stmt.fetch_params, {
'set_name': 'name',
'where_address': 'address'
})
query = pretty_sql(stmt.update(None, False))
self.assertEqual(
query.strip(),
pretty_sql("""
UPDATE `users` SET name = :set_name WHERE address = :where_address
"""))
def _get_my_upload_board(board_type):
"""
:param board_type: 'sheet' or 'streaming'
:return: BasePost[]
"""
user = request.user
with db.engine_rdonly.connect() as connect:
query = db.statement(db.table.board('music')) \
.inner_join(db.table.MUSIC_CONTRACTS, 'post_id') \
.inner_join((db.table.MUSIC_PAYMENTS, db.table.MUSIC_CONTRACTS), 'contract_address') \
.where(type=board_type) \
.where_advanced(db.table.MUSIC_PAYMENTS, buyer_address=user['address']) \
.order('post_id', 'desc').select(connect)
return helper.response_ok([db.to_relation_model(row) for row in query])
def _get_draftbox(board_type):
user_id = request.user['user_id']
with db.engine_rdonly.connect() as connection:
draft_box_stmt = db.statement(db.table.DRAFT_BOX)\
.where(user_id=user_id, board_type=board_type).limit(1)\
.select(connection).fetchone()
if draft_box_stmt is None:
# if user's draftbox does not exist, return empty draft box
return helper.response_ok({
board_type: []
})
else:
return helper.response_ok(json.loads(draft_box_stmt['draft_box']))
def test_select_with_inner_join_and_columns(self):
"""
Test that db.statement joined column in select query
"""
query = pretty_sql(
db.statement(db.table.USERS).columns(
(db.table.board('music'), 'post_id')).inner_join(
db.table.board('music'),
'user_id').where(user_id='3').select(None, False))
self.assertEqual(
query.strip(),
pretty_sql("""
SELECT `mb`.post_id
FROM `users` `u`
INNER JOIN `music_board` `mb` ON (`mb`.user_id = `u`.user_id)
WHERE `u`.user_id = :where_u_user_id
"""))
def test_select_with_inner_join(self):
"""
Test that db.statement inner join test in select query
"""
stmt = db.statement(db.table.USERS) \
.inner_join(db.table.board('music'), 'user_id') \
.where(user_id='3')
self.assertDictEqual(stmt.fetch_params, {'where_u_user_id': '3'})
query = pretty_sql(stmt.select(None, False))
self.assertEqual(
query.strip(),
pretty_sql("""
SELECT `u`.*
FROM `users` `u`
INNER JOIN `music_board` `mb` ON (`mb`.user_id = `u`.user_id)
WHERE `u`.user_id = :where_u_user_id
"""))
def test_select_multiple_inner_join(self):
query = pretty_sql(
db.statement(db.table.board('music')).columns(
'*', '!music_contract',
(db.table.MUSIC_CONTRACTS, '*')).inner_join(
db.table.MUSIC_CONTRACTS, 'post_id').left_join(
(db.table.MUSIC_PAYMENTS, db.table.MUSIC_CONTRACTS),
'contract_address').where(post_id='5').select(
None, False))
self.assertEqual(
query.strip(),
pretty_sql("""
SELECT `mb`.*, '!music_contract', `mc`.*
FROM `music_board` `mb`
INNER JOIN `music_contracts` `mc` ON (`mc`.post_id = `mb`.post_id)
LEFT JOIN `music_payments` `mp` ON (`mp`.contract_address = `mc`.contract_address)
WHERE `mb`.post_id = :where_mb_post_id
"""))
def test_multiple_where_advanced(self):
stmt = db.statement(db.table.USERS) \
.inner_join(db.table.board('music'), 'user_id') \
.inner_join(db.table.board('video'), 'user_id') \
.where_advanced(db.table.board('music'), post_id=3) \
.where_advanced(db.table.board('video'), post_id=5)
self.assertDictEqual(stmt.fetch_params, {
'where_mb_post_id': 3,
'where_vb_post_id': 5
})
query = pretty_sql(stmt.select(None, False))
self.assertEqual(
query.strip(),
pretty_sql("""
SELECT `u`.* FROM `users` `u`
INNER JOIN `music_board` `mb` ON (`mb`.user_id = `u`.user_id)
INNER JOIN `video_board` `vb` ON (`vb`.user_id = `u`.user_id)
WHERE `mb`.post_id = :where_mb_post_id AND `vb`.post_id = :where_vb_post_id
"""))
def _post_music_purchase():
"""
Receive txHash from client. This hash is about music purchase.
For simplify and security, only accept txHash (any of other extra information is not received)
That is, no contract address and price are given. (After mined, automatically saved the information)
See works/update_payments.py
"""
json_form = request.get_json(force=True, silent=True)
tx_hash = json_form.get('tx_hash')
requester = request.user['address']
if not txhash_validation(tx_hash):
return helper.response_err(ERR.COMMON.INVALID_TX_HASH)
with db.engine_rdonly.connect() as connection:
payment_id = db.statement(db.table.MUSIC_PAYMENTS).set(
# Set to lowercase
tx_hash=tx_hash.lower(),
requester=requester).insert(connection).lastrowid
return helper.response_ok({'payment_id': payment_id, 'tx_hash': tx_hash})
def _modify_user():
"""
Modify user's information.
"""
json_form = request.get_json(force=True, silent=True)
user_name = json_form.get('name')
address = request.user['address']
if not isinstance(user_name, str):
return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG)
with db.engine_rdwr.connect() as connection:
result = db.statement(db.table.USERS) \
.where(address=address) \
.set(name=user_name) \
.update(connection)
if result.row_count:
return helper.response_ok({'status': 'success'})
else:
return helper.response_err(ER.ALREADY_EXIST, ER.ALREADY_EXIST_MSG)
def generate_jwt_token(connection,
web3,
address,
signature,
protocol='eth',
**kwargs):
"""
Validate the user signature and if authenticated, generate a new JWT token for the user.
:param connection: database connection.
:param web3: web3(ethereum) instance.
:param address: the wallet address of the user.
:param signature_version: signature creation type (Trezur, Metamask signature creation type)
:param signature: message signed by user's wallet.
:param default_user_name: if not-None-type-value and user_id not exists at the address, this function creates user
:return: return JWT token if authenticated and generated, nor None if not authenticated.
"""
import arrow
import datetime
from config import AppConfig
from modules.sign_message import (generate_random_sign_message,
register_sign_message_by_id,
expire_sign_message)
from modules.signature import validate_signature
from modules.sign_message import get_message_for_user
# if first sign in, get message not by sign message id since db doesn't have it
signature_version = kwargs.get('signature_version')
default_user_name = kwargs.get('default_user_name', None)
platform_type = kwargs.get('platform_type')
checksum_address = web3.toChecksumAddress(
address) if protocol == 'eth' else address
if platform_type not in PLATFORM_TYPES:
return None
"""
Get sign message and its private key. Private key is not account private key, but it's the
internally saved random bytes in database for random hashing. The private key is used for
the hash value in JWT token.
get random-generated sign message from database (get by message_id),
"""
sign_message = get_message_for_user(address,
always_new=False,
protocol=protocol)
tz = arrow.now(AppConfig.timezone).datetime
"""
Validate the signature. If fail to validate, never generate JWT token.
If succeed to validate,
1. Create an user if the address is not registered in database
2. Generate a new JWT token
"""
# if validation failed, don't generate JWT token
if not validate_signature(web3,
address,
sig_obj={
'purpose': 'Login to Muzika!',
'message': sign_message,
'signature': signature,
'signature_version': signature_version
},
protocol=protocol):
return None
# get user id by address
user_id = db.statement(db.table.USERS).where(
address=checksum_address).select(connection).fetchone()
user_id = user_id['user_id'] if user_id is not None else None
# if user(wallet) is not registered yet, register it with empty name
if not user_id:
if default_user_name is not None:
user_id = db.statement(db.table.USERS).set(
address=checksum_address,
name=default_user_name).insert(connection).lastrowid
else:
return None
# create a new sign message
sign_message_id, _ = register_sign_message_by_id(connection, user_id,
platform_type,
sign_message)
private_key = generate_random_sign_message()
# after checking validation, authenticated, so update sign message
db.statement(db.table.SIGN_MESSAGES) \
.set(private_key=private_key) \
.where(message_id=sign_message_id, user_id=user_id).update(connection)
# JWT payload
payload = {
'hash':
hashlib.md5(
"{}-{}-{}-{}".format(user_id, sign_message_id, checksum_address,
private_key).encode('utf-8')).hexdigest(),
'jti':
'{}-{}'.format(address, sign_message_id),
'iss':
AppConfig.issuer,
'aud':
AppConfig.issuer,
'iat':
tz - datetime.timedelta(seconds=60),
'exp':
tz + datetime.timedelta(days=30)
}
# if validated, expire the message, so never use the sign message anymore.
expire_sign_message(address)
# return JWT token
return jwt.encode(payload=payload,
key=JWT_SECRET_KEY,
algorithm='HS256',
headers={
'jti': payload['jti']
}).decode('utf-8')
def put(self, connection, name, value, user_id=None, file_type=None, content_type='', hash=None, expired_at=None):
from time import time
import os
import hashlib
# get config from member variable or parameter
file_type = file_type or self.file_type
config = self.config or aws_config[file_type]
# check file extension
valid_exts = config['ext']
_, ext = os.path.splitext(name)
ext = ext.split('.')[-1]
if ext not in valid_exts:
# if file extension is not allowed, don't put it to the bucket
return None
# get file size and check limitation
file_size_limit = config['file_size_limit']
file_len = len(value)
if file_len > file_size_limit:
# if file size is over the limitation, don't put it to the bucket
return None
# get configuration
bucket = config['bucket']
directory = config['directory']
# if hash value is not set, calculate file hash value
file_hash = hash or hashlib.sha256(value).hexdigest()
object_key = '{}-{}.{}'.format(str(int(time() * 100)), file_hash, ext)
if directory:
object_key = ''.join([directory, '/', object_key])
# insert the file information into the db
file_id = db.statement(db.table.FILES).set(
user_id=user_id,
type=file_type,
bucket=bucket,
object_key=object_key,
file_name=name,
file_size=file_len,
hash=file_hash,
expired_at=expired_at
).insert(connection).lastrowid
s3 = session.client('s3')
# upload to the S3 bucket
s3.put_object(
Bucket=bucket,
Key=object_key,
Body=value,
ContentType=content_type
)
return {
'file_id': file_id,
'file_type': file_type,
'file_hash': file_hash,
'file_size': file_len,
's3_bucket': bucket,
'object_key': object_key,
'content_type': content_type
}
