Query not preparing correctly

[alex-ondrus]

When I run

test_courses <- mdl_courses(con = mdl_get_connection(use_cache = FALSE))

I receive the error

Error: Failed to prepare query: ERROR:  relation "courses" does not exist
LINE 2: FROM "courses" AS "q01"
             ^

My Moodle database runs off of Postgres, and when I run the command

test <- dplyr::tbl(mdl_con, "courses")

I get the same error

[dietrichson]

@alex-ondrus Just updated the code for mdl_courses, and tested on mySQL and seemed to work fine. When you have a moment, can you test with postgres as well? I'll continue refactoring the other queries today, but don't foresee any problems --not that anyone ever does :)

When testing make sure to install the branch "issue_9".

[alex-ondrus]

@dietrichson I'm having difficulties with mdl_get_connection now. When I run the command with the same config.yml file I was using before, I receive

Error: Failed to connect: Lost connection to server at 'handshake: reading initial communication packet', system error: 2

If I paste my username, password, dbname, etc. into the following, however,

my_connection <-
      DBI::dbConnect(
        RPostgres::Postgres(),
        user = myConf$moodleR$user,
        password = myConf$moodleR$password,
        dbname = myConf$moodleR$dbname,
        host = myConf$moodleR$host,
        port = db_port,
        bigint = "integer64"
      )

everything seems to connect fine. When I run the following

test_courses <- mdl_courses(my_connection)

I receive the following error

Error in mdl_courses(my_connection) : object 'my_con' not found

If I run the following, however

tbl_prefix <- "mdl_"

my_courses <-
  tbl(my_connection, glue("{tbl_prefix}course")) %>%
  mutate(courseid = id) %>%
  mutate(categoryid = category) %>%
  left_join(
    tbl(my_connection, glue("{tbl_prefix}course_categories")) %>%
      select(categoryid = id, category_name = name)
  )

test_courses <- collect(my_courses)

Things seem to work.

[dietrichson]

@alex-ondrus I made some updates this morning (see commits). Please let me know if this works for you.

[alex-ondrus]

@dietrichson The connection issue persists. It looks as thought the following lines are running even when the driver is set to "postgres":

my_connection <- DBI::dbConnect(RMariaDB::MariaDB(), user = myConf$moodleR$user, 
        password = myConf$moodleR$password, dbname = myConf$moodleR$dbname, 
        host = myConf$moodleR$host, bigint = "integer64")

which may be causing the error. I have defined my_connection using the following:

my_connection <-
  DBI::dbConnect(
    RPostgres::Postgres(),
    user = *****,
    password = *****,
    dbname = *****,
    host = *****,
    port = *****,
    bigint = "integer64"
  )

attr(my_connection, "use_cache") <- FALSE

When I do this, the command

test_courses <- mdl_courses(con = my_connection)

executes without error and returns an object of class Large tbl_PqConnection. If I run dplyr::collect on test_courses it returns a tibble. The following command

test_forms <- mdl_forum_posts(con = my_connection)

also runs without errors but outputs a tibble directly.

[dietrichson]

@alex-ondrus yes. It looks like there was an else clause missing.

[alex-ondrus]

Success! Connection issue is resolved. Is there a reason that mdl_courses and mdl_log need to have dplyr::collect run on them while mdl_grades and mdl_forum_posts output tibbles directly?

[dietrichson]

@alex-ondrus yes, the reason that mdl_grades and mdl_forum_posts execute a query in the background, you can see these in "mdl_grades_query" etc, which were written in sql and not refactored to dplyr syntax. If you use the caching mechanism this doesn't matter, since these queries run and store the results in the cache, but for the direct connection I wasn't able to refactor the queries (take a look!) to dplyr syntax within a reasonable timeframe.