summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2018-12-19 17:07:31 -0800
committerBryan Newbold <bnewbold@robocracy.org>2018-12-24 16:27:01 -0800
commitd8f290285832402ba7220440f29e5bbff0aecdca (patch)
treefc1cf84b25c3c89b5e7f88198a4ca73d9e6414e2
parentbb7604b4db67f50cb0c860d548c780ab218175a8 (diff)
downloadfatcat-d8f290285832402ba7220440f29e5bbff0aecdca.tar.gz
fatcat-d8f290285832402ba7220440f29e5bbff0aecdca.zip
fileset and webcapture SQL schemas
-rw-r--r--rust/migrations/2018-05-12-001226_init/up.sql116
1 files changed, 115 insertions, 1 deletions
diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql
index c842295e..57209d2c 100644
--- a/rust/migrations/2018-05-12-001226_init/up.sql
+++ b/rust/migrations/2018-05-12-001226_init/up.sql
@@ -185,6 +185,109 @@ CREATE TABLE file_edit (
UNIQUE (editgroup_id, ident_id)
);
+-------------------- Fileset -----------------------------------------------
+CREATE TABLE fileset_rev (
+ id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ extra_json JSONB
+);
+
+CREATE TABLE fileset_rev_url (
+ id BIGSERIAL PRIMARY KEY,
+ fileset_rev UUID REFERENCES fileset_rev(id) NOT NULL,
+ rel TEXT NOT NULL, -- TODO: enum? web, webarchive, repo, etc TODO: default web?
+ url TEXT NOT NULL
+);
+
+CREATE INDEX fileset_rev_url_rev_idx ON fileset_rev_url(fileset_rev);
+
+CREATE TABLE fileset_rev_manifest (
+ id BIGSERIAL PRIMARY KEY,
+ fileset_rev UUID REFERENCES fileset_rev(id) NOT NULL,
+ surt TEXT NOT NULL,
+ url TEXT NOT NULL,
+ timestamp BIGINT NOT NULL,
+ status_code BIGINT NOT NULL,
+ mimetype TEXT,
+ sha1 TEXT CHECK(octet_length(sha1) = 40) NOT NULL,
+ sha256 TEXT CHECK(octet_length(sha256) = 64),
+ extra_json JSONB
+);
+
+CREATE INDEX fileset_rev_manifest_rev_idx ON fileset_rev_manifest(fileset_rev);
+
+CREATE TABLE fileset_ident (
+ id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ is_live BOOL NOT NULL DEFAULT false,
+ rev_id UUID REFERENCES fileset_rev(id),
+ redirect_id UUID REFERENCES fileset_ident(id)
+);
+
+CREATE INDEX fileset_ident_rev_idx ON fileset_ident(rev_id);
+
+CREATE TABLE fileset_edit (
+ id BIGSERIAL PRIMARY KEY,
+ editgroup_id UUID REFERENCES editgroup(id) NOT NULL,
+ updated TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
+ ident_id UUID REFERENCES fileset_ident(id) NOT NULL,
+ rev_id UUID REFERENCES fileset_rev(id),
+ redirect_id UUID REFERENCES fileset_ident(id),
+ prev_rev UUID REFERENCES fileset_rev(id),
+ extra_json JSONB,
+ UNIQUE (editgroup_id, ident_id)
+);
+
+-------------------- Webcapture -----------------------------------------------
+CREATE TABLE webcapture_rev (
+ id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ extra_json JSONB,
+
+ original_url TEXT NOT NULL,
+ timestamp TIMESTAMP WITH TIME ZONE NOT NULL
+);
+
+CREATE TABLE webcapture_rev_archive_url (
+ id BIGSERIAL PRIMARY KEY,
+ webcapture_rev UUID REFERENCES webcapture_rev(id) NOT NULL,
+ rel TEXT NOT NULL, -- TODO: enum? web, webarchive, repo, etc TODO: default web?
+ url TEXT NOT NULL
+);
+
+CREATE INDEX webcapture_rev_url_archive_rev_idx ON webcapture_rev_archive_url(webcapture_rev);
+
+CREATE TABLE webcapture_rev_cdx (
+ id BIGSERIAL PRIMARY KEY,
+ webcapture_rev UUID REFERENCES webcapture_rev(id) NOT NULL,
+ path_name TEXT NOT NULL,
+ size_bytes BIGINT NOT NULL,
+ md5 TEXT CHECK(octet_length(md5) = 32),
+ sha1 TEXT CHECK(octet_length(sha1) = 40),
+ sha256 TEXT CHECK(octet_length(sha256) = 64)
+ -- could extend with: language (detection), simhash, redirect
+);
+
+CREATE INDEX webcapture_rev_cdx_rev_idx ON webcapture_rev_cdx(webcapture_rev);
+
+CREATE TABLE webcapture_ident (
+ id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ is_live BOOL NOT NULL DEFAULT false,
+ rev_id UUID REFERENCES webcapture_rev(id),
+ redirect_id UUID REFERENCES webcapture_ident(id)
+);
+
+CREATE INDEX webcapture_ident_rev_idx ON webcapture_ident(rev_id);
+
+CREATE TABLE webcapture_edit (
+ id BIGSERIAL PRIMARY KEY,
+ editgroup_id UUID REFERENCES editgroup(id) NOT NULL,
+ updated TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
+ ident_id UUID REFERENCES webcapture_ident(id) NOT NULL,
+ rev_id UUID REFERENCES webcapture_rev(id),
+ redirect_id UUID REFERENCES webcapture_ident(id),
+ prev_rev UUID REFERENCES webcapture_rev(id),
+ extra_json JSONB,
+ UNIQUE (editgroup_id, ident_id)
+);
+
-------------------- Release -----------------------------------------------
CREATE TABLE release_rev (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
@@ -323,8 +426,19 @@ CREATE TABLE file_release (
target_release_ident_id UUID REFERENCES release_ident(id) NOT NULL,
PRIMARY KEY (file_rev, target_release_ident_id)
);
-
CREATE INDEX file_release_target_release_idx ON file_release(target_release_ident_id);
+CREATE TABLE fileset_release (
+ fileset_rev UUID REFERENCES fileset_rev(id) NOT NULL,
+ target_release_ident_id UUID REFERENCES release_ident(id) NOT NULL,
+ PRIMARY KEY (fileset_rev, target_release_ident_id)
+);
+CREATE INDEX fileset_release_target_release_idx ON fileset_release(target_release_ident_id);
+CREATE TABLE webcapture_release (
+ webcapture_rev UUID REFERENCES webcapture_rev(id) NOT NULL,
+ target_release_ident_id UUID REFERENCES release_ident(id) NOT NULL,
+ PRIMARY KEY (webcapture_rev, target_release_ident_id)
+);
+CREATE INDEX webcapture_release_target_release_idx ON webcapture_release(target_release_ident_id);
---------------------------------------------------------------------------
---------------------------------------------------------------------------