PRAGMA foreign_keys = OFF; DROP TABLE IF EXISTS FileRecords; DROP TABLE IF EXISTS FileLeaves; DROP TABLE IF EXISTS FileNodes; DROP TABLE IF EXISTS Versions; DROP TABLE IF EXISTS ACLLeaves; DROP TABLE IF EXISTS ACLNodes; DROP TABLE IF EXISTS BCLeaves; DROP TABLE IF EXISTS BCNodes; DROP TABLE IF EXISTS CFLeaves; DROP TABLE IF EXISTS CFNodes; CREATE TABLE IF NOT EXISTS FileRecords ( Idx INTEGER PRIMARY KEY, Ver INTEGER, Ctr INTEGER, Cert BLOB, HMAC BLOB, ACL_logleaves INTEGER ); CREATE TABLE IF NOT EXISTS FileLeaves ( LeafIdx INTEGER PRIMARY KEY, Idx INTEGER, NextIdx INTEGER, Val BLOB ); CREATE TABLE IF NOT EXISTS FileNodes ( NodeIdx INTEGER PRIMARY KEY, Val BLOB ); CREATE TABLE IF NOT EXISTS Versions ( FileIdx INTEGER, Version INTEGER, KF BLOB, encrypted_secret BLOB, Cert BLOB, HMAC BLOB, Buildcode_logleaves INTEGER, Composefile_logleaves INTEGER, PRIMARY KEY (FileIdx, Version), FOREIGN KEY (FileIdx) REFERENCES FileRecords(Idx) ); CREATE TABLE IF NOT EXISTS ACLLeaves ( FileIdx INTEGER, LeafIdx INTEGER, Idx INTEGER, NextIdx INTEGER, Val BLOB, PRIMARY KEY (FileIdx, LeafIdx), FOREIGN KEY (FileIdx) REFERENCES FileRecords(Idx) ); CREATE TABLE IF NOT EXISTS ACLNodes ( FileIdx INTEGER, NodeIdx INTEGER, Val BLOB, PRIMARY KEY (FileIdx, NodeIdx), FOREIGN KEY (FileIdx) REFERENCES FileRecords(Idx) ); CREATE TABLE IF NOT EXISTS BCLeaves ( FileIdx INTEGER, Version INTEGER, LeafIdx INTEGER, Idx INTEGER, NextIdx INTEGER, Val BLOB, PRIMARY KEY (FileIdx, Version, LeafIdx), FOREIGN KEY (FileIdx) REFERENCES FileRecords(Idx), FOREIGN KEY (Version) REFERENCES Versions(Version) ); CREATE TABLE IF NOT EXISTS BCNodes ( FileIdx INTEGER, Version INTEGER, Val BLOB, NodeIdx INTEGER, PRIMARY KEY (FileIdx, Version, NodeIdx), FOREIGN KEY (FileIdx) REFERENCES FileRecords(Idx), FOREIGN KEY (Version) REFERENCES Versions(Version) ); CREATE TABLE IF NOT EXISTS CFLeaves ( FileIdx INTEGER, Version INTEGER, LeafIdx INTEGER, Idx INTEGER, NextIdx INTEGER, Val BLOB, PRIMARY KEY (FileIdx, Version, LeafIdx), FOREIGN KEY (FileIdx) REFERENCES FileRecords(Idx), FOREIGN KEY (Version) REFERENCES Versions(Version) ); CREATE TABLE IF NOT EXISTS CFNodes ( FileIdx INTEGER, Version INTEGER, Val BLOB, NodeIdx INTEGER, PRIMARY KEY (FileIdx, Version, NodeIdx), FOREIGN KEY (FileIdx) REFERENCES FileRecords(Idx), FOREIGN KEY (Version) REFERENCES Versions(Version) ); CREATE INDEX Idx1 ON Versions(FileIdx, Version); CREATE INDEX Idx2 ON FileLeaves(Idx, NextIdx); CREATE INDEX Idx3 ON FileLeaves(NextIdx); CREATE INDEX Idx4 ON ACLLeaves(FileIdx, LeafIdx); CREATE INDEX Idx5 ON ACLNodes(FileIdx, NodeIdx);