# Functions ## Table of Contents * [Informational Functions](#informational-functions) * [active\_branch()](#active_branch) * [dolt\_merge\_base()](#dolt_merge_base) * [dolt\_hashof()](#dolt_hashof) * [dolt\_hashof\_table()](#dolt_hashof_table) * [dolt\_hashof\_db()](#dolt_hashof_db) * [dolt\_version()](#dolt_version) * [has\_ancestor()](#has_ancestor) * [last\_insert\_uuid()](#last_insert_uuid) * [Table Functions](#table-functions) * [dolt\_diff()](#dolt_diff) * [dolt\_diff\_stat()](#dolt_diff_stat) * [dolt\_diff\_summary()](#dolt_diff_summary) * [dolt\_log()](#dolt_log) * [dolt\_patch()](#dolt_patch) * [dolt\_preview\_merge\_conflicts\_summary()](#dolt_preview_merge_conflicts_summary) * [dolt\_preview\_merge\_conflicts()](#dolt_preview_merge_conflicts) * [dolt\_reflog()](#dolt_reflog) * [dolt\_schema\_diff()](#dolt_schema_diff) * [dolt\_query\_diff()](#dolt_query_diff) * [dolt\_branch\_status()](#dolt_branch_status) * [dolt\_test\_run()](#dolt_test_run) ## Informational Functions ### `ACTIVE_BRANCH()` The `ACTIVE_BRANCH()` function returns the name of the currently active branch for this session. {% embed url="" %} ### `DOLT_MERGE_BASE()` `DOLT_MERGE_BASE()` returns the hash of the common ancestor between two branches. Consider the following branch structure: ``` A---B---C feature / D---E---F---G main ``` The following would return the hash of commit `E`: {% embed url="" %} ### `DOLT_HASHOF()` The `DOLT_HASHOF()` function returns the commit hash of a branch or other commit spec. {% embed url="" %} ### `DOLT_HASHOF_TABLE()` The `DOLT_HASHOF_TABLE()` function returns the value hash of a table. The hash is the hash of all the rows in the table, and is dependent on their serialization format. As such a table could have the same rows, but different hashes if the serialization format has changed, however if a table hash has not changed, then it's guaranteed that the table's data has not changed. This function can be used to watch for changes in data by storing previous hashes in your application and comparing them to the current hash. For example, you can use this function to get the hash of a table named `color` like so: ```sql mysql> SELECT dolt_hashof_table('color'); +----------------------------------+ | dolt_hashof_table('color') | +----------------------------------+ | q8t28sb3h5g2lnhiojacpi7s09p4csjv | +----------------------------------+ 1 row in set (0.01 sec) ``` ### `DOLT_HASHOF_DB()` The `DOLT_HASHOF_DB()` function returns the value hash of the entire versioned database. The hash is the hash of all tables (schema and data) in the database, and includes additional versioned items such as stored procedures and triggers. The hash does not include unversioned items such as tables which have been [ignored](https://docs.dolthub.com/sql-reference/dolt-system-tables#dolt_ignore). The function takes an optional argument to specify a branch or one of the values of 'STAGED', 'WORKING', or 'HEAD' (default no argument call is equivalent to 'WORKING'). This function can be used to watch for changes in the database by storing previous hashes in your application and comparing them to the current hash. For example, you can use this function to get the hash of the entire database like so: ```sql mysql> SELECT dolt_hashof_db(); +----------------------------------+ | dolt_hashof_db() | +----------------------------------+ | 1q8t28sb3h5g2lnhiojacpi7s09p4csj | +----------------------------------+ ``` It should be noted that if you are connected to branch 'main' and you call `dolt_hashof_db('feature')`, the hash may be different than if you were connected to branch 'feature' and called `dolt_hashof_db()`. This happens if there exist changes to the working set on branch 'feature' that have not been committed. Calling `dolt_hashof_db('feature')` while on 'main' is equivalent to calling `dolt_hashof_db('HEAD')` while on branch 'feature'. The general recommendation when trying to look for changes to the database is to connect to the branch you want to use, then call `dolt_hashof_db()` without any arguments. Any change in the hash means that the database has changed. ### `DOLT_VERSION()` The `DOLT_VERSION()` function returns the version string for the Dolt binary. ```sql mysql> select dolt_version(); +----------------+ | dolt_version() | +----------------+ | 0.40.4 | +----------------+ ``` ### `HAS_ANCESTOR()` The `HAS_ANCESTOR(target, ancestor)` function returns a `boolean` indicating whether a candidate `ancestor` commit is in the commit graph of the `target` ref. Consider the example commit graph from above: ``` A---B---C feature / D---E---F---G main ``` A hypothetical example where we substitute letters for commit hashes would look like: ```sql select has_ancestor('feature', 'A'); -- true select has_ancestor('feature', 'E'); -- true select has_ancestor('feature', 'F'); -- false select has_ancestor('main', 'E'); -- true select has_ancestor('G', 'main'); -- true ``` ### `LAST_INSERT_UUID()` The `last_insert_uuid()` function returns the UUID of the first row inserted by the last statement executed in the current session. This is the UUID analogue of [MySQL's `LAST_INSERT_ID()` function](https://dev.mysql.com/doc/refman/8.3/en/information-functions.html#function_last-insert-id). We [recommend using UUIDs in keys instead of auto\_increment values](https://www.dolthub.com/blog/2023-10-27-uuid-keys/) due to their better support for merging values across distributed clones of your database. While `last_insert_id()` uses the presence of the `auto_increment` modifier on a column to determine which automatically generated key value to return, `last_insert_uuid()` instead depends on the column having a specific definition. For `last_insert_uuid()` to grab an inserted UUID value, the column **must** be part of the table's primary key, and it **must** have one of the following type definitions: * `VARCHAR(36)` or `CHAR(36)` with a default value expression of `(UUID())` * `VARBINARY(16)` or `BINARY(16)` with a default value expression of `(UUID_TO_BIN(UUID()))` When the column is defined as `VARBINARY` or `BINARY` and uses the `UUID_TO_BIN()` function in the default value expression, [the `swap_flag` for `UUID_TO_BIN` may optionally be specified](https://dev.mysql.com/doc/refman/8.3/en/miscellaneous-functions.html#function_uuid-to-bin). The following code shows how to create a table that conforms to the requirements above and demonstrates how to use `last_insert_uuid()`: ```sql > create table t (pk binary(16) primary key default (UUID_to_bin(UUID())), c1 varchar(100)); > insert into t (c1) values ("one"), ("two"); Query OK, 2 rows affected (0.00 sec) > select last_insert_uuid(); +--------------------------------------+ | last_insert_uuid() | +--------------------------------------+ | 6cd58555-bb3f-45d8-9302-d32d94d8e28a | +--------------------------------------+ > select c1 from t where pk = uuid_to_bin(last_insert_uuid()); +-----+ | c1 | +-----+ | one | +-----+ ``` ## Table Functions Table functions operate like regular SQL functions, but instead of returning a single, scalar value, a table function returns rows of data, just like a table. Dolt's table functions have several restrictions in how they can be used in queries. For example, you cannot currently alias a table function or join a table function with another table or table function. ### `DOLT_DIFF()` The `DOLT_DIFF()` table function calculates the differences in a table's data at any two commits in the database. Each row in the result set describes how a row in the underlying table has changed between the two commits, including the row's values at to and from commits and the type of change (i.e. `added`, `modified`, or `removed`). `DOLT_DIFF()` is an alternative to the [`dolt_commit_diff_$tablename` system table](https://docs.dolthub.com/sql-reference/dolt-system-tables#dolt_commit_diff_usdtablename). You should generally prefer the system tables when possible, since they have less restrictions on use. However, some use cases, such as viewing a table data diff containing schema changes or viewing the [three dot diff](https://www.dolthub.com/blog/2022-11-11-two-and-three-dot-diff-and-log/#three-dot-diff), can be easier to view with the `DOLT_DIFF` table function. The main difference between the results of the `DOLT_DIFF()` table function and the `dolt_commit_diff_$tablename` system table is the schema of the returned results. `dolt_commit_diff_$tablename` generates the resulting schema based on the table's schema at the currently checked out branch. `DOLT_DIFF()` will use the schema at the `from_commit` for the `from_` columns and the schema at the `to_commit` for the `to_` columns. This can make it easier to view diffs where the schema of the underlying table has changed. Note that the `DOLT_DIFF()` table function currently requires that argument values be literal values. #### Privileges `DOLT_DIFF()` table function requires `SELECT` privilege on the specified table. #### Options ```sql DOLT_DIFF(, , ) DOLT_DIFF(, ) DOLT_DIFF(, ) ``` The `DOLT_DIFF()` table function takes either two or three required arguments: * `from_revision` — the revision of the table data for the start of the diff. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~"). * `to_revision` — the revision of the table data for the end of the diff. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~"). * `from_revision..to_revision` — gets the two dot diff, or revision of table data between the `from_revision` and `to_revision`. This is equivalent to `dolt_diff(, , )`. * `from_revision...to_revision` — gets the three dot diff, or revision of table data between the `from_revision` and `to_revision`, *starting at the last common commit*. * `tablename` — the name of the table containing the data to diff. In addition to required arguments listed above, `DOLT_DIFF()` also supports the following optional arguments: * `-sk`,`--skinny`: Shows only primary key columns and any columns with data changes. Always includes primary keys, commit metadata columns, and `diff_type`. * `-ic`,`--include-cols=`: A comma-separated list of additional columns to include in the skinny output even if they did not change. #### Schema ``` +------------------+----------+ | field | type | +------------------+----------+ | from_commit | TEXT | | from_commit_date | DATETIME | | to_commit | TEXT | | to_commit_date | DATETIME | | diff_type | TEXT | | other cols | | +------------------+----------+ ``` The remaining columns are dependent on the schema of the user table as it existed at the `from_commit` and at the `to_commit`. For every column `X` in your table at the `from_commit` revision, there is a column in the result set named `from_X`. Likewise, for every column `Y` in your table at the `to_commit` revision, there is a column in the result set named `to_Y`. This is the major difference between the `DOLT_DIFF()` table function and the `dolt_commit_diff_$tablename` system table – `DOLT_DIFF()` uses the two schemas at the `to_commit` and `from_commit` revisions to form the to and from columns of the result set, while `dolt_commit_diff_$tablename` uses only the table schema of the currently checked out branch to form the to and from columns of the result set. #### Example Consider a table named `inventory` in a database with two branches: `main` and `feature_branch`. We can use the `DOLT_DIFF()` function to calculate a diff of the table data from the `main` branch to the `feature_branch` branch to see how our data has changed on the feature branch. Here is the schema of `inventory` at the tip of `main`: ``` +----------+------+ | field | type | +----------+------+ | pk | int | | name | text | | quantity | int | +----------+------+ ``` Here is the schema of `inventory` at the tip of `feature_branch`: ``` +----------+------+ | field | type | +----------+------+ | pk | int | | name | text | | color | text | | size | int | +----------+------+ ``` Based on the schemas at the two revision above, the resulting schema from `DOLT_DIFF()` will be: ``` +------------------+----------+ | field | type | +------------------+----------+ | from_pk | int | | from_name | text | | from_quantity | int | | from_commit | TEXT | | from_commit_date | DATETIME | | to_pk | int | | to_name | text | | to_color | text | | to_size | int | | to_commit | TEXT | | to_commit_date | DATETIME | | diff_type | text | +------------------+----------+ ``` To calculate the diff and view the results, we run the following query: ```sql SELECT * FROM DOLT_DIFF("main", "feature_branch", "inventory") ``` The results from `DOLT_DIFF()` show how the data has changed going from `main` to `feature_branch`: ``` +---------+-------+---------+----------+----------------+-----------------------------------+-----------+---------+---------------+-------------+-----------------------------------+-----------+ | to_name | to_pk | to_size | to_color | to_commit | to_commit_date | from_name | from_pk | from_quantity | from_commit | from_commit_date | diff_type | +---------+-------+---------+----------+----------------+-----------------------------------+-----------+---------+---------------+-------------+-----------------------------------+-----------+ | shirt | 1 | 15 | false | feature_branch | 2022-03-23 18:57:38.476 +0000 UTC | shirt | 1 | 70 | main | 2022-03-23 18:51:48.333 +0000 UTC | modified | | shoes | 2 | 9 | brown | feature_branch | 2022-03-23 18:57:38.476 +0000 UTC | shoes | 2 | 200 | main | 2022-03-23 18:51:48.333 +0000 UTC | modified | | pants | 3 | 30 | blue | feature_branch | 2022-03-23 18:57:38.476 +0000 UTC | pants | 3 | 150 | main | 2022-03-23 18:51:48.333 +0000 UTC | modified | | hat | 4 | 6 | grey | feature_branch | 2022-03-23 18:57:38.476 +0000 UTC | NULL | NULL | NULL | main | 2022-03-23 18:51:48.333 +0000 UTC | added | +---------+-------+---------+----------+----------------+-----------------------------------+-----------+---------+---------------+-------------+-----------------------------------+-----------+ ``` **Three dot `DOLT_DIFF`** Let's say the above database has a commit graph that looks like this: ``` A - B - C - D (main) \ E - F (feature_branch) ``` The example above gets the two dot diff, or differences between two revisions: `main` and `feature_branch`. `dolt_diff('main', 'feature_branch', 'inventory')` (equivalent to `dolt_diff('main..feature_branch', 'inventory')`) outputs the difference from F to D (i.e. with effects of E and F). Three dot diff is useful for showing differences introduced by a feature branch from the point at which it *diverged* from the main branch. Three dot diff is used to show pull request diffs. Therefore, `dolt_diff('main...feature_branch')` outputs just the differences in `feature_branch` (i.e. E and F). Learn more about two vs three dot diff [here](https://www.dolthub.com/blog/2022-11-11-two-and-three-dot-diff-and-log). ### `DOLT_DIFF_STAT()` *Previously `dolt_diff_summary()`* The `DOLT_DIFF_STAT()` table function calculates the data difference stat between any two commits in the database. Schema changes such as creating a new table with no rows, or deleting a table with no rows will return empty result. Each row in the result set describes a diff stat for a single table with statistics information of number of rows unmodified, added, deleted and modified, number of cells added, deleted and modified and total number of rows and cells the table has at each commit. `DOLT_DIFF_STAT()` works like [CLI `dolt diff --stat` command](https://docs.dolthub.com/cli-reference/cli#dolt-diff), but two commits are required to use the `DOLT_DIFF_STAT()` table function and the table name is optional. For keyless tables, this table function only provides the number of added and deleted rows. It returns empty result for tables with no data changes. Note that the `DOLT_DIFF_STAT()` table function currently requires that argument values be literal values. #### Privileges `DOLT_DIFF_STAT()` table function requires `SELECT` privilege for all tables if no table is defined or for the defined table only. #### Options ```sql DOLT_DIFF_STAT(, , ) DOLT_DIFF_STAT(, ) DOLT_DIFF_STAT(, ) ``` The `DOLT_DIFF_STAT()` table function takes three arguments: * `from_revision` — the revision of the table data for the start of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~", "WORKING", "STAGED"). * `to_revision` — the revision of the table data for the end of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~", "WORKING", "STAGED"). * `from_revision..to_revision` — gets the two dot diff stat, or revision of table data between the `from_revision` and `to_revision`. This is equivalent to `dolt_diff_stat(, , )`. * `from_revision...to_revision` — gets the three dot diff stat, or revision of table data between the `from_revision` and `to_revision`, *starting at the last common commit*. * `tablename` — the name of the table containing the data to diff. This argument is optional. When it's not defined, all tables with data diff will be returned. #### Schema ``` +-----------------+--------+ | field | type | +-----------------+--------+ | table_name | TEXT | | rows_unmodified | BIGINT | | rows_added | BIGINT | | rows_deleted | BIGINT | | rows_modified | BIGINT | | cells_added | BIGINT | | cells_deleted | BIGINT | | cells_modified | BIGINT | | old_row_count | BIGINT | | new_row_count | BIGINT | | old_cell_count | BIGINT | | new_cell_count | BIGINT | +-----------------+--------+ ``` #### Example Consider we start with a table `inventory` in a database on `main` branch. When we make any changes, we can use the `DOLT_DIFF_STAT()` function to calculate a diff of the table data or all tables with data changes across specific commits. Here is the schema of `inventory` at the tip of `main`: ``` +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | pk | int | NO | PRI | NULL | | | name | varchar(50) | YES | | NULL | | | quantity | int | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ ``` Here is what table `inventory` has at the tip of `main`: ``` +----+-------+----------+ | pk | name | quantity | +----+-------+----------+ | 1 | shirt | 15 | | 2 | shoes | 10 | +----+-------+----------+ ``` We perform some changes to the `inventory` table and create new keyless table: ``` ALTER TABLE inventory ADD COLUMN color VARCHAR(10); INSERT INTO inventory VALUES (3, 'hat', 6, 'red'); UPDATE inventory SET quantity=0 WHERE pk=1; CREATE TABLE items (name varchar(50)); INSERT INTO items VALUES ('shirt'),('pants'); ``` Here is what table `inventory` has in the current working set: ``` +----+-------+----------+-------+ | pk | name | quantity | color | +----+-------+----------+-------+ | 1 | shirt | 0 | NULL | | 2 | shoes | 10 | NULL | | 3 | hat | 6 | red | +----+-------+----------+-------+ ``` To calculate the diff and view the results, we run the following query: ```sql SELECT * FROM DOLT_DIFF_STAT('main', 'WORKING'); ``` The results from `DOLT_DIFF_STAT()` show how the data has changed going from tip of `main` to our current working set: ``` +------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+ | table_name | rows_unmodified | rows_added | rows_deleted | rows_modified | cells_added | cells_deleted | cells_modified | old_row_count | new_row_count | old_cell_count | new_cell_count | +------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+ | inventory | 1 | 1 | 0 | 1 | 6 | 0 | 1 | 2 | 3 | 6 | 12 | | items | NULL | 2 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+ ``` To get a table specific changes going from the current working set to tip of `main`, we run the following query: ```sql SELECT * FROM DOLT_DIFF_STAT('WORKING', 'main', 'inventory'); ``` With result of single row: ``` +------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+ | table_name | rows_unmodified | rows_added | rows_deleted | rows_modified | cells_added | cells_deleted | cells_modified | old_row_count | new_row_count | old_cell_count | new_cell_count | +------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+ | inventory | 1 | 0 | 1 | 1 | 0 | 6 | 1 | 3 | 2 | 12 | 6 | +------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+ ``` ### `DOLT_DIFF_SUMMARY()` *The previous version of `dolt_diff_summary` was renamed to `dolt_diff_stat`.* The `DOLT_DIFF_SUMMARY()` table function is a summary of what tables changed and how between any two commits in the database. Only changed tables will be listed in the result, along with the diff type ('added', 'dropped', 'modified', 'renamed') and whether there are data and schema changes. `DOLT_DIFF_SUMMARY()` works like [CLI `dolt diff --summary` command](https://docs.dolthub.com/cli-reference/cli#dolt-diff), but two commits are required to use the `DOLT_DIFF_SUMMARY()` table function and the table name is optional. It returns empty result if there are no tables with changes. Note that the `DOLT_DIFF()` table function currently requires that argument values be literal values. #### Privileges `DOLT_DIFF_SUMMARY()` table function requires `SELECT` privilege for all tables if no table is defined or for the defined table only. #### Options ```sql DOLT_DIFF_SUMMARY(, , ) DOLT_DIFF_SUMMARY(, ) DOLT_DIFF_SUMMARY(, ) ``` The `DOLT_DIFF_SUMMARY()` table function takes three arguments: * `from_revision` — the revision of the table data for the start of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~", "WORKING", "STAGED"). * `to_revision` — the revision of the table data for the end of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~", "WORKING", "STAGED"). * `from_revision..to_revision` — gets the two dot diff summary, or revision of table data between the `from_revision` and `to_revision`. This is equivalent to `dolt_diff_summary(, , )`. * `from_revision...to_revision` — gets the three dot diff summary, or revision of table data between the `from_revision` and `to_revision`, *starting at the last common commit*. * `tablename` — the name of the table containing the data to diff. This argument is optional. When it's not defined, all tables with data diff will be returned. #### Schema ``` +-----------------+---------+ | field | type | +-----------------+---------+ | from_table_name | TEXT | | to_table_name | TEXT | | diff_type | TEXT | | data_change | BOOLEAN | | schema_change | BOOLEAN | +-----------------+---------+ ``` #### Example Consider we start with a table `inventory` in a database on `main` branch. When we make any changes, we can use the `DOLT_DIFF_SUMMARY()` function to calculate a diff of the table data or all tables with data changes across specific commits. Here is the schema of `inventory` at the tip of `main`: ``` +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | pk | int | NO | PRI | NULL | | | name | varchar(50) | YES | | NULL | | | quantity | int | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ ``` Here is what table `inventory` has at the tip of `main`: ``` +----+-------+----------+ | pk | name | quantity | +----+-------+----------+ | 1 | shirt | 15 | | 2 | shoes | 10 | +----+-------+----------+ ``` We perform some changes to the `inventory` table and create new keyless table: ``` ALTER TABLE inventory ADD COLUMN color VARCHAR(10); INSERT INTO inventory VALUES (3, 'hat', 6, 'red'); UPDATE inventory SET quantity=0 WHERE pk=1; CREATE TABLE items (name varchar(50)); ``` Here is what table `inventory` has in the current working set: ``` +----+-------+----------+-------+ | pk | name | quantity | color | +----+-------+----------+-------+ | 1 | shirt | 0 | NULL | | 2 | shoes | 10 | NULL | | 3 | hat | 6 | red | +----+-------+----------+-------+ ``` To calculate the diff and view the results, we run the following query: ```sql SELECT * FROM DOLT_DIFF_SUMMARY('main', 'WORKING'); ``` The results from `DOLT_DIFF_SUMMARY()` show how the data has changed going from tip of `main` to our current working set: ``` +-----------------+---------------+-----------+-------------+---------------+ | from_table_name | to_table_name | diff_type | data_change | schema_change | +-----------------+---------------+-----------+-------------+---------------+ | inventory | inventory | modified | true | true | | items | items | added | false | true | +-----------------+---------------+-----------+-------------+---------------+ ``` To get a table specific changes going from the current working set to tip of `main`, we run the following query: ```sql SELECT * FROM DOLT_DIFF_SUMMARY('WORKING', 'main', 'inventory'); ``` With result of single row: ``` +-----------------+---------------+-----------+-------------+---------------+ | from_table_name | to_table_name | diff_type | data_change | schema_change | +-----------------+---------------+-----------+-------------+---------------+ | inventory | inventory | modified | true | true | +-----------------+---------------+-----------+-------------+---------------+ ``` ### `DOLT_JSON_DIFF()` The `DOLT_JSON_DIFF()` table function is a summary of the changes between two JSON documents. #### Options ```sql DOLT_DIFF_SUMMARY(, ) ``` The `DOLT_DIFF_SUMMARY()` table function takes two arguments: * `from_document` — the document for the start of the diff. This argument is required. This may be a value from a JSON column, or a string that can be converted to JSON. * `to_document` — the document for the end of the diff. This argument is required. This may be a value from a JSON column, or a string that can be converted to JSON. #### Schema ``` +-----------------+---------+ | field | type | +-----------------+---------+ | diff_type | TEXT | | path | TEXT | | from_value | JSON | | to_value | JSON | +-----------------+---------+ ``` #### Example Consider we start with a table `inventory` in a database on `main` branch. Here is the schema of `inventory` at the tip of `main`: ``` +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | pk | int | NO | PRI | NULL | | | name | varchar(50) | YES | | NULL | | | metadata | json | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ ``` And here's the initial state of `inventory` has at the tip of `main`: ``` +----+-------+----------------------------------------------------------------+ | pk | name | metadata. | +----+-------+----------------------------------------------------------------+ | 1 | shirt | {"colors": ["red"] } | | 2 | shoes | {"colors": ["black"], "size": "small" } | | 3 | pants | {"colors": ["blue", "beige"], "materials": ["denim", "silk"] } | | 4 | tie | {"colours": ["red"], "clip-on": true } | +----+-------+----------------------------------------------------------------+ ``` We then create (but don't stage) a number of different changes, resulting in a working set that looks like this: ``` +----+-------+-------------------------------------------------------------+ | pk | name | metadata | +----+-------+-------------------------------------------------------------+ | 1 | shirt | {"colors": ["red", "blue"], "types": ["tee", "hawaiian"] } | | 2 | shoes | {"colors": ["white"], "size": "medium" } | | 3 | pants | {"colors": ["blue"] } | | 4 | tie | { "colors": ["red"], "clip-on": false } | +----+-------+-------------------------------------------------------------+ ``` We added values to the "shirt" document, edited data in the "shoes" document, deleted data from the "pants" document, and renamed a key in the "tie" document. If we want to get a list of every unstaged change made to any value in the metadata column, we can combine the DOLT\_JSON\_DIFF() table function with the DOLT\_WORKSPACE\_inventory table, via a lateral join: ```sql SELECT to_pk as pk, to_name as name, json_diff.diff_type as json_diff_type, row_diff.from_metadata, row_diff.to_metadata, path, json_diff.from_value, json_diff.to_value FROM DOLT_WORKSPACE_inventory AS row_diff JOIN lateral (SELECT * FROM DOLT_JSON_DIFF(from_metadata, to_metadata)) json_diff WHERE row_diff.diff_type = 'modified' and row_diff.staged = false; ``` The results of the query provide a summary of only the parts of the JSON documents that have changed between the staged version and the working set: ``` +----+-------+----------------+----------------------------------------------------------+------------------------------------------------------+-------------+------------------+--------------------+ | pk | name | json_diff_type | from_metadata | to_metadata | path | from_value | to_value | +----+-------+----------------+----------------------------------------------------------+------------------------------------------------------+-------------+------------------+--------------------+ | 0 | shirt | added | {"colors":["red"]} | {"colors":["red","blue"],"types":["tee","hawaiian"]} | $.colors[1] | NULL | "blue" | | 0 | shirt | added | {"colors":["red"]} | {"colors":["red","blue"],"types":["tee","hawaiian"]} | $.types | NULL | ["tee","hawaiian"] | | 1 | shoes | modified | {"colors":["black"],"size":"small"} | {"colors":["white"],"size":"medium"} | $.colors[0] | "black" | "white" | | 1 | shoes | modified | {"colors":["black"],"size":"small"} | {"colors":["white"],"size":"medium"} | $.size | "small" | "medium" | | 2 | pants | removed | {"colors":["blue","beige"],"materials":["denim","silk"]} | {"colors":["blue"]} | $.colors[1] | "beige" | NULL | | 2 | pants | removed | {"colors":["blue","beige"],"materials":["denim","silk"]} | {"colors":["blue"]} | $.materials | ["denim","silk"] | NULL | | 3 | tie | modified | {"clip-on":true,"colours":["red"]} | {"clip-on":false,"colors":["red"]} | $.clip-on | true | false | | 3 | tie | added | {"clip-on":true,"colours":["red"]} | {"clip-on":false,"colors":["red"]} | $.colors | NULL | ["red"] | | 3 | tie | removed | {"clip-on":true,"colours":["red"]} | {"clip-on":false,"colors":["red"]} | $.colours | ["red"] | NULL | +----+-------+----------------+----------------------------------------------------------+------------------------------------------------------+-------------+------------------+--------------------+ ``` Note how multiple changes in a single row of the `inventory` table are rendered as multiple rows in the result. When multiple keys in the same object have changed, `DOLT_JSON_DIFF` reports an individual diff for each key, instead of reporting a single diff for the entire object. Arrays are diffed by considering each index of the array separately. This means that inserting or removing values in an array anywhere other than the end will shift the indexes of each element, and will be reported as a modification at each index where the value changed. ### `DOLT_LOG()` The `DOLT_LOG` table function gets the commit log for all commits reachable from the provided revision's `HEAD` (or the current `HEAD` if no revision is provided). `DOLT_LOG()` works like [CLI `dolt log` command](https://docs.dolthub.com/cli-reference/cli#dolt-log). Note that the `DOLT_LOG()` table function currently requires that argument values be literal values. #### Privileges `DOLT_LOG()` table function requires `SELECT` privilege for all tables. #### Options ```sql DOLT_LOG([...], [--tables ...]) ``` The `DOLT_LOG()` table function takes any number of optional revision arguments: * `optional_revision`: a branch name, tag, or commit ref (with or without an ancestor spec) that specifies which ancestor commits to include in the results. If no revisions are specified, the default is the current branch `HEAD`. * If you'd like to get [two dot logs](https://www.dolthub.com/blog/2022-11-11-two-and-three-dot-diff-and-log/#two-dot-log) (all commits reachable by `revision2`, but NOT reachable by `revision1`), you can use `..` between revisions (`DOLT_LOG('revision1..revision2')`) or `^` in front of the revision you'd like to exclude (`DOLT_LOG('revision2', '^revision1')`). Note: if providing two revisions, one must contain `^`. * If you'd like to get [three dot logs](https://www.dolthub.com/blog/2022-11-11-two-and-three-dot-diff-and-log/#three-dot-log) (all commits reachable by `revision1` or `revision2`, excluding commits reachable by BOTH `revision1` AND `revision2`), you can use `...` between revisions (`DOLT_LOG('revision1...revision2')`. * `--min-parents`: The minimum number of parents a commit must have to be included in the log. * `--merges`: Equivalent to min-parents == 2, this will limit the log to commits with 2 or more parents. * `--parents`: Shows all parents of each commit in the log. * `--decorate`: Shows refs next to commits. Valid options are short, full, no, and auto. Note: the CLI `dolt log` command defaults to "short", while this table function defaults to "no". * `--not`: Excludes commits reachable by revision. * `--tables`: Limits the log to commits that affect the specified tables. Any number of comma separated tables can be specified. #### Schema ``` +--------------+----------+ | field | type | +--------------+--------- + | commit_hash | text | | committer | text | | email | text | | date | datetime | | message | text | | commit_order | int | | parents | text | -- column hidden unless `--parents` flag provided | refs | text | -- column hidden unless `--decorate` is "short" or "full" +--------------+--------- + ``` The `commit_order` field is an integer value that indicates the order of commits in descending order from HEAD. Note that `commit_order` values can be repeated for different levels of the topological sort of the commit graph. #### Example Consider we have the following commit graph: ``` A - B - C - D (main) \ E - F (feature) ``` To get the commit log for the `main` branch, we can use the query: ```sql SELECT * FROM DOLT_LOG('main'); ``` And it would return commits in reverse-chronological order - `D`,`C`, `B`, and `A`. The output will look something like: ``` +----------------------------------+-----------+--------------------+-----------------------------------+---------------+ | commit_hash | committer | email | date | message | +----------------------------------+-----------+--------------------+-----------------------------------+---------------+ | qi331vjgoavqpi5am334cji1gmhlkdv5 | bheni | brian@dolthub.com | 2019-06-07 00:22:24.856 +0000 UTC | update rating | | 137qgvrsve1u458briekqar5f7iiqq2j | bheni | brian@dolthub.com | 2019-04-04 22:43:00.197 +0000 UTC | change rating | | rqpd7ga1nic3jmc54h44qa05i8124vsp | bheni | brian@dolthub.com | 2019-04-04 21:07:36.536 +0000 UTC | fixes | | qfk3bpan8mtrl05n8nihh2e3t68t3hrk | bheni | brian@dolthub.com | 2019-04-04 21:01:16.649 +0000 UTC | test | +----------------------------------+-----------+--------------------+-----------------------------------+---------------+ ``` To get the commit log for the `feature` branch, we can change the revision in the above query: ```sql SELECT * FROM DOLT_LOG('feature'); ``` And it would return all commits reachable from the `HEAD` of `feature` - `F`, `E`, `C`, `B`, and `A`. **Two and three dot log** We also support two and three dot log. Two dot log returns commits from a revision, excluding commits from another revision. If we want all commits in `feature`, excluding commits from `main`, all of these queries will return commits `F` and `E`. ```sql SELECT * FROM DOLT_LOG('main..feature'); SELECT * FROM DOLT_LOG('feature', '^main'); SELECT * FROM DOLT_LOG('feature', '--not', 'main'); ``` Three dot log returns commits in either revision, excluding commits in BOTH revisions. If we want commits in `main` OR `feature`, excluding commits in `main` AND `feature`, this query would return commits `F`, `E`, and `D`. ```sql SELECT * FROM DOLT_LOG('main...feature'); ``` Note: The order of revisions in two dot log matters, but not for three dot log. `DOLT_LOG('main..feature')` returns `F` and `E`, while `DOLT_LOG('feature..main')` returns just `D`. `DOLT_LOG('main...feature')` and `DOLT_LOG('feature...main')` both return `F`, `E`, and `D`. Learn more about two vs three dot log [here](https://www.dolthub.com/blog/2022-11-11-two-and-three-dot-diff-and-log). ### `DOLT_PATCH()` Generate the SQL statements needed to patch a table (or all tables) from a starting revision to a target revision. This can be useful when you want to import data into Dolt from an external source, compare differences, and generate the SQL statements needed to patch the original source. This command is equivalent of [`dolt diff -r sql` CLI command](https://docs.dolthub.com/cli-reference/cli#dolt-diff). Both schema and/or data diff statements are returned if applicable. Some data diff cannot be produced from incompatible schema changes; these are shown as warnings containing which table this occurred on. The order of the statements is that the schema patch comes first after the data patch. If patching all tables, then we recommend to turn off the foreign key checks (`SET foreign_key_checks=0;`) before applying these patch statements in order of them returned to avoid conflicts. Getting SQL patch statements is only available as table function for now; the CLI `dolt patch` command will be supported in the future. #### Privileges `DOLT_PATCH()` table function requires `SELECT` privilege for all tables if no table is defined or for the defined table only. #### Options ```sql DOLT_PATCH(, , ) DOLT_PATCH(, ) DOLT_PATCH(, ) ``` The `DOLT_PATCH()` table function takes the following arguments: * `from_revision` — the revision of the table data for the start of the patch. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~", "WORKING", "STAGED"). * `to_revision` — the revision of the table data for the end of the patch. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~", "WORKING", "STAGED"). * `from_revision..to_revision` — gets the two dot patch, or revision of table data between the `from_revision` and `to_revision`. This is equivalent to `dolt_patch(, , )`. * `from_revision...to_revision` — gets the three dot patch, or revision of table data between the `from_revision` and `to_revision`, *starting at the last common commit*. * `tablename` — the name of the table containing the data and/or schema to patch. This argument is optional. When it's not defined, all tables with data and/or schema patch will be returned. #### Schema ``` +------------------+--------+ | field | type | +------------------+--------+ | statement_order | BIGINT | | from_commit_hash | TEXT | | to_commit_hash | TEXT | | table_name | TEXT | | diff_type | TEXT | | statement | TEXT | +------------------+--------+ ``` #### Example Consider we start with a table `inventory` in a database on `main` branch. When we make any changes, we can use the `DOLT_PATCH()` function to get SQL patch statements of the table data or all tables with data changes across specific commits. Here is the schema of `inventory` at the tip of `main`: ``` +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | pk | int | NO | PRI | NULL | | | name | varchar(50) | YES | | NULL | | | quantity | int | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ ``` Here is what table `inventory` has at the tip of `main`: ``` +----+-------+----------+ | pk | name | quantity | +----+-------+----------+ | 1 | shirt | 15 | | 2 | shoes | 10 | +----+-------+----------+ ``` We perform some changes to the `inventory` table and create new keyless table: ``` INSERT INTO inventory VALUES (3, 'hat', 6); UPDATE inventory SET quantity=0 WHERE pk=1; CREATE TABLE items (name varchar(50)); INSERT INTO items VALUES ('shirt'),('pants'); ``` Here is what table `inventory` has in the current working set: ``` +----+-------+----------+ | pk | name | quantity | +----+-------+----------+ | 1 | shirt | 0 | | 2 | shoes | 10 | | 3 | hat | 6 | +----+-------+----------+ ``` To get SQL patch statements, we run the following query: ```sql SELECT * FROM DOLT_PATCH('main', 'WORKING'); ``` The results from `DOLT_PATCH()` show how the data has changed going from tip of `main` to our current working set: ``` +-----------------+----------------------------------+----------------+------------+-----------+----------------------------------------------------------------------+ | statement_order | from_commit_hash | to_commit_hash | table_name | diff_type | statement | +-----------------+----------------------------------+----------------+------------+-----------+----------------------------------------------------------------------+ | 1 | gg4kasjl6tgrtoag8tnn1der09sit4co | WORKING | inventory | data | UPDATE `inventory` SET `quantity`=0 WHERE `pk`=1; | | 2 | gg4kasjl6tgrtoag8tnn1der09sit4co | WORKING | inventory | data | INSERT INTO `inventory` (`pk`,`name`,`quantity`) VALUES (3,'hat',6); | | 3 | gg4kasjl6tgrtoag8tnn1der09sit4co | WORKING | items | schema | CREATE TABLE `items` ( | | | | | | | `name` varchar(50) | | | | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | | 4 | gg4kasjl6tgrtoag8tnn1der09sit4co | WORKING | items | data | INSERT INTO `items` (`name`) VALUES ('shirt'); | | 5 | gg4kasjl6tgrtoag8tnn1der09sit4co | WORKING | items | data | INSERT INTO `items` (`name`) VALUES ('pants'); | +-----------------+----------------------------------+----------------+------------+-----------+----------------------------------------------------------------------+ ``` To get a table specific schema patch going from the current working set to tip of `main`, we run the following query: ```sql SELECT * FROM DOLT_PATCH('WORKING', 'main', 'items') WHERE diff_type = 'schema'; ``` With result of single row: ``` +-----------------+------------------+----------------------------------+------------+-----------+---------------------+ | statement_order | from_commit_hash | to_commit_hash | table_name | diff_type | statement | +-----------------+------------------+----------------------------------+------------+-----------+---------------------+ | 1 | WORKING | gg4kasjl6tgrtoag8tnn1der09sit4co | items | schema | DROP TABLE `items`; | +-----------------+------------------+----------------------------------+------------+-----------+---------------------+ ``` ### `DOLT_PREVIEW_MERGE_CONFLICTS_SUMMARY()` The `DOLT_PREVIEW_MERGE_CONFLICTS_SUMMARY()` table function provides a summary of merge conflicts that would occur when merging a branch. This function is useful for understanding potential conflicts before performing an actual merge operation, allowing you to identify which tables would have conflicts and how many data and schema conflicts would occur. This function performs a "dry run" merge operation and returns information about conflicts without actually modifying the database or creating a merge commit. #### Privileges `DOLT_PREVIEW_MERGE_CONFLICTS_SUMMARY()` table function requires `SELECT` privilege for all tables. #### Options ```sql DOLT_PREVIEW_MERGE_CONFLICTS_SUMMARY(, ) ``` The `DOLT_PREVIEW_MERGE_CONFLICTS_SUMMARY()` table function takes two required arguments: * `base_branch` — the base branch to merge into (e.g. "main"). * `merge_branch` — the branch to merge into the base branch (e.g. "feature\_branch"). #### Schema ``` +---------------------+--------+ | field | type | +---------------------+--------+ | table | TEXT | | num_data_conflicts | BIGINT | | num_schema_conflicts| BIGINT | +---------------------+--------+ ``` #### Example Consider a scenario where you have a `main` branch and a `feature_branch` that have diverged and made conflicting changes to the same data. You can preview the conflicts that would occur when merging `feature_branch` into `main`: ```sql SELECT * FROM DOLT_PREVIEW_MERGE_CONFLICTS_SUMMARY('main', 'feature_branch'); ``` This might return results like: ``` +----------+--------------------+---------------------+ | table | num_data_conflicts | num_schema_conflicts| +----------+--------------------+---------------------+ | users | 3 | 0 | | orders | 1 | 0 | | products | NULL | 2 | +----------+--------------------+---------------------+ ``` Note that if there are schema conflicts the data conflicts are not able to be calculated and that column will be null. This output indicates that merging `feature_branch` into `main` would create conflicts in three tables: * The `users` table would have 3 data conflicts and no schema conflicts * The `orders` table would have 1 data conflict and no schema conflicts * The `products` table would have 2 schema conflicts If there would be no conflicts, the function returns an empty result set. This information helps you understand the scope of conflicts before attempting a merge, allowing you to plan conflict resolution strategies or coordinate with other developers who may have made conflicting changes. ### `DOLT_PREVIEW_MERGE_CONFLICTS()` The `DOLT_PREVIEW_MERGE_CONFLICTS()` table function provides detailed information about merge conflicts that would occur when merging a branch. Unlike `DOLT_PREVIEW_MERGE_CONFLICTS_SUMMARY()` which only provides a count of conflicts per table, this function returns the actual conflicting rows with their base, ours, and theirs values. This function performs a "dry run" merge operation and returns detailed conflict information without actually modifying the database or creating a merge commit. The results are similar to what you would see in the `dolt_conflicts_$TABLENAME` system tables after performing an actual merge, but without making any changes to the database. #### Privileges `DOLT_PREVIEW_MERGE_CONFLICTS()` table function requires `SELECT` privilege on the specified table. #### Options ```sql DOLT_PREVIEW_MERGE_CONFLICTS(, , ) ``` The `DOLT_PREVIEW_MERGE_CONFLICTS()` table function takes three required arguments: * `base_branch` — the base branch to merge into (e.g. "main"). * `merge_branch` — the branch to merge into the base branch (e.g. "feature\_branch"). * `table_name` — the name of the table to preview conflicts for. #### Schema The schema of the `DOLT_PREVIEW_MERGE_CONFLICTS()` function depends on the schema of the specified table. For each column `X` in the table, the result set contains three columns: * `base_X` — the value of column X at the common ancestor commit * `our_X` — the value of column X in the base branch * `their_X` — the value of column X in the merge branch Additionally, the result set includes these metadata columns: ``` +------------------+--------+ | field | type | +------------------+--------+ | from_root_ish | TEXT | | our_diff_type | TEXT | | their_diff_type | TEXT | | dolt_conflict_id | TEXT | +------------------+--------+ ``` Where: * `from_root_ish` — the commit hash of the merge branch (the "from" branch of the merge). This hash can be used to identify which merge produced a conflict, since conflicts can accumulate across merges. User code generally ignores this column. * `our_diff_type` and `their_diff_type` indicate whether the row was "added", "modified", or "removed" in the corresponding branch * `dolt_conflict_id` is a unique identifier for each conflict #### Example Consider a table `users` with columns `id`, `name`, and `email` that has conflicts between `main` and `feature_branch`. You can preview the specific conflicts: ```sql SELECT * FROM DOLT_PREVIEW_MERGE_CONFLICTS('main', 'feature_branch', 'users'); ``` This might return results like: ``` +----------------------------------+---------+-----------+----------------+---------+-----------+------------------+---------------+-----------+-----------+-------------------+-----------------+------------------------+ | from_root_ish | base_id | base_name | base_email | our_id | our_name | our_email | our_diff_type | their_id | their_name| their_email | their_diff_type | dolt_conflict_id | +----------------------------------+---------+-----------+----------------+---------+-----------+------------------+---------------+-----------+-----------+-------------------+-----------------+------------------------+ | abc123def456789012345678901234567 | 1 | John | john@email.com | 1 | John Doe | john@email.com | modified | 1 | John | john@newemail.com | modified | abc123def456 | | abc123def456789012345678901234567 | NULL | NULL | NULL | 2 | Jane | jane@email.com | added | 2 | Jane Doe | jane@email.com | added | def789ghi012 | +----------------------------------+---------+-----------+----------------+---------+-----------+------------------+---------------+-----------+-----------+-------------------+-----------------+------------------------+ ``` This output shows: * Row 1: Both branches modified the same user but with different changes (name vs email) * Row 2: Both branches added a new user with the same ID but different data To view only specific columns for easier reading: ```sql SELECT dolt_conflict_id, base_name, our_name, our_diff_type, their_name, their_diff_type FROM DOLT_PREVIEW_MERGE_CONFLICTS('main', 'feature_branch', 'users'); ``` #### Keyless Tables For keyless tables (tables without primary keys), the behavior is slightly different. Dolt uses content-based addressing to identify rows, so conflicts in keyless tables are detected when the same content would be added or modified differently on each branch. Keyless tables include additional columns not present in tables with primary keys: ``` +-------------------+--------+ | field | type | +-------------------+--------+ | base_cardinality | BIGINT | | our_cardinality | BIGINT | | their_cardinality | BIGINT | +-------------------+--------+ ``` * `base_cardinality` — the number of occurrences of the conflicting row in the merge ancestor commit * `our_cardinality` — the number of occurrences of the conflicting row in the base branch * `their_cardinality` — the number of occurrences of the conflicting row in the merge branch Consider a keyless table `logs` with columns `timestamp`, `level`, and `message`: ```sql SELECT * FROM DOLT_PREVIEW_MERGE_CONFLICTS('main', 'feature_branch', 'logs'); ``` This might return results like: ``` +----------------------------------+---------------------+-------------+------------------+---------------------+-------------+------------------+---------------+---------------------+-------------+------------------+-----------------+------------------------+------------------+-------------------+---------------------+ | from_root_ish | base_timestamp | base_level | base_message | our_timestamp | our_level | our_message | our_diff_type | their_timestamp | their_level | their_message | their_diff_type | dolt_conflict_id | base_cardinality | our_cardinality | their_cardinality | +----------------------------------+---------------------+-------------+------------------+---------------------+-------------+------------------+---------------+---------------------+-------------+------------------+-----------------+------------------------+------------------+-------------------+---------------------+ | abc123def456789012345678901234567 | 2023-01-01 10:00:00 | ERROR | Database timeout | 2023-01-01 10:00:00 | ERROR | Database timeout | modified | 2023-01-01 10:00:00 | ERROR | Database timeout | modified | xyz789abc123 | 1 | 3 | 2 | +----------------------------------+---------------------+-------------+------------------+---------------------+-------------+------------------+---------------+---------------------+-------------+------------------+-----------------+------------------------+------------------+-------------------+---------------------+ ``` In this example, the same log entry exists once in the base branch, but appears 3 times in our branch and 2 times in their branch, creating a conflict about cardinality (how many times the row should appear). #### Notes If there are no conflicts in the specified table, the function returns an empty result set. This detailed view allows you to examine the exact differences that would cause conflicts and plan appropriate resolution strategies before performing the actual merge. The results are similar to what you would see in the `dolt_conflicts_$TABLENAME` system tables after an actual merge, but without making any changes to your database. ### `DOLT_REFLOG()` The `DOLT_REFLOG()` table function shows the history of named refs (e.g. branches and tags), which is useful when you want to understand how a branch or tag has changed over time to reference different commits, particularly for information that isn't surfaced through the `dolt_log` system table or `dolt_log()` table function. For example, if you use `dolt_reset()` to change the commit a branch points to, you can use `dolt_reflog()` to see what commit the branch was pointing to before it was moved to that commit. Another common use case for `dolt_reflog()` is to recreate a branch or tag that was accidentally deleted. The example section below shows how to recreate a deleted branch. The data from Dolt's reflog comes from [Dolt's journaling chunk store](https://www.dolthub.com/blog/2023-03-08-dolt-chunk-journal/). This data is local to a Dolt database and never included when pushing, pulling, or cloning a Dolt database. This means when you clone a Dolt database, it will not have any reflog data until you perform operations that change what commit branches or tags reference. Dolt's reflog is similar to [Git's reflog](https://git-scm.com/docs/git-reflog), but there are a few differences: * The Dolt reflog currently only supports named references, such as branches and tags, and not any of Git's special refs (e.g. `HEAD`, `FETCH-HEAD`, `MERGE-HEAD`). * The Dolt reflog can be queried for the log of references, even after a reference has been deleted. In Git, once a branch or tag is deleted, the reflog for that ref is also deleted and to find the last commit a branch or tag pointed to you have to use Git's special `HEAD` reflog to find the commit, which can sometimes be challenging. Dolt makes this much easier by allowing you to see the history for a deleted ref so you can easily see the last commit a branch or tag pointed to before it was deleted. #### Privileges There are no special privileges required to use the `dolt_reflog()` table function. #### Options ```sql DOLT_REFLOG() DOLT_REFLOG(['--all'], ) ``` The `dolt_reflog()` table function can be called with no arguments or with one argument. If called without any arguments, it will return the full reference log, which lists changes from newest to oldest for all tracked references. If called with one argument, that argument is the name of a ref to query. This can be the name of a branch (e.g. "myBranch") or the name of a tag (e.g. "v1.1.4") or it can be the fully qualified ref path (e.g. "refs/heads/myBranch"). The `ref_name` parameter is case-insensitive. The `dolt_reflog()` table function can also be called with the `--all` flag to show all refs, including hidden refs, such as DoltHub workspace refs. #### Schema ``` +-----------------------+-----------+ | field | type | +-----------------------+-----------+ | ref | TEXT | | ref_timestamp | TIMESTAMP | | commit_hash | TEXT | | commit_message | TEXT | +-----------------------+-----------+ ``` #### Example The example below shows how to recreate a branch that was deleted by finding the last commit it referenced in Dolt's reflog. ```sql -- Someone accidentally deletes the wrong branch! call dolt_branch('-D', 'prodBranch'); -- After we realize the wrong branch has been deleted, we query the Dolt reflog on the same Dolt database instance -- where the branch was deleted to see what commits the prodBranch branch has referenced. Using the same Dolt -- instance is important, since reflog information is always local and not included when pushing/pulling databases. select * from dolt_reflog('prodBranch'); +-----------------------+---------------------+----------------------------------+-------------------------------+ | ref | ref_timestamp | commit_hash | commit_message | +-----------------------+---------------------+----------------------------------+-------------------------------+ | refs/heads/prodBranch | 2023-10-25 20:54:37 | v531ptpmv2tquig8v591tsjghtj84ksg | inserting row 42 | | refs/heads/prodBranch | 2023-10-25 20:53:12 | rvt34lqrbtdr3dhnjchruu73lik4e398 | inserting row 100000 | | refs/heads/prodBranch | 2023-10-25 20:53:06 | v531ptpmv2tquig8v591tsjghtj84ksg | inserting row 42 | | refs/heads/prodBranch | 2023-10-25 20:52:43 | ihuj1l7fmqq37sjhtlrgpup5n76gfhju | inserting row 1 into table xy | +-----------------------+---------------------+----------------------------------+-------------------------------+ -- The last commit prodBranch pointed to was v531ptpmv2tquig8v591tsjghtj84ksg, so to restore our branch, we -- just need to create a branch with the same name, pointing to that last commit. call dolt_branch('prodBranch', 'v531ptpmv2tquig8v591tsjghtj84ksg'); ``` ### `DOLT_SCHEMA_DIFF()` The `DOLT_SCHEMA_DIFF()` table function calculates the schema difference between any two commits in the database. Each row in the result set describes how a table was altered between the two commits, including the table's create statement at to and from commits. Note that the `DOLT_SCHEMA_DIFF()` table function currently requires that argument values be literal values. #### Privileges `DOLT_SCHEMA_DIFF()` table function requires `SELECT` privilege for all tables if no table is defined or for the defined table only. #### Options ```sql DOLT_SCHEMA_DIFF(, , ) DOLT_SCHEMA_DIFF(, ) DOLT_SCHEMA_DIFF(, ) ``` The `DOLT_SCHEMA_DIFF()` table function takes three arguments: * `from_revision` — the revision of the table data for the start of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~", "WORKING", "STAGED"). * `to_revision` — the revision of the table data for the end of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main\~", "WORKING", "STAGED"). * `from_revision..to_revision` — gets the two dot diff, or revision of table schema between the `from_revision` and `to_revision`. This is equivalent to `dolt_schema_diff(, , [])`. * `from_revision...to_revision` — gets the three dot diff, or revision of table schema between the `from_revision` and `to_revision`, *starting at the last common commit*. * `tablename` — the name of the table to diff. This argument is optional. When it's not defined, all tables with schema diffs will be returned. #### Schema ``` +-----------------------+------+ | field | type | +-----------------------+------+ | from_table_name | TEXT | | to_table_name | TEXT | | from_create_statement | TEXT | | to_create_statement | TEXT | +-----------------------+------+ ``` #### Example For this example, we'll consider three tables within the context of two branches: `main` and `feature_branch`. These are the tables on `main`: `employees`, `inventory`, `vacations`. These are the tables on `feature_branch`: `inventory`, `photos`, `trips`. To figure out how these tables changed, we run the following query: ```sql SELECT * FROM DOLT_SCHEMA_DIFF("main", "feature_branch") ``` The results from `DOLT_SCHEMA_DIFF()` show how the schema for all tables has changed going from tip of `main` to tip of `feature_branch`: ``` +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ | from_table_name | to_table_name | from_create_statement | to_create_statement | +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ | employees | | CREATE TABLE `employees` ( | | | | | `pk` int NOT NULL, | | | | | `name` varchar(50), | | | | | PRIMARY KEY (`pk`) | | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | | | inventory | inventory | CREATE TABLE `inventory` ( | CREATE TABLE `inventory` ( | | | | `pk` int NOT NULL, | `pk` int NOT NULL, | | | | `name` varchar(50), | `name` varchar(50), | | | | `quantity` int, | `color` varchar(10), | | | | PRIMARY KEY (`pk`) | PRIMARY KEY (`pk`) | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | | | photos | | CREATE TABLE `photos` ( | | | | | `pk` int NOT NULL, | | | | | `name` varchar(50), | | | | | `dt` datetime(6), | | | | | PRIMARY KEY (`pk`) | | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | | vacations | trips | CREATE TABLE `vacations` ( | CREATE TABLE `trips` ( | | | | `pk` int NOT NULL, | `pk` int NOT NULL, | | | | `name` varchar(50), | `name` varchar(50), | | | | PRIMARY KEY (`pk`) | PRIMARY KEY (`pk`) | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ ``` Let's look at the returned data. 1. The first row has values in `from_table_name` and `from_create_statement` columns, while `to_table_name` and `to_create_statement` columns are empty. This means that between `main` and `feature_branch`, the table `employees` was deleted. 2. The second row has identical values for `from_table_name` and `to_table_name`, but `from_create_statement` is different from `to_create_statement`. This means the table's schema changed between `main` and `feature_branch`. 3. The third row is similar to the first row, except its `to_*` columns are empty, and `from_*` columns are set. This means that between `main` and `feature_branch`, the table `photos` was added. 4. Finally, the last row has mostly identical `from_create_statement` and `to_create_statement` columns, but different `from_table_name` and `to_table_name` columns. This means the table was renamed changed between `main` and `feature_branch`. We invoked `DOLT_SCHEMA_DIFF()` with branch names, but we could have used any revision specifier. For example, we could have used commit hashes or tag names, and would have gotten the same results. Using tags or commit hashes: ```sql select * from dolt_schema_diff('v1', 'v1.1'); select * from dolt_schema_diff('tjj1kp2mnoad8crv6b94mh4a4jiq7ab2', 'v391rm7r0t4989sgomv0rpn9ue4ugo6g'); ``` So far, we have always supplied just the first two parameters, the `from` and `to` revisions, but we have not specified the optional table parameter, so `DOLT_SCHEMA_DIFF()` returned schema diffs of all changed tables. We can scope `DOLT_SCHEMA_DIFF()` to a specific table simply by specifying it as the last parameter. Let's try this with the `inventory` table. ```sql SELECT * FROM DOLT_SCHEMA_DIFF("main", "feature_branch", "inventory") ``` We will see this set of results: ``` +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ | from_table_name | to_table_name | from_create_statement | to_create_statement | +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ | inventory | inventory | CREATE TABLE `inventory` ( | CREATE TABLE `inventory` ( | | | | `pk` int NOT NULL, | `pk` int NOT NULL, | | | | `name` varchar(50), | `name` varchar(50), | | | | `quantity` int, | `color` varchar(10), | | | | PRIMARY KEY (`pk`) | PRIMARY KEY (`pk`) | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ ``` When a table is renamed, we can specify either the "old" table name, or the "new" table name, and we will receive the same results. The following two queries will provide the same results: ```sql SELECT * FROM DOLT_SCHEMA_DIFF("main", "feature_branch", "trips"); SELECT * FROM DOLT_SCHEMA_DIFF("main", "feature_branch", "vacations"); ``` Here are the results: ``` +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ | from_table_name | to_table_name | from_create_statement | to_create_statement | +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ | vacations | trips | CREATE TABLE `vacations` ( | CREATE TABLE `trips` ( | | | | `pk` int NOT NULL, | `pk` int NOT NULL, | | | | `name` varchar(50), | `name` varchar(50), | | | | PRIMARY KEY (`pk`) | PRIMARY KEY (`pk`) | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ ``` Finally, we can flip the order of the revisions to get the schema diff in the opposite direction. ```sql select * from dolt_schema_diff('feature_branch', 'main'); ``` The above query will produce this output: ``` +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ | from_table_name | to_table_name | from_create_statement | to_create_statement | +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ | photos | | CREATE TABLE `photos` ( | | | | | `pk` int NOT NULL, | | | | | `name` varchar(50), | | | | | `dt` datetime(6), | | | | | PRIMARY KEY (`pk`) | | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | | | | employees | | CREATE TABLE `employees` ( | | | | | `pk` int NOT NULL, | | | | | `name` varchar(50), | | | | | PRIMARY KEY (`pk`) | | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | | inventory | inventory | CREATE TABLE `inventory` ( | CREATE TABLE `inventory` ( | | | | `pk` int NOT NULL, | `pk` int NOT NULL, | | | | `name` varchar(50), | `name` varchar(50), | | | | `color` varchar(10), | `quantity` int, | | | | PRIMARY KEY (`pk`) | PRIMARY KEY (`pk`) | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | | trips | vacations | CREATE TABLE `trips` ( | CREATE TABLE `vacations` ( | | | | `pk` int NOT NULL, | `pk` int NOT NULL, | | | | `name` varchar(50), | `name` varchar(50), | | | | PRIMARY KEY (`pk`) | PRIMARY KEY (`pk`) | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; | +-----------------+---------------+-------------------------------------------------------------------+-------------------------------------------------------------------+ ``` Note the difference between this call and the previous `dolt_schema_diff('main', 'feature_branch')` invocation: 1. First row shows that the table `photos` was deleted 2. Second row show the creation of `employees` table 3. Third row has the `from_create_statement` and `to_create_statement` columns swapped 4. Fourth row shows the inverse rename of `trips` to `vacations` #### Example query You can try calling `DOLT_SCHEMA_DIFF()` against the [DoltHub docs\_examples DB](https://www.dolthub.com/repositories/dolthub/docs_examples), by getting the diff of schemas between `schema_diff_v1` and `schema_diff_v2` tags, which correspond to `main` and `feature_branch` branches from these examples. {% embed url="" %} ### `DOLT_QUERY_DIFF()` The `DOLT_QUERY_DIFF()` table function calculates the data difference between any two queries, producing a table similar to the `DOLT_DIFF()` table function. #### Privileges `DOLT_QUERY_DIFF()` table function requires `SELECT` privilege on all tables in the database (e.g. `GRANT SELECT ON mydb.*`). #### Example For this example, we have the table `t` in two branches `main` and `other`. On `main`, the table `t` has the following data: ``` +---+----+ | i | j | +---+----+ | 0 | 0 | | 1 | 10 | | 3 | 3 | | 4 | 4 | +---+----+ ``` On `other`, the table `t` has the following data: ``` +---+---+ | i | j | +---+---+ | 0 | 0 | | 1 | 1 | | 2 | 2 | | 4 | 4 | +---+---+ ``` We can use the `DOLT_QUERY_DIFF()` table function to calculate the difference between the two tables: ``` dolt> select * from dolt_query_diff('select * from t as of main', 'select * from t as of other'); +--------+--------+------+------+-----------+ | from_i | from_j | to_i | to_j | diff_type | +--------+--------+------+------+-----------+ | 1 | 10 | 1 | 1 | modified | | NULL | NULL | 2 | 2 | added | | 3 | 3 | NULL | NULL | deleted | +--------+--------+------+------+-----------+ 3 rows in set (0.00 sec) ``` #### Note Query diff is performed brute force and thus, will be slow for large result sets. The algorithm is super linear (`n^2`) on the size of the results sets. Over time, we will optimize this to use features of the storage engine to improve performance. ### `DOLT_BRANCH_STATUS()` The `DOLT_BRANCH_STATUS()` table function calculates the number of commits `ahead` and `behind` the target branch is from the base branch. In other words, this tells you the number of commits target branch has that base does not and vice versa. #### Privileges `DOLT_BRANCH_STATUS()` table function requires `SELECT` privilege for all tables used in each query. #### Options ```sql DOLT_BRANCH_STATUS(, [ select * from dolt_log(); +----------------------------------+-----------+-------------------+---------------------+----------------------------+--------------+ | commit_hash | committer | email | date | message | commit_order | +----------------------------------+-----------+-------------------+---------------------+----------------------------+--------------+ | 0qkkos3enbd4bh8e1ppbcupsa1paubr0 | root | root@localhost | 2025-06-02 21:06:20 | main commit | 2 | | 8elol3v7a8u94rti5fjpakkm1vq25slv | jcor | james@dolthub.com | 2025-06-02 21:05:52 | Initialize data repository | 1 | +----------------------------------+-----------+-------------------+---------------------+----------------------------+--------------+ 2 rows in set (0.00 sec) ``` `other`'s history looks like this: ```sql tmp/other> select * from dolt_log(); +----------------------------------+-----------+-------------------+---------------------+----------------------------+--------------+ | commit_hash | committer | email | date | message | commit_order | +----------------------------------+-----------+-------------------+---------------------+----------------------------+--------------+ | ip47q9ee2un84se8nvq5c5kuil7uuqvo | root | root@localhost | 2025-06-02 21:06:12 | other commit 2 | 3 | | hoitroluotdc94cdmma82mvh9s0ct94b | root | root@localhost | 2025-06-02 21:06:11 | other commit 1 | 2 | | 8elol3v7a8u94rti5fjpakkm1vq25slv | jcor | james@dolthub.com | 2025-06-02 21:05:52 | Initialize data repository | 1 | +----------------------------------+-----------+-------------------+---------------------+----------------------------+--------------+ 3 rows in set (0.00 sec) ``` We can get the number of commits `other` is ahead and behind of `main`, like so: ```sql tmp/main> SELECT * FROM DOLT_BRANCH_STATUS('main', 'other'); +--------+---------------+----------------+ | branch | commits_ahead | commits_behind | +--------+---------------+----------------+ | other | 2 | 1 | +--------+---------------+----------------+ 1 row in set (0.00 sec) ``` This means that `other` has commits `"other commit 1"` and `"other commit 2"` that are missing from `main`, and `main` has commit `"main commit"` that is missing from `other`. ### `DOLT_TEST_RUN()` The `DOLT_TEST_RUN()` table function executes tests defined in the [`dolt_tests` system table](https://docs.dolthub.com/sql-reference/dolt-system-tables#dolt_tests) and returns the results. Tests can be run individually by name, by test group, or all at once. #### Privileges `DOLT_TEST_RUN()` table function requires `SELECT` privilege for all tables used in test queries, as well as access to the `dolt_tests` system table. #### Options ```sql DOLT_TEST_RUN() DOLT_TEST_RUN('*') DOLT_TEST_RUN() DOLT_TEST_RUN() DOLT_TEST_RUN(, , ) ``` The `DOLT_TEST_RUN()` table function will accept any number of arguments: * If called with no arguments, or with the wildcard `*`, it will run all tests in the `dolt_tests` table * One or more test names, or test group names, can be passed in. #### Schema ``` +-----------------+------+ | field | type | +-----------------+------+ | test_name | TEXT | | test_group_name | TEXT | | query | TEXT | | status | TEXT | | message | TEXT | +-----------------+------+ ``` The `status` field will be either `PASS` or `FAIL`. The `message` field contains information about test failures, and will be empty for passing tests. #### Example Consider a `dolt_tests` table with the following test definitions: ```sql INSERT INTO dolt_tests VALUES ('user_count_test', 'users', 'SELECT COUNT(*) FROM users', 'expected_single_value', '>=', '10'), ('active_users_test', 'users', 'SELECT COUNT(*) FROM users WHERE active = 1', 'expected_single_value', '>', '5'), ('table_columns_test', 'schema', 'SELECT * FROM products', 'expected_columns', '==', '4'); ``` Run all tests: ```sql SELECT * FROM DOLT_TEST_RUN(); ``` Results might look like: ``` +-------------------+-----------------+-----------------------------------------------+--------+-----------------------------------------------------------------+ | test_name | test_group_name | query | status | message | +-------------------+-----------------+-----------------------------------------------+--------+-----------------------------------------------------------------+ | user_count_test | users | SELECT COUNT(*) FROM users | PASS | | | active_users_test | users | SELECT COUNT(*) FROM users WHERE active = 1 | FAIL | Assertion failed: expected_single_value greater than 5, got 3 | | table_columns_test| schema | SELECT * FROM products | PASS | | +-------------------+-----------------+-----------------------------------------------+--------+-----------------------------------------------------------------+ ``` Other valid arguments include: ```sql --- This will run all tests SELECT * FROM DOLT_TEST_RUN('*'); --- This will run both groups SELECT * FROM DOLT_TEST_RUN('users', 'schema') --- This will run these two specific tests SELECT * FROM DOLT_TEST_RUN('user_count_test', 'table_columns_test') ```