BEGIN EXCLUSIVE;
CREATE TABLE branch_epochs
	(
	hash not null unique,         -- hash of remaining fields separated by ":"
	branch not null unique,       -- joins with revision_certs.value
	epoch not null                -- random hex-encoded id
	);
CREATE TABLE db_vars
        (
        domain not null,      -- scope of application of a var
        name not null,        -- var key
        value not null,       -- var value
        unique(domain, name)
        );
CREATE TABLE file_deltas
	(	
	id not null,      -- strong hash of file contents
	base not null,    -- joins with files.id or file_deltas.id
	delta not null,   -- rdiff to construct current from base
	unique(id, base)
	);
CREATE TABLE files
	(
	id primary key,   -- strong hash of file contents
	data not null     -- compressed, encoded contents of a file
	);
INSERT INTO files VALUES('a5de1b65f54b120f5986028af3f83632f4c65470','H4sIAAAAAAAA/8tMU6jML1VITsxTKEpNTFEoycgs1lEoz0gsUYhPLEqNB8um5GfmpdtzAQAG
ubkCLAAAAA==
');
INSERT INTO files VALUES('c0eff70406a3aeed4a50e0772c0ee1f8919d9988','H4sIAAAAAAAA/ytOTS5KLVHITS0uTkxPVeQCANb0C7UQAAAA
');
CREATE TABLE manifest_certs
	(
	hash not null unique,   -- hash of remaining fields separated by ":"
	id not null,            -- joins with manifests.id or manifest_deltas.id
	name not null,          -- opaque string chosen by user
	value not null,         -- opaque blob
	keypair not null,       -- joins with public_keys.id
	signature not null,     -- RSA/SHA1 signature of "[name@id:val]"
	unique(name, id, value, keypair, signature)
	);
CREATE TABLE manifest_deltas
	(
	id not null,         -- strong hash of all the entries in a manifest
	base not null,       -- joins with either manifest.id or manifest_deltas.id
	delta not null,      -- rdiff to construct current from base
	unique(id, base)
	);
CREATE TABLE manifests
	(
	id primary key,      -- strong hash of all the entries in a manifest
	data not null        -- compressed, encoded contents of a manifest
	);
INSERT INTO manifests VALUES('27b0525617c99793c45b7a7f135676c09113528d','H4sIAAAAAAAA/yXLwQnDMAwF0HunyAaVbUmWxlHsLxooFFLvT0J7fryQibKrpPBeKqW4KVWL
bGlNW00eKtxp2xa+ax7n87NeOPN44zEImZ2YNFoAk0MI1Hu9ASXNi093s//9lQuN3ZOKbwAA
AA==
');
CREATE TABLE public_keys
	(
	hash not null unique,   -- hash of remaining fields separated by ":"
	id primary key,         -- key identifier chosen by user
	keydata not null        -- RSA public params
	);
INSERT INTO public_keys VALUES('de84b575d5e47254393eba49dce9dc4db98ed42d','njs@pobox.com','MIGdMA0GCSqGSIb3DQEBAQUAA4GLADCBhwKBgQC54vVjrrqYoTfPTgWm6JpuL+kOERcN2OSc
BsWq6cb4Wm3nlymwVqJJywq6cbfygUYwmqyiRLPxRosfLGu228AhEzaM4JbAH1pgg7CwvvVd
fHRXNAXEMgO89gBjkkecxLi4U/T67DrLjkRPAilCgWLZNv8YeOG9XAPegWyr7hNA9wIBEQ==');
CREATE TABLE revision_ancestry
	(
	parent not null,     -- joins with revisions.id
	child not null,      -- joins with revisions.id
	unique(parent, child)
	);
INSERT INTO revision_ancestry VALUES('','707047e1627d3e9da02be34f8742e653f131c3dc');
CREATE TABLE revision_certs
	(
	hash not null unique,   -- hash of remaining fields separated by ":"
	id not null,            -- joins with revisions.id
	name not null,          -- opaque string chosen by user
	value not null,         -- opaque blob
	keypair not null,       -- joins with public_keys.id
	signature not null,     -- RSA/SHA1 signature of "[name@id:val]"
	unique(name, id, value, keypair, signature)
	);
INSERT INTO revision_certs VALUES('e2f9d168dc3e776868a9268acce560035c798f1b','707047e1627d3e9da02be34f8742e653f131c3dc','branch','dGVzdGJyYW5jaA==
','njs@pobox.com','FCD3vGbF9/RGh/Vogn0rYimY4k0XrC5bJaSD4B6uhPZsoRNHlS+TbTbafa5MEQegmaMFq2p3
CXkj6Ne0szcdGGFDpM7X5Q2zFdQPJQVfCSj633ChxzF6m3cTN5lcVEbXxRhsg2kmTeu+DG75
Fqa0drbFnS21axHXnhHGFMQ2IBQ=
');
INSERT INTO revision_certs VALUES('7ae21e6a353ee5e26752a879d9d87366177fff18','707047e1627d3e9da02be34f8742e653f131c3dc','date','MjAwNi0wMS0xMFQwMTozOToxNQ==
','njs@pobox.com','giFYo0udYUMdT8EFSJP31lql7KmkRbXWACZUrHVPkP9ZuR30pFTKBk90xMb3J6Dm4wbSg2v5
Wtwf8LOSg29D14A2g1zwg7u8GyNxG7W/9x1aquNXAlL5bUvSCHbOYbfpoiqV3GmNLCNmheRy
r4P56tK4uP0YxmzANtWv1Li7YxM=
');
INSERT INTO revision_certs VALUES('cc339984b4981499f5e3bf0571d6ecae323ced0c','707047e1627d3e9da02be34f8742e653f131c3dc','author','bmpzQHBvYm94LmNvbQ==
','njs@pobox.com','N9KyN8My8hKfayzwirsp99DIHw+r84K5a/I6r9mC+K4dMacySOtPkMGT9upUu+TvmR9FAVSB
WKiLClzUEcNA9mQMWr2D8Gawihux1iggzTcbx8v82NWAf4bOKSsWYDBCkVlnYN/PKB3uWbjx
GW3RXH6ntXYs2wI2Ne0pXEBjWNY=
');
INSERT INTO revision_certs VALUES('646ee0d4edd4f346855a996a259a88249805235d','707047e1627d3e9da02be34f8742e653f131c3dc','changelog','YmxhaGJsYWg=
','njs@pobox.com','jNp5S9PGMlrYQcIN71jJ/4m+cpQ3VKoFEd+TcuFZTg3VM5hTWVAciVAtdOsNgGRDoy/yNPHS
yfrYYyv9Wg+UIsg6E1lhxiQqdL7Qf/Cx40VcS7W3HcdXqMygjd1EU1tcqqgfO+psqYOhBTgd
HlQw6t4Kzqkwu8v0ZuLiAuws3Wo=
');
CREATE TABLE revisions
	(
	id primary key,      -- SHA1(text of revision)
	data not null        -- compressed, encoded contents of a revision
	);
INSERT INTO revisions VALUES('707047e1627d3e9da02be34f8742e653f131c3dc','H4sIAAAAAAAA/22MbWrDMBBE//sUIhfo6mO10lmMMWtplwgSqziivX6dUigt+TfzZni7fK53
3pvKY5jZ0QboMFoqOVP2JeBGTGo9RooFsj2TS3WZpn6r6yEf7dH6bublu/+KzgPXumq7ibmM
E9V2vPVxleOJLv/XH/jOo1xf/o0e/f7UGmNGNzNjFbtFVAybdaCYUwSXWL0mH73TUCIGguWP
9LWrgKgSBIjsWaQGRhAgcucgVlO2ueac0jJ9AVMt2GYtAQAA
');
CREATE INDEX revision_ancestry__child ON revision_ancestry (child);
CREATE INDEX revision_certs__id ON revision_certs (id);
CREATE INDEX revision_certs__name_value ON revision_certs (name, value);
COMMIT;