DoltHubBlogDiscordGitHub
Search…
Introduction
What Is Dolt?
Installation
Getting Started
Use Cases
Concepts
Dolt
DoltHub/DoltLab
Guides
Contributing
DoltLab
Importing Data
SQL Reference
Running the Server
Version Control Features
Using Branches
Merges
Querying History
Procedures
Functions
System Tables
System Variables
Saved Queries
SQL Language Support
Supported Clients
Benchmarks and Metrics
CLI Reference
CLI Command Reference
Architecture
Overview
Storage Engine
SQL
Other
FAQ
Roadmap
Powered By GitBook
Functions

Table of Contents

Informational Functions

ACTIVE_BRANCH()

The ACTIVE_BRANCH() function returns the name of the currently active branch for this session.
mysql> select active_branch();
+-----------------+
| active_branch() |
+-----------------+
| main |
+-----------------+

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:
mysql> SELECT DOLT_MERGE_BASE('feature', 'main');
+------------------------------------+
| DOLT_MERGE_BASE('feature', 'main') |
+------------------------------------+
| tjj1kp2mnoad8crv6b94mh4a4jiq7ab2 |
+------------------------------------+

HASHOF()

The HASHOF() function returns the commit hash of a branch or other commit spec.
mysql> select hashof('main');
+----------------------------------+
| hashof('main') |
+----------------------------------+
| v391rm7r0t4989sgomv0rpn9ue4ugo6g |
+----------------------------------+

DOLT_VERSION()

The DOLT_VERSION() function returns the version string for the Dolt binary.
mysql> select dolt_version();
+----------------+
| dolt_version() |
+----------------+
| 0.40.4 |
+----------------+

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 function support is currently limited to only the DOLT_DIFF() function and 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. 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, 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 has restrictions on how it can be used in queries. It does not support aliasing or joining with other tables, and argument values must currently be literal values.

Options

DOLT_DIFF(<from_revision>, <to_revision>, <tablename>)
The DOLT_DIFF() table function takes 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~").
  • tablename — the name of the table containing the data to diff.

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:
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 |
+---------+-------+---------+----------+----------------+-----------------------------------+-----------+---------+---------------+-------------+-----------------------------------+-----------+

DOLT_DIFF_SUMMARY()

The DOLT_DIFF_SUMMARY() table function calculates the data difference summary 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 summary 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_SUMMARY() works like CLI dolt diff --summary command, but two commits must be defined to use DOLT_DIFF_SUMMARY() table function and the table name is optional. This table function only provides rows added and deleted information for keyless tables. It returns empty result for tables with no data changes.
Note that the DOLT_DIFF_SUMMARY() table function currently has restrictions on how it can be used in queries. It does not support aliasing or joining with other tables, and argument values must 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

DOLT_DIFF_SUMMARY(<from_revision>, <to_revision>, <optional_tablename>)
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").
  • 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_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));
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:
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:
+------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+
| 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:
SELECT * FROM DOLT_DIFF_SUMMARY('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 |
+------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+

Version Control Functions

Deprecation Warning

All of the functions in this section have been deprecated and replaced with their stored procedure equivalents. They will be removed in a future release.

DOLT_ADD()

Deprecated. Use the DOLT_ADD stored procedure.
SELECT DOLT_ADD('-A');
SELECT DOLT_ADD('.');
SELECT DOLT_ADD('table1', 'table2');

DOLT_CHECKOUT()

Deprecated. Use the DOLT_CHECKOUT stored procedure instead.
SELECT DOLT_CHECKOUT('-b', 'my-new-branch');
SELECT DOLT_CHECKOUT('my-existing-branch');
SELECT DOLT_CHECKOUT('my-table');

DOLT_COMMIT()

Deprecated. Use the DOLT_COMMIT stored procedure instead.
SELECT DOLT_COMMIT('-a', '-m', 'This is a commit');
SELECT DOLT_COMMIT('-m', 'This is a commit');
SELECT DOLT_COMMIT('-m', 'This is a commit', '--author', 'John Doe <[email protected]>');

DOLT_FETCH()

Deprecated. Use the DOLT_FETCH stored procedure instead.
SELECT DOLT_FETCH('origin', 'main');
SELECT DOLT_FETCH('origin', 'feature-branch');
SELECT DOLT_FETCH('origin', 'refs/heads/main:refs/remotes/origin/main');

DOLT_MERGE()

Deprecated. Use the DOLT_MERGE stored procedure instead.
SELECT DOLT_MERGE('feature-branch'); -- Optional --squash parameter
SELECT DOLT_MERGE('feature-branch', '-no-ff', '-m', 'This is a msg for a non fast forward merge');
SELECT DOLT_MERGE('--abort');

DOLT_RESET()

Deprecated. Use the DOLT_RESET stored procedure instead.
SELECT DOLT_RESET('--hard');
SELECT DOLT_RESET('my-table'); -- soft reset

DOLT_PUSH()

Deprecated. Use the DOLT_PUSH stored procedure instead.
SELECT DOLT_PUSH('origin', 'main');
SELECT DOLT_PUSH('--force', 'origin', 'main');

DOLT_PULL()

Deprecated. Use the DOLT_PULL stored procedure instead.
SELECT DOLT_PULL('origin');
SELECT DOLT_PULL('feature-branch', '--force');
Previous
Procedures
Next
System Tables
Last modified 1d ago
Export as PDF
Copy link
Edit on GitHub
On this page
Table of Contents
Informational Functions
ACTIVE_BRANCH()
DOLT_MERGE_BASE()
HASHOF()
DOLT_VERSION()
Table Functions
DOLT_DIFF()
DOLT_DIFF_SUMMARY()
Version Control Functions
Deprecation Warning
DOLT_ADD()
DOLT_CHECKOUT()
DOLT_COMMIT()
DOLT_FETCH()
DOLT_MERGE()
DOLT_RESET()
DOLT_PUSH()
DOLT_PULL()