def _get_board_comment(board_type, comment_id):
table_name = db.table.comment(board_type)
# if unknown board type
if not table_name:
return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY)
statement = db.Statement(table_name).where(comment_id=comment_id,
status='posted')
subcomments_query_str = """
SELECT `c`.*, '!user', `u`.* FROM `{}` `c`
INNER JOIN `users` `u`
ON (`u`.`user_id` = `c`.`user_id`)
WHERE `parent_comment_id` = :parent_comment_id AND `status` = :comment_status
ORDER BY `c`.`created_at` ASC
""".format(table_name)
with db.engine_rdonly.connect() as connection:
comment = statement.select(connection).fetchone()
# if the comment does not exist
if not comment:
return helper.response_err(ERR.COMMON.NOT_EXIST)
comment = db.to_relation_model(comment)
subcomments = connection.execute(
text(subcomments_query_str),
parent_comment_id=comment['comment_id'],
comment_status='posted')
comment.update({'subcomments': db.to_relation_model_list(subcomments)})
return helper.response_ok(comment)
def _get_community_post(board_type, post_id):
table_name = db.table.board(board_type)
# if unknown board type
if not table_name:
return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY)
if board_type == 'music':
# if board type is music, show with related music contracts and IPFS file.
additional_columns = """
, '!music_contract', `mc`.*
"""
inner_join = """
LEFT JOIN `{}` `mc`
ON (`mc`.`post_id` = `b`.`post_id`)
""".format(db.table.MUSIC_CONTRACTS, db.table.IPFS_FILES)
else:
additional_columns = ''
inner_join = ''
post_query_statement = """
SELECT `b`.* {} FROM `{}` `b`
{}
WHERE `b`.`post_id` = :post_id AND `b`.`status` = :status
""".format(additional_columns, table_name, inner_join)
ipfs_files_query_statement = """
SELECT * FROM `ipfs_files` `if`
INNER JOIN `music_contracts` `mc`
ON (`mc`.`ipfs_file_id` = `if`.`file_id` OR `mc`.`ipfs_file_id` = `if`.`root_id`)
WHERE `mc`.`post_id` = :post_id
"""
tags_statement = db.Statement(
db.table.tags(board_type)).columns('name').where(post_id=post_id)
with db.engine_rdonly.connect() as connection:
post = connection.execute(text(post_query_statement),
post_id=post_id,
status='posted').fetchone()
# if the post does not exist,
if post is None:
return helper.response_err(ERR.COMMON.NOT_EXIST)
post = db.to_relation_model(post)
if board_type == 'music':
ipfs_files = db.to_relation_model_list(
connection.execute(text(ipfs_files_query_statement),
post_id=post_id))
post['music_contract'].update({'ipfs_file': ipfs_files})
tags = tags_statement.select(connection)
post.update({'tags': [tag['name'] for tag in tags]})
return helper.response_ok(post)
def _get_comments_likes(user_id, board_type):
"""
Gets an user's comment likes list
"""
table_name = db.table.comment_like(board_type)
if not table_name:
return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY)
with db.engine_rdonly.connect() as connection:
comment_like_statement = db.Statement(table_name).where(user_id=user_id)
return helper.response_ok(db.to_relation_model_list(comment_like_statement.select(connection)))
def update_payments():
"""
All of transaction for purchase of music is not reliable,
which can have price, buyer or contract address.
Therefore we have to wait for transaction to be mined with txHash and get receipt
of the txHash, then all of information (price, buyer etc.) are obtained by the receipt.
Then, save the information to our database
Status
pending : Ready for mined transaction given hash.
success : The transaction is valid and accepted in the blockchain.
failed : The transaction is failed because of out of gas, not enough muzika or any other reason.
invalid : The transaction is invalid (e.g. this transaction is not for purchase of music)
disabled : The transaction is not found in the blockchain (That is, timeout of waiting for transaction)
"""
web3 = get_web3()
purchase_event_name = web3.sha3(b'Purchase(address,uint256)')
# query for updating status of timeout transaction
update_query_statement = """
UPDATE `{}` SET `status` = 'disabled'
WHERE
`status` = 'pending'
AND `created_at` < NOW() - INTERVAL 3 HOUR
""".format(db.table.MUSIC_PAYMENTS)
# query for deleting timeout transaction
delete_query_statement = """
DELETE FROM `{}`
WHERE
`status` = 'disabled'
AND `created_at` < NOW() - INTERVAL 6 HOUR
""".format(db.table.MUSIC_PAYMENTS)
with db.engine_rdwr.connect() as connection:
# get list of payment history that is not mined (wait)
payments = db.to_relation_model_list(
db.Statement(db.table.MUSIC_PAYMENTS).where(
status='pending').select(connection))
def invalid_payment(__payment):
# this transaction is not valid
db.Statement(db.table.MUSIC_PAYMENTS)\
.set(status='invalid')\
.where(payment_id=__payment['payment_id'])\
.update(connection)
for payment in payments:
try:
receipt = web3.eth.waitForTransactionReceipt(
transaction_hash=payment['tx_hash'], timeout=1)
except Timeout:
continue
if not receipt:
continue
if receipt.status == 0:
# this transaction is failed
db.Statement(db.table.MUSIC_PAYMENTS)\
.set(status='failed')\
.where(payment_id=payment['payment_id'])\
.update(connection)
continue
"""
Because we use approval function to purchase music, value of `to` in
transaction is MuzikaCoin contract's address
MuzikaCoin.address == tx['to']
Contract address is equal to event.address
"""
purchase_events = [
event for event in receipt.logs
if event.topics[0] == purchase_event_name
]
if len(purchase_events) == 0:
# Purchase event is not emitted
invalid_payment(payment)
continue
event = purchase_events[-1]
contract_address = event.address
contract_exists = db.Statement(db.table.MUSIC_CONTRACTS).columns('"_"')\
.where(contract_address=contract_address, status='success')\
.limit(1).select(connection).fetchone()
if contract_exists is None:
# Contract is not exists in our database. It is not a contract for muzika platform
invalid_payment(payment)
continue
"""
Contract is valid for our platform. Use it!
"""
# price is equal to event.data (type is str)
price = event.data
# buyer is equal to event.topics[1]
# structure of event is `Purchase(address,uint256)`
# type is HexBytes
buyer = web3.toChecksumAddress('0x' + event.topics[1].hex()[-40:])
db.Statement(db.table.MUSIC_PAYMENTS)\
.set(buyer_address=buyer,
contract_address=contract_address,
price=price,
status='success')\
.where(payment_id=payment['payment_id'])\
.update(connection)
# execute update query
connection.execute(text(update_query_statement))
# execute delete query (timeout is doubled)
connection.execute(text(delete_query_statement))
def update_contracts():
web3 = get_web3()
complete_delete_query_statement = """
DELETE FROM `{}`
WHERE `status` NOT IN :delete_status AND `created_at` < NOW() - INTERVAL 6 HOUR
""".format(db.table.MUSIC_CONTRACTS)
# query for deleting music contracts and their IPFS files list if not mined
delete_query_statement = """
UPDATE `{}` `mc`
LEFT JOIN `{}` `mb`
ON (`mb`.`post_id` = `mc`.`post_id`)
SET
`mb`.`status` = :set_board_status,
`mc`.`status` = :set_contract_status
WHERE
`mc`.`status` = :delete_status AND `mc`.`created_at` < NOW() - INTERVAL 3 HOUR
""".format(db.table.MUSIC_CONTRACTS, db.table.board('music'))
transaction_query_statement = """
SELECT `mc`.*, `u`.`address`
FROM
`{}` `mc`
LEFT JOIN `{}` `mb`
ON (`mb`.`post_id` = `mc`.`post_id`)
LEFT JOIN `{}` `u`
ON (`u`.`user_id` = `mb`.`user_id`)
WHERE
`mc`.`status` = :track_status
""".format(db.table.MUSIC_CONTRACTS, db.table.board('music'), db.table.USERS)
with db.engine_rdwr.connect() as connection:
# DELETE contracts completely that are not mined over specific time
connection.execute(text(complete_delete_query_statement),
delete_status=['success'])
# DELETE contracts that are not mined over specific time
connection.execute(text(delete_query_statement),
set_board_status='deleted',
set_contract_status='disabled',
delete_status='pending')
# QUERY not mined contracts
contracts = db.to_relation_model_list(
connection.execute(text(transaction_query_statement), track_status='pending')
)
# get original bytecode of the paper contract
contract_handler = MuzikaContractHandler()
payment_interface_contract = contract_handler.get_interface('Dispatcher')
payment_contract = contract_handler.get_interface('MuzikaPaperContract')
contract_bytecode = payment_contract['bytecode'][:-68]
contract_bytecode = contract_bytecode.replace(
'__LibPaperPaymentInterface______________',
payment_interface_contract['networks'][web3.version.network]['address'][2:]
)
for contract in contracts:
contract_status = 'success'
board_status = 'posted'
try:
tx_receipt = web3.eth.waitForTransactionReceipt(transaction_hash=contract['tx_hash'], timeout=1)
except Timeout:
# if failed to get contract (not mined or fake transaction), check next contract
continue
if tx_receipt:
# TODO : validate the contract
try:
contract_address = tx_receipt['contractAddress']
tx_contract = MuzikaPaperContract(web3, contract_address=contract_address)
seller_address = tx_contract.get_seller()
tx = web3.eth.getTransaction(contract['tx_hash'])
# if tx data is invalid, set contract status to invalid and board status to deleted
if tx.input[:len(contract_bytecode)] != contract_bytecode:
contract_status = 'invalid'
board_status = 'deleted'
# Check seller.
# If the contract is derived from music post but the author of it is different from the real
# contract owner, set status to success but delete post
if contract['address'] and seller_address != contract['address']:
board_status = 'deleted'
except ValueError as e:
# if ValueError occurs (may be wrong transaction), set invalid contracvt and delete post
contract_status = 'invalid'
board_status = 'deleted'
if board_status == 'deleted':
db.Statement(db.table.board('music'))\
.set(status='deleted')\
.where(post_id=contract['post_id'])\
.update(connection)
if contract_status == 'success':
db.Statement(db.table.MUSIC_CONTRACTS)\
.set(contract_address=contract_address, seller_address=seller_address, status='success')\
.where(contract_id=contract['contract_id'])\
.update(connection)
else:
db.Statement(db.table.MUSIC_CONTRACTS)\
.set(status=contract_status)\
.where(contract_id=contract['contract_id'])\
.update(connection)