Database deltas¶

Dsynq is able to version control database data as a set of deltas.

Database delta is the difference between two states of the data in a database. Dsynq stores deltas as a set of operations to perform in order to transition the database data from one state to another. In case of a relational database such as MySQL or PostgreSQL, it's sufficient to describe these operations as a sequence of SQL insert, update, or delete statements applying delta rows to the database tables.

Note

Dsynq database delta format is database engine agnostic.

This section shows how to create, apply, and rollback database deltas. As usual, start by checking out the data.

> dsynq checkout
receiving incremental file list
receiving incremental file list

Checkout done

Data for 2272ae9e2a3aff02208dcc549e9cfaf9849891e5 checked out
When finished, don't forget to check in the data to unlock it for others

Make a new delta for table item which you created in Tracking databases.

> dsynq delta new -D hello_dsynq_db --tables item
Warning: Database "hello_dsynq_db" does not exist in data/.databases/
Delta: 1528442433.821985
Making database delta stubs for table "item"...

> find data/.databases/
data/.databases/
data/.databases/hello_dsynq_db
data/.databases/hello_dsynq_db/delta
data/.databases/hello_dsynq_db/delta/1528442433.821985
data/.databases/hello_dsynq_db/delta/1528442433.821985/.meta
data/.databases/hello_dsynq_db/delta/1528442433.821985/.meta/.dsynqmeta.json
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.d.csv
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.i.csv
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.u.csv

dsynq delta new creates directories for the database (if needed), the delta, as well as stubs for the individual delta files. Dsynq uses timestamps as delta IDs which, besides providing practical uniqueness, make it easy to order the deltas chronologically.

Table item is empty. To populate it, fill in the insert delta stub item.i.csv with a couple of rows.

> echo $(find data/.databases/hello_dsynq_db/delta | grep item.i.csv | tail -n 1 | tr -d '\n')
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.i.csv

> cat > $(find data/.databases/hello_dsynq_db/delta | grep item.i.csv | tail -n 1 | tr -d '\n') <<EOF
id,code,name
1,10,One
2,20,Two
EOF

When finished, apply the database delta with dsynq delta apply.

> dsynq delta apply

Applying delta(s) to database #1...
Database: hello_dsynq_db

Transitioning to database delta @1528442433.821985...
Making the reverse database delta for @1528442433.821985...

Inserting...
Inserting into table "item"

Updating...
Updating table "item"

Deleting...
Deleting from table "item"

Database deltas applied successfully!

For each table in a database delta, Dsynq always applies operations in the following order.

insert
update
delete

Note

Dsynq tries to infer the order in which to process the tables from the database schema.

Output the results of dsynq delta apply.

> mysql -u root hello_dsynq_db -e "SELECT * FROM item;"
id   code    name
1    10      One
2    20      Two

Make another delta with several operations in one delta.

> dsynq delta new -D hello_dsynq_db --tables item
Delta: 1528442435.056242
Making database delta stubs for table "item"...

This time fill in the complete set of three stub files: insert, update, and delete.

Insert item Three.

> echo $(find data/.databases/hello_dsynq_db/delta | grep item.i.csv | tail -n 1 | tr -d '\n')
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.i.csv

> cat > $(find data/.databases/hello_dsynq_db/delta | grep item.i.csv | tail -n 1 | tr -d '\n') <<EOF
id,code,name
3,30,Three
EOF

Update item One.

> echo $(find data/.databases/hello_dsynq_db/delta | grep item.u.csv | tail -n 1 | tr -d '\n')
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.u.csv

> cat > $(find data/.databases/hello_dsynq_db/delta | grep item.u.csv | tail -n 1 | tr -d '\n') <<EOF
id,code
1,100
EOF

Delete item Two.

> echo $(find data/.databases/hello_dsynq_db/delta | grep item.d.csv | tail -n 1 | tr -d '\n')
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.d.csv

> cat > $(find data/.databases/hello_dsynq_db/delta | grep item.d.csv | tail -n 1 | tr -d '\n') <<EOF
id
2
EOF

Run dsynq delta apply again.

> dsynq delta apply

Applying delta(s) to database #1...
Database: hello_dsynq_db

Transitioning to database delta @1528442435.056242...
Making the reverse database delta for @1528442435.056242...

Inserting...
Inserting into table "item"

Updating...
Updating table "item"

Deleting...
Deleting from table "item"

Database deltas applied successfully!

> mysql -u root hello_dsynq_db -e "SELECT * FROM item;"
id   code    name
1    100     One
3    30      Three

Dsynq finds the new deltas missing in the database and applies only them.

When applying a database delta, Dsynq automatically creates a corresponding reverse delta. Dsynq needs the reverse deltas to be able to roll back the applied deltas.

You have two deltas applied, thus Dsynq has created two corresponding reverse deltas. Dsynq can transition the database to a state defined by a delta ID (the timestamp) or by a relative integer offset. The following command rolls back hello_dsynq_db by one delta.

> dsynq delta apply -D hello_dsynq_db -n -1

Applying delta(s) to database #1...
Database: hello_dsynq_db

Transitioning to database delta @1528442433.821985...

Inserting...
Inserting into table "item"

Updating...
Updating table "item"

Deleting...
Deleting from table "item"

Database deltas applied successfully!

> mysql -u root hello_dsynq_db -e "SELECT * FROM item;"
id   code    name
1    10      One
2    20      Two

By default, dsynq delta apply transitions all databases all the way forward.

> dsynq delta apply

Applying delta(s) to database #1...
Database: hello_dsynq_db

Transitioning to database delta @1528442435.056242...

Inserting...
Inserting into table "item"

Updating...
Updating table "item"

Deleting...
Deleting from table "item"

Database deltas applied successfully!

> mysql -u root hello_dsynq_db -e "SELECT * FROM item;"
id   code    name
1    100     One
3    30      Three

List the database deltas for hello_dsynq_db.

> find data/.databases/hello_dsynq_db/
data/.databases/hello_dsynq_db/
data/.databases/hello_dsynq_db/delta
data/.databases/hello_dsynq_db/delta/1528442433.821985
data/.databases/hello_dsynq_db/delta/1528442433.821985/.meta
data/.databases/hello_dsynq_db/delta/1528442433.821985/.meta/.dsynqmeta.json
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.d.csv
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.i.csv
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.u.csv
data/.databases/hello_dsynq_db/delta/1528442435.056242
data/.databases/hello_dsynq_db/delta/1528442435.056242/.meta
data/.databases/hello_dsynq_db/delta/1528442435.056242/.meta/.dsynqmeta.json
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.d.csv
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.i.csv
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.u.csv
data/.databases/hello_dsynq_db/delta.rev
data/.databases/hello_dsynq_db/delta.rev/1528442433.821985
data/.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.d.csv
data/.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.i.csv
data/.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.u.csv
data/.databases/hello_dsynq_db/delta.rev/1528442435.056242
data/.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.d.csv
data/.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.i.csv
data/.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.u.csv

It's a good idea to check the status of the database deltas before submitting the changes.

> dsynq delta status
Databases:
     Databases found: 1

     1.
     Database: hello_dsynq_db
     engine: mysql
     host: localhost
     port: 3306
     name: hello_dsynq_db
     user: root
     password:

     Database deltas found: 1528442433.821985,1528442435.056242

     Last applied database delta: 1528442435.056242

Finally, commit and check in.

> git add .dsynqconfig.yaml
warning: LF will be replaced by CRLF in .dsynqconfig.yaml.
The file will have its original line endings in your working directory.

> git commit -m "added hello_dsynq_db"
[master 6c1408b] added hello_dsynq_db
 1 file changed, 7 insertions(+), 1 deletion(-)

> dsynq checkin -m "initial hello_dsynq_db"
sending incremental file list
sending incremental file list
.databases/
.databases/hello_dsynq_db/
.databases/hello_dsynq_db/delta.rev/
.databases/hello_dsynq_db/delta.rev/1528442433.821985/
.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.d.csv.xz

             76 100%    0.00kB/s    0:00:00
             76 100%    0.00kB/s    0:00:00 (xfr#1, to-chk=17/24)
.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.i.csv.xz

             80 100%   78.12kB/s    0:00:00
             80 100%   78.12kB/s    0:00:00 (xfr#2, to-chk=16/24)
.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.u.csv.xz

             80 100%   78.12kB/s    0:00:00
             80 100%   78.12kB/s    0:00:00 (xfr#3, to-chk=15/24)
.databases/hello_dsynq_db/delta.rev/1528442435.056242/
.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.d.csv.xz

             72 100%   70.31kB/s    0:00:00
             72 100%   70.31kB/s    0:00:00 (xfr#4, to-chk=14/24)
.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.i.csv.xz

             88 100%   85.94kB/s    0:00:00
             88 100%   85.94kB/s    0:00:00 (xfr#5, to-chk=13/24)
.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.u.csv.xz

             80 100%   78.12kB/s    0:00:00
             80 100%   78.12kB/s    0:00:00 (xfr#6, to-chk=12/24)
.databases/hello_dsynq_db/delta/
.databases/hello_dsynq_db/delta/1528442433.821985/
.databases/hello_dsynq_db/delta/1528442433.821985/item.d.csv.xz

             68 100%   66.41kB/s    0:00:00
             68 100%   66.41kB/s    0:00:00 (xfr#7, to-chk=9/24)
.databases/hello_dsynq_db/delta/1528442433.821985/item.i.csv.xz

             96 100%   93.75kB/s    0:00:00
             96 100%   93.75kB/s    0:00:00 (xfr#8, to-chk=8/24)
.databases/hello_dsynq_db/delta/1528442433.821985/item.u.csv.xz

             80 100%   78.12kB/s    0:00:00
             80 100%   78.12kB/s    0:00:00 (xfr#9, to-chk=7/24)
.databases/hello_dsynq_db/delta/1528442433.821985/.meta/
.databases/hello_dsynq_db/delta/1528442433.821985/.meta/.dsynqmeta.json

             40 100%   39.06kB/s    0:00:00
             40 100%   39.06kB/s    0:00:00 (xfr#10, to-chk=5/24)
.databases/hello_dsynq_db/delta/1528442435.056242/
.databases/hello_dsynq_db/delta/1528442435.056242/item.d.csv.xz

             72 100%   70.31kB/s    0:00:00
             72 100%   70.31kB/s    0:00:00 (xfr#11, to-chk=4/24)
.databases/hello_dsynq_db/delta/1528442435.056242/item.i.csv.xz

             88 100%   85.94kB/s    0:00:00
             88 100%   85.94kB/s    0:00:00 (xfr#12, to-chk=3/24)
.databases/hello_dsynq_db/delta/1528442435.056242/item.u.csv.xz

             80 100%   78.12kB/s    0:00:00
             80 100%   78.12kB/s    0:00:00 (xfr#13, to-chk=2/24)
.databases/hello_dsynq_db/delta/1528442435.056242/.meta/
.databases/hello_dsynq_db/delta/1528442435.056242/.meta/.dsynqmeta.json

             40 100%   39.06kB/s    0:00:00
             40 100%   39.06kB/s    0:00:00 (xfr#14, to-chk=0/24)
Promoting data from 2272ae9e2a3aff02208dcc549e9cfaf9849891e5 to 6c1408ba8450e325f041b8fccb924d9e6da92a19

Warning: Checking in the data for the preceding commit

Compressing database delta(s)...

Compressing database delta(s) in database #1...
Database: hello_dsynq_db
delta/1528442433.821985
delta/1528442435.056242
delta.rev/1528442433.821985
delta.rev/1528442435.056242
Database delta(s) compressed

Data checked in successfully!

The resulting outline of data/.databases/ is as follows.

> find data/.databases/
data/.databases/
data/.databases/hello_dsynq_db
data/.databases/hello_dsynq_db/delta
data/.databases/hello_dsynq_db/delta/1528442433.821985
data/.databases/hello_dsynq_db/delta/1528442433.821985/.meta
data/.databases/hello_dsynq_db/delta/1528442433.821985/.meta/.dsynqmeta.json
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.d.csv.xz
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.i.csv.xz
data/.databases/hello_dsynq_db/delta/1528442433.821985/item.u.csv.xz
data/.databases/hello_dsynq_db/delta/1528442435.056242
data/.databases/hello_dsynq_db/delta/1528442435.056242/.meta
data/.databases/hello_dsynq_db/delta/1528442435.056242/.meta/.dsynqmeta.json
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.d.csv.xz
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.i.csv.xz
data/.databases/hello_dsynq_db/delta/1528442435.056242/item.u.csv.xz
data/.databases/hello_dsynq_db/delta.rev
data/.databases/hello_dsynq_db/delta.rev/1528442433.821985
data/.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.d.csv.xz
data/.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.i.csv.xz
data/.databases/hello_dsynq_db/delta.rev/1528442433.821985/item.u.csv.xz
data/.databases/hello_dsynq_db/delta.rev/1528442435.056242
data/.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.d.csv.xz
data/.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.i.csv.xz
data/.databases/hello_dsynq_db/delta.rev/1528442435.056242/item.u.csv.xz

Note that Dsynq compresses the delta files for the regular as well as the reverse deltas on dsynq checkin.