I was recently working on a social network back-end when I came across a problem. There was a users table, and these users had to have the option of “friending” each other. And so I created a “connections” table, which associated user to user. There were a few extra pieces but essentially what I ended up with was a table that stated the ID, friendA and friendB. I did not want multiple records for each connection so for each “friend” link there would be only one connection record.

The problem here is when it came time to use code to display the list of a given user’s friends, I hit a bit of a wall. I didn’t think I could arrange the users in any sort of order because I would need to use two queries like this (assuming 1 is your ID as the user trying to query the database for a list of your friends):

		SELECT
		user_to_user.id,
		user_to_user.userA
		FROM
		user_to_user
		WHERE
		user_to_user.userB = '1'

		SELECT
		user_to_user.id,
		user_to_user.userB
		FROM
		user_to_user
		WHERE
		user_to_user.userA = '1'

The first query checks all results where you are the user initiating the “friend” connection, and the second checks results where others initiated the connection with you. I thought I would have to loop through the results with userA and then userB separately.

As it turns out, I was wrong. The UNION operator is used to combine the result-set of two or more SELECT statements. So all I had to do is put UNION between the two queries and finish up with an ORDER BY clause and I was in business. Incidentally, I’ll note that the results of this would have columns id and userA – but what about userB? W3Schools says it best: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION. So all of the results – both userA and userB – will return under the userA column. If I were to swap the queries so I ask for the userB results first, then all results would be as userB.

Ok this has been done before, but everything I’ve read up until now seemed incomplete to me, although what is available was very instructive. For example, Ben Forta’s blog covers Reading MP3 ID3 tags with ColdFusion, while Rupesh Kumar covered Reading MP3 Meta-data from ColdFusion. At first glance the titles almost look like they’re talking about the same thing, but they’re not. The java library “jid3lib” that Ben spoke of could not easily be harnessed to give information such as track length, frequency or bitrate while Rupesh’s code didn’t handle any of the ID3 tag info.

So I decided to combine them.

Firstly you must download the jid3lib and place it in %CFHOME%\runtime\servers\lib and restart the application server.

Secondly, the file I used thanks to Ben Forta.
mp3.cfc:

<cfcomponent>
<!---<cfset mp3File = createObject("java", "coldfusion.util.MP3File").init(".\MP3")>
<cfoutput>mp3File.getBitRate() : #mp3File.getBitRate()# kbps</cfoutput><br>
<cfoutput>mp3File.getFrequency() : #mp3File.getFrequency()# Hz</cfoutput><br>
<cfoutput>mp3File.getVersion() : #mp3File.getVersion()#</cfoutput><br>
<cfoutput>mp3File.getDuration() : #mp3File.getDuration()# Sec</cfoutput><br>
<cfoutput>mp3File.isCopyRighted() : #mp3File.isCopyRighted()#</cfoutput><br>
<cfoutput>mp3File.getChannelMode() : #mp3File.getChannelMode()#</cfoutput><br>--->
    <cfset variables.filename = "">
    <cfset variables.loaded = false>
    <cfset variables.id3tag = "">

    <cffunction name="init" access="public" returnType="mp3" output="false">
        <cfargument name="filename" type="string" required="false">

        <!--- create an instance of the java code --->
        <cfset variables.mp3 = createObject("java", "org.farng.mp3.MP3File")>

        <cfif structKeyExists(arguments, "filename")>
            <!--- read it in --->
            <cfset variables.filename = arguments.filename>
            <cfset read(variables.filename)>
        </cfif>

        <cfreturn this>
    </cffunction>

    <cffunction name="checkLoaded" access="private" returnType="void" output="false"
                hint="Helper function to throw error if no mp3 loaded.">
        <cfif not variables.loaded>
            <cfthrow message="You must first read in an MP3!">
        </cfif>
    </cffunction>

	<cffunction name="getArtist" access="public" returntype="string" output="false" hint="Returns the Artist.">
		<cfreturn variables.id3tag.getLeadArtist()>
	</cffunction>

    <cffunction name="getAlbumTitle" access="public" returnType="string" output="false"
                hint="Returns the album title.">
        <cfreturn variables.id3tag.getAlbumTitle()>
    </cffunction>

    <cffunction name="getSongGenre" access="public" returnType="string" output="false"
                hint="Returns the song genre.">
        <cfreturn variables.id3tag.getSongGenre()>
    </cffunction>

    <cffunction name="getSongTitle" access="public" returnType="string" output="false"
                hint="Returns the song title.">
        <cfreturn variables.id3tag.getSongTitle()>
    </cffunction>

    <cffunction name="getTrackNumber" access="public" returnType="string" output="false"
                hint="Returns the song title.">
        <cfreturn variables.id3tag.getTrackNumberOnAlbum()>
    </cffunction>

    <cffunction name="getYearReleased" access="public" returnType="string" output="false"
                hint="Returns the song's release date.">
        <cfreturn variables.id3tag.getYearReleased()>
    </cffunction>

    <cffunction name="hasID3V1" access="public" returnType="boolean" output="true"
                hint="Returns true if the mp3 has id3v1 information.">
        <cfset checkLoaded()>

        <cfreturn variables.mp3.hasID3v1Tag()>
    </cffunction>

    <cffunction name="hasID3V2" access="public" returnType="boolean" output="false"
                hint="Returns true if the mp3 has id3v2 information.">
        <cfset checkLoaded()>

        <cfreturn variables.mp3.hasID3v2Tag()>
    </cffunction>

    <cffunction name="read" access="public" returnType="void" output="false">
        <cfargument name="filename" type="string" required="true">

        <!--- does the file exist? --->
        <cfif not fileExists(arguments.fileName)>
            <cfthrow message="#arguments.fileName# does not exist.">
        </cfif>

        <!--- copy to global scope --->
        <cfset variables.filename = arguments.filename>

        <cftry>
            <cfset variables.mp3.init(variables.filename)>
            <cfset variables.loaded = true>

            <cfif hasID3V1()>
                <cfset variables.id3tag = variables.mp3.getID3v1Tag()>
            </cfif>
            <cfif hasID3V2()>
                <cfset variables.id3tag = variables.mp3.getID3v2Tag()>
            </cfif>

            <cfcatch>
                <cfthrow message="Invalid MP3 file: #arguments.filename# #cfcatch.message#">
            </cfcatch>
        </cftry>
    </cffunction>

</cfcomponent>

Next, I modified Rupesh’s code to include the coldfusion.util.mp3file class – which you’ll notice I put inside the file loop because the class will not read a directory but will only read an individual file. I just made this the index.cfm file but it can be anything.

<cfset mp3 = createObject("component", "mp3").init()>
<cfset dir = "C:\ColdFusion9\wwwroot\Media Library\MP3\">

<cfdirectory action="list" directory="#dir#" filter="*.mp3" name="music">

<cfloop query="music">
	<cfoutput>filename = #name#<br></cfoutput>
	<cftry>
		<cfset mp3.read(dir &amp; name)>
		<cfset mp3File = createObject("java", "coldfusion.util.MP3File").init(dir & name)>
		<cfset minutes = #mp3file.getDuration()#>
		<cfset minutes = #numberformat(int(minutes/60),"00")#>
		<cfset seconds = #mp3File.getDuration()# mod 60>
		<cfoutput>
			artist: #mp3.getArtist()#<br>
			title: #mp3.getSongTitle()#<br>
			album: #mp3.getAlbumTitle()#<br>
			runtime : #minutes#':#numberformat(seconds,'00')#"<br>
			bitrate: #mp3File.getBitRate()# kbps<br>
			genre: #mp3.getSongGenre()#<br>
			track: #mp3.getTrackNumber()#<br>
			year: #mp3.getYearReleased()#<br>
			frequency: #mp3File.getFrequency()# Hz
		</cfoutput>
	<cfcatch>
		bad file <cfoutput>#cfcatch.message#</cfoutput>
	</cfcatch>
	</cftry>
	<hr>
</cfloop>

At this point it only reads files in a single directory and will not recursively check any subfolders. The output is pictured below:

I’m open to any suggestions on how to extend the class or code to include subfolders – obviously I derived most of the code based on the genius of others so I’m a bit hazy on how to take the ball and run with it. Another problem is the fact that the genre cranks out a genre code unless it’s a custom genre. You’ll notice Styx and Toto both have a genre code of 17 in the image above. This corresponds to “Rock”, but for some reason It won’t show a string.

I arrived in San Diego safely this afternoon after a layover in Seattle.

It’s been a long day since my son decided he needed to wake up at 5:30 this morning and potty and wouldn’t return to sleep.

I’m staying in a Hilton and let me tell you, the thread-count on this bed is unbelievable.  So soft I can barely handle it. This room in general is pretty off-the-hook. I would post some pictures but I forgot my transfer cable back at home and the card is not compatible with my laptop.

I’ve forgotten about the size of American food portions. The layover in Seattle left us hungry so we made a quick stop at Burger King in the airport. The man asked me if I’d like a large drink, and my business partner quickly said to me “remember you’re in the states now”, which prompted me to ask for a medium. The man then presented a cup equivalent to the Super Big Gulp available at 7-11 (about a gallon). I then said I’d take a regular which is about the size of a Canadian large.

Not that I’m complaining. At least you get your money’s worth.

I just created my first Hello World page in ColdFusion. This seems like a strange step backwards to me but firstly I had never made one before (again, because it was too simple to even bother), and I was trying to verify that my local ColdFusion environment was functioning properly.

Just wiped my laptop again to prepare for my trip in the morning to San Diego.

This is as much for my own posterity as for any readers who haven’t read it, John Whish’s “Why Use ColdFusion?” post. I’d like to keep this on record for any clients asking me the same question.

It’s that time of year again, of particular importance this year due to the fact that business has been agonizingly slow for the last year, but has now picked up to the point that it has become necessary to travel south of the border. South of the Canadian border, that is. I’ll be spending a week or so in a hotel in San Diego locked in a room with my two business partners while we hammer out some ideas on a gargantuan whiteboard.

I’ve had further opportunity to play with ColdFusion 8/9, but I haven’t discovered anything particularly noteworthy aside from finding that I suck at cfscript. I vastly prefer tag syntax, but I’ve done a bit of reading on it and it seems like something I’ll need to know, even though it is similar to JavaScript with I have a hate/hate relationship with.

I have enjoyed using ColdFusion Builder Beta. I don’t generally need “Live View” from Dreamweaver anymore – I am quite comfortable using a simple text editor so Dreamweaver comes across as a bit bloated at this point for my purposes. ColdFusion Builder has just what I need and it seems to do it well.

This last week has been prep work for a project that has been looming without funding for quite a long time (and has recently been funded). I fly to San Diego tomorrow morning and after that I suspect there will be plenty to write about; I only hope I have time to do so.

I just discovered I’m not even in the top 10 pages of Google results when searching my name.  How disappointing. Will have to remedy this soon and crush all other Andrew McLeans in the way.

One of the projects I’m working on has a number of disk space requirements and I decided to play around with CFCHART to give me an “at-a-glance” idea how much space is left on the drive without having to go into the server backend. I mostly used tips from Ray Camden’s site here.

Not much to it – like I said, it’s just an experiment so I didn’t jazz it up at all, but it works.

<cfset fileOb = createObject("java", "java.io.File").init("/")>

<cfset usable = #fileOb.getUsableSpace()#/1024/1024/1024>
<cfset total = #fileOb.getTotalSpace()#/1024/1024/1024>

<cfset used = total-usable>

<cfchart format="flash" chartHeight = "300" chartWidth = "600" foregroundcolor="Blue">
<cfchartseries type="pie" colorlist="blue,red">
<cfchartdata item = "Available" value = "#round(usable)#">
<cfchartdata item = "Used" value = "#round(used)#">
</cfchartseries>
</cfchart>

I’m working off my laptop with a 220 GB partition, 60 of which is taken. It says 161 free because it’s rounding, but the numbers are essentially right. The repeated use of 1024 simply bumps the value up from bytes to kilobytes to megabytes to gigabytes. Everything else is pretty self-explanitory. Good thing Ray Camden is around because I never would have figured out that frst line for myself.

Edit: In case anyone wonders, the first line in which I put “/” can be replaced with a specific drive such as “C:/” or “D:/” but I chose to use the webroot. Any linux path will also work.

If anyone visited the site and was greeted by a “domain deactivated” message over the weekend, it’s just a bit of a hiccup with my hosting provider, it’ll be taken care of within a couple days.

Well things were touch and go for a while there, and stress was shared by all, but I’m back to programming again. Funding for both an old and a new project is imminent and so I returned to work eagerly. Of course nothing ever happens on time so the artificial deadline three weeks past is long gone, but I’d rather wait on funding for a contract than work for the Man.

I created a warm-up app for internal business use. Essentially it activates users for a service without them needing to pay for it. It was something we had to do manually before and was getting to be a pain. But those days are gone now and I’ve had a chance to get back into things, and in the meantime was reminded of the fact that linux is sensitive to camelCase SQL queries.

I went tubing in a river a couple days back, and with my wife out of town it was all too easy to blow off the sunscreen. Three hours later everyone tells me I’m really pink and I don’t believe them. But I sure feel it now.