def create_truncate_psql_objects(dbreq, schemas_path, psql_schema): """ drop and create tables for all collections """ schema_engines = get_schema_engines_as_dict(schemas_path) for _, schema in schema_engines.iteritems(): tables_obj = create_tables_load_bson_data(schema, None) drop = True create_psql_tables(tables_obj, dbreq, psql_schema, '', drop)
def test_external_data_loader(): def table_rows_list(table): """ get list of rows, every row is values list @param table object schema_engine.SqlTable""" res = [] firstcolname = table.sql_column_names[0] reccount = len(table.sql_columns[firstcolname].values) for val_i in xrange(reccount): values = [] for column_name in table.sql_column_names: col = table.sql_columns[column_name] values.append(col.values[val_i]) res.append( values ) return res tables1 = test_all_tables() ext_tables = create_tables_load_bson_data(tables1.schema_engine, None) # empty before data loaded assert(ext_tables.is_empty()==True) ext_tables_data = {} for name in tables1.tables: ext_tables_data[name] = table_rows_list(tables1.tables[name]) ext_tables.load_external_tables_data(ext_tables_data) # non empty after data loaded assert(ext_tables.is_empty()==False) assert(tables1.compare(ext_tables))
def main(): """ main """ parser = argparse.ArgumentParser() parser.add_argument("--config-file", action="store", help="Settings file", type=file, required=True) parser.add_argument("--psql-section", help="Psql section name from config", type=str, required=True) parser.add_argument("-cn", "--collection-name", help="Mongo collection name", type=str, required=True) parser.add_argument("--psql-table-name", type=str, required=True) parser.add_argument("-psql-table-prefix", type=str, required=False) parser.add_argument("--input-csv-dir", type=str, required=True) args = parser.parse_args() config = configparser.ConfigParser() config.read_file(args.config_file) schema_name = config['psql']['psql-schema-name'] schemas_dir = config['misc']['schemas-dir'] schema_path = join(schemas_dir, args.collection_name + '.json') schema_file = open(schema_path, 'r') psql_settings = psql_settings_from_config(config, args.psql_section) table_prefix = "" if args.psql_table_prefix: table_prefix = args.psql_table_prefix schema = SchemaEngine(args.collection_name, [load(schema_file)]) table = create_tables_load_bson_data(schema, None)\ .tables[args.psql_table_name] dbreq = PsqlRequests(psql_conn_from_settings(psql_settings)) create_psql_table(table, dbreq, schema_name, table_prefix, drop=True) create_psql_index(table, dbreq, schema_name, table_prefix) dbreq.conn.commit() csv_files = [f \ for f in listdir(args.input_csv_dir) \ if isfile(join(args.input_csv_dir, f))] csv_files.sort() for name in csv_files: csvpath = join(args.input_csv_dir, name) with open(csvpath, 'rb') as csv_f: schema_name_subst = schema_name if len(schema_name): schema_name_subst += '.' tname = '%s"%s%s"' % (schema_name_subst, table_prefix, args.psql_table_name) copy_from_csv(dbreq, csv_f, tname)
def async_worker_handle_mongo_rec(schema_engines, rec_data_and_collection): """ function intended to call by FastQueueProcessor. process mongo record / bson data in separate process. schema_engines -- dict {'collection name': SchemaEngine}. Here is many schema engines to use every queue to handle items from any collection; rec_data_and_collection - tuple('collection name', bson record)""" rec = rec_data_and_collection[0] collection = rec_data_and_collection[1] return create_tables_load_bson_data(schema_engines[collection], [rec])
def get_schema_tables(schema_engine_obj): collection_name = schema_engine_obj.root_node.name dirpath=os.path.dirname(os.path.abspath(__file__)) data_fname = files[collection_name][1] data_path = os.path.join(dirpath, data_fname) tables_with_data = create_tables_load_file(schema_engine_obj, data_path) tables_no_data = create_tables_load_bson_data(schema_engine_obj, None) assert(tables_with_data.tables.keys() == tables_no_data.tables.keys()) return tables_with_data
def async_worker_handle_mongo_rec(schema_engines, rec_data_and_collection): """ function intended to call by FastQueueProcessor. process mongo record / bson data in separate process. schema_engines -- dict {'collection name': SchemaEngine}. Here is many schema engines to use every queue to handle items from any collection; rec_data_and_collection - tuple('collection name', bson record)""" rec = rec_data_and_collection[0] collection = rec_data_and_collection[1] return create_tables_load_bson_data(schema_engines[collection], [rec])
def cb_insert(psql_schema, ts, ns, schema_engine, bson_data): tables = create_tables_load_bson_data(schema_engine, bson_data) collection_name = tables.schema_engine.root_node.name log_table_errors( "collection: %s data for opinsert load from MONGO OPLOG \ with errors:" % collection_name, tables.errors) res = [] for name, table in tables.tables.iteritems(): res.append( OplogQuery("i", generate_insert_queries(table, psql_schema, ""))) return res
def load_mongo_data_to_psql(schema_engine, mongo_data_path, psql, psql_schema): getLogger(__name__).info("Load initial data from %s" \ % (mongo_data_path)) with open(mongo_data_path, "r") as input_f: raw_bson_data = input_f.read() for one_record in loads(raw_bson_data): tables = create_tables_load_bson_data(schema_engine, [one_record]) log_table_errors('test info msg:', tables.errors) getLogger(__name__).info("Loaded tables=%s" % tables.tables) insert_tables_data_into_dst_psql(psql, tables, psql_schema, '') psql.cursor.execute('COMMIT')
def load_mongo_data_to_psql(schema_engine, mongo_data_path, psql, psql_schema): getLogger(__name__).info("Load initial data from %s" \ % (mongo_data_path)) with open(mongo_data_path, "r") as input_f: raw_bson_data = input_f.read() for one_record in loads(raw_bson_data): tables = create_tables_load_bson_data(schema_engine, [one_record]) log_table_errors('test info msg:', tables.errors) getLogger(__name__).info("Loaded tables=%s" % tables.tables) insert_tables_data_into_dst_psql(psql, tables, psql_schema, '') psql.cursor.execute('COMMIT')
def cb_insert(psql_schema, ts, ns, schema_engine, bson_data): tables = create_tables_load_bson_data(schema_engine, bson_data) collection_name = tables.schema_engine.root_node.name log_table_errors("collection: %s data for opinsert load from MONGO OPLOG \ with errors:" % collection_name, tables.errors) res = [] for name, table in tables.tables.iteritems(): res.append(OplogQuery("i", generate_insert_queries(table, psql_schema, ""))) return res
def main(): """ main """ parser = argparse.ArgumentParser() parser.add_argument("--config-file", action="store", help="Settings file", type=file, required=True) parser.add_argument("--psql-section", help="Psql section name from config", type=str, required=True) parser.add_argument("-cn", "--collection-name", help="Mongo collection name", type=str, required=True) parser.add_argument("--psql-table-name", type=str, required=True) parser.add_argument("-psql-table-prefix", type=str, required=False) parser.add_argument("--input-csv-dir", type=str, required=True) args = parser.parse_args() config = configparser.ConfigParser() config.read_file(args.config_file) schema_name = config['psql']['psql-schema-name'] schemas_dir = config['misc']['schemas-dir'] schema_path = join(schemas_dir, args.collection_name + '.json') schema_file = open(schema_path, 'r') psql_settings = psql_settings_from_config(config, args.psql_section) table_prefix = "" if args.psql_table_prefix: table_prefix = args.psql_table_prefix schema = SchemaEngine(args.collection_name, [load(schema_file)]) table = create_tables_load_bson_data(schema, None)\ .tables[args.psql_table_name] dbreq = PsqlRequests(psql_conn_from_settings(psql_settings)) create_psql_table(table, dbreq, schema_name, table_prefix, drop=True) create_psql_index(table, dbreq, schema_name, table_prefix) dbreq.conn.commit() csv_files = [f \ for f in listdir(args.input_csv_dir) \ if isfile(join(args.input_csv_dir, f))] csv_files.sort() for name in csv_files: csvpath = join(args.input_csv_dir, name) with open(csvpath, 'rb') as csv_f: schema_name_subst = schema_name if len(schema_name): schema_name_subst += '.' tname = '%s"%s%s"' % (schema_name_subst, table_prefix, args.psql_table_name) copy_from_csv(dbreq, csv_f, tname)
def async_worker_handle_mongo_rec(schema_engines, rec_collection): """ function intended to call by FastQueueProcessor. process mongo record / bson data in separate process. schema_engine -- SchemaEngine rec_collection - tuble(bson record, collection name)""" rows_as_dict = {} collection = rec_collection[1] rec = rec_collection[0] schema_engine = schema_engines[collection] tables_obj = create_tables_load_bson_data(schema_engine, [rec]) for table_name, table in tables_obj.tables.iteritems(): rows = table_rows_list(table, ENCODE_ONLY, null_value=NULLVAL) rows_as_dict[table_name] = rows return TablesToSave(rows=rows_as_dict, errors=tables_obj.errors)
def load_single_rec_into_tables_obj(src_dbreq, schema_engine, psql_schema, rec_id): """ Return Tables obj loaded from postgres. """ if len(psql_schema): psql_schema += '.' tables = create_tables_load_bson_data(schema_engine, None) # fetch mongo rec by id from source psql ext_tables_data = {} for table_name, table in tables.tables.iteritems(): id_name, quotes = parent_id_name_and_quotes_for_table(table) if quotes: id_val = "'" + str(rec_id) + "'" else: id_val = rec_id indexes = [name \ for name in table.sql_column_names \ if table.sql_columns[name].index_key()] idx_order_by = '' if len(indexes): idx_order_by = "ORDER BY " + ','.join(indexes) select_fmt = 'SELECT * FROM {schema}"{table}" \ WHERE {id_name}={id_val} {idx_order_by};' select_req = select_fmt.format(schema=psql_schema, table=table_name, id_name=id_name, id_val=id_val, idx_order_by=idx_order_by) getLogger(__name__).debug("Get psql data: "+select_req) src_dbreq.cursor.execute(select_req) ext_tables_data[table_name] = [] idx = 0 for record in src_dbreq.cursor: record_decoded = [] if type(record) is tuple: for titem in record: if type(titem) is str: record_decoded.append(titem.decode('utf-8')) else: record_decoded.append(titem) record = tuple(record_decoded) getLogger(__name__).debug("result[%d]=%s", idx, record) ext_tables_data[table_name].append(record) idx += 1 # set external tables data to Tables tables.load_external_tables_data(ext_tables_data) return tables
def save_ddl_create_statements(create_statements_file, schema_engine, psql_schema_name, table_prefix): """ save create table statements to file """ ddls = {} if not psql_schema_name: psql_schema_name = '' if not table_prefix: table_prefix = '' sqltables = create_tables_load_bson_data(schema_engine, None).tables for tablename, sqltable in sqltables.iteritems(): ddls[tablename] = create_table(sqltable, psql_schema_name, table_prefix) for table_name in ddls: create_query = ddls[table_name] create_statements_file.write(create_query)
def save_ddl_create_statements(create_statements_file, schema_engine, psql_schema_name, table_prefix): """ save create table statements to file """ ddls = {} if not psql_schema_name: psql_schema_name = '' if not table_prefix: table_prefix = '' sqltables = create_tables_load_bson_data(schema_engine, None).tables for tablename, sqltable in sqltables.iteritems(): ddls[tablename] = create_table(sqltable, psql_schema_name, table_prefix) for table_name in ddls: create_query = ddls[table_name] create_statements_file.write(create_query)
def test_complete_partial_record2(): logging.basicConfig(level=logging.DEBUG, format='%(asctime)s %(levelname)-8s %(message)s') PSQL_SCHEMA_NAME = '' # etalon of data sample_data_before = { 'posts2': { 'id': [133], "updated_at": [loads('{ "$date" : "2016-02-08T20:02:12.985Z"}')] }, 'posts2_comments': { 'idx': [1, 2], 'id_oid': [ str(loads('{ "$oid": "56b8f35ef9fcee1b0000001a" }')), str(loads('{ "$oid": "56b8f344f9fcee1b00000018" }')) ], 'updated_at': [ loads('{ "$date" : "2016-02-08T20:02:12.985Z"}'), loads('{ "$date" : "2016-02-08T20:02:12.985Z"}') ] }, 'posts2_comment_struct_tests': { 'v': [1, 2, 3], 'idx': [1, 2, 1] }, 'posts2_comment_struct_test_nested': { 'nested': [20, 23, 24, 25, 26], 'idx': [1, 1, 2, 1, 2] } } wrong_raw_bson_data = '[{\ "_id": 133,\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "comments": "error in data"\ }]' existing_raw_bson_data = '[{\ "_id": 133,\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "comments": [ {\ "_id": { "$oid": "56b8f35ef9fcee1b0000001a" },\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "struct" : {\ "tests": [{\ "v": 1,\ "nested": [20]\ }, {\ "v": 2,\ "nested": [23, 24]\ }]}\ }, {\ "_id": { "$oid": "56b8f344f9fcee1b00000018" },\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "struct" : {\ "tests": [{\ "v": 3,\ "nested": [25, 26]\ }]}\ } ]\ }]' oplog_object_id_bson_raw_data = '{"_id": 133}' # insert request should be created, to add 'tests' item oplog_path_array_bson_raw_data = '{\ "comments.0.struct.tests.0.nested.1": 21,\ "comments.2": { \ "_id": { "$oid": "56b8f35ef9fcee1b0000001a" },\ "updated_at": { "$date" : "2016-02-08T20:02:14.985Z"},\ "struct": {\ "tests": [{\ "v": 12,\ "nested": [30]\ }, {\ "v": 13,\ "nested": [32, 31]\ }\ ]}}\ }' dbname = 'rails4_mongoid_development' db_schemas_path = '/'.join(['test_data', 'schemas', dbname]) schemas = get_schema_engines_as_dict(db_schemas_path) schema_engine = schemas['posts2'] connstr = os.environ['TEST_PSQLCONN'] psql = PsqlRequests(psycopg2.connect(connstr)) # test wrong bson data existing_bson_data = loads(wrong_raw_bson_data) tables_obj_before = \ create_tables_load_bson_data(schema_engine, existing_bson_data) assert (False == tables_obj_before.compare_with_sample(sample_data_before)) # tables loaded from existing_raW_bson_data existing_bson_data = loads(existing_raw_bson_data) tables_obj_before = \ create_tables_load_bson_data(schema_engine, existing_bson_data) assert (False == tables_obj_before.compare_with_sample({})) assert (True == tables_obj_before.compare_with_sample(sample_data_before)) # create table structure, drop existing create_psql_tables(tables_obj_before, psql, PSQL_SCHEMA_NAME, '', True) # insert data totables insert_tables_data_into_dst_psql(psql, tables_obj_before, PSQL_SCHEMA_NAME, '') # oplog path with indexes. insert array item bson_data = loads(oplog_path_array_bson_raw_data) object_id_bson_data = loads(oplog_object_id_bson_raw_data) partial_inserts_list = get_tables_data_from_oplog_set_command(\ schema_engine, bson_data, object_id_bson_data) for partial_insert in partial_inserts_list: tables_for_insert = partial_insert.tables initial_indexes = partial_insert.initial_indexes for name, table in tables_for_insert.iteritems(): query_tuple = generate_insert_queries(table, PSQL_SCHEMA_NAME, "", initial_indexes) for query in query_tuple[1]: getLogger(__name__).debug("EXECUTE: " + \ str(query_tuple[0]) + str(query)) psql.cursor.execute(query_tuple[0], query) # tables loaded from existing_bson_data rec_obj_id = object_id_bson_data['_id'] tables_obj_after = load_single_rec_into_tables_obj(psql, schema_engine, PSQL_SCHEMA_NAME, rec_obj_id) sample_data_after = sample_data_before sample_data_after['posts2_comments']['idx'].append(3) sample_data_after['posts2_comments']['id_oid'].append(\ "56b8f35ef9fcee1b0000001a") sample_data_after['posts2_comments']['updated_at'].append( loads('{ "$date" : "2016-02-08T20:02:14.985Z"}')) sample_data_after['posts2_comment_struct_tests'] = { 'v': [1, 2, 3, 12, 13], 'idx': [1, 2, 1, 1, 2] } sample_data_after['posts2_comment_struct_test_nested'] = { 'nested': [20, 21, 23, 24, 25, 26, 30, 32, 31], 'idx': [1, 2, 1, 2, 1, 2, 1, 1, 2] } assert (False == tables_obj_after.compare_with_sample({})) assert (True == tables_obj_after.compare_with_sample(sample_data_after))
def main(): """ main """ #for debugging purposes #profiler = Profile() # profiling #profiler.enable() args = getargs() config = configparser.ConfigParser() config.read_file(args.config_file) schema_name = config['psql']['psql-schema-name'] schemas_dir = config['misc']['schemas-dir'] schema_path = os.path.join(schemas_dir, args.collection_name + '.json') schema_file = open(schema_path, 'r') mongo_settings = mongo_settings_from_config(config, 'mongo') mongo_reader = mongo_reader_from_settings(mongo_settings, args.collection_name, json.loads(args.js_request)) schema_engine = SchemaEngine(args.collection_name, [json.load(schema_file)]) table_names = create_tables_load_bson_data(schema_engine, None).tables.keys() csm = CsvWriteManager(table_names, args.csv_path, CSV_CHUNK_SIZE) etl_mongo_reader = EtlMongoReader(ETL_PROCESS_NUMBER, ETL_QUEUE_SIZE, async_worker_handle_mongo_rec, #1st worker param {args.collection_name: schema_engine}, {args.collection_name: mongo_reader}) etl_mongo_reader.execute_query(args.collection_name, json.loads(args.js_request)) getLogger(__name__).info("Connecting to mongo server " + mongo_settings.host) received_rec_ids = Set([]) errors = {} all_written = {} while True: tables_to_save = etl_mongo_reader.next() if not tables_to_save: break # don't process duplicates that can be accidently returned by transp if tables_to_save.rec_id not in received_rec_ids: received_rec_ids.add(tables_to_save.rec_id) all_written = merge_dicts(all_written, save_csvs(csm, tables_to_save.rows)) errors = merge_dicts(errors, tables_to_save.errors) else: getLogger(__name__).warning("Skip duplicated rec_id=%s", tables_to_save.rec_id) if args.ddl_statements_file: save_ddl_create_statements(args.ddl_statements_file, schema_engine, schema_name, args.psql_table_prefix) # save csv files csm.finalize() #for debugging purposes #print_profiler_stats(profiler) print_etl_stats(errors, all_written, etl_mongo_reader.etl_recs_count) save_etl_stats(args.stats_file, all_written) exit_code = 0 if etl_mongo_reader.current_mongo_reader.failed or \ etl_mongo_reader.fast_queue.error: exit_code = 1 del etl_mongo_reader exit(exit_code)
def main(): """ main """ #for debugging purposes #profiler = Profile() # profiling #profiler.enable() args = getargs() config = configparser.ConfigParser() config.read_file(args.config_file) schema_name = config['psql']['psql-schema-name'] schemas_dir = config['misc']['schemas-dir'] schema_path = os.path.join(schemas_dir, args.collection_name + '.json') schema_file = open(schema_path, 'r') mongo_settings = mongo_settings_from_config(config, 'mongo') mongo_reader = mongo_reader_from_settings(mongo_settings, args.collection_name, json.loads(args.js_request)) schema_engine = SchemaEngine(args.collection_name, [json.load(schema_file)]) table_names = create_tables_load_bson_data(schema_engine, None).tables.keys() csm = CsvWriteManager(table_names, args.csv_path, CSV_CHUNK_SIZE) etl_mongo_reader = EtlMongoReader(ETL_PROCESS_NUMBER, ETL_QUEUE_SIZE, async_worker_handle_mongo_rec, #1st worker param {args.collection_name: schema_engine}, {args.collection_name: mongo_reader}) etl_mongo_reader.execute_query(args.collection_name, json.loads(args.js_request)) getLogger(__name__).info("Connecting to mongo server " + mongo_settings.host) errors = {} all_written = {} tables_list = etl_mongo_reader.next_processed() while tables_list is not None: for tables in tables_list: all_written = merge_dicts(all_written, save_csvs(csm, tables.rows)) errors = merge_dicts(errors, tables.errors) tables_list = etl_mongo_reader.next_processed() if args.ddl_statements_file: save_ddl_create_statements(args.ddl_statements_file, schema_engine, schema_name, args.psql_table_prefix) # save csv files csm.finalize() #for debugging purposes #print_profiler_stats(profiler) print_etl_stats(errors, all_written, etl_mongo_reader.etl_recs_count) save_etl_stats(args.stats_file, all_written) exit_code = 0 if etl_mongo_reader.current_mongo_reader.failed or \ etl_mongo_reader.fast_queue.error: exit_code = 1 del etl_mongo_reader exit(exit_code)
def test_complete_partial_record3(): logging.basicConfig(level=logging.DEBUG, format='%(asctime)s %(levelname)-8s %(message)s') PSQL_SCHEMA_NAME = '' # etalon of data sample_data_before = { 'posts': { 'id_oid': ['56b8da59f9fcee1b00000007'], "updated_at": [loads('{ "$date" : "2016-02-08T20:02:12.985Z"}')] }, 'post_comments': { 'id_oid': [ str(loads('{ "$oid": "56b8f35ef9fcee1b0000001a" }')), str(loads('{ "$oid": "56b8f344f9fcee1b00000018" }')) ], 'updated_at': [ loads('{ "$date" : "2016-02-08T20:02:12.985Z"}'), loads('{ "$date" : "2016-02-08T20:02:13.985Z"}') ], 'idx': [1, 2] }, 'post_comment_tests': { 'tests': [0, 2], 'idx': [1, 2] } } existing_raw_bson_data = '[{\ "_id": { "$oid": "56b8da59f9fcee1b00000007" },\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "comments": [ {\ "_id": { "$oid": "56b8f35ef9fcee1b0000001a" },\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"}\ }, {\ "_id": { "$oid": "56b8f344f9fcee1b00000018" },\ "updated_at": { "$date" : "2016-02-08T20:02:13.985Z"},\ "tests": [0,2]\ } ]\ }]' oplog_object_id_bson_raw_data = '{\ "_id": { "$oid": "56b8da59f9fcee1b00000007" }\ }' # insert request should be created, to add a record with only single field: updated_at oplog_path_array_bson_raw_data = '{"comments.2.updated_at": \ { "$date" : "2016-02-08T20:02:14.985Z"}}' dbname = 'rails4_mongoid_development' db_schemas_path = '/'.join(['test_data', 'schemas', dbname]) schemas = get_schema_engines_as_dict(db_schemas_path) schema_engine = schemas['posts'] connstr = os.environ['TEST_PSQLCONN'] psql = PsqlRequests(psycopg2.connect(connstr)) # tables loaded from existing_raW_bson_data existing_bson_data = loads(existing_raw_bson_data) tables_obj_before = \ create_tables_load_bson_data(schema_engine, existing_bson_data) assert (True == tables_obj_before.compare_with_sample(sample_data_before)) # create table structure, drop existing create_psql_tables(tables_obj_before, psql, PSQL_SCHEMA_NAME, '', True) # insert data totables insert_tables_data_into_dst_psql(psql, tables_obj_before, PSQL_SCHEMA_NAME, '') # oplog path inserting just a field bson_data = loads(oplog_path_array_bson_raw_data) print bson_data object_id_bson_data = loads(oplog_object_id_bson_raw_data) partial_inserts_list = get_tables_data_from_oplog_set_command(\ schema_engine, bson_data, object_id_bson_data) tables_for_insert = partial_inserts_list[0].tables initial_indexes = partial_inserts_list[0].initial_indexes print "tables_for_insert", tables_for_insert.keys() print "initial_indexes", initial_indexes insert_tests_t = tables_for_insert['post_comments'] insert_query = generate_insert_queries(insert_tests_t, "", "", initial_indexes) print "columns", insert_tests_t.sql_column_names print "insert_query=", insert_query for query in insert_query[1]: print insert_query[0], query psql.cursor.execute(insert_query[0], query) # tables loaded from existing_bson_data rec_obj_id = object_id_bson_data['_id'] tables_obj_after = load_single_rec_into_tables_obj(psql, schema_engine, PSQL_SCHEMA_NAME, rec_obj_id) sample_data_after = sample_data_before sample_data_after['post_comments']['idx'].append(3) sample_data_after['post_comments']['id_oid'].append(None) sample_data_after['post_comments']['updated_at'].append( loads('{ "$date" : "2016-02-08T20:02:14.985Z"}')) assert (True == tables_obj_after.compare_with_sample(sample_data_after))
def test_complete_partial_record2(): logging.basicConfig(level=logging.DEBUG, format='%(asctime)s %(levelname)-8s %(message)s') PSQL_SCHEMA_NAME = '' # etalon of data sample_data_before = { 'posts2': { 'id': [133], "updated_at": [loads('{ "$date" : "2016-02-08T20:02:12.985Z"}')] }, 'posts2_comments': { 'idx': [1,2], 'id_oid': [str(loads('{ "$oid": "56b8f35ef9fcee1b0000001a" }')), str(loads('{ "$oid": "56b8f344f9fcee1b00000018" }'))], 'updated_at': [loads('{ "$date" : "2016-02-08T20:02:12.985Z"}'), loads('{ "$date" : "2016-02-08T20:02:12.985Z"}')] }, 'posts2_comment_struct_tests': { 'v': [1,2,3], 'idx': [1,2,1] }, 'posts2_comment_struct_test_nested': { 'nested': [20,23,24,25,26], 'idx': [1,1,2,1,2] } } wrong_raw_bson_data = '[{\ "_id": 133,\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "comments": "error in data"\ }]' existing_raw_bson_data = '[{\ "_id": 133,\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "comments": [ {\ "_id": { "$oid": "56b8f35ef9fcee1b0000001a" },\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "struct" : {\ "tests": [{\ "v": 1,\ "nested": [20]\ }, {\ "v": 2,\ "nested": [23, 24]\ }]}\ }, {\ "_id": { "$oid": "56b8f344f9fcee1b00000018" },\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "struct" : {\ "tests": [{\ "v": 3,\ "nested": [25, 26]\ }]}\ } ]\ }]' oplog_object_id_bson_raw_data = '{"_id": 133}' # insert request should be created, to add 'tests' item oplog_path_array_bson_raw_data = '{\ "comments.0.struct.tests.0.nested.1": 21,\ "comments.2": { \ "_id": { "$oid": "56b8f35ef9fcee1b0000001a" },\ "updated_at": { "$date" : "2016-02-08T20:02:14.985Z"},\ "struct": {\ "tests": [{\ "v": 12,\ "nested": [30]\ }, {\ "v": 13,\ "nested": [32, 31]\ }\ ]}}\ }' dbname = 'rails4_mongoid_development' db_schemas_path = '/'.join(['test_data', 'schemas', dbname]) schemas = get_schema_engines_as_dict(db_schemas_path) schema_engine = schemas['posts2'] connstr = os.environ['TEST_PSQLCONN'] psql = PsqlRequests(psycopg2.connect(connstr)) # test wrong bson data existing_bson_data = loads(wrong_raw_bson_data) tables_obj_before = \ create_tables_load_bson_data(schema_engine, existing_bson_data) assert(False==tables_obj_before.compare_with_sample(sample_data_before)) # tables loaded from existing_raW_bson_data existing_bson_data = loads(existing_raw_bson_data) tables_obj_before = \ create_tables_load_bson_data(schema_engine, existing_bson_data) assert(False==tables_obj_before.compare_with_sample({})) assert(True==tables_obj_before.compare_with_sample(sample_data_before)) # create table structure, drop existing create_psql_tables(tables_obj_before, psql, PSQL_SCHEMA_NAME, '', True) # insert data totables insert_tables_data_into_dst_psql(psql, tables_obj_before, PSQL_SCHEMA_NAME, '') # oplog path with indexes. insert array item bson_data = loads(oplog_path_array_bson_raw_data) object_id_bson_data = loads(oplog_object_id_bson_raw_data) partial_inserts_list = get_tables_data_from_oplog_set_command(\ schema_engine, bson_data, object_id_bson_data) for partial_insert in partial_inserts_list: tables_for_insert = partial_insert.tables initial_indexes = partial_insert.initial_indexes for name, table in tables_for_insert.iteritems(): query_tuple = generate_insert_queries(table, PSQL_SCHEMA_NAME, "", initial_indexes) for query in query_tuple[1]: getLogger(__name__).debug("EXECUTE: " + \ str(query_tuple[0]) + str(query)) psql.cursor.execute(query_tuple[0], query) # tables loaded from existing_bson_data rec_obj_id = object_id_bson_data['_id'] tables_obj_after = load_single_rec_into_tables_obj(psql, schema_engine, PSQL_SCHEMA_NAME, rec_obj_id) sample_data_after = sample_data_before sample_data_after['posts2_comments']['idx'].append(3) sample_data_after['posts2_comments']['id_oid'].append(\ "56b8f35ef9fcee1b0000001a") sample_data_after['posts2_comments']['updated_at'].append( loads('{ "$date" : "2016-02-08T20:02:14.985Z"}')) sample_data_after['posts2_comment_struct_tests'] = { 'v': [1,2,3,12,13], 'idx': [1,2,1,1,2] } sample_data_after['posts2_comment_struct_test_nested'] = { 'nested': [20,21,23,24,25,26,30,32,31], 'idx': [1,2,1,2,1,2,1,1,2] } assert(False==tables_obj_after.compare_with_sample({})) assert(True==tables_obj_after.compare_with_sample(sample_data_after))
def test_complete_partial_record3(): logging.basicConfig(level=logging.DEBUG, format='%(asctime)s %(levelname)-8s %(message)s') PSQL_SCHEMA_NAME = '' # etalon of data sample_data_before = { 'posts': { 'id_oid': ['56b8da59f9fcee1b00000007'], "updated_at": [loads('{ "$date" : "2016-02-08T20:02:12.985Z"}')] }, 'post_comments': { 'id_oid': [str(loads('{ "$oid": "56b8f35ef9fcee1b0000001a" }')), str(loads('{ "$oid": "56b8f344f9fcee1b00000018" }'))], 'updated_at': [loads('{ "$date" : "2016-02-08T20:02:12.985Z"}'), loads('{ "$date" : "2016-02-08T20:02:13.985Z"}')], 'idx': [1,2] }, 'post_comment_tests': { 'tests': [0,2], 'idx': [1,2] } } existing_raw_bson_data = '[{\ "_id": { "$oid": "56b8da59f9fcee1b00000007" },\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"},\ "comments": [ {\ "_id": { "$oid": "56b8f35ef9fcee1b0000001a" },\ "updated_at": { "$date" : "2016-02-08T20:02:12.985Z"}\ }, {\ "_id": { "$oid": "56b8f344f9fcee1b00000018" },\ "updated_at": { "$date" : "2016-02-08T20:02:13.985Z"},\ "tests": [0,2]\ } ]\ }]' oplog_object_id_bson_raw_data = '{\ "_id": { "$oid": "56b8da59f9fcee1b00000007" }\ }' # insert request should be created, to add a record with only single field: updated_at oplog_path_array_bson_raw_data = '{"comments.2.updated_at": \ { "$date" : "2016-02-08T20:02:14.985Z"}}' dbname = 'rails4_mongoid_development' db_schemas_path = '/'.join(['test_data', 'schemas', dbname]) schemas = get_schema_engines_as_dict(db_schemas_path) schema_engine = schemas['posts'] connstr = os.environ['TEST_PSQLCONN'] psql = PsqlRequests(psycopg2.connect(connstr)) # tables loaded from existing_raW_bson_data existing_bson_data = loads(existing_raw_bson_data) tables_obj_before = \ create_tables_load_bson_data(schema_engine, existing_bson_data) assert(True==tables_obj_before.compare_with_sample(sample_data_before)) # create table structure, drop existing create_psql_tables(tables_obj_before, psql, PSQL_SCHEMA_NAME, '', True) # insert data totables insert_tables_data_into_dst_psql(psql, tables_obj_before, PSQL_SCHEMA_NAME, '') # oplog path inserting just a field bson_data = loads(oplog_path_array_bson_raw_data) print bson_data object_id_bson_data = loads(oplog_object_id_bson_raw_data) partial_inserts_list = get_tables_data_from_oplog_set_command(\ schema_engine, bson_data, object_id_bson_data) tables_for_insert = partial_inserts_list[0].tables initial_indexes = partial_inserts_list[0].initial_indexes print "tables_for_insert", tables_for_insert.keys() print "initial_indexes", initial_indexes insert_tests_t = tables_for_insert['post_comments'] insert_query = generate_insert_queries(insert_tests_t, "", "", initial_indexes) print "columns", insert_tests_t.sql_column_names print "insert_query=", insert_query for query in insert_query[1]: print insert_query[0], query psql.cursor.execute(insert_query[0], query) # tables loaded from existing_bson_data rec_obj_id = object_id_bson_data['_id'] tables_obj_after = load_single_rec_into_tables_obj(psql, schema_engine, PSQL_SCHEMA_NAME, rec_obj_id) sample_data_after = sample_data_before sample_data_after['post_comments']['idx'].append(3) sample_data_after['post_comments']['id_oid'].append(None) sample_data_after['post_comments']['updated_at'].append( loads('{ "$date" : "2016-02-08T20:02:14.985Z"}')) assert(True==tables_obj_after.compare_with_sample(sample_data_after))