I need to explore git hooks to see if I can automate the process, since I don't feel like manually generating the dump file. But I think you are talking about something else. Can you elaborate?
O_o
I use "Mercurial" so can't help with the hooks, but I am indeed talking about generating text files for the repository to difference from the normal binary database on commit.
The approach isn't much of a stretch; I'm just using some simple information along with the binary database to generate different files which thanks to the repository mechanisms track different tables/schema separately without much hoop-jumpery nonsense.
I don't have a huge amount of time available right now, but you may feel free to ask for more information if you don't follow anything from my strategy.
I have a directory in each project that holds the database header (".json") files and any build instruction set (".sql.txt"*) files, if any exists, called "database" or similar.
The header file contains options used by a script, which triggers with commit actions, that changes what information is included or excluded in the build instruction set.
Code:
(layout)
/ cboard
/ stable
/ cboard.db
/ database
001_members.json
002_posts.json
/ development
/ cboard.db
/ database
001_members.json
002_posts.json
/ features
/ reputation
/ cboard.db
/ database
001_members.json
002_posts.json
003_reputation.json
Code:
(stable -> 001_members.json)
// the members table stores members which i know is a dumb comment but whatever this is just an example
{
"table": "members",
"collate": "none",
"command": "CREATE TABLE members(id INTEGER PRIMARY KEY, name TEXT);"
}
Code:
(development -> 001_members.json)
// the members table stores members which i know is a dumb comment but whatever this is just an example
// revision: added posts counter
{
"table": "members",
"collate": "none"
}
The header itself is part of the repository so notes and revisions can be included in the markup and/or commit messages for the relevant files. The bits and bobs have defaults, but the example is illustrative. The "table" is used so that the files and names can follow different standards for naming. The "collate" is used for optionally sorting "INSERT" lines for the sake of focusing the visibility of changes between versions. (The point is to focus on small changes in large sets without a lot of overhead to be seen in the difference files.) The "command" is used as a cheap firewall (If the "command" doesn't exist, the value is ignored allowing free changes to the table without warning during development.) to prevent accidental changes between stable versions.
The script executed on commit action generates the build instruction set corresponding to the database ("cboard.db") for each header.
Code:
(layout)
/ cboard
/ stable
/ database
001_members.sql.txt
Code:
(stable -> generated 001_members.sql.txt)
CREATE TABLE members(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "members" VALUES(1,'Mario F.');
INSERT INTO "members" VALUES(2,'phantomotap');
The script also glues the varies build instruction sets together according to those same header files.
Code:
sql3revision -b ./database | sqlite3 "cboard.db"
Soma
*) I'm used to ".sql" extension for binary instruction files.