def run_udf(
udf_creation_stmt: str,
udf_execution_query: str,
):
if udf_creation_stmt:
MonetDB().execute(udf_creation_stmt)
MonetDB().execute(udf_execution_query)
def create_remote_table(table_info: TableInfo, monetdb_socket_address: str):
columns_schema = convert_schema_to_sql_query_format(table_info.schema)
MonetDB().execute(f"""
CREATE REMOTE TABLE {table_info.name}
( {columns_schema}) ON 'mapi:monetdb://{monetdb_socket_address}/{node_config.monetdb.database}'
WITH USER 'monetdb' PASSWORD 'monetdb'
""")
def _drop_udfs_by_context_id(context_id: str):
"""
Drops all functions of specific context_id from the DB.
Parameters
----------
context_id : str
The id of the experiment
"""
function_names = MonetDB().execute_and_fetchall(f"""
SELECT name FROM functions
WHERE name LIKE '%{context_id.lower()}%'
AND system = false
""")
for name in function_names:
MonetDB().execute(f"DROP FUNCTION {name[0]}")
def get_table_schema(table_name: str) -> TableSchema:
"""
Retrieves a schema for a specific table name from the monetdb.
Parameters
----------
table_name : str
The name of the table
Returns
------
TableSchema
A schema which is TableSchema object.
"""
schema = MonetDB().execute_and_fetchall(f"""
SELECT columns.name, columns.type
FROM columns
RIGHT JOIN tables
ON tables.id = columns.table_id
WHERE
tables.name = '{table_name}'
AND
tables.system=false
""")
if not schema:
raise TablesNotFound([table_name])
return TableSchema([
ColumnInfo(name, _convert_monet2mip_column_type(table_type))
for name, table_type in schema
])
def get_non_existing_tables(table_names: List[str]) -> List[str]:
names_clause = str(table_names)[1:-1]
existing_tables = MonetDB().execute_and_fetchall(
f"SELECT name FROM tables WHERE name IN ({names_clause})"
)
existing_table_names = [table[0] for table in existing_tables]
return [name for name in table_names if name not in existing_table_names]
def insert_data_to_table(table_name: str,
table_values: List[List[Union[str, int, float]]]):
row_length = len(table_values[0])
if all(len(row) != row_length for row in table_values):
raise Exception("Row counts does not match")
params_format = ", ".join(("%s", ) * row_length)
sql_clause = "INSERT INTO %s VALUES (%s)" % (table_name, params_format)
MonetDB().execute(query=sql_clause, parameters=table_values, many=True)
def validate_tables_can_be_merged(tables_names: List[str]):
table_names = ",".join(f"'{table}'" for table in tables_names)
distinct_table_types = MonetDB().execute_and_fetchall(f"""
SELECT DISTINCT(type)
FROM tables
WHERE
system = false
AND
name in ({table_names})""")
if len(distinct_table_types) != 1:
raise IncompatibleTableTypes(distinct_table_types)
def _drop_table_by_type_and_context_id(table_type: str, context_id: str):
"""
Drops all tables of specific type with name that contain a specific context_id from the DB.
Parameters
----------
table_type : str
The type of the table
context_id : str
The id of the experiment
"""
table_names_and_types = MonetDB().execute_and_fetchall(f"""
SELECT name, type FROM tables
WHERE name LIKE '%{context_id.lower()}%'
AND tables.type = {str(_convert_mip2monet_table_type(table_type))}
AND system = false
""")
for name, table_type in table_names_and_types:
if table_type == _convert_mip2monet_table_type("view"):
MonetDB().execute(f"DROP VIEW {name}")
else:
MonetDB().execute(f"DROP TABLE {name}")
def create_view(
view_name: str,
table_name: str,
columns: List[str],
filters: dict,
):
filter_clause = ""
if filters:
filter_clause = f"WHERE {build_filter_clause(filters)}"
columns_clause = ", ".join(columns)
MonetDB().execute(f"""
CREATE VIEW {view_name}
AS SELECT {columns_clause}
FROM {table_name}
{filter_clause}
""")
def create_view(view_name: str,
table_name: str,
columns: List[str],
datasets: List[str] = None):
# TODO: Add filters argument
# TODO: With filters dataset_clause will be deleted because it will be a part of the filters
dataset_clause = ""
if datasets is not None:
dataset_names = ",".join(f"'{dataset}'" for dataset in datasets)
dataset_clause = f"WHERE dataset IN ({dataset_names})"
columns_clause = ", ".join(columns)
MonetDB().execute(f"""
CREATE VIEW {view_name}
AS SELECT {columns_clause}
FROM {table_name}
{dataset_clause}""")
def add_to_merge_table(merge_table_name: str, table_names: List[str]):
non_existing_tables = get_non_existing_tables(table_names)
table_infos = [TableInfo(name, get_table_schema(name)) for name in table_names]
try:
for name in table_names:
MonetDB().execute(
f"ALTER TABLE {merge_table_name} ADD TABLE {name.lower()}"
)
except pymonetdb.exceptions.OperationalError as exc:
if str(exc).startswith("3F000"):
raise IncompatibleSchemasMergeException(table_infos)
elif str(exc).startswith("42S02"):
raise TablesNotFound(non_existing_tables)
else:
raise exc
def get_table_names(table_type: str, context_id: str) -> List[str]:
"""
Retrieves a list of table names, which contain the context_id from the monetdb.
Parameters
----------
table_type : str
The type of the table
context_id : str
The id of the experiment
Returns
------
List[str]
A list of table names.
"""
table_names = MonetDB().execute_and_fetchall(f"""
SELECT name FROM tables
WHERE
type = {str(_convert_mip2monet_table_type(table_type))} AND
name LIKE '%{context_id.lower()}%' AND
system = false""")
return [table[0] for table in table_names]
def get_table_data(
table_name: str) -> List[List[Union[str, int, float, bool]]]:
"""
Retrieves the data of a table with specific name from the monetdb.
Parameters
----------
table_name : str
The name of the table
Returns
------
List[List[Union[str, int, float, bool]]
The data of the table.
"""
data = MonetDB().execute_and_fetchall(f"""
SELECT {table_name}.*
FROM {table_name}
INNER JOIN tables ON tables.name = '{table_name}'
WHERE tables.system=false
""")
return data
def create_merge_table(table_info: TableInfo):
columns_schema = convert_schema_to_sql_query_format(table_info.schema)
MonetDB().execute(
f"CREATE MERGE TABLE {table_info.name} ( {columns_schema} )")