Support for composite keys

[ozhyrenkov]

Hey there! Thank you so much for incredible work done so far, this is an extremely useful :)

I got confused near vector where_cols = c('id') parameter and thought it can be on multiple columns (aka composite key) insode parameter, how do you think is that possible to achieve this? It can be considered as a feature request. E.g. dbxUpsert(db, 'table', df, where_cols = c('col1', 'col2'))

So far, as I can see it, it's possible to specify multiple columns in WHERE clause in sql UPDATE at least for mysql and postgres.

[ankane]

Hey @AZhyrenkov, what error or behavior are you seeing? Upsert should support multiple where columns.

[ozhyrenkov]

Hey @ankane, sorry for late reply. The usage and errors are the following:

> dbxUpsert(db, 'scorecards_sections', scorecards_sections, where_cols = c('scorecard_id', 'section_id'))
Error in dbxUpsert(db, "scorecards_sections", scorecards_sections, where_cols = c("scorecard_id",  : 
  where_cols not in records

To check whether the colnames are the same I did this:

> test <- dbReadTable(db, 'scorecards_sections')
> names(test) %in% names(scorecards_sections)
[1] TRUE TRUE TRUE TRUE TRUE TRUE
> names(scorecards_sections) %in% names(test)
[1] TRUE TRUE TRUE TRUE TRUE TRUE

Data Frames str:

> str(scorecards_sections)
tibble [91 × 6] (S3: tbl_df/tbl/data.frame)
 $ section_name     : chr [1:91] "Communication" "Context / Procedure" "Fact-finding and Problem-solving" "Zero Tolerance" ...
 $ section_weight   : num [1:91] NA NA NA NA NA 100 NA NA NA NA ...
 $ section_type     : chr [1:91] "default" "default" "default" "default" ...
 $ section_id       : chr [1:91] "5da03ef239594d755f373fe6" "5da03f06c08b38721b449487" "5da03f09297b6b3c5a1d1e10" "5da03f0b9c4aed5db0d4e754" ...
 $ section_max_score: num [1:91] 2.8e+01 2.6e+01 4.6e+01 1.0e-12 1.0e+01 ...
 $ scorecard_id     : chr [1:91] "5edb454162d5cb44430289b6" "5edb454162d5cb44430289b6" "5edb454162d5cb44430289b6" "5edb454162d5cb44430289b6" ...

DDL of the table:

create table if not exists scorecards_sections
(
    section_name varchar(255) null,
    section_weight double null,
    section_type varchar(255) null,
    section_id varchar(255) null,
    section_max_score double null,
    scorecard_id varchar(255) null
);

[ankane]

Hey @AZhyrenkov, that error means dbx can't find one or more of the where_cols in the data frame you're passing to dbxUpsert. Here's the relevant code:

https://github.com/ankane/dbx/blob/9caf03cd933733c639ce6208f2570cf1eef670a7/R/upsert.R#L20-L25

[ankane]

Cleaning up issues