It doesn't detect tables if they are not in public schema of Postgres

[vikram-rawat]

I have a database where there is no public schema but multiple different schemas ( which is how a proper database is supposed to be). The moment I try dm_from_src it doesn't provide me any table whatsoever and I have no way of telling it that search in all schemas.

dm_from_src(learn_keys = TRUE,
            src = con,
            table_names = c("dimensions.exchange")) 

### Error: Failed to learn keys from database. Use `learn_keys = FALSE` to work around.

I have multiple schemas in a DB and I really want to use your package. I couldn't find anything to make it work on say dimensions schema.

[krlmlr]

Thanks. Have you tried the schema argument to dm_from_src() ?

[vikram-rawat]

Yes, I have. first of all, I had to dig that argument up by reading the code. It should be documented

It turns out to be you can only give one schema at a time. If you could please make it a little flexible to work on more than one schema. like if 2 tables have the same name but are in different schemas.

This is a great package and I would love to use it more and more because I work with Databases all the time. Please come up with a solution.

[vikram-rawat]

I had same problem with datamodeler. I was able to extract data from different schema but then I have to manually change the name of every column. because my schema had 2 table by same name and it graphed it as single table everytime. I have raised an issue there too....

bergant/datamodelr#23

[krlmlr]

AFAICT it's documented, a bit hard to find. I'm happy to review a pull request that improves the documentation.

Have you seen dm_bind() ?

[vikram-rawat]

Thanks for replying. I Just tried but it's like if you have same names across schemas then it causes a problem.

Error: Each new table needs to have a unique name. Duplicate new name(s): `prices`, `balance_sheet`...

[krlmlr]

Yeah, you'll need to make the tables unique before binding. Do you think dm_bind() should have a name repair argument?

[moodymudskipper]

Here's an almost reproducible script :

library(DBI)
library(dm)
#> 
#> Attaching package: 'dm'
#> The following object is masked from 'package:stats':
#> 
#>     filter
# Connect, create schemas and tables
con <- dbConnect(RPostgres::Postgres(), user = "postgres", password = Sys.getenv("POSTGRESS_ADMIN_PWD"))
rs <- dbSendQuery(con, "CREATE SCHEMA sch1")
dbClearResult(rs)
rs <- dbSendQuery(con, "CREATE SCHEMA sch2")
dbClearResult(rs)
df1 <- data.frame(a="A", b = 1)
df2 <- data.frame(a="A", b = 2)
dbWriteTable(con, SQL("sch1.mytbl"), df1)
dbWriteTable(con, SQL("sch2.mytbl"), df2)


# create separate dm objects, 

# but used correctly works fine
dm1 <- 
  dm_from_src(
    learn_keys = TRUE,
    src = con,
    table_names = c("mytbl"),
    schema = "sch1") %>% 
  dm_rename_tbl(sch1.mytbl = mytbl)

dm2 <- dm_from_src(
  learn_keys = TRUE,
  src = con,
  table_names = c("mytbl"),
  schema = "sch2") %>% 
  dm_rename_tbl(sch2.mytbl = mytbl)

dm_combined <- dm_bind(dm1, dm2) %>% 
  dm_add_pk(sch1.mytbl, a) %>% 
  dm_add_fk(sch2.mytbl, a, sch1.mytbl) 

dm_draw(dm_combined)

# clean the clutter
rs <- dbSendQuery(con, "DROP SCHEMA sch1 CASCADE")
dbClearResult(rs)
rs <- dbSendQuery(con, "DROP SCHEMA sch2 CASCADE")
dbClearResult(rs)

^{Created on 2021-10-18 by the reprex package (v2.0.1)}

@krlmlr

• Should dm_from_src() have an prepend_schema argument to skip the renaming step above ?
• Should the help page mention "To build a dm from several schemas you may to create a separate dm for each schema using dm_from_src() and bind them with dm_bind().
• Should the arguments forwarded through dots (schema, dbname, table_type) be made explicit instead (and we can remove ellipsis)?

We could also have a name_repair argument to dm_bind as you suggest and have a special value name_repair = "prepend_schema" doing a renaming equivalent to above example. It would be neat but maybe confusing since a custom .name_repair function as in tibble::as_tibble take only names as inputs.

[krlmlr]

This might work better with the current development version. Could you please take a look?

[krlmlr]

Please open new issues, referencing this issue, if problems persist.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.