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 = ''

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 = ''

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 timestamp, e_tag text, mime text, created timestamp, checksum text, status text, PRIMARY KEY(original_id));

Column / Label	original_id	last_modified	e_tag	mime	created	checksum	status
Attribute	PRIMARY KEY, Partition Key
Data Type	text	timestamp	text	text	timestamp	text	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.	Aus den Daten errechnete Prüfsumme	Status des Download-Prozesses ( 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(originals_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 (log)

use binaries;
CREATE TABLE IF NOT EXISTS log (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	Content des Binary-Chunks

Scaled images 800 (scaled_800x600)

use binaries;
CREATE TABLE IF NOT EXISTS scaled_800x600 (original_id text, data blob, PRIMARY KEY(originals_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(originals_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(originals_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(originals_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

/binary/AB123CD