Binärdaten in PostgreSQL Datenbank schreiben. Beste Methode?

[Micha122]

Hallo,
ich habe hier mal 3 Möglichkeiten, Binärdaten in eine PostgreSQL Datenbank zu schreiben vorbereitet.
Wenn überhaupt, welche der 3 Methoden würdet ihr benutzen?
Ich frage deswegen, weil ich keinen "Festplatten- Füller" programmieren möchte.

1. Möglichkeit: Ganz normal Database blob

Code: Alles auswählen

EnableExplicit
Define Mem_Size, bytes_gelesen, *Mem_Zeiger


;######### Testbild in den Speicher laden #########
If ReadFile(0,"testbild.jpg")
  Mem_Size=Lof(0)
  *Mem_Zeiger=AllocateMemory(Mem_Size)
  bytes_gelesen=ReadData(0,*Mem_Zeiger,Mem_Size)
  Debug "Speicherverbrauch ohne Base64"
  Debug "Größe im Speicher : " + bytes_gelesen
Else
  Debug "Bild konnte nicht geöffnet werden"
  End
EndIf
CloseFile(0)


;######### Bild in PostgreSQL Db schreiben #########
UsePostgreSQLDatabase()
If OpenDatabase(0, "host=localhost port=5432 dbname=postgres", "postgres", "***Paßwort****")
  DatabaseUpdate(0,"create table bilder (bild bytea);")
  SetDatabaseBlob(0, 0, *Mem_Zeiger, Mem_Size)
  DatabaseUpdate(0, "INSERT INTO bilder (bild) values ($1);")
Else
  Debug DatabaseError()
EndIf


;######### Bild aus Db lesen #########
*Mem_Zeiger=ReAllocateMemory(*Mem_Zeiger,Mem_Size)
If DatabaseQuery(0, "SELECT * FROM bilder")
  FirstDatabaseRow(0)
  Mem_Size=DatabaseColumnSize(0,0)
  Debug "Bildgröße in der Db :" + Mem_Size
  GetDatabaseBlob(0,0,*Mem_Zeiger, Mem_Size)
  FinishDatabaseQuery(0)
EndIf
DatabaseUpdate(0,"drop table bilder;")
CloseDatabase(0)

Laut PB- Hilfe sollte diese Methode aber so nicht benutzt werden??

Hinweis: PostgreSQL verwendet BYTEA zum Speichern großer Objekte. Das "Escaping" (Sperren), was zum Speichern binärer Daten in einem solchen Feld benötigt wird, macht es oft größer als nötig. Eine gute Möglichkeit zum Speichern binärer Daten ist es, diese mit Base64Encoder() zu verschlüsseln, bevor sie an den Datenbank-Manager übergeben werden.

Die Debugger- Ausgabe zeigt aber keinen erhöhten Speicherverbrauch in der DB.

Code: Alles auswählen

Speicherverbrauch ohne Base64
Größe im Speicher : 51753
Bildgröße in der Db :51753

2. Möglichkeit Database blob und Base64 kodiert

Code: Alles auswählen

EnableExplicit
Define Mem_Size, bytes_gelesen, *Mem_Zeiger, *Ausgabe_Zeiger, Size, Size_Neu


;######### Testbild in den Speicher laden #########
If ReadFile(0,"testbild.jpg")
  Mem_Size=Lof(0)
  *Mem_Zeiger=AllocateMemory(Mem_Size)
  bytes_gelesen=ReadData(0,*Mem_Zeiger,Mem_Size)
  Debug "Speicherverbrauch mit Base64"
  Debug "Größe im Speicher : " + bytes_gelesen
Else
  Debug "Bild konnte nicht geöffnet werden"
  End
EndIf
CloseFile(0)


;######### Speicher Base64 kodieren########
*Ausgabe_Zeiger= AllocateMemory(Mem_Size * 1.35)
Size=Base64Encoder(*Mem_Zeiger,Mem_Size,*Ausgabe_Zeiger,Mem_Size * 1.35)
Debug "Größe Base64 kodiert : " + Size





;######### Bild in PostgreSQL Db schreiben #########
UsePostgreSQLDatabase()
If OpenDatabase(0, "host=localhost port=5432 dbname=postgres", "postgres", "***Paßwort***")
  DatabaseUpdate(0,"create table bilder (bild bytea);")
  SetDatabaseBlob(0, 0, *Ausgabe_Zeiger, Size)
  DatabaseUpdate(0, "INSERT INTO bilder (bild) values ($1);")
Else
  Debug DatabaseError()
EndIf


;######### Bild aus Db lesen #########
*Mem_Zeiger=ReAllocateMemory(*Ausgabe_Zeiger,Size)
If DatabaseQuery(0, "SELECT * FROM bilder")
  FirstDatabaseRow(0)
  Size=DatabaseColumnSize(0,0)
  Debug "Bildgröße in der Db :" + Size
  GetDatabaseBlob(0,0,*Ausgabe_Zeiger, Size)
  FinishDatabaseQuery(0)
EndIf
DatabaseUpdate(0,"drop table bilder;")
CloseDatabase(0)


;######### Speicher Base64 dekodieren #########
*Mem_Zeiger=ReAllocateMemory(*Mem_Zeiger, Mem_Size)
Size_Neu = Base64Decoder(*Ausgabe_Zeiger, Size, *Mem_Zeiger, Mem_Size)
Debug "Größe nach dem dekodiern :" + Size_Neu

Diese Methode zeigt jedoch einen höheren Speicherverbrauch, natürlich wegen der Kodierung.

Code: Alles auswählen

Speicherverbrauch mit Base64
Größe im Speicher : 51753
Größe Base64 kodiert : 69004
Bildgröße in der Db :69004
Größe nach dem dekodiern :51753

3.Möglichkeit die Daten in einen String kodieren und als Type text in die Db schreiben. Verbraucht allerdings mit Unicode den doppelten Speicher in der DB.

Code: Alles auswählen

EnableExplicit
Define Mem_Size, bytes_gelesen, *Mem_Zeiger, Ausgabe_String.s, String_Size, Size_Neu


;######### Testbild in den Speicher laden #########
If ReadFile(0,"testbild.jpg")
  Mem_Size=Lof(0)
  *Mem_Zeiger=AllocateMemory(Mem_Size)
  bytes_gelesen=ReadData(0,*Mem_Zeiger,Mem_Size)
  Debug "Speicherverbrauch mit Base64"
  Debug "Größe im Speicher : " + bytes_gelesen
Else
  Debug "Bild konnte nicht geöffnet werden"
  End
EndIf
CloseFile(0)


;######### Speicher >> String (Base64) #########
String_Size = Mem_Size * 1.35
Ausgabe_String.s=Space(String_Size)
Base64Encoder(*Mem_Zeiger,Mem_Size,@Ausgabe_String.s,String_Size)
Debug "Größe als Base64 kodierter String: " + MemoryStringLength(@Ausgabe_String.s,#PB_Ascii)


;######### String (Base64) in PostgreSQL Db schreiben #########
UsePostgreSQLDatabase()
If OpenDatabase(0, "host=localhost port=5432 dbname=postgres", "postgres", "***Paßwort****")
  DatabaseUpdate(0,"create table bilder (bild text);")
  DatabaseUpdate(0, "INSERT INTO bilder VALUES ('"+Ausgabe_String.s+"');")
Else
  Debug DatabaseError()
EndIf


;######### String (Base64) aus Db lesen #########
*Mem_Zeiger=ReAllocateMemory(*Mem_Zeiger,Mem_Size)
If DatabaseQuery(0, "SELECT * FROM bilder")
  FirstDatabaseRow(0)
  Mem_Size=DatabaseColumnSize(0,0)
  Debug "Bildgröße in der Db :" + Mem_Size
  Ausgabe_String.s=GetDatabaseString(0,0)
  FinishDatabaseQuery(0)
EndIf
DatabaseUpdate(0,"drop table bilder;")
CloseDatabase(0)
Debug "String wieder aus Db gelesen, noch Base64 kodiert : " + MemoryStringLength(@Ausgabe_String.s,#PB_Ascii)


;######### String in Speicher dekodieren #########
*Mem_Zeiger=ReAllocateMemory(*Mem_Zeiger, MemoryStringLength(@Ausgabe_String.s,#PB_Ascii))
Size_Neu = Base64Decoder(@Ausgabe_String.s, MemoryStringLength(@Ausgabe_String.s,#PB_Ascii), *Mem_Zeiger, MemoryStringLength(@Ausgabe_String.s,#PB_Ascii))
Debug "Größe nach dem dekodiern :" + Size_Neu

Die Debugger- Ausgabe:

Code: Alles auswählen

Speicherverbrauch mit Base64
Größe im Speicher : 51753
Größe als Base64 kodierter String: 69004
Bildgröße in der Db :69004
String wieder aus Db gelesen, noch Base64 kodiert : 69004
Größe nach dem dekodiern :51753

Ihr müsst natürlich noch testbild.jpg und postgres Passwort im Code ersetzen.

Gruß, Micha122

[ts-soft]

Ich würde die Referenz auf das Bild in der DB speichern, wenn dies möglich ist?
Jedenfalls ist das die übliche Methode große Bilder zu speichern ohne die DB zu
belasten.

[Micha122]

Ich würde die Referenz auf das Bild in der DB speichern, wenn dies möglich ist?
Jedenfalls ist das die übliche Methode große Bilder zu speichern ohne die DB zu
belasten.

Klar, könnte natürlich alle Bilder auf nen Webserver tun und in der DB lediglich die URL und die Beschreibung hinterlegen.
Ist bezüglich des Festplattenspeichers und des Netzwerktraffics wahrscheinlich die bessere Lösung.

Würde mich aber trotzdem interessieren, was für einen "Elefanten" PG aus einem nicht ASCII kodierten Binary macht?
Ist sicherlich nicht so einfach, die tatsächliche Größe in der DB fest zu stellen?

Gruß

[HeX0R]

[Viel aus dem Kopf, möglicherweise stimmt nicht alles]

Ich hatte da mal vor ein paar Jahren experimentiert und festgestellt, dass die platzsparendste Methode die mit TEXT-Feld und base64 ist.
Deine Methode 1 zeigt ja nicht den tatsächlichen Speicher an, den die Daten in der Datenbank verbrauchen.
Mit phppgmyadmin (so du denn das auf einem webspace laufen hast) kannst Du Dir den tatsächlichen Verbrauch glaube ich anzeigen lassen.

Wenn ich das noch richtig im Kopf habe, gab es zwei Arten, wie postgresql blobs speichert (und die Art liess sich in irgendeiner ini ändern):
Einmal die BYTEA-Variante, bei der tatsächlich jede Menge unnötiger Escape-Zeichen mit verwurstet wird, und eine andere (weiss nicht mehr, wie die heisst), bei der einfach jedes Byte als HexadezimalByte in Klarschrift benutzt wird.
Also hast du z.B. ein Null-Byte, so wird daraus Ascii 48 48 also 00.
Das bedeutet, selbst diese Methode wird den tatsächlichen Speicherverbrauch verdoppeln.

Übrigens wäre es ziemlich hirnrissig base64 Strings unicode codiert in die Datenbank zu packen.
Da nimmt man UTF8 und alles wird gut.

[Micha122]

Deine Methode 1 zeigt ja nicht den tatsächlichen Speicher an, den die Daten in der Datenbank verbrauchen.

Hast natürlich recht.

Mit phppgmyadmin (so du denn das auf einem webspace laufen hast) kannst Du Dir den tatsächlichen Verbrauch glaube ich anzeigen lassen.

Guter Tipp Im SQL Editor kann man z.B. mit dieser Abfrage

Code: Alles auswählen

SELECT pg_size_pretty(pg_database_size('postgres'));

einen vorher/nachher vergleich machen.

Übrigens wäre es ziemlich hirnrissig base64 Strings unicode codiert in die Datenbank zu packen.

Wollte auch nur drauf hingewiesen haben. Allerdings bekomme ich so langsam die Meinung, das das verwenden von base64 in meinem Fall generell hirnrissig ist.



Hier mal einige Zahlen:
Bildgröße 1395619 bytes
Tatsächlicher Speicherverbrauch in der DB 1499136 bytes ohne Base64 ;Als blob in bytea Feld
Also keine 7%

Tatsächlicher Speicherverbrauch in der DB 1990656 bytes mit Base64 ;Als String in text Feld
Fast 30%

Gruß, Micha122

[hyperG]

Ich habe mir auch eine Bilderdatenbank für meine Zwecke gebastelt, da ich über 800000 Fotos von 3 Leuten mit 4 Kameras habe. Pro Bild 50kB ... 10 MB. (natürlich nicht online)
a) Du darfst nicht den Denkfehler machen, dass die Datenbankgeschwindigkeit nur linear langsamer wird. Spätestens ab 1 GB (viele kostenlose DBs haben hier schon ihre Grenzen und unterstützen außerdem nur 1 Kern der CPU) wird es exponentiell langsamer. Je nach Bildgröße und DB ab 100000 ... 1 Mio. Datensätze.

b) Zum schnellen Suchen und Scrollen habe ich mir beim Import (ich stecke dazu die DVD ein) erstellt, der neben den Daten auch ein kleines Vorschaubild erstellt. Selbst dieses ist nur als LINK hinterlegt. Für schnelles scrollen werden nur die Datensätze abgefragt, die im sichtbaren bereich liegen. Erst wenn ich DVD / USB online habe und auf das Vorschaubild klicke, wird das volle große Bild geladen.

c) JPG ist schon eine sehr gute Kompressionsmethode. Normalerweise speichern DBs nicht alle 256 Bytes sicher. Erst nach einem Test mit allen 00 ... FF würde ich mir trauen Daten im Binärformat abzulegen. Die Länge muss ja auch hinterlegt sein. Eine feste Länge wäre bei der Spanne 50kB ... 10 MB verschwenderisch...

d) Da alle anderen Verschlüsselungen die Datenmenge noch weiter aufblähen (wie base64 mit Faktor 1,39), würde das wegen der weiteren Verlagsamung der DB nur für wenige kleine Bilder einen Sinn machen.

[Micha122]

Hallo hyperG,

Ich habe mir auch eine Bilderdatenbank für meine Zwecke gebastelt, da ich über 800000 Fotos von 3 Leuten mit 4 Kameras habe. Pro Bild 50kB ... 10 MB

Ganz so viele Bilder werden es bei mir nicht. Ich schätze mal so 800/Jahr. Aber die Größe 50kb -10MB ist schon realistisch.

Zum schnellen Suchen und Scrollen habe ich mir beim Import (ich stecke dazu die DVD ein) erstellt, der neben den Daten auch ein kleines Vorschaubild erstellt.

Klar, Vorschaubild und Info zum Bild muss natürlich auch alles sein. Die 3 Codes in der ersten Post habe ich nur mal auf die schnelle zum testen gemacht.

Normalerweise speichern DBs nicht alle 256 Bytes sicher. Erst nach einem Test mit allen 00 ... FF würde ich mir trauen Daten im Binärformat abzulegen. Die Länge muss ja auch hinterlegt sein. Eine feste Länge wäre bei der Spanne 50kB ... 10 MB verschwenderisch...

Deswegen ja auch der Versuch mit Base64. Binär wird dann zu sicheren ASCII. Leider aber auch sehr groß. An die Bildgröße kommst immer wider ran z.B. DatabaseColumnSize


Gruß