SQL tables with auto-translate strings for web tools

[bluekirby0]

Maybe someone can find another use for them, but I just finished putting together a list of tables for parsing auto-translate strings. This should be particularly useful for web tools such as roster scripts with bazaar comments.

Basically the idea is you detect auto-translated text by searching for 0xFD characters. It will then always follow this pattern:

0xFD XX YY ZZ AA 0xFD

where 0xFD are literals.

XX is the type, and will usually be 0x02. If this is the case, you will be using the auto_translate_strings table.
YY is a language code and can be safely ignored.
ZZ is the category you should use in your query
AA is the index you should use in your query

So just replace that sequence with the string retrieved from your query and voila!

The pattern is a little different if XX is not 02.

If it is 0x07 then it is an item. If it is 0x13 then it is a key item.
In either case, ZZ AA together become the id you use in your query. You will query auto_translate_items for 0x07 and auto_translate_keyitems for 0x13.

[PrBlahBlahtson]

Abusing these for a little project. Wanted to let you know that zone names have been moved around. Gotta check them all, so I'll update once they're found.

Code: Select all

   if (string.byte(zoneID,1) == 0xFD and string.byte(zoneID,2) == 2 and string.byte(zoneID,4) == 0x14) then -- Autotranslate string found
      if (string.len(zoneID) > 6) then
         print("Long string! Length: "..string.len(zoneID));
      end
      for i = 5, string.len(zoneID)-1 do
         byte = string.format("%x",string.byte(zoneID,i));
         if (string.len(byte) == 1) then
            byte = 0 ..byte;
         end
         test = test..byte;
      end
      print(test);
   end

[bluekirby0]

Forgot I even did this...might have a script somewhere to regenerate these...if not I should make one.

[PrBlahBlahtson]

Devi Farms remembered. And then Dingo made me look up how to muck with Hex in Lua. Zones I was able to find below. I imagine some came up truncated because of string escaping and such.

http://code.google.com/p/onetimexi/sour ... 510&r=3510

[atom0s]

Using this in php:

Code: Select all

<?php

    /**
     * @brief Queries the database for the given auto-translate information. 
     */
    function query_autotranslate($type, $cat, $index)
    {
        $sql = "";
        if ($type == 0x02)
            $sql = "SELECT `value` FROM auto_translate_strings WHERE `category` = '{$cat}' AND `index` = '{$index}';";
        else if ($type == 0x07)
            $sql = "SELECT `string` FROM auto_translate_items WHERE `id` = '{$index}';";
        
        // Do nothing if we have no query..
        if ($sql == "")
            return NULL;
        
        // Query for the value..
        $db = mysqli_connect( '127.0.0.1', 'root', 'root' ) or die($db->error());
        $db->select_db( 'dspdb' );
        $res = $db->query( $sql ) or die($db->error);
        $row = $res->fetch_assoc();
        $db->close();
        
        // Return the value..
        return ($type == 0x02) ? $row['value'] : $row['string'];
    }
    
    /**
     * @brief Parses the given string for auto-translate tags and replaces them.
     */
    function parse_autotranslate($str)
    {
        // Locate the start of an auto-translate message..
        $pos = stripos($str, chr(0xFD));
        if ($pos === NULL)
            return $str;
        
        // Pull the auto-translate array..
        $trans = unpack("C*", substr($str, $pos, 6));

        // Are we a string translation..
        if ($trans[2] == 0x02)
        {
            $cat    = $trans[4];
            $index  = $trans[5];
            $str = str_replace(substr($str, $pos, 6), "{" . query_autotranslate(0x02, $cat, $index) . "}", $str);
            return parse_autotranslate($str);
        }
        
        // Are we an item translation..
        else if ($trans[2] == 0x07)
        {
            $item_id = implode(unpack("n", substr($str, $pos + 3, 2)));
            $str = str_replace(substr($str, $pos, 6), "{" . query_autotranslate(0x07, 0, $item_id) . "}", $str);
            return parse_autotranslate($str);
        }
        
        return $str;
    }
    
    $db = mysqli_connect( '127.0.0.1', 'root', 'root' ) or die('Failed to connect to database.');
    $db->select_db( 'dspdb' );
    $sql = "SELECT message FROM linkshells WHERE linkshellid = 1";
    $res = $db->query( $sql ) or die('Failed to query for character keyitems.');
    $row = $res->fetch_assoc();
    print(parse_autotranslate($row['message']));
?>

However, the key item translation is not correct. There are multiple key item tables that the game will uses.
0x13 is not the only one for key items.