December 27, 2009

MySQL - Using SELECT to UPDATE records.

I've been experimenting with MySQL over the holidays, specifically evolution of a db schema and how to roll out changes effectively. I setup a scenario where I had a mapping table with two ids. One id from table 1 and one id from table 2. Now, let's assume table 1 and table 2 no longer need a many to many mapping relation, and we can get away with table 2 having a direct foreign key relation to table 1 on a per-record basis. (We also assume there is no many to many information in the mapping table, so, table 1 and table 2 only have on occurence)

So our scenario is, we're moving from a table structure like this:
Table 1: id, value 1, value 2
Mapping: table 1 id, table 2 id
Table 2: id, value 1, value 2

to this:

Table 1: id, value 1, value 2
Table 2: id, table 1 id (FK), value 1, value 2

So I went ahead and altered table 2, but kept the mapping table intact as it still had all the information in it. I thought at first, I could write a script that just took the values from the mapping and ran the updates on table 2 so that the table 1 id from the mapping table was inserted into table 2. What would be the fun in that, if I can't learn to flex my SQL muscles...

I know that MySQL supports nested SELECTs on INSERT statements, but found out that p to and including MySQL 5.5 you can not use a nested SELECT for use in an UPDATE. So, I figured this was a simple work around:

SELECT @TABLE1_ID:=table_1_id, @TABLE2_ID:=table_2_id FROM mapping_table FOR UPDATE;
UPDATE table_2 SET table_1_id = @TABLE1_ID WHERE id = @TABLE2_ID;

This didn't quite work as I expected it would... Result:

SELECT @TABLE1_ID:=table_1_id, @TABLE2_ID:=table_2_id FROM mapping_table FOR UPDATE;# Rows: 3
UPDATE table_2 SET table_1_id = @TABLE1_ID WHERE id = @TABLE2_ID;# 1 row(s) affected.

Looks like it used the highest @TABLE2_ID from the mapping table and ran the update on that id. Once.

What started as something "simple" had now turned into re-learning SQL procedures and cursors.

This cursor takes care of the limitation encountered above where only the first row in the result set was being updated. Please note this is meant for command line mysql as the creation of this procedure relies on the 'delimiter ' instruction.


DROP PROCEDURE IF EXISTS fix_mapping;
delimiter //
CREATE PROCEDURE fix_mapping()
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE table1_id, table2_id INT;
DECLARE select_cursor CURSOR FOR SELECT mapping_one, mapping_two FROM mapping_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN select_cursor;

FETCH select_cursor INTO table1_id, table2_id;
WHILE
 finished <> 1
DO
 UPDATE table2 SET value_from_mapping_two = table2_id WHERE id = table1_id;
 FETCH select_cursor INTO table1_id, table2_id;
END WHILE;

CLOSE select_cursor;
END//
delimiter ;
CALL fix_mapping;

Note: If you encounter 'ERROR 1436 (HY000): Thread stack overrun:' go increase the thread_stack value in your my.cnf file. Mine was set to 64K, the increase to 256K worked for me.

You can validate that your procedure has been created by running: SHOW PROCEDURE STATUS;

When the procedure ran, all the values in the table2.value_from_mapping_two were correct and I could remove the mapping table. Also, don't forget to remove your procedure, as it won't work with the now deleted mapping table, accomplish this by running DROP PROCEDURE `table`.`procedure_name`

December 23, 2009

Flash and JavaScript

I've been doing some experiments on having a small SWF communicate with JavaScript (and vice versa). Essentially it's a little pop sound that gets triggered on a certain event completion by JavaScript. (Think GTalk in GMail or Facebook chat pop notifications).

I first attempted to have a single frame with an AudioLayer and simply rewind and play the movie each time I needed the notification to sound. Easier said than done. The Flash file would play immediately when it was loaded and (for whatever reason) was not listening to {play: 'false'} that SWFObject was sending it as a parameter. Also, the JavaScript event listener in my ActionScript was not letting me call gotoAndPlay(1); - go to the first frame and play it.

I should mention I know very, very little about ActionScript and Flash in general.

Regardless, I finally changed my approach and started googling referencing a sound file directly in the library, rather than importing it into the frame and replaying the frame.

I found a great page detailing which steps to follow to reference and play a sound directly from a library (the sound is exported when you publish your fla to SWF).

The solution is to "link" the audio file and give it a class identifier. This way, in my ActionScript, I can simply create a new instance of the sound using its identifier and call play() on the class.

Here's my ActionScript - it's all in a AS3 fla, first (and only) frame - that sets up the external interface listener:

import flash.external.ExternalInterface;

function playNotification():void {
 var sound = new NotificationPop();
 sound.play();
}

ExternalInterface.addCallback("playNotification", playNotification);

Here's my JavaScript that I call to trigger the SWF's external interface function declared above. The #notification id in XHTML refers to the tag that SWFObject generates. You simply call your declared external interface callback directly on .
function playNotification()
{
 $('#notification').playNotification();
}
Thanks to republicofcode.com for the tutorial. Check out the full article here.

December 12, 2009

Xbox Live: The Good, The Bad and The Ugly

I've had my Xbox for just over a 6 weeks now. My trial of Xbox Live Gold went without a glitch. I was gaming merrily with friends on a network I perceived to be superior to PlayStation Network (PSN). Overlooking the fact that the hardware is "terrible" – I can't over look that... rant:

Since the dawn of PS3 (before Slim) and 360 there's been discussion on technical blogs about how "fat" the PS3 is. It's not a small console, but come one people, the Xbox has an external power supply brick that's half the size of the PS3 - and still the 360 is barely smaller. Also, I thought people were exaggerating the fact that the Xbox is loud. They're not. The disc drive is ridiculously loud and when the fans kick in it's like someone plugged in a vacuum cleaner. /rant.

This gives the consoles a draw for me. Sony's hardware is superior to Microsoft's, but Microsoft's strong point is the software. While I find the New Xbox Experience user interface unintuitive and harder to navigate than Sony's XMB, the Live experience is amazing, well worth the $60 / year.

Back to my point, my Gold trial membership went without a glitch - all of this changed in a second when it was time to turn my trial membership into a real membership and pay up. I signed on to xbox.com and attempted to add my credit card information. Long story short - turns out I left my Live Account in the US when I signed up, instead of switching over to Canada. Microsoft has a "limitation" in their billing system that won't let a person change country once the gamer tag was started. DRM anyone? So, I had to "cancel" (more to that later) my account and restart a new gamer tag in Canada so I could add my Canadian credit card on the account - without the option of migrating anything over to the new account. Luckily I've only been playing for a month...

Xbox Live: The Good

  • Provides a fantastic online gaming experience
  • Servers are filled and fast
  • Parties are awesome
  • Better than PSN
The Bad
  • Don't ever think about moving to another country, you'll lose anything
    • Unless, you buy your country's Xbox redeem vouchers online
The Ugly
  • Xbox Live customer support - shockingly terrible
    • I was told by two reps that I could in fact change my country, but they couldn't seem to do it at the moment
    • A supervisor finally told me that I couldn't due to the "limiation" in the billing system
    • You can't cancel your account. Ever.
    • You can "close" your profile, but they will keep your gamer tag active
      • The reason for this, according to the rep "closing" my account, is that I am located in Canada trying to close a US gamer tag.
While I was lucky that I didn't lose much of my achievements and gamer points moving to my new account, it will be quite terrible should I ever move back to Germany.