def register_object(connection, ipfs_hash, file_type, aes_key=None, name=None, tracking=False, **kwargs): ipfs_object = { 'file_type': file_type, 'ipfs_hash': ipfs_hash, 'encrypted': True if aes_key else False, 'name': name, 'status': 'pending' } ipfs_file_id = db.Statement(db.table.IPFS_FILES).set( file_type=file_type, ipfs_hash=ipfs_hash, encrypted=True if aes_key else False, name=name, status='pending').insert(connection).lastrowid # if encrypted, add AES key to the private table if aes_key: db.Statement(db.table.IPFS_FILES_PRIVATE).set( file_id=ipfs_file_id, aes_key=aes_key).insert(connection) if tracking: ipfs_object.update({'file_id': ipfs_file_id}) if aes_key: ipfs_object.update({'aes_key': aes_key}) track_object(connection, ipfs_object=ipfs_object, **kwargs) return ipfs_file_id
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 _modify_board_comment(board_type, comment_id): json_form = request.get_json(force=True, silent=True) content = json_form.get('content') user_id = request.user['user_id'] if not isinstance(content, str): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) table_name = db.table.comment(board_type) # if unknown board type if not table_name: return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) statement = db.Statement(table_name).set(content=content)\ .where(user_id=user_id, comment_id=comment_id, status='posted') with db.engine_rdwr.connect() as connection: updated = statement.update(connection).rowcount if updated: return helper.response_ok({'status': 'success'}) else: return helper.response_err(ER.AUTHENTICATION_FAILED, ER.AUTHENTICATION_FAILED_MSG)
def _delete_post(board_type, post_id): """ Delete a post that user has. """ user_id = request.user['user_id'] table_name = db.table.board(board_type) # if unknown board type if not table_name: return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) statement = db.Statement(table_name)\ .set(status='deleted')\ .where(post_id=post_id, user_id=user_id, status='posted') with db.engine_rdwr.connect() as connection: deleted = statement.update(connection).rowcount # if the post does not exist or is not the user's post if not deleted: return helper.response_err(ER.AUTHENTICATION_FAILED, ER.AUTHENTICATION_FAILED_MSG) return helper.response_ok({'status': 'success'})
def _post_board_comment(board_type, post_id): json_form = request.get_json(force=True, silent=True) content = json_form.get('content') user_id = request.user['user_id'] if not isinstance(content, str): return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY) 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).set(user_id=user_id, post_id=post_id, content=content) with db.engine_rdwr.connect() as connection: try: comment_id = statement.insert(connection).lastrowid except IntegrityError: # if failed to insert return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY) return helper.response_ok({'comment_id': comment_id})
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(self, connection, file_id): s3 = session.client('s3') statement = db.Statement(db.table.FILES).where(file_id=file_id).limit(1) file = db.to_relation_model(statement.select(connection).fetchone()) # if file does not exist, if file is None: return None return s3.get_object(Bucket=file['bucket'], Key=file['object_key'])
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 _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(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) statement = db.Statement(table_name).where(post_id=post_id, status='posted') tags_statement = db.Statement( db.table.tags(board_type)).columns('name').where(post_id=post_id) with db.engine_rdonly.connect() as connection: post = statement.select(connection).fetchone() tags = tags_statement.select(connection) # if the post does not exist, if post is None: return helper.response_err(ER.NOT_EXIST, ER.NOT_EXIST_MSG) post = db.to_relation_model(post) post.update({'tags': [tag['name'] for tag in tags]}) return helper.response_ok(post)
def _like_comment(board_type, comment_id): user_id = request.user['user_id'] board_table_name = db.table.board(board_type) comment_like_table_name = db.table.comment_like(board_type) if not board_table_name or not comment_like_table_name: return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY) like_statement = db.Statement(comment_like_table_name).set(comment_id=comment_id, user_id=user_id) with db.engine_rdwr.begin() as connection: try: like_statement.insert(connection) except IntegrityError: return helper.response_err(ERR.COMMON.ALREADY_EXIST) return helper.response_ok({'status': 'success'})
def _cancel_like_post(board_type, post_id): user_id = request.user['user_id'] board_table_name = db.table.board(board_type) like_table_name = db.table.like(board_type) if not board_table_name or not like_table_name: return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY) like_statement = db.Statement(like_table_name).where(post_id=post_id, user_id=user_id) with db.engine_rdwr.begin() as connection: deleted = like_statement.delete(connection).rowcount if deleted: return helper.response_ok({'status': 'success'}) else: return helper.response_err(ERR.COMMON.NOT_EXIST)
def _delete_board_comment(board_type, comment_id): user_id = request.user['user_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).set(status='deleted')\ .where(user_id=user_id, comment_id=comment_id, status='posted') with db.engine_rdwr.connect() as connection: deleted = statement.update(connection).rowcount if not deleted: return helper.response_err(ERR.COMMON.AUTHENTICATION_FAILED) return helper.response_ok({'status': 'success'})
def _post_board_subcomment(board_type, parent_comment_id): json_form = request.get_json(force=True, silent=True) content = json_form.get('content') user_id = request.user['user_id'] if not isinstance(content, str) or not isinstance(parent_comment_id, int): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) table_name = db.table.comment(board_type) # if unknown board type if not table_name: return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) statement = db.Statement(table_name).set( user_id=user_id, parent_comment_id=parent_comment_id, content=content) parent_update_statement = """ UPDATE `{}` SET `reply_count` = `reply_count` + 1 WHERE `comment_id` = :parent_comment_id LIMIT 1 """.format(table_name) with db.engine_rdwr.begin() as connection: try: comment_id = statement.insert(connection).lastrowid except IntegrityError: # if failed to insert return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) connection.execute(text(parent_update_statement), parent_comment_id=parent_comment_id) return helper.response_ok({'comment_id': comment_id})
def _upload_paper_contract(): """ Uploads a paper contract. json form: { "tx_hash": tx-hash value, "name": paper name, "file_id": file id (optional), } The background process checks the transaction is mined, and if then, save the contract address. It must checks the contract file IPFS hash, seller address (seller's user id) and if invalid, remove the requested paper from the database for paper contract integrity. Also, when the transaction is not mined for over 10 minutes, ignore it too. """ user_id = request.user['user_id'] json_form = request.get_json(force=True, silent=True) tx_hash = json_form.get('tx_hash') file_id = json_form.get('file_id') name = json_form.get('name') statement = db.Statement(db.table.PAPERS).set(user_id=user_id, tx_hash=tx_hash, name=name) # When the transaction is mined, so the created contract would have file hash value. If the server and storage # (s3 bucket) have the file hash, the background process gets file id from database and update the file id. If # not, remove the paper contract in the database. if file_id: statement.set(file_id=file_id) with db.engine_rdwr.connect() as connection: statement.insert(connection) return helper.response_ok({'status': 'success'})
def _post_to_community(board_type): """ Uploads a post to the community. """ json_form = request.get_json(force=True, silent=True) title = json_form.get('title') content = json_form.get('content') tags = json_form.get('tags', []) user_id = request.user['user_id'] if not isinstance(title, str) or not isinstance( content, str) or not isinstance(tags, (list, type(None))): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) # remove duplication tags = set(tags) # define columns that all boards have table_name = db.table.board(board_type) # if unknown board type if not table_name: return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) tag_insert_query_str = """ INSERT INTO `{}` (`post_id`, `name`) VALUES(:post_id, :tag_name) """.format(db.table.tags(board_type)) statement = db.Statement(table_name).set(user_id=user_id, title=title, content=content) if board_type == 'community': # community needs no additional columns pass elif board_type == 'video': # video needs additional columns (youtube video id and genre) genre = json_form.get('genre') youtube_video_id = parse_youtube_id(json_form.get('youtube_url')) # if parameter is invalid or does not exist if not isinstance(genre, str) or not isinstance(youtube_video_id, str): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) statement.set(genre=genre, youtube_video_id=youtube_video_id) elif board_type == 'sheet': # sheet needs additional columns for file file_id = json_form.get('file_id') # if parameter is invalid or does not exist if not isinstance(file_id, int): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) statement.set(file_id=file_id) with db.engine_rdwr.connect() as connection: post_id = statement.insert(connection).lastrowid # if tags exist, insert tags if tags: tag_multi_params = [{ 'post_id': post_id, 'tag_name': tag_name } for tag_name in tags] connection.execute(text(tag_insert_query_str), *tag_multi_params) return helper.response_ok({'post_id': post_id})
def _modify_post(board_type, post_id): """ Modify a post that the user has. """ json_form = request.get_json(force=True, silent=True) title = json_form.get('title') content = json_form.get('content') tags = json_form.get('tags', []) user_id = request.user['user_id'] # if invalid parameter type if not isinstance(title, str) or not isinstance( content, str) or not isinstance(tags, (type(None), list)): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) # remove duplication tags = set(tags) table_name = db.table.board(board_type) # if unknown board type if not table_name: return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) # construct a default column that all boards have statement = db.Statement(table_name)\ .set(title=title, content=content)\ .where(post_id=post_id, user_id=user_id, status='posted') # queries for inserting or deleting tags when modified tag_insert_query_str = """ INSERT INTO `{}` (`post_id`, `name`) VALUES(:post_id, :tag_name) """.format(db.table.tags(board_type)) tag_delete_query_str = """ DELETE FROM `{}` WHERE `post_id` = :post_id AND `name` IN :delete_tags """.format(db.table.tags(board_type)) if board_type == 'community': # community needs no additional columns pass elif board_type == 'video': # video needs additional columns (youtube video id and genre) genre = json_form.get('genre') youtube_video_id = parse_youtube_id(json_form.get('youtube_url')) if not isinstance(genre, str) or not isinstance(youtube_video_id, str): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) statement.set(youtube_video_id=youtube_video_id, genre=genre) elif board_type == 'sheet': # sheet needs additional columns for file file_id = json_form.get('file_id') if not isinstance(file_id, int): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) statement.set(file_id=file_id) with db.engine_rdwr.connect() as connection: modified = statement.update(connection).rowcount # if the post does not exist or is not the user's post if not modified: return helper.response_err(ER.AUTHENTICATION_FAILED, ER.AUTHENTICATION_FAILED_MSG) current_tags = db.Statement(db.table.tags(board_type)).columns('name')\ .where(post_id=post_id).select(connection) current_tags = set([tag['name'] for tag in current_tags]) # get tags for deletion delete_tags = current_tags - tags # get tags for insert insert_tags = tags - current_tags # insert new tags if insert_tags: tag_multi_params = [{ 'post_id': post_id, 'tag_name': tag_name } for tag_name in insert_tags] connection.execute(text(tag_insert_query_str), *tag_multi_params) # remove tags for deletion if delete_tags: connection.execute(text(tag_delete_query_str), post_id=post_id, delete_tags=delete_tags) return helper.response_ok({'status': 'success'})
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 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)
def _get_paper_file(contract_address): """ Downloads a paper from a contract. json form: { "public_key": public key for encryption } """ json_form = request.get_json(force=True, silent=True) public_key = json_form.get('public_key') # not support downloading without encryption if not isinstance(public_key, str): return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) # parse public key try: public_key = RSA.import_key(public_key) except ValueError: return helper.response_err(ER.INVALID_REQUEST_BODY, ER.INVALID_REQUEST_BODY_MSG) user_address = request.user['address'] web3 = get_web3() contract = MuzikaPaperContract(web3, contract_address=contract_address) if not contract.purchased(user_address): # if the user hasn't purchased this paper return helper.response_err(ER.AUTHENTICATION_FAILED, ER.AUTHENTICATION_FAILED_MSG) paper_statement = db.Statement( db.table.PAPERS).where(contract_address=contract_address).limit(1) with db.engine_rdonly.connect() as connection: paper = db.to_relation_model( paper_statement.select(connection).fetchone()) # if not supporting this paper contract if not paper: return helper.response_err(ER.NOT_EXIST, ER.NOT_EXIST_MSG) file_id = paper['file_id'] # if not having file if not file_id: # TODO: return helper.response_err(ER.NOT_EXIST, ER.NOT_EXIST_MSG) else: # get ipfs file hash file_hash = paper['ipfs_file_hash'] # download from bucket bucket = MuzikaS3Bucket() s3_response = bucket.get(connection, file_id) file_blob = s3_response['Body'].read() block = Block(data=file_blob, hash=file_hash) block_request = BlockRequest(block_hash=file_hash, public_key=public_key) encrypted_block = block_request.encrypt(block) response_body = encrypted_block.encrypted_key + encrypted_block.data response = make_response(response_body) response.headers['content-length'] = len(response_body) response.headers['content-type'] = 'text/plain' return response
def track_object(connection, ipfs_file_id=None, ipfs_object=None, **kwargs): if ipfs_file_id: object_query = """ SELECT `f`.*, `p`.`aes_key` FROM `{}` `f` INNER JOIN `{}` `p` ON (`f`.`file_id` = `p`.`file_id`) WHERE `f`.`file_id` = :ipfs_file_id LIMIT 1 """.format(db.table.IPFS_FILES, db.table.IPFS_FILES_PRIVATE) ipfs_object = connection.execute(text(object_query), ipfs_file_id=ipfs_file_id).fetchone() # if object does not exist if not ipfs_object: return ipfs_object = db.to_relation_model(ipfs_object) ipfs_file_id = ipfs_object['file_id'] root_ipfs_hash = ipfs_object['ipfs_hash'] file_type = ipfs_object['file_type'] aes_key = ipfs_object.get('aes_key') name = ipfs_object.get('name', '') root_id = kwargs.get('root_id') or ipfs_file_id if name == '/' or name is None: name = '' ipfs = RelayIpfs().get_connection() try: # query object links. If timeout, it will raise Timeout Error object_links = ipfs.ls(root_ipfs_hash, opts={'timeout': kwargs.get('timeout', '30s')})['Objects'][0]['Links'] except ipfsapi.exceptions.ErrorResponse: # if timeout error, set status to "disabled" db.Statement(db.table.IPFS_FILES)\ .set(status='disabled')\ .where(file_id=ipfs_file_id)\ .update(connection) return # if no object links, this object is just a file if not len(object_links): db.Statement(db.table.IPFS_FILES)\ .set(ipfs_object_type='file', root_id=root_id, status='success')\ .where(file_id=ipfs_file_id)\ .update(connection) # if having object links, this object is a directory and insert files else: db.Statement(db.table.IPFS_FILES)\ .set(ipfs_object_type='directory', root_id=root_id, name=name if name else '/', status='success')\ .where(file_id=ipfs_file_id)\ .update(connection) for link in object_links: link_object = { 'parent_id': ipfs_file_id, 'file_type': file_type, 'ipfs_hash': link['Hash'], 'encrypted': True if aes_key else False, 'name': '/'.join([name, link['Name']]), 'root_id': root_id } # if the linked object is directory if link['Type'] == 1: link_object.update({ 'ipfs_object_type': 'directory', 'status': 'pending' }) # track recursively # track_object(connection, ipfs_object=link_object, **kwargs) # if the linked object is file (Type == 2) else: link_object.update({ 'file_size': link['Size'], 'ipfs_object_type': 'file', 'status': 'success' }) # db.Statement(db.table.IPFS_FILES).set(**link_object).insert(connection) # insert an directory object in this IPFS object link_object.update({ 'file_id': db.Statement(db.table.IPFS_FILES).set( **link_object).insert(connection).lastrowid }) # if aes_key exists, insert AES KEY into the private table if aes_key: db.Statement(db.table.IPFS_FILES_PRIVATE)\ .set(file_id=link_object['file_id'], aes_key=aes_key)\ .insert(connection) link_object.update({'aes_key': aes_key}) # if directory, track recursively if link['Type'] == 1: kwargs.update({'root_id': root_id}) track_object(connection, ipfs_object=link_object, **kwargs)
def _post_to_community(board_type): """ Uploads a post to the community. """ json_form = request.get_json(force=True, silent=True) title = json_form.get('title') content = json_form.get('content') tags = json_form.get('tags', []) user_id = request.user['user_id'] if not isinstance(title, str) or not isinstance( content, str) or not isinstance(tags, (list, type(None))): return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY) # remove duplication tags = set(tags) # define columns that all boards have table_name = db.table.board(board_type) # if unknown board type if not table_name: return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY) tag_insert_query_str = """ INSERT INTO `{}` (`post_id`, `name`) VALUES(:post_id, :tag_name) """.format(db.table.tags(board_type)) post_statement = db.Statement(table_name).set(user_id=user_id, title=title, content=content) if board_type == 'community': # community needs no additional columns pass elif board_type == 'video': # video needs additional columns (youtube video id and genre) genre = json_form.get('genre') youtube_video_id = parse_youtube_id(json_form.get('youtube_url')) # if parameter is invalid or does not exist if not isinstance(genre, str) or not isinstance(youtube_video_id, str): return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY) post_statement.set(genre=genre, youtube_video_id=youtube_video_id) elif board_type == 'music': post_type = json_form.get('type') # if post type is not valid if post_type not in MUSIC_POST_TYPE: return helper.response_err(ERR.COMMON.INVALID_REQUEST_BODY) post_statement.set(type=post_type) with db.engine_rdwr.connect() as connection: post_id = post_statement.insert(connection).lastrowid if board_type == 'music': music_contract = json_form.get('music_contract') tx_hash = music_contract.get('tx_hash') # if tx_hash already in music contracts, tx_hash_exists = db.Statement(db.table.MUSIC_CONTRACTS).columns('"_"') \ .where(tx_hash=tx_hash).limit(1).select(connection).fetchone() if tx_hash_exists: return helper.response_err(ERR.COMMON.TX_HASH_DUPLICATED) # if the board type is music, register IPFS files and contracts ipfs_file_id = ipfs.register_object( connection=connection, ipfs_hash=music_contract.get('ipfs_file_hash'), file_type=music_contract.get('file_type', 'music'), aes_key=music_contract.get('aes_key')) contract_statement = db.Statement(db.table.MUSIC_CONTRACTS).set( ipfs_file_id=ipfs_file_id, tx_hash=tx_hash, post_id=post_id) contract_id = contract_statement.insert(connection).lastrowid # update IPFS file info later tasks.update_contract_files.delay(ipfs_file_id, contract_id) # if tags exist, insert tags if tags: tag_multi_params = [{ 'post_id': post_id, 'tag_name': tag_name } for tag_name in tags] connection.execute(text(tag_insert_query_str), *tag_multi_params) return helper.response_ok({'post_id': post_id})
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)