Add a filter argument to a list

Description

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.

Usage

add_argument(list, conn, variable, operator, values, statement = NULL)

Arguments

Value

A list object with the new argument (SQL query) added.

Examples

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)"
)

Add Join Paths to Query

Description

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.

Usage

add_join_paths_to_query(
  conn,
  filter_statements,
  join_path_list,
  argument_sequence,
  requested_vars = NULL
)

Arguments

Value

A SQL query string that represents the joined tables and requested variables.

Check validity of operator and values

Description

This function checks the validity of the operator and values used in a condition.

Usage

check_operator(operator, values)

Arguments

Value

NULL (no explicit return value).

Connect to an SQLite database

Description

This function establishes a connection to an SQLite database file located at the specified path using the DBI and RSQLite packages.

Usage

connect_to_db(path_to_db)

Arguments

Value

A database connection object.

Examples

# 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

Discover ID Introduction Steps

Description

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.

Usage

discover_id_introduction_steps(conn, full_path_dataframe)

Arguments

Value

A data frame with information about newly discovered IDs and the corresponding join step.

Find relevant tables based on column name

Description

This function finds the relevant database tables that contain a specified column.

Usage

find_relevant_tables(conn, column_name, info = NULL, strict = FALSE)

Arguments

Value

A character vector containing the names of the relevant tables.

Get argument sequence based on argument relation

Description

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").

Usage

get_argument_sequence(arguments, argument_relation)

Arguments

Value

A numeric vector representing the sequence of arguments.

Get column names from database tables

Description

This function retrieves the column names from all tables in the specified database connection.

Usage

get_column_names(conn)

Arguments

Value

A data frame containing the column names and corresponding table names.

Get Filter Statement

Description

This function constructs a SQL filter statement based on the provided filter statements and argument sequence.

Usage

get_filter_statement(filter_statements, argument_sequence, introduction_table)

Arguments

Value

A character string representing the constructed SQL filter statement.

Create a valid SQL statement based on variable, operator, and values

Description

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".

Usage

make_valid_sql(conn, variable, operator, values)

Arguments

Value

A character string representing the valid SQL statement.

Precompute Table Join Paths

Description

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.

Usage

precompute_table_join_paths(conn, input_table = NULL, relevant_tables = NULL)

Arguments

Value

A list of join paths for each table in the database.

Query Database

Description

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.

Usage

query_db(
  conn,
  arguments,
  target_vars = "default",
  target_table = "observation_table",
  argument_relation = "and"
)

Arguments

Value

A data frame containing the query results.

Examples

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"
)

Return ID column name from table name

Description

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.

Usage

return_id_name_from_table(table_name)

Arguments

Value

The generated ID column name.

Return table name from ID column name

Description

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.

Usage

return_table_name_from_id(id_name)

Arguments

Value

The generated table name.