Support for binary type fields (aka: bytea in postgres and VARBINARY in mysql)

Please post your patch, I think I'm not the only one who is interested to see it.

Here is the patch against the HEAD of django's subversion tree. What I actually use is a teeny bit different in that I have put the BinaryField? class in my project instead of modifying django's source. I had to modify django/db/models/manipulator.py::manipulator_validator_unique_together() because binary types do not support case-insensitive comparison under postgresql at least (see http://code.djangoproject.com/ticket/2422). Otherwise this is an extremely trivial patch.

A small patch to add a BinaryField? type directly to django. Supports small'ish binary fields.

This looks very, very good to me.

However, it needs (a) tests and (b) documentation before it can be checked in. Can you add 'em to the patch?

There could be potential issues here. There should be some sort of support for setting the maximum size that a file can be that gets inserted into this field to prevent a DOS issue by uploading multiple large items. The BYTEA column can contain any size data (see http://www.postgresql.org/docs/8.1/static/datatype-binary.html).

Would be great if this could be used for ImageField? as well. Maybe instead of adding a new field type, allow the user to set the "backend" that Django uses to store the file. When set to Binary it would create a new table to hold the binary objects, then a ForeignKey? relationship between the table that contains the metadata for the binary object, and the the table which just contains the binary object itself. Deciding whether to update the binary information in the binary table could be done as the result of an md5sum hash or some other form.

Let's try to keep one issue per ticket, please. This ticket is not about storing ImageFields in the database.

Could we consider using LARGE_BLOB as the default type for MySQL (and ado_mssql, I guess), so that there's not a 64k limit on the size, by default. I realize your application is specifically for small bits of binary data, but if there's only going to be one binary type, it should be as flexible as possible.

Also, I'm not sure about extending CharField?. As I mentioned in an email to django-developers, that means you show up in the admin, and I have to agree with Malcolm that it's probably not a good thing to just display random binary data. I'm not sure what the best way to allow binary input is, but maybe using hex, as you mentioned in the patch, is an option. At any rate, I'm not sure BinaryField? should inherit from any currently defined field type.

Is this getting any attention?

I also need binary fields to store mid-states of large computing tasks i do in python - storing and restoring them using pickle and having them all well sorted in a database...

As per Jacob's comments above.

Just a side note, BinaryField? will require maxlength (as it inherits CharField?) but PostgreSQL backend will ignore it. Related to this, you can use this BinaryField? to store any amount of data on PostgreSQL but only maxlength bytes on MySQL.

The ticket is marked "needs documentation" so I'm writting it, I'll write a note stating that ;)

Oh, forgot to mention, CharFields? show as <input type="text"> on the Admin, how is BinaryField? shown? It could be maybe a checkbox stating "there is data on this BinaryField?, uncheck to delete it" or something like that?

Documentation for BinaryField?

Where are tests for Field-types written? could not find them :)

Please see discussion at http://groups.google.com/group/django-developers/t/266565a23b765ea2

BTW, Postgres (at least) requires escaping for some characters put into BYTEA field. In my code I used to do it like this (this yields octal representation for all characters):

class ByteAField(models.CharField):
  def get_db_prep_save(self, value):
    return value and ''.join(['\\%03o' % ord(c) for c in value])

However it's put in field's code and I don't know how to do it in a backend-specific way...

This looks good, and we'd like to get this in. However, the patch needs a few improvements (and tests!) if possible (see the discussion in the list thread I linked above). First, it should not subclass CharField?, and it will need to maintain database portability (do we need to transparently store everything in base64 here?). Finally, we should increase the size of this (not to store huge blobs of data, but larger than 255 bytes - 64kb is enough?)

Replying to Marc Fargas <telenieko@telenieko.com>:

Just a side note, BinaryField? will require maxlength (as it inherits CharField?) but PostgreSQL backend will ignore it. Related to this, you can use this BinaryField? to store any amount of data on PostgreSQL but only maxlength bytes on MySQL. The ticket is marked "needs documentation" so I'm writting it, I'll write a note stating that ;)

If a binary field is provided it should allow the developer to decide which amount of data does he/she want to store so in the case of PostgreSQL maxlength could be enforced checking the length before saving **and** it should not be limited to 64Kb, it should allow **any amount** from 1 byte to the maximum field size (1GB on postgresql).

Also maxlength should be "maxbytes" ?

Ok - so does someone want to rewrite the attached patch to take into account my and Marc's comments above?

How about calling the argument which limits the file size "maxsize"?

This would be analogous to "maxlength" (not "maxchars") where you limit the length of a CharField?.

But make sure you call it max_size (or _bytes), as the CharField? one is changing to max_length eventually.

The Admin interface to a binary field should probably be an upload/download interface.

Like a FileField? (and a downlaod link next to it), except that it stores its data in the database instead.

Simon,

I'm interested in bringing this patch so some sort of close. I read through the thread you posted earlier, and I think the best thing to do is exactly what you said in your last message: focus on this patch first, then think about storing large data later (or at least leave it up to individual developers).

As far as I understand, we need the following changes to this BinaryField?:

1. The Field should not inherit from anything.
2. The Field should transparently store everything in base64
3. The Field needs a max_size attribute to let developers specify the maximum amount of data it can store. This will have to be enforced by Django before saving it to the database, since PostgreSQL will ignore the attribute.
4. Many more good tests.

Am I missing anything?

Nick

Adding myself to cc list.

I'm very keen to see this feature. I'd like to use Django for a project that will store a lot of binary data and I'd rather store it in the database for scalability and ease of backups.

Nick - re #2, base64 surely isn't necessary if the database supports binary BLOBs, or am I missing something?

Please do not add transparent base64 encoding as

• none of the current database backends need it for storing binary data - except postgres, that needs only escaping a fixed set of symbols (and that could indeed be done transparently, see http://www.postgresql.org/docs/8.3/interactive/datatype-binary.html)
• it degrades performance with no gain (needless pessimisation)
• it makes interoperability with other frameworks (e.g. openid.store.sqlstore) that may need to store their data in a Django-managed database impossible.

So the roadmap should be as follows (according to the discussion in mailing list):

1. There should be two fields, BinaryField? and SmallBinaryField? that use different backend features as appropriate (NEEDS BACKEND EXPERTISE AND DECISION, use BLOB/VARBINARY/bytea properly)
2. The fields should inherit directly from db.models.fields.Field (TRIVIAL)
3. The fields should NOT mangle the provided binary data on save (no base64-encoding), except for postgres (TRIVIAL, postgres escaping should be in the postgres backend)
   • the fields could base64-encode or hexencode (a subset of?) the raw data for display on retrieve (TRIVIAL, interface needs decision though, should __unicode__() do it?)
   • also, for large fields, a download link from admin could be provided (NON-TRIVIAL, less essential)
4. The fields may need a max_size attribute to let developers specify the maximum amount of data they can store. This will have to be enforced by Django before saving it to the database, since PostgreSQL will ignore the attribute (NEEDS DECISION, a naive len(1_gb_of_data) is not a good idea, the restriction is less essential IMHO)
5. An appropriate test suite is required.

Image/FileField backend in DB with these fields is non-essential and should be marked someday/maybe.

I'm willing to update the patch once design decision is reached.

mrts,

You're absolutely right about transparent base64 encoding. I didn't think through the implications, and your reasoning explains why it's not necessary.

For #1, I'm thinking that the BinaryField? should provide lots of data (i.e. unlimited) while SmallBinaryField? should have a max_size attribute (#4) enforced by Django before saving it to the database, as you mentioned. Jacob talked about this in http://groups.google.com/group/django-developers/browse_thread/thread/266565a23b765ea2

-- PostgreSQL --

I think the BLOB in Postgres is best suited for BinaryField?, as it has no size limit. Although bytea in Postgres also has no size limit, it's a "binary string," so it seems like we should save that for the SmallBinaryField? with a defined max_size.

As far as #3, we should definitely escape data passed into postgres, but I couldn't find a method in the backend to do it.

-- MySQL --

I'm not sure what the best choice is for MySQL. Quoting from the MySQL documentation:

• TINYBLOB - A BLOB column with a maximum length of 255 (28 - 1) characters.
• BLOB - A BLOB column with a maximum length of 65,535 (216 - 1) characters.
• MEDIUMBLOB - A BLOB column with a maximum length of 16,777,215 (224 - 1) characters.
• LONGBLOB - A BLOB column with a maximum length of 4,294,967,295 (232 - 1) characters.

Using MEDIUMBLOB will give us about 15mb of data, but if we want BinaryField? to truly be unlimited in size, that won't work for all cases. I'm not sure what to do with that.

For SmallBinaryField?, I thought of using VARBINARY, which doesn't pad values up to the limit specified, but both BINARY and VARBINARY have a maximum size of 65535 bytes, which isn't nearly enough. It seems like we'll need something like MEDIUMBLOB instead? Or maybe different BLOB types depending on the max_size? I'm not sure how to resolve this.

I'm not sure about providing a download link from the admin interface. It's a possibility, but I'm -0 on it since actually getting the fields ready is a higher priority. Also, I don't think we should think about a DB backend for Image/FileField. If someone really wants to do that, they can code it on their own.

Resetting triage state to design decision needed. Once concensus is reached, I'm ready to start hacking.

mrts, could you offer your comments on my last comment? I've attempted to lay out the specifications for how this should be done.

I think the Django BDFLs should comment on this now.

Whoever works on this should realise it's a little trickier than it looks. You'll need to work carefully with the database backends so that this binary data isn't automatically converted to unicode (which happens for all bytestrings at the moment). Make sure you test with binary data that cannot possibly be treated as valid UTF-8, so that you don't accidentally get fooled by it being converted to Unicode. I'd suggest creating a new class, similar to SmartUnicode?, etc, for autoescaping that is used to tell the database backend *not* to convert this string. Might require some magical incantations for things like psycopg2 and mysql that do the UTF-8 -> Unicode conversion in the Python database wrapper (beyond the Django borders); that will need research.

I don't know why this ticket is in design decision needed, since there aren't any specific questions that seem to be unresolved beyond "what should we choose?". Just pick something and go. The field doesn't need infinitely many options. One case cannot possibly meet all requirements in any case, so pick a practical middle ground that works completely portably on all our database backends and if people want something else they can always subclass the field.

If you do need a design discussion, this ticket isn't the place for it. We have a mailing list for that purpose.

-> Accepted.