The More You Know

When working with relational data in a stateless environment (e.g. web applications) it is sometimes not known if something is being created (insert) or being updated. This is generally seen when an object has an 0..N number of sub elements.

When the data from the user is to be processed, it would be best to handle both inserting and updating with a single statement. Developers new to databases generally handle this transaction with multiple statements using conditional “IF” statements to determine the number of rows a result set has. Based on the number of rows (0 or 1) an insert or update is performed. This approach suffers from lag between the logical language (Java, PHP, PL/SQL, etc) and the SQL engine. Even Oracle suffers overhead from this approach due to context switch overhead that is incurred during PL/SQL to SQL (or vice versa) switches.

This performance loss may be trivial or non-trivial based on use. If the process in question will accept thousands of items it can perform poorly. A few hundred rows may not. However, it is possible to write a single version that will work better overall and scale well.

The first step is changing from a multiple query approach to a single query approach. This can be done with UPSERTS or more commonly known as a MERGE statement. In Oracle a MERGE statement is defined by the following syntax:

1
2
3
4
5
6
7

MERGE INTO destination_table dest
    USING (SELECT col1, col2, col3 FROM source_table) source
    ON (dest.col1 = source.col1)WHEN MATCHED THEN
        UPDATE SET dest.col2 = source.col2,dest.col3 = source.col3
    WHEN NOT MATCHED THEN
        INSERT (dest.col1, dest.col2, dest.col3)
      VALUES (source.col1, source.col2, source.col3)

A more complete Oracle MERGE syntax definition can be found here (Oracle 11g Database)

For a more practical example, here is a PL/SQL function that updates a data column on a table with a single primary key named “ID”.

1
2
3
4
5
6
7
8
9

PROCEDURE UPSERT_TEST_BY_MERGE(P_ID NUMBER, P_DATA VARCHAR2) IS
BEGIN
    MERGE INTO MY_UPSERT_TEST_TABLE DEST
    USING( SELECT p_id AS ID, p_data AS DATA FROM DUAL ) src
    ON (DEST.ID = P_ID)WHEN MATCHED THEN
        UPDATE SET DEST.DATA = P_DATA
    WHEN NOT MATCHED THEN
        INSERT(DEST.ID,DEST.DATA) VALUES(P_ID,P_DATA);
END;

The select statement is required in the USING clause of the MERGE statement. In this example a fast dual look up is used to fulfill the USING clause. It is notable that neither the INSERT or UPDATE clauses need to refer to the USING clause data. It is possible to reffer to simple PL/SQL variables that are also valid SQL types (VARCHAR2, NUMBER, etc but NOT TABLE/complex types without casting/treating).

The above example is a good single entry UPSERT/MERGE. However it will still suffer from heavy serial use. Again using this function for thousands of rows from within a “WHILE” loop in PL/SQL will result in performance issues. To solve this issue in PL/SQL collections (read as: arrays) are used in conjunction with a “FORALL” PL/SQL statement.

The following example uses PL/SQL collections with a PL/SQL “FORALL” statement in order to MERGE hundreds of thousands of rows into a table in sub-second speed. It is VERY important to remember that MERGE is a SQL statement and thusly can only work with SQL (not PL/SQL) types. Thusly, a collection is defined as a table of PL/SQL types that are cast or “treated” as schema level types. This requires a PL/SQL type being defined in a package and also as a schema level type. Both definitions need to have matching data type properties and must use SQL safe types only.

The last example uses a PL/SQL table defined as MY_USERT_TYPE. This type is defined as a “TABLE OF MY_UPSERT_ROW”. MY_UPSERT_ROW is defined exactly the same as the schema level object MY_UPSERT_OBJ.

1
2
3
4
5
6
7
8
9
10

PROCEDURE UPSERT_TEST_BY_MERGE_COL( p_records MY_UPSERT_TYP) IS
BEGIN    FORALL i IN 1..p_records.COUNT
        MERGE INTO MY_UPSERT_TEST_TABLE DEST
        USING( SELECT TREAT(p_records(i) AS My_Upsert_Obj) AS obj FROM DUAL ) src
            ON (DEST.ID =  src.obj.id)
        WHEN MATCHED THEN
            UPDATE SET DEST.DATA = src.obj.data
        WHEN NOT MATCHED THEN
             INSERT (id, data) VALUES( src.obj.id, src.obj.data );
END;

If you had the delight of picking up Portal from Valve and beat it, you know all about cake, the lies, and the masterful end song. I found that upon beating it, I kept humming the song and wishing to know the words. Googling to find the song in any format I found the a YouTube link, awesome. That satiated my ears for a little bit, but I found that my MP3 player, desktop, and laptops were all missing the song while I worked. So, I set out to find how to obtain the song from the data files. Most games store songs as either WAV files or MP3 files and I figured there was a good chance that Valve did just that! I was right and it took a little working.

Digging into the directory structure you can find the following directory: \Steam\steamapps\*. This directory holds two very large files name “portal english.gcf” and “portal content.gcf”. It is a common practice for game makers to use a proprietary file format to store contiguous game data in a single file. This allows known pointer offsets in files to allow fast reading from disk. It also turns out that the file extension GFC is commonly for Grid Cache Files. Which is a common format for Steam released games. It also turns out there are many developer out there making programs capable of browsing these data caches without having to hack them yourself (you should, just for fun!). One that I found useful was GFCScrape.

Extraction Tutorial

1. Download GFCScrape & install (or any other GFC editor/browser)
2. Start GFCScrape
3. File => Open
4. Navigate to your Steam installation directory (e.g. C:\Program Files\Steam)
   
5. Open the directory “steamapps” (e.g. C:\Program Files\Steam\steamapps)
6. Open the file “portal content.gcf”
7. Within GFCScrape, navigate to: root\portal\sound\music\
   
8. Drag and drop the file “portal_still_alive.mp3” to your desktop or mp3 directory

Quick Links

Valve Wiki Article About GFCScrape

GCFScrape Home Page

One of my guild officers created WoW Jutsu and it got me thinking how powerful the armory could be if the data could be accessed. Essentially, the WoW Armory could be a web service that allows the player base to do interesting and cool things with the data. In fact, this has become more and more popular as people are using the Armory as a way to obtain census like data and report their findings.

I have a project in the works that may or may not see the light of day, but either way after Googling and inspecting the Armory, I found out how to obtain XML access. It is quite simple, you just need to instantiate a HTTP request with a modern user agent header. Not knowing this, kept my first inspection scripts from obtaining XML, but rather HTML.

Below you will find a simple script that will obtain a list of all players in a specific guild on a given realm. The script uses the Pear library HTTP_Client as well as PHP5’s built in XML abilities. Also the require line (”require_once ‘HTTP/Client.php’;”) for HTTP_Client may have to be changed based on how your Pear libraries are setup/included on your include path.

Example File : WoW Armory Reverse Engineer Example