[RELEASE] White Stats Decoder (SQL ONLY)

[Syloxx]

Hello Community,

I`ve decided to release my SQL based white stats decoder.

This task has been completed multiple times via php and / or c# but never via SQL (at lease not that I am aware of)

I personally don't have any use cases however maybe you guys have an Idea what this could be used for.

SET NOCOUNT ON;

DECLARE @Variance BIGINT      = 34359738367
      , @Type     VARCHAR(16) = 'WEAPON'
      , @Counter  INT         = 0;

DECLARE @tmpTable TABLE (ID INT IDENTITY(1, 1), Value INT);
DECLARE @resultTable TABLE (ID INT IDENTITY(1, 1), Description VARCHAR(128), Value INT);

WHILE @Variance > 0
BEGIN
    INSERT @tmpTable (Value)
    VALUES ((@Variance & 0x1F) * 100 / 31);
    SET @Counter = 5;

    WHILE @Counter <> 0
    BEGIN
        SET @Variance = (@Variance - (@Variance & 1)) / 2;
        SET @Counter -= 1;
    END;
END;

IF @Type = 'WEAPON'
BEGIN
    IF (SELECT COUNT(*)FROM @tmpTable) <> 7
    BEGIN
        PRINT ('INVALID TYPE!');
        RETURN;
    END;

    INSERT @resultTable (Description, Value)
    VALUES ('Physitcal Damage', (SELECT Value FROM @tmpTable WHERE ID = 5))
         , ('Magical Damage', (SELECT Value FROM @tmpTable WHERE ID = 6))
         , ('Durability', (SELECT Value FROM @tmpTable WHERE ID = 1))
         , ('Hit Ratio', (SELECT Value FROM @tmpTable WHERE ID = 4))
         , ('Critical', (SELECT Value FROM @tmpTable WHERE ID = 7))
         , ('Physitcal Reinforce', (SELECT Value FROM @tmpTable WHERE ID = 2))
         , ('Magical Reinforce', (SELECT Value FROM @tmpTable WHERE ID = 3));
END;
ELSE IF @Type = 'SHIELD'
BEGIN
    IF (SELECT COUNT(*)FROM @tmpTable) <> 6
    BEGIN
        PRINT ('INVALID TYPE!');
        RETURN;
    END;

    INSERT @resultTable (Description, Value)
    VALUES ('Physitcal Defensive', (SELECT Value FROM @tmpTable WHERE ID = 5))
         , ('Magical Defensive', (SELECT Value FROM @tmpTable WHERE ID = 6))
         , ('Durability', (SELECT Value FROM @tmpTable WHERE ID = 1))
         , ('Block Ratio', (SELECT Value FROM @tmpTable WHERE ID = 4))
         , ('Physitcal Reinforce', (SELECT Value FROM @tmpTable WHERE ID = 2))
         , ('Magical Reinforce', (SELECT Value FROM @tmpTable WHERE ID = 3));
END;
ELSE IF @Type = 'EQUIPMENT'
BEGIN
    IF (SELECT COUNT(*)FROM @tmpTable) <> 6
    BEGIN
        PRINT ('INVALID TYPE!');
        RETURN;
    END;

    INSERT @resultTable (Description, Value)
    VALUES ('Physitcal Defensive', (SELECT Value FROM @tmpTable WHERE ID = 4))
         , ('Magical Defensive', (SELECT Value FROM @tmpTable WHERE ID = 5))
         , ('Durability', (SELECT Value FROM @tmpTable WHERE ID = 1))
         , ('Parry Ratio', (SELECT Value FROM @tmpTable WHERE ID = 6))
         , ('Physitcal Reinforce', (SELECT Value FROM @tmpTable WHERE ID = 2))
         , ('Magical Reinforce', (SELECT Value FROM @tmpTable WHERE ID = 3));
END;
ELSE IF @Type = 'ACCESSORY'
BEGIN
    IF (SELECT COUNT(*)FROM @tmpTable) <> 2
    BEGIN
        PRINT ('INVALID TYPE!');
        RETURN;
    END;

    INSERT @resultTable (Description, Value)
    VALUES ('Physitcal Absorption', (SELECT Value FROM @tmpTable WHERE ID = 1))
         , ('Magical Absorption', (SELECT Value FROM @tmpTable WHERE ID = 2));
END;

SELECT Description
     , Value
FROM @resultTable
ORDER BY ID;

-Syloxx

[JustNrik]

Since no explanation at all was given, I will add it.

Variance is pretty simple, it's an int64 with 7 flags (occupies upto 35 bits), each flag is 5 bit and represents a value from 0 to 31, 0 being 0% and 31 being 100%

This order is for Little-endian.

first 5 bits: Durability (wep, prot, shield) | Phy. Abs. (acc)
second 5 bits: Phy. Reinforcement (eu str wep, ch wep, prot, shield) | Mag. Abs. (acc)
third 5 bits: Mag. Reinforcement (eu int wep, ch wep, prot, shield)
forth 5 bits: Hit Ratio (wep) | Parry Ratio (prot)
fifth 5 bits: Phy. Power (eu str wep, ch wep) | Phy. Def. (prot, shield)
sixth 5 bits: Mag. Power (eu int wep, ch wep) | Mag. Def. (prot, shield)
seventh 5 bits: Critical (eu str wep, ch wep) | Block Ratio (shield)

If you want to set all values to a specific %, take this value 1108378657 and multiply it for any number between 0 and 31.

0 -> 0%
7 -> 22%
13 -> 41%
19 -> 61%
25 -> 80%
31 -> 100%

If you want to set a specific value, then or-it with the flag mask. Example, let's say you want to set 41% stats but for whatever reason you want 61% parry/hit rate. This is the formula (pseudo-code):

(1108378657 * 13) | (19 << (5 * 4))

1108378657 is every flag set to 1 (binary 00001-00001-00001-00001-00001-00001-00001)
* 13 makes everything 41% (binary 01101-01101-01101-01101-01101-01101-01101)
19 is the 61% stat (binary 11101)
5 is the bits length
* 4 is the position for hit rate, which is the 4th 5 bits.
5 * 4 = 20 so it will be rotated 20 bits to the left.
19 << 20 = binary 00000-00000-10011-00000-00000-00000-00000
(1108378657 * 13) | (19 << 20)
01101-01101-01101-01101-01101-01101-01101
00000-00000-10011-00000-00000-00000-00000 OR
--------------------------------------------------------------
01101-01101-11101-01101-01101-01101-01101

And there you have, I hope this explanation serves to people to know how it works.

[kotsh23]

Thank you Syloxx you great
can you please help us with blue too

[Ezhel]

Can someone tell me what rates White Stats got on official servers ?

How high is the percentage to hit 22% 44% 61% 80% and 100% couldnt find any info on the web.

[Syloxx]

Originally Posted by kotsh23

Thank you Syloxx you great
can you please help us with blue too

Value = MagParam / 4294967296 (example 7 STR will return 7)
Magic ID = MagParam % 2147483647 (ID from _RefMagicOpt)

[kotsh23]

Hello Syloxx

i tried

DECLARE @MagParam BIGINT = 7301444403647
SELECT FLOOR(@MagParam / 4294967296) as BlueValue , @MagParam % 2147483647 as MagicID

but its return magicID not found in my database

[Otakanikaru]

Thanks @Syloxx I'll mix it with this: