Act 1 (Co-keypad) Rabbit arrival: 19:00am Par arrival: 19:03am Average arrival: 19:00am 60 minutes times 5 people times 16 teams = 4800 manminutes 2 people need 15 minutes to solve puzzle, so 30 manminutes per puzzle 4800/30 = 160 puzzles Have 256 available? Each team trigger breaks 16 devices. ---------- Act 2 (CMM) Rabbit arrival: 5:55am Par arrival: 7:29am Ending: 8:00am Average arrival? 7:10pm People: 5 * 16 = 80 Solve-time: 10 minutes? so, in 50 minutes, 400 puzzles get solved? Have 640 available? Each team trigger breaks 40 devices. --------------- Table structure: DROP TABLE IF EXISTS `assignments`; CREATE TABLE `assignments` ( `puzzle_id` smallint(6) NOT NULL, `solver_id` tinyint(4) NOT NULL, `assign_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `abandoned` tinyint(1) NOT NULL, KEY `puzzle_id` (`puzzle_id`,`solver_id`) ); DROP TABLE IF EXISTS `released`; CREATE TABLE `released` ( `puzzle_id` smallint(6) NOT NULL, `release_state` tinyint(1) NOT NULL, `change_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `puzzle_id` (`puzzle_id`) ); DROP TABLE IF EXISTS `scores`; CREATE TABLE `scores` ( `puzzle_id` smallint(6) NOT NULL, `score_value` mediumint(9) NOT NULL, UNIQUE KEY `puzzle_id` (`puzzle_id`) ); DROP TABLE IF EXISTS `solvers`; CREATE TABLE `solvers` ( `solver_id` tinyint(4) NOT NULL, `role_id` tinyint(4) NOT NULL, `name` varchar(60) COLLATE utf8_bin NOT NULL, `passcode` varchar(6) COLLATE utf8_bin NOT NULL, `present` tinyint(1) NOT NULL, `fake` tinyint(1) NOT NULL, UNIQUE KEY `solver_id` (`solver_id`) ); DROP TABLE IF EXISTS `solves`; CREATE TABLE `solves` ( `puzzle_id` smallint(6) NOT NULL, `solver_id` tinyint(4) NOT NULL, `solve_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY `solver_id` (`solver_id`) ); DROP TABLE IF EXISTS `requests`; CREATE TABLE `requests` ( `request_id` mediumint(9) NOT NULL AUTO_INCREMENT, `puzzle_id` smallint(6) NOT NULL, `solver_id` tinyint(4) NOT NULL, `request_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `request_type` tinyint(4) NOT NULL, `display` tinyint(1) NOT NULL, PRIMARY KEY (`request_id`) ); DROP TABLE IF EXISTS `chartdata`; CREATE TABLE `chartdata` ( `chart_num` tinyint(4) NOT NULL, `quantity` mediumint(9) NOT NULL, `color` varchar(20) NOT NULL, `name` varchar(15) NOT NULL, PRIMARY KEY (`chart_num`, `name`) ); --------------- mysqladmin create DB_name -u DB_user --password=DB_pass && \ mysqldump -u DB_user --password=DB_pass DB_name | mysql -u DB_user --password=DB_pass -h DB_host DB_name username: laboratory passsword: forscience ------------------- SELECT * FROM released WHERE release_state = TRUE; SELECT * FROM released JOIN solves ON released.puzzle_id = solves.puzzle_id WHERE release_state = TRUE; SELECT released.puzzle_id, score_value FROM released, scores WHERE release_state = TRUE AND released.puzzle_id = scores.puzzle_id AND NOT EXISTS ( SELECT solves.puzzle_id FROM solves WHERE released.puzzle_id = solves.puzzle_id ); SELECT released.puzzle_id, role_id, solve_time, score_value FROM released, solves, solvers, scores WHERE released.puzzle_id = solves.puzzle_id AND released.puzzle_id = scores.puzzle_id AND solves.solver_id = solvers.solver_id AND release_state = TRUE; SELECT * FROM assignments WHERE puzzle_id = 4 ORDER BY assign_time; ============== GC NOTES ==Delete everything in a table: TRUNCATE TABLE tablename; ==Reloading the puzzle information: mysql -Dtrenchwood -u laboratory --password=forscience < /home/whuang/prog/keypad/puzinfo.sql mysql -Dpeachfrontier -u laboratory --password=forscience < /home/whuang/public_html/cmm/puzinfo.sql (sql file was generated by /home/whuang/public_html/cmm/select-puzzles.pl) ==Reloading team information: TRUNCATE TABLE solvers; LOAD DATA INFILE '/home/whuang/public_html/nodir/doctorwhen/gross/teams.csv' INTO TABLE solvers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (solver_id, name, role_id, passcode, present, fake); ======================== ======================= SELECT puzzle_id, release_type FROM release_info WHERE team_index = 7 AND release_type < 3 INSERT INTO released (puzzle_id, release_state) VALUES (43, TRUE) SELECT DISTINCT released.puzzle_id, score_value FROM released, scores, assignments WHERE release_state = TRUE AND released.puzzle_id = scores.puzzle_id AND assignments.puzzle_id = scores.puzzle_id AND assignments.solver_id = 11 AND NOT EXISTS ( SELECT solves.puzzle_id FROM solves WHERE released.puzzle_id = solves.puzzle_id ) ORDER BY scores.puzzle_id