Introducing DOLT_PATCH()
Dolt is a version controlled relational database that is a combination of the version control features of Git and the relational database features of MySQL. Today, we announce another new feature, the DOLT_PATCH() table function. This feature's functionality is similar to git format-patch command, producing a patch that can be applied to any working directory. In this post, we'll go over what DOLT_PATCH() is useful for, how to use it and go over some notes and limitations of the first version of this feature.
What is this feature?
DOLT_PATCH() returns SQL statements representing a patch between two revisions that can be applied on any working set that shares a common ancestor with both of those revisions. The CLI dolt diff -r SQL command is an equivalent of the DOLT_PATCH(). Here is a simple example of how you can the same get SQL patch statements with both options.
$ dolt diff -r SQL --data main dev products
INSERT INTO `products` (`id`,`name`,`version`) VALUES (1,'Dolt','0.75.6');
INSERT INTO `products` (`id`,`name`,`version`) VALUES (2,'DoltHub',NULL);The optional flags, --schema and --data, for the dolt diff -r SQL command can be applied on DOLT_PATCH() result as a filter.
$ dolt sql -q "SELECT statement FROM DOLT_PATCH('main','dev','products') WHERE diff_type = 'data';"
+----------------------------------------------------------------------------+
| statement |
+----------------------------------------------------------------------------+
| INSERT INTO `products` (`id`,`name`,`version`) VALUES (1,'Dolt','0.75.6'); |
| INSERT INTO `products` (`id`,`name`,`version`) VALUES (2,'DoltHub',NULL); |
+----------------------------------------------------------------------------+This feature is a great enhancement to our product, but it had not been implemented in SQL until now. As many customers requested the feature for their uses cases, we prioritized implementing it, since customer requests are a great form of feedback for our product. Prior to the release of dolt_patch(), the only way to get SQL patch statements was the CLI dolt diff -r SQL command. This did not accommodate the use case of generating SQL patches when you did not have access to the command line, like Hosted Dolt.
We initially implemented getting SQL patch statements in SQL contexts as stored procedures, but we switched it to a table function. We already have several table functions that provide information on diffs, and stored procedures generally modify state and return whether the procedure was executed successfully or not. Therefore, it makes more sense to make this feature as a table function rather than a stored procedure.
How to use...
Let's go over how this feature works. It is very simple as it uses the same logic of every table function we support. The result of the DOLT_PATCH() table function is equivalent to the result of the DOLT_DIFF() table function except that DOLT_PATCH() returns the result as SQL statements.
Here, we set up our data to see different ways to display patches by committing each change we make.
> CALL DOLT_CHECKOUT('-b', 'dev');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.04 sec)
> CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50));
> CREATE TABLE editing_tools (id INT PRIMARY KEY, tool_name VARCHAR(50), tested bool);
> CALL DOLT_COMMIT('-Am', 'create table');
+----------------------------------+
| hash |
+----------------------------------+
| 9re904lb1vbvl0bl3ab4f8va6ditsgum |
+----------------------------------+
1 row in set (0.02 sec)
> INSERT INTO products VALUES (1, 'Dolt'), (2, 'DoltHub');
Query OK, 2 rows affected (0.00 sec)
> CALL DOLT_COMMIT('-am', 'insert rows for Dolt and DoltHub');
+----------------------------------+
| hash |
+----------------------------------+
| 6vmlsom7rbkee1tctj1gglqj5f14gnm2 |
+----------------------------------+
1 row in set (0.02 sec)
> ALTER TABLE products ADD COLUMN version TEXT;
> CALL DOLT_COMMIT('-am', 'add column for version');
+----------------------------------+
| hash |
+----------------------------------+
| 5ba750qtdp5di5b5qc2hb6mb61nmn26i |
+----------------------------------+
1 row in set (0.02 sec)
> UPDATE products SET version = '0.75.6' WHERE name = 'Dolt';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Let's look at different combinations of revision inputs to display SQL patch statements.
- We get a patch for our changes in the current working set on
devbranch.
> SELECT * FROM DOLT_PATCH('HEAD', 'WORKING');
+-----------------+----------------------------------+----------------+------------+-----------+--------------------------------------------------------+
| statement_order | from_commit_hash | to_commit_hash | table_name | diff_type | statement |
+-----------------+----------------------------------+----------------+------------+-----------+--------------------------------------------------------+
| 1 | 5ba750qtdp5di5b5qc2hb6mb61nmn26i | WORKING | products | data | UPDATE `products` SET `version`='0.75.6' WHERE `id`=1; |
+-----------------+----------------------------------+----------------+------------+-----------+--------------------------------------------------------+
1 row in set (0.00 sec)- We get a patch for our changes between our current working set and commit before we altered the table schema. There is no data diff present in the result above even though there are some. We can see the reason for this in the warnings.
> SELECT * FROM DOLT_PATCH('6vmlsom7rbkee1tctj1gglqj5f14gnm2', 'WORKING');
+-----------------+----------------------------------+----------------+------------+-----------+--------------------------------------------+
| statement_order | from_commit_hash | to_commit_hash | table_name | diff_type | statement |
+-----------------+----------------------------------+----------------+------------+-----------+--------------------------------------------+
| 1 | 6vmlsom7rbkee1tctj1gglqj5f14gnm2 | WORKING | products | schema | ALTER TABLE `products` ADD `version` text; |
+-----------------+----------------------------------+----------------+------------+-----------+--------------------------------------------+
1 row in set (0.00 sec)
> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 1235 | Incompatible schema change, skipping data diff for table 'products' |
+---------+------+---------------------------------------------------------------------+Let's commit our changes in current working directory to see patches on different branches.
> CALL DOLT_COMMIT('-am', 'update Dolt version');
+----------------------------------+
| hash |
+----------------------------------+
| 9jf3nu9us8j6cmvbvse9bhp8ul6pqrb8 |
+----------------------------------+
1 row in set (0.02 sec)- We get a patch for all changes we made in
devbranch againstmainbranch, which has no changes to begin with.
> SELECT * FROM DOLT_PATCH('main', 'dev');
+-----------------+----------------------------------+----------------------------------+---------------+-----------+----------------------------------------------------------------------------+
| statement_order | from_commit_hash | to_commit_hash | table_name | diff_type | statement |
+-----------------+----------------------------------+----------------------------------+---------------+-----------+----------------------------------------------------------------------------+
| 1 | vl88s0k0esh244r13ibdpj8qenil0vnj | 9jf3nu9us8j6cmvbvse9bhp8ul6pqrb8 | editing_tools | schema | CREATE TABLE `editing_tools` ( |
| | | | | | `id` int NOT NULL, |
| | | | | | `tool_name` varchar(50), |
| | | | | | `tested` tinyint, |
| | | | | | PRIMARY KEY (`id`) |
| | | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; |
| 2 | vl88s0k0esh244r13ibdpj8qenil0vnj | 9jf3nu9us8j6cmvbvse9bhp8ul6pqrb8 | products | schema | CREATE TABLE `products` ( |
| | | | | | `id` int NOT NULL, |
| | | | | | `name` varchar(50), |
| | | | | | `version` text, |
| | | | | | PRIMARY KEY (`id`) |
| | | | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; |
| 3 | vl88s0k0esh244r13ibdpj8qenil0vnj | 9jf3nu9us8j6cmvbvse9bhp8ul6pqrb8 | products | data | INSERT INTO `products` (`id`,`name`,`version`) VALUES (1,'Dolt','0.75.6'); |
| 4 | vl88s0k0esh244r13ibdpj8qenil0vnj | 9jf3nu9us8j6cmvbvse9bhp8ul6pqrb8 | products | data | INSERT INTO `products` (`id`,`name`,`version`) VALUES (2,'DoltHub',NULL); |
+-----------------+----------------------------------+----------------------------------+---------------+-----------+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)- We get a schema-only patch to get to
mainbranch fromdevbranch.
> SELECT * FROM DOLT_PATCH('dev', 'main', 'products') WHERE diff_type = 'schema';
+-----------------+----------------------------------+----------------------------------+------------+-----------+------------------------+
| statement_order | from_commit_hash | to_commit_hash | table_name | diff_type | statement |
+-----------------+----------------------------------+----------------------------------+------------+-----------+------------------------+
| 1 | 9jf3nu9us8j6cmvbvse9bhp8ul6pqrb8 | vl88s0k0esh244r13ibdpj8qenil0vnj | products | schema | DROP TABLE `products`; |
+-----------------+----------------------------------+----------------------------------+------------+-----------+------------------------+
1 row in set (0.00 sec)Notes & Limitations
-
This feature is only available as a table function in SQL context. The CLI
dolt patchcommand will be supported in the future. The equivalent of this feature in CLI isdolt diff -r SQLcommand, which creates the same SQL patch statements asDOLT_PATCH()table function. The results of the last two queries from the example above can be achieved with this example in terminal shell.$ dolt diff -r SQL main dev CREATE TABLE `editing_tools` ( `id` int NOT NULL, `tool_name` varchar(50), `tested` tinyint, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; CREATE TABLE `products` ( `id` int NOT NULL, `name` varchar(50), `version` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; INSERT INTO `products` (`id`,`name`,`version`) VALUES (1,'Dolt','0.75.6'); INSERT INTO `products` (`id`,`name`,`version`) VALUES (2,'DoltHub',NULL); $ dolt diff -r SQL --schema dev main products DROP TABLE `products`; -
Patching tables with foreign keys may cause an issue if the patch is applied following the order of
statement_orderas diff results on multiple tables are returned sorted in order of table names. To avoid this issue, you can turn off foreign key checks with aSET foreign_key_checks=0;statement before applying the whole patch, then turn it back on withSET foreign_key_checks=1;. -
Note that using the SQL patch statements returned from
DOLT_PATCH()can cause conflicts when applying them on certain working sets.
Conclusion
Currently, we support getting SQL patch statements without the ability to apply the patch on a working set. Our next goal is to support applying the patch to any working set. Let us know if there is any way we can improve this feature. We will be happy to get it done as soon as we can. Implementation of the patch and apply feature is being tracked through this GitHub issue.
Check out the DOLT_PATCH() table function documentation for more detailed syntax support. We are happy to help you with any issues using this feature or our product on our Discord channel.