Test_Functionality

This test markdown covers and example workflow of a user of the package.

Bring in the new package

library(DBconEZ)

Check all available ODBC drivers

sort(unique(odbcListDrivers()[[1]])) 
#>  [1] "MariaDB ODBC 3.1 Driver"                               
#>  [2] "Microsoft Access Driver (*.mdb, *.accdb)"              
#>  [3] "Microsoft Access Text Driver (*.txt, *.csv)"           
#>  [4] "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
#>  [5] "MySQL ODBC 8.0 ANSI Driver"                            
#>  [6] "MySQL ODBC 8.0 Unicode Driver"                         
#>  [7] "ODBC Driver 17 for SQL Server"                         
#>  [8] "ODBC Driver 18 for SQL Server"                         
#>  [9] "SQL Server"                                            
#> [10] "SQL Server Native Client 11.0"

Next establish a connection to a DB. In this example we are checking a MySQL db, for the purposes of this exercise only. IP address is internal, password and user are self-expiring after one login.

con <- DBconnect(RMySQL::MySQL(), 
                 dbname = "sakila", 
                 host = "192.168.50.71", 
                 port = 49153,
                 user = "root",
                 password = "Camplovers01")

Next we can use the dm_from_src function to creat a DM (data model) of the available schemas/tables. The example connection connects to only a single schema

con_dm <- dm_from_src(con, learn_keys = FALSE)

We can then see if an ERD diagram can be drawn from available information of the Database *This is an experimental function in the dm package and as such is quite buggy itself)

dm_draw(con_dm)
#> The dm cannot be drawn because it is empty.

We can then test a query of the database using an analytical type of request from a stakeholder ##You want to run an email marketing campaign in Canada, for which you will need the names and email addresses of all Canadian customers.

email_results <- dbGetQuery(con, "select concat(c.first_name,' ',c.last_name) as 'Name', c.email as 'E-mail'
    from customer as c
    join address as a on c.address_id = a.address_id
    join city as cy on a.city_id = cy.city_id
    join country as ct on ct.country_id = cy.country_id
    where ct.country = 'Canada';")
Email Query
Name E-mail
DERRICK BOURQUE
DARRELL POWER
LORETTA CARPENTER
CURTIS IRBY
TROY QUIGLEY

Let’s try a more complicated query ##List the top five genres in gross revenue in descending order

sales_dollars_genre <- dbGetQuery(con, "select c.name as 'Film', sum(p.amount) as 'Gross Revenue'
    from category as c
    join film_category as fc on fc.category_id = c.category_id
    join inventory as i on i.film_id = fc.film_id
    join rental as r on r.inventory_id = i.inventory_id
    join payment as p on p.rental_id = r.rental_id
    group by c.name
    order by sum(p.amount) desc
    limit 5;
")
#> Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
#> numeric
Sales by Genre
Film Gross Revenue
Sports 5314.21
Sci-Fi 4756.98
Animation 4656.30
Drama 4587.39
Comedy 4383.58

##We see here that the BDconEZ package aids greatly in streamlining the process to explore and query data in a single place. Future versions will include integration of the gWidgets GUI interface to streamline selection and querying inline code.