0

I have a PHP function to create those famouse AlphaID representations of integers as follows:

function alphaID( $input ) {

    $index = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    $base    = strlen( $index );
    $input  += pow( $base, 4 );
    $output  = '';

    for( $i = floor( log( $input, $base ) ); $i >= 0; $i-- ) {

        $bcp     = bcpow( $base, $i );
        $start   = floor( $input / $bcp ) % $base;
        $output .= substr( $index, $start, 1 );
        $input   = $input - ( $start * $bcp );
    }

    return $output;
}

Encoding, for example, the maximum integer provided by PHP_MAX_INT constant (2147483647) would return cwuCBb

But I thought it was a little too slow in the whole application picture so I tried to create a MYSQL function so, theoretically, I wouldn't waste performance doing such conversion with PHP once data would come ready for me while querying.

The MySQL manual is not exactly friendly, but searching here and there I came up to this:

DROP FUNCTION IF EXISTS ENCODE_ALPHAID;

DELIMITER $$

CREATE FUNCTION ENCODE_ALPHAID( input integer ) RETURNS CHAR( 6 ) DETERMINISTIC

BEGIN

    DECLARE output CHAR( 6 );

    DECLARE letters CHAR( 62 );
    DECLARE base TINYINT( 2 );

    DECLARE iterator TINYINT( 2 );
    DECLARE bcp CHAR( 9 );
    DECLARE start TINYINT( 2 );

    SET output   = '';

    SET letters  = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    SET base     = CHAR_LENGTH( letters );
    SET input    = input + POW( base, 4 );

    SET iterator = FLOOR( LOG( base, input ) );

    ENCODING: LOOP

        SET bcp      = POW( base, iterator );
        SET start    = FLOOR( input / bcp ) % base + 1;

        SET output   = CONCAT( output, SUBSTR( letters, start, 1 ) );

        SET input    = input - ( start * bcp );

        SET iterator = iterator-1;

        IF iterator < 0 THEN LEAVE ENCODING; END IF;

    END LOOP ENCODING;

    RETURN output;

END $$

DELIMITER ;

But when I ran it through the MySQL manager I use (DBNinja), apparently, nothing happens. After run the query it shows 0 row affected and I'm not sure if this is the expected output or not.

Right or not, the function didn't work either way because when I tried to use it in a query:

SELECT ENCODE_ALPHAID( `c`.`cid` ) from `table` c WHERE `c.user` = 1

I first received an error saying table.ENCODE_ALPHAID didn't exist. Then I noticed that, indeed, I created the function while in a different database of the one I was trying to use.

But I created it again, in the proper database, but the same error came up.

So here are the questions:

  1. What did I do wrong? This is the very first time I try to do something like that and I'm not exactly sure
  2. My implementation of the PHP code is a valid MySQL counterpart? Did I miss anything?
  3. Can I make this function "global"? I mean, available to any database I need it? Because for the application I'm working on, in order to keep the two subsystems involved separated, I created two databases.

Updated

Apparently the problem with the function not being created is on DBNinja's end, because I ran the statement directly in MySQL console and, although with the same output status of 0 rows affected, I was able to effectively use the function in a statement, so the function was created afterall.

However, something is wrong with the routine itself because when I tested it, the resulting string was... "one ahead".

For example, if I run the following PHP code:

echo alphaID( 2 );

It would produce baaac, but the MySQL function is returning baaad, which would be the AlphaID representation of the integer 3

Not only this is wrong, but I think it could also overflow the INT type used when reaching the maximum 2147483647

  • Are you sure you 're not on the wrong DB/Table as you said earlier – Peter Chaula Jul 11 '16 at 20:08
  • Any reason you're doing this in MySQL instead of PHP? MySQL's scripting language is a lot weaker than PHP and usually *considerably* slower when executing complicated functions. What measurable performance problem are you having with PHP? How many millions of times is that function being called per hour? – tadman Jul 11 '16 at 20:13
  • Positive, but I doubled checked right now. The full query has a JOIN to a table in the second database, but even running the CREATE FUNCTION query in both databases, the error remains. The reason is, like I briefly said, is to maximize performance. Specially in the case of this application that this function doesn't simply "exist" but it's part a much more complex protoypical data-type structure. Having such information coming ready from database would be better. –  Jul 11 '16 at 20:17
  • When people talk about "maximizing performance" without specifying what "acceptable performance is" then I'm often highly suspicious that this is necessary and have a sense that this is *premature optimization*. That function looks fine. It could be cleaned up on an algorithmic level which would make it execute more quickly, but unless you're hammering this thing millions of times a minute I doubt that would have an measurable impact on performance. – tadman Jul 11 '16 at 20:20
  • For an example of an algorithmic-level improvement, look at these answers about [base62 encoding in PHP](http://stackoverflow.com/questions/4964197/converting-a-number-base-10-to-base-62-a-za-z0-9) where some of the solutions are very simple and yet are almost identical to your example. – tadman Jul 11 '16 at 20:22
  • Do you have insert priviledges – Peter Chaula Jul 11 '16 at 20:24
  • yes, Laser, I do have insert permission. As a matter of fact, while developing locally, I stick to root access –  Jul 11 '16 at 20:34
  • Ok. I am trying out on my dev server – Peter Chaula Jul 11 '16 at 20:37
  • Topic updated with more informations. As a "bonus", if anyone can think outside the box, the real requirement for the project, more than just mask the PRIMARY KEY, is to have a unique textual representation of the ID and an *AlphaID* was the best I had in hands at the moment. –  Jul 14 '16 at 17:30

2 Answers2

0

Bruno, from MySQL version you are missing:

  1. $input += pow( $base, 4 );
  2. SUBSTR in PHP starts from 0, in MySQL it starts from 1.
  3. The loop ending condition should be:

    IF iterator < 0 THEN LEAVE ENCODING; END IF;

  4. When using CONCAT, make sure you do not CONCAT NULL's (you should initialize parameters)

Routines are always created in one database. You can call them in any of them (if you have privileges) by including the database name in the query:

call mydb.ENCODE_ALPHAID( 2147483647 );

Also, when declaring variables, you might want to use prefixes so that the variables do not get mixed up with possible column names / reserved words. For example instead of "output" use "v_output".

slaakso
  • 8,331
  • 2
  • 16
  • 27
  • Also: `index` is a reserved keyword. And a call using ` c.ccid ` (with everything in ``) will not work either. – Solarflare Jul 11 '16 at 21:15
  • I'll try this tomorrow but in this meanwhile, how would I quote a column defined with a reserved keyword then? In such cases, would I be "forced" to name a column with differently (and maybe with a lesser meaningfulness)? –  Jul 11 '16 at 21:18
  • @Bruno Augusto I meant: you cannot quote everything at once, you have to use quote around the single names, e.g. c.` ccid ` (without the spaces, they are just there to prevent displaying it as code in the comments). And it's a good idea to not use space or reserved keywords anyway... – Solarflare Jul 11 '16 at 21:25
  • Now that I read again the code posted I understood you. I'm aware that the quoting must be done before the dot and after it `(\`c\`.\`cid\`)`. It was just a typo. Post updated with new informations –  Jul 14 '16 at 13:26
0

Seems like you were missing END IF; Do not use reserved words for variables. If you want to go the evil way make sure you quote them .ie ` table``, . Note the back tick - not single quotes. On my machine this code returns null:

CREATE DEFINER=`root`@`localhost` FUNCTION `ENCODE_ALPHAID`(`input` INT)
RETURNS CHAR(6) DETERMINISTIC NO SQL SQL SECURITY DEFINER 
BEGIN     
DECLARE output CHAR(6);
DECLARE `index` CHAR(62); 
DECLARE base TINYINT(2); 
DECLARE iterator TINYINT( 2 );
DECLARE bcp CHAR( 9 ); 
DECLARE start TINYINT( 2 ); SET `index` = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
SET base = CHAR_LENGTH( `index` ); 
SET iterator = FLOOR( LOG( base, input ) ); 
ENCODING: LOOP
SET bcp = POW( base, iterator ); 
SET start = FLOOR( input / bcp ) % base;
SET output = CONCAT( output, SUBSTR( `index`, start, 1 ) ); 
SET input = input - ( start * bcp );
SET iterator = iterator-1;
IF iterator >= 0 THEN LEAVE ENCODING; 
END IF;
   END LOOP ENCODING; 
   RETURN output; 
END;

enter image description here

PS: On the back tick use one. I have no idea how to escape markdown. And eaid is the shorter name I was using

Peter Chaula
  • 3,456
  • 2
  • 28
  • 32