39 lines
1.4 KiB
SQL
39 lines
1.4 KiB
SQL
-- Create a local cache of indices.
|
|
|
|
CREATE TABLE indices (
|
|
index_short_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
index_sha256 TEXT NOT NULL
|
|
);
|
|
CREATE UNIQUE INDEX indices_index_sha256 ON indices(index_sha256);
|
|
|
|
CREATE TABLE bloblogs (
|
|
bloblog_short_id INTEGER PRIMARY KEY NOT NULL,
|
|
bloblog_sha256 TEXT NOT NULL
|
|
);
|
|
CREATE UNIQUE INDEX bloblogs_bloblog_sha256 ON bloblogs(bloblog_sha256);
|
|
|
|
-- Track the relationship between indices and bloblogs
|
|
CREATE TABLE indices_bloblogs (
|
|
index_short_id INTEGER NOT NULL REFERENCES indices(index_short_id),
|
|
bloblog_short_id INTEGER NOT NULL REFERENCES bloblogs(bloblog_short_id),
|
|
forgotten_bytes INTEGER NOT NULL,
|
|
PRIMARY KEY (index_short_id, bloblog_short_id)
|
|
);
|
|
|
|
CREATE TABLE blobs (
|
|
chunk_id TEXT NOT NULL,
|
|
bloblog_short_id INTEGER NOT NULL,
|
|
index_short_id INTEGER NOT NULL,
|
|
offset INTEGER NOT NULL,
|
|
size INTEGER NOT NULL,
|
|
PRIMARY KEY (chunk_id, bloblog_short_id, index_short_id),
|
|
FOREIGN KEY (index_short_id, bloblog_short_id) REFERENCES indices_bloblogs(index_short_id, bloblog_short_id)
|
|
);
|
|
CREATE INDEX blobs_bloblog_short_id ON blobs(bloblog_short_id);
|
|
CREATE INDEX blobs_index_short_id ON blobs(index_short_id);
|
|
|
|
CREATE TABLE indices_supersede (
|
|
superseded_sha256 TEXT NOT NULL,
|
|
successor_sha256 TEXT NOT NULL REFERENCES indices(index_sha256),
|
|
PRIMARY KEY (superseded_sha256, successor_sha256)
|
|
); |