Psychic Origami

So I’ve now uploaded the results of the saturday morning code-a-thon. This means that you can now browse all of the previous moves and they also have a convenient URL. So if you want to show someone a move that you thought was particularly good/bad you can just send them the link. e.g. in this pretty amazing (fun at least) game against Kennon, I was particularly proud of this move:

http://psychicorigami.com/chrss/game/9/browse/29

Pretty happy with what I’ve got implemented for chrss now. This features take it slightly past the core ability to “just play chess”. Hopefully I can start leveraging the fact that everything is being recorded in the database and is now “just data” that can be manipulated and viewed in different ways. Though I think I might spend a bit of time on some of the more mundane aspects first (like being able to reset your password).

On a more technical note, starting to feel a lot more like I really know my SQL now. Been proficient at it for a while, but I was quite proud of this:

alter table move add column move_num int;

-- temp table for calculating the move numbers
create table tmp_move (moveid int,movenum int);
-- insert move numbers into tmp table
insert into tmp_move (moveid,movenum) select id as moveid, (select count(*) from move m2 where m2.id <= m.id and m2.game_id = m.game_id) as movenum from move m;
-- then update moves from tmp table
update move set move.move_num = (select movenum from tmp_move where tmp_move.moveid = move.id);
-- remove tmp table
drop table tmp_move;

-- increase constraints on the move_num values
alter table move modify column move_num int not null;

alter table move add unique game_move_num_index (game_id, move_num);
alter table move add index move_num_index (move_num);

Which basically adds a “move_num” column to the move table and the calculates the correct value for each move (i.e. whether a move is move 9, 10 etc. in a particular game). Previously I was calculating this on the fly, but that was forcing me to always read all of the moves for a game. This should make things a bit simpler for me down the line. Just a shame I couldn’t do it without a temporary table.

This entry was posted on Friday, March 30th, 2007 at 7:11 am and is filed under Chrss, Development, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.