| Title: | Query the Attentional Control Data Collection |
|---|---|
| Description: | Interact with the Attentional Control Data Collection (ACDC). Connect to the database via connect_to_db(), set filter arguments via add_argument() and query the database via query_db(). |
| Authors: | Sven Lesche [aut, cre, cph], Julia M. Haaf [ctb, ths], Madlen Hoffstadt [ctb] |
| Maintainer: | Sven Lesche <[email protected]> |
| License: | GPL (>= 3) |
| Version: | 1.0.1 |
| Built: | 2025-01-27 05:55:54 UTC |
| Source: | https://github.com/slesche/acdc-query |
This function adds an argument to a list containing filter arguments later used to select data from the database. When supplying the variable used for filtering, the operator and the value, an SQL query will be constructed for the user and added as the next object to the list of arguments.#' When supplying only variable, operator and value, a SQL query will be constructed for the user and added as the next object to a list. Alternatively, the user may specify an SQL query manually.
add_argument(list, conn, variable, operator, values, statement = NULL)add_argument(list, conn, variable, operator, values, statement = NULL)
list |
The list to which the argument will be added. |
conn |
The connection object or database connection string. |
variable |
The variable name to be used in the argument. |
operator |
The operator to be used in the argument (i.e., "greater", "between", "equal", "less"). |
values |
The values to be used in the argument. |
statement |
The manual SQL query to be used. |
A list object with the new argument (SQL query) added.
conn <- connect_to_db(":memory:") mtcars$mtcars_id = 1:nrow(mtcars) example_data = data.frame( example_id = 1:150, mtcars_id = rep(1:30, each = 5), example_value = runif(150, 0, 1) ) DBI::dbWriteTable(conn, "mtcars_table", mtcars) DBI::dbWriteTable(conn, "example_table", example_data) # Initializing argument list arguments = list() # Using "equal" operator arguments = add_argument( list = arguments, conn = conn, variable = "cyl", operator = "equal", values = c(4, 6) ) # Using "greater" operator arguments = add_argument( list = arguments, conn = conn, variable = "cyl", operator = "greater", values = 2 ) # Using "between" operator arguments = add_argument( list = arguments, conn = conn, variable = "cyl", operator = "between", values = c(2, 8) ) # Manully constructing a filter statement manual_arguments = add_argument( list = arguments, conn = conn, statement = "SELECT mtcars_id FROM mtcars WHERE cyl = 4 OR cyl = 6)" )conn <- connect_to_db(":memory:") mtcars$mtcars_id = 1:nrow(mtcars) example_data = data.frame( example_id = 1:150, mtcars_id = rep(1:30, each = 5), example_value = runif(150, 0, 1) ) DBI::dbWriteTable(conn, "mtcars_table", mtcars) DBI::dbWriteTable(conn, "example_table", example_data) # Initializing argument list arguments = list() # Using "equal" operator arguments = add_argument( list = arguments, conn = conn, variable = "cyl", operator = "equal", values = c(4, 6) ) # Using "greater" operator arguments = add_argument( list = arguments, conn = conn, variable = "cyl", operator = "greater", values = 2 ) # Using "between" operator arguments = add_argument( list = arguments, conn = conn, variable = "cyl", operator = "between", values = c(2, 8) ) # Manully constructing a filter statement manual_arguments = add_argument( list = arguments, conn = conn, statement = "SELECT mtcars_id FROM mtcars WHERE cyl = 4 OR cyl = 6)" )
This function generates an SQL query based on a specified connection, argument, and join path list. It constructs a query that performs joins on multiple tables according to the provided join path, incorporating requested variables and filter conditions as needed.
add_join_paths_to_query( conn, filter_statements, join_path_list, argument_sequence, requested_vars = NULL )add_join_paths_to_query( conn, filter_statements, join_path_list, argument_sequence, requested_vars = NULL )
conn |
The connection object or database connection string. |
filter_statements |
The SQL-Filter statements extracted from the filter arguments list via 'get_filter_statement()'. |
join_path_list |
A list representing the join path. Each element of the list should be a data frame describing a step in the join path with columns: "table_to_join", "method", and "common_var". |
argument_sequence |
A numeric vector representing the AND/OR sequence of arguments. |
requested_vars |
A character vector specifying the variables to be selected from the final query result.
If |
A SQL query string that represents the joined tables and requested variables.
This function checks the validity of the operator and values used in a condition.
check_operator(operator, values)check_operator(operator, values)
operator |
The operator to be checked. |
values |
The values to be checked. |
NULL (no explicit return value).
This function establishes a connection to an SQLite database file located at the specified path using the DBI and RSQLite packages.
connect_to_db(path_to_db)connect_to_db(path_to_db)
path_to_db |
The path to the SQLite database file. |
A database connection object.
# Connect to a SQLite database file in memory conn <- connect_to_db(":memory:") # When connecting to a specific file, like the downloaded ACDC-Database # just use the path to the database ## Not run: conn <- connect_to_db("path/to/database.db") # Want the most recent version of the database? # Download it at https://github.com/jstbcs/acdc-database/blob/main/acdc.db# Connect to a SQLite database file in memory conn <- connect_to_db(":memory:") # When connecting to a specific file, like the downloaded ACDC-Database # just use the path to the database ## Not run: conn <- connect_to_db("path/to/database.db") # Want the most recent version of the database? # Download it at https://github.com/jstbcs/acdc-database/blob/main/acdc.db
This function identifies the steps in a join path where new IDs are introduced, allowing you to determine at which join steps each ID variable is added to the query. It returns a data frame with information about newly discovered IDs and the corresponding join step in the path.
discover_id_introduction_steps(conn, full_path_dataframe)discover_id_introduction_steps(conn, full_path_dataframe)
conn |
The connection object or database connection string. |
full_path_dataframe |
A data frame representing the full join path, including columns: "table_to_join", "method", and "common_var". |
A data frame with information about newly discovered IDs and the corresponding join step.
This function finds the relevant database tables that contain a specified column.
find_relevant_tables(conn, column_name, info = NULL, strict = FALSE)find_relevant_tables(conn, column_name, info = NULL, strict = FALSE)
conn |
The connection object or database connection string. |
column_name |
The name of the column to search for in the database tables. |
info |
Optional. The information data frame obtained from |
strict |
Should only one table be returned? Relevant for id variables |
A character vector containing the names of the relevant tables.
This function returns the sequence of arguments based on the specified argument relation. The argument relation determines the logical relationship between the arguments (e.g., "and", "or").
get_argument_sequence(arguments, argument_relation)get_argument_sequence(arguments, argument_relation)
arguments |
The list of arguments. |
argument_relation |
The specified argument relation. If "and", the sequence will be 1:length(arguments). If "or", the sequence will be rep(1, length(arguments)). If a vector is provided, it should have the same length as the number of arguments. |
A numeric vector representing the sequence of arguments.
This function retrieves the column names from all tables in the specified database connection.
get_column_names(conn)get_column_names(conn)
conn |
The connection object or database connection string. |
A data frame containing the column names and corresponding table names.
This function constructs a SQL filter statement based on the provided filter statements and argument sequence.
get_filter_statement(filter_statements, argument_sequence, introduction_table)get_filter_statement(filter_statements, argument_sequence, introduction_table)
filter_statements |
A character vector of SQL filter statements, one for each argument in the argument sequence. |
argument_sequence |
A numeric vector representing the argument sequence for constructing the filter statement. |
introduction_table |
A data frame containing information about table prefixes for ID variables. |
A character string representing the constructed SQL filter statement.
This function creates a valid SQL statement based on the specified variable, operator, and values. It handles different operators such as "greater", "less", "equal", and "between".
make_valid_sql(conn, variable, operator, values)make_valid_sql(conn, variable, operator, values)
conn |
The connection object or database connection string. |
variable |
The variable for which the SQL statement is created. |
operator |
The operator to be used in the SQL statement. |
values |
The values to be used in the SQL statement. |
A character string representing the valid SQL statement.
This function precomputes join paths for all tables in a given database using a combination of forward and backward joins. It generates a list of data frames representing the join paths for each table, including information about tables to join, walk approaches (forward or backward), and common variables used for joining.
precompute_table_join_paths(conn, input_table = NULL, relevant_tables = NULL)precompute_table_join_paths(conn, input_table = NULL, relevant_tables = NULL)
conn |
The connection object or database connection string. |
input_table |
The table from which the join path is computed. |
relevant_tables |
A vector of tables that are relevant to the query. |
A list of join paths for each table in the database.
This function performs targeted queries on an SQLite database using specified filtering arguments and returns the query results. It extracts information about which tables of the database are relevant for the query and then joins these relevant tables to the target table. The function constructs an SQL query which incorporates both the joining and filtering target variables. This SQL statement is then applied to the database and the resulting dataframe is returned to the user.
query_db( conn, arguments, target_vars = "default", target_table = "observation_table", argument_relation = "and" )query_db( conn, arguments, target_vars = "default", target_table = "observation_table", argument_relation = "and" )
conn |
The connection object to an SQLite database. |
arguments |
A list of filtering arguments for the query. The list must have only one filter argument per list-entry. |
target_vars |
A character vector specifying the variables to be included in the query results. |
target_table |
The target table in the database for querying. |
argument_relation |
A character string specifying the relation between filtering arguments ("and" or "or" or a numerical vector with the same length as the number of arguments). Arguments with equal numbers in their index are joined using the OR operator, others using AND. To represent (A OR B) AND C AND D use the vector c(1, 1, 2, 3). |
A data frame containing the query results.
conn <- connect_to_db(":memory:") mtcars$mtcars_id = 1:nrow(mtcars) example_data = data.frame( example_id = 1:150, mtcars_id = rep(1:30, each = 5), example_value = runif(150, 0, 1) ) DBI::dbWriteTable(conn, "mtcars_table", mtcars) DBI::dbWriteTable(conn, "example_table", example_data) # Initializing argument list arguments = list() arguments = add_argument( list = arguments, conn = conn, variable = "cyl", operator = "equal", values = c(4, 6) ) arguments = add_argument( list = arguments, conn = conn, variable = "example_value", operator = "greater", values = 0.4 ) # Return specified variables target_vars = c("mtcars_id", "example_id", "cyl") query_results = query_db( conn = conn, arguments = arguments, target_vars = target_vars, target_table = "example_table", argument_relation = "and" ) # Return all variables in mtcars_table and example_value from example_table query_results = query_db( conn = conn, arguments = arguments, target_vars = c("default", "example_value"), target_table = "mtcars_table", argument_relation = "and" )conn <- connect_to_db(":memory:") mtcars$mtcars_id = 1:nrow(mtcars) example_data = data.frame( example_id = 1:150, mtcars_id = rep(1:30, each = 5), example_value = runif(150, 0, 1) ) DBI::dbWriteTable(conn, "mtcars_table", mtcars) DBI::dbWriteTable(conn, "example_table", example_data) # Initializing argument list arguments = list() arguments = add_argument( list = arguments, conn = conn, variable = "cyl", operator = "equal", values = c(4, 6) ) arguments = add_argument( list = arguments, conn = conn, variable = "example_value", operator = "greater", values = 0.4 ) # Return specified variables target_vars = c("mtcars_id", "example_id", "cyl") query_results = query_db( conn = conn, arguments = arguments, target_vars = target_vars, target_table = "example_table", argument_relation = "and" ) # Return all variables in mtcars_table and example_value from example_table query_results = query_db( conn = conn, arguments = arguments, target_vars = c("default", "example_value"), target_table = "mtcars_table", argument_relation = "and" )
This function generates the ID column name based on the provided table name. It replaces the "table" suffix with "id" to obtain the ID column name.
return_id_name_from_table(table_name)return_id_name_from_table(table_name)
table_name |
The name of the table. |
The generated ID column name.
This function generates the table name based on the provided ID column name. It replaces the "id" suffix with "table" to obtain the table name.
return_table_name_from_id(id_name)return_table_name_from_id(id_name)
id_name |
The name of the ID column. |
The generated table name.