Monthly Archives: June 2014

Fun With SQL (OVER and PARTITION clauses)

0
Posted by admin on June 27, 2014 – 12:02 am
Filed under Hiring, SQL

I’ve been interviewing over the last few weeks for a new position (If anyone has a need for a very experienced .Net developer with some recent Ruby/Rails/Javascript/Coffeescript/JQuery etc chops, shoot me an email!)

At any rate, one company had an interesting SQL challenge.

In a nutshell, given a table with an ID field and a FLAG bit field, find all the consecutive rows where the FLAG field is 1 (ie a bad record).

The trick was that the output needed to look like:

ID RANGE        Error Count
4000-6000       2001

First, lets insert a CSV file into a table….

BULK INSERT Flags
FROM 'c:\dev\darin\sqltest\sqltest.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
GO

They supplied a 1.6 million row sample table, and expected sub 10 second response time. Sooooo, you also might want to add a clustered index to the ID field.

Let’s see some SQL!

With the sample table in place, I could get down to business.

I’ll admit, I had to google the syntax of the OVER and PARTITION clauses in T-SQL. They’re not something I use every day.

First, I needed all the rows, with a group number grouping all the consecutive rows who’s flag value was 1. I used a Common Table Expression for this. I could have used temp tables and it would have resulted in about the same performance, but… what the hey!

WITH parts AS (
   SELECT *, id - ROW_NUMBER() OVER (ORDER BY id) AS grp
   FROM flags
   WHERE flag = 1
)

Next, I needed the count of each group as part of the result set….

WITH counts AS (
   SELECT *, COUNT(*) OVER (PARTITION BY grp) AS cnt
   FROM parts
)

With those two CTEs in place, all that was left was the output Select….

SELECT cast(min(id) as varchar(20)) + '-' + cast(max(id) as varchar(20)) as IDRange, 
   cnt as ErrorCount
FROM counts
WHERE cnt >= 1000
GROUP BY grp, cnt
ORDER BY IDRange

In this case, I only cared about blocks of errors of more than 1000 rows. The concatenated SELECT is just to make a nice output format. realistically, I’d probably only select MIN(ID), MAX(ID), CNT

I haven’t used CTE’s a lot, but I’m finding more and more uses for them.

And the OVER clause is something I really need to get more familiar with.

For the 1.6 million row source table, I was getting sub 2 second response times on this query. No doubt, there’s ways to speed this up, but that’s not too shabby.

PhaseShift and Frets On Fire X Song Packs

2
Posted by admin on June 8, 2014 – 5:19 pm
Filed under Arcade, AutoHotKey, Frets On Fire, Games, Guitar, Stepmania

PhaseShift is an excellent freeware (not open source yet, as far as I can tell, but here’s hoping) version of the RockBand genre of games. It’s even more interesting in that it can interoperate with guitar controllers, game drum kits, vocals, keyboards, MIDI, and even step pads (for Dance Dance Revolution style games, all at the same time!).

Very cool stuff.

What’s even better is that it can read both Frets on Fire songs as well as Stepmania format songs.

Mostly.

There are a ton of great song packs available out on the web, and most that I’ve found work seamlessly with PhaseShift. However, I’ve run into a bunch that do not.

The problem appears to be with the NOTES.MID file (the file that contains all the midi notes that is used by the game to show the onscreen “notes”).

After some digging using a hex editor (my favorite right now is Tiny Hexer), I discovered that in all the cases that failed, the NOTES.MID file appears to contain extranous junk. In many cases, it was a string of keyboard key names, in others it was code snippets. I’m guessing that whatever MIDI editor was used to generate those MID files didn’t properly clear garbage out or compact memory.

At any rate, FoFix appears to ignore the extra junk but PhaseShift does not.

Enter EOF

EOF is a song editor for “fretting” songs for use with rhythm games like Frets On Fire and PhaseShift. The latest version even has specific features to take advantage of elements of PhaseShift that don’t exist in Frets On Fire.

I found that simply using FILE – IMPORT MIDI and importing the NOTES.MID file into EOF, then immediately SAVING the file (and clicking YES to the prompt of “The file hasn’t changed, Save Anyway?”), fixed the problem for every single song I tried it on.

It worked so good in fact, that I wrote up a simple script in AutoHotKey to just run through all the files in a directory and perform those magic steps on them

/*
Convert songs via EOF
*/


F12::
Reload
return


F11::
loop, 1 {
    ;match anywhere in title
    SetTitleMatchMode, 2
    IfWinExist \Songs\
    {
        WinActivate
    }
    else
    {
        msgbox No Window
        return
    }

    ;Copy full path name
    sleep 500
    SendPlay ^+C
    sleep 500
    SendPlay ^+C
    ;msgbox %clipboard%

    ;Over to EOF
    IfWinExist EOF -
    {
        WinActivate
    }
    else
    {
        msgbox No EOF
        return
    }


    sleep 500
    SendInput {F6}{F6}
    sleep 500
    SendInput %clipboard%{enter}
    sleep 1000
    Send ^s
    sleep 500
    Send ^s!FS
    sleep 200
    SendInput Y
    sleep 200
    SendInput Y


    ;Back to DirOpus
    IfWinExist \Songs\
    {
        WinActivate
    }
    else
    {
        return
    }

    sleep 500
    SendPlay {down}

}

Change the loop value from 1 to some count if you want to run it through more than one song at a time.

To explain:

The F12 key I’ve mapped to a reload just to make editing this script easier. It’s not necessary for actually running the script.

The F11 key hooks to the secret sauce.

I used the SEARCH feature in Directory Opus to list only the NOTES.MID files from all songs in all subdirectories of a folder I was targeting. If you don’t have DirOpus, you’ll need to recode the script to accommodate some other logic for getting the list of full pathnames to process.

The script starts by activating the DirOpus window and copying the full path to the selected NOTES.MID file.

Then it switches to EOF, (which needs to already be loaded), and performs the FILE – IMPORT function, pasting in the filename of NOTES.MID.

It then immediately does a FILE – SAVE (and supplies a few Y keypresses to answer the “save anyway” prompt.

And finally, it switches back to DirOpen and moves down to the next file.

It’s a hack to be sure. But it worked a treat for me.

Rock on!

Frets On Fire X From Source Code

2
Posted by admin on June 3, 2014 – 11:40 am
Filed under Games, Python

poseNOTE: In the process of putting together this post, I discovered both the game PhaseShift, and a fantastic theme for it based on RockBand 3. PhaseShift appears to be everything I was looking for in modifying FoFix and a WHOLE LOT MORE. I pressed on with this post for two main reasons:

  1. It might serve to help anyone else who may be interested in continuing on with more development on FoFix
  2. I had a lot of fun getting everything together and just seeing it work from source.

Still, I highly recommend checking out PhaseShift and the themes that are available for it. There seems to be a lot more work going on with it these days than any of the other rhythm games out there.

If you haven’t played it, Frets on Fire is a RockBand clone from back around 2009. You can read more about the genesis of the game here.

It’s written in Python and open source, so there have been a number of mods made to the original game over the years.

By far the best, in my opinion, is the Frets on Fire X mod, usually called FoFix. Tons of great changes and improvements, plus a fantastic looking theme called ‘RockBand’.

The unfortunate part is the FoFix (and Frets on Fire in general) appear to have fallen by the wayside in terms of people working on them. That’s really too bad, as

it’s a fantastic game which works quite seamlessly.

Still, as good it as it, it could always be better, right?

Features

Really, all I set out to do was add code to the song selection list that would show what instruments were available in each song, without having to actually pick the song and then getting a “This song has no drum part” message. Pretty simple stuff. But this is Python, and open source, and while I’ve worked with open source before, Python was a new beast for me to tangle with.

Getting Organized

If you’re like me, you already have a copy of FoFix installed, it’s just that you run the EXE version of the game, and not the source code version.

If you want to make any changes, you’ll need to be running from the source code itself. That’s what all this is about.

First things first though. This all might not be strictly necessary, but I don’t want to wreck my playable install of the game, so…

  1. Copy the entire FoFix installation to somewhere else, I created a folder called FoFix35 (since 3.121 was the current version).  However, If you have a LOT of songs in your \data\songs folder, don’t copy all of them. There’s no need. Just a few will do. So, at this point, you should have 2 root folders somewhere:
    \FoFix (your original Frets On Fire X installation)
    \FoFix35 (the copy, minus most of the songs)
  2. Create a \FoFix35\src folder if it doesn’t already exist (this is where you’ll want the source files)
  3. Create a \FoFix35\installs folder (this is where I downloaded all the necessary files, see the next section)

The Downloads

Download all these files into the installs folder you created above.

First, the code. You’ll likely want the most recent, active version of FoFix, which, as far as I can tell is 3.121

https://codeload.github.com/fofix/fofix/zip/Release_3.121

Python (Duh, right? You’ll need the 2.6 version)

https://www.python.org/ftp/python/2.6.6/python-2.6.6.msi

PIL (Python Imaging Library, you’ll want 1.1.6, not 1.1.7!)

http://effbot.org/media/downloads/PIL-1.1.6.win32-py2.6.exe

PyAudio

http://people.csail.mit.edu/hubert/pyaudio/packages/pyaudio-0.2.4.py26.exe

PyGame (I used the 1.9.2a version, but if that doesn’t work for you, try installing 1.9.1 over it)

http://www.lfd.uci.edu/~gohlke/pythonlibs/zit7bosa/pygame-1.9.2a0.win32-py2.6.exe

http://pygame.org/ftp/pygame-1.9.1.win32-py2.6.msi

PyOpenGL

http://www.lfd.uci.edu/~gohlke/pythonlibs/zit7bosa/PyOpenGL-3.1.0b2.win32-py2.6.exe

PyOpenGL Accelerator

http://www.lfd.uci.edu/~gohlke/pythonlibs/zit7bosa/PyOpenGL-accelerate-3.1.0b2.win32-py2.6.exe

PyWin32

http://softlayer-dal.dl.sourceforge.net/project/pywin32/pywin32/Build216/pywin32-216.win32-py2.6.exe

The Win32 Dependency Pack

https://fofix.googlecode.com/files/fofix-win32-deppack-20130304.zip

And finally, NumPy (Note: you’ll want this version in particular, older versions didn’t work for me)

http://softlayer-dal.dl.sourceforge.net/project/numpy/NumPy/1.8.1/numpy-1.8.1-win32-superpack-python2.6.exe

 

Installing Everything

First, unzip the Source code zip file into the \src folder you created above. When done, you should have

\FoFix35\src\midi

\FoFix35\src\scripts

\FoFix35\src\win32

plus a bunch of *.py files in the \FoFix35\src folder itself

 

Next, dblclick the Python installation exe and work your way down the list above, till you get to the Win32 dependency pack. Just accept the defaults and you should be able to just install each one after the other.

The dependency pack itself is a zip file that needs to be unpacked in a specific spot.

The zip contains a root folder, ‘deps’. This ‘deps’ folder should be unpacked into:

\FoFix35\src\win32

such that when you’re done, you end up with:

\FoFix35\src\win32\deps

and this deps folder contains ‘bin’, ‘include’ and ‘lib’ folders.

Finally, finish up with the numpy package.

Checking it out

If everything has gone well, you should be able to navigate to

\FoFix35\src\scripts

and run

RunFofFromSources.bat

FoFix should compile and open its normal intro window.

If it doesn’t, be sure to check the log file

\FoFix35\src\fofix.log

This should give you a pretty good indication of what’s gone wrong.

Wrapping Up

If things do go wrong, first check the log I mentioned above.

I ran into several issues while working through this process.

  • I got the wrong versions of some libraries, based on outdated information on the web (Yeah, this post might eventually end up in that same boat!).
  • I started with the wrong version of Python (2.4, you need 2.6).
  • NumPy gave me some issues, in that I didn’t have the right version.
  • The Python Imaging Library I had to back out to an older rev to get functioning

I stuck with the 32bit version of everything. It might be possible to get 64 bit working, but I didn’t go that route.

I’ve played FoFix for almost 4 years now, using 2 Ashley Rock Axe wireless controllers, as well as a Red Octane wireless drum controller. Great fun! And now I can experiment with some Python coding to boot!