def test_full_query_region_codes_to_include_is_none(self) -> None:
"""Given the value None for region_codes_to_include, it returns a full query string that does not filter out
any rows.
"""
query_builder = CloudSqlSchemaTableRegionFilteredQueryBuilder(
StateBase,
self.fake_state_table,
self.mock_columns_to_include,
region_codes_to_include=None,
)
expected_query = (
f"SELECT {self.fake_state_table.name}.column1,{self.fake_state_table.name}.state_code "
f"FROM {self.fake_state_table.name}")
self.assertEqual(expected_query, query_builder.full_query())
def test_full_query_region_codes_to_include(self) -> None:
"""Given a list of region_codes_to_include, it returns a full query string that filters for rows matching the
the region codes to include.
"""
query_builder = CloudSqlSchemaTableRegionFilteredQueryBuilder(
StateBase,
self.fake_state_table,
self.mock_columns_to_include,
region_codes_to_include=["US_ND"],
)
expected_query = (
f"SELECT {self.fake_state_table.name}.column1,{self.fake_state_table.name}.state_code "
f"FROM {self.fake_state_table.name} WHERE state_code IN ('US_ND')")
self.assertEqual(expected_query, query_builder.full_query())
def test_full_query_region_codes_to_exclude(self) -> None:
"""Given a list of region_codes_to_excludes, it returns a full query string that filters out the excluded
region codes.
"""
query_builder = CloudSqlSchemaTableRegionFilteredQueryBuilder(
SchemaType.STATE,
self.fake_state_table,
self.mock_columns_to_include,
region_codes_to_exclude=["US_nd"],
)
expected_query = (
f"SELECT {self.fake_state_table.name}.column1,{self.fake_state_table.name}.state_code "
f"FROM {self.fake_state_table.name} "
"WHERE state_code NOT IN ('US_ND')")
self.assertEqual(expected_query, query_builder.full_query())
def get_table_export_query(self, table_name: str) -> str:
"""Return a formatted SQL query for a given table name
For association tables, it adds a region code column to the select statement through a join.
If there are region codes to exclude from the export, it either:
1. Returns a query that excludes rows by region codes from the table
2. Returns a query that joins an association table to its referred table to filter
by region code. It also includes the region code column in the association table's
select statement (i.e. state_code, region_code).
"""
table = get_table_class_by_name(table_name, self.sorted_tables)
columns = self._get_table_columns_to_export(table)
query_builder = \
CloudSqlSchemaTableRegionFilteredQueryBuilder(self.metadata_base, table, columns,
region_codes_to_exclude=self.region_codes_to_exclude)
return query_builder.full_query()
def test_full_query_association_table(self) -> None:
"""Given an association table with excluded region codes, it returns a full query string."""
query_builder = CloudSqlSchemaTableRegionFilteredQueryBuilder(
SchemaType.STATE,
self.fake_association_table,
self.mock_association_table_columns_to_include,
region_codes_to_exclude=["US_nd"],
)
expected_query = (
f"SELECT {self.fake_association_table.name}.column1_simple,"
f"{self.fake_association_table.name}.column1_complex,"
f"{self.fake_state_table.name}.state_code AS state_code "
f"FROM {self.fake_association_table.name} "
f"JOIN {self.fake_state_table.name} ON "
f"{self.fake_state_table.name}.column1 = "
f"{self.fake_association_table.name}.column1_simple "
"WHERE state_code NOT IN ('US_ND')")
self.assertEqual(expected_query, query_builder.full_query())
