Skip to contents

Create the tables of a schema, and add the columns added since

Source: R/schema.R
create_schema.Rd

A project defines its schema once, as a named list of CREATE TABLE statements whose column comments are the data dictionary (see schema_dictionary()). create_schema() creates the tables that do not exist yet and, since CREATE TABLE IF NOT EXISTS never changes an existing table, adds the columns introduced after a table was first created.

Usage

create_schema(con, schema, added_columns = NULL)

Arguments

con

A DBI connection with write access.

schema

Named list of character strings: one CREATE TABLE IF NOT EXISTS statement per table, named after the table.

added_columns

Named list or NULL. For each table, a named character vector c(column = "TYPE") of columns to add if they are missing (migration of databases created with an older schema).

Value

The names of the tables, invisibly.

Details

The recommended layout, learnt from the author's database projects:

  • one database file per project as the single source of truth;

  • singular table names, every table linked to document by doc_id (<source>:<identifier in the source>, see make_doc_id());

  • no PRIMARY KEY constraint: DuckDB checks it eagerly, which makes a "delete then insert" update fail inside one transaction. Uniqueness is checked in R before writing (check_unique()) and in the sanity check.

See also

Other schema: add_missing_columns(), check_documented_columns(), check_schema(), schema_dictionary(), schema_markdown()

Examples

schema <- list(
  document = "
    CREATE TABLE IF NOT EXISTS document (
      doc_id    VARCHAR,  -- <source>:<source_id>
      source    VARCHAR,  -- collection the record comes from
      title     VARCHAR
    )"
)
con <- db_connect(tempfile(fileext = ".duckdb"))
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
create_schema(con, schema, added_columns = list(document = c(year = "INTEGER")))
#> ℹ document: added column year.
DBI::dbListFields(con, "document")
#> [1] "doc_id" "source" "title"  "year"  
db_disconnect(con)