def handle(self, **options): data_sources = list(DataSourceConfiguration.all()) data_sources.extend(list(StaticDataSourceConfiguration.all())) engine_ids = self._get_engine_ids(data_sources, options.get('engine_id')) tables_to_remove_by_engine = defaultdict(list) for engine_id in engine_ids: engine = connection_manager.get_engine(engine_id) with engine.begin() as connection: migration_context = get_migration_context(connection, include_object=_include_object) raw_diffs = compare_metadata(migration_context, metadata) diffs = reformat_alembic_diffs(raw_diffs) tables_to_remove_by_engine[engine_id] = [ diff.table_name for diff in diffs if diff.type == 'remove_table' ] for engine_id, tablenames in tables_to_remove_by_engine.items(): engine = connection_manager.get_engine(engine_id) for tablename in tablenames: with engine.begin() as connection: try: result = connection.execute( 'SELECT COUNT(*), MAX(inserted_at) FROM "{tablename}"'.format(tablename=tablename) ) except Exception: print(tablename, "no inserted_at column, probably not UCR") else: print(tablename, result.fetchone())
def handle(self, **options): data_sources = list(DataSourceConfiguration.all()) data_sources.extend(list(StaticDataSourceConfiguration.all())) engine_ids = self._get_engine_ids(data_sources, options.get('engine_id')) tables_to_remove_by_engine = defaultdict(list) for engine_id in engine_ids: engine = connection_manager.get_engine(engine_id) with engine.begin() as connection: migration_context = get_migration_context( connection, include_object=_include_object) raw_diffs = compare_metadata(migration_context, metadata) diffs = reformat_alembic_diffs(raw_diffs) tables_to_remove_by_engine[engine_id] = [ diff.table_name for diff in diffs if diff.type == 'remove_table' ] for engine_id, tablenames in tables_to_remove_by_engine.items(): engine = connection_manager.get_engine(engine_id) for tablename in tablenames: with engine.begin() as connection: try: result = connection.execute( 'SELECT COUNT(*), MAX(inserted_at) FROM "{tablename}"' .format(tablename=tablename)) except Exception: print(tablename, "no inserted_at column, probably not UCR") else: print(tablename, result.fetchone())
def handle(self, **options): data_sources = list(DataSourceConfiguration.all()) data_sources.extend(list(StaticDataSourceConfiguration.all())) tables_by_engine_id = self._get_tables_by_engine_id( data_sources, options.get('engine_id')) tables_to_remove_by_engine = defaultdict(list) for engine_id, expected_tables in tables_by_engine_id.items(): engine = connection_manager.get_engine(engine_id) with engine.begin() as connection: # Using string formatting rather than execute with %s syntax # is acceptable here because the strings we're inserting are static # and only templated for DRYness results = connection.execute(f""" SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND ( table_name LIKE '{UCR_TABLE_PREFIX}%%' OR table_name LIKE '{LEGACY_UCR_TABLE_PREFIX}%%' ); """).fetchall() tables_in_db = {r[0] for r in results} tables_to_remove_by_engine[ engine_id] = tables_in_db - expected_tables for engine_id, tablenames in tables_to_remove_by_engine.items(): print("\nTables no longer referenced in database: {}:\n".format( engine_id)) engine = connection_manager.get_engine(engine_id) if not tablenames: print("\t No tables to prune") continue for tablename in tablenames: with engine.begin() as connection: try: result = connection.execute( f'SELECT COUNT(*), MAX(inserted_at) FROM "{tablename}"' ) except Exception: print( f"\t{tablename}: no inserted_at column, probably not UCR" ) else: row_count, idle_since = result.fetchone() if row_count == 0: print(f"\t{tablename}: {row_count} rows") if options['drop_empty_tables']: connection.execute(f'DROP TABLE "{tablename}"') print(f'\t^-- deleted {tablename}') else: print( f"\t{tablename}: {row_count} rows, idle since {idle_since}" )
def handle(self, source_db_alias, target_db_alias, **options): self.dry_run = options['dry_run'] with connection_manager.get_engine(source_db_alias).begin() as conn: self.parent_child_mapping = get_parent_child_mapping(conn) source_engine = connection_manager.get_engine(source_db_alias) target_engine = connection_manager.get_engine(target_db_alias) with source_engine.begin() as source_conn: for table in keep_child_tables: self.create_child_tables(source_conn, target_engine, table)
def handle(self, **options): data_sources = list(DataSourceConfiguration.all()) data_sources.extend(list(StaticDataSourceConfiguration.all())) tables_by_engine_id = self._get_tables_by_engine_id( data_sources, options.get('engine_id')) tables_to_remove_by_engine = defaultdict(list) for engine_id, expected_tables in tables_by_engine_id.items(): engine = connection_manager.get_engine(engine_id) with engine.begin() as connection: results = connection.execute(""" SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND ( table_name LIKE '{}%%' OR table_name LIKE '{}%%' ); """.format(UCR_TABLE_PREFIX, LEGACY_UCR_TABLE_PREFIX)).fetchall() tables_in_db = {r[0] for r in results} tables_to_remove_by_engine[ engine_id] = tables_in_db - expected_tables for engine_id, tablenames in tables_to_remove_by_engine.items(): print("\nTables no longer referenced in database: {}:\n".format( engine_id)) engine = connection_manager.get_engine(engine_id) if not tablenames: print("\t No tables to prune") continue for tablename in tablenames: if options['show_counts']: with engine.begin() as connection: try: result = connection.execute( 'SELECT COUNT(*), MAX(inserted_at) FROM "{tablename}"' .format(tablename=tablename)) except Exception: print( "\t{}: no inserted_at column, probably not UCR" .format(tablename)) else: print("\t{}: {}".format(tablename, result.fetchone())) else: print("\t{}".format(tablename))
def _setup_ucr_tables(): with mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ): with override_settings(SERVER_ENVIRONMENT=TEST_ENVIRONMENT): configs = StaticDataSourceConfiguration.by_domain(TEST_DOMAIN) adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: try: adapter.drop_table() except Exception: pass adapter.build_table() engine = connection_manager.get_engine('aaa-data') metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) for file_name in os.listdir(INPUT_PATH): with open(os.path.join(INPUT_PATH, file_name), encoding='utf-8') as f: table_name = FILE_NAME_TO_TABLE_MAPPING[file_name[:-4]] table = metadata.tables[table_name] columns = [ '"{}"'.format(c.strip()) # quote to preserve case for c in f.readline().split(',') ] postgres_copy.copy_from(f, table, engine, format='csv', null='', columns=columns)
def setUpModule(): _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) _call_center_domain_mock.start() domain = create_domain('champ-cameroon') try: configs = StaticDataSourceConfiguration.by_domain(domain.name) adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: adapter.build_table() engine = connection_manager.get_engine(UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): with open(os.path.join(path, file_name), encoding='utf-8') as f: table_name = get_table_name(domain.name, file_name[:-4]) table = metadata.tables[table_name] postgres_copy.copy_from(f, table, engine, format='csv', null='', header=True) except Exception: tearDownModule() raise _call_center_domain_mock.stop()
def _setup_ucr_tables(): with mock.patch('corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider'): with override_settings(SERVER_ENVIRONMENT=TEST_ENVIRONMENT): configs = StaticDataSourceConfiguration.by_domain(TEST_DOMAIN) adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: try: adapter.drop_table() except Exception: pass adapter.build_table() engine = connection_manager.get_engine('aaa-data') metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) for file_name in os.listdir(INPUT_PATH): with open(os.path.join(INPUT_PATH, file_name), encoding='utf-8') as f: table_name = FILE_NAME_TO_TABLE_MAPPING[file_name[:-4]] table = metadata.tables[table_name] columns = [ '"{}"'.format(c.strip()) # quote to preserve case for c in f.readline().split(',') ] postgres_copy.copy_from( f, table, engine, format='csv' if six.PY3 else b'csv', null='' if six.PY3 else b'', columns=columns )
def _rebuild_sql_tables(self, adapters): tables_by_engine = defaultdict(dict) for adapter in adapters: try: tables_by_engine[adapter.engine_id][adapter.get_table().name] = adapter except BadSpecError: _soft_assert = soft_assert(to='{}@{}'.format('jemord', 'dimagi.com')) _soft_assert(False, "Broken data source {}".format(adapter.config.get_id)) _assert = soft_assert(notify_admins=True) _notify_rebuild = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): table_names = list(table_map) engine = connection_manager.get_engine(engine_id) # Temporary measure necessary during the process of renaming tables # - Configs point to new tables which may just be views and not real tables # - The global metadata contains references to the new table names legacy_tables = {} table_names_for_diff = [] diff_metadata = sqlalchemy.MetaData() with engine.begin() as connection: for table_name in table_names: sql_adapter = table_map[table_name] legacy_table_name = get_legacy_table_name( sql_adapter.config.domain, sql_adapter.config.table_id ) if not table_exists(connection, table_name) and table_exists(connection, legacy_table_name): legacy_tables[legacy_table_name] = table_name pillow_logging.debug("[rebuild] Using legacy table: %s", legacy_table_name) # popultate metadata with the table schema get_indicator_table( sql_adapter.config, metadata=diff_metadata, override_table_name=legacy_table_name ) table_names_for_diff.append(legacy_table_name) else: # popultate metadata with the table schema get_indicator_table(sql_adapter.config, metadata=diff_metadata) table_names_for_diff.append(table_name) diffs = get_table_diffs(engine, table_names_for_diff, diff_metadata) tables_to_act_on = get_tables_rebuild_migrate(diffs, table_names_for_diff) for real_table_name in tables_to_act_on.rebuild: table_name = legacy_tables.get(real_table_name, real_table_name) pillow_logging.debug("[rebuild] Rebuilding table: %s (%s)", real_table_name, table_name) sql_adapter = table_map[table_name] if not sql_adapter.config.is_static: try: self.rebuild_table(sql_adapter) except TableRebuildError as e: _notify_rebuild(six.text_type(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter) pillow_logging.debug("[rebuild] Application migrations to tables: %s", tables_to_act_on.migrate) migrate_tables(engine, diffs.raw, tables_to_act_on.migrate)
def setup_tables_and_fixtures(domain_name): configs = StaticDataSourceConfiguration.by_domain(domain_name) adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: try: adapter.drop_table() except Exception: pass adapter.build_table() cleanup_misc_agg_tables() engine = connection_manager.get_engine(ICDS_UCR_CITUS_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): with open(os.path.join(path, file_name), encoding='utf-8') as f: table_name = FILE_NAME_TO_TABLE_MAPPING[file_name[:-4]] table = metadata.tables[table_name] if not table_name.startswith('icds_dashboard_'): columns = [ '"{}"'.format(c.strip()) # quote to preserve case for c in f.readline().split(',') ] postgres_copy.copy_from(f, table, engine, format='csv', null='', columns=columns) _distribute_tables_for_citus(engine)
def _rebuild_sql_tables(self, adapters): # todo move this code to sql adapter rebuild_if_necessary tables_by_engine = defaultdict(dict) for adapter in adapters: sql_adapter = get_indicator_adapter(adapter.config) tables_by_engine[sql_adapter.engine_id][ sql_adapter.get_table().name] = sql_adapter _assert = soft_assert(notify_admins=True) _notify_rebuild = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): engine = connection_manager.get_engine(engine_id) table_names = list(table_map) with engine.begin() as connection: migration_context = get_migration_context( connection, table_names) raw_diffs = compare_metadata(migration_context, metadata) diffs = reformat_alembic_diffs(raw_diffs) tables_to_rebuild = get_tables_to_rebuild(diffs, table_names) for table_name in tables_to_rebuild: sql_adapter = table_map[table_name] if not sql_adapter.config.is_static: try: self.rebuild_table(sql_adapter) except TableRebuildError as e: _notify_rebuild(six.text_type(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter) tables_to_migrate = get_tables_to_migrate(diffs, table_names) tables_to_migrate -= tables_to_rebuild migrate_tables(engine, raw_diffs, tables_to_migrate)
def tearDownModule(): if settings.USE_PARTITIONED_DATABASE: return _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) _call_center_domain_mock.start() with override_settings(SERVER_ENVIRONMENT='icds'): configs = StaticDataSourceConfiguration.by_domain('icds-cas') adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: if adapter.config.table_id == 'static-child_health_cases': # hack because this is in a migration adapter.clear_table() continue adapter.drop_table() engine = connection_manager.get_engine(ICDS_UCR_ENGINE_ID) with engine.begin() as connection: metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) table = metadata.tables['ucr_table_name_mapping'] delete = table.delete() connection.execute(delete) LocationType.objects.filter(domain='icds-cas').delete() SQLLocation.objects.filter(domain='icds-cas').delete() Domain.get_by_name('icds-cas').delete() _call_center_domain_mock.stop()
def _rebuild_sql_tables(self, adapters): tables_by_engine = defaultdict(dict) for adapter in adapters: sql_adapter = get_indicator_adapter(adapter.config) try: tables_by_engine[sql_adapter.engine_id][sql_adapter.get_table().name] = sql_adapter except BadSpecError: _soft_assert = soft_assert(to='{}@{}'.format('jemord', 'dimagi.com')) _soft_assert(False, "Broken data source {}".format(adapter.config.get_id)) _assert = soft_assert(notify_admins=True) _notify_rebuild = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): engine = connection_manager.get_engine(engine_id) table_names = list(table_map) with engine.begin() as connection: migration_context = get_migration_context(connection, table_names) raw_diffs = compare_metadata(migration_context, metadata) diffs = reformat_alembic_diffs(raw_diffs) tables_to_rebuild = get_tables_to_rebuild(diffs, table_names) for table_name in tables_to_rebuild: sql_adapter = table_map[table_name] if not sql_adapter.config.is_static: try: self.rebuild_table(sql_adapter) except TableRebuildError as e: _notify_rebuild(six.text_type(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter) tables_to_migrate = get_tables_to_migrate(diffs, table_names) tables_to_migrate -= tables_to_rebuild migrate_tables(engine, raw_diffs, tables_to_migrate)
def setUpModule(): if isinstance(Domain.get_db(), Mock): # needed to skip setUp for javascript tests thread on Travis return _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) _call_center_domain_mock.start() domain = create_domain('champ-cameroon') with override_settings(SERVER_ENVIRONMENT='production'): configs = StaticDataSourceConfiguration.by_domain(domain.name) adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: adapter.build_table() engine = connection_manager.get_engine(UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): with open(os.path.join(path, file_name), encoding='utf-8') as f: table_name = get_table_name(domain.name, file_name[:-4]) table = metadata.tables[table_name] postgres_copy.copy_from( f, table, engine, format='csv' if six.PY3 else b'csv', null='' if six.PY3 else b'', header=True ) _call_center_domain_mock.stop()
def catch_signal(sender, **kwargs): from fluff.pillow import get_fluff_pillow_configs if settings.UNIT_TESTING or kwargs['using'] != DEFAULT_DB_ALIAS: return table_pillow_map = {} for config in get_fluff_pillow_configs(): pillow = config.get_instance() for processor in pillow.processors: doc = processor.indicator_class() if doc.save_direct_to_sql: table_pillow_map[doc._table.name] = { 'doc': doc, 'pillow': pillow } print('\tchecking fluff SQL tables for schema changes') engine = connection_manager.get_engine('default') with engine.begin() as connection: migration_context = get_migration_context(connection, list(table_pillow_map)) raw_diffs = compare_metadata(migration_context, fluff_metadata) diffs = reformat_alembic_diffs(raw_diffs) tables_to_rebuild = get_tables_to_rebuild(diffs, list(table_pillow_map)) for table in tables_to_rebuild: info = table_pillow_map[table] rebuild_table(engine, info['pillow'], info['doc']) engine.dispose()
def _rebuild_sql_tables(self, adapters): tables_by_engine = defaultdict(dict) for adapter in adapters: sql_adapter = get_indicator_adapter(adapter.config) try: tables_by_engine[sql_adapter.engine_id][sql_adapter.get_table().name] = sql_adapter except BadSpecError: _soft_assert = soft_assert(to='{}@{}'.format('jemord', 'dimagi.com')) _soft_assert(False, "Broken data source {}".format(adapter.config.get_id)) _assert = soft_assert(notify_admins=True) _notify_rebuild = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): table_names = list(table_map) engine = connection_manager.get_engine(engine_id) diffs = get_table_diffs(engine, table_names, metadata) tables_to_act_on = get_tables_rebuild_migrate(diffs, table_names) for table_name in tables_to_act_on.rebuild: sql_adapter = table_map[table_name] if not sql_adapter.config.is_static: try: self.rebuild_table(sql_adapter) except TableRebuildError as e: _notify_rebuild(six.text_type(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter) migrate_tables(engine, diffs.raw, tables_to_act_on.migrate)
def _rebuild_sql_tables(self, adapters): # todo move this code to sql adapter rebuild_if_necessary tables_by_engine = defaultdict(dict) for adapter in adapters: sql_adapter = get_indicator_adapter(adapter.config) tables_by_engine[sql_adapter.engine_id][sql_adapter.get_table().name] = sql_adapter _assert = soft_assert(to='@'.join(['czue', 'dimagi.com'])) _notify_cory = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): engine = connection_manager.get_engine(engine_id) with engine.begin() as connection: migration_context = get_migration_context(connection, table_map.keys()) raw_diffs = compare_metadata(migration_context, metadata) diffs = reformat_alembic_diffs(raw_diffs) tables_to_rebuild = get_tables_to_rebuild(diffs, table_map.keys()) for table_name in tables_to_rebuild: sql_adapter = table_map[table_name] if not sql_adapter.config.is_static: try: rev_before_rebuild = sql_adapter.config.get_db().get_rev(sql_adapter.config._id) self.rebuild_table(sql_adapter) except TableRebuildError, e: _notify_cory(unicode(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter)
def _rebuild_sql_tables(self, adapters): tables_by_engine = defaultdict(dict) all_adapters = [] for adapter in adapters: if getattr(adapter, 'all_adapters', None): all_adapters.extend(adapter.all_adapters) else: all_adapters.append(adapter) for adapter in all_adapters: tables_by_engine[adapter.engine_id][adapter.get_table().name] = adapter _assert = soft_assert(notify_admins=True) _notify_rebuild = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): table_names = list(table_map) engine = connection_manager.get_engine(engine_id) diffs = get_table_diffs(engine, table_names, get_metadata(engine_id)) tables_to_act_on = get_tables_rebuild_migrate(diffs) for table_name in tables_to_act_on.rebuild: pillow_logging.debug("[rebuild] Rebuilding table: %s", table_name) sql_adapter = table_map[table_name] table_diffs = [diff for diff in diffs if diff.table_name == table_name] if not sql_adapter.config.is_static: try: self.rebuild_table(sql_adapter, table_diffs) except TableRebuildError as e: _notify_rebuild(str(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter, table_diffs) self.migrate_tables(engine, diffs, tables_to_act_on.migrate, table_map)
def _rebuild_sql_tables(self, adapters): tables_by_engine = defaultdict(dict) for adapter in adapters: tables_by_engine[adapter.engine_id][adapter.get_table().name] = adapter _assert = soft_assert(to='@'.join(['czue', 'dimagi.com'])) _notify_cory = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): engine = connection_manager.get_engine(engine_id) with engine.begin() as connection: migration_context = get_migration_context(connection, table_map.keys()) raw_diffs = compare_metadata(migration_context, metadata) diffs = reformat_alembic_diffs(raw_diffs) tables_to_rebuild = get_tables_to_rebuild(diffs, table_map.keys()) for table_name in tables_to_rebuild: sql_adapter = table_map[table_name] if not sql_adapter.config.is_static: try: rev_before_rebuild = sql_adapter.config.get_db().get_rev(sql_adapter.config._id) self.rebuild_table(sql_adapter) except TableRebuildError, e: _notify_cory(unicode(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter)
def handle(self, **options): data_sources = list(DataSourceConfiguration.all()) data_sources.extend(list(StaticDataSourceConfiguration.all())) tables_by_engine_id = self._get_tables_by_engine_id(data_sources, options.get('engine_id')) tables_to_remove_by_engine = defaultdict(list) for engine_id, expected_tables in tables_by_engine_id.items(): engine = connection_manager.get_engine(engine_id) with engine.begin() as connection: results = connection.execute(""" SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND ( table_name LIKE '{}%%' OR table_name LIKE '{}%%' ); """.format(UCR_TABLE_PREFIX, LEGACY_UCR_TABLE_PREFIX)).fetchall() tables_in_db = {r[0] for r in results} tables_to_remove_by_engine[engine_id] = tables_in_db - expected_tables for engine_id, tablenames in tables_to_remove_by_engine.items(): print("\nTables no longer referenced in database: {}:\n".format(engine_id)) engine = connection_manager.get_engine(engine_id) if not tablenames: print("\t No tables to prune") continue for tablename in tablenames: if options['show_counts']: with engine.begin() as connection: try: result = connection.execute( 'SELECT COUNT(*), MAX(inserted_at) FROM "{tablename}"'.format(tablename=tablename) ) except Exception: print("\t{}: no inserted_at column, probably not UCR".format(tablename)) else: print("\t{}: {}".foramt(tablename, result.fetchone())) else: print("\t{}".format(tablename))
def cleanup_misc_agg_tables(): engine = connection_manager.get_engine(ICDS_UCR_CITUS_ENGINE_ID) with engine.begin() as connection: metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) for name in ('ucr_table_name_mapping', 'awc_location', 'awc_location_local'): table = metadata.tables[name] delete = table.delete() connection.execute(delete)
def setUpClass(cls): super(IntraHealthTestCase, cls).setUpClass() cls.engine = connection_manager.get_engine(DEFAULT_ENGINE_ID) cls.domain = create_domain(TEST_DOMAIN) cls.region_type = LocationType.objects.create(domain=TEST_DOMAIN, name='Région') cls.district_type = LocationType.objects.create(domain=TEST_DOMAIN, name='District') cls.pps_type = LocationType.objects.create(domain=TEST_DOMAIN, name='PPS') cls.region = make_location(domain=TEST_DOMAIN, name='Test region', location_type='Région') cls.region.save() cls.district = make_location(domain=TEST_DOMAIN, name='Test district', location_type='District', parent=cls.region) cls.district.save() cls.pps = make_location(domain=TEST_DOMAIN, name='Test PPS', location_type='PPS', parent=cls.district) cls.pps.save() cls.mobile_worker = CommCareUser.create(domain=TEST_DOMAIN, username='******', password='******', phone_number='777777') cls.mobile_worker.location_id = cls.pps.get_id cls.mobile_worker.save() cls.product = Product(_id='81457658bdedd663f8b0bdadb19d8f22', name='ASAQ Nourisson', domain=TEST_DOMAIN) cls.product2 = Product(_id='81457658bdedd663f8b0bdadb19d83d8', name='ASAQ Petit Enfant', domain=TEST_DOMAIN) cls.product.save() cls.product2.save() cls.recap_table = RecapPassageFluff._table cls.intra_table = IntraHealthFluff._table cls.taux_rupt_table = TauxDeRuptureFluff._table cls.livraison_table = LivraisonFluff._table cls.taux_sat_table = TauxDeSatisfactionFluff._table cls.couverture_table = CouvertureFluff._table with cls.engine.begin() as connection: cls.recap_table.create(connection, checkfirst=True) cls.intra_table.create(connection, checkfirst=True) cls.taux_rupt_table.create(connection, checkfirst=True) cls.livraison_table.create(connection, checkfirst=True) cls.taux_sat_table.create(connection, checkfirst=True) cls.couverture_table.create(connection, checkfirst=True)
def generate_dump_script(self, source_engine_id): self.seen_tables = set() source_engine = connection_manager.get_engine(source_engine_id) # direct dump and load from parent + child tables with source_engine.begin() as source_conn: insp = sqlinspect(source_conn) for table in keep_child_tables + plain_tables: if table in self.all_tables: for line in self.get_table_date_target(insp, table): self.insert_row(line) # direct dump and load from parent # dump from all child tables into parent table for table in drop_child_tables: if table in self.all_tables: for line in self.get_table_date_target(insp, table, all_in_parent=True): self.insert_row(line) for datasource in StaticDataSourceConfiguration.by_domain( DASHBOARD_DOMAIN): if source_engine_id == datasource.engine_id or source_engine_id in datasource.mirrored_engine_ids: adapter = get_indicator_adapter(datasource) table_name = adapter.get_table().name # direct dump and load from parent # dump from all child tables into parent table # - if table is distrubuted, citus will distribute the data # - if table is partitioned the triggers on the parent will distribute the data for line in self.get_table_date_target(insp, table_name, all_in_parent=True): self.insert_row(line) remaining_tables = self.all_tables - self.seen_tables - IGNORE_TABLES icds_ucr_prefix = '{}{}_'.format(UCR_TABLE_PREFIX, DASHBOARD_DOMAIN) def keep_table(table): root_table = self.child_parent_mapping.get(table, table) return not root_table.startswith( UCR_TABLE_PREFIX) or root_table.startswith(icds_ucr_prefix) remaining_tables = list(filter(keep_table, remaining_tables)) if remaining_tables: self.stderr.write("Some tables not seen:") for t in remaining_tables: parent = self.child_parent_mapping.get(t) if parent: self.stderr.write("\t{} (parent: {})".format( t, parent)) else: self.stderr.write("\t{}".format(t))
def drop_tables(apps, schema_editor): metadata = MetaData(bind=connection_manager.get_engine()) for table_name in [ 'fluff_OPMHierarchyFluff', 'fluff_OpmCaseFluff', 'fluff_OpmFormFluff', 'fluff_OpmHealthStatusAllInfoFluff', 'fluff_VhndAvailabilityFluff', ]: Table(table_name, metadata).drop(checkfirst=True)
def setUpClass(cls): cls.engine = connection_manager.get_engine() cls.metadata = sqlalchemy.MetaData() cls.table_name = 'diff_table_' + uuid.uuid4().hex sqlalchemy.Table( cls.table_name, cls.metadata, sqlalchemy.Column('user_id', sqlalchemy.Integer, primary_key=True), sqlalchemy.Column('user_name', sqlalchemy.String(16), nullable=False), sqlalchemy.Column('email_address', sqlalchemy.String(60), key='email'), sqlalchemy.Column('password', sqlalchemy.String(20), nullable=False), ) cls.metadata.create_all(cls.engine)
def _assert_migrated(apps, schema_editor): for engine_id, data_sources in _data_sources_by_engine_id().items(): with connection_manager.get_engine(engine_id).begin() as conn: for data_source in data_sources: legacy_table_name = get_legacy_table_name(data_source) new_table_name = get_table_name(data_source.domain, data_source.table_id) if (table_exists(conn, legacy_table_name) and not table_exists(conn, new_table_name)): print("") print(AUTO_MIGRATE_FAILED_MESSAGE) sys.exit(1)
def setUpClass(cls): super(TestAlembicDiffs, cls).setUpClass() cls.engine = connection_manager.get_engine() cls.metadata = sqlalchemy.MetaData() cls.table_name = 'diff_table_' + uuid.uuid4().hex sqlalchemy.Table( cls.table_name, cls.metadata, sqlalchemy.Column('user_id', sqlalchemy.Integer, primary_key=True), sqlalchemy.Column('user_name', sqlalchemy.String(16), nullable=False), sqlalchemy.Column('email_address', sqlalchemy.String(60), key='email'), sqlalchemy.Column('password', sqlalchemy.String(20), nullable=False), ) cls.metadata.create_all(cls.engine)
def _rebuild_sql_tables(self, adapters): tables_by_engine = defaultdict(dict) all_adapters = [] for adapter in adapters: if getattr(adapter, 'all_adapters', None): all_adapters.extend(adapter.all_adapters) else: all_adapters.append(adapter) for adapter in all_adapters: try: tables_by_engine[adapter.engine_id][ adapter.get_table().name] = adapter except BadSpecError: _soft_assert = soft_assert( to='{}@{}'.format('jemord', 'dimagi.com')) _soft_assert( False, "Broken data source {}".format(adapter.config.get_id)) _assert = soft_assert(notify_admins=True) _notify_rebuild = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): table_names = list(table_map) engine = connection_manager.get_engine(engine_id) diffs = get_table_diffs(engine, table_names, get_metadata(engine_id)) tables_to_act_on = get_tables_rebuild_migrate(diffs, table_names) for table_name in tables_to_act_on.rebuild: pillow_logging.debug("[rebuild] Rebuilding table: %s", table_name) sql_adapter = table_map[table_name] table_diffs = [ diff for diff in diffs.formatted if diff.table_name == table_name ] if not sql_adapter.config.is_static: try: self.rebuild_table(sql_adapter, table_diffs) except TableRebuildError as e: _notify_rebuild(six.text_type(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter, table_diffs) migration_diffs = diffs.filter(tables_to_act_on.migrate) self.migrate_tables(engine, migration_diffs.raw, tables_to_act_on.migrate, table_map)
def tearDownModule(): _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) domain = Domain.get_by_name('test-pna') engine = connection_manager.get_engine(UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): table_name = get_table_name(domain.name, file_name[:-4]) table = metadata.tables[table_name] table.drop() _call_center_domain_mock.start() domain.delete() _call_center_domain_mock.stop()
def _assert_migrated(apps, schema_editor): if settings.UNIT_TESTING: return for engine_id, data_sources in _data_sources_by_engine_id().items(): with connection_manager.get_engine(engine_id).begin() as conn: for data_source in data_sources: legacy_table_name = get_legacy_table_name(data_source) new_table_name = get_table_name(data_source.domain, data_source.table_id) if ( table_exists(conn, legacy_table_name) and not table_exists(conn, new_table_name) ): print("") print(AUTO_MIGRATE_FAILED_MESSAGE) sys.exit(1)
def setUpClass(cls): super(IntraHealthTestCase, cls).setUpClass() cls.engine = connection_manager.get_engine('default') cls.domain = create_domain(TEST_DOMAIN) cls.region_type = LocationType.objects.create(domain=TEST_DOMAIN, name='Région') cls.district_type = LocationType.objects.create(domain=TEST_DOMAIN, name='District') cls.pps_type = LocationType.objects.create(domain=TEST_DOMAIN, name='PPS') cls.region = make_location(domain=TEST_DOMAIN, name='Test region', location_type='Région') cls.region.save() cls.district = make_location( domain=TEST_DOMAIN, name='Test district', location_type='District', parent=cls.region ) cls.district.save() cls.pps = make_location(domain=TEST_DOMAIN, name='Test PPS', location_type='PPS', parent=cls.district) cls.pps.save() cls.mobile_worker = create_mobile_worker( domain=TEST_DOMAIN, username='******', password='******', phone_number='777777' ) cls.mobile_worker.location_id = cls.pps.get_id cls.mobile_worker.save() cls.product = Product(_id='81457658bdedd663f8b0bdadb19d8f22', name='ASAQ Nourisson', domain=TEST_DOMAIN) cls.product2 = Product( _id='81457658bdedd663f8b0bdadb19d83d8', name='ASAQ Petit Enfant', domain=TEST_DOMAIN ) cls.product.save() cls.product2.save() cls.recap_table = RecapPassageFluff._table cls.intra_table = IntraHealthFluff._table cls.taux_rupt_table = TauxDeRuptureFluff._table cls.livraison_table = LivraisonFluff._table cls.taux_sat_table = TauxDeSatisfactionFluff._table cls.couverture_table = CouvertureFluff._table with cls.engine.begin() as connection: cls.recap_table.create(connection, checkfirst=True) cls.intra_table.create(connection, checkfirst=True) cls.taux_rupt_table.create(connection, checkfirst=True) cls.livraison_table.create(connection, checkfirst=True) cls.taux_sat_table.create(connection, checkfirst=True) cls.couverture_table.create(connection, checkfirst=True)
def _rename_tables(dry_run=False, tables_by_engine=None, confirm=False, engine_id=None): tables_by_engine = tables_by_engine or _get_old_new_tablenames(engine_id) for engine_id, dsconfs in tables_by_engine.items(): engine = connection_manager.get_engine(engine_id) print('\tChecking {} tables in engine "{}"'.format( len(dsconfs), engine_id)) for dsconf in dsconfs: with engine.begin() as conn: executed = False rename = table_exists(conn, dsconf.old_table) and not table_exists( conn, dsconf.new_table) if rename: drop_partition = rename_children = None if dsconf.config.sql_settings.partition_config: drop_partition = _get_drop_partitioning_features_sql( conn, dsconf, dry_run) rename_children = _get_rename_child_tables_sql( conn, dsconf, dry_run) drop_view_rename_table = """ {drop_partition} {rename_children} DROP VIEW IF EXISTS "{new_table}"; ALTER TABLE "{old_table}" RENAME TO "{new_table}"; """.format(drop_partition=drop_partition or '', rename_children=rename_children or '', old_table=dsconf.old_table, new_table=dsconf.new_table) print('\t\tRenaming table "{}" to "{}"'.format( dsconf.old_table, dsconf.new_table)) executed = _run_sql_with_logging(conn, drop_view_rename_table, dry_run, confirm) # do this outside the previous transaction to avoid deadlock if rename and executed and dsconf.config.sql_settings.partition_config: print('\t\tReinstalling partitioning on "{}"'.format( dsconf.new_table)) if not dry_run: dsconf.adapter._install_partition()
def handle(self, output_database, source_engine_id, **options): with connection_manager.get_engine(source_engine_id).begin() as conn: self.all_tables = get_all_tables(conn) self.parent_child_mapping = get_parent_child_mapping(conn) self.child_parent_mapping = { child: parent for parent, children in self.parent_child_mapping.items() for child in children } self.table_count = 0 self.db = sqlite3.connect(output_database) try: self.setup_sqlite_db() self.generate_dump_script(source_engine_id) self.stdout.write("\n{} tables processed\n".format(self.table_count)) finally: self.db.close()
def partition_child_health(): engine = connection_manager.get_engine(ICDS_UCR_CITUS_ENGINE_ID) queries = [ "ALTER TABLE child_health_monthly RENAME TO child_health_old_partition", "CREATE TABLE child_health_monthly (LIKE child_health_old_partition) PARTITION BY LIST (month)", "SELECT create_distributed_table('child_health_monthly', 'supervisor_id')", "ALTER TABLE child_health_monthly ATTACH PARTITION child_health_old_partition DEFAULT" ] with engine.begin() as connection: # check if we have already partitioned this table (necessary for reusedb) q = connection.execute("select exists (select * from pg_tables where tablename='child_health_old_partition')") if q.first()[0]: return for query in queries: connection.execute(query) for view in get_view_migrations(): with open(view.sql, "r", encoding='utf-8') as sql_file: sql_to_execute = sql_file.read() connection.execute(sql_to_execute)
def load_data(): engine = connection_manager.get_engine('default') metadata.bind = engine user_table.drop(engine, checkfirst=True) region_table.drop(engine, checkfirst=True) metadata.create_all() user_data = [ {"user": "******", "date": date(2013, 1, 1), "indicator_a": 1, "indicator_b": 0, "indicator_c": 1, "indicator_d": 1}, {"user": "******", "date": date(2013, 2, 1), "indicator_a": 0, "indicator_b": 1, "indicator_c": 1, "indicator_d": 1}, {"user": "******", "date": date(2013, 1, 1), "indicator_a": 0, "indicator_b": 1, "indicator_c": 1, "indicator_d": 2}, {"user": "******", "date": date(2013, 2, 1), "indicator_a": 1, "indicator_b": 0, "indicator_c": 1, "indicator_d": 2}, ] region_data = [ {"region": "region1", "sub_region": "region1_a", "date": date(2013, 1, 1), "indicator_a": 1, "indicator_b": 0}, {"region": "region1", "sub_region": "region1_a", "date": date(2013, 2, 1), "indicator_a": 1, "indicator_b": 1}, {"region": "region1", "sub_region": "region1_b", "date": date(2013, 1, 1), "indicator_a": 0, "indicator_b": 1}, {"region": "region1", "sub_region": "region1_b", "date": date(2013, 2, 1), "indicator_a": 0, "indicator_b": 0}, {"region": "region2", "sub_region": "region2_a", "date": date(2013, 1, 1), "indicator_a": 0, "indicator_b": 1}, {"region": "region2", "sub_region": "region2_a", "date": date(2013, 2, 1), "indicator_a": 1, "indicator_b": 1}, {"region": "region2", "sub_region": "region2_b", "date": date(2013, 1, 1), "indicator_a": 1, "indicator_b": 0}, {"region": "region2", "sub_region": "region2_b", "date": date(2013, 2, 1), "indicator_a": 0, "indicator_b": 0}, ] connection = engine.connect() try: connection.execute(user_table.delete()) connection.execute(region_table.delete()) for d in user_data: insert = user_table.insert().values(**d) connection.execute(insert) for d in region_data: insert = region_table.insert().values(**d) connection.execute(insert) finally: connection.close() engine.dispose()
def tearDownModule(): if isinstance(Domain.get_db(), Mock): # needed to skip setUp for javascript tests thread on Travis return _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) domain = Domain.get_by_name('test-pna') engine = connection_manager.get_engine(UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): table_name = get_table_name(domain.name, file_name[:-4]) table = metadata.tables[table_name] table.drop() _call_center_domain_mock.start() domain.delete() _call_center_domain_mock.stop()
def tearDownModule(): if isinstance(Domain.get_db(), Mock): # needed to skip setUp for javascript tests thread on Travis return _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) domain = Domain.get_by_name('up-nrhm') engine = connection_manager.get_engine(UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): table_name = get_table_name(domain.name, file_name[:-4]) table = metadata.tables[table_name] table.drop() _call_center_domain_mock.start() domain.delete() _call_center_domain_mock.stop()
def create_ucr_views(dry_run=False, tables_by_engine=None): tables_by_engine = tables_by_engine or _get_old_new_tablenames() for engine_id, dsconfs in tables_by_engine.items(): print('\tChecking {} tables in engine "{}"'.format( len(dsconfs), engine_id)) engine = connection_manager.get_engine(engine_id) view_creates = [] with engine.begin() as conn: for dsconf in dsconfs: if _should_add_view(conn, dsconf.old_table, dsconf.new_table): view_creates.append( 'CREATE VIEW "{}" AS SELECT * FROM "{}";'.format( dsconf.new_table, dsconf.old_table)) if view_creates: print('\tCreating {} views in engine "{}"'.format( len(view_creates), engine_id)) with engine.begin() as conn: _run_sql_with_logging(conn, '\n'.join(view_creates), dry_run) else: print('\tNo views to create in engine "{}"'.format(engine_id))
def rebuild_tables_if_necessary(self): tables_by_engine = defaultdict(dict) for adapter in self.table_adapters: tables_by_engine[adapter.engine_id][adapter.get_table().name] = adapter _assert = soft_assert(to='@'.join(['czue', 'dimagi.com'])) _notify_cory = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): engine = connection_manager.get_engine(engine_id) with engine.begin() as connection: migration_context = get_migration_context(connection, table_map.keys()) diffs = compare_metadata(migration_context, metadata) tables_to_rebuild = get_tables_to_rebuild(diffs, table_map.keys()) for table_name in tables_to_rebuild: sql_adapter = table_map[table_name] if not is_static(sql_adapter.config._id): try: rev_before_rebuild = sql_adapter.config.get_db().get_rev(sql_adapter.config._id) self.rebuild_table(sql_adapter) except TableRebuildError, e: _notify_cory(unicode(e), sql_adapter.config.to_json()) else: # note: this fancy logging can be removed as soon as we get to the # bottom of http://manage.dimagi.com/default.asp?211297 # if no signs of it popping back up by april 2016, should remove this rev_after_rebuild = sql_adapter.config.get_db().get_rev(sql_adapter.config._id) _notify_cory( u'rebuilt table {} ({}) because {}. rev before: {}, rev after: {}'.format( table_name, u'{} [{}]'.format(sql_adapter.config.display_name, sql_adapter.config._id), diffs, rev_before_rebuild, rev_after_rebuild, ), sql_adapter.config.to_json(), ) else: self.rebuild_table(sql_adapter)
def get_growth_monitoring_details(domain, case_id): engine = connection_manager.get_engine(ICDS_UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) ucr_table = metadata.tables[get_table_name(domain, 'static-child_health_cases')] chm_table = metadata.tables['child_health_monthly'] select_query = select([ ucr_table.c.case_id, ucr_table.c.name, ucr_table.c.mother_name, ucr_table.c.dob, ucr_table.c.sex, chm_table.c.age_in_months, chm_table.c.recorded_weight, chm_table.c.recorded_height, ]).select_from( ucr_table.join( chm_table, chm_table.c.case_id == ucr_table.c.doc_id)).where( chm_table.c.case_id == case_id).order_by('month asc') return select_query.execute()
def _rebuild_sql_tables(self, adapters): tables_by_engine = defaultdict(dict) all_adapters = [] for adapter in adapters: if getattr(adapter, 'all_adapters', None): all_adapters.extend(adapter.all_adapters) else: all_adapters.append(adapter) for adapter in all_adapters: try: tables_by_engine[adapter.engine_id][adapter.get_table().name] = adapter except BadSpecError: _soft_assert = soft_assert(to='{}@{}'.format('jemord', 'dimagi.com')) _soft_assert(False, "Broken data source {}".format(adapter.config.get_id)) _assert = soft_assert(notify_admins=True) _notify_rebuild = lambda msg, obj: _assert(False, msg, obj) for engine_id, table_map in tables_by_engine.items(): table_names = list(table_map) engine = connection_manager.get_engine(engine_id) diffs = get_table_diffs(engine, table_names, get_metadata(engine_id)) tables_to_act_on = get_tables_rebuild_migrate(diffs, table_names) for table_name in tables_to_act_on.rebuild: pillow_logging.debug("[rebuild] Rebuilding table: %s", table_name) sql_adapter = table_map[table_name] if not sql_adapter.config.is_static: try: self.rebuild_table(sql_adapter) except TableRebuildError as e: _notify_rebuild(six.text_type(e), sql_adapter.config.to_json()) else: self.rebuild_table(sql_adapter) self.migrate_tables(engine, diffs.raw, tables_to_act_on.migrate, table_map)
def get_sql_engine(cls): engine = getattr(cls, '_engine', None) if not engine: cls._engine = connection_manager.get_engine('default') return cls._engine
def __init__(self, database_name): self.database_name = database_name # use db1 engine to create db2 http://stackoverflow.com/a/8977109/8207 self.root_engine = connection_manager.get_engine('default')
def setUpClass(cls): super(ReportTestCase, cls).setUpClass() cls.domain = create_domain('test-domain') cls.web_user = WebUser.get_by_username('test') if not cls.web_user: cls.web_user = WebUser.create('test-domain', 'test', 'test') SQLProduct.objects.create( domain=cls.domain.name, name='Product 2', code='p2', product_id='p2' ) SQLProduct.objects.create( domain=cls.domain.name, name='Product 3', code='p3', product_id='p3' ) SQLProduct.objects.create( domain=cls.domain.name, name='Product 1', code='p1', product_id='p1' ) region_location_type = LocationType.objects.create( domain=cls.domain.name, name='Region', ) SQLLocation.objects.create( domain=cls.domain.name, name='Region 1', location_id='r1', location_type=region_location_type ) district_location_type = LocationType.objects.create( domain=cls.domain.name, name='District', ) SQLLocation.objects.create( domain=cls.domain.name, name='District 1', location_id='d1', location_type=district_location_type ) cls.engine = connection_manager.get_engine('default') cls.intra_table = IntraHealthFluff._table cls.couverture_table = CouvertureFluff._table cls.taux_table = TauxDeRuptureFluff._table cls.taux_de_satisfaction_table = TauxDeSatisfactionFluff._table cls.livraison_table = LivraisonFluff._table cls.recouvrement_table = RecouvrementFluff._table with cls.engine.begin() as connection: cls.couverture_table.create(connection, checkfirst=True) cls.intra_table.create(connection, checkfirst=True) cls.taux_table.create(connection, checkfirst=True) cls.taux_de_satisfaction_table.create(connection, checkfirst=True) cls.recouvrement_table.create(connection, checkfirst=True) cls.livraison_table.create(connection, checkfirst=True) insert = cls.intra_table.insert().values([ dict( doc_id='1', date=datetime(2017, 11, 17), product_name='Product 1', product_id='p1', district_id='d1', district_name='District 1', location_id='pps1', region_id='r1', PPS_name='PPS 1', actual_consumption_total=10, billed_consumption_total=5, stock_total=33, total_stock_total=70, quantity_total=13, cmm_total=16 ), dict( doc_id='1', date=datetime(2017, 11, 17), product_name='Product 2', product_id='p2', district_id='d1', district_name='District 1', location_id='pps1', region_id='r1', PPS_name='PPS 1', actual_consumption_total=2, billed_consumption_total=2, stock_total=1, total_stock_total=2, quantity_total=3, cmm_total=4 ), dict( doc_id='1', date=datetime(2017, 11, 17), product_name='Product 3', product_id='p3', district_id='d1', district_name='District 1', location_id='pps1', region_id='r1', PPS_name='PPS 1', actual_consumption_total=6, billed_consumption_total=4, stock_total=14, total_stock_total=0, quantity_total=88, cmm_total=99 ), dict( doc_id='2', date=datetime(2017, 11, 17), product_name='Product 1', product_id='p1', district_id='d1', district_name='District 1', location_id='pps2', region_id='r1', PPS_name='PPS 2', actual_consumption_total=13, billed_consumption_total=11, stock_total=50, total_stock_total=100, quantity_total=1, cmm_total=8 ), dict( doc_id='2', date=datetime(2017, 11, 17), product_name='Product 2', product_id='p2', district_id='d1', district_name='District 1', location_id='pps2', region_id='r1', PPS_name='PPS 2', actual_consumption_total=0, billed_consumption_total=0, stock_total=2, total_stock_total=17, quantity_total=3, cmm_total=15 ), dict( doc_id='2', date=datetime(2017, 11, 17), product_name='Product 3', product_id='p3', district_id='d1', district_name='District 1', location_id='pps2', region_id='r1', PPS_name='PPS 2', actual_consumption_total=150, billed_consumption_total=11, stock_total=4, total_stock_total=0, quantity_total=11, cmm_total=12 ) ]) connection.execute(insert)
def setUpModule(): if settings.USE_PARTITIONED_DATABASE: print('============= WARNING: not running test setup because settings.USE_PARTITIONED_DATABASE is True.') return _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) _call_center_domain_mock.start() domain = create_domain('icds-cas') location_type = LocationType.objects.create( domain=domain.name, name='block', ) SQLLocation.objects.create( domain=domain.name, name='b1', location_id='b1', location_type=location_type ) state_location_type = LocationType.objects.create( domain=domain.name, name='state', ) SQLLocation.objects.create( domain=domain.name, name='st1', location_id='st1', location_type=state_location_type ) SQLLocation.objects.create( domain=domain.name, name='st2', location_id='st2', location_type=state_location_type ) SQLLocation.objects.create( domain=domain.name, name='st3', location_id='st3', location_type=state_location_type ) SQLLocation.objects.create( domain=domain.name, name='st4', location_id='st4', location_type=state_location_type ) SQLLocation.objects.create( domain=domain.name, name='st5', location_id='st5', location_type=state_location_type ) SQLLocation.objects.create( domain=domain.name, name='st6', location_id='st6', location_type=state_location_type ) SQLLocation.objects.create( domain=domain.name, name='st7', location_id='st7', location_type=state_location_type ) awc_location_type = LocationType.objects.create( domain=domain.name, name='awc', ) SQLLocation.objects.create( domain=domain.name, name='a7', location_id='a7', location_type=awc_location_type ) with override_settings(SERVER_ENVIRONMENT='icds'): configs = StaticDataSourceConfiguration.by_domain('icds-cas') adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: try: adapter.drop_table() except Exception: pass adapter.build_table() engine = connection_manager.get_engine(ICDS_UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): with open(os.path.join(path, file_name), encoding='utf-8') as f: table_name = FILE_NAME_TO_TABLE_MAPPING[file_name[:-4]] table = metadata.tables[table_name] if not table_name.startswith('icds_dashboard_'): columns = [ '"{}"'.format(c.strip()) # quote to preserve case for c in f.readline().split(',') ] postgres_copy.copy_from( f, table, engine, format='csv' if six.PY3 else b'csv', null='' if six.PY3 else b'', columns=columns ) for state_id in ('st1', 'st2'): _aggregate_child_health_pnc_forms(state_id, datetime(2017, 3, 31)) _aggregate_gm_forms(state_id, datetime(2017, 3, 31)) _aggregate_bp_forms(state_id, datetime(2017, 3, 31)) try: move_ucr_data_into_aggregation_tables(datetime(2017, 5, 28), intervals=2) build_incentive_report(agg_date=datetime(2017, 5, 28)) except Exception as e: print(e) tearDownModule() raise finally: _call_center_domain_mock.stop()
def setUpModule(): if isinstance(Domain.get_db(), Mock): # needed to skip setUp for javascript tests thread on Travis return _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) _call_center_domain_mock.start() domain = create_domain('test-pna') region_location_type = LocationType.objects.create( domain='test-pna', name='R\u00e9gion', ) SQLLocation.objects.create( domain='test-pna', name='Region Test', location_id='8cde73411ddc4488a7f913c99499ead4', location_type=region_location_type ) SQLLocation.objects.create( domain='test-pna', name='PASSY', location_id='1991b4dfe166335e342f28134b85fcac', location_type=region_location_type ) SQLLocation.objects.create( domain='test-pna', name='r1', location_id='0682630532ff25717176320482ff1028', location_type=region_location_type ) SQLLocation.objects.create( domain='test-pna', name='r2', location_id='582c5d65a307baa7a38e7b5e651fd5fc', location_type=region_location_type ) SQLLocation.objects.create( domain='test-pna', name='r3', location_id='bd0395ba4a4fbd38c90765bd04208a8f', location_type=region_location_type ) SQLLocation.objects.create( domain='test-pna', name='r4', location_id='6ed1f958fccd1b8202e8e30851a2b326', location_type=region_location_type ) SQLLocation.objects.create( domain='test-pna', name='r5', location_id='1991b4dfe166335e342f28134b85f516', location_type=region_location_type ) district_location_type = LocationType.objects.create( domain='test-pna', name='District', ) SQLLocation.objects.create( domain='test-pna', name='District Test', location_id='3db74fac2bad4e708e2b03800cc5ab73', location_type=district_location_type ) pps_location_type = LocationType.objects.create( domain='test-pna', name='PPS', ) SQLLocation.objects.create( domain='test-pna', name='P2', location_id='ccf4430f5c3f493797486d6ce1c39682', location_type=pps_location_type ) SQLProduct.objects.create( domain='test-pna', name='Collier', code='product1', product_id='product1' ) SQLProduct.objects.create( domain='test-pna', name='CU', code='product2', product_id='product2' ) SQLProduct.objects.create( domain='test-pna', name='Depo-Provera', code='product3', product_id='product3' ) SQLProduct.objects.create( domain='test-pna', name='DIU', code='product4', product_id='product4' ) SQLProduct.objects.create( domain='test-pna', name='Jadelle', code='product5', product_id='product5' ) SQLProduct.objects.create( domain='test-pna', name='Microgynon/Lof.', code='product6', product_id='product6' ) SQLProduct.objects.create( domain='test-pna', name='Microlut/Ovrette', code='product7', product_id='product7' ) SQLProduct.objects.create( domain='test-pna', name='Preservatif Feminin', code='product8', product_id='product8' ) SQLProduct.objects.create( domain='test-pna', name='Preservatif Masculin', code='product9', product_id='product9' ) SQLProduct.objects.create( domain='test-pna', name='Sayana Press', code='product10', product_id='product10' ) SQLProduct.objects.create( domain='test-pna', name='IMPLANON', code='product11', product_id='product11' ) SQLProduct.objects.create( domain='test-pna', name='Product 7', code='p7', product_id='p7' ) with override_settings(SERVER_ENVIRONMENT='production'): configs = StaticDataSourceConfiguration.by_domain(domain.name) adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: adapter.build_table() engine = connection_manager.get_engine(UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): with open(os.path.join(path, file_name), encoding='utf-8') as f: table_name = get_table_name(domain.name, file_name[:-4]) table = metadata.tables[table_name] postgres_copy.copy_from( f, table, engine, format='csv' if six.PY3 else b'csv', null='' if six.PY3 else b'', header=True ) _call_center_domain_mock.stop()