Title: Database Management with R
Version: 0.0.4
Description: Provides functions to manage databases: select, update, insert, and delete records, list tables, backup tables as CSV files, and import CSV files as tables.
License: GPL-3
Encoding: UTF-8
RoxygenNote: 7.2.1
URL: https://github.com/special-uor/dabr/, https://special-uor.github.io/dabr/, https://research.reading.ac.uk/palaeoclimate/
BugReports: https://github.com/special-uor/dabr/issues/
Language: en-GB
Imports: knitr, magrittr, RMariaDB, tibble
NeedsCompilation: no
Packaged: 2022-10-16 13:19:07 UTC; roberto.villegas-diaz
Author: Roberto Villegas-Diaz ORCID iD [aut, cre], SPECIAL Research Group @ University of Reading [cph]
Maintainer: Roberto Villegas-Diaz <r.villegas-diaz@outlook.com>
Repository: CRAN
Date/Publication: 2022-10-17 09:52:35 UTC

dabr: Database Management with R

Description

Provides functions to manage databases: select, update, insert, and delete records, list tables, backup tables as CSV files, and import CSV files as tables.

Author(s)

Maintainer: Roberto Villegas-Diaz r.villegas-diaz@outlook.com (ORCID)

Other contributors:

See Also

Useful links:

Pipe operator

Description

See magrittr::%>% for details.

Usage

lhs %>% rhs

Combine attributes

Description

Combine attributes from a vector of strings.

Usage

attributes(...)

Arguments

...

Strings.

Value

Combined string with all the attributes.

Examples

dabr::attributes("A", "B", "C")
dabr::attributes(c("A", "B", "C"))
dabr::attributes(c("A", "B", "C"), "D", "E", "F")

Close connection to database

Description

Close connection to database

Usage

close_conn(conn, ...)

## S3 method for class 'MariaDBConnection'
close_conn(conn, ...)

## Default S3 method:
close_conn(conn, ...)

Arguments

conn

DB connection object.

...

Optional parameters.

See Also

Other DB functions: delete(), insert(), list_tables(), open_conn_mysql(), select_all(), select(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
close_conn(conn)

## End(Not run)

Execute DELETE query

Description

Execute DELETE query

Usage

delete(conn, ...)

## S3 method for class 'MariaDBConnection'
delete(conn, ..., quiet = FALSE)

Arguments

conn

DB connection object.

...

DELETE query and optional parameters.

quiet

Boolean flag to hide status messages.

See Also

Other DB functions: close_conn(), insert(), list_tables(), open_conn_mysql(), select_all(), select(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
out <- delete(conn, "DELETE sys_config SET value = 1")
close_conn(conn)

## End(Not run)

Get attributes of a table

Description

Get attributes of a table

Usage

get_attr(conn, ...)

## S3 method for class 'MariaDBConnection'
get_attr(conn, name, ...)

Arguments

conn

DB connection object.

...

Optional parameters.

name

Table name.

Value

List of attributes for table name.

Execute INSERT query

Description

Execute INSERT query

Usage

insert(conn, ...)

## S3 method for class 'MariaDBConnection'
insert(conn, ..., quiet = FALSE)

Arguments

conn

DB connection object.

...

INSERT query and optional parameters.

quiet

Boolean flag to hide status messages.

See Also

Other DB functions: close_conn(), delete(), list_tables(), open_conn_mysql(), select_all(), select(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
query <- paste0(
  "INSERT INTO sys_config (variable, value, set_time, set_by) VALUES ",
  "('test_var', 999, '", Sys.time(), "', NULL)"
)
out <- insert(conn, query)
close_conn(conn)

## End(Not run)

Verify connection

Description

Verify if connection object is still valid, is connected to the database server.

Usage

is.connected(conn, ...)

## S3 method for class 'MariaDBConnection'
is.connected(conn, ...)

Arguments

conn

DB connection object.

...

Optional parameters.

Value

Connection status.

List tables

Description

List tables

Usage

list_tables(conn, ...)

## S3 method for class 'MariaDBConnection'
list_tables(conn, quiet = FALSE, attr = TRUE, ...)

Arguments

conn

DB connection object.

...

Optional parameters.

quiet

Boolean flag to hide status messages.

attr

Boolean flag to list the attributes of each table.

Value

If quiet = TRUE returns a list with the tables' names. If attr = TRUE includes each attribute of the tables.

See Also

Other DB functions: close_conn(), delete(), insert(), open_conn_mysql(), select_all(), select(), update()

Connect to database

Description

Uses RMariaDB to open a connection to a MySQL database.

Usage

open_conn_mysql(
  dbname,
  user = "root",
  password = NULL,
  host = "127.0.0.1",
  port = 3306
)

Arguments

dbname

Database/Schema name.

user

Username of database owner.

password

Password (default: NULL).

host

Database host, it can be local (default) or remote.

port

Database port.

Value

MariaDBConnection connection object.

See Also

Other DB functions: close_conn(), delete(), insert(), list_tables(), select_all(), select(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys")

## End(Not run)

Add single quotes

Description

Add single quotes to string.

Usage

quote(str)

Arguments

str

String.

Value

String surrounded by single quotes

Examples

dabr::quote("A")
dabr::quote("l'A")

Execute SELECT query

Description

Execute SELECT query

Usage

select(conn, ...)

## S3 method for class 'MariaDBConnection'
select(conn, ..., quiet = FALSE)

Arguments

conn

DB connection object.

...

SELECT query and optional parameters.

quiet

Boolean flag to hide status messages.

Value

Data frame containing the selected records.

See Also

Other DB functions: close_conn(), delete(), insert(), list_tables(), open_conn_mysql(), select_all(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
out <- select(conn, "SELECT variable, value FROM sys_config")
close_conn(conn)

## End(Not run)

Select all the records

Description

Select all the records inside a particular table, use the table parameter.

Usage

select_all(conn, ...)

## S3 method for class 'MariaDBConnection'
select_all(conn, table, quiet = FALSE, ...)

Arguments

conn

MariaDBConnection connection object.

...

Optional parameters.

table

Name of the table.

quiet

Boolean flag to hide status messages.

Value

Data frame with records.

See Also

Other DB functions: close_conn(), delete(), insert(), list_tables(), open_conn_mysql(), select(), update()

Examples

## Not run: 
conn <- dabr::open_conn_mysql("sys", "root")
out <- dabr::select_all(conn, "sys_config")
dabr::close_conn(conn)

## End(Not run)

Execute UPDATE query

Description

Execute UPDATE query

Usage

update(conn, ...)

## S3 method for class 'MariaDBConnection'
update(conn, ..., quiet = FALSE)

Arguments

conn

DB connection object.

...

UPDATE query and optional parameters.

quiet

Boolean flag to hide status messages.

See Also

Other DB functions: close_conn(), delete(), insert(), list_tables(), open_conn_mysql(), select_all(), select()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
out <- update(conn, "UPDATE sys_config SET value = 1")
close_conn(conn)

## End(Not run)