Skip to contents

Connect to orderly databases

Source: R/db.R
orderly_db.Rd

Connect to the orderly databases. These should be treated as as read-only.

Usage

orderly_db(type, root = NULL, locate = TRUE, validate = TRUE, instance = NULL)

Arguments

type

The type of connection to make (source, destination, csv or rds).

root

The path to an orderly root directory, or NULL (the default) to search for one from the current working directory if locate is TRUE.

locate

Logical, indicating if the configuration should be searched for. If TRUE and config is not given, then orderly looks in the working directory and up through its parents until it finds an orderly_config.yml file.

validate

Logical, indicating if the database schema should be validated on open (currently only applicable with type = "destination"). This is primarily intended for internal use.

instance

Used only by type = "source", and used to select the instance, where multiple instances are configured. Use a single unnamed character string to indicate an instance to match. If given, then this name must be present in all databases where instances are listed in orderly_config.yml, and will be ignored by all database where instances are not given. See the "orderly" vignette for further information.

Value

A database connection, or list of connections in the case of source.

Details

Orderly has several databases:

  • source: All of the databases named in the database section of the orderly_config.yml

  • destination: The orderly index database (typically a SQLite database stored at the orderly root)

  • csv: The cache of database query results, in csv format

  • rds: The cache of database query results, in rds format

Examples

# Create an orderly that has a single commited report:
path <- orderly::orderly_example("minimal")
id <- orderly::orderly_run("example", root = path)
#> [ name       ]  example
#> [ id         ]  20230621-105026-bffdf642
#> [ start      ]  2023-06-21 10:50:26
#> [ data       ]  source => dat: 20 x 2
#> 
#> > png("mygraph.png")
#> 
#> > par(mar = c(15, 4, 0.5, 0.5))
#> 
#> > barplot(setNames(dat$number, dat$name), las = 2)
#> 
#> > dev.off()
#> agg_png 
#>       2 
#> [ end        ]  2023-06-21 10:50:26
#> [ elapsed    ]  Ran report in 0.01625848 secs
#> [ artefact   ]  mygraph.png: 175369b2bcf4115f343c8ad746c0c072
orderly::orderly_commit(id, root = path)
#> [ commit     ]  example/20230621-105026-bffdf642
#> [ copy       ]
#> [ import     ]  example:20230621-105026-bffdf642
#> [ success    ]  :)
#> [1] "/tmp/RtmpGRuIRx/file47432b567c/archive/example/20230621-105026-bffdf642"

# The source database holds the data that might be accessible via
# the 'data' entry in orderly.yml:
db <- orderly::orderly_db("source", root = path)
# This is a list, with one connection per database listed in the
# orderly_config.yml (an empty list if none are specified):
db
#> $source
#> <SQLiteConnection>
#>   Path: /tmp/RtmpGRuIRx/file47432b567c/source.sqlite
#>   Extensions: TRUE
#> 
DBI::dbListTables(db$source)
#> [1] "data"  "thing"
head(DBI::dbReadTable(db$source, "data"))
#>   id thing       value
#> 1  1     1  0.92606273
#> 2  2    13  0.03693769
#> 3  3     6 -1.06620017
#> 4  4    17 -0.23845635
#> 5  5     9  1.49522344
#> 6  6     7  1.17215855
DBI::dbDisconnect(db$source)

# The destination database holds information about the archived
# reports:
db <- orderly::orderly_db("destination", root = path)
DBI::dbListTables(db)
#>  [1] "artefact_format"              "changelog"                   
#>  [3] "changelog_label"              "custom_fields"               
#>  [5] "data"                         "depends"                     
#>  [7] "file"                         "file_artefact"               
#>  [9] "file_input"                   "file_input_global"           
#> [11] "file_purpose"                 "orderly_schema"              
#> [13] "orderly_schema_tables"        "parameters"                  
#> [15] "parameters_type"              "report"                      
#> [17] "report_batch"                 "report_version"              
#> [19] "report_version_artefact"      "report_version_batch"        
#> [21] "report_version_custom_fields" "report_version_data"         
#> [23] "report_version_instance"      "report_version_package"      
#> [25] "report_version_tag"           "report_version_view"         
#> [27] "report_version_workflow"      "tag"                         
#> [29] "workflow"                    

# These tables are documented online:
# https://vimc.github.io/orderly/schema
DBI::dbReadTable(db, "report_version")
#>                         id  report                date displayname description
#> 1 20230621-105026-bffdf642 example 2023-06-21 10:50:26        <NA>        <NA>
#>   connection published    elapsed git_sha git_branch git_clean
#> 1          0         0 0.01625848    <NA>       <NA>        NA