Database versioning system

Methods, systems, and apparatus, including computer programs encoded on a computer storage medium, for identifying one or more rows in a relational database that have each been modified since a first point in time, wherein each of the rows is associated with a respective table in the relational database; for each of the identified rows, updating a respective file in an external representation of the table associated with the row to reflect the modification to the row wherein the respective file is not stored in the database; and creating a first version of the relational database in a version control system using the external representation including one or more of the updated files, wherein the first version of the database comprises one or more files in the version control system.

BACKGROUND

This specification relates to digital information retrieval, and particularly to providing versioning for databases.

SUMMARY

In general, one aspect of the subject matter described in this specification can be embodied in methods that include the actions of identifying one or more rows in a relational database that have each been modified since a first point in time, wherein each of the rows is associated with a respective table in the relational database; for each of the identified rows, updating a respective file in an external representation of the table associated with the row to reflect the modification to the row wherein the respective file is not stored in the database; and creating a first version of the relational database in a version control system using the external representation including one or more of the updated files, wherein the first version of the database comprises one or more files in the version control system. Other embodiments of this aspect include corresponding systems, apparatus, and computer programs.

These and other aspects can optionally include one or more of the following features. A particular row of the identified rows is modified if the particular row has been created, deleted, or changed in the database since the first point in time. Each respective file stores a plurality of rows of a table in the database. Identifying the plurality of rows is performed by a database management system. Creating the new version is performed by a version control system. One or more differences are determined between one or more files of the first version of the relational database in the version control system and one or more files of a different second version of the relational database in the version control system; and one or more database commands are executed based on the determined differences so that contents of the relational database are the same as the second version. A change to the relational database is detected and a second version of the database is created in the version control system based on the detected change. One or more database commands are executed based on one or more determined differences between the first version of the relational database and the second version of the relational database to restore the relational database to the first version of the database. One or more database commands are executed based on one or more determined differences between the first version of the relational database and the second version of the relational database to restore the relational database to the second version of the database.

Particular embodiments of the subject matter described in this specification can be implemented to realize one or more of the following advantages. The version control system and database management system do not need to be modified in order to be used for database versioning. Database versions can be stored locally or in a central repository. Likewise, the database itself can be stored locally or centrally. A database can be dynamically switched between versions using operations that minimize the amount of changes to the database in order to change it to a different version. Various implementations support undo and redo operations which rely on temporary versions of the database created in the version control system.

DETAILED DESCRIPTION

A Database Management System (DBMS) is software that controls the creation, maintenance, and the use of one or more underlying databases. Examples of databases include row or column-oriented relational databases and object-oriented databases. Other types of databases are possible. A relational database organizes information in tables, rows and columns. An example is a list of customers, which would be represented as a table, which contains one row for each individual customer, and each row has columns for name, address, phone number, and so on. Another example is the set of tax parcels in a city—in such a case each row may also contain a geometric description of the parcel, in addition to columns for valuation, owner, address, and so on. The DBMS provides functionality for inserting, deleting, updating and selecting rows from tables in a database. This can be done, for example, using an application programming interface provided by the DBMS or a query language like Structured Query Language (SQL). Other types of query languages are possible, however.

A Version Control System (VCS) is software that tracks and maintains control over changes to a set of one or more electronic files. A set of files in the VCS is referred to as a version or “branch”. The VCS allows users or software processes to create, modify, or delete files in a given version, or create new version based on changes to files in an existing version. Different versions of a set of files can exist simultaneously. Each version is associated with a time stamp which indicates when the version was created or last modified in the VCS. Versions can be compared, restored, and in some cases, merged. In various implementations, a database can be exported from the DBMS to an external representation comprising a set of one or more electronic files, and the external representation can then be created as a new version in the VCS. In further implementations, each VCS version is a version of an entire database. Switching the a database's contents from one version to another in the DBMS is performed efficiently, however, as is described further below.

FIG. 1is an illustration of an example database versioning system100for maintaining multiple versions of the data stored in a database management system (DBMS) using a version control system (VCS). By way of illustration, the DBMS can be a relational DBMS and the VCS can be a distributed VCS. Other types of database management systems and version control systems are possible. The system includes one or more clients (e.g., clients102,104and106) which can optionally communicate with one or more servers (e.g., server108) through a network110such as an intranet or the Internet, for example. Clients and servers are data processing apparatus such as, for example, personal computers and server computers, respectively.

In some implementations, a client includes software applications or modules that implement a version layer (version layer103), a DBMS (e.g., DBMS102b) and a VCS (e.g., VCS102c) which execute on the client (e.g., client102). Clients can include local storage102dsuch as a hard disk, Flash memory, or other persistent storage, for storing information used by the DBMS102band the VCS102c. In other implementations, clients can utilize non-local storage accessible through the network110by means of a distributed network file system or other type of network-accessible storage system. Clients optionally include a software application102athat is configured to utilize the DBMS102a. In some implementations, the software application102ais a Computer Aided Design (CAD) program. Alternatively, the clients do not include software applications or modules that implement the DBMS and the VCS. Instead, the server108includes these software applications or modules (DBMS108aand VCS108b) and the clients issue requests to the DBMS108aand VCS108bon behalf of the application102a.

The version layer103uses the VCS to store, retrieve and modify versions of databases in the DBMS. In some implementations, the VCS102cstores database versions in local client storage102d. This allows differences between versions to be quickly computed on the client102because the information needed to perform the difference is available locally. In other implementations, the VCS102cor108bstores database versions centrally in a central storage system112which is accessible through the server108. The central storage system112can implement a distributed network file system on top of persistent storage, for example. Using built-in remote distribution functionality of the VCS, which replicates database versions to the central storage system112, users can choose to synchronize the contents of their VCS data stored in local storage102dwith the contents of the central storage112. This operation can be performed, for example, by issuing remote synchronization commands to the VCS.

FIG. 2is an example external representation of a database200in a file system202and in a VCS204. The external representation of a database allows for efficient updating of the contents of a live database in the DBMS to represent any given version. The efficiency stems from mapping database versions to the versions in the VCS, serializing row data to files in a specific format, and exploiting the intrinsic efficiency of various VCS operations to “morph” the contents of the database from one version of the data to another.

In order to enable versioning for a database, the contents of the database are inserted into the VCS. In various implementations, this is accomplished by the version layer103first creating an external representation of the database in a file system (e.g., local storage102dor central storage112) that is outside of the control of the DBMS by commanding the DBMS to export data of the database. In some implementations, each table in the database is represented by a directory in the file system representation202. For example, a database200named “X” comprises three tables: the Table1(200a), Table2(200b), and Table3(200c). In some implementations the name of the directory matches the name of the table. The external file system202representation of the database200includes a directory for Table1(directory202a), Table2(directory202b) and Table3(directory202c). Each table directory includes one or more files which store the serialized rows of the respective table. For example, the Table1directory (202a) contains files202d, the Table2directory (202b) contains files202e, and the Table3directory (202c) contains files202f.

In various implementations, each file contains a table's rows with a fixed range of database row identifiers, and the file's name reflects that range. For example a file named “0” contains rows 0 to 255, a file named “1” contains rows 256 to 511, and so on. The number of rows per file can be such that the resulting file size is approximately optimal for the VCS. Inside each file, table rows can be serialized as text with column values listed in a way that is simple to convert back to a binary representation used by the DBMS. In some implementations, the column names do not need to be explicitly stored for each row, since they do not vary and knowing their order is sufficient to know the column corresponding to each value in the comma separated list.

In some implementations, the version layer103serializes the rows to JavaScript Object Notation (JSON) format, one row per line in the file. (Other formats are possible.) The following shows four example table rows as they would be represented in such a file using JSON format (note that new lines are marked with [NL], in order to distinguish them from line wrapping):

After serializing the table rows, a file-based (“external”) representation202of the contents of the database200exists. At this point the version layer103can command the VCS (e.g., using a “commit” operation) to initialize a new version in the VCS using the set of files in the file-based representation202. By way of illustration, VCS contains the following versions of database200: version 1.0 (204a), version 2 (204d) and version 3 (204e). Version1.0(204a) contains sub-version 1.1 (204b) and sub-version 1.2 (204c). In some implementations, the VCS maintains each database version as a set of binary files204on a file system (e.g., local storage102dor central storage112) which the VCS manages independently of the application102alogic. For example, subversion 1.2 (204c) can contain the contents of the external representation202.

Once a new version of a database is active in the DBMS, the user or the software application (e.g., application102a) is free to query and make changes to rows in the database using SQL commands or by using the software application in ways that result in updates to the database. To keep track of exactly which rows change in a database, SQL triggers can be used so insert a row into so called “journal” tables whenever a row is inserted, updated or deleted in a database tables. Triggers are commands executed automatically whenever certain operations or events happen in a given table or in the database in general. Journal tables contain the identifiers of rows that become “dirty” due to modification. For simplicity, a separate journal table can be maintained for each database table that is versioned. The journal tables are not versioned; they are automatically filled up with information about which rows change.

At a point in time when the application or the user decides to synchronize the data in the DBMS with a VCS version, the rows identified in each journal table are serialized by the version layer103to their respective external row text files corresponding to the tables to which the rows belong. This ensures that only rows that have been modified are serialized. After this partial serialization, the external representation matches the database contents, and a “commit” VCS operation is executed by the version layer103to add the changed text files into the version control content store. The journal tables can then be emptied since the version in the VCS now exactly matches the current state of the database. In some implementations, the VCS “commit” operations record checkpoints at which the DBMS and the VCS contents match, and can then be used for switching between versions.

A database in the DBMS can be switched from one version to another by the version layer103. Similar to the “journaling” scheme used for reflecting changes from the DBMS to the VCS, the version layer103identifies exactly what rows changed in the VCS representation of the database version from the last “commit” operation. To get the list of changes, the version layer103executes a “diff” operation, which is a common way to list changes made to text files on a per-line basis. The diff operation can be natively implemented by the VCS.

The output of the diff operation lists all text lines in row text files which changed from one commit (A) to another (B), between two versions, or between the last commit and the current state of the serialized row files. To switch the DBMS from containing version/commit A to version/commit B, the version layer103executes a text diff from A to B, using the VCS. The result of a diff might look as follows:

This diff shows an update made to row number946of the BUILDINGS table, and an insertion done to the ROADS table. The “external” files affected are “BUILDINGS/3” and “ROADS/3”. Note that the diff output includes a ‘+’ or ‘−’ character next to rows which are added and removed respectively. If a row was changed, its “from” version appears with a ‘−’ and the “to” version appears with a ‘+’.

Due to the intentional organization of row-per-line in the row text files, each line (or each two lines in case of updates) in the diff text corresponds to an entire row of a database table. The version layer103infers target table names from the diff text (lines prefixed by ‘+++’ and ‘−−−’) and interprets lines representing rows (prefixed by single ‘+’ and ‘−’) as SQL insert, update or delete commands and applies those to the DBMS. Once each line in the diff text stream is applied as command to the database in the DBMS, the database contains exactly the rows of version B. Moreover, the number of commands the version layer103has to execute is minimal. Subsequently we also execute a VCS command to switch the external representation files to version B as well, to be consistent with the database state—this is an operation that can be performed automatically by the VCS.

A complication may arise when merging two versions (for example when adding work from a work-in-progress version into the master version) in the case when both versions contain independent modifications to the same row. In this case the version layer103can use the diff3 (which compares 3 files and determines differences between them) output to first insert textual “conflict markers” into the external text representation (usually sequences of ‘>’ and ‘<’ characters). The markers show all rows for which there is a merge conflict, in which case the application logic decides which version to use for the merged row.

FIG. 3is a diagram of example version control system branches reflecting changes to a database. The VCS contains timelines300-304each representing a specific version of the database. The Master version of the database is represented by timeline300and was created in the VCS at time1. A second version of Master database, Version B, was created at time2and is represented by timeline302. Version B of the database might hold proposed changes to the master version without yet modifying the master version, for example. A key insight is that the VCS contains all history and versions of a database while the DBMS contains a version of the database at one point in time (so the DBMS contents can represent the database at any one of the numbered points inFIG. 3). As described above, the version layer103can switch the contents of a database in the DBMS from representing one of the numbered points to another (e.g., between the Master version at time1and Version B at time2, or vice versa) by using the VCS “diff” functionality. In some implementations, an undo and redo functionality can be implemented by the version layer103using its ability to switch between versions of a database.

By way of illustration, suppose a user wants to do an edit while they are working on Version B of a database in the DBMS. At this point the version layer103will silently create another, temporary version in the VCS (Version B Undo History as indicated by timeline304), which will contain the undo history of the edits. At the moment of creation at time3, the Version B Undo History contains the same exact data as Version B branch at time2. When the user edits the database in the DBMS, the change is first done directly to the database by the DBMS application logic. At this time, the DBMS contents are “ahead” of the contents of the VCS. This is when the version layer103reads the journal table(s) to see what row identifiers the user modified, and then writes the row's corresponding text files into the VCS. The edits are then committed to the VCS at time4in Version B Undo History. An “undo” command by the user at that point will jump the contents of the database back to time3. A “redo” command at time3will jump the contents of the database to time4again. At some point, the user will exit their application (e.g., application102a) or perform a watershed operation that has to flush the undo history) at which point the version layer103merges the Version B Undo History back into its parent branch Version B at time7. Note that the contents of the DBMS do not change between times6and7.

FIG. 4is a flowchart of example technique for creating a version of a database in an external version control system that can be implemented by software executing on one or more data processing apparatus. One or more of rows in a relational database that have each been modified since a first point in time are identified, wherein each of the rows is associated with a respective table in the relational database (step402). For each of the identified rows, a respective file is updated in an external representation of the respective table associated with the row to reflect the modification to the row wherein the respective file is not stored in the database (step404). The a version of the database is created in a version control system using the updated files, wherein the first version of the database comprises one or more files in the version control system.