| Title: | Collection of 'SQL' Utilities for 'T-SQL' and 'Postgresql' |
|---|---|
| Description: | Includes functions for interacting with common meta data fields, writing insert statements, calling functions, and more for 'T-SQL' and 'Postgresql'. |
| Authors: | Timothy Conwell [aut, cre] |
| Maintainer: | Timothy Conwell <[email protected]> |
| License: | GPL (>= 3) |
| Version: | 0.1.2 |
| Built: | 2025-02-05 02:39:31 UTC |
| Source: | https://github.com/cran/sqlHelpers |
Call a SQL function/procedure.
call_function(con, schema, function_name, args, dialect = "T-SQL", cast = TRUE)call_function(con, schema, function_name, args, dialect = "T-SQL", cast = TRUE)
con |
A database connection. |
schema |
A string, the schema to query. |
function_name |
A string, the function/procedure to query. |
args |
A named list or vector, names are the parameter names and values are the parameter values. |
dialect |
A string, "T-SQL" or "Postgresql"., |
cast |
TRUE/FALSE, if TRUE, will add SQL to cast the parameters to the specified type. |
A data.table.
call_function(con = NULL)call_function(con = NULL)
Connect to a database using a connection string via DBI/odbc.
connect( con_str = "Driver={PostgreSQL ANSI};Host=localhost;Port=5432;Database=postgres;" )connect( con_str = "Driver={PostgreSQL ANSI};Host=localhost;Port=5432;Database=postgres;" )
con_str |
A database connection string. |
A database connection.
connect(NULL)connect(NULL)
Generate a CREATE TABLE statement based on a data.frame, optionally execute the statement if con is not NULL.
create_table_from_data_frame(x, table_name, con = NULL)create_table_from_data_frame(x, table_name, con = NULL)
x |
A data.frame. |
table_name |
A string, the name of the SQL table to create. |
con |
A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery. |
A string, the CREATE TABLE statement; or the results retrieved by DBI::dbSendQuery after executing the statement.
create_table_from_data_frame(x = iris, table_name = "test")create_table_from_data_frame(x = iris, table_name = "test")
Generate a DROP TABLE statement, optionally execute the statement if con is not NULL.
drop_table(args, con = NULL)drop_table(args, con = NULL)
args |
A string, the arguments to add to the DROP TABLE statement. |
con |
A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery. |
A string, the DROP TABLE statement; or the results retrieved by DBI::dbSendQuery after executing the statement.
drop_table("sample")drop_table("sample")
Retrieve the columns/types in a table.
fetch_columns(con, schema, table)fetch_columns(con, schema, table)
con |
A database connection. |
schema |
A string, the schema to query. |
table |
A string, the table to query. |
A data.table.
fetch_columns(con = NULL)fetch_columns(con = NULL)
Retrieve the definition of a function/procedure.
fetch_function_definition(con, schema, function_name, type = "FUNCTION")fetch_function_definition(con, schema, function_name, type = "FUNCTION")
con |
A database connection. |
schema |
A string, the schema to query. |
function_name |
A string, the function/procedure to query. |
type |
A string, "FUNCTION" or "PROCEDURE". |
A data.table.
fetch_function_definition(con = NULL)fetch_function_definition(con = NULL)
Retrieve the output parameters of a function/procedure.
fetch_function_output_parameters(con, schema, function_name, type = "FUNCTION")fetch_function_output_parameters(con, schema, function_name, type = "FUNCTION")
con |
A database connection. |
schema |
A string, the schema to query. |
function_name |
A string, the function/procedure to query. |
type |
A string, "FUNCTION" or "PROCEDURE". |
A data.table.
fetch_function_output_parameters(con = NULL)fetch_function_output_parameters(con = NULL)
Retrieve the input parameters of a function/procedure.
fetch_function_parameters(con, schema, function_name, type = "FUNCTION")fetch_function_parameters(con, schema, function_name, type = "FUNCTION")
con |
A database connection. |
schema |
A string, the schema to query. |
function_name |
A string, the function/procedure to query. |
type |
A string, "FUNCTION" or "PROCEDURE". |
A data.table.
fetch_function_parameters(con = NULL)fetch_function_parameters(con = NULL)
Retrieve the tables in a schema
fetch_tables(con, schema)fetch_tables(con, schema)
con |
A database connection. |
schema |
A string, the schema to query. |
A data.table.
fetch_tables(con = NULL)fetch_tables(con = NULL)
Helper function for INSERT
insert_batch_chunker(x, n_batches, batch_size)insert_batch_chunker(x, n_batches, batch_size)
x |
A vector of data to insert. |
n_batches |
Integer, the number of batches needed to insert the data. |
batch_size |
Integer, the size of each batch. |
A list.
insert_batch_chunker(c(1, 2, 3), 1, 100)insert_batch_chunker(c(1, 2, 3), 1, 100)
Generate a INSERT statement, optionally execute the statement if con is not NULL.
insert_values( x = NULL, schema = NULL, table, returning = NULL, quote_text = TRUE, cast = TRUE, types = NULL, batch_size = 1000, con = NULL, table_is_temporary = FALSE, retain_insert_order = FALSE, n_cores = 1, connect_db_name = NULL, dialect = "T-SQL" )insert_values( x = NULL, schema = NULL, table, returning = NULL, quote_text = TRUE, cast = TRUE, types = NULL, batch_size = 1000, con = NULL, table_is_temporary = FALSE, retain_insert_order = FALSE, n_cores = 1, connect_db_name = NULL, dialect = "T-SQL" )
x |
A list, data.frame or data.table, names must match the column names of the destination SQL table. |
schema |
A string, the schema name of the destination SQL table. |
table |
A string, the table name of the destination SQL table. |
returning |
A vector of character strings specifying the SQL column names to be returned by the INSERT statement. |
quote_text |
TRUE/FALSE, if TRUE, calls quoteText() to add single quotes around character strings. |
cast |
TRUE/FALSE, if TRUE, will add SQL to cast the data to be inserted to the specified type. |
types |
A vector of types to use for casting columns. If blank, will look at meta data about table to decide types. |
batch_size |
Integer, the maximum number of records to submit in one statement. |
con |
A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery. |
table_is_temporary |
TRUE/FALSE, if TRUE, prevents parallel processing. |
retain_insert_order |
TRUE/FALSE, if TRUE, prevents parallel processing. |
n_cores |
A integer, the number of cores to use for parallel forking (passed to parallel::mclapply as mc.cores). |
connect_db_name |
The name of the database to pass to connect() when inserting in parallel. |
dialect |
A string, "T-SQL" or "Postgresql". |
A string, the INSERT statement; or the results retrieved by DBI::dbGetQuery after executing the statement.
insert_values( x = list(col1 = c("a", "b", "c"), col2 = c(1, 2, 3)), schema = "test", table = "table1", types = c("VARCHAR(12)", "INT") )insert_values( x = list(col1 = c("a", "b", "c"), col2 = c(1, 2, 3)), schema = "test", table = "table1", types = c("VARCHAR(12)", "INT") )
Add single quotes to strings using stringi::stri_join, useful for converting R strings into SQL formatted strings.
quoteText2(x, char_only = TRUE, excluded_chars = c("NULL"))quoteText2(x, char_only = TRUE, excluded_chars = c("NULL"))
x |
A string. |
char_only |
TRUE/FALSE, if TRUE, adds quotes only if is.character(x) is TRUE. |
excluded_chars |
A character vector, will not add quotes if a value is in excluded_chars. |
A string, with single quotes added to match SQL string formatting.
quoteText2("Sample quotes.")quoteText2("Sample quotes.")
Convert a column name into a SQL compatible name.
sqlizeNames(x, dialect = "T-SQL")sqlizeNames(x, dialect = "T-SQL")
x |
A string, a column name. |
dialect |
A string, "T-SQL" or "Postgresql". |
A string, a SQL compatible column name.
sqlizeNames("column 100 - sample b")sqlizeNames("column 100 - sample b")
Get the equivalent SQL data type for a given R object.
sqlizeTypes(x, dialect = "T-SQL")sqlizeTypes(x, dialect = "T-SQL")
x |
A R object. |
dialect |
A string, "T-SQL" or "Postgresql". |
A string, the equivalent SQL data type for x.
sqlizeTypes(100.1209)sqlizeTypes(100.1209)
Generate a BULK INSERT statement, optionally execute the statement if con is not NULL.
t_sql_bulk_insert(file, schema = NULL, table, con = NULL, ...)t_sql_bulk_insert(file, schema = NULL, table, con = NULL, ...)
file |
A string, the file path to the file with data to insert. |
schema |
A string, the schema name of the destination SQL table. |
table |
A string, the table name of the destination SQL table. |
con |
A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery. |
... |
named arguments are passed to the WITH statement. |
A string, the BULK INSERT statement; or the results retrieved by DBI::dbGetQuery after executing the statement.
t_sql_bulk_insert( file = tempfile(), schema = "test", table = "table1", format = 'CSV', first_row = 2, )t_sql_bulk_insert( file = tempfile(), schema = "test", table = "table1", format = 'CSV', first_row = 2, )
Generate a CREATE TABLE statement for an existing table in Microsoft SQL Server.
t_sql_script_create_table(con, table)t_sql_script_create_table(con, table)
con |
A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery. |
table |
A string, the schema qualified table name of an existing SQL table. |
A data table, contains the DDL scripts for creating a table.
t_sql_script_create_table(con = NULL)t_sql_script_create_table(con = NULL)
Fetch the object definition of a proc in Microsoft SQL Server.
t_sql_script_proc_definition(con, proc)t_sql_script_proc_definition(con, proc)
con |
A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery. |
proc |
A string, the database and schema qualified table name of an existing SQL stored procedure. |
A string, contains the script for defining a stored procedure.
t_sql_script_proc_definition(con = NULL)t_sql_script_proc_definition(con = NULL)
Generate a TRUNCATE TABLE statement, optionally execute the statement if con is not NULL.
truncate_table(args, con = NULL)truncate_table(args, con = NULL)
args |
A string, the arguments to add to the TRUNCATE TABLE statement. |
con |
A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery. |
A string, the TRUNCATE TABLE statement; or the results retrieved by DBI::dbGetQuery after executing the statement.
truncate_table(args = "table1")truncate_table(args = "table1")
Generate a UPDATE statement, optionally execute the statement if con is not NULL.
update_values( x, schema = NULL, table, where = NULL, returning = NULL, quote_text = TRUE, cast = TRUE, types = NULL, con = NULL, dialect = "T-SQL" )update_values( x, schema = NULL, table, where = NULL, returning = NULL, quote_text = TRUE, cast = TRUE, types = NULL, con = NULL, dialect = "T-SQL" )
x |
A list, data.frame or data.table, names must match the column names of the destination SQL table. |
schema |
A string, the schema name of the destination SQL table. |
table |
A string, the table name of the destination SQL table. |
where |
A string, conditions to add to a WHERE statement. |
returning |
A vector of character strings specifying the SQL column names to be returned by the UPDATE statement. |
quote_text |
TRUE/FALSE, if TRUE, calls quoteText() to add single quotes around character strings. |
cast |
TRUE/FALSE, if TRUE, will add SQL to cast the data to be inserted to the specified type. |
types |
A vector of types to use for casting columns. If blank, will look at meta data about table to decide types. |
con |
A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery. |
dialect |
A string, "T-SQL" or "Postgresql". |
A string, the UPDATE statement; or the results retrieved by DBI::dbGetQuery after executing the statement.
update_values( x = list(col1 = c("a"), col2 = c(1)), schema = "test", table = "table1", where = "1=1", types = c("VARCHAR(12)", "INT") )update_values( x = list(col1 = c("a"), col2 = c(1)), schema = "test", table = "table1", where = "1=1", types = c("VARCHAR(12)", "INT") )