Creating a rich data structure in a traditional relational database such as MySQL can often be difficult, and there are much better ways of going about it. When working with a path based data structure with a hierarchy I like to create a flat-file based system that uses a data-serialization format such as JSON to store information about a specific file, directory or an entire repository.
This way you can use current available tools to navigate and manipulate the structure easily, and you can read, edit and understand the structure easily. XML is good for this too - it's slightly more verbose than JSON but easy to read and good for messaging and other XML-based systems too.
A quick example. If we have a repository that has a directory and three files. Looking at it front on it will look like this:
/repo
/folder
code.php
file.txt
image.jpg
We can have a metadata folder, which contains our JSON files, hidden from the OS, at the root of each directory, which describe that directory's contents. This is how traditional versioning systems work, except they use a custom language instead of JSON.
/repo
*/.folderdata*
/code
*/.folderdata*
code.php
file.txt
image.jpg
Each .folderdata folder could contain it's own structure that we can use to organize the folder's data properly. Each .folderdata folder could then be compressed to save disk space. If we look at the .folderdata folder inside the /code directory:
*/.folderdata*
/revisions
code.php.r1
code.php.r2
code.php.r3
folderstructure.json
filerevisions.json
The folder structure defines the structure of our folder, where the files and folders are in relation to one another etc. This could look something like this:
{
'.': 'code',
'..': 'repo',
'code.php': {
'author_id': 11543,
'author_name': 'Jamie Rumbelow',
'file_hash': 'a26hb3vpq22'
'access': 'public'
}
}
This allows us to associate metadata about that file, check for authenticity and integrity, keep persistent data, specify file attributes and do much more. We can then keep information about specific revisions in the filerevisions.json file:
{
'code.php': [
1: {
'commit': 'ah32mncnj654oidfd',
'commit_author_id': 11543,
'commit_author_name': 'Jamie Rumbelow',
'commit_message': 'Made some changes to code.php',
'additions': 2,
'subtractions': 4
},
2: {
'commit': 'ljk4klj34khn5nkk5',
'commit_author_id': 18676,
'commit_author_name': 'Jo Johnson',
'commit_message': 'Fixed Jamie\'s bad code!',
'additions': 2,
'subtractions': 0
},
3: {
'commit': '77sdnjhhh4ife943r',
'commit_author_id': 11543,
'commit_author_name': 'Jamie Rumbelow',
'commit_message': 'Whoah, showstopper found and fixed',
'additions': 8,
'subtractions': 5
},
]
}
This is a basic outline plan for a file versioning system - I like this idea and how it works, and I've used JSON in the past to great effect with rich datastructures like this. This sort of data just isn't suitable for a relational database such as MySQL - as you get more revisions and more files the database will grow bigger and bigger, this way you can stagger the revisions across multiple files, keep backups of everything, make sure you have persistent data across interfaces and platforms etc.
Hope this has given you some insight, and hopefully it'll provide some food for thought for the community too!