List:Internals« Previous MessageNext Message »
From:Jan Steemann Date:January 29 2010 11:39am
Subject:RE: Adding native base64 functions to MySQL server?
View as plain text  
Hi Alexander,

thanks. I will try to write up the patch in the next week or two.

Best regards
Jan

-----Original Message-----
From: Alexander.Barkov@stripped [mailto:Alexander.Barkov@stripped] 
Sent: Friday, January 29, 2010 11:41 AM
To: Jan Steemann
Cc: Marc.Alff@stripped; Guilhem Bichot; MySQL Internal
Subject: Re: Adding native base64 functions to MySQL server?

Hi Jan,

Alexander Barkov wrote:
> Hi Jan,
> 
> I think you can use DECODE() and ENCODE() as examples.
> The files you will need to edit are:
> 
> - sql/item_strfunc.h
>   Add two new classes:
>   Item_func_base64_decode
>   Item_func_base64_encode
> 
> - sql/item_strfunc.cc
>   Implement val_str() and fix_length_and_dec() for the above
>   two classes.
> 
> - sql/item_create.cc
>   * Add classes Create_func_base64_encode and
>   Create_func_base64_decode.
> 
>   * Add singleton instances of these two classes:
> 
>   Create_func_base64_decode Create_func_base64_decode::s_singleton;
>   Create_func_base64_encode Create_func_base64_encode::s_singleton;
> 
>   * Register them in the function list:
> 
>  { { C_STRING_WITH_LEN("BASE64_DECODE") }, 
> BUILDER(Create_func_base64_decode)},
>  { { C_STRING_WITH_LEN("BASE64_ENCODE") }, 
> BUILDER(Create_func_base64_encode)},
> 
> 
> I think no other changes are needed.
> 
> The trickiest part will be implementation of the val_str() method.
> 
> I have a related question.
> 
> It's clear what to with with string and binary types, like
> VARCHAR, CHAR, TEXT, VARBINARY, BINARY, BLOB. You just execute
> val_str() method for the argument and pass the result to the actual
> base64_encode() C function.
> 
> It's not clear what to do in BASE64_ENCODE() the other data types, like 
> INT, TIME, DATETIME, ENUM, SET. Should we convert to text representation
> and then encode? Or should we encode the binary representation of the
> underlying data?

There's no way to insert data using internal binary representation.
So, perhaps encoding the text representation is better than
encoding the binary representation, for this "roundtrip" possibility:

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1);
SELECT @v:=base64_encode(a);
DELETE FROM t1;
INSERT INTO t1 base64_decode(@v);
SELECT * FROM t1;  -- this should return 1


That means you just always call val_str() for the argument,
no matter what the data type is.


> 
> 
> 
> Jan Steemann wrote:
>> Hi everybody,
>>
>> please find my answers below.
>>
>> Best regards
>> Jan
>>
>>
>> -----Original Message-----
>> From: Marc.Alff@stripped [mailto:Marc.Alff@Sun.COM] Sent: Thursday, 
>> January 28, 2010 10:17 PM
>> To: Guilhem Bichot
>> Cc: Jan Steemann; MySQL Internal
>> Subject: Re: Adding native base64 functions to MySQL server?
>>
>>
>> Hi Guilhem, Jan
>>
>>
>> Guilhem Bichot wrote:
>>> Hello,
>>>
>>> Jan Steemann a écrit, Le 28.01.2010 10:26:
>>>> Hi everybody,
>>>>
>>>> the MySQL server currently does not expose any functions to base64
>>>> encode or decode values.
>>>> To actually use base64 encoding/decoding in queries, one has to use
>>>> stored functions.
>>>>
>>>> Stored functions have a few disadvantages compared to native functions:
>>>> - much slower than a native function: for base64 encoding, I once
>>>> tested a stored function implementation vs. a native implementation.
>>>> Unsurprisingly, the native function was around 90-100x faster than
>>>> the stored routine.
>>>> - possible deployment issues: need to deploy stored functions on all
>>>> servers, versioning issues if stored function code changes.
>>>>
>>>> There are a few C UDFs out there to add support for base64 encoding
>>>> and decoding functionality to MySQL. These UDFs solve the performance
>>>> issue but not the deployment issue.
>>>>
>>>> There, I think it would be useful to include native base64 encoding
>>>> and decoding functions into standard MySQL in one of the future
>>>> releases.
>>>>
>>>> There is also a pending feature request for this:
>>>> http://bugs.mysql.com/bug.php?id=18861
>>>>
>>>> If native base64_encode() and base64_decode() functions would be
>>>> added to MySQL, this would cause the same potential naming conflicts
>>>> as when adding any other functions. If installations have already
>>>> implemented their own versions of base64_encode() and base64_decode()
>>>> via stored functions, there will be a naming conflict. It would need
>>>> to be defined what should happen in this situation.
>>>> However, as base64_encode and decode are fully deterministic and the
>>>> functions are fully standardized (i.e. all versions of base64_encode
>>>> and decode around should produce the same output for a given input),
>>>> I think people will be willing to drop their own implementations of
>>>> base64 if it would be included natively.
>>>>
>>>> What do you think?
>>>> If you like the suggestion and think it has a valid chance to be
>>>> included into a future release, I will be happy to supply the
>>>> function code.
>>> So I submitted the idea internally, and there are topics for discussion.
>>>
>>> 1) Thanks a lot for offering to send a patch!
>>
>> The place to start will be sql/item_create.cc
>> The parser (sql/sql_yacc.yy) should be un changed for this.
>>
>>
>> Will look into this. I think it is "only" about adding two functions 
>> in sql/item_create.cc, probably also sql/item_strfunc.*.
>> I see no need to change the parser or any other part of the server to 
>> add the functions.
>>
>>
>>> 2) In order to contribute a patch, there is a contributor agreement to
>>> sign, all info is here:
>>> http://forge.mysql.com/wiki/Sun_Contributor_Agreement . Could you
>>> please look into this?
>>
>>
>> => I have signed that & am listed in the signatories list.
>>
>>
>>> 3) There is always a maintenance cost for every function that we add
>>> to MySQL, so we need to ask ourselves whether we really need those
>>> functions. Especially given that most languages support them already.
>>> I mean, I can already do
>>> run_query( "INSERT INTO t VALUES('" + f('x') + "')" )
>>> where f() is the function which does base64-encoding in my scripting
>>> language's library: here are three examples
>>> http://docs.python.org/library/base64.html
>>> http://search.cpan.org/~gaas/MIME-Base64-3.09/Base64.pm
>>> http://php.net/manual/en/function.base64-decode.php
>>> What gain would it bring to support those functions directly in SQL?
>>
>> Make them usable from stored procedures, stored functions, triggers, and
>> events.
>>
>> => plus make them available in bulk operations as INSERT ... SELECT, 
>> SELECTS or UPDATEs with high selectivity where base64 functionality in 
>> a programming language doesn't help much.
>>
>>
>>> 4) Assuming it's a good idea to implement that, there are details like
>>> what the output type should be (BLOB, TEXT, etc), I'll collect more
>>> info from specialists and get back to you.
>>
>> => Thanks for collecting these information. In the meantime, here are 
>> my suggestions: The output value of base64_decode() should be treated 
>> as binary because the output may contain arbitrary values (including 
>> non-printable characters \0 etc., furthermore, base64_decode does not 
>> know if and which input character set was used when the data was 
>> base64 encoded). NULL may be used for sequences that cannot be 
>> properly decoded.
>> The output of base64_encode() is a string in the ASCII range, however, 
>> I think it is no harm to treat it as binary as well.
>>
>>
>> Regards,
>> -- Marc
>>
> 
> 

Thread
Adding native base64 functions to MySQL server?Jan Steemann28 Jan
  • Re: Adding native base64 functions to MySQL server?Guilhem Bichot28 Jan
    • RE: Adding native base64 functions to MySQL server?Jan Steemann28 Jan
  • Re: Adding native base64 functions to MySQL server?Guilhem Bichot28 Jan
    • Re: Adding native base64 functions to MySQL server?Marc Alff28 Jan
      • RE: Adding native base64 functions to MySQL server?Jan Steemann29 Jan
        • Re: Adding native base64 functions to MySQL server?Alexander Barkov29 Jan
          • RE: Adding native base64 functions to MySQL server?Jan Steemann29 Jan
          • Re: Adding native base64 functions to MySQL server?Alexander Barkov29 Jan
            • RE: Adding native base64 functions to MySQL server?Jan Steemann29 Jan