Postgres client functions possible ???

[infratec]

Hi,

we need BYTEA or the OID method to store documents inside the database.
If you read arround you'll find that the OID method is faster and needs less resources.

But...

the client side functions lo_open, lo_write etc. are needed.

Is this possible with the PB implementation?

Bernd

P.S.:

In PHP it looks like:

Code: Select all

function saveOid($Conn, $Filename)   
{    
  global $Dir;
  $FileContent = file_get_contents($Dir.'/'.$Filename);    
  pg_query($Conn, "begin;");    
  $Oid = pg_lo_create($Conn);    
  $Query = "INSERT INTO images (name, image) VALUES ('".$Filename."', '".$Oid."');";    
  pg_query($Conn, $Query);
  $OidHandle = pg_lo_open($Conn, $Oid, "w");   
  pg_lo_write($OidHandle, $FileContent);    
  pg_lo_close($OidHandle);    
  pg_query($Conn, "commit;");
  unset($FileContent);   
}

[infratec]

Hi,

I try now the following:

Code: Select all

UsePostgreSQLDatabase()

ImportC "oldnames.lib" : EndImport 

ImportC "postgresql.lib"
  pg_lo_create(*conn, mode.l=0) As "_lo_create"
  pg_lo_open(*conn, flags.l) As "_lo_open"
  pg_lo_write(*conn, fd.l, *buffer, size.l) As "_lo_write"
  pg_lo_close(*conn, fd.l) As "_lo_close"
EndImport

Debug @pg_lo_create()
Debug @pg_lo_open()

Tomorrow I'll test it.

Bernd

[dige]

@Infratec: does it work?

[infratec]

Hi dige,

YES

Since 20 minutes I'm able to use the OID way of storing binary data.
I'll just make a few corrections, than I post it here.

Only disadvantage at the moment:

I can't use the ID of the OpenDatabse() procedure of PB.
I have to open a new connection with the low level Postgres functions

Bernd

[dige]

I read somewhere that Postgres stores data as hex or binary. Is OID different from that?
With both methods, I've also found that storing an 1000KB picture is after reload suddenly 1100KB big.
Working with BLOBs does not seem to really work..

[infratec]

Hi,

I have both ways working: BYTEA and OID.

And both works without problems.
The OID way has the advantage that when you make a SELECT * it is fast,
since the binary data is not loaded.
And the disadvantage that you have to delete the binary data 'by hand' when you delete
the record in the table.

Bernd

[infratec]

As promised, here are my results so far:

Code: Select all

; else you get a polink error
UsePostgreSQLDatabase()

#INV_WRITE = $00020000
#INV_READ = $00040000

; else you get an error symbols not found
ImportC "oldnames.lib" : EndImport

ImportC "postgresql.lib"
  PQconnectdb(Conn$) As "_PQconnectdb"
  PQstatus(*PGconn) As "_PQstatus"
  PQsendQuery(*PGconn, command$) As "_PQsendQuery"
  PQgetResult(*PGconn) As "_PQgetResult"
  PQclear(*PGresult) As "_PQclear"
  PQfinish(*PGconn) As "_PQfinish"
  pg_lo_create(*PGconn, OID.l=0) As "_lo_create"
  pg_lo_open(*PGconn, oid.l, mode.l) As "_lo_open"
  pg_lo_read(*PGconn, fd.l, *buffer, size.l) As "_lo_read"
  pg_lo_write(*PGconn, fd.l, *buffer, size.l) As "_lo_write"
  pg_lo_close(*PGconn, fd.l) As "_lo_close"
  pg_lo_import(*PGconn, Filename$) As "_lo_import"
  pg_lo_export(*PGconn, OID.l, Filename$) As "_lo_export"
  pg_lo_unlink(*PGconn, OID.l) As "_lo_unlink"
EndImport




Procedure.i PGSaveOidDataFromFile(Connect$, Filename$)

  Protected Result.i, SQL$, OID.l, *PGConn, *PGresult
  
  
  *PGConn = PQconnectdb(Connect$)
  
  Debug *PGConn
  Debug PQstatus(*PGConn)

  
  SQL$ = "BEGIN;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
  
  OID = pg_lo_import(*PGconn, Filename$)
  Debug OID
  Result = OID
  
  If Result > 0
    SQL$ = "INSERT INTO " + #DQUOTE$ + "Aktuell" + #DQUOTE$ + ".dokumente (datei_name, datei_oid, datei_groesse) VALUES (E'" + ReplaceString(Filename$,"\", "\\") + "', '" + Str(OID) + "'," + Str(FileSize(Filename$)) + ");"
    Debug SQL$
    PQsendQuery(*PGConn, SQL$)
    Repeat
      *PGresult = PQgetResult(*PGConn)
      If *PGresult
        PQclear(*PGresult)
      EndIf
    Until *PGresult = #Null
  EndIf
  
  SQL$ = "COMMIT;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
  
  PQfinish(*PGConn)
  
  ProcedureReturn Result
  
EndProcedure




Procedure.i PGLoadOIDDataToFile(Connect$, Filename$, OID.l)
  
  Protected Result.i, *PGconn, SQL$, *PGresult
  
  
  *PGConn = PQconnectdb(Connect$)
  
  Debug *PGConn
  Debug PQstatus(*PGConn)
  
  SQL$ = "BEGIN;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
  
  If pg_lo_export(*PGConn, OID, Filename$) > 0
    Result = #True
  EndIf
  
  SQL$ = "COMMIT;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
  
  PQfinish(*PGConn)
  
  ProcedureReturn Result

EndProcedure




Procedure.i PGSaveOidDataFromMemory(Connect$, *Buffer, Length.i)
  
  Protected Result.i, SQL$, OID.l, OIDHandle.i, *PGConn, *PGresult
  
  
  *PGConn = PQconnectdb(Connect$)
  
  Debug *PGConn
  Debug PQstatus(*PGConn)

  
  SQL$ = "BEGIN;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null

  OID = pg_lo_create(*PGconn)
  Debug OID
  
  SQL$ = "INSERT INTO " + #DQUOTE$ + "Aktuell" + #DQUOTE$ + ".dokumente (datei_name, datei_oid, datei_groesse) VALUES ('" + Filename$ + "', '" + Str(OID) + "'," + Str(Size) + ");"
  Debug SQL$
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
  
  OIDHandle = pg_lo_open(*PGConn, OID, #INV_WRITE)
  If OIDHandle > -1
    If pg_lo_write(*PGConn, OIDHandle, *Buffer, Length) = Length
      Result = #True
    EndIf
    pg_lo_close(*PGConn, OIDHandle)
  EndIf
  
  SQL$ = "COMMIT;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
    
  PQfinish(*PGConn)
  
  ProcedureReturn Result
  
EndProcedure




Procedure.i PGLoadOIDDataToMemory(Connect$, *Buffer, Length.i, OID.l)
  
  Protected Result.i, *PGconn, SQL$, OIDHandle.l, *PGresult
  
  
  *PGConn = PQconnectdb(Connect$)
    
  Debug *PGConn
  Debug PQstatus(*PGConn)
    
  SQL$ = "BEGIN;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
  
  OIDHandle = pg_lo_open(*PGConn, OID, #INV_READ)
  If OIDHandle > -1
    Debug "-----"
    If pg_lo_read(*PGConn, OIDHandle, *Buffer, Length) > 0
      Result = #True
    EndIf
    pg_lo_close(*PGConn, OIDHandle)
  EndIf
    
  SQL$ = "COMMIT;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
    
  PQfinish(*PGConn)

  ProcedureReturn Result
  
EndProcedure




Procedure PGDeleteOIDData(Connect$, OID.l)
  
  Protected *PGConn, SQL$, *PGresult
  
  
  *PGConn = PQconnectdb(Connect$)
  
  Debug *PGConn
  Debug PQstatus(*PGConn)
  
  SQL$ = "BEGIN;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
  
  If pg_lo_unlink(*PGConn, OID) > 0
    Result = #True
  EndIf
  
  SQL$ = "COMMIT;"
  PQsendQuery(*PGConn, SQL$)
  Repeat
    *PGresult = PQgetResult(*PGConn)
    If *PGresult
      PQclear(*PGresult)
    EndIf
  Until *PGresult = #Null
  
  PQfinish(*PGConn)
  
  ProcedureReturn Result
  
EndProcedure




Define Connect$, Filename$, OID.i

Connect$ = "host=xxx.xxx.xxx.xxx dbname=xxx user=xxx password=xxx"

Filename$ = OpenFileRequester("Choose a file", "", "*.*", 0)
If Filename$
  Debug Filename$
  OID = PGSaveOidDataFromFile(Connect$, Filename$)
  
  PGLoadOIDDataToFile(Connect$, Filename$ + "." + GetExtensionPart(Filename$), OID)
  
  PGDeleteOIDData(Connect$, OID)
EndIf

You need to adjust the Connect$ and the INSERT commands.
You need fields like:
file_name type text
file_oid type oid
(file_size type integer) only for memory stuff

Bernd

[infratec]

Hi,

we have investigated this stuff a bit closer:

If you use the OID method, the file is truncated in small pieces
and stored in BYTEA fields in the table pg_largeobject.

So it is slower, you need more overhead when deleting or updating.
The only real advantage is:
If you use pgadmin you can show the complete table.

With the direct BYTEA field method I found one problem with PB:
DatabaseColumnsize() returns wrong values.
That was the reason to add a size field to the table.

Bernd

[infratec]

Here is the BYTEA way:

Code: Select all

EnableExplicit

UsePostgreSQLDatabase()


Procedure SaveDataFromFile(DB.i, Table$, Filename$)
  
  Protected Result.i, File.i, Size.i, *Buffer
  
  
  File = ReadFile(#PB_Any, Filename$)
  If File
    Size = Lof(File)
    *Buffer = AllocateMemory(Size)
    If *Buffer
      If ReadData(File, *Buffer, Size) = Size
        SetDatabaseBlob(DB, 0, *Buffer, Size)
        If DatabaseUpdate(DB, "INSERT INTO " + Table$ + " (filename, filesize, filedata) VALUES (E'" + ReplaceString(Filename$, "\", "\\") + "'," + Str(Size) + ", $1)")
          Result = #True
        Else
          Debug DatabaseError()
        EndIf
      EndIf
      FreeMemory(*Buffer)
    EndIf
    CloseFile(File)
  EndIf
  
  ProcedureReturn Result
  
EndProcedure




Procedure LoadDataToFile(DB.i, Table$, Filename$="")
  
  Protected Result.i, File.i, Size.q, *Buffer
  
  
  If DatabaseQuery(DB, "SELECT filename, filesize, filedata FROM " + Table$)
    If NextDatabaseRow(DB)
      If Filename$ = ""
        Filename$ = GetDatabaseString(DB, 0)
      EndIf
      Size = GetDatabaseLong(DB, 1) ;/ Get size of the blob
      *Buffer = AllocateMemory(Size)
      If *Buffer
        GetDatabaseBlob(DB, 2, *Buffer, Size)
        File = CreateFile(#PB_Any, Filename$)
        If File
          If WriteData(File, *Buffer, Size) = Size
            Result = #True
          EndIf
          CloseFile(File)
        EndIf
        FreeMemory(*Buffer)
      EndIf
    EndIf
    FinishDatabaseQuery(DB)
  EndIf
  
  ProcedureReturn Result
  
EndProcedure



Define DB.i, Table$, Filename$

;/ Create DatabaseUsePostgreSQLDatabase()
DB = OpenDatabase(#PB_Any, "host=xxx.xxx.xxx.xxx port=5432", "xxx", "xxx")
If DB
  
  Table$ = "datatest"
  
  If DatabaseUpdate(DB, "CREATE TEMP TABLE " + Table$ + " (filename TEXT, filesize INTEGER, filedata BYTEA)")
    
    Filename$ = OpenFileRequester("Choose a file", "", "*.*", 0)
    If Filename$
      
      SaveDataFromFile(DB, Table$, Filename$)
      
      LoadDataToFile(DB, Table$, Filename$ + "." + GetExtensionPart(Filename$))
      
    EndIf
    
  EndIf
  CloseDatabase(DB)
Else
  MessageRequester("Error", "Was not able to connect.")
EndIf

Bernd

[JHPJHP]

Hi,

I've been using ByteA (images) / PostgreSQL for a personal project, and I've had the database size exceed 30 Gb with no problems, but one of the first things I had to do was set the following:

ALTER DATABASE dbname SET bytea_output TO 'escape';

HTH

[dige]

@JHPJHP: thx for that hint. I thought so far, that's only awkward via config file.