Skip to contents

Mine table relationships from a collection of SQL scripts

Source: R/sq_sql_mine.R
sq_sql_mine.Rd

Iterates over a data.frame of SQL scripts (as returned by sq_read_sql_files), parses each one, and returns a combined data.frame of all table relationships found. Files that produce no relationships emit a message and are skipped rather than causing an error.

Usage

sq_sql_mine(scripts)

Arguments

scripts

A data.frame with at least columns filename and content, as returned by sq_read_sql_files.

Value

A data.frame with one row per table relationship and columns:

script_name

File the relationship was extracted from.

source_table

Primary read table (the FROM clause target).

source_alias

Alias of the source table, or NA if none.

source_is_cte

TRUE if source_table is a CTE defined in the same script.

target_table

JOIN target or INSERT write target.

target_alias

Alias of the target table, or NA if none.

target_is_cte

TRUE if target_table is a CTE defined in the same script.

relationship_type

"JOIN" or "INSERT".

join_type

e.g. "LEFT JOIN", "INNER JOIN"; NA for INSERTs.

on_condition

The ON predicate string; NA for INSERTs.

depth

Integer depth relative to CTE nesting. Base tables = 1.

Examples

if (FALSE) { # \dontrun{
scripts <- sq_read_sql_files("path/to/sql/folder")
relationships <- sq_sql_mine(scripts)
} # }