id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
12165	Ability to use SQL functions in queries	premalshah	nobody	"MySQL has text data type fields like TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT and binary data type fields like TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. Text fields have a character set, while binary fields dont. We store a lot of data in text fields. Recently, during a database audit, it was recommended by MySQL experts to compress the data we store in the text fields and move it into the binary fields since text fields cannot store compressed binary data. There are 2 ways of doing this.

 1. Create a CompressedTextField custom model field. This code works fine. There are other side effects though. 
  * MySQL query log now has binary characters. So, if you tailiing or catting it, its not a good experience. 
  * You are compressing/uncompressing on the web server. Most people have powerful database servers and less powerful web servers. So, it can hurt performance.

The code is as under.
{{{ 
import zlib

class CompressedField(Field):
    __metaclass__ = SubfieldBase

    def to_python(self, value):
        try:            
            value = zlib.decompress(value[4:])
        except (TypeError, zlib.error):
            return value

    def get_db_prep_value(self, value):
        if not value: return None
        c = zlib.compress(value)
        return buffer(struct.pack('I', len(value)) + c)

    def get_internal_type(self):
        return 'TextField'

    def db_type(self):
        return 'longblob'
}}}

 2. MySQL has compress and uncompress functions. http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html
{{{
COMPRESS(string_to_compress)
ex: INSERT INTO <table_name> (col1) values (COMPRESS(<string_to_compress>));
UNCOMPRESS(string_to_uncompress)
ex: SELECT UNCOMPRESS(<string_to_uncompress>) FROM <table_name>;
}}}

We can create a custom model field as under.
{{{
class BlobField(Field):
    __metaclass__ = SubfieldBase

    def get_internal_type(self):
        return 'TextField'

    def db_type(self):
        return 'longblob'
}}}

Then a field can be defined as
{{{
class Test(models.Model):
    blog = models.BlobField(input_function='compress', output_function='uncompress')
}}}

compress and uncompress are the mysql functions are wrapped around the text or the column name when django constructs the query. This is just one example of database functions that could be used by developers via the Django ORM. Someone might find use for Encrypt and Decrypt or Encode and Decode.

Any thoughts?"	New feature	closed	Database layer (models, ORM)	1.0	Normal	invalid	mysql functions		Design decision needed	0	0	0	0	0	0