| Title: | Query the Attentional Control Data Collection or the Truth Effect Database |
|---|---|
| Description: | Interact with the Attentional Control Data Collection (ACDC) or the Truth Effect Database (TED). Download the databases using download_acdc() or download_ted(), 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.2.3 |
| Built: | 2026-05-21 08:32:12 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.
## Not run: conn <- connect_to_db("path/to/db") # 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)" ) ## End(Not run)## Not run: conn <- connect_to_db("path/to/db") # 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)" ) ## End(Not run)
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, filter_variables = NULL )add_join_paths_to_query( conn, filter_statements, join_path_list, argument_sequence, requested_vars = NULL, filter_variables = 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 |
filter_variables |
A character vector specifying the variables needed for filtering |
A SQL query string that represents the joined tables and requested variables.
Compares the SHA256 hash of a local ACDC SQLite database (acdc.db)
with the hash stored in the latest (or specified) release on
jstbcs/acdc-database.
check_acdc( local_sqlite_path, tag = NULL, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )check_acdc( local_sqlite_path, tag = NULL, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )
local_sqlite_path |
Path to the local |
tag |
Optional release tag. If NULL, uses latest release. |
token |
Optional GitHub token. Defaults to |
algo |
Hash algorithm used for comparison (default: |
Returns TRUE if the local database is up to date, FALSE if it is outdated.
## Not run: check_acdc("acdc.db") ## End(Not run)## Not run: check_acdc("acdc.db") ## End(Not run)
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).
Compares the SHA256 hash of a local TED SQLite database (ted.db)
with the hash stored in the latest (or specified) release on
SLesche/truth-effect-database.
check_ted( local_sqlite_path, tag = NULL, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )check_ted( local_sqlite_path, tag = NULL, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )
local_sqlite_path |
Path to the local |
tag |
Optional release tag. If NULL, uses latest release. |
token |
Optional GitHub token. Defaults to |
algo |
Hash algorithm used for comparison (default: |
Returns TRUE if the local database is up to date, FALSE if it is outdated.
## Not run: check_ted("ted.db") ## End(Not run)## Not run: check_ted("ted.db") ## End(Not run)
Compares the SHA256 hash of a local SQLite database file with the hash file stored in a GitHub release.
compare_sqlite_to_release( owner, repo, asset_name, local_sqlite_path, tag = NULL, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )compare_sqlite_to_release( owner, repo, asset_name, local_sqlite_path, tag = NULL, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )
owner |
Repository owner. |
repo |
Repository name. |
asset_name |
Name of the SQLite asset (used to derive hash filename). |
local_sqlite_path |
Path to local SQLite database. |
tag |
Optional release tag. If NULL, uses latest release. |
token |
Optional GitHub token. Defaults to |
algo |
Hash algorithm (default: |
This avoids downloading the full database when checking for updates.
TRUE if hashes match, FALSE otherwise.
## Not run: compare_sqlite_to_release( owner = "myuser", repo = "myrepo", asset_name = "database.sqlite", local_sqlite_path = "database.sqlite" ) ## End(Not run)## Not run: compare_sqlite_to_release( owner = "myuser", repo = "myrepo", asset_name = "database.sqlite", local_sqlite_path = "database.sqlite" ) ## End(Not run)
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.
# When connecting to a specific file, like the downloaded ACDC-Database # just use the path to the database # If the specified database file does not exist, an error will be thrown. # Consider downloading the database using `download_acdc()` or `download_ted()`. ## Not run: conn <- connect_to_db("path/to/database.db")# When connecting to a specific file, like the downloaded ACDC-Database # just use the path to the database # If the specified database file does not exist, an error will be thrown. # Consider downloading the database using `download_acdc()` or `download_ted()`. ## Not run: conn <- connect_to_db("path/to/database.db")
Computes a SHA256 hash for a SQLite database file and writes it to
a .sha256 file. The hash file can be uploaded alongside the database
in a GitHub release to allow version comparison without downloading
the full database.
create_sqlite_hash_file(sqlite_path, output_file = NULL, algo = "sha256")create_sqlite_hash_file(sqlite_path, output_file = NULL, algo = "sha256")
sqlite_path |
Path to the SQLite file. |
output_file |
Optional output path for the hash file.
Defaults to |
algo |
Hash algorithm (default: |
Invisibly returns the path to the hash file.
## Not run: create_sqlite_hash_file("database.sqlite") ## End(Not run)## Not run: create_sqlite_hash_file("database.sqlite") ## End(Not run)
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.
Downloads the acdc.db SQLite database from the latest (or specified)
release on jstbcs/acdc-database to a given directory.
download_acdc(dest_dir, tag = NULL, token = Sys.getenv("GITHUB_PAT"))download_acdc(dest_dir, tag = NULL, token = Sys.getenv("GITHUB_PAT"))
dest_dir |
Directory where the database should be saved. |
tag |
Optional release tag. If NULL, uses latest release. |
token |
Optional GitHub token. Defaults to |
Invisibly returns the full path to the downloaded file.
## Not run: download_acdc(tempdir()) ## End(Not run)## Not run: download_acdc(tempdir()) ## End(Not run)
Downloads a SQLite database file from the latest or a specific GitHub release.
download_sqlite_release( owner, repo, asset_name, dest_dir, tag = NULL, algo = "sha256", token = Sys.getenv("GITHUB_PAT"), overwrite = TRUE )download_sqlite_release( owner, repo, asset_name, dest_dir, tag = NULL, algo = "sha256", token = Sys.getenv("GITHUB_PAT"), overwrite = TRUE )
owner |
Repository owner. |
repo |
Repository name. |
asset_name |
Name of the SQLite asset in the release. |
dest_dir |
Local directory where the file should be saved. |
tag |
Optional release tag (e.g., |
algo |
Hash algorithm (default: |
token |
Optional GitHub token. Defaults to |
overwrite |
Whether to overwrite existing file (default: TRUE). |
Invisibly returns the full path to the downloaded file.
## Not run: download_sqlite_release( owner = "myuser", repo = "myrepo", asset_name = "database.sqlite", dest_dir = tempdir() ) ## End(Not run)## Not run: download_sqlite_release( owner = "myuser", repo = "myrepo", asset_name = "database.sqlite", dest_dir = tempdir() ) ## End(Not run)
Downloads the ted.db SQLite database from the latest (or specified)
release on SLesche/truth-effect-database to a given directory.
download_ted(dest_dir, tag = NULL, token = Sys.getenv("GITHUB_PAT"))download_ted(dest_dir, tag = NULL, token = Sys.getenv("GITHUB_PAT"))
dest_dir |
Directory where the database should be saved. |
tag |
Optional release tag. If NULL, uses latest release. |
token |
Optional GitHub token. Defaults to |
Invisibly returns the full path to the downloaded file.
## Not run: download_ted(tempdir()) ## End(Not run)## Not run: download_ted(tempdir()) ## End(Not run)
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.
## Not run: conn <- connect_to_db("path/to/database.db") arguments <- add_argument(list(), "publication_id", "greater", 0) target_vars <- c("default", "n_participants") target_table <- "study_table" results <- query_db(conn, arguments, target_vars, target_table) ## End(Not run)## Not run: conn <- connect_to_db("path/to/database.db") arguments <- add_argument(list(), "publication_id", "greater", 0) target_vars <- c("default", "n_participants") target_table <- "study_table" results <- query_db(conn, arguments, target_vars, target_table) ## End(Not run)
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.
Checks whether the local ACDC SQLite database (acdc.db) is up to date
with the release on jstbcs/acdc-database and optionally downloads
the latest version.
update_acdc( local_sqlite_path, tag = NULL, update = FALSE, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )update_acdc( local_sqlite_path, tag = NULL, update = FALSE, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )
local_sqlite_path |
Path to the local |
tag |
Optional release tag. If NULL, uses latest release. |
update |
Logical; if TRUE, will download the latest database when the local copy is outdated (default: FALSE). |
token |
Optional GitHub token. Defaults to |
algo |
Hash algorithm (default: |
Invisibly returns TRUE if up to date, FALSE if outdated.
## Not run: update_acdc("acdc.db") update_acdc("acdc.db", update = TRUE) ## End(Not run)## Not run: update_acdc("acdc.db") update_acdc("acdc.db", update = TRUE) ## End(Not run)
Internal helper function that compares a local SQLite database with a GitHub release and optionally downloads the latest version.
update_database( owner, repo, asset_name, local_sqlite_path, tag = NULL, token = Sys.getenv("GITHUB_PAT"), algo = "sha256", update = FALSE )update_database( owner, repo, asset_name, local_sqlite_path, tag = NULL, token = Sys.getenv("GITHUB_PAT"), algo = "sha256", update = FALSE )
owner |
GitHub repository owner. |
repo |
GitHub repository name. |
asset_name |
Name of the SQLite file in the release. |
local_sqlite_path |
Path to the local SQLite database. |
tag |
Optional release tag. If NULL, uses latest release. |
token |
Optional GitHub token. Defaults to |
algo |
Hash algorithm to use (default: |
update |
Logical; if TRUE, will download the latest database when the local copy is outdated (default: FALSE). |
Invisibly returns TRUE if the local database is up to date,
FALSE if it is outdated (and possibly updated if update = TRUE).
Checks whether the local TED SQLite database (ted.db) is up to date
with the release on SLesche/truth-effect-database and optionally downloads
the latest version.
update_ted( local_sqlite_path, tag = NULL, update = FALSE, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )update_ted( local_sqlite_path, tag = NULL, update = FALSE, token = Sys.getenv("GITHUB_PAT"), algo = "sha256" )
local_sqlite_path |
Path to the local |
tag |
Optional release tag. If NULL, uses latest release. |
update |
Logical; if TRUE, will download the latest database when the local copy is outdated (default: FALSE). |
token |
Optional GitHub token. Defaults to |
algo |
Hash algorithm (default: |
Invisibly returns TRUE if up to date, FALSE if outdated.
## Not run: update_ted("ted.db") update_ted("ted.db", update = TRUE) ## End(Not run)## Not run: update_ted("ted.db") update_ted("ted.db", update = TRUE) ## End(Not run)