def __get_column_subquery(self, column_strategy):
if column_strategy.strategy_type == UpdateColumnStrategyTypes.EMPTY:
return "('')"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_EMAIL:
return f"( SELECT CONCAT(NEWID(), '@', NEWID(), '.com') )"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_LOGIN:
return f"( SELECT NEWID() )"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.FAKE_UPDATE:
return f"( SELECT TOP 1 [{column_strategy.qualifier}] FROM [{SEED_TABLE_NAME}] ORDER BY NEWID())"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.LITERAL:
return column_strategy.value
else:
raise UnsupportedColumnStrategyError(column_strategy)
def _get_column_subquery(seed_table_name, column_strategy):
if column_strategy.strategy_type == UpdateColumnStrategyTypes.EMPTY:
return "('')"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_EMAIL:
return f"( SELECT CONCAT({_RAND_MD5}, '@', {_RAND_MD5}, '.com') )"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_LOGIN:
return f"( SELECT {_RAND_MD5} )"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.FAKE_UPDATE:
return f"( SELECT `{column_strategy.qualifier}` FROM `{seed_table_name}` ORDER BY RAND() LIMIT 1)"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.LITERAL:
return column_strategy.value
else:
raise UnsupportedColumnStrategyError(column_strategy)
def _get_column_subquery(seed_table_name, column_strategy):
if column_strategy.strategy_type == UpdateColumnStrategyTypes.EMPTY:
return "('')"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_EMAIL:
return f"( SELECT CONCAT({_RAND_MD5}, '@', {_RAND_MD5}, '.com') WHERE \"updatetarget\"=\"updatetarget\" )"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_LOGIN:
return f"( SELECT {_RAND_MD5} WHERE \"updatetarget\"=\"updatetarget\" )"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.FAKE_UPDATE:
# Add a dummy "updatetarget" where clause to fool the postgres optimizer into running the subquery on every row
# instead of once for the whole query
# See Also https://www.simononsoftware.com/problem-with-random-in-postgresql-subselect/
return f"( SELECT {column_strategy.qualifier} FROM {seed_table_name} WHERE \"updatetarget\"=\"updatetarget\" ORDER BY RANDOM() LIMIT 1)"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.LITERAL:
return column_strategy.value
else:
raise UnsupportedColumnStrategyError(column_strategy)
def _get_column_subquery(seed_table_name, column_strategy):
if column_strategy.strategy_type == UpdateColumnStrategyTypes.EMPTY:
return "('')"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_EMAIL:
return f"( SELECT CONCAT({_RAND_MD5}, '@', {_RAND_MD5}, '.com') ORDER BY MD5(\"updatetarget\"::text) LIMIT 1)"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_LOGIN:
return f"( SELECT {_RAND_MD5} ORDER BY MD5(\"updatetarget\"::text) LIMIT 1)"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.FAKE_UPDATE:
column = column_strategy.qualifier
if column_strategy.sql_type:
column += "::" + column_strategy.sql_type
return f"( SELECT {column} FROM {seed_table_name} ORDER BY RANDOM(), MD5(\"updatetarget\"::text) LIMIT 1)"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.LITERAL:
return column_strategy.value
else:
raise UnsupportedColumnStrategyError(column_strategy)
def __get_column_subquery(self, column_strategy, table_name, column_name):
if column_strategy.strategy_type == UpdateColumnStrategyTypes.EMPTY:
return "('')"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_EMAIL:
return f"( SELECT CONCAT(NEWID(), '@', NEWID(), '.com') )"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.UNIQUE_LOGIN:
return f"( SELECT NEWID() )"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.FAKE_UPDATE:
column = f"[{column_strategy.qualifier}]"
if column_strategy.sql_type:
column = f"CAST({column} AS {column_strategy.sql_type})"
# Add WHERE LIKE % OR NULL to make subquery correlated with outer table, therefore uncachable
return f"( SELECT TOP 1 {column} FROM [{SEED_TABLE_NAME}] WHERE [{table_name}].[{column_name}] LIKE '%' OR [{table_name}].[{column_name}] IS NULL ORDER BY NEWID())"
elif column_strategy.strategy_type == UpdateColumnStrategyTypes.LITERAL:
return column_strategy.value
else:
raise UnsupportedColumnStrategyError(column_strategy)
def test_unsupport_column_strategy_error():
error = UnsupportedColumnStrategyError("COLUMN_STRATEGY_TYPE")