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

Column / Label	original_id	last_modified	e_tag	mime	created	last_checked	checksum	status
Attribute	PRIMARY KEY, Partition Key
Data Type	text	text	text	text	timestamp	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.	Zeitstempel der angibt wann überprüft wurde ob die Daten auf dem Server sich geändert haben	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(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 (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(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

/binary/AB123CD