#12165: Ability to use SQL functions in queries
------------------------------------------+---------------------------------
Reporter: premalshah | Owner: nobody
Status: new | Milestone: 1.2
Component: Database layer (models, ORM) | Version: 1.0
Keywords: mysql functions | Stage: Unreviewed
Has_patch: 0 |
------------------------------------------+---------------------------------
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?
--
Ticket URL: <http://code.djangoproject.com/ticket/12165>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
