View Source

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 = '' ......
~~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);

Column / Label	reference_id	original_id	context
Attribute	PRIMARY KEY, Partition Key
Data Type	text	text	text
Description	Die Referenz die benutzt wird um auf die Binaries zuzugreifen	Verweis auf die Id in der Originals-Tabelle	generiert aus dataset-id und revision-id

Originals (originals)

use binaries;
CREATE TABLE IF NOT EXISTS originals (original_id text, last_modified text, e_tag text, mime text, created timestamp, last_checked timestamp, checksum text, size bigint, status text, PRIMARY KEY(original_id));

Column / Label	original_id	last_modified	e_tag	mime	created	last_checked	checksum	size	status
Attribute	PRIMARY KEY, Partition Key
Data Type	text	text	text	text	timestamp	timestamp	text	bigint	text
Description		Last Modified Datum aus dem Response Header des Servers	E-Tag aus dem Response Header des Servers	Mime-Type des Binaries	Zeitstempel der den Zeitpunkt des Speicherns der Daten angibt.	Zeitstempel der angibt wann überprüft wurde ob die Daten auf dem Server sich geändert haben	Aus den Daten errechnete Prüfsumme	Größe des Binaries	Status des Download-Prozesses ( PROCESSING \| OK \| ERROR \| RETRY )

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);

Column / Label	url	original_id
Attribute	PRIMARY KEY, Partition Key
Data Type	text	text
Description	die URL von der ein Binary geladen wurde	Verweis auf die Id in der Originals-Tabelle

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));

Column / Label	original_id	part	data
Attribute	PRIMARY KEY, Partition Key	PRIMARY KEY, Partition Key
Data Type	text	int	blob
Description	Verweis auf die Id in der Originals-Tabelle	Chunk des Binaries	Content des Binary-Chunks

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));

Column / Label	original_id	part	data
Attribute	PRIMARY KEY, Partition Key	PRIMARY KEY, Partition Key
Data Type	text	int	blob
Description	Verweis auf die Id in der Originals-Tabelle	Chunk des Binaries	Content des Binary-Chunks

Log (logs)

use binaries;
CREATE TABLE IF NOT EXISTS logs (reference_id text, timestamp timestamp, message text, PRIMARY KEY(reference_id, timestamp));

Column / Label	reference_id	timestamp	message
Attribute	PRIMARY KEY, Partition Key	PRIMARY KEY, Partition Key
Data Type	text	timestamp	text
Description	Verweis auf die Id in der References-Tabelle	Zeitstempel der Log-Meldung	Log-Meldung

Scaled images 800 (scaled_800x600)

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

Column / Label	original_id	data
Attribute	PRIMARY KEY, Partition Key
Data Type	text	blob
Description	Verweis auf die Id in der Originals-Tabelle	das skalierte Bild

Scaled images 140 (scaled_140x105)

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

Column / Label	original_id	data
Attribute	PRIMARY KEY, Partition Key
Data Type	text	blob
Description	Verweis auf die Id in der Originals-Tabelle	das skalierte Bild

Scaled images 116 (scaled_116x87)

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

Column / Label	original_id	data
Attribute	PRIMARY KEY, Partition Key
Data Type	text	blob
Description	Verweis auf die Id in der Originals-Tabelle	das skalierte Bild

Scaled images 440 (scaled_440x330)

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

Column / Label	original_id	data
Attribute	PRIMARY KEY, Partition Key
Data Type	text	blob
Description	Verweis auf die Id in der Originals-Tabelle	das skalierte Bild

Job Queue Entries (job_queue_entries)

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

Column / Label	reference_id	created	data
Attribute	PRIMARY KEY, Partition Key
Data Type	text	timestamp	blob
Description	Die Referenz die benutzt wird um auf die Binaries zuzugreifen	Zeitstempel der den Zeitpunkt des Speicherns der Daten angibt.	Der Eintrag in der Job-Queue, Java-Object, Serialisiert

/binary/AB123CD