def test_get_columns_using_enumerator(self,
database: PostgresqlExtDatabase,
database_table: Tuple[str, str]):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
columns = enumerators.get_columns_used_by_enumerator(enum_name)
assert len(columns) == 0
schema_name = database_table[0]
table_name = database_table[1]
column_name = "test_column"
sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}"
database.execute_sql(sql_add_column)
columns = enumerators.get_columns_used_by_enumerator(enum_name)
assert len(columns) == 1
expected_data = {
"schema_name": schema_name,
"table_name": table_name,
"column_name": column_name
}
assert columns[0] == expected_data
assert enumerators.is_enumerator_used_by_any_column(enum_name) is True
def test_create_and_drop_enumerator(self, database: PostgresqlExtDatabase):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
enumerators.drop_enumerator(enum_name)
sql_select = self.build_select_enum_sql(enum_name)
cursor = database.execute_sql(sql_select)
rows = cursor.fetchall()
assert len(rows) == 0
def test_create_enumerator_duplicate(self,
database: PostgresqlExtDatabase):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
with pytest.raises(peewee.ProgrammingError):
enumerators.create_enumerator(enum_name)
sql_select = self.build_select_enum_sql(enum_name)
cursor = database.execute_sql(sql_select)
rows = cursor.fetchall()
assert len(rows) == 1
def test_create_enumerator_string_values(self,
database: PostgresqlExtDatabase):
enum_name = TEST_ENUM_NAME
enum_values = ("v1", "v2", "v3")
enumerators.create_enumerator(enum_name, enum_values)
sql_select = self.build_select_enum_sql(enum_name)
cursor = database.execute_sql(sql_select)
rows = cursor.fetchall()
assert len(rows) == 1
actual_values = self.get_enum_values(database, enum_name)
assert actual_values == enum_values
def test_drop_enumerator_which_is_used_by_columns(
self, database: PostgresqlExtDatabase, database_table: Tuple[str,
str]):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
schema_name = database_table[0]
table_name = database_table[1]
column_name = "test_column"
sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}"
database.execute_sql(sql_add_column)
with pytest.raises(peewee.InternalError):
enumerators.drop_enumerator(enum_name)
def test_is_enumerator_used_by_any_column(self,
database: PostgresqlExtDatabase,
database_table: Tuple[str, str]):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
assert enumerators.is_enumerator_used_by_any_column(enum_name) is False
schema_name = database_table[0]
table_name = database_table[1]
column_name = "test_column"
sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}"
database.execute_sql(sql_add_column)
assert enumerators.is_enumerator_used_by_any_column(enum_name) is True
def add_dict_column(self, column_name: str, values: List[Any] = None):
if self.column_exists(column_name):
raise ValueError("column exists")
values = [] or values
if not Dict.dict_values_length_valid(values):
raise ValueError('dict value must be 63 characters or less')
with self.db.atomic():
enumerator_name = f"{column_name}_{uuid.uuid4()}"
enumerator_name = enumerator_name.replace("-", "_")
enumerators.create_enumerator(enumerator_name, values)
sql = SQL("ALTER TABLE {} ADD COLUMN {} {};").format(
Identifier(self.name), Identifier(column_name),
SQL(enumerator_name))
Dict.create(layer_id=self.lid,
column_name=column_name,
enumerator_name=enumerator_name)
self.execute(sql)
def set_values(self, values: List[Any]):
cloud = get_cloud()
table_name = cloud.unhash_name(self.layer_id)
current_values = set(enumerators.get_values_of_enumerator(self.enumerator_name))
values_to_be_removed = current_values - set(values)
sql_set_type_to_text = psysql.SQL("ALTER TABLE {} ALTER COLUMN {} TYPE TEXT").format(
psysql.Identifier(table_name), psysql.Identifier(self.column_name)
)
values_to_be_removed_sql = [psysql.Literal(str(value)) for value in values_to_be_removed]
sql_set_null = psysql.SQL("UPDATE {table_name} SET {column_name} = NULL WHERE {column_name} IN ({values})").format(
table_name=psysql.Identifier(table_name),
column_name=psysql.Identifier(self.column_name),
values=psysql.SQL(",").join(values_to_be_removed_sql)
)
sql_drop_old_enumerator = psysql.SQL("DROP TYPE {}").format(psysql.Identifier(self.enumerator_name))
sql_set_type_to_enumerator = psysql.SQL("""ALTER TABLE {table_name} ALTER COLUMN {column_name}
TYPE {enumerator_name} USING {column_name}::{enumerator_name}""").format(
table_name=psysql.Identifier(table_name), column_name=psysql.Identifier(self.column_name),
enumerator_name=psysql.Identifier(self.enumerator_name)
)
db = self._meta.database
with db.atomic():
db.execute_sql(sql_set_type_to_text)
db.execute_sql(sql_drop_old_enumerator)
if len(values_to_be_removed) > 0:
db.execute_sql(sql_set_null)
enumerators.create_enumerator(self.enumerator_name, values)
db.execute_sql(sql_set_type_to_enumerator)