dolt_branches
dolt_branches
contains information about branches known to the database.INSERT
, UPDATE
, or DELETE
statements.dolt_docs
dolt_docs
stores the contents of Dolt docs (LICENSE.md
, README.md
).LICENSE.md
or README.md
. Simply run dolt init
or touch README.md
and touch LICENSE.md
from a Dolt database to get started. Then, dolt add
and dolt commit
the docs like you would a table.dolt_procedures
dolt_procedures
stores each stored procedure that has been created on the database.CREATE PROCEDURE
workflow, the name
column will always be lowercase. Dolt assumes that name
is always lowercase as a result, and manually inserting a stored procedure must also have a lowercase name
. Otherwise, it will be invisible to some operations, such as DROP PROCEDURE
.dolt_query_catalog
dolt_query_catalog
system table stores named queries for your database. Like all data stored in Dolt, these named queries are versioned alongside your data, so after you create, modify, or remove a named query, you'll need to commit that change to save it.
You can use the Dolt CLI to save and execute named queries or you can use the
dolt_query_catalog
system table directly to add, modify, or delete named queries. All named queries are displayed in the Queries tab of your database on DoltHub.jfulghum/iris-flower-dataset
from DoltHub as an example, you can create a named query using the CLI, or by directly inserting into the dolt_query_catalog
table.dolt_query_catalog
table:dolt_query_catalog
table.dolt_remotes
dolt_remotes
returns the remote subcontents of the repo_state.json
, similar to running dolt remote -v
from the command line.dolt_remotes
table is currently read only. Use the CLI dolt remote
functions to add, update or delete remotes.dolt_schemas
dolt_schemas
stores SQL schema fragments for a dolt database that are versioned alongside the database itself. Certain DDL statements will modify this table and the value of this table in a SQL session will affect what database entities exist in the session.dolt_schemas
. type
is currently always the string view
. name
is the name of the view as supplied in the CREATE VIEW ...
statement. fragment
is the select
fragment that the view is defined as.dolt_blame_$tablename
dolt_blame_$tablename
which can be queried to see the user and commit responsible for the current value of each row. This is equivalent to the dolt blame
CLI command. Tables without primary keys will not have an associated dolt_blame_$tablename
.dolt_blame_$tablename
system view has the following columns:dolt_blame_$tablename
system table.SELECT *
query for a dolt_blame_$tablename
system view will show you the primary key columns for every row in the underlying user table and the commit metadata for the last commit that modified that row. Note that if a table has any uncommitted changes in the working set, those will not be displayed in the dolt_blame_$tablename
system view.dolt_blame_$tablename
is only available for tables with a primary key. Attempting to query dolt_blame_$tablename
for a table without a primary key will return an error message.app_config
that holds configuration data:dolt_blame_app_config
table:dolt_commit_ancestors
dolt_commit_ancestors
table records the ancestors for every commit in the database. Each commit has one or two ancestors, two in the case of a merge commit.NULL
parent. For merge commits, the merge base will have parent_index
0, and the commit merged will have parent_index
1.dolt_commit_diff_$TABLENAME
$TABLENAME
, there is a read-only system table named dolt_commit_diff_$TABLENAME
that can be queried to see a diff of the data in the specified table between any two commits in the database. For example, you can use this system table to view the diff between two commits on different branches. The schema of the returned data from this system table is based on the schema of the underlying user table at the currently checked out branch.from_commit
and to_commit
in all queries to this system table in order to specify the to and from points for the diff of your table data. Each returned row describes how a row in the underlying user table has changed from the from_commit
ref to the to_commit
ref by showing the old and new values.dolt_commit_diff_$TABLENAME
is the analogue of the dolt diff
CLI command.
It represents the two-dot diff between the two commits provided. The dolt_diff_$TABLENAME
system table also exposes diff information, but instead of a two-way diff, it returns a log of individual diffs between all adjacent commits in the history of the current branch. In other words, if a row was changed in 10 separate commits, dolt_diff_$TABLENAME
will show 10 separate rows – one for each individual delta. In contrast, dolt_commit_diff_$TABLENAME
would show a single row that combines all the individual commit deltas into one diff.DOLT_DIFF()
table function is an alternative to the dolt_commit_diff_$tablename
system table for cases where a table's schema has changed between the to
and from
commits. Consider the DOLT_DIFF()
table function if you need to see the schema from each of those commits, instead of using the schema from the currently checked out branch.X
in your table at the currently checked out branch, there are columns in the result set named from_X
and to_X
with the same type as X
in the current schema. The from_commit
and to_commit
parameters must both be specified in the query, or an error is returned.dolt_commit_diff_$TABLENAME
will be:to_commit
value WORKING
which can be used to see what changes are in the working set that have yet to be committed to HEAD. It is often useful to use the HASHOF()
function to get the commit hash of a branch, or an ancestor commit. The above table requires both from_commit
and to_commit
to be filled.dolt_commits
dolt_commits
system table shows ALL commits in a Dolt database.dolt_log
system table and the dolt log
CLI command. dolt log
shows you commit history for all commit ancestors reachable from the current HEAD
of the checked out branch, whereas dolt_commits
shows all commits from the entire database, no matter which branch is checked out.dolthub/SHAQ
database from DoltHub, we can query for the five most recent commits before November 1st, 2021, across all commits in the database (regardless of what is checked out to HEAD
) with this query:dolt_diff
dolt_diff
system table shows which tables in the current database were changed in each commit reachable from the active branch's HEAD. When multiple tables are changed in a single commit, there is one row in the dolt_diff
system table for each table, all with the same commit hash. Any staged or unstaged changes in the working set are included with the value WORKING
for their commit_hash
. After identifying the tables that changed in a commit, the dolt_diff_$TABLENAME
system tables can be used to determine the data that changed in each table.DOLT_DIFF
system table has the following columnsdolt_diff
displays the changes from the current branch HEAD, including any working set changes. If a commit did not make any changes to tables (e.g. an empty commit), it is not included in the dolt_diff
results.dolthub/nba-players
database from DoltHub as our example, the following query uses the dolt_diff
system table to find all commits, and the tables they changed, from the month of October, 2020.rla1p8em
and jbk2ckro
only changed the draft_history
table, commit pu60cdpp
changed the players
table, and commit 1gtq675i
made changes to seven tables. To dig deeper into these changes, we can query the dolt_diff_$TABLE
system tables specific to each of the changed tables, like this:dolt_diff_$TABLENAME
$TABLENAME
, there is a read-only system table named dolt_diff_$TABLENAME
that returns a list of diffs showing how rows have changed over time on the current branch. Each row in the result set represents a row that has changed between two adjacent commits on the current branch – if a row has been updated in 10 commits, then 10 individual rows are returned, showing each of the 10 individual updates.dolt_commit_diff_$TABLENAME
system table, the dolt_diff_$TABLENAME
system table focuses on how a particular row has evolved over time in the current branch's history. The major differences are that dolt_commit_diff_$TABLENAME
requires specifying from_commit
and to_commit
, works on any commits in the database (not just the current branch), and returns a single combined diff for all changes to a row between those two commits. In the example above where a row is changed 10 times, dolt_commit_diff_$TABLENAME
would only return a single row showing the diff, instead of the 10 individual deltas.X
in your table at the current branch there will be columns in the result set named from_X
and to_X
with the same type as X
.states
with the following schema:dolt_diff_states
would be:SELECT *
query for a diff table will show you every change that has occurred to each row for every commit in this branch's history. Using to_commit
or from_commit
will limit the data to specific commits. There is one special to_commit
value WORKING
which can be used to see what changes are in the working set that have yet to be committed to HEAD. It is often useful to use the HASHOF()
function to get the commit hash of a branch, or an ancestor commit. For example, to get the differences between the last commit and its parent you could use to_commit=HASHOF("HEAD") and from_commit=HASHOF("HEAD^")
diff_type
will be one of the values added
, modified
, or removed
. You can filter which rows appear in the result set to one or more of those diff_type
values in order to limit which types of changes will be returned.dolthub/wikipedia-ngrams
database from DoltHub as our example, the following query will retrieve the bigrams whose total counts have changed the most between 2 versions.dolt_history_$TABLENAME
$TABLENAME
, there is a read-only system table named dolt_history_$TABLENAME
that can be queried to find a row's value at every commit in the current branch's history.commit_hash
, committer
, and commit_date
, plus every column from the user table's schema at the current checked out branch.states
with the following schema:dolt_history_states
would be:states
table above and the following commit graph:main
branch is checked out, the following query returns the results below, showing the state of the Virginia row at every ancestor commit reachable from our current branch.dolt_log
dolt_log
system table contains the commit log for all commits reachable from the current HEAD
. This is the same data returned by the dolt log
CLI command.bheni
since April, 2019:dolt_conflicts
dolt_conflicts_$TABLENAME
$TABLENAME
in conflict after a merge, there is a corresponding system table named dolt_conflicts_$TABLENAME
. The schema of each such table contains three columns for each column in the actual table, representing each row in conflict for each of ours, theirs, and base values.mytable
with this schema:our
values, I would simply run:their
values, I would first run this statement:ours
, theirs
and base
rows in these replacements.dolt_status
dolt_status
returns the status of the database session, analogous to running dolt status
from the command line.dolt_constraint_violations
dolt_constraint_violations
system table contains one row for every table that has a constraint violation introduced by a merge. Dolt enforces constraints (such as foreign keys) during normal SQL operations, but it's possible that a merge puts one or more tables in a state where constraints no longer hold. For example, a row deleted in the merge base could be referenced via a foreign key constraint by an added row in the merged commit. Use dolt_constraint_violations
to discover such violations.dolt_constraint_violations_$TABLENAME
$TABLENAME
with a constraint violation after a merge, there is a corresponding system table named dolt_constraint_violations_$TABLENAME
. Each row in the table represents a constraint violation that must be resolved via INSERT
, UPDATE
, or DELETE
statements. Resolve each constraint violation before committing the result of the merge that introduced them.a
with the following schema:dolt_constraint_violations_a
will have the following schema:violation_info
field is a JSON payload describing the violation.dolt_conflicts
, delete rows from the corresponding dolt_constraint_violations
table to signal to dolt that you have resolved any such violations before committing.