silverdaz
commented
6 years ago About harmonizing the data-in and data-out database schema, there is a better approach: Database Views.
It allows us to have whatever database backend we want, with whatever schema we fancy, and then we use 2 views, one for data-in, one for data-out.
That way, there is no need to harmonize the code to have given table names, column names, etc... In other word: Those views are an interface to the database.
Here is an example:
CREATE TABLE local_ega (
id SERIAL, PRIMARY KEY(id), UNIQUE (id),
status status,
-- Original file
elixir_id TEXT NOT NULL,
inbox_path TEXT NOT NULL,
-- EGA file ids
stable_id TEXT,
-- Vault information
vault_path TEXT,
vault_filesize INTEGER,
vault_display_name TEXT, -- no idea what that is
-- Crypt4GH header
header TEXT,
-- Useless checksums when using Crypt4GH
checksum VARCHAR(32),
checksum_type checksum_algorithm,
unencrypted_checksum VARCHAR(32),
unencrypted_checksum_type checksum_algorithm,
-- Status
status status NOT NULL,
-- Audit / Logs
created_by TEXT,
last_modified_by TEXT,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp(),
last_modified TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp()
);
-- WITH (OIDS=FALSE);
CREATE UNIQUE INDEX file_id_idx ON local_ega (id);
-- ################## VIEWS #########################
-- Used by data-in
CREATE VIEW files AS
SELECT id, elixir_id, inbox_path, status, vault_path, vault_filesize, stable_id, header, created_at, last_modified
FROM local_ega;
-- Used by data-out from EBI
CREATE VIEW dev_ega_file AS
SELECT id AS file_id,
stable_id AS file_name,
vault_path AS file_path,
display_file_name,
vault_filesize AS file_size,
checksum, checksum_type, unencrypted_checksum, unencrypted_checksum_type,
status AS file_status,
created_by, last_updated_by, header,
created_at AS created
last_modified AS last_updated
FROM local_ega;
blankdots
....so that ingestion and data-out play nicely with each other.
One database is enough for both.