Branching and Three-Way Merge in a Browser-Based Data Editor

January 6, 2026 Software Engineering Game Development php mysql version-control tools archaeology

Here is the entire branch creation path of a data editor I prototyped on my own time in 2016, minus the locking around it:

$sSql = "SELECT MAX(nBranchId) + 1 AS nMaxBranchId FROM " . ReservedTableName('tblBranches');
...
$sSql = "INSERT INTO " . ReservedTableName('tblBranches') . " VALUE ($nBranchId, '$sBranchName')";

One row, two columns, an id and a name. No data is copied, nothing is snapshotted, and no table is touched other than the list of branches. A branch in this tool is a coordinate that rows can be filed under, and rows only start existing at that coordinate when somebody edits one.

What DataTool Is

It came off a web host backup, out of home/matt/public_html/DataTool. It was never in Subversion and it isn't on GitHub, so there's no real history for it. What history exists was reconstructed by grouping the files by last-modified date and committing each date in order, which gives six commits between 26 February and 11 March 2016 and hides every edit before the last one to each file.

It was a personal prototype, and it later became the basis for Primate, a data authoring tool adopted by a game team of about 60 developers.

Twenty-six files and 2,331 lines of PHP. It manages tables, rows, fields, and lookups through a browser, aimed at the kind of structured design data a game accumulates: units, weapons, tiers, costs, the tables a designer edits all day and a build then compiles into the game.

The version control is the part it exists for. add-branch.php, merge-row.php, merge-conflict.php, resolve-conflict.php, and undo-row.php are half the reason the other files are shaped the way they are.

Four Places a Row Can Live

Every data table carries seven bookkeeping columns, all prefixed with an underscore: _nRowId, _nUserId, _nBranchId, _nVersion, _nBaseVersion, _nBlameUserId, and _nTimestamp. A row is identified by _nRowId, and the same _nRowId can have many physical rows behind it.

Two of those columns are the model. _nUserId says whose edit this is, _nBranchId says which branch it's on, and zero on either axis means shared. That gives four scopes, and every read resolves all four:

SELECT _nRowId, $sFieldName FROM
(
    SELECT ... WHERE _nUserId = $nUserId AND _nBranchId = $nBranchId ...  -- tblPrivateBranch
    UNION DISTINCT
    SELECT ... WHERE _nUserId = 0        AND _nBranchId = $nBranchId ...  -- tblSharedBranch
    UNION DISTINCT
    SELECT ... WHERE _nUserId = $nUserId AND _nBranchId = 0          ...  -- tblPrivateTrunk
    UNION DISTINCT
    SELECT ... WHERE _nUserId = 0        AND _nBranchId = 0          ...  -- tblTrunk
) tblCombined
GROUP BY _nRowId

The subquery aliases name the four scopes and they are the design written down. My uncommitted edits on my branch, everyone's committed edits on my branch, my uncommitted edits on trunk, and trunk itself.

Two axes rather than one is the unusual part. Git has branches and nothing else, so an uncommitted change is invisible to the model until it becomes a commit on some branch. Here, being uncommitted is a coordinate of its own, and it composes with the branch axis. A designer can have work in progress on trunk and work in progress on a feature branch at the same time, and both are rows in the same table, distinguishable by two integers.

Setup inserts user 0 named Trunk and branch 0 named Trunk, so the shared state is a real row on both axes rather than a null.

The Schema Is the Metadata

There are exactly two reserved tables in the whole system, DT_tblUsers and DT_tblBranches. There is no table describing tables, no table describing fields, and no table describing relationships.

Introspection is SHOW FULL COLUMNS, and everything the tool needs to know is encoded into the schema it's reading:

Table role comes from the name prefix: DT_data_ for a designer's table, DT_junction_ for a many-to-many join.
Bookkeeping columns start with an underscore, and the field loader drops them unless debug is on, so a designer sees their own columns and none of the machinery.
A single-value reference is a column named DT_lookup_<field>, with the MySQL column comment holding Table.Field as the target.
A multi-value reference is DT_multi_lookup_<field>, with the comment holding the four name parts that identify both ends of the junction table.

The type system is six entries wide: Integer, Float, Boolean, String, Lookup, and Lookup Multiple, mapped onto INT, FLOAT, BOOLEAN, and VARCHAR(255), and mapped back by matching on the exact MySQL type string. Adding a seventh type means editing four switch statements, and the round trip breaks the moment a column is VARCHAR(128).

That's the cost, and the benefit is that the database is the only source of truth. There is no schema file to fall out of sync with the tables, and no migration step, because changing the schema is the migration. add-field.php runs an ALTER TABLE and the tool immediately knows about the new column, because the next page load asks the database what the columns are.

The Merge Is Per Field

_nBaseVersion is what makes the merge a real one. When a row is edited it records the version it was edited against, so at merge time three states exist: the base, my version, and whatever the shared branch has moved to since.

merge-row.php walks the columns and decides one at a time:

if($rowLatest[$field['Field']] != $arrBaseData[$field['Field']])
{
    if(isset($arrData[$field['Field']]))
    {
        //merge conflict
        header("Location: merge-conflict.php?sTableName=$sTableDisplayName&nRowId=$nRowId");
        exit();
    }
    ...
}

A field only I touched takes my value. A field only they touched takes theirs. A field we both touched is a conflict, and the whole merge stops and hands the row to a human. Two designers editing different columns of the same unit never collide, which is the case that happens constantly and that a row-level or file-level lock would have blocked for no reason.

On success the merged row is inserted with _nUserId = 0 and _nBlameUserId set to whoever merged it, then the private rows are deleted. Committing is a write plus a delete, and the merged row remembers who did it.

The Conflict Screen Is Four Rows

merge-conflict.php renders a table with one column per field and four rows. The base, the other person's version labelled with their actual name pulled through _nBlameUserId, mine, and the merged result.

Cells that differ across all three get a CSS error class in every row, so the conflicting columns are visible down the whole table. In the merge row, only those cells become editable inputs. Every other cell renders as read-only text with a hidden input carrying the already-agreed value.

The person resolving is given exactly the cells that need a decision and can't accidentally change anything else. Conflict granularity should match edit granularity. A merge that operates per field and then asks a human to resolve a whole record has thrown away the work it just did.

What I'd Change

The merge drops the other side's change. In the branch where only they edited a field, the code writes $row[$field['Field']], which is my row. But I didn't change that field, so my row still holds the base value, and the merge writes the base over their edit. It should be writing $rowLatest. The conflict case is correct and the clean case silently reverts, which is the worse way round, because nothing tells anyone it happened.

The table lock never takes. Three files run this before merging:

LOCK TABLES $sTableName, tblLockSelect1, tblLockSelect2, tblLockSelect3, tblLockSelect4,
    tblPrivateBranch, tblSharedBranch, tblPrivateTrunk, tblTrunk, tblCombined WRITE

Everything after $sTableName is a subquery alias, not a table. MySQL does require every alias to be locked while a session holds table locks, which is why those aliases have names like tblLockSelect1 in the first place, but the form is LOCK TABLES t AS alias WRITE. Naming a table that doesn't exist fails the whole statement, so no lock is acquired, the merge runs unprotected, and the UNLOCK TABLES at the end has nothing to release. The author knew the rule and wrote it in a way that turns it off.

Latest wins is not guaranteed. The idiom throughout is ORDER BY _nVersion DESC in a subquery and GROUP BY _nRowId outside it, relying on the group picking the first row of the ordered input. MySQL has never promised that, and the outer group over the four-way union has no defined precedence at all, so which of the four scopes wins is left to the optimiser. The fix is a join against MAX(_nVersion) per group.

Base values are escaped before being compared. $arrBaseData is filled with mysql_real_escape_string applied to the base row, and then compared against a raw value from the other row. Any string containing a quote compares unequal to itself and reads as a change that didn't happen.

There is no authentication. Identity is intval($_COOKIE['DT_nUserId']), looked up in a table with two columns and no password field. Anyone who can reach the page can be anyone. For a prototype on my own web host that was a decision rather than an oversight, and it means the blame data is a record of who clicked, not a claim about who they were.

What Transfers

Make a branch a coordinate, not a copy. The reason branching is free here is that no branch owns any storage. Rows carry the branch they belong to and reads union the scopes back together, so an empty branch costs one row and a branch someone has used costs exactly the rows they changed. Anything that copies a dataset to branch it will get slower with the dataset. This doesn't.

Resolve conflicts at the same granularity they were detected. Detecting per field and resolving per record is the common version of this and it wastes the analysis. The four-row table with only the conflicting cells editable is a small amount of HTML on top of a merge that already knew which columns were in dispute.

A schema can carry its own metadata, up to a point. Column name prefixes and MySQL column comments got this tool a type system and a relational model with no metadata tables and no possibility of drift. It also capped the type system at six entries and made VARCHAR(255) the only string a column is allowed to be. That is the right call for a tool whose schema changes daily and the wrong one for a tool that has to describe types it can't spell in DDL.

Record who, not just what. _nBlameUserId survives the merge that erases _nUserId, and it's the reason the conflict screen can say Jim's instead of Theirs. A conflict between two named people is a conversation. A conflict between two anonymous versions is a guess.

A later tool of mine, SchemaTools, opens its README with "Trying to rebuild my life's greatest work from memory, RIP Data Explorer." Data Explorer was the generation after Primate, which makes DataTool the start of that line, and it's the only version I still have the code for.