From b15fa552e288bec5bbc2b07a3e11bab9235a1e7c Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Mon, 10 Sep 2018 19:46:02 -0700 Subject: use JSONB over JSON Sort of an impulsive "why not" decision. From earlier tests, expect this to consume a few % more disk (but not index)... adds a lot more query flexibility if we want that in the future. Seems worth the price. --- rust/migrations/2018-05-12-001226_init/up.sql | 26 +++++++++++++------------- rust/src/database_schema.rs | 26 +++++++++++++------------- 2 files changed, 26 insertions(+), 26 deletions(-) diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql index 9e0f471e..2134b455 100644 --- a/rust/migrations/2018-05-12-001226_init/up.sql +++ b/rust/migrations/2018-05-12-001226_init/up.sql @@ -22,7 +22,7 @@ CREATE TABLE editgroup ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), editor_id UUID REFERENCES editor(id) NOT NULL, created TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL, - extra_json JSON, + extra_json JSONB, description TEXT ); @@ -47,7 +47,7 @@ CREATE TABLE abstracts ( -------------------- Creators ----------------------------------------------- CREATE TABLE creator_rev ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), - extra_json JSON, + extra_json JSONB, display_name TEXT NOT NULL, given_name TEXT, @@ -81,7 +81,7 @@ CREATE TABLE creator_edit ( rev_id UUID REFERENCES creator_rev(id), redirect_id UUID REFERENCES creator_ident(id), prev_rev UUID REFERENCES creator_rev(id), - extra_json JSON + extra_json JSONB ); CREATE INDEX creator_edit_idx ON creator_edit(editgroup_id); @@ -89,7 +89,7 @@ CREATE INDEX creator_edit_idx ON creator_edit(editgroup_id); -------------------- Containers -------------------------------------------- CREATE TABLE container_rev ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), - extra_json JSON, + extra_json JSONB, name TEXT NOT NULL, publisher TEXT, @@ -119,7 +119,7 @@ CREATE TABLE container_edit ( rev_id UUID REFERENCES container_rev(id), redirect_id UUID REFERENCES container_ident(id), prev_rev UUID REFERENCES container_rev(id), - extra_json JSON + extra_json JSONB ); CREATE INDEX container_edit_idx ON container_edit(editgroup_id); @@ -127,7 +127,7 @@ CREATE INDEX container_edit_idx ON container_edit(editgroup_id); -------------------- Files ------------------------------------------------- CREATE TABLE file_rev ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), - extra_json JSON, + extra_json JSONB, size BIGINT, sha1 CHAR(40), @@ -166,7 +166,7 @@ CREATE TABLE file_edit ( rev_id UUID REFERENCES file_rev(id), redirect_id UUID REFERENCES file_ident(id), prev_rev UUID REFERENCES file_rev(id), - extra_json JSON + extra_json JSONB ); CREATE INDEX file_edit_idx ON file_edit(editgroup_id); @@ -174,7 +174,7 @@ CREATE INDEX file_edit_idx ON file_edit(editgroup_id); -------------------- Release ----------------------------------------------- CREATE TABLE release_rev ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), - extra_json JSON, + extra_json JSONB, work_ident_id UUID NOT NULL, -- FOREIGN KEY; see ALRTER below container_ident_id UUID REFERENCES container_ident(id), @@ -233,7 +233,7 @@ CREATE TABLE release_edit ( rev_id UUID REFERENCES release_rev(id), redirect_id UUID REFERENCES release_ident(id), prev_rev UUID REFERENCES release_rev(id), - extra_json JSON + extra_json JSONB ); CREATE INDEX release_edit_idx ON release_edit(editgroup_id); @@ -241,7 +241,7 @@ CREATE INDEX release_edit_idx ON release_edit(editgroup_id); -------------------- Works -------------------------------------------------- CREATE TABLE work_rev ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), - extra_json JSON + extra_json JSONB ); CREATE TABLE work_ident ( @@ -261,7 +261,7 @@ CREATE TABLE work_edit ( rev_id UUID REFERENCES work_rev(id), redirect_id UUID REFERENCES work_ident(id), prev_rev UUID REFERENCES work_rev(id), - extra_json JSON + extra_json JSONB ); CREATE INDEX work_edit_idx ON work_edit(editgroup_id); @@ -279,7 +279,7 @@ CREATE TABLE release_contrib ( raw_name TEXT, role TEXT, -- TODO: enum? index_val BIGINT, - extra_json JSON + extra_json JSONB ); CREATE INDEX release_contrib_rev_idx ON release_contrib(release_rev); @@ -291,7 +291,7 @@ CREATE TABLE release_ref ( target_release_ident_id UUID REFERENCES release_ident(id), -- or work? index_val BIGINT, key TEXT, - extra_json JSON, -- title, year, container_title, locator (aka, page), oci_id + extra_json JSONB, -- title, year, container_title, locator (aka, page), oci_id container_title TEXT, year BIGINT, title TEXT, diff --git a/rust/src/database_schema.rs b/rust/src/database_schema.rs index a6605d81..bc3f6c3a 100644 --- a/rust/src/database_schema.rs +++ b/rust/src/database_schema.rs @@ -22,7 +22,7 @@ table! { rev_id -> Nullable, redirect_id -> Nullable, prev_rev -> Nullable, - extra_json -> Nullable, + extra_json -> Nullable, } } @@ -38,7 +38,7 @@ table! { table! { container_rev (id) { id -> Uuid, - extra_json -> Nullable, + extra_json -> Nullable, name -> Text, publisher -> Nullable, issnl -> Nullable, @@ -57,7 +57,7 @@ table! { rev_id -> Nullable, redirect_id -> Nullable, prev_rev -> Nullable, - extra_json -> Nullable, + extra_json -> Nullable, } } @@ -73,7 +73,7 @@ table! { table! { creator_rev (id) { id -> Uuid, - extra_json -> Nullable, + extra_json -> Nullable, display_name -> Text, given_name -> Nullable, surname -> Nullable, @@ -87,7 +87,7 @@ table! { id -> Uuid, editor_id -> Uuid, created -> Timestamp, - extra_json -> Nullable, + extra_json -> Nullable, description -> Nullable, } } @@ -111,7 +111,7 @@ table! { rev_id -> Nullable, redirect_id -> Nullable, prev_rev -> Nullable, - extra_json -> Nullable, + extra_json -> Nullable, } } @@ -134,7 +134,7 @@ table! { table! { file_rev (id) { id -> Uuid, - extra_json -> Nullable, + extra_json -> Nullable, size -> Nullable, sha1 -> Nullable, sha256 -> Nullable, @@ -160,7 +160,7 @@ table! { raw_name -> Nullable, role -> Nullable, index_val -> Nullable, - extra_json -> Nullable, + extra_json -> Nullable, } } @@ -173,7 +173,7 @@ table! { rev_id -> Nullable, redirect_id -> Nullable, prev_rev -> Nullable, - extra_json -> Nullable, + extra_json -> Nullable, } } @@ -193,7 +193,7 @@ table! { target_release_ident_id -> Nullable, index_val -> Nullable, key -> Nullable, - extra_json -> Nullable, + extra_json -> Nullable, container_title -> Nullable, year -> Nullable, title -> Nullable, @@ -204,7 +204,7 @@ table! { table! { release_rev (id) { id -> Uuid, - extra_json -> Nullable, + extra_json -> Nullable, work_ident_id -> Uuid, container_ident_id -> Nullable, title -> Text, @@ -244,7 +244,7 @@ table! { rev_id -> Nullable, redirect_id -> Nullable, prev_rev -> Nullable, - extra_json -> Nullable, + extra_json -> Nullable, } } @@ -260,7 +260,7 @@ table! { table! { work_rev (id) { id -> Uuid, - extra_json -> Nullable, + extra_json -> Nullable, } } -- cgit v1.2.3