[Zope] Upload files in Zope and store elsewhere

[Andy Dustman]

On Wed, 22 Mar 2000, Tres Seaver wrote:

> Chris Withers <chrisw@nipltd.com> writes:
> > 
> > I'd also love to be able to store files uploaded through a Zope form into a
> > MySQL database so if
> > anyone else has done this successfully, particularly for medium size files
> > (5-6 MB) please can you
> > let the list know!
> 
> My advice here is not based on work with Zope, but on years of painful RDBMS
> experience:  I do *not* recommend storing large BLOBs in any existing RDBMS --
> they aren't built for them, and generally make your life miserable when you
> try.  Most of the reasons you would choose to use an RDBMS (performance,
> replication, stability, searchability, etc.) don't apply when it comes to large
> BLOBs;  you are still much better off storing the "BLOB" in a filesystem
> somewhere, and then storing a path/URL to it in the RDBMS.
> 
> With Zope, BLOBs present another issue:  because they aren't standardized, each
> RDBMS has a proprietary API for BLOBS;  the Zope database adapter therefore has
> to have custom code for each RDBMS, making the BLOB code one of the likeliest
> sources of weird, hard-to-reproduce bugs I can think of.
> 
> In short, *don't go there*.  Really, I'm serious.

I tend to agree with Tres here. MySQL can certainly handle BLOBS, but
here's something you might not realize: All MySQL queries are literals.
Let that sink in for a moment. What does this mean? Suppose you are
using MySQLdb (not a ZSQLDA, we'll get to that). You need to do
something like this:

c = db.cursor()
c.execute("INSERT INTO BlobCity (Pix) VALUES (%s)", pix)

Looks simple, doesn't it? Well, here's what happens behind the scenes.
First MySQLdb has to turn pix into a MySQL literal value. Fortunately, if
you have, say, a JPEG class, you can write a function (not method) which
converts it into a MySQL literal. Suppose your class has a __str__ method
which returns the raw data as a Python string (maybe it IS a string
internally). Then you can do:

def JPEG2Literal(j): return "'%s'" % MySQLdb.escape_string(str(j))

(Although this is actually the default converter, used for strings; if you
have a __str__ method, you can just pass it to MySQLdb and it should do
the right thing.)

MySQLdb.escape string does all of necssary escaping of special characters,
namely backslash, NUL, and single-quote. But to do this, it has to
allocate a temporary buffer that's twice the size of the original string
to handle the pathological cases. Then quotes have to be added. Then this
all has to be inserted back into the original query. In the case of a 5 MB
BLOB, these steps work out to be:

1) str(j): 5MB
2) escape_string(#1): intermediate 10MB, return new 5MB+ string
3) "'%s'" % #2: return new 5MB+ string, but del #2
4) querystring % (#3): return new 5MB+ string, but del #3

So you're making and destroying a lot of big strings, which takes a
considerable amount of time. And we're only using one column and one row.
(I should probably make a new C function that combines #2 and #3 for
efficiency.) Most of what MySQLdb does is string crunching. For that
matter, MySQL returns all values to the C API as strings (including
numbers).

And now the Zope ZMySQLDA case: Z DA's always build literal queries
anyway, so this makes MySQL no worse off than any other database. (Most
database APIs, like ODBC, seem to use positional placeholders in the query
and pass the rows/columns as a data structure, which makes for much less
parsing on the server side, and is much easier to construct on the client
side.)

Compare this to writing into the filesystem. Well, no comparison, really.
open, write, done. So long as you can keep things organized, you're ok.

-- 
andy dustman       |     programmer/analyst     |      comstar.net, inc.
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage, 
come no further, for death awaits you all, with nasty, big, pointy teeth!"