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

[scanner@…]

I have a need of having fields that store small blips of binary information. In general this works fine with the CharField, however it will not work for all such fields. For example a datum that is just the NUL character, or a series of NUL characters. With postgres at least: "Character strings disallow zero octets,"

These are not things like images or other such large amounts of data. They are typically fairly short (10 to 200 bytes).

For my purposes I created a small patch that adds a new field type that basically is a CharField except it uses the binary type on postgres and mysql.

Does the django project plan on supporting a binary type field, if so should I post my patch for this?

[chukharev@…]

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

[scanner@…]

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.

[scanner@…]

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

[Jacob]

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?

[todd.kennedy@…]

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.

[Malcolm Tredinnick]

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

[toddobryan@…]

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.

[anonymous]

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...

[Gary Wilson <gary.wilson@…>]

As per Jacob's comments above.

[Marc Fargas <telenieko@…>]

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 ;)

[Marc Fargas <telenieko@…>]

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?

[Marc Fargas <telenieko@…>]

Documentation for BinaryField

[Marc Fargas <telenieko@…>]

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

[Simon G. <dev@…>]

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

[Ivan Sagalaev <Maniac@…>]

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...

[Simon G. <dev@…>]

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?)

[Marc Fargas <telenieko@…>]

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

[Simon G. <dev@…>]

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

[Michael P. Jung]

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.

[Chris Beaven]

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

[smurf@…]

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.

[Nick Fishman <bsdlogical@…>]

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

[Nick Fishman <bsdlogical@…>]

Adding myself to cc list.

[David Swift <david@…>]

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.

[David Swift <david@…>]

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

[mrts]

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.

[mrts]

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

[Nick Fishman <bsdlogical@…>]

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.

[mrts]

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

[Nick Fishman <bsdlogical@…>]

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

[mrts]

I think the Django BDFLs should comment on this now.

[Malcolm Tredinnick]

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.

[Jacob]

-> Accepted.

[prz@…]

So, here's a little something (against 5773). Basically I started out with the idea along the postgress suggestion (write the octal string out). That ain't working with MySQL since basically a string is stored into the varbinary(X). The solution is to go the SQL ISO path and come up with something akind of

INSERT INTO X field VALUES (x'000001')

BUT the placeholder code in the database backend takes anything passed as '000001' as a string (of course). Now, if we could only put things in the generated SQL before/after placeholder ;-) Look now Ma, now hands

class BinValueField(Field):
    """ @brief stores raw binary data """
    def __init__(self,verbose_name=None, name=None, help_text='', db_column=None, db_tablespace=None):
        super(BinValueField,self).__init__(verbose_name=verbose_name, name=name,
                                           primary_key=False,
                                           max_length=15, unique=False,
                                           blank=True, null=True,
                                           db_index=False,
                                           core=False, rel=None, default=NOT_PROVIDED,
                                           editable=False, serialize=True,
                                           prepopulate_from=None, unique_for_date=None, unique_for_month=None,
                                           unique_for_year=None, validator_list=None, choices=None, radio_admin=None,
                                           help_text=help_text, db_column=db_column, db_tablespace=db_tablespace)    
    
    def db_type(self):        
        return "VARBINARY(15)"
    
    def get_db_prep_save(self, value):
        #print ['\\%03o' % ord(c) for c in value]
        return value and ''.join(['%02x' % ord(c) for c in value])+"" or None
        #return unicode(value)

    def get_db_value_placeholder_prefix(self, value):
        return value and 'x' or ''

    def get_db_value_placeholder_postfix(self, value):
        return None

The idea is that Field allows for optional method that provides an optional prefix and postfix on the placeholder and the SQL generated is then something like (postfix is good if you need casts & such jazz)

INSERT INTO `T` ('F') VALUES (x'5f80')

Patch attached to list of files.

[prz@…]

small patch to allow prefix/postfix on placeholders in SQL inserts, makes casts & typing of input in MySQL possible.

[alex@…]

BinaryField, BlobField patch to Django

[alex@…]

Hello,

I have looked at the various patches and tried to produce my own, improved version. I've improved it by by adding modeltests, made sure it works fine with MySQL, PostgresSQL and SQLite.

The main idea here is to get similar results, similar data types for all supported databases - that is why I used 'buffer' type to
be always returned from backend.

I have re-read all related discussions in google groups, and looked at tickets 2422, 5135.

A few small problems described in django-developers and other bugs has been solved too, like using 'repr' instead of 'unicode'to show SQL debug in Debug mode.

I am ready to support this patch for some time until it gets stable and good enough to have a chance being included to trunk.

Comments are welcome. I don't think that this work is 100% final, but it have good chances to became good enough if you comment & help me to improve.

We are using this patch in a production version of one site, and we will improve it along the way.

[Malcolm Tredinnick]

Thanks for looking at the "make it work across all databases" aspect of this. That's a necessary part of the process. I had a quick read of the patch. I like the use of buffer(), although just using a bytestring (Python's str type) would have worked to. We might end up using str for simplicity in the end, but that's a very minor thing.

There are a few things that I don't immediately like, however (and I'm really happy you've done the work here, so take this as a serious design review as part of getting it into trunk, rather than dissing your effort): it feels messy to have to test for certain field types in Model.__init__. Part of that would be solved by using (or at least, permitting) bytestrings instead of buffers. More generally, though, you've introduced a problem with all other data types. Using repr(v) instead of simply v in the backends/__init__.py file when you're converting to unicode means that all the places where smart objects with a __unicode__ method are used for database input will no longer work. You can't do that. I'm also a little surprised that change works, because calling unicode() on something that isn't valid UTF-8 data (which binary data can easily do) is just going to be broken there. What is you have data such as \xff\xff for example, which isn't valid UTF-8?

Instead, I'd consider creating a special storage type (e.g. BinaryData) analogous to SafeString. The database backend wrappers are then taught to treat such data as binary, rather than converting them to/from unicode. The conversion between BinaryData (or whatever it's called) and str or buffer can be done in db_prep_save() and db_prep_load(), etc. I haven't done all the research about what is needed to convince each backend to treat the data as real binary data (rather than converting it from UTF-8 to Unicode or something) and that was one thing holding up me doing anything here. From your patch, it looks like the answer is that not much is required, which is better than I'd hoped, but you have to do something to not require the change in backend/__init__.py.

I'm a little concerned to see get_manipulator_* functions being added, simply because they shouldn't be needed. Manipulators are Old Busted and newforms-style fields and newforms-admin (which merged probably after you made this patch, so it's no biggie) are the New Hotness. So nothing manipulator-related should be needed.

Finally, it will be easier to review the patches if the random blank lines and print statements around line 157 of fields/__init.py weren't there acting as a distraction.

It's quite possible I'm missing some subtle stuff here and I'll think about it a bit more to check that, but the above are the main things I'm concerned about at the moment. Particularly, the leaking of binary data into Model.__init__ and the incorrect handling of converting objects to Unicode strings in backend/__init__.py. If anything's not clear, or you want to bounce ideas around in a better UI than a webform in Trac, feel free to email me directly.

[alex@…]

latest version of the patch, fixed up to 8020 with some improvements

[alex@…]

Thanks for looking at the "make it work across all databases" aspect of this

We also plan to install Oracle (may be) and try to test it with it.

I like the use of buffer(), although just using a bytestring (Python's str type) would have
worked to.

buffer() has been used by performance reasons, PostgreSQL and SQLite already return it (and psycopg2 developers mention that its a way faster of using str here).

it feels messy to have to test for certain field types in Model.init

Agree. We have found better solution, by using MySQL converters. Other database backends don't require any conversion.

Using repr(v) instead of simply v in the backends

This conversion was only applied when DEBUG=True and some SQL error raise. Since binary data can be quite large, we have decided to print only indication that binary data is passed, and size of it. May be thats not ideal, but to understand problem in SQL should be enough.

Alex & Vic @ halogen-dg.com

[alex@…]

latest version of the patch (with tests), fixed up to 8020 with some improvements

[alex@…]

Please note, this patch does not yet include:

(a) Newform Admin functionality
(b) Fixtures dump/load

So its not quite ready for check-in. As soon as we finish all those functional parts we will notify.

[hanne.moa@…]

Just a quick reminder that at least Postgres' BYTEA is deliberately used to store raw bytestrings in legacy databases, aka. b'' in Pythons-to-be. Just thinking forward here... For instance, amavisd-new (antispam system) now uses BYTEA instead VARCHAR and TEXT for all email-headers since email-headers are all defined to be 7-bit ASCII and hence BYTEA is more ideologically correct (Email with headers that are not 7-bit ASCII are breaking the RFCs). This means that a frontend to amavisd-new (and potentially any system that stores email in SQL) written in Django would have to be able to dump the contents of a BYTEA-field as text. I foresee that just uploads and downloads of binary data will lead to "how to edit binary data in a text-field in the admin" to become a FAQ.

[mspang]

What are the prospects of including this patch in 1.0? There are lots of applications that could use this. F.e., any app that stores a hash like SHA1 or a UUID would need this. Such apps may pass over django if 1.0 doesn't allow this. Using base64 is tedious, and you can't even work around that using a custom field because there's no distinction between the "load field from database" and "set field by user" operations in a Field. You would never know in to_python() whether you were receiving a base64 string or a binary string.

[Malcolm Tredinnick]

@mspang: Ticket comments are not the place to ask when something will be included. That doesn't help move it towards resolution.

@hanne.moa: Being able to treat binary fields as text transparently isn't a design requirement for this ticket. If you want to do that and don't want to just do the buffer -> string conversion you can subclass the eventual binary field class, or you can easily enough write your own class. Django doesn't provide every possible db column type to Python type and that's intentional. We aim to hit the common and most natural uses (and storing text in a database naturally uses a text field, not a binary field) and allow people to write their own field subclasses for extra cases.

[alex@…]

latest version of the patch (2008.07.28) - fix for MySQL converters

[Alex Koval]

patch against latest trunk (tested to work on 8960...9002)

[ConradIrwin]

patch against r9679

[ConradIrwin]

patch.3.txt is not an improvement over patch.2.txt, just the context of one of the changes had changed.

It would be really great if this could become part of django by default.

[dodgyville@…]

I am trying to store the output from a RSA encryption call in my
django database ... about 200 bytes I believe. I think BinaryField is
exactly what I am looking for. I applied the patch3 against r10175 and it
works.

Unfortunately it does appears to function a bit differently on sqlite
(local) v. mysql (remote).

On my local machine, the value of the field is as expected, but on the
remote machine, type(obj.cipher) gives me a <type 'buffer'> when
accessing the attribute which I don't know what to do with.

Is this the expected behaviour?

[avk]

Replying to dodgyville@gmail.com:

Is this the expected behaviour?

I agree that for a consistency reasons there should be always one type returned, probably 'buffer',
since PostgreSQL developers mention that it is more efficient in this use case. I will try to fix
it for all databases again so we will have consistent behavior.

Meanwhile, I would like to mention that from usage standpoint there is no big difference between two types:
str can be converted to buffer, and buffer can be converted to 'str' when required, using functions str(buffer)
or buffer(str).

[Simon Law]

Much like TextField uses LONGTEXT in MySQL, BlobField should use LONGBLOB.

[funkycoder@…]

So… is there a friendly way to get this to work in 1.3.x? I don't really want to apply years old patches. Could this maybe be moved to trunk and just left undocumented while there are quirks between backends?

[TTimo]

Pretty please?

[Claude Paroz]

I recently worked on this feature, and put the result in a pull request:
​https://github.com/django/django/pull/597

Note that I choose to add only one type of field, as only MySQL has different sized blob fields, and I don't think the few spared size bytes are worth the trouble the creation of a dedicated field.

I didn't test at all on Oracle.
I'd love to get some feedback about this. I can imagine it does not cover all of the possible use cases, but hopefully it covers the basic ones. We can always improved things later.

[Claude Paroz]

Just updated the pull request after Alex Gaynor's review.

[Florian Apolloner]

As noted in the pull request: looks good to me, let's ship it now so people actually try using it and fix bugs as they come up.

[Claude Paroz]

Fixed with commits:

• 8ee1eddb7e148de89aebde9e68da495633fc1ec9 Add a BinaryField model field
• d680a3f4477056c69629b0421db4bb254b8c69d0 Added support for serializing BinaryField
• c5a25c2771f0362a5775f81f9f4eb6b3c9caed62 Add introspection support for BinaryField

[Sławek Ehlert]

This is awesome! I can provide some Oracle testing if it's still needed. Though, I probably would need some guidance about performing those tests.