Write into Citavi database (via SQL update query)

Write into Citavi database (via SQL update query)

update_Citavi_ctv6(
  CitDat = NULL,
  path = NULL,
  CitDBTableName = NULL,
  CitDatVarToCitDBTableVar = NULL,
  quiet = TRUE
)

Arguments

CitDat	A table (likely originally read via read_Citavi_ctv6).
path	Path to the local Citavi project file (.ctv6, i.e. CitDB).
CitDBTableName	Name of the table from the connected Citavi database to be updated.
CitDatVarToCitDBTableVar	Names of Citavi fields to be updated for all references in the Citavi project. Such a name must be both: (i) a column name in CitDat and (ii) a respective field name in the CitDB. SQL queries will be created to update all CitDatVarToCitDBTableVar for all references/StaticIDs.
quiet	If TRUE (default), all output will be suppressed.

Details

The underlying core functions are DBI::dbConnect() RSQLite::SQLite() and DBI::dbSendQuery().

Examples

library(dplyr, warn.conflicts = FALSE)

# use this package's example Citavi project
example_path <- example_file("3dupsin5refs/3dupsin5refs.ctv6")
CitDat <- read_Citavi_ctv6(example_path)

# looks like this
CitDat %>% select(StaticIDs, Title, Year)
#> # A tibble: 5 x 3
#>   StaticIDs                    Title                                       Year 
#>   <chr>                        <chr>                                       <chr>
#> 1 "[\"3840ac9c-6193-4b10-9a29~ More, Larger, Simpler: How Comparable Are ~ 2018 
#> 2 "[\"49eef40c-3927-4392-992d~ Heritability in Plant Breeding on a Genoty~ 2019 
#> 3 "[\"41c262fc-19bb-4dd9-9d48~ Hritability in Plant Breeding on a Genotyp~ 2019 
#> 4 "[\"a4e2c5f7-5144-4299-8e69~ Heritability in plant breeding on a genoty~ 2019 
#> 5 "[\"4cc778b0-6078-4bf2-9cae~ Estimating broad-sense heritability with u~ 2019 

# let's set all years to 1990
CitDat %>%
  mutate(AlteredYear = "1990") %>%
  update_Citavi_ctv6(
    path = example_path,
    CitDBTableName = "Reference",
    CitDatVarToCitDBTableVar = c("AlteredYear" = "Year"),
    quiet = FALSE
  )
#> Sending SQL queries:
#> UPDATE Reference SET Year = '1990' WHERE StaticIDs = '["3840ac9c-6193-4b10-9a29-99e3702fcdca"]' 
#> UPDATE Reference SET Year = '1990' WHERE StaticIDs = '["49eef40c-3927-4392-992d-2788c222af86"]' 
#> UPDATE Reference SET Year = '1990' WHERE StaticIDs = '["41c262fc-19bb-4dd9-9d48-dccfe5d64aa2"]' 
#> UPDATE Reference SET Year = '1990' WHERE StaticIDs = '["a4e2c5f7-5144-4299-8e69-423d8d862bc9"]' 
#> UPDATE Reference SET Year = '1990' WHERE StaticIDs = '["4cc778b0-6078-4bf2-9cae-f949698cb353"]' 

# it worked!
read_Citavi_ctv6(example_path) %>% select(StaticIDs, Title, Year)
#> # A tibble: 5 x 3
#>   StaticIDs                    Title                                       Year 
#>   <chr>                        <chr>                                       <chr>
#> 1 "[\"3840ac9c-6193-4b10-9a29~ More, Larger, Simpler: How Comparable Are ~ 1990 
#> 2 "[\"49eef40c-3927-4392-992d~ Heritability in Plant Breeding on a Genoty~ 1990 
#> 3 "[\"41c262fc-19bb-4dd9-9d48~ Hritability in Plant Breeding on a Genotyp~ 1990 
#> 4 "[\"a4e2c5f7-5144-4299-8e69~ Heritability in plant breeding on a genoty~ 1990 
#> 5 "[\"4cc778b0-6078-4bf2-9cae~ Estimating broad-sense heritability with u~ 1990 

# we should change it back
CitDat %>%
  mutate(OriginalYear = c("2018", "2019", "2019", "2019", "2019")) %>%
  update_Citavi_ctv6(
    path = example_path,
    CitDBTableName = "Reference",
    CitDatVarToCitDBTableVar = c("OriginalYear" = "Year"),
    quiet = FALSE
  )
#> Sending SQL queries:
#> UPDATE Reference SET Year = '2018' WHERE StaticIDs = '["3840ac9c-6193-4b10-9a29-99e3702fcdca"]' 
#> UPDATE Reference SET Year = '2019' WHERE StaticIDs = '["49eef40c-3927-4392-992d-2788c222af86"]' 
#> UPDATE Reference SET Year = '2019' WHERE StaticIDs = '["41c262fc-19bb-4dd9-9d48-dccfe5d64aa2"]' 
#> UPDATE Reference SET Year = '2019' WHERE StaticIDs = '["a4e2c5f7-5144-4299-8e69-423d8d862bc9"]' 
#> UPDATE Reference SET Year = '2019' WHERE StaticIDs = '["4cc778b0-6078-4bf2-9cae-f949698cb353"]' 

# it worked!
read_Citavi_ctv6(example_path) %>% select(StaticIDs, Title, Year)
#> # A tibble: 5 x 3
#>   StaticIDs                    Title                                       Year 
#>   <chr>                        <chr>                                       <chr>
#> 1 "[\"3840ac9c-6193-4b10-9a29~ More, Larger, Simpler: How Comparable Are ~ 2018 
#> 2 "[\"49eef40c-3927-4392-992d~ Heritability in Plant Breeding on a Genoty~ 2019 
#> 3 "[\"41c262fc-19bb-4dd9-9d48~ Hritability in Plant Breeding on a Genotyp~ 2019 
#> 4 "[\"a4e2c5f7-5144-4299-8e69~ Heritability in plant breeding on a genoty~ 2019 
#> 5 "[\"4cc778b0-6078-4bf2-9cae~ Estimating broad-sense heritability with u~ 2019