FAQ
Hi,

during review of SQL::Statement wrt. RT#100551 I realized that the introduced SQL_FUNCTION_CONV (https://github.com/perl5-dbi/SQL-Statement/blob/master/lib/SQL/Statement/Functions.pm#L454) is unnecessary over-complex.

Since I learned that silent removal of once shipped functionality is a evil - I'd like to get some suggestions.
For existing implementation I miss a proper documentation of edge-cases:
* How is 1.E converted to octal and why is that necessary in SQL?
* Why do the alphabets lower 63 and 63+ based number differ?
* If different encoding schemes are supported either, why is xnt and b85 missing and what's about plain ascii?

From my perspective - unless proper answers are given - the implementation of SQL_FUNCTION_CONV should be replaced by an easy to use wrapper around https://metacpan.org/release/Math-Base-Convert to rely on dedicated tested and approved.

I started a rough test whether it's possible to keep the existing API and run into different results for 1st chosen test
         {
            test => 'conv 2->92',
            sql => "SELECT CONV('101 0100 1111 0111 0110 1011', 2, 92)",
            result => [ ['HN(/'] ],
         },
Mike's module resulted 'HN)/' - so there seem to be more quirks in individual S::S solution.

Any comments?

Cheers
--
Jens Rehsack
rehsack@gmail.com

Search Discussions

  • Darren Duncan at Dec 15, 2014 at 9:54 am

    On 2014-12-15 1:40 AM, Jens Rehsack wrote:
    during review of SQL::Statement wrt. RT#100551 I realized that the introduced SQL_FUNCTION_CONV (https://github.com/perl5-dbi/SQL-Statement/blob/master/lib/SQL/Statement/Functions.pm#L454) is unnecessary over-complex.
    Funny, when I first saw that sentence I had thought you added support for
    parsing SQL stored procedures/functions. Oh well. -- Darren Duncan
  • Jens Rehsack at Dec 17, 2014 at 9:28 am

    Am 15.12.2014 um 10:54 schrieb Darren Duncan <darren@darrenduncan.net>:
    On 2014-12-15 1:40 AM, Jens Rehsack wrote:
    during review of SQL::Statement wrt. RT#100551 I realized that the introduced SQL_FUNCTION_CONV (https://github.com/perl5-dbi/SQL-Statement/blob/master/lib/SQL/Statement/Functions.pm#L454) is unnecessary over-complex.
    Funny, when I first saw that sentence I had thought you added support for parsing SQL stored procedures/functions. Oh well. -- Darren Duncan
    Beside of that - no other statement, comment, ...?
    --
    Jens Rehsack
    rehsack@gmail.com
  • Harryfmudd at Dec 17, 2014 at 3:31 pm
    Hi Jens,

    Thank you for trying to deal with this mess, whatever happens. The RT ticket https://rt.cpan.org/Ticket/Display.html?id=100551 was not based on any deep knowledge of SQL, its standards, and its implementations, but just on the observation that the hex representation of 16 (base 10) was not "1".

    I am not sure what I would do if I were trying to address this issue. On the one hand, letting someone like Math::Base::Convert deal with the problem is a very tempting solution. On the other hand, this may involve a change in functionality.

    I have had trouble getting Google's link to the SQL-92 standard to display, but other search results seem to say that CONV is not a standard function. There _is_ a standard function called CONVERT, but its signature is completely different.

    The rest is my response to your questions. Please understand that in these responses I speak only for myself. I have no idea what other people might need.
    ----- Original Message -----
    Hi,

    during review of SQL::Statement wrt. RT#100551 I realized that the introduced
    SQL_FUNCTION_CONV
    (https://github.com/perl5-dbi/SQL-Statement/blob/master/lib/SQL/Statement/Functions.pm#L454)
    is unnecessary over-complex.

    Since I learned that silent removal of once shipped functionality is a evil -
    I'd like to get some suggestions.
    For existing implementation I miss a proper documentation of edge-cases:
    * How is 1.E converted to octal and why is that necessary in SQL?
    I no longer know exactly why I needed this kind of functionality, but I do remember I was only interested in converting integers. I suppose the octal representation of 1.5 would be 1.4. I know of no scientific notation for any number in any base other than 10.
    * Why do the alphabets lower 63 and 63+ based number differ?
    The documentation SQL::Statement::Functions says (or at least implies) that the change is so that conversion to base 64 is compatible with MIME Base-64 encoding.
    * If different encoding schemes are supported either, why is xnt and b85
    missing and what's about plain ascii?
    I do not know.
    From my perspective - unless proper answers are given - the implementation of
    SQL_FUNCTION_CONV should be replaced by an easy to use wrapper around
    https://metacpan.org/release/Math-Base-Convert to rely on dedicated tested
    and approved.
    For my personal use I would be happy with this implementation. Maybe check for base 64 and use Mime::Base64 in that case. Maybe warn if you detect a case where the result changes. But I personally need neither of these.

    If it turns out that CONV is in fact non-standard, another way to go would be to simply deprecate and remove it, and replace it (if you decide to replace it at all) with functionality that does what you want.
    I started a rough test whether it's possible to keep the existing API and run
    into different results for 1st chosen test
    {
    test => 'conv 2->92',
    sql => "SELECT CONV('101 0100 1111 0111 0110 1011', 2, 92)",
    result => [ ['HN(/'] ],
    },
    Mike's module resulted 'HN)/' - so there seem to be more quirks in individual
    S::S solution.

    Any comments?

    Cheers
    --
    Jens Rehsack
    rehsack@gmail.com
    Thank you again for your work on this.

    Tom
  • Brendan Byrd at Feb 5, 2015 at 2:37 pm
    Sorry for my very late reply.

    After I wrote the CONV function, I did take much of the code and improved
    Math::BaseCalc with it. Although, that patch is still stuck in review (
    https://github.com/kenahoo/perl-math-basecalc/pull/2).

    So, I don't mind if the code was stripped out to use a separate module for
    the conversions. I agree that it's overly complex, since most of these
    functions should really just be calls to modules or relatively short subs.

    The CONV function itself wasn't based on SQL-92, but since functions like
    HEX/BIN/OCT were already needed, I wrote it for those. The name was based
    on the MySQL function.
    On Mon, Dec 15, 2014 at 4:40 AM, Jens Rehsack wrote:

    Hi,

    during review of SQL::Statement wrt. RT#100551 I realized that the
    introduced SQL_FUNCTION_CONV (
    https://github.com/perl5-dbi/SQL-Statement/blob/master/lib/SQL/Statement/Functions.pm#L454)
    is unnecessary over-complex.

    Since I learned that silent removal of once shipped functionality is a
    evil - I'd like to get some suggestions.
    For existing implementation I miss a proper documentation of edge-cases:
    * How is 1.E converted to octal and why is that necessary in SQL?
    * Why do the alphabets lower 63 and 63+ based number differ?
    * If different encoding schemes are supported either, why is xnt and b85
    missing and what's about plain ascii?

    From my perspective - unless proper answers are given - the implementation
    of SQL_FUNCTION_CONV should be replaced by an easy to use wrapper around
    https://metacpan.org/release/Math-Base-Convert to rely on dedicated
    tested and approved.

    I started a rough test whether it's possible to keep the existing API and
    run into different results for 1st chosen test
    {
    test => 'conv 2->92',
    sql => "SELECT CONV('101 0100 1111 0111 0110 1011', 2, 92)",
    result => [ ['HN(/'] ],
    },
    Mike's module resulted 'HN)/' - so there seem to be more quirks in
    individual S::S solution.

    Any comments?

    Cheers
    --
    Jens Rehsack
    rehsack@gmail.com

    --
    Brendan Byrd <perl@resonatorsoft.org>
    Brendan Byrd <bbyrd@cpan.org>
  • Jens Rehsack at Feb 5, 2015 at 5:11 pm
    Hi Michael,

    I think I will do. Math::Base::Convert with convert for up-to-base64
    seems to be fine, whatever MySQL extended - MySQL isn't a standard
    I orientate myself or my software ;)

    Cheers,
    Jens

    Am 05.02.2015 um 18:07 schrieb michael@insulin-pumpers.org:
    Take a look at Math::Base::Convert, maybe that will be helpful. It
    handles VERY large integer numbers efficently instead of returning
    floats, has a larger conversion set, including the missing bases Jens
    asked about, and can be easily expanded to any base. You don't have
    to re-invent the wheel.

    Michael
    Sorry for my very late reply.

    After I wrote the CONV function, I did take much of the code and
    improved Math::BaseCalc with it. Although, that patch is still stuck
    in review ( https://github.com/kenahoo/perl-math-basecalc/pull/2).

    So, I don't mind if the code was stripped out to use a separate module
    for the conversions. I agree that it's overly complex, since most of
    these functions should really just be calls to modules or relatively
    short subs.

    The CONV function itself wasn't based on SQL-92, but since functions
    like HEX/BIN/OCT were already needed, I wrote it for those. The name
    was based on the MySQL function.

    On Mon, Dec 15, 2014 at 4:40 AM, Jens Rehsack <rehsack@gmail.com>
    wrote:
    Hi,

    during review of SQL::Statement wrt. RT#100551 I realized that the
    introduced SQL_FUNCTION_CONV (
    https://github.com/perl5-dbi/SQL-Statement/blob/master/lib/SQL/State
    ment/Functions.pm#L454) is unnecessary over-complex.

    Since I learned that silent removal of once shipped functionality is
    a evil - I'd like to get some suggestions. For existing
    implementation I miss a proper documentation of edge-cases: * How is
    1.E converted to octal and why is that necessary in SQL? * Why do
    the alphabets lower 63 and 63+ based number differ? * If different
    encoding schemes are supported either, why is xnt and b85 missing
    and what's about plain ascii?

    From my perspective - unless proper answers are given - the
    implementation of SQL_FUNCTION_CONV should be replaced by an easy to
    use wrapper around https://metacpan.org/release/Math-Base-Convert to
    rely on dedicated tested and approved.

    I started a rough test whether it's possible to keep the existing
    API and run into different results for 1st chosen test
    {
    test => 'conv 2->92',
    sql => "SELECT CONV('101 0100 1111 0111 0110 1011',
    2, 92)", result => [ ['HN(/'] ],
    },
    Mike's module resulted 'HN)/' - so there seem to be more quirks in
    individual S::S solution.

    Any comments?

    Cheers
    --
    Jens Rehsack
    rehsack@gmail.com

    --
    Brendan Byrd <perl@resonatorsoft.org>
    Brendan Byrd <bbyrd@cpan.org>
    --
    Jens Rehsack
    rehsack@gmail.com
  • Peter Rabbitson at Feb 5, 2015 at 5:14 pm

    On 02/05/2015 06:11 PM, Jens Rehsack wrote:
    Hi Michael,

    I think I will do. Math::Base::Convert with convert for up-to-base64
    seems to be fine
    I still think providing an arbitrary "up to base64" conversion is a
    mistake. Consider the standardized base32 alphabet:
    http://en.wikipedia.org/wiki/Base32
  • Jens Rehsack at Feb 6, 2015 at 7:52 am

    Am 05.02.2015 um 18:14 schrieb Peter Rabbitson <rabbit@rabbit.us>:
    On 02/05/2015 06:11 PM, Jens Rehsack wrote:
    Hi Michael,

    I think I will do. Math::Base::Convert with convert for up-to-base64
    seems to be fine
    I still think providing an arbitrary "up to base64" conversion is a mistake. Consider the standardized base32 alphabet:
    http://en.wikipedia.org/wiki/Base32
    I'm also fine with that, thanks for the suggestion.

    Cheers
    --
    Jens Rehsack
    rehsack@gmail.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-dev @
categoriesperl
postedDec 15, '14 at 9:40a
activeFeb 6, '15 at 7:52a
posts8
users5
websitedbi.perl.org

People

Translate

site design / logo © 2019 Grokbase