Page History

Queries:

PUT /binaries (url, context, mime)

• SELECT original_id FROM urls WHERE url = ''

DELETE /binaries/context/{context}

• SELECT reference_id FROM binary_refs WHERE context = ''
• DELETE FROM log WHERE reference_id = ''
• DELETE FROM binary_refs WHERE context = ''

GET /binaries/{context}

• SELECT original_id FROM binary_refs WHERE context = ''
• SELECT status FROM originals WHERE original_id = ''

DELETE /binaries/reference/{reference_id}

• DELETE FROM binary_refs WHERE reference_id = ''
• DELETE FROM log WHERE reference_id = ''

GET /binary/AB123CD

• SELECT original_id FROM binary_refs WHERE reference_id = ''
• SELECT * FROM originals_data WHERE original_id = ''
• SELECT mime FROM originals WHERE original_id = ''

GET /binary/full/!800,600/default.jpg

• SELECT * FROM scaled_800x600 WHERE original_id = '' ......  Handled by IIIF Image Server
• SELECT mime FROM originals WHERE original_id = ''  Brauchen wir nicht, da hier immer ein image/jpeg zurückkommt.

GET /binary/logs/{context}

• SELECT reference_id FROM binary_refs WHERE context = ''
• SELECT message FROM logs WHERE reference_id = ''

Löschen nicht mehr benötigter Originals:

(asynchron, daher nicht zeitkritisch)

• SELECT original_id FROM originals
• SELECT reference_id FROM binary_refs WHERE original_id = ''
• DELETE FROM url WHERE original_id = ''
• DELETE FROM originals WHERE original_id = ''
• DELETE FROM originals_data WHERE original_id = ''
• DELETE FROM scaled_... WHERE original_id = ''

Job Queue Entries

• INSERT
• DELETE
• SELECT ALL

Binary References (binary_refs)

use binaries;
CREATE TABLE IF NOT EXISTS binary_refs (reference_id text, original_id text, context text, PRIMARY KEY(reference_id));
CREATE MATERIALIZED VIEW binaryrefs_context AS SELECT context FROM binary_refs WHERE reference_id IS NOT NULL AND context IS NOT NULL PRIMARY KEY(context, reference_id);
CREATE MATERIALIZED VIEW binaryrefs_original AS SELECT original_id FROM binary_refs WHERE reference_id IS NOT NULL AND original_id IS NOT NULL PRIMARY KEY(original_id, reference_id);

Originals (originals)

CREATE TABLE binaries.originals (original_id text PRIMARY KEY,checksum text,created timestamp,e_tag text,height int,last_checked timestamp,last_modified text,mime text,size bigint,status text,width int);

...

...

URLs (urls)

use binaries;
CREATE TABLE IF NOT EXISTS urls (url text, original_id text, PRIMARY KEY(url));
CREATE MATERIALIZED VIEW urls_original AS SELECT original_id FROM urls WHERE url IS NOT NULL AND original_id IS NOT NULL PRIMARY KEY(original_id, url);

Originals Data (originals_data)

use binaries;
CREATE TABLE IF NOT EXISTS originals_data (original_id text, part int, data blob, PRIMARY KEY(original_id, part));

Temporary Originals Data (originals_data_temp) for images that get saved in file-system

use binaries;
CREATE TABLE IF NOT EXISTS originals_data_temp (original_id text, part int, data blob, PRIMARY KEY(original_id, part));

...

Scaled images 800 (scaled_800x600) (obsolete)

use binaries;
CREATE TABLE IF NOT EXISTS scaled_800x600 (original_id text, data blob, PRIMARY KEY(original_id));

...

Scaled images 140 (scaled_140x105) (obsolete)

use binaries;
CREATE TABLE IF NOT EXISTS scaled_140x105 (original_id text, data blob, PRIMARY KEY(original_id));

Scaled images 116 (scaled_116x87) (obsolete)

use binaries;
CREATE TABLE IF NOT EXISTS scaled_116x87 (original_id text, data blob, PRIMARY KEY(original_id));

Scaled images 440 (scaled_440x330) (obsolete)

use binaries;
CREATE TABLE IF NOT EXISTS scaled_440x330 (original_id text, data blob, PRIMARY KEY(original_id));

Job Queue Entries (job_queue_entries)

use binaries;
CREATE TABLE job_queue_entries (reference_id text, created timestamp, data blob, PRIMARY KEY (reference_id)); 

/binary/AB123CD