def task_init_db_if_required(database_url, task_num=2): task_message = fmt_task_message( 'Initialising db if required', emoji_code_point=u'\U0001F50C', task_num=task_num) click.echo(task_message) init_tables(database_url, Base.metadata)
def raw_sql(ctx, sql): """Execute raw sql query""" engine = ctx.obj['engine'] database_url = ctx.obj['database_url'] metadata = ctx.obj['metadata'] from sqlalchemy.sql import text # init tables first init_tables(database_url, metadata) stmt = text(sql) with engine.connect() as conn: results = conn.execute(stmt).fetchall() click.echo(json.dumps(results))
def insert_blocks(ctx, blocks): """Insert blocks into the database""" engine = ctx.obj['engine'] database_url = ctx.obj['database_url'] metadata = ctx.obj['metadata'] # init tables first init_tables(database_url, metadata) # configure session Session.configure(bind=engine) session = Session() add_blocks(blocks, session)
def last_block(ctx): """Return the highest block stored in the database""" engine = ctx.obj['engine'] database_url = ctx.obj['database_url'] metadata = ctx.obj['metadata'] # init tables first init_tables(database_url, metadata) # configure session Session.configure(bind=engine) session = Session() click.echo(Block.highest_block(session))
def find_missing_blocks(ctx, url): """Return JSON array of block_nums from missing blocks""" from sbds.storages.db.tables import Block engine = ctx.obj['engine'] database_url = ctx.obj['database_url'] metadata = ctx.obj['metadata'] rpc = SimpleSteemAPIClient(url) # init tables first init_tables(database_url, metadata) # configure session Session.configure(bind=engine) session = Session() last_chain_block = rpc.last_irreversible_block_num() click.echo( json.dumps( Block.find_missing(session, last_chain_block=last_chain_block)))
def bulk_add_blocks(ctx, blocks, chunksize): """Insert many blocks in the database""" engine = ctx.obj['engine'] database_url = ctx.obj['database_url'] metadata = ctx.obj['metadata'] # init tables first init_tables(database_url, metadata) # configure session Session.configure(bind=engine) session = Session() click.echo("SQL: 'SET SESSION innodb_lock_wait_timeout=150'", err=True) session.execute('SET SESSION innodb_lock_wait_timeout=150') try: for chunk in chunkify(blocks, chunksize): bulk_add(chunk, session) except Exception as e: raise e finally: session.close_all()
def init_db_tables(ctx): """Create any missing tables on the database""" database_url = ctx.obj['database_url'] metadata = ctx.obj['metadata'] init_tables(database_url, metadata)
def task_init_db_if_required(database_url): init_tables(database_url, Base.metadata)
def _populate(database_url, steemd_http_url, steemd_websocket_url, max_procs, max_threads): # pylint: disable=too-many-locals, too-many-statements rpc = SimpleSteemAPIClient(steemd_http_url) engine_config = configure_engine(database_url) db_name = engine_config.url.database db_user_name = engine_config.url.username Session.configure(bind=engine_config.engine) session = Session() # [1/7] confirm db connectivity task_message = fmt_task_message( 'Confirm database connectivity', emoji_code_point=u'\U0001F4DE', counter=1) click.echo(task_message) url, table_count = test_connection(database_url) if url: success_msg = fmt_success_message( 'connected to %s and found %s tables', url.__repr__(), table_count) click.echo(success_msg) if not url: raise Exception('Unable to connect to database') del url del table_count # [2/7] kill existing db threads task_message = fmt_task_message( 'Killing active db threads', emoji_code_point='\U0001F4A5', counter=2) click.echo(task_message) all_procs, killed_procs = kill_db_processes(database_url, db_name, db_user_name) if len(killed_procs) > 0: success_msg = fmt_success_message('killed %s processes', len(killed_procs)) click.echo(success_msg) del all_procs del killed_procs # [3/7] init db if required task_message = fmt_task_message( 'Initialising db if required', emoji_code_point=u'\U0001F50C', counter=3) click.echo(task_message) init_tables(database_url, Base.metadata) # [4/7] find last irreversible block last_chain_block = rpc.last_irreversible_block_num() task_message = fmt_task_message( 'Finding highest blockchain block', emoji_code_point='\U0001F50E', counter=4) click.echo(task_message) success_msg = fmt_success_message( 'learned highest irreversible block is %s', last_chain_block) click.echo(success_msg) # [5/7] get missing block_nums task_message = fmt_task_message( 'Finding blocks missing from db', emoji_code_point=u'\U0001F52D', counter=5) click.echo(task_message) missing_block_nums_gen = Block.get_missing_block_num_iterator( session, last_chain_block, chunksize=100000) with click.progressbar( missing_block_nums_gen, label='Finding missing block_nums', color=True, show_eta=False, show_percent=False, empty_char='░', fill_char='█', show_pos=True, bar_template='%(bar)s %(info)s') as pbar: all_missing_block_nums = [] for missing_gen in pbar: all_missing_block_nums.extend(missing_gen()) success_msg = fmt_success_message('found %s missing blocks', len(all_missing_block_nums)) click.echo(success_msg) del missing_block_nums_gen del pbar session.invalidate() # [6/7] adding missing blocks task_message = fmt_task_message( 'Adding missing blocks to db, this may take a while', emoji_code_point=u'\U0001F4DD', counter=6) click.echo(task_message) max_workers = max_procs or os.cpu_count() or 1 chunksize = len(all_missing_block_nums) // max_workers if chunksize <= 0: chunksize = 1 map_func = partial( block_adder_process_worker, database_url, steemd_http_url, max_threads=max_threads) chunks = chunkify(all_missing_block_nums, 10000) with concurrent.futures.ProcessPoolExecutor( max_workers=max_workers) as executor: executor.map(map_func, chunks , chunksize=1) success_msg = fmt_success_message('added missing blocks') click.echo(success_msg) del all_missing_block_nums # [7/7] stream blocks task_message = fmt_task_message( 'Streaming blocks', emoji_code_point=u'\U0001F4DD', counter=7) click.echo(task_message) highest_db_block = Block.highest_block(session) ws_rpc = SteemNodeRPC(steemd_websocket_url) blocks = ws_rpc.block_stream(highest_db_block) add_blocks(blocks, session)