6.10.1. pilotscope.DBController.BaseDBController
- class BaseDBController(config: pilotscope.PilotConfig.PilotConfig, echo=True)[source]
Bases:
abc.ABC- __init__(config: pilotscope.PilotConfig.PilotConfig, echo=True)[source]
- Parameters
config – The config of PilotScope including the config of database.
echo – if true, the more detailed information will be printed when executing the sql statement.
- abstract explain_physical_plan(sql)[source]
Get a physical plan from database’s optimizer for a given SQL query.
- Parameters
sql – The SQL query to be explained.
- abstract explain_execution_plan(sql)[source]
Get an execution plan from database’s optimizer for a given SQL query.
- Parameters
sql – The SQL query to be explained.
- abstract execute(sql, fetch=False)[source]
Execute a sql statement.
- Parameters
sql – A SQL statement to be executed.
fetch – fetch result or not. If True, the function will return a list of tuple representing the result of the sql.
- abstract set_hint(key, value)[source]
Set the value of each hint (i.e., the run-time config) when execute SQL queries. The hints can be used to control the behavior of the database system in a session.
For PostgreSQL, you can find all valid hints in https://www.postgresql.org/docs/13/runtime-config.html.
For Spark, you can find all valid hints (called conf in Spark) in https://spark.apache.org/docs/latest/configuration.html#runtime-sql-configuration
- Parameters
key – The key associated with the hint.
value – The value of the hint to be set.
- abstract create_index(index: pilotscope.Common.Index.Index)[source]
Create an index on columns index.columns of table index.table with name index.index_name.
- Parameters
index – a Index object including the information of the index
- abstract drop_index(index: pilotscope.Common.Index.Index)[source]
Drop an index by its index name.
- Parameters
index – an index that will be dropped
- abstract drop_all_indexes()[source]
Drop all indexes across all tables in the database. This will not delete the system indexes and unique indexes.
- abstract get_all_indexes_byte()[source]
Get the size of all indexes across all tables in the database in bytes. This will include the system indexes and unique indexes.
- Returns
the size of all indexes in bytes
- abstract get_table_indexes_byte(table_name)[source]
Get the size of all indexes on a table in bytes. This will include the system indexes and unique indexes.
- Parameters
table_name – a table name that the indexes belong to
- Returns
the size of all indexes on the table in bytes
- abstract get_index_byte(index: pilotscope.Common.Index.Index)[source]
Get the size of an index in bytes by its index name.
- Parameters
index – the index to get size
- Returns
the size of the index in bytes
- get_index_number(table)[source]
Get the number of indexes built on the specified table.
- Parameters
table – name of the table
- Returns
the number of index
- get_existed_indexes(table)[source]
Retrieves the existing index on the specified table. This will not include the system indexes and unique indexes.
- Parameters
table – the name of the table
- Returns
a list of pilotscope.common.Index
- get_all_indexes()[source]
Get all indexes across all tables in the database.
- Returns
a list of pilotscope.common.Index
- get_estimated_cost(sql, comment='')[source]
Get an estimated cost of a SQL query.
- Parameters
sql – The SQL query for which to estimate the cost.
comment – An optional comment to include with the query plan. Useful for debugging.
- Returns
The estimated total cost of executing the SQL query.
- create_table_if_absences(table_name, column_2_value, primary_key_column=None, enable_autoincrement_id_key=True)[source]
Create a table according to parameters if absences. This function will not insert any data into the table. The column names and types of the table will be inferred from column_2_value.
- Parameters
table_name – the name of the table you want to create
column_2_value – a dict, whose keys are the names of columns and values. This data will be used to infer the column names and types of the table.
primary_key_column – A column name in column_2_value. The corresponding column will be set as primary key. Otherwise, there will be no primary key.
enable_autoincrement_id_key – If it is True, the primary_key_column will be autoincrement. It is only meaningful when primary_key_column is not None.
- drop_table_if_exist(table_name)[source]
Try to drop table named table_name
- Parameters
table_name – the name of the table
- exist_table(table_name) → bool[source]
If the table named table_name exist or not
- Returns
the table named table_name exist, it returns True; otherwise, it returns False
- get_all_table_names()[source]
Retrieves a list of all table names in the database.
- Returns
A list of table names present in the database.
- insert(table_name, column_2_value: dict)[source]
Insert a new row into the table with each column’s value set as column_2_value.
- Parameters
table_name – the name of the table
column_2_value – a dict where the keys are column names and the values are the values to be inserted
- get_table_columns(table_name)[source]
Get all column names of a table
- Parameters
table_name – the names of the table
- Returns
the list of the names of column
- get_table_row_count(table_name)[source]
Get the row count of the table
- Parameters
table_name – the name of the table
- Returns
the row count
- get_column_max(table_name, column_name)[source]
Get the maximum of a column
- Parameters
table_name – the name of the table that the column belongs to
column_name – the name of the column
- Returns
the maximum, type of which is same as the data of the column
- get_column_min(table_name, column_name)[source]
Get the minimum of a column
- Parameters
table_name – the name of the table that the column belongs to
column_name – the name of the column
- Returns
the minimum, type of which is same as the data of the column