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!"