AGAGD Database Schema

From US Go Wiki
Jump to: navigation, search

Contents

biography

DROP TABLE IF EXISTS `biography`;
CREATE TABLE `biography` (
  `Pin_Player` int(8) NOT NULL default '0',
  `Bio_Type` char(1) NOT NULL default '' COMMENT 'H=Html; T=txt',
  `Biography` mediumtext,
  `photopath` char(255),
  `Elab_Date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Pin_Player`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Insert Into biography (Pin_Player, Biography, photopath) Values (0, 'The "no player" player', './Gallery/NoPhotoOrig.png');

chapter

DROP TABLE IF EXISTS `chapter`;
CREATE TABLE `chapter` (
  `Chapter_Code` char(2) collate latin1_bin NOT NULL default '',
  `Chapter_Descr` varchar(30) collate latin1_bin NOT NULL default '',
  PRIMARY KEY  (`Chapter_Code`),
  KEY `Ind_Country` (`Chapter_Descr`)
);
Insert into chapter (Chapter_Code, Chapter_Descr) values ('19SQ', '19 Squared Points Go Club');
Insert into chapter (Chapter_Code, Chapter_Descr) values ('KOGO', 'A Measure of Ko Go Club/School');
Insert into chapter (Chapter_Code, Chapter_Descr) values ('ABIN', 'Abington Heights Go Club');
...

country

DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
  `Country_Code` char(2) collate latin1_bin NOT NULL default '',
  `Country_Descr` varchar(30) collate latin1_bin default NULL,
  PRIMARY KEY  (`Country_Code`),
  KEY `Ind_Country` (`Country_Descr`)
);
Insert Into country (Country_Code, Country_Descr, Country_Flag) Values ('AD',  'Andorra', 'andr');
Insert Into country (Country_Code, Country_Descr, Country_Flag) Values ('AE',  'United Arab Emirates', NULL );
Insert Into country (Country_Code, Country_Descr, Country_Flag) Values ('AF',  'Afghanistan', NULL );
...

games

DROP TABLE IF EXISTS `games`;
CREATE TABLE `games` (
  `Tournament_Code` varchar(20) collate latin1_bin NOT NULL default '0',
  `Game_Date` date NOT NULL default '0000-00-00',
  `Round` tinyint(2) unsigned NOT NULL default '0',
  `Pin_Player_1` int(8) unsigned NOT NULL default '0',
  `Color_1` char(1) collate latin1_bin NOT NULL default '_',
  `Pin_Player_2` int(8) unsigned NOT NULL default '0',
  `Color_2` char(1) collate latin1_bin NOT NULL default '_',
  `Handicap` tinyint(2) unsigned NOT NULL default '0',
  `Komi` tinyint(2) unsigned NOT NULL default '0',
  `Result` char(1) collate latin1_bin NOT NULL default '_',
  `Sgf_Code` varchar(26) collate latin1_bin default '0',
  `Online` tinyint(1) default 0,
  `Exclude` tinyint(1) default 0,
  `Rated` tinyint(1) default 0,
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Tournament_Code`,`Game_Date`,`Round`,`Pin_Player_1`,`Pin_Player_2`),
  UNIQUE KEY `Ind_Games` (`Tournament_Code`,`Game_Date`,`Round`,`Pin_Player_1`,`Pin_Player_2`)
);

pin_changes

DROP TABLE IF EXISTS `pin_changes`;
CREATE TABLE `pin_changes` (
  `Old_Pin_Player` int(8) NOT NULL default '0',
  `New_Pin_Player` int(8) NOT NULL default '0',
  PRIMARY KEY  (`Old_Pin_Player`)
)

players

DROP TABLE IF EXISTS `players`;
CREATE TABLE `players` (
  `Pin_Player` int(8) unsigned NOT NULL,
  `EGDID` int(8) unsigned NOT NULL default '0',
  `Last_Name` varchar(30) collate latin1_bin NOT NULL default '',
  `Name` varchar(30) collate latin1_bin NOT NULL default '',
  `Country_Code` char(2) collate latin1_bin NOT NULL default '',
  `State_Code` char(2) collate latin1_bin NOT NULL default '',
  `Club` char(4) collate latin1_bin,
  `Last_Rank` char(3) collate latin1_bin NOT NULL default '',
  `Rating` float(8,6) default NULL,
  `Sigma` float(7,6) default NULL,
  `Tot_Tournaments` int(4) unsigned default '0',
  `Tot_Games` int(5) unsigned default '0',
  `Last_Appearance` varchar(20) collate latin1_bin default 'null_tournament',
  `Elab_Date` date NOT NULL default '0000-00-00',
  `MType` varchar(8) collate latin1_bin NOT NULL default '',
  `MExp` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Pin_Player`),
  KEY `Ind_Players` (`Country_Code`,`Club`,`Last_Name`,`Name`),
  KEY `Reverse_Ind` (`Last_Name`,`Name`,`Pin_Player`)
);
INSERT INTO players (Pin_Player, Last_Name, Last_Rank, Rating, Sigma) values (0, 'Forfeits
to all', '50k', -50, 0);

player_variations

DROP TABLE IF EXISTS `player_variations`;
CREATE TABLE `player_variations` (
  `Pin_Player` int(8) unsigned NOT NULL default '0',
  `Field` varchar(30) collate latin1_bin NOT NULL,
  `Old`   varchar(30) collate latin1_bin NOT NULL,
  `New`   varchar(30) collate latin1_bin NOT NULL
);

ratings

:n
DROP TABLE IF EXISTS `ratings`;
CREATE TABLE `ratings` (
  `Pin_Player` int(8) unsigned NOT NULL,
  `Rating` float(8,6) default NULL,
  `Sigma` float(7,6) default NULL,
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Pin_Player`,`Elab_Date`)
);

sgf

DROP TABLE IF EXISTS `sgf`;
CREATE TABLE `sgf` (
  `Comments_Author` varchar(20) default NULL,
  `Sgf_Code` varchar(26) NOT NULL default '0',
  `Sgf_File` blob NOT NULL,
  `Elab_Date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Sgf_Code`),
  UNIQUE KEY `Tournament_Code` (`Sgf_Code`)
);

states

DROP TABLES IF EXISTS `state`;
CREATE TABLE `state` (
  `State_Code` char(2) collate latin1_bin NOT NULL default '',
  `State_Name` char(35) collate latin1_bin NOT NULL default '',
  PRIMARY KEY  (`State_Code`)
);
Insert into state (State_Name, State_Code) values ('Alabama', 'AL');
Insert into state (State_Name, State_Code) values ('Alaska', 'AK');
Insert into state (State_Name, State_Code) values ('American Samoa', 'AS');
...

tournaments

DROP TABLE IF EXISTS `tournaments`;
CREATE TABLE `tournaments` (
  `Tournament_Code` varchar(20) collate latin1_bin NOT NULL default '0',
  `Tournament_Descr` varchar(80) collate latin1_bin NOT NULL default '',
  `Tournament_Date` date NOT NULL default '0000-00-00',
  `City` varchar(30) collate latin1_bin NOT NULL default '',
  `Nation` char(2) collate latin1_bin NOT NULL default '0',
  `Rounds` smallint(2) NOT NULL default '0',
  `Total_Players` smallint(3) unsigned default '0',
  `Wallist` text collate latin1_bin,
  `Elab_Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Tournament_Code`),
  UNIQUE KEY `Tournament_Code` (`Tournament_Code`)
);

American_Go_Assocation_Go_Database

Personal tools