This vignette walks through a full cycle: connect, write a table, commit, branch, modify, diff, merge, and resolve a conflict.
Connecting
A DoltLite database is a single file.
path <- tempfile(fileext = ".db")
con <- DBI::dbConnect(Doltlite(), path)
con
#> <DoltliteConnection>
#> Database: /tmp/RtmpXbsEOu/file20943fc5fcbb.db
#> Branch: mainOne caveat worth stating up front: pass a real path, not
"". SQLite’s anonymous temporary database is created in the
original B-tree format, and version control is not available
there.
Commits are attributed per connection, and the setting is not persisted, so configure it after connecting:
dolt_config(con, user.name = "Ada Lovelace", user.email = "ada@example.com")Writing data
Nothing surprising: this is ordinary DBI.
users <- data.frame(
id = 1:3,
name = c("ada", "bob", "cleo"),
active = c(1L, 0L, 1L),
stringsAsFactors = FALSE
)
DBI::dbWriteTable(con, "users", users,
field.types = c(id = "INTEGER PRIMARY KEY"))
DBI::dbReadTable(con, "users")
#> id name active
#> 1 1 ada 1
#> 2 2 bob 0
#> 3 3 cleo 1The primary key is declared here because dolt_blame()
later needs one: row attribution has to have a stable row identity to
attribute to. An INTEGER PRIMARY KEY stays an ordinary
writable rowid alias, exactly as in SQLite. A
non-INTEGER primary key is different — it makes
the table clustered, with no rowid at all and
NOT NULL key columns.
The data is in the database, but nothing is in the history yet:
dolt_status(con)
#> table_name staged status
#> 1 users 0 new tableThe two kinds of commit
This is the distinction to hold on to.
DBI::dbBegin() / DBI::dbCommit() delimit a
SQL transaction: the statements between them either all
apply or none do. That is about atomicity.
dolt_commit() writes a commit into the version
history, the way git commit does. That is about
being able to come back later.
dbCommit() is “save the file”;
dolt_commit() is “commit to the repository”. Writing a
table and committing the SQL transaction leaves the rows in
dolt_status() until you make a Dolt commit:
dolt_commit(con, "Initial load")
dolt_status(con)
#> [1] table_name staged status
#> <0 rows> (or 0-length row.names)
dolt_log(con)[, c("committer", "message")]
#> committer message
#> 1 Ada Lovelace Initial load
#> 2 doltlite Initialize data repositoryA fresh database already contains one commit — DoltLite’s own
Initialize data repository.
Branching
Each connection has its own active branch.
dolt_branch(con, "experiment")
dolt_checkout(con, "experiment")
active_branch(con)
#> [1] "experiment"Make a change on the branch and commit it:
DBI::dbExecute(con, "UPDATE users SET active = 1 WHERE id = 2")
#> [1] 1
dolt_commit(con, "Activate bob")main is untouched:
dolt_checkout(con, "main")
DBI::dbGetQuery(con, "SELECT id, name, active FROM users ORDER BY id")
#> id name active
#> 1 1 ada 1
#> 2 2 bob 0
#> 3 3 cleo 1You can also connect straight onto a branch. doltliter
translates this to DoltLite’s dbname@branch path syntax
internally, and errors if the branch does not exist rather than silently
opening main:
con2 <- DBI::dbConnect(Doltlite(), path, branch = "experiment")
active_branch(con2)
#> [1] "experiment"
DBI::dbDisconnect(con2)Diffing
dolt_table_diff() compares a table between two refs,
with from_/to_ column pairs:
d <- dolt_table_diff(con, "users", from = "main", to = "experiment")
d[, c("diff_type", "to_id", "from_active", "to_active")]
#> diff_type to_id from_active to_active
#> 1 modified 2 0 1dolt_diff() works at the level of which tables
changed in each commit. The pseudo-commit WORKING covers
uncommitted changes:
DBI::dbExecute(con, "UPDATE users SET name = 'ADA' WHERE id = 1")
#> [1] 1
dolt_diff(con, "users")[, c("commit_hash", "table_name", "data_change")]
#> commit_hash table_name data_change
#> 1 WORKING users 1
#> 2 915699d882c5aa8aba2f14cc2b1043b56ebba435 users 1
dolt_reset(con, "hard")For summaries rather than rows, there are
dolt_diff_stat(), dolt_diff_summary() and
dolt_schema_diff(); for an executable rebuild,
dolt_patch().
Merging
A clean merge just works:
dolt_merge(con, "experiment")
#> [1] "bb16e173e7d93a540cb96f355c1f6fab84e8c3aa"
DBI::dbGetQuery(con, "SELECT id, name, active FROM users ORDER BY id")
#> id name active
#> 1 1 ada 1
#> 2 2 bob 1
#> 3 3 cleo 1Resolving a conflict
Conflicts in DoltLite are never durable: they exist only inside the transaction that produced them, and a commit is refused while any remain. An autocommit merge that conflicts is therefore rolled back whole — so to inspect and resolve conflicts, run the merge inside a transaction.
Set up two branches that edit the same row:
dolt_checkout(con, "theirs", create = TRUE)
DBI::dbExecute(con, "UPDATE users SET name = 'THEIRS' WHERE id = 1")
#> [1] 1
dolt_commit(con, "Rename on theirs")
dolt_checkout(con, "main")
DBI::dbExecute(con, "UPDATE users SET name = 'OURS' WHERE id = 1")
#> [1] 1
dolt_commit(con, "Rename on main")Now merge inside a transaction:
DBI::dbBegin(con)
dolt_merge(con, "theirs")
#> [1] "Merge has 1 conflict(s). Resolve and then commit with dolt_commit."
dolt_conflicts(con)
#> table num_conflicts
#> 1 users 1Inspect the detail, then take one side (or edit the rows directly):
dolt_conflicts_resolve(con, "ours")
dolt_conflicts(con)
#> [1] table num_conflicts
#> <0 rows> (or 0-length row.names)
dolt_commit(con, "Merge theirs, keeping ours")
DBI::dbGetQuery(con, "SELECT id, name FROM users WHERE id = 1")
#> id name
#> 1 1 OURSdolt_commit() also ends the SQL transaction, so there is
nothing left to dbCommit().
History and blame
dolt_log(con)[, c("committer", "message")]
#> committer message
#> 1 Ada Lovelace Merge theirs, keeping ours
#> 2 Ada Lovelace Rename on main
#> 3 Ada Lovelace Rename on theirs
#> 4 Ada Lovelace Activate bob
#> 5 Ada Lovelace Initial load
#> 6 doltlite Initialize data repositorydolt_history() gives every version of every row, and
dolt_at() reads a table as it was at a revision:
lg <- dolt_log(con)
loaded <- lg$commit_hash[lg$message == "Initial load"]
dolt_at(con, "users", loaded)
#> id name active
#> 1 1 ada 1
#> 2 2 bob 0
#> 3 3 cleo 1dolt_blame() attributes each live row to the commit that
last set it. It needs a primary key — there is no stable row identity
without one:
dolt_blame(con, "users")[, c("id", "committer", "message")]
#> id committer message
#> 1 1 Ada Lovelace Merge theirs, keeping ours
#> 2 2 Ada Lovelace Activate bob
#> 3 3 Ada Lovelace Initial loaddplyr
dplyr::tbl() works over the connection with no extra
setup: DoltLite’s SQL dialect is SQLite’s, and doltliter
reuses dbplyr’s SQLite translation.
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
tbl(con, "users") |>
filter(active == 1) |>
select(id, name) |>
collect()
#> # A tibble: 3 × 2
#> id name
#> <int> <chr>
#> 1 1 OURS
#> 2 2 bob
#> 3 3 cleoRemotes
Databases can push, pull, fetch and clone between each other, over the filesystem or HTTP:
origin <- tempfile(fileext = ".db")
file.copy(path, origin)
#> [1] TRUE
clone_path <- tempfile(fileext = ".db")
clone <- DBI::dbConnect(Doltlite(), clone_path)
dolt_clone(clone, paste0("file://", origin))
#> [1] 0
DBI::dbReadTable(clone, "users")
#> id name active
#> 1 1 OURS 1
#> 2 2 bob 1
#> 3 3 cleo 1
dolt_remotes(clone)[, c("name", "url")]
#> name url
#> 1 origin file:///tmp/RtmpXbsEOu/file2094797a411a.db
DBI::dbDisconnect(clone)Concurrency
DoltLite allows one durable writer at a time. A second connection
that begins a write transaction gets SQLITE_BUSY until the
first finishes, and a transaction that tries to upgrade after a peer
advanced the store gets SQLITE_BUSY_SNAPSHOT. Both are
retryable, and dbConnect() sets a five-second busy timeout
by default:
DBI::dbDisconnect(con)