General Notes

From US Go Wiki
Jump to: navigation, search

Contents

Miscellaneous


mysql> SELECT Pin_Player_2 as Pin_Player, p.Country_Code, p.Last_Name, p.Name, p.Club, g.Tournament_Code, Tournament_Date, 
Tournament_Descr, City, Round, Color_1 as Color, Handicap, Komi, if(result=color_1, 'Win', if(result=color_2,'Loss','Jigo')), 
Result FROM games g, players p, tournaments t WHERE g.Pin_Player_1 = 6079 AND g.Pin_Player_2 = p.Pin_Player AND 
g.Tournament_Code = t.Tournament_Code;
+------------+--------------+-----------+--------+------+-----------------+-----------------+--------------------+------+-------+-------+----------+------+-------------------------------------------------------------+--------+
| Pin_Player | Country_Code | Last_Name | Name   | Club | Tournament_Code | Tournament_Date | Tournament_Descr   | City | Round | Color | Handicap | Komi | if(result=color_1, 'Win', if(result=color_2,'Loss','Jigo')) | Result |
+------------+--------------+-----------+--------+------+-----------------+-----------------+--------------------+------+-------+-------+----------+------+-------------------------------------------------------------+--------+
|      13220 | US           | Britt     | Phil   | TACO | port20051023    | 2005-10-22      | Portland Fall 2005 |      |     2 | W     |        0 |    0 | Loss                                                        | B      |
|       9473 | US           | Peters    | Glenn  | PORT | port20051023    | 2005-10-22      | Portland Fall 2005 |      |     4 | W     |        0 |    0 | Win                                                         | W      |
|      11728 | US           | Bazzano   | Justin | CORV | port20041030    | 2004-10-30      | Portland 2004      |      |     2 | W     |        0 |    0 | Win                                                         | W      |
|       7647 | US           | O'Malley  | Robert | CORV | port20041030    | 2004-10-30      | Portland 2004      |      |     3 | W     |        2 |    0 | Win                                                         | W      |
|       5857 | US           | Marsh     | Gordon | PORT | port20041030    | 2004-10-30      | Portland 2004      |      |     4 | W     |        0 |    7 | Loss                                                        | B      |
|       9473 | US           | Peters    | Glenn  | PORT | port20041030    | 2004-10-30      | Portland 2004      |      |     5 | W     |        4 |    0 | Loss                                                        | B      |
+------------+--------------+-----------+--------+------+-----------------+-----------------+--------------------+------+-------+-------+----------+------+-------------------------------------------------------------+--------+
6 rows in set (0.00 sec)

mysql>


mysql> select count(*) from players p, games g where p.Pin_Player = 2802 and ((p.Pin_Player = g.Pin_Player_1) and 
(upper(g.Color_1) = upper(g.Result)) or (p.Pin_Player = g.Pin_Player_2) and (upper(g.Color_2) = upper(g.Result)) );
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql>

Future Database Changes

  • Add rank/grade table. Replace all explicit ranks/grades with table references.

Database Schema

Not pretending to know Date's work, yet the following seems clear:

  • The number of games and t_games is identical: 428840
  • There is significant redunancy in the data of t_games, players, and placements
  • There are no foreign keys...a consequence of using MyISAM and MySQL 4.1.9-max. InnoDB and MySQL 5.0 are required for foreign keys
  • The placements table appears to have two main purposes:
    1. player rating before and after tournament
    2. hold Win_Games, Lost_Games, Jigo_Games so that neither games and t_games need be queried for this data
  • The placements table has several fields should be calculated values (Win_Games, Lost_Games, Jigo_Games)
  • Should be a table of player ranks rather than both '3D' and '3d' appearing in the data

Software

  • The EGD runs on Windows using EasyPHP-1.8 with Apache 1.3.33, MySQL 4.1.9, PHP 4.3.10.
  • The AGA web site runs on Linux using MySQL 5.0.22, Apache 2.2.2, and PHP 5.1.6.

Software Required

AWstats

https://ml.ctipc.com/aw/awstats.pl?config=agagd.usgo.org

Web Server Logs

the account can read these logs but cannot obtain an directory listing of /var/log/httpd

  • /var/log/httpd/usgo.org-access_log
  • /var/log/httpd/usgo.org-error_log
  • /var/log/httpd/usgo.org-ssl_access_log
  • /var/log/httpd/usgo.org-ssl_error_log
  • /var/log/httpd/usgo.org-ssl_request_log

Completed Items

Future Database Changes

  • Add a state table. Allow the individual states/provinces to take the place of the countries in the Statistics page display.
  • Remove redundant data from t_games and country. Use joins, based upon Country_Code, in the database to get the data needed. Specifically remove the following fields from t_games:
    • Nation
  • Remove redundant data from t_games and players. Use joins, based upon Pin_Player, in the database to get the data needed. Specifically remove the following fields from t_games:
    • Club_1
    • Club_2
    • Last_Name_1
    • Last_Name_2
    • First_Name_1
    • First_Name_2
  • Remove redundant data from t_games and games. Use joins, based upon sgf_code, in the database to get the data needed. Specifically remove the following fields from t_games:
    • Game_Date
    • Round
    • Pin_Player_1
    • Color_1
    • Pin_Player_2
    • Color_2
    • Handicap
    • Reconstructed_hp
    • Result
    • Elab_Date
  • Remove redundant data from t_games and tournament. Use joins, based upon Tournament_Code, in the database to get the data needed. Specifically remove the following fields from t_games:
    • Tournament_Descr
    • Tournament_Date
    • City
    • Nation

American_Go_Association_Go_Database

Personal tools