[Zope] Postgres and BLOBS

[Yves Bastide]

On Saturday 27 April 2002 03:48, Colin Fox wrote:
 > Hi, everyone.
 >
 > I need to be able to store binaries (PDFs, JPEGs, etc) in postgres
 > tables and embed them in web pages.
 >
 > The idea is I have entries in the database and there is an image to go
 > with each entry.
 >
 > Postgres supports BLOBs, but there is a somewhat awkward interface to
 > using them (you have to actually extract the blob to a file on disk).
 >
 > I was wondering if anyone had created an object (simlar to the Zope
 > image object) that would let me reference binaries in the database
 > without having to do local file manipulation?


PostgreSQL supports two sorts of blobs: lo and bytea.
lo is the old, kludgy interface, whith a few advantages over bytea; most 
notably one can retrieve parts of a blob. And many well-known 
disadvantages.  Bytea corresponds to the (really minimal) standard SQL 
blob interface.

Micro-howto: Using a bytea (what follows is a quick slash-and-paste from 
an app here; may work or not as-is):

Say you've got a "File" table:

create table File (
	id serial primary key,
	fname text,
	mimetype text,
	content bytea);


Inserting a file
----------------

 From a form with a "file" input:

## Script (Python) "doInsFile"
##bind container=container
##bind context=context
##bind namespace=
##bind script=script
##bind subpath=traverse_subpath
##parameters=fichier
##title=
##
from psycopg import Binary

container.insertFile(
     fname = container.REQUEST.file.filename,
     mimetype = container.REQUEST.file.headers['Content-Type'],
     content = Binary(container.REQUEST.file.read())
     )


With the insertFile SQL method:

<params>fname:string
mimetype:string
content:string</params>
insert into file
  (fname, mimetype, content)
values (
  <dtml-sqlvar fname type="string">,
  <dtml-sqlvar mimetype type="string">,
  <dtml-var content>::bytea
)


For this to work, you must allow psycopg's Binary in a Product (see 
Products/PythonScripts/module_access_examples.py for instructions).


Retrieving a file
-----------------

The following query retrieves a file:

<params>id:int</params>
select
  fname,
  mimetype,
  octet_length(content) AS fsize,
  content
from file
where
  <dtml-sqltest id type="int">


It can be called by a Python script like:

files=container.qryFichier(id=id)
for file in files:
     RESPONSE.setHeader('Content-Type', file.mimetype)
     RESPONSE.setHeader('Content-Length', file.fsize)
     RESPONSE.setHeader('Content-Disposition', 'attachment; filename=%s'
                        % file.name)
     if file.fsize:
         RESPONSE.write(fic.content)


Misc. Remarks
-------------

* When uploading a file, IE gives the full pathname while NS and Moz 
return the filename with no path (I didn't try Konqueror, Opera and 
others). You should sanitize "file.filename" before putting it in the 
DB, if you do.

* Blob-access is totally DB-dependant.., even db-adapter-dependant. It's 
a PITA :-/


yves