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:
/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.