The Rube Goldberg Integration



  • I work for a tribal casino. This is a new gig for me. I have only been here ~3 months.

     

    Recently the casino decided to replace its aging player tracking system with a spanky new system built by a gaming power house well known in Vegas. The system is a fairly standard player rewards club.

     

    Our Keno system sends messages every 15 minutes in burst to whatever IP address and port it’s given.  If no response is received it keeps the data in an internal database and tries again in 15 minutes.

     

    In the old tracking system, a .NET windows service was written that listened to the specific port, received the message and called a set of stored procedures that logged each transaction into the database. In the event that the service lost connection to the database, the stored procedure calls were logged to a text file. This would allow someone to run the log file in query analyzer to insert the failed data. Pretty simple.

     

    During the discovery phase of the implementation, someone provided the vendor with the error log file from the windows service. This is where things go sideways a bit.

     

    The day we are set to roll out this spanky new system, someone asks, “hey where did this log file come from? “ And that is when they brought me in to take a look at this process.

     

    Using Reflector, I was quickly able to tell them exactly the way the windows service worked and offered to modify the set of stored procedures to work with the new tracking systems database. All I needed was some form of documentation on what needs to be called in the database to log keno data.

     

    The vendor sadly didn’t see it as such an easy fix. Apparently they have an “incomprehensible by mere mortals” data structure that I would never be able to understand. In the mean time, we came up with the band-aid solution of having our windows service generate the log file of stored procedure calls they were expecting.

     

    I quickly write a stored procedure that outputs to a log file. We then started noticing all kinds of oddities. The system would randomly decide it was not going to import a file. The system event log was filled with type conversion errors. And it absolutely would not process a log file over 200k.

     

    We reported these issues to the developer wizards at our vendor and were given the run around answer of “we are looking into it.”

     

    I was tasked with digging in and finding out what the hell was going on. I launched my trusty copy of Reflector and here is what I found.

     

    1. Every 15 minutes the keno system would send a burst of player activity data to the server.
    2. The windows service would accept the data and call a stored procedure in the player tracking database.
    3. The stored procedure would output keno data as a sql stored prodecure call to a log file.
    4. Every hour the log file would be copied into an “Import” folder.
    5. A separate windows service would fire off a process to import the new file from the import folder.
    6. This new process would copy the log file into a new temp file but slyly wrapped it with a root tag. Seriously, <ROOT>ENTIRE CONTENTS OF LOG FILE</ROOT>
    7. The service would then perform and XSL transform on the “XML” log file that created a well formed XML document.
    8. Then a stored procedure named loadXML is called in the player tracking database.
    9. The stored procedure would load the XML into a temporary table using the trusty xp_cmdshell to TYPE the document.
    10. The temp table is then cursor’d over one row at a time, performing string manipulation to strip all the XML out and just get to the values.
    11. Finally once all the values have been parsed out, the stored procedure “DoRating” is called.

     

     



  • But... they get to put so many acronyms in their marketing materials!



  • I don't get it. Are you saying that XML is a bad format?

    The real WTF is using stored procedures, which should be replaced by quick Perl scripts on the application server.
     



  • No, I am saying the entire process as designed by our vendor is just shy of retarded.

     

    Did I mention their multi million dollar player tracking system uses a front end client made in Access with a direct connection to the database?



  • [quote user="Bobby.NET"]Did I mention their multi million dollar player tracking system uses a front end client made in Access with a direct connection to the database?[/quote]

    ...damn. makes me think twice about going to foxwoods. on the other hand.... :D



  • [quote user="Bobby.NET"]The stored procedure would load the XML into a temporary table using the trusty xp_cmdshell to TYPE the document.

    The temp table is then cursor’d over one row at a time, performing string manipulation to strip all the XML out and just get to the values.[/quote] 

     

    Yes, that's right.  They make XML, then unmake it, except that there was no point in transforming it into XML.  That and the xp_cmdshell.

     

     



  • [quote user="Benanov"]

    Yes, that's right.  They make XML, then unmake it, except that there was no point in transforming it into XML.  That and the xp_cmdshell.

     

     

    [/quote]

     

    It really smacked of a *IO walking in and stating this application needs more XML... All of our competitors are using XML, we need XML... and the Dev saying ok... we now use XML... 


Log in to reply